Issue with DATE_FORMAT() call returning wrong year
Hello all, I have a query that for some reason is now returning the incorrect year. First here is my server configuration. Windows 2003 Advanced server MySQL version 4.0.15-nt Here is my SQL statement: SELECT DATE_FORMAT(E.DateTime, '%X-%m-%d %h:%i:%s %p' ) AS DateTime, E.SendCount AS SendCount, E.TransactionStatus AS TransactionStatus, E.Message AS Message, TS.StatusDescription AS StatusDescription, TS.ErrorWarningInd AS ErrorWarningInd FROM errlog As E LEFT JOIN TransStatusTable AS TS ON E.TransactionStatus=TS.TransStatusCode WHERE AccountName='INSTILL000' AND Filename='SYSCO076_20040103125040.850' AND AccountScheduleDetailID=331 ORDER BY SendCount, DateTime, TransactionStatus The first column returned is the date. The values returned are such as '2003-01-03 02:50:16 PM' If I replace the DATE_FORMAT() call 'DATE_FORMAT(E.DateTime, '%X-%m-%d %h:%i:%s %p' ) AS DateTime' with a simple 'E.DateTime AS DateTime'. the returned value is now correct but the format is not. I have run this SQL statement from the mysql command line interface. I did initially use phpMyAdmin to see this. I just wanted to make sure this was not a PHP issue. Any thoughts? Paul
RE: Issue with DATE_FORMAT() call returning wrong year
I just saw thay in the online documentation. Sometimes it help to read the manual before posting. Sorry. [EMAIL PROTECTED] wrote: Try %Y instead of %X -Original Message- From: PAUL MENARD [mailto:[EMAIL PROTECTED] Sent: Monday, January 05, 2004 10:08 AM To: [EMAIL PROTECTED] Subject: Issue with DATE_FORMAT() call returning wrong year Hello all, I have a query that for some reason is now returning the incorrect year. First here is my server configuration. Windows 2003 Advanced server MySQL version 4.0.15-nt Here is my SQL statement: SELECT DATE_FORMAT(E.DateTime, '%X-%m-%d %h:%i:%s %p' ) AS DateTime, E.SendCount AS SendCount, E.TransactionStatus AS TransactionStatus, E.Message AS Message, TS.StatusDescription AS StatusDescription, TS.ErrorWarningInd AS ErrorWarningInd FROM errlog As E LEFT JOIN TransStatusTable AS TS ON E.TransactionStatus=TS.TransStatusCode WHERE AccountName='INSTILL000' AND Filename='SYSCO076_20040103125040.850' AND AccountScheduleDetailID=331 ORDER BY SendCount, DateTime, TransactionStatus The first column returned is the date. The values returned are such as '2003-01-03 02:50:16 PM' If I replace the DATE_FORMAT() call 'DATE_FORMAT(E.DateTime, '%X-%m-%d %h:%i:%s %p' ) AS DateTime' with a simple 'E.DateTime AS DateTime'. the returned value is now correct but the format is not. I have run this SQL statement from the mysql command line interface. I did initially use phpMyAdmin to see this. I just wanted to make sure this was not a PHP issue. Any thoughts? Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[MYSQL] Combine tables from two different systems
Hello All, I have two system, we shall call then system A (OLD) and system B (NEW), running MySQL that I want to combine their tables. The system B is the newer and most accurate. The table structure is the exact same. Some of the rows in system B (NEW) tables are also in system A (OLD). For those rows I want to leave the system B data unchanged. I have a select statement with a left join that will tell me the rows that are in system A but not in system B (the target). Question is how to I write a SQL statement to also insert these rows? System information. Windows NT4 (System A), Windows server 2003 (System B). MySQL 3.23.42-nt running on both systems. Paul
[MYSQL] Confused about DATETIME compare/subtraction
Hello All, I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds). Any help? FPM
How to terminate dead connections that are in SLEEP?
Good morning all, I have a problem with inceasing connection to my database that eventually result in connections being rejected. I know I have an application that is terminating a thread without closing the database handle. Problem is I'm waiting for the fix on some other changes. Here is my reseach. If I check the connections with 'SHOW PROCESSLIST'. I may have 30 connections showing. Some of these show a 'Time' value of 500+ and the 'Command' column reads 'Sleep'. If I check in 15 minutes this same ID will still show as connected but of course the 'Time' value will be greater. So, I checked my variables and for 'wait_timeout' I have this set to '65'. Question is how can I terminate these 'dead' connections? Paul
RE: How to terminate dead connections that are in SLEEP?
Sure I could create an external process that will poll the process list every few minutes but I was hoping for some direction on how to have the MySQL server do it internally. At least that was my impression from reading the docs on the 'wait_timeout' value. Paul Rudy Metzger [EMAIL PROTECTED] wrote: Kill them :) http://www.mysql.com/doc/en/KILL.html Cheers /rudy -Original Message- From: PAUL MENARD [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 16:44 To: [EMAIL PROTECTED] Subject: How to terminate dead connections that are in SLEEP? Good morning all, I have a problem with inceasing connection to my database that eventually result in connections being rejected. I know I have an application that is terminating a thread without closing the database handle. Problem is I'm waiting for the fix on some other changes. Here is my reseach. If I check the connections with 'SHOW PROCESSLIST'. I may have 30 connections showing. Some of these show a 'Time' value of 500+ and the 'Command' column reads 'Sleep'. If I check in 15 minutes this same ID will still show as connected but of course the 'Time' value will be greater. So, I checked my variables and for 'wait_timeout' I have this set to '65'. Question is how can I terminate these 'dead' connections? Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 and PHP/Perl Clients
Hi All, Im a member of a similar PHP email list and posed the question to community about issues anyone has experienced with being able to connect to MySQL 4.1. Below are my email and a response. Is this reply statement correct? Why would MySQL break an interface to clients? Read his comment #2 below. Can anyone here explain this. I do NOT wish to start using ODBC. Im going to panic now. On Wed, 2003-06-25 at 03:50, Paul Menard wrote: I've been considering upgrading my data , MySQL 3.23.42 to 4.013 or even going to 4.1. I've read on the MySQL upgrade documents that some client might be affected when upgrading to 4.1. It does specifically mention Perl which I use to supplement PHP. By I'm more concerned about PHP. Should I be concerned? Yes, it will not work at all. MySQL 4.1 uses a protocol that's not compatible with MySQL 3.23 and 4.0. You need to use the ext/mysqli extension for it to work, but there are two problems with this: 1) it is only available with PHP5 which is not released yet. 2) it cannot be legally used since the MySQL 4.1 client libraries have been re-licensed under the GPL (old versions of the client libraries used to be under the more friendly LGPL). The only way out of this mess would be to use the ODBC driver for MySQL, but that will likely not be at all as fast as the native driver. -- Best regards, Per Lundberg / Capio ApS Phone: +46-18-4186040 Fax: +46-18-4186049 Web: http://www.nobolt.com
[MYSQL] Question about 'SHOW PROCESSLIST' output columns
Hello All, I'm having an intermittent problem with a MySQL server that I have running on a Windows 2000 Advance server systems. This issue occurs every few days depending on the load of the system. Here are the details. MySQL version 3.23.42-nt (I know I need to upgrade. Soon). Machine: Windows 2000 Advanced Server 2G memory 36G disk space. I have a Web page that allows my operators to monitor various process tasks on this server. Sometimes they receive an error that PHP cannot connect to MySQL. If this happens during the day they call me. I log into the server and bring up the WinMySQLadmin 3.1 client and click on the 'Process' tab. There are a few (maybe 250 attached processes). I know there are many machines connecting to this database to feed updates from enterprise applications. So I bumped up the 'max_connections' limit to 1000. This just seems to delay the limit and the phone call. My question is in looking at the output form the 'Process' tab I see the columns 'Command' and 'Time'. In all of the rows for 'Command' the value is 90% 'Sleep'. This would tell me I have some processes out there that are not dis-connecting (right?). In the 'Time' column is see numbers ranging from 150 to 1000+. I'm guessing this is the time the connection is idle (correct?). What I would like to do is define my parameters so that these dead processes will be disconnected by MySQL after a shorter time but not sure which variable to change. Advice? Paul
Re: mysql 4.1 authentication problems
Funny. I was just readin about this on the www.mysql.com web site. Under the
documentation section about upgrading form 3.23 to 4.0. The exact URL is
http://www.mysql.com/doc/en/Upgrading-from-3.23.html
Ensure that you don't have any MySQL clients that use shared libraries (like the Perl
DBD-mysql mode). If you do, you should recompile them, because the data structures
used in `libmysqlclient.so' have changed. The same applies to other MySQL interfaces
as well, such as the Python MySQLdb module.
MySQL 4.0 will work even if you don't do the above, but you will not be able to use
the new security privileges that MySQL 4.0 and you may run into problems when
upgrading later to MySQL 4.1 or newer
[EMAIL PROTECTED] wrote:
Just installed mysql 4.1.
The same perl/DBIscripts which worked fine on 4.0 now failing toconnect
with the following error:
DBI connect('host=localhost;database=webdb','webdev',...) failed: Client
does not support authentication protocol requested by server. Consider
upgrading MySQL client at ./intro7.pl line 7
Any ideas, anyone?
Thank you.
Eugene
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Confused about MyISAM vs InnoDB tabel types
Can anyone either summarize for me a comparison between the MyISAM and InnoDB MySQL table type? I am getting ready to upgrade from MySQL 3.23.42 to 4.0.13 in the coming week and started reading the upgrade documents on the www.mysql.com site. Never had even thought about using another table type since my current database seems to work fine. But thought I would ask.
RE: SCO and getrusage()
Boyd, Thanks for the reply. I do have to agree with you however. I do have a system here at home in which client programs compile correctly with the SCO cc. I am still trying to figure out what is different from the development system at work and the system I recently built at home. Thanks, Paul -Original Message- From: Boyd Lynn Gerber [mailto:[EMAIL PROTECTED]] Sent: Monday, May 14, 2001 5:50 PM To: F. Paul Menard Cc: [EMAIL PROTECTED] Subject: Re: SCO and getrusage() Paul, I am not sure, but I think this is coming up because you need to use gcc libraries. I seem to remember that I tried to link libmysql... with the regular SCO compiler and I got this message. The regular SCO cc will not work. You have to use gcc-2.95.2 or newer, because the regular SCO compiler does not support data types needed by mysql. Good Luck, -- Boyd Gerber [EMAIL PROTECTED] ZENEZ 3748 Valley Forge Road, Magna Utah 84044 Office 801-250-0795 FAX 801-250-7975 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SCO and getrusage()
Hello all, I am having trouble compiling a client program on a SCO UNIX 5.0.4 I wrote a simple C program to connect to the mysql server which by the way is the binary distribution 3.23.36. I am using the SCO cc compiler and linker. I know how to compile programs on this platform. At least I used to think I knew what I was doing. What I have receiving is an error at link time about an unresolved reference to a symbol 'getrusage' in libmysqlclient.a. The development system I am using is my main system at my company. My goal is to migrate the development from using CTREE to mysql. I tried using various flavors of GCC over the past few weeks after reading many posting and suggestions. this produces more errors. As a test I took one of my old system from home. Put the SCO 5.0.4 system on it, added the default SCO development system, added the mysql distribution and magically the program compiled! Does anyone out there know where 'getrusage' lives. I see a reference to the function in my /usr/include/sys/resources.h header but man show nothing. I have also post a request to SCO about their libraries. Any information/direction would be greatly appreciated. Thanks, Paul Menard Houston, TX
