Bug #12273 "ALTER TABLE" is not replicated by MASTER THREAD in 4.0.20
Submitted: 29 Jul 2005 15:54 Modified: 19 Jan 2006 12:46
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:4.0.20, 4.0.26 OS:M:SuSE 8.x,S:ditto,Debian x,XP
Assigned to: CPU Architecture:Any

[29 Jul 2005 15:54] [ name withheld ]
Description:
From the manual (6.2. Replication Implementation Overview):

"MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in the binary logs." 

Well, "and so on" is a little awkward here. "all changes to your databases" would imply "ALTER TABLE" as well, which is NOT replicated (as of 4.0.20) as was easy for me to find out - hm, hm ...

"all" should mean "all", and if it is not, you should be VERY precise about that. 

As the binary log does record "ALTER TABLE" statements, this problem should be due to the MASTER THREAD. I would consider this behaviour a serious bug and will file a report.

"Please do not report bugs until you have verified that the problem is present in the latest release." 

I sure won't take the hassle and risk and upgrade all my equipment to the latest release. Which one, by the way? The latest in that series, or the latest gamma?

I made sure that "alter table" was in the binary log of the master. It was done at TIMESTAMP=1122639328. 

One of the slaves has log-slave-update; so I inspected the bin log of that slave at TIMESTAMP=1122639328 - no "alter table". 

This is proof that the slave did not get that statement - apart from the fact that none of the slaves altered that table. Which of course broke the replication.

How to repeat:
Should be easy enough. Set up master/slave with that version, turn on log-slave-updates on the slave, issue ALTER TABLE on the master and look at the slave. The bin logs should tell the rest.

Suggested fix:
Find out why the MASTER THREAD doesn't send ALTER TABLE and fix it. 

Or should you consider this a feature? Then fix the manual and tell everybody about that feature so they take care.
[31 Jul 2005 10:30] Victoria Reznichenko
Hello,

Thank you for the report, but you didn't provide enough info in the report.
What is the table structure? What was _exact_ ALTER TABLE statement? What replication setting do you use? Check if ALTER TABLE is in relay log on the slave (if you have this relay log)?

TIMESTAMP value on the slave is the time when statement was executed and it might be different from TIMESTAMP on the master.
[31 Jul 2005 13:47] [ name withheld ]
> What is the table structure? 

CREATE TABLE `googleAdsDeleted` (
  `auto` mediumint(8) unsigned NOT NULL auto_increment,
  `deleted` timestamp(14) NOT NULL,
  `createDate` timestamp(14) NOT NULL default '00000000000000',
  `amount` float(6,2) unsigned NOT NULL default '50.00',
  `url` varchar(255) NOT NULL default '',
  `label` varchar(30) NOT NULL default '',
  `title` varchar(50) NOT NULL default '',
  `slogan` varchar(80) NOT NULL default '',
  `kid` mediumint(8) unsigned NOT NULL default '0',
  `paid` smallint(5) unsigned NOT NULL default '0',
  `type` enum('one','two','three','four','five','six') NOT NULL default 'one',
  `deducted` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`auto`),
  KEY `datum` (`deleted`),
  KEY `kid` (`kid`),
  KEY `url` (`url`),
  KEY `type` (`type`)
) TYPE=MyISAM PACK_KEYS=1

> What was _exact_ ALTER TABLE statement?

From the master log, the "ALTER TABLE"-statement and two surrounding queries, all at the same timestamp:

SET TIMESTAMP=1122639328;
UPDATE anbieter_log_0507  
		SET log234 = log234 + 1
		WHERE idAnbieter = '238' ;
# at 85435028
#050729 14:15:28 server id 2  log_pos 85435028 	Query	thread_id=26809	exec_time=0	error_code=0
SET TIMESTAMP=1122639328;
ALTER TABLE `xxx`.`googleAdsDeleted` CHANGE `type` `type` ENUM('one','two','three','four','five','six') DEFAULT 'one' NOT NULL;
# at 85435229
#050729 14:15:28 server id 2  log_pos 85435229 	Query	thread_id=26783	exec_time=0	error_code=0
use xxx;
SET TIMESTAMP=1122639328;
UPDATE anzeigen
		SET counter = counter +1
		WHERE id = '36536' /* Link_ID Resource id No.13 | Host_Master pz-server1.xxx.com | SERVER_ADDR ***.***.***.25*/;

