Archive for category MySQL
Convert SQL Server to MySQL
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 istinyint unsigned
by default, but not in MySQL!); - Add support for the default column values;
- Add support for the
bit
data type (usetinyint
instead ofbit
, 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!
AVG function returns 0.9999
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;
How to dump MySQL functions and stored procedures
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 themysql.ini
configuration file
N.B. Execute the following commands to display the list of stored procedures and functions:
SHOW PROCEDURE STATUS; SHOW FUNCTION STATUS;
JDBC and the “zero” date
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:
-
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';
-
Add the parameter
zeroDateTimeBehavior=convertToNull
to the connection url which will automatically convert the “zero” date toNULL
. 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.
Why GROUP_CONCAT returns BLOB?
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.