Important Note for MySQL 5.x Users - NOT SUPPORTED YET!!

Post by **TheWitness** » Wed Oct 26, 2005 7:34 pm

[EDIT]

All,

Please note, there have been additional issues raised in MySQL that will require patches to Cacti. Therefore, we are not going to officially support at this time.

For those interested in helping us test, the BRANCH_0_8_6 SVN is pretty much functional, but a note today, proved we have more work to do.

TheWitness

------------------------------------------------------

All,

In order for Cacti to function properly in MySQL 5.x, you must run in MySQL compatibility mode. In order to do this, you must specify the runtime option below:

--sql-mode=MYSQL40

You can obtain additional information on the --sql-mode directive at the following link:

http://dev.mysql.com/doc/refman/5.0/en/ ... -mode.html

TheWitness

silicon_freak · Post by **silicon_freak** » Thu Oct 27, 2005 12:28 pm

What do they mean by this and how do I do it? I am dumb with Mysql sorry...

In MySQL 5.0, you can also change the SQL mode after startup time by setting the sql_mode variable using a SET [SESSION|GLOBAL] sql_mode='modes' statement. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

modes is a list of different modes separated by comma (‘,’) characters. You can retrieve the current mode by issuing a SELECT @@sql_mode statement. The default value is empty (no modes set).

Post by **TheWitness** » Thu Oct 27, 2005 1:06 pm

I just use MySQL Administrator on the box MySQL is running on and then go to Startup Variables->Advanced and set it there. Apply and then restart the service.

TheWitness

silicon_freak · Post by **silicon_freak** » Thu Oct 27, 2005 1:09 pm

I have it set however it still does not work correctly.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24 to server version: 5.0.15-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT @@SESSION.sql_mode;
+----------------------------------------------+
| @@SESSION.sql_mode |
+----------------------------------------------+
| NO_FIELD_OPTIONS,MYSQL40,HIGH_NOT_PRECEDENCE |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+----------------------------------------------+
| @@GLOBAL.sql_mode |
+----------------------------------------------+
| NO_FIELD_OPTIONS,MYSQL40,HIGH_NOT_PRECEDENCE |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> exit