In the master log, there are 17 queries with that timestamp. 4 of them are not in the slave log, as they apply to a table which is not replicated. So 13 of them should appear in the slave log, one of them being the "ALTER TABLE" statement. There are 12 of them, all as is to be expected, but the one in question does not show up.

> TIMESTAMP value on the slave is the time when statement was executed
> and it might be different from TIMESTAMP on the master.

Replication may happen any time later, that is true, but timestamps are vital data and have to be set exactly on the slave as on the master. Therefore they are recorded in the log and taken from there by the slave to produce the required data. 

If the slave would put its own timestamp, we would have different data. Timestamp data is often applied to qualify data, so this must not happen, as it would render data useless in such cases.
[31 Jul 2005 14:11] [ name withheld ]
Accidentally, I found something that might be the cause of that problem.

I'm sorry because if this turns out to be the case, I am to blame. I was so sure that I didn't even had the idea to check. Anyway, this is unexpected behaviour and a word of warning in the manual should be fine. Besides, the questions remains why this should be so.

I know there is some rule as to which the master should have the oldest version (apart from some special rules regarding specific versions). So it would be safe to upgrade the slave and leave the master alone. Actually, this would be a good upgrading policy and a chance to find out problems without compromising the whole system.

Well: I said all the servers are 4.0.20, but this is not the case. I have no idea why. I was sure to have set up all of them with this version, but only the XP has ist, Debian has 4.0.18 and the SuSE have 4.0.24. As one of those is the master, here we have a master with a newer version than the slaves.

Actually the SuSE slave does not replicate at all which is a problem in itself that I investigate into with a bug report in mind. It runs both threads, is up to date, but does not execute any change at all. I found garbage in the relay log, set up the system again to find the same problem. 

Fine, I can reproduce the problem, first requisite to discover what happens. As soon as I find time I will look more thoroughly. So far, this machine just runs unused in my load balancing scheme.

The reason for the rule that the master should not be newer than the slaves is of course the fact that the protocol might change (and it has) and that the slaves might not understand what the master says. Replication would not be possible at all in this case, whereas the other way around would work if backward compatibility was implemented (as is generally the case with few exceptions).

There does not seem to be an obvious reason why that behaviour should appply to the "ALTER TABLE" statement (or others which I did not have the time to test). If there would be a change in the administrative command set, the slave would just stop with a syntax error - which is no problem but expected behaviour.

