Archive for March, 2011

Segmentation fault at end of PHP script

Posted by smoreau in PHP on 14 Mar 2011

This one is a very strange problem!
I created a little PHP script which connects to a MySQL database. Nothing amazing, except that I needed to run it via command line using the php command. What happened is that the script ran perfectly fine but, for some reason, it returned “Segmentation fault” when it reached the end of the file! 😯
It reminds me of the old days when I worked on C language, I used to see this not-very-useful message quite often. 😉

Anyway, why do we get a “Segmentation fault” at the end of a PHP script? And when I say ‘the end’, I mean after any other lines!

Looking around the web, I found the following bug report: http://bugs.php.net/bug.php?id=43823
This user seems to have the same problem than me except he is using a PostgreSQL database instead of a MySQL database.

Reading through the page, it looks like the problem comes from the order in which PHP loads his extensions! They explain that PostgreSQL should be loaded before cURL. If I apply the same logic, MySQL should then be loaded before cURL too.

To do that, we need to comment out the line extension=curl.so from the file /etc/php5/cli/conf.d/curl.ini:

# configuration for php CURL module
#extension=curl.so

And add it to the file /etc/php5/cli/conf.d/mysql.ini AFTER the line extension=mysql.so:

# configuration for php MySQL module
extension=mysql.so
extension=curl.so

To be honest, I don’t really like this solution as it is a bit messy. I would prefer PHP developers to fix the root cause or at least have a way to change the order in which PHP loads the extensions.

NB: I was executing this PHP script on a Debian GNU/Linux 5.0 with Apache 2.2.9 and PHP 5.2.6.

, , , , , ,

1 Comment

How to dump MySQL functions and stored procedures

Posted by smoreau in MySQL on 01 Mar 2011

I discovered something interesting about MySQL the other day. Interesting enough to share it with you. 😉
I was in the process of moving a MySQL database from a server to another. In order to do this, I created a dump file from the source database using the command mysqldump and I then restored the database on the destination server using the previously created dump file.

Below is the command I used to create the dump file:

mysqldump -u dbuser -p dbname > dump.sql

And here is the command used to restore the database:

mysql -u dbuser -p dbname2 < dump.sql

The interesting thing is that the functions and stored procedures have not been migrated using these commands… 🙁
To be honest with you, this was the first time I encountered this problem. Which probably means that it is the first time I migrate a database containing functions and/or stored procedures.

Checking the man page of the mysqldump command, I found the following:

--routines, -R
Dump stored routines (procedures and functions) from the dumped databases. Use of this option requires the SELECT privilege for the mysql.proc table. The output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION statements to re-create the routines. However, these statements do not include attributes such as the routine creation and modification timestamps. This means that when the routines are reloaded, they will be created with the timestamps equal to the reload time.
If you require routines to be re-created with their original timestamp attributes, do not use --routines. Instead, dump and reload the contents of the mysql.proc table directly, using a MySQL account that has appropriate privileges for the mysql database.
This option was added in MySQL 5.0.13. Before that, stored routines are not dumped. Routine DEFINER values are not dumped until MySQL 5.0.20. This means that before 5.0.20, when routines are reloaded, they will be created with the definer set to the reloading user. If you require routines to be re-created with their original definer,
dump and load the contents of the mysql.proc table directly as described earlier.

Alright, that looks straight forward! We simply need to add the parameter --routines to the mysqldump command in order to include the functions and stored procedures to our dump file.
For example:

mysqldump -u dbuser -p dbname --routines > dump.sql

We now have our dump file containing the functions and stored procedures.
I thought I was done at this stage. But I got the following error message while restoring the database on the destination server:

ERROR 1419 (HY000) at line 1140: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

The error message is pretty clear. Binary logging needs to be enabled in order to restore the functions. There are two ways for doing this:

  • Execute the following command into the MySQL console: 
    	SET GLOBAL log_bin_trust_function_creators = 1;
  • Add the line log_bin_trust_function_creators = 1 to the mysql.ini configuration file

N.B. Execute the following commands to display the list of stored procedures and functions:

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

, , , , , ,

6 Comments