Thursday, May 30, 2013

Size per Table information with MySQL

Knowing the size of your data is of course helpful.  The tools have become easier over the years and different versions of MySQL but it is something you should be checking regardless of your MySQL version.

If you are running an old version of MySQL (before information_schema) then you can still gather this data by using "Show table status and add the Data_length to the index_length." The information_schema makes this much easier but you are free to use them whenever you like.

Take advantage of the pager command to gather just the information you are after.
[world]> pager egrep -h "Data_length|Index_length"
PAGER set to 'egrep -h "Data_length|Index_length"'

Use the show table status command to gather the related information:
[world]> show table status like 'City'\G
Data_length: 409600
Index_length: 131072
1 row in set (0.00 sec)

Reset the pager:
[world]> pager
Default pager wasn't set, using stdout.
Table Size = Data_length + Index_length
[world]> select 409600 + 131072 as Table_Size;
+------------+
| Table_Size |
+------------+
| 540672 |
+------------+

The same information is available via the information_schema:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH) AS size,SUM(INDEX_LENGTH) AS index_size
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN ('world') AND TABLE_NAME IN ('City') AND ENGINE IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME

TABLE_SCHEMA: world
TABLE_NAME: City
ENGINE: InnoDB
size: 540672
index_size: 131072
1 row in set (0.00 sec)


The point, pay attention and know your data. 

Wednesday, May 29, 2013

MySQL 4.1 -- Please Upgrade

A MySQL DBA is often asked to help with various versions of MySQL.

SELECT VERSION();
+----------------+
| VERSION() |
+----------------+
| 4.1.18-classic |
+----------------+
But I beg you all... Evaluate your options and upgrade. 

MySQL has made numerous SECURITY issues updates let alone performance updates. Check your version of MySQL. If it is anything below 5.5  or at a big stretch 5.1.69 PLEASE UPGRADE. 

While you might consider your database "working" and available for upgrade when it breaks.... Will it require some work... Yes. Will it save you in the long run.. Yes . Will you get more out of your system... Yes...  Could you take advantage of fixed bugs... Yes.   Would you rather be "broken" by a security vulnerability?  

Stop and think for a moment about what you will say to the CEO when the CEO asks why did you get hacked ?

Take a look at your system against the known security vulnerabilities:

4.1

Take advantage of all the new versions available:





Saturday, May 18, 2013

MySQL Users :: Grants :: mysql_config_editor :: Security

Secure access to the database is likely priority number one for any database administrator. If it is not then you need to seriously look into why it is not.

General guidelines via the manual are already available:

One of the primary issues with security in MySQL is of course the permissions that you give users.
These are a few simply guidelines.

First keep "super user" or "root" accounts to a minimum. A user with full access or "GRANT ALL" will still have access when you have reached your max connections. So the last thing you would want is a program to be executing commands with a user with full access.

Keep in mind what types of accounts you are creating. You can limit a user to MAX QUERIES, MAX UPDATES, MAX CONNECTIONS and MAX USER CONNECTIONS per HOUR.

Keep in mind of the network environment your users are connecting from. If users are going to be using DHCP network addresses within the same subnet you would just be creating more work for yourself if you limited them to a single IP. You can still limit them to the subnet though with a wildcard. For example '192.168.0.2' versus '192.168.0.%'

Stay away from entire wildcard access for host and users.



> CREATE USER ''@'192.168.0.56' ;
Query OK, 0 rows affected (0.02 sec)

> show grants for ''@'192.168.0.56';
+-----------------------------------------+
| Grants for @192.168.0.56                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO ''@'192.168.0.56' |
+-----------------------------------------+



This would leave you wide open for anyone from 192.168.0.56 and is not a smart secure thing to do.
It could also violate other accounts from 192.168.0.56 because MySQL checks host first and username second.


> GRANT SELECT ON test.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY 'somepassword';

> show grants for 'exampleuser'@'192.168.0.%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@192.168.0.%                                                                                   |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'192.168.0.%' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT ON `test`.* TO 'exampleuser'@'192.168.0.%'                                                              |
+----------------------------------------------------------------------------------------------------------------------+


This will allow selects only for exampleuser from '192.168.0.%'. You must also keep in mind that if exampleuser is connecting from LOCAL HOST that the system will likely user localhost first before the 192.168.0.% subnet address unless the user used the subnet address the host to connect to.

This means that you can create one user and password with different privileges per host.


> SHOW GRANTS FOR 'exampleuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for exampleuser@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'exampleuser'@'localhost' IDENTIFIED BY PASSWORD '*DAABDB4081CCE333168409A6DB119E18D8EAA073' |
| GRANT SELECT, UPDATE, DELETE ON `test`.* TO 'exampleuser'@'localhost'                                              |
+--------------------------------------------------------------------------------------------------------------------+


Try your best to not use the --password=<password> option via the mysql client. You can use -p to prompt for a password.

You also have the option with MySQL 5.6 to use the MySQL Configuration Utility.


# mysql_config_editor set  --login-path=local --host=localhost --user=root --password
Enter password:




# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost


# mysql 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# mysql  --login-path=local
Welcome to the MySQL monitor.

# mysql  --login-path=local -e 'SELECT NOW()';


You do have options to name different paths like local or remote and etc as well. So you can encrypt more than one access user account in your ~/.mylogin.cnf file that is created once you use the set command.

If you have shell scripts that use the mysql client and likely then have passwords in the scripts updating them to use the "--login-path=" is a more secure way to go.


Of course when you no longer need a user... Drop the user.


> DROP USER 'exampleuser'@'localhost';



A Smaller IBDATA file


I have seen the desire for a smaller ibdata file come up lately on the forums.mysql.com

The innodb database uses the ibdata file(s) to store the database data to disk. Configuring your system correctly is key and you can learn more about such options here: http://dev.mysql.com/doc/refman/5.6/en/innodb-configuration.html

InnoDB provides an ACID compliant and transaction safe storage engine; it is very productive but if you are deleting and/or replacing data often, you will need to recover the lost space over time. How much time is dependent on your system and use. You cannot run a single command and recover space in an ibdata file. It will take a few steps and it is not a behind the scenes job, unless done on a slave server. If you have a slave it is best to do this work on the slaved database first then plan to rotate that database server to become the master database.

