Category Archives: MySQL MariaDB

Only on Linux. Windows is way too messy for MySQL.

Converting osCommerce to osCmax mySQL database structure

I am in the process of converting some older osCommerce based web sites to osCmax and the underlying database structure is different. The following is the step by step that I have worked through for one site.

Updated: 24th Aug 2011: Embedded updates for tables in the following as I worked through the conversion.

In preparing for this I have exported the structure only from the old site and from the current osCmax svn version that I have running on my system. I use Beyond Compare from Scooter Software for my file / folder comparison work and I have opened up the two structure files for comparison.

Note: the old osCommerce is based on an early 2.1 version updated and modified for unique requirements over the 10 years or so it has been in operation. It’s been a reluctance to even start contemplating this change that has led it to be so long.

Table: address_book:

The first two fields are in reverse order and the address_book_id is now a unique auto_increment field.
The address_book_id field in the old db will need to be dumped and the auto_increment value accepted.
entry_company_tax_id is a new field
entry_telephone is no longer used – checking my data they are all NULL so not required

Tables: address_book and address_book_new both exist in the old osCommerce database but not in osCmax. Both tables are populated so I’ll need to compare the data in the two.
Update: address_book_new is from an attempt to convert to creloaded some years ago, it is not an osCommerce table and can be ignored.

Table: anti_robotreg does not exist in osCmax and is empty in the old system. Dump this.

Tables: admin, etc. through to authors_info do not exist in the old system. These can be ignored.

Table: banners and banners_history no change.
Update: There is data in the new system that can be cleared.

Table: cache does not exist in the old.

Table: categories. Need to add new fields to old to align with new.
after sort_order
`categories_featured_until` date default NULL,
`categories_featured` tinyint(1) default ‘0’,
after last_modified add
`categories_hide_from_groups` varchar(255) NOT NULL default ‘@’,

In old remove the fields
`categories_customizable` tinyint(1) DEFAULT ‘1’,
`tpl_file` varchar(30) DEFAULT NULL,
these are not in the new

Table: categories_description
Add two new fields to old to align with new
`categories_description` text,
`categories_heading_title` varchar(64) default NULL,

Update: the categories column order changed to match and confirmed that data in new can be cleared.

Tables: configuration, configuration_group, counter, & counter_history are the same barring some minor formatting and default settings that should not impact.

Update: configuration table has a huge difference in the number of entries. The data content needs to be compared and merged rather than overwritten.

Table: countries
Add a new field to old to align with new
`active` tinyint(3) unsigned default ‘1’,

Table: currencies, no change.

Table: customers
Add new field after customers_id
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,

Change `customers_default_address_id` int(5) to `customers_default_address_id` int(11)
Add new fields after customers_newsletter
`customers_newsletter_type` varchar(32) NOT NULL default ‘html’,
`guest_account` tinyint(1) NOT NULL default ‘0’,
`customers_login` varchar(96) default NULL,
`customers_group_name` varchar(27) NOT NULL default ‘Retail’,
`customers_group_id` int(11) NOT NULL default ‘0’,
`customers_group_ra` enum(‘0′,’1’) NOT NULL default ‘0’,
`customers_payment_allowed` varchar(255) NOT NULL,
`customers_shipment_allowed` varchar(255) NOT NULL,
`customers_paypal_payerid` varchar(20) default NULL,
`customers_paypal_ec` tinyint(1) unsigned NOT NULL default ‘0’,

Table: customers_basket no change.

Table: customers_basket_attributes
add new field after products_options_value_id
`products_options_value_text` varchar(32) default NULL,

Tables: customers_groups is new customers_info no change.

Table customers_new is in old but not new. Need to validate data in this and customers prior to export.

Tables: customers_notes and wishlist related, extra fields, and feeds are only in new.

Table: geozones no change, new tables for google etc only in new.

Table: languages
Add new field after sort_order
`meta_keywords` varchar(255) default NULL,

Table: manufacturers
Add new fields after manufacturers_image
`manufacturers_featured_until` date default NULL,
`manufacturers_featured` tinyint(1) default ‘0’,
`manufacturer_featured_until` date default NULL,
`manufacturer_featured` tinyint(1) default ‘0’,

Table: manufacturers_info
Add new field after manufacturers_url
`manufacturers_description` text,

Table: orders
Add new field after customers_address_format_id
`customers_dummy_account` tinyint(3) unsigned NOT NULL,

Remove `delivery_telephone` varchar(32) DEFAULT NULL, from old table as not in new data is all null.
Remove `billing_telephone` varchar(32) DEFAULT NULL,

Old field `cc_ccv` varchar(3) DEFAULT NULL COMMENT ‘CCV 3 Digits’, is not in new but seems like a necessary field. Need to include for the moment and dump later if not required. Some values are present.

Old field `payment_method` varchar(32) NOT NULL DEFAULT ”, is changed to
`payment_method` varchar(255) NOT NULL DEFAULT ”
Not sure how this interacts with payment processes. Will retain field as payment_method_old leaving the new field as payment_method. Refer to table orders_pay_methods which is also retained and linked to this field but is not in the new structure.

