|
Can you enable the general query log on the server and show the SQL executed?
What does
SHOW CREATE SEQUENCE user_account_id_seq
|
show?
|
|
It shows this:
MariaDB [useraccounttest]> show create sequence user_account_id_seq;
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
|
| user_account_id_seq | CREATE SEQUENCE `user_account_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB |
|
+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
|
|
|
Thanks,
SELECT PREVIOUS VALUE FOR user_account_id_seq;
|
would be useful too. Sorry should of asked earlier.
Still waiting on general query log from actions (https://mariadb.com/kb/en/library/general-query-log/ ).
|
|
The issue vanished and I'm unable to reproduce it any longer.
I don't think I have changed anything.
I had just reordered some lines in my my.cnf file:
[mysqld]
|
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
|
socket = /home/stephane/programs/install/mariadb/tmp/mariadb.sock
|
basedir = /home/stephane/programs/install/mariadb
|
datadir = /home/stephane/programs/install/mariadb/data
|
user = stephane
|
log-bin = /home/stephane/programs/install/mariadb/mariadb.bin.log
|
general_log = 1
|
general-log-file = /home/stephane/programs/install/mariadb/mariadb.log
|
log-error = /home/stephane/programs/install/mariadb/mariadb.error.log
|
long_query_time = 1
|
log-queries-not-using-indexes = 1
|
slow-query-log-file = /home/stephane/programs/install/mariadb/mariadb.slow.queries.log
|
innodb_file_per_table = 1
|
sync_binlog = 1
|
skip-name-resolve = 1
|
performance_schema = 0 # from ON for this IMPORT processing speed
|
character-set-server = utf8mb4
|
collation-server = utf8mb4_unicode_ci
|
wait_timeout = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
|
interactive_timeout = 28800 # same, but for interactive sessions
|
max_allowed_packet = 128M
|
net_write_timeout = 180
|
#skip-networking
|
#skip-host-cache
|
#bulk_insert_buffer_size = 1G
|
innodb_log_file_size = 128M
|
innodb_buffer_pool_size = 1G
|
innodb_buffer_pool_instances = 2
|
innodb_flush_log_at_trx_commit = 2
|
innodb_flush_method = O_DIRECT
|
max_connect_errors = 10 # why tolerate 100 hacker/cracker attempts?
|
thread_cache_size = 30 # from 4 to ensure threads ready to go
|
character-set-client-handshake = 0
|
innodb_io_capacity_max = 10000 # from 2000 default, for SSD vs HDD
|
innodb_io_capacity = 5000 # from 200 default, for SSD vs HDD
|
symbolic-links = 1 # to protect server from RANSOMWARE crowd
|
innodb_flush_neighbors = 0 # from 1, no need when SSD - no rotational delay
|
innodb_lru_scan_depth = 512 # from 1024 to conserve CPU see v8 refman
|
innodb_print_all_deadlocks = 1 # from OFF in error log for proactive correction
|
innodb_purge_threads = 4 # from 1 to speed purge processing
|
log_bin = 0 # from ON unless you need to invest the resources during import
|
log_warnings = 2 # from 1 for addl info on aborted_connection in error log
|
max_join_size = 1000000000 # from upper limit of 4 Billion rows
|
max_seeks_for_key = 32 # rather than allowing optimizer to search 4 Billion ndx's.
|
max_write_lock_count = 16 # to allow RD after nn lcks rather than 4 Billion
|
log_queries_not_using_indexes = 0 # not likely to look at these, for import
|
[client]
|
socket = /home/stephane/programs/install/mariadb/tmp/mariadb.sock
|
default-character-set = utf8mb4
|
[mysql]
|
default-character-set = utf8mb4
|
|
High chaparal...
|
|
The other
MariaDB [useraccounttest]> SELECT PREVIOUS VALUE FOR user_account_id_seq;
|
+----------------------------------------+
|
| PREVIOUS VALUE FOR user_account_id_seq |
|
+----------------------------------------+
|
| NULL |
|
+----------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
The issue is back ! I've been fighting the whole morning to get it back. And now it is.
12:33:31.779 [main] DEBUG jdbc.sqltiming - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
|
1. select user0_.id as id1_0_, user0_.version as version2_0_, user0_.confirmed_email as confirme3_0_,
|
user0_.email as email4_0_, user0_.firstname as firstnam5_0_, user0_.lastname as lastname6_0_,
|
user0_.password as password7_0_, user0_.password_salt as password8_0_, user0_.readable_password
|
as readable9_0_, user0_.work_phone as work_ph10_0_ from user_account user0_ where user0_.email='stephane@thalasoft.com'
|
{executed in 2 msec}
|
12:33:31.780 [main] DEBUG jdbc.resultset - 1. ResultSet.new ResultSet returned com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
|
12:33:31.780 [main] DEBUG jdbc.audit - 1. PreparedStatement.executeQuery() returned net.sf.log4jdbc.ResultSetSpy@13250132 com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
|
12:33:31.780 [main] DEBUG jdbc.resultset - 1. ResultSet.next() returned false com.zaxxer.hikari.pool.HikariProxyResultSet.next(HikariProxyResultSet.java:-1)
|
12:33:31.781 [main] DEBUG jdbc.resultset - 1. ResultSet.close() returned com.zaxxer.hikari.pool.HikariProxyResultSet.close(HikariProxyResultSet.java:-1)
|
12:33:31.781 [main] DEBUG jdbc.audit - 1. PreparedStatement.getMaxRows() returned 0 com.zaxxer.hikari.pool.HikariProxyPreparedStatement.getMaxRows(HikariProxyPreparedStatement.java:-1)
|
12:33:31.782 [main] DEBUG jdbc.audit - 1. PreparedStatement.getQueryTimeout() returned 0 com.zaxxer.hikari.pool.HikariProxyPreparedStatement.getQueryTimeout(HikariProxyPreparedStatement.java:-1)
|
12:33:31.782 [main] DEBUG jdbc.audit - 1. PreparedStatement.close() returned com.zaxxer.hikari.pool.ProxyStatement.close(ProxyStatement.java:76)
|
12:33:31.783 [main] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Found thread-bound EntityManager [SessionImpl(PersistenceContext[entityKeys=[],collectionKeys=[]];ActionQueue[insertions=ExecutableList{size=0} updates=ExecutableList{size=0} deletions=ExecutableList{size=0} orphanRemovals=ExecutableList{size=0} collectionCreations=ExecutableList{size=0} collectionRemovals=ExecutableList{size=0} collectionUpdates=ExecutableList{size=0} collectionQueuedOps=ExecutableList{size=0} unresolvedInsertDependencies=null])] for JPA transaction
|
12:33:31.783 [main] DEBUG org.springframework.orm.jpa.JpaTransactionManager - Participating in existing transaction
|
12:33:31.783 [main] DEBUG org.hibernate.event.internal.AbstractSaveEventListener - Generated identifier: -29, using strategy: org.hibernate.id.enhanced.SequenceStyleGenerator
|
|
|
Some of the general log:
101 Query /* mysql-connector-java-8.0.11 (Revision: 6d4eaa273bc181b4cf1c8ad0821a2227f116fedf) */SELECT @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
|
101 Query SET character_set_results = NULL
|
101 Query SET autocommit=1
|
101 Query SET sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
|
101 Query SET autocommit=1
|
180630 12:33:29 92 Query SET autocommit=0
|
92 Query select nextval(user_account_id_seq)
|
180630 12:33:30 92 Query select nextval(user_account_id_seq)
|
92 Query insert into user_account (version, confirmed_email, email, firstname, lastname, password, password_salt, readable_password, work_phone, id) values (0, 0, 'stephane@thalasoft.com', 'Stephane', 'Eybert', 'toto', '', null, null, 1)
|
92 Query commit
|
92 Query SET autocommit=1
|
92 Query SET autocommit=0
|
92 Query insert into user_account (version, confirmed_email, email, firstname, lastname, password, password_salt, readable_password, work_phone, id) values (0, 0, 'marceybert@yahoo.fr', 'Marc', 'Eybert', 'marcus', '', null, null, 2)
|
92 Query commit
|
92 Query SET autocommit=1
|
92 Query SET autocommit=0
|
92 Query select nextval(user_account_id_seq)
|
92 Query insert into user_account (version, confirmed_email, email, firstname, lastname, password, password_salt, readable_password, work_phone, id) values (0, 0, 'cyril@yahoo.es', 'Cyril', 'Eybert', 'cyr', '', null, null, -46)
|
92 Query rollback
|
92 Query SET autocommit=1
|
92 Query SET autocommit=0
|
92 Query delete from user_role
|
92 Query SHOW WARNINGS
|
92 Query delete from user_account
|
92 Query SHOW WARNINGS
|
92 Query commit
|
92 Query SET autocommit=1
|
92 Query SET autocommit=0
|
92 Query insert into user_account (version, confirmed_email, email, firstname, lastname, password, password_salt, readable_password, work_phone, id) values (0, 0, 'stephane@thalasoft.com', 'Stephane', 'Eybert', 'toto', '', null, null, -45)
|
92 Query rollback
|
92 Query SET autocommit=1
|
92 Query SET autocommit=0
|
92 Query delete from user_role
|
92 Query SHOW WARNINGS
|
92 Query delete from user_account
|
92 Query SHOW WARNINGS
|
92 Query commit
|
92 Query SET autocommit=1
|
92 Query SET autocommit=0
|
92 Query insert into user_account (version, confirmed_email, email, firstname, lastname, password, password_salt, readable_password, work_phone, id) values (0, 0, 'stephane@thalasoft.com', 'Stephane', 'Eybert', 'toto', '', null, null, -44)
|
92 Query rollback
|
|
|
I confirm the issue OCCURED with BOTH the above my.cnf file and this one below:
[mysqld]
|
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # This is strict mode: NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
|
socket = /home/stephane/programs/install/mariadb/tmp/mariadb.sock
|
basedir = /home/stephane/programs/install/mariadb
|
datadir = /home/stephane/programs/install/mariadb/data
|
user = stephane
|
log-bin = /home/stephane/programs/install/mariadb/mariadb.bin.log
|
general_log = 1
|
general-log-file = /home/stephane/programs/install/mariadb/mariadb.log
|
log-error = /home/stephane/programs/install/mariadb/mariadb.error.log
|
long_query_time = 1
|
log-queries-not-using-indexes = 1
|
slow-query-log-file = /home/stephane/programs/install/mariadb/mariadb.slow.queries.log
|
innodb_file_per_table = 1
|
sync_binlog = 1
|
skip-name-resolve = 1
|
performance_schema = 1
|
innodb_log_file_size = 256M
|
innodb_buffer_pool_size = 2G
|
innodb_buffer_pool_instances = 2
|
innodb_flush_log_at_trx_commit = 2
|
innodb_flush_method = O_DIRECT
|
thread_cache_size = 4
|
character-set-client-handshake = 0
|
character-set-server = utf8mb4
|
collation-server = utf8mb4_general_ci
|
wait_timeout = 28800 # amount of seconds during inactivity that MySQL will wait before it will close a connection on a non-interactive connection
|
interactive_timeout = 28800 # same, but for interactive sessions
|
max_allowed_packet = 128M
|
net_write_timeout = 180
|
[client]
|
socket = /home/stephane/programs/install/mariadb/tmp/mariadb.sock
|
default-character-set = utf8mb4
|
[mysql]
|
default-character-set = utf8mb4
|
[mysqladmin]
|
socket = /home/stephane/programs/install/mariadb/tmp/mariadb.sock
|
|
So I reckon the my.cnf file content here is probably not explaning the issue.
|
|
After dropping and creating the sequence, the issue appears again.
Here are the sequences content:
MariaDB [useraccounttest]> select * from user_account_id_seq;
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
| 1001 | 1 | 9223372036854775806 | 1 | 1 | 1000 | 0 | 0 |
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
1 row in set (0.001 sec)
|
|
MariaDB [useraccounttest]> select * from user_role_id_seq;
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
| next_not_cached_value | minimum_value | maximum_value | start_value | increment | cache_size | cycle_option | cycle_count |
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
| 1 | 1 | 9223372036854775806 | 1 | 1 | 1000 | 0 | 0 |
|
+-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
|
1 row in set (0.001 sec)
|
Sometimes the issue disappears, and does not appear again. Only after dropping and creating the sequences and tables does it appear again. The re-creation of the schema triggers the issue.
|
|
For the record, I'm using a MariaDB that I built from source:
mkdir /home/stephane/programs/install/mariadb/;
|
mkdir /home/stephane/programs/install/mariadb/data;
|
mkdir /home/stephane/programs/install/mariadb/var;
|
mkdir /home/stephane/programs/install/mariadb/etc;
|
mkdir /home/stephane/programs/install/mariadb/tmp
|
|
cd mariadb;
|
rm -f CMakeCache.txt;
|
cmake \
|
-DCMAKE_INSTALL_PREFIX=/home/stephane/programs/install/mariadb \
|
-DMYSQL_DATADIR=/home/stephane/programs/install/mariadb/data \
|
-DMYSQL_UNIX_ADDR=/home/stephane/programs/install/mariadb/tmp/mariadb.sock \
|
-DWITH_INNOBASE_STORAGE_ENGINE=1
|
|
make
|
|
make install
|
|
chmod 755 scripts/mysql_install_db;
|
scripts/mysql_install_db --defaults-file=/home/stephane/programs/install/mariadb/etc/my.cnf \
|
--no-defaults \
|
--explicit_defaults_for_timestamp \
|
--basedir=/home/stephane/programs/install/mariadb \
|
--datadir=/home/stephane/programs/install/mariadb/data \
|
--tmpdir=/home/stephane/programs/install/mariadb/tmp \
|
--lc-messages-dir=/home/stephane/programs/install/mariadb/share
|
|
cd;
|
ln -s /home/stephane/programs/install/mariadb/etc/my.cnf .my.cnf
|
|
export MYSQL_HOME=/home/stephane/programs/install/mariadb
|
export PATH=$PATH:$MYSQL_HOME/bin
|
|
|
|
|
For the record, the issue is also described on SO
|
|
stephaneeybert, it looks like you are inserting a negative number in the column id,
so server returns error "Out of range value for column 'id'".
MariaDB [test]> CREATE sequence user_account_id_seq;
|
Query OK, 0 rows affected (0.023 sec)
|
|
MariaDB [test]> CREATE TABLE user_account (
|
-> id bigint(20) unsigned NOT NULL DEFAULT (NEXT value FOR user_account_id_seq),
|
-> VERSION int(10) unsigned NOT NULL,
|
-> firstname varchar(255) NOT NULL,
|
-> lastname varchar(255) NOT NULL,
|
-> password varchar(100),
|
-> password_salt varchar(50),
|
-> readable_password varchar(50),
|
-> email varchar(50) NOT NULL,
|
-> confirmed_email bit(1) NOT NULL CHECK (confirmed_email IN (0, 1)),
|
-> work_phone varchar(20),
|
-> UNIQUE KEY email (email),
|
-> PRIMARY KEY (id));
|
Query OK, 0 rows affected (0.015 sec)
|
|
MariaDB [test]> insert into user_account (version, confirmed_email, email, firstname, lastname, password, password_salt, readable_password, work_phone, id)
|
-> values (0, 0, 'stephane@thalasoft.com', 'Stephane', 'Eybert', 'toto', '', null, null, -29);
|
ERROR 1264 (22003): Out of range value for column 'id' at row 1
|
|
|
@Alice Sherepa
Yes and that is the whole point of the issue. The negative number is given to me by the sequence. I'm not making it. If you look at the quoted console log output above in the issue description you will see it:
Generated identifier: -29,
|
|
Hi stephaneeybert, I am not sure, but my guess is that the issue is related to Hibernate, I found a lot of articles like "hibernate generates negative id", e.g. https://stackoverflow.com/questions/47896049/h2-sequence-is-generating-negative-no-in-column-jpa-spring
|
|
@Alice Sherepa
I had to add the
attribute so as to have my sequence mapping as
@SequenceGenerator(name = "id_generator", sequenceName = "user_account_id_seq", allocationSize = 10)
|
matching the sequence increment size I had created with the
create sequence user_account_id_seq start with 1 increment by 10;
|
statement to finally not have any negative sequence numbers anymore, this using MariaDB 10.3.7 in a Spring Boot 2.0.3.RELEASE version project.
|
|
Great that it worked.)
|
mariadb.issue-after-drop-create.log