Configure FOG Database to use INNODB Engine

[george1421]

In this thread I’ll discuss the steps needed to convert the FOG default (prior to new installs of FOG 1.5.10) to use the INNODB database engine vs the MySQL?MariaDB default of MyISAM.

In larger FOG installations (> 500 computers with the FOG Client installed) there are speed advantages to using the INNODB engine over MyISAM.

The first step is to check which is the current data engine that is being used in your FOG installation.

From the FOG Server host command prompt login to the mysql client and run this command:

SELECT TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'fog' and ENGINE = 'MyISAM';

If the output appears as below, then your FOG database is still configured for MyISAM

+------------------------+--------+
| TABLE_NAME             | ENGINE |
+------------------------+--------+
| LDAPServers            | MyISAM |
| clientUpdates          | MyISAM |
| dirCleaner             | MyISAM |
| globalSettings         | MyISAM |
| greenFog               | MyISAM |
| groupMembers           | MyISAM |
...

If the INNODB engine is listed for all of the tables then you can stop following this procedure because your database has already been upgraded.

This next command will see if your MySQL/MariaDB server is running with extended startup parameters (centos 7 typ no, debian and ubuntu typ yes). At the mysql cli command prompt key in

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;

The expected output will look like this for debian and ubuntu

mysql> SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;
+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| global                                                                                                                | session                                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

In this output there are two default options that will cause a problem when converting 2 FOG tables to innodb format. These startup values that need to be removed are NO_ZERO_IN_DATE,NO_ZERO_DATE

We need to take the original query output value of

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

and remove those troubled values making the startup parameters look like this:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Hint: Don't just copy and paste the startup values from above because your install of MySQL might have different startup values than what I'm showing above

Once we have the the new configuration, using the example the entire startup parameter value will look like this

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Now we need to find the mysql startup config files for your distribution. The quickest way to find it is to use this search query keyed into the FOG Host server command prompt grep -R -e '[mysqld]' /etc/* That query should return the file where the [mysqld] stanza is location. Edit that file and just below the [mysqld] section header insert the sql-mode= parameters. Make sure there is only one slq-mode entry in the entire [mysqld] section. Once you’ve inserted/updated the startup parameters save the file and restart the mysqld/mariadb service.

After the service restarts run the command again

SELECT @@GLOBAL.sql_mode global;

And confirm that the values NO_ZERO_IN_DATE,NO_ZERO_DATE are no longer listed in the startup parameters.

I realize it took us a long time to get here, but we are nearing the final steps to convert the MyISAM tables into INNODB tables. Note: This can be done on line under normal load, but I might suggest that you shut down apache while we are moving the tables just to avoid any locked table issue.

Log into the mysql cli program as root.

Now run these two commands:

USE fog;
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM
INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'fog'
INTO outfile '/tmp/fog-db-migrate.sql';

The commands to upgrade the FOG database engine will be copied into the /tmp/fog-db-migrate.sql file.

Lastly we just need to load those into the mysql cli program with:

SOURCE /tmp/fog-db-migrate.sql;

The output of that SOURCE command will look something like this:

Query OK, 1 row affected (0.005 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 19 rows affected (0.003 sec)
Records: 19  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.004 sec)
Records: 0  Duplicates: 0  Warnings: 0

Lastly lets run the check command again to see database engine in use. Confirm that no tables have MyISAM listed.

SELECT TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'fog';

A correctly configured FOG database should contain only INNODB table references.

+------------------------+--------+
| TABLE_NAME             | ENGINE |
+------------------------+--------+
| groups                 | InnoDB |
| os                     | InnoDB |
| plugins                | InnoDB |
| taskStates             | InnoDB |
| printerAssoc           | InnoDB |
| hostScreenSettings     | InnoDB |
| snapins                | InnoDB |
...

Upgrade procedure is Done.