[Solved] All parts of a PRIMARY KEY must be NOT NULL on MySQL 5.7+

Hi,

I’m trying to create a new site on a remote database and I’ve already followed this tutorial:
https://discuss.frappe.io/t/remote-database-setup/22687/6, which used to work for me on ERPNext 11.

Now, I am doing everything same on ERPNext 12 but it’s giving me error now, which is:

frappe@ubuntu:~/frappe-bench$ bench new-site site1.remote --install-app erpnext --verbose
MySQL root password: 
Created user _a1947c1ffeceb24a
Created database _a1947c1ffeceb24a
Granted privileges to user _a1947c1ffeceb24a and database _a1947c1ffeceb24a
Starting database import...
Creating Database...
9.23KiB 0:00:00 [ 180MiB/s] [========================================================================>] 100%            
ERROR 1171 (42000) at line 174: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
Imported from database /home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/framework_mariadb.sql
Database not installed, this can due to lack of permission, or that the database name exists.
                        Check your MySQL root password, or use --force to reinstall
frappe@ubuntu:~/frappe-bench$ 

Can someone please help?

Thanks!

1 Like

Hi,

I fixed this. If anyone comes across this, for my case everything was good. It’s just that MySQL version 5.7 was installed on my remote server and it doesn’t allow PRIMARY Key Defaults to be NULL.

Solution was:

nano +174 frappe-bench/apps/frappe/frappe/database/mariadb/framework_mariadb.sql

and replace the contents of the query with:

CREATE TABLE `tabSeries` (
  `name` varchar(100),
  `current` int(10) NOT NULL DEFAULT 0,
  PRIMARY KEY(`name`)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED CHARACTER SET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

After that, it would be good to go.

Thanks!

5 Likes

Just ran into this on a fresh install. Is this fixed in the core installation? Took me a while to find this post and it would be great to add into the installation documentation.

2 Likes

Me too with Mysql server 8