Cannot connect MySQL database to Confluence

[Anthony]

Hi,

we have to follow all instruction so setup MySQL database for Confluence but t didn't work for us. We always get the message: "Your database needs to use InnoDB as the default storage engine." Our my.cnf and my.ini. has been edited in /root/my.cnf and /root/my.ini and /etc/my.cnf as:

[mysqld]
character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
transaction-isolation=READ-COMMITTED
binlog_format=row

after all we don't have any luck, we still get the message "Your database needs to use InnoDB as the default storage engine."

we also heave find my.cnf in following paths but we didn't edited all of them:

/etc/my.cnf
/usr/share/mysql-test/suite/ndb_team/my.cnf
/usr/share/mysql-test/suite/ndb/my.cnf
/usr/share/mysql-test/suite/ndb_big/my.cnf
/usr/share/mysql-test/suite/rpl_ndb/my.cnf
/usr/share/mysql-test/suite/federated/my.cnf
/usr/share/mysql-test/suite/ndb_rpl/my.cnf
/usr/share/mysql-test/suite/rpl/extension/bhs/my.cnf
/usr/share/mysql-test/suite/rpl/my.cnf
/usr/share/mysql-test/suite/ndb_binlog/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/ndb_team/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/ndb/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/ndb_big/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/rpl_ndb/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/federated/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/ndb_rpl/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/rpl/extension/bhs/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/rpl/my.cnf
/home/virtfs/testserver/usr/share/mysql-test/suite/ndb_binlog/my.cnf
/home/virtfs/testserver/etc/my.cnf

We really need help ASAP, Thank you.

[Alexis Robert · Accepted solution]

Hello @Anthony, 

after you made the change to your configuration file, did you properly reload/restart the MySQL service ? 

Also, can you specify what version of MySQL you're using ? 

--Alexis

[Anthony]

Hi Alexis,

thank you for prompt response.

Yes we did restart the MySQL in few ways:

Following commands DID NOT work:

1. # /etc/init.d/mysqld stop:  No such file or directory  

2. # /etc/init.d/mysql stop:  No such file or directory 

3. # service mysqld stop
Redirecting to /bin/systemctl start mysql.service
Failed to start mysqld.service: Unit not found.

Following commands DID work:

4. # service mysql stop
Redirecting to /bin/systemctl start mysql.service

5. service mysql status (was Active: inactive (dead))

6. # service mysql start
Redirecting to /bin/systemctl start mysql.service

7. # service mysql status (was Active: active (running))

 MySQL version 14.14 Distrib 5.6.39, for Linux (x86_64) using  EditLine wrapper

Error screenshot  

[Alexis Robert]

Hi @Anthony, 

can you connect to your database isntance, select your Confluence database and execute this query: 

SHOW TABLE STATUS;

Just to make sure that your instance is indeed using INNODB.

Thanks, 

--Alexis

[Anthony]

mysql> use confluence;
Database changed
mysql> SHOW TABLE STATUS;
Empty set (0.00 sec)

that's all we get. 

[Alexis Robert]

Can you do a "show engines;" in MySQL ? This should give more information on the default engine.

[Anthony]

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql>

We can see that MyISAM is DEFAULT, but it's quite impossible to change it from my.cnf configuration file. It seams that  "default-storage-engine" variable doesn't work or it hasn't been change in right path.

[Alexis Robert]

OK so this is why Confluence can't continue the installation properly, InnoDB is not set as default storage engine on your instance.

The MySQL configuration file located in /etc/my.cnf, make sure that this one has the correct setting for default-storage-engine.

You should also have a look at the MySQL log files, in /var/log/mysql/ and see if you have any error messages related to InnoDB or your config file.

What linux distribution are you using ? This might help with investigations too.

--Alexis

[Anthony]

 - configuration file located in /etc/my.cnf  contains:

[mysqld]
character-set-server=utf8
collation-server=utf8_bin
default-storage-engine=INNODB
default-tmp-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
transaction-isolation=READ-COMMITTED
binlog_format=row

- cd /var/log/mysql
No such file or directory

- linux distribution:  CentOS Linux release 7.5.1804 (Core)

Thank you again for your help and effort

[Alexis Robert]

Can you make sure that you don't have a my.cnf file in /etc/mysql/ and /root/ as these files will be read too on startup.

What's weird is that InnoDB is the default storage engine on MySQL 5.6 so you shoud not have to do anything, but somehow on your server it seems that is the default.

Is that a server that was upgraded maybe ? or did you create it from scratch ? 

Another thing you could try to set InnoDB: log into mysql and paste the following command:

SET default_storage_engine=NDBCLUSTER;

and then  use "SHOW ENGINES" to check if it worked.

--Alexis

[Anthony]

I know it's weird that InnoDB isn't the default storage engine on MySQL 5.6 but unfortunately that's our bad luck. The server was purchased as brand new Dedicated server, and few developers was working on our project so we are not sure what they have done before. 

we have change the  my.cnf file in these paths /etc/mysql/ and /root/ and they look they are even now. 

We have try the following command and we finally get InnoDB as DEFAULT but still didn't work cos once you reset mysqld it comes back to  MyISAM is DEFAULT

SET default_storage_engine=INNODB;

This is really frustrating  

[Alexis Robert]

Can you try uninstalling and reinstalling completely MySQL ? Also, try to keep only one configuration file int /etc/mysql/ if possible and not in /root/ .

[Anthony]

1. I'm afraid if we delete my.cnf file from the /root/ that mysql will go down cos that file contain root username and password.  

2. Can uninstalling and reinstalling completely MySQL make any damage on my existing databases and websites in sense that existing ones will not work properly after installation ?

3. If we going to reinstalling completely MySQL, shall we replace it with newer version eg. 5.7

[Alexis Robert]

You can run Confluence on MySQL 5.7, but make sure all your other websites/applications run fine on this version first.

What you can try to force InnoDB is to modify your MySQL startup script to include the storage engine in the daemon command line:

mysqld --default-storage-engine=InnoDB

I am not an expert with CentOS but you should be able to find this script easily.

--Alexis 

[Anthony]

Hi,

I was followed @Alexis Robert advice "uninstalling and reinstalling completely MySQL with the same  MySQL version 5.6.39 " after that everything was great. Our InnoDB has changed to default-storage-engine=INNODB.

Thank you @Alexis Robert for your help

[Alexis Robert]

Good to hear @Anthony !

Please mark my answer as accepted so that other people can find this and fix similar issues :)

--Alexis