So two different situations; these are not the only solutions but some solutions :

  • You want to keep the ibdata file the same size but you want to just clear out the wasted space
The best way to recover lost space is to dump the data and reload it. Yes, not the first choice for a DBA I know. This is more troublesome the bigger your database is. I would hope you have a slave database and can do this off the slave then make it a master later.

  1. Backup the database 
    1. mysqldump --user=<username> --password=<> --add-drop-database   --master-data=2  --triggers --routines --events --databases (list database names and do not add mysql to this list) > /Just_AN_example/mysqldump_<DATEHERE>_.sql 
      1. This gives you an ASCII copy just in case of binary corruption. 
      2. It also has master data via a comment if needed.
      3. This will keep your mysql authentication in tact as well. 
        1. I would save the mysql database as a dump separately. 
    2. You  also can create a backup with MySQL Enterprise Backup or Percona XtraBackup, if the system was a bigger db and needed online backups these are good choices. Up to you which you use for various reasons. 
  2. Checksum your database. 
    1. Gather some numbers on what you have so you can compare it when you load it back. 
      1. This can be done with Percona Toolkit

        1. # ./pt-table-checksum --password=<Password>   > checksum_before_dump.txt
      2. A query you can write yourself.
        1. I have a blog post on this as well 
          1. http://anothermysqldba.blogspot.com/2013/05/mysql-checksum.html
  3. Stop/Start the database and take advantage of this downtime for any read only variables you would like to adjust 
  4. Load the database back 


  1. Follow steps 1 through 2 in the process listed above. 
  2. In the step 4 of the above process you will want to add the following to your my.cnf file.
    1. innodb_file_format=Barracuda
    2. innodb_file_per_table=1
  3. Remove the ibdata file and logs.
    1. No coming back from this point 
  4. Start the database 
  5. Confirm it is up and running
  6. Load the database from backup. 

This of course would be best to do on a non production/slave server so you can confirm all the steps and get yourself to a workable situation then rotate the slave to be the new master.

MySQL CHECKSUM

CHECKSUM TABLE is useful information when you are checking the status of a table. This is often used before and after a backup and restore to ensure data is intact.

Here is a simple way to use it via the MySQL command line and the tools already available to you.


mysql> CREATE USER 'checksumuser'@'localhost';
mysql>GRANT SELECT ON *.* TO 'checksumuser'@'localhost';


mysql>SELECT
CONCAT('mysql --user=checksumuser -e \'CHECKSUM TABLE ',TABLE_SCHEMA,'.',TABLE_NAME ,' EXTENDED\'; ') as cmd_line_query
INTO OUTFILE '/tmp/checksums.sh'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
AND ENGINE IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME;

mysql> exit


# chmod +x /tmp/checksums.sh
# /tmp/checksums.sh > /tmp/checksums_b4dump.sql

Now you will have all your checksum data available to you in the file listed. Simple example of the data below


Table Checksum
world.City 2011482258
Table Checksum
world.Country 580721939
Table Checksum
world.CountryLanguage 1546017027


After the dump or process you are running you can then run the same script just change the output file then load it and compare results. It is not the cleanest method but it is an easy fast check for you to do.

# /tmp/checksums.sh > /tmp/checksums_after_dump.sql


mysql> use test
mysql> CREATE TABLE `checksums` (
`checksums_id` int(11) NOT NULL AUTO_INCREMENT,
table_name varchar(100) DEFAULT NULL,
size_a int(11) DEFAULT NULL,
size_b int(11) DEFAULT NULL,
PRIMARY KEY (`checksums_id`)
) ENGINE=InnoDB ;

LOAD DATA INFILE '/tmp/checksums_b4dump.sql'
IGNORE INTO TABLE checksums
(table_name, size_a);

LOAD DATA INFILE '/tmp/checksums_after_dump.sql'
IGNORE INTO TABLE checksums
(table_name, size_b);

DELETE FROM checksums WHERE table_name = 'Table';

SELECT a.table_name , a.size_a, b.size_b
FROM checksums a
INNER JOIN checksums b ON a.table_name = b.table_name and a.checksums_id != b.checksums_id
WHERE a.size_a IS NOT NULL AND b.size_b IS NOT NULL ;
+-----------------------------------------------------------------------+------------+------------+
| table_name | size_a | size_b |
+-----------------------------------------------------------------------+------------+------------+
| world.City | 2011482258 | 2011482258 |
| world.Country | 580721939 | 580721939 |
| world.CountryLanguage | 1546017027 | 1546017027 |
+-----------------------------------------------------------------------+------------+------------+


#mysql -p
mysql> DROP USER 'checksumuser'@'localhost';

DO NOT FORGET TO DROP THE USER when done.

Wednesday, May 15, 2013

MySQL count and select from a partition

The MySQL Forums had a question about how to count rows per partition.
I figured it would make a good blog post as well.

First these are some good links to have for partition review and at least get you started. The examples used here reference examples started with these pages.

> CREATE TABLE t2
    -> (
    ->   dt DATE
    -> )
    -> PARTITION BY RANGE (TO_DAYS(dt))
    -> (
    ->   PARTITION p01 VALUES LESS THAN (TO_DAYS('2007-01-01')),
    ->   PARTITION p02 VALUES LESS THAN (TO_DAYS('2008-01-01')),
    ->   PARTITION p03 VALUES LESS THAN (TO_DAYS('2009-01-01')),
    ->   PARTITION p04 VALUES LESS THAN (MAXVALUE));

> desc t2;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| dt    | date | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

INSERT INTO t2 VALUES ('2007-02-02'),('2008-02-02'),('2009-02-02'),(CURDATE());
Query OK, 4 rows affected (0.04 sec)


OK so now we can select from the partition as well as count from them..

> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

> explain partitions select count(*) from t2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: p01,p02,p03,p04
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: NULL

> select * from t2 PARTITION (p02);
+------------+
| dt         |
+------------+
| 2007-02-02 |
+------------+


> select * from t2 PARTITION (p04);
+------------+
| dt         |
+------------+
| 2009-02-02 |
| 2013-05-15 |
+------------+

