Bug #10583 Error query in replication
Submitted: 12 May 2005 11:01 Modified: 12 May 2005 11:34
Reporter: Abner Trindade Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:Mysql 5.0.4-beta-max OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[12 May 2005 11:01] Abner Trindade
Description:
My Mysql-Servers version.
---Master---
Server version          3.23.55-max-log
Protocol version        10
Connection              gorpo via TCP/IP
TCP port                3306
Uptime:                 2 days 12 hours 5 min 53 sec

---Slave---
Server version          5.0.4-beta-max
Protocol version        10
Connection              alucicrazy via TCP/IP
TCP port                3306
Uptime:                 1 hour 36 min 41 sec

---Master table---

mysql> desc cons_virtual.call;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| msg_id     | int(10) unsigned    |      | PRI | 0       |       |
| parent_id  | int(10) unsigned    | YES  |     | 0       |       |
| ref_id     | int(10) unsigned    |      | MUL | 0       |       |
| sender_id  | int(10) unsigned    | YES  | MUL | 0       |       |
| receipt_id | int(10) unsigned    | YES  | MUL | 0       |       |
| date       | datetime            | YES  | MUL | NULL    |       |
| viewed     | tinyint(1)          | YES  |     | NULL    |       |
| analise    | tinyint(1)          | YES  | MUL | NULL    |       |
| origem     | tinyint(3) unsigned | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+

---replication error---
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.1.1.1
                Master_User: art
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.6381
        Read_Master_Log_Pos: 2175387
             Relay_Log_File: art-relay-bin.000001
              Relay_Log_Pos: 13564507
      Relay_Master_Log_File: mysql-bin.6369
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB: mysql,test,bd_conline_backup
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: master%.%,temporario%.%,netqi%.%
                 Last_Errno: 1064
                 Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call (msg_id, parent_id, ref_id, sender_id, receipt_id, date, viewed, analise, o' at line 1' on query. Default database: 'cons_virtual'. Query: 'insert into call (msg_id, parent_id, ref_id, sender_id, receipt_id, date, viewed, analise, origem) values ('166258', '165080', '165080', '312', '4786641', now(),'0','5','1')'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 39888433
            Relay_Log_Space: 124812552
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: NULL
1 row in set (0.01 sec)

---table slave---
mysql> desc cons_virtual.call;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| msg_id     | int(10) unsigned    | NO   | PRI | 0       |       |
| parent_id  | int(10) unsigned    | YES  |     | 0       |       |
| ref_id     | int(10) unsigned    | NO   | MUL | 0       |       |
| sender_id  | int(10) unsigned    | YES  | MUL | 0       |       |
| receipt_id | int(10) unsigned    | YES  | MUL | 0       |       |
| date       | datetime            | YES  | MUL | NULL    |       |
| viewed     | tinyint(1)          | YES  |     | NULL    |       |
| analise    | tinyint(1)          | YES  | MUL | NULL    |       |
| origem     | tinyint(3) unsigned | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
9 rows in set (0.01 sec)

My replication error:
When replication goes to update/insert(off course)records on table call into database cons_virtual.
This occurs due to binarylog of master or the word "call" turned a private word in mysql-5.0?
I have too manys slaves in diferent(3.23.xx and 4.x.x) version but my master is version 3.23.x and i can't change upgrade master it now.
If i run this query by hand with works fine but a indicate database.table ex:
insert into cons_virtual.call (msg_id, parent_id, ref_id, sender_id, receipt_id, date, viewed, analise, origem) values ('2', '2', '2', '312', '11', now(),'0','5','1');set global sql_slave_skip_counter=1;slave start;

How to repeat:
Create a table called call on the server with 3.23.xx and 4.x.x version, put a slave with version 5.0.4-beta and when the replication will update it the error will ocurr.

Suggested fix:
Report the changes of reserved words on the docs.
[12 May 2005 11:34] Guilhem Bichot
Hello,
Well there is a section of reserved words, just we don't put the additions to it in the changelog:
http://dev.mysql.com/doc/mysql/en/reserved-words.html
As said there, CALL is reserved (it's used for stored procedures). The solution is: change the query sent to your master to use ``: `call` instead of call. With the ``, your 5.0 slave will understand that call is used as an identifier, not a SQL word.