We're using MySQL 5.0.22 on CentOS 3.7 (running on VMWare). We use InnoDB tables and also use views, stored procedures, and functions. I had to move our development databases to a new server this week by doing a backup with mysqldump and then restoring the databases on the new server. I ran into a weird problem, in one database the views were restored as tables! I also got this error:
ERROR 1418 (HY000) at line 206: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) Looking at the dump file, in general the sequence of statements is: 1. create tables 2. insert into tables 3. create tables that are really views in the original db 4. create functions 5. create stored procedures 6. drop the tables that should be views and re-create them as actual views In my case, #4 caused the error, #5 succeeded (all procs were created), and then it's like #6 doesn't happen. I fixed this by adding deterministic to the offending function, but it brings up some questions. Why does mysqldump first create views as tables, then drop and re-create them as views? Why did the stored procs get created after the error, but not the views? I also tried to create another function without specifying deterministic, and was unable to...so how did I create that function in the first place? Note that when we built out the new server, we used the same my.cnf file so the two should have been configured the same. Thank you. Donna DeVaudreuil
