Discontinued AUTO_INCREMENT problem....
Hi everybody ! A have a discontinued AUTO_INCREMENT sequence when i insert data in a table with a 100 (or more) items SELECT request. The problem (or situation) is reproductible, you can see an example below. Anybody could explain this to me ? Cheers XC My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 == Example = -- CREATE test table mysql create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.00 sec) -- INSERT DATA FROM ANOTHER TABLE mysql insert into test(name) select `name`from user limit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = OK mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 100 | +---+ 1 row in set (0.00 sec) --INSERT DATA WITH CHECKED SELECTREQUEST 2 = DATA INSERT OK mysql insert into test(name) select `name` from userlimit 100; Query OK, 100 rows affected (0.01 sec) Records: 100 Duplicates: 0 Warnings: 0 -- AUTO_INCREMENT ID CHECK = should be 100 + 100 = 200 -- = 27 IDs are unset, first ID of 2nd insert is 128 instead of 101 -- No field between 100 and 128 mysql select max(`id`) from test; +---+ | max(`id`) | +---+ | 227 | +---+ 1 row in set (0.00 sec) == End Example = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error in mysql replication with LOAD DATA INFILE
The application is designed to work such a way that it will process the csv files daily as part of the aggregate process to calculate some metrics. it runs fine on the master, when it come to slave through replicaiton it fails with the error. i even tried upgrading the slave to latest version mysql 5.1.53 after i see some post on the internet saying we have some issues in the older version , but it keeps giving the same error. thanks Anand On Mon, Dec 20, 2010 at 7:42 PM, who.cat win@gmail.com wrote: i wanna know you have done LOAD DATA INFILE in master ,why are you tring to do it in the slave ?The master didn't replication the data to the master ? All you best What we are struggling for ? The life or the life ? On Mon, Dec 20, 2010 at 3:32 PM, Anand Kumar sanan...@gmail.com wrote: On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand
[NEWS]
Hello! Our team created new comprehensive solution for data analysts. Could you place basic information about this product on your page. Best Regards, Sergey Lazurenko dbForge Data Studio for SQL Server 1.00.odt Description: application/vnd.oasis.opendocument.text -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [NEWS]
SPAM .. Am 20.12.2010 um 17:58 schrieb Sergey Lazurenko serg...@devart.com: Hello! Our team created new comprehensive solution for data analysts. Could you place basic information about this product on your page. Best Regards, Sergey Lazurenko dbForge Data Studio for SQL Server 1.00.odt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=schackenb...@termindoc.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: odd problem with select as statement
I can't tell you 'why' it is occurring when the field name begins with 4E5, but you can solve your problem by enclosing all your field names in backticks ( ` ). IE: SELECT field AS `4E5664736F400E8B482EA7AA67853D13` On Mon, Dec 20, 2010 at 11:43 AM, Ramsey, Robert L robert-ram...@uiowa.eduwrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: odd problem with select as statement
Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.eduwrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
Problem with WHERE .. IN
I have table post (id INT and parent VARCHAR) +--+-+ | id | parent | +--+-+ |1 | 0 | |2 | 0 | |3 | 1 | |4 | 0 | |5 | 1 | |6 | 0 | |7 | 1,5 | |8 | 1,5 | |9 | 1,5 | | 10 | 5,7,11 | | 11 | 1,5,7,10| | 12 | 1,5,7,10,11 | +--+-+ SELECT id FROM post WHERE where id IN (SELECT parent FROM post WHERE id = 10); +--+ | id | +--+ |5 | +--+ whereas the results I want is +--+ | id | +--+ |5 | |7 | | 11 | +--+ Please tell me, where is wrong Thanks Regards -- Muhammad Subair +62 8176583311
Re: odd problem with select as statement
i.e. just try this: mysql select 4E5664736F400E8B482EA7AA67853D13; ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing -Hank On Mon, Dec 20, 2010 at 12:50 PM, Hank hes...@gmail.com wrote: Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.edu wrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
Re: Problem with WHERE .. IN
The sub-select only returns a single row, so IN(...) is only looking at a single value in the list .. it doesn't expand to into IN (5,7,11). On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair msub...@gmail.com wrote: I have table post (id INT and parent VARCHAR) +--+-+ | id | parent | +--+-+ |1 | 0 | |2 | 0 | |3 | 1 | |4 | 0 | |5 | 1 | |6 | 0 | |7 | 1,5 | |8 | 1,5 | |9 | 1,5 | | 10 | 5,7,11 | | 11 | 1,5,7,10| | 12 | 1,5,7,10,11 | +--+-+ SELECT id FROM post WHERE where id IN (SELECT parent FROM post WHERE id = 10); +--+ | id | +--+ |5 | +--+ whereas the results I want is +--+ | id | +--+ |5 | |7 | | 11 | +--+ Please tell me, where is wrong Thanks Regards -- Muhammad Subair +62 8176583311 -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Autostart not working for me in 5.5.8 version
Hi Noel, all! Noel Butler wrote: On Thu, 2010-12-16 at 10:08 -0300, Alejandro Bednarik wrote: You are right. In previous version init script have a default value, now is empty. mysql 5.1..53 - basedir=/usr/local/mysql mysql-5.5.8 - basedir=. /me ponders at who the IDIOT is that decided that cmake is the way of mysql now. I mean WTF... It was the ones who realized that two different build mechanisms (autotools for Unix, cmake for Windows) will always cause divergence, when one is maintained with a change and the other isn't. Being database people, we know the importance of consistency ;) [[...]] /me sticks with 5.1 Do as you like, but realize that 5.1 won't see major development in the future and will be put on extended support only in due time. So if anybody encounters differences between 5.1 and 5.5 in such settings which aren't documented (= by intention, or unavoidable), please file bugs to get them fixed. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problem with WHERE .. IN
In the last episode (Dec 20), Johnny Withers said: On Mon, Dec 20, 2010 at 11:52 AM, muhammad subair msub...@gmail.com wrote: I have table post (id INT and parent VARCHAR) +--+-+ | id | parent | +--+-+ |1 | 0 | |2 | 0 | |3 | 1 | |4 | 0 | |5 | 1 | |6 | 0 | |7 | 1,5 | |8 | 1,5 | |9 | 1,5 | | 10 | 5,7,11 | | 11 | 1,5,7,10| | 12 | 1,5,7,10,11 | +--+-+ SELECT id FROM post WHERE where id IN (SELECT parent FROM post WHERE id = 10); +--+ | id | +--+ |5 | +--+ whereas the results I want is +--+ | id | +--+ |5 | |7 | | 11 | +--+ Please tell me, where is wrong The sub-select only returns a single row, so IN(...) is only looking at a single value in the list .. it doesn't expand to into IN (5,7,11). You might need to use the FIND_IN_SET function: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set so something like this should work (although the 2nd query won't be able to use any indexes): SELECT parent FROM post WHERE id = 10 into @parent; SELECT id from post where FIND_IN_SET(id, @parent) 0; If you normalize your table so that you have one row per relation: +--+-+ | id | parent | +--+-+ | 10 | 5 | | 10 | 7 | | 10 | 11 | +--+-+ , then your original query would work the way you expected. -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: odd problem with select as statement
Yes! The illegal double error only happens if you do the select like you did. The only error I was getting was the generic there's an error in your sql. Thank you! Bob From: Hank [mailto:hes...@gmail.com] Sent: Monday, December 20, 2010 11:52 AM To: Ramsey, Robert L Cc: mysql@lists.mysql.com Subject: Re: odd problem with select as statement i.e. just try this: mysql select 4E5664736F400E8B482EA7AA67853D13; ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing -Hank On Mon, Dec 20, 2010 at 12:50 PM, Hank hes...@gmail.commailto:hes...@gmail.com wrote: Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.edumailto:robert-ram...@uiowa.edu wrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
Trigger?
I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger?
The expression you supplied looks right enough.. how was it declared? as an on UPDATE/on INSERT trigger or just a single case? - michael dykman On Mon, Dec 20, 2010 at 5:21 PM, Jerry Schwartz je...@gii.co.jp wrote: I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Trigger?
Well, to produce this result, the first thing that we have to do is to *get rid of* the NOT NULL constraint of the column `foo`. After it, the 'null' can be sent within a INSERT statement, as below: mysql show create table testtrigger\G *** 1. row *** Table: testtrigger Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 1 row in set (0.05 sec) so, after to create table, we create the trigger: mysql create trigger trg_test - before insert on testtrigger - for each row - begin - if(NEW.foo IS NULL || NEW.foo = '') then - set NEW.foo = 'Ok'; - end if; - end; - // Query OK, 0 rows affected (0.04 sec) mysql insert into testtrigger set id =100, foo =null; Query OK, 1 row affected (0.03 sec) mysql select * from testtrigger; +-+--+ | id | foo | +-+--+ | 100 | Ok | +-+--+ 1 row in set (0.00 sec) The way that your table is now, with foo NOT NULL, you can't send foo =null with a query cause column don't accept null values. The column was defined as a not null. Look this: mysql alter table testtrigger modify foo char(10) not null; Query OK, 1 row affected (0.10 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql insert into testtrigger set id =100, foo =null; ERROR 1048 (23000): Column 'foo' cannot be null Did you get? Best regards. -- Wagner Bianchi 2010/12/20 Jerry Schwartz je...@gii.co.jp I've never used a trigger before, and I want to make one that sounds like it should be simple. Create Table: CREATE TABLE `testtrigger` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `foo` char(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 Here's what I want to do: if no value is supplied for `foo`, or if a NULL value is supplied for `foo`, I want to set it to a particular value. I tried things like this: SET NEW.foo = IFNULL(NEW.foo,'ok') But that didn't work. If you point me in the right direction, I'll be okay from there (I hope). Thanks. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wagnerbianch...@gmail.com
Re: Query Stored Index instead of Group By
Hi Johan, On Sun, Dec 19, 2010 at 7:11 PM, Johan De Meersman vegiv...@tuxera.bewrote: You can't query the index directly, but if you select only fields that are in the index, no table lookups will be performed - this is called a covering index. Great.. Thanks for the confirmation. Regards, Feris -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Autostart not working for me in 5.5.8 version
Hi Joerg, On Mon, 2010-12-20 at 18:59 +0100, Joerg Bruehe wrote: Hi Noel, all! Noel Butler wrote: On Thu, 2010-12-16 at 10:08 -0300, Alejandro Bednarik wrote: You are right. In previous version init script have a default value, now is empty. mysql 5.1..53 - basedir=/usr/local/mysql mysql-5.5.8 - basedir=. /me ponders at who the IDIOT is that decided that cmake is the way of mysql now. I mean WTF... It was the ones who realized that two different build mechanisms (autotools for Unix, cmake for Windows) will always cause divergence, when one is maintained with a change and the other isn't. Being database people, we know the importance of consistency ;) I can tell its done by windowsy people, it has pretty colours :) seriously though , it's almost as bad as trying to figure out what to give postifx hehe And in the real world, many sysadmins have to build the database servers for the database admins, I've made my opinion known about cmake so I'll leave my cursing at that. /me sticks with 5.1 Do as you like, but realize that 5.1 won't see major development in the future and will be put on extended support only in due time. For what we use it for, it's likely fine, (user auth/radius/web/mail etc etc etc) nothing complex. So if anybody encounters differences between 5.1 and 5.5 in such settings which aren't documented (= by intention, or unavoidable), please file bugs to get them fixed. Well, the documentation could be a little more in depth, if you remove method A, and if method B is completely compatible, then there must be detailed information, it is not very good for time management to spend hours looking over the website, yes forge.mysql.com has a handy reference, but the bit about replacing, for instance plugins=max, it is not clear what we need to include, we know what is default, and some examples of to add given the example given, but no reference to the max server, without time consuming research to see what plugins=max includes, compare, then find equivalent DINSTALL_blah=1's to add, I think it is a backwards step, and you're likely to see more cussing as more go to upgrade. Nice thing though, my original my.cnf didnt cause it to bail out upon restart, however I only installed it on one very light use server. want to lay with it a bit before it goes on anything too serious. Cheers attachment: face-smile.png signature.asc Description: This is a digitally signed message part
