MySQL 8 insert values into selected columns only

MySQLmysql-8.0

I have a huge insert sql script for insertion and the problem that this insertion was possible in MySQL 5.5 version, but not in Mysql 8:

CREATE TABLE IF NOT EXISTS `accounts` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NickName` varchar(24) CHARACTER SET cp1251 COLLATE cp1251_general_cs NOT NULL,
  `Password` varchar(64) NOT NULL DEFAULT 'NULL',
  `RegIP` varchar(16) NOT NULL DEFAULT 'No IP Adress',
  `OldIP` varchar(16) NOT NULL DEFAULT 'No IP Adress',
  `CheckIP1` varchar(16) NOT NULL DEFAULT 'No IP Adress',
  `RegData` varchar(16) NOT NULL DEFAULT 'No IP Adress',
  `Referal` varchar(24) NOT NULL DEFAULT 'No Referal',
  `Mail` varchar(50) NOT NULL DEFAULT 'No Mail Adress',
  `Telephone` varchar(20) NOT NULL DEFAULT 'No Telephone',
  `ForumName` varchar(30) NOT NULL DEFAULT 'No Forum Name',
  `Level` int(11) NULL,
  `Exp` int(11) NULL,
... more than 150 columns
 PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=8 ;

I've tried to disable all sql modes by setting it to: --sql-mode="NO_ENGINE_SUBSTITUTION". I checked then it in DB and was set, even after this it doesn't work. Before was too many modes like strict_trans_tables and etc.

Then the game returning in logs:

[ERROR] CMySQLQuery::Execute[OnPlayerRegisterMysql] – (error #1364) Field 'Exp' doesn't have a default value:

(Query: "INSERT INTO `accounts` (`ID`,`NickName`,`Password`,`RegIP`,`CheckIP1`,`RegData`,`Level`,`Sex`,`Age`,`Race`,`Referal`,`Mail`,`Telephone`,`Money`,`Bank`,`HouseKey`,`BizKey`,`From
`,`OOC`,`Snow`, `VirMoney`, `Online_status`,`Online`) VALUE (NULL,'Jonny_Sky', 'password', '0.0.0.0', 'No IP Adress', '2021-7-23', '1', '1', '0', '0'
, 'No Referal', 'No Mail Adress', 'No Telephone', '10000000', '0', '-1', '-1', '-1', '0', '1', '10000', '1001', '3')")

Is there any setting in MySQL 8 that will allow such insertions? As I mentioned everything was working in MySQL 5.5.

Update:
I can't understand what is the difference between version of MySQL 5.5,6 to 8. The working game script stops working after migrating to 8. Even I took another working dump (in believe if my was wrong), then same – Mysql 8 complaining for insert as abode indicated.
Moreover, I even changed all fields where was NOT NULL without default value to NULL. And even there, no luck.

To nbk: Thanks, it seems I probably missed that step, I didn't know. I've to learn about upgrade process. Then I will report back. In word, I was aware that upgrade has to be made to some of type of databases (Sybase), but was confused and believe not MySql 8. Also all other dumps (cms, websites) is working without upgrade on MySQL 8.

Best Answer

New problem raised which changed the question life: I've always used to start second instance of MySql 8 with command:

mysqld_safe --defaults-file=/etc/mysql/conf.d/game.cnf  --sql mode="NO_ENGINE_SUBSTITUTION"

And the problem that in Debian command line I can connect to this database with: mysql --socket=/var/run/mysqld/second_server.sock -u root -p When I connect from php Adminer (with localhost:3307) - I'am connected to localhost:3307, but the databases are from 3306, from the firs instance! In first instance also present the game db, an old database. So all that time the game was communicated with first, the wrong, old database! Even in game script the port 3307 was set.

So the Mysql 8 by some way has redirected the 3306 instance to 3307. From command line I can connect to needed database.

netstat -tlnp

tcp6 0 0 :::3306 :::* LISTEN 6149/mysqld

tcp6 0 0 :::3307 :::* LISTEN 7162/mysqld

As you can see the second instance is up, but why Mysql cloned 3306 databases to 3307? I would appreciate any help. I am trying now utilize the mysqld_multi, but this tool wont launch, saying - Wide character in print at /usr/bin/mysqld_multi line 684.

I need the second instance in order to set different configurations.

Update: It looks like I probably found an answer.

This is because if you specify "-h localhost" - it will connect you to MySQL socket, and --port will be ignored (no TCP/IP will be used, and hence the --port option has no meaning).

To connect to a different port, simply use an address which is not "localhost", but 127.0.0.1:

Credits to: https://lxadm.com/MySQL:_connecting_to_different_ports_on_localhost

So, I have to take care of 127.0.0.1:my port. Probably requires binding, etc.

So, finally it's working now, the lesson I've learned that localhost isn't the same as 127.0.0.1. I just made edit to *.cnf:

bind-address=127.0.0.1
Related Question