After all, we still have the question: Is this a bug or a feature?
[2 Aug 2005 9:41] Aleksey Kishkin
well I would say according to mysql documentation ( http://dev.mysql.com/doc/mysql/en/replication-compatibility.html ) 4.0.20 and 4.0.24 must works properly together. So, the problem still remains, and we still need to create test case for this bug report.
[2 Aug 2005 15:29] Sinisa Milivojevic
Not a bug as described by user.
[2 Aug 2005 15:47] [ name withheld ]
Thanks a lot. This is what I really expect from a company bragging about its superior support. Congratulations! Closing the case without discussion or explanation or substantiation although a colleague has recognized the problem just a couple of hours before. Absolutely great!

By the way: A test case would be a matter of minutes, nothing special to worry about. We waste time by discussing things back and forth instead of doing real work. If I would be a C-programmer and would have the time, I'd find the bug myself. But sorry, I must do other things in my life. It's your problem.
[29 Aug 2005 9:59] Sergei Golubchik
Sorry for that.
Of course you and Aleksey are right. Sinisa is wrong.

Could you please try to provide a repeatable test case ?
[29 Aug 2005 21:48] [ name withheld ]
Thanks a lot. 

It has been some time since I reported the problem. Unfortunately, I still cannot provide a test case. But I had some more experiences in this direction. The whole thing is highly mysterious. Best is I tell what I know. Maybe somebody can relate to this and knows more.

The story is too long for this form, so I add it as a text file.
[29 Aug 2005 21:50] [ name withheld ]
description and examples of replication irregularities

Attachment: MySQL-050829.txt (text/plain), 16.74 KiB.

[1 Sep 2005 14:51] [ name withheld ]
Another incident and a test. See file.
[1 Sep 2005 14:53] [ name withheld ]
incident and test

Attachment: MySQL-050901.txt (text/plain), 12.18 KiB.

[25 Sep 2005 9:36] Valeriy Kravchuk
Thank you for the detailed descriptions of tests in your last 2 files attached. Looks like it will be possible to produce a repeatable test case from them.

But I have 2 more question to you. According to the manual (http://dev.mysql.com/doc/mysql/en/replication-compatibility.html):

"We recommend using the most recent MySQL version available because replication capabilities are continually being improved. We also recommend using the same version for both the master and the slave."

So, I'd like to ask: have you tried to use MySQL 4.0.26 on master and on thel Debian slave? If yes, please, inform about the results. I any case, we'll use the latest version for verification.

And, finally, do you think it is a Debian slave-only related problem? Is it OS-specific? Have you used Debian package or MySQL binary, or compiled from source code (configure options used are needed in the later case)? For the last test, posted on September 1st, I mean.

Sorry if you already answered to some of this questions - there is a lot of information provided and it is hard for me to explore it all at once.
[25 Sep 2005 9:55] [ name withheld ]
Thanks for the follow-up! 

I know I should have made sure that I had 1) the latest versions and 2) the same version on all machines. But as things are - 1) never change a running system (if you can) and 2) do what you can if you got the time. 

I thought (and I was pretty sure about that) I had installed the same versions on all machines, but in fact this isn't the case. On the SuSE machines, the provider may have upgraded in the meantime without telling me, but on the Debian I am not sure at all. This machine came in later, and I fear I have taken it as it was, set up by that provider, without even thinking about it, just eager to see it it work and then leave it at that. Also, I may have upgraded the XP machine for some reason I don't remember, maybe for a training session.

Meanwhile, I have just continued to observe things and found that the problems with "ALTER TABLE" etc. only occur on the Debian, not on the XP machine. I have plenty of records to prove this (just as work afforded it), but unfortunately still can't reproduce consistently, the same as before.

The XP machine tends to stop up to several times a day. The slave thread does not appear in the master processlist then. It looks like it is a DSL-related problem which cuts the connection. But then - why is the slave ignorant about this condition? If she knew, wouldn't she try to reconnect periodically? 

Third: I still didn't have time to find out why the SuSE slave behaves so weird. As this machine looks like it is doing fine but doesn't change anything ever, I don't work with it. That server reads its data from the master, so I can't say anything about that slave, unfortunately. 

Let's see if I can carve out some time to do my homework. I'll inform you then. Error conditions without reproducible setup is a nightmare, I know.
[25 Sep 2005 20:00] [ name withheld ]
Addendum: It happens right now - I can hear the disk working like mad...

I noticed that the XP client may eventually "sleep" for hours and then, all of a sudden, find out about the disconnected state by itself, reconnect and catch up. This happens only when I don't find out myself and don't need an up-to-date slave, of course. Before you ask: Connect_retry: 60
[27 Sep 2005 13:34] [ name withheld ]
Hi! I have 4.0.26-log on both SuSE machines, 4.0.20a-nt-log on XP and 4.0.18 on Debian. As I had problems with debian mostly, I upgrades that System with apt-get etc.

Fine. Now I have 4.0.24_Debian-10-log; upgrading was great. Next I testet the following:

ALTER TABLE `pferdezeitung`.`cc` ADD `test` VARCHAR(255) NOT NULL; 

Result: nothing on the Debian slave. Hm. I dropped that column without checking with the XP slave. Next I tried to find out how to upgrade Debian to 4.0.26. No idea how to do it their way. Googling didn't help. I found instructions, but they seemed to find 4.0.24 only, that being declared stable by them.

