ERROR in syntax...
Can anyone tell me why this query is generating an ERROR: DROP TRIGGER rtsadven_development.content_assets_after_insert_subtypes; CREATE TRIGGER `rtsadven_development`.`content_assets_after_insert_subtypes` AFTER INSERT ON `rtsadven_development`.`content_assets` FOR EACH ROW BEGIN IF NEW.content_asset_type_code = 'CURRI' THEN INSERT INTO curriculums (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'COURS' THEN INSERT INTO courses (content_asset_id) VALUES (NEW.id); IF NEW.content_asset_type_code = 'DOC' THEN INSERT INTO documents (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'MODUL' THEN INSERT INTO modules (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'TOPIC' THEN INSERT INTO topics (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'WEBPG' THEN INSERT INTO web_pages (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'EMAIL' THEN INSERT INTO emails (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'FBPOST' THEN INSERT INTO facebook_posts (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'TWEET' THEN INSERT INTO tweets (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'CHALL' THEN INSERT INTO challenges (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'IMAGE' THEN INSERT INTO images (content_asset_id) VALUES (NEW.id); ELSEIF NEW.content_asset_type_code = 'VIDEO' THEN INSERT INTO videos (content_asset_id) VALUES (NEW.id); END IF; INSERT INTO content_asset_statistics (content_asset_id, statistic_type_code, seq) SELECT NEW.id, statistic_type_code, seq from content_asset_statistic_types where content_asset_type_code = NEW.content_asset_type_code; END; Appreciate it. Don Wieland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: ERROR in syntax...
2014/09/06 09:06 -0700, Don Wieland Can anyone tell me why this query is generating an ERROR: Which error? The first IF statement is not properly ended? it isn't. (A series of equality tests against the same variable is done more conveniently with CASE ... END CASE.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
sql syntax error
hello, i am working on my personal website wih php 5.4.16 / mysql 5.6.12 (my
system : windows 7 / wampserver 2).
i have a bug when i am running my connection to database webpage.
My error message is the following :
Erreur SQL : 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 ''membres2' WHERE
'pseudo_membre' = '\'Flarose59\'' GROUP BY
'id_membre'' at line 1
Ligne : 29.
this is my php code :
$result = sqlquery(SELECT COUNT('id_membre') AS nbr, 'id_membre',
'pseudo_membre', 'mdp_membre' FROM 'espace_membre2'.'membres2' WHERE
'pseudo_membre' = '\'.mysql_real_escape_string($_POST['pseudo']).\'' GROUP BY
'id_membre', 1);
I tried several delimitator for the query function (like ' \* ').
Re: sql syntax error
- Original Message - From: florent larose florent.lar...@hotmail.com Subject: sql syntax error near ''membres2' WHERE [...] FROM 'espace_membre2'.'membres2' WHERE You were on the right path - mysql is wibbly about quotes. Either remove the quotes entirely ( espace_membre2.membres2 ) or try backticks ( ` ). They're MySQL's favourite quote, presumably because they were convenient to type on whatever abomination Monty used to type code :-) As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr plus the rightmost key (right next to return) on the middle row. Enjoy spraining your fingers :-p /johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: sql syntax error
Hi, Le 08/08/2014 17:48, Johan De Meersman a écrit : As your code is french, I'll assume you're on Azerty; the backtick is Alt-Gr plus the rightmost key (right next to return) on the middle row. Enjoy spraining your fingers :-p /johan Alt-GR plus '7' for French keyboard layout ;) Christophe. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
alter table modify syntax error
Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): 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 'after model' at line 1 I'm just wondering what I'm doing wrong here, because the syntax looks correct to me! Thanks -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): 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 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars...@bitbybit.dk wrote: On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): 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 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: alter table modify syntax error
Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): 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 'column color varchar(10) sixth' at line 1 Here's my table one more time for reference: mysql describe car_table; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | car_id | int(11) | NO | PRI | NULL| auto_increment | | vin| varchar(17) | YES | | NULL|| | year | decimal(4,0) | YES | | NULL|| | make | varchar(10) | YES | | NULL|| | model | varchar(20) | YES | | NULL|| | color | varchar(10) | YES | | NULL|| | price | decimal(7,2) | YES | | NULL|| ++--+--+-+-++ 7 rows in set (0.01 sec) I appreciate your suggestions so far and it would be great if I could get some help with this one too. Thanks Tim On Sat, Jun 28, 2014 at 1:34 PM, Tim Dunphy bluethu...@gmail.com wrote: Cool guys, that did it.. ALTER TABLE car_table MODIFY COLUMN color VARCHAR(10) AFTER model; For some reason the book I'm following doesn't specify that you have to note the data type in moves! This helped. and thanks again. Tim On Sat, Jun 28, 2014 at 1:24 PM, Carsten Pedersen cars...@bitbybit.dk wrote: On 28-06-2014 19:11, Tim Dunphy wrote: Hello, I'm trying to use a very basic alter table command to position a column after another column. This is the table as it exists now: mysql describe car_table; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | car_id | int(11) | NO | PRI | NULL | auto_increment | | vin | varchar(17) | YES | | NULL | | | color | varchar(10) | YES | | NULL | | | year | decimal(4,0) | YES | | NULL | | | make | varchar(10) | YES | | NULL | | | model | varchar(20) | YES | | NULL | | | howmuch | decimal(5,2) | YES | | NULL | | +-+--+--+-+-++ 7 rows in set (0.03 sec) I am trying to position the 'color' column after the 'model' column with the following command: mysql alter table car_table modify column color after model; And I'm getting the following error: ERROR 1064 (42000): 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 'after model' at line 1 Try: alter table car_table modify column color varchar(10) after model; / Carsten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
RE: alter table modify syntax error
Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 03:45 Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): 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 'column color varchar(10) sixth' at line 1 The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in-mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Thanks Tim On Sat, Jun 28, 2014 at 7:46 PM, Jesper Wisborg Krogh my...@wisborg.dk wrote: Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 03:45 Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error Hey guys, Sorry to hit you with one more. But I'm trying to use a positional statement in a column move based on what you all just taught me: mysql alter table modify column color varchar(10) sixth; But I am getting this error: ERROR 1064 (42000): 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 'column color varchar(10) sixth' at line 1 The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. See also: https://dev.mysql.com/doc/refman/5.5/en/alter-table.html Best regards, Jesper Krogh MySQL Support -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
RE: alter table modify syntax error
Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 10:09 To: Jesper Wisborg Krogh Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in- mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table modify syntax error
Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Hey, that's some good input. Thanks and makes total sense. I guess the reason I thought I could use that syntax is that the book uses MySQL for all it's examples and explains that it does so because MySQL is a free and open source version of SQL that's easy to install. But maybe you're right and they do depart into other syntaxes of SQL. I just don't know where they got that 'first, second, third, etc' version of the alter table syntax from. Definitely not sweatin' this detail tho, I am totally fine with what you showed me that works. Thanks again for your input! Tim On Sat, Jun 28, 2014 at 9:14 PM, Jesper Wisborg Krogh my...@wisborg.dk wrote: Hi Tim, -Original Message- From: Tim Dunphy [mailto:bluethu...@gmail.com] Sent: Sunday, 29 June 2014 10:09 To: Jesper Wisborg Krogh Cc: mysql@lists.mysql.com Subject: Re: alter table modify syntax error The syntax sixth is not a supported syntax. You should use the syntax AFTER column_name where you replace column_name with the column name you want to position the modified column after. Oh thanks. That's actually what I ended up doing after I got frustrated with that error. I was following the book 'Head First SQL' which was suggesting that you could do something like what this user was trying in this stack overflow thread: http://stackoverflow.com/questions/19175240/re-arranging-columns-in- mysql-using-position-keywords-such-as-first-second But the answer in that thread too suggests that this is wrong. So is the Head First SQL book just referring to an outdated syntax that doesn't work anymore? I can't imagine that it never worked if it's in that book. But hey ya never know! ;) Given the title of the book is Head First SQL and not Head First MySQL it probably isn't exclusively using syntax for MySQL. While SQL is a standard the various SQL databases are not completely identical with the syntax they support. This may be due to not completely conforming to the standard, using different versions of the SQL standard, or that there is not standard for that operation. Best regards, Jesper Krogh MySQL Support -- GPG me!! gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B
Re: syntax to create a user called starsky
On 29/02/12 12:38, Brown, Charles wrote: Hello, Can someone give me syntax to create a user called starsky and password hutch with the following objectives: - user the minimum to run back scripts - user has the minimum to lock tables - user has the minimum to do SELECT on tables Thx This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. You should read these pages: http://dev.mysql.com/doc/refman/5.1/en/create-user.html http://dev.mysql.com/doc/refman/5.1/en/grant.html CREATE USER 'starsky'@'domain/ipaddress' IDENTIFIED BY 'hutch'; GRANT SELECT,LOCK TABLES ON database.* TO 'starsky'@'domain/ipaddress'; FLUSH PRIVILEGES; When you say run back scripts, what does that mean? What sort of scripts? PS. IMO hutch is terrible choice of password for a user called starsky! -- *Chris Tate-Davies* *Software Development* Inflight Productions Ltd Telephone: 01295 269 680 15 Stukeley Street | London | WC2B 5LT *Email:*chris.tatedav...@inflightproductions.com mailto:chris.tatedav...@inflightproductions.com *Web:*www.inflightproductions.com http://www.inflightproductions.com/ - Registered Office: 15 Stukeley Street, London WC2B 5LT, England. Registered in England number 1421223 This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. Please note that the information provided in this e-mail is in any case not legally binding; all committing statements require legally binding signatures. http://www.inflightproductions.com
Re: delete syntax
2011/12/2 Reindl Harald h.rei...@thelounge.net well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database mysql and use flush privileges after changes The privileges should be maintained only using the designated commands. You cannot rely on the knowledge you have of the underlying implementation which can change anytime , while the privileges command are standard. *Cheers* Claudio Nanni DROP USER is the only SINGLE COMMAND as long as you do not use table/column-privileges there are exactly two relevant tables: user and db Am 02.12.2011 05:15, schrieb Stdranwl: DROP USER command is the only command to remove any user and its association from all other tables. On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald h.rei...@thelounge.net wrote: ALWAYS start with select * from mysql.user where user='mail_admin' and host like '\%'; and look what records are affected to make sure the were-statement works as expected and then use CURSOR UP and edit the last command to delete from not only doing this while unsure with escapes protects you against logical mistakes like forget a and column=1 and get 1000 rows affected with no way back -- Claudio
Re: delete syntax
Am 02.12.2011 21:59, schrieb Claudio Nanni: 2011/12/2 Reindl Harald h.rei...@thelounge.net well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database mysql and use flush privileges after changes The privileges should be maintained only using the designated commands. You cannot rely on the knowledge you have of the underlying implementation which can change anytime , while the privileges command are standard. do what you think is good for you if YOU can't be sure what you do there where i work i test updates and look at the user-tables and that is why i fixed problems where root did not have the right permissions after upgrade to 5.1 what was not corrected with mysql_upgrade an rolled out without any problems the same way i currently roll out fedora 15 on 24 production servers because i know what i do and have the infrastructure to prepare such major-upgrades to do them finally live after all tests are successfull and the local cach- and internal-repos are filled so please do not tell other peopole on what knowledge they can rely signature.asc Description: OpenPGP digital signature
Re: delete syntax
well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database mysql and use flush privileges after changes The privileges should be maintained only using the designated commands. You cannot rely on the knowledge you have of the underlying implementation which can change anytime , while the privileges command are standard. do what you think is good for you if YOU can't be sure what you do [snip] so please do not tell other peopole on what knowledge they can rely *all* the back and forth in these threads is good.. is susses out all the knowledge for everyone to see. Sincerely thanks to everyone who chimes in from all perspectives, -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
delete syntax
hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy bluethu...@jokefire.com wrote: hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
Hello Krishna, Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec) sorry for not including enough information last time. best tim - Original Message - From: Krishna Chandra Prajapati prajapat...@gmail.com To: Tim Dunphy bluethu...@jokefire.com Cc: mysql@lists.mysql.com Sent: Thursday, December 1, 2011 9:03:46 PM Subject: Re: delete syntax delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy bluethu...@jokefire.com wrote: hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
On 2011-12-02, Tim Dunphy bluethu...@jokefire.com wrote: Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec) This is just a regular table with a user and host column. If you wanted to delete the localhost row, you'd do delete from mysql.user where user='mail_admin' and host='localhost'; flush privileges; You'd need to flush privileges because you're munging the user table. But it's probably much better to use the DROP USER command. --keith -- kkeller-use...@wombat.san-francisco.ca.us (try just my userid to email me) AOLSFAQ=http://www.therockgarden.ca/aolsfaq.txt see X- headers for PGP signature information -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
You can try delete from mysql.user where user='mail_admin' and host like '\%' ; Note: I haven't tested it and since % is a wildcard you need to escape it. Best, Shiv On Thu, Dec 1, 2011 at 6:09 PM, Tim Dunphy bluethu...@jokefire.com wrote: Hello Krishna, Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec) sorry for not including enough information last time. best tim - Original Message - From: Krishna Chandra Prajapati prajapat...@gmail.com To: Tim Dunphy bluethu...@jokefire.com Cc: mysql@lists.mysql.com Sent: Thursday, December 1, 2011 9:03:46 PM Subject: Re: delete syntax delete from mysql.user where user='mail_admin'; Krishna On Fri, Dec 2, 2011 at 7:23 AM, Tim Dunphy bluethu...@jokefire.com wrote: hello list, I am attempting to delete a user from the mysql.user table without success. mysql delete from mysql.user where user='mail_admin@%'; Query OK, 0 rows affected (0.00 sec) mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | I would appreciate any advice you may have. Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete syntax
ALWAYS start with select * from mysql.user where user='mail_admin' and host like '\%'; and look what records are affected to make sure the were-statement works as expected and then use CURSOR UP and edit the last command to delete from not only doing this while unsure with escapes protects you against logical mistakes like forget a and column=1 and get 1000 rows affected with no way back Am 02.12.2011 03:43, schrieb Shiva: delete from mysql.user where user='mail_admin' and host like '\%' ; Note: I haven't tested it and since % is a wildcard you need to escape it. On Thu, Dec 1, 2011 at 6:09 PM, Tim Dunphy bluethu...@jokefire.com wrote: Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec) signature.asc Description: OpenPGP digital signature
Re: delete syntax
DROP USER command is the only command to remove any user and its association from all other tables. Cheers On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald h.rei...@thelounge.netwrote: ALWAYS start with select * from mysql.user where user='mail_admin' and host like '\%'; and look what records are affected to make sure the were-statement works as expected and then use CURSOR UP and edit the last command to delete from not only doing this while unsure with escapes protects you against logical mistakes like forget a and column=1 and get 1000 rows affected with no way back Am 02.12.2011 03:43, schrieb Shiva: delete from mysql.user where user='mail_admin' and host like '\%' ; Note: I haven't tested it and since % is a wildcard you need to escape it. On Thu, Dec 1, 2011 at 6:09 PM, Tim Dunphy bluethu...@jokefire.com wrote: Thanks but I probably should have noted that I only want to delete the wildcard user. There are other users I would prefer to not delete. mysql select user,host from mysql.user where user='mail_admin'; ++---+ | user | host | ++---+ | mail_admin | % | | mail_admin | 127.0.0.1 | | mail_admin | localhost | | mail_admin | localhost.localdomain | ++---+ 4 rows in set (0.00 sec)
Re: delete syntax
well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database mysql and use flush privileges after changes DROP USER is the only SINGLE COMMAND as long as you do not use table/column-privileges there are exactly two relevant tables: user and db Am 02.12.2011 05:15, schrieb Stdranwl: DROP USER command is the only command to remove any user and its association from all other tables. On Fri, Dec 2, 2011 at 8:22 AM, Reindl Harald h.rei...@thelounge.netwrote: ALWAYS start with select * from mysql.user where user='mail_admin' and host like '\%'; and look what records are affected to make sure the were-statement works as expected and then use CURSOR UP and edit the last command to delete from not only doing this while unsure with escapes protects you against logical mistakes like forget a and column=1 and get 1000 rows affected with no way back signature.asc Description: OpenPGP digital signature
syntax for strings in REQUIRE ISSUER / REQUIRE SUBJECT
I cannot seem to get SSL connections working using the REQUIRE ISSUER or REQUIRE SUBJECT clauses. I have a mysql working with ssl. I can connect from the client host to the server using ssl, where the user has been setup using: GRANT ALL PRIVILEGES ON x.* TO ''@'ipaddress' IDENTIFIED BY 'xx' REQUIRE X509; and the connection from client is done by mysql -h xxx -u xx -p --ssl-ca=/etc/mysql/ca-cert.pem --ssl-key=/etc/mysql/client-key.pem --ssl-cert=/etc/mysql/client-cert.pem However, the moment I try to restrict access to certs with specific issuer or subject I cannot connect GRANT ALL PRIVILEGES ON x.* TO ''@'ipaddress' IDENTIFIED BY 'xx' REQUIRE ISSUER 'C=IT, ST=Como, L=Erba, O=erba.tv, OU=erba.tv, CN=erba.tv/emailAddress=postmas...@erba.tv'; I have tried various permutations of specifying issuer string, i.e. C=IT, ST=Como, L=Erba, O=erba.tv, OU=erba.tv, CN=erba.tv/emailAddress=postmas...@erba.tv C=IT, ST=Como, L=Erba, O=erba.tv, OU=erba.tv, CN=erba.tv C=IT/ST=Como/L=Erba/O=erba.tv/OU=erba.tv/CN=erba.tv/emailAddress=postmas...@erba.tv C=IT/ST=Como/L=Erba/O=erba.tv/OU=erba.tv/CN=erba.tv but none seem to work (after flushing privileges each time). The first of these values is what is given by the command: openssl x509 -in /etc/mysql/client-cert.pem -noout -text The message I get is on trying to connect is: ERROR 1045 (28000): Access denied for user ''@'ipaddress' (using password: YES) The basics of ssl are obviously working, but for some reason the ISSUER check is not working. How can I debug that futher? John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.1 change master syntax issues?
Hi All I am trying to setup replication between 2 mysql servers, however when running the command below on the slave machine, I get the error as shown below. CHANGE MASTER TO MASTER_HOST='IP removed', MASTER_USER='repladmin', MASTER_PASSWORD='password', master_log_file=‘mysql-bin.000620’, master_log_pos=713184200; ERROR 1064 (42000): 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 '‘IP’, master_user=‘repladmin’, master_password=‘password’' at line 1 I have confirmed that I am able to connect to the master using these credentials, however Can't seem to spot my mistake in the command. The slave server is running mysql-5.1.50 and the master server is mysql-5.0 Machiel Richards Relational Database Consulting (Pty) Ltd MYSQL / POSTGRES specialist 20 Stirrup Lane, Woodmead Office Park, Van Reenens Road, Woodmead, South Africa Office: 0861 RDC RDC (0861 732 732) Mobile: +27 72 238 6008 Fax: 0861 RDC FAX (0861 732 329) E-Mail: machi...@rdc.co.za
stored procedure syntax error
Hi all !
I m getting an error while writing stored procedure, the code is as below and
error
CODE
--
DELIMITER $$
DROP PROCEDURE IF EXISTS `aaa` $$
CREATE definer=`ro...@`%mailto:definer=`ro...@`%` PROCEDURE `aaa`()
BEGIN
DECLARE b VARCHAR(255);
DECLARE c VARCHAR(255);
SET b= CONCAT(SUBDATE(CURDATE(), INTERVAL 15 DAY), 00:00:00);
SET c= CONCAT(',C://cells_summary.csv,');
SELECT * INTO OUTFILE (c) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b; //wrong
END $$
DELIMITER ;
--
ERROR
---
Script line: 4 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 '(c)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n''
at line 7
-
I guess there is a wronge syntax,
SELECT * INTO OUTFILE (c) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b;
kindly anyone can help with correct syntax.
Thanx
Re: stored procedure syntax error
I think, you have to use prepare() before run that select statement.
i.e
SET @s = CONCAT(SELECT * INTO OUTFILE, c ,FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED
BY '' LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b);
PREPARE stmt FROM @s;
EXECUTE stmt;
regards,
nilnandan
DAREKAR, NAYAN (NAYAN) wrote:
Hi all !
I m getting an error while writing stored procedure, the code is as below and
error
CODE
--
DELIMITER $$
DROP PROCEDURE IF EXISTS `aaa` $$
CREATE definer=`ro...@`%mailto:definer=`ro...@`%` PROCEDURE `aaa`()
BEGIN
DECLARE b VARCHAR(255);
DECLARE c VARCHAR(255);
SET b= CONCAT(SUBDATE(CURDATE(), INTERVAL 15 DAY), 00:00:00);
SET c= CONCAT(',C://cells_summary.csv,');
SELECT * INTO OUTFILE (c) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b; //wrong
END $$
DELIMITER ;
--
ERROR
---
Script line: 4 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 '(c)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n''
at line 7
-
I guess there is a wronge syntax,
SELECT * INTO OUTFILE (c) FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n' FROM cells_summary WHERE PMMDATEANDTIME=b;
kindly anyone can help with correct syntax.
Thanx
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
[ I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. snip I appreciate your thoughts on this. My first thought is that you're going to endup with some very inefficient queries or come unstuck with that table schema the first time you have an attributes of different types. What happens if attribute 1 is dateTaken has the type date, attribute 2 is authorName with the type string and attribute 3 is an aspect ratio N:n? My second thought is to make sure you have a unique index on (pid,aid) in table b. Sticking to the question you asked. Lets assume the search for this run of the search query is owned by userId 35 and two attribute clauses: has attribute 1 50 and attribute 3 = 4 I'd use: drop temporary table if exists AttSearchMatches; select pid as targetPid, count(*) as criteraMatched from B where userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by pid having criteraMatched = 2; drop temporary table if exists AttSearchMatches; select fields you want from criteraMatched cm on cm. inner join A on a.pid=criteraMatched.pid; drop temporary table AttSearchMatches; For best performance specify the temp table structure explicitly and add an index to pid. You could do this with a single query containing a sub-query rather than temporary tables but I've been bitten by sub-query performance before. Hope that helps, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
On Wed, 2010-06-16 at 08:59 +0100, Nigel Wood wrote: I'd use: drop temporary table if exists AttSearchMatches; select pid as targetPid, count(*) as criteraMatched from B where userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by pid having criteraMatched = 2; drop temporary table if exists AttSearchMatches; select fields you want from criteraMatched cm on cm. inner join A on a.pid=criteraMatched.pid; drop temporary table AttSearchMatches; Nope :-) Without the silly errors I'd use: drop temporary table if exists AttSearchMatches; select pid, count(*) as criteraMatched from B where b.userId=35 and ( (b.aid=1 and b.value 50) OR (b.aid=3 and b.value =4) ) group by b.pid having criteraMatched = 2; select a.fields you want from AttSearchMatches asm inner join A on a.pid=asm.pid; drop temporary table AttSearchMatches; Sorry, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
Hi! Daniel Brown wrote: [Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! [[...]] I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. [[...]] You need to do a multi-table join, table A joined to one instance of table B for each attribute relevant to your search. Roughly, syntax not tested, it is something like SELECT a.uid, a.pid FROM a JOIN b AS b1 ON a.pid=b1.pid JOIN b AS b2 ON a.pid=b2.pid JOIN ... WHERE b1.aid = 1 AND b1.value 100 AND b2.aid = 3 AND b2.value = 5 AND ... (assuming 'jpg' is coded as 5, what I take from your text). Now, I see some difficulties with this: 1) You are using the value column for anything, that may cause data type problems. 2) AFAIR, there was a post recently claiming the alias names (b1, b2, ...) could not be used in WHERE conditions, and the recommendation was to replace WHERE by HAVING. 3) If you need to support many attributes in one search, the number of tables joined grows, and the amount of data to handle (cartesian product!) will explode. What works fine with 3 criteria on 10 pictures (10 * 10 * 10 = 1000) may totally fail with 4 criteria on 200 pictures (200**4 = 800.000.000 = 800 million) 4) The more different attributes you store per picture, the larger your table B will become, and this will make the data grow for each join step. If you store 4 attributes each for 200 pictures, table B will already have 800 entries. In itself, that isn't much, but now the 4-way join will produce a cartesian product of 800**4 = 8**4 * 100**4 = 4096 * 100.000.000 = 409.600.000.000 combinations. In your place, I would use a separate table for attributes which are expected to be defined for all pictures, like size and image type. Then your general attributes table B will hold much fewer rows, thus each join step will profit. 5) Because of that explosion, it may be better to work with a temporary table, joining it to B for one attribute and thus reducing the data, then looping over such a step for all the relevant attributes. Good luck in experimenting! Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] SQL Syntax
[Top-post.] You'll probably have much better luck on the MySQL General list. CC'ed on this email. On Tue, Jun 15, 2010 at 20:58, Jan Reiter the-fal...@gmx.net wrote: Hi folks! I'm kind of ashamed to ask a question, as I haven't followed this list very much lately. This isn't exactly a PHP question, but since mysql is the most popular database engine used with php, I figured someone here might have an idea. I have 2 tables. Table A containing 2 fields. A user ID and a picture ID = A(uid,pid) and another table B, containing 3 fields. The picture ID, an attribute ID and a value for that attribute = B(pid,aid,value). Table B contains several rows for a single PID with various AIDs and values. Each AID is unique to a PID. (e.g. AID = 1 always holding the value for the image size and AID = 3 always holding a value for the image type) The goal is now to join table A on table B using pid, and selecting the rows based on MULTIPLE attributes. So the result should only contain rows for images, that relate to an attribute ID = 1 (size) that is bigger than 100 AND!!! an attribute ID = 5 that equals 'jpg'. I know that there is an easy solution to this, doing it in one query and I have the feeling, that I can almost touch it with my fingertips in my mind, but I can't go that final step, if you know what I mean. AND THAT DRIVES ME CRAZY!! I appreciate your thoughts on this. Regards, Jan -- /Daniel P. Brown daniel.br...@parasane.net || danbr...@php.net http://www.parasane.net/ || http://www.pilotpig.net/ We now offer SAME-DAY SETUP on a new line of servers! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Join syntax problem
As Tom Worster said, print($query); would show you what the query was trying to run. Without testing it, you also have some other whitespace issues between the hw.wildlife and FROM, and also, i m unsure of the asterix infront of the *images. On another note, when I do my JOINs, I tend to write ON (table1.field=jointable.field) rather than just ON (field). And on a final thought, the where cause, seems to be the join clause as well, so isn't that redundant? (or is would that only be in the way that i said i do my joins?) ++ | Steven Staples | ++ | I may be wrong, but at least I tried...| ++ -Original Message- From: Gary [mailto:g...@paulgdesigns.com] Sent: April 26, 2010 10:29 PM To: mysql@lists.mysql.com Subject: Re: Join syntax problem Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date: 04/26/10 02:31:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Join syntax problem
I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.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: Join syntax problem
On 4/26/10 7:36 PM, Gary gp...@paulgdesigns.com wrote: I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . looks like there's no space between 'im' and 'JOIN' in the line above WHERE ky.image_id = im.image_id; try: print($query); Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.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: Join syntax problem
I am reading this on a tiny screen but it looks like you need whitespace before the where. On 4/26/10, Gary gp...@paulgdesigns.com wrote: I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.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: Join syntax problem
Thanks for the replies. It was my understanding that whitespace is ignored, and I did not think that not having space, in particular with . would result in an error message. Gary Gary gp...@paulgdesigns.com wrote in message news:20100426233621.10789.qm...@lists.mysql.com... I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com __ Information from ESET NOD32 Antivirus, version of virus signature database 5063 (20100426) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.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: Like Syntax
Have you considered Reading The *Fine* Manual at http://dev.mysql.com/doc/#manual ? On Sat, Dec 5, 2009 at 4:59 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; I remember vaguely how to do this but don't know how to google it: show tables like categories$; such that it will return tables such as: categoriesProducts, categoriesPrescriptions, etc. TIA, Victor
Like Syntax
Hi; I remember vaguely how to do this but don't know how to google it: show tables like categories$; such that it will return tables such as: categoriesProducts, categoriesPrescriptions, etc. TIA, Victor
Re: Like Syntax
On Sat, Dec 5, 2009 at 11:09 AM, Michael Dykman mdyk...@gmail.com wrote: show tables like 'categories%'; Thanks. V
Create Syntax (easy)
Hi; Please give me the syntax below such that I can force the insert statements to use only selected values (item1, item2, item3): create table (field SOMETHING_HERE item1 item2 item3, ... ) TIA, Victor
Re: Create Syntax (easy)
On Tue, Sep 29, 2009 at 11:09 AM, Victor Subervi victorsube...@gmail.comwrote:
Hi;
Please give me the syntax below such that I can force the insert statements
to use only selected values (item1, item2, item3):
create table (field SOMETHING_HERE item1 item2 item3,
...
)
TIA,
Victor
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
From: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
David
--
There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa
Re: Create Syntax (easy)
That's it! Thanks,
V
On Tue, Sep 29, 2009 at 12:13 PM, David Giragosian dgiragos...@gmail.comwrote:
On Tue, Sep 29, 2009 at 11:09 AM, Victor Subervi victorsube...@gmail.com
wrote:
Hi;
Please give me the syntax below such that I can force the insert
statements
to use only selected values (item1, item2, item3):
create table (field SOMETHING_HERE item1 item2 item3,
...
)
TIA,
Victor
CREATE TABLE set_test(
rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
myset SET('Travel','Sports','Dancing','Fine Dining')
);
From: http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html
David
--
There is more hunger for love and appreciation in this world than for
bread.- Mother Teresa
Update Syntax
Hi; I would like to test the following: update maps set map where site=mysite; to see if there is such an entry in maps. If there is, then update. If there is not, then I would like to execute an insert statement. How do I do that? TIA, Victor
Re: Update Syntax
from: http://dev.mysql.com/doc/refman/5.1/en/insert.html:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
The ON DUPLICATE KEY predicate tells you that if you create a unique
key for when ever your 'search' criteria is, you can get this
behaviour like so:
INSERT INTO mytable SET col1 = val1, ... ON DUPLIATE KEY UPDATE
- michael dykman
On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com wrote:
Hi;
I would like to test the following:
update maps set map where site=mysite;
to see if there is such an entry in maps. If there is, then update. If there
is not, then I would like to execute an insert statement. How do I do that?
TIA,
Victor
--
- michael dykman
- mdyk...@gmail.com
Don’t worry about people stealing your ideas. If they’re any good,
you’ll have to ram them down their throats!
Howard Aiken
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Syntax
Hi Vicor, Look into INSERT ON DUPLICATE or REPLACE statements. You need to have a primary key or unique key for these too work. On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com wrote: Hi; I would like to test the following: update maps set map where site=mysite; to see if there is such an entry in maps. If there is, then update. If there is not, then I would like to execute an insert statement. How do I do that? TIA, Victor -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Update Syntax
Perfect. Thank you. Victor On Sun, Jul 26, 2009 at 2:18 PM, Darryle Steplight dstepli...@gmail.comwrote: Hi Vicor, Look into INSERT ON DUPLICATE or REPLACE statements. You need to have a primary key or unique key for these too work. On Sun, Jul 26, 2009 at 1:11 PM, Victor Subervivictorsube...@gmail.com wrote: Hi; I would like to test the following: update maps set map where site=mysite; to see if there is such an entry in maps. If there is, then update. If there is not, then I would like to execute an insert statement. How do I do that? TIA, Victor -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions?
LOAD DATA INFILE Syntax error
Hallo List! I have a CVS file which i would like to import to MySQL. The file header and an example: | Page,Device,Group,ItemID,Item,Value | Overview,General,Computer,513,OS,Linux The table has: | Create Table: CREATE TABLE `table` ( | `ID` int(11) NOT NULL auto_increment, | `Page` varchar(128) default NULL, | `Device` varchar(128) default NULL, | `Group` varchar(128) default NULL, | `ItemID` varchar(128) default NULL, | `Item` varchar(128) default NULL, | `Value` varchar(128) default NULL, | PRIMARY KEY (`ID`) | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So I would like to import the first file field to the second table field, the second file field to the third table,... Just to have an index. I'm using: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, GROUP , ItemID, Item, Value); which gives me: | #1064 - 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 'Group, ItemID, Item, Value)' at line 2 I cannot find the error. Please help me! MySQL version = 5.0.32-Debian_7etch8-log TIA, Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: LOAD DATA INFILE Syntax error
Group is a keyword in mysql: You need to put backticks around it in your statement: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, `GROUP` , ItemID, Item, Value); On Mon, Jun 29, 2009 at 7:07 AM, Ralph Kutschera news2...@ecuapac.dyndns.org wrote: Hallo List! I have a CVS file which i would like to import to MySQL. The file header and an example: | Page,Device,Group,ItemID,Item,Value | Overview,General,Computer,513,OS,Linux The table has: | Create Table: CREATE TABLE `table` ( | `ID` int(11) NOT NULL auto_increment, | `Page` varchar(128) default NULL, | `Device` varchar(128) default NULL, | `Group` varchar(128) default NULL, | `ItemID` varchar(128) default NULL, | `Item` varchar(128) default NULL, | `Value` varchar(128) default NULL, | PRIMARY KEY (`ID`) | ) ENGINE=MyISAM DEFAULT CHARSET=latin1 So I would like to import the first file field to the second table field, the second file field to the third table,... Just to have an index. I'm using: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, GROUP , ItemID, Item, Value); which gives me: | #1064 - 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 'Group, ItemID, Item, Value)' at line 2 I cannot find the error. Please help me! MySQL version = 5.0.32-Debian_7etch8-log TIA, Ralph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: LOAD DATA INFILE Syntax error
Johnny Withers schrieb: Group is a keyword in mysql: You need to put backticks around it in your statement: | LOAD DATA INFILE 'test.csv' INTO TABLE table | FIELDS TERMINATED BY ',' | LINES STARTING BY '' TERMINATED BY '\n' | (Page, Device, `GROUP` , ItemID, Item, Value); Ooookay. Thank you very much! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
SOS mysql signal syntax error
Hi Folks, I am getting syntax error with the mysql signal. I have a trigger that needs a signal for raising an error condition if a row with specific value is removed. CREATE TRIGGER my_trig BEFORE DELETE ON my_tbl FOR EACH ROW BEGIN DECLARE mysig CONDITION FOR SQLSTATE '45000'; IF OLD.name = 'base' THEN SIGNAL mysig SET MESSAGE_TEXT='base row removal is not allowed'; END IF; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SOS mysql signal syntax error
Interesting. This syntax is only supposed to be available as of 5.4, but it doesn't even work there. The reference I found was at : http://dev.mysql.com/tech-resources/articles/mysql-54.html But I couldn't find other references to the new signal support. This is listed as the example on that page, but it doesn't work in 5.4.0-beta CREATE PROCEDURE p (divisor INT) BEGIN DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN SIGNAL divide_by_zero; END IF; END Methinks someone forgot to include this feature in the release! -Original Message- From: Alex Katebi [mailto:alex.kat...@gmail.com] Sent: Wednesday, May 20, 2009 10:58 AM To: mysql Subject: SOS mysql signal syntax error Hi Folks, I am getting syntax error with the mysql signal. I have a trigger that needs a signal for raising an error condition if a row with specific value is removed. CREATE TRIGGER my_trig BEFORE DELETE ON my_tbl FOR EACH ROW BEGIN DECLARE mysig CONDITION FOR SQLSTATE '45000'; IF OLD.name = 'base' THEN SIGNAL mysig SET MESSAGE_TEXT='base row removal is not allowed'; END IF; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SOS mysql signal syntax error
OK I tried this exact syntax and I get the same error. I tried it on mysql client for 6.0.10 On Wed, May 20, 2009 at 2:22 PM, Gavin Towey gto...@ffn.com wrote: Interesting. This syntax is only supposed to be available as of 5.4, but it doesn't even work there. The reference I found was at : http://dev.mysql.com/tech-resources/articles/mysql-54.html But I couldn't find other references to the new signal support. This is listed as the example on that page, but it doesn't work in 5.4.0-beta CREATE PROCEDURE p (divisor INT) BEGIN DECLARE divide_by_zero CONDITION FOR SQLSTATE '22012'; IF divisor = 0 THEN SIGNAL divide_by_zero; END IF; END Methinks someone forgot to include this feature in the release! -Original Message- From: Alex Katebi [mailto:alex.kat...@gmail.com] Sent: Wednesday, May 20, 2009 10:58 AM To: mysql Subject: SOS mysql signal syntax error Hi Folks, I am getting syntax error with the mysql signal. I have a trigger that needs a signal for raising an error condition if a row with specific value is removed. CREATE TRIGGER my_trig BEFORE DELETE ON my_tbl FOR EACH ROW BEGIN DECLARE mysig CONDITION FOR SQLSTATE '45000'; IF OLD.name = 'base' THEN SIGNAL mysig SET MESSAGE_TEXT='base row removal is not allowed'; END IF; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
What is wrong with this SYNTAX?
It is complaining about near STRCMP. CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global FOR EACH ROW BEGIN IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN set NEW.Variable_name=NULL; ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Variable_name=NULL ELSEIF STRCMP(NEW.Variable_name, 'ASN') != 0 THEN set NEW.Value=CONVERT(CONVERT(NEW.Value, UNSIGNED), CHAR); ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Value=INET_NTOA(INET_ATON(NEW.Value)); ENDIF END Thanks in advance! Alex
RE: What is wrong with this SYNTAX?
DELIMITER $$ CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global FOR EACH ROW BEGIN IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN set NEW.Variable_name=NULL; ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Variable_name=NULL (-- Missing Semicolon) ELSEIF STRCMP(NEW.Variable_name, 'ASN') != 0 THEN (-- Same as IF) set NEW.Value=CONVERT(CONVERT(NEW.Value, UNSIGNED), CHAR); ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN (-- Same as First ELSEIF) set NEW.Value=INET_NTOA(INET_ATON(NEW.Value)); ENDIF END; DELIMITER ; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Alex Katebi [mailto:alex.kat...@gmail.com] Sent: Tuesday, May 19, 2009 10:33 AM To: mysql@lists.mysql.com Subject: What is wrong with this SYNTAX? It is complaining about near STRCMP. CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global FOR EACH ROW BEGIN IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN set NEW.Variable_name=NULL; ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Variable_name=NULL ELSEIF STRCMP(NEW.Variable_name, 'ASN') != 0 THEN set NEW.Value=CONVERT(CONVERT(NEW.Value, UNSIGNED), CHAR); ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Value=INET_NTOA(INET_ATON(NEW.Value)); ENDIF END Thanks in advance! Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: What is wrong with this SYNTAX?
DELIMITER $$ CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global FOR EACH ROW BEGIN IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN set NEW.Variable_name=NULL; ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Variable_name=NULL (-- Missing Semicolon) ELSEIF STRCMP(NEW.Variable_name, 'ASN') != 0 THEN (-- Same as IF) set NEW.Value=CONVERT(CONVERT(NEW.Value, UNSIGNED), CHAR); ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN (-- Same as First ELSEIF) set NEW.Value=INET_NTOA(INET_ATON(NEW.Value)); ENDIF (-- Missing Semicolon) END; DELIMITER ; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Rolando Edwards [mailto:redwa...@logicworks.net] Sent: Tuesday, May 19, 2009 12:40 PM To: Alex Katebi; mysql@lists.mysql.com Subject: RE: What is wrong with this SYNTAX? DELIMITER $$ CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global FOR EACH ROW BEGIN IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN set NEW.Variable_name=NULL; ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Variable_name=NULL (-- Missing Semicolon) ELSEIF STRCMP(NEW.Variable_name, 'ASN') != 0 THEN (-- Same as IF) set NEW.Value=CONVERT(CONVERT(NEW.Value, UNSIGNED), CHAR); ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN (-- Same as First ELSEIF) set NEW.Value=INET_NTOA(INET_ATON(NEW.Value)); ENDIF END; DELIMITER ; Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Alex Katebi [mailto:alex.kat...@gmail.com] Sent: Tuesday, May 19, 2009 10:33 AM To: mysql@lists.mysql.com Subject: What is wrong with this SYNTAX? It is complaining about near STRCMP. CREATE TRIGGER bgp.tglobal BEFORE UPDATE on bgp.global FOR EACH ROW BEGIN IF STRCMP(NEW.Variable_name,'ASN') != 0 THEN set NEW.Variable_name=NULL; ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Variable_name=NULL ELSEIF STRCMP(NEW.Variable_name, 'ASN') != 0 THEN set NEW.Value=CONVERT(CONVERT(NEW.Value, UNSIGNED), CHAR); ELSEIF STRCMP(NEW.Variable_name, 'RouterId') != 0 THEN set NEW.Value=INET_NTOA(INET_ATON(NEW.Value)); ENDIF END Thanks in advance! Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Confused about syntax for specific join with 3 tables
Date: Sat, 16 May 2009 15:39:56 -0700
From: davidmichaelk...@gmail.com
To: mysql@lists.mysql.com
Subject: Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
Hi David,
Well I could say it's probably because grade_event is a parent table while
score is a child table. And the parent joined first (you know, the deserved
respect) :)). Cheers.
Alugo Abdulazeez
www.frangeovic.com
_
Windows Live™: Keep your life in sync. Check it out!
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
Confused about syntax for specific join with 3 tables
I've been doing some experimenting with the data model from the MySQL
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sexENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this logically, I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as student joining to score joining to grade_event.
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
Thanks, Scott. I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! Thanks, again. Have a great day. :) On Thu, Apr 30, 2009 at 12:52 PM, Scott Haneda talkli...@newgeo.com wrote: On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: This is MySQL data structure. - I underlined where it causes the error message. (datetime) `id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL `Revenue` mediumint(6) NOT NULL, `Company_Size` mediumint(6) NOT NULL, `Ownership` tinyint(1) NOT NULL, `Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *`Created` datetime NOT NULL, *PRIMARY KEY (`id_Company`), KEY `Ownership` (`Ownership`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE CASCADE ON UPDATE CASCADE; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. Here is php script - $sql = INSERT INTO company SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source' *Created=NOW() // if I remove this line it works fine. *; mysql_query ($sql) or die (mysql_error()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ *
Re: Now() : SQL syntax error. But why?
Always echo out your SQL string, it will make it a lot more obvious. You want to see the result. I php concatenated string can be confusing at times. Also, you are not escaping your data, so if you had a word of 'stops, here' that would break it as well. So in your case, you very well may break it by changing the data you put in. You could also do something like stuffing drop database foo; into your data, and be in for real fun. Pass every string to http://us2.php.net/mysql_real_escape_string On Apr 30, 2009, at 9:27 PM, Antonio PHP wrote: I thought I couldn't have missed ','(comma) before. But today somehow it works... ;; I wasted hours figuring this out, but you saved me! Maybe I'm still a complete newbie! -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
Thanks. NOW() and php date(); work for my newly created test tables, but it
doesn't work for my working table. I can't insert date, time or now()
into my old table (which is as below).
For now, I'm using MySQL auto timestamp ('Updated' field), but I need to
insert date when the data was created!
Why is this? Please help me. (I'm using the newest versions of PHP and
MySQL)
This is MySQL data structure. - I underlined where it causes the error
message. (datetime)
`id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
`Revenue` mediumint(6) NOT NULL,
`Company_Size` mediumint(6) NOT NULL,
`Ownership` tinyint(1) NOT NULL,
`Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT
NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
*`Created` datetime NOT NULL,
*PRIMARY KEY (`id_Company`),
KEY `Ownership` (`Ownership`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE
CASCADE ON UPDATE CASCADE;
Here is php script -
$sql = INSERT INTO company SET
Name='$Name',
Revenue='$Revenue',
Company_Size='$Company_Size',
Ownership='$Ownership',
Homepage='$Homepage',
Job_Source='$Job_Source'
*Created=NOW() // if I remove this line it works fine.
*;
mysql_query ($sql) or die (mysql_error());
Also, this doesn't work for this table.
$Datetime = date( 'Y-m-d H:i:s');
INSERT INTO
Created='$Datetime'...
On Wed, Apr 29, 2009 at 9:28 AM, Scott Haneda talkli...@newgeo.com wrote:
We need to see your entire query and the table structure. timestamp fields
can have options set to auto update them, where order matters, and only one
field can support that feature.
Please supply more data.
On Apr 28, 2009, at 2:18 PM, Antonio PHP wrote:
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 'Created =
NOW(),
Updated = NOW()' at line 8
'Created' and 'Updated' are set to datetime (InnoDB).
The same syntax works for some newly created tables... and gives no error.
It's very strange. 'Now()' works for some tables, and it doesn't for some.
(All set in phpmyadmin...)
What could have caused this? Any similar experience?
--
Scott * If you contact me off list replace talklists@ with scott@ *
Re: Now() : SQL syntax error. But why?
On Apr 29, 2009, at 11:29 AM, Antonio PHP wrote: This is MySQL data structure. - I underlined where it causes the error message. (datetime) `id_Company` smallint(6) unsigned NOT NULL AUTO_INCREMENT, `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL `Revenue` mediumint(6) NOT NULL, `Company_Size` mediumint(6) NOT NULL, `Ownership` tinyint(1) NOT NULL, `Homepage` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Job_Source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, *`Created` datetime NOT NULL, *PRIMARY KEY (`id_Company`), KEY `Ownership` (`Ownership`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FOREIGN KEY (`Ownership`) REFERENCES `ownership` (`id_Ownership`) ON DELETE CASCADE ON UPDATE CASCADE; Next time can you include unmodified SQL so it is a copy and paste for me, rather than debugging what changes you made that are causing error. Here is php script - $sql = INSERT INTO company SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source' *Created=NOW() // if I remove this line it works fine. *; mysql_query ($sql) or die (mysql_error()); Same here, as I am not sure your edits are just edits, or the lack of a comma after the job source variable is the issue. This works on my end: $Name = 'Tom'; $Revenue = '100'; $Company_Size = '500'; $Ownership= 'partner'; $Homepage = 'example.com'; $Job_Source = 'friend'; $sql = INSERT INTO mailing SET Name='$Name', Revenue='$Revenue', Company_Size='$Company_Size', Ownership='$Ownership', Homepage='$Homepage', Job_Source='$Job_Source', Created=NOW(); echo $sql; mysql_query ($sql) or die (mysql_error()); -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Now() : SQL syntax error. But why?
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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? Please help~.
Re: Now() : SQL syntax error. But why?
Can you please give the full table structure and query? On Tue, Apr 28, 2009 at 23:18, Antonio PHP php.anto...@gmail.com wrote: 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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? Please help~. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
We need to see your entire query and the table structure. timestamp fields can have options set to auto update them, where order matters, and only one field can support that feature. Please supply more data. On Apr 28, 2009, at 2:18 PM, Antonio PHP wrote: 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 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) you have to use the ` sign, not the ' Uwe :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSes1W0JXG7BUuynnAQJWExAAuzKonpnWPeynnFyvodfGYSP1p9A5ybC6 Azi0j4iGk237HlJ8I8OKh+aBsQGnykzGEQ5eE4xAAaL7bocFlYjHkvaIAhqxzg7c /ZaS9j8k+sGrteAsiA1y8fTj4L3uzLJP+0+crm1yKGKO8Jl9BKxqWl+x3Di6XMYF 1R8OdgBDY1mClUZEqbbBtm0pOs6N/SGcLGyiZNH5xEyRrnXbrh24WY0OQov5pDPj T2G0vC2GpZOZEN1qQeJp5G2ZZ+8gPvZhW+cMElvLNVF3aWkWyd/hEFIxG0cBZ/cR afcreK3AcnAP80ng8150cFN+y5nMR5GExqPDx1j91s9zdW7poMjZiRZKjAOMSqJv Aj+m/MitxGECTOIrCiYo9kP5CyzT6LIng9jyqiNCNR3L2T4sx/LgpLvap+ynwsmz kbqBaLcuPwzCW2f5eIYsfrh5lJMoF86GjTshbFnXXk/EN4etcjTTuFJMiNqPjqvy jonw0b1S17qHRJJPSfLvgk0NEWOB6tYQQuVcWyZyEb0TamfHP04Vdbe9NIKexU04 gafSM5pOBed4DfCsy6qaO9jstrJGMVQlZxxbDzgvsDazMmyh9lTwtup+5TGmxk+I e7HziEFBun8sf1BhDfwm1CJfAQs28lkuD4KctV3DuKyXYqDBftEr0fguxCtqNCtJ 0dTcSkPFzKU= =jim1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
Thanks Uwe, I used ` now. It says ls: z*: no such file or directory exists then, when I enter the password it dumps all the tables. (instead of tables starting with 'z') Yes, I'm a newbie. (I didn't know to use ` instead of '...) But please help me. Plz tell me what I've done wrongly this time. :) Date: Sun, 19 Apr 2009 16:29:48 +0200 From: m...@kiewel-online.ch To: saeho...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) you have to use the ` sign, not the ' Uwe :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSes1W0JXG7BUuynnAQJWExAAuzKonpnWPeynnFyvodfGYSP1p9A5ybC6 Azi0j4iGk237HlJ8I8OKh+aBsQGnykzGEQ5eE4xAAaL7bocFlYjHkvaIAhqxzg7c /ZaS9j8k+sGrteAsiA1y8fTj4L3uzLJP+0+crm1yKGKO8Jl9BKxqWl+x3Di6XMYF 1R8OdgBDY1mClUZEqbbBtm0pOs6N/SGcLGyiZNH5xEyRrnXbrh24WY0OQov5pDPj T2G0vC2GpZOZEN1qQeJp5G2ZZ+8gPvZhW+cMElvLNVF3aWkWyd/hEFIxG0cBZ/cR afcreK3AcnAP80ng8150cFN+y5nMR5GExqPDx1j91s9zdW7poMjZiRZKjAOMSqJv Aj+m/MitxGECTOIrCiYo9kP5CyzT6LIng9jyqiNCNR3L2T4sx/LgpLvap+ynwsmz kbqBaLcuPwzCW2f5eIYsfrh5lJMoF86GjTshbFnXXk/EN4etcjTTuFJMiNqPjqvy jonw0b1S17qHRJJPSfLvgk0NEWOB6tYQQuVcWyZyEb0TamfHP04Vdbe9NIKexU04 gafSM5pOBed4DfCsy6qaO9jstrJGMVQlZxxbDzgvsDazMmyh9lTwtup+5TGmxk+I e7HziEFBun8sf1BhDfwm1CJfAQs28lkuD4KctV3DuKyXYqDBftEr0fguxCtqNCtJ 0dTcSkPFzKU= =jim1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=saeho...@hotmail.com _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: Thanks Uwe, I used ` now. It says ls: z*: no such file or directory exists You need to use the path full qualified, e.g. ls /var/lib/mysql/db/z*, or wherever your mysql installation live. then, when I enter the password it dumps all the tables. (instead of tables starting with 'z') Yes, I'm a newbie. (I didn't know to use ` instead of '...) But please help me. Plz tell me what I've done wrongly this time. :) Date: Sun, 19 Apr 2009 16:29:48 +0200 From: m...@kiewel-online.ch To: saeho...@hotmail.com CC: mysql@lists.mysql.com Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ChoiSaehoon schrieb: I tried it, then it gives the following error message mysqldump: Can't get CREATE TABLE for table 'ls z*' (Table '(db-name).ls z*' doesn't exist) What does 'ls' mean? (as in linux command 'ls'?) you have to use the ` sign, not the ' Uwe :) Date: Sun, 19 Apr 2009 08:53:36 -0500 Subject: Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*) From: jlyons4...@gmail.com To: saeho...@hotmail.com CC: mysql@lists.mysql.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com _ 강력해진 보안성, 아웃룩을 닮아 편리해진 기능들로 무장한 Windows Live Hotmail! 지금 로그인해 보세요! http://www.hotmail.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSes1W0JXG7BUuynnAQJWExAAuzKonpnWPeynnFyvodfGYSP1p9A5ybC6 Azi0j4iGk237HlJ8I8OKh+aBsQGnykzGEQ5eE4xAAaL7bocFlYjHkvaIAhqxzg7c /ZaS9j8k+sGrteAsiA1y8fTj4L3uzLJP+0+crm1yKGKO8Jl9BKxqWl+x3Di6XMYF 1R8OdgBDY1mClUZEqbbBtm0pOs6N/SGcLGyiZNH5xEyRrnXbrh24WY0OQov5pDPj T2G0vC2GpZOZEN1qQeJp5G2ZZ+8gPvZhW+cMElvLNVF3aWkWyd/hEFIxG0cBZ/cR afcreK3AcnAP80ng8150cFN+y5nMR5GExqPDx1j91s9zdW7poMjZiRZKjAOMSqJv Aj+m/MitxGECTOIrCiYo9kP5CyzT6LIng9jyqiNCNR3L2T4sx/LgpLvap+ynwsmz kbqBaLcuPwzCW2f5eIYsfrh5lJMoF86GjTshbFnXXk/EN4etcjTTuFJMiNqPjqvy jonw0b1S17qHRJJPSfLvgk0NEWOB6tYQQuVcWyZyEb0TamfHP04Vdbe9NIKexU04 gafSM5pOBed4DfCsy6qaO9jstrJGMVQlZxxbDzgvsDazMmyh9lTwtup+5TGmxk+I e7HziEFBun8sf1BhDfwm1CJfAQs28lkuD4KctV3DuKyXYqDBftEr0fguxCtqNCtJ 0dTcSkPFzKU= =jim1 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=saeho...@hotmail.com 강력한 폴더 공유 기능과 무료 문자 메시지, 오프라인 쪽지 보내기 기능까지! MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQIVAwUBSetE9UJXG7BUuynnAQLWXQ/6AhduBZHFSrfsyhmaK0yRjjy6H6+fWZ0s cUfCANJlGbkP1RW6VpMVeF6U2o8xDBcs7m4OLOfLckT5/Lf+RX7AFj9T9T++3oPd DMGZzHEAStApcD0yvYqDPi5Mc88aPUdBaJyNbhc1Ufs+8M42T5sGkqfPWjB5r4Co REdKFt+6JC7VlIBGNn0EdVYA554IQ+93WJus5p9IGk+k5YS5NNBzDiF38SNRszco 2qH9b7I3FP8nxYnlWbpbNdVb0WC5RRk8HojpOE1X+jSJKcWqiebjC+ayVkAytgKL zZUxcmBmQjf2lRpbMatpR2YV1TZKLkWu6nMCfdYFtK/ggxrA23riIbvehjibXRIJ JdLSUp49EWUSx9Fk3DrsuDHiXyZy0mhcEanmBNU5jQSspq6pseYWXDoQUBW1TXY1 i9fs0nItaI+dXZcyvcMbYDRXsttuPUrfzB9lEQORPK2d7htxnsCRZtL0vcMmV8b7 yGVkirLyL4+6RlSnEfGk0lxb+Hi6MgVvOJ2V1J46A0pF6Pab+Mwi0+RoQ3YcLdPI OaWsVGelx+gKsY1szB7kYq2mfHcN+L0Hcdh+6U06+Y2SvJgavVn57sBTislBPfds fl3DfDdHayBFDI2IyrpLPuvq7Zug1Raj4pc8SyMswVeN0MWI4akxl77+hVTeKfrS UYxuDNNhrSc= =OFKv -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqldump syntax - dumping only specific tables starting with a certain character. (e.g. z*)
actually, that was stupid of me - you need a list of tables not files. I think the only to do this, and the way we do it, is to run some command like: mysql -eshow tables in db-name like 'z%' tabnames Note the use of double-quotes and single-quotes. then use a loop to read the file tabnames and build a string to tack on the mysqldump command, or issue multiple mysqldump commands. A shell interpreter like bash or a program like perl or php can do this easily. You might try putting the above command in back-tics (`) and then inserting directly into the mysqldump command. 2009/4/19 Jim Lyons jlyons4...@gmail.com try something like: mysqldump -u(user) -p (db-name) `ls z*` (filename) 2009/4/19 ChoiSaehoon saeho...@hotmail.com Is there a way to dump only specific tables starting with a certain character? For example, I only want to dump tables starting with the character 'z'. The following doesn't work. mysqldump -u(user) -p (db-name) z* (filename) Do I have to use regular expression here? Please help thanks in advance. _ MSN 메신저의 차세대 버전, Windows Live Messenger! http://windowslive.msn.co.kr/wlm/messenger/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Creating table syntax error with mysql 5.0!
I'm new with MySQL server 5.0 ,I tried to create a table MemberDetails from the mysql command shell, I got an error 1064 (42000): *You have an error in your SQL syntax*; Here is my code: mysql CREATE TABLE memberDetails - *(* -memberId INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY(memberId), -first_name varchar(100), -last_name varchar(100), -Email varchar(60), -phoneNum varchar(25) - *);* Any thoughts ?
Re: Creating table syntax error with mysql 5.0!
mysql CREATE TABLE memberDetails - *(* -memberId INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY(memberId), -first_name varchar(100), -last_name varchar(100), -Email varchar(60), -phoneNum varchar(25) - *);* Works without the asterisks. PB - jean claude babin wrote: I'm new with MySQL server 5.0 ,I tried to create a table MemberDetails from the mysql command shell, I got an error 1064 (42000): *You have an error in your SQL syntax*; Here is my code: mysql CREATE TABLE memberDetails - *(* -memberId INT NOT NULL AUTO_INCREMENT, -PRIMARY KEY(memberId), -first_name varchar(100), -last_name varchar(100), -Email varchar(60), -phoneNum varchar(25) - *);* Any thoughts ? Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.9.0/1777 - Release Date: 11/9/2008 9:53 AM
Re: Wierd INSERT ... SELECT syntax problem
Well, for your simple example, you can use query variables to add the counters. SET @cntr:=0, @lastVal:='A' INSERT INTO tableB LOC,DATA SELECT CONCAT(LOC, CONCAT( IF(@lastVal=LOC, @cntr:[EMAIL PROTECTED], @cntr:=0), IF(@lastVal:=LOC,'',''))) LOC, CONCAT(DATA, @cntr) FROM tableA ORDER BY LOC That should add a sequential number to LOC and DATA that will reset to 0 whenever the value of LOC changes. Some of the IFs in there are just to suppress output of variable assignment. Hope that helps Brent Baisley On Sep 5, 2008, at 5:44 PM, Dan Tappin wrote: I have an existing data set - here is an example (the real one is more complex than this) LOC DATA - A 1 B 2 C 3 D 4 E 5 F 6 ... and I am looking to run some sort of INSERT ... SELECT on this to make a new table like this: LOC DATA - A0 10 A1 11 A2 12 A3 13 B0 20 B1 21 B2 22 B3 23 C0 30 C1 31 C2 32 C3 33 D0 40 D1 41 D2 42 D3 43 E0 50 E1 51 E2 52 E3 53 F0 60 F1 61 F2 62 F3 63 I basically want to take the data from each row, perform n number of operations on it and insert it into a new table. I could make a PHP script that does this but I figured there had to be a better way. Any ideas? Dan -- 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: Wierd INSERT ... SELECT syntax problem
Thanks for the tip. I am looking at just making 16 separate queries. It will be easier to manage and faster to run. Dan On Sep 6, 2008, at 9:37 PM, Brent Baisley [EMAIL PROTECTED] wrote: Well, for your simple example, you can use query variables to add the counters. SET @cntr:=0, @lastVal:='A' INSERT INTO tableB LOC,DATA SELECT CONCAT(LOC, CONCAT( IF(@lastVal=LOC, @cntr:[EMAIL PROTECTED], @cntr:=0), IF(@lastVal:=LOC,'',''))) LOC, CONCAT(DATA, @cntr) FROM tableA ORDER BY LOC That should add a sequential number to LOC and DATA that will reset to 0 whenever the value of LOC changes. Some of the IFs in there are just to suppress output of variable assignment. Hope that helps Brent Baisley On Sep 5, 2008, at 5:44 PM, Dan Tappin wrote: I have an existing data set - here is an example (the real one is more complex than this) LOCDATA - A1 B2 C3 D4 E5 F6 ... and I am looking to run some sort of INSERT ... SELECT on this to make a new table like this: LOCDATA - A010 A111 A212 A313 B020 B121 B222 B323 C030 C131 C232 C333 D040 D141 D242 D343 E050 E151 E252 E353 F060 F161 F262 F363 I basically want to take the data from each row, perform n number of operations on it and insert it into a new table. I could make a PHP script that does this but I figured there had to be a better way. Any ideas? Dan -- 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]
Wierd INSERT ... SELECT syntax problem
I have an existing data set - here is an example (the real one is more complex than this) LOC DATA - A 1 B 2 C 3 D 4 E 5 F 6 ... and I am looking to run some sort of INSERT ... SELECT on this to make a new table like this: LOC DATA - A0 10 A1 11 A2 12 A3 13 B0 20 B1 21 B2 22 B3 23 C0 30 C1 31 C2 32 C3 33 D0 40 D1 41 D2 42 D3 43 E0 50 E1 51 E2 52 E3 53 F0 60 F1 61 F2 62 F3 63 I basically want to take the data from each row, perform n number of operations on it and insert it into a new table. I could make a PHP script that does this but I figured there had to be a better way. Any ideas? Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT WHERE NOT EXISTS syntax
Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't? Roger
RE: INSERT WHERE NOT EXISTS syntax
I think I sorted it out ... INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS ( SELECT comment_no FROM master_comments WHERE mComments.comment_no = master_comments.comment_no ); Hope this helps someone else . -Original Message- From: roger.maynard [mailto:[EMAIL PROTECTED] Sent: 23 January 2008 18:58 To: mysql@lists.mysql.com Subject: INSERT WHERE NOT EXISTS syntax Can anyone tell me why this isn't working... v5.0 INSERT INTO master_comments (comment_no,comment_text,language_id) SELECT comment_no,comment_text,language_id from mComments WHERE NOT EXISTS (SELECT comment_no FROM master_comments); I thought I had it working once but now it isn't? Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
errors in mysql syntax
ALTER TABLE TimeTracker.TT_ProjectMembers ADD CONSTRAINT FK_Roles_Projects FOREIGN KEY ( ProjectID ) REFERENCES TimeTracker.TT_Projects ( ProjectID ) ON DELETE CASCADE , CONSTRAINT FK_WorksOn_Users FOREIGN KEY ( UserID ) REFERENCES TimeTracker.TT_Users ( UserID ) As iam new to this mysql when i was doing the program mysql shows the error as 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 'CONSTRAINT FK_WorksOn_Users FOREIGN KEY ( UserID ) REFERENCES TimeTracke' at line 8 what to do if this error comes plz anyone help me -- View this message in context: http://www.nabble.com/errors-in-mysql-syntax-tf4532650.html#a12935188 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count syntax
Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- 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: Count syntax
Beauford, you might find this article on cross joins interesting, it was something shown to me a few weeks ago that discusses this kind of function. http://dev.mysql.com/tech-resources/articles/wizard/print_version.html Cheers, craig This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system. -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 1:36 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax 1 means that 1 will be added to the sum if the condition tests, otherwise 0 will be added to the sum. the condition in this case is (supportertype = 'L') and will be applied to every row. On 9/28/07, Beauford [EMAIL PROTECTED] wrote: Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- 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] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- 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: Count syntax
Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count syntax
1 means that 1 will be added to the sum if the condition tests, otherwise 0 will be added to the sum. the condition in this case is (supportertype = 'L') and will be applied to every row. On 9/28/07, Beauford [EMAIL PROTECTED] wrote: Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- 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] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Count syntax
Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count syntax
Thanks to all. -Original Message- From: Michael Dykman [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:36 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax 1 means that 1 will be added to the sum if the condition tests, otherwise 0 will be added to the sum. the condition in this case is (supportertype = 'L') and will be applied to every row. On 9/28/07, Beauford [EMAIL PROTECTED] wrote: Thanks - it works, but what does the 1 and 0 do in this - SUM(IF(supportertype = 'L', 1, 0)) -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: September 28, 2007 1:00 PM To: Beauford Cc: mysql@lists.mysql.com Subject: Re: Count syntax Beauford wrote: Hi, I have the following line of code and I keep getting wrong results from it. Can someone let me know what I'm doing wrong here. I just can't quite figure out the syntax that I need. select count(*) as numrows, count(supportertype) as leadcar from registrar where supportertype = 'L'; What I want to do is count the total number of records and then the total number of records that have L as the supportertype and then display them. So I should have something like There are 100 total records and 22 with Supporter Type L. Try this: SELECT COUNT(*), SUM(IF(supportertype = 'L', 1, 0)) FROM registrar; Baron -- 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] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- 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]
Syntax Error in Stored Procedure
Below is a new stored procedure that I am trying to make for building a SQL
with the supplied parameters.
1:DELIMITER $$
2:CREATE PROCEDURE `Search_Code_Samples`(
3:IN search_words VARCHAR(300)
4: , IN multi_word_condition VARCHAR(3)
5: , IN language_id INT
6: , IN sample_type CHAR(1)
7:)
8:READS SQL DATA
9:COMMENT 'Prepares and executes SQL to find records according to the
data provided.'
10:BEGIN
11:DECLARE Base_SQL VARCHAR(1500);
12:DECLARE Filter_Clause VARCHAR(3000);
13:DECLARE Final_SQL VARCHAR(5500);
14:DECLARE First_Instance CHAR(1);
15:DECLARE len_search_words INT;
16:DECLARE word_idx INT;
17:DECLARE last_pos INT;
18:DECLARE word_length INT;
19:DECLARE new_word varchar(200);
20:DECLARE Search_Clause VARCHAR(1000);
21:DECLARE this_Word varchar(200);
22:DECLARE no_more_rows INT default 0;
23:
24:-- First handle the list of search words
25:IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
26:SET multi_word_condition = 'AND';
27:END IF;
28:IF (sample_type IS NULL) OR (sample_type = ' ') THEN
29:SET sample_type = 'A';
30:END IF;
31:SET Base_SQL = 'select * from CodeSamples_View ';
32:SET Filter_Clause = '';
33:SET First_Instance = 'Y';
34:IF (search_words IS NOT NULL) AND (search_words '') AND
(search_words ' ') THEN
35:-- Parse the list of search words by spaces
36:SET len_search_words = LENGTH(search_words);
37:SET word_idx = 1;
38:SET last_pos = 1;
39:CREATE TEMPORARY TABLE Temp_Word_List (Search_Word VARCHAR(200)
NOT NULL);
40:WHILE (word_idx = len_search_words) DO
41:IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
42:SET word_length = word_idx - last_pos;
43:SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
44:SET last_pos = word_idx;
45:IF (new_word '') THEN
46:INSERT INTO Temp_Word_List VALUES (new_word);
47:END IF;
48:END IF;
49:SET word_idx = word_idx + 1;
50:END WHILE;
51:-- Put in the very last word
52:IF (word_idx last_pos) THEN
53:SET word_length = word_idx - last_pos;
54:SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
55:IF (new_word '') THEN
56:INSERT INTO Temp_Word_List VALUES (new_word);
57:END IF;
58:END IF;
59:-- Generate the SQL clause for the search words.
60:DECLARE words_list CURSOR FOR SELECT Search_Word FROM
Temp_Word_List;
61:DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
62:OPEN words_list;
63:REPEAT
64:FETCH words_list INTO this_Word;
65:IF (no_more_rows 1) THEN
66:IF (First_Instance = 'Y') THEN
67:SET First_Instance = 'N';
68:ELSE
69:SET Filter_Clause = concat(Filter_Clause,
multi_word_condition, ' ');
70:END IF;
71:SET Filter_Clause = concat(Filter_Clause,
'match(title,description) against(''', this_Word, ''') ');
72:END IF;
73:UNTIL (no_more_rows = 1) END REPEAT;
74:CLOSE words_list;
75:DROP TEMPORARY TABLE Temp_Word_List;
76:END IF;
77:
78:-- Language Id
79:IF (language_id IS NOT NULL AND language_id 0) THEN
80:IF (First_Instance = 'Y') THEN
81:SET First_Instance = 'N';
82:ELSE
83:SET Filter_Clause = concat(Filter_Clause, ' AND ');
84:END IF;
85:SET Filter_Clause = concat(Filter_Clause, 'language = ',
cast(language_id as CHAR(4)), ' ');
86:END IF;
87:
88:-- Sample type
89:IF (sample_type IS NOT NULL AND sample_type 'A' AND sample_type
'' AND sample_type ' ') THEN
90:IF (First_Instance = 'Y') THEN
91:SET First_Instance = 'N';
92:ELSE
93:SET Filter_Clause = concat(Filter_Clause, ' AND ');
94:END IF;
95:SET Filter_Clause = concat(Filter_Clause, 'sample_type = ''',
sample_type, );
96:END IF;
97:
98:-- Construct the final SQL statement.
99:IF (Filter_Clause = '') THEN
100:SET Final_SQL = Base_SQL;
101:ELSE
102:SET Final_SQL = concat(Base_SQL, 'WHERE ', Filter_Clause);
103:END IF;
104:
105:-- Execute the constructed SQL Statement.
106:PREPARE search_statement FROM Final_SQL;
107:EXECUTE search_statement;
108:END; $$
When attempting to compile this procedure I get this 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 'DECLARE
this_Word varchar(200); DECLARE no_more_rows INT
RE: Syntax Error in Stored Procedure
Tom,
I think the problem might be that you have to put all the DECLARE
statements at the top before the other statements. It seems like I had
a similar problem with this once.
Hope this helps.
Randall Price
Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology
1700 Pratt Drive
Blacksburg, VA 24060
Email: [EMAIL PROTECTED]
Phone: (540) 231-4396
-Original Message-
From: Tom Khoury [mailto:[EMAIL PROTECTED]
Sent: Monday, August 13, 2007 2:38 PM
To: mysql@lists.mysql.com
Subject: Syntax Error in Stored Procedure
Below is a new stored procedure that I am trying to make for building a
SQL
with the supplied parameters.
1:DELIMITER $$
2:CREATE PROCEDURE `Search_Code_Samples`(
3:IN search_words VARCHAR(300)
4: , IN multi_word_condition VARCHAR(3)
5: , IN language_id INT
6: , IN sample_type CHAR(1)
7:)
8:READS SQL DATA
9:COMMENT 'Prepares and executes SQL to find records according to
the
data provided.'
10:BEGIN
11:DECLARE Base_SQL VARCHAR(1500);
12:DECLARE Filter_Clause VARCHAR(3000);
13:DECLARE Final_SQL VARCHAR(5500);
14:DECLARE First_Instance CHAR(1);
15:DECLARE len_search_words INT;
16:DECLARE word_idx INT;
17:DECLARE last_pos INT;
18:DECLARE word_length INT;
19:DECLARE new_word varchar(200);
20:DECLARE Search_Clause VARCHAR(1000);
21:DECLARE this_Word varchar(200);
22:DECLARE no_more_rows INT default 0;
23:
24:-- First handle the list of search words
25:IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
26:SET multi_word_condition = 'AND';
27:END IF;
28:IF (sample_type IS NULL) OR (sample_type = ' ') THEN
29:SET sample_type = 'A';
30:END IF;
31:SET Base_SQL = 'select * from CodeSamples_View ';
32:SET Filter_Clause = '';
33:SET First_Instance = 'Y';
34:IF (search_words IS NOT NULL) AND (search_words '') AND
(search_words ' ') THEN
35:-- Parse the list of search words by spaces
36:SET len_search_words = LENGTH(search_words);
37:SET word_idx = 1;
38:SET last_pos = 1;
39:CREATE TEMPORARY TABLE Temp_Word_List (Search_Word
VARCHAR(200)
NOT NULL);
40:WHILE (word_idx = len_search_words) DO
41:IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
42:SET word_length = word_idx - last_pos;
43:SET new_word = TRIM(SUBSTRING(search_words,
last_pos,
word_length));
44:SET last_pos = word_idx;
45:IF (new_word '') THEN
46:INSERT INTO Temp_Word_List VALUES (new_word);
47:END IF;
48:END IF;
49:SET word_idx = word_idx + 1;
50:END WHILE;
51:-- Put in the very last word
52:IF (word_idx last_pos) THEN
53:SET word_length = word_idx - last_pos;
54:SET new_word = TRIM(SUBSTRING(search_words, last_pos,
word_length));
55:IF (new_word '') THEN
56:INSERT INTO Temp_Word_List VALUES (new_word);
57:END IF;
58:END IF;
59:-- Generate the SQL clause for the search words.
60:DECLARE words_list CURSOR FOR SELECT Search_Word FROM
Temp_Word_List;
61:DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
62:OPEN words_list;
63:REPEAT
64:FETCH words_list INTO this_Word;
65:IF (no_more_rows 1) THEN
66:IF (First_Instance = 'Y') THEN
67:SET First_Instance = 'N';
68:ELSE
69:SET Filter_Clause = concat(Filter_Clause,
multi_word_condition, ' ');
70:END IF;
71:SET Filter_Clause = concat(Filter_Clause,
'match(title,description) against(''', this_Word, ''') ');
72:END IF;
73:UNTIL (no_more_rows = 1) END REPEAT;
74:CLOSE words_list;
75:DROP TEMPORARY TABLE Temp_Word_List;
76:END IF;
77:
78:-- Language Id
79:IF (language_id IS NOT NULL AND language_id 0) THEN
80:IF (First_Instance = 'Y') THEN
81:SET First_Instance = 'N';
82:ELSE
83:SET Filter_Clause = concat(Filter_Clause, ' AND ');
84:END IF;
85:SET Filter_Clause = concat(Filter_Clause, 'language = ',
cast(language_id as CHAR(4)), ' ');
86:END IF;
87:
88:-- Sample type
89:IF (sample_type IS NOT NULL AND sample_type 'A' AND
sample_type
'' AND sample_type ' ') THEN
90:IF (First_Instance = 'Y') THEN
91:SET First_Instance = 'N';
92:ELSE
93:SET Filter_Clause = concat(Filter_Clause, ' AND ');
94:END IF;
95:SET Filter_Clause = concat(Filter_Clause, 'sample_type =
''',
sample_type, );
96:END IF;
97:
98:-- Construct the final SQL
Re: Syntax Error in Stored Procedure
Thanks. That fixed the problem. I put all of my DECLARE statements at the
beginning of the procedure. I finally got the thing to compile and it looks
like this:
DELIMITER $$
DROP PROCEDURE IF EXISTS `code_library`.`Search_Code_Samples` $$
CREATE [EMAIL PROTECTED] PROCEDURE `Search_Code_Samples`(
IN search_words VARCHAR(300)
, IN multi_word_condition VARCHAR(3)
, IN language_id INT
, IN sample_type CHAR(1)
)
READS SQL DATA
COMMENT 'Prepares and executes SQL to find records according to the
data'
BEGIN
DECLARE Base_SQL VARCHAR(1500);
DECLARE Filter_Clause VARCHAR(3000);
DECLARE Final_SQL VARCHAR(5500);
DECLARE First_Instance CHAR(1);
DECLARE len_search_words INT;
DECLARE word_idx INT;
DECLARE last_pos INT;
DECLARE word_length INT;
DECLARE new_word varchar(200);
DECLARE Search_Clause VARCHAR(1000);
DECLARE this_Word varchar(200);
DECLARE no_more_rows INT default 0;
DECLARE words_list CURSOR FOR SELECT Search_Word FROM Temp_Word_List;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
-- First handle the list of search words
IF (multi_word_condition IS NULL OR multi_work_condition = '' OR
multi_word_condition = ' ') THEN
SET multi_word_condition = 'AND';
END IF;
IF (sample_type IS NULL) OR (sample_type = ' ') THEN
SET sample_type = 'A';
END IF;
SET Base_SQL = 'select * from CodeSamples_View ';
SET Filter_Clause = '';
SET First_Instance = 'Y';
IF (search_words IS NOT NULL) AND (search_words '') AND (search_words
' ') THEN
-- Parse the list of search words by spaces
SET len_search_words = LENGTH(search_words);
SET word_idx = 1;
SET last_pos = 1;
CREATE TEMPORARY TABLE Temp_Word_List (Search_Word VARCHAR(200) NOT NULL);
WHILE (word_idx = len_search_words) DO
IF (SUBSTRING(search_words, word_idx, 1) = ' ') THEN
SET word_length = word_idx - last_pos;
SET new_word = TRIM(SUBSTRING(search_words, last_pos, word_length));
SET last_pos = word_idx;
IF (new_word '') THEN
INSERT INTO Temp_Word_List VALUES (new_word);
END IF;
END IF;
SET word_idx = word_idx + 1;
END WHILE;
-- Put in the very last word
IF (word_idx last_pos) THEN
SET word_length = word_idx - last_pos;
SET new_word = TRIM(SUBSTRING(search_words, last_pos, word_length));
IF (new_word '') THEN
INSERT INTO Temp_Word_List VALUES (new_word);
END IF;
END IF;
-- Generate the SQL clause for the search words.
OPEN words_list;
REPEAT
FETCH words_list INTO this_Word;
IF (no_more_rows 1) THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, multi_word_condition, ' ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'match(title,description)
against(''', this_Word, ''') ');
END IF;
UNTIL (no_more_rows = 1) END REPEAT;
CLOSE words_list;
DROP TEMPORARY TABLE Temp_Word_List;
END IF;
-- Language Id
IF (language_id IS NOT NULL AND language_id 0) THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, ' AND ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'language = ', cast(language_id
as CHAR(4)), ' ');
END IF;
-- Sample type
IF (sample_type IS NOT NULL AND sample_type 'A' AND sample_type ''
AND sample_type ' ') THEN
IF (First_Instance = 'Y') THEN
SET First_Instance = 'N';
ELSE
SET Filter_Clause = concat(Filter_Clause, ' AND ');
END IF;
SET Filter_Clause = concat(Filter_Clause, 'sample_type = ''', sample_type,
);
END IF;
-- Construct the final SQL statement.
IF (Filter_Clause = '') THEN
SET @Final_SQL = Base_SQL;
ELSE
SET @Final_SQL = concat(Base_SQL, 'WHERE ', Filter_Clause);
END IF;
-- Execute the constructed SQL Statement.
PREPARE search_statement FROM @Final_SQL;
EXECUTE search_statement;
DEALLOCATE PREPARE search_statement;
END $$
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
syntax to revoke
Hi folks, OpenBSD 4.0 x86_64 Mysql Postfix_2.4.3 After running following command; mysql GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'vmailuser'@'localhost' IDENTIFIED BY 'password123'; Query OK, 0 rows affected (0.00 sec) I found I made a mistake to run it. I need to revoke the GRANT given to vmailuser to test database. I found on; 13.5.1.5. REVOKE Syntax http://dev.mysql.com/doc/refman/5.0/en/revoke.html The syntax; REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] but can't resolve whether retaining the 1st 'user' and replace [, user] with [, vmailuser]??? Please shed me some light. TIA B.R. Stephen Liu Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
Hi Stephen, Stephen Liu wrote: Hi folks, OpenBSD 4.0 x86_64 Mysql Postfix_2.4.3 After running following command; mysql GRANT SELECT, INSERT, UPDATE, DELETE ON test.* TO 'vmailuser'@'localhost' IDENTIFIED BY 'password123'; Query OK, 0 rows affected (0.00 sec) I found I made a mistake to run it. I need to revoke the GRANT given to vmailuser to test database. I found on; 13.5.1.5. REVOKE Syntax http://dev.mysql.com/doc/refman/5.0/en/revoke.html The syntax; REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] but can't resolve whether retaining the 1st 'user' and replace [, user] with [, vmailuser]??? To undo this GRANT, run REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
Hi Baron, Tks for your advice. To undo this GRANT, run REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; mysql REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql I suppose it has been done ??? B.R. Stephen Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
Hi, Stephen Liu wrote: Hi Baron, Tks for your advice. To undo this GRANT, run REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; mysql REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql I suppose it has been done ??? Yes, but you can check with SHOW GRANTS FOR 'vmailuser'@'localhost' to be sure. If you are running an older version of MySQL you may also need to run FLUSH PRIVELEGES. Check the manual for the versions where this is necessary. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
Hi Baron, I suppose it has been done ??? Yes, but you can check with SHOW GRANTS FOR 'vmailuser'@'localhost' to be sure. mysql SHOW GRANTS FOR 'vmailuser'@'localhost'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON *.* TO 'vmailuser'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' | +--+ 1 row in set (0.00 sec) * end * If you are running an older version of MySQL you may also need to run FLUSH PRIVELEGES. Check the manual for the versions where this is necessary. I'm running Mysql version; $ mysql --version mysql Ver 14.12 Distrib 5.0.24a, for unknown-openbsd4.0 (x86_64) using readline 4.3 To safe guard, it would be better to run 'FLUSH PRIVELEGES'. Can I re-run REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; then FLUSH PRIVELEGES; ??? Tks. B.R. Stephen Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
Stephen Liu wrote: mysql SHOW GRANTS FOR 'vmailuser'@'localhost'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON *.* TO 'vmailuser'@'localhost' IDENTIFIED BY PASSWORD '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B' | +--+ OK, the privileges are gone. USAGE is a synonym for no privileges. If you want to get rid of the user entirely, use DROP USER. To safe guard, it would be better to run 'FLUSH PRIVELEGES'. Can I re-run REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; then FLUSH PRIVELEGES; Yes. I think on this recent a version, it will have no effect, but will not harm anything. Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
--- Baron Schwartz [EMAIL PROTECTED] wrote: OK, the privileges are gone. USAGE is a synonym for no privileges. Noted with tks. If you want to get rid of the user entirely, use DROP USER. Could you please explain in more detail??? Where shall I add DROP USER To safe guard, it would be better to run 'FLUSH PRIVELEGES'. Can I re-run REVOKE SELECT, INSERT, UPDATE, DELETE ON test.* FROM 'vmailuser'@'localhost'; then FLUSH PRIVELEGES; Yes. I think on this recent a version, it will have no effect, but will not harm anything. Noted with tks. B.R. Stephen Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
Stephen Liu wrote: If you want to get rid of the user entirely, use DROP USER. Could you please explain in more detail??? Where shall I add DROP USER The manual always explains the full syntax (http://dev.mysql.com/), but in brief, DROP USER 'vmailuser'@'localhost'; will remove the user if you wish. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: syntax to revoke
--- Baron Schwartz [EMAIL PROTECTED] wrote: Stephen Liu wrote: If you want to get rid of the user entirely, use DROP USER. Could you please explain in more detail??? Where shall I add DROP USER The manual always explains the full syntax (http://dev.mysql.com/), Whether you meant; MySQL 5.0 Reference Manual http://dev.mysql.com/doc/refman/5.0/en/index.html Chapter 13. SQL Statement Syntax http://dev.mysql.com/doc/refman/5.0/en/sql-syntax.html ??? Tks but in brief, DROP USER 'vmailuser'@'localhost'; will remove the user if you wish. Noted with tks. Stephen Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax error
Hello again. I am rather (actually very) rusty when it comes to composing SQL these days. Can anyone spot the error here? SELECT o.orderid, u.username, o.date, sum( p.price ) FROM order o, users u, order_item oi, product p WHERE o.userid = u.id AND o.orderid = oi.orderid AND oi.productid = p.productid AND o.status = 'new' ORDER BY o.date DESC , o.status, u.username GROUP BY o.orderid LIMIT 0 , 30 MySQL said: #1064 - 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 'GROUP BY o.orderid LIMIT 0, 30' at line 5 I've test it without the SUM() and GROUP BY so I know its my aggregate function that is doing it. Like I said - pretty rusty. -- View this message in context: http://www.nabble.com/Syntax-error-tf2938979.html#a8216800 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
