Error Adding new User java.sql.SQLException: Field 'is_empty' doesn't have a default value


(Maurice) #1
  • SonarQube Docker Version 7.4
  • LDAP Plugin Version 2.2
  • Mysql Database

I’ve upgraded from SonarQube 6.7.6 to 7.4
All seems to be working as expected, except we cannot add new users to the system.

We get the same error, weather a new user tries to login via LDAP, or if we try and add a new user from the User Interface.

The error that appears in the logs file is as follows:

2018.12.06 16:56:52 ERROR web[AWd9h5hvyt4dZKI3ACF7][o.s.s.w.WebServiceEngine]   
Fail to process request http://sonar/api/users/create
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: Field 'is_empty' doesn't 
have a default value 
### The error may involve org.sonar.db.user.UserMapper.insert-Inline
### The error occurred while setting parameters
### SQL: insert into users (         uuid,         login,         name,         email,         active,         
scm_accounts,         external_id,         external_login,         external_identity_provider,         
user_local,         salt,         crypted_password,         hash_method,         is_root,          
onboarded,         homepage_type,         homepage_parameter,         organization_uuid,         
created_at,         updated_at         ) values (         ?,         ?,         ?,         ?,         ?,         ?,         
?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         ?,         
?,         ?         )
### Cause: java.sql.SQLException: Field 'is_empty' doesn't have a default value
    at 
 
 org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
    at 
org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)       
at 
org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    at com.sun.proxy.$Proxy54.insert(Unknown Source)
    at org.sonar.db.user.UserDao.insert(UserDao.java:153)
at org.sonar.server.user.UserUpdater.saveUser(UserUpdater.java:414)
at org.sonar.server.user.UserUpdater.createAndCommit(UserUpdater.java:102)
at org.sonar.server.user.ws.CreateAction.doHandle(CreateAction.java:149)
at org.sonar.server.user.ws.CreateAction.handle(CreateAction.java:132)
at org.sonar.server.ws.WebServiceEngine.execute(WebServiceEngine.java:110)
at org.sonar.server.ws.WebServiceFilter.doFilter(WebServiceFilter.java:88)

As an Update:
I Updated debugging to see what sql was failing and found the following:

2018.12.07 10:33:33 TRACE web[AWeIOZfZ4NCcelpjAAAk][sql] time=0ms | sql=select 
p.prop_key as "key", p.is_empty as empty, p.text_value as textValue, p.clob_value as 
clobValue, p.resource_id as resourceId, p.user_id as userId from properties p where    
p.prop_key=? and p.resource_id is null and p.user_id is null | 
params=sonar.onboardingTutorial.showToNewUsers

2018.12.07 10:33:33 TRACE web[AWeIOZfZ4NCcelpjAAAk][sql] time=13ms | sql=insert 
into users ( uuid, login, name, email, active, scm_accounts, external_id, external_login, 
external_identity_provider, user_local, salt, crypted_password, hash_method, is_root,  
onboarded, homepage_type, homepage_parameter, organization_uuid, created_at, 
updated_at ) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) | 
params=AWeIOsi0LuVE1L0rGIaS, testuser1, testuser1, 12, true, 12, testuser1, 
testuser1, sonarqube, true, 12, 
$2a$12$PKBjvEnH8/Wz.KinBSE1Gu2gUdOAVG6AaRlIoNt4J.cDYdwG8fRRq, BCRYPT, 
false, true, 12, 12, 12, 1544178813108, 1544178813108
2018.12.07 10:33:33 ERROR web[AWeIOZfZ4NCcelpjAAAk][o.s.s.w.WebServiceEngine] 
Fail to process request http://sonar/api/users/create
org.apache.ibatis.exceptions.PersistenceException:

The issue seems to be related to no default value being specified for a number of columns in some tables.
Updating the schema as follows resolved the issue for me.

mysql> ALTER TABLE properties MODIFY COLUMN is_empty tinyint(1) NOT NULL 
DEFAULT 0;
mysql> ALTER TABLE internal_properties MODIFY COLUMN is_empty tinyint(1) NOT 
NULL DEFAULT 0;

mysql> ALTER TABLE properties MODIFY COLUMN created_at bigint(20) NOT NULL 
DEFAULT 4000000000000000 ;

mysql>  ALTER TABLE users MODIFY COLUMN created_at bigint(20) NOT NULL 
DEFAULT 4000000000000000 ;

My question: Is this a bug in the upgrade process or have i missed something else.