Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - MsSQL DB to MySQL DB
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

MsSQL DB to MySQL DB
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
Andersen View Drop Down
Groupie
Groupie


Joined: 07 July 2002
Location: Denmark
Status: Offline
Points: 152 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Andersen Quote  Post ReplyReply Direct Link To This Post Topic: MsSQL DB to MySQL DB
    Posted: 09 April 2006 at 8:20pm
I have problems converting version 8 MsSQL database to MySQL.
I´m using the MySQL Migration toolkit, and most of the migration is going well, but there are some errors.
 
Here is a snip of the logfile.. Please Note, I´m running MySQL 4.1.18 and the 3.5ODBC driver:
 
`forum2_dbo`.`tblActiveUser`
      ----------------------------
          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 'getdate(),
              `Active` DATETIME NOT NULL DEFAULT getdate(),
              `OS` VARCHAR(15) CH' at line 4
 
Same error goes for:
tblAuthor
tblForum
tblPMMessage
tblSession
tblThread
tblTopic
 
2. Data Bulk Transfer
---------------------
`forum2_dbo`.`tblActiveUser`
      ----------------------------
          Table 'forum2_dbo.tblactiveuser' doesn't exist
          0 row(s) transfered.
      `forum2_dbo`.`tblAuthor`
      ------------------------
          Table 'forum2_dbo.tblauthor' doesn't exist
`forum2_dbo`.`tblForum`
      -----------------------
          Table 'forum2_dbo.tblforum' doesn't exist
          0 row(s) transfered.
`forum2_dbo`.`tblPMMessage`
      ---------------------------
          Table 'forum2_dbo.tblpmmessage' doesn't exist
          Table 'forum2_dbo.tblpmmessage' doesn't exist
          Table 'forum2_dbo.tblpmmessage' doesn't exist
          Table 'forum2_dbo.tblpmmessage' doesn't exist
          Table 'forum2_dbo.tblpmmessage' doesn't exist
          0 row(s) transfered.
