SQL error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

4 posts / 0 new
Log in to post comments
Last post
Fri, 2018-01-19 05:04
#1
adolforolle
SQL error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead
Hello,
we installed CWIS 4.0.1 on Centos 7.3 Server with MySQL 5.7.21.
During installation process we receive an error message, making impossible to proceed.
Help needed. Thank you.
Best.
 
Beginning Installation Process...
Creating configuration file...
Creating .htaccess file...
Creating robots.txt file...
Testing database permissions...
Setting up session tables...
Setting up user tables...
Setting up application framework tables...
Setting up search engine tables...
Setting up event logging tables...
Setting up plugin manager tables...
Setting up RSS client tables...
Setting up CWIS tables...
Errors Encountered:
Database command failed:
CREATE TABLE IF NOT EXISTS UserPermsCache (
        ResourceId INT DEFAULT NULL,
        UserClass TEXT DEFAULT NULL,
        CanView BOOL DEFAULT FALSE,
        PRIMARY KEY (ResourceId, UserClass(32) ),
        INDEX Index_R (ResourceId),
        INDEX Index_U (UserClass(32))
);
(SQL error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead)
 
Please correct these problems and re-run the installation.
 
Top
Mon, 2018-01-22 09:25
chalpin
Re: SQL error: All parts of a PRIMARY KEY must be NOT NULL;...

This would appear to be one of the backward-incompatible changes that MySQL introduced in version 5.7.3 (see https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html). As far as I can tell, there's no MySQL setting availble to restore the old behavior.

I'll do some testing and get back to you with a workaround.

Top
Mon, 2018-01-22 10:28
chalpin
Re: SQL error: All parts of a PRIMARY KEY must be NOT NULL;...

To fix this problem, you'll need to edit two files. First, edit install/CreateTables.sql, changing line 500 from PRIMARY KEY (ResourceId, UserClass(32) ), to UNIQUE UIndex_RU (ResourceId, UserClass(32) ), (note that the line should end in a comma, as shown).

Next, edit install/CHECKSUMS and delete line 887, which will contain cdcbcdc71a69d40d378bce584ca9082f  install/CreateTables.sql.

In my CentOS 7 test VM, those changes were enough to get everything working.

The next release of CWIS will incorporate this fix. Thank you for reporting the issue!

Top
Wed, 2018-01-24 06:24
(Reply to #3)
adolforolle
Re: SQL error: All parts of a PRIMARY KEY must be NOT NULL;...

Hello chalpin!

I followed your instructions, problem solved.

Thank you very much. Best.

Adolfo

 

Top