| Author |
 Topic  |
|
xr4i
Starting Member
3 Posts |
 Posted - 06 February 2007 : 14:37:53
|
Hi All,
My 20Mb Access 2000 .mdb is crashing my hosts IIS (memory leaks?) about 3 times a month now, or so they tel me..., and they are not happy. They are not happy? jees, my users are screaming for blood!!
So, time for me to have a go. I've got MySQL running nicely on my XP PC and am using HeidiSQL to build the database back up from scratch - http://www.heidisql.com/
I've used the http://www.convert-in.com/acc2sql.htm free demo download to recreate the whole db structure inc the first five lines and it dropped it straight onto the MySQL server on XP 
Having some issues thought with Heidi not importing all rows e.g. forum_members table has 612 rows, Heidi only imported 61 (10%) 
will keep all posted and I'll be back for help!
Jonathan< |
Edited by - xr4i on 06 February 2007 15:07:46 |
 |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 February 2007 : 15:06:53
|
Hmmm 20M not exactly huge, and as for Memory Leak - I'm not aware of any leaks in Jet 4 (Access 2k is a desktop application that uses Jet as the DBEngine)
Possibly your db is growing/has messy pages and a compact/repair will bring it back in line.
But, enjoy your migration anyway! Let us know how you get along< |
|
|
|
xr4i
Starting Member
3 Posts |
Posted - 06 February 2007 : 15:09:43
|
quote:
Originally posted by pdrg
Possibly your db is growing/has messy pages and a compact/repair will bring it back in line.
Tried that, no joy  it is nearly four years old though 
Cheers for the wishes, I'm editing my above post rather than keep adding new ones. < |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 06 February 2007 : 15:33:22
|
| XR4i - that's just confusing! Post to a new topic or append this one to keep a dialogue going! Reason why you only hit 10% I guess is a script timeout on the server, by the way< |
|
|
|
xr4i
Starting Member
3 Posts |
Posted - 06 February 2007 : 17:19:06
|
Oops, sorry - don't want to cause confusion 
Seing as this thread is 2 plus years old, I thought i'd try and bring it up to date....
I now have my Windows XP Pro 1.8MHz 256Mb PC running the following:
Apache 2.2.0
PHP 5.2.0
MySQL 5.0.27-community-nt
I'm too tired now to actualy do any database conversion work...I was going to do all this on Linux but it's just too **** complicated for a Windows chap like me < |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 07 February 2007 : 09:37:23
|
| No worries, stay in the MS camp, life's easier sometimes not having to recompile your own operating system when it updates ;-)< |
|
|
|
sibi
Starting Member
33 Posts |
Posted - 14 February 2007 : 07:25:32
|
Hi
The simplest option is to use MySql Migration toolkit . It gives a nice wizard interface for the migration. U can download from http://dev.mysql.com/downloads/gui-tools/5.0.html
Note: i had the mysql migration toolkit running on the server in which i ran mysql server. This might not be a necessary requirement. but i have not checked
Steps
1) On welcome page click next, on next page choose "direct migration" might be selected by defaut. Click next
2) and select source database as "MS Access". In connection parameters browse to the acess db. keep username and password as blank (my access db was not password protected. Have not checked iwth password protected DB. but u can always remove the password protection and migrate). click next
3) choose target database as mysql. Give the hostname of ur mysql database and its username and password (i gave the root username and password) that has permissins to create tables add data etc. Click next
4) In next page the connection to server must execute successfully. click next
5) select the source schema. ie name of ur DB in access. the db will be displayed as an icon . click on the db icon to select and click next
6) click next in all folowing pages and u will have the db migrated successfully.
it works like a breeze
regards
sibi< |
Edited by - sibi on 14 February 2007 07:29:02 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 February 2007 : 07:43:47
|
I am sorry to inform you, but that is not a valid migration strategy. The migration wizard fails to create the proper table structure, which is nothing to be surprised about, since the Access table structure has no info on indexes, except for the obvious primary key ones. As such, the indexing info, essencial for the proper performance of a MySQL DB, will not be created.
We keep on saying how the migration needs to be done. If you choose to do it differently, do it, but please don't come here stating that a procedure that apparently, but apparently only works, is the solution to all migration problems.
If there was a simple migration procedure, don't you think we would have posted about it?
Also, the migration wizard fails miserably quite often, not importing all the records it should, and failing to properly migrate unicode characters. Will you be here to support people, when they use your migration strategy and it fails?
I'm a bit tired of everyone second guessing the advice given here about how to migrate a Snitz Db to MySQL. I would guess that I'm one of people here who has done more migrations. I can't advise a single method to do it, since the available tools fail one time or another. Anyway, the first step is always to create a proper Snitz MySQL table structure, using setup.asp, or recreating the table structure from a good, existing MySQL Db, with a tool such as SQLYog (or a phpmyadmin dump). Once that is done, you can use every method that moves just the data into the DB. The choice of methods varies and sometimes the only solution is to have two DBs, a good one created in the way I just desccribed, and another one created through an Access export, or the migration wizard. This second one has a bad structure and good data (usually, at least), but you can just get the data and export it to the one that has the good data...
Giving ample reason to my dislike of MySQL, I have not found a single strategy that works all the time. The same thing cannot be said, for example, for SQL Server moves, where EM or Management Studio have never failed me.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
Posted - 14 February 2007 : 15:05:56
|
quote:
The choice of methods varies and sometimes the only solution is to have two DBs, a good one created in the way I just desccribed, and another one created through an Access export, or the migration wizard. This second one has a bad structure and good data (usually, at least), but you can just get the data and export it to the one that has the good data...
I have done several migration, of base snitz and modded snitz and that is the best way. Forum HAS TO CREATE TABLES AND INDEXES or mysql doesn't work properly. You can see it in search function that stop with out of time or in profile showing that is slowing.< |
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum |
|
|
|
markuser
Starting Member
1 Posts |
Posted - 30 June 2007 : 08:54:30
|
Hello!
I have to convert my database from Access to MySQL. I have read all this text above, and I must say, I think I will not manage it . I never had to do with MySQL before. So, is there anyone, who could do this for me? Of course against payment. The Access-Database has a size of 80 MB. The forum is a V.3.4.06 (just upgraded from 3.3.05).
Thank you very much!
Markus (from Austria)< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
thelodger
Junior Member
United Kingdom
296 Posts |
Posted - 25 October 2007 : 16:29:03
|
| Ruirib is it possible to convert ms sql to mysql? I have had a forum running on a site that I am leaving and want to move the forum to a site I am keeping, the site I am keeping is mysql and the one I am leaving ms sql, I have created the new forum and its in use, the old forum is just sat waiting to have the posts moved over.< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 October 2007 : 16:45:57
|
It's always possible, it's just a matter of the amount of work required. Is this a modded forum? If so, how many mods? And if you have mods, do you have the dbs files for them?
< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
thelodger
Junior Member
United Kingdom
296 Posts |
Posted - 25 October 2007 : 18:10:46
|
| Yeah I have quite a few mods installed, all from snitzbits more installed in the new forum (mysql)than the old one (ms sql) as it was easier to do from a new build and without the forum being used so all was set up before I opened the new (mysql) forum, so yes I have all the dbs files and all the backup files for each mod before the next one was done all saved in the right folders on my PC.< |
Edited by - thelodger on 25 October 2007 18:11:24 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 25 October 2007 : 20:15:25
|
Well if you do have the dbs files, then transferring the data is just a matter of creating a blank DB, seting it up using setup.asp and installing all the dbs files. Once you do that, you will need to transfer the data from the SQL Server DB. There are several ways to do this, depending on the tools you have. An easy path would be to create an ODBC data source for the SQL server DB and use it to import all the data to an Access Db. You could then use the MySQL migration tool to export the data to another Db (not the one created with setup.asp), because the migration tool will create the table structures too (though incorrectly). Anyway, it will transfer the data and then you can use a tool such as SQLYog to move the data to the destination DB from the DB created by the MySQL migration tool.
It's not direct, but should work.
Buying the pro version of SQLYog would allow you to import the data from the SQL Server Db to a MySQL Db (not the final one, again)... and then use SQLYog to copy just the data from that DB to the one created by setup.asp.
Sounds a bit complex? Yeah, I guess. One of the reasons for my preference for SQL Server.< |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
How I converted my Access DB to mySQL - A guide
