** Using WeBWorK **

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 |>

I would try accessing it from the command line under the names webworkRead and webworkWrite, as in:

mysql -uwebworkRead -p

use webwork;
show tables;
describe admin_set;

John

<| Post or View Comments |>

webworkRead/Write can see the admin tables using those commands, but the problem persists.

<| Post or View Comments |>

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 |>

The passwords in database.conf are correct...I believe I have followed the installation instructions verbatim, with one exception. I could not convert the admin course to a sql-single format from the command line (can't remember what the obstacle was), so I ended up just deleting the admin directory in /opt/webwork2/courses. I then added the new admin course per the CourseAdministration manual (i.e. addcourse admin --db-layout=sql_single --users=adminClasslist.lst --professors=admin). I don't think this should have mattered; in any case I have deleted and re-created the admin course from the command line (and reinitialized mysql) a couple of times since then without any explicit error messages.

Does anyone have any pointers on how to go about debugging here?

<| Post or View Comments |>

Davide

<| Post or View Comments |>

<| Post or View Comments |>

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 |>

<| Post or View Comments |>

$externalPrograms{mysql}    = "/usr/bin/mysql";

The default is as above, but our system actually uses /usr/local/bin/mysql.

Typing

which mysql
or 
whereis mysql  

on the command line will help find where the application is installed.

Good luck.

--Mike

<| Post or View Comments |>

Thank you everyone for your suggestions; I will be sure to report my mistake if I can find it!

<| Post or View Comments |>

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 |>

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};

John

<| Post or View Comments |>

<| Post or View Comments |>

Davide

<| Post or View Comments |>

<| Post or View Comments |>

Davide

<| Post or View Comments |>

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 |>

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 |>

There is just one installation of mysql. This is "new" machine and an initial attempt at installation here, so there were no pre-exising uses of the system. I have completely deleted WW and isntalled WW 2.1 from tarball at this point, and I upgraded to mysql 4.0 in the hopes that would change things, but not as yet. All the courses I try are new sql_single courses.

Per John's suggestion, when I enter my ID/password and try to log in to admin course, I do get the same error message with the mysql server running or shutdown! But when the server is running, I can access it from the command line, as webworkRead or root, see the tables, etc. Furthermore, addcourse and delcourse can access the database when the mysql server is up, and if the server is shutdown, they complain (with a "DBI connect failed").

It's clearly some kind of access issue. "13" in the error message below means permission denied, if it's a system message. But mysql.sock has the loosest possible permissions on it...

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 at /opt/webwork2/lib/WeBWorK.pm line 211

It seems strange that command line scripts can access the database, but the web scripts cannot. The word "instantiating" leads me to believe it is something other than permissions on the socket...it's looking for a table that isn't there, or in an incorrect directory?

<| Post or View Comments |>

You can see the authorized users via the command

    mysql> select host,user from mysql.user;

You might try looking at the security documentation for mysql at

http://dev.mysql.com/doc/mysql/en/access-denied.html

(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 |>

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 |>

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

<| Post or View Comments |>

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 |>

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 |>

mysql> select host,user,Select_priv,Insert_priv,Update_priv,Delete_priv,Grant_priv from mysql.user';

+-----------+--------------+-------------+-------------+-------------+-------------+------------+
| 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 |>

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 |>

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          |
+-----------+--------------+-------------+-------------+-------------+-------------+------------+

select host,db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Grant_priv from db;

+-----------+----------------+--------------+-------------+-------------+-------------+-------------+------------+
| 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 |>

<| Post or View Comments |>

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 |>

<| Post or View Comments |>