A short note about migrating dbaTicket application from MySQL 4.0 to 5.0
dbaTicket is a small internal MS Access application that is using MySQL as a backend.
First, I downloaded and installed MySQL 5.0.45 on Windows XP, then I downloaded and installed MySQL ODBC 3.51.19. During installation I picked up CP1250 for default character set of database. So far so good.
The next step was to prepare database and user who’ll own the dbaticket database:
cmd> mysqladmin -u root -p create dbaticket cmd> mysql -u root -p mysql> grant all privileges on dbaticket.* to 'dbaticket'@'localhost' identified by 'mypwd'; mysql> grant all privileges on dbaticket.* to 'dbaticket'@'%' identified by 'mypwd';
The next step was to export database from production server and import the dump into new MySQL 5.0 (everything below was executed while being logged on Windows XP workstation with MySQL 5.0 running):
E:> mysqldump -u root -p -h prodserver dbaticket > dbaticket.sql After I checked that data in the file is encoded in CP1250 characterset I imported data to MySQL 5 with: E:> cat dbaticket.sql | mysqldump -u root -p dbaticket
The next step was to prepare new ODBC System DSN, named DBATICKET with the MySQL ODBC driver 3.51.19. After that, I had to delete all linked tables from dbaticket.mdb and replace them with the new ones, referring to System DSN: DBATICKET.
At first all seemed OK, application worked. However, I soon noticed that all slovenian letters displayed according to CP1250 code page, except for letters Č and č. Weird. I checked
my.ini, I couldn’t find anything wrong – default-character-set was cp1250. Yet, I couldn’t read (nor write) Č and č characters with MS Access.
Quick search on Google revealed solution; you have to add
init-connect="SET NAMES cp1250" to [mysqld] section of my.ini.
# SERVER SECTION # ---------------------------------------------------------------------- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] init-connect="SET NAMES cp1250" # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=cp1250 ...