Bug #19344 Replication stops because of "unknown database"
Submitted: 25 Apr 2006 17:23 Modified: 23 Aug 2006 18:22
Reporter: Janko Richter Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.20,5.0.21,5.0.22 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[25 Apr 2006 17:23] Janko Richter
Description:
I'm using a mysql server with 3 replications. Sometimes, one of the replication stops. SHOW SLAVE STATUS reports:

Error 'Unknown database '{P¯|Ã¥ ×0 _8'' on query. Default database: 'instruments'. Query: 'UPDATE instruments.inst_market SET deleted=now() WHERE provider_symbol='BLBT-LN,USD' AND provider_id = 1'

Of cource, the database doesn't exist :) I'm not using special chars with database names (ANSI only).
The master executed the command as well the other replications. I have the problem with
the other replications too: one replication stops, the other are working well. It seems not to depend on one replication server.

The table instruments.inst_market is a INNODB table using triggers and foreign keys.

How to repeat:
Sorry, currently I cannot reproduce the problem. Hints are welcome.
[26 Apr 2006 7:36] Peter Laursen
{P¯|Ã¥ ×0 _8'

.. looks very much like a 8-bit (or ANSI if you like) reprensentation of a multibyte encoded string.  Like 'SET NAMES' or 'SET character_set_connection' enforces another character set than what should be used for the data.

Are some of the ANSI characters you use non-ASCII like accented characters (ä. é, ñ etc)?  Which character sets do you use ??

Is it exactly this string every time or does it vary?
[26 Apr 2006 8:18] Janko Richter
The string does vary.
I guess it is not a multibyte problem because the problem does not occur on all
replications for one statement, but for an other statement an other replication is affected. IMHO, the displayed string looks more like a uninitilized pointer, but I'm not sure.

SHOW VARIABLES get the encoding:

character_set_client: latin1
character_set_connection: latin1
character_set_database: utf8
character_set_filesystem: binary
character_set_results: latin1
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: latin1_swedish_ci
collation_database: utf8_general_ci
collation_server: latin1_swedish_ci

It equals for master and slaves.

How can I help to debug the problem? Where can I add debug code? Is it usefull to attach gdb, if the replication stops?
[26 Apr 2006 8:44] Valeriy Kravchuk
Please, send the SHOW CREATE TABLE instruments.inst_market results and text of all the triggers defined for that table.

SHOW DATABASES results from master and problematic slave may be also useful, just to check.
[26 Apr 2006 9:50] Janko Richter
I have attached the whole database schema, because there are a lot of dependencies.
[26 Apr 2006 9:59] Janko Richter
And here are the databases (identical for master and slave)

+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| dbload                |
| depots                |
| instrument_management |
| instruments           |
| mysql                 |
| stocks                |
| ws5_ger               |
+-----------------------+
[12 May 2006 10:05] Valeriy Kravchuk
Can you, please, also upload the binlog/relay log corresponding to that UPDATE statement that caused problem?
[12 Jun 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[27 Jun 2006 16:44] Valeriy Kravchuk
Bug #20702 marked as a duplicate of this one.
[30 Jun 2006 18:35] Chris DiMartino
We've been experiencing the same issue.  We've found that in our case it happens when a trigger on a table in one database utilizes a stored function from a different database.  We've been able to get around it in some cases by explicitly prefixing each and every table/function/procedure with the database name that it is associated with, but that has not proved successful in every instance.  We found it necessary to remove a trigger completely yesterday in order to make our replication function again, even though each table and function in the trigger had explicit database names, as well as each table in the function definitions.
[2 Jul 2006 10:50] Thilo Raufeisen
We´ve the same issue on our slave too.
Running 5.0.22 on CentOS 4.3 with the RHEL 4 - Binaries.
But on our slave, "only" a function is involved and not a trigger.
Replication seems to work as long as no function or procedure is involved.
We´ve also tried a self compiled version of mysqld, but the error still occurs.

I also got this error when submitting such a query manually on a testserver.
This query contained a function-call too, but submitting the same query again worked.
[20 Jul 2006 18:22] Valeriy Kravchuk
All reporters: exact steps/setup to reproduce (on 5.0.22) is still needed.

I think, replication problems involving finctions from other database are different ones.
[7 Aug 2006 5:15] Simon Garner
We are also having this problem. I cannot come up with any conditions to reproduce it however; it seems to occur randomly.

The error in SHOW SLAVE STATUS looks like this...

                 Last_Errno: 1049
                 Last_Error: Error 'Unknown database '<'' on query. Default database: 'blah'. Query: 'UPDATE
                                        blah.table
                                SET
                                        ...
                                WHERE
                                        x = 13797'

blah.table has a trigger AFTER UPDATE which conditionally executes up to two stored procedures which issue queries on other tables. The procedures exist in the blah database.

As far as I can tell the queries always execute perfectly on the master, but all the slaves (4 in our case) will get the same error at the same time, and stop replication. Sometimes typing START SLAVE is sufficient to make it work, other times I have to restart mysqld on each of the slaves, then issue START SLAVE and it works.
[7 Aug 2006 7:29] Thilo Raufeisen
I´ve updated the slave to 5.0.24 and it looks good so far.
A query that ends in this error works fine now.
Can anyone else confirm that replication works with 5.0.24?
[20 Aug 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[23 Aug 2006 17:53] Shane Bester
I repeated this under 5.0.21 after many attempts.  Am in process of trying 5.0.24 now.
[23 Aug 2006 18:03] Shane Bester
repeated on 5.0.22 too.
[ERROR] Slave: Error 'Unknown database '∞≡7♥≈∞a'' on query. Default database: 'c5f1'...
[23 Aug 2006 18:16] Shane Bester
I cannot reproduce on 5.0.24.  Could this be a duplicate of bug #19022 really?
[23 Aug 2006 18:21] Shane Bester
proper standalone testcase php script for reproducing on <5.0.23

Attachment: 19344.php (application/octet-stream, text), 2.87 KiB.

[23 Aug 2006 18:30] Thilo Raufeisen
This bug does no longer occur with 5.0.24.
However, replication is still broken in 5.0.x: see bug #20141