Next I tested the XP slave - fine. Debian: fine as well. Hm.

ALTER TABLE `cc` DROP `test`;

ALTER TABLE `pferdezeitung`.`cc` ADD `test` VARCHAR(255) NOT NULL; 

fine

ALTER TABLE `cc` DROP `test`;

fine

ALTER TABLE `pferdezeitung`.`cc` ADD `test2` VARCHAR(255) NOT NULL; 

fine

ALTER TABLE `pferdezeitung`.`cc` CHANGE `test2` `test2` VARCHAR(100) NOT NULL  

Bingo!

MySQL Debian>desc cc;
+-----------+----------------------+------+-----+------------+----------------+
| Field     | Type                 | Null | Key | Default    | Extra          |
+-----------+----------------------+------+-----+------------+----------------+
| idCC      | smallint(5) unsigned |      | PRI | NULL       | auto_increment |
| kid       | smallint(5) unsigned |      | MUL | 0          |                |
| ccCompany | char(1)              |      | MUL |            |                |
| ccNumber  | varchar(20)          |      | MUL |            |                |
| ccDate    | varchar(5)           |      |     |            |                |
| ccName    | varchar(50)          |      | MUL |            |                |
| datum     | date                 |      |     | 0000-00-00 |                |
| test2     | varchar(255)         |      |     |            |                |
+-----------+----------------------+------+-----+------------+----------------+
8 rows in set (0.00 sec)

MySQL XP>desc cc;
+-----------+----------------------+------+-----+------------+----------------+
| Field     | Type                 | Null | Key | Default    | Extra          |
+-----------+----------------------+------+-----+------------+----------------+
| idCC      | smallint(5) unsigned |      | PRI | NULL       | auto_increment |
| kid       | smallint(5) unsigned |      | MUL | 0          |                |
| ccCompany | char(1)              |      | MUL |            |                |
| ccNumber  | varchar(20)          |      | MUL |            |                |
| ccDate    | varchar(5)           |      |     |            |                |
| ccName    | varchar(50)          |      | MUL |            |                |
| datum     | date                 |      |     | 0000-00-00 |                |
| test2     | varchar(100)         |      |     |            |                |
+-----------+----------------------+------+-----+------------+----------------+
8 rows in set (0.00 sec)

Okay: how do I install 4.0.26 on Debian? Like on a SuSE? rpm-wise? Or should I compile?

Another hint would be fine:

The other SuSE runs excellent as a slave, but does not change anything at all. What can I do to find out what happens here?? I know that different character sets yield weird results on 4.1.x, but then the replication will not start at all and I will get hints in the error log. Here it runs fine and no error is given. What could I do to test? Needless to say that all my tests yield nothing on that machine:

MySQL pz-server2>desc cc;
+-----------+----------------------+------+-----+------------+----------------+
| Field     | Type                 | Null | Key | Default    | Extra          |
+-----------+----------------------+------+-----+------------+----------------+
| idCC      | smallint(5) unsigned |      | PRI | NULL       | auto_increment |
| kid       | smallint(5) unsigned |      | MUL | 0          |                |
| ccCompany | char(1)              |      | MUL |            |                |
| ccNumber  | varchar(20)          |      | MUL |            |                |
| ccDate    | varchar(5)           |      |     |            |                |
| ccName    | varchar(50)          |      | MUL |            |                |
| datum     | date                 |      |     | 0000-00-00 |                |
+-----------+----------------------+------+-----+------------+----------------+
7 rows in set (0.00 sec)
[29 Sep 2005 19:07] [ name withheld ]
I found out I have to compile on Debian. Will do ASAP.
[3 Oct 2005 6:35] Valeriy Kravchuk
Thank you for a lot of additional information. I (and anybody else who will want to help) really need some time to analyze it all and provide answers to many questions asked.

