Advanced Query Error Parsing
Hello All, I was recently approached by someone looking for better error parsing in MySQL. He does queries on large sets of data for research using MySQL, and the vagueness of the you have an error before ; messages often returned leave him desiring more. My goal is to develop some kind of advanced error parsing through MySQL Proxy. My question is: has anyone heard of anything remotely similar to this before? I've been searching for several days and am surprised to find no mention of the quality of MySQL's error messages. I don't want to go reinventing the wheel, so I figured I'd check to see if there is already progress on this front. Bonus points if anyone can tell me if there is a simple syntax summary for MySQL, it would make life easier as opposed to pulling all the pseudo-BNF from the individual manual pages (I know this probably belongs on the doc list, but I figured since it relates to the above question I'd ask it here first). Thanks in advance for the help! --Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query error
The comma at the end of the SELECT statement needs to be removed Naz Gassiep wrote: Hi, I'm trying to execute this query: SELECT group_post.group_thread_id, FROM group_post LEFT OUTER JOIN group_post_moderation ON (group_post.group_post_id = group_post_moderation.group_post_id) LEFT OUTER JOIN group_post_mod_option ON (group_post_moderation.group_post_moderation_option = group_post_mod_option.option_id) WHERE group_thread_id = '6' GROUP BY group_post.group_thread_id ORDER BY lft; But when I do, I get this error: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right sy ntax to use near 'FROM group_post LEFT OUTER JOIN group_post_moderation ON (grou Can anyone please tell me what is causing that? I'm using MySQL4. Thanks, - Naz.
Query error
Hi, I'm trying to execute this query: SELECT group_post.group_thread_id, FROM group_post LEFT OUTER JOIN group_post_moderation ON (group_post.group_post_id = group_post_moderation.group_post_id) LEFT OUTER JOIN group_post_mod_option ON (group_post_moderation.group_post_moderation_option = group_post_mod_option.option_id) WHERE group_thread_id = '6' GROUP BY group_post.group_thread_id ORDER BY lft; But when I do, I get this error: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right sy ntax to use near 'FROM group_post LEFT OUTER JOIN group_post_moderation ON (grou Can anyone please tell me what is causing that? I'm using MySQL4. Thanks, - Naz. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by query error syntax
Mike Blezien wrote: Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #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 '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Something is strange here. The piece of your query quoted in the syntax error does not match the query you gave us. That makes me think you've given us an edited version of your query. It's hard to catch a syntax error if you don't give us the actual query. The piece of the query quoted in the error has a lot of extraneous spaces. If I had to guess, I'd bet that there is a space between SEC_TO_TIME and the opening parenthesis in your real query. That is, you have SEC_TO_TIME ( SUM... instead of SEC_TO_TIME(SUM... The parser distinguishes functions from columns by the presence of a parenthesis attached to the function name. For example: mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.15| +---+ 1 row in set (0.00 sec) but mysql SELECT VERSION (); 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 '()' at line 1 Note that the error message quotes the query starting with the opening parenthesis, as is the case for you. If that isn't it, please copy and paste your actual query into your next message. I'm sure someone will spot the problem. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by query error syntax
sheeri kritzer wrote: snip MySQL usually gives a syntax error *where* the error happens. In this case, it would indicate a problem with SEC_TO_TIME( but there shouldn't be a problem, both according to the manual AND according to my example. The parser reads the query left-to-right and always quotes the first thing it doesn't understand. As often as not, that's the first thing *after* the actual error. Here's a simple example: SELECT version (); SELECT is proper, of course. Next comes version. It doesn't have a parenthesis attached, so it must be a column name. Since version is a column, it should be followed by a comma, an alias, the word AS, or some operator. In that context, the ( that comes next doesn't make sense, so that is what mysql tells you: mysql SELECT VERSION (); 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 '()' at line 1 The actual error, though, is the space right before the quoted part of the query. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Baffled by query error syntax
Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #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 '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Again, any help would be much appreciated. Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by query error syntax
Mike, I can't really help except to ask if you're sure you copied and pasted the query correctly. I did a similar query against a test system: select u.uid,u.username,b.buddyUid,SEC_TO_TIME(SUM(TIME_TO_SEC(u.modified))) as mins from Users u left join BuddyList b on u.uid = b.uid where u.modified = DATE_SUB(NOW(),INTERVAL 14 DAY) and country=au group by u.uid having mins = '2' order by mins; Similar joins, similar where clause, etc and yet I got an answer (almost 700 rows, took 2 seconds) while you got a syntax error. select @@version; +-+ | @@VERSION | +-+ | 4.1.12-standard-log | +-+ So I'm not sure what to recommend other than trying the query again to make sure there aren't typos. MySQL usually gives a syntax error *where* the error happens. In this case, it would indicate a problem with SEC_TO_TIME( but there shouldn't be a problem, both according to the manual AND according to my example. I would prepare for a bug report -- create 2 new tables in the test db, in this case you don't need a lot of test data, do the join, and if you still get the problem, submit a bug report (you've just done the steps to recreate part). Many times I've done this and realized where my bug was because the query worked in the test table. -Sheeri On 5/17/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #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 '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Again, any help would be much appreciated. Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by query error syntax
Hi Sheeri, Is your 'u.modified' column a TIME datatype '00:00:00' Mike - Original Message - From: sheeri kritzer [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Wednesday, May 17, 2006 9:10 AM Subject: Re: Baffled by query error syntax Mike, I can't really help except to ask if you're sure you copied and pasted the query correctly. I did a similar query against a test system: select u.uid,u.username,b.buddyUid,SEC_TO_TIME(SUM(TIME_TO_SEC(u.modified))) as mins from Users u left join BuddyList b on u.uid = b.uid where u.modified = DATE_SUB(NOW(),INTERVAL 14 DAY) and country=au group by u.uid having mins = '2' order by mins; Similar joins, similar where clause, etc and yet I got an answer (almost 700 rows, took 2 seconds) while you got a syntax error. select @@version; +-+ | @@VERSION | +-+ | 4.1.12-standard-log | +-+ So I'm not sure what to recommend other than trying the query again to make sure there aren't typos. MySQL usually gives a syntax error *where* the error happens. In this case, it would indicate a problem with SEC_TO_TIME( but there shouldn't be a problem, both according to the manual AND according to my example. I would prepare for a bug report -- create 2 new tables in the test db, in this case you don't need a lot of test data, do the join, and if you still get the problem, submit a bug report (you've just done the steps to recreate part). Many times I've done this and realized where my bug was because the query worked in the test table. -Sheeri On 5/17/06, Mike Blezien [EMAIL PROTECTED] wrote: Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #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 '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Again, any help would be much appreciated. Mike(mickalo)Blezien === Thunder Rain Internet Publishing Providing Internet Solution that Work http://www.thunder-rain.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Error Help
Hello, I have this query: SELECT FL3_PatientControlNumber, claims.RecordKey as reckey, FL1_ProviderName, CalcFlag FROM claims INNER JOIN service_line ON service_line.ClaimKey = claims.RecordKey WHERE ( service_line.FL42_ServiceLineRevCode BETWEEN LPAD('110',4,'0') AND LPAD('210',4,'0' ) ) ORDER BY FL3_PatientControlNumber which returns this error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'between' and am confused as to what's going on with this? Rob
RE: Query Error Help
nm ... I found the problem ... I need to use BINARY(LPAD ...) -Original Message- From: Rob Brooks [mailto:[EMAIL PROTECTED] Sent: Monday, December 19, 2005 10:04 AM To: mysql@lists.mysql.com Subject: Query Error Help Hello, I have this query: SELECT FL3_PatientControlNumber, claims.RecordKey as reckey, FL1_ProviderName, CalcFlag FROM claims INNER JOIN service_line ON service_line.ClaimKey = claims.RecordKey WHERE ( service_line.FL42_ServiceLineRevCode BETWEEN LPAD('110',4,'0') AND LPAD('210',4,'0' ) ) ORDER BY FL3_PatientControlNumber which returns this error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation 'between' and am confused as to what's going on with this? Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie Query: Error starting MySQL.
Hello. Start points for you problem: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html http://dev.mysql.com/doc/refman/5.0/en/starting-server.html socket=/home.dbdata/mysql/mysql.sock What is your client thinks about the location of mysqld socket? Put the same line in [client] section of my.cnf. Sanjay Arora wrote: Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf --- [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running though it reports failure instead of OK when I start it. Also when I stop myswl from commandline, it stops and shows OK, but cannot also connect to it. Checked the pid file mentioned in the my.cnf and it gets created and deleted when mysql starts stops respectively. Please advise what is happening and what step I have taken wrong and what to do to correct it. Am a newbie but can follow instructions. Please help. With best regards. Sanjay. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Query: Error starting MySQL.
Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf --- [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running though it reports failure instead of OK when I start it. Also when I stop myswl from commandline, it stops and shows OK, but cannot also connect to it. Checked the pid file mentioned in the my.cnf and it gets created and deleted when mysql starts stops respectively. Please advise what is happening and what step I have taken wrong and what to do to correct it. Am a newbie but can follow instructions. Please help. With best regards. Sanjay. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Query: Error starting MySQL..changed Data Directory
Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pidmysql 6184 0.2 3.4 125808 17636 pts/0 Sl 15:47 0:01 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usrroot 6336 0.0 0.1 2920 744 pts/0R+ 15:57 0:00 ps -aux Log shows 051114 14:51:07 mysqld started 051114 14:51:08 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution 051114 15:47:06 [Note] /usr/libexec/mysqld: Normal shutdown 051114 15:47:06 InnoDB: Starting shutdown... 051114 15:47:09 InnoDB: Shutdown completed; log sequence number 0 43634 051114 15:47:09 [Note] /usr/libexec/mysqld: Shutdown complete 051114 15:47:09 mysqld ended 051114 15:47:33 mysqld started 051114 15:47:33 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution Please advise what I have done wrong. I suspect permissions /or incorrect data directory migration. Please help. With regards. Sanjay. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie Query: Error starting MySQL..changed Data Directory
Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT This doesn't nesessarily mean that mysql hasn't started. This script waits only for certain time to check if mysql has started or not. If it is not started in that time, it simply says 'mysql start failed'. But mysql may take some more time to come up. So the best practice is to have a look at the logs (as you rightly did) to find out what exactly is going on. And your logs anyway are saying /usr/libexec/mysqld: ready for connections.. This means that mysql has started without any problems and waiting for connections. ps -aux shows that mysql is running And for the same reason I mentioned above, you are seeing this. Hope this helps. sujay -Original Message- From: Sanjay Arora [mailto:[EMAIL PROTECTED] Sent: Monday, November 14, 2005 4:46 PM To: MySql Mailing List Subject: Newbie Query: Error starting MySQL..changed Data Directory Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pidmysql 6184 0.2 3.4 125808 17636 pts/0 Sl 15:47 0:01 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usrroot 6336 0.0 0.1 2920 744 pts/0R+ 15:57 0:00 ps -aux Log shows 051114 14:51:07 mysqld started 051114 14:51:08 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution 051114 15:47:06 [Note] /usr/libexec/mysqld: Normal shutdown 051114 15:47:06 InnoDB: Starting shutdown... 051114 15:47:09 InnoDB: Shutdown completed; log sequence number 0 43634 051114 15:47:09 [Note] /usr/libexec/mysqld: Shutdown complete 051114 15:47:09 mysqld ended 051114 15:47:33 mysqld started 051114 15:47:33 InnoDB: Started; log sequence number 0 43634 /usr/libexec/mysqld: ready for connections. Version: '4.1.12' socket: '/home.dbdata/mysql/mysql.sock' port: 3306 Source distribution Please advise what I have done wrong. I suspect permissions /or incorrect data directory migration. Please help. With regards. Sanjay. -- 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]
Query Error Log
Is there any way to find log of queries that returned errors. Syntax errors or column not found, for example? The General Log simply logs the query without giving any information as to whether the query was successful or not Thank you in advance. Best regards, TS
Query error need help please
Hello. I have a subroutine which checks for multiple entries and if a user has voted once in the same day, it will not calculate the vote. However, I found out that it does not really do this. What it does is only look at the last entry. If the user IP address is the last entry it does not calculate the vote, but if it is not, it goes ahead and let the user vote. So, basically it only looks at the very last entry. Can someone please tell me what needs to be done to fix this? Thanks so much. if(isset($_POST['submit_rating']) isset($_POST['user_id']) ($_POST['submit_rating'] = 3 $_POST['submit_rating'] = 10)){ $user_id = (int) $_POST['user_id']; if(isset($_SESSION['ra'])){ $_SESSION['ra'] .= $user_id . ,; } else { $_SESSION['ra'] = $user_id . ,; } $rating = (int) $_POST['submit_rating']; $rater_id = isset($_SESSION['userid']) ? $_SESSION['userid'] : 0; $check_ip_sql = select * from $tb_ratings where user_id = '$user_id' order by timestamp desc ; $check_ip_query = mysql_query($check_ip_sql) or die(mysql_error()); $last_rater_ip = @mysql_result($check_ip_query, 0, rater_ip); $last_rater_id = @mysql_result($check_ip_query, 0, rater_id); $last_rated = @mysql_result($check_ip_query, 0, timestamp); $yesterday = date(YmdHis, mktime(date(H), date(i), date(s), date(m), date(d)-10, date(Y))); $same_ip = false; $too_soon = false; $same_user = false; if($last_rater_ip == $HTTP_SERVER_VARS['REMOTE_ADDR']) $same_ip = true; if($last_rated $yesterday) $too_soon = true; if($user_id == $rater_id) $same_user = true; if(!$same_user (!$same_ip || !$too_soon)){ $rating_accepted = true; $is_sql = insert into $tb_ratings ( id, user_id, rating, rater_id, rater_ip ) values ( '', '$user_id', '$rating', '$rater_id', '$_SERVER[REMOTE_ADDR]' ) ; $is_query = mysql_query($is_sql) or die(mysql_error()); $gs_sql = select total_ratings, total_points, average_rating from $tb_users where id = '$user_id' ; $gs_query = mysql_query($gs_sql) or die(mysql_error()); $total_ratings = mysql_result($gs_query, 0, total_ratings); $total_points = mysql_result($gs_query, 0, total_points); $total_ratings++; $total_points += $rating; $average_rating = $total_points / $total_ratings; $ps_sql = update $tb_users set total_ratings = '$total_ratings', total_points = '$total_points', average_rating = '$average_rating' where id = '$user_id' ; $ps_query = mysql_query($ps_sql) or die(mysql_error()); } } -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query error need help please
On Tue, 14 Sep 2004 19:55:42 -0700, Soheil Shaghaghi [EMAIL PROTECTED] wrote: I have a subroutine which checks for multiple entries and if a user has voted once in the same day, it will not calculate the vote. However, I found out that it does not really do this. What it does is only look at the last entry. If the user IP address is the last entry it does not calculate the vote, but if it is not, it goes ahead and let the user vote. So, basically it only looks at the very last entry. Can someone please tell me what needs to be done to fix this? Thanks so much. This is fixed in the latest version: http://sourceforge.net/projects/destiney/ If not, let me know and I'll look into it. I'm still maintaining the 0.3 series. -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Query error in Access
Ed The MS Access SQL syntax for if() is iif(condition, then stuff, else stuff) Maybe that's the problem, I am not sure - try it Freddie -Ursprüngliche Nachricht- Von: Ed Reed [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. Februar 2004 02:09 An: [EMAIL PROTECTED] Betreff: Query error in Access Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, Add New Part AS PartNumber, AS VendorPartNo, AS Description, AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, Primary,Alternate) AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC; If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: Query error in Access
I mentioned that at the end of my original message. I've tried it both ways and it doesn't solve the problem. The query is good. For some reason though Access or MySQL is removing the IF statements in the middle of it. What next? Freddie Sorensen [EMAIL PROTECTED] 2/26/04 12:25:03 PM Ed The MS Access SQL syntax for if() is iif(condition, then stuff, else stuff) Maybe that's the problem, I am not sure - try it Freddie -Ursprüngliche Nachricht- Von: Ed Reed [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. Februar 2004 02:09 An: [EMAIL PROTECTED] Betreff: Query error in Access Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, Add New Part AS PartNumber, AS VendorPartNo, AS Description, AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, Primary,Alternate) AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC; If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Query error in Access
Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, Add New Part AS PartNumber, AS VendorPartNo, AS Description, AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, Primary,Alternate) AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC; If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts?
MySQL sub-query error.
Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL sub-query error.
I don't think Sub queries like the = and != operators as groupid will never equal the result of a multi row subquery, but it can be IN a list of results. Just to check, you are using 4.1+ right? I think this alteration would work: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id IN (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id NOT IN (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = That being said, I think you can do it without Subqueries with this query: SELECT group_id, name, IF(1=userid,'SELECTED',' ') FROM groups ORDER BY group_id Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:03 AM To: [EMAIL PROTECTED] Subject: MySQL sub-query error. Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL sub-query error.
If it's below 4.0 then you can't use subqueries at all, subqueries were (will be) introduced in 4.1. That query is just a standard query, but the third item will return the value of 'SELECTED' if the userid is equal to 1 and ' ' otherwise. Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 10:00 AM To: Chris Subject: Re: MySQL sub-query error. See..I changed it = only coz the IN wouldn't work. I am guessing its a MySQL version issue. I am using a version below 4.0. I am curious about the second version of my query you wrote ? Can you elaborate ? Chris wrote: I don't think Sub queries like the = and != operators as groupid will never equal the result of a multi row subquery, but it can be IN a list of results. Just to check, you are using 4.1+ right? I think this alteration would work: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id IN (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id NOT IN (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = That being said, I think you can do it without Subqueries with this query: SELECT group_id, name, IF(1=userid,'SELECTED',' ') FROM groups ORDER BY group_id Chris -Original Message- From: Geeta Rajaraman [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 05, 2003 7:03 AM To: [EMAIL PROTECTED] Subject: MySQL sub-query error. Good Morning everyone: I need help figuring out what is wrong with this query. I have tried to use the LEFT JOIN, but it doesn't solve the purpose. This is what I am trying to run: SELECT group_id,name,'SELECTED' FROM groups WHERE group_id = (SELECT group_id FROM user_groups WHERE userid= 1) UNION Select group_id,name,' ' FROM groups WHERE group_id != (SELECT group_id FROM user_groups WHERE userid= 1) ORDER by group_id = I get this error: You have an error in your SQL syntax near 'SELECT group_id FROM user_groups WHERE userid= 1) union select group_id,name I have run the sub queries individually, and they work. Can anyone help ? I have tried various combinations..but each time I get the same error. Thanks! -- 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]
Query Error
Pls help me guys. what's wrong with this command ? select a.siswa_id,a.nama,b.Nilai from Query_DataSiswa a left join ( select siswa_id,Nilai,Tes_Id from Nilai_Harian where tes_id = 1 and pertemuan = 1 and paket_id = 1 and tingkat = 1) b on a.siswa_id = b.siswa_id where a.paket_id = 1 and a.Tingkat = 1 order by a.siswa_id I've got error if I execute it. pls check the mysql version for this command ? thanks - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: Query Error
Timotius Alfa wrote: Pls help me guys. what's wrong with this command ? select a.siswa_id,a.nama,b.Nilai from Query_DataSiswa a left join ( select siswa_id,Nilai,Tes_Id from Nilai_Harian where tes_id = 1 and pertemuan = 1 and paket_id = 1 and tingkat = 1) b on a.siswa_id = b.siswa_id where a.paket_id = 1 and a.Tingkat = 1 order by a.siswa_id I've got error if I execute it. pls check the mysql version for this command ? thanks Subquery. You need to be using version 4.1 at a minimum. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2013: Lost connection to MySQL server during query ERROR 1129: Host 'webfrontend' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'
Hi! I've look a bit around google for theese errors - but i can't find any useful answers to my problem. My setup is; web frontend running apache/php for dynamic page creation (p4 - 2,4 Ghz - 1Gb - ide) sql backend running mysql 4.0.12-log (p3 - 1 Ghz - ide) both machines running debian gnu/linux 2.4 linux kernels - alle software installed from debian packages. the web frontend are serving 1,4 mill dynamic pages / day (access file recording) the sql server says: Connection: majeskuel via TCP/IP Uptime: 3 hours 53 min 36 sec Threads: 226 Questions: 1616956 Slow queries: 79 Opens: 467 Flush tables: 1 Open tables: 461 Queries per second avg: 115.365 Essentials from my.conf; set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= long_query_time=2 set-variable= max_connections=525 set-variable= thread_cache=8 set-variable= thread_concurrency=2 set-variable= myisam_sort_buffer_size=64M set-variable= query_cache_size=64M My Problem is... I restarted my mysql server last nite - and suddenly this afternoon my error reporting system flushed me with theese 2 errors happining at connect time: 2013: Lost connection to MySQL server during query 1129: Host 'webfrontend' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' (both php scripts and some cron job are sending theese errors) it's coming in periods of cuple of minutes up til 14 minutes.. It stops by it self sometimes and other times i've tried with shutting down the services and started them agian. (and flush-hosts also stop it) ...and... I've checked for code changes in the web application. Somebody says its tcp/ip stack problem - but no packets are dropped on the ethernenet layer. Nothing in the mysql err log either.. *looking kinda desperate ;)* Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query error log
Hello. How can I recover query errors done on a mysql server? Is there a log file? It's very important to me to know about this. Thanx so much!!! Lorenzo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query error
In the last episode (Nov 21), ck nuke said: I have a problem about : mysql query I need help for this. Sever: mysql Version: 4.0.4-bata SELECT p.pid, p.dateline, t.tid, t.replies FROM cdb_threads t, cdb_posts p WHERE p.tid=t.tid AND t.fid='24' ORDER BY p.dateline DESC LIMIT 0,1 When use this command, no result --- SELECT p.pid, p.dateline, t.tid, t.replies FROM cdb_threads t, cdb_posts p WHERE p.tid=t.tid AND t.fid='24' ORDER BY p.pid DESC LIMIT 0,1 When use this command, have one result --- SELECT pid FROM cdb_posts ORDER BY dateline DESC When use this command, have results --- Don't know where is the mistake??? Upgrade to 4.0.5. From the changelog: * Fixed a newly introduced bug that caused 'ORDER BY ... LIMIT #' to not return all rows. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error updating slave list: Query error / replication on 4.0.4-beta
Hi. I'm setting up a slave for a 4.0.4-beta mysql server. The first time it worked perfectly, doing what is written here: http://www.mysql.com/doc/en/Replication_HOWTO.html But I am trying to do it again and I receive this error: 021117 23:10:31 mysqld started /mysql/libexec/mysqld: ready for connections 021117 23:10:31 Slave I/O thread: connected to master 'repl@director:3306', replication started in log 'FIRST' at position 4 021117 23:10:31 Error updating slave list: Query error 021117 23:10:31 Slave I/O thread exiting, read up to log 'FIRST', position 4 Would you please tell me what this error means? What should I read/try? I'm using a fresh snapshot and I've deleted the binary logs... I have searched the web but I think this does not happen often. Thanks, Andres.- _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select query error
Hi All, I have a table in mysql as Attributes, which has 10 recordsin it. If I run the query as select * from Attributes, it gives me 10 records, but if i run the query as Select Attribute from Attributes it gives me 9 records. But this error does not occur every time. It comes very rarely. I am using mysql 3.23.49. Please let of what could be wrong. Regards Amit Lonkar __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Error
Hello gurus, Please help. Where is my mistake? I get the following error... MYSQL: query failed: You have an error in your SQL syntax near ') rei_ts' at line 1 When I run the query below SELECT rei_ip \ FROM relay_ip \ WHERE rei_ip=${sender_host_address} AND DATE_SUB(NOW(), INTERVAL 10) rei_ts And the tabledesign is ... CREATE TABLE relay_ip ( rei_aid int(11) NOT NULL auto_increment, rei_uname varchar(30) NOT NULL default '', rei_domain varchar(128) NOT NULL default '', rei_ip varchar(64) NOT NULL default '', rei_ts datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (rei_aid), KEY rei_ip (rei_ip) ) ; -Wash -- Odhiambo Washington [EMAIL PROTECTED] The box said 'Requires Wananchi Online Ltd. www.wananchi.com Windows 95, NT, or better,' Tel: +254 2 313985-9 +254 2 313922 so I installed FreeBSD. GSM: +254 72 743223 +254 733 744121 This sig is McQ! :-) If the King's English was good enough for Jesus, it's good enough for me! -- Ma Ferguson, Governor of Texas (circa 1920) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Error
Odhiambo Washington wrote: Hello gurus, Please help. Where is my mistake? I get the following error... MYSQL: query failed: You have an error in your SQL syntax near ') rei_ts' at line 1 When I run the query below SELECT rei_ip \ FROM relay_ip \ WHERE rei_ip=${sender_host_address} AND DATE_SUB(NOW(), INTERVAL 10) rei_ts ...INTERVAL 10 day) rei_ts missing the unit of the interval... And the tabledesign is ... CREATE TABLE relay_ip ( rei_aid int(11) NOT NULL auto_increment, rei_uname varchar(30) NOT NULL default '', rei_domain varchar(128) NOT NULL default '', rei_ip varchar(64) NOT NULL default '', rei_ts datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (rei_aid), KEY rei_ip (rei_ip) ) ; -Wash -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection to server during query error just started
I have been running MySQL on a W2K platform for over a year now. The system is normally accessed through servlets, using the mm jdbc driver. I have a number of standalone java programs that also access the database using the mm driver. These have always worked quite well, until today. One of the programs creates a comma-separated-file export of some tables for me. It takes a couple of hours to run - but this has never been a problem until today. Today, on 10 separate occasions so far, the application has died - always with the same error: SQL Problem: Lost connection to server during query SQL State: 08007 Vendor Error: 0 This error is printed from the SQLException in the catch clause of my Java program. According to the MySQL manual, this should only occur if the connection is closed and then accessed (not the case), or if the connection times out. According to the manual, the default is 8 hours. I've run 'mysqladmin variables' and captured the output. I have not changed the variables since installing the system over 1 year ago, so I don't see how this should be the problem, but ... what variable do I check? Also - the program died between 10 minutes in and 40 minutes in. Before today, the application ran fine - and takes up to 4 hours. Heres the output from mysqladmin version: mysqladmin Ver 8.22 Distrib 4.0.0-alpha, for Win95/Win98 on i32 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version4.0.0-alpha-nt Protocol version10 Connection. via named pipe UNIX socketMySQL Uptime:19 hours 49 min 6 sec Threads: 2 Questions: 1032774 Slow queries: 0 Opens: 149 Flush tables: 1 Open tables: 0 Queries per second avg: 14.476 Any ideas for me? Thanks in advance. -Richard -- This communication is intended for the use of the recipient to which it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Lost connection to server during query error just started
I have been running MySQL on a W2K platform for over a year now. The system is normally accessed through servlets, using the mm jdbc driver. I have a number of standalone java programs that also access the database using the mm driver. These have always worked quite well, until today. One of the programs creates a comma-separated-file export of some tables for me. It takes a couple of hours to run - but this has never been a problem until today. Today, on 10 separate occasions so far, the application has died - always with the same error: SQL Problem: Lost connection to server during query SQL State: 08007 Vendor Error: 0 This error is printed from the SQLException in the catch clause of my Java program. According to the MySQL manual, this should only occur if the connection is closed and then accessed (not the case), or if the connection times out. According to the manual, the default is 8 hours. I've run 'mysqladmin variables' and captured the output. I have not changed the variables since installing the system over 1 year ago, so I don't see how this should be the problem, but ... what variable do I check? Also - the program died between 10 minutes in and 40 minutes in. Before today, the application ran fine - and takes up to 4 hours. Heres the output from mysqladmin version: mysqladmin Ver 8.22 Distrib 4.0.0-alpha, for Win95/Win98 on i32 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version4.0.0-alpha-nt Protocol version10 Connection. via named pipe UNIX socketMySQL Uptime:19 hours 49 min 6 sec Threads: 2 Questions: 1032774 Slow queries: 0 Opens: 149 Flush tables: 1 Open tables: 0 Queries per second avg: 14.476 Any ideas for me? Thanks in advance. -Richard -- This communication is intended for the use of the recipient to which it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connection to server during query error just started
Richard S. Huntrods wrote: I have been running MySQL on a W2K platform for over a year now. The system is normally accessed through servlets, using the mm jdbc driver. I have a number of standalone java programs that also access the database using the mm driver. These have always worked quite well, until today. One of the programs creates a comma-separated-file export of some tables for me. It takes a couple of hours to run - but this has never been a problem until today. Today, on 10 separate occasions so far, the application has died - always with the same error: Has anything in your network setup changed? New switch/router/network card, etc...Have you noticed any other errors in software that requires long-term network connections between these machines? My guess is that something along these lines have changed and no longer allow you to keep a long-term connection. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Lost connection to server during query error just started
Mark, Mark Matthews wrote: Richard S. Huntrods wrote: I have been running MySQL on a W2K platform for over a year now. The system is normally accessed through servlets, using the mm jdbc driver. I have a number of standalone java programs that also access the database using the mm driver. These have always worked quite well, until today. One of the programs creates a comma-separated-file export of some tables for me. It takes a couple of hours to run - but this has never been a problem until today. Today, on 10 separate occasions so far, the application has died - always with the same error: Has anything in your network setup changed? New switch/router/network card, etc...Have you noticed any other errors in software that requires long-term network connections between these machines? My guess is that something along these lines have changed and no longer allow you to keep a long-term connection. Thanks for the quick reply. Nothing has changed on the network, and the machine running the java application is the same W2K machine as the MySQL installation. It was all working as recently as 9/6/2002. I tried rebooting the machine and starting fresh - this time the application just hung at about 5000 records processed - no error message or exceptions at all. I am wondering about a new HP printer driver, however. I am wondering if it is interfering with network timings enough to break something (you can see from the zonealarm activity light that even running on one machine, MySQL accesses are indeed network based). I'm going to turn off the print driver and re-run the app. I'll let the list know. Still, it is puzzling. -RIchard -Mark -- This communication is intended for the use of the recipient to which it is addressed, and may contain confidential, personal, and or privileged information. Please contact us immediately if you are not the intended recipient of this communication, and do not copy, distribute, or take action relying on it. Any communications received in error, or subsequent reply, should be deleted or destroyed. -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query error
When I issue the following query from phpMyAdmin, SELECT b.* FROM newblocks b LEFT JOIN groups_blocks_link l ON l.block_id=b.bid WHERE (l.groupid=3) AND b.isactive=1 AND b.side=0 AND b.visible=1 ORDER BY b.weight,b.bid I get the following error Can't create/write to file '/var/tmp/#sql3f2_194_0.MYI' (Errcode: 2) but when I change * to bid in the above query like this SELECT b.bid FROM newblocks b LEFT JOIN groups_blocks_link l ON l.block_id=b.bid WHERE (l.groupid=3) AND b.isactive=1 AND b.side=0 AND b.visible=1 ORDER BY b.weight,b.bid I have no problem retrieving data, and no errors as I received with the first query. Has anybody got this problem? thanks in advance K. Ono - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql's ignoring query.. error
Franco, Friday, June 21, 2002, 6:03:16 AM, you wrote: FMG I'm using mysql-3.23.41 and initially able to use it without any problem. However, after I issued this command, grant all privileges on *.* to root@localhost identified by 'mypassword' with FMG grant option; , mysql will simply display the message Ignoring query to other database after issuing any command such as show databases;. FMG I am able to access mysql using : mysql -root -p, and entering the my password. FMG Would appreciate any assistance. Did you run mysql client program with --one-database option? (check also my.cnf/my.ini file if there is one-database entry) FMG Best regards, FMG Franco -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql's ignoring query.. error
Hello to everyone! I'm using mysql-3.23.41 and initially able to use it without any problem. However, after I issued this command, grant all privileges on *.* to root@localhost identified by 'mypassword' with grant option; , mysql will simply display the message Ignoring query to other database after issuing any command such as show databases;. I am able to access mysql using : mysql -root -p, and entering the my password. Would appreciate any assistance. Best regards, Franco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: query error
HI, The 127 error message is Record-file is crashed - use myisamchk -r table_name to fix it. For further information on this topic check http://www.mysql.com/doc/R/e/Repair.html Hope this helps Dobromir Velev Web Developer http://www.websitepulse.com/ -Original Message- From: WANG_FAITH??îÈõ-þì?L [EMAIL PROTECTED] To: '[EMAIL PROTECTED]' [EMAIL PROTECTED] Date: Saturday, December 29, 2001 03:25 Subject: query error Hi, all When I query data from a table ,the following error occurs: ERROR 1030: Got error 127 from table handler. Would you please tell me the reason to cause the error and how to deal with it? Thank you! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query error
Hi, all When I query data from a table ,the following error occurs: ERROR 1030: Got error 127 from table handler. Would you please tell me the reason to cause the error and how to deal with it? Thank you! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Error
Hi ! I´m trying to query some news with multiple rows in my database. I´m receiving an error when i use: SELECT table1.*, table2.table2_row FROM table1,table2 ORDER BY table1_row but, if I try: SELECT table1.table1_row, table2.table2_row FROM table1,table2 ORDER BY table1_row or SELECT table1.*, table2.table2_row FROM table1,table2 it works fine, and I don´t understand why it always work and now is returnig a error. I don´t have any idea of what is happennig. I think it´s a PHP problem. Thank You for Help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Query Error
hi, try your first SELECT statement, but with ORDER BY table1.table1_row maybe this helps (I didnt try it). Anton Tichawa -Original Message- From: Bruno Faé [mailto:[EMAIL PROTECTED]] Sent: Monday, October 22, 2001 5:44 PM To: [EMAIL PROTECTED] Subject: Query Error Hi ! I´m trying to query some news with multiple rows in my database. I´m receiving an error when i use: SELECT table1.*, table2.table2_row FROM table1,table2 ORDER BY table1_row but, if I try: SELECT table1.table1_row, table2.table2_row FROM table1,table2 ORDER BY table1_row or SELECT table1.*, table2.table2_row FROM table1,table2 it works fine, and I don´t understand why it always work and now is returnig a error. I don´t have any idea of what is happennig. I think it´s a PHP problem. Thank You for Help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Delete Query Error
Descritpion: Hello, I've got MySQL installed on a P-III 1ghz machine running RedHat 7. I am havign the same error, over and over again when I attempt to delete anything from any table. I've tried this delete from being ROOT to being a peon user. Heres my query: DELETE FROM topicexception WHERE word='SEXY'; And heres my return text: ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column As part of a sanity check, i deleted and readded ALL of the tables and database, even restarted MySQLd using safe_mysqld and after it stil did it, i ran one mysqld --args, and STILL got the error. Heres some info on the tables and database: +--+ | Tables_in_dynastynet | +--+ | admfounder | | admin| | nickexception| | nickkeyword | | topicexception | | topickeyword | | vhost| +--+ 7 rows in set (0.00 sec) +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | word | varchar(30) | | | | | +---+-+--+-+-+---+ +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| varchar(30) | | | | | | pass | varchar(30) | | | | | +---+-+--+-+-+---+ +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | word | varchar(30) | | | | | +---+-+--+-+-+---+ How-To-Repeat: Just for fun, i created a stable from scratch with the same problem.. ALL tables do this. mysql CREATE TABLE test (badword text); Query OK, 0 rows affected (0.00 sec) mysql insert into test ('lala'); ERROR 1064: You have an error in your SQL syntax near ''lala')' at line 1 mysql insert into test values ('lala'); Query OK, 1 row affected (0.01 sec) mysql delete from test where badword='lala'; ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql drop table test - ; Query OK, 0 rows affected (0.00 sec) Fix: No fix.. Nothing in the documentation about this either. I've asked onIRC, deja news, etc etc.. no ones ever seen this so im convinced its a bug of some sort. Submitter-Id: submitter ID Originator:Jason Brascum Organization: No organiztion MySQL support: none Synopsis: im completely unable to delete from tables Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.39 (Source distribution) Environment: System: Linux whorebox.terron.com 2.2.16-22 #1 Tue Aug 22 16:49:06 EDT 2000 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.0) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 11 Mar 24 20:54 /lib/libc.so.6 - libc-2.2.so -rwxr-xr-x 1 root root 5155229 Jan 10 2001 /lib/libc-2.2.so -rw-r--r-- 1 root root 24498288 Jan 10 2001 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Jan 10 2001 /usr/lib/libc.so Configure command: ./configure - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Delete Query Error
On 14-Jul-01 [EMAIL PROTECTED] wrote: Descritpion: Hello, I've got MySQL installed on a P-III 1ghz machine running RedHat 7. I am havign the same error, over and over again when I attempt to delete anything from any table. I've tried this delete from being ROOT to being a peon user. Heres my query: DELETE FROM topicexception WHERE word='SEXY'; And heres my return text: ERROR 1175: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column take safe-updates out of your ~/.my.cnf file or type SET SQL_SAFE_UPDATES=0 after connecting. snip Fix: No fix.. Nothing in the documentation about this either. My manual has it : `-U, --safe-updates[=#], --i-am-a-dummy[=#]' Only allow `UPDATE' and `DELETE' that uses keys. See below for more information about this option. You can reset this option if you have it in your `my.cnf' file by using `--safe-updates=0'. ... A useful startup option for beginners (introduced in *MySQL* Version 3.23.11) is `--safe-updates' (or `--i-am-a-dummy' for users that has at some time done a `DELETE FROM table_name' but forgot the `WHERE' clause) ... Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query: Error 2002 Can't connect to local mysql server.
Dear Sir, We are trying to install and configure mysql version .23.32 on Linux Red Hat 7. Usinf the configure option ./configure --prefix=/usr/local --localstatedir=/usr/local/mysql/data --with-unix-socket-path=/usr.local/mysql/tmp/mysql.sock the make and make install. we have tried several variations of the configure options. no error is shown during compilation and installation. On executing mysql from root there is an error sayin Can't connect to local MySql server. through var/lib/mysql/mysql.sock. We have tried with changed socket path in configure option giving the above path. But we do'nt find the sock file in any of the directory. Plese help us to resolve this problem. We have already installed apache and PHP4 in our system with success. Akshaya - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query: Error 2002 Can't connect to local mysql server.
First try to find out where you socket is located: # lsof -p {$PID_OF_MYSQL] |grep unix mysqld 1277 root 4u unix 0xcb596580 1712 /var/lib/mysql/mysql.sock And then point to this file in mysql conf file: # more /etc/my.cnf [client] socket=/var/lib/mysql/mysql.sock [mysqld] socket=/var/lib/mysql/mysql.sock (this config should startup mysqld with default socket to /var/lib/...) Regards, Erik On Fri, 23 Mar 2001, Foresight Systems Ltd. wrote: Dear Sir, We are trying to install and configure mysql version .23.32 on Linux Red Hat 7. Usinf the configure option ./configure --prefix=/usr/local --localstatedir=/usr/local/mysql/data --with-unix-socket-path=/usr.local/mysql/tmp/mysql.sock the make and make install. we have tried several variations of the configure options. no error is shown during compilation and installation. On executing mysql from root there is an error sayin Can't connect to local MySql server. through var/lib/mysql/mysql.sock. We have tried with changed socket path in configure option giving the above path. But we do'nt find the sock file in any of the directory. Plese help us to resolve this problem. We have already installed apache and PHP4 in our system with success. Akshaya - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- === = Erik Ahlstrm, [EMAIL PROTECTED] Systems Engineer, ProAct Datasystem AB Vx: +46 8 4106 6600 Ph: +46 8 4106 6621 Fx: +46 8 623 18 56 Mob:+46 73 356 6621 Lin*x, *BSD, Solaris === Unix IS user friendly. It's just selective about who its friends are. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query: Error 2002 Can't connect to local mysql server.
Did you follow the rest of the instructions? Did you create the mysql user? Did you run mysql_install_db --user=mysql ? Did you start the server ? "Foresight Systems Ltd." wrote: Dear Sir, We are trying to install and configure mysql version .23.32 on Linux Red Hat 7. Usinf the configure option ./configure --prefix=/usr/local --localstatedir=/usr/local/mysql/data --with-unix-socket-path=/usr.local/mysql/tmp/mysql.sock the make and make install. we have tried several variations of the configure options. no error is shown during compilation and installation. On executing mysql from root there is an error sayin Can't connect to local MySql server. through var/lib/mysql/mysql.sock. We have tried with changed socket path in configure option giving the above path. But we do'nt find the sock file in any of the directory. Plese help us to resolve this problem. We have already installed apache and PHP4 in our system with success. Akshaya - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php