MivaSQL to MySQL converter for MM5

[James Harrell]

This simple script reads your mivadata/merchdb.dat mivasql database definition file (ex: from Miva Merchant) and converts the mivasql database to mysql. The mysql target database should already exist, as well as a mysql user with proper permissions to connect, create, alter, index, insert, delete, etc.

WARNING: ALL DATA IN THE TARGET MYSQL DATABASE WILL BE LOST AND REPLACED BY DATA FROM THE SOURCE MIVASQL DATABASE. YOU HAVE BEEN WARNED! MAKE A BACKUP FIRST!

NOTE: As a simple failsafe, calling this script again (after you change your store merchdb.dat configuration file to point to MySQL) will NOT re-run the conversion. It will bail - saying you have already converted.

NOTE: Put store in maintenance mode before running this - and better yet also do the conversion in Miva Mia offline so no data changes during the conversion.

How to convert Miva Merchant 5 from MivaSQL to MySQL:

1. Copy your mivadata/merchdb.dat file to mivadata/mysqldb.dat. Edit the mysqldb.dat file to reflect your new MySQL configuration.

- merchdb.dat probable looks like this: mivasql|mivamerchant.dbf|||
- change mysqldb.dat, to replace the connection string with: mysql|mysql_database@mysql_server|mysql_username|m ysql_password|

2. Set your MivaVM/Empresa timeout to zero.

3. Place this script (mivasql2mysql.mvc) at the root of your httpdocs directory, and load it with a web browser. ex: http://www.yoursite.com/mivasql2mysql.mvc?go=1

4. Grab a cup of coffee.

5. Assuming all went well, all of your MivaSQL database table structures, indexes, and data have been transferred to MySQL. Now just change your mivadata/merchdb.dat file to run your store from the MySQL database:
- rename merchdb.dat to mivasqldb.dat as a backup
- rename mysqldb.dat to merchdb.dat

[ILoveHostasaurus]

Re: MivaSQL to MySQL converter for MM5

The timeout to zero implies it will be doing the conversion in one large step? Is there a rough idea of how long it takes per X products since the web server timeout will likely also have to be raised enough to let it not close the connection and terminate emprsea.

[James Harrell]

Re: MivaSQL to MySQL converter for MM5

Hi Dave,

Yes, the entire conversion runs in one page load. Please report back on your conversion time observations. ;)

If your Empresa timeout is zero, the Miva Empresa script will continue to run in the background even after 5 minutes (typical web browser timeout), and on some servers even after 10 minutes (typical web server timeout). I've seen scripts run much longer - it just depends if your server will actually kill the CGI program or if it will just disconnect from it and let it keep running.

Best,
James

[ids]

Re: MivaSQL to MySQL converter for MM5

Hi folks,

The MivaSQL to MySQL converter has arrived!

See attached source (.mv) and compiled (.mvc) files. The compiled version can be used by those without a compiler, though the target mysql username, database and password are hardcoded. If you use the binary version, run the conversion, then change the database name, username and password before running online. Hardcoded values for target mysql db:
- database: miva2my@localhost
- username: miva2my
- password: miva2my

Instructions:
| How to convert Miva Merchant 5 from MivaSQL to MySQL:
| ---------------------------------------------------------
| 1) Edit the configuration section below. Provide your MySQL database, username
| and password.
|
| 2) Compile (http://www.mivamerchant.com/products/compiler/)
|
| 3) Set your MivaVM/Empresa timeout to zero.
|
| 4) Place the compiled script (mivasql2mysql.mvc) at the root of your httpdocs
| directory, and load it with a web browser. ex:
| http://www.yoursite.com/mivasql2mysql.mvc
|
| 5) Grab a cup of coffee.
|
| 6) Assuming all went well, all of your MivaSQL database table structures,
| indexes, and data have been transferred to MySQL. Now just change your
| mivadata/merchdb.dat file to run your store from the MySQL database:
| - copy merchdb.dat to merchdb.mivasql.dat as a backup
| - merchdb.dat probable looks like this: mivasql|mivamerchant.dbf|||
| - edit merchdb.dat, replace the connection string with:
| mysql|mysql_username@mysql_database|mysql_username |mysql_password|


Enjoy!
James

hmmm....

Obviously I haven't done this yet. I probably won't need to so far. But, this doesn't seem like an operation just anyone should do. Who is the target for actually running the conversion? Just mentioning, to change the timeout; If an end user had access and could do it, and did it wrong, what would the implication be on that particular server? How would all the sites on that server be protected? Is there any danger of overloading the server?

Seems like the best advise would be for endusers get their host involved. But, that's why I'm asking.

[James Harrell]

Re: MivaSQL to MySQL converter for MM5

I would say the target audience of this utility would be someone technical enough to understand how to set a timeout, compile, set up mysql databases, database permissions, etc. I almost put unix commands (cp, vi, etc) in the instructions. :) End users should definitely get in touch with their host or developer.

I also recommend running this offline - perhaps on a development server or even Miva Mia, though Mia (and MySQL on a Win32 desktop) is notoriously slow.

Setting a timeout to zero doesn't have any security implication - but it could allow a rogue Miva Script to bog the server down for a long time significantly slowing performance.

Nothing from this conversion takes effect in the live store until step 6. So if you botch a conversion and don't change your Miva Merchant connection string, you're still running on the MivaSQL database.

Best,
James

[Gilbert]