Please, inform me about the results of 4.0.26 compilation on Debian. If you have time, run tests on your platform too: make test is one way to do it. There are some (local) replication tests in the test suite and I just want to be sure they work OK.
[11 Oct 2005 9:47] [ name withheld ]
Finally, I upgraded my Debian machine to 4.0.26 and found out something weird about the SuSE-product policy. See attached file for a proof that the bug seems to not be a question of version - so far.

Hm, how did I attach a file recently?
[11 Oct 2005 9:48] [ name withheld ]
Here is the proof on 4.0.26

Attachment: MySQL-bug12273-051011.txt (text/plain), 5.03 KiB.

[11 Oct 2005 10:33] Valeriy Kravchuk
Thank you for the additional proof about 4.0.26 behaviour on Debian. According to these:

"I will upgrade to a standard binary ASAP, first on the SuSE slave,
then on the master.  I will repeat my tests thereafter."

I understood that you'd like to upgrade to 4.0.26 on these machines too. Please, perform standard tests on all the platforms (as I asked you in the previous comments), and inform about any failures.

I did not understand your comments about SuSE... You had used "their" binaries from their packages? Please, use only those officially released by MySQL, from http://dev.mysql.com/downloads/mysql/4.0.html.
[11 Oct 2005 15:10] [ name withheld ]
Hi! Next insight... See file MySQL-bug12273-051011-2.txt

Topics: hangup and analysis, SuSE weird setting and behaviour, brain block and solution: SuSE-slave is running fine. Debian can reproduce problems.
[11 Oct 2005 15:13] [ name withheld ]
Hm, my file doesn't show... Isn't it shown immediately?
[11 Oct 2005 16:48] [ name withheld ]
Sorry, the file doesn't show. It has 41 KB, so it is well below the 200 KB granted. Some problem on your site? It doesn't make sense that I try another time... What shall I do?
[12 Oct 2005 13:01] Valeriy Kravchuk
20+ K HTML file, just a test

Attachment: dispelling-the-myths.html (text/html), 21.76 KiB.

[12 Oct 2005 13:03] Valeriy Kravchuk
I've just posted a 20 K HTML file (article about MySQL) as a test. You should be able to post yours too. So, please, try, and inform about the results.
[12 Oct 2005 16:52] [ name withheld ]
Another try

Attachment: MySQL-bug12273-051011-2.txt (text/plain), 40.24 KiB.

[12 Oct 2005 16:54] [ name withheld ]
Okay, I managed it, the first try was empty again. The only difference was the subject, but I don't see any problem with that...
[14 Oct 2005 12:40] Valeriy Kravchuk
Thank you for the additional information. I've read your long report, but surely have to reread a couple of times more...

What I can tell you for sure: table with a fulltext index can not be migrated to InnoDB as it is (FULLTEXT indexes are for MyISAM only). And we surely do not support any packages provided by SuSE - only our binaries, really. 

Looks like I may need the SHOW CREATE TABLE results for the tables you have a lock contention on. Please, provide also a short summary of the problems you are now fighting with (on 4.0.26, I assume from the results). Just for me to be able to concentrate on those related to the original topic and make some suggestions on the others.
[14 Oct 2005 16:15] [ name withheld ]
I know about the properties of InnoDB tables, and I didn't want to switch all and any table to InnoDB, only those who seemed to have lock contention problems. 

I know also that you are not responsible for SuSE, but it would be good to know that these practises are possible and indeed used which lead to crippled versions not offering what MySQL does promise as default.

I know the pros and cons of InnoDB/MyISAM and when and why you should use either type. Using InnoDB to circumvent lock contention might be feasible in rare occasions, but certainly not here. I thought of it as a workaround only. Rather switch to InnoDB and not know why it is necessary than risk interrupted service on the site.

Okay, see my attachment for further information.
[14 Oct 2005 16:15] [ name withheld ]
Next set of details

Attachment: MySQL-bug12273-051014.txt (text/plain), 13.13 KiB.

[16 Oct 2005 8:20] [ name withheld ]
Something new, highly interesting.

This morning, I had to issue another ALTER TABLE statement. As expected, Debian1 did not execute that. But surprise, Debian2 did! Hm. What does that mean? 

