ERPNext Conference 2019* ERPNext.com Blog

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


#1

Hi,

I’m trying to create a new site on a remote database and I’ve already followed this tutorial:
https://discuss.erpnext.com/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!


Error while creating a new site
Installation Experience (Mysql Installation Error ! #1171 - All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead)
#2

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!