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

Msheehan2712 · December 6, 2018, 9:14pm

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.

sns-seb · December 13, 2018, 10:30am

Hello,

First, what version of MySQL are your using?

Second, you shouldn’t tamper with SonarQube’s database.

If you did it to solve the current problem, chances are you did it in the past.
Since you are the only one reporting this error and we have a hard time understanding how such an error can occur and we can not reproduce it, chances are those previous actions are actually the cause of your current problem.

On top of this, the changes you made have several drawbacks and anyone reading this thread should not reproduce them:

you introduced default values on columns which do not have any. And as a general rule, there is no default values in SonarQube DB. We do not use them because they hide problems in the code base and split the logic across multiple layer of the stack and we don’t want that
you basically hide the dust under the carpet. Indeed you do not have a problem anymore, but we do not know what the problem was in the first place. On top of this you made changes to several tables, so we don’t know which one actually had a problem.
why did you even alter the created_at columns?

You broke the general contract that the database should be considered as a black box and not directly queried nor modified. The schema you are running SQ on is basically unknown. It’s hard to help you in these conditions.

However, some investigation could still be possible.

Could you query tables properties and internal_properties for rows will null value in column is_empty and share the result?

Cheers,

Msheehan2712 · December 13, 2018, 3:14pm

Hi Sebastien,

Thanks for replying to this call.

The version of the mysql database is below:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.23                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.23-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.04 sec)


mysql>

This has the default STRICT_TRANS_TABLES mode enabled.
I dont know if this setting is somehow related to the problem.
I havent been able to get access to a server with this setting turned off yet.

Totally agree about you comments regarding updating the database.

It is not my intention to do so. I’ve updated the tables detailed simply
as a mechanism to get some more information about the underlying issue, which seems
to be related to default values in the database.

The created_at column was modified as the application also complained about the lack of a
default value for this column. This is only a test system.

Output from your suggested queries is below:

mysql> select count(*) from properties where is_empty is NULL;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)


mysql> select count(*) from internal_properties where is_empty is NULL;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.04 sec)

Msheehan2712 · January 7, 2019, 6:07pm

Hi,
I’ve spent some time looking at this issue and have made some progress.

The issue appears after upgrading from version 5.6.7 to 6.7.6
The issue does NOT appear if i install a new installation of Version 6.7.6

After comparing all the plugins versions, database settings and tables between the fresh install ( which worked) and the upgraded instance ( which didn’t ), the only difference i could find was the following trigger

MySQL [SonarQube]>  show triggers;
+-----------+--------+-------+-------------------------------------------------------------------------------------------------------------- 
---------------------------+--------+------------------------+---------------------+------------------+---------------      -------+------- 
--------------+--------------------+
 | Trigger   | Event  | Table | Statement                                                                                                                                      
 | Timing | Created                | sql_mode            | Definer          | character_set_      client | 
 collation_connection | Database Collation |
+-----------+--------+-------+-------------------------------------------------------------------------------------------------------------   
--------------------------+--------+------------------------+---------------------+------------------+---------------      -------+--------
-------------+--------------------+
 | ins_notif | INSERT | users | INSERT INTO properties (prop_key, text_value, user_id) values 
 ("notification.ChangesOnMyIssue.EmailNotificationChannel","true", NEW.id) | AFTER  | 2019-01-04 
 20:09:14.27 | STRICT_TRANS_TABLES | userAppliSONAR@% | latin1                     | latin1_swedish_ci    | 
 utf8_general_ci    |
 +-----------+--------+-------+-------------------------------------------------------------------------------------------------------------
---------------------------+--------+------------------------+---------------------+------------------+---------------      -------+------ 
--------------+--------------------+
1 row in set (0.04 sec)

MySQL [SonarQube]>

As a test, i dropped this trigger and then i was able to successfully able to add a new user.

My Questions: Is this ins_notif trigger created by SonarQube?
Is it ok to just drop this trigger, or should it be modified to make it work after the upgrade to 6.7

Topic		Replies	Views
I have deleted admin account and locked myself out -- Please help! SonarQube Server / Community Build sonarqube	4	905	October 17, 2019
Different login and LDAP_default for few LDAP users SonarQube Server / Community Build sonarqube , ldap	2	351	February 8, 2024
LDAP Users Reset to Default SonarQube Server / Community Build sonarqube , ldap	3	1346	July 19, 2018
User Permission Gets Reset to Default SonarQube Server / Community Build sonarqube	2	1266	June 15, 2020
Mysql upgraded from 5.7 to 8.0 and Sonarqube 7.8 not starting GUI SonarQube Server / Community Build sonarqube	2	360	December 26, 2023

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

Related topics