MySQL Error1075 – Incorrect table definition; What’s happening?

Hey Folks,

A few months ago, I found an issue, where, for some reason, someone ignored the warnings and tried to restore a backup from a different version of MySQL (or even MariaDB, IDK). And as a result, half the database was running without Primary Keys. So when a system was trying to update their schema, we were getting errors (like the error bellow) trowed at our face.

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Ok, first things first, you would like to run an ALTER TABLE TABLE_NAME_HERE
ADD PRIMARY KEY (ID);
and see if it works.  The error was being thrown because the table key doesn’t have a single index on it… if you have problems with duplicated records on it, you can try the following script to solve the issue.

First, get the max id from the table, and then run the following: 

UPDATE TABLE_NAME_HERE JOIN (SELECT @sequence := MAX_ID_HERE ) r SET id=@sequence:=@sequence+1 where id= DUPLICATED_ID_HERE;

WARNING

Be aware that, if the rows that were duplicated, where referenced as FK on another table, you will get some headache (well, you already have problems…) !!

MySQL Error ‘Unknown or incorrect time zone’ at a replica

Have you ever tried to do a MySQL replication at a different timezone/SO and got the following error message at SHOW REPLICA STATUS? Well, there are two ways to solve this, Error ‘Unknown or incorrect time zone: ‘America/Cuiaba” on query. Default database: ‘glpi_tiab’. Query: ‘BEGIN’

If you are on Linux, you can run the following and solve the problem (after a restart of the service).

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

On Windows, I found it to be easier if you download the timezone instead of build/import your own. You can download from here https://dev.mysql.com/downloads/timezones.html.

use mysql;
source /path/to/file/timezone_posix.sql;

Well, that’s it for today. See ya.

Retrieve the SQL Server Version from a Backup File

Have you ever been in the need to retrieve the SQL Server version that was used on a backup file?

Well, if for some weird reason that happens to you, the following SQL can help you. It won’t restore the database, it’ll just retrieve some basic info about it.

With that, you’ll have the DatabaseVersion (Internal Number Version) where the backup was from. You can also grab some useful information like the Server Name, Creation Date, and more.

RESTORE HEADERONLY FROM DISK = N'b:\backup\data_backup.bak'

Below we have a table of  Versions x Internal Number.

Version Internal Number Version Compat. Level
SQL Server 2019 895 – 904 150
SQL Server 2017 868 – 869 140
SQL Server 2016 852 130
SQL Server 2014 782 120
SQL Server 2012 706 110

I hope it helps!