Three fields in old can be dropped as they are all empty.
`account_name` varchar(32) NOT NULL DEFAULT ”,
`account_number` varchar(20) DEFAULT NULL,
`po_number` varchar(12) DEFAULT NULL,

New fields to add after currency_value
`paypal_ipn_id` int(11) NOT NULL default ‘0’,
`fedex_tracking` varchar(20) NOT NULL,
`purchased_without_account` tinyint(1) unsigned NOT NULL default ‘0’,
`shipping_tax` decimal(7,4) NOT NULL default ‘0.0000’,
`shipping_module` varchar(255) default NULL,

Table: orders_pay_methods not in new. Retain in new until determining how this works.

Table: orders_premade_comments not in old.

Table: orders_products
Add new field after `products_model`
`products_code` varchar(40) NOT NULL,

Add new field after `products_quantity`
`products_stock_attributes` varchar(255) default NULL,

Table: orders_products_attributes
Add new field after `products_options_values`
`code_suffix` varchar(16) NOT NULL,

Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,

Table: orders_products_download no change.

Table: orders_ship_methods not in new. appears to relate to new field in orders regarding shipping_module. Retain and test post change.

Table: orders_status
Add new fields after `orders_status_name`
`public_flag` int(11) default ‘1’,
`downloads_flag` int(11) default ‘0’,

Tables: orders_status_history and orders_total no change

Table: packaging is new

Table: paypalipn_txn appears the same / similar to paypal_ipn. Will need to look at data changes.

There are many fields in old and not in new and vice-versa. Trying to match the data will be time-consuming. Need to assess if this needs to be done or not. Retain old table as itself for the time being.

Table: pm_configuration is new.

Table: products is also complex in the changes. Need to review.

Table: products_attributes
Add new fields after `options_values_id`
`code_suffix` varchar(16) NOT NULL,
`suffix_sort_order` int(4) NOT NULL default ‘0’,
and Add new fields after `price_prefix`
`weight_prefix` char(1) NOT NULL,
`options_values_weight` decimal(6,3) NOT NULL default ‘0.000’,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,

Table: products_attributes_download no change

Table: products_description
Add new field after `products_name`
`products_short` text,

Add new fields after `products_description` text,
`tab1` text,
`tab2` text,
`tab3` text,
`tab4` text,
`tab5` text,
`tab6` text,

Table: products_groups is new

Table: products_notifications no change

Table: products_options

Add new fields after `products_options_name`
`products_options_track_stock` tinyint(4) NOT NULL default ‘0’,
`products_options_type` int(5) NOT NULL default ‘0’,
`products_options_length` smallint(2) NOT NULL default ’32’,
`products_options_comment` varchar(32) default NULL,
`products_options_sort_order` int(10) unsigned NOT NULL default ‘0’,

Table: products_options_products is not in new.

Table: products_options_description is new

Tables: products_options_values, products_options_values_to_products_options are the same

Tables: products_price_break & products_stock are new

Table: products_to_categories is the same

Tables: products_to_discount_categories, products_xsell, & quick_links are new

Table: reviews
Add new field after `reviews_rating`
`approved` tinyint(3) unsigned default ‘0’,

Tables: scart, all search*, shipping_manifest and slideshow are new.

Table: sessions no change.

Table: specials
Add new field after `status`
`customers_group_id` int(11) NOT NULL default ‘0’,

Table: suppliers is custom to the old system. links in products and currencies need to be managed. relates to proposed module to manage outbound purchase orders and product costing.

Table: specials_retail_prices is new

Tables: tax_class, tax_rates no change

Table: theme_configuration is new

Table: tep_meta_tags is not in new

Tables: topics* and usu_cache are new

Table: whos_online
Add new fields after `ip_address`
`hostname` varchar(255) NOT NULL,
`country_code` varchar(2) NOT NULL,
`country_name` varchar(64) NOT NULL,
`region_name` varchar(64) NOT NULL,
`city` varchar(64) NOT NULL,
`latitude` float NOT NULL,
`longitude` float NOT NULL,

Add new fields after `last_page_url` text NOT NULL,
`http_referer` varchar(255) NOT NULL,
`user_agent` varchar(255) NOT NULL,

Tables: zones* are no change

Next is to build a script to make the structure changes and then extend it to convert the data. I need this in order to do some trial runs before a live cut-over. It will be useful for the creLoaded stores that I need to do as well.

Moving Moodle to a Plesk environment

I am migrating a moodle install from an older system into a new hosted environment with Plesk in the background.

The setup of the Plesk account is straight forward. While the transfer was simple enough using SFTP via FileZilla out of one Linux system to my workstation and off to the new server.

The first obvious task was to modify the config.php file for moodle with its new information.

The less obvious task was the error accessing the moodledata directory which for preference sits outside the Plesk default httpdocs directory.