Both have identical MySQL engines, have been upgraded identically to the latest Debian version sarge. The difference is that Debian1 is used in production and has 2 GB RAM, Debian2 is not yet used in production (the load balancing mechanism has to be reworked for that) and has 500 MB RAM.

Any idea for a test setup to find out what's happening here?

BTW, I have not set up Debian1 new as slave yet as the Duplicate key errors disappeared once is caught up. I am still monitoring.
[16 Oct 2005 8:33] [ name withheld ]
Oh my - I changed the wrong column! So, once more, on master:

ALTER TABLE `giftpflanzen` CHANGE `Dateiname` `Dateiname` VARCHAR(35) NOT NULL  

And again, Debian1 behaved bad, Debian2 good:

MySQL Debian>desc giftpflanzen;
+--------------+----------------------+------+-----+----------------+-------+
| Field        | Type                 | Null | Key | Default        | Extra |
+--------------+----------------------+------+-----+----------------+-------+
| nrEd         | smallint(5) unsigned |      | PRI | 0              |       |
| Pflanzenname | varchar(35)          |      | UNI |                |       |
| Dateiname    | varchar(30)          |      |     |                |       |
| content_gp   | text                 |      | MUL |                |       |
| changed_gp   | timestamp(14)        | YES  |     | NULL           |       |
| created_gp   | timestamp(14)        | YES  |     | 00000000000000 |       |
+--------------+----------------------+------+-----+----------------+-------+
6 rows in set (0.00 sec)

Debian2>desc giftpflanzen;
+--------------+----------------------+------+-----+----------------+-------+
| Field        | Type                 | Null | Key | Default        | Extra |
+--------------+----------------------+------+-----+----------------+-------+
| nrEd         | smallint(5) unsigned |      | PRI | 0              |       |
| Pflanzenname | varchar(35)          |      | UNI |                |       |
| Dateiname    | varchar(35)          |      |     |                |       |
| content_gp   | text                 |      | MUL |                |       |
| changed_gp   | timestamp(14)        | YES  |     | NULL           |       |
| created_gp   | timestamp(14)        | YES  |     | 00000000000000 |       |
+--------------+----------------------+------+-----+----------------+-------+
6 rows in set (0.00 sec)
[16 Oct 2005 10:52] Valeriy Kravchuk
Just curious, what are the IP-addresses of your Debian and Debian2? Are they different by 1 in the last part (like 192.168.0.46 and 192.168.0.47)? And what Linux kernel versions are used (2.4.x)? Please, send the results of SHOW PROCESSLIST on the master just when you observing this non-replicated ALTER TABLE statement.
[16 Oct 2005 14:11] [ name withheld ]
I put more information and the answers to your questions into the file.
[16 Oct 2005 14:12] [ name withheld ]
Notes from oct. 14th and 16th

Attachment: MySQL-bug12273-051014-2.txt (text/plain), 15.63 KiB.

[17 Oct 2005 11:59] Valeriy Kravchuk
Thank you for the additional information. At least, I know now that your problem is not related in any way to incorrect DNS reporting...

Now I can comment only on your last issue with the DELAYED inserts and replaces:

"MySQL Debian>alter table robinson type=innodb;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

MySQL Debian>REPLACE DELAYED INTO robinson (email) VALUES('test@test.com');
ERROR 1031: Table handler for 'robinson' doesn't have this option

Hm, looks like I'm wrong. The manual should be updated."

