Skip to main content

Container specific

MySQL

Error migrating from 5.x to 8.x

Error messages

  • ORDER BY clause is not in SELECT list, references column ... ([more])
  • Invalid default value for 'CreatedAt' ([more])

The default SQL mode is causing trouble:

  • ONLY_FULL_GROUP_BY (prevent order on fields not selected)
  • STRICT_TRANS_TABLES (prevent default date values)
  • NO_AUTO_VALUE_ON_ZERO (allow default date values)

Temporary fix

SET GLOBAL sql_mode='NO_AUTO_VALUE_ON_ZERO';

Permanent fix Set it in the configuration file

  1. Open my.ini
  2. Replace sql_mode definition with
sql_mode="NO_AUTO_VALUE_ON_ZERO"
Driver for +8.4 not being used

Make sure that the 8.4 driver is present in

  • TOMCAT\lib
  • TOMCAT\webapps\XXX\WEB-INF\lib

In the application context file: TOMCAT\conf\Catalina\localhost\XXX.xml

driverClassName=com.mysql.jdbc.Driver

Should be changed to (simply add '.cj')

driverClassName=com.mysql.cj.jdbc.Driver

Import timeouts

Sometimes large blobs will cause timeouts when running the mysql tool

  • Create a config file with (example import.cnf)
[mysqld]
max_allowed_packet=1024M
innodb_lock_wait_timeout=3600
net_read_timeout=3600
net_write_timeout=3600
wait_timeout=3600
innodb_log_file_size=2048M
innodb_log_buffer_size=2048M
bulk_insert_buffer_size=1024M
  • Then a parameter in the import --
mysql.exe --defaults-file=import.cnf ...
Unable to restart services

Kill the underlying processes for both webserver and database

sudo killall -KILL mysqld_safe mysqld

If you cannot start MySQL again try removing the logfiles

sudo mv /mnt/sda/mysql/ib_logfile0 /mnt/sda/backup/
sudo mv /mnt/sda/mysql/ib_logfile1 /mnt/sda/backup/
sudo mv /mnt/sda/mysql/ibdata1 /mnt/sda/backup/

In some cases DB table repairs are needed

sudo nano /etc/my.cnf

Add the line

innodb_force_recovery = 1

Recover data from backup

Create a restore script

 sudo nano ./restoreall.sh
 chmod 777 ./restoreall.sh

Add the following

APP="ts"

for sql_file in `ls /mnt/sda/backup/rsync/${APP}base_*.sql`;
do mysql -uroot -pPASSWORD ${APP}base < $sql_file ;
done

for sql_file in `ls /mnt/sda/backup/rsync/${APP}live_*.sql`;
do mysql -uroot -pPASSWORD ${APP}live < $sql_file ;
done

for sql_file in `ls /mnt/sda/backup/rsync/${APP}test_*.sql`;
do mysql -uroot -pPASSWORD ${APP}test < $sql_file ;
done

Encoding problems with SQL dumps

If it is caused by special characters (æøå) in the sql files

  1. Open the .sql file in Notepad++
  2. Set encoding to ANSI
  3. Save

Tomcat

Warning during server stop

Example

SEVERE: The web application [/sandbox] registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.

The warning is of no importance because the shutdown automatically kills all objects in memory, and no memory leaks are there possible.

JBoss

WARN errors during boot

Example

17:56:40,337 WARN [org.jboss.as.server.deployment] (MSC service thread 1-12) Class Path entry ...

The warnings are due to jar taht have their version number appended to their name. Possible fixes include stripping the names of all jar files in the /WEB-INF/lib/, but recommended solution is ignoring the warnings.

Back to top