Re: Trigger
Hi, I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Instead of column, I guess you mean value? Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, If you want to store a date-value, use a date-datatype, not a character based datatype. `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Server not running
what is the error ur getting.
regards
anandkl
On 10/11/07, Daniel Bau [EMAIL PROTECTED] wrote:
Dear MySQL Support team ,
I have bis problems to upgrade my MySQL 3.23.58 Version on a Linux Red Hat
3 system.
I nearly download all MySQL Versions (4,5,6) and try to test them but
nowbody work.
I stopp my MySql Server, reinstall the old version and install the new
with rpm.
It never workes and when I instal the old version it works.
I hope you can help me, in attachment the mysql_bug.script result
Greetings from Germany
Bau Daniel
university for applied science
www.fh-offenburg.de
Der folgende Teil dieser Nachricht enthält einen Anhang im
sogenannten Internet MIME Nachrichtenformat.
Wenn Sie Pegasus Mail oder ein beliebiges anderes MIME-kompatibles
Email-System verwenden, sollte Sie den Anhang mit Ihrem Email-System
speichern oder anzeigen können. Anderenfalls fragen Sie Ihren
Administrator.
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any another MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
Datei Information/File information ---
Datei/File: problem.txt
Datum/Date: 11 Oct 2007, 16:12
Größe/Size: 3405 bytes.
Typ/Type:Text
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Server not running
Dear MySQL Support team ,
I have bis problems to upgrade my MySQL 3.23.58 Version on a Linux Red Hat 3
system.
I nearly download all MySQL Versions (4,5,6) and try to test them but nowbody
work.
I stopp my MySql Server, reinstall the old version and install the new with
rpm.
It never workes and when I instal the old version it works.
I hope you can help me, in attachment the mysql_bug.script result
Greetings from Germany
Bau Daniel
university for applied science
www.fh-offenburg.de
Der folgende Teil dieser Nachricht enthält einen Anhang im
sogenannten Internet MIME Nachrichtenformat.
Wenn Sie Pegasus Mail oder ein beliebiges anderes MIME-kompatibles
Email-System verwenden, sollte Sie den Anhang mit Ihrem Email-System
speichern oder anzeigen können. Anderenfalls fragen Sie Ihren Administrator.
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any another MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
Datei Information/File information ---
Datei/File: problem.txt
Datum/Date: 11 Oct 2007, 16:12
Größe/Size: 3405 bytes.
Typ/Type:Text
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
If i define that the value for the field must be varchar data type, `fecha` varchar(25) default NULL, the triger records the right date. I think because is wrinting a string only. But if i define the field as a date data type, `fecha` date default NULL, the value writen is: 1969-31-12. I suppose this is because the table is waiting for a date data type, but the triger sends a string data type. When you send an INSERT statement to the server, what value are you passing for the date column? If you want to store a DATE, send a date! Don't send a unixtime, or a varchar in some format, send a date. Why on earth would you go sending a unixtime to the server and then using a trigger to convert it into a real date?! im correct? if im, should CAST help me? No, sending the proper value, that's what you should be doing. And storing a date-value in a DATE datatype will only be more convenient later on. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Instead of column, I guess you mean value? Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, If you want to store a date-value, use a date-datatype, not a character based datatype. `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN (SELECT ...) -- no joy
using 5.0.24 mysql describe order_details; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | mediumint(8) unsigned | NO | PRI | NULL| auto_increment | | order_id | mediumint(8) unsigned | NO | MUL | | | | product_id | smallint(5) unsigned | NO | MUL | | | | quantity | smallint(5) unsigned | NO | | | | | unit_price | decimal(8,2) unsigned | NO | | | | ++---+--+-+-++ 5 rows in set (0.01 sec) mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal - FROM order_details GROUP BY order_id; +--+--+ | order_id | subtotal | +--+--+ |1 | 101.94 | |2 |47.97 | +--+--+ 2 rows in set (0.00 sec) -- same query used in LEFT JOIN clause: mysql SELECT po.id, po.customer_id, po.delivered, od.subtotal - FROM purchase_order AS po - LEFT JOIN (SELECT order_id, SUM(quantity * unit_price) - AS subtotal FROM order_details GROUP BY order_id) - AS od ON od.order_id = po.id - ORDER BY po.id\G *** 1. row *** id: 10001 customer_id: 1 delivered: 2007-10-10 23:51:32 subtotal: NULL *** 2. row *** id: 10002 customer_id: 2 delivered: 2007-10-10 23:51:32 subtotal: NULL 2 rows in set (0.00 sec) So, i'm wondering why this inner query is returning NULL for subtotal here. I know that SUM() returns NULL on error but i can't see how that's the situation here. This works fine with Postgres. Is this a bug, possibly? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
Mostly because use 2 field to represent the same data is a waste of storage i think. Oh, ok. So you're sending a non-date value and you want to transform it into an actual character presentation of a date value, but making things easier on yourself is a waste of storage. Feel free to continu jumping through hoops and try to solve the problem down the line instead of right there at the beginning. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. Righto. Well, what does this mean then: im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column Why not store the unixtime value and use an additional column to store an actual DATE value instead? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
Mostly because use 2 field to represent the same data is a waste of storage i think. - Martijn Tonies [EMAIL PROTECTED] escribió: The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. Righto. Well, what does this mean then: im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column Why not store the unixtime value and use an additional column to store an actual DATE value instead? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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]
Re: Trigger
The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. Righto. Well, what does this mean then: im converting the unixtime to normal time with from_unixtime. So after i did the convertion i write the result to the table. It works ok when i write to a varchar column, but not to a date column Why not store the unixtime value and use an additional column to store an actual DATE value instead? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger
The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help. The application pass the 'xx.xx' value when is inserting to the table. - Martijn Tonies [EMAIL PROTECTED] escribió: If i define that the value for the field must be varchar data type, `fecha` varchar(25) default NULL, the triger records the right date. I think because is wrinting a string only. But if i define the field as a date data type, `fecha` date default NULL, the value writen is: 1969-31-12. I suppose this is because the table is waiting for a date data type, but the triger sends a string data type. When you send an INSERT statement to the server, what value are you passing for the date column? If you want to store a DATE, send a date! Don't send a unixtime, or a varchar in some format, send a date. Why on earth would you go sending a unixtime to the server and then using a trigger to convert it into a real date?! im correct? if im, should CAST help me? No, sending the proper value, that's what you should be doing. And storing a date-value in a DATE datatype will only be more convenient later on. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Instead of column, I guess you mean value? Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, If you want to store a date-value, use a date-datatype, not a character based datatype. `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; -- 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: MySQL Server not running
Given that FEHLGESCHLAGEN means failed and coupled with the proceding
text, I would assume that you don't have mysql installed in the first
place. If you are on an rpm based system, try the following:
rpm -q mysql
If you don't get anything back, you need to reinstall.
Ananda Kumar wrote:
if u do ps -ef | grep mysql, what do u get.
regards
anandkl
On 10/12/07, Daniel Bau [EMAIL PROTECTED] wrote:
He say he can not connect to the server -- no server is installed and no
socket is in
var/lib/mysql
When I install it he want to start the server and say FEHLGESCHLAGEN
No mysql server or manager
Or can not connect to mysql.sock
So he not realy install the server and say me he has a problem with the
mysql_install_db but
dont tell me what for problem.
The message say i should execute the bug script and send it to you .
Greetings
Bau Daniel
Datum: Fri, 12 Oct 2007 14:45:45 +0530
Von:Ananda Kumar [EMAIL PROTECTED]
An: Daniel Bau [EMAIL PROTECTED]
Betreff:Re: MySQL Server not running
Kopie an: mysql@lists.mysql.com
what is the error ur getting.
regards
anandkl
On 10/11/07, Daniel Bau [EMAIL PROTECTED] wrote:
Dear MySQL Support team ,
I have bis problems to upgrade my MySQL 3.23.58 Version on a Linux Red
Hat
3 system.
I nearly download all MySQL Versions (4,5,6) and try to test them but
nowbody work.
I stopp my MySql Server, reinstall the old version and install the new
with rpm.
It never workes and when I instal the old version it works.
I hope you can help me, in attachment the mysql_bug.script result
Greetings from Germany
Bau Daniel
university for applied science
www.fh-offenburg.de
Der folgende Teil dieser Nachricht enthält einen Anhang im
sogenannten Internet MIME Nachrichtenformat.
Wenn Sie Pegasus Mail oder ein beliebiges anderes MIME-kompatibles
Email-System verwenden, sollte Sie den Anhang mit Ihrem Email-System
speichern oder anzeigen können. Anderenfalls fragen Sie Ihren
Administrator.
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any another MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
Datei Information/File information ---
Datei/File: problem.txt
Datum/Date: 11 Oct 2007, 16:12
Größe/Size: 3405 bytes.
Typ/Type:Text
--
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: MySQL Server not running
He say he can not connect to the server -- no server is installed and no
socket is in
var/lib/mysql
When I install it he want to start the server and say FEHLGESCHLAGEN
No mysql server or manager
Or can not connect to mysql.sock
So he not realy install the server and say me he has a problem with the
mysql_install_db but
dont tell me what for problem.
The message say i should execute the bug script and send it to you .
Greetings
Bau Daniel
Datum: Fri, 12 Oct 2007 14:45:45 +0530
Von:Ananda Kumar [EMAIL PROTECTED]
An: Daniel Bau [EMAIL PROTECTED]
Betreff:Re: MySQL Server not running
Kopie an: mysql@lists.mysql.com
what is the error ur getting.
regards
anandkl
On 10/11/07, Daniel Bau [EMAIL PROTECTED] wrote:
Dear MySQL Support team ,
I have bis problems to upgrade my MySQL 3.23.58 Version on a Linux Red Hat
3 system.
I nearly download all MySQL Versions (4,5,6) and try to test them but
nowbody work.
I stopp my MySql Server, reinstall the old version and install the new
with rpm.
It never workes and when I instal the old version it works.
I hope you can help me, in attachment the mysql_bug.script result
Greetings from Germany
Bau Daniel
university for applied science
www.fh-offenburg.de
Der folgende Teil dieser Nachricht enthält einen Anhang im
sogenannten Internet MIME Nachrichtenformat.
Wenn Sie Pegasus Mail oder ein beliebiges anderes MIME-kompatibles
Email-System verwenden, sollte Sie den Anhang mit Ihrem Email-System
speichern oder anzeigen können. Anderenfalls fragen Sie Ihren
Administrator.
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any another MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
Datei Information/File information ---
Datei/File: problem.txt
Datum/Date: 11 Oct 2007, 16:12
Größe/Size: 3405 bytes.
Typ/Type:Text
--
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: MySQL Server not running
if u do ps -ef | grep mysql, what do u get. regards anandkl On 10/12/07, Daniel Bau [EMAIL PROTECTED] wrote: He say he can not connect to the server -- no server is installed and no socket is in var/lib/mysql When I install it he want to start the server and say FEHLGESCHLAGEN No mysql server or manager Or can not connect to mysql.sock So he not realy install the server and say me he has a problem with the mysql_install_db but dont tell me what for problem. The message say i should execute the bug script and send it to you . Greetings Bau Daniel Datum: Fri, 12 Oct 2007 14:45:45 +0530 Von:Ananda Kumar [EMAIL PROTECTED] An: Daniel Bau [EMAIL PROTECTED] Betreff:Re: MySQL Server not running Kopie an: mysql@lists.mysql.com what is the error ur getting. regards anandkl On 10/11/07, Daniel Bau [EMAIL PROTECTED] wrote: Dear MySQL Support team , I have bis problems to upgrade my MySQL 3.23.58 Version on a Linux Red Hat 3 system. I nearly download all MySQL Versions (4,5,6) and try to test them but nowbody work. I stopp my MySql Server, reinstall the old version and install the new with rpm. It never workes and when I instal the old version it works. I hope you can help me, in attachment the mysql_bug.script result Greetings from Germany Bau Daniel university for applied science www.fh-offenburg.de Der folgende Teil dieser Nachricht enthält einen Anhang im sogenannten Internet MIME Nachrichtenformat. Wenn Sie Pegasus Mail oder ein beliebiges anderes MIME-kompatibles Email-System verwenden, sollte Sie den Anhang mit Ihrem Email-System speichern oder anzeigen können. Anderenfalls fragen Sie Ihren Administrator. The following section of this message contains a file attachment prepared for transmission using the Internet MIME message format. If you are using Pegasus Mail, or any another MIME-compliant system, you should be able to save it or view it from within your mailer. If you cannot, please ask your system administrator for assistance. Datei Information/File information --- Datei/File: problem.txt Datum/Date: 11 Oct 2007, 16:12 Größe/Size: 3405 bytes. Typ/Type:Text -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: open_files_limit problem.
Paul, Take a look at the ulimit command. We had a similar issue, and it turns out there are OS level per-process limits as well. We have now configured the /etc/init.d/mysql file set the ulimit to what we need. here is the snippet form the file: echo $echo_n Starting MySQL ulimit -n 4096 We just added the ulimit line. Hope that helps. Keith - Original Message - From: Paul Halliday [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, October 11, 2007 3:02:27 PM (GMT-0500) America/New_York Subject: open_files_limit problem. I am trying to change this value and it doesn't seem to work. Looking at the processes I have: mysql 21752 0.0 0.1 1652 1092 p3 I 3:50PM 0:00.01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid mysql 21770 0.0 1.3 58188 26168 p3 S 3:50PM 0:00.21 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --pid-file=/var/db/mysql/nsccmadb2.campus.nscc.ca.pid Within /var/db/mysql/my.cnf I have: [mysqld_safe] open_files_limit = 32768 [mysqld] open_files_limit = 32768 But when I try something like mysql show variables like '%files%';: I get: open_files_limit 11095 Is there another variable that needs to be adjusted to bump this up? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- B. Keith Murphy Database Administrator iContact 2635 Meridian Parkway, 2nd Floor Durham, North Carolina 27713 blog: http://blog.paragon-cs.com (o) 919-433-0786 (c) 850-637-3877
Re: Trigger
Martijn, If i define that the value for the field must be varchar data type, `fecha` varchar(25) default NULL, the triger records the right date. I think because is wrinting a string only. But if i define the field as a date data type, `fecha` date default NULL, the value writen is: 1969-31-12. I suppose this is because the table is waiting for a date data type, but the triger sends a string data type. im correct? if im, should CAST help me? Thanks - Martijn Tonies [EMAIL PROTECTED] escribió: Hi, I have a problem with a trigger which should conver a unix timestamp to a MySQL date datatype. The trigger works if the column is varchar, but when the column is date type, it write the date of 1969-31-12. Instead of column, I guess you mean value? Any ideas? DROP TABLE IF EXISTS `visitas`; CREATE TABLE `visitas` ( `id` int(11) NOT NULL auto_increment, `date` varchar(25) default NULL, If you want to store a date-value, use a date-datatype, not a character based datatype. `elapsed` int default NULL, `src_ip` varchar(15) default NULL, `result_code` varchar(25) default NULL, `http_status` TINYINT default NULL, `bytes` int default NULL, `request` varchar(50) default NULL, `authname` varchar(10) default NULL, `type` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; DELIMITER ;; /*!50003 SET SESSION SQL_MODE=STRICT_TRANS_TABLES */;; /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ /*!50003 TRIGGER `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin set New.date=date(from_unixtime(New.date)); end */;; Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.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]
Need help with a natural sort order for version numbers and release code names
I'm trying to get some 'release/version numbers' to sort properly. mysql SELECT ReleaseID, Name FROM releases ORDER BY Name DESC; +---+-+ | ReleaseID | Name| +---+-+ |18 | Unspecified | |20 | Next Patch | |58 | LOCset | |74 | Abashiri| |54 | 4.6.0 (Folsom) | -- 4.5.10 should be here |99 | 4.5.9 | |98 | 4.5.6 | |93 | 4.5.5 (Purdy) | |97 | 4.5.4 | |96 | 4.5.3 | |94 | 4.5.2 | | 100 | 4.5.10 | -- should be ^ there |91 | 4.5.1 Deferred | |78 | 4.5.1 (Leavenworth) | |95 | 4.2.7.4 | |92 | 4.2.7.3 | |90 | 4.2.7.2 | |87 | 4.2.7.1 | |88 | 4.2.7.0 | I like this order, especially with the top four, except for that 4.5.10 should be higher up, just under 4.6.0, not under 4.5.2 as it is now. So I tried the + 0 trick which makes things even worse (notice the 4.2.6.1 and 4.2.6.0 -- yipes!): mysql SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC; +---+-+ | ReleaseID | Name| +---+-+ (18,20,58,74) are moved :( |54 | 4.6.0 (Folsom) | |78 | 4.5.1 (Leavenworth) | | 100 | 4.5.10 | |91 | 4.5.1 Deferred | |93 | 4.5.5 (Purdy) | |94 | 4.5.2 | |96 | 4.5.3 | |97 | 4.5.4 | |98 | 4.5.6 | |99 | 4.5.9 | |82 | 4.2.6.1 |? |76 | 4.2.2 | |75 | 4.2.4 | |72 | 4.2.1 | |73 | 4.2.3 | |67 | 4.2.6.0 |? I'm pretty sure this is going to involve some sort of splitting the version from the release codeword via some string functions, and then operating on that part. D.Vin http://daevid.com --- eval() is my favorite templating engine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not In join query.
I have 2 queries to give me a list of names. Q1: SELECT DISTINCT FName, LName FROM user u JOIN userprofile p USING ( UserID ) JOIN trainingstatus t USING ( UserID ) WHERE ProgramID =12 ORDER BY LName, FName Q2 SELECT DISTINCT FName, LName FROM namelist WHERE `Date` What I need is query that will give me a list of names that are in the Q2 result but not in the Q1 result. This is easy enough if I am just doing the match on one filed I can do this SELECT Name FROM namelist WHERE `Date` AND Name NOT IN( SELECT Name FROM . . . . . . ) What I can't figure out is how to do it if I want to match of FName and LName. I tried to use concat to build the full name and do the not in based on the new field MySQL didn't like that query at all. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a natural sort order for version numbers and release code names
Daevid Vincent wrote: I'm trying to get some 'release/version numbers' to sort properly. mysql SELECT ReleaseID, Name FROM releases ORDER BY Name DESC; +---+-+ | ReleaseID | Name| +---+-+ |18 | Unspecified | |20 | Next Patch | |58 | LOCset | |74 | Abashiri| |54 | 4.6.0 (Folsom) | -- 4.5.10 should be here |99 | 4.5.9 | |98 | 4.5.6 | |93 | 4.5.5 (Purdy) | |97 | 4.5.4 | |96 | 4.5.3 | |94 | 4.5.2 | | 100 | 4.5.10 | -- should be ^ there |91 | 4.5.1 Deferred | |78 | 4.5.1 (Leavenworth) | |95 | 4.2.7.4 | |92 | 4.2.7.3 | |90 | 4.2.7.2 | |87 | 4.2.7.1 | |88 | 4.2.7.0 | I like this order, especially with the top four, except for that 4.5.10 should be higher up, just under 4.6.0, not under 4.5.2 as it is now. So I tried the + 0 trick which makes things even worse (notice the 4.2.6.1 and 4.2.6.0 -- yipes!): mysql SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC; What about ORDER BY REPLACE(Name, '.', '') + 0 DESC? +---+-+ | ReleaseID | Name| +---+-+ (18,20,58,74) are moved :( |54 | 4.6.0 (Folsom) | |78 | 4.5.1 (Leavenworth) | | 100 | 4.5.10 | |91 | 4.5.1 Deferred | |93 | 4.5.5 (Purdy) | |94 | 4.5.2 | |96 | 4.5.3 | |97 | 4.5.4 | |98 | 4.5.6 | |99 | 4.5.9 | |82 | 4.2.6.1 |? |76 | 4.2.2 | |75 | 4.2.4 | |72 | 4.2.1 | |73 | 4.2.3 | |67 | 4.2.6.0 |? I'm pretty sure this is going to involve some sort of splitting the version from the release codeword via some string functions, and then operating on that part. D.Vin http://daevid.com --- eval() is my favorite templating engine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN (SELECT ...) -- no joy
Andrew Carlson wrote: Is there a purchase order (10002) with no orders in the order_details table? No, PO 10002 is related to the order_id = 2 here: mysql SELECT order_id, SUM(quantity * unit_price) AS subtotal - FROM order_details GROUP BY order_id; +--+--+ | order_id | subtotal | +--+--+ |1 | 101.94 | |2 |47.97 | +--+--+ But i'm getting NULL for subtotal on all of them, anyway. The same query works as exactly as expected in Postgres with some table modifications to allow for the different datatypes. The outer query should have no problem grabbing the correct subtotal from the joined query. b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with a natural sort order for version numbers and release code names
-Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Friday, October 12, 2007 2:07 PM To: Daevid Vincent Cc: mysql@lists.mysql.com Subject: Re: Need help with a natural sort order for version numbers and release code names Daevid Vincent wrote: I'm trying to get some 'release/version numbers' to sort properly. mysql SELECT ReleaseID, Name FROM releases ORDER BY Name DESC; +---+-+ | ReleaseID | Name| +---+-+ |18 | Unspecified | |20 | Next Patch | |58 | LOCset | |74 | Abashiri| |54 | 4.6.0 (Folsom) | -- 4.5.10 should be here |99 | 4.5.9 | |98 | 4.5.6 | |93 | 4.5.5 (Purdy) | |97 | 4.5.4 | |96 | 4.5.3 | |94 | 4.5.2 | | 100 | 4.5.10 | -- should be ^ there |91 | 4.5.1 Deferred | |78 | 4.5.1 (Leavenworth) | |95 | 4.2.7.4 | |92 | 4.2.7.3 | |90 | 4.2.7.2 | |87 | 4.2.7.1 | |88 | 4.2.7.0 | I like this order, especially with the top four, except for that 4.5.10 should be higher up, just under 4.6.0, not under 4.5.2 as it is now. So I tried the + 0 trick which makes things even worse (notice the 4.2.6.1 and 4.2.6.0 -- yipes!): mysql SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC; +---+-+ | ReleaseID | Name| +---+-+ (18,20,58,74) are moved :( |54 | 4.6.0 (Folsom) | |78 | 4.5.1 (Leavenworth) | | 100 | 4.5.10 | |91 | 4.5.1 Deferred | |93 | 4.5.5 (Purdy) | |94 | 4.5.2 | |96 | 4.5.3 | |97 | 4.5.4 | |98 | 4.5.6 | |99 | 4.5.9 | |82 | 4.2.6.1 |? |76 | 4.2.2 | |75 | 4.2.4 | |72 | 4.2.1 | |73 | 4.2.3 | |67 | 4.2.6.0 |? I'm pretty sure this is going to involve some sort of splitting the version from the release codeword via some string functions, and then operating on that part. What about ORDER BY REPLACE(Name, '.', '') + 0 DESC? Thanks for the tip Jay, but nope. It's all out of whack then. mysql SELECT ReleaseID, Name FROM releases ORDER BY REPLACE(Name, '.', '') + 0 DESC; +---+-+ | ReleaseID | Name| +---+-+ | 100 | 4.5.10 | |95 | 4.2.7.4 | |92 | 4.2.7.3 | |90 | 4.2.7.2 | |87 | 4.2.7.1 | |88 | 4.2.7.0 | |86 | 4.2.6.3 | |83 | 4.2.6.2 | |82 | 4.2.6.1 | |67 | 4.2.6.0 | |85 | 4.2.5.4 | |84 | 4.2.5.3 | |80 | 4.2.5.2 | |79 | 4.2.5.1 | |54 | 4.6.0 (Folsom) | |99 | 4.5.9 | |98 | 4.5.6 | |93 | 4.5.5 (Purdy) | |97 | 4.5.4 | |96 | 4.5.3 | |94 | 4.5.2 | |91 | 4.5.1 Deferred | |78 | 4.5.1 (Leavenworth) | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a natural sort order for version numbers and release code names
Here's my full test solution:
use test;
drop table if exists releases;
create temporary table releases
(
releaseid int(10) unsigned not null auto_increment primary key,
name varchar(255)
)engine=myisam;
insert into releases ( name ) values
( 'Unspecified' ),
( 'Next Patch' ),
( 'LOCset' ),
( 'Abashiri' ),
( '4.6.0 (Folsom)' ),
( '4.5.9' ),
( '4.5.6' ),
( '4.5.5 (Purdy)' ),
( '4.5.5' ),
( '4.5.4' ),
( '4.5.3' ),
( '4.5.2' ),
( '4.5.10' ),
( '4.5.1 Deferred' ),
( '4.5.1 (Leavenworth)' ),
( '4.2.7.4' ),
( '4.2.7.3' ),
( '4.2.7.2' ),
( '4.2.7.1' ),
( '4.2.7.0' );
select
releaseid,
name
from
releases
order by
case name
when 'Unspecified' then 0
when 'Next Patch' then 1
when 'LOCset' then 2
else 10
end,
if(
locate( ' ', name ) 0 and locate( '.', name ) 0,
inet_aton( substr( name, 1, locate( ' ', name ) - 1 ) ),
if(
locate( '.', name ) 0,
inet_aton( name ),
4294967295 ) ) desc,
if(
locate( ' ', name ) 0 and locate( '.', name ) 0,
replace( replace( substr( name, locate( ' ', name ) + 1 ), '(',
'' ), ')', '' ),
if(
locate( '.', name ) = 0,
name,
null ) );
This is the end result after talking to Daevid and finding out what
the ultimate goal was. I think it's kind of clever myself :)
Yes, there's no possibility of indexing here, but it gets the job done.
Adam.
On Oct 12, 2007, at 12:39 PM, Daevid Vincent wrote:
I'm trying to get some 'release/version numbers' to sort properly.
mysql SELECT ReleaseID, Name FROM releases ORDER BY Name DESC;
+---+-+
| ReleaseID | Name|
+---+-+
|18 | Unspecified |
|20 | Next Patch |
|58 | LOCset |
|74 | Abashiri|
|54 | 4.6.0 (Folsom) |
-- 4.5.10 should be here
|99 | 4.5.9 |
|98 | 4.5.6 |
|93 | 4.5.5 (Purdy) |
|97 | 4.5.4 |
|96 | 4.5.3 |
|94 | 4.5.2 |
| 100 | 4.5.10 | -- should be ^ there
|91 | 4.5.1 Deferred |
|78 | 4.5.1 (Leavenworth) |
|95 | 4.2.7.4 |
|92 | 4.2.7.3 |
|90 | 4.2.7.2 |
|87 | 4.2.7.1 |
|88 | 4.2.7.0 |
I like this order, especially with the top four,
except for that 4.5.10 should be higher up,
just under 4.6.0, not under 4.5.2 as it is now.
So I tried the + 0 trick which makes things even worse
(notice the 4.2.6.1 and 4.2.6.0 -- yipes!):
mysql SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC;
+---+-+
| ReleaseID | Name|
+---+-+
(18,20,58,74) are moved
:(
|54 | 4.6.0 (Folsom) |
|78 | 4.5.1 (Leavenworth) |
| 100 | 4.5.10 |
|91 | 4.5.1 Deferred |
|93 | 4.5.5 (Purdy) |
|94 | 4.5.2 |
|96 | 4.5.3 |
|97 | 4.5.4 |
|98 | 4.5.6 |
|99 | 4.5.9 |
|82 | 4.2.6.1 |?
|76 | 4.2.2 |
|75 | 4.2.4 |
|72 | 4.2.1 |
|73 | 4.2.3 |
|67 | 4.2.6.0 |?
I'm pretty sure this is going to involve some sort of splitting the
version
from the release codeword via some string functions, and then
operating on
that part.
D.Vin
http://daevid.com
---
eval() is my favorite templating engine.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Adam Randall
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a natural sort order for version numbers and release code names
Oh, and here's the output (Sorry):
+---+-+
| releaseid | name|
+---+-+
| 1 | Unspecified |
| 2 | Next Patch |
| 3 | LOCset |
| 4 | Abashiri|
| 5 | 4.6.0 (Folsom) |
|13 | 4.5.10 |
| 6 | 4.5.9 |
| 7 | 4.5.6 |
| 9 | 4.5.5 |
| 8 | 4.5.5 (Purdy) |
|10 | 4.5.4 |
|11 | 4.5.3 |
|12 | 4.5.2 |
|14 | 4.5.1 Deferred |
|15 | 4.5.1 (Leavenworth) |
|16 | 4.2.7.4 |
|17 | 4.2.7.3 |
|18 | 4.2.7.2 |
|19 | 4.2.7.1 |
|20 | 4.2.7.0 |
+---+-+
20 rows in set (0.00 sec)
Adam.
On Oct 12, 2007, at 3:56 PM, Adam Randall wrote:
Here's my full test solution:
use test;
drop table if exists releases;
create temporary table releases
(
releaseid int(10) unsigned not null auto_increment primary key,
name varchar(255)
)engine=myisam;
insert into releases ( name ) values
( 'Unspecified' ),
( 'Next Patch' ),
( 'LOCset' ),
( 'Abashiri' ),
( '4.6.0 (Folsom)' ),
( '4.5.9' ),
( '4.5.6' ),
( '4.5.5 (Purdy)' ),
( '4.5.5' ),
( '4.5.4' ),
( '4.5.3' ),
( '4.5.2' ),
( '4.5.10' ),
( '4.5.1 Deferred' ),
( '4.5.1 (Leavenworth)' ),
( '4.2.7.4' ),
( '4.2.7.3' ),
( '4.2.7.2' ),
( '4.2.7.1' ),
( '4.2.7.0' );
select
releaseid,
name
from
releases
order by
case name
when 'Unspecified' then 0
when 'Next Patch' then 1
when 'LOCset' then 2
else 10
end,
if(
locate( ' ', name ) 0 and locate( '.', name ) 0,
inet_aton( substr( name, 1, locate( ' ', name ) - 1 ) ),
if(
locate( '.', name ) 0,
inet_aton( name ),
4294967295 ) ) desc,
if(
locate( ' ', name ) 0 and locate( '.', name ) 0,
replace( replace( substr( name, locate( ' ', name ) + 1 ), '(',
'' ), ')', '' ),
if(
locate( '.', name ) = 0,
name,
null ) );
This is the end result after talking to Daevid and finding out what
the ultimate goal was. I think it's kind of clever myself :)
Yes, there's no possibility of indexing here, but it gets the job
done.
Adam.
On Oct 12, 2007, at 12:39 PM, Daevid Vincent wrote:
I'm trying to get some 'release/version numbers' to sort properly.
mysql SELECT ReleaseID, Name FROM releases ORDER BY Name DESC;
+---+-+
| ReleaseID | Name|
+---+-+
|18 | Unspecified |
|20 | Next Patch |
|58 | LOCset |
|74 | Abashiri|
|54 | 4.6.0 (Folsom) |
-- 4.5.10 should be here
|99 | 4.5.9 |
|98 | 4.5.6 |
|93 | 4.5.5 (Purdy) |
|97 | 4.5.4 |
|96 | 4.5.3 |
|94 | 4.5.2 |
| 100 | 4.5.10 | -- should be ^ there
|91 | 4.5.1 Deferred |
|78 | 4.5.1 (Leavenworth) |
|95 | 4.2.7.4 |
|92 | 4.2.7.3 |
|90 | 4.2.7.2 |
|87 | 4.2.7.1 |
|88 | 4.2.7.0 |
I like this order, especially with the top four,
except for that 4.5.10 should be higher up,
just under 4.6.0, not under 4.5.2 as it is now.
So I tried the + 0 trick which makes things even worse
(notice the 4.2.6.1 and 4.2.6.0 -- yipes!):
mysql SELECT ReleaseID, Name FROM releases ORDER BY Name + 0 DESC;
+---+-+
| ReleaseID | Name|
+---+-+
(18,20,58,74) are moved
:(
|54 | 4.6.0 (Folsom) |
|78 | 4.5.1 (Leavenworth) |
| 100 | 4.5.10 |
|91 | 4.5.1 Deferred |
|93 | 4.5.5 (Purdy) |
|94 | 4.5.2 |
|96 | 4.5.3 |
|97 | 4.5.4 |
|98 | 4.5.6 |
|99 | 4.5.9 |
|82 | 4.2.6.1 |?
|76 | 4.2.2 |
|75 | 4.2.4 |
|72 | 4.2.1 |
|73 | 4.2.3 |
|67 | 4.2.6.0 |?
I'm pretty sure this is going to involve some sort of splitting
the version
from the release codeword via some string functions, and then
operating on
that part.
D.Vin
http://daevid.com
