I do have 4.0.26 on both SuSE machines, so I was puzzled that they didn't want to change to InnoDB engine for one table. I wanted to do that because I had very ugly lock contention on that table for no apparent reason. This is another problem to investigate into. Now that I had this problem, I wanted to circumvent this as a workaround by changing engine from MYISAM to INNODB and found out that this was not possible. Next I found that skip-innodb was not the cause for this behaviour, instead the engine was not compiled into the binary. So I suspected the provider would compile himself. I know since we did business (6 years now) that he always uses self-compiled versions which I thought were done by him. Also, I remembered to have read that there are compiler switches with which to omit InnoDB and replication. (I tried to find this, but it seems not to be in the manual.) So this would then explain why replication did not work. Hence I wrote the engineer and told him about my observations and hypotheses. He wrote back that he didn't compile himself but rather uses the patches and new versions provided by SuSE. He didn't even seem to know about this possibility to slim the server via compiler switches. If so, and I think there is little reason to doubt about it, this is a very bad policy by SuSE in my eyes which should be mentioned properly in the manual. Now I took the SuSE slave out of business, installed the regular 4.0.26 binary provided by MySQL AB , set it up as a slave and it started with setting up a table space. Good and expected. Then I tested replication and found, surprisingly, no. Bad and unexpected. As the Debian works with the SuSE master, the SuSE slave should as well. I set up the second Debian without problems, too. Hm, what to do now? In the meantime, I had a very bad case of lock contention again and investigated into this. mysqld has been shot by a watchdog. Current status later on. I have a processlist via mysql but this is cut off at the beginning, so not complete (no, I didn't tee, but I changed that). I have one complete list via phpMyAdmin. That one is interesting. Nothing there that should cause problems. Unfortunately we don't see which of the queries is causing the problem: all are equally rated as locked. See the info column of this state (or skip to analysis): Info    NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL      NULL   SELECT count(*) FROM bild         WHERE 1         AND pid = '34111'         AND typBild = 'p'         AND format IN ('.m SELECT * FROM bild             WHERE pid = '33339'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT * FROM bild             WHERE pid = '33670'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT * FROM bild             WHERE pid = '35321'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') (SELECT idEdSc, keySc  FROM secPage       WHERE 1 AND (( MATCH (itemSc) AGAINST ('offenstall') ))  ORD SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT * FROM bild             WHERE pid = '34716'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT  SQL_CALC_FOUND_ROWS   DISTINCT          DATE_FORMAT(a.tmstmp, '%d.%m.%y
%H:%i:%s') Datum,         C SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m    NULL      NULL   SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT * FROM bild             WHERE pid = '34245'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT * FROM bild                 WHERE pid = '37743'                 AND paid != 'n'                 AND typBild = 'a'                 ORDER B    NULL   SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT * FROM bild             WHERE pid = '34339'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') (SELECT idEdSc, keySc  FROM secPage       WHERE 1 AND (( MATCH (itemSc) AGAINST ('paso') ))  ORDER BY  SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT * FROM bild             WHERE pid = '34601'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT  SQL_CALC_FOUND_ROWS   anzeigen.id FROM anzeigen                      WHERE av='g' AND rubrik = 'Reitbeteil SELECT * FROM bild             WHERE pid = '33935'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT * FROM bild             WHERE pid = '34502'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') INSERT INTO bild             (id, comment, pid , typBild, width , height, format, paid , datum, kid)             VAL SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT count(*) FROM bild         WHERE 1         AND pid = '35320'         AND typBild = 'p'         AND format IN ('.m SELECT * FROM bild             WHERE pid = '34501'              AND typBild = 'p'             AND format IN ('.jpg', '.gif')    NULL   SELECT * FROM bild             WHERE pid = '33167'              AND typBild = 'p'             AND format IN ('.jpg', '.gif') SELECT count(*) as num FROM bild         WHERE pid = '31318'         AND format IN ('.jpg', '.gif')         AND typ    NULL   UPDATE xxx_counter         SET counterTabelle = counterTabelle +1         WHERE id = '31496' /* Link_ID Res    NULL      NULL      NULL      NULL   SHOW PROCESSLIST    NULL   The first lines are the replication clients and idle threads. We have only 7 types of queries, 5 selects, 3 of them fine: Copying to tmp table SELECT  SQL_CALC_FOUND_ROWS   DISTINCT  Sorting result (SELECT idEdSc, keySc  FROM secPage Sending data SELECT  SQL_CALC_FOUND_ROWS   anzeigen.id FROM anzeigen  Updating UPDATE xxx_counter Locked SELECT count(*) FROM bild Locked SELECT * FROM bild Locked INSERT INTO bild So what happens here? Eveything is locked on bild. MySQL master>check table bild; +--------------------+-------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------+-------+----------+-----------------------------------------------------------+ | 8145145784125.bild | check | warning | 24 clients are using or haven't closed the table properly | | 8145145784125.bild | check | error | Record at pos: 849296 is not remove-marked | | 8145145784125.bild | check | error | record delete-link-chain corrupted | | 8145145784125.bild | check | error | Corrupt | +--------------------+-------+----------+-----------------------------------------------------------+ 4 rows in set (0.00 sec) MySQL master>repair table bild; +--------------------+--------+----------+--------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------+--------+----------+--------------------------------------------+ | 8145145784125.bild | repair | warning | Number of rows changed from 53493 to 53494 | | 8145145784125.bild | repair | status | OK | +--------------------+--------+----------+--------------------------------------------+ 2 rows in set (2.26 sec) MySQL master>check table bild; +--------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+-------+----------+----------+ That doesn't give much insight. One line has been marked as deleted and wasn't? Well, maybe this was the cause... we'll see. By the way, query cache is on and should help a lot with these kind of queries. Back to replication. Today, by chance, we have only about half the traffic as usual (maybe due to vacation & excellent weather). Sometime during the day or at night I will replace the version on the master as well. Then we will have the same version on all machines (except XP, which will be a snap to change and should not be related to the other slaves). I send a processlist as well; you see that the server is idle and has 4 slaves running. All of a sudden lock contention may occur. As far as I can see now, it is due to two myisam tables, most probably caused by an update or insert which does not execute for some reason so the lock is not released. The updates are not critical says explain, ref is const. Now these are dynamic tables and concurrent inserts are not guaranteed, but nevertheless this should not be a problem unless we have extreme long selects to wait for - which could be caused by the search engine. There were 2 such a queries using a fulltext index, but explain said 1 row would have to be examined using the fulltext index... I have changed my code so that deletes on these tables are postponed to later and will be accompanied by an optimize to ensure concurrent inserts. I have checked and optimized / repaired the tables in question and will continue to check what happens. InnoDB should not be needed here... Now my report: Installed mysql-standard-4.0.26-pc-linux-gnu-i686.tar.gz on SuSE slave Restored last backup Started replication: SuSE 22>CHANGE MASTER TO -> MASTER_HOST='pz-server1.xxx.com', -> MASTER_USER='repl', -> MASTER_PASSWORD='xxx', -> MASTER_LOG_FILE='pz-server1-bin.679', -> MASTER_LOG_POS=4; Query OK, 0 rows affected (0.35 sec) SuSE 22>show slave status\G *************************** 1. row *************************** Master_Host: pz-server1.xxx.com Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: pz-server1-bin.679 Read_Master_Log_Pos: 4 Relay_Log_File: pz-server2-relay-bin.001 Relay_Log_Pos: 4 Relay_Master_Log_File: pz-server1-bin.679 Slave_IO_Running: No Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 4 Relay_log_space: 4 1 row in set (0.00 sec) SuSE 22>START SLAVE; Query OK, 0 rows affected (0.02 sec) SuSE 22>show slave status\G *************************** 1. row *************************** Master_Host: pz-server1.xxx.com Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: pz-server1-bin.679 Read_Master_Log_Pos: 8468445 Relay_Log_File: pz-server2-relay-bin.001 Relay_Log_Pos: 1155243 Relay_Master_Log_File: pz-server1-bin.679 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 1155198 Relay_log_space: 8468490 1 row in set (0.03 sec) SuSE 22>select count(*) from cc; +----------+ | count(*) | +----------+ | 207 | +----------+ 1 row in set (0.07 sec) Now on master via browser/phpMyAdmin (klick, klick): INSERT INTO `cc` (`idCC`, `kid`, `ccCompany`, `ccNumber`, `ccDate`, `ccName`, `datum`, `test`) VALUES ('', '0', '', '', '', 'test', '0000-00-00', NOW(NULL)); SuSE 22>select count(*) from cc; +----------+ | count(*) | +----------+ | 207 | +----------+ 1 row in set (0.00 sec) SuSE 22>show variables like 'hav%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_bdb | NO | | have_crypt | YES | | have_innodb | YES | | have_isam | YES | | have_openssl | NO | | have_query_cache | YES | | have_raid | YES | | have_symlink | YES | +------------------+-------+ 8 rows in set (0.00 sec) SuSE 22>select version(); +---------------------+ | version() | +---------------------+ | 4.0.26-standard-log | +---------------------+ 1 row in set (0.00 sec) now on master: MySQL master>select version(); +------------+ | version() | +------------+ | 4.0.26-log | +------------+ 1 row in set (0.00 sec) MySQL master>show variables like 'hav%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | have_bdb | NO | | have_crypt | YES | | have_innodb | NO | | have_isam | YES | | have_openssl | NO | | have_query_cache | YES | | have_raid | NO | | have_symlink | YES | +------------------+-------+ 8 rows in set (0.00 sec) MySQL master>select count(*) from cc; +----------+ | count(*) | +----------+ | 208 | +----------+ 1 row in set (0.00 sec) ySQL master>status; -------------- mysql Ver 12.22 Distrib 4.0.26, for pc-linux-gnu (i686) Connection id: 1762 Current database: xxx Current user: root@localhost SSL: Not in use Current pager: less Using outfile: '/root/mysql.log' Server version: 4.0.26-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 39 min 25 sec Threads: 15 Questions: 355308 Slow queries: 65 Opens: 217 Flush tables: 1 Open tables: 205 Queries per second avg: 150.236 -------------- MySQL master>show processlist\G *************************** 1. row *************************** Id: 45 User: repl Host: p54876D96.dip.t-dialin.net:4927 db: NULL Command: Binlog Dump Time: 2822 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** Id: 51 User: repl Host: xxx.serverpool.info:45509 db: NULL Command: Binlog Dump Time: 2820 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 52 User: repl Host: xxx:32952 db: NULL Command: Binlog Dump Time: 2820 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 4. row *************************** Id: 214 User: repl Host: pz-server2.xxx.com:24329 db: NULL Command: Binlog Dump Time: 2625 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 5. row *************************** Id: 787 User: root Host: localhost db: xxx Command: Sleep Time: 2228 State: Info: NULL *************************** 6. row *************************** Id: 1762 User: root Host: localhost db: xxx Command: Query Time: 0 State: NULL Info: show processlist *************************** 7. row *************************** Id: 2347 User: xxx Host: xxx.serverpool.info:47014 db: xxx Command: Sleep Time: 1278 State: Info: NULL *************************** 8. row *************************** Id: 2968 User: xxx Host: xxx.serverpool.info:47456 db: xxx Command: Sleep Time: 949 State: Info: NULL *************************** 9. row *************************** Id: 4490 User: xxx Host: pz-server2.xxx.com:29544 db: xxx Command: Sleep Time: 115 State: Info: NULL *************************** 10. row *************************** Id: 4509 User: xxx Host: xxx.serverpool.info:48628 db: xxx Command: Sleep Time: 1 State: Info: NULL *************************** 11. row *************************** Id: 4547 User: xxx Host: localhost db: xxx Command: Sleep Time: 6 State: Info: NULL *************************** 12. row *************************** Id: 4641 User: xxx Host: pz-server2.xxx.com:29720 db: xxx Command: Sleep Time: 0 State: Info: NULL *************************** 13. row *************************** Id: 4658 User: xxx Host: pz-server2.xxx.com:29740 db: xxx Command: Sleep Time: 0 State: Info: NULL *************************** 14. row *************************** Id: 4662 User: xxx Host: pz-server2.xxx.com:29743 db: xxx Command: Sleep Time: 1 State: Info: NULL *************************** 15. row *************************** Id: 4663 User: xxx Host: pz-server2.xxx.com:29746 db: xxx Command: Sleep Time: 2 State: Info: NULL *************************** 16. row *************************** Id: 4674 User: xxx Host: pz-server2.xxx.com:29761 db: xxx Command: Sleep Time: 0 State: Info: NULL *************************** 17. row *************************** Id: 4677 User: xxx Host: pz-server2.xxx.com:29770 db: xxx Command: Sleep Time: 1 State: Info: NULL *************************** 18. row *************************** Id: 4679 User: xxx Host: pz-server2.xxx.com:29773 db: xxx Command: Sleep Time: 0 State: Info: NULL *************************** 19. row *************************** Id: 4681 User: xxx Host: xxx.serverpool.info:48754 db: xxx Command: Sleep Time: 1 State: Info: NULL *************************** 20. row *************************** Id: 4683 User: xxx Host: xxx.serverpool.info:48757 db: xxx Command: Sleep Time: 0 State: Info: NULL *************************** 21. row *************************** Id: 4685 User: xxx Host: xxx.serverpool.info:48760 db: xxx Command: Sleep Time: 0 State: Info: NULL 21 rows in set (0.00 sec) Addendum: The SuSE2-slave experienced 2 error while catching up; the first occured right at the start, Exec_master_log_pos: 79: SuSE 22>show slave status\G *************************** 1. row *************************** Master_Host: pz-server1.xxx.com Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: pz-server1-bin.682 Read_Master_Log_Pos: 37384343 Relay_Log_File: pz-server2-relay-bin.001 Relay_Log_Pos: 95688650 Relay_Master_Log_File: pz-server1-bin.680 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Query caused different errors on master and slave. Error on master: 'Got error %d from table handler' (1030), Error on slave: 'no error' (0). Default database: 'xxx'. Query: 'DELETE FROM sessData WHERE datum < DATE_SUB(NOW(), INTERVAL 30 MINUTE) LIMIT 100 /* Link_ID Resource id No.18 | Host_Master pz-server1.xxx.com | SERVER_ADDR */' Skip_counter: 0 Exec_master_log_pos: 79 Relay_log_space: 133384978 1 row in set (0.00 sec) SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; SHOW SLAVE STATUS\G Now this is something that really puzzles me: 'Got error %d from table handler' This is one of the tables I have problems with. I changed my code as indiceted with respect to this table, so problems should be gone now. MySQL master>check table sessData; +------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------+-------+----------+----------+ | xxx.sessData | check | status | OK | +------------------------+-------+----------+----------+ 1 row in set (0.07 sec) Well, to be sure it has no holes: MySQL master>optimize table sessData; +------------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------+----------+----------+----------+ | xxx.sessData | optimize | status | OK | +------------------------+----------+----------+----------+ And now we see why ist "did not replicate" - it just had not caught up yet! SuSE 22>select count(*) from cc; +----------+ | count(*) | +----------+ | 208 | +----------+ 1 row in set (0.00 sec) By the way, catching up was really fast, about 1 MB per second. Much faster than Debian, who is 200 km or so apart. SuSE 22>SHOW SLAVE STATUS\G *************************** 1. row *************************** Master_Host: pz-server1.xxx.com Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: pz-server1-bin.682 Read_Master_Log_Pos: 37473924 Relay_Log_File: pz-server2-relay-bin.001 Relay_Log_Pos: 102902596 Relay_Master_Log_File: pz-server1-bin.682 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 1062 Last_error: Error 'Duplicate entry '765' for key 1' on query. Default database: 'xxx'. Query: 'INSERT INTO log_raw_google_0510 (ip, ruri, referer, queryString, query , bannerID) VALUES ('195.234.224.13', '', 'http://www2.xxx.com/Alter/16', '33953', 'sid=c34c10a67034d9784839488beb604adb1129027860&j=1', '13046' ) /* Link_ID Resource id No.14 | Host_Master pz-server1.xxx.com | SERVER_ADDR xxx.83.154*/' Skip_counter: 0 Exec_master_log_pos: 6901961 Relay_log_space: 133474559 1 row in set (0.00 sec) This really IS A PROBLEM!!! How can we get a Duplicate entry error on a table which logs only? key 1 is an autoincrement column... MySQL master>desc log_raw_google_0510; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | smallint(5) unsigned | | MUL | 0 | | +-------------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MySQL master>show index from log_raw_google_0510\G *************************** 1. row *************************** Table: log_raw_google_0510 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: autoLog Collation: A Cardinality: 774 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: log_raw_google_0510 Non_unique: 1 Key_name: bannerID Seq_in_index: 1 Column_name: bannerID Collation: A Cardinality: 64 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 3. row *************************** Table: log_raw_google_0510 Non_unique: 1 Key_name: queryString Seq_in_index: 1 Column_name: queryString Collation: A Cardinality: 154 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 3 rows in set (0.00 sec) MySQL master>check table log_raw_google_0510; +-----------------------------------+-------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------------+-------+----------+-----------------------------------------------------------+ | xxx.log_raw_google_0510 | check | warning | 19 clients are using or haven't closed the table properly | | xxx.log_raw_google_0510 | check | status | OK | +-----------------------------------+-------+----------+-----------------------------------------------------------+ 2 rows in set (0.07 sec) Now I realize that the column definition is not on bannerID is not adequate, so I issue (klick in phpMyAdmin) ALTER TABLE `log_raw_google_0510` CHANGE `bannerID` `bannerID` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL What says Debian? MySQL Debian>desc log_raw_google_0510; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | smallint(5) unsigned | | MUL | 0 | | +-------------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) Ahem... And Debian2? Debian2>desc log_raw_google_0510; +-------------+----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | smallint(5) unsigned | | MUL | 0 | | +-------------+----------------------+------+-----+---------+----------------+ 8 rows in set (0.03 sec) Ok, we know and we can reproduce. Debian2>ALTER TABLE `xxx`.`log_raw_google_0510` CHANGE `bannerID` `bannerID` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL; Query OK, 645 rows affected (0.08 sec) Records: 645 Duplicates: 0 Warnings: 0 Debian2>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MySQL Debian>ALTER TABLE `log_raw_google_0510` CHANGE `bannerID` `bannerID` MEDIUMINT UNSIGNED DEFAULT '0' NOT NULL; Query OK, 765 rows affected (0.07 sec) Records: 765 Duplicates: 0 Warnings: 0 MySQL Debian>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) Wait: what says the master? MySQL master>select count(*) from log_raw_google_0510; +----------+ | count(*) | +----------+ | 775 | +----------+ 1 row in set (0.00 sec) So none of the slaves has the correct number of rows. Now SuSE 2: SuSE 22>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.01 sec) SuSE 22>select count(*) from log_raw_google_0510; +----------+ | count(*) | +----------+ | 775 | +----------+ 1 row in set (0.00 sec) That's the way it should be. And XP? It has stopped at the "Query caused different errors on master and slave." event: Error on master: 'Got error %d from table handler' (1030), Error on slave: 'no error' (0). And again at the other error as well. MySQL XP>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) Ok, fine. MySQL XP>select count(*) from log_raw_google_0510; +----------+ | count(*) | +----------+ | 760 | +----------+ 1 row in set (0.02 sec) Hm... Not correct either. How do I explain and correct that? BTW, I had to set up the XP anew a couple of days ago. I had some problems on that machine and finally it claimed reading errors on the relay log... MySQL master>select autoLog from log_raw_google_0510 procedure analyse()\G *************************** 1. row *************************** Field_name: log_raw_google_0510.autoLog Min_value: 1 Max_value: 779 Min_length: 1 Max_length: 3 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 390.0000 Std: 224.8777 Optimal_fieldtype: SMALLINT(3) UNSIGNED NOT NULL 1 row in set (0.02 sec) I saw this, too. Ok then. ALTER TABLE `xxx`.`log_raw_google_0510` CHANGE `autoLog` `autoLog` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT SuSE 22>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | mediumint(8) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MySQL Debian>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) Debian2>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | bigint(20) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MySQL XP>desc log_raw_google_0510; +-------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-----------------------+------+-----+---------+----------------+ | autoLog | mediumint(8) unsigned | | PRI | NULL | auto_increment | | ip | varchar(15) | | | | | | ts | timestamp(14) | YES | | NULL | | | ruri | varchar(255) | | | | | | referer | varchar(255) | | | | | | queryString | varchar(255) | | MUL | | | | query | varchar(255) | | | | | | bannerID | mediumint(8) unsigned | | MUL | 0 | | +-------------+-----------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) MySQL Debian>ALTER TABLE `xxx`.`log_raw_google_0510` CHANGE `autoLog` `autoLog` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT; Query OK, 765 rows affected (0.02 sec) Records: 765 Duplicates: 0 Warnings: 0 Debian2>ALTER TABLE `xxx`.`log_raw_google_0510` CHANGE `autoLog` `autoLog` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT; Query OK, 645 rows affected (0.02 sec) Records: 645 Duplicates: 0 Warnings: 0 Again: How can we get a Duplicate entry error on a table which logs only? key 1 is an autoincrement column... I had this sunday on the master. Well, the master was shot dead in consequence of these contention problems and that might have corrupted his autoincrement counter - which is a kind of error that should be repaired automatically by the server, if you ask me. But how could that kind of error happen on the slaves which have not been killed? In case you wonder, what Debian1 did with those errors - I wondered, too. Actually, I run a cron job every minute on the master to check the slave status of Debian1, and issue the appropriate commands when necessary. But as I send myself a mail in case so that I know and I didn't get any, I thought everything is fine. It was not: MySQL Debian>show slave status\G *************************** 1. row *************************** Master_Host: pz-server1.pferdezeitung.com Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: pz-server1-bin.682 Read_Master_Log_Pos: 61978617 Relay_Log_File: server8324611625-relay-bin.162 Relay_Log_Pos: 54917959 Relay_Master_Log_File: pz-server1-bin.682 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: pferdezeitung Replicate_ignore_db: Last_errno: 1054 Last_error: Error 'Unknown column 'test' in 'field list'' on query. Default database: 'pferdezeitung'. Query: 'INSERT INTO `cc` (`idCC`, `kid`, `ccCompany`, `ccNumber`, `ccDate`, `ccName`, `datum`, `test`) VALUES ('', '0', '', '', '', 'test', '0000-00-00', NOW(NULL))' Skip_counter: 0 Exec_master_log_pos: 1586601 Relay_log_space: 115309975 1 row in set (0.00 sec) So why didn't we have that column there? Don't know. Looks like some leftover from testing earlier. And then we got that Duplicate entry '765' for key 1' on query on Debian1 as well. Hm. 765?? Didn't we expect 775 rows in total? And what about the other error? Didn't show up. Or am I totally confused now? Later: I know why slave checking didn't work; found a silly typo in my script that checks the slave status :-(