From SQL Server to MySQL

This week someone handed me a SQL Server backup, if I could read that data into a MySQL database. Off course was my initial answer, it's my job, sort of. However, after restoring the database in SQL Server there was no easy way to convert it into MySQL. I tried using de SQL Server Import and Export Wizard, after i had setup a correct ODBC connection to the newly created MySQL database.
No matter what i tried, SQL Server was not able to execute any query. The reason for this was the fact that SQL Server escapes table and field names with a double quote ("). MySQL on the other hand does not understand that kind of quoting. Unless....
Unless you tell MySQL to go into ansi-sql mode. Which proved to be the solution. To force this, you need to edit your ODBC connection;
Open your ODBC connections, select the connection you use to connect to your export server, click on configure , open the details pane, click on Misc Options and enter:

set session sql_mode='ansi'

in the 'Initial statement' field.
Hit ok.
You can now run your SQL Server import export wizard and your data will be copied this time.

SQL Server and PHP

Today was a big day for our Medialab. After a succeeded test we decided to port one of our applications from IIS, SQL Server and Coldfusion to Apache, MySQL and Coldfusion. The last was not very problematic. Only a couple of lines of code needed upgrading from the ancient version 4 where the application was written in to the current version 8. The most difficult part proved to be the transition from SQL Server to MySQL.
The only thing I could work with was a full database backup provided by our IT department. So I could only recreate the database in SQL Server. With the aid of MySQL Migration Toolkit part of the GUI tools I could transfer the database to MySQL. There was hwever a problem, the encoding. And no matter what I tried, there was always one token that did not translate well. So I decided to do the data by hand.


At the moment the server is a bit slow and I know why it is. I was looking through some log files and discovered that some SQL queries became slow. The queries in question were all optimized, indexes in place and so on. Then it struck me, there was a huge amount of unused data that could end up in this queries to be examined. And there was no easy way to get around this unwanted feature.
There was only one solution; clean up the database of this bits from the past. And so I wrote a couple of queries to examine how much data there would be deleted. An astonishing 1.000.000 rss items I keep in my database were orphans. That means items that belong to feeds that are gone or that I just don't read anymore. That's one third of all the items that are stored inside the feeds database, and I was never ever going to look at them again. (Well in theory I could, but over the last five years I have never done that)
So at the moment the items of over 500 RSS feeds are being cleared out and that will take a couple of hours. Next an index rebuild will be done so the indexes are optimal for the table again.

MySQL table cache

Dit is voorlopig het laatste deel over MySQL tuning. In de eerdere delen zagen we al dat een juiste instelling van de query_cache en key_buffer variabelen voor een betere performance van MySQL kunnen zorgen. De table_cache variabele verhoogt helaas niet de performance. Toch is een correcte instelling wel essentieel. Een foute (te lage) instelling beinvloedt de performance van MySQL in negatieve zin. Eerst maar eens onderzoeken wat de standaard instelling van MySQL is voor de table_cache.

MySQL Key buffers

In een andere howto hebben we gekeken hoe we ervoor kunnen zorgen dat MySQL queries kon oplsaan in zijn cache waardoor er een performance winst geboekt kan worden wanneer dezelfde query nogmaals wordt uitgevoerd. Voor de keys of indexen bestaat er eenzelfde mechanisme. Ik zal bij de behandeling ervan niet ingaan op de uitbreiding van dit principe in versie 4.1 en hoger.

Syndicate content
thank you for watching  Creative Commons License