Query Question
For the given table:
FIELD TYPE COLLATION NULL
KEY DEFAULT Extra PRIVILEGES COMMENT
-
-- -- --- -- ---
---
job_coop VARCHAR(6)latin1_swedish_ci
PRI SELECT,INSERT,UPDATE,REFERENCES
ftp_serverVARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
ftp_login VARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
ftp_password VARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
ftp_mode ENUM('Production','Test','Both') latin1_swedish_ci YES
(NULL) SELECT,INSERT,UPDATE,REFERENCES
ftp_passive ENUM('Normal','Passive') latin1_swedish_ci YES
(NULL) SELECT,INSERT,UPDATE,REFERENCES
url_serverVARCHAR(255) latin1_swedish_ci
SELECT,INSERT,UPDATE,REFERENCES
url_port INT(11) NULL
0SELECT,INSERT,UPDATE,REFERENCES
I have situations where different rows have the same value in the
ftp_server column:
job_coop ftp_server ftp_loginftp_password
ftp_modeftp_passiveurl_serverurl_port
B1502715027dbs.nisc.lanmailroomca15027
Both Normal 15027dbs35000
B1512715027dbs.nisc.lanmailroomca15027
Both Normal 0
B1522715027dbs.nisc.lanmailroomca15027
Test Normal 0
I'd like to update the url_server and url_port fields in this example
for B15127 and B15227 to the values contained in B15027. There are other
examples as well. I would like a query that would update all instances
where the ftp_server values matched and where the url_server and
url_port have no assigned values and they would be updated from the
matching ftp_server that did have values in the url_server and url_port.
I imagine that this might require a join and perhaps a temporary table.
Please advise.
Dirk Bremer - Senior Systems Engineer - Utility - AMS
NISC Lake St. Louis MO - USA Central Time Zone
636-755-2652 fax 636-755-2502
dirk.bre...@nisc.coop www.nisc.coop
RE: Replication Issues
That appear to have been it, thanks to all for the assist and have a great weekend! Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: Thursday, March 26, 2009 18:00 To: mysql@lists.mysql.com Subject: Re: Replication Issues I notice in the startup messages there is an underscore in the file names, but there are dashes in the dir list you provided. ??? On 3/26/09, Dirk Bremer dirk.bre...@nisc.coop wrote: On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General
Replication Issues
We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop
RE: Replication Issues
On the master in the directory specified in the my.ini on the master, there are seven log-files: D:\Logsdir mysql*.* Volume in drive D is Local Disk Volume Serial Number is A46F-D8E7 Directory of D:\Logs 2009-03-25 00:1738,366,715 mysql-bin.01 2009-03-25 00:17 198 mysql-bin.02 2009-03-25 00:17 672 mysql-bin.03 2009-03-26 00:1856,190,604 mysql-bin.04 2009-03-26 00:18 672 mysql-bin.05 2009-03-26 16:5121,984,192 mysql-bin.06 2009-03-26 17:00 414,487 mysql-bin.07 2009-03-26 16:51 175 mysql-bin.index The contents of the mysql-bin.index are: D:\Logs\mysql-bin.01 D:\Logs\mysql-bin.02 D:\Logs\mysql-bin.03 D:\Logs\mysql-bin.04 D:\Logs\mysql-bin.05 D:\Logs\mysql-bin.06 D:\Logs\mysql-bin.07 There is nothing in the master's error-log. I followed the instructions here: http://forums.mysql.com/read.php?26,9390,242387#msg-242387 With negative results. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -Original Message- From: Claudio Nanni [mailto:claudio.na...@gmail.com] Sent: Thursday, March 26, 2009 16:18 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Replication Issues Hi Dirk, it seems that the information about binary files on the master contained in the mysql-bin.index does not match the actual files on the disk. The first two log messages are just info on the slave starting the two replication threads, the other two seem to be a message coming from the master which says I can't find the binary log file! check the content of the index file and check the binary log files actually present on the master. Cheers Claudio Dirk Bremer wrote: We recently completed a project where all of our production servers were replaced. As part of this project, one of the new servers acted as a replication-slave to one of the old servers, which was the replication-master. I had replication up and running just fine on multiple slaves for a couple of years. Last weekend I moved all functionality from the old server to the new server so that the new server is now setup at the replication-master. I am trying to setup replication-slaves on some of the other new servers and have run into problems doing so. In the my.ini for the master, there is: [mysqld] log-error=D:\Logs\mysql_error.log log-warnings server-id=1 lower_case_table_names=2 log-bin=D:\Logs\mysql-bin.log tmpdir=D:/Temp The master is creating the binary-log and index. There is a replication account setup on the master called replication. The slave is creating a relay-log and index, has what appears to be correct master.info and relay-log.info files. When I start the slave I get the following in the error log: 090325 9:03:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql_bin.04' at position 10120038, relay log '.\AMSCD2-relay-bin.02' position: 4 090325 9:03:30 [Note] Slave I/O thread: connected to master 'replicat...@amstcop:3306', replication started in log 'mysql_bin.04' at position 10120038 090325 9:03:30 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236) 090325 9:03:30 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log I'm sure that I've overlooked something, well, stupid, but have struggled with it the last few days to no avail. All servers are using the same version of 4.1.22-community-nt. I feel a bit naked without the replication so your assistance in helping me getting it up and running will be very appreciated. Dirk Bremer - Senior Systems Engineer - Utility - AMS NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 dirk.bre...@nisc.coop www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: zipcode to timezone
Indiana has two time zones as I recall. The state is divided roughly in half between the two. Dirk Bremer - Senior Systems Engineer - Utility - AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2502 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Hiep Nguyen [mailto:[EMAIL PROTECTED] Sent: Friday, July 06, 2007 10:51 To: David T. Ashley Cc: mysql@lists.mysql.com Subject: Re: zipcode to timezone On Fri, 6 Jul 2007, David T. Ashley wrote: On 7/6/07, Hiep Nguyen [EMAIL PROTECTED] wrote: we have warehouses all over U.S. and i just wonder what is the best way to find out their timezone base on zipcode. Should i buy a database or is there any function in mysql or php to get timezone base on a zipcode? I looked at the zipcode databases ... not as expensive as I would have thought. It might be worth it just to spend the $100 or so. However, ... My understanding is that U.S. zipcodes have their first two digits based on state, i.e. 48 is Michigan. Since most of the time zone boundaries seem to fall on state boundaries, a simple mapping from the first two digits to the time zone might get you most of the way there. http://images.google.com/imgres?imgurl=http://worldatlas.com/webimage/co untrys/namerica/usstates/timezone.gifimgrefurl=http://worldatlas.com/we bimage/countrys/namerica/usstates/timezone.htmh=307w=427sz=23tbnid=p XERv6TKqAu7DM:tbnh=91tbnw=126prev=/images%3Fq%3Du.s.%2Btime%2Bzone%2B map%26um%3D1start=2sa=Xoi=imagesct=imagecd=2 However, for those states that are split, I don't know an easy way ... but there shouldn't be very many of those. Dave. i don't think there is any state got 2 timezones, i could be wrong. but if that the case, state - timezone is working for me. now, how do i look up for timezone if i got state? i don't mind buying the database, but i just don't want to replace the database if something change down the road. thanks T. Hiep -- 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: Mysqlcheck issues
-Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 22:40 To: Dilipkumar; Dirk Bremer; mysql@lists.mysql.com Subject: RE: Mysqlcheck issues The trouble with myisamchk is that it requires the server to be offline. This may not be suitable. Do you have a bad area on the disk? The easiest way would be to stop the server briefly, rename the index thus keeping it occupying the potentially bad part of the disk and recreate the index. This would at least take the disk out of the equation if the problem re-occurs. There are reasons that I doubt this is a disk problem. That being said, I will try your advice the next time the index goes bad. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysqlcheck issues
-Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, July 27, 2006 22:42 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Mysqlcheck issues Dirk Bremer wrote: I am using MySQL server 4.1.10 on Windows 2003 Server with MyISAM tables. I have an issue where occasionally an index (MYI) file becomes corrupted. I do not know why this occurs. To combat this issue, I tried running the following command every half-hour: mysqlcheck -Aamov --auto-repair --use-frm This command runs on the host. For some reason, when this command executes, the MySQL service aborts and the MYI for the main table is corrupted. I must then restart the service and repair the affected table. Do you get any errors in the logfiles? Not sure why that would be corrupting the database tables, what user are you running this as (root, mysql) ? The mysql_error.log file does not reveal the problem. The mysqlcheck runs with administrative privileges under Windows and a privileged MySQl user. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysqlcheck issues
I am using MySQL server 4.1.10 on Windows 2003 Server with MyISAM tables. I have an issue where occasionally an index (MYI) file becomes corrupted. I do not know why this occurs. To combat this issue, I tried running the following command every half-hour: mysqlcheck -Aamov --auto-repair --use-frm This command runs on the host. For some reason, when this command executes, the MySQL service aborts and the MYI for the main table is corrupted. I must then restart the service and repair the affected table. I am at a loss here. I would really like to use the --auto-repair option, but don't understand what is causing the service to abort. Your thoughts? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relay-bin logs
I'm using MySQL 4.1 and the master runs on a Windows 2000 server. This master replicates to several slaves. While browsing the data directory on the master, there are a lot of binary log files that are named: MasterName-relay-bin.99 (where MasterName is the server-name and 99 is a six-digit number) I have about 350 of these files and they all appear to be held open by the mater, i.e. they cannot be deleted from Windows. I assume that these files have something to do with replication. How can I purge these files to a more manageable number? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT Question
Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Relay-bin logs
Dan, Thanks for your tips, but I still have an issue. Note the following: mysql PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); Query OK, 0 rows affected (0.01 sec) mysql PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 10 DAY); Query OK, 0 rows affected (0.01 sec) These command did not purge any of these logs. These logs range in creation-date from 12/30/2005 through today. Note this: mysql show master logs; +--+ | Log_name | +--+ | mysql_bin.000344 | | mysql_bin.000345 | | mysql_bin.000346 | +--+ 3 rows in set (0.00 sec) There are relay-bin files that correspond in the 6-digit number to the mysql-bin logs above. I was under the impression that the PURGE MASTER command would delete the mysql-bin files, not the replay-bin files. I was looking at the manual earlier and could not find a reference to deleting the relay-bin files. Thankfully, the relay-bin files are small in size, but I would still like to prune them. Thoughts? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:23 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Relay-bin logs Those do indeed have something to do with replication - they're a record of all data manipulation commands (inserts, updates, deletes, table creates and alters, etc). The slaves basically read the commands from those files in order to replicate what the master has done. You can purge them fairly easily. The one gotcha is that you will want to keep the most recent files around so that in case one or more of your slaves fall behind, you're not purging the logs out from underneath it. See http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html A nice command (taken from an example on that page) that gives you a moving window of binary log info is something like: PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); In my opinion running this command as a cron job or scheduled task is a great solution in most situations, better than purging to a specific file or purging them manually whenever you remember to (which is usually about 5 minutes after you run out of disk space). Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: I'm using MySQL 4.1 and the master runs on a Windows 2000 server. This master replicates to several slaves. While browsing the data directory on the master, there are a lot of binary log files that are named: MasterName-relay-bin.99 (where MasterName is the server-name and 99 is a six-digit number) I have about 350 of these files and they all appear to be held open by the mater, i.e. they cannot be deleted from Windows. I assume that these files have something to do with replication. How can I purge these files to a more manageable number? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- 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: LIMIT Question
Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. The goal of the query, which currently returns all of the results, is to find all entries for a given customer regardless of when they occurred. I would like no more than the last 50 rows inserted for this customer (this could be based upon the auto-increment value) and would prefer to have them ordered within the 50-possible results in the order they were inserted, from lowest-ID to the highest-ID. This will prevent the query from showing possible hundreds of results. There are multiple customers in the table. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:28 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Depends what you mean by last - you could show the 50 with the latest datestamps by ending your query with something like: ORDER BY datestampcolumn DESC LIMIT 50; or the 50 with the highest ID numbers, same thing: ORDER BY id DESC LIMIT 50; only real problem there is then they're sorted highest to lowest, but it is still the last 50. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- 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: Relay-bin logs
I should add I ran a FLUSH LOGS on the master and this had no effect on the relay-bin files. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dirk Bremer Sent: Wednesday, June 28, 2006 15:59 To: 'Dan Buettner' Cc: mysql@lists.mysql.com Subject: RE: Relay-bin logs Dan, Yes they are on the master. The master is not configured as a slave: mysql show slave status; Empty set (0.07 sec) mysql show master status; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql_bin.000346 | 14862859 | | | +--+--+--+--+ 1 row in set (0.00 sec) The relay-bin files contents are four characters: pbin They reside in the data directory. There is also a corresponding file: MAILCD3-relay-bin.index In this instance, MAILCD3 is the name of the server. There are least 346 of the relay-bin files. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:47 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Relay-bin logs My bad, Dirk, sorry. I missed that you were asking about relay-bin files, thought you were asking about bin files. These relay-bin files are on the master server? This doc: http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html makes it sound like they should only exist on the slave servers. Also makes it sound like they should be cleaned up automagically after a while. The master isn't or wasn't a slave to another server? I wonder if a FLUSH LOGS command would help things out. See URL above - it says There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Dan, Thanks for your tips, but I still have an issue. Note the following: mysql PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); Query OK, 0 rows affected (0.01 sec) mysql PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 10 DAY); Query OK, 0 rows affected (0.01 sec) These command did not purge any of these logs. These logs range in creation-date from 12/30/2005 through today. Note this: mysql show master logs; +--+ | Log_name | +--+ | mysql_bin.000344 | | mysql_bin.000345 | | mysql_bin.000346 | +--+ 3 rows in set (0.00 sec) There are relay-bin files that correspond in the 6-digit number to the mysql-bin logs above. I was under the impression that the PURGE MASTER command would delete the mysql-bin files, not the replay-bin files. I was looking at the manual earlier and could not find a reference to deleting the relay-bin files. Thankfully, the relay-bin files are small in size, but I would still like to prune them. Thoughts? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:23 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Relay-bin logs Those do indeed have something to do with replication - they're a record of all data manipulation commands (inserts, updates, deletes, table creates and alters, etc). The slaves basically read the commands from those files in order to replicate what the master has done. You can purge them fairly easily. The one gotcha is that you will want to keep the most recent files around so that in case one or more of your slaves fall behind, you're not purging the logs out from underneath it. See http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html A nice command (taken from an example on that page) that gives you a moving window of binary log info is something like: PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); In my opinion running this command as a cron job or scheduled task is a great solution in most situations, better than purging to a specific file or purging them manually whenever you remember to (which is usually about 5 minutes after you run
RE: Relay-bin logs
Dan, Yes they are on the master. The master is not configured as a slave: mysql show slave status; Empty set (0.07 sec) mysql show master status; +--+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--+--+--+--+ | mysql_bin.000346 | 14862859 | | | +--+--+--+--+ 1 row in set (0.00 sec) The relay-bin files contents are four characters: pbin They reside in the data directory. There is also a corresponding file: MAILCD3-relay-bin.index In this instance, MAILCD3 is the name of the server. There are least 346 of the relay-bin files. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:47 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Relay-bin logs My bad, Dirk, sorry. I missed that you were asking about relay-bin files, thought you were asking about bin files. These relay-bin files are on the master server? This doc: http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html makes it sound like they should only exist on the slave servers. Also makes it sound like they should be cleaned up automagically after a while. The master isn't or wasn't a slave to another server? I wonder if a FLUSH LOGS command would help things out. See URL above - it says There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Dan, Thanks for your tips, but I still have an issue. Note the following: mysql PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); Query OK, 0 rows affected (0.01 sec) mysql PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 10 DAY); Query OK, 0 rows affected (0.01 sec) These command did not purge any of these logs. These logs range in creation-date from 12/30/2005 through today. Note this: mysql show master logs; +--+ | Log_name | +--+ | mysql_bin.000344 | | mysql_bin.000345 | | mysql_bin.000346 | +--+ 3 rows in set (0.00 sec) There are relay-bin files that correspond in the 6-digit number to the mysql-bin logs above. I was under the impression that the PURGE MASTER command would delete the mysql-bin files, not the replay-bin files. I was looking at the manual earlier and could not find a reference to deleting the relay-bin files. Thankfully, the relay-bin files are small in size, but I would still like to prune them. Thoughts? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:23 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Relay-bin logs Those do indeed have something to do with replication - they're a record of all data manipulation commands (inserts, updates, deletes, table creates and alters, etc). The slaves basically read the commands from those files in order to replicate what the master has done. You can purge them fairly easily. The one gotcha is that you will want to keep the most recent files around so that in case one or more of your slaves fall behind, you're not purging the logs out from underneath it. See http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html A nice command (taken from an example on that page) that gives you a moving window of binary log info is something like: PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); In my opinion running this command as a cron job or scheduled task is a great solution in most situations, better than purging to a specific file or purging them manually whenever you remember to (which is usually about 5 minutes after you run out of disk space). Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: I'm using MySQL 4.1 and the master runs on a Windows 2000 server. This master replicates to several slaves. While browsing the data directory on the master, there are a lot of binary log files that are named: MasterName-relay-bin.99 (where MasterName is the server-name and 99 is a six-digit number) I have about 350 of these files and they all appear to be held open by the mater, i.e. they cannot be deleted from Windows. I
RE: LIMIT Question
Dan, Close, but there appears to be some differences under 4.1 which are interesting to say the least. Using: SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50 I get the expected result, i.e. 50 ordered in reverse. Using: (SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50) ORDER BY customertableid ASC; I get the full set (272 results) ordered in reverse. Here is the query: set @job_coop = 'B28013'; set @cycle = 1; set @type = 2; set @test = 1; set @cur_date=date_sub(curdate(),interval 7 day); (select ftp_transfers.queue.ident as 'ID', ftp_transfers.queue.job_coop as 'JCoop', ftp_transfers.queue.cycle as 'Cyc', lpad(ftp_transfers.queue.status,10,' ') as 'Status', case when (ftp_transfers.queue.type = 1) then 'Internal' when (ftp_transfers.queue.type = 2) then 'PDF To Coop' when (ftp_transfers.queue.type = 3) then 'iVUE Zip To Coop' when (ftp_transfers.queue.type = 4) then 'iVUE Zip To Mandan' endas 'Type', if(ftp_transfers.queue.test = 0,'No','Yes') as 'Test', lpad(format(ftp_transfers.queue.file_size,0),11,' ') as 'Size', substring(date_format(ftp_transfers.queue.queue_time,'%Y-%m-%d %T'),12,8) as 'q_time', ftp_transfers.queue.file_time as 'f_time', substring(ftp_transfers.queue.transfer_start,12,8) as 't_start', substring(ftp_transfers.queue.transfer_end,12,8) as 't_end' from ftp_transfers.queue where ftp_transfers.queue.job_coop = @job_coop and ftp_transfers.queue.type = @type # and ftp_transfers.queue.test = @test # and ftp_transfers.queue.cycle = @cycle # and (@cur_date = ftp_transfers.queue.queue_time) # and param5 not like '%ebi%' order by ftp_transfers.queue.ident desc limit 50) order by ftp_transfers.queue.ident asc I'm not in an immediate position to upgrade to 5.x at this point in time and this is a non-critical issue for me. Thanks for you help and advice. If someone has another solution, please chime in. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:54 To: Chris White Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Dirk, you could try this: (SELECT * FROM customertable WHERE some criteria ORDER BY customertableid DESC LIMIT 50) ORDER BY customertableid ASC; Like one sometimes does with UNIONs, but without any UNIONs. Didn't know whether it would work, but it does (on 5.0.21 anyway). That will give you the 50 entries with the highest ID numbers, sorted lowest to highest. Dan On 6/28/06, Chris White [EMAIL PROTECTED] wrote: On Wednesday 28 June 2006 01:39 pm, Dirk Bremer wrote: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. That's why ORDER BY id DESC is used, it basically flips your table reverse, so that last inserted (in essence the highest ID) is first, all the way down to the first inserted (the lowest ID). Then LIMIT 50 will give you 50 from highest id to lowest ID, or the last 50. -- Chris White PHP Programmer/DBlonde Interfuel -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicates, etc.
-Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 16:55 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: Finding duplicates, etc. Dirk, I would like to know where there ar duplicate accounts that do not have duplicate addresses. I think that at this point in time that these are included in the first query. I would like to separate these out to report on them. How about ... select account,ident,address from group15034_i g1 inner join group15034_i g2 using (account) where g1.address g2.address; Peter, You got me started on the right track. Here is what I ended up with that seems to satisfy my requirements: select distinct g1.account,g1.sub_account,g1.address from group15034 as g1 inner join group15034 as g2 using (account) where (g1.status = 'single') and (g1.address g2.address) order by account,sub_account; The distinct clause had the most effect on limiting the results to a manageable set. Thanks for your help and if anyone has any suggestions to refine this query, please let me know. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding duplicates, etc.
I have the following table: *Column Information For - production.group15034_I*/ FieldType Collation NullKey Default Extra Privileges Comment --- - - -- -- -- -- --- --- identint(11)NULL PRI (NULL) auto_increment select,insert,update,references account int(10) unsigned zerofill NULL 00 select,insert,update,references sub_account tinyint(3) unsignedNULL 0 select,insert,update,references address varchar(132) latin1_swedish_ci select,insert,update,references data text latin1_swedish_ci select,insert,update,references /*Index Information For - production.group15034_I*/ --- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed NullIndex_type Comment -- --- - --- -- -- -- --- group15034_I 0 PRIMARY 1 identA 0(NULL) (NULL) BTREE /*DDL Information For - production.group15034_I*/ - Table Create Table -- group15034_I CREATE TABLE `group15034_I` ( `ident` int(11) NOT NULL auto_increment, `account` int(10) unsigned zerofill NOT NULL default '00', `sub_account` tinyint(3) unsigned NOT NULL default '0', `address` varchar(132) NOT NULL default '', `data` text NOT NULL, PRIMARY KEY (`ident`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 The account and address fields will contain duplicate values under certain scenarios. I have the following 3 requirements: 1. Select the accounts that have unique accounts and addresses, i.e. not duplicate: select ident,account,address,count(*) as N,data from group15034_I group by account,address having N = 1 This select appears to work fine. 2. Select the acocunts that have both duplicate accounts and addresses: select account,address,count(*) as N from group15034_I group by account,address having N 1 This select appears to work fine. 3. I want the exceptions to the above two conditions. Specifically, I would like to know where there ar duplicate accounts that do not have duplicate addresses. I think that at this point in time that these are included in the first query. I would like to separate these out to report on them. I can't figure out a way to accomplish number 3 but know that in the given data set that this condition does occur. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Byte Swapping (Re Post)
-Original Message- From: David Godsey [mailto:[EMAIL PROTECTED] Sent: Friday, February 17, 2006 08:42 To: Dirk Bremer Cc: Gordon Bruce; [EMAIL PROTECTED]; gerald_clark; mysql@lists.mysql.com; David Godsey Subject: RE: Byte Swapping (Re Post) Endianess is byte ordering not bit ordering: http://www.cs.umass.edu/~verts/cs32/endian.html http://www.webopedia.com/TERM/b/big_endian.html Note that endian-ness can be expressed within a byte and within a word. It depends on how the data is delivered. If you were using a string representation of a number, it would be possible to reverse the bytes. If using a binary-form of a number that spans multiple bytes, simply reversing the bytes might not suffice. Again, it depends on the data. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: (mysqldump) Serial output. . .?
Rather than changing the function of the mysqldump program, why not massage its output to your specifications. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: mwilliams [mailto:[EMAIL PROTECTED] Sent: Thursday, February 16, 2006 14:44 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: (mysqldump) Serial output. . .? Shawn, I'd actually considered such, but I was hoping for it to already be present. I'm currently on a tight deadline to finish a project I'm working on so devoting time to getting 'mysqldump' stable enough to then propagate across corporate servers in such a short period is not very likely. As far as your comment regarding replication vs syncing, I have noticed the same thing. And it really blows my mind that so few people are interested in two-way syncing (e.g. I'll give you mine and you give me yours). This seems to be a necessity, and the very foundation for many corporate applications, yet it also apears that most are aparently proprietary. Anyway, thanks again for your input. Regards, Michael -- Original Message -- From: [EMAIL PROTECTED] Date: Thu, 16 Feb 2006 15:31:04 -0500 My suggestion: Modify the source of mysqldump yourself. After all, it is open source. Make sure you adhere to any and all licensing requirements and copyright notices and you will keep yourself out of any legal trouble. For the vast majority of users, replication is a better solution than what you propose so the changes you propose haven't been discussed at all. If others would like to have your changes, perhaps you would consider synching your mods with the main development tree and releasing them to the community? Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- 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: Byte Swapping (Re Post)
-Original Message- From: Gordon Bruce [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 14, 2006 15:42 To: [EMAIL PROTECTED]; gerald_clark Cc: mysql@lists.mysql.com; David Godsey Subject: RE: Byte Swapping (Re Post) If the order of the bytes is opposite between big-endian and little-endian, then if you can get the bytes in a string REVERSE() should flip the order. REVERSE would alter the order of the bytes. To convert between big-endian and little-endian, I believe that you need to reverse the order of the bits in either a byte or a word. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Nested scripts in MySQL?
-Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Monday, January 16, 2006 09:10 To: mysql Subject: Nested scripts in MySQL? Is it possible to run nested scripts in MySQL? I tried this with success. Create a file that contains lines like this: \. today.sql \. status.sql \. active.sql Save this file. Execute this file from the client. I did not try nesting more than this example (1-level), but suppose it would work for more. Note that the '\.' equates to the 'source' command. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculate LONG/LAT from ZIP+4
The USPS has been de-emphasizing the usage of carrier-routes for several years now. They will eventually phase them out completely in lieu of other schemes, including enhanced line-of-travel (ELOT), etc. The pool of eligible carrier-routes decreases every month (from the mailer's perspective). Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Kieran Kelleher [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 28, 2005 16:21 To: Brian Dunning Cc: mysql@lists.mysql.com Subject: Re: Calculate LONG/LAT from ZIP+4 One compromise between the large 5 digit zips and the 9-digit zip+4's is carrier route. There are about 600,000 carrier routes in the USA each denoted by the 5-digit zip and the carrier route, for example 34685-R036. 600,000 is certainly more manageable than 70,000,000 zip+4's. Does anyone know where you can buy USA carrier route lat/lng data? -Kieran Blog: http://webobjects.webhop.org/ On Jun 28, 2005, at 11:53 AM, Brian Dunning wrote: http://www.buyzips.com/platinum-expanded.htm This one also says it's only updated every 6 months. Ouch!! Another reason I recommend Zipwise instead. Cheaper and fresher data: http://www.zipwise.com -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Range
Whether he needs semicolons depends on which tool he is using to execute his statements. If he is using the MySQL client or any of several other tools, I agree. If he is going through an ODBC connection, he doesn't need them. (At least my 3.52.x drivers can't accept more than one statement per request.) I am using the command-line client. I have it working but it is off by one-month, run as is, it is showing July data rather than August. I am checking into it. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Range
- Original Message - From: [EMAIL PROTECTED] To: Dirk Bremer (NISC) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 27, 2004 11:40 Subject: Re: Date Range To compute the date range for two months ago. (if the current month is September, this will return July's data) set @dtBegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY) set @dtEnd = DATE_SUB(@dtBegOfMonth, INTERVAL 1 month) set @dtStart = DATE_SUB(@dtEnd, INTERVAL 1 month) Then, continue with the same query as above. These should be really fast as you are comparing constant values against what should be indexed table data. You also avoid string conversions, substring comparisons, and other data type conversions this way. The Date information stays date information The following produced the result set that I was interested in. set @BegOfMonth = DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY); set @Start = DATE_SUB(@BegOfMonth, INTERVAL 1 month); set @End = DATE_SUB(@BegOfMonth, INTERVAL 1 day); SELECT ident, job_coop, cycle, lpad(status,10,' ') as status, type, file_size, date_format(queue_time,'%Y-%m-%d %T') as queue_time, file_time, transfer_start, transfer_end FROM queue WHERE queue_time = @Start AND queue_time @End ORDER BY ident; Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Range
- Original Message - From: [EMAIL PROTECTED] To: Dirk Bremer (NISC) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, September 27, 2004 14:30 Subject: Re: Date Range I think you are very close but you are missing the LAST day of each month. I think you need to change your WHERE to read WHERE queue_time = @Start AND queue_time @BegOfMonth Reasoning: If @BegOfMonth is '2004-09-01' then @End will be '2004-08-31'. If queue_time is a datetime value somewhen during the day of 8/31 (like '2004-08-31 13:15:46') then you miss it with @End. Because of the time component, you still miss that event if you change the comparator to =. You need to compare to the next date (@BegOfMonth). '2004-08-31 13:15:46' '2004-08-31' is false '2004-08-31 13:15:46' = '2004-08-31' is false '2004-08-31 13:15:46' '2004-09-01' is true Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, You are correct. It also eliminates any purpose for the @End variable, which is fine. I just tested it and the results were correct. Thanks a lot. It's still Monday! Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Brainstorming' time!
Note that based upon this thread, I downloaded and installed MyODBC (simple Google search), configured a DSN, created an Excel query, and loaded the DB into the worksheet in under 5-minutes total time. While I don't use Excel that much for anything, it was a very simple exercise and might prove handy for some things. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: Eamon Daly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 20, 2004 13:55 Subject: Re: Brainstorming' time! 100 quatloos to Chris for the simplest answer. Do that. I'll add that you could also use mysqldump: mysqldump -T /tmp test users This would create /tmp/users.sql, containing the CREATE TABLE, and /tmp/users.txt, containing a tab-delimited file suitable for import. No header row, though. And, apparently, my perl solution completely reinvented the wheel. See pp.527-530 MySQL Cookbook, 1st ed. for a better script. Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, August 20, 2004 1:24 PM Subject: RE: Brainstorming' time! Ok unless I missed something here, wouldn't you just use the SELECT ... INTO OUTFILE Syntax found here http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html Just search for outfile and you can find it faster. You can run a query against a table and save the output into a file and in the case of creating excel type data, just save it into a file with the fields separated by commas, a CSV file in other words. Then use excel to open the file. -- 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]
Server Down/General Server Questions
I had an interesting situation to deal with this morning. The server that runs our production version of MySQL crashed with some type of kernel error (Win2K). When the server was brought back up, MySQL which runs as a service was not running when I checked on it this morning. I could not restart the service so I opened a command prompt and issued: mysqld --console This instance aborted saying that the InnoDB logfile size in the .cnf file did not match the actual logfile size on disk, which was zero. I have a single database defined that is MyISAM and don't use InnoDB at all at this point. I deleted all of the files that matched the pattern 'ib_logfile' from the mysql\data directory and successfully restarted the service. This begs a few questions: 1. Is there a way to configure the server to just support MyISAM databases, i.e. disabling the support for InnoDB? My though is that this would prevent the same error from happening again. 2. I have more or less a default installation and do not use a .cnf file for settings. I could not locate a .cnf file on the server other than the examples in the mysql root directory, i.e. my-huge, my-large, my-medium, and my-small. Why did the MySQL server complain about the logfile size from a .cnf file? Should I be using a .cnf file and where should it be located? I have but a single, small database with only two tables. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date/Time Difference Calculations
I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- 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: Date/Time Difference Calculations
Close, but time_to_sec requires a time argument, not a datetime argument. My next iteration is: select ident, transfer_start, transfer_end, sec_to_time(time_to_sec(substring(transfer_end,12,8)) - time_to_sec(substring(tra cast(transfer_end - transfer_start as signed) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 02:29:16 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:01:56 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:09:10 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | -23:11:07 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:00:52 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 00:20:34 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:00:35 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:20:14 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.01 sec) Which appears to be working correctly (Transfer Time1) except for ident = 1309, which spans a date boundary. So, back to the drawing board to figure out the date boundary issue. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Dirk Bremer (NISC) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:50 Subject: Re: Date/Time Difference Calculations select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start)); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Bingo, Shawn wins the virtual beer(s). I never looked at the unix_timestamp function. Thanks to everyone else and wishing you virtual beers as well! Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: [EMAIL PROTECTED] To: Dirk Bremer (NISC) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:54 Subject: Re: Date/Time Difference Calculations I didn't see where these were 4.1+ function so I think it will work. I refer you to: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into 'negative' time differences) SELECT sec_to_time(unix_timestamp(transfer_end) - unix_timestamp(transfer_start)) from queue; I know it will work for values up to 24 hours different. If sec_to_time is using an internal TIME data type value you get just under 840 hours of differential. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY Question
The following query produces the following results: select job_coop as 'Job/Coop', count(*) as Count from queue group by job_coop; +--+---+ | Job/Coop | Count | +--+---+ | B03013 |19 | | B05044 | 9 | | B07037 | 6 | | B15037 | 4 | | B16032 | 6 | | B17026 | 6 | | B17056 |18 | | B18032 | 5 | | B20009 |31 | | B21012 | 1 | | B24026 | 8 | | B25001 |42 | | B27043 |10 | | B27047 | 8 | | B29064 | 6 | | B31004 |61 | | B36035 |60 | | B36529 |54 | | B38023 |38 | | B38034 | 7 | | B40020 |30 | | D18032 |31 | | D27047 | 2 | | D31004 |59 | +--+---+ Is there a way to use the ORDER BY clause to order the results by the numeric value of the count(*), i.e. so that the results would be sorted by the result of the count(*)? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY Question
select job_coop as 'Job/Coop', count(*) as Count from queue group by job_coop order by Count; The other alternative is to omit the 'as Count' and use this query: select job_coop as 'Job/Coop', count(*) from queue group by job_coop order by 2; where the '2' in the 'order by' is the number of the column you are sorting. (The count(*) expression is the second column of the result set so you replace it with a 2). This saves you from having to use an 'As' expression for 'count(*)' although it makes the query less clear too. (It won't be apparent to some people what the effect of the '2' in the 'order by' is.) Rhino Thanks for all of the suggestions, the 'order by count' worked like a charm. Concerning Rhino's suggestion quoted above, it this method of using numbers to represent the columns documented anywhere? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Client Program Questions
At 16:03 -0500 4/20/04, Dirk Bremer (NISC) wrote: I have a couple of questions concerning the MySql client program. 1. Are the option for the client program, i.e. --auto-rehash, etc. documented anywhere? I searched the included HTML file and could not find a reference to the client options. What does the --auto-rehash option do? Heck, what do all of the rest of the options do? mysql --help http://dev.mysql.com/doc/mysql/en/mysql.html 2.When in the client program, what do the clear, ego, go, and rehash commands do? Is there documentation for these commands? http://dev.mysql.com/doc/mysql/en/mysql_Commands.html 3. Speaking specifically of Win2K platforms, what would be required to install just the client program for other users to be able to access the database server program residing on a different machine? Install MySQL normally, then throw away everything you don't need? -- Paul DuBois, MySQL Documentation Team Paul, I once spent a pleasant afternoon in Madison waiting on a rescheduled flight after a driving trip from Madison to LaCrosse and back. Note that the first link that you posted does not work, at least not with the current problems the website is experiencing. Concerning the second question I asked and the link that you provided, it still doesn't answer my question. For example: help(\h)Display this help. ? (\?)Synonym for `help'. clear (\c)Clear command. connect (\r)Reconnect to the server. Optional arguments are db and host. ego (\G)Send command to mysql server, display result vertically. exit(\q)Exit mysql. Same as quit. go (\g)Send command to mysql server. notee (\t)Don't write into outfile. print (\p)Print current command. prompt (\R)Change your mysql prompt. quit(\q)Quit mysql. rehash (\#)Rebuild completion hash. source (\.)Execute a SQL script file. Takes a file name as an argument. status (\s)Get status information from the server. tee (\T)Set outfile [to_outfile]. Append everything into given outfile. use (\u)Use another database. Takes database name as argument. Re the ego command, what commands can it send to the server and why would you want to use ego? Same for the clear and go command. For the third question, I direct my response to a wider audience and suggest that there should be a client-only installation download available. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Client Program Questions
I have a couple of questions concerning the MySql client program. 1. Are the option for the client program, i.e. --auto-rehash, etc. documented anywhere? I searched the included HTML file and could not find a reference to the client options. What does the --auto-rehash option do? Heck, what do all of the rest of the options do? 2.When in the client program, what do the clear, ego, go, and rehash commands do? Is there documentation for these commands? 3. Speaking specifically of Win2K platforms, what would be required to install just the client program for other users to be able to access the database server program residing on a different machine? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Query Question
I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:06 Subject: RE: SQL Query Question WHERE queue_time = Now() + 0 Are you wanting just the date or the datetime? -Original Message- From: Dirk Bremer (NISC) To: [EMAIL PROTECTED] Sent: 4/16/04 2:54 PM Subject: SQL Query Question I have a simple table where one of the columns is named queue_time and is defined as a timestamp-type. I would like to query this table for all rows where the queue_time equals the current date. I an a newbie and have been wrestling with the docs for a solution. You help will be appreciated. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc Victor, I just want to match the date, not the time, i.e. all of the rows for the current date regardless of the time they were entered. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Query Question
- Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Dirk Bremer (NISC) ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 16, 2004 15:57 Subject: RE: SQL Query Question If your data is stored in the following format 2004-04-16 00:00:00 you can do WHERE queue_time = CURRENT_DATE() + 0 You will also be able to take advantage of an index. Else, if you data is kept in the datetime format, 2004-04-16 15:53:27 one option is to do WHERE DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE() + 0 ...no index usage though Victor, The data defined as a timestamp, i.e. a number rather than a string, so it has MMDDHHMMSS values. So it looks like I'll need to do some type of substring on it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
