Archive for category 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

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

Why GROUP_CONCAT returns BLOB?

Posted by smoreau in MySQL, Tricks on 11 Feb 2010

Last week, when using the GROUP_CONCAT() function on a MySQL database, I got an unexpected result. 🙁

Indeed, instead of getting my result as VARCHAR types, I got it as BLOB types! For information, a BLOB is a binary large object that can hold a variable amount of data:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Because BLOB values are treated as binary strings, it is not easy to use. This is why we would prefer to have VARCHAR values.

So the question is how to get around this frustrating problem?

The answer is, for once, very simple! 😀
You simply need to:

  • Open your my.ini or my.cnf file;
  • Change the value of the group_concat_max_len system variable to 512 (no ‘k’ suffix);
  • Restart the mysql service

To verify if the value has been successfully updated, execute the following command in your mysql client:

mysql> show variables like "%concat%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 512   |
+----------------------+-------+
1 row in set (0.00 sec)

Note that you cannot set the value of group_concat_max_len to less than 1Kb using the MySQL Administrator GUI. Which means that the only way to set this system variable to 512 (which is less than 1Kb) is to edit your MySQL configuration file as described above.

, , , ,

5 Comments