Common Errors

kaibosh edited this page Jan 7, 2017 · 23 revisions

Import issue with mariadb 10.0.21

A recent issue with mariadb import throws this error:

ERROR 1064 (42000) at line 12182: You have an error in your SQL syntax; check th
e manual that corresponds to your MariaDB server version for the right syntax to
use near '*/' at line 1

Until the bug is fixed upstream, one work-around is to delete the line (in this case 12182) from the dump file, import the database, then manually insert the trigger with:

CREATE TRIGGER insert_MD5 BEFORE INSERT ON release_comments FOR EACH ROW SET NEW.text_hash = MD5(NEW.text);

Depending on available RAM, you'll probably need to use a "low-level" editor like vim if the SQL dump file is larger than say 10G. This bug has been seen on Ubuntu 14.04.3 LTS and Fedora 23.

Lock Wait Timeout

When using innodb formated tables, this is caused when 1 script locks a row and another script is waiting for that row. The script that is waiting, times out.

There are several things you can do to mitigate this behavior (in order of preference):

  1. Use Simple Threaded mode for Update Binaries NOT Complete Threaded (in tmux-edit)
  2. Move the CBP tables to Tables Per Group (using convert_to_tpg.php)
  3. Reduce number of update_binaries threads
  4. Allow the first script to release the row as soon as it is finished with it.
  5. Increase you lock wait timeout, so the script can wait a little longer.

The last two steps are done by changing the database configuration in either my.cnf/server.cnf:

transaction-isolation    = READ-COMMITTED
innodb_lock_wait_timeout = 240

These are both InnoDB settings and will reduce the occurrence of lock wait timeouts. The second has the added benefit of releasing the row lock as soon as possible where without it, its not released until the query is finished.

SPHINX engine missing in MariaDB

If you upgraded your MariaDB and "SHOW ENGINES;" doesn't list Sphinx, you have to manually load it again (once):

INSTALL SONAME 'ha_sphinx';

If that fails with Duplicate entry 'SPHINX' for key 'PRIMARY', check if it's still in the plugins table:

MariaDB [nzedb]> select * from mysql.plugin;
+-------------------------------+--------------+
| name                          | dl           |
+-------------------------------+--------------+
| SPHINX                        | ha_sphinx.so |
+-------------------------------+--------------+
1 rows in set (0.00 sec)

If it is listed there, delete it and load the plugin again:

DELETE FROM mysql.plugin WHERE name = 'SPHINX';
INSTALL SONAME 'ha_sphinx';

Now "SHOW ENGINES;" should contain a row like this:

| SPHINX | YES | Sphinx storage engine 2.2.6-release | NO | NO | NO |
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.