`forum2_dbo`.`tblThread`
      ------------------------
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          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 '2005-03-16 12:18:27", 1, "83.88.144.229", 0, NULL),
                          (24322, 3202, 173, "Ja je' at line 968
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          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 'Hvis den bliver ved med at vandre, sÃ¥ skal du kigge pÃ¥ hvormeget cirkulation d' at line 619
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          Table 'forum2_dbo.tblthread' doesn't exist
          0 row(s) transfered.
`forum2_dbo`.`tblTopic`
      -----------------------
          Table 'forum2_dbo.tbltopic' doesn't exist
          0 row(s) transfered.
 
One thing which is part of all the error descriptions are to check the version of MySQL, so I did, and doubble checked again. Installed 3.51.12 ODBC driver again (just to make sure). But still I get the same errors.
 
What to do from here?
 
Greets
Andersen
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post Posted: 10 April 2006 at 11:50am
I'm surprised that this tool kit doesn't handle these problems, maybe worth looking for a better one.

The issue is that in MS SQL Server in a date field you can set the default value as 'GetDate()' this only works in SQL Server. In mySQL v4.1 it doesn't have a default date property, so in version 8 this is handled by the ASP code and not the database.

The rest of you errors look like they are related to this issue.

Another thing to watch for is that mySQL 4.1 doesn't have a bit or boolean datatype, so this needs to be changed to 'tinyint', but hopefully the conversion tool should handle this.

This means that you can remove all 'GetDate()' default values from the SQL Server database before running the conversion tool. This should get rid of this error.
Back to Top
Andersen View Drop Down
Groupie
Groupie


Joined: 07 July 2002
Location: Denmark
Status: Offline
Points: 152 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Andersen Quote  Post ReplyReply Direct Link To This Post Posted: 10 April 2006 at 11:58am
There may be some settings in the migration tool kit that handle this. I just ran it straight away following a wizard MySQL have made (a small flash movie). I just hoped someone could tell me for sure, cause I´m no good with databases. Infact, my knowlegde limits to opening and closing the database LOL
Greets
Andersen
Back to Top
Andersen View Drop Down
Groupie
Groupie


Joined: 07 July 2002
Location: Denmark
Status: Offline
Points: 152 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Andersen Quote  Post ReplyReply Direct Link To This Post Posted: 10 April 2006 at 11:38pm
I figured out how to delete those GetDate values, and got the forum converted to MySQL (with one error though):
 
`Forum konverter test _dbo`.`tblThread`
      ---------------------------------------
          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 '2005-03-16 12:18:27", 1, "83.88.144.229", 0, NULL),
                          (24322, 3202, 173, "Ja je' at line 968
          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 'Hvis den bliver ved med at vandre, sÃ¥ skal du kigge pÃ¥ hvormeget cirkulation d' at line 619
          34040 row(s) transfered.
-------
 
I ignored the error, as I have no idea what´s all about. Copied my whole WWF forum from my old server to my new one. Edited the database connection to now use the new MySQL database, and it looked really good. I entered the forum. Looked like all messages where there. Entered the admin, no problems at all (except for the moderator forum which is showen though it´s marked as hidden).
But!
When trying to enter a new message, I got this error:
 
Server Error in Forum Application
An error has occured while writing to the database.
Please contact the forum administrator.
Support Error Code:- err_mySQL_save_new_post_data
File Name:- new_post.asp
Error details:-
Microsoft OLE DB Provider for ODBC Drivers
[Microsoft][ODBC Driver Manager] Invalid string or buffer length
 
Now I have to ask.. Has anyone sucessfully converted WWF MsSQL to MySQL database and got the forum up running afterwards?
 
I´m feeling quite alone here, with tasks which should have been so easy, tools to covering everything, and yet I stumble into errors all the time.
First it was the upgrade tool from 7.9 to 8 - Ended up with some sort of error in the database, so forum now shows hidden forums. (Still havn´t been able to fix it though).
Second I ended up with errors trying to migrate MsSQL to MySQL - Some default value in MsSQL not supported by MySQL.
Third is this new one...Invalid string.. HMMM.. Whats next??
 
I´m not angry, it´s just that I for god knows what reason thought these things were taken care of by using whatever tools available. Now I sit here with a feeling, that perhaps I should never have upgraded to version 8, and afterwards never have got the idea of going to MySQL. It seems like upgrading and converting has not really been covered, and now I´m doing all the tests, which I normally wouldnt mind at all. I just would have liked to be informed first.
 
Well, life sucks as most probably would say. Dont try fix something which is already working. Stay with MsSQL server. Though it´s way overkill, it´s still working (except for the hidden forum thing).
 
Just before someone yells... NO, my tests going form MsSQL to MySQL is NOT on my public server, so no harm done to my almost 4 year old WebWiz Forum, yet Stern Smile
 
 
Greets
Andersen
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844 		Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post Posted: 11 April 2006 at 11:58am
Moving from SQL Server to mySQL is uncharted territory with Web Wiz Forums, so I'm afraid it will be a difficult trial and error procedure.

The problem looks to me that possibly on the database fields isn't set to the correct datatype and/or field length.

The only solution that I can think of is that make a clean mySQL web wiz forums database using the setup file provided then compare the two databases side by side, so you can spot any differences.

SQLyog is a good program for messing around inside mySQL databases.
Back to Top
starchou View Drop Down
Newbie
Newbie


Joined: 21 June 2006
Status: Offline
Points: 1 		Post Options Post Options   Thanks (0) Thanks(0)   Quote starchou Quote  Post ReplyReply Direct Link To This Post Posted: 21 June 2006 at 11:28pm
Hello :)
Sorry for my bad english.

Have youy succeeded to convert your MSSQL bdd to Mysql ?

Thanks
Back to Top
Andersen View Drop Down
Groupie
Groupie


Joined: 07 July 2002
Location: Denmark
Status: Offline
Points: 152 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Andersen Quote  Post ReplyReply Direct Link To This Post Posted: 22 June 2006 at 12:32pm
No, I gave up.. To much hassle with errors.
Greets
Andersen
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.