| Bug #20702 | Replication stops because of "unknown database" | ||
|---|---|---|---|
| Submitted: | 26 Jun 2006 18:45 | Modified: | 27 Jun 2006 16:47 |
| Reporter: | Brad Culberson | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S1 (Critical) |
| Version: | 5.0.19 | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[26 Jun 2006 18:51]
Brad Culberson
here it is again:
| | content2.garmin.com | repl | 3306 | 60 | mysql-bin.001691 | 25730855 | content-relay-bin.000413 | 58720023 | mysql-bin.001673 | No | No | | | | mb.activity_full_text_index,mb.activity_full_test_index_temp,jboss.JMS_MESSAGES,jbossditto.JMS_MESSAGES,jboss.JMS_TRANSACTIONS,jbossditto.JMS_TRANSACTIONS,mb.activity_full_text_index,mb.activity_full_test_index_temp,jboss.JMS_MESSAGES,jbossditto.JMS_MESSAGES,jboss.JMS_TRANSACTIONS,jbossditto.JMS_TRANSACTIONS | | | 1049 | Error 'Unknown database '`]''' on query. Default database: 'mbgeo'. Query: 'INSERT into mb.activity_full_text_index_temp
SELECT mb.episode.name as episode_name, mb.episode.description as episode_description, t.geo_feature_name_full_name as geo_feature_name_full_name_nd, mb.user_profile.username as user_profile_username, mb.user_profile.email_address as user_profile_email_address, mb.user_profile.first_name as user_profile_first_name, mb.user_profile.last_name as user_profile_last_name, mb.activity.activity_pk as activity_activity_pk, TRIM(REPLACE(CONCAT_WS(' ',mb.activity_type.db_description,parent_activity_type.db_description),'MotionBased','')) as activity_type_db_description, mb.event_type.db_description as event_type_db_description
FROM mb.mb_activity
INNER JOIN mb.activity ON mb.activity.activity_pk = mb.mb_activity.activity_pk
INNER JOIN mb.episode ON mb.episode.episode_pk = mb.activity.episode_pk
INNER JOIN mb.gps_track ON mb.gps_track.gps_track_pk = mb.mb_activity.gps_track_ | 0 | 58719886 | 1902913466 | None | | 0 | No | | | | | | |
i will try to get you a dump of the data around that position and attach it.
[26 Jun 2006 18:56]
Brad Culberson
here is the data from the part of the log that crashed it the last time.
./mysqlbinlog --start-position=58720023 --stop-position=58720025 content-relay-bin.000413
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 58720023
#060626 3:00:02 server id 2 end_log_pos 58721689 Query thread_id=9266 exec_time=61 error_code=0
use mb;
SET TIMESTAMP=1151308802;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1;
SET @@session.sql_mode=0;
/*!\C latin1 */;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33;
SET @@session.time_zone='SYSTEM';
INSERT into mb.activity_full_text_index_temp
SELECT mb.episode.name as episode_name, mb.episode.description as episode_description, t.geo_feature_name_full_name as geo_feature_name_full_name_nd, mb.user_profile.username as user_profile_username, mb.user_profile.email_address as user_profile_email_address, mb.user_profile.first_name as user_profile_first_name, mb.user_profile.last_name as user_profile_last_name, mb.activity.activity_pk as activity_activity_pk, TRIM(REPLACE(CONCAT_WS(' ',mb.activity_type.db_description,parent_activity_type.db_description),'MotionBased','')) as activity_type_db_description, mb.event_type.db_description as event_type_db_description
FROM mb.mb_activity
INNER JOIN mb.activity ON mb.activity.activity_pk = mb.mb_activity.activity_pk
INNER JOIN mb.episode ON mb.episode.episode_pk = mb.activity.episode_pk
INNER JOIN mb.gps_track ON mb.gps_track.gps_track_pk = mb.mb_activity.gps_track_pk
INNER JOIN mb.user_profile ON mb.user_profile.user_profile_pk = mb.gps_track.owner_pk
INNER JOIN mb.activity_type ON mb.activity_type.activity_type_pk = mb.episode.activity_type_pk
INNER JOIN mb.event_type on mb.event_type.event_type_pk = mb.episode.event_type_pk
INNER JOIN mb.activity_type_has_subtype on mb.activity_type_has_subtype.activity_subtype_pk = mb.activity_type.activity_type_pk
INNER JOIN mb.activity_type parent_activity_type on parent_activity_type.activity_type_pk = mb.activity_type_has_subtype.activity_type_pk
INNER JOIN (Select st.location_pk, mbgeo.fn_GeoFeatureExtendedName(st.location_pk,' ') as geo_feature_name_full_name From (Select distinct location_pk from mb.gps_track) as st) as t on t.location_pk = mb.gps_track.location_pk;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[26 Jun 2006 19:03]
Brad Culberson
These seem to be similar/identical problems: http://bugs.mysql.com/bug.php?id=19344 http://bugs.mysql.com/bug.php?id=11008
[27 Jun 2006 16:47]
Valeriy Kravchuk
Thank you for a problem report. This looks like a perfect duplicate of bug #19344. Please, add your comment to that report. Bug #11008 is different, it seems.
Description: Slaves stop with error when a stored procedure is ran on the live system (daily). We have to set global skip and start it back up every day... The goal of this proc is to create a full text index for our data that is stored in INNODB. The error is: [ERROR] Slave: Error 'Unknown database '~PÏ$^X'' on query. Default database: 'mbgeo'. Query: 'INSERT into mb.activity_full_text_index_temp^M SELECT mb.episode.name as episode_name, mb.episode.description as episode_description, t.geo_feature_name_full_name as geo_feature_name_full_name_nd, mb.user_profile.username as user_profile_username, mb.user_profile.email_address as user_profile_email_address, mb.user_profile.first_name as user_profile_first_name, mb.user_profile.last_name as user_profile_last_name, mb.activity.activity_pk as activity_activity_pk, TRIM(REPLACE(CONCAT_WS(' ',mb.activity_type.db_description,parent_activity_type.db_description),'MotionBased','')) as activity_type_db_description, mb.event_type.db_description as event_type_db_description^M FROM mb.mb_activity^M INNER JOIN mb.activity ON mb.activity.activity_pk = mb.mb_activity.activity_pk^M INNER JOIN mb.episode ON mb.episode.episode_pk = mb.activity.episode_pk^M INNER JOIN mb.gps_track ON mb.gps_track.gps_track_pk = mb.mb_activit The database '~PÏ$^X'' does not exist? | sp_ActivityFullTextIndexReset | | CREATE DEFINER=`root`@`192.168.10.20` PROCEDURE `sp_ActivityFullTextIndexReset`() BEGIN TRUNCATE TABLE mb.activity_full_text_index_temp; INSERT into mb.activity_full_text_index_temp SELECT mb.episode.name as episode_name, mb.episode.description as episode_description, t.geo_feature_name_full_name as geo_feature_name_full_name_nd, mb.user_profile.username as user_profile_username, mb.user_profile.email_address as user_profile_email_address, mb.user_profile.first_name as user_profile_first_name, mb.user_profile.last_name as user_profile_last_name, mb.activity.activity_pk as activity_activity_pk, TRIM(REPLACE(CONCAT_WS(' ',mb.activity_type.db_description,parent_activity_type.db_description),'MotionBased','')) as activity_type_db_description, mb.event_type.db_description as event_type_db_description FROM mb.mb_activity INNER JOIN mb.activity ON mb.activity.activity_pk = mb.mb_activity.activity_pk INNER JOIN mb.episode ON mb.episode.episode_pk = mb.activity.episode_pk INNER JOIN mb.gps_track ON mb.gps_track.gps_track_pk = mb.mb_activity.gps_track_pk INNER JOIN mb.user_profile ON mb.user_profile.user_profile_pk = mb.gps_track.owner_pk INNER JOIN mb.activity_type ON mb.activity_type.activity_type_pk = mb.episode.activity_type_pk INNER JOIN mb.event_type on mb.event_type.event_type_pk = mb.episode.event_type_pk INNER JOIN mb.activity_type_has_subtype on mb.activity_type_has_subtype.activity_subtype_pk = mb.activity_type.activity_type_pk INNER JOIN mb.activity_type parent_activity_type on parent_activity_type.activity_type_pk = mb.activity_type_has_subtype.activity_type_pk INNER JOIN (Select st.location_pk, mbgeo.fn_GeoFeatureExtendedName(st.location_pk,' ') as geo_feature_name_full_name From (Select distinct location_pk from mb.gps_track) as st) as t on t.location_pk = mb.gps_track.location_pk; TRUNCATE TABLE mb.activity_full_text_index; INSERT into mb.activity_full_text_index SELECT * From mb.activity_full_text_index_temp; END | | activity_full_text_index_temp | CREATE TABLE `activity_full_text_index_temp` ( `episode_name` varchar(255) NOT NULL default '', `episode_description` varchar(255) default '', `geo_feature_name_full_name_nd` text NOT NULL, `user_profile_username` varchar(255) NOT NULL default '', `user_profile_email_address` varchar(255) NOT NULL default '', `user_profile_first_name` varchar(255) NOT NULL default '', `user_profile_last_name` varchar(255) NOT NULL default '', `activity_activity_pk` int(11) NOT NULL default '0', `activity_type_db_description` varchar(255) NOT NULL default '', `event_type_db_description` varchar(255) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 character sets match on master and slave How to repeat: run the stored procedure on the master. when it gets to the slave, the slave will die.