Rewrite MySQL / MariaDB Database Dump Create View Statements For Current User
Hi!
If you would like to import a database dump file created by MySQL's or MariaDB's mysqldump executable, but it contains statements to create views, the import process may abort with an error when it comes to creating views.
When creating a view, MySQL wants to know who created it, and for that it needs a username and the host. If the user who is executing the import does not have sufficient privileges or the original user referenced in the dump does not exist (for example when importing the dump into a fresh database for a migration), this leads to an error.
Usually when I import a dump, I don't care so much about the "SQL Security Definer", so I just want to set it to the importing user.
You can generate a new, modified SQL dump file very easily with the following shell command:
$ sed -r 's#^(/\*!50013 DEFINER=).+?( SQL SECURITY DEFINER \*/)$#\1CURRENT_USER\2#' input_file.sql > output_file.sql
This command simply scans through the entire dump, looking for the statement created by mysqldump which triggers the database to create the view if it doesn't exist already. It then sets the user information to CURRENT_USER which refers to the user that is currently executing the import.
Please note that because the search pattern is so specific, it will probably require some modification in the future, depending on the version of mysqldump you're using and if / how they change this particular statement. On the upside the chance that it will accidentally modify something it shouldn't is pretty low.
I hope this is helpful to you!
Thanks for reading!
Compiling Exim and MySQL on a (CentOS) 64 bit Environment
Hey there!
I was recently trying to compile Exim with MySQL support on a CentOS 5.x 64 bit system. However, I had my dear share of trouble when it came to adjust the Local/Makefile for the 64 bit architecture.
The following error was what I got stuck on:
...
gcc dkim-exim.c
awk '{ print ($1+1) }' cnumber.h > cnumber.temp
rm -f cnumber.h; mv cnumber.temp cnumber.h
gcc version.c
rm -f exim
gcc -o exim
/usr/bin/ld: cannot find -lmysqlclient
collect2: ld returned 1 exit status
make[1]: *** [exim] Error 1
make[1]: Leaving directory `/root/incoming/exim-4.69/build-Linux-x86_64'
make: *** [go] Error 2
Turns out it was easier to solve than I thought. The point was that it was looking for 32 bit libraries where it should have been looking for 64 bit ones. I adjusted the following lines in Local/Makefile:
LOOKUP_INCLUDE=-I /usr/include/mysql LOOKUP_LIBS=-L/usr/lib/mysql -lmysqlclient -lz -lm
to say this:
LOOKUP_INCLUDE=-I /usr/include/mysql LOOKUP_LIBS=-L/usr/lib64/mysql -lmysqlclient -lz -lm
And - hurray! - it works 🙂
Be careful though, it seems to need the /mysql after /usr/lib64.