From the manual (http://dev.mysql.com/doc/refman/4.1/en/insert-delayed.html):

"INSERT DELAYED works only with ISAM, MyISAM, (beginning with MySQL 4.1) MEMORY, and ARCHIVE tables."

and, later:

"The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE..."

Then, on the following page, http://dev.mysql.com/doc/refman/4.1/en/replace.html:

"REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted. See Section 13.2.4, “INSERT Syntax”."

So, I see no reasons to make a conclusions like your's and update the manual.
Do you agree with me?

Please, let us concentrate on problems with ALTER TABLE replication in 4.0.x in this bug report. Please, inform about any other problems and suggestions you have in a separate reports.
[18 Nov 2005 0: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".
[18 Nov 2005 15:07] [ name withheld ]
Since last time, I recorded all incidents and found that only some ALTER TABLE statements failed on the first Debian box, but those systematically. As both boxes are set up with identical MySQL and Debian software and work with identical application software, I have no idea who would be responsible for the phenomena.

1) the following worked fine on both Debian boxes

ALTER TABLE `table1` DROP INDEX `ausb_besch`, ADD FULLTEXT `ausb_besch` (`ausbildung`,`beschreibung`,`name`,`vater`,`mutter`,`mvater`,`farbe`)  

ALTER TABLE `table1` ADD FULLTEXT `ft_ausbildung` (`ausbildung`)  

ALTER TABLE `table2` DROP PRIMARY KEY, ADD PRIMARY KEY (`idBuch`,`idAutor`)  

ALTER TABLE `table3` CHANGE `idBuch` `idBuch` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, CHANGE `nrEd` `nrEd` SMALLINT UNSIGNED DEFAULT '0' NOT NULL ,CHANGE `idVerlag` `idVerlag` SMALLINT UNSIGNED DEFAULT '0' NOT NULL  ,DROP PRIMARY KEY, ADD PRIMARY KEY (`idBuch`)  ,
DROP INDEX `isbn`, ADD INDEX `isbn` (`isbn`,`idBuch`,`idAutor`)  

2) the following worked fine only on box 2 and failed on box 1 as described:

ALTER TABLE `table1` CHANGE `alt` `alt` TINYINT(4) UNSIGNED DEFAULT '0' NOT NULL  

ALTER TABLE `trackPQueries` ADD `kid` MEDIUMINT UNSIGNED NOT NULL, ADD `parkID` CHAR(32) NOT NULL;

ALTER TABLE `trackPQueries` ADD INDEX (`kid`, `parkID`); 

ALTER TABLE `table1_counter` ADD `counterBildwerbung` MEDIUMINT UNSIGNED NOT NULL;  

So it looks like the problem can be pinned down to ADD and CHANGE columns alone, as we had a CHANGE combined with key manipulation which worked fine.

If you have no idea what to do now and nobody else complains, I am fine with closing this bug down. I know how to cope with it. Thanks a lot.
[26 Nov 2005 9:41] Valeriy Kravchuk
Can you, please, try to upload the binary logs from master and both "good working" and "problematic" slaves for the ALTER TABLE statements that produce different results?

I do not want to close this bug report, because even if you found a workaround for your problem, we yet neither proved that it is your environment problem only, not that it is a repeatable bug.
[28 Nov 2005 18:43] [ name withheld ]
Ok, I understand. I will try to produce a minimal test sample. Normally, the bin logs are up to 1 GB, so that makes no sense.

Right now I reproduced the problem, wanted to set up a test case and then the Debian worked fine with the same statement when issued the second time! Interesting.  I know I had this before when I tried to reproduce the phenomenon, but never in normal operations. Ok, will find out.
[29 Nov 2005 16:14] Valeriy Kravchuk
Thank you for your commitment to the analysis of this problem. Please, inform about any results you got trying to isolate the smallest possible test case in binlog.
[30 Dec 2005 0: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".
[19 Jan 2006 12:35] [ name withheld ]
Still one of the Debian machines do not behave well. New evidence. Keep case closed.

1. I have to correct myself. I misunderstood my provider. He did not state, as I put it, that SuSE provides binaries without InnoDB, it is him who does. He offers the two types to choose, albeit all else comes from SuSE.

2. Having switched to MySQL with InnoDB on the master now, I changed a number of tables to InnoDB as a batch, as they most probably caused lock contention and no other means worked. Some of them were changed on the problematic Debian, some not. So that is another evidence of the Debian1 picture. 

Besides that, I think we should keep this case closed.
[19 Jan 2006 12:46] Miguel Solorzano
Thank you for the feedback and bug report.