Re: New error to me...
At 6:53 PM -0700 7/14/07, Steffan A. Cline wrote: I was trying to reduce a set of queries and ran into this: insert into forums (forum_reply_id, forum_dev_id, forum_subject, forum_message) values (0, 1, (select forum_subject from forums where forum_id=3 ), "I figured this one needed a reply too.") yields: error: You can't specify target table from 'forums' for update in from clause I read somewhere online when they referenced 4.x that said you cannot do subqueries in an update. Is this true of 5.x ? You can do subqueries *unless* you're selecting from the same table that you're inserting into. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html (You're not quite using INSERT INTO ... SELECT syntax, but what you're doing appears to be effectively equivalent.) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New error to me...
Mogens , forum_id is a primary auto increment key therefore it's unlikely that there would be a dupe. I can see there being an error if there is a dupe but in this case there is not. Is it really necessary to do this: set @x = (select forum_subject from forums where forum_id=3 ); insert into forums (project_id, forum_reply_id, forum_dev_id, forum_subject, forum_message) values (42, 6, 1, @x, "I figured this one needed a reply too."); ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- > From: Mogens Melander <[EMAIL PROTECTED]> > Date: Sun, 15 Jul 2007 04:02:18 +0200 (CEST) > To: "Steffan A. Cline" <[EMAIL PROTECTED]> > Cc: > Subject: Re: New error to me... > > > On Sun, July 15, 2007 03:53, Steffan A. Cline wrote: >> I was trying to reduce a set of queries and ran into this: >> >> >> insert into forums (forum_reply_id, forum_dev_id, forum_subject, >> forum_message) values (0, 1, (select forum_subject from forums where >> forum_id=3 ), "I figured this one needed a reply too.") > > Your subselect "could" return more than one row, and because of that, > can't be used in a direct insert/update. > >> >> yields: >> >> error: You can't specify target table from 'forums' for update in from >> clause >> >> I read somewhere online when they referenced 4.x that said you cannot do >> subqueries in an update. Is this true of 5.x ? >> >> Thanks >> >> Steffan >> >> --- >> T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 >> Steffan A. Cline >> [EMAIL PROTECTED] Phoenix, Az >> http://www.ExecuChoice.net USA >> AIM : SteffanC ICQ : 57234309 >> Lasso Partner Alliance Member >> --- >> >> >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> >> -- >> This message has been scanned for viruses and >> dangerous content by MailScanner, and is >> believed to be clean. >> > > > -- > Later > > Mogens Melander > +45 40 85 71 38 > +66 870 133 224 > > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > > > -- > 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: New error to me...
On Sun, July 15, 2007 03:53, Steffan A. Cline wrote: > I was trying to reduce a set of queries and ran into this: > > > insert into forums (forum_reply_id, forum_dev_id, forum_subject, > forum_message) values (0, 1, (select forum_subject from forums where > forum_id=3 ), "I figured this one needed a reply too.") Your subselect "could" return more than one row, and because of that, can't be used in a direct insert/update. > > yields: > > error: You can't specify target table from 'forums' for update in from > clause > > I read somewhere online when they referenced 4.x that said you cannot do > subqueries in an update. Is this true of 5.x ? > > Thanks > > Steffan > > --- > T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 > Steffan A. Cline > [EMAIL PROTECTED] Phoenix, Az > http://www.ExecuChoice.net USA > AIM : SteffanC ICQ : 57234309 > Lasso Partner Alliance Member > --- > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > This message has been scanned for viruses and > dangerous content by MailScanner, and is > believed to be clean. > -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New error to me...
I was trying to reduce a set of queries and ran into this: insert into forums (forum_reply_id, forum_dev_id, forum_subject, forum_message) values (0, 1, (select forum_subject from forums where forum_id=3 ), "I figured this one needed a reply too.") yields: error: You can't specify target table from 'forums' for update in from clause I read somewhere online when they referenced 4.x that said you cannot do subqueries in an update. Is this true of 5.x ? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hanging Database
Hi, First up, the sites were working fine until the old server went buggy. I hadn't worked on either of the shopping carts, so they should have been fine when I transfered them over. No, I'm confident there is nothing wrong with my code, etc. I suspect it has something to do with copying over the database, which was strange. Certain databases didn't copy over for some reason, and I had to install them manually. Plus, the permissions database (whichever that is, forgive my ignorance) didn't copy and I just recreated it (that is, created the correct user gave the permissions to the proper databases). Now, here's the output from mytop: 2 mysqluser localhost mrtableclo 0 Sleep 14 root localhost test 0 Query show full processlist 4 mysqluser localhost mrtableclo 6154 Sleep 3 mysqluser localhost mrtableclo 9210 Sleep So, apparently it's asleep. How do I wake it up ;) 'Sleep' means the connection is idle, waiting for a query. You have a connection that has been idle, waiting for a query, for 9210 seconds -- that's about 2:30:00. Is your website running a query and then getting into an infinite loop on the result, or do you expect a connection to be held open this long due to connection pooling or for some other reason? If your application closes its connections after rendering the page, you shouldn't be seeing that unless there's a connection pool. You also have a connection that just finished a query and has been idle for 0 seconds. That may or may not mean anything... you know the code. But it doesn't look like anything is wrong with MySQL, so you're probably on the wrong list and need to address it with Zope or whoever else is responsible (sorry, I don't know much about the technologies you're using). Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hanging Database
First up, the sites were working fine until the old server went buggy. I hadn't worked on either of the shopping carts, so they should have been fine when I transfered them over. No, I'm confident there is nothing wrong with my code, etc. I suspect it has something to do with copying over the database, which was strange. Certain databases didn't copy over for some reason, and I had to install them manually. Plus, the permissions database (whichever that is, forgive my ignorance) didn't copy and I just recreated it (that is, created the correct user gave the permissions to the proper databases). Now, here's the output from mytop: 2 mysqluser localhost mrtableclo 0 Sleep 14 root localhost test 0 Query show full processlist 4 mysqluser localhost mrtableclo 6154 Sleep 3 mysqluser localhost mrtableclo 9210 Sleep So, apparently it's asleep. How do I wake it up ;) TIA, Pat Baron Schwartz <[EMAIL PROTECTED]> wrote: Hi, Pat Singer wrote: > Hi; > I just built out a new server and transfered my databases. I use Zope to > server my web sites and ZMySQLDA to interface between the two. Everything > seems to be fine. I can see the databases, the tables, the columns in the > tables, the permissions are correct, etc. I hook up ZMySQLDA and that seems > to be fine. It connects to the database. However, when I go to look at the > pages from served the database, the browser hangs. Any ideas how to > troubleshoot this? Is it really the database that's hanging? Use innotop or mytop to see if a query is stuck. Maybe the problem is in your code or on the web server, not the database. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.
Re: Hanging Database
Hi, Pat Singer wrote: Hi; I just built out a new server and transfered my databases. I use Zope to server my web sites and ZMySQLDA to interface between the two. Everything seems to be fine. I can see the databases, the tables, the columns in the tables, the permissions are correct, etc. I hook up ZMySQLDA and that seems to be fine. It connects to the database. However, when I go to look at the pages from served the database, the browser hangs. Any ideas how to troubleshoot this? Is it really the database that's hanging? Use innotop or mytop to see if a query is stuck. Maybe the problem is in your code or on the web server, not the database. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hanging Database
Hi; I just built out a new server and transfered my databases. I use Zope to server my web sites and ZMySQLDA to interface between the two. Everything seems to be fine. I can see the databases, the tables, the columns in the tables, the permissions are correct, etc. I hook up ZMySQLDA and that seems to be fine. It connects to the database. However, when I go to look at the pages from served the database, the browser hangs. Any ideas how to troubleshoot this? TIA, Pat - Shape Yahoo! in your own image. Join our Network Research Panel today!
Re: command line questions
At 01:38 AM 7/14/2007, Olav Mørkrid wrote: hi, i have some questions about the command line of mysql for WIN32: - does mysql have any macro system that lets you shorten often-used commands, like expanding "n5 mytable" into "select * from mytable order by id desc limit 5" - how can tab completion be enabled (it does not work by default, even though auto-rehash lists as "true"). - is there any way to make mysql remember command history between each time the command line window is opened and closed? - say you have a table with 8 columns, where one of them is a long text, then each row runs over multiple lines making the table unreable. to get a nice display, you have to type "select c1, c2, c3, c4, c5, c6, c7" which is boring to write. so is there any way to say "select [all except that wide column] from mytable"? or does mysql possibly an intelligent "fit table to screen width" function (like html tables)? thanks! Try getting a GUI like SqlYog http://www.webyog.com/en/ or MySQL Mgr Lite from EMS http://www.sqlmanager.net/products/mysql/manager. They both work on Windows are are free. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: command line questions
As fat as your first and last question go you can create views. create view somename as select c1,c2,c2 from table (limit 5); and then select from somename; i am not familiar with the windows client but i would expect auto completion there too olaf -Original Message- From: Olav Mørkrid [mailto:[EMAIL PROTECTED] Sent: Sat 7/14/2007 3:38 AM To: mysql@lists.mysql.com Subject: command line questions hi, i have some questions about the command line of mysql for WIN32: - does mysql have any macro system that lets you shorten often-used commands, like expanding "n5 mytable" into "select * from mytable order by id desc limit 5" - how can tab completion be enabled (it does not work by default, even though auto-rehash lists as "true"). - is there any way to make mysql remember command history between each time the command line window is opened and closed? - say you have a table with 8 columns, where one of them is a long text, then each row runs over multiple lines making the table unreable. to get a nice display, you have to type "select c1, c2, c3, c4, c5, c6, c7" which is boring to write. so is there any way to say "select [all except that wide column] from mytable"? or does mysql possibly an intelligent "fit table to screen width" function (like html tables)? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
command line questions
hi, i have some questions about the command line of mysql for WIN32: - does mysql have any macro system that lets you shorten often-used commands, like expanding "n5 mytable" into "select * from mytable order by id desc limit 5" - how can tab completion be enabled (it does not work by default, even though auto-rehash lists as "true"). - is there any way to make mysql remember command history between each time the command line window is opened and closed? - say you have a table with 8 columns, where one of them is a long text, then each row runs over multiple lines making the table unreable. to get a nice display, you have to type "select c1, c2, c3, c4, c5, c6, c7" which is boring to write. so is there any way to say "select [all except that wide column] from mytable"? or does mysql possibly an intelligent "fit table to screen width" function (like html tables)? thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
