Posts Tagged MySQL

Convert SQL Server to MySQL

Posted by smoreau in MySQL, Python on 17 Oct 2011

Some time ago, I had to convert a Microsoft SQL Server database to a MySQL database. The main reason was the cost of the SQL Server license for such a small database.

Looking on the web, I found the following open source application:

Name: mssql2mysql
URL: http://sourceforge.net/projects/mssql2mysql/
Description: mssql2mysql is a python script used to create a SQL dump from a Microsoft SQL server that is ready to use with MySQL. Supports Schema and data dumping, including Primary Keys for each table, allows to dump all data or just a small portion of it.

To be honest, this tool wasn’t working as expected but it was a very good start! 🙂

Below is the list of changes I’ve made:

  • Add support for the bool data type;
  • Add support for the datetime data type;
  • Only convert tables and views of the ‘dbo’ owner;
  • Add the test columnas[6]==True on the primary key;
  • Add support for the uniqueidentifier data type;
  • Add support for the tinyint data type (SQL Server is tinyint unsigned by default, but not in MySQL!);
  • Add support for the default column values;
  • Add support for the bit data type (use tinyint instead of bit, go to this page for more information).

To download the amended script, please click on the following link: mssql2mysql.tar.bz2

This script worked perfectly fine for me. However, please note that my interest was focused on converting the database structure but not the content!

, , , , ,

1 Comment

AVG function returns 0.9999

Posted by smoreau in MySQL on 15 Sep 2011

This one is a very odd bug! 😐

For some reason, the AVG function from MySQL is always returning the value 0.9999 instead of the average value. This has been experienced on MySQL 5.5.12 hosted on Amazon RDS (Relational Database Service).
However, the exact same query executed on MySQL 5.1.28 is returning the right values.

Why is that? Is it a bug in MySQL 5.5.12?
I did a search on internet and I couldn’t find anything about it. So to be honest, I am not sure what is this bug or even if MySQL is aware of it.

Anyway, if you encounter the same problem, you can simply replace the AVG function by the combination SUM/COUNT.
For example, the following query:

SELECT student_name, AVG(test_score)
FROM student
GROUP BY student_name;

can be replaced by the one below:

SELECT student_name, SUM(test_score)/COUNT(test_score)
FROM student
GROUP BY student_name;

, , , ,

3 Comments

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

JDBC and the “zero” date

Posted by smoreau in Java, MySQL on 06 Feb 2011

A few weeks ago, I was in the process of writing a little script in Java to extract data from a MySQL database. Because this is what we can call a micro project, I decided to simply use JDBC to talk to the database.

But when I tried to run my script, I got the following exception:

java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Date
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
	at com.mysql.jdbc.ResultSet.getDateFromString(ResultSet.java:2032)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1956)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1924)
	at com.mysql.jdbc.ResultSet.getDate(ResultSet.java:1972)
	...

After looking at the database, it seems that this exception has been thrown because the fields of type datetime contain the value ‘0000-00-00 00:00:00’. Don’t ask me why I have this kind of value in the database! Obviously, we would prefer to have a NULL value instead of a “zero” date.

There are two solutions to fix this problem:

  1. Replace all the “zero” dates by NULL using the following SQL query:

    UPDATE table SET datefield = NULL WHERE datefield = '0000-00-00 00:00:00';
  2. Add the parameter zeroDateTimeBehavior=convertToNull to the connection url which will automatically convert the “zero” date to NULL. Please find below an example of connection url with this parameter:

    jdbc:mysql://localhost:3306/dbname?zeroDateTimeBehavior=convertToNull

Personally, I choose the second solution as I didn’t want to change the original data of the database.

, , , ,

No Comments