11-03-14, 14:37 #1Registered User
- Join Date
- Nov 2014
Unanswered: Problems Migrating MS Access to MySQL Database
We recently developed a PHP/MySQL site/program that works fine when test data is entered through the site but when we migrated actual data from MS Access to MySQL using ODBC there were problems.
The MySQL and Access databases have quite different schemas. Looking at the migrated data in phpmyadmin, it appears that the data was imported (all the data is there) but when we try to view data through the site, the data displayed is selective (some data appears and some does not). For example, if I select "company" on a certain page from a dropdown, it correctly displays company information but if I navigate to another page that is supposed to display products associated with a subdivision and select "subdivision" from a dropdown, it either doesn't display any data at all or displays only one product even though there are several that should be displayed.
We're not quite sure how to troubleshoot this. I'm assuming that something went wrong in the migration but all the data seems to be there. Any thoughts, ideas or suggestions?
11-07-14, 16:29 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if you have imported the data, and the SCHEMA's are not the same then you haven't imported the data properly. if so then its hardly surprising that your data isn't showing up in the PHP application
as to how your resolve that I dunno
I guess first off comapre the two schema's, table by table, query by query, column by column. spot what is different and work out if that difference is because of the fundamental differences between MySQL and Access/JET (eg some column definitions and or datatypes or because your conversion process did somethign.
how did you migrate the data, what tool or mechanism did you use
Ive pumped data from Access to MySQL many, many times and to be honest I struggled at first but its not that difficult. I think I started using dbtools to copy data and the schema. but there are other toosl
if the schema is correctly defined then it shoudl be easy to port the data accross. main tripping point was IIRC decimal numbers and dates, also colum,ns containing ' or ", which needed escaping in Access.
but as to why it wokred with test data and doesnt' with live data then thats down to you and your debugging skills. based on what you have described I doubt anyone can really help you if you are passively accepting that 'the schemas are different'.
I take it you have turned on PHP error reporting, are using suitable debugging process (ie echoing values / data), examining the source as shown in the rendered web page.I'd rather be riding on the Tiger 800 or the Norton
11-17-14, 20:40 #3Registered User
- Join Date
- Nov 2014
Thanks for your advice.
I decided it would be more manageable to initially look at a small section of data only. We discovered that the data needed to be cleaned. There were text strings in fields that should not allow them and strange characters in certain fields. There was also an issue with blank spaces on certain data. To fix this, we had to strip all the blanks and then add them in again. Once the data was cleaned, the program was working as it should (at least with the small sample of data).