Error: SQL Server internal error when creating publisher account, adding entries, or at the end of installation

If you are receiving a server internal error when:

  • creating a new publisher on an own server setup
  • during installation or at the end of the admin system setup
  • creating a new entry in the admin system (i.e. a new publication, customer account, etc.)

then check your SQL error logs.  Log files are typically located at /var/log/.  MySQL server log files are usually identified by mysql.nameOfLogFile (e.g. mysqld.err).  Its path is /var/log/mysqld.log defined in the log_error config variable.

Check that mysql server is loading the my.ini config file and that it is not corrupt.

Sometimes the SQL global database mode is set to strict and affects writing alpha characters to certain fields in the database.

  • An example of an error (exact error may differ) when creating a publisher account:
    LockLizard database error (REMOTE_ADDR: 172.16.5.102 ):
    [Incorrect integer value: ‘un’ for column ‘validity‘ at row 1]
  • An example of an error (exact error may differ) when adding a publication:
    Server internal error.  Error logged with id 60
    [Incorrect integer value: ‘for column ‘obeyPubDate’ at row 1]
  • Error messages during or at the end of installation may say that a table does not exist or a column does not have a default value.  For example:
    [Table ‘ebooks.viewerusers’ doesn’t exist]
    [BLOB/TEXT column ‘USBID’ can’t have a default value]

To fix this problem you must disable the strict mode in SQL

This can either be done using a management interface such as MySQL Workbench or PHPMyAdmin or by opening the “my.ini” file within the MySQL installation directory and searching for something like:

# Set the SQL mode to strict
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

Replace with:
# Set the SQL mode to strict
sql-mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

Then restart the MySql service/server.

Quick Fix:  A temporary workaround is to use the command SET GLOBAL SQL_MODE=”;
Note however that this will be lost if the computer is restarted so you will need to edit the my.ini file accordingly for a more permanent change.