problem with converting mssql to mysql

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

Jonas Gunnarsson 44 posts 194 karma points

Mar 31, 2010 @ 00:18
0

Problem with converting msSQL to MySql
Hi,

I try to to convert a existing umbraco site from msSql to MySql, but I got several SQL syntax error when I use MySql Migration ToolKit.

The columns I get trouble with is
- cmsDocument
- cmsTask
- umbracoStylesheetProperty
- umbracoUser
- cmsDocumentTyep
- cmsMemberType
- umbracoStatSession
- umbracoNode
- cmsPropertyType
- cmsMacroProperty
- cmsMacro
- umbracoAppTree
For example i got this error message for cmsDocument:
```
You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near '(0),
```
```
  PRIMARY KEY (`versionId`),
```
```
  CONSTRAINT `FK_cmsDocument_umbracoNode` FO' at line 12
```
And this for cmsTask
```
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(0),
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `taskTypeId` TINYINT(3) NOT NUL' at line 2
```
Any suggestion what I can do to solv this problem?

I use umbraco 4, asp.net 2.0 and MySql server v5.4 on localhost.

Best regards Jonas
Copy Link
Paul Sterling 718 posts 1534 karma points MVP 9x admin c-trib

Mar 31, 2010 @ 01:29
0
Jonas -

Verify you have the correct datalayer keyword in your connection string so umbraco knows you're using MySQL:
```
<add key="umbracoDbDSN" value="Datalayer=MySQL; Database=mydb; 
Server=myserver; User ID=myuser; Password=mypass;" />
```
-Paul
Copy Link
Jonas Gunnarsson 44 posts 194 karma points

Mar 31, 2010 @ 07:58

0

I think I have the correct datalayer keyword, I´m at work so I can´t check right now, but I can login to umbraco but all my content, xslt-files and so on are gone. I used the install guide to change database from msSQL to MySql, and I have no problem to change database from msSQL to MySql but I think my problem is when I use the MySql ToolKit to convert the existing SQL-database to MySQL and I get these errors.

Because I use a exsisting umbraco project, that works great with msSQL, I suppose that I have all the file in the file structure but the problem is in the database.

Sry for my bad english, but I hope you understand what I mean.

/Jonas

Copy Link
T_Gu3 41 posts 30 karma points

May 12, 2010 @ 11:39
0
I experienced this problem and got around it by doing the following:
- Go to the MySQL Migration Toolkit's Creation Results screen
- You should see the tables that contain errors, click on the first one and click the Advanced button
- You will now see the SQL Create Script for the table, look in the script for DEFAULT (0) or DEFAULT (1)
- Delete any of these you find so `closed` TINYINT NOT NULL DEFAULT (0), becomes `closed` TINYINT NOT NULL,
- When this is done click Apply Changes and repeat for every table with errors
- When all tables have been updated click Recreate Objects hopefully you should get no more errors
Hope this helps

Thomas
Copy Link
T_Gu3 41 posts 30 karma points

May 12, 2010 @ 12:05

0

I have updated the above after looking at the migration toolkit again:
Go to the MySQL Migration Toolkit's Creation Results screen
You should see the tables that contain errors, click on the first one and click the Advanced button
You will now see the SQL Create Script for the table, look in the script for DEFAULT (0) or DEFAULT (1)
When you find one these in the script remove the brackets surrounding the number
Example: closed TINYINT NOT NULL DEFAULT (0), becomes closed TINYINT NOT NULL DEFAULT 0,
When this is done click Apply Changes and repeat for every table with errors
When all tables have been updated click Recreate Objects hopefully you should get no more errors

Thomas

Flag this post as spam?