Re: MivaSQL to MySQL converter for MM5

Just read, not technical at all. However do you recommend doing it ... in other word are you "dumping" MivaSQL ?

[James Harrell]

Re: MivaSQL to MySQL converter for MM5

Hi Gilbert,

No, we are not planning to discontinue MivaSQL. Many folks have asked for a way to change from MivaSQL to MySQL after running their store for a period of time. This utility simply provides a means of doing so.

Best,
James

[Eric]

Re: MivaSQL to MySQL converter for MM5

James,

This is great news and a big help for large stores that were setup on mivaSQL on mm5. MivaSQL is great for smaller stores but very large stores with a large number of products run best on the MySQL backend.

James,

Any news on the delete shopping baskets bug while inventory is enabled when running MySQL not mivaSQL. Their is a deadlock issue on a table lock that marianne was working on we have a large number of stores affected by this but we have customized some indexes to get around it for them until the patch comes out. I can PM you if you cant read the pm's back and forth with marianne.

Thanks!
Eric

[James Harrell]

Re: MivaSQL to MySQL converter for MM5

I think I have PM disabled - please email me (jharrell) a detailed description of the issue, this is the first I've heard of it.

Best,
James

[Eric]

Re: MivaSQL to MySQL converter for MM5

James,

No problem I emailed it to you

Thanks!
Eric

[Hostasaurus]

Re: MivaSQL to MySQL converter for MM5

Just ran this, but it was a small store so took about 2s.

Now I have a whole directory still full of .dbf and .mvx files though, how do we clean those up? Can Merchant5/ be emptied (excluding third party mod directories)?

Jen

[nixusr]

Re: MivaSQL to MySQL converter for MM5

I'm not quite sure what was meant by "1) Edit the configuration section below." There was nothing specifically just below that line. Are you refering to step 6?

Thanks

Hi folks,

The MivaSQL to MySQL converter has arrived!

See attached source (.mv) and compiled (.mvc) files. The compiled version can be used by those without a compiler, though the target mysql username, database and password are hardcoded. If you use the binary version, run the conversion, then change the database name, username and password before running online. Hardcoded values for target mysql db:
- database: miva2my@localhost
- username: miva2my
- password: miva2my

Instructions:
| How to convert Miva Merchant 5 from MivaSQL to MySQL:
| ---------------------------------------------------------
| 1) Edit the configuration section below. Provide your MySQL database, username
| and password.
|
| 2) Compile (http://www.mivamerchant.com/products/compiler/)
|
| 3) Set your MivaVM/Empresa timeout to zero.
|
| 4) Place the compiled script (mivasql2mysql.mvc) at the root of your httpdocs
| directory, and load it with a web browser. ex:
| http://www.yoursite.com/mivasql2mysql.mvc
|
| 5) Grab a cup of coffee.
|
| 6) Assuming all went well, all of your MivaSQL database table structures,
| indexes, and data have been transferred to MySQL. Now just change your
| mivadata/merchdb.dat file to run your store from the MySQL database:
| - copy merchdb.dat to merchdb.mivasql.dat as a backup
| - merchdb.dat probable looks like this: mivasql|mivamerchant.dbf|||
| - edit merchdb.dat, replace the connection string with:
| mysql|mysql_username@mysql_database|mysql_username |mysql_password|


Enjoy!
James

[James Harrell]

Re: MivaSQL to MySQL converter for MM5

Hi Dave,

Glad to hear it was relatively quick for a small store. I would expect stores with bloated affsess and baskets databases to take the longest, so it may be prudent to pack and clean those up for active stores. Also stores with thousands of products would probably take a bit longer.

Nixusr, the instructions were copied straight out of the script - so it's meant to imply "edit the configuration items at the top of the script". This means setting your mysql username, database and password before compiling the script.

Best,
James

[James Harrell]

Re: MivaSQL to MySQL converter for MM5

As far as the mivasql databases in place, yes, once you transfer, you can remove them. IF the 3rd party databases were in MivaSQL (rather than 4x style XBASE3), they were synchronized to MySQL as well. They would only be transferred IF the developer was using the same MivaSQL "global merchdb.dat" connection (ex: all SQL queries were against the database "Merchant"). Note that this is more or less a generic mivasql to MySQL transfer utility - the only thing that makes it merchant specific is that it reads merchdb.dat to get your mivasql connection information.

So IF they made their own mivasql or XBASE3 databases, the metadata for these tables wouldn't have been in mivamerchant.dbf (from merchdb.dat). Therefore they would not have been synchronized to MySQL and you must leave those dbf/dbt/mvx files in place. Contact your developers for specifics!

I would also leave the directory structures in place. Some modules export to the store directory, vendor modules probably export to their own data directories, etc.

Good luck!
James

[Gilbert]

Re: MivaSQL to MySQL converter for MM5

Hello James

I have run into trouble while using the attached compiled (.mvc) file with the Hard-coded

- database: miva2my@localhost
- username: miva2my
- password: miva2my

I am getting this error

# Opening mivasql... OK.
# Opening mysql... OOPS, BAILING!: Cannot open mysql:miva2my@localhost

Though I created from within Plesk the database as follow

- database: miva2my
- username: miva2my
- password: miva2my

(@localhost is not allowed + I had to use a workaround as Plesk does not allow the user-name and password to be the same. I have created the database under a different user-name and then change it to miva2my)

Best