Re: Error: You can't specify target table '...' for update in FROM clause
On 07.02.2008 03:52 CE(S)T, Chris wrote: If you don't mind a mysql-specific fix, and can get the data you want from a select query you could: insert into table (select goes here) on duplicate key update; or maybe a replace into ? INSERT/REPLACE ... SELECT will always overwrite the entire row, but I only want to copy a single column of it. The rest of the record must remain intact. So I can't use that, too. I also try to avoid DBMS-specific workarounds where I can in this project. So maybe one day MySQL will drop the above mentioned restriction. :) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1136 problem
Hi,
On Feb 6, 2008 4:47 PM, Olaf Stein [EMAIL PROTECTED] wrote:
Hi All
I am trying to run this query:
update minpheno set TMP_ados_version='0' where ident=898;
On below table:
And get error 1136
ERROR 1136 (21S01): Column count doesn't match value count at row 1
How can that be (obviously ident=898 exists)?
It sounds like the table is corrupt. Run CHECK TABLE.
The other possibility is that there is a trigger that is doing a blind
insert (one that doesn't specify column names) into another table.
I am going nuts
Thanks
Olaf
+---+---+--+
-+-++
| Field | Type | Null | Key
| Default | Extra |
+---+---+--+
-+-++
| minpheno_id | mediumint(8) unsigned | NO | PRI
| NULL| auto_increment |
| ident | mediumint(8) unsigned | NO | MUL
| ||
| irb | tinyint(3) unsigned | YES |
| NULL||
| ethnicity | enum('0','1','2') | NO |
| 0 ||
| race | enum('0','1','2','3','4','5') | NO |
| 0 ||
| dob | date | YES |
| NULL||
| current_age | smallint(4) unsigned | YES |
| NULL||
| epilepsy_hx | enum('0','1','2','7','9') | YES |
| NULL||
| parental_lang_delay | enum('0','1','2','9') | YES |
| NULL||
| best_est_dx | enum('1','2','3','4','5','6') | YES |
| NULL||
| best_est_iq | enum('0','1','2','3','4','5') | YES |
| NULL||
| adi_age | smallint(4) unsigned | YES |
| NULL||
| adi_version | enum('1','2') | YES |
| NULL||
| adi_social| tinyint(3) unsigned | YES |
| NULL||
| adi_comm | tinyint(3) unsigned | YES |
| NULL||
| adi_stereo| tinyint(3) unsigned | YES |
| NULL||
| adi_onset | enum('0','1') | YES |
| NULL||
| adi_age_first_words | smallint(4) unsigned | YES |
| NULL||
| adi_age_first_phrases | smallint(4) unsigned | YES |
| NULL||
| adi_verbal| enum('0','1','2') | YES |
| NULL||
| adi_dx| enum('0','1') | YES |
| NULL||
| TMP_adi_dx| enum('0','1') | YES |
| NULL||
| ados_age | smallint(4) unsigned | YES |
| NULL||
| ados_version | enum('1','2') | YES |
| NULL||
| TMP_ados_version | enum('0','1','2','3','4','5','6','7') | YES |
| NULL||
| ados_module | enum('0','1','2','3','4') | YES |
| NULL||
| ados_social | tinyint(3) unsigned | YES |
| NULL||
| ados_comm | tinyint(3) unsigned | YES |
| NULL||
| ados_stereo | tinyint(3) unsigned | YES |
| NULL||
| ados_total| tinyint(3) unsigned | YES |
| NULL||
| ados_dx | enum('0','1','2') | YES |
| NULL||
| TMP_ados_dx | enum('0','1','2') | YES |
| NULL||
| vine_age | smallint(4) unsigned | YES |
| NULL||
| vine_comm | smallint(3) unsigned | YES |
| NULL||
| vine_living | smallint(3) unsigned | YES |
| NULL||
| vine_social | smallint(3) unsigned | YES |
| NULL||
| vine_comp | smallint(3) unsigned | YES |
| NULL||
| comment | text | YES |
| NULL||
+---+---+--+
-+-++
-
stored procedure, parameter type help needed
Dear List, I want to create a stored procedure that runs a query using the IN operator (or is IN a function???) on values sent as argument. That is, my procedure should be called with something like: CALL get_users((1,2)); and I was hoping to implement something like this: CREATE PROCEDURE get_users(uids LIST) -- what type to use here? BEGIN SELECT * FROM user_test WHERE user_id IN uids; END:: Thanks, Magne Westlie Working test code for getting one user only: -- DROP TABLE IF EXISTS user_test; CREATE TABLE user_test ( user_id INT, user_name VARCHAR(100), PRIMARY KEY (user_id) ) ENGINE=MyIsam; INSERT INTO user_test VALUES(1, 'Bob'); INSERT INTO user_test VALUES(2, 'Ann'); INSERT INTO user_test VALUES(3, 'Bill'); DROP PROCEDURE IF EXISTS get_users; DELIMITER :: CREATE PROCEDURE get_users(uid INT) BEGIN SELECT * FROM user_test WHERE user_id = uid; END:: DELIMITER ; CALL get_users(3); -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only problem
is that the query I actually want to use this in, is a 100 line query
with lots of arguments. I don't feel to good about creating it into a
bunch of strings (16) that I have to concatenate with the variables
inbetween.
Also, I was moving the query into a stored procedure because I wanted to
make the request fast, and the concatenating and string handling takes
some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you like.
When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Magne, I want to create a stored procedure that runs a query using the IN operator ... See 'Variable-length argument for query IN() clause' at http://www.artfulsoftware.com/queries.php PB - Magne Westlie wrote: Dear List, I want to create a stored procedure that runs a query using the IN operator (or is IN a function???) on values sent as argument. That is, my procedure should be called with something like: CALL get_users((1,2)); and I was hoping to implement something like this: CREATE PROCEDURE get_users(uids LIST) -- what type to use here? BEGIN SELECT * FROM user_test WHERE user_id IN uids; END:: Thanks, Magne Westlie Working test code for getting one user only: -- DROP TABLE IF EXISTS user_test; CREATE TABLE user_test ( user_id INT, user_name VARCHAR(100), PRIMARY KEY (user_id) ) ENGINE=MyIsam; INSERT INTO user_test VALUES(1, 'Bob'); INSERT INTO user_test VALUES(2, 'Ann'); INSERT INTO user_test VALUES(3, 'Bill'); DROP PROCEDURE IF EXISTS get_users; DELIMITER :: CREATE PROCEDURE get_users(uid INT) BEGIN SELECT * FROM user_test WHERE user_id = uid; END:: DELIMITER ; CALL get_users(3); -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Hi Magne
...the query I actually want to use this in, is a 100 line query with
lots of arguments.
I don't feel to good about creating it into a bunch of strings (16)
that I have to
concatenate with the variables inbetween.
The only alternative I know for current versions of MySQL is to assemble
the query in the app layer.
Also, I was moving the query into a stored procedure because I wanted
to make
the request fast, and the concatenating and string handling takes some
of that away.
Unfortunately MySQL sprocs do not yet deliver this advantage.
PB
-
Magne Westlie wrote:
Hi,
Thanks a lot Peter, that was useful and it worked fine. The only
problem is that the query I actually want to use this in, is a 100
line query with lots of arguments. I don't feel to good about creating
it into a bunch of strings (16) that I have to concatenate with the
variables inbetween.
Also, I was moving the query into a stored procedure because I wanted
to make the request fast, and the concatenating and string handling
takes some of that away.
Is there another way?
Magne
Peter Brawley wrote:
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param
VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN ( |
but so long as it has those elements, it can be as complex as you
like. When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (',
('''abc'',''def'',''ghi''' ); |
||
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
very strange slow plain select
Hello: I just stumbled on this hard to explain problem. Below select * from BigTable (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf. What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. Thanks for help.
Is there any workbench or development studio available for Linux?
Hi thank you for reading my post Is there a development workbench or development/ administration studio available for linux? Whether from MySQL AB itself or other 3rd party companies? Thanks -- View this message in context: http://www.nabble.com/Is-there-any-workbench-or-development-studio-available-for-Linux--tp15345661p15345661.html 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: very strange slow plain select
In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf. What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. You want the --quick option. From the mysql manpage: o --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access limited after restore
Hi, all, I had a server crash a few weeks ago and had to restore my mySQL installation from backup after reinstalling Fedora. After the restore operation, I was no longer able to log into mySQL, either as root or as myself. I believe this may be because the UID in the recovered database is now different from the UID in my installation, although I'm having a hard time believing root will have a different UID after a standard installation. I've been living with this for a while because my daily operation doesn't require anything other than the ability to write to the DB through Wordpress, and that's working fine. However, today I attempted to delete a comment, and found that I could not. So, now I have a reason to try to fix this. Hence my questions: 1) Why would restoring from a backup wreck my ability to log into the database? 2) How can I go about reestablishing access to mySQL in a way that does not require blowing away all the data and starting over? Thanks. Phil W. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stored procedure, parameter type help needed
Magne,
Sorry, the server is down at the moment, here is the entry ...
To have an sproc accept a variable-length parameter list for an
|IN(...)| clause in a query, code the sproc to |PREPARE| the query
statement:
|
DROP PROCEDURE IF EXISTS passInParam;
DELIMITER |
CREATE PROCEDURE passInParam( IN qry VARCHAR(100), IN param VARCHAR(1000) )
BEGIN
SET @qry = CONCAT( qry, param, ')' );
PREPARE stmt FROM @qry;
EXECUTE stmt;
DROP PREPARE stmt;
END;
|
DELIMITER ;
|
For this example, the query string should be of the form:
|
SELECT ... FROM ... WHERE ... IN (
|
but so long as it has those elements, it can be as complex as you like.
When you call the sproc:
1. Quote each argument with a /pair/ of single quotes,
2. Separate these quoted arguments with commas,
3. Surround the whole |param| string with another set of single quotes:
|
CALL passInParam( 'SELECT * FROM tbl WHERE colval IN (', ('''abc'',''def'',''ghi''' );
|
||
PB
Re: Is there any workbench or development studio available for Linux?
http://dev.mysql.com/downloads/gui-tools/5.0.html http://dev.mysql.com/workbench/ legolas wrote: Hi thank you for reading my post Is there a development workbench or development/ administration studio available for linux? Whether from MySQL AB itself or other 3rd party companies? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very strange slow plain select
Thanks very much. The default mysql behavior is a little unusual. What is the parameter in my.cnf to control the cache size before forcing a flush. I waited for 2 mininutes second before the output came out without the -q option. My server is fast enough to read in more than 2 GB data during this time so the cache would have been filled long before that. Kind of puzzling. In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf. What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. You want the --quick option. From the mysql manpage: o --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there any workbench or development studio available for Linux?
Is there any plan to release the second link (workbench) for linux? thanks John Comerford-2 wrote: http://dev.mysql.com/downloads/gui-tools/5.0.html http://dev.mysql.com/workbench/ legolas wrote: Hi thank you for reading my post Is there a development workbench or development/ administration studio available for linux? Whether from MySQL AB itself or other 3rd party companies? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Is-there-any-workbench-or-development-studio-available-for-Linux--tp15345661p15346122.html 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: Is there any workbench or development studio available for Linux?
From the download page: Please note that at this point only the Windows Beta version is available. Linux and OS X releases will be available in 2008. It looks nice, I was looking for a similar tool 5 -6 years ago. It looks like what I would have wanted at the time. legolas wrote: Is there any plan to release the second link (workbench) for linux? thanks John Comerford-2 wrote: http://dev.mysql.com/downloads/gui-tools/5.0.html http://dev.mysql.com/workbench/ legolas wrote: Hi thank you for reading my post Is there a development workbench or development/ administration studio available for linux? Whether from MySQL AB itself or other 3rd party companies? 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]
Re: How to delete duplicates with full row comapring
Hello! I am looking for an easy solution for eliminate duplicates but on a row level. I am having 2 tables. 1 destination for all not duplicated info (a) and 1 for input table (b) which might have duplicates related to table a. Now I am using this kind of insert: INSERT INTO a SELECT fields FROM b WHERE ... NOT EXISTS ( SELECT * FROM a WHERE (a.a,a.b,a.c,a.d)=(b.a,b.b,b.c,b.d) ) Looks like it works but is it any solution for row level compare without naming all fields? For example WHERE (a.*) = (b.*) instead of currently used (a.a,a.b,...)=(b.a,b.b,...). Have you tried create table B as select * from A where 1 group by 'index';
Very slow update
I'm trying to write an update which generates ranking data for a table. Table is as follows CREATE TABLE `A` ( `id` INT NOT NULL , `score` DOUBLE NOT NULL , `projrank` INT NOT NULL , `other` VARCHAR( 10 ) NOT NULL ) ENGINE = MYISAM Real table actually contains 30 or so more fields but it gives a similar issue Score changes often, so 4 times per day I want to re-rank the data. Primary index is on score desc, id So I run the following set @rank:=0; update A set [EMAIL PROTECTED]:=rank+1 order by score desc,id For 20,000 rows the update takes 0.8 seconds For 50,000 rows it takes 1.9 seconds For 140,000 rows it takes ~ 5 seconds Scale up to 400,000 and it takes 7 minutes?? I'm sure it's probably some setting in my.cnf but I've tweaking them to no avail. Currently I have them set as follows key_buffer_size=256M max_allowed_packet=16M thread_stack=128K thread_cache_size=8 sort_buffer_size=48M join_buffer_size=3M read_buffer_size=4M query_cache_size=4M query_cache_limit=4M table_cache=100 max_connections=20 max_heap_table_size=64M myisam_sort_buffer_size=64M which is probably too high in places. Ideas? The full table actually has 1.7M rows in it which takes over an hour to update.. I've been puzzling on this for weeks now. Server is a 4core opteron 275 with 2Gb ram Thanks
Re: very strange slow plain select
At 03:49 PM 2/7/2008, [EMAIL PROTECTED] wrote: Thanks very much. The default mysql behavior is a little unusual. What is the parameter in my.cnf to control the cache size before forcing a flush. I waited for 2 mininutes second before the output came out without the -q option. My server is fast enough to read in more than 2 GB data during this time so the cache would have been filled long before that. Kind of puzzling. Have you tried Select SQL_NO_CACHE col1,col2 from table ...? If you are returning more than 10,000 rows at a time, why not use the LIMIT Offset,Limit to pull in just a few thousand rows at a time, then re-execute with a new offset to get the next thousand rows etc.. Why tie up the server with one huge query? You're also transferring 2gb over the network in one gulp so that can't be efficient either. Break it into smaller queries should help. Mike In the last episode (Feb 07), [EMAIL PROTECTED] said: I just stumbled on this hard to explain problem. Below select * from BigTable     (BigTable has 5 million rows) takes forever to even print out the first row. as you could see from the sql, it is supposed to print out some rows right away regardless of server config in my.cnf.  What is interesting is the following sql select * from BigTable limit 1000 return the rows immediately as expected. You want the --quick option. From the mysql manpage: o --quick, -q Do not cache each query result, print each row as it is received. This may slow down the server if the output is suspended. With this option, mysql does not use the history file. -- Dan Nelson [EMAIL PROTECTED] -- 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: Very slow update
Phil, What happens if you drop the index when you do the update? Does it run faster? If so then the index is slowing you down. Try increasing the key_buffer_size to 756M. If there isn't much difference, try the update without the Order By clause. If it runs considerably faster, then you need to increase your sort_buffer_size. See the link http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html for more info on optimizing the sort. BTW, I would definitely change the VarChar(10) to Char(10) because fixed length records should be faster. Mike At 06:49 PM 2/7/2008, Phil wrote: I'm trying to write an update which generates ranking data for a table. Table is as follows CREATE TABLE `A` ( `id` INT NOT NULL , `score` DOUBLE NOT NULL , `projrank` INT NOT NULL , `other` VARCHAR( 10 ) NOT NULL ) ENGINE = MYISAM Real table actually contains 30 or so more fields but it gives a similar issue Score changes often, so 4 times per day I want to re-rank the data. Primary index is on score desc, id So I run the following set @rank:=0; update A set [EMAIL PROTECTED]:=rank+1 order by score desc,id For 20,000 rows the update takes 0.8 seconds For 50,000 rows it takes 1.9 seconds For 140,000 rows it takes ~ 5 seconds Scale up to 400,000 and it takes 7 minutes?? I'm sure it's probably some setting in my.cnf but I've tweaking them to no avail. Currently I have them set as follows key_buffer_size=256M max_allowed_packet=16M thread_stack=128K thread_cache_size=8 sort_buffer_size=48M join_buffer_size=3M read_buffer_size=4M query_cache_size=4M query_cache_limit=4M table_cache=100 max_connections=20 max_heap_table_size=64M myisam_sort_buffer_size=64M which is probably too high in places. Ideas? The full table actually has 1.7M rows in it which takes over an hour to update.. I've been puzzling on this for weeks now. Server is a 4core opteron 275 with 2Gb ram Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
