Foreign Key issue creating SCM repository in MySQL database

[#1]

Installing Cloudera Manager Standard edition and a MySQL database on different (virtual) servers.

When the SCM service is started, several .ddl scripts are executed but it fails with a foreign key constraint error.

Cloudera Manager server

# uname -r
2.6.32-358.23.2.el6.x86_64

# cat /etc/redhat-release
CentOS release 6.4 (Final)

# rpm -aq | grep cloudera
cloudera-manager-server-4.7.2-1.cm472.p0.135.el6.x86_64
cloudera-manager-daemons-4.7.2-1.cm472.p0.135.el6.x86_64

# /usr/share/cmf/schema/scm_prepare_database.sh mysql -h <dbserver> -uadmin -pXXXXXXX --scm-host <clmgr> scm scm scm
Verifying that we can write to /etc/cloudera-scm-server
Creating SCM configuration file in /etc/cloudera-scm-server
Executing:  /usr/java/jdk1.7.0_25/bin/java -cp /usr/share/java/mysql-connector-java.jar:/usr/share/cmf/schema/../lib/* com.cloudera.enterprise.dbutil.DbCommandExecutor /etc/cloudera-scm-server/db.properties com.cloudera.cmf.db.
[2013-10-22 09:50:24,783] INFO     0[main] - com.cloudera.enterprise.dbutil.DbCommandExecutor.testDbConnection(DbCommandExecutor.java:233) - Successfully connected to database.
All done, your SCM database is configured correctly!

MySQL database server

$ mysql -uroot -p
Enter password:
Server version: 5.6.14-log MySQL Community Server (GPL)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| amon               |
| hive               |
| hmon               |
| mysql              |
| nav                |
| performance_schema |
| rman               |
| scm                |
| smon               |
+--------------------+
10 rows in set (0.00 sec)

mysql> use scm;
Database changed
mysql> show tables;
Empty set (0.00 sec)

Cloudera Manager server

# service cloudera-scm-server start
Starting cloudera-scm-server:                              [  OK  ]

# cat /var/log/cloudera-scm-server/cloudera-scm-server.log
2013-10-22 09:54:26,154  INFO [main:dbutil.DbUtil@203] Schema version table doesn't exist.
2013-10-22 09:54:26,154  INFO [main:dbutil.DbUtil@348] Current database schema version: 0
2013-10-22 09:54:28,731  INFO [main:dbutil.SqlScriptRunnerFactory$SqlStringRunner@204] Processed: /usr/share/cmf/schema/mysql/00001_cmf_schema.mysql.ddl
2013-10-22 09:54:28,781  INFO [main:dbutil.SqlScriptRunnerFactory$SqlStringRunner@204] Processed: Updated Schema Version to 1
2013-10-22 09:54:29,226  INFO [main:dbutil.SqlScriptRunnerFactory$SqlStringRunner@204] Processed: /usr/share/cmf/schema/mysql/00002_cmf_schema.mysql.ddl
...
...
2013-10-22 09:54:36,177  INFO [main:dbutil.SqlScriptRunnerFactory$SqlStringRunner@204] Processed: Updated Schema Version to 33
2013-10-22 09:54:36,206  INFO [main:dbutil.SqlScriptRunnerFactory$SqlStringRunner@204] Processed: /usr/share/cmf/schema/mysql/00034_cmf_schema.mysql.ddl
2013-10-22 09:54:36,232  INFO [main:dbutil.SqlScriptRunnerFactory$SqlStringRunner@204] Processed: Updated Schema Version to 34
2013-10-22 09:54:37,074 FATAL [main:cmdline.SqlFile@68] SQL Error at 'UTF-8' line 13:
"alter table CONFIG_REVISIONS
      modify column REVISION_ID bigint not null auto_increment"
Cannot change column 'REVISION_ID': used in a foreign key constraint 'FK_SERVICE_CONFIG_REVISION' of table 'scm.SERVICES'
2013-10-22 09:54:37,074 FATAL [main:cmdline.SqlFile@68] Rolling back SQL transaction.

After this error there are Java errors and the SCM services can't be started.

Regards  

  OLA

[#2]

I am having the same issue, any updates on this?

[#3]

I have a theory. I beleive the script assume you are running myisam which does not enforce Foreign Keys all the well (actualy not at all) 

You have two choices

1. change your default engine to myisam, not what I would prefer

2. alter the ddl scripts in /usr/share/cmf/schema/mysql directory

You will need to add two lines:

After the first update add 

SET FOREIGN_KEY_CHECKS=0;

Then at the bottom of the file add 

SET FOREIGN_KEY_CHECKS=1;

You will need to do that to the following files: 

00035_cmf_schema.mysql.ddl
00043_cmf_schema.mysql.ddl
04509_cmf_schema.mysql.ddl
04511_cmf_schema.mysql.ddl

I did this and I got through that part of the install.

[#4 · Accepted solution]

Well, never mind, you have to use MyISAM. Lots of stuff breaks if you don't. It would be nice if the table creation scripts set them to MyISAM rather than having to change the default engine to MyISAM

[#5]

Thanks for the answer. It's strange that the documentation points to InnoDB but that building the repository fails. I will test it with the ISAM.

[#6]

Using MyISAM solved the problem. I've a workaround, thx for the help.

[#7]

This is a known issue with CM and MySQL 5.6.7 or newer. We're working on it and should have it fixed in an upcoming release.

In general, MySQL 5.6 isn't yet supported, so the safest thing you can do is to downgrade to an older version, like 5.5. Switching to MyISAM is a really bad idea; a bunch of stuff won't work as expected down the line.