> select * from t2 PARTITION (p02,p04);
+------------+
| dt         |
+------------+
| 2007-02-02 |
| 2009-02-02 |
| 2013-05-15 |
+------------+

> select count(*) from t2 PARTITION (p04);
+----------+
| count(*) |
+----------+
|        2 |
+----------+


Hope this helps.

Monday, May 13, 2013

Checking out MariaDB 10.0.2

I downloaded the MariaDB 10.0.2 source package and did a custom install.  I did this because of a previous post in which I had 2 masters already built. This time I removed the circular replication and pointed them to this mariadb install. I used port 3310 this time. Same install configuration examples from previous post would apply here just now put into mariadb-10.0.2 folders. I added the install at the bottom of this post just in case you want it.

The reason I did this was because I wanted to check out the latest MariaDB features primarily the following:
Multi-source replication

Make sure you have different server-ids set per server to start with.

Just started so nothing here should be expected
> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| |
+-----------------------------+

So gather information from a master server
> show master status\G
*************************** 1. row ***************************
File: percona_mysql-bin.000005
Position: 107


Now update the Mariadb 10.0.2 slave
SET @@default_master_connection='percona';

CHANGE MASTER 'percona' TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'root',
MASTER_PASSWORD = '',
MASTER_PORT = 3307 ,
MASTER_LOG_FILE = 'percona_mysql-bin.000005',
MASTER_LOG_POS = 107



> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| percona                     |
+-----------------------------+

OK Now let me add the second master
SET @@default_master_connection='oracle';

CHANGE MASTER 'oracle' TO MASTER_HOST = '127.0.0.1',
MASTER_USER = 'root',
MASTER_PASSWORD = '',
MASTER_PORT = 3309 ,
MASTER_LOG_FILE = 'oracle_mysql-bin.000009',
MASTER_LOG_POS = 5453


Next you can check the status to ensure both settings are set up.
>SHOW ALL SLAVES STATUS\G

*************************** 1. row ***************************
Connection_name: oracle
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3309
Connect_Retry: 60
Master_Log_File: oracle_mysql-bin.000009
Read_Master_Log_Pos: 5453
Relay_Log_File: relay-bin-oracle.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: oracle_mysql-bin.000009
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5453
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: 0
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Pos:
*************************** 2. row ***************************
Connection_name: percona
Slave_SQL_State:
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: percona_mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin-percona.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: percona_mysql-bin.000005
Slave_IO_Running: No
Slave_SQL_Running: No
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 248
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: 0
Retried_transactions: 0
Max_relay_log_size: 1073741824
Executed_log_entries: 0
Slave_received_heartbeats: 0
Slave_heartbeat_period: 1800.000
Gtid_Pos:

OK time to start it

> START ALL SLAVES;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

root@localhost [(none)]> show warnings;
+-------+------+-------------------------+
| Level | Code | Message |
+-------+------+-------------------------+
| Note | 1937 | SLAVE 'percona' started |
| Note | 1937 | SLAVE 'oracle' started |
+-------+------+-------------------------+



  Relay_Master_Log_File: percona_mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 Relay_Master_Log_File: oracle_mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes


So let us test some situations.

