MySQL Forums
Forum List  »  Backup

mysqldump and views
Posted by: Manjit Patel
Date: November 02, 2005 03:32AM

I upgraded to the MySQL Development Release 5.0.15 last Tuesday morning, everything seemed to be running fine. On Wednesday morning I discovered that Replication had stalled at 5am & found the following messages in the error log on the slave.

We have 53 views in our database so there is a error message in the binary log for each view.

Output of Error log on Slave:

***********************************************************
051026 5:00:13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the sl
ave SQL thread with "SLAVE START". We stopped at log 'binarylog.000086' position 15823693
051026 8:18:01 [Note] Slave SQL thread initialized, starting replication in log 'binarylog.000086' at pos
ition 15823693, relay log './slave-relay-bin.000201' position: 2864
051026 8:18:01 [ERROR] Slave: Error 'View definer is not fully qualified' on query. Default database: 'DB
Issues'. Query: 'CREATE TEMPORARY TABLE `EU_Count` SELECT * FROM `EU_Count` WHERE 0', Error_code:
1446

***********************************************************

Output of show master status:

*************************** 1. row ***************************
File: binarylog.000086
Position: 15858788
Binlog_Do_DB:
Binlog_Ignore_DB:

************************************************************

Output of show slave status:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: user
Master_Port: 3378
Connect_Retry: 60
Master_Log_File: binarylog.000086
Read_Master_Log_Pos: 15858788
Relay_Log_File: mysql02-relay-bin.000202
Relay_Log_Pos: 565
Relay_Master_Log_File: binarylog.000086
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1446
Last_Error: Error 'View definer is not fully qualified' on query. Default database: 'DBIssues'. Query: 'CREATE
TEMPORARY TABLE `EU_View` SELECT * FROM `EU_View` WHERE 0'
Skip_Counter: 0
Exec_Master_Log_Pos: 15830206
Relay_Log_Space: 30122
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

************************************************************

I have a CRON job running that dumps my database to a text file every day at 5am so this is what's causing the problem. This still happens regardless of whether I run this as a CRON task or direct from the shell prompt.

This didn't cause a problem in version 5.0.12.
Does anyone know:
1. How to stop the mysqldump outputs being logged to the binary log (I've tried --master-data=2 --single-transaction but I get a lock table error).
2. How can I stop this error from occuring, is it a bug?

Thanks in advance
Manjit

Options: ReplyQuote


Subject
Views
Written By
Posted
mysqldump and views
3147
November 02, 2005 03:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.