Category Archives: MySQL

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

PHP mySQL osCmax and converting databases

This week I started looking at the conversion of some older osCommerce databases and moving to osCmax. I’m almost on top of the templating but need to look at getting live data into the system to work with the templates.

This post is not about that but about the bits of code that I’ve worked on to get my converter working.

Connecting to two databases

Stackoverflow came to my aid again! The post on PHP connecting to two databases seemed fine but I did not read completely the first (or second) time and only after doing a decent read realised that there was the additional parameter for a new connection. Setting that worked fine.

There was also interesting reading on the logic and best practice around opening and closing database connections.

Over at http://net.tutsplus.com/ there are some best practices for php and mysql coding.

Altering a column in a table

I picked up a script from EdmondsCommerce (nice play on eCommerce!) and the script worked fine. I then liked the concept of the script posted by Arnan in the comments to the post but that did not work. I also noted the comment to use ‘IF NOT EXISTS’ except that is only valid for tables not for columns, at least not in my reading.

Adding a new column in a table

Following on from the alter script I created a new function and modified Arnan’s code to work and dealt with some potential issues with columns existing or not. Which led me to……

Logging Whats Happening

Using ‘echo’ to the screen is the common debug process for PHP code. But in this case I am building something a bit more robust and potentially big once I get to grips with over 100 tables in the databases.

I think an interactive script presenting information to the screen is good but not as a long scrolling list. I’d prefer to see a screen that sits and presents the current processing. In addition I want a log file to report on all actions and a separate log for the data line items just in case.

So I am now on the hunt for error logging and reporting and a great start is this post about a Logging class from http://www.redips.net/php/write-to-log-file/ that I have left as is and it works a treat.

Next I will need to come back to progress indicators like http://valums.com/ajax-upload/ and maybe here http://www.tutorialized.com/view/tutorial/PHP-GD-Progress-Bar-Demo/779 or http://davidwalsh.name/progress-bar-animated-mootools. The issue with this is how to update client side the progress occurring server side during a database table pass that in theory is either 0% or 100% and no feedback in between without splitting the query into segments and passing a client-side update. Needs more thought.

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

#/var/www/vhosts/your_domain_name/statistics/logs/error_log

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)


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

and create an entry like

<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>

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


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

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


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

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:

KEY BUFFER
ERROR 1317 (70100) at line 1: Query execution was interrupted

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.