MySQL errors

Posted: 18th October 2010 by Darren Cassar in Databases, MySQL
Tags: , , ,

Next time you have an error whilst running an sql script into mysql using the pretty easy redirect into eg: mysql -uroot -pmsandbox -h127.0.0.1 -P3306 dbname < filename you might want to give the -v option a shot.

MySQL normally outputs an error with the line number like:

[mysql@dcassar-ubuntu /mysql/dumps/stored_procs 11:10:12]$ mysql -uroot -pmsandbox -h127.0.0.1 -P5151 test < file
ERROR 1267 (HY000) at line 375: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '='

But 375 is not including comments and stuff so it’s a bit hard to go through the file to locate the exact section which is failing.

Run the same command with -v:

[mysql@dcassar-ubuntu /mysql/dumps/stored_procs 11:10:30]$ mysql -uroot -pmsandbox -h127.0.0.1 -P5151 -v test < file | tail -5
ERROR 1267 (HY000) at line 375: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_cs,IMPLICIT) for operation '='
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`dcassar`@`%` SQL SECURITY DEFINER */
/*!50001 VIEW `viewname` AS select ifnull .....

Btw, remember to include the `| tail -5` or you might regret the output :)

Because Sharing is Caring

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

  1. [...] This post was mentioned on Twitter by SQL Manager, Zuissi. Zuissi said: MySQL: MySQL errors: Next time you have an error whilst running an sql script into mysql using the pretty easy red… http://bit.ly/bhaXg6 [...]

  2. [...] URL: MySQL errors « MySQL Preacher -uroot-pmsandbox, a-shot–, give-the, pretty, pretty-easy, redirect-into, shot–, sql-script, [...]

*