[thegreatall]

Ok, I tested again fully to make sure it was not fixed and no it is not. here is how to replicate step by step:

• Setup the server as a master, with 'binlog-ignore-db=mysql' (which is how many binlogs are setup)
• Restart MySql
• SSH into the MySql server and login using root. (ie: 'mysql -u root -p')
• Run “RESET MASTER;”
• Run “SHOW MASTER STATUS;”
• (You should see it as “Position=106” and “File=xxxxxxxx.000001”)
• Open SQLyog navigate to any table not in the mysql database
• In the MySQL terminal run: “SHOW PROCESSLIST;”
• Find the process ID of SQLyog and kill it with: “KILL *;” (where * is the ID of the process)
• Go back into SQLyog and alter the table and add a column to the table you where in from a few steps ago (DO NOT NAVIGATE TO ANOTHER TABLE)
• Go back into the terminal and run a “SHOW MASTER STATUS;”
• You will notice that the “Position” should still be at position 106, which means it did not binlog the ALTER action.

If you do this without killing the process it should binlog it, because the process will be in the proper database (not in mysql database). In a nutshell, if you have a database ignored on the master, and you issue commands while in that database (even if they are fully qualified names) mysql will not binlog them.

This is NOT a bug in MySql as it is expected behavior: http://bugs.mysql.com/bug.php?id=24883

I am using:

* MYSQL 5.1.41-3ubuntu12.8

* Linux dev 2.6.32-24-server #43-Ubuntu SMP Thu Sep 16 16:05:42 UTC 2010 x86_64 GNU/Linux

[thegreatall]

'peterlaursen' wrote:

What is the SQLyog version you are using?

I tried (with 8.8 beta):

USE somedb

SELECT * FROM sometable — success

(stop server)

SELECT * FROM sometable — error ''can't connect ..'

(start server)

SELECT * FROM sometable — success

.. what shows that when we reconnect we 'reinstantiate' the USE statement. I think since version 8.13.

I believe It should be handled automatically by the reconnect flag/parameter in the MySQL API mysql_real_connect() (or mysql_connect_options() – don't remember). But only since 8.13 we used taht flag – before we used our own code (as we at the time had no other solution to handle reconnects with HTTP-tunnel as far as I remember).

BTW: are you using HTTP-tunnel?

I just upgraded today, and the issue is still present… (v8.71)

here's how to recreate:

Open SQLyog create a table in a database.

Go to the server and kill the connections, via “KILL” command.

Without closing SQLyog alter that same table and add a column.

If you look at the history you'll see it something like this:

Code:

/*[1:04:43 PM][ 0 ms]*/ SHOW VARIABLES LIKE 'lower_case_table_names';
/*[1:04:43 PM][ 0 ms]*/ SET NAMES 'utf8';
/*[1:04:43 PM][ 0 ms]*/ SET SQL_MODE='';
/*[1:04:43 PM][ 0 ms]*/ SHOW DATABASES;
/*[1:04:44 PM][ 0 ms]*/ USE `demo`;
/*[1:04:45 PM][ 0 ms]*/ SHOW FULL TABLES FROM `demo` WHERE table_type = 'BASE TABLE';
/*[1:04:57 PM][ 63 ms]*/ CREATE TABLE `demo`.`TableName1`( `test` INT );
/*[1:04:59 PM][ 0 ms]*/ SHOW FULL TABLES FROM `demo` WHERE table_type = 'BASE TABLE';
/*[1:05:19 PM][ 0 ms]*/ SHOW FULL FIELDS FROM `demo`.`TableName1`;
/*[1:05:19 PM][ 0 ms]*/ SHOW KEYS FROM `demo`.`TableName1`;
/*[1:05:19 PM][ 0 ms]*/ SHOW TABLE STATUS FROM `demo` LIKE 'TableName1';
/* KILL CONNECTION TO DATABASE HERE ON THE SERVER HERE !!!! */
/*[1:05:52 PM][ 62 ms]*/ ALTER TABLE `demo`.`TableName1` ADD COLUMN `test2` INT(11) NULL AFTER `test`;
/*[1:05:54 PM][ 0 ms]*/ SHOW FULL TABLES FROM `demo` WHERE table_type = 'BASE TABLE';

Notice how no USE command was sent, this causes an issue because MYSQL will not BINLOG this action (if you do not have the mysql table set to BINLOG [which most masters setups do not]).

[Author]

Posts