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.

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.

Automysql Backup deleting old files

I was looking for an update to Automysqlbackup that would allow me to delete older files and keep down the disk space being used. The daily and weekly directories are rotated in version 2.5 that I have used for the last couple of years, but the monthly are retained in perpetuity.

Sourceforge has version 2.5.1-01 as the latest from 2010 which I grabbed to see what has changed.

While Mark over at his MySQL Fanboy site has also made some updates to Automysqlbackup to add some deletion options. The download link in his post is broken, but you can find his updated Automysqlbackup script in his Files area.

Now, just to be sure of what I was working with I ran up BeyondCompare (the excellent comparison tool that I have used for years) to see what Mark had done to Automysqlbackup and what the new version held.

To start Marks version is based from the July 2010 2.5.1-01 Sourceforge release and he has updated the version numbers as he added features, currently 2.5.3 released Jan 2011. Except in the script proper at line 511 where it is still v2.5.2. I think it could be added as a constant in the settings at the top of the file which would ensure consistency.

There are a few other changes he has made including directory chmod’ing of 700 to the directories that it makes. Again I’d prefer to see this, if it exists, not to be hard-coded but driven by a setting that can be modded at the top of the file like:  PERMISSIONS= ‘0700’  or 0770 as I would prefer.

Now the bit I was interested in was the retention of older files.  The new version from Sourceforge has added monthly rotation settings which are hard coded into 2.5.1-01 as 5 months.  Marks 2.5.3 provides two new constants that allow for the selection of periods other than 5 months or weeks.

In the end Marks changes dont really help me, so I am sticking with the 2.5.1-01 version for the moment. However some of the features he has added may be useful in the future so the time I spent today was not wasted.

PHPMYAdmin on SME Server 8.0 beta 5

Adding an ibay for PHPMyAdmin to manage the SQL Db’s on the server.

The install is fine but an error message in the PHPMyadmin screen tells me that mcrypt is required.

So I found the appropriate page in the contribs Wiki for SME Server but I get a dependency error on libmycrypt.so.4

I located a suitable rpm for CentOS 5 and having installed it I could then complete the process as per the Wiki for SME Server.

In the Wiki page for PHP at the bottom is the section for SME 8.0 beta 5 and you need to insert a new step prior to step 4.

yum localinstall libmcrypt-2.5.8-4.el5.centos.i386.rpm

After this all the rest of the process works as it should.

MySQL root password fails or forgotten

I had setup a new Ubuntu server with MySQL and during the install I was sure I knew the password I set for the mysql root user.

So I then setup webmin and went to access mysqladmin and it failed. I then tried at the command line and it failed.

So I am stuck with a running but inaccessible mysql server.

This thread at Trap17 was the one that helped but it was missing a couple of key points.

I’ll repeat it here in case the link goes away in the future. If you are using Ubuntu without a root password you will need to prefix some of the following with the ‘sudo’ command so that they will run ok.

First stop mysql

/etc/init.d/mysql stop

Then run this command that basically says “run mysql without password control”

mysqld_safe –skip-grant-tables &

With my server this resulted in not returning to the command prompt. Press “Enter” to get the command prompt again.
Then open up mysql for a mysql prompt

mysql -u root -p

Which should result in a mysql> command prompt

From the original post this next command appears redundant on Ubuntu so skip this one. mysql client – mysql -u root

The next command is done at the mysql prompt and again I differ from the original post

use mysql;

Note the semi-colon to complete the command. If you do copy and paste the two lines successfully then skipping the semi-colon on the first line is ok, but I like most people will initially try to do it a line at a time so I think it is clearer to execute as a sequence.

The next command sets up your new password and you need to edit this line with your new password and your relevant user account name.

update user set password=PASSWORD(”NEW-ROOT-PASSWORD”) where User=’root’;

Ok, so now you possibly have an error. Depending on the way you held your lips you may or may not have the correct syntax copied to your command line. I am not sure if this is consistent with all browsers and all telnet clients but I use PuTTy and when I copy/paste the above from the original thread I get this

update user set password=PASSWORD(.NEW-ROOT-PASSWORD.) where User=.root.;

Note that the quote marks in the command were converted to a ‘.’ (period) character and when I hit the enter key I got an error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

So if you check your syntax and make sure you have the quotes correctly placed then it will work fine.

Finish up by closing the mysql command line with ‘exit’ and stop / start mysql to get it to use passwords again.

/etc/init.d/mysql stop

/etc/init.d/mysql start

And that should get your account running again.