Forum archive 2000-2006

John Curran - Problem with database on upgrade from WW2.0 to WW2.1

by Arnold Pizer -
Number of replies: 0
 Problem with database on upgrade from WW2.0 to WW2.1 topic started 8/29/2005; 4:24:22 PMlast post 9/8/2005; 12:15:24 PM
 John Curran - Problem with database on upgrade from WW2.0 to WW2.1  8/29/2005; 4:24:22 PM (reads: 2638, responses: 32) I upgraded to Webwork 2.1 from 2.0 (My OS is Fedora Core 2), and am using the sql-single database layout. I get the following error when I try to access the admin course from my webwork2 index page: error instantiating DB driver WeBWorK::DB::Driver::SQL for table problem_user: DBI connect('webwork','webworkRead',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) at /opt/webwork2/lib/WeBWorK/DB/Driver/SQL.pm line 62 I have been unable to find a problem with mysql per se, but reinitialized it anyway. I have granted the appropriate mysql permissions to webworkRead@localhost, webworkWrite@localhost and inserted their passwords into database.conf, but the error message is the same. I am stumped. Does anyone have a sense where I might begin troubleshooting this problem? <| Post or View Comments |>

 John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1  8/29/2005; 6:34:51 PM (reads: 2728, responses: 1) Hi, I would try accessing it from the command line under the names webworkRead and webworkWrite, as in: mysql -uwebworkRead -p then enter the password for webworkRead, then use webwork;show tables;describe admin_set; to see if webworkRead can really see the tables associated to the admin course. John <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  8/31/2005; 10:13:50 AM (reads: 2766, responses: 0) John, webworkRead/Write can see the admin tables using those commands, but the problem persists. <| Post or View Comments |>

 John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1  8/31/2005; 2:10:20 PM (reads: 2690, responses: 0) Hmmm, The reasons I know of for why this can fail are: mysql has all available connections used up. Try restarting the mysql server and try to connect. If you get in, that may have been the problem. The number of allowed connections for mysql can be configured (e.g., in the file /etc/my.cnf. mysql is not actually going (ruled out since you connected by hand) there is a file permissions problem (also ruled out since you connected by hand) the password situation is not configured correctly. Double check these values in database.conf. I have seen several variations on how to set passwords and grant permissions inside mysql. Some have worked and others didn't. It still confuses me as to how to best handle that. Maybe you will need to redo that part from the documentation in the webwork 2 twiki. Good luck. John <| Post or View Comments |>

 Davide P. Cervone - Re: Problem with database on upgrade from WW2.0 to WW2.1  8/31/2005; 3:02:07 PM (reads: 2660, responses: 1) Were you using sql-single with your 2.0 installation? That is, did sql work with your previous installation and broke as a result of the upgrade, or is this the first time trying to configure sql with WW? Davide <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  8/31/2005; 9:15:52 PM (reads: 2760, responses: 0) I was using the sql database format (but not sql-single) with WW 2.0. I did have a working system, insofar as I could use the admin course and use it. <| Post or View Comments |>

 Michael Gage - Re: Problem with database on upgrade from WW2.0 to WW2.1  8/31/2005; 9:38:22 PM (reads: 2670, responses: 0) John Jones summary of what could be going on seems pretty complete to me. You can connect to the mysql database by hand, but you can't do that when you try to connect through the webwork script. What happens if you try adding another course (from the command line if you have to) with a different name. Another possibility is to create a gdbm version of the admin course and then use it to add an sql_single version of the course. (Our admin course on our U of R machine is still gdbm based because we have never gotten around to switching it over.) These are somewhat wild ideas, but if one of them works, then you can start working backwards to see what was wrong with your current setup. It's usually easier to trouble shoot from something that works towards the setup you want then to trouble shoot in the reverse direction. Good luck. -- Mike <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/1/2005; 5:11:52 PM (reads: 2648, responses: 0) I managed to create a gdbm version of the admin course from the command line (after setting very loose permissions). I can create gdbm courses via the admin course that seem to work. I still get the same error messages as above after I create a course with a sql format from the admin course or the command line, though. <| Post or View Comments |>

 Michael Gage - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/2/2005; 8:36:28 AM (reads: 2642, responses: 0) I had one more idea -- is WeBWorK looking for mysql in the right place? The specification is in global.conf at $externalPrograms{mysql} = "/usr/bin/mysql"; The default is as above, but our system actually uses /usr/local/bin/mysql. Typing which mysqlor whereis mysql  on the command line will help find where the application is installed. Good luck. --Mike <| Post or View Comments |>  John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1 9/2/2005; 10:57:22 AM (reads: 2646, responses: 0) My system has mysql in /usr/bin/mysql. I am going to reinstall with the 2.1 tarball, and see if I still have the same difficulties. Supposing I still have the same issue, is it possible to move back to 2.0 from 2.1 via cvs? Thank you everyone for your suggestions; I will be sure to report my mistake if I can find it! <| Post or View Comments |>  John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1 9/2/2005; 3:52:27 PM (reads: 2646, responses: 0) Right now, the only course I have created is the admin (back to WW 2.1 and sql-single), and when I try to enter I get error instantiating DB driver WeBWorK::DB::Driver::SQL for table problem_user: DBI connect('webwork','webworkRead',...) failed: Can't ...etc. Question: There is a table named admin_problem_user in the webwork database in mysql, but not one named problem_user... I don't suppose that should matter? <| Post or View Comments |>  John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1 9/2/2005; 5:30:42 PM (reads: 2641, responses: 0) I am not sure, but that does look suspicious. A table named problem_user sounds likes the name of an sql table, not an sql-single table. The file .../webwork2/courses/admin/course.conf should specify which type of database is being used in lines like $dbLayoutName = 'sql_single';*dbLayout = $dbLayouts{$dbLayoutName}; Is that sql or sql_single? John <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/2/2005; 8:07:11 PM (reads: 2616, responses: 1) It's given as sql_single in course.conf & global.conf. <| Post or View Comments |>

 Davide P. Cervone - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/2/2005; 9:16:00 PM (reads: 2710, responses: 0) I assume you created the admin course from the command line. Did you use sql_single when you did it, or just sql? (The sql form is given first in the installation instructions.) Davide <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/2/2005; 9:22:45 PM (reads: 2614, responses: 0) I used sql_single (the shorter command in the course admin document.) I can delete the course via delcourse admin without any other parameters. <| Post or View Comments |>

 Davide P. Cervone - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/2/2005; 10:17:51 PM (reads: 2626, responses: 0) I don't seem to have a problem_user table in my webwork database (or in any database), but I don't know what that indicates, as I don't really know anything about the database layout. I do see that the WW1Hash schema seems to use a table called problem_user. Anyone know how WW1Hash gets used? Should it be used in this situation? Davide <| Post or View Comments |>

 John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 1:47:06 AM (reads: 2628, responses: 0) I think WW1Hash is part of the interface to the WW1 style gdbm database. The error about connecting to problem_user would make sense if you were trying to connect to a course named problem. For what its worth, my test server uses sql for the admin course. I changed the course.conf to sql_single and tried to connect. I got an error for trying to connect to Table 'webwork.admin_user', but even then the error was that the table didn't exist (which is right), instead of that it couldn't connect, like your message said. I assume /usr/bin/mysql has permissions 755? Also, you have just one installation of mysql on the machine? John <| Post or View Comments |>

 John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 2:06:20 AM (reads: 2617, responses: 0) Hi, As another test, I stopped mysql on my machine and tried to access a course. I got exactly the error message you got (except for the number after sock). John <| Post or View Comments |>

 Davide P. Cervone - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 2:24:06 PM (reads: 2681, responses: 0) I don't understand the mysql permissions model very well, but I recall that there are both host and user restrictions. Is it possible that the mysql server is set up to reject connections from the web server account in some way? Perhaps the access is not being made as locahost, but by the full host name or something? You can see the authorized users via the command  mysql> select host,user from mysql.user; within mysql. On my machine, I seem to have entries for both localhost and the full machine name for both webworkRead and webworkWrite. You might try looking at the security documentation for mysql at (and the other sections listed in the left-hand panel). Quoting from this document:  Make sure that the server is running. If it is not running, you cannot connect to it. For example, if you attempt to connect to the server and see a message such as one of those following, one cause might be that the server is not running: shell> mysql ERROR 2003: Can't connect to MySQL server on 'host_name' (111) shell> mysql ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) It might also be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from those on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port, or a --socket option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can do: shell> netstat -ln | grep mysql You might check to see that mysql is actually using the file at /var/lib/mysql/mysql.sock. (Mine uses /tmp/mysql.sock). There are also some user comments (toward the bottom of the page) about configuration file issue that caused similar problems for them having to do with the host addresses. You might find something useful there. Hope that helps. Davide <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 3:41:19 PM (reads: 2595, responses: 1) Thanks Davide. I guess I'll respond in reverse order. From "netstat -ln | grep mysql" and other routes, the system is finding the mysql.sock file, which is in var/lib/mysql. I think that's OK. As to the access denied docs, I think everything checks out OK (from the command line.) I can enter mysql with the basic host (localhost) and user names (root, webworkRead, webworkWrite) without appending @localhost, or things of that kind. (There's one minor difference from the documnetation, that I need to include -p in, e.g, mysql -u root -p test, to access the test database.) I haven't seen the explicit message "Access denied". Is there a log file where it would show up? There seems to be something specific to how the WW scripts are starting mysql that is amiss. I can't get mysql to take the command "select host.user from mysql.user;", I get ERROR 1109: Unknown table 'host' in field list. Was I supposed to substitute something for host? There are host and user tables in the mysql database. What I see in the user table includes webworkRead, webworkWrite, root, localhost, and my machine's name. On the other hand, table host seems to be empty. Perphaps it shouldn't be?  <| Post or View Comments |>

 Davide P. Cervone - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 4:39:58 PM (reads: 2650, responses: 0) As to the access denied docs, I think everything checks out OK (from the command line.) What I'm suggesting is that there is a difference between doing it from the command line and from the Perl commands within WeBWorK, so that the test from the command line are not testing the same thing as WW is using.  I can enter mysql with the basic host (localhost) and user names (root, webworkRead, webworkWrite) without appending @localhost It should default to localhost, so you shouldn't need to specify anything to get that, but you WOULD in order to get something OTHER than localhost. It might be interesting to try mysql -u webworkWrite -h your-host-name -p and see if you can get in.  There's one minor difference from the documnetation, that I need to include -p in, e.g, mysql -u root -p test, to access the test database. Yes, that is normal. I don't know why they don't include it in their examples.  I can't get mysql to take the command "select host.user from mysql.user;" OOPS, there was a typo. It should be host,user not host.user (comma, not dot). Sorry about that.  What I see in the user table includes webworkRead, webworkWrite, root, localhost, and my machine's name. Here's what I see for my server from select host,user from user;: +----------------------+--------------+| host | user |+----------------------+--------------+| localhost | root || localhost | webworkRead || localhost | webworkWrite || omega.math.union.edu | root || omega.math.union.edu | webworkRead || omega.math.union.edu | webworkWrite |+----------------------+--------------+(I have removed lines that don't relate to WW). On closer inspection, however, the ones from omega.math.union.edu don't have any privileges activated, so they probably aren't doing any good. (You can use select * from user; to get the privileges as well, but it is a very wide list.)  On the other hand, table host seems to be empty. Perphaps it shouldn't be? Mine is empty, too. I still think it sounds like a host name problem and that the server is refusing the connection for some reason. The user comments at the bottom of the documentation page have some things to say about that. Davide <| Post or View Comments |>

 John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 6:13:00 PM (reads: 2591, responses: 1) Have you checked the error log for mysql for clues? Based on the path to the .sock file, if your machine name was myserver.la.asu.edu, then the error log would be /var/lib/mysql/myserver.err. John <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 6:49:37 PM (reads: 2648, responses: 0) John, I don't see an .err file in /var/lib/mysql (I assume myserver is the my actual server's name?) I have looked at /var/log/mysqld.log, but it's uninformative, I think it only records start/stop actions for mysql. <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 6:58:16 PM (reads: 2589, responses: 1) Here's what I see in the database for hosts & users: mysql> select host,user,Select_priv,Grant_priv from mysql.user; |host | user | Select_priv | | localhost | root | Y | | vneumann.emich.edu | root | Y | | localhost | | N | | vneumann.emich.edu | | N | | localhost | webworkWrite | N | | localhost | webworkRead | N | [APOLOGIES for the formatting -- post seems to delete white space] I don't know the siginificance of the Select_priv, since I have granted select privileges on the webwork database to webworkWrite, webworkRead. If anything looks strange here, let me know. I'm working through various suggestions from http://dev.mysql.com/doc/mysql/en/access-denied.html and related. Changing the my.cnf file doesn't seem to matter. It is unclear to me whether one wants to access the database using localhost or the explicit IP address: there seem to be advocates on both sides. Am fiddling with these. <| Post or View Comments |>

 Davide P. Cervone - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 9:13:18 PM (reads: 2654, responses: 0) Well, I'm a little concerned about the lack of privileges for webworkRead and webworkWrite. Here are the privileges for the command mysql> select host,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Grant_priv from mysql.user'; on my machine: +-----------+--------------+-------------+-------------+-------------+-------------+------------+| host | user | Select_priv | Insert_priv | Update_priv | Delete_priv | Grant_priv |+-----------+--------------+-------------+-------------+-------------+-------------+------------+| localhost | root | Y | Y | Y | Y | Y || localhost | webworkRead | Y | N | N | N | Y || localhost | webworkWrite | Y | Y | Y | Y | Y |+-----------+--------------+-------------+-------------+-------------+-------------+------------+It's possible that the lack of select privileges could cause this message, but I would have expected an "Access denied" message, not a "can't connect" message. But who knows? I had set these up originally before sql_single was available, and these accounts needed more global access. Now that there is sql_single, they may get away with less. As I said, I don't know all that much about sql. Davide <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 9:23:03 PM (reads: 2644, responses: 0) Do you remember how you set those privileges? Was it just a grant command as in the WW installation manual? webworkRead/Write don't seem to have any listed in my user table, but when I type msql> show grants for webworkRead@localhost; for example, it does list select privileges on webwork.*. I cannot find an alias for webworkRead@localhost that solves my problem. I think I have gone through the non-windows recommendations for access denials from the web site. Does anyone know how to find out what the exact string is WW is using to access the database? If I knew what it was using, I could try to fix the privileges in mysql for it. <| Post or View Comments |>

 John Jones - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/3/2005; 9:47:12 PM (reads: 2594, responses: 0) Hi, I am not sure that the values in the user mysql.user table are the right things to look at. Privileges can be database and even table specific (I think), and I don't see how that is reflected in mysql.user. For the record, the values I have on one system are: +-----------+--------------+-------------+-------------+-------------+-------------+------------+| host | user | Select_priv | Insert_priv | Update_priv | Delete_priv | Grant_priv |+-----------+--------------+-------------+-------------+-------------+-------------+------------+| localhost | root | Y | Y | Y | Y | Y || webwork2 | root | Y | Y | Y | Y | Y || localhost | | N | N | N | N | N || webwork2 | | N | N | N | N | N || % | webworkWrite | N | N | N | N | N || localhost | webworkWrite | N | N | N | N | N || webwork2 | webworkWrite | N | N | N | N | N || localhost | webworkRead | N | N | N | N | N |+-----------+--------------+-------------+-------------+-------------+-------------+------------+ Nosing around, I think the right table is db. The command select host,db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Grant_priv from db; gives +-----------+----------------+--------------+-------------+-------------+-------------+-------------+------------+| host | db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Grant_priv |+-----------+----------------+--------------+-------------+-------------+-------------+-------------+------------+| % | test | | Y | Y | Y | Y | N || % | test_% | | Y | Y | Y | Y | N || % | DvipngDepths | webworkWrite | Y | Y | Y | Y | N || localhost | DvipngDepths | webworkWrite | Y | Y | Y | Y | N || webwork2 | DvipngDepths | webworkWrite | Y | Y | Y | Y | N || localhost | webwork | webworkRead | Y | N | N | N | N || localhost | webwork | webworkWrite | Y | Y | Y | Y | N || % | ProblemLibrary | webworkWrite | Y | Y | Y | Y | N |+-----------+----------------+--------------+-------------+-------------+-------------+-------------+------------+ John <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/5/2005; 2:13:27 PM (reads: 2556, responses: 0) It makes sense to look there...looks like the permissions are OK for the webwork table. <| Post or View Comments |>

 Sam Hathaway - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/7/2005; 11:54:57 AM (reads: 2505, responses: 0) John (Curran),Can you post a copy of your global.conf and database.conf files? How about a mysqldump of your mysql database (with passwords redacted of course)? Thanks. -sam <| Post or View Comments |>

 Sam Hathaway - Re: Problem with database on upgrade from WW2.0 to WW2.1  9/7/2005; 11:55:49 AM (reads: 2511, responses: 0) Oh, also a copy of the course.conf file for some course (how about the admin course) would be helpful. -sam <| Post or View Comments |>

 John Curran - Re: Problem with database on upgrade from WW2.0 to WW2.1