Re: SP not seeing INSERTs within WHILE loop
Hi, Your procedure returned correct numbers. Does misrepresentation happens for all the runs or adhoc. Thanks ViSolve DB Team. - Original Message - From: brian stone [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 03, 2007 8:55 AM Subject: SP not seeing INSERTs within WHILE loop I have an issue where inserts are not being seen properly while in a SP. The goal is to track payments made by customers, payment distribution. I narrowed down the issue to the below, so I was able to exclude many columns and other tables in hopes it is simple enough to get some help. I have a payment table and a payment distribution table. By joining the two, I can determine how much money is left for a payment. I can then apply those monies to a customer charge. I created a simple procedure that loops and distributes $1 5 times. Problem: the SELECT returns $10 twice in a row. Unless I am missing something, it should be returning $9 on the second SELECT. After the second select, it returns 8, then 7, etc... For some reason, that second select is wrong? CREATE TABLE payment ( payment_id INT PRIMARY KEY AUTO_INCREMENT, amount DECIMAL(15,5), date_dist DATETIME NULL DEFAULT NULL -- date fully distributed ); CREATE TABLE payment_dist ( payment_id INT NOT NULL DEFAULT 0, amount DECIMAL(15,5) ); -- make a $10 payment INSERT INTO PAYMENT VALUES (DEFAULT, 10.00, DEFAULT); DROP PROCEDURE p; DROP FUNCTION safe_decimal; delimiter // CREATE FUNCTION safe_decimal(d DECIMAL(15,5)) RETURNS DECIMAL(15,5) BEGIN IF d IS NULL THEN RETURN 0; END IF; RETURN d; END; // delimiter ; delimiter // CREATE PROCEDURE p () BEGIN DECLARE count INT DEFAULT 0; WHILE count 5 DO SELECT payment.payment_id AS payment_id, (payment.amount - SUM(safe_decimal(d.amount))) AS amount FROM payment LEFT JOIN payment_dist d ON payment.payment_id = d.payment_id WHERE payment.date_dist IS NULL GROUP BY 1 ORDER BY 1; SET count = count + 1; INSERT INTO payment_dist (payment_id, amount) VALUES (1, 1.00); END WHILE; END; // delimiter ; CALL p(); mysql CALL p(); ++--+ | payment_id | amount | ++--+ | 1 | 10.0 | ++--+ 1 row in set (0.00 sec) ++--+ | payment_id | amount | ++--+ | 1 | 10.0 | ++--+ 1 row in set (0.00 sec) ++-+ | payment_id | amount | ++-+ | 1 | 8.0 | ++-+ 1 row in set (0.00 sec) ++-+ | payment_id | amount | ++-+ | 1 | 7.0 | ++-+ 1 row in set (0.00 sec) ++-+ | payment_id | amount | ++-+ | 1 | 6.0 | ++-+ 1 row in set (0.00 sec) Query OK, 1 row affected (0.00 sec) mysql SELECT payment.payment_id AS payment_id, - (payment.amount - SUM(safe_decimal(d.amount))) AS amount - FROM payment LEFT JOIN payment_dist d - ON payment.payment_id = d.payment_id - WHERE payment.date_dist IS NULL GROUP BY 1 ORDER BY 1; ++-+ | payment_id | amount | ++-+ | 1 | 5.0 | ++-+ 1 row in set (0.00 sec) I end up with the correct number but am getting the wrong result after the first insert. any ideas what is happening here? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to get (lapse) time in microseconds ?
Hi List, I need the lapse time in microseconds. I have tried various things, like: SELECT TIME_FORMAT(CURTIME(), '%f'); SELECT TIME_FORMAT(NOW(), '%f'); SELECT MICROSECOND(CURTIME()); but all I get is 0. What am I doing wrong ? TIA, Cor
Data types and space needs
Hi All, I have somewhat of a silly question. If I define a column as int it needs 4 bytes. Do I gain anything space wise if I restrict the length to e.g. 10, int(10), or is this only a logical restriction? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data types and space needs
Olaf, not a silly question at all. You can indeed save space by using different forms of integer. See http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html and http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html From that second page: Storage Requirements for Numeric Types Data Type Storage Required TINYINT 1 byte SMALLINT2 bytes MEDIUMINT 3 bytes INT, INTEGER4 bytes BIGINT 8 bytes You may also be able to use UNSIGNED to extend the range of a column, if you don't need to store negative values. HTH, Dan On 1/5/07, Olaf Stein [EMAIL PROTECTED] wrote: Hi All, I have somewhat of a silly question. If I define a column as int it needs 4 bytes. Do I gain anything space wise if I restrict the length to e.g. 10, int(10), or is this only a logical restriction? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select statement question
You want to use a LEFT JOIN, which will select all the records from A and link them with records in B if there are any. If there are no matches in B, the the fields from B will be NULL. You then just check for the NULL value (no match) in a B field and use that as your filter. SELECT A.*,B.D FROM A LEFT JOIN B ON A.C=B.D WHERE B.D IS NULL - Original Message - From: Aaron Cannon [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, January 03, 2007 3:47 PM Subject: select statement question -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all. I hope this is the right place to ask this. I have two tables, A and B. Each of these tables has a column with integers. The column in table A is C and the one in B is D. I need a select statement that will return all records whose C value is not in any row in D. for example: C = 1 2 3 4 5 6 7 8 11 D = 2 4 6 8 10 and the statement would return: 1 3 5 7 11 Probably an easy question for those of you more experienced but I have no clew. Thanks in advance. Sincerely Aaron Cannon - -- Skype: cannona MSN/Windows Messenger: [EMAIL PROTECTED] (don't send email to the hotmail address.) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) - GPGrelay v0.959 Comment: Key available from all major key servers. iD8DBQFFnBbLI7J99hVZuJcRAiF3AJ4mR4UjLa0sG+hIDbErj7LvuzfU4wCggEDh DtnfmVsHL84me4qVw/mA4s8= =l2gE -END PGP SIGNATURE- -- 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]
mysqld_safe failing
Hi all When i try to run mysqld_safe cmd, it fails saying : Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 070105 20:23:40 mysqld ended Also mysqld.pid does not exist at the given path! Any Idea what to do??? mysql_install_db is running fine as well. Contents of mysqld.log are : 070105 20:23:40 mysqld started InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 070105 20:23:40 [ERROR] Can't init databases 070105 20:23:40 [ERROR] Aborting 070105 20:23:40 [Note] /usr/libexec/mysqld: Shutdown complete 070105 20:23:40 mysqld ended When i tried to see the shell script running, i get the following msg: sh -x bin/mysqld_safe --user=root + KILL_MYSQLD=1 + trap '' 1 2 3 15 + umask 007 + defaults= + case $1 in ++ pwd + MY_PWD=/usr + test -d /usr/data/mysql -a -f ./share/mysql/english/errmsg.sys -a -x ./bin/mysqld + test -f ./var/mysql/db.frm -a -f ./share/mysql/english/errmsg.sys -a -x ./libexec/mysqld + MY_BASEDIR_VERSION=/usr + DATADIR=/var/lib/mysql + ledir=/usr/libexec + user=mysql + niceness=0 + test -x /usr/libexec/mysqld-max + MYSQLD=mysqld + pid_file= + err_log= + test -x ./bin/my_print_defaults + print_defaults=./bin/my_print_defaults + args= + SET_USER=2 ++ ./bin/my_print_defaults --loose-verbose mysqld server + parse_arguments --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --old_passwords=1 + pick_args= + test --datadir=/var/lib/mysql = PICK-ARGS-FROM-ARGV + for arg in '$@' + case $arg in ++ echo --datadir=/var/lib/mysql ++ sed -e 's;--datadir=;;' + DATADIR=/var/lib/mysql + for arg in '$@' + case $arg in ++ echo --socket=/var/lib/mysql/mysql.sock ++ sed -e 's;--socket=;;' + mysql_unix_port=/var/lib/mysql/mysql.sock + for arg in '$@' + case $arg in + test -n '' + test 2 -eq 2 + SET_USER=0 ++ ./bin/my_print_defaults --loose-verbose mysqld_safe safe_mysqld + parse_arguments --err-log=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid + pick_args= + test --err-log=/var/log/mysqld.log = PICK-ARGS-FROM-ARGV + for arg in '$@' + case $arg in ++ echo --err-log=/var/log/mysqld.log ++ sed -e 's;--err-log=;;' + err_log=/var/log/mysqld.log + for arg in '$@' + case $arg in ++ echo --pid-file=/var/run/mysqld/mysqld.pid ++ sed -e 's;--pid-file=;;' + pid_file=/var/run/mysqld/mysqld.pid + parse_arguments PICK-ARGS-FROM-ARGV --user=root + pick_args= + test PICK-ARGS-FROM-ARGV = PICK-ARGS-FROM-ARGV + pick_args=1 + shift + for arg in '$@' + case $arg in ++ echo --user=root ++ sed -e 's;--[^=]*=;;' + user=root + SET_USER=1 + safe_mysql_unix_port=/var/lib/mysql/mysql.sock + test '!' -x /usr/libexec/mysqld + test -z /var/run/mysqld/mysqld.pid + case $pid_file in + test -z /var/log/mysqld.log + test -n /var/lib/mysql/mysql.sock + args='--socket=/var/lib/mysql/mysql.sock ' + test -n '' + test 0 -eq 0 + NOHUP_NICENESS=nohup + nohup nice ++ nice + normal_niceness=0 ++ nohup nice + nohup_niceness=0 + numeric_nice_values=1 + for val in '$normal_niceness' '$nohup_niceness' + case $val in + for val in '$normal_niceness' '$nohup_niceness' + case $val in + test 1 -eq 1 + nice_value_diff=0 + test 1 -eq 0 + USER_OPTION= + test -w / -o root = root + test root '!=' root -o 1 = 1 + USER_OPTION=--user=root + touch /var/log/mysqld.log + chown root /var/log/mysqld.log + test -n '' + test -n '' + test -f /var/run/mysqld/mysqld.pid + echo 'Starting mysqld daemon with databases from /var/lib/mysql' Starting mysqld daemon with databases from /var/lib/mysql ++ date '+%y%m%d %H:%M:%S mysqld started' + echo '070105 20:22:45 mysqld started' + true + rm -f /var/lib/mysql/mysql.sock /var/run/mysqld/mysqld.pid + test -z '--socket=/var/lib/mysql/mysql.sock ' + eval 'nohup /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=root --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock /var/log/mysqld.log 21' ++ nohup /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=root --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock + test '!' -f /var/run/mysqld/mysqld.pid + echo 'STOPPING server from pid file /var/run/mysqld/mysqld.pid' STOPPING server from pid file /var/run/mysqld/mysqld.pid + break ++ date '+%y%m%d %H:%M:%S' + echo '070105 20:22:45 mysqld ended' + tee -a /var/log/mysqld.log 070105 20:22:45 mysqld ended + echo '' + tee -a /var/log/mysqld.log --
Coping table
Hello, i have this problem: I have two databases in diferent servers. I need to copy the contents from some tables in Server1 to Server2, so i tried using the select into outfile and then load data , but the problem is that this commands creates the files on the server, and i want/need to do that on a client machine...¿what should i do? Guillermo
Re: 5.1.14-beta with ssl build failure
Chris White wrote: Duncan Hutty wrote: I attempted to build 5.1.14-beta with ssl support and it failed (output below). It builds quite happily on this system without the ssl support parameter to configure. Since it appears to fail in an area (timezone system) that to me seems rather unrelated, I wondered if something odd was afoot. x86-suse9.3, openssl-0.9.8d, gcc-3.4.6 (or gcc-4.1.1) using: CFLAGS=-O3 CXX=gcc \ CXXFLAGS=-O3 -felide-constructors \ -fno-exceptions -fno-rtti \ Kill all this and try changing -O3 to -O2 as -O3 optimizations can get dicey sometimes, especially when it comes into C++ oddities. ./configure --with-ndbcluster \ --with-ssl=/usr/local/openssl \ --enable-assembler make Also, it seems to be failing during the linking stage, so take a look at throwing out --enable-assembler and see what happens. Thanks for the suggestions, but they didn't change the result. -- Duncan Hutty System Administrator, ECE Carnegie Mellon University Please use informative subject lines -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Coping table
Guillermo - You likely want mysqldump - http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Dan On 1/5/07, Guillermo [EMAIL PROTECTED] wrote: Hello, i have this problem: I have two databases in diferent servers. I need to copy the contents from some tables in Server1 to Server2, so i tried using the select into outfile and then load data , but the problem is that this commands creates the files on the server, and i want/need to do that on a client machine...¿what should i do? Guillermo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to get (lapse) time in microseconds ?
C.R.Vegelin wrote:
Hi List,
I need the lapse time in microseconds.
I have tried various things, like:
SELECT TIME_FORMAT(CURTIME(), '%f');
SELECT TIME_FORMAT(NOW(), '%f');
Your arguments are reversed. It's:
SELECT TIME_FORMAT('%f',CURTIME());
SELECT TIME_FORMAT('%f',NOW());
SELECT MICROSECOND(CURTIME());
Don't know about this one though.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1.14-beta with ssl build failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It is unfortunate that mysql does not offer a binary version with ssl support for Linux. Does anyone know why? It can't be for export reasons, as they do offer a windows version with SSL support. Aaron Cannon - -- Skype: cannona MSN/Windows Messenger: [EMAIL PROTECTED] (don't send email to the hotmail address.) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) - GPGrelay v0.959 Comment: Key available from all major key servers. iD8DBQFFnniYI7J99hVZuJcRApGtAKD73Z3nSn4viL5mulFj0ijNjZWaOgCgrN2w JN/foKnc4hmXwzoaiupjbr4= =n7j+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data types and space needs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Actually, I think he was asking if the sized used by the storage engine would change if you used for example int(2) as apposed to int(10). My guess is it would not, but that's just a guess. Aaron Cannon - -- Skype: cannona MSN/Windows Messenger: [EMAIL PROTECTED] (don't send email to the hotmail address.) - - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: Olaf Stein [EMAIL PROTECTED] Cc: MySql mysql@lists.mysql.com Sent: Friday, January 05, 2007 8:45 AM Subject: Re: Data types and space needs Olaf, not a silly question at all. You can indeed save space by using different forms of integer. See http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html and http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html From that second page: Storage Requirements for Numeric Types Data Type Storage Required TINYINT 1 byte SMALLINT 2 bytes MEDIUMINT 3 bytes INT, INTEGER 4 bytes BIGINT 8 bytes You may also be able to use UNSIGNED to extend the range of a column, if you don't need to store negative values. HTH, Dan On 1/5/07, Olaf Stein [EMAIL PROTECTED] wrote: Hi All, I have somewhat of a silly question. If I define a column as int it needs 4 bytes. Do I gain anything space wise if I restrict the length to e.g. 10, int(10), or is this only a logical restriction? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) - GPGrelay v0.959 Comment: Key available from all major key servers. iD8DBQFFnnnGI7J99hVZuJcRAkstAJsEw8S1ZxnEpL+oXvpDsTfKx3C34QCgpnNT hd379sQHorwV3eV9NcYeq0E= =WAXX -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to get (lapse) time in microseconds ?
Thanks Chris,
You're right, I reversed the arguments. However,
SELECT TIME_FORMAT('%f',CURTIME());
gives normal time format like 17:37:47
SELECT TIME_FORMAT('%f',NOW());
gives normal date/time format.
SELECT MICROSECOND(CURTIME());
gives 0.
I need microseconds to get the query runtime.
But a format like 0.05 sec is also right for me.
Maybe any other ideas ?
- Original Message -
From: Chris White [EMAIL PROTECTED]
To: C.R.Vegelin [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Friday, January 05, 2007 3:58 PM
Subject: Re: how to get (lapse) time in microseconds ?
C.R.Vegelin wrote:
Hi List,
I need the lapse time in microseconds.
I have tried various things, like:
SELECT TIME_FORMAT(CURTIME(), '%f');
SELECT TIME_FORMAT(NOW(), '%f');
Your arguments are reversed. It's:
SELECT TIME_FORMAT('%f',CURTIME());
SELECT TIME_FORMAT('%f',NOW());
SELECT MICROSECOND(CURTIME());
Don't know about this one though.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Varchar limit warning
Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Varchar limit warning
You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We set this for the server in the my.cnf file. Be careful, though, because there are some third-party GUI clients that don't read the my.cnf file and thus don't set the sql_mode to what you expect. In that case, set it yourself in the client: set SESSION sql_mode='STRICT_TRANS_TABLES'; select @@session.sql_mode; You can read about sql_mode here. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Donna Olaf Stein [EMAIL PROTECTED] 01/05/2007 12:37 PM To MySql mysql@lists.mysql.com cc Subject Varchar limit warning Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
Re: Varchar limit warning - similar question
Thanks That works nicely. Why does that not have any effect with numeric values. E.g. I can insert the same max numbers in a tinyint column then in a tinyint(1) column, For example, how do I create a column that only allows 1 numeric value from 0 to 5? Thanks Olaf On 1/5/07 12:52 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We set this for the server in the my.cnf file. Be careful, though, because there are some third-party GUI clients that don't read the my.cnf file and thus don't set the sql_mode to what you expect. In that case, set it yourself in the client: set SESSION sql_mode='STRICT_TRANS_TABLES'; select @@session.sql_mode; You can read about sql_mode here. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Donna Olaf Stein [EMAIL PROTECTED] 01/05/2007 12:37 PM To MySql mysql@lists.mysql.com cc Subject Varchar limit warning Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED]
Re: Varchar limit warning - similar question
Sorry, wrong example For instance, it makes no difference regarding the values I can enter if I declare a field as int or int(3) On 1/5/07 1:52 PM, Olaf Stein [EMAIL PROTECTED] wrote: Thanks That works nicely. Why does that not have any effect with numeric values. E.g. I can insert the same max numbers in a tinyint column then in a tinyint(1) column, For example, how do I create a column that only allows 1 numeric value from 0 to 5? Thanks Olaf On 1/5/07 12:52 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We set this for the server in the my.cnf file. Be careful, though, because there are some third-party GUI clients that don't read the my.cnf file and thus don't set the sql_mode to what you expect. In that case, set it yourself in the client: set SESSION sql_mode='STRICT_TRANS_TABLES'; select @@session.sql_mode; You can read about sql_mode here. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Donna Olaf Stein [EMAIL PROTECTED] 01/05/2007 12:37 PM To MySql mysql@lists.mysql.com cc Subject Varchar limit warning Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Varchar limit warning - similar question
For limiting possible values to 0, 1, 2, 3, 4, 5 you could use an ENUM, although that is not advised because you're using numeric indices to refer to numeric values and that can be confusing. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Olaf Stein [mailto:[EMAIL PROTECTED] Sent: Friday, January 05, 2007 1:52 PM To: [EMAIL PROTECTED] Cc: MySql Subject: Re: Varchar limit warning - similar question Thanks That works nicely. Why does that not have any effect with numeric values. E.g. I can insert the same max numbers in a tinyint column then in a tinyint(1) column, For example, how do I create a column that only allows 1 numeric value from 0 to 5? Thanks Olaf On 1/5/07 12:52 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We set this for the server in the my.cnf file. Be careful, though, because there are some third-party GUI clients that don't read the my.cnf file and thus don't set the sql_mode to what you expect. In that case, set it yourself in the client: set SESSION sql_mode='STRICT_TRANS_TABLES'; select @@session.sql_mode; You can read about sql_mode here. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Donna Olaf Stein [EMAIL PROTECTED] 01/05/2007 12:37 PM To MySql mysql@lists.mysql.com cc Subject Varchar limit warning Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Varchar limit warning - similar question
The length of the numeric type defines the way it will be displayed. The storage requirement is defined by the numeric data type (tinyint, etc.). I don't think there's any way to have MySQL limit input data to the length you use. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Olaf Stein [mailto:[EMAIL PROTECTED] Sent: Friday, January 05, 2007 2:26 PM To: Jerry Schwartz Subject: Re: Varchar limit warning - similar question So what effect does e.g. int(3) have compared with just int Thanks Olaf On 1/5/07 2:24 PM, Jerry Schwartz [EMAIL PROTECTED] wrote: For limiting possible values to 0, 1, 2, 3, 4, 5 you could use an ENUM, although that is not advised because you're using numeric indices to refer to numeric values and that can be confusing. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Olaf Stein [mailto:[EMAIL PROTECTED] Sent: Friday, January 05, 2007 1:52 PM To: [EMAIL PROTECTED] Cc: MySql Subject: Re: Varchar limit warning - similar question Thanks That works nicely. Why does that not have any effect with numeric values. E.g. I can insert the same max numbers in a tinyint column then in a tinyint(1) column, For example, how do I create a column that only allows 1 numeric value from 0 to 5? Thanks Olaf On 1/5/07 12:52 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: You need to set the sql_mode to STRICT_TRANS_TABLES or STRICT_ALL_TABLES. We set this for the server in the my.cnf file. Be careful, though, because there are some third-party GUI clients that don't read the my.cnf file and thus don't set the sql_mode to what you expect. In that case, set it yourself in the client: set SESSION sql_mode='STRICT_TRANS_TABLES'; select @@session.sql_mode; You can read about sql_mode here. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Donna Olaf Stein [EMAIL PROTECTED] 01/05/2007 12:37 PM To MySql mysql@lists.mysql.com cc Subject Varchar limit warning Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] - Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Varchar limit warning
Complementary to what Donna said, You can issue a SHOW WARNINGS sql after some of these queries... the cutt-off will be listed there ! You will get a mysql_result in the form: Level - Code - Message Warning - 1265 - Data truncated for column 'column_name' at row X. Gabriel PREDA On 1/5/07, Olaf Stein [EMAIL PROTECTED] wrote: Hi all If I insert a value to great for a field (e.g. '123456' into a varchar(5) field), mysql runs the insert without warning or error and cuts of what doesn't fit. How can I tell it to launch an error and abort the insert? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data types and space needs
Hi Olaf, For soon to be a decade I'm fighting with this question... !!! It's not a silly one... it's quite a tricky one... As many have noticed all INTEGER types (SMALLINT, MEDIUMINT, INT, BIGINT) have a LENGTH... by all means I can assure you that it has nothing to do with the maximum number you can store in a column or the size on the disk ! The only situation, known to me, when the length has a value is with the ZEROFILL atribute. Let's say you have 2 numbers to be stored: 4567 456789 If you insert then in a table in a column with the definition: INT(6) NOT NULL DEFAULT 0 You will get the same values and any value will take 4 bytes on the disk. If you insert then in a table in a column with the definition: INT(6) ZEROFILL NOT NULL DEFAULT 0 You will get : 004567 456789 as values and any value will also take 4 bytes on the disk. The only notable difference... as you can see... is in prepending 0 to the number to reach the desired length... if the number is greater than or equal to the declared length it has no effect. So AFAIK this is the purpose of the length...in INTEGER columns. -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
On Duplicate Key Update question
I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: On Duplicate Key Update question
Sorry for the premature question. I think I figured it out. On Duplicate Key Update Qty=Qty+Values(Qty); I haven't tested it yet but it makes sense that it'll work. Ed Reed [EMAIL PROTECTED] 1/5/07 2:40 PM I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks
Re: On Duplicate Key Update question
Ed Reed wrote: I use On Duplicate Key Update a lot and I usually use it like this, Insert Into tablename (myID, Qty) Values (1034,15), (6,13), (5001,1), (145,20) On Duplicate Key Update Qty=Values(Qty); This works very well but now I'd like to do something a little different. I'd like to have a query like this but instead of replacing the value of the previous Qty I'd like it to take the old Qty and the new Qty and store the sum of the two values in the Qty field. Is this possible and can anyone tell me how? Thanks doing it one record at a time I would do something like this... Insert Into tablename (myID, Qty) Values ($myID,$Qyt) On Duplicate Key Update Qty = Qty + $Qty you may also be able to use... On Duplicate Key Update Qty = Qty + Values(Qty) But I have never used that before so I'm not sure -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data types and space needs
Gabriel PREDA wrote: snip So AFAIK this is the purpose of the length...in INTEGER columns. -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer Olaf, As Gabriel so eloquently demonstrated, the LENGTH parameter only affects how the numbers are displayed, not how they are stored. Even if you declare a field to be INT(1) then whatever value you put into it will take up just as much room on disk and in memory as any other INT. The only difference is how the number is rendered as part of a result. -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: db/query question...
Hi Bruce, bruce wrote: hi... a further test... the following test tbl/information: dog name char statusint _date timestamp idint snip here's the tricky part. if i want to get the row with the status=3, but only if there's not a status=4 that has a later date, how do i accomplish this...?? so, for tom, i would return 'null', and for sue, i'd return '3' for the '01/24/07' the date for the last '3' is later than the date for the last '4'... i imagine that there's a way to accomplish this using subselects. any thoughts/comments.. There are three ways to solve this. One involving a subquery, one involving a self-join, and another involving a temporary table. I will demonstrate all three. temp table solution (attempt to match the condition you are testing against and only keep those rows that do not match): CREATE TEMPORARY TABLE tmpStatus3 SELECT id, name, status, _date FROM dog WHERE status=3; SELECT s3.id, s3.name, s3.status, s3._date FROM tmpStatus3 s3 LEFT JOIN dog ON dog.name = s3.name AND dog.status = 4 AND dog._date s3.date WHERE dog.id is null; self-join solution: SELECT d1.id, d1.name, d1.status, d1._date FROM dog d1 LEFT JOIN dog d2 ON d1.name = d2.name AND d2.status = 4 AND d2._date d1._date WHERE d1.status = 3 AND d2.id is null; subquery solution (1): SELECT d1.id, d1.name, d1.status, d1._date FROM dog d1 WHERE d1.status = 3 AND NOT EXISTS (SELECT id FROM dog d2 WHERE d1.name = d2.name AND d2.status = 4 AND d2._date d1._date); subquery solution (2): SELECT d1.id, d1.name, d1.status, d1._date FROM dog d1 LEFT JOIN (SELECT name, _date FROM dog WHERE status = 4) d2 ON d2.name = d1.name AND d2._date d1._date WHERE d1.status = 3 AND d2.name is NULL ; Typically, the techniques used in temporary table or subquery solution (2) will provide the best results as you only need compute the comparison set of data once. The self-join technique creates a Cartesian product which degrades in performance with the square of the number of rows being compared and will be slower than the dependent subquery evaluation of the EXISTS() clause used in subquery solution (1) . Does this demonstration help you see some additional patterns you can use to solve this and perhaps a few other similar query problems? -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
