mysqldump fails

mysqldump fails

by Andrew Leahy -
Number of replies: 5

I'm trying to upgrade from an older 2.12 WebWork server to a new WebWork 2.17 installation.  My strategy for exporting and importing the existing database information into the new database was to dump the old database and use it to build the new one.  However, I can't get past the mysqldump command (from "backup and disaster recovery" in the Wiki).  I'm getting:

root@webwork webwork2]# mysqldump -u webworkWrite -p --opt webwork | /bin/gzip -c > /tmp/webwork_backup.sql.gz

mysqldump: Got error: 1146: "Table 'webwork.Winter2021-Math145-1_past_answer' doesn't exist" when using LOCK TABLES

mysqlcheck says this table does exist and is OK.  Any ideas?  Any other approaches to accomplishing what I want to do?

In reply to Andrew Leahy

Re: mysqldump fails

by Glenn Rice -

Try adding the -Q flag to the command.  So

mysqldump -u webworkWrite -p --opt -Q webwork | /bin/gzip -c > /tmp/webwork_backup.sql.gz

You have dashes in the course id that is causing the problem.  So the table name needs to be quoted properly.  The -Q flag tells mysqldump to do this.  You can also add this option to your mysqldump.cnf file (usually located in /etc/mysql/conf.d).  In that file you have to use the long version though and without the dash, so add quote-names after [mysqldump].

In reply to Glenn Rice

Re: mysqldump fails

by Andrew Leahy -

Actually, this didn't work--though it helped.  There seems to be an issue at the database level that I am not understanding.  My webwork database has tables for two engines--InnoDB and MyISAM.  Almost every table is in myISAM format, but there are three InnoDB tables that haven't been touched since the day I installed the server in 2016:




The problems seem to be connected to these InnoDB tables.  After some Googling, I stopped the server and deleted the ib_logfile{0,1} files in the mysql folder.  Then I was able to overcome my first problem, but now I'm stuck at the error

mysqldump -u webworkWrite -p webwork

mysqldump: Got error: 1932: "Table 'webwork.OPL_global_statistics' doesn't exist in engine" when using LOCK TABLES

I'm not sure what's happening here.  Going into MariaDB and running "show plugins ; " shows that the MyISAM and InnoDB engines are BOTH installed and active, but apart from seeing them in 'show tables' the DB server will not acknowledge that these tables exist:

MariaDB [webwork]> show create table OPL_global_statistics ;

ERROR 1932 (42S02): Table 'webwork.OPL_global_statistics' doesn't exist in engine

Any ideas?  Since they have apparently never been touched, I'd like to just delete them somehow.  But I don't know how to do it or what it would imply as far as the integrity of the DB, and since I can't make a mysqldump backup of the database, I don't want to be too hasty.


In reply to Andrew Leahy

Re: mysqldump fails

by Danny Glin -
Those tables are used by the Library Browser to maintain statistics on problem usage. They are generated when you run OPL-update, so you shouldn't lose anything if they aren't present on your new install.

The easiest thing to try would be to skip them in the mysqldump using the --ignore-table option, so something like
mysqldump -u webworkWrite -p --opt -Q --ignore-table=webwork.OPL_global_statistics --ignore-table=webwork.OPL_local_statistics --ignore-table=webwork.OPL_problem_user webwork | /bin/gzip -c > /tmp/webwork_backup.sql.gz

If you are then able to copy the DB to your new server you can run OPL-update there and it will recreate those tables.
In reply to Danny Glin

Re: mysqldump fails

by Andrew Leahy -

Thanks.  I'll look into this.  Interestingly,  when I ran OPL-update it generated an error while updating library statistics:

. . . . Wrote Library Textbook Tree to /opt/webwork/webwork2/htdocs/DATA/textbook-tree.json

Updating Library Statistics.

Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle webwork:localhost at /opt/webwork/webwork2/bin/OPL-update line 970.


In reply to Danny Glin

Re: mysqldump fails

by Andrew Leahy -

This worked.  I only need to add a  “--single-transaction” flag.  Thanks!

Now onto the next error . . .