Data corruption during a simple update. Version 3.23.51-max-nt
am 05.09.2002 13:13:07 von Leif MortensonLooking at the following page:
http://www.mysql.com/doc/en/Bug_reports.html
It says to run a "mysqlbug" script in the bin directory of my
installation. But It does not
exist. So I'll try to make the problem, example script and example
output as clear as possible.
I am running 3.23.51-max-nt on a Windows XP machine.
This seems like a very simple test case, so I am having problems
understanding how this
could really be a bug. But here is the situation:
How-To-Repeat:
In a clean brand new database, run the following script:
---
CREATE TABLE time_periods (
id INTEGER NOT NULL,
plant INTEGER NOT NULL,
start_time TIMESTAMP NOT NULL,
stop_time TIMESTAMP NOT NULL,
needs_calculation CHAR(1) NOT NULL,
has_data CHAR(1) NOT NULL
);
INSERT INTO time_periods VALUES (1, 1, '2002-08-30 00:00:00',
'2002-08-31 00:00:00', '1', '0');
SELECT * FROM time_periods;
# At this point everything is ok
UPDATE time_periods SET needs_calculation='1', start_time=start_time
WHERE stop_time > '2002-08-29 00:00:00';
SELECT * FROM time_periods;
# This is also Ok, because nothing was changed
UPDATE time_periods SET needs_calculation='0', start_time=start_time
WHERE stop_time > '2002-08-29 00:00:00';
SELECT * FROM time_periods;
# This is ok because I am setting the value of start_time. (My workaround)
UPDATE time_periods SET needs_calculation='1' WHERE stop_time >
'2002-08-29 00:00:00';
SELECT * FROM time_periods;
# For some reason, the start_time is being changed to the time that the
update query was run.
---
For some reason, this is the output that I get:
---
mysql> CREATE TABLE time_periods (
-> id INTEGER NOT NULL,
-> plant INTEGER NOT NULL,
-> start_time TIMESTAMP NOT NULL,
-> stop_time TIMESTAMP NOT NULL,
-> needs_calculation CHAR(1) NOT NULL,
-> has_data CHAR(1) NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO time_periods VALUES (1, 1, '2002-08-30 00:00:00',
'2002-08-31 00:00:00', '1', '0');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM time_periods;
+----+-------+----------------+----------------+------------ -------+----------+
| id | plant | start_time | stop_time | needs_calculation | has_data |
+----+-------+----------------+----------------+------------ -------+----------+
| 1 | 1 | 20020830000000 | 20020831000000 | 1 | 0 |
+----+-------+----------------+----------------+------------ -------+----------+
1 row in set (0.00 sec)
mysql> # At this point everything is ok
mysql>
mysql> UPDATE time_periods SET needs_calculation='1',
start_time=start_time WHERE stop_time > '2002-08-29 00:00:00';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> SELECT * FROM time_periods;
+----+-------+----------------+----------------+------------ -------+----------+
| id | plant | start_time | stop_time | needs_calculation | has_data |
+----+-------+----------------+----------------+------------ -------+----------+
| 1 | 1 | 20020830000000 | 20020831000000 | 1 | 0 |
+----+-------+----------------+----------------+------------ -------+----------+
1 row in set (0.00 sec)
mysql> # This is also Ok, because nothing was changed
mysql>
mysql> UPDATE time_periods SET needs_calculation='0',
start_time=start_time WHERE stop_time > '2002-08-29 00:00:00';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM time_periods;
+----+-------+----------------+----------------+------------ -------+----------+
| id | plant | start_time | stop_time | needs_calculation | has_data |
+----+-------+----------------+----------------+------------ -------+----------+
| 1 | 1 | 20020830000000 | 20020831000000 | 0 | 0 |
+----+-------+----------------+----------------+------------ -------+----------+
1 row in set (0.00 sec)
mysql> # This is ok because I am setting the value of start_time. (My
workaround)
mysql>
mysql> UPDATE time_periods SET needs_calculation='1' WHERE stop_time >
'2002-08-29 00:00:00';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM time_periods;
+----+-------+----------------+----------------+------------ -------+----------+
| id | plant | start_time | stop_time | needs_calculation | has_data |
+----+-------+----------------+----------------+------------ -------+----------+
| 1 | 1 | 20020905200032 | 20020831000000 | 1 | 0 |
+----+-------+----------------+----------------+------------ -------+----------+
1 row in set (0.00 sec)
mysql> # For some reason, the start_time is being changed to the time
that the update query was run.
---
Am I doing something stupid here? It seems like this should be working.
Cheers,
Leif
Folling is the output of SHOW VARIABLES:
---
+---------------------------------+------------------------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------+
| back_log | 50 |
| basedir | d:\mysql\ |
| bdb_cache_size | 8388600 |
| bdb_log_buffer_size | 131072 |
| bdb_home | d:\mysql\data\ |
| bdb_max_lock | 10000 |
| bdb_logdir | |
| bdb_shared_data | OFF |
| bdb_tmpdir | C:\WINDOWS\TEMP\ |
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a: (June 5, 2002) |
| binlog_cache_size | 32768 |
| character_set | sjis |
| character_sets | latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis
dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew
win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257
latin5 |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | d:\mysql\data\ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 1800 |
| have_bdb | YES |
| have_gemini | NO |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| innodb_additional_mem_pool_size | 2097152 |
| innodb_buffer_pool_size | 16777216 |
| innodb_data_file_path | ibdata1 |
| innodb_data_home_dir | d:\mysql\ibdata |
| innodb_file_io_threads | 4 |
| innodb_force_recovery | 0 |
| innodb_thread_concurrency | 8 |
| innodb_flush_log_at_trx_commit | ON |
| innodb_fast_shutdown | ON |
| innodb_flush_method | |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | d:\mysql\iblogs |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 3 |
| innodb_log_group_home_dir | d:\mysql\iblogs |
| innodb_mirrored_log_groups | 1 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 268431360 |
| language | d:\mysql\share\english\ |
| large_files_support | ON |
| log | ON |
| log_update | OFF |
| log_bin | ON |
| log_slave_updates | OFF |
| log_long_queries | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_user_connections | 0 |
| max_tmp_tables | 32 |
| max_write_lock_count | 4294967295 |
| myisam_max_extra_sort_file_size | 256 |
| myisam_max_sort_file_size | 2047 |
| myisam_recover_options | 0 |
| myisam_sort_buffer_size | 67108864 |
| named_pipe | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | d:\mysql\data\Phobos.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 1044480 |
| record_rnd_buffer | 1044480 |
| query_buffer_size | 0 |
| safe_show_database | OFF |
| server_id | 1 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | MySQL |
| sort_buffer | 1048568 |
| sql_mode | 0 |
| table_cache | 256 |
| table_type | MYISAM |
| thread_cache_size | 8 |
| thread_stack | 65536 |
| transaction_isolation | READ-COMMITTED |
| timezone | 東京 (標準時) |
| tmp_table_size | 33554432 |
| tmpdir | C:\WINDOWS\TEMP\ |
| version | 3.23.51-max-nt-log |
| wait_timeout | 28800 |
+---------------------------------+------------------------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------+
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12492@lists.mysql.com
To unsubscribe, e-mail