[Building Sakai] Experiences migrating from Oracle to MySQL

[Matthew Jones]

Hi Miguel,

We did a conversion for a client about 6 months ago from Oracle to MySQL.
It took a decent amount of time and research, but in the end it turned out
okay.

One of the programs that worked really well (Almost did 95% of the work)
was OraDump to Mysql.
http://www.convert-in.com/ord2sql.htm#vserver

The software is like $80 but it saved well over that much in time. It takes
a standard Oracle dump file (which I couldn't find anything else to read)
and either populates into a Mysql database or creates a mysql dump file.
Because I didn't have the bandwidth available, I did this all on a Windows
EC2, which was able to be launched very quickly and was also very
inexpensive to run for the conversion.

Just had to remember to load the dump with the utf8 parameter, as the
school we converted had many utf-8 characters in their database.
mysql --default-character-set=utf8

I say 95% of the work because at the time there were a few (seemingly
random) tables that this program didn't convert completely. I found out it
wasn't complete by running table row counts on every table and doing a
comparison to see what didn't make it.

select concat('select concat(''',table_name,':'',count(*)) from
',table_name,';') from information_schema.tables where table_schema =
'<schema_name>'
(Will generate the mysql command to get counts, I forgot what I used for
Oracle but it was similar.

There were only like 3-4 tables, but they had a lot of data. We selectively
dumped those with SQL Developer to either XML or CSV then wrote some script
to change the timestamps to mysql compatible format and load them. They
have made many updates since then and might have fixed whatever problem I
had (I gave them some sample data but I couldn't get everything because it
was somewhat random and didn't want to send the entire dumps)

Here were some other notes I'd written down, which may or may not be
applicable to this current version.
---------------------------------------------------------------------------------------
The program didn't convert triggers to auto_increment, need to run this on
an auto_ddl'd database or something equal to get auto_increment statements.
Then run this against the converted database to apply all triggers.

select concat('ALTER TABLE ',table_name,' MODIFY ',column_name,'
',column_type,' NOT NULL AUTO_INCREMENT;') FROM  information_schema.columns
where TABLE_SCHEMA = 'sakai1' and EXTRA='auto_increment';

The program *seemed* to mess up some constraints which caused an error in
the load. This needs to be investigated. The lines in question were
ALTER TABLE `SAKAI_SITE_USER` ADD CONSTRAINT `` FOREIGN KEY(`SITE_ID`)
REFERENCES `SAKAI_SITE`(`SITE_ID`);

And I had to fix it by doing a search/replace for the
CONSTRAINT ``
to
CONSTRAINT

With a shell script. I'm not sure if this is important or not?

Also the current version doesn't set COLLATE correctly either, so the
script needs to be fixed
perl -pi -e 's/CHARACTER SET utf8/CHARACTER SET utf8 COLLATE utf8_bin/g'
<filename>


On Fri, May 31, 2013 at 6:10 AM, Miguel Carro Pellicer <
farreri.sakai at gmail.com> wrote:

>  Hi Subscribers!
>
> We're thinking about migrating from Oracle to MySQL in three of our
> instances. I'm looking for some opinions and experiences with the impact in
> Sakai after this migration.
>
> Regards and thanks in advance, Miguel.
> --
>
> [image: cid:image001.jpg at 01C9F98A.71C74E60]**** <http://www.samoo.es/>
>
> ** ** <http://www.samoo.es/>
>
> *Miguel Carro Pellicer*
>
> *Chief Operations Officer - Director de Operaciones*
>
> miguel.carro at samoo.es****
>
> +34 - 96 393 69 15****
>
> Skype: mcpellicer
>
> www.samoo.es****
>
> ** **
>
> Este  correo  y  sus archivos asociados son privados y confidenciales y va
> dirigido  exclusivamente  a su destinatario. Si recibe este correo sin ser
> el  destinatario del mismo, le rogamos proceda a su eliminación y lo ponga
> en  conocimiento del emisor. La difusión por cualquier medio del contenido
> de  este  correo podría ser sancionada conforme a lo previsto en las leyes
> españolas, Ley Orgánica 15/1999 de Protección de Datos de Carácter
> Personal.  No  se autoriza la utilización con fines comerciales o para su
> incorporación a ficheros automatizados de las direcciones del emisor o del
> destinatario.”****
>
> ** **
>
> This mail and its attached files are confidential and are only and
> exclusively intended to their addressee. In case you may receive this mail
> not being its addressee, we beg you to let us know the error by reply and
> to proceed to destroy it. The circulation by any mean of this mail could be
> penalised in accordance with the Spanish legislation. Is not allowed the
> use of both, the transmitter and the addressee’s, address with a commercial
> aim, or in order to be incorporated to automated data process or to any
> kind of files. ****
>
> ** **
>
> P Antes de imprimir este correo electrónico piense bien si es necesario
> hacerlo. El medioambiente es cosa de todos.****
>
> ** **
>
> ** **
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130601/7d787f7b/attachment.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 3022 bytes
Desc: not available
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130601/7d787f7b/attachment.jpe