comparison on timestamp fields

Hi,

I'm seeing the following problem on a client's mysql installation. I don't
have access to the sql server, so I can't use mysqlbug unfortunately.

How-To-Repeat:

mysql> create table test (a int, b timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (1, NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+----------------+
| a | b |
+------+----------------+
| 1 | 20030129163341 |
+------+----------------+
1 row in set (0.00 sec)

mysql> select b from test where b > 20030129163341;
+----------------+
| b |
+----------------+
| 20030129163341 |
+----------------+
1 row in set (0.00 sec)

This row should not be returned.

The client's host tells me that uname -a gives:

Linux mysql02.futurequest.net 2.4.19-MYSQL-1001 #1 Wed Oct 2 06:25:52 EDT
2002 i686 unknown

Mysql is stock binary from mysql.com: mysql-3.23.54a-pc-linux-i686.tar.gz.

Has anyone seen a problem like this? Any ideas on what information might be
useful in tracking this down (given that I don't have access to the sql server).

Cheers,

Alex

--
Alex Krohn

------------------------------------------------------------ ---------
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-thread13643@lists.mysql.com
To unsubscribe, e-mail

Re: comparison on timestamp fields

Alex Krohn writes:
> Hi,
>
> I'm seeing the following problem on a client's mysql installation. I don't
> have access to the sql server, so I can't use mysqlbug unfortunately.
>
> How-To-Repeat:
>
> mysql> create table test (a int, b timestamp);
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into test values (1, NULL);
> Query OK, 1 row affected (0.00 sec)
>
> mysql> select * from test;
> +------+----------------+
> | a | b |
> +------+----------------+
> | 1 | 20030129163341 |
> +------+----------------+
> 1 row in set (0.00 sec)
>
> mysql> select b from test where b > 20030129163341;
> +----------------+
> | b |
> +----------------+
> | 20030129163341 |
> +----------------+
> 1 row in set (0.00 sec)
>

Hi!

I was not able to reproduce your case with latest 3.23 version.

Here is the output:

mysql> create table xx (id timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into xx values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from xx;
+----------------+
| id |
+----------------+
| 20030131143922 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from xx where id > 20030131143922;
Empty set (0.00 sec)


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13646@lists.mysql.com
To unsubscribe, e-mail

Re: comparison on timestamp fields

Hi Sinisa,

> > I'm seeing the following problem on a client's mysql installation. I don't
> > have access to the sql server, so I can't use mysqlbug unfortunately.
> >
> > How-To-Repeat:
> >
> > mysql> create table test (a int, b timestamp);
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql> insert into test values (1, NULL);
> > Query OK, 1 row affected (0.00 sec)
> >
> > mysql> select * from test;
> > +------+----------------+
> > | a | b |
> > +------+----------------+
> > | 1 | 20030129163341 |
> > +------+----------------+
> > 1 row in set (0.00 sec)
> >
> > mysql> select b from test where b > 20030129163341;
> > +----------------+
> > | b |
> > +----------------+
> > | 20030129163341 |
> > +----------------+
> > 1 row in set (0.00 sec)
> >
>
> Hi!
>
> I was not able to reproduce your case with latest 3.23 version.
>
> Here is the output:
>
> mysql> create table xx (id timestamp);
> Query OK, 0 rows affected (0.01 sec)

It works with a table with only a timestamp, but with a table that contains an
int and a timestamp, I see the problem behavior.

Again, I can't reproduce this on a system that I have access to, but do you
have any suggestions on what would cause this type of bug (strange locale
perhaps?)

Below is the output of show variables in case it's helpful:

mysql> show variables;
+---------------------------------+------------------------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------+
| back_log | 25 |
| basedir | /usr/local/mysql/ |
| binlog_cache_size | 32768 |
| character_set | latin1 |
| 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 | /usr/local/mysql-data/ |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| flush | OFF |
| flush_time | 600 |
| have_bdb | NO |
| have_gemini | NO |
| have_innodb | NO |
| have_isam | YES |
| have_raid | NO |
| have_openssl | NO |
| init_file | |
| interactive_timeout | 1800 |
| join_buffer_size | 5238784 |
| key_buffer_size | 268431360 |
| language | /usr/local/mysql/share/mysql/english/ |
| large_files_support | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_update | OFF |
| log_bin | OFF |
| log_slave_updates | OFF |
| log_long_queries | ON |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1047552 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connections | 200 |
| max_connect_errors | 10000 |
| 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 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 0 |
| pid_file | /usr/local/mysql-data/mysql02.futurequest.net.pid |
| port | 3306 |
| protocol_version | 10 |
| record_buffer | 1044480 |
| record_rnd_buffer | 1044480 |
| query_buffer_size | 0 |
| safe_show_database | ON |
| server_id | 0 |
| slave_net_timeout | 3600 |
| skip_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer | 8388600 |
| sql_mode | 0 |
| table_cache | 256 |
| table_type | MYISAM |
| thread_cache_size | 8 |
| thread_stack | 262144 |
| transaction_isolation | READ-COMMITTED |
| timezone | EST |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| version | 3.23.54-log |
| wait_timeout | 28800 |
+---------------------------------+------------------------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------+
82 rows in set (0.00 sec)

mysql>

Cheers,

Alex

--
Alex Krohn

------------------------------------------------------------ ---------
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-thread13649@lists.mysql.com
To unsubscribe, e-mail

Re: comparison on timestamp fields

Alex Krohn writes:
> Hi Sinisa,
>
> It works with a table with only a timestamp, but with a table that contains an
> int and a timestamp, I see the problem behavior.
>
> Again, I can't reproduce this on a system that I have access to, but do you
> have any suggestions on what would cause this type of bug (strange locale
> perhaps?)
>
> I've attached the output of show variables in case it's helpful.
>
> Cheers,
>
> Alex
>
> --
> Alex Krohn

No luck again with 3.23.55:

mysql> create table xx (a int, b timestamp);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into xx values (1,NULL);
Query OK, 1 row affected (0.13 sec)

mysql> select * from xx;
+------+----------------+
| a | b |
+------+----------------+
| 1 | 20030131220821 |
+------+----------------+
1 row in set (0.02 sec)

mysql> select * from xx where b > 20030131220821;
Empty set (0.02 sec)


I also could not find any specifics in your variables.

The only possible cause could be that you were not using our binary.

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
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-thread13650@lists.mysql.com
To unsubscribe, e-mail

Re: comparison on timestamp fields

Hi Sinisa,

> No luck again with 3.23.55:
>
> mysql> create table xx (a int, b timestamp);
> Query OK, 0 rows affected (0.17 sec)
>
> mysql> insert into xx values (1,NULL);
> Query OK, 1 row affected (0.13 sec)
>
> mysql> select * from xx;
> +------+----------------+
> | a | b |
> +------+----------------+
> | 1 | 20030131220821 |
> +------+----------------+
> 1 row in set (0.02 sec)
>
> mysql> select * from xx where b > 20030131220821;
> Empty set (0.02 sec)
>
>
> I also could not find any specifics in your variables.
>
> The only possible cause could be that you were not using our binary.

I spoke to the host, and we were able to consistently reproduce the problem.
On linux, set your timezone to use 'right'. i.e.:

[root@penguin alex]# ll /etc/localtime
lrwxrwxrwx 1 root root 30 Jan 31 14:50 /etc/localtime -> /usr/share/zoneinfo/US/Pacific
[root@penguin alex]#

works properly, but switching the timezone to:

[root@penguin alex]# ll /etc/localtime
lrwxrwxrwx 1 root root 30 Jan 31 14:50 /etc/localtime -> /usr/share/zoneinfo/right/US/Pacific
[root@penguin alex]#

will cause problems (need to restart mysql in between). Apparently (I'm not
100% sure), the /right/ directory is for taking into account leap-year seconds.
See the following example when you use the second timezone:

How-To-Repeat:

mysql> create table xx (a timestamp);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into xx values (NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from xx;
+----------------+
| a |
+----------------+
| 20030131150815 |
+----------------+
1 row in set (0.00 sec)

mysql> insert into xx values (20030131150815);
Query OK, 1 row affected (0.00 sec)

mysql> select * from xx;
+----------------+
| a |
+----------------+
| 20030131150815 |
| 20030131150853 |
+----------------+
2 rows in set (0.00 sec)

The time discrepancy will cause unpredictable behavior. The host say they
require this timezone setting in order to have accurate time (to account for
leap year seconds).

Hope this helps, and I'd be interested to hear your thoughts.

Cheers,

Alex

--
Alex Krohn

------------------------------------------------------------ ---------
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-thread13651@lists.mysql.com
To unsubscribe, e-mail