MargateSteve Posted January 10, 2011 Share Posted January 10, 2011 The site that I am working on has a MySQL4.0 database although they do have MySQL5.0 ones available (presumably 4.0 was the most recent when the site was set up) and am considering whether it is worth upgrading. Personally I think that with the nature of the data on the site, just the option to create VIEWS is probably enough but I am looking for some advice before I jump in.... The account that the site is hosted on (1&1) is at the limit of available databases so I will need to drop the current one to create a new one. I assume that this will be as simple as exporting the SQL of the whole site, dropping the database, creating the new one, importing the SQL back in and then finding any references to the old database in the site and updating them with the new details (there are a couple of 3rd party programs being used in the site at the moment). Is there any danger of there being anything in the exported 4.0 SQL that will not import correctly into 5.0? I would assume not but as the site is live, I cannot run any risk whatsoever that anything can go wrong. Along a similar vein, would there be any statements that I may have used in the site that will not work correctly running from MySQL 5.0? I know that is quite a broad question but there is nothing more than very basic code being used and I would only imagine a problem if a previously commonly used statements underwent a name changed or was superceded by a new statement. Once again, unlikely I know, but I need to cover my back! Finally, as I said, VIEWS are the main reason for wanting to change but are there any other pro's and con's that may sway me either way. I have seen snippets of code that suggest that you can aggregate alias's but as I have never made it work, maybe this is another 5.0 feature? If it was a case that upgrading would be a hassle far greater than the benefits I would probably leave it for now. Thanks in advance Steve Quote Link to comment Share on other sites More sharing options...
MacRedrum Posted January 10, 2011 Share Posted January 10, 2011 hmm, what I would do, is make a copy of the database, then install it on some other server/place and test if it works. Even some temporary desktop computer will do fine in my opinion. Mysql does have some update scripts that should work with migrating the db. Though you need to run them manually to see the errors it produce, as I had to run some update queries manually on my database when I did a upgrade. Quote Link to comment Share on other sites More sharing options...
jdavidbakr Posted January 10, 2011 Share Posted January 10, 2011 You'll also want to test your back-end code to make sure your queries work correctly too. I think it was when I migrated a database from 4 to 5 that I got hammered with a whole bunch of incorrectly formatted sql statements that were using a list of tables and then a left join, such as: select * from table_1, table_2 left join table_3 on (table_3.id = table_1.id) ... that worked in the old version but not in the new. Quote Link to comment Share on other sites More sharing options...
MargateSteve Posted January 10, 2011 Author Share Posted January 10, 2011 In hindsight, I do have MySQL 5.1 available on my PC through WAMP. I could copy the site to Localhost and test it all out that way. Thanks Steve Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.