MySQL Bugs: #16266: Definer is not fully qualified error during replication

Bug #16266	Definer is not fully qualified error during replication
Submitted:	6 Jan 2006 22:52	Modified:	15 Mar 2006 15:15
Reporter:	Gleb Paharenko
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.17 >	OS:	Linux (Linux)
Assigned to:	Alexander Nozdrin	Target Version:

Description:
First reported at:
  http://lists.mysql.com/mysql/193542

If the upgrade of MySQL from version older than 5.0.17 to 5.0.18 (17) was performed using
just raw copy of database directories, replication will break if it uses triggers

How to repeat:
Copy MyISAM table files and trigger TRG file created in 5.0.x (x<17, I've used 5.0.11) to
5.0.18 data directory,
setup a replication from 5.0.11 to 5.0.18 for example, and execute a query on 5.0.11
master.
Replication will break with error:
Last_Error: Error 'Definer is not fully qualified' on query. Default database: 'test'.
Query: 'insert into ab set a=3'
mysql> show triggers\G;
*************************** 1. row ***************************
  Trigger: t_ab
    Event: INSERT
    Table: ab
Statement:  set @a=NEW.a
   Timing: BEFORE
  Created: NULL
 sql_mode:
  Definer:
1 row in set (0.00 sec)
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.18-debug-log |
+------------------+

Ask me If you need more details to reproduce this problem.

Suggested fix:
Document this in the manual, I suggest to 'STOP SLAVE', mysqldump --add-drop-tables
--triggers the database, and import it again

I've found that replication breaks if it is a setup with 5.0.11 master and 5.0.18 slave.
Created on the master triggers without a DEFINER clause is replicated to slave (I see them
in the relay log), where it causes an error. So seems it is difficult to replicate
triggers from master older than 5.0.17 to 5.0.18(17) slave. In my opinion this should be
documented

Hi Gleb,

This is documented to a certain extent in the 5.0.17 change log, although I agree that a
note should probably be added about replication compatibility, such as here:

http://dev.mysql.com/doc/refman/5.0/en/replication-features.html

I've set this as a documentation bug for you, and will get this fixed. 

For reference, if you would like to create triggers on master which is older than the
slaves you can use the version comments, such as:

CREATE /*!50017 DEFINER = root@localhost */ TRIGGER ....

This will then replicate to the newer slaves, which will pick up the DEFINER from the
comment properly. 

Best regards

Mark

Changing back from Server from Server docs - as was not related to the initial bug report.

Notes will be added to documentation soon

The proposed solution doesn't seem to work.
- Master 5.0.18 (or 5.0.17)
- Slave 5.0.16
- Make a trigger on table:

CREATE TABLE t1 (id INT, updated DATETIME);
CREATE /*!50017 DEFINER='geert'@'localhost' */ TRIGGER t_t1_updated BEFORE UPDATE ON t1
FOR EACH ROW SET NEW.updated = CURRENT_TIMESTAMP;

Error on the Slave:
 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 'TRIGGER
t_t1_updated BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.updated = CURRENT_' at line 1' on
query. Default database: 'replica'. Query: 'CREATE DEFINER=`geert`@`localhost` TRIGGER
t_t1_updated BEFORE UPDATE ON t1 FOR EACH ROW SET NEW.updated = CURRENT_TIMESTAMP'

On a side note: skipping the log entry on the slave, and restarting replication. Then
dropping the trigger on the Master crashed 5.0.16.

There are no issues found between 5.0.18 Master/Slave. So this is more to document
incompatibility or something between versions.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2411

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2434

Pushed into 5.0 tree, currently tagged 5.0.20.

Pushed into 5.1 tree, currently tagged 5.1.8-beta.

Noted in 5.0.20, 5.1.8 changelog.

Replication slaves could not replicate triggers from older
servers that included no <literal>DEFINER</literal> clause in
the trigger definition. Now the trigger executes with the
privileges of the invoker (which on the slave is the slave SQL
thread). (Bug #16266)

I have a problem with this query here is the error message:
-------------------
060328 13:54:34 [ERROR] Slave: Error 'Definer is not fully qualified' on query. Default
database: 'symbology'. Query: 'Insert into tblproc_temp Select tblsymbology_temp.symbol
from tblsymbology_temp left join tblsymbology on tblsymbology_temp.symbol =
tblsymbology.symbol where tblsymbology.symbol is NULL', Error_code: 1446
--------------------
I don't understand why it would happen since I am not using or having any trigger in the
database. Why does it still happen to me?
Note: The Query is succesfully executed in the Master DB. No Error generated.

Thanks,
Clement

Clement,

thanks for comment, but unfortunately your description is not complete to start analysis.
Could you please submit a new bug report and provide the following information:
  - server version;
  - OS;

Also, if you could submit a reproducible test case, we would really appreciate that.

Thanks!