Updating MySQL Windows Server to new release version.

I spent a lot of time researching how to upgrade our MySQL windows server from version 5.5 to 5.7 there are indeed many resources spouting the best way to achieve it.

The biggest problem with this type of upgrade is the fact that the underlying system tables can (and do ) vary between the different versions.

We initially followed the advice and documentation from MySQL using the Windows Zip distribution option. This being an abject failure we made further searches to find more and more complicated solutions.

The failure was after copying the files over the existing files the MySQL service would not restart.

It all got very complicated so in the end we used another server to set up a test environment to ensure we could carry out the upgrade without major service interruption.

After various attempts we found the following procedure completed the upgrade successfully. This process would work for either moving servers of upgrading the same server.

Thanks to the thread at Serverfault  and Tom (his comment is buried way down list of solutions) for pointing us in the right direction (I’ve not got enough points on their website so so can’t comment directly, their loss!)

Using a command prompt take a backup of all databases, use this option as it also backs up the tables in mysql  database which includes the users and their security settings:  “mysqldump -u root -p –all-databases > dbdump.sql”

Stop MySQL service (if updating same server)

Use MySQL Installer to add new version of MySQL, this step will fail if you attempt it without stopping any running MySQL on the server. Use the same root password as currently set on the old version. this process will leave you with a running MySQL service.

Import the exported Databases using “mysql -u root -p < dbdump.sql”

We stopped and restarted the MySQL server after this import although not sure if this step is required. Use “Net Stop MySQL57” and then “Net Start MySQL57”

Our testing showed this imported all the databases and users with their associated privileges however the User table is effectively corrupt at this point.

This it is essential to run “mysql_upgrade -u root -p” on the new instance of MySQL. This runs through the tables and updates any inconsistencies in the table structures.

Thus in brief do the following

  • mysqldump -u root -p –all-databases > dbdump.sql
  • Net Stop MySQL
  • Run MySQL Installer use Add and select MySQL Server appropriate version, as part of the MySQL Installer routine add user root with existing password
  • mysql -u root -p < dbdump.sql
  • Restart MySQL
  • mysql_upgrade -u root -p

Doing this meant our production server was offline for no more than 10 minutes tops.

Hope this helps you, I’ve documented it so I’ll remember it for future releases.

Potential gotchas:

  • When you install the new MySQL using the installer it does not appear to add any statements to the server PATH variable thus mysql statements will fail unless you are on the MySQL bin directory (C:\Program Files\MySQL\MySQL Server 5.7\bin) or you’ve added this to the PATH statement.

 

 

 

 

 

 

 

Classic ASP and SQL Server 2008

I’ve spent a fair bit of time trying to get an ecommerce package written in classic ASP to run well using a network connection to a SQL Server 2008 Express edition. These are my findings which may help someone.

Don’t settle for the standard connection string you used to use.

The ADO connection string provided worked however tweaking this gave a significant increase in through put

Initially we had a choice of

strconn= “DRIVER={SQL Native Client}; Server=” & varServerIP & “; Database=” & varDatabaseName & “; UID=” & varUserName & “; PWD=” & varPassword

or

strconn= “DRIVER={SQL Server}; Server=” & varServerIP & “; Database=” & varDatabaseName & “; UID=” & varUserName & “; PWD=” & varPassword’

The first string failed with a connection error, being SQL Server 2008 you needed to use {SQL Server Native Client 10.0} as the driver name. Using this worked quite well however if you use varchar(max), nvarchar(max), varbinary(max), xml, udt or other large objects you will not get these fields showing up in the results as SQL Native client converts the cursor to a static one making these unavailable. (This Library Article refers – opens in new window)

The second worked although response times were atrocious

The Rules to follow

Don’t mix you metaphors!

You can use OLE DB provider terms in the connection string for an ADO connection and it will work most of the time however in the background the system is deciphering what you mean which in turn impacts how quickly it responds, where possible use the correct connection term. i.e. UID and pwd will work on an ADO connection User ID and password will work better. (this Library page refers to the correct terminology)

Be Explicit

If you are using a connection to a SQL server that is not on the same server and shared memory either reorder the client protocols in SQL Server or specify the connection protocol, the previous link details this however to highlight and emphasise I’ve reproduced the relevant bit here:

The complete syntax for the Address keyword is as follows:

[protocol:]Address[,port |pipepipename]

protocol can be tcp (TCP/IP), lpc (shared memory), or np (named pipes). For more information about protocols, see Choosing a Network Protocol.

If neither protocol nor the Network keyword is specified, SQL Server Native Client will use the protocol order specified in SQL Server Configuration Manager. [My emphasis]

port is the port to connect to, on the specified server. By default, SQL Server uses port 1433.

Thus as we were using a tcp connection we preceded the ip address with ‘;tcp:’

Again if you do not specify this the server has to test what connection is going to work, thus more time is consumed identifying the connection method required.

The correct provider for an ADO connection to a SQL Server 2008 is ‘SQLNCLI10’ use it!

Don’t waste your time with Network Packet size unless you are trying to squeeze every last bit of speed out of the system (in which case rewrite the application/web site in a modern code form!)

Define DataTypeCompatibility ADO works best if set to =80

Final connection string that worked:

strconn= “Provider=SQLNCLI10; Data Source=tcp:ipaddress,post_noSQLExpress;DataTypeCompatibility=80;MARS Connection=True; Initial Catalog=databasename;User ID=SQL username; Password=SQLUserpassword;”

The instance string (i.e. sqlexpress) appeared to assist in responsiveness thus I would suggest it is included every time.

If you are using pre-existing ASP code then very little will be gained from including the MARS connection=true as your code will be designed to close the connection on a single use basis. To gain advantage you may need to alter the code to not close the connection until all the sql interaction has completed.

 

Database Driven Web Solutions

There are many ways of delivering content for your web pages, with a database back end solution it is possible to ensure your web pages are dynamic, interactive and deliver up to date information at a lower cost than having to update static pages.

Using a database can significantly reduce costs for your web presence where a single page with a database back end can deliver hundreds or thousands of different pages that would otherwise need to be developed and maintained using static html pages.

Responding to changes in products or content can also be carried out significantly quicker with a single change on the database being reflected in all pages once the transaction is committed in the database.

The cost to implement a database back end need not be expensive either depending upon your target market and its potential size you could implement a database solution using MS Access or MySQL database which are very powerful and relatively low cost.

As way of example the web site we developed Anniversary Ideas is currently running with a MS Access database and MS SQL 2008 back end and using two files delivers over 1600 pages of different content which in turn generates tens of thousands of links for customers visiting the site, all of which are relevant to their specific needs.

If you would like to discuss your needs with an initial free consultation then please contact us either by phone on 0870 765 3436 or email us