mysql> SET sql_mode='MYSQL40';
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES;
+---------------------------------+---------------------------------------------
-+
| Variable_name | Value
|
+---------------------------------+---------------------------------------------
-+
| auto_increment_increment | 1
|
| auto_increment_offset | 1
|
| automatic_sp_privileges | ON
|
| back_log | 50
|
| basedir | /usr/local/mysql/
|
| binlog_cache_size | 32768
|
| bulk_insert_buffer_size | 8388608
|
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/
|
| collation_connection | latin1_swedish_ci
|
| collation_database | latin1_swedish_ci
|
| collation_server | latin1_swedish_ci
|
| completion_type | 0
|
| concurrent_insert | 1
|
| connect_timeout | 5
|
| datadir | /usr/local/mysql/var/
|
| date_format | %Y-%m-%d
|
| datetime_format | %Y-%m-%d %H:%i:%s
|
| default_week_format | 0
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| div_precision_increment | 4
|
| engine_condition_pushdown | OFF
|
| expire_logs_days | 0
|
| flush | OFF
|
| flush_time | 0
|
| ft_boolean_syntax | + -><()~*:""&|
|
| ft_max_word_len | 84
|
| ft_min_word_len | 4
|
| ft_query_expansion_limit | 20
|
| ft_stopword_file | (built-in)
|
| group_concat_max_len | 1024
|
| have_archive | NO
|
| have_bdb | NO
|
| have_blackhole_engine | NO
|
| have_compress | YES
|
| have_crypt | YES
|
| have_csv | NO
|
| have_example_engine | NO
|
| have_federated_engine | NO
|
| have_geometry | YES
|
| have_innodb | YES
|
| have_isam | NO
|
| have_ndbcluster | NO
|
| have_openssl | NO
|
| have_query_cache | YES
|
| have_raid | NO
|
| have_rtree_keys | YES
|
| have_symlink | YES
|
| init_connect |
|
| init_file |
|
| init_slave |
|
| innodb_additional_mem_pool_size | 1048576
|
| innodb_autoextend_increment | 8
|
| innodb_buffer_pool_awe_mem_mb | 0
|
| innodb_buffer_pool_size | 8388608
|
| innodb_checksums | ON
|
| innodb_commit_concurrency | 0
|
| innodb_concurrency_tickets | 500
|
| innodb_data_file_path | ibdata1:10M:autoextend
|
| innodb_data_home_dir |
|
| innodb_doublewrite | ON
|
| innodb_fast_shutdown | 1
|
| innodb_file_io_threads | 4
|
| innodb_file_per_table | OFF
|
| innodb_flush_log_at_trx_commit | 1
|
| innodb_flush_method |
|
| innodb_force_recovery | 0
|
| innodb_lock_wait_timeout | 50
|
| innodb_locks_unsafe_for_binlog | OFF
|
| innodb_log_arch_dir |
|
| innodb_log_archive | OFF
|
| innodb_log_buffer_size | 1048576
|
| innodb_log_file_size | 5242880
|
| innodb_log_files_in_group | 2
|
| innodb_log_group_home_dir | ./
|
| innodb_max_dirty_pages_pct | 90
|
| innodb_max_purge_lag | 0
|
| innodb_mirrored_log_groups | 1
|
| innodb_open_files | 300
|
| innodb_support_xa | ON
|
| innodb_sync_spin_loops | 20
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 20
|
| innodb_thread_sleep_delay | 10000
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 16777216
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| language | /usr/local/mysql/share/mysql/english/
|
| large_files_support | ON
|
| large_page_size | 0
|
| large_pages | OFF
|
| license | GPL
|
| local_infile | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_bin | ON
|
| log_bin_trust_routine_creators | OFF
|
| log_error |
|
| log_slave_updates | OFF
|
| log_slow_queries | OFF
|
| log_warnings | 1
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_file_system | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 1047552
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 100
|
| max_delayed_threads | 20
|
| max_error_count | 64
|
| max_heap_table_size | 16777216
|
| max_insert_delayed_threads | 20
|
| max_join_size | 4294967295
|
| max_length_for_sort_data | 1024
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 4294967295
|
| multi_range_count | 256
|
| myisam_data_pointer_size | 6
|
| myisam_max_sort_file_size | 2147483647
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 8388608
|
| myisam_stats_method | nulls_unequal
|
| net_buffer_length | 8192
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| new | OFF
|
| old_passwords | OFF
|
| open_files_limit | 1024
|
| optimizer_prune_level | 1
|
| optimizer_search_depth | 62
|
| pid_file | /usr/local/mysql/var/alphawatch.pid
|
| port | 3306
|
| preload_buffer_size | 32768
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 0
|
| query_cache_type | ON
|
| query_cache_wlock_invalidate | OFF
|
| query_prealloc_size | 8192
|
| range_alloc_block_size | 2048
|
| read_buffer_size | 258048
|
| read_only | OFF
|
| read_rnd_buffer_size | 520192
|
| relay_log_purge | ON
|
| relay_log_space_limit | 0
|
| rpl_recovery_rank | 0
|
| secure_auth | OFF
|
| server_id | 1
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_compressed_protocol | OFF
|
| slave_load_tmpdir | /tmp/
|
| slave_net_timeout | 3600
|
| slave_skip_errors | OFF
|
| slave_transaction_retries | 10
|
| slow_launch_time | 2
|
| socket | /tmp/mysql.sock
|
| sort_buffer_size | 524280
|
| sql_mode | NO_FIELD_OPTIONS,MYSQL40,HIGH_NOT_PRECEDENCE
|
| sql_notes | ON
|
| sql_warnings | ON
|
| storage_engine | MyISAM
|
| sync_binlog | 0
|
| sync_frm | ON
|
| sync_replication | 0
|
| sync_replication_slave_id | 0
|
| sync_replication_timeout | 10
|
| system_time_zone | EDT
|
| table_cache | 64
|
| table_lock_wait_timeout | 50
|
| table_type | MyISAM
|
| thread_cache_size | 0
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| tmp_table_size | 33554432
|
| tmpdir |
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| updatable_views_with_limit | YES
|
| version | 5.0.15-log
|
| version_comment | Source distribution
|
| version_compile_machine | i686
|
| version_compile_os | pc-linux-gnu
|
| wait_timeout | 28800
|
+---------------------------------+---------------------------------------------
-+
209 rows in set (0.01 sec)

Post by **TheWitness** » Thu Oct 27, 2005 6:14 pm

Please make sure it's ONLY set to:

MYSQL40

Remove the other two options.

NO_FIELD_OPTIONS and HIGH_NOT_PRECEDENCE

TheWitness

silicon_freak · Post by **silicon_freak** » Thu Oct 27, 2005 6:21 pm

They set automatically when running the set_mode...

Post by **TheWitness** » Thu Oct 27, 2005 6:23 pm

Have you tried from MySQL Administrator?

TheWitness

silicon_freak · Post by **silicon_freak** » Thu Oct 27, 2005 6:43 pm

[root@alpha silicon_freak]# /usr/local/mysql/bin/mysqladmin --sql-mode=MYSQL40
/usr/local/mysql/bin/mysqladmin: unknown variable 'sql-mode=MYSQL40'

Is that it?

Post by **TheWitness** » Thu Oct 27, 2005 6:46 pm

Cacti

Important Note for MySQL 5.x Users - NOT SUPPORTED YET!!

Important Note for MySQL 5.x Users - NOT SUPPORTED YET!!

MYSQL 5.0.xx

Who is online