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!
Memory Leaks / Problems with Long-Running Symfony / Doctrine Console Applications
Hi!
I recently built several console applications that have to keep running as daemons as part of a more complex website. As soon as I added Doctrine for its highly comfortable ORM functionality, I noticed a significant increase in the applications' memory usage, which made sense because it would load Doctrine's code in order to use it. The worst part of it, however, was that the processes kept eating up more and more memory the longer they ran and with each Doctrine query they executed. Finally the processes ended up being killed by Linux's OOM Killer due to the high amount of memory that they wanted allocated for them.
Clearing the Doctrine Entity Manager ($entityManager->clear()) and triggering PHP's garbage collection manually did not help at all. So I assumed it had something to do with the data that Doctrine accumulates in the background.
During my research I finally stumbled upon this Stack Overflow question: Memory leaks Symfony2 Doctrine2 / exceed memory limit
Apparently, Doctrine uses an SQL Logger to log each and every query it uses when running in debug mode.
Due to the nature of Symfony console applications starting up in the dev environment by default when launching them from the command line, it also kept enabling the debug mode. That in turn enabled the SQL Logger which gathered more and more data and kept it in memory.
I decided to launch the processes by simply manually disabling the debug flag. Using the dev environment is not a problem for me, so I just did the following:
$ php app/console custom:command --no-debug
You could also set the environment variable SYMFONY_DEBUG to 0 before launching the command without the --no-debug flag, as it also respects its value - refer to app/console's source code.
Alternatively, you could start it in a non-dev environment:
$ php app/console custom:command --env=prod
or
$ php app/console custom:command -e prod
or just set the environment variable SYMFONY_ENV before launching it.
This way, the SQL Logger is not enabled.
My processes have been running at stable memory usage size ever since.
Another way would be to deactivate the SQL Logger in the code by principle, but that would be defeating the purpose of the nice built-in features of enabling/disabling the debugging mode, so I rather chose the first path. It might come in handy sooner or later after all, and reverting those changes just to re-enable the SQL Logger would be an unnecessary pain.
I hope this was helpful to you. It certainly took me some time to get behind it.
AJAX with Chrome – empty responseText
Hi there!
When I implemented AJAX today in a new script of mine and persuaded 😛 a couple of friends of mine to visit the page, I found that the Chrome users received error messages when trying to use the AJAX functions. However, they actually worked. The server received the AJAX request and processed it correctly, leaving me scratching my head.
Turns out that Chrome only received an empty AJAX response in responseText which caused the JavaScript function to throw out an error message. But why was it empty?
A couple of web searches later I realized that apparently not too many people had come across that problem. Luckily enough I found a post in a forum that pushed me in the right direction.
Chrome seems to be a little sensitive concerning headers in the AJAX responses. Because I hadn't given my server-side AJAX processing script the appropriate "Content-Type: text/plain" header, it didn't "accept" the response as text.
Why it has to be so picky, however, I cannot understand 🙂
If you have encountered that problem before, I hope I managed to help you out.
Certainly made me go a little crazy for a while there 😛
Compiling PHP 5 with IMAP support on SuSE / openSUSE Linux
If you get the following configure error:
configure: error: utf8_mime2text() has old signature, but U8T_CANONICAL is present. This should not happen. Check config.log for additional information.
It's probably because you're missing either the libc-client-devel package or imap-lib and imap-devel. Fire up yast and install those. You should be good to go now 🙂
(I have openSUSE 11.0 and it doesn't have the libc-client-devel package, but I read about it on another page and thought I'd add it, just to be safe 🙂 )