Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.13, 10.2(EOL), 10.3(EOL)
-
None
-
linux, n.a.
Description
CREATE TABLE Employees ( |
EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY |
, FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) |
, LastName VARCHAR(40) NOT NULL |
, Title VARCHAR(50) NOT NULL CHECK (Title != 'God') |
, DeptID SMALLINT UNSIGNED NOT NULL |
, ManagerID INT UNSIGNED NULL |
, Salary DECIMAL(38) DEFAULT 0.0 CHECK (Salary < 10000.0) |
, CONSTRAINT c_deptid CHECK (DeptID > 0) |
);
|
root@ma-102-master [test] SQL> INSERT INTO Employees VALUES |
-> (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16,NULL, 9999) |
-> ,(273, 'Brian', 'Welcker', 'Vice President of Sales',3,1, 8000) |
-> ;
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Ken' |
root@ma-102-master [test] SQL> INSERT INTO Employees VALUES (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16,NULL, 9999) ; |
Query OK, 1 row affected, 1 warning (0.00 sec)
|
|
|
root@ma-102-master [test] SQL> show warnings;
|
+-------+------+-----------------------------------------+ |
| Level | Code | Message | |
+-------+------+-----------------------------------------+ |
| Error | 1292 | Truncated incorrect DOUBLE value: 'Ken' | |
+-------+------+-----------------------------------------+ |
1 row in set (0.00 sec) |
This works without check constraints and I do not see why the insert should fail. And IF, at least the error message is nonsense...
Docu states that most deterministic functions work.
Attachments
Activity
If you mean that INSERT produces an error when you attempt to insert two rows, but only a warning when you attempt to insert one – I would agree, except that I can't reproduce it. For me, both INSERTs produce errors in strict mode and warnings in non-strict mode, quite as expected. Is it possible that you changed sql_mode between those two attempts? If not, please paste or attach your cnf file(s), or better still, unabridged output from the same client session – failed INSERT, succeeded INSERT, and the output of SHOW VARIABLES.
No, I have NOT changed sql_mode in between.
As you wish, madame, a complete test case:
oli@laptop4:~ [mariadb-102, 3324]> sudo su - mysql
|
mysql@laptop4:~ [mariadb-102, 3324]> installMyEnv
|
mysql@laptop4:~ [mariadb-102, 3324]> source ~/.bash_profile
|
mysql@laptop4:~ [mariadb-102, 3324]> ma14561
|
mysql@laptop4:~ [ma14561, 3350]> start
|
mysql@laptop4:~ [ma14561, 3350]> mysql -uroot
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 10
|
Server version: 10.2.13-MariaDB-log MariaDB Server
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
root@ma14561 [(none)] SQL> use test |
Database changed |
root@ma14561 [test] SQL> CREATE TABLE Employees ( |
-> EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY |
-> , FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) |
-> , LastName VARCHAR(40) NOT NULL |
-> , Title VARCHAR(50) NOT NULL CHECK (Title != 'God') |
-> , DeptID SMALLINT UNSIGNED NOT NULL |
-> , ManagerID INT UNSIGNED NULL |
-> , Salary DECIMAL(38) DEFAULT 0.0 CHECK (Salary < 10000.0) |
-> , CONSTRAINT c_deptid CHECK (DeptID > 0) |
-> );
|
Query OK, 0 rows affected (0.06 sec) |
root@ma14561 [test] SQL> INSERT INTO Employees VALUES |
-> (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16,NULL, 9999) |
-> , (273, 'Brian', 'Welcker', 'Vice President of Sales',3,1, 8000) |
-> ;
|
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Ken' |
root@ma14561 [test] SQL> INSERT INTO Employees VALUES (1, 'Ken', 'Sánchez', 'Chief Executive Officer',16,NULL, 9999); |
Query OK, 1 row affected, 1 warning (0.00 sec)
|
|
|
root@ma14561 [test] SQL> show warnings;
|
+-------+------+-----------------------------------------+ |
| Level | Code | Message | |
+-------+------+-----------------------------------------+ |
| Error | 1292 | Truncated incorrect DOUBLE value: 'Ken' | |
+-------+------+-----------------------------------------+ |
1 row in set (0.00 sec) |
and SHOW VARIABLES;
root@ma14561 [(none)] SQL> pager cut -b-120
|
PAGER set to 'cut -b-120'
|
root@ma14561 [(none)] SQL> SHOW VARIABLES;
|
+--------------------------------------------------------+--------------------------------------------------------------
|
| Variable_name | Value
|
+--------------------------------------------------------+--------------------------------------------------------------
|
| aria_block_size | 8192
|
| aria_checkpoint_interval | 30
|
| aria_checkpoint_log_activity | 1048576
|
| aria_encrypt_tables | OFF
|
| aria_force_start_after_recovery_failures | 0
|
| aria_group_commit | none
|
| aria_group_commit_interval | 0
|
| aria_log_file_size | 1073741824
|
| aria_log_purge_type | immediate
|
| aria_max_sort_file_size | 9223372036853727232
|
| aria_page_checksum | ON
|
| aria_pagecache_age_threshold | 300
|
| aria_pagecache_buffer_size | 134217728
|
| aria_pagecache_division_limit | 100
|
| aria_pagecache_file_hash_size | 512
|
| aria_recover_options | BACKUP,QUICK
|
| aria_repair_threads | 1
|
| aria_sort_buffer_size | 268434432
|
| aria_stats_method | nulls_unequal
|
| aria_sync_log_dir | NEWFILE
|
| aria_used_for_temp_tables | ON
|
| auto_increment_increment | 1
|
| auto_increment_offset | 1
|
| autocommit | ON
|
| automatic_sp_privileges | ON
|
| back_log | 151
|
| basedir | /home/mysql/product/mariadb-10.2.13
|
| big_tables | OFF
|
| binlog_annotate_row_events | ON
|
| binlog_cache_size | 1048576
|
| binlog_checksum | CRC32
|
| binlog_commit_wait_count | 0
|
| binlog_commit_wait_usec | 100000
|
| binlog_direct_non_transactional_updates | OFF
|
| binlog_format | ROW
|
| binlog_optimize_thread_scheduling | ON
|
| binlog_row_image | FULL
|
| binlog_stmt_cache_size | 1048576
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | latin1
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | /home/mysql/product/mariadb-10.2.13-linux-glibc_214-x86_64/sh
|
| check_constraint_checks | ON
|
| collation_connection | utf8_general_ci
|
| collation_database | latin1_swedish_ci
|
| collation_server | latin1_swedish_ci
|
| completion_type | NO_CHAIN
|
| concurrent_insert | AUTO
|
| connect_timeout | 10
|
| datadir | /home/mysql/database/ma14561/data/
|
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| deadlock_search_depth_long | 15
|
| deadlock_search_depth_short | 4
|
| deadlock_timeout_long | 50000000
|
| deadlock_timeout_short | 10000
|
| debug_no_thread_alarm | OFF
|
| default_master_connection |
|
| default_regex_flags |
|
| default_storage_engine | InnoDB
|
| default_tmp_storage_engine |
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| div_precision_increment | 4
|
| encrypt_binlog | OFF
|
| encrypt_tmp_disk_tables | OFF
|
| encrypt_tmp_files | OFF
|
| enforce_storage_engine |
|
| error_count | 0
|
| event_scheduler | OFF
|
| expensive_subquery_limit | 100
|
| expire_logs_days | 5
|
| explicit_defaults_for_timestamp | OFF
|
| external_user |
|
| extra_max_connections | 1
|
| extra_port | 0
|
| flush | OFF
|
| flush_time | 0
|
| foreign_key_checks | ON
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 4
|
| ft_query_expansion_limit | 20
|
| ft_stopword_file | (built-in)
|
| general_log | OFF
|
| general_log_file | /home/mysql/database/ma14561/log/laptop4_ma14561_general.log
|
| group_concat_max_len | 1048576
|
| gtid_binlog_pos | 0-3350-2
|
| gtid_binlog_state | 0-3350-2
|
| gtid_current_pos | 0-3350-2
|
| gtid_domain_id | 0
|
| gtid_ignore_duplicates | OFF
|
| gtid_seq_no | 0
|
| gtid_slave_pos |
|
| gtid_strict_mode | OFF
|
| have_compress | YES
|
| have_crypt | YES
|
| have_dynamic_loading | YES
|
| have_geometry | YES
|
| have_openssl | NO
|
| have_profiling | YES
|
| have_query_cache | YES
|
| have_rtree_keys | YES
|
| have_ssl | DISABLED
|
| have_symlink | YES
|
| histogram_size | 0
|
| histogram_type | SINGLE_PREC_HB
|
| host_cache_size | 628
|
| hostname | laptop4
|
| identity | 0
|
| ignore_builtin_innodb | OFF
|
| ignore_db_dirs |
|
| in_transaction | 0
|
| init_connect |
|
| init_file |
|
| init_slave |
|
| innodb_adaptive_flushing | ON
|
| innodb_adaptive_flushing_lwm | 10.000000
|
| innodb_adaptive_hash_index | ON
|
| innodb_adaptive_hash_index_partitions | 8
|
| innodb_adaptive_hash_index_parts | 8
|
| innodb_adaptive_max_sleep_delay | 150000
|
| innodb_autoextend_increment | 64
|
| innodb_autoinc_lock_mode | 1
|
| innodb_background_scrub_data_check_interval | 3600
|
| innodb_background_scrub_data_compressed | OFF
|
| innodb_background_scrub_data_interval | 604800
|
| innodb_background_scrub_data_uncompressed | OFF
|
| innodb_buf_dump_status_frequency | 0
|
| innodb_buffer_pool_chunk_size | 134217728
|
| innodb_buffer_pool_dump_at_shutdown | ON
|
| innodb_buffer_pool_dump_now | OFF
|
| innodb_buffer_pool_dump_pct | 25
|
| innodb_buffer_pool_filename | ib_buffer_pool
|
| innodb_buffer_pool_instances | 1
|
| innodb_buffer_pool_load_abort | OFF
|
| innodb_buffer_pool_load_at_startup | ON
|
| innodb_buffer_pool_load_now | OFF
|
| innodb_buffer_pool_populate | OFF
|
| innodb_buffer_pool_size | 134217728
|
| innodb_change_buffer_max_size | 25
|
| innodb_change_buffering | all
|
| innodb_checksum_algorithm | crc32
|
| innodb_checksums | ON
|
| innodb_cleaner_lsn_age_factor | DEPRECATED
|
| innodb_cmp_per_index_enabled | OFF
|
| innodb_commit_concurrency | 0
|
| innodb_compression_algorithm | zlib
|
| innodb_compression_default | OFF
|
| innodb_compression_failure_threshold_pct | 5
|
| innodb_compression_level | 6
|
| innodb_compression_pad_pct_max | 50
|
| innodb_concurrency_tickets | 5000
|
| innodb_corrupt_table_action | deprecated
|
| innodb_data_file_path | ibdata1:12M:autoextend
|
| innodb_data_home_dir |
|
| innodb_deadlock_detect | ON
|
| innodb_default_encryption_key_id | 1
|
| innodb_default_row_format | dynamic
|
| innodb_defragment | OFF
|
| innodb_defragment_fill_factor | 0.900000
|
| innodb_defragment_fill_factor_n_recs | 20
|
| innodb_defragment_frequency | 40
|
| innodb_defragment_n_pages | 7
|
| innodb_defragment_stats_accuracy | 0
|
| innodb_disable_sort_file_cache | OFF
|
| innodb_disallow_writes | OFF
|
| innodb_doublewrite | ON
|
| innodb_empty_free_list_algorithm | DEPRECATED
|
| innodb_encrypt_log | OFF
|
| innodb_encrypt_tables | OFF
|
| innodb_encryption_rotate_key_age | 1
|
| innodb_encryption_rotation_iops | 100
|
| innodb_encryption_threads | 0
|
| innodb_fake_changes | OFF
|
| innodb_fast_shutdown | 1
|
| innodb_fatal_semaphore_wait_threshold | 600
|
| innodb_file_format | Barracuda
|
| innodb_file_format_check | ON
|
| innodb_file_format_max | Barracuda
|
| innodb_file_per_table | ON
|
| innodb_fill_factor | 100
|
| innodb_flush_log_at_timeout | 1
|
| innodb_flush_log_at_trx_commit | 2
|
| innodb_flush_method |
|
| innodb_flush_neighbors | 1
|
| innodb_flush_sync | ON
|
| innodb_flushing_avg_loops | 30
|
| innodb_force_load_corrupted | OFF
|
| innodb_force_primary_key | OFF
|
| innodb_force_recovery | 0
|
| innodb_foreground_preflush | DEPRECATED
|
| innodb_ft_aux_table |
|
| innodb_ft_cache_size | 8000000
|
| innodb_ft_enable_diag_print | OFF
|
| innodb_ft_enable_stopword | ON
|
| innodb_ft_max_token_size | 84
|
| innodb_ft_min_token_size | 3
|
| innodb_ft_num_word_optimize | 2000
|
| innodb_ft_result_cache_limit | 2000000000
|
| innodb_ft_server_stopword_table |
|
| innodb_ft_sort_pll_degree | 2
|
| innodb_ft_total_cache_size | 640000000
|
| innodb_ft_user_stopword_table |
|
| innodb_idle_flush_pct | 100
|
| innodb_immediate_scrub_data_uncompressed | OFF
|
| innodb_instrument_semaphores | OFF
|
| innodb_io_capacity | 200
|
| innodb_io_capacity_max | 2000
|
| innodb_kill_idle_transaction | 0
|
| innodb_large_prefix | ON
|
| innodb_lock_schedule_algorithm | vats
|
| innodb_lock_wait_timeout | 50
|
| innodb_locking_fake_changes | OFF
|
| innodb_locks_unsafe_for_binlog | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_arch_expire_sec | 0
|
| innodb_log_archive | OFF
|
| innodb_log_block_size | 0
|
| innodb_log_buffer_size | 8388608
|
| innodb_log_checksum_algorithm | DEPRECATED
|
| innodb_log_checksums | ON
|
| innodb_log_compressed_pages | ON
|
| innodb_log_file_size | 268435456
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | ./
|
| innodb_log_write_ahead_size | 8192
|
| innodb_lru_scan_depth | 1024
|
| innodb_max_bitmap_file_size | 0
|
| innodb_max_changed_pages | 0
|
| innodb_max_dirty_pages_pct | 75.000000
|
| innodb_max_dirty_pages_pct_lwm | 0.000000
|
| innodb_max_purge_lag | 0
|
| innodb_max_purge_lag_delay | 0
|
| innodb_max_undo_log_size | 10485760
|
| innodb_mirrored_log_groups | 0
|
| innodb_monitor_disable |
|
| innodb_monitor_enable |
|
| innodb_monitor_reset |
|
| innodb_monitor_reset_all |
|
| innodb_mtflush_threads | 8
|
| innodb_numa_interleave | OFF
|
| innodb_old_blocks_pct | 37
|
| innodb_old_blocks_time | 1000
|
| innodb_online_alter_log_max_size | 134217728
|
| innodb_open_files | 2000
|
| innodb_optimize_fulltext_only | OFF
|
| innodb_page_cleaners | 1
|
| innodb_page_size | 16384
|
| innodb_prefix_index_cluster_optimization | OFF
|
| innodb_print_all_deadlocks | ON
|
| innodb_purge_batch_size | 300
|
| innodb_purge_rseg_truncate_frequency | 128
|
| innodb_purge_threads | 4
|
| innodb_random_read_ahead | OFF
|
| innodb_read_ahead_threshold | 56
|
| innodb_read_io_threads | 4
|
| innodb_read_only | OFF
|
| innodb_replication_delay | 0
|
| innodb_rollback_on_timeout | OFF
|
| innodb_rollback_segments | 128
|
| innodb_sched_priority_cleaner | 0
|
| innodb_scrub_log | OFF
|
| innodb_scrub_log_speed | 256
|
| innodb_show_locks_held | 0
|
| innodb_show_verbose_locks | 0
|
| innodb_sort_buffer_size | 1048576
|
| innodb_spin_wait_delay | 6
|
| innodb_stats_auto_recalc | ON
|
| innodb_stats_include_delete_marked | OFF
|
| innodb_stats_method | nulls_equal
|
| innodb_stats_modified_counter | 0
|
| innodb_stats_on_metadata | OFF
|
| innodb_stats_persistent | ON
|
| innodb_stats_persistent_sample_pages | 20
|
| innodb_stats_sample_pages | 8
|
| innodb_stats_traditional | ON
|
| innodb_stats_transient_sample_pages | 8
|
| innodb_status_output | OFF
|
| innodb_status_output_locks | OFF
|
| innodb_strict_mode | ON
|
| innodb_support_xa | ON
|
| innodb_sync_array_size | 1
|
| innodb_sync_spin_loops | 30
|
| innodb_table_locks | ON
|
| innodb_temp_data_file_path | ibtmp1:12M:autoextend
|
| innodb_thread_concurrency | 0
|
| innodb_thread_sleep_delay | 10000
|
| innodb_tmpdir |
|
| innodb_track_changed_pages | OFF
|
| innodb_track_redo_log_now | OFF
|
| innodb_undo_directory | ./
|
| innodb_undo_log_truncate | OFF
|
| innodb_undo_logs | 128
|
| innodb_undo_tablespaces | 0
|
| innodb_use_atomic_writes | ON
|
| innodb_use_fallocate | OFF
|
| innodb_use_global_flush_log_at_trx_commit | OFF
|
| innodb_use_mtflush | OFF
|
| innodb_use_native_aio | ON
|
| innodb_use_stacktrace | OFF
|
| innodb_use_trim | ON
|
| innodb_version | 5.7.21
|
| innodb_write_io_threads | 4
|
| insert_id | 0
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| join_buffer_space_limit | 2097152
|
| join_cache_level | 2
|
| keep_files_on_create | OFF
|
| key_buffer_size | 8388608
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| key_cache_file_hash_size | 512
|
| key_cache_segments | 0
|
| large_files_support | ON
|
| large_page_size | 0
|
| large_pages | OFF
|
| last_gtid |
|
| last_insert_id | 0
|
| lc_messages | en_US
|
| lc_messages_dir |
|
| lc_time_names | en_US
|
| license | GPL
|
| local_infile | ON
|
| lock_wait_timeout | 86400
|
| locked_in_memory | OFF
|
| log_bin | ON
|
| log_bin_basename | /home/mysql/database/ma14561/binlog/laptop4_ma14561_binlog
|
| log_bin_compress | OFF
|
| log_bin_compress_min_len | 256
|
| log_bin_index | /home/mysql/database/ma14561/binlog/laptop4_ma14561_binlog.in
|
| log_bin_trust_function_creators | OFF
|
| log_error | /home/mysql/database/ma14561/log/laptop4_ma14561_error.log
|
| log_output | FILE
|
| log_queries_not_using_indexes | OFF
|
| log_slave_updates | ON
|
| log_slow_admin_statements | ON
|
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cac
|
| log_slow_rate_limit | 1
|
| log_slow_slave_statements | ON
|
| log_slow_verbosity |
|
| log_tc_size | 24576
|
| log_warnings | 2
|
| long_query_time | 0.500000
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 0
|
| master_verify_checksum | OFF
|
| max_allowed_packet | 16777216
|
| max_binlog_cache_size | 18446744073709547520
|
| max_binlog_size | 134217728
|
| max_binlog_stmt_cache_size | 18446744073709547520
|
| max_connect_errors | 100
|
| max_connections | 505
|
| max_delayed_threads | 20
|
| max_digest_length | 1024
|
| max_error_count | 64
|
| max_heap_table_size | 67108864
|
| max_insert_delayed_threads | 20
|
| max_join_size | 18446744073709551615
|
| max_length_for_sort_data | 1024
|
| max_long_data_size | 16777216
|
| max_prepared_stmt_count | 16382
|
| max_recursive_iterations | 4294967295
|
| max_relay_log_size | 134217728
|
| max_seeks_for_key | 4294967295
|
| max_session_mem_used | 9223372036854775807
|
| max_sort_length | 1024
|
| max_sp_recursion_depth | 0
|
| max_statement_time | 0.000000
|
| max_tmp_tables | 32
|
| max_user_connections | 500
|
| max_write_lock_count | 4294967295
|
| metadata_locks_cache_size | 1024
|
| metadata_locks_hash_instances | 8
|
| min_examined_row_limit | 100
|
| mrr_buffer_size | 262144
|
| multi_range_count | 256
|
| myisam_block_size | 1024
|
| myisam_data_pointer_size | 6
|
| myisam_max_sort_file_size | 9223372036853727232
|
| myisam_mmap_size | 18446744073709551615
|
| myisam_recover_options | BACKUP,FORCE
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 134216704
|
| myisam_stats_method | NULLS_UNEQUAL
|
| myisam_use_mmap | OFF
|
| mysql56_temporal_format | ON
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| old | OFF
|
| old_alter_table | OFF
|
| old_mode |
|
| old_passwords | OFF
|
| open_files_limit | 16384
|
| optimizer_prune_level | 1
|
| optimizer_search_depth | 62
|
| optimizer_selectivity_sampling_limit | 100
|
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on
|
| optimizer_use_condition_selectivity | 1
|
| performance_schema | OFF
|
| performance_schema_accounts_size | -1
|
| performance_schema_digests_size | -1
|
| performance_schema_events_stages_history_long_size | -1
|
| performance_schema_events_stages_history_size | -1
|
| performance_schema_events_statements_history_long_size | -1
|
| performance_schema_events_statements_history_size | -1
|
| performance_schema_events_waits_history_long_size | -1
|
| performance_schema_events_waits_history_size | -1
|
| performance_schema_hosts_size | -1
|
| performance_schema_max_cond_classes | 80
|
| performance_schema_max_cond_instances | -1
|
| performance_schema_max_digest_length | 1024
|
| performance_schema_max_file_classes | 50
|
| performance_schema_max_file_handles | 32768
|
| performance_schema_max_file_instances | -1
|
| performance_schema_max_mutex_classes | 200
|
| performance_schema_max_mutex_instances | -1
|
| performance_schema_max_rwlock_classes | 40
|
| performance_schema_max_rwlock_instances | -1
|
| performance_schema_max_socket_classes | 10
|
| performance_schema_max_socket_instances | -1
|
| performance_schema_max_stage_classes | 150
|
| performance_schema_max_statement_classes | 188
|
| performance_schema_max_table_handles | -1
|
| performance_schema_max_table_instances | -1
|
| performance_schema_max_thread_classes | 50
|
| performance_schema_max_thread_instances | -1
|
| performance_schema_session_connect_attrs_size | -1
|
| performance_schema_setup_actors_size | 100
|
| performance_schema_setup_objects_size | 100
|
| performance_schema_users_size | -1
|
| pid_file | /home/mysql/database/ma14561/data/laptop4.pid
|
| plugin_dir | /home/mysql/product/mariadb-10.2.13-linux-glibc_214-x86_64/li
|
| plugin_maturity | unknown
|
| port | 3350
|
| preload_buffer_size | 32768
|
| profiling | OFF
|
| profiling_history_size | 15
|
| progress_report_time | 5
|
| protocol_version | 10
|
| proxy_user |
|
| pseudo_slave_mode | OFF
|
| pseudo_thread_id | 11
|
| query_alloc_block_size | 16384
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 1048576
|
| query_cache_strip_comments | OFF
|
| query_cache_type | OFF
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 24576
|
| rand_seed1 | 863078148
|
| rand_seed2 | 77094069
|
| range_alloc_block_size | 4096
|
| read_binlog_speed_limit | 0
|
| read_buffer_size | 131072
|
| read_only | OFF
|
| read_rnd_buffer_size | 262144
|
| relay_log |
|
| relay_log_basename |
|
| relay_log_index |
|
| relay_log_info_file | relay-log.info
|
| relay_log_purge | ON
|
| relay_log_recovery | OFF
|
| relay_log_space_limit | 0
|
| replicate_annotate_row_events | ON
|
| replicate_do_db |
|
| replicate_do_table |
|
| replicate_events_marked_for_skip | REPLICATE
|
| replicate_ignore_db |
|
| replicate_ignore_table |
|
| replicate_wild_do_table |
|
| replicate_wild_ignore_table |
|
| report_host |
|
| report_password |
|
| report_port | 3350
|
| report_user |
|
| rowid_merge_buff_size | 8388608
|
| secure_auth | ON
|
| secure_file_priv |
|
| server_id | 3350
|
| session_track_schema | ON
|
| session_track_state_change | OFF
|
| session_track_system_variables |
|
| session_track_transaction_info | OFF
|
| skip_external_locking | ON
|
| skip_name_resolve | OFF
|
| skip_networking | OFF
|
| skip_parallel_replication | OFF
|
| skip_replication | OFF
|
| skip_show_database | OFF
|
| slave_compressed_protocol | OFF
|
| slave_ddl_exec_mode | IDEMPOTENT
|
| slave_domain_parallel_threads | 0
|
| slave_exec_mode | STRICT
|
| slave_load_tmpdir | /tmp
|
| slave_max_allowed_packet | 1073741824
|
| slave_net_timeout | 60
|
| slave_parallel_max_queued | 131072
|
| slave_parallel_mode | conservative
|
| slave_parallel_threads | 0
|
| slave_parallel_workers | 0
|
| slave_run_triggers_for_rbr | NO
|
| slave_skip_errors | OFF
|
| slave_sql_verify_checksum | ON
|
| slave_transaction_retries | 10
|
| slave_type_conversions |
|
| slow_launch_time | 2
|
| slow_query_log | OFF
|
| slow_query_log_file | /home/mysql/database/ma14561/log/laptop4_ma14561_slow.log
|
| socket | /var/run/mysqld/mysql-3350.sock
|
| sort_buffer_size | 2097152
|
| sql_auto_is_null | OFF
|
| sql_big_selects | ON
|
| sql_buffer_result | OFF
|
| sql_log_bin | ON
|
| sql_log_off | OFF
|
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE
|
| sql_notes | ON
|
| sql_quote_show_create | ON
|
| sql_safe_updates | OFF
|
| sql_select_limit | 18446744073709551615
|
| sql_slave_skip_counter | 0
|
| sql_warnings | OFF
|
| ssl_ca |
|
| ssl_capath |
|
| ssl_cert |
|
| ssl_cipher |
|
| ssl_crl |
|
| ssl_crlpath |
|
| ssl_key |
|
| standard_compliant_cte | ON
|
| storage_engine | InnoDB
|
| stored_program_cache | 256
|
| strict_password_validation | ON
|
| sync_binlog | 0
|
| sync_frm | ON
|
| sync_master_info | 10000
|
| sync_relay_log | 10000
|
| sync_relay_log_info | 10000
|
| system_time_zone | CET
|
| table_definition_cache | 1400
|
| table_open_cache | 2000
|
| table_open_cache_instances | 16
|
| thread_cache_size | 505
|
| thread_concurrency | 10
|
| thread_handling | one-thread-per-connection
|
| thread_pool_idle_timeout | 60
|
| thread_pool_max_threads | 65536
|
| thread_pool_oversubscribe | 3
|
| thread_pool_prio_kickup_timer | 1000
|
| thread_pool_priority | auto
|
| thread_pool_size | 4
|
| thread_pool_stall_limit | 500
|
| thread_stack | 299008
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| timestamp | 1520280725.464666
|
| tmp_disk_table_size | 18446744073709551615
|
| tmp_memory_table_size | 33554432
|
| tmp_table_size | 33554432
|
| tmpdir | /tmp
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| tx_read_only | OFF
|
| unique_checks | ON
|
| updatable_views_with_limit | YES
|
| use_stat_tables | NEVER
|
| userstat | OFF
|
| version | 10.2.13-MariaDB-log
|
| version_comment | MariaDB Server
|
| version_compile_machine | x86_64
|
| version_compile_os | Linux
|
| version_malloc_library | system
|
| version_ssl_library | YaSSL 2.4.4
|
| wait_timeout | 28800
|
| warning_count | 0
|
| wsrep_osu_method | TOI
|
| wsrep_auto_increment_control | ON
|
| wsrep_causal_reads | OFF
|
| wsrep_certify_nonpk | ON
|
| wsrep_cluster_address |
|
| wsrep_cluster_name | my_wsrep_cluster
|
| wsrep_convert_lock_to_trx | OFF
|
| wsrep_data_home_dir | /home/mysql/database/ma14561/data/
|
| wsrep_dbug_option |
|
| wsrep_debug | OFF
|
| wsrep_desync | OFF
|
| wsrep_dirty_reads | OFF
|
| wsrep_drupal_282555_workaround | OFF
|
| wsrep_forced_binlog_format | NONE
|
| wsrep_gtid_domain_id | 0
|
| wsrep_gtid_mode | OFF
|
| wsrep_load_data_splitting | ON
|
| wsrep_log_conflicts | OFF
|
| wsrep_max_ws_rows | 0
|
| wsrep_max_ws_size | 2147483647
|
| wsrep_mysql_replication_bundle | 0
|
| wsrep_node_address |
|
| wsrep_node_incoming_address | AUTO
|
| wsrep_node_name | laptop4
|
| wsrep_notify_cmd |
|
| wsrep_on | OFF
|
| wsrep_patch_version | wsrep_25.21
|
| wsrep_provider | none
|
| wsrep_provider_options |
|
| wsrep_recover | OFF
|
| wsrep_replicate_myisam | OFF
|
| wsrep_restart_slave | OFF
|
| wsrep_retry_autocommit | 1
|
| wsrep_slave_fk_checks | ON
|
| wsrep_slave_uk_checks | OFF
|
| wsrep_slave_threads | 1
|
| wsrep_sst_auth |
|
| wsrep_sst_donor |
|
| wsrep_sst_donor_rejects_queries | OFF
|
| wsrep_sst_method | rsync
|
| wsrep_sst_receive_address | AUTO
|
| wsrep_start_position | 00000000-0000-0000-0000-000000000000:-1
|
| wsrep_sync_wait | 0
|
+--------------------------------------------------------+--------------------------------------------------------------
|
635 rows in set (0.00 sec)
|
And my.cnf attached
Thanks! Binary logging makes the difference. Maybe there is some historical reason for it, but I can't find it documented so far, so I'll assume for now it's a bug.
--source include/have_log_bin.inc
|
|
|
create table t1 (i int, check(i>'foo')); |
--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD,ER_TRUNCATED_WRONG_VALUE
|
insert into t1 values (1),(2); |
insert into t1 values (1); |
|
|
drop table t1; |
|
10.2.13 |
create table t1 (i int, check(i>'foo'));
|
insert into t1 values (1),(2);
|
Got one of the listed errors
|
insert into t1 values (1);
|
Warnings:
|
Error 1292 Truncated incorrect DOUBLE value: 'foo'
|
drop table t1;
|
Without binary logging, both INSERTs fail (in strict mode).
The error text is correct as it comes from wrong usage of CHAR_LENGTH()
MariaDB [test]> select CHAR_LENGTH("ken" > 2);
------------------------
| CHAR_LENGTH("ken" > 2) |
------------------------
| 1 |
------------------------
1 row in set, 1 warning (0.000 sec)
MariaDB [test]> show warnings;
----------------------------------------------------
| Level | Code | Message |
----------------------------------------------------
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ken' |
----------------------------------------------------
1 row in set (0.000 sec)
Problem was that verify_constraints() didn't check if
there was an error as part of evaluating constraints (can
happen in strict mode).
In one-row-insert the error was ignored when using binary logging as
binary logging clear errors if insert succeeded. In multi-row-insert
the error was noticed for the second row.
After this fix one will get an error for both one and
multi-row inserts if the constraints generates a warning
in strict mode.
You seem to have a typo in the constraint: CHECK (CHAR_LENGTH(FirstName > 2)). I guess you meant CHECK (CHAR_LENGTH(FirstName) > 2)