Unsupported Screen Size: The viewport size is too small for the theme to render properly.

[1/2 A Bug] Which Can Break Slave

forums › forums › SQLyog › SQLyog: Bugs / Feature Requests › [1/2 A Bug] Which Can Break Slave

  • This topic is empty.
Viewing 11 reply threads
  • Author
    Posts
    • January 14, 2011 at 6:13 pm #12228
      thegreatall
      Member

      Ok, sorry for the bad title, but I couldn't find another good name for it. So let me explain the problem…

      Here at work, we have a simple master/master setup in MySql, which we setup and everything was working fine, but for some reason the “slave” of the 2 masters was breaking and stopped syncing. When we dug though the logs we found that the table(s) that we altered did not get altered on the slave. After some researching we found the problem, this is not a “bug” by definition, but you would expect SQLyog to issue a new USE command when a connection gets broken and re-connected.

      SQLyog automatically issues a USE command when you click into the database or go to ALTER a table, however if you are already in the database, and you loose a connection to the database, SQLyog automatically reconnects to the server silently then sends the ALTER (or whatever command you asked it) without first sending a USE command, which by default MySQL will put you into the “mysql” database, which “most” people do not binlog, causing MySQL to ignore the command sent and not log it into the BINLOG.

      This is not an issue in MYSQL as MYSQL expects you to send a USE command before issuing any INSERT/ALTER/DELETE/exc… command.

      SOLUTION:

      If you loose connection to a server a user would expect it to re-issue a USE command on the “auto” reconnect. OR on a connection-reconnect make SQLyog change it's database to the default database.

      WORKAROUND:

      Send a USE command or re-select the database from the database list.

      If you would like more data on this issue or did not explain it well enough I'd be happy to assist further.

      -Nathan

    • January 14, 2011 at 6:54 pm #31823
      peterlaursen
      Participant

      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?

    • January 14, 2011 at 7:11 pm #31824
      thegreatall
      Member
      '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]).

    • January 14, 2011 at 9:33 pm #31825
      peterlaursen
      Participant

      You cannot see USE statements in HISTORY if it is handled automatically by a parameter to an API call. Only what SQLyog executes *as SQL* you can see. What the client code handles *in binary protocol* you cannot see. What happens during (re)connections is handled inside API calls(mysql_real_connect() and mysql_options()) and not as SQL.

      That not not exclude that you you have a problem (and we have a bug), but I believe the solution is another than what you imagine. I will let our developer team take over this discussion from Monday.

    • January 14, 2011 at 9:59 pm #31826
      peterlaursen
      Participant

      A clarification: In our code we use the mysql_options() function (http://dev.mysql.com/doc/refman/5.1/en/mysql-options.html) to set the reconnect flag.

      We use 5.0 client code in SQLyog currently (we tried 5.1, but faced some memory leaks, a crash and similar issues). The issue your are facing could be related to the API version we use in SQLyog.

      I get suspicious about an issue with the client codewhen I see this in the API documentation: “Note: mysql_real_connect() incorrectly reset the MYSQL_OPT_RECONNECT option to its default value before MySQL 5.1.6. Therefore, prior to that version, if you want reconnect to be enabled for each connection, you must call mysql_options() with the MYSQL_OPT_RECONNECT option after each call to mysql_real_connect(). “

    • January 17, 2011 at 7:38 am #31827
      nithin
      Member

      Your first post:-

      Quote:
      Here at work, we have a simple master/master setup in MySql, which we setup and everything was working fine, but for some reason the “slave” of the 2 masters was breaking and stopped syncing.

      When we dug though the logs we found that the table(s) that we altered did not get altered on the slave. After some researching we found the problem, this is not a “bug” by definition, but you would expect SQLyog to issue a new USE command when a connection gets broken and re-connected.

      What error you got during syncing?

      How you connected to server(direct/ssh/http)?

      Your server versions?

      Alter table returns any error? or it executed against wrong database/table?

      2nd post:-

      Quote:
      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 that ALTER TABLE statement used fully qualified table name(database.table), so USE database is not required here.

      Can you tell whether it's thrown any error or table is not altered, when you did this ALTER TABLE after killing the connection? Please explain it.

      I have tried this case, it worked fine for me.

      Its not showing the USE database in history because we handle this internally.

      Please try this case and let us know whether it works for you:-

      – Select a database from object-browser(the left panel where the databases are shown in tree view)

      – Execute a SELECT query from editor like, SELECT * from (Note that the table name is not fully qualified)

      – Stop server. Reconnect. Execute this query again. It returns the result, because we handled the USE internally.

      Please explain the case in step-by step so that we can reproduce and handle the problem quickly.

    • January 17, 2011 at 8:59 am #31828
      peterlaursen
      Participant

      It looks like Nithin and I understand differently. I understand that you are using replication (not SQLyog/SJA data sync). This is how I understand “a simple master/master setup in MySql”. Please clarifiy your setup: replication, data sync or both?

    • January 17, 2011 at 3:59 pm #31829
      thegreatall
      Member

      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

    • January 17, 2011 at 4:18 pm #31830
      peterlaursen
      Participant

      OK .. looks like you have given a good reproducible description. Lets us check this.

    • January 20, 2011 at 5:38 am #31831
      nithin
      Member

      We have tried the case at our end in Linux as well as Windows. But it works as expected.

      Quote:
      You will notice that the “Position” should still be at position 106, which means it did not binlog the ALTER action.

      Its not happening at our end, the “Position” got changed after killed-Alter table.

      Since we are not able to reproduce this we can consider the web meeting with screen sharing session.

      We request you to create a support ticket by sending mail to [email protected]

      Please tell us your timezone also in the mail.

    • January 21, 2011 at 9:10 am #31832
      peterlaursen
      Participant

      BTW: I came across a Blog that says that maybe you should not use binlog_ignore:

      http://krishna-mysql.blogspot.com/2011/01/test-default-schema.html

    • February 21, 2011 at 4:12 pm #31833
      peterlaursen
      Participant

      As I have replied in your ticket:

      I have reported this to MySQL bugs system:

      http://bugs.mysql.com/bug.php?id=60188

      .. where it has been verified as a server bug. They also set the severity 'S2' what leaves hope that it will be fixed soon. We will not attempt any workaround for this.

  • Author
    Posts
Viewing 11 reply threads
  • You must be logged in to reply to this topic.