Via Percona master
use test;
CREATE TABLE `multi_test` (
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

MariaDB Slave
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| multi_test |
+----------------+

Via Oracle MySQL master
use test;
CREATE TABLE `multi_test2` (
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

MariaDB Slave
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| multi_test |
| multi_test2 |
+----------------+

OK that works !


SHOW EXPLAIN
This is rather straight forward but nice to catch a query as it is running.
> show explain for 17;
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | sbtest | range | PRIMARY | PRIMARY | 4 | NULL | 99 | Using where |
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)

root@localhost [test]> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------+
| Note | 1003 | SELECT SUM(K) from sbtest where id between 4997 and 5096 |
+-------+------+----------------------------------------------------------+


Side notes:


Cassandra Storage Engine
I am curious about this and how it relates to the NoSQL and Innodb solution via memcache.
I have a post on that here: http://anothermysqldba.blogspot.com/2013/04/nosql-php-memcache-innodb-mysql.html

I will have to come back to this as I set up Cassandra on my environment. I am not eager but curious.


User Feedback Plugin
The documentation "Quick start" says to add to the my.cnf file under [mysqld]
[mysqld]
feedback=ON
port = 3310
socket = /tmp/mariadb-10.0.2.sock

130513 17:45:10 InnoDB: 10.0.2-MariaDB started; log sequence number 20183690
130513 17:45:10 [ERROR] /usr/local/mariadb-10.0.2/bin/mysqld: unknown variable 'feedback=ON'

This worked a lot easier and as expect this way once I removed the "Quick start" instructions.
> INSTALL PLUGIN feedback SONAME 'feedback.so';

> SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'feedback';
+---------------+
| plugin_status |
+---------------+
| ACTIVE |
+---------------+


Via the error log you can also see it working:

[Note] feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent
[Note] feedback plugin: server replied 'ok'



Overall my current favorite enhancements are :



The basic install was this:
# Preconfiguration setup
shell> groupadd mariadb
shell> useradd -r -g mariadb mariadb

# Beginning of source-build specific instructions
shell> tar zxvf MariaDB-VERSION.tar.gz
shell> cd MariaDB-VERSION
shell> cmake .
shell> make
shell> make install DESTDIR="/usr/local/mariadb-10.0.2-tmp"
# End of source-build specific instructions

Build files have been written to: /usr/local/src/MySQL/MariaDB/10.0.2/mariadb-10.0.2

I do not like the results
-- Installing: /usr/local/mariadb-10.0.2-tmp/usr/local/mysql/
If DESTDIR is should install into that location not start with user under that location. This is a MySQL original issue as it does this with all versions of MySQL.

# Fix the odd/bug setup
shell> cd /usr/local/mariadb-10.0.2-tmp
shell> mv usr/local/mysql/ ../mariadb-10.0.2 ;
shell> cd ../; # rm -Rf mariadb-10.0.2-tmp

# Postinstallation setup
shell> cd /usr/local/mariadb-10.0.2
shell> chown -R mariadb .
shell> chgrp -R mariadb .

# Next command is optional
shell> cp support-files/my-small.cnf /etc/mariadb-10.0.2.cnf
shell> vi /etc/mariadb-10.0.2.cnf
port = 3310
socket = /tmp/mariadb-10.0.2.sock

shell> scripts/mysql_install_db --defaults-file=/etc/mariadb-10.0.2.cnf --basedir=/usr/local/mariadb-10.0.2 --skip-name-resolve --datadir=/var/lib/mariadb-10.0.2 --user=mariadb
shell> chown -R mariadb /var/lib/mariadb-10.0.2/*

shell> # bin/mysqld_safe --defaults-file=/etc/mariadb-10.0.2.cnf --user=mariadb --datadir=/var/lib/mariadb-10.0.2/ --port=3310 &


shell> # ./bin/mysql --port=3310 --socket=/tmp/mariadb-10.0.2.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.2-MariaDB Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.






Sunday, May 12, 2013

Using the MySQL event scheduler

The MySQL event scheduler  is very effective for scheduling different things as you need them.
Below is a simple example of how to use events and also how you can use the event scheduler differently across a master & a slave.

First check the status of your event scheduler:



> show variables like '%event%';
+---------------------------------------------------+-------+
| Variable_name                                     | Value |
+---------------------------------------------------+-------+
| event_scheduler                                   | OFF   |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size      | 10    |
+---------------------------------------------------+-------+



To turn it on you can use the following command or you can also set it in your my.cnf file.

set GLOBAL event_scheduler=ON;


I will create a database just for this demo


create database events_test;
use events_test;



For a test on the master I will use this simple table in the events_test database.


CREATE TABLE `foobar` (
  `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


I like to have my events run stored procedures because I can test the procedure easily before pushing it into an event.

delimiter //
CREATE PROCEDURE mastereventtest()
     BEGIN
        insert into foobar values (NOW());
     END//
delimiter ;


Now I will have this event run every minute just for this demo



CREATE EVENT mastereventtest
        ON SCHEDULE EVERY 1 MINUTE
        COMMENT 'testing master events'
        DO
        call mastereventtest();





Confirm the that it is in the system.



>  show create event  mastereventtest\G
*************************** 1. row ***************************
               Event: mastereventtest
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `mastereventtest` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-12 21:25:22' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'testing master events' DO call mastereventtest()
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci






> show events \G
*************************** 1. row ***************************
                  Db: events_test
                Name: mastereventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2013-05-12 21:25:22
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci




I will also create an event on the slave and since I plan on doing that I need to disable the events that I want off. So I will alter the event mastereventtest on the SLAVE server.


ALTER EVENT mastereventtest disable on slave;



I will again create a different table on the slave only under the events_test database, that was replicated


CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);


Again I like to create a procedure to use with my events. This is not required but just makes it easier to test.

delimiter //
CREATE PROCEDURE slaveeventtest()
     BEGIN
        insert into foo values ();
     END//
delimiter ;


This will be the event I run on the slave


CREATE EVENT slaveeventtest
        ON SCHEDULE EVERY 2 MINUTE
        COMMENT 'testing master events'
        DO
        call slaveeventtest();





Confirm that is also made it into the system



> show create event  slaveeventtest\G
*************************** 1. row ***************************
               Event: slaveeventtest
            sql_mode: NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
           time_zone: SYSTEM
        Create Event: CREATE DEFINER=`root`@`localhost` EVENT `slaveeventtest` ON SCHEDULE EVERY 2 MINUTE STARTS '2013-05-12 21:14:08' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'testing master events' DO call slaveeventtest()
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci





> show events\G

                Db: events_test
                Name: slaveeventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 2
      Interval field: MINUTE
              Starts: 2013-05-12 21:14:08
                Ends: NULL
              Status: ENABLED
          Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci





You can also query the information schema for event information




> select * from information_schema.EVENTS\G





To debug an event the best location to check is your error log.

Keep in mind that when you check the events on the slave you will see both events



> > show events \G
*************************** 1. row ***************************
                  Db: events_test
                Name: mastereventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2013-05-12 21:25:22
                Ends: NULL
              Status: SLAVESIDE_DISABLED
          Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
                  Db: events_test
                Name: slaveeventtest
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 2
      Interval field: MINUTE
              Starts: 2013-05-12 21:14:08
                Ends: NULL
              Status: ENABLED
          Originator: 3
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 rows in set (0.01 sec)






Now did it work?

If you execute a show processlist you will be able to see the current event thread status  and you can read more about the state results here:


You should be seeing : Waiting for next activation
I also have results in the tables:



root@localhost [events_test]> select * from foobar;
+---------------------+
| time_recorded       |
+---------------------+
| 2013-05-12 21:25:22 |
| 2013-05-12 21:26:22 |
| 2013-05-12 21:27:22 |
| 2013-05-12 21:28:22 |
| 2013-05-12 21:29:22 |
| 2013-05-12 21:30:22 |
| 2013-05-12 21:31:22 |
| 2013-05-12 21:32:22 |
+---------------------+
8 rows in set (0.00 sec)

root@localhost [events_test]> select * from foo;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)








To Drop or Disable Events:

DROP EVENT IF EXISTS mastereventtest;
ALTER EVENT mastereventtest disable;





A MySQL Credo

Credo: a statement of beliefs, principles, or opinions.

The MySQL conference of 2013 (Percona Live) is behind us and we saw all the MySQL players come together again in one place. Lots of sessions and people all able to talk and learn together. It got me thinking and for some reason today I developed the following thoughts.

The MySQL Community is a vibrant and intelligent community. We are made up of top engineers, powerful software companies, as well as eager and excited newcomers eager to volunteer and learn. Like any community, things change and we debate over the validity of those changes. We have shown concern over the MySQL bug database changes and heard Oracle's response. We have shown concern over the ability to provide enhancements or patches, and heard Oracle's response. We have valid concerns and Oracle has valid responses. It is up to us how much we continue to volunteer and support the MySQL Community as a whole. I of course hope we all continue to support it regardless of which flavor or MySQL you decide to use, MySQL, Percona or MariaDB.

What we do need is continue the support of volunteers as a whole:


I am only one, 
but I am one. 
I cannot do everything, 
but I can do something. 
And because I cannot do everything,
I will not refuse to do 
the something that I can do. 
What I can do, I should do.

We all need to also:

  • Recognize that problems exist 
    • problems of some kind always will
  • Negotiation of our social contract should occur
  • Accept our personal responsibility of community success 
  • Encourage community leaders who can empower others 
  • Encourage a sense of hope, opportunity to others in the community
  • Acknowledge that major problems can rarely be solved by a single jurisdiction 
  • Share resources, power and information. 
"Liberty and duty, freedom and responsibility. That's the deal." --  John W. Gardner

“We are all faced with a series of great opportunities - brilliantly disguised as insoluble problems.”
 John W. Gardner 
(American Writer and Secretary of Health, Education and Welfare, 1912-2002)


So how can you currently help:




Innotop

Techmint.com has a nice article about Innotop: http://www.tecmint.com/install-innotop-to-monitor-mysql-server-performance/

Innotop is a helpful monitoring tool and this article has nice examples to get you started.

innotop-1.9.0]# /usr/local/bin/innotop --help
Usage: innotop

--[no]color -C Use terminal coloring (default)
--config -c Config file to read
--count Number of updates before exiting
--delay -d Delay between updates in seconds
--help Show this help message
--host -h Connect to host
--[no]inc -i Measure incremental differences
--mode -m Operating mode to start in
--nonint -n Non-interactive, output tab-separated fields
--password -p Password to use for connection
--port -P Port number to use for connection
--skipcentral -s Skip reading the central configuration file
--socket -S MySQL socket to use for connection
--spark Length of status sparkline (default 10)
--timestamp -t Print timestamp in -n mode (1: per iter; 2: per line)
--user -u User for login if not current user
--version Output version information and exit
--write -w Write running configuration into home directory if no config files were loaded

innotop is a MySQL and InnoDB transaction/status monitor, like 'top' for
MySQL. It displays queries, InnoDB transactions, lock waits, deadlocks,
foreign key errors, open tables, replication status, buffer information,
row operations, logs, I/O operations, load graph, and more. You can
monitor many servers at once with innotop.


Some additional reading on this topic can be found here:

Saturday, May 11, 2013

MySQL & GIS & The Haversine formula and distance between two points.

MySQL is not the database that comes to mind first when people think of GIS.  The databases listed below are:

  • Oracle
  • Microsoft SQL Server
  • IBM DB2
  • IBM Informix
  • PostgreSQL

http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/types_of_geodatabases.htm
http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/determ-1479045992.htm


MySQL has been working with GIS for sometime now, it goes back to MySQL 4.1:
Mark Maunder's post above is a great look at GIS. This blog post will focus more on formulas but I wanted to point out Mark's nice post.

The use of a SPATIAL index works with the MyISAM storage engine, so if you are on MySQL 5.5 or above keep that in mind as Innodb is the default storage engine.

> CREATE TABLE geom (
-> lat float(10,7) NOT NULL,
-> lon float(10,7) NOT NULL,
-> g GEOMETRY NOT NULL,
-> SPATIAL INDEX(g)
-> ) ENGINE=Innodb;
ERROR 1464 (HY000): The used table type doesn't support SPATIAL indexes
> CREATE TABLE geom (
-> lat float(10,7) NOT NULL,
-> lon float(10,7) NOT NULL,
-> g GEOMETRY NOT NULL,
-> SPATIAL INDEX(g)
-> ) ENGINE=MyISAM;

So with Mark's schema design I populated the lat/long of some cities in China.
This data was gathered via here http://www.infoplease.com/ipa/A0001769.html

CREATE TABLE china (
cityname varchar(50) DEFAULT NULL,
lat float(10,7) NOT NULL,
lon float(10,7) NOT NULL,
g GEOMETRY NOT NULL,
SPATIAL INDEX(g)
) ENGINE=MyISAM;

INSERT INTO china VALUES ('Beijing', 39.55, 116.25, GeomFromText('POINT(39.55 116.25)'));
INSERT INTO china VALUES ('Canton', 23.7, 113.15, GeomFromText('POINT(23.7 113.15)'));
INSERT INTO china VALUES ('Chongqing', 29.46, 106.34, GeomFromText('POINT(29.46, 106.34)'));
INSERT INTO china VALUES ('Hong Kong', 22.20, 114.11, GeomFromText('POINT( 22.20 114.11)'));
INSERT INTO china VALUES ('Shanghai', 31.10, 121.28, GeomFromText('POINT(31.10 121.28)')); 

Just to make sure it all worked....

select lat, lon from china;
+------------+-------------+
| lat        | lon         |
+------------+-------------+
| 39.5499992 | 116.2500000 |
| 23.7000008 | 113.1500015 |
| 22.2000008 | 114.1100006 |
| 31.1000004 | 121.2799988 |
+------------+-------------+


So lets us check the distance from Beijing to Hong Kong.
A quick look online tells us it is 1963 km but let us check our data.


Now it was time to dig into the world of distance formulas..  I am not a GIS focused DBA, I admit that no problem. I enjoyed more of the range of answers to the math formulas I encountered, otherwise I would have used the table information above more. Instead it is just a reference for you.  I took at look at the widely debated ways to calculate distance between two lat/long points. You will find a lot of different functions, procedures and etc online to calculate this.

First I set some variables because I wanted to test the formulas

SET @lat1 =39.55;
SET @long1 =116.25;
SET @lat2 =22.20;
SET @long2 =114.11;

For example:

This website has a function for Distance. I have added it below to ensure that you can cut and paste it with the Delimiter options in place for you. But does it work?  BTW I also updated the radius of the earth value to 3959.
http://www.sqlexamples.info/SPAT/mysql_distance.htm

delimiter //
CREATE FUNCTION fn_distance
(p_x1 FLOAT, p_y1 FLOAT, p_x2 FLOAT, p_y2 FLOAT)
RETURNS FLOAT
DETERMINISTIC
BEGIN
DECLARE v_dist FLOAT;
DECLARE A FLOAT; DECLARE B FLOAT;
DECLARE C FLOAT; DECLARE D FLOAT;
/*
returns distance calculation between two points in LAT-LONG coordinates
*/

SET v_dist = 0;

-- convert to radians
SET A = p_x1 / 57.29577951;
SET B = p_y1 / 57.29577951;
SET C = p_x2 / 57.29577951;
SET D = p_y2 / 57.29577951;

IF (A = C && B = D) THEN
SET v_dist = 0;
ELSEIF ((sin(A)*sin(C)+cos(A)*cos(C)*cos(B - D)) > 1) THEN
SET v_dist = 3959 * acos(1);
ELSE
SET v_dist = 3959 *acos(sin(A)*sin(C) + cos(A)*cos(C)*cos(B - D));
END IF;

SET v_dist = v_dist * 1.609;

/* return distance in km. */
RETURN v_dist;

END;
//
delimiter ;

> SELECT fn_distance (@lat1, @long1, @lat2 , @long2) AS dist_km;
+-------------------+
| dist_km |
+-------------------+
| 1939.5457763671875 |
+-------------------+

Another query test shows

> SELECT ( GLength( LineString(( PointFromWKB( POINT( @lat1, @long1 ))), ( PointFromWKB( POINT( @lat2, @long2 ) ))))) * 100 AS distance;
+--------------------+
| distance |
+--------------------+
| 1748.1478770401545 |
+--------------------+

Yet another found here http://www.posteet.com/view/1555 :

set log_bin_trust_function_creators=TRUE;
DELIMITER |
CREATE FUNCTION GeoDistKM( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
DECLARE pi, q1, q2, q3 FLOAT;
DECLARE rads FLOAT DEFAULT 0;
SET pi = PI();
SET lat1 = lat1 * pi / 180;
SET lon1 = lon1 * pi / 180;
SET lat2 = lat2 * pi / 180;
SET lon2 = lon2 * pi / 180;
SET q1 = COS(lon1-lon2);
SET q2 = COS(lat1-lat2);
SET q3 = COS(lat1+lat2);
SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
RETURN 6378.388 * rads;
END;
|
DELIMITER ;
select geodistkm(
@lat1, @long1, @lat2 , @long2) as distance;
+----------------------------------------+
| distance |
+----------------------------------------+
| 1942.0909423828125 |
+----------------------------------------+


Yet this is still wrong.  Because the distance from Beijing to Hong King is 1224.9 miles with his 1963km, according to this website: http://www.timeanddate.com/worldclock/distances.html?n=102 .  So the first and last results are very close.

So after wasting a lot of time, yes I do admit that, I still wanted a result using mathematical formulas that I could easily compare against.

The great circle distance d between two points with coordinates {lat1,lon1} and {lat2,lon2} is given by:
d=acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(lon1-lon2))
> SELECT ACOS(
-> SIN(@lat1) * SIN(@lat2) + COS(@lat1) * COS(@lat2) * COS( @long1 - @long2 )
-> ) *1000 as Aviation_forumula_DISTANCE;
+----------------------------+
| Aviation_forumula_DISTANCE |
+----------------------------+
| 1923.0473470093848 |
+----------------------------+


Another formula is the Haversine Formula
> SELECT 3956* 2 * ASIN ( SQRT (POWER(SIN((@lat1 - @lat2)*pi()/180 / 2),2) + COS(@lat1 * pi()/180) * COS(@lat2 *pi()/180) * POWER(SIN((@long1 - @long2) *pi()/180 / 2), 2) ) ) as Haversine_Formula_distance;
+----------------------------+
| Haversine_Formula_distance |
+----------------------------+
| 1204.5222518763514 |
+----------------------------+

Once again you see very different results. I expected better results with the Haversine formula. 
So after  playing with these formulas I went with the GeoDistKM function because it appears to me to be the closest to the  Haversine formula which I believe to be the correct formula to use when implemented correctly. This is followed by a close second to the Aviation formula I wrote based on Williams information. 

While 1942 is not the 1963 result I gathered via the online search, who is to say that they calculated it correctly as well. The curvature of the earth and the lat,lon coming closer together at the poles will allow for some errors in different formulas. So I will stick with this currently:

select geodistkm(@lat1, @long1, @lat2 , @long2) as distance;
+----------------------------------------+
| distance |
+----------------------------------------+
| 1942.0909423828125 |
+----------------------------------------+




As an additional note:
Look into MariaDB if you are curious about more options with GIS  https://kb.askmonty.org/en/gis-functionality/

Friday, May 10, 2013

The server quit without updating PID file: DO NOT MOVE THE my.cnf file

I just ran across a blog post that said when you get the error "The server quit without updating PID file" you should move the my.cnf file out of the way and restart MySQL.

That is just NOT the correct step to solving a problem. What about all the settings that you have in the MySQL file? If you are running a MySQL server correctly, then you will have very important variables set in that my.cnf file.

Consider this, if someone you worked with truncated a table would you react by removing the privileges of everyone that has access to the database for a unknown amount of time or would you instead, adjust and or remove the offending command from the offending user's privileges as well as talking with them about the error and the problems surrounding it? The same goes for moving the my.cnf file, why remove everything when the problem is targeted?

So you need to look at your error log file, it will tell you the problem. You or someone else could have edited the my.cnf file recently with an improper variable or maybe left a typo behind in the file. Moving the my.cnf does not fix the real problem it just ignores the real problem.

The best tool available for MySQL is the error log and what it can tell you. Learn to use it not ignore it.

To put it simply:  "The server quit without updating PID file" is equivalent to MySQL could not start because of an error that has been noted in the error log.


Find additional PID FILE posts here:

oscommerce & MySQL

It has been awhile since I looked at the oscommerce software package. It is a great platform for building out a web store online.

However when they ask if you are above "MySQL\V5" or below it starts to make me nervous. Apparently I am not alone with the concern that InnoDB should be the storage engine of choice.


So I decided to dig a little more.....

I am assuming that you are running a more updated MySQL or at the very least you plan on doing that very soon. 


> SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL  GROUP BY TABLE_SCHEMA, ENGINE \G
*************************** 1. row ***************************
TABLE_SCHEMA: oscommerce
      ENGINE: MyISAM
count_tables: 62
        size: 795816
  index_size: 546816


SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce'  AND ENGINE IS NOT NULL   GROUP BY TABLE_SCHEMA, TABLE_NAME;

+--------------+---------------------------------------+--------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME                            | ENGINE | size   | index_size |
+--------------+---------------------------------------+--------+--------+------------+
| oscommerce   | address_book                          | MyISAM |   1024 |       1024 |
| oscommerce   | administrators                        | MyISAM |   9268 |       9216 |
| oscommerce   | administrators_access                 | MyISAM |   9236 |       9216 |
| oscommerce   | administrators_log                    | MyISAM |   4096 |       4096 |
| oscommerce   | administrator_shortcuts               | MyISAM |   4096 |       4096 |
| oscommerce   | banners                               | MyISAM |   4096 |       4096 |
| oscommerce   | banners_history                       | MyISAM |   1024 |       1024 |
| oscommerce   | categories                            | MyISAM |   3192 |       3072 |
| oscommerce   | categories_description                | MyISAM |  11348 |      11264 |
| oscommerce   | configuration                         | MyISAM |  32908 |       7168 |
| oscommerce   | configuration_group                   | MyISAM |   2948 |       2048 |
| oscommerce   | counter                               | MyISAM |   1034 |       1024 |
| oscommerce   | countries                             | MyISAM |  39816 |      30720 |
| oscommerce   | credit_cards                          | MyISAM |   2656 |       2048 |
| oscommerce   | currencies                            | MyISAM |   3192 |       3072 |
| oscommerce   | customers                             | MyISAM |   1024 |       1024 |
| oscommerce   | fk_relationships                      | MyISAM |   7652 |       2048 |
| oscommerce   | geo_zones                             | MyISAM |   2104 |       2048 |
| oscommerce   | languages                             | MyISAM |   5224 |       5120 |
| oscommerce   | languages_definitions                 | MyISAM |  90292 |      24576 |
| oscommerce   | manufacturers                         | MyISAM |   9292 |       9216 |
| oscommerce   | manufacturers_info                    | MyISAM |   4176 |       4096 |
| oscommerce   | modules                               | MyISAM |   2568 |       2048 |
| oscommerce   | newsletters                           | MyISAM |   1024 |       1024 |
| oscommerce   | newsletters_log                       | MyISAM |   4096 |       4096 |
| oscommerce   | orders                                | MyISAM |   1024 |       1024 |
| oscommerce   | orders_products                       | MyISAM |   1024 |       1024 |
| oscommerce   | orders_products_download              | MyISAM |   1024 |       1024 |
| oscommerce   | orders_products_variants              | MyISAM |   1024 |       1024 |
| oscommerce   | orders_status                         | MyISAM |  10332 |      10240 |
| oscommerce   | orders_status_history                 | MyISAM |   1024 |       1024 |
| oscommerce   | orders_total                          | MyISAM |   1024 |       1024 |
| oscommerce   | orders_transactions_history           | MyISAM |   1024 |       1024 |
| oscommerce   | orders_transactions_status            | MyISAM |  10324 |      10240 |
| oscommerce   | products                              | MyISAM |   8596 |       8192 |
| oscommerce   | products_description                  | MyISAM |  17924 |      15360 |
| oscommerce   | products_images                       | MyISAM |   3216 |       3072 |
| oscommerce   | products_images_groups                | MyISAM |   3280 |       3072 |
| oscommerce   | products_notifications                | MyISAM |   1024 |       1024 |
| oscommerce   | products_to_categories                | MyISAM |   4123 |       4096 |
| oscommerce   | products_variants                     | MyISAM |   4156 |       4096 |
| oscommerce   | products_variants_groups              | MyISAM |   3216 |       3072 |
| oscommerce   | products_variants_values              | MyISAM |   4348 |       4096 |
| oscommerce   | product_attributes                    | MyISAM |   4136 |       4096 |
| oscommerce   | product_types                         | MyISAM |   9236 |       9216 |
| oscommerce   | product_types_assignments             | MyISAM |  10328 |      10240 |
| oscommerce   | reviews                               | MyISAM |   1024 |       1024 |
| oscommerce   | sessions                              | MyISAM |   6816 |       2048 |
| oscommerce   | shipping_availability                 | MyISAM |   3124 |       3072 |
| oscommerce   | shopping_carts                        | MyISAM |   1024 |       1024 |
| oscommerce   | shopping_carts_custom_variants_values | MyISAM |   1024 |       1024 |
| oscommerce   | specials                              | MyISAM |   1024 |       1024 |
| oscommerce   | tax_class                             | MyISAM |   2152 |       2048 |
| oscommerce   | tax_rates                             | MyISAM |   4144 |       4096 |
| oscommerce   | templates                             | MyISAM |   2160 |       2048 |
| oscommerce   | templates_boxes                       | MyISAM |   3732 |       2048 |
| oscommerce   | templates_boxes_to_pages              | MyISAM |  11968 |      11264 |
| oscommerce   | weight_classes                        | MyISAM |   3172 |       3072 |
| oscommerce   | weight_classes_rules                  | MyISAM |   4288 |       4096 |
| oscommerce   | whos_online                           | MyISAM |  10332 |      10240 |
| oscommerce   | zones                                 | MyISAM | 375892 |     247808 |
| oscommerce   | zones_to_geo_zones                    | MyISAM |   5147 |       5120 |
+--------------+---------------------------------------+--------+--------+------------+

If you are running a store, with customer data then stability should be an important factor in your database of choice. I would like to update these to InnoDB easily. 


>SELECT

CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE=InnoDB;') as query
INTO OUTFILE '/tmp/update_oscommerce.sql'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')  AND ENGINE IS NOT NULL AND TABLE_SCHEMA = 'oscommerce'
GROUP BY TABLE_SCHEMA, TABLE_NAME;


This query creates a simple ALTER TABLE for all of the oscommerce tables. If you have set your tables with a prefix into a database with other tables you can adjust query accordingly. 


mysql -p < /tmp/update_oscommerce.sql

So did it work? Yes and you will have to be aware that you will see a different in the size and index size. 


> SELECT TABLE_SCHEMA, ENGINE, COUNT(*) AS count_tables,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce' AND ENGINE IS NOT NULL  GROUP BY TABLE_SCHEMA, ENGINE \G

*************************** 1. row ***************************
TABLE_SCHEMA: oscommerce
      ENGINE: InnoDB
count_tables: 62
        size: 3407872
  index_size: 2031616


 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,   SUM(DATA_LENGTH+INDEX_LENGTH) AS size, SUM(INDEX_LENGTH) AS index_size FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'oscommerce'  AND ENGINE IS NOT NULL  GROUP BY TABLE_SCHEMA, TABLE_NAME;

+--------------+---------------------------------------+--------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME                            | ENGINE | size   | index_size
+--------------+---------------------------------------+--------+--------+------------+
| oscommerce   | address_book                          | InnoDB |  65536 |      49152
| oscommerce   | administrators                        | InnoDB |  32768 |      16384
| oscommerce   | administrators_access                 | InnoDB |  32768 |      16384
| oscommerce   | administrators_log                    | InnoDB |  65536 |      49152
| oscommerce   | administrator_shortcuts               | InnoDB |  32768 |      16384
| oscommerce   | banners                               | InnoDB |  49152 |      32768
| oscommerce   | banners_history                       | InnoDB |  32768 |      16384
| oscommerce   | categories                            | InnoDB |  32768 |      16384
| oscommerce   | categories_description                | InnoDB |  65536 |      49152
| oscommerce   | configuration                         | InnoDB |  81920 |      16384
| oscommerce   | configuration_group                   | InnoDB |  16384 |          0
| oscommerce   | counter                               | InnoDB |  16384 |          0
| oscommerce   | countries                             | InnoDB |  65536 |      49152
| oscommerce   | credit_cards                          | InnoDB |  16384 |          0
| oscommerce   | currencies                            | InnoDB |  32768 |      16384
| oscommerce   | customers                             | InnoDB |  32768 |      16384
| oscommerce   | fk_relationships                      | InnoDB |  16384 |          0
| oscommerce   | geo_zones                             | InnoDB |  16384 |          0
| oscommerce   | languages                             | InnoDB |  65536 |      49152
| oscommerce   | languages_definitions                 | InnoDB | 147456 |      32768
| oscommerce   | manufacturers                         | InnoDB |  32768 |      16384
| oscommerce   | manufacturers_info                    | InnoDB |  49152 |      32768
| oscommerce   | modules                               | InnoDB |  16384 |          0
| oscommerce   | newsletters                           | InnoDB |  16384 |          0
| oscommerce   | newsletters_log                       | InnoDB |  49152 |      32768
| oscommerce   | orders                                | InnoDB |  49152 |      32768
| oscommerce   | orders_products                       | InnoDB |  49152 |      32768
| oscommerce   | orders_products_download              | InnoDB |  49152 |      32768
| oscommerce   | orders_products_variants              | InnoDB |  49152 |      32768
| oscommerce   | orders_status                         | InnoDB |  49152 |      32768
| oscommerce   | orders_status_history                 | InnoDB |  49152 |      32768
| oscommerce   | orders_total                          | InnoDB |  32768 |      16384
| oscommerce   | orders_transactions_history           | InnoDB |  32768 |      16384
| oscommerce   | orders_transactions_status            | InnoDB |  49152 |      32768
| oscommerce   | products                              | InnoDB | 114688 |      98304
| oscommerce   | products_description                  | InnoDB |  81920 |      65536
| oscommerce   | products_images                       | InnoDB |  32768 |      16384
| oscommerce   | products_images_groups                | InnoDB |  32768 |      16384
| oscommerce   | products_notifications                | InnoDB |  49152 |      32768
| oscommerce   | products_to_categories                | InnoDB |  49152 |      32768
| oscommerce   | products_variants                     | InnoDB |  49152 |      32768
| oscommerce   | products_variants_groups              | InnoDB |  32768 |      16384
| oscommerce   | products_variants_values              | InnoDB |  49152 |      32768
| oscommerce   | product_attributes                    | InnoDB |  65536 |      49152
| oscommerce   | product_types                         | InnoDB |  32768 |      16384
| oscommerce   | product_types_assignments             | InnoDB |  49152 |      32768
| oscommerce   | reviews                               | InnoDB |  65536 |      49152
| oscommerce   | sessions                              | InnoDB |  16384 |          0
| oscommerce   | shipping_availability                 | InnoDB |  32768 |      16384
| oscommerce   | shopping_carts                        | InnoDB |  65536 |      49152
| oscommerce   | shopping_carts_custom_variants_values | InnoDB |  81920 |      65536
| oscommerce   | specials                              | InnoDB |  32768 |      16384
| oscommerce   | tax_class                             | InnoDB |  16384 |          0
| oscommerce   | tax_rates                             | InnoDB |  49152 |      32768
| oscommerce   | templates                             | InnoDB |  16384 |          0
| oscommerce   | templates_boxes                       | InnoDB |  16384 |          0
| oscommerce   | templates_boxes_to_pages              | InnoDB |  65536 |      49152
| oscommerce   | weight_classes                        | InnoDB |  32768 |      16384
| oscommerce   | weight_classes_rules                  | InnoDB |  49152 |      32768
| oscommerce   | whos_online                           | InnoDB |  65536 |      49152
| oscommerce   | zones                                 | InnoDB | 606208 |     376832
| oscommerce   | zones_to_geo_zones                    | InnoDB |  65536 |      49152
+--------------+---------------------------------------+--------+--------+------------+

Since I happen to have innodb_file_per_table set I will get .ibd files per table of course as well.
> select @@innodb_file_per_table ;

+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+



A quick test of the administration site as well as testing the shopping cart shows everything working just fine so far.  An easy fix that will depend on table sizes as to how fast it is done for you.  This example is with a fresh install. 

If you have it replicated, then being able to turn off the slave and update the tables on the slave first would be a good start.  Then rotate the master unless you can afford downtime. 

If you do not have it replicated.. then you should look into it . You should also and I hope you do, have it backed up daily at the least.