If you are getting an error about the $CFG->dataroot being invalid or similar it will be the open_basedir setting.

Check the logs for Apache errors

[bash]
#/var/www/vhosts/your_domain_name/statistics/logs/error_log
[/bash]

to modify the default Apache settings in a Plesk world create a file called vhost.conf in the conf directory I use mcedit as my favourite Linux command line editor (apt-get install mc) (Midnight Commander tools)

[bash]

#mcedit /var/www/vhosts/your_domain_name/conf/vhost.conf

[/bash]

and create an entry like

[xml]
<Directory /var/www/vhosts/your_domain_name/httpdocs/>
php_admin_value open_basedir "/var/www/vhosts/your_domain_name/httpdocs:/var/www/vhosts/your_domain_name/moodledata"
</Directory>
[/xml]

I referenced Aaron Gadberry’s blog for the above but stumbled when it came to restarting the Apache service

[bash]

#/usr/local/psa/admin/bin/websrvmng -a

[/bash]

is no longer valid. Given that Aaron wrote his info in 2006  and 5 years later Plesk is version 10 for my server it is not surprising there is a change. The correct command for restarting / reconfiguring a single vhost is

[bash]

#/usr/local/psa/admin/bin/httpdmng –reconfigure-domain your_domain_name

[/bash]

The final part of this conversion was to modify the Moodle MySQL database to a UTF8 format.

I have used phpmyadmin for years and most Linux servers including Plesk offer it for mySQL admin.

It’s a simple thing to change the database. Just select the database in phpmyadmin and select the Operations tab. Down the bottom is the current collation in a select list. Change the selection to utf8_unicode_ci which is at the very bottom of the list and click on Go.

MySQL slowing down over time

I’ve got a mySQL server based in a Debian distro with updates etc, in a virtual (vmware) server.

Over time the slow queries log starts to get entries and it seems that the more it gets the slower mySQL is responding to all queries.

Shutdown and restart the server and it’s now 5 days without a single slow query entry. (of course, I could have buggered up the slow query logging, but probably not)

I have started trawling to find a possible solution, the now unmaintained blog Hack mySQL has this reporting process that I might want to come back to.

Yes, I came back to this and you may need to fiddle with the mysqld.sock location but otherwise this script works fine. Also use the prompt for your username and password if it is not root. The script appears to fall over with the following error:
[php]KEY BUFFER
ERROR 1317 (70100) at line 1: Query execution was interrupted
[/php]
but in fact it is just thinking. Wait 5 minutes or so (I didnot time it) and it will complete.

The information about your config and resources may just point you at some setting to improve the performance.

However for me I have not used the tuning script to makes changes as yet.

Earlier in the week I was checking the DNS settings as a result of a thread (that I have lost the link to) which led me to read both the basic notes at MySQL on DNS and this entry at HackMySQL

Now I am not copying the information from those sites verbatim, but suffice to say that your MySQL server and the application server(s) making requests must all see each other correctly by hostname and ip address. If your hosts file (local) or DNS server (local or remote) have incorrect details it may result in regular misdirection of the responses from the database.

In my case I found a small mistake in the hosts file on the mySQL server that was misdirecting responses to the wrong server. i.e the app server was requesting but being found with a different location on reverse lookup. I made that change last weekend and this week we have 0 entries in the slow log and no user issues. (At least none we were told about).

On that basis I think it might be case closed.

So if your MySQL is slowing for no apparent reason check and double-check the name resolution forward and backward.

MySQL & PHP Upgrade CentOS server

I now appear to have a new Linux VPS running with CentOS and Plesk. I wont go into the stupid details of why this has taken 2 weeks to provision, or another 2 days for me to ‘fix’ the ip addresses etc, only to find that CentOS has an older version of PHP at 5.1 and MySQL needing to be upgraded to match with PHP 5.3 when I tried to upgrade.

I referenced Christian Montoya’s blog for the update for PHP but found that I had a couple of issues.

The first was that when I ran the yum command for the PHP upgrade it complained about the MySQL support / version.

yum update php

was changed to include MySQL

yum update php mysql

Which did what I needed.

Running the

php -v

Advised that the updated 5.3 version was installed but I then found that MySQL was not running.

A quick wade around discovered that /var/log/mysqld.log that there was another error preventing MySQL from starting. This was the message:

[ERROR] /usr/libexec/mysqld: unknown option '--skip-bdb'

Editing the /etc/my.cnf file to comment out this option in both the main section and the mysql_safe section of the file fixed this issue and allowed MySQL to start.

Next I had in the mysql log messages like:

[ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. 
Created with MySQL 50077, now running 50156. 
Please use mysql_upgrade to fix this error.

So trying to use mysql_upgrade as a command by itself fails for user root@local host

]# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when 
trying to connect
FATAL ERROR: Upgrade failed

Using the correct username is as simple as 

#mysqlupgrade -u username -p  [Enter]

Which will prompt you for the password and then run the upgrade with the authenticated username.

And that, I hope, completes the backend upgrade so I can run SugarCRM on this server.