Re: Timestamps replicating inconsistently depending on local timezone of server?
Rob Wultsch wrote: On Tue, May 13, 2008 at 2:07 PM, Ed W [EMAIL PROTECTED] wrote: I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Sure - but I'm observing the opposite. My datetime is correct in UTC, but the timestamp col has definitely jumped forward one hour. Orig server: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 17:52:53 New server where the localtime variable has been changed: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 18:52:53 Using default mysql client settings on each server to examine the data, so possibly problem is related to client incorrectly adjusting values for display? I then changed the second servers localtime option, restored the same database as before and again replicated the same data across to catch up and this time they show the same values. So basically the value retrieved from the second database is influenced by the localtime options being different on each server *at the time replication occurs* Anyone shed some light on this? Ed W
comparison operations in IN subquery
Hi guys,
look at the following test case:
mysql create table temp1( id int)ENGINE=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql create table temp2( tid varchar(10))ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql insert into temp1 values(1);
Query OK, 1 row affected (0.07 sec)
mysql insert into temp1 values(2);
Query OK, 1 row affected (0.05 sec)
mysql insert into temp1 values(3);
Query OK, 1 row affected (0.04 sec)
mysql insert into temp1 values(4);
Query OK, 1 row affected (0.04 sec)
mysql insert into temp2 values('2,3,4');
Query OK, 1 row affected (0.05 sec)
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql select * from temp1;
+--+
| id |
+--+
|1 |
|2 |
|3 |
|4 |
+--+
4 rows in set (0.01 sec)
mysql select * from temp2;
+---+
| tid |
+---+
| 2,3,4 |
+---+
1 row in set (0.00 sec)
mysql select * from temp1 where id in (select tid from temp2);
+--+
| id |
+--+
|2 |
+--+
1 row in set (0.00 sec)
The problem:
Why there is result for the last SELECT statement???
How does mysql compare id with tid ?? they are different type and have
different format value.
Thanks a lot!!!
*^_^*
-
雅虎邮箱，您的终生邮箱！
Query execution time - MySQL
Hi, When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etc Thanks, Neil _ All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
Re: Query execution time - MySQL
Greetings Niel, Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth. HOWEVER - Just so you know, if you execute the query MANUALLY via the *command-line of MySQL* it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer: mysql query or mysql run the query (use the below quoted/threaded example as a starting place to write your own query...?) Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be): http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information ***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. )) Let me know if you have any questions. Standing by and I hope this helped you. Sincerely, Craig Huffstetler xq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi, When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etc Thanks, Neil _ All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
RE: Query execution time - MySQL
Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. Regards Neil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/
Re: Query execution time - MySQL
If you using C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote: Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. Regards Neil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query execution time - MySQL
Hi Neil, If your using Linux then you have to install the glib RPM's in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries. Neil Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you using C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote: Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. Regards Neil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404 SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself. TOTAL TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are you running? This would be helpful to give you the step-by-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apache On Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote: Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ _ Great deals on almost anything at eBay.co.uk. Search, bid, find and win on eBay today! http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Miss your Messenger buddies when on-the-go? Get Messenger on your Mobile! http://clk.atdmt.com/UKM/go/msnnkmgl001001ukm/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
RE: Query execution time - MySQL
Hi Ben
I running on Windows. I think I need a solution where i can get the search
time within my sql query. Is this possible. At the moment I;m running mysql
server version 3.28
Neil
Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL
PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time -
MySQL Hi Neil, If your using Linux then you have to install the glib
RPM's in the usual way. I don't know about other platforms, but I am sure
there will be a version of glib out there... Also ensure the correct
include and link directives are in your Makefile, which you can get (on
Linux) using the commands: # glib-config --cflags # glib-config --libs
Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the
header file glib.h in my libraries.Neil
Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Query execution time - MySQL If you using C++ then you
can use this:
http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this
in my code, does an excelent job. Also you may want to look at the
'slow log' in mysql which will show, to the nearest second, the length
of queries Ben Neil Tompkins wrote:Hi Craig,
Thanks for your detailed reply. Basically what I'm trying to
extract is the time taken from when I execute the mysql query in my C++
Builder program until the time the query has finished. So my
question is can I build in to my SQL query SELECT Name FROM Customers the
time the query actually took or do I need to do this outside of my
query. RegardsNeil Date: Wed, 14
May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re:
Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much
detail there (but I'll go off what you provided...). Some people limit
the actual MySQL system for times it TAKES MySQL to execute queries. For
THIS to be accomplished, MySQL has built-in functionality to measure the
time is takes queries to take place so it can ... limit them. So, in
essence, I guess we can extract that data and get it back to you for
whatever usage statistic you are looking to measure. ( See:
http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html
) or for usage in JDBC by calling the setQueryTimeout() function of a
Statement object...and so forth.HOWEVER - Just so you know, if you
execute the query MANUALLY via the command-line of MySQL it will tell you
how long the query took. Just use normal SQL syntax, execute the query
on the table and VOILA! Your answer:mysql queryormysqlrun the query (use
the below quoted/threaded example as a starting place to write your own
query...?)Take a look at this thread (it basically explains the answer
with a bit more detail on what the output will
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404
SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT
BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I
believe the results are the following: The first number is the time it
took MySQL server to send the result set to the client. The second number
(in parens) is the time it took MySQL server to execute the query
itself.TOTAL TIME will EQUAL A + B (for total time it took on your
server/P.C. or wherever you are running the query...). Many things come
into factoring why it takes longer or shorter. So this is why I asked if
you are attempting to optimize or what not, but that is whole new story.
(( What Operating System are you running? This would be helpful to
give you the step-by-step, so to speak. Or perhaps provide us with a bit
more information***Also, if you are looking to perhaps make it so queries
take shorter times (optimization effort) to execute a little bit more
about your MySQL database setup and machine(s) would be beneficial to us
as well. ))Let me know if you have any questions.Standing by and I hope
this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql |
#apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL
PROTECTED] wrote:Hi,When performing a SQL query like SELECT Name
FROM Customers. How do I obtain the time in which the query took to
execute like 1.5 seconds
etcThanks,Neil_All
new Live Search at
Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_
Great deals on almost anything at eBay.co.uk. Search, bid, find and win
on eBay today!
RE: Query execution time - MySQL
Thanks for your help. In the end I've decided to use GetTickCount()
Neil
Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL
PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time -
MySQL Hi Neil, If your using Linux then you have to install the glib
RPM's in the usual way. I don't know about other platforms, but I am sure
there will be a version of glib out there... Also ensure the correct
include and link directives are in your Makefile, which you can get (on
Linux) using the commands: # glib-config --cflags # glib-config --libs
Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the
header file glib.h in my libraries.Neil
Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: Query execution time - MySQL If you using C++ then you
can use this:
http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this
in my code, does an excelent job. Also you may want to look at the
'slow log' in mysql which will show, to the nearest second, the length
of queries Ben Neil Tompkins wrote:Hi Craig,
Thanks for your detailed reply. Basically what I'm trying to
extract is the time taken from when I execute the mysql query in my C++
Builder program until the time the query has finished. So my
question is can I build in to my SQL query SELECT Name FROM Customers the
time the query actually took or do I need to do this outside of my
query. RegardsNeil Date: Wed, 14
May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re:
Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much
detail there (but I'll go off what you provided...). Some people limit
the actual MySQL system for times it TAKES MySQL to execute queries. For
THIS to be accomplished, MySQL has built-in functionality to measure the
time is takes queries to take place so it can ... limit them. So, in
essence, I guess we can extract that data and get it back to you for
whatever usage statistic you are looking to measure. ( See:
http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html
) or for usage in JDBC by calling the setQueryTimeout() function of a
Statement object...and so forth.HOWEVER - Just so you know, if you
execute the query MANUALLY via the command-line of MySQL it will tell you
how long the query took. Just use normal SQL syntax, execute the query
on the table and VOILA! Your answer:mysql queryormysqlrun the query (use
the below quoted/threaded example as a starting place to write your own
query...?)Take a look at this thread (it basically explains the answer
with a bit more detail on what the output will
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404
SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT
BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I
believe the results are the following: The first number is the time it
took MySQL server to send the result set to the client. The second number
(in parens) is the time it took MySQL server to execute the query
itself.TOTAL TIME will EQUAL A + B (for total time it took on your
server/P.C. or wherever you are running the query...). Many things come
into factoring why it takes longer or shorter. So this is why I asked if
you are attempting to optimize or what not, but that is whole new story.
(( What Operating System are you running? This would be helpful to
give you the step-by-step, so to speak. Or perhaps provide us with a bit
more information***Also, if you are looking to perhaps make it so queries
take shorter times (optimization effort) to execute a little bit more
about your MySQL database setup and machine(s) would be beneficial to us
as well. ))Let me know if you have any questions.Standing by and I hope
this helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql |
#apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL
PROTECTED] wrote:Hi,When performing a SQL query like SELECT Name
FROM Customers. How do I obtain the time in which the query took to
execute like 1.5 seconds
etcThanks,Neil_All
new Live Search at
Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_
Great deals on almost anything at eBay.co.uk. Search, bid, find and win
on eBay today!
http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ --
MySQL General Mailing List For list
Re: comparison operations in IN subquery
In the last episode (May 14), xian liu said: mysql select * from temp1; +--+ | id | +--+ |1 | |2 | |3 | |4 | +--+ 4 rows in set (0.01 sec) mysql select * from temp2; +---+ | tid | +---+ | 2,3,4 | +---+ 1 row in set (0.00 sec) mysql select * from temp1 where id in (select tid from temp2); +--+ | id | +--+ |2 | +--+ 1 row in set (0.00 sec) The problem: Why there is result for the last SELECT statement??? How does mysql compare id with tid ?? they are different type and have different format value. http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa. When the string 2,3,4 gets converted to a number, the first non-numeric character finishes the conversion so you get the number 2. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query execution time - MySQL
Neil Tompkins wrote:
Thanks for your help. In the end I've decided to use GetTickCount()
Neil
Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time - MySQL Hi Neil, If your using Linux then you have to install the glib RPM's
in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the
commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries.Neil
Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL
PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you us
ing C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote:Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. RegardsNeil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much
detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to
write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT
BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server
to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself.TOTAL
TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes
longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are
you running? This would be helpful to give you the step-b
y-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute
a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this
helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote:
Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds
etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_Great deals on almost anything at eBay.c
o.uk. Search, bid, find and win on eBay today!http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL
General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
RE: comparison operations in IN subquery
Hi guys,
look at the following test case:
mysql create table temp1( id int)ENGINE=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql create table temp2( tid varchar(10))ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql insert into temp1 values(1);
Query OK, 1 row affected (0.07 sec)
mysql insert into temp1 values(2);
Query OK, 1 row affected (0.05 sec)
mysql insert into temp1 values(3);
Query OK, 1 row affected (0.04 sec)
mysql insert into temp1 values(4);
Query OK, 1 row affected (0.04 sec)
mysql insert into temp2 values('2,3,4');
Query OK, 1 row affected (0.05 sec)
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql select * from temp1;
+--+
| id |
+--+
|1 |
|2 |
|3 |
|4 |
+--+
4 rows in set (0.01 sec)
mysql select * from temp2;
+---+
| tid |
+---+
| 2,3,4 |
+---+
1 row in set (0.00 sec)
mysql select * from temp1 where id in (select tid from temp2);
+--+
| id |
+--+
|2 |
+--+
1 row in set (0.00 sec)
The problem:
Why there is result for the last SELECT statement???
How does mysql compare id with tid ?? they are different type and have
different format value.
[JS] MySQL converts the string to a number in this case. It stops at the
first character that cannot be part of a number.
Thanks a lot!!!
*^_^*
-
ÑÅ»¢ÓÊÏ䣬ÄúµÄÖÕÉúÓÊÏ䣡
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CONCAT doesn't work with NULL?
hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) On 5/14/08, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
Re: CONCAT doesn't work with NULL?
Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On Wednesday 14 May 2008 18:02:42 Olexandr Melnyk wrote: It doesn't return no rows, it returns row(s) with a single column set to a NULL value. In case one of the arguments is NULL, CONCAT() will return NULL. To replace the value of one of the fields with an empty string when it's NULL, you can use something like: CONCAT(COAESCE(a, ''), ' ', COAESCE(b, '')) or CONCAT_WS IIRC W On 5/14/08, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: CONCAT doesn't work with NULL?
Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
Thanks Ewen, that's what I was looking for! :D -afan ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
First, I want to thank to everybody on such afast respond. Thank you. Second, what would be difference between concat_ws and the Randalll's solution (bellow)? -afan Price, Randall wrote: Could you use something like this (untried): SELECT CONCAT(COALESCE(r.first_name, ''), ' ', COALESCE(r.last_name,''), '\n', COALESCE(r.organization, ''), '\n', COALESCE(r.title,''), '\n', COALESCE(a.address1, ''), '\n', COALESCE(a.city, ''), ', ', COALESCE(a.state,''), ' ', COALESCE(a.zip, ''), '\n', COALESCE(r.email,'')) FROM registrants r, addresses a WHERE r.reg_id=121 Randall Price Secure Enterprise Technology Initiatives Microsoft Implementation Group Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 -Original Message- From: Afan Pasalic [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 14, 2008 11:53 AM To: mysql@lists.mysql.com Subject: CONCAT doesn't work with NULL? hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replace chr(10) in field
Hi, I've got some data in our fields which contain a carriage return 'chr(10)', as saved using a ASP page. I'm now trying to extract the information from a different system, however the saved chr(10) are showing as binary values. What would be the best way for my to replace chr(10) to a \n ? I tried using the mysql REPLACE() function, but it did not appear to work. Thanks, Neil _ All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
Re: CONCAT doesn't work with NULL?
On Wednesday 14 May 2008 18:52:20 Afan Pasalic wrote: actually, this will not work for me (or I got it wrong :D) because I need to have street, state and zip in one line and with separator defined on the beginning it will put everything in separate lines. Use a 'space' as sparator instead of '\n' :D ewen fortune wrote: Hi Afan, You can use concat_ws http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_con cat-ws --- CONCAT() returns NULL if any argument is NULL. CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument --- Ewen On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote: hi, I have query SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n', r.email) FROM registrants r, addresses a WHERE r.reg_id=121 if any of columns has value (e.g. title) NULL, I'll get as result 0 records. If query doesn't have concat() - it works fine. Why is that? -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: replace chr(10) in field
I thought this, but when I display the information in a Memo box in my C++ builder application I get little square boxes (binary type chars). And all the information is displayed on the same line. Any ideas why ? Date: Wed, 14 May 2008 13:08:04 -0500 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: replace chr(10) in field Neil Tompkins wrote: Hi,I've got some data in our fields which contain a carriage return 'chr(10)', as saved using a ASP page. I'm now trying to extract the information from a different system, however the saved chr(10) are showing as binary values.What would be the best way for my to replace chr(10) to a \n ? I tried using the mysql REPLACE() function, but it did not appear to work.Thanks, Neil _All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/ Chr(10) is a \n. Chr(13) is a \r. -- Gerald L. Clark Sr. V.P. Development Supplier Systems Corporation Unix since 1982 Linux since 1992 _ All new Live Search at Live.com http://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
Re: Timestamps replicating inconsistently depending on local timezone of server?
On Tue, May 13, 2008 at 11:56 PM, Ed W [EMAIL PROTECTED] wrote: Rob Wultsch wrote: On Tue, May 13, 2008 at 2:07 PM, Ed W [EMAIL PROTECTED] wrote: I had naively assumed that dates would always be stored in UTC in the database and the only effect of localtime would be for display purposes? Can anyone shed some light on what's happening here please? TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, not for other types such as DATETIME.) http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Sure - but I'm observing the opposite. My datetime is correct in UTC, but the timestamp col has definitely jumped forward one hour. Orig server: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 17:52:53 New server where the localtime variable has been changed: created_at: 2008-05-13 17:52:53 updated_at: 2008-05-13 18:52:53 Using default mysql client settings on each server to examine the data, so possibly problem is related to client incorrectly adjusting values for display? I then changed the second servers localtime option, restored the same database as before and again replicated the same data across to catch up and this time they show the same values. So basically the value retrieved from the second database is influenced by the localtime options being different on each server *at the time replication occurs* Anyone shed some light on this? Ed W This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. mysql CREATE TABLE `t1` (`c1` TIMESTAMP,`c2` DATETIME); Query OK, 0 rows affected (0.05 sec) mysql SET time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 VALUES(NOW(),NOW()); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t1; +-+-+ | c1 | c2 | +-+-+ | 2008-05-14 19:43:00 | 2008-05-14 19:43:00 | +-+-+ 1 row in set (0.00 sec) mysql SET time_zone = '+1:00'; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 VALUES(NOW(),NOW()); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t1; +-+-+ | c1 | c2 | +-+-+ | 2008-05-14 20:43:00 | 2008-05-14 19:43:00 | | 2008-05-14 20:43:15 | 2008-05-14 20:43:15 | +-+-+ 2 rows in set (0.00 sec) mysql SET time_zone = '+0:00'; Query OK, 0 rows affected (0.00 sec) mysql SELECT * FROM t1; +-+-+ | c1 | c2 | +-+-+ | 2008-05-14 19:43:00 | 2008-05-14 19:43:00 | | 2008-05-14 19:43:15 | 2008-05-14 20:43:15 | +-+-+ 2 rows in set (0.00 sec) But I could be completely off the mark. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Structure
On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, Below is the user_delivery table structure. CREATE TABLE `user_delivery` ( `user_id` decimal(22,0) NOT NULL default '0', `delivery_id` decimal(22,0) NOT NULL default '0', `send_to_regulator` char(1) default NULL, PRIMARY KEY (`user_id`), KEY `user_delivery_comp1` (`user_id`,`delivery_id`), CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1; According to me user_delivery_comp1 index can be dropped and new index can be created on delivery_id column. I would to know that the changes will work or not. Yours suggestion regarding this table structure. Thanks, -- Krishna Chandra Prajapati Define work. The effect should be something like: Queries that have where clauses for delivery_id but not user_id would be able to use an index. Queries that have where clauses for delivery_id and user_id might not be able to use as much of an index. Depending on your version of mysql merge index may apply, but I am not knowledge enough to comment of the performance differences between the two. I would assume a composite index when available would generally be more ideal. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamps replicating inconsistently depending on local timezone of server?
Rob Wultsch wrote: This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. OK, your example is clearly demonstrating the effect I am seeing - however, by changing the server localtime option I appear to be influencing the default mysql time offset. I still don't understand the reality of what is happening here - your example appears to show that datetime fields are correctly stored as GMT and adjusted as desired, but that a timestamp is a function of localtime? Either way they appear inconsistent... The end result needs to be that I can get these dates out of the database and correctly adjust them for the desired users localtime. What you are demonstrating here is that I either need to ditch all my timestamp columns (inconvenient) or switch the server to only run in UTC (inconvenient in that I need to mentally adjust in order to make sense of the log files). It would appear that if I run the server with a correct localtime then I have a bag of trouble when I want to figure out the time something happened (as you can see c1 and c2 should be the same in all cases, but not in your example) Can anyone shed some light on the best approach? Thanks Ed W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Timestamps replicating inconsistently depending on local timezone of server?
On Wed, May 14, 2008 at 12:55 PM, Ed W [EMAIL PROTECTED] wrote: Rob Wultsch wrote: This sounds like expected behavior to me. If you set the timezone one hour forward a timestamp will be one hour forward. The data stored on the server is the same, and will display the same if you change the timezone. The timezone setting when the insert occurred should have no effect. OK, your example is clearly demonstrating the effect I am seeing - however, by changing the server localtime option I appear to be influencing the default mysql time offset. I still don't understand the reality of what is happening here - your example appears to show that datetime fields are correctly stored as GMT and adjusted as desired, but that a timestamp is a function of localtime? Either way they appear inconsistent... The end result needs to be that I can get these dates out of the database and correctly adjust them for the desired users localtime. What you are demonstrating here is that I either need to ditch all my timestamp columns (inconvenient) or switch the server to only run in UTC (inconvenient in that I need to mentally adjust in order to make sense of the log files). It would appear that if I run the server with a correct localtime then I have a bag of trouble when I want to figure out the time something happened (as you can see c1 and c2 should be the same in all cases, but not in your example) Can anyone shed some light on the best approach? Thanks Ed W The display of the timestamp is dependent on the local time zone. Datetime is not adjusted for display. I don't use timestamp because I think it is Voodoo or some other form of black magic. I don't trust black magic that is not my own (or for that matter anything I write involving pointers). If I want a record to store NOW() then I tell it NOW(). For whatever it is worth I suggest ditching timestamp and going to datetime. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inserting client time instead of server time
On Mon, May 12, 2008 at 11:25 PM, Sebastian Mendel [EMAIL PROTECTED] wrote: Hi, is there a way or a function like NOW() except it returns the client time and not the server time? -- Sebastian Mendel Timezone for the client is used for NOW(), but the client timezone default to that of the server. You can reset this using SET time_zone = timezone;. There is probably some way to pass this as an argument to the client so that you do not have to manually do it. I don't know how to do this. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On May 14, 2008, at 10:53 AM, Afan Pasalic wrote:
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
That's how CONCAT() is documented to work:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
CONCAT() returns NULL if any argument is NULL.
You might want to try CONCAT_WS('', ...) instead. CONCAT_WS() isn't
fazed
by NULL values the same way that CONCAT() is. :-)
mysql select CONCAT('a',NULL,'b');
+--+
| CONCAT('a',NULL,'b') |
+--+
| NULL |
+--+
1 row in set (0.07 sec)
mysql select CONCAT_WS('','a',NULL,'b');
++
| CONCAT_WS('','a',NULL,'b') |
++
| ab |
++
1 row in set (0.00 sec)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Structure
Hi, Since user_id is a primary key. It should work either with any of the column and with both the column. Any suggestion. Thanks On Thu, May 15, 2008 at 1:22 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Tue, May 13, 2008 at 1:30 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, Below is the user_delivery table structure. CREATE TABLE `user_delivery` ( `user_id` decimal(22,0) NOT NULL default '0', `delivery_id` decimal(22,0) NOT NULL default '0', `send_to_regulator` char(1) default NULL, PRIMARY KEY (`user_id`), KEY `user_delivery_comp1` (`user_id`,`delivery_id`), CONSTRAINT `fk_user_delivery` FOREIGN KEY (`user_id`) REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1; According to me user_delivery_comp1 index can be dropped and new index can be created on delivery_id column. I would to know that the changes will work or not. Yours suggestion regarding this table structure. Thanks, -- Krishna Chandra Prajapati Define work. The effect should be something like: Queries that have where clauses for delivery_id but not user_id would be able to use an index. Queries that have where clauses for delivery_id and user_id might not be able to use as much of an index. Depending on your version of mysql merge index may apply, but I am not knowledge enough to comment of the performance differences between the two. I would assume a composite index when available would generally be more ideal. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Site Attack/Failure Recovery
Hi Folks, I am fairly new to MySQL and I am going to be setting up a web site on a third party hosting machine. I continuously hear horror stories about machines/sites being hacked and databases being destroyed. Despite my best efforts I am sure I have some security flaws in my site. What I am looking to do is provide myself with a mechanism to roll back my database to a clean state if I happen to suffer one of these attacks (or a failure). I was wondering what is the best way to do this. From my limited knowledge of MySQL I think maybe I could use one of the following: 1) Binary Logs - Not really sure how these work but I was thinking of maybe coping them to an off site machine every half hour and apply them in sequence if I need to go back to a point in time ? 2) Incremental Backups - say one every half hour, then a script to transfer that to an off site machine that way I can get the DB back to within the last good half hour... I don't really know much about either so if someone could give me some pointers as to which is best it would be much appreciated... Thanks, JC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ????: RE: comparison operations in IN subquery
In the last episode (May 15), raid fifa said:
Jerry Schwartz [EMAIL PROTECTED] :
look at the following test case:
mysql create table temp1( id int)ENGINE=innodb;
Query OK, 0 rows affected (0.18 sec)
mysql create table temp2( tid varchar(10))ENGINE=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql insert into temp1 values(1);
Query OK, 1 row affected (0.07 sec)
mysql insert into temp1 values(2);
Query OK, 1 row affected (0.05 sec)
mysql insert into temp1 values(3);
Query OK, 1 row affected (0.04 sec)
mysql insert into temp1 values(4);
Query OK, 1 row affected (0.04 sec)
mysql insert into temp2 values('2,3,4');
Query OK, 1 row affected (0.05 sec)
mysql commit;
Query OK, 0 rows affected (0.00 sec)
mysql select * from temp1;
+--+
| id |
+--+
| 1 |
| 2 |
| 3 |
| 4 |
+--+
4 rows in set (0.01 sec)
mysql select * from temp2;
+---+
| tid |
+---+
| 2,3,4 |
+---+
1 row in set (0.00 sec)
mysql select * from temp1 where id in (select tid from temp2);
+--+
| id |
+--+
| 2 |
+--+
1 row in set (0.00 sec)
The problem: Why there is result for the last SELECT statement???
How does mysql compare id with tid ?? they are different type and
have different format value.
[JS] MySQL converts the string to a number in this case. It stops at
the first character that cannot be part of a number.
thank you!
But if MySQL handles this case, the results of this query is not
what I want to get. So, is there some way to avoid it?
If you want to compare both fields as string, you will need to cast
your integer field:
select * from temp1 where CAST(id AS CHAR) in (select tid from temp2);
That will convert id to a character string, which will let mysql use
a string-string comparison against tid instead of trying to convert
both to numbers.
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
i execute follow sql.
select concat_ws('','d','\n','c');
the result is :
++
| concat_ws('','d','\n','c') |
++
| d
c|
++
There are no result as you said.
- Original Message -
From: Afan Pasalic [EMAIL PROTECTED]
To: ewen fortune [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, May 15, 2008 12:52 AM
Subject: Re: CONCAT doesn't work with NULL?
actually, this will not work for me (or I got it wrong :D) because I
need to have street, state and zip in one line and with separator
defined on the beginning it will put everything in separate lines.
:D
ewen fortune wrote:
Hi Afan,
You can use concat_ws
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws
---
CONCAT() returns NULL if any argument is NULL.
CONCAT_WS() does not skip empty strings. However, it does skip any
NULL values after the separator argument
---
Ewen
On Wed, May 14, 2008 at 5:53 PM, Afan Pasalic [EMAIL PROTECTED] wrote:
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization, '\n',
r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ', a.zip, '\n',
r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Site Attack/Failure Recovery
On Wed, May 14, 2008 at 10:25 PM, John Comerford [EMAIL PROTECTED] wrote: 2) Incremental Backups - say one every half hour, then a script to transfer that to an off site machine that way I can get the DB back to within the last good half hour... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Being compromised is not inevitable, but hardware failure is. Having trusted (an therefore tested) backups is the only way to operate. Is there some practice in particular you are concerned about? Blanket suggestion: Don't escape things manually, have the db (or emulation) do it for you using prepared statements. It is easier to code this way, and much more secure in the long run. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
