Re: About the leftmost index prefixes using nounique index
Basically it says that if you have an index let's say INDEX_1 on columns: INDEX_1 : a, b, c, d MySQL will act as if you had setup indexes on: INDEX_1_1 : a, b, c INDEX_1_2 : a, b INDEX_1_1 : a A query like: SELECT a FROM table_name WHERE a 9; - will use the index SELECT a, b, c FROM table_name WHERE d 9; - will use the index Hope this clears up things ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a tricky join
Hi all I'm gradually learning how much simpler it is to do things with joins. I can tell that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all falls into place brain-wise and I can wallow in the joy of smaller more efficient sql! I'd really appreciate a little help with this one: I have an order database containing multiple rows for each order. The problem is that if there is further activity on an order in future days, the system feeding me resends the whole order again. So I'd like a way of excluding all previous instances of an order when I'm doing calculations. So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! Helen
Problem searching in grouped rows
Hello everyone! I have a problem with matching in grouped rows. I have: - one DB with customers - one DB with advertisement articles - one DB that holds what customer got which article the linked DB looks like: CREATE TABLE adverticlelink ( c_id int(11) NOT NULL, aa_id int(11) NOT NULL, recieved date NOT NULL, PRIMARY KEY (k_id,ml_id) ) ENGINE=MyISAM; ++-+---+ |c_id|aa_id|recieved | ++-+---+ |4 |2|48642465464| |4 |6|35465432234| |4 |15 |31354513213| ++-+---+ I want now to match customers that got for example the advertisement umbrella but not the advertisement zippo. No idea how to start that query. On top of that is use the MySQL Version 3.23.54, for pc-linux (i686). Anyone with any ideas? I did try it with WHERE (aa_id = 4) AND (aa_id != 6) GROUP BY c_id Also tried the HAVINg clause but that looked really false. Thanks for any replies :) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WITH ROLLUP, percentage calculations and pivots
Good day list, I've encountered a behaviour with 'WITH ROLLUP' that I don't understand. SELECT a.report_date, SUM(b.daytotal*last1) last1, ( 100*SUM(b.daytotal*last1) / (select sum(daytotal) FROM aggregate_logs WHERE aggr_source like 's%' and aggr_date=CURDATE() ) ) tper FROM master.pivot_customer_overview a, aggregate_logs b WHERE a.report_date=CURDATE() AND a.actual_date=b.aggr_date AND aggr_source LIKE 's%' GROUP BY aggr_source; This produces +-+---+---+ | report_date | last1 | tper | +-+---+---+ | 2006-06-21 | 0 | 0.00 | | 2006-06-21 |14 | 14.29 | | 2006-06-21 |84 | 85.71 | | 2006-06-21 | 0 | 0.00 | | 2006-06-21 | 0 | 0.00 | +-+---+---+ which is correct. 14 is 14.29% of 98. When I apply a WITH ROLLUP after the GROUP BY clause, the output changes to +-+---+--+ | report_date | last1 | tper | +-+---+--+ | 2006-06-21 | 0 |0 | | 2006-06-21 |14 | NULL | | 2006-06-21 |84 | NULL | | 2006-06-21 | 0 | NULL | | 2006-06-21 | 0 | NULL | | 2006-06-21 |98 | 100 | +-+---+--+ 98 is indeed 100% of 98, but why have the the rest of the calculated columns been determined as NULL? The docs on with rollup don't indicate (to me) why this happens. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Helen M Hudson ha scritto: So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! I do not know if i can give you the light because my english is not as good as I'd like. If i've understood your question, you can try this SQL statement: SELECT max( `date` ) AS 'date', sum( `amount` ) AS 'amount' FROM `Prova` WHERE `order` =100 GROUP BY `order` LIMIT 0 , 30 Enjoy and please tell me if it solves your problem... -- vittorio zuccalà Finconsumo Banca SPA [EMAIL PROTECTED] Tel: 011-6319464 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Helen M Hudson schrieb: Hi all I'm gradually learning how much simpler it is to do things with joins. I can tell that I haven't seen the light yet... but I'm expecting fireworks pretty soon when it all falls into place brain-wise and I can wallow in the joy of smaller more efficient sql! I'd really appreciate a little help with this one: I have an order database containing multiple rows for each order. The problem is that if there is further activity on an order in future days, the system feeding me resends the whole order again. So I'd like a way of excluding all previous instances of an order when I'm doing calculations. So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 I know its a one-liner for someone who has reached the light... so any help very much appreciated! Helen Well... MAX(date) AS latest_date_on_order, SUM(amount) FROM table GROUP BY date is it that what you looked for? Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Yes, I can see how this would work for just the one order and hardcoding the 100... but I cannot assume only to sum distinct values and my table has other order_refs in it with the same multiple rows of over multiple days, so I need a more generic select that will list this nice summary for all orders... do you see what I mean? e.g. id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude for order 100 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 6 | 2/1/01 | 101 | 1 i also need to exclude these 2 rows 7 | 2/1/01 | 101 | 2000 out of the calculation for order 101 8 | 2/1/01 | 101 | 1 9 | 3/1/01 | 101 | 2000 10 | 3/1/01 | 101 | 500 and I want to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 3/1/01 | 101 | 12500 Helen Quoting Helen M Hudson [EMAIL PROTECTED]: So, if my table structure was: id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 I'd like to end up with latest_date_on_order | order_ref | sum(amount) 2/1/01 | 100 | 1250 Not tested SELECT MAX(Date), order_ref, SUM(amount) FROM table WHERE order_ref=100 GROUP BY amount That should sum all the amounts that is distinct, and have a order ref of 100. I'm not sure if MAX(date) will be accepted - but there are better ways to select the date depending on the column type.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MATCH and return some text
Taco Fleur schrieb: Hi all, is it possible to do a MATCH AGAINST and return some of the text, for example the first paragraph that contains the matching words? Kind regards, Well you can use the substring function if mysql for that. http://dev.mysql.com/doc/refman/5.0/en/string-functions.html good luck Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about mailing list protocals
Ligaya Turmelle schrieb: I have been subscribed to this list for a couple of years now. I don't often respond and rarely ask questions, but I do read it every day and typically learn something new. Around the 13th of this month I suddenly stopped receiving the mailing list (though I was able to send a question to the list). Are email addresses dropped from the list if they are inactive for a given period of time? Can notices be sent out before that to inform the readers? I ended up simply having to resubscribe to the mailing list to start receiving it again. And had to go into the archive to find my last question (which no one answered by the way :) ) Well yes. The list will kick you out after some time when you stop sending mails to it. I don't know how long it takes but the list do it. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with altering a table
Alex wrote: I'm running mysql 5.0.22 on SLES9, using the mysql.com appropriate rpm. I've tried other versions of mysql 5, including 5.0.6, 5.0.17, 5.0.18 and 5.0.21. The result is always the same. This leads me to believe, that there are new requirements for mysql 5 and that's the reason ALTER fails. I can't figure it out myself, please help. Here's again the create table + alter table which fails: SET FOREIGN_KEY_CHECKS=0; CREATE TABLE dbmail_messageblks ( messageblk_idnr bigint(21) NOT NULL auto_increment, message_idnr bigint(21) NOT NULL default '0', messageblk longtext NOT NULL, blocksize bigint(21) NOT NULL default '0', PRIMARY KEY (messageblk_idnr), UNIQUE KEY messageblk_idnr_2 (messageblk_idnr), KEY messageblk_idnr (messageblk_idnr), KEY msg_index (message_idnr), FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) ON DELETE CASCADE ) TYPE=InnoDB; SET FOREIGN_KEY_CHECKS=0; SET SQL_LOG_OFF=1; SET SQL_LOG_UPDATE=0; ALTER TABLE dbmail_messageblks DROP INDEX messageblk_idnr, DROP INDEX messageblk_idnr_2, DROP INDEX msg_index, CHANGE message_idnr physmessage_id bigint(21) NOT NULL DEFAULT '0', ADD COLUMN is_header tinyint(1) DEFAULT '0' NOT NULL, ADD INDEX physmessage_id_index (physmessage_id), ADD INDEX physmessage_id_is_header_index (physmessage_id, is_header), ADD FOREIGN KEY physmessage_id_fk (physmessage_id) REFERENCES dbmail_physmessage (id) ON DELETE CASCADE ON UPDATE CASCADE; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a tricky join
Hi, 2006/6/21, Helen M Hudson [EMAIL PROTECTED]: Yes, I can see how this would work for just the one order and hardcoding the 100... but I cannot assume only to sum distinct values and my table has other order_refs in it with the same multiple rows of over multiple days, so I need a more generic select that will list this nice summary for all orders... do you see what I mean? e.g. id | date | order_ref | amount 1 | 1/1/01 | 100 | 1000 these 2 are the rows 2 | 1/1/01 | 100 | 200 i want to exclude for order 100 3 | 2/1/01 | 100 | 1000 4 | 2/1/01 | 100 | 200 5 | 2/1/01 | 100 | 50 6 | 2/1/01 | 101 | 1 i also need to exclude these 2 rows 7 | 2/1/01 | 101 | 2000 out of the calculation for order 101 8 | 2/1/01 | 101 | 1 9 | 3/1/01 | 101 | 2000 10 | 3/1/01 | 101 | 500 What you're asking does not involve join, but is a trick called group-wise maximum. Depending on your version of MySQL, there are several options to resolve this : http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html (I haven't tested it) : SELECT date, order_ref, sum(amount) FROM table s1 WHERE date=(SELECT MAX(s2.date) FROM table s2 WHERE s1.order_ref= s2.order_ref) GROUP BY s1.order_ref; the subquery get you the maximum date for each order_ref, and then you do the sum of this date. Was it what you were looking for ? -- http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if else statement
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if else statement
On Wednesday 21 June 2006 11:16, Song Ken Vern-E11804 wrote:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Maybe:
(SELECT col2 FROM table2 left join table1 on table1.id=1 where table1.col1=3
and table2.id=1)
UNION
(SELECT col2 FROM table3 left join table1 on table1.id=1 where table1.col13
and table3.id=1);
I have not tested it...
--
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About the leftmost index prefixes using nounique index
Hi Gabriel, Can you tell the benefits of a composite index, compared to 4 individual indices in this case ? Suppose I need to select on the fields b, c or d. Then I also need also indices on fields b, c and d. Together with the composite index on (a,b,c,d), there is a lot of redundancy in the indices. Regards, Cor - Original Message - From: Gabriel PREDA [EMAIL PROTECTED] To: Takanobu Kawabe [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 8:47 AM Subject: Re: About the leftmost index prefixes using nounique index Basically it says that if you have an index let's say INDEX_1 on columns: INDEX_1 : a, b, c, d MySQL will act as if you had setup indexes on: INDEX_1_1 : a, b, c INDEX_1_2 : a, b INDEX_1_1 : a A query like: SELECT a FROM table_name WHERE a 9; - will use the index SELECT a, b, c FROM table_name WHERE d 9; - will use the index Hope this clears up things ! -- Gabriel PREDA Senior Web Developer -- 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: About the leftmost index prefixes using nounique index
MySQL wil only use one index per table in a query... this is why in most cases a composite index will do better that a single column index. And for the second is true... this is leftmost rule... You have an index on: a, b, c You gain indexes on: a, b a But you will need to set up yourself an index on: a, c or c, a Try variations... of indexes toghether with EXPLAIN SQL... Also have a look at MySQL Optimization by Jeremy Zawodny at http://jeremy.zawodny.com/mysql/mysql-optimization.html -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with altering a table
When creating the InnoDB table the InnoDB engine asigns to the FOREIGN KEY you defined a symbol. On my server it generated dbmail_messageblks_ibfk_1... and if in the ALTER statement I entered: DROP FOREIGN KEY dbmail_messageblks_ibfk_1 Then the ALTER table worked fine... If you want to continue with this you should add a symbol name manually like this in the create table statement: CONSTRAINT `fk_message_idnr_manually_set` FOREIGN KEY (`message_idnr`) REFERENCES `messages` (`message_idnr`) Now in the ALTER statement you will have to write before you change the name of the column: DROP FOREIGN KEY `fk_message_idnr_manually_set` If you DROP an index a FOREIGN KEY based on that index will not be dropped automaticaly... Hope this helps ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP_CONCAT returns BLOB
Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Now, I haven't set any value for the group_concat_max_len, so it shouldn't be different from the default value, but the strange thing is that the same function returns a VARCHAR column when used on another table - they are both MyISAM tables. Here's the query in question: SELECT GROUP_CONCAT(product_id) FROM products WHERE product_retail_id LIKE '%dfl-%' Any ideas? Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP_CONCAT returns BLOB
Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Now, I haven't set any value for the group_concat_max_len, so it shouldn't be different from the default value, but the strange thing is that the same function returns a VARCHAR column when used on another table - they are both MyISAM tables. Here's the query in question: SELECT GROUP_CONCAT(product_id) FROM products WHERE product_retail_id LIKE '%dfl-%' Any ideas? (Sorry if this is a double-post, I accidently sent the first message from an unsubsribed address) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Split a Delimited String in SQL ( PROCEDURE split_string )
Hi all, I was trying this: http://forge.mysql.com/snippets/view.php?id=4 That is supposed to emulate a split() in mysql. Could anyone make it work ? I've been trying without luck. I 'm getting strange errors when trying to create this procedure. _ DROP PROCEDURE IF EXISTS split_string CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END __-- Best regards, MARTIN
[SOLVED] Re: problem with altering a table
Gabriel PREDA wrote: Hope this helps ! Thanks a bunch, that was it. Problem solved. I'll tell about it on the dbmail list as well. Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP_CONCAT returns BLOB
On 6/21/06, Kim Christensen [EMAIL PROTECTED] wrote: Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Hmm, I just realized this won't make any difference to my applications. Sorry! *Beats himself to death with his MySQL handbook* -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP_CONCAT returns BLOB
On 6/21/06, Kim Christensen [EMAIL PROTECTED] wrote: Hey list; I'm having trouble with the GROUP_CONCAT() function, which (according to the docs) is supposed to give me a column with the VARCHAR type, unless group_concat_max_len is 512. Instead it BLOBs me! Hmm, I just realized this won't make any difference to my applications. Sorry! *Beats himself to death with his MySQL handbook* Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Split a Delimited String in SQL ( PROCEDURE split_string )
Sorry forgot to copy my version: It's not acepting the delimiter command, can be ? I'm really missing something here. +---+ | version() | +---+ | 4.0.24_Debian-10ubuntu2.3-log | +---+ mysql delimiter // - CREATE PROCEDURE simpleproc (OUT param1 INT) - BEGIN - - SELECT COUNT(*) INTO param1 FROM t; 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 'delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote: Hi all, I was trying this: http://forge.mysql.com/snippets/view.php?id=4 That is supposed to emulate a split() in mysql. Could anyone make it work ? I've been trying without luck. I 'm getting strange errors when trying to create this procedure. _ DROP PROCEDURE IF EXISTS split_string CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END __-- Best regards, MARTIN
Re: Split a Delimited String in SQL ( PROCEDURE split_string )
Definitivaly stopping and going for one or two cups of coffee. I was logged in to another mysql... when I copied the last email. Sorry guys, I don't want to add extra non-sense text to the list! +--+ | version()| +--+ | 5.0.22-Debian_1.dotdeb.1-log | +--+ Now delimiter works fine, but I still get error trying to create the procedure ( http://forge.mysql.com/snippets/view.php?id=4 ): 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 'CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10' at line 2 Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote: Sorry forgot to copy my version: It's not acepting the delimiter command, can be ? I'm really missing something here. +---+ | version() | +---+ | 4.0.24_Debian-10ubuntu2.3-log | +---+ mysql delimiter // - CREATE PROCEDURE simpleproc (OUT param1 INT) - BEGIN - - SELECT COUNT(*) INTO param1 FROM t; 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 'delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN On 6/21/06, listsql listsql [EMAIL PROTECTED] wrote: Hi all, I was trying this: http://forge.mysql.com/snippets/view.php?id=4 That is supposed to emulate a split() in mysql. Could anyone make it work ? I've been trying without luck. I 'm getting strange errors when trying to create this procedure. _ DROP PROCEDURE IF EXISTS split_string CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END __-- Best regards, MARTIN
RE: Split a Delimited String in SQL ( PROCEDURE split_string )
Hi, There are a couple of errors in the way it is defined, firstly 1) There should be a ; after the DROP PROCEDURE statement 2) use a delimiter //, this stops the mysql client trying to interpret the other ; as the end of the command. It then takes everything until the next // as belonging to the PROCEDURE. 3) it also needs a ; after the final END 4) Will object if you don't have a database selected already as it is trying to create a temporary table. eg. DROP PROCEDURE IF EXISTS split_string; delimiter // CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END; // Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: listsql listsql [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June 2006 8:36 PM To: mysql@lists.mysql.com Subject: Split a Delimited String in SQL ( PROCEDURE split_string ) Hi all, I was trying this: http://forge.mysql.com/snippets/view.php?id=4 That is supposed to emulate a split() in mysql. Could anyone make it work ? I've been trying without luck. I 'm getting strange errors when trying to create this procedure. _ DROP PROCEDURE IF EXISTS split_string CREATE PROCEDURE split_string ( IN input TEXT, IN delimiter VARCHAR(10) ) SQL SECURITY INVOKER BEGIN DECLARE cur_position INT DEFAULT 1 ; DECLARE remainder TEXT; DECLARE cur_string VARCHAR(1000); DECLARE delimiter_length TINYINT UNSIGNED; DROP TEMPORARY TABLE IF EXISTS SplitValues; CREATE TEMPORARY TABLE SplitValues ( value VARCHAR(1000) NOT NULL PRIMARY KEY ) ENGINE=MEMORY; SET remainder = input; SET delimiter_length = CHAR_LENGTH(delimiter); WHILE CHAR_LENGTH(remainder) 0 AND cur_position 0 DO SET cur_position = INSTR(remainder, delimiter); IF cur_position = 0 THEN SET cur_string = remainder; ELSE SET cur_string = LEFT(remainder, cur_position - 1); END IF; IF TRIM(cur_string) != '' THEN INSERT INTO SplitValues VALUES (cur_string); END IF; SET remainder = SUBSTRING(remainder, cur_position + delimiter_length); END WHILE; END __-- Best regards, MARTIN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Split a Delimited String in SQL ( PROCEDURE split_string )
On Wednesday 21 June 2006 13:37, listsql listsql wrote: Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN 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 'Argentina Will win the match tonight against Holland' at line 1 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text problems
Perhaps the searches that return nothing are actually matching more than 50% of the record in the table. From the manual: In addition, words that are present in more than 50% of the rows are considered common and do not match. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 12:21 AM Subject: Full-Text problems Hi all, I am experiencing some issues with Full-Text and was hoping someone could shed some light on the following. I have some content which I know contains for example the word news, the table is MyISAM, the column type is LONGTEXT, there is an index on the column of FULLTEXT. I have adjusted the system variables so that the minimum word length is 2 (ft_min_word_len = 2) and the stop word file is set to (ft_stopword_file = '') Can anyone tell me why it is not picking up the word news plus some others? I have verified that the system picked up the new variable settings with SHOW VARIABLES I have also deleted everything in that table and reinserted the content, which I hope rebuilds the table? Other words that do not appear are; - dealer - sign in - contact I have verified that it returns other results, i.e. when searching on words like; - headline - engineering - user - her The query is as below SELECTI.indexIdentity , I.webpageIdentity , I.content , I.indexDate , MATCH ( I.content ) AGAINST ( '#form.searchString#' ) AS score , W.universalResourceLocator , W.title FROMtbl_index I INNER JOIN tbl_webpage W ON I.webpageIdentity = W.webpageIdentity WHEREMATCH ( I.content ) AGAINST ( '#form.searchString#' ) ORDER BY score DESC; The content is text I gathered from our webpages, I am basically trying to create a site search. Any help is much appreciated. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * Accept online Credit Card payments www.commerceengine.com.au http://www.commerceengine.com.au/ * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating Triggers
I am trying to create a trigger which will eventually call a judf.. However my mysql does not understand the delimiter command. actually... help doesnot even list delimiter as a possible command.. I am using MySQL- 5.0-22 Max server ... Installed it from a non-rpm binary distribution.. Also.. is it possible to call UDFs from within Triggers... Hope to hear at the earliest... Thanks in advance. Palani.
Re: if else statement
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Split a Delimited String in SQL ( PROCEDURE split_string )
Hi, It's very important to have instaled the library client related to the MySQL server version.For example, you cannot use libmysql.dll version 3.X or 4.x to use stored procedure from version 5.X of MySQL RDBMS. Regards, _ G.NET SOFTWARE COMPANY SYSTEM INTEGRATOR - AUTOMATION SOFTWARE DEVELOPMENT http://www.gonetsoftware.com Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] -Original Message- From: Jørn Dahl-Stamnes [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June, 2006 2:42 PM To: mysql@lists.mysql.com Subject: Re: Split a Delimited String in SQL ( PROCEDURE split_string ) On Wednesday 21 June 2006 13:37, listsql listsql wrote: Just for the happy comment: Argentina Will win the match tonight against Holland :) MARTIN 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 'Argentina Will win the match tonight against Holland' at line 1 -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- 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: Full-Text problems
Is there any way to test this? I doubt it is 50%, some of these words only appear once or twice within the content. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * Accept online Credit Card payments www.commerceengine.com.au * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June 2006 10:24 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Full-Text problems Perhaps the searches that return nothing are actually matching more than 50% of the record in the table. From the manual: In addition, words that are present in more than 50% of the rows are considered common and do not match. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 12:21 AM Subject: Full-Text problems Hi all, I am experiencing some issues with Full-Text and was hoping someone could shed some light on the following. I have some content which I know contains for example the word news, the table is MyISAM, the column type is LONGTEXT, there is an index on the column of FULLTEXT. I have adjusted the system variables so that the minimum word length is 2 (ft_min_word_len = 2) and the stop word file is set to (ft_stopword_file = '') Can anyone tell me why it is not picking up the word news plus some others? I have verified that the system picked up the new variable settings with SHOW VARIABLES I have also deleted everything in that table and reinserted the content, which I hope rebuilds the table? Other words that do not appear are; - dealer - sign in - contact I have verified that it returns other results, i.e. when searching on words like; - headline - engineering - user - her The query is as below SELECTI.indexIdentity , I.webpageIdentity , I.content , I.indexDate , MATCH ( I.content ) AGAINST ( '#form.searchString#' ) AS score , W.universalResourceLocator , W.title FROMtbl_index I INNER JOIN tbl_webpage W ON I.webpageIdentity = W.webpageIdentity WHEREMATCH ( I.content ) AGAINST ( '#form.searchString#' ) ORDER BY score DESC; The content is text I gathered from our webpages, I am basically trying to create a site search. Any help is much appreciated. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * Accept online Credit Card payments www.commerceengine.com.au http://www.commerceengine.com.au/ * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -- 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: if else statement
SELECT IF(col1=3, (Select col2 from table2 where table2.id = 1), (Select
col2 from table3 where table3.id = 1)) FROM table1 WHERE id=1;
That should do it.
-Original Message-
From: Thomas Lundström [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 21, 2006 7:51 PM
To: Song Ken Vern-E11804
Cc: mysql@lists.mysql.com
Subject: Re: if else statement
Not sure what you're aming for here and how your data is structured but
why not use a join and alias and fetch all info in one select and then
solve what you need in your code?
Something in the line of:
select t2.col2 from_t2, t3.col2 from_t3
from table1 t1, table2 t2, table3 t3
where t1.id = t2.id
and t1.id = t3.id
and t1.id = 3
Maybe you can do something like that?
Regards,
Thomas L.
ons 2006-06-21 klockan 17:16 +0800 skrev Song Ken Vern-E11804:
Hi,
I'm trying to build a query in using SQL instead of doing it in Perl.
I am trying to do something like this :
If ((select col1 from table1 where id = 1) == 3)
Then
Select col2 from table2 where table2.id = 1;
Else
Select col2 from table3 where table3.id = 1;
In Perl I would probably do have to access the DB twice.
Select col2 from table1 where if = 1;
If (col2 == 3) {
Select col2 from table2 where table2.id = 1;
} else {
Select col2 from table3 where table3.id = 1;
}
I've read the manual on subqueries but the example don't indicate how I
can
do a conditional test using a subquery?
Am I on the right track or is there another way to do this?
Thanks
--
Ken
e11804
--
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]
Index on MERGE table
Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld refuses to run on boot
Hi all I have problems getting MySQL autoboot on my RedHat installation. I have tried to add the mysql.server start script from the install dir to the system with chkconfig --add mysql (I copied it to /etc/init.d/) and then trying to add mysql to the default boot order with chkconfig mysql on but no luck there. I have even tried to edit rc.local with the commands service mysql start (this work when I run it myself) and mysqld -u mysql ... The log file says that mysql has been started and then ended directly after.. 060621 13:12:29 mysqld started 060621 13:12:33 mysqld ended Anyone that have any tip on how to solve this? I can add that chkconfig -- list | grep mysql tells me that MySQL has been configured to run on boot levels 3,4 and 5 correctly and go down on 1,2 and 6 but still it refuses to work. =( Version being used is, RHEL 4.3 and MySQL 5.0.22 All help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Just need script for creating tables
Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create the table, is there a command for this end? (I could copy paste the part from 'mysqldump' but it's not what I want to do.) Cheers. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BOOLEAN search with asterisk as preceeding operand?
Hey gang; If I have understood the boolean search method correctly, from own experiments and the docs, the asterisk operand cannot be put before a word - it negates the preceeding word completely. How have you solved this? I want my searches to match both words that starts with, contains, and ends with keyword. I guess I'm not alone to have this problem ;-) Regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on MERGE table
Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same. HTH, Cor - Original Message - From: Eugene Kosov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 2:58 PM Subject: Index on MERGE table Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- 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: mysqld refuses to run on boot
On 6/21/06, Fredrik Andersson [EMAIL PROTECTED] wrote: Hi all I have problems getting MySQL autoboot on my RedHat installation. I have tried to add the mysql.server start script from the install dir to the system with chkconfig --add mysql (I copied it to /etc/init.d/) and then trying to add mysql to the default boot order with chkconfig mysql on but no luck there. I have even tried to edit rc.local with the commands service mysql start (this work when I run it myself) and mysqld -u mysql ... The log file says that mysql has been started and then ended directly after.. 060621 13:12:29 mysqld started 060621 13:12:33 mysqld ended Anyone that have any tip on how to solve this? I can add that chkconfig -- list | grep mysql tells me that MySQL has been configured to run on boot levels 3,4 and 5 correctly and go down on 1,2 and 6 but still it refuses to work. =( Version being used is, RHEL 4.3 and MySQL 5.0.22 All help is greatly appreciated. OK, I have an old RH here, maybe I can help. Check if you have /etc/init.d/mysql file and that it has proper permissions i.e: -rwxr-xr-x Check your /etc/rc.d/rc#.d dirs (where # is the number of the level) and that they have symlinks to your /etc/init.d/mysql file. If they are not there... ln -s /etc/init.d/mysql /etc/rc.d/rc#.d/S90mysql One time for each of the runlevels where you want mysql on. Note that I put S90 so I don't accidently confuse it with the real init script. Good luck, PS: That's why I love Gentoo. rc-update add mysql default and voilá, its done. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Just need script for creating tables
On 6/21/06, Xiaobo Chen [EMAIL PROTECTED] wrote: Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create the table, is there a command for this end? (I could copy paste the part from 'mysqldump' but it's not what I want to do.) Read the reference manual for the mysqldump program, you can check the syntax to do that: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Specially for the --no-data append. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Just need script for creating tables
You can do something like: mysqldump --no-data -Original Message- From: Xiaobo Chen [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 9:11 PM To: mysql@lists.mysql.com Subject: Just need script for creating tables Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create the table, is there a command for this end? (I could copy paste the part from 'mysqldump' but it's not what I want to do.) Cheers. Xiaobo -- 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: Index on MERGE table
Oops! I think I've missed it... Thanks a lot! :) C.R.Vegelin пишет: Hi Eugene, I suppose you have read: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html Especially the paragraph starting with: The order of indexes in the MERGE table and its underlying tables should be the same. HTH, Cor - Original Message - From: Eugene Kosov [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 2:58 PM Subject: Index on MERGE table Hi everyone! I have a bunch of MyISAM tables and one MERGE table. All have same structure. It seems to me indecies on MERGE table aren't fine. When I fetch rows by indexed field I get empty result set. Index size showed by 'SHOW TABLE STATUS' is 0. SHOW INDEXES also shows somthing strange. Index cardinality is 81 while table contains over than 12 millions of different values. I've tried recreating indecies but this didn't help. I couldn't find anything relevant in documentation. How should I create index on MERGE table? Thanks in advance! -- Regards, Eugene Kosov. -- 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: Just need script for creating tables
--no-data should do the trick, try to do mysqldump --help and read the output Xiaobo Chen wrote: Hi, all If I use 'mysqldump', I will get the script to create the tables and those 'insert' statements to insert the data. I am wondering if I just want the first part, i.e, the script to create the table, is there a command for this end? (I could copy paste the part from 'mysqldump' but it's not what I want to do.) Cheers. Xiaobo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld refuses to run on boot
Probably a permissions issue. Ensure that the directory in which the pid file is created (I believe /var/run or /var/lib/mysql on RH) has write permission for the mysql system user/group. Also, ensure permissions/ownership on the datadir (/var/lib/mysql) for the mysql owner/group. Fredrik Andersson wrote: Hi all I have problems getting MySQL autoboot on my RedHat installation. I have tried to add the mysql.server start script from the install dir to the system with chkconfig --add mysql (I copied it to /etc/init.d/) and then trying to add mysql to the default boot order with chkconfig mysql on but no luck there. I have even tried to edit rc.local with the commands service mysql start (this work when I run it myself) and mysqld -u mysql ... The log file says that mysql has been started and then ended directly after.. 060621 13:12:29 mysqld started 060621 13:12:33 mysqld ended Anyone that have any tip on how to solve this? I can add that chkconfig -- list | grep mysql tells me that MySQL has been configured to run on boot levels 3,4 and 5 correctly and go down on 1,2 and 6 but still it refuses to work. =( Version being used is, RHEL 4.3 and MySQL 5.0.22 All help is greatly appreciated. -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user can see more than it's allowed to see?
Hello, I don't understand why user 'test1user' can see database 'test' as well. I think user 'test1user' should only be able to see database 'test1'. What did I do wrong here? I'd appreciate any help. Thanks. As root: mysql show databases; +---+ | Database | +---+ | LTM | | dummy | | mysql | | test | | test1 | +---+ 8 rows in set (0.00 sec) mysql grant all on test1.* to [EMAIL PROTECTED] identified by 'test1user'; Query OK, 0 rows affected (0.06 sec) mysql show grants for [EMAIL PROTECTED]; +--+ | Grants for [EMAIL PROTECTED]| +--+ | GRANT USAGE ON *.* TO 'test1user'@'localhost' IDENTIFIED BY PASSWORD '3b8031664a43a963' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `test1`.* TO 'test1user'@'localhost' | +--+ 2 rows in set (0.00 sec) As user test1user: mysql show databases; +--+ | Database | +--+ | test | | test1| +--+ 2 rows in set (0.00 sec) Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user can see more than it's allowed to see?
Bing Du schrieb: Hello, I don't understand why user 'test1user' can see database 'test' as well. I think user 'test1user' should only be able to see database 'test1'. What did I do wrong here? I'd appreciate any help. Thanks. As root: mysql show databases; +---+ | Database | +---+ | LTM | | dummy | | mysql | | test | | test1 | +---+ 8 rows in set (0.00 sec) mysql grant all on test1.* to [EMAIL PROTECTED] identified by 'test1user'; Query OK, 0 rows affected (0.06 sec) mysql show grants for [EMAIL PROTECTED]; +--+ | Grants for [EMAIL PROTECTED]| +--+ | GRANT USAGE ON *.* TO 'test1user'@'localhost' IDENTIFIED BY PASSWORD '3b8031664a43a963' | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `test1`.* TO 'test1user'@'localhost' | +--+ 2 rows in set (0.00 sec) As user test1user: mysql show databases; +--+ | Database | +--+ | test | | test1| +--+ 2 rows in set (0.00 sec) Bing database test itself has Grants that it shows itself to everyone. You have to set this in the table database. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
About mysqldump
Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disaster with dash on mysql cli interface
Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About mysqldump
Jørn Dahl-Stamnes schrieb: Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? Dump the Grant tables? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
Kevin Old schrieb: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin On this one use LIMIT. If you want to delete specific rows alway use LIMIT. even if you f**k up you just have deleted one row. If you are luck it is an old one and easy restoreable. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About mysqldump
On Wednesday 21 June 2006 17:17, Barry wrote: Jørn Dahl-Stamnes schrieb: Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? Dump the Grant tables? I have though of it. Currently I am dumping the mysql database, but there is a lot of tables that I probably don't need. Based on a dump of the mysql database, I guess that the following tables are the one I need to dump: mysql.db mysql.table_privs mysql.user Comments? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text problems
It's not necessarily the word appearing in more than 50% of thre records. If you search result returns more than 50% of the records, mysql considers the result irrlevant and doesn't return anything. You can kind of test it by using LIKE. SELECT count(*) from table where field like %dealer% or field like %contact% Remember, by default full text will find records that contain any of the words you are searching on. If you want to find only records that contain all the words, you need to do full text boolean search. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: 'Brent Baisley' [EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 8:51 AM Subject: RE: Full-Text problems Is there any way to test this? I doubt it is 50%, some of these words only appear once or twice within the content. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * Accept online Credit Card payments www.commerceengine.com.au * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Wednesday, 21 June 2006 10:24 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Full-Text problems Perhaps the searches that return nothing are actually matching more than 50% of the record in the table. From the manual: In addition, words that are present in more than 50% of the rows are considered common and do not match. - Original Message - From: Taco Fleur [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 12:21 AM Subject: Full-Text problems Hi all, I am experiencing some issues with Full-Text and was hoping someone could shed some light on the following. I have some content which I know contains for example the word news, the table is MyISAM, the column type is LONGTEXT, there is an index on the column of FULLTEXT. I have adjusted the system variables so that the minimum word length is 2 (ft_min_word_len = 2) and the stop word file is set to (ft_stopword_file = '') Can anyone tell me why it is not picking up the word news plus some others? I have verified that the system picked up the new variable settings with SHOW VARIABLES I have also deleted everything in that table and reinserted the content, which I hope rebuilds the table? Other words that do not appear are; - dealer - sign in - contact I have verified that it returns other results, i.e. when searching on words like; - headline - engineering - user - her The query is as below SELECTI.indexIdentity , I.webpageIdentity , I.content , I.indexDate , MATCH ( I.content ) AGAINST ( '#form.searchString#' ) AS score , W.universalResourceLocator , W.title FROMtbl_index I INNER JOIN tbl_webpage W ON I.webpageIdentity = W.webpageIdentity WHEREMATCH ( I.content ) AGAINST ( '#form.searchString#' ) ORDER BY score DESC; The content is text I gathered from our webpages, I am basically trying to create a site search. Any help is much appreciated. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * Accept online Credit Card payments www.commerceengine.com.au http://www.commerceengine.com.au/ * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -- 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: BOOLEAN search with asterisk as preceeding operand?
I wouldn't consider that a problem. If you want to search on contains or ends with, you can't use an index. Thus you should use LIKE. select * from table where field like %searchtext% That would give you the functionality you are looking for. How would you look up a word in the dictionary that ends with ely? You have to flip through every page, you can't take adavantage of the word index (alphabetical). - Original Message - From: Kim Christensen [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Wednesday, June 21, 2006 10:13 AM Subject: BOOLEAN search with asterisk as preceeding operand? Hey gang; If I have understood the boolean search method correctly, from own experiments and the docs, the asterisk operand cannot be put before a word - it negates the preceeding word completely. How have you solved this? I want my searches to match both words that starts with, contains, and ends with keyword. I guess I'm not alone to have this problem ;-) Regards -- Kim Christensen -- 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 slow
Dear friends is their any way to optimize this query bellow, it take +- 2minutes do complete, i think it becouse their no index by the emissao field SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC The table structure is bellow CREATE TABLE `sav00_sava0400_dbf` ( `unidade` double(2,0) default NULL, `duplicata` double(6,0) default NULL, `ordem` double(2,0) default NULL, `unidadeped` double(2,0) default NULL, `pedido` double(6,0) default NULL, `emissao` date default NULL, `vencto` date default NULL, `venctoorig` date default NULL, `cliente` double(5,0) default NULL, `cidade` double(4,0) default NULL, `estado` char(2) default NULL, `regiao` double(2,0) default NULL, `microregia` double(2,0) default NULL, `represent` double(3,0) default NULL, `comissao` double(5,2) default NULL, `valorface` double(15,2) default NULL, `valorbaixa` double(15,2) default NULL, `produtogrp` char(3) default NULL, `codbaixa` double(2,0) default NULL, `ocorrencia` double(2,0) default NULL, `databaixa` date default NULL, `jurosdev` double(15,2) default NULL, `jurospagos` double(15,2) default NULL, `dliquidupl` date default NULL, `jurospend` double(15,2) default NULL, `jurosmerc` double(15,2) default NULL, `saldodupl` double(15,2) default NULL, `jurosabona` double(15,2) default NULL, `statusfina` double(2,0) default NULL, `dstatusfin` date default NULL, `valorfatu` double(15,2) default NULL, `trans` date default NULL, `proc004` date default NULL, `agcobr` double(3,0) default NULL, `sitdupl` double(2,0) default NULL, `valordevol` double(15,2) default NULL, `valordesc` double(15,2) default NULL, `sr_recno` bigint(15) NOT NULL auto_increment, `sr_deleted` char(1) NOT NULL default '', `indkey_001` char(254) default NULL, UNIQUE KEY `sr_recno` (`sr_recno`), KEY `SAV00_SAVI0401_01` (`unidade`,`duplicata`,`ordem`,`sr_recno`), KEY `SAV00_SAVI0402_02` (`saldodupl`,`sr_recno`), KEY `SAV00_SAVI0403_03` (`proc004`,`sr_recno`), KEY `SAV00_SAVI0404_04` (`indkey_001`), KEY `SAV00_SAVI0405_05` (`represent`,`emissao`,`unidade`,`duplicata`,`ordem`,`sr_recno`), KEY `SAV00_SAVI0406_06` (`vencto`,`sr_recno`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
luiz Rafael wrote: Dear friends is their any way to optimize this query bellow, it take +- 2minutes do complete, i think it becouse their no index by the emissao field SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '2000-01-01' AND '2000-12-31' UNION ALL SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '1999-12-01' AND '1999-12-31' -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user can see more than it's allowed to see?
database test itself has Grants that it shows itself to everyone. How should I verify that? Thanks, Bing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
On 6/21/06, Barry [EMAIL PROTECTED] wrote: Kevin Old schrieb: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin On this one use LIMIT. If you want to delete specific rows alway use LIMIT. even if you f**k up you just have deleted one row. If you are luck it is an old one and easy restoreable. Hi Barry, So if I understand you correctly, I'd do the following: delete from tablename where id - 12345 limit 1; Is that correct? -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spanish MySQL Manual launched
The Spanish translation of the MySQL Reference Manual is finally complete. It was done by one of our partners from Barcelona, Spain, with a lot of help from our community (volunteer translators and reviewers). The translation covers MySQL 5.0 and can be found here: http://dev.mysql.com/doc/#spanish-5.0 In case you understand this, one of the first sentences in this 1500 pages document says: Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a [EMAIL PROTECTED] So, don't hesitate to suggest improvements! :-) Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead. Berlin, Germany (UTC +2:00) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
Hi, On Jun 21, 2006, at 12:24 PM, Kevin Old wrote: On 6/21/06, Barry [EMAIL PROTECTED] wrote: Kevin Old schrieb: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin On this one use LIMIT. If you want to delete specific rows alway use LIMIT. even if you f**k up you just have deleted one row. If you are luck it is an old one and easy restoreable. Hi Barry, So if I understand you correctly, I'd do the following: delete from tablename where id - 12345 limit 1; Is that correct? That still will delete one row, so you still might need a backup to get back that row. Another option you might want to look into is using the --safe- updates option to the command line client. This will prevent you from doing DELETEs and UPDATEs that don't use an index properly. For example, in your case deleting the entire table would have been prevented, whereas the correct id = 12345 would be allowed (assuming id is the PK or index). I generally always use that option on a production machine. It does a few other things as well (LIMIT 1000, max_join_size), so make sure you check it out before using it. It used to be called --i- am-a-dummy mode (that option works too), so you might see it referred to as that in some places. http://dev.mysql.com/doc/refman/5.0/en/safe-updates.html Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/ packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
Hi Jay Thanks for the help Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reclaim disk space
Dear Friends how to reclain the disk space used by an table that was dropped? Regards Luiz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reclaim disk space
Luiz, if you are working with MyISAM tables, the table files should be deleted when you DROP the table. If not, you might have an OS permissions issue. If you are working with InnoDB tables in one tablespace, you cannot currently easily reclaim the space. See http://bugs.mysql.com/bug.php?id=1287 One way to work around the InnoDB behavior is to enable the file-per-table option when you do initial set up; it's discussed in the link above. Dan luiz Rafael wrote: Dear Friends how to reclain the disk space used by an table that was dropped? Regards Luiz -- Dan Buettner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reclaim disk space
In the last episode (Jun 21), luiz Rafael said: how to reclain the disk space used by an table that was dropped? For most storage engines, each table is in its own file so a dropped table immediately returns space back to the OS. For InnoDB in tablespace mode (i.e. innodb_file_per_table is unset), you will have to back up and drop all your InnoDB tables, delete the tablespace files, and reload the tables. http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html -- 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: reclaim disk space
I had to do some disk space recovery mysql last month... I have backed up some of the raw .MYI, .MYD files and deleted them from the data directory. Running 5.0.18, I had to shut down mysql and restart before it freed up the space. -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 3:58 PM To: luiz Rafael Cc: mysql@lists.mysql.com Subject: Re: reclaim disk space In the last episode (Jun 21), luiz Rafael said: how to reclain the disk space used by an table that was dropped? For most storage engines, each table is in its own file so a dropped table immediately returns space back to the OS. For InnoDB in tablespace mode (i.e. innodb_file_per_table is unset), you will have to back up and drop all your InnoDB tables, delete the tablespace files, and reload the tables. http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html -- 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: query slow
Jay Pipes wrote: SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '2000-01-01' AND '2000-12-31' UNION ALL SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '1999-12-01' AND '1999-12-31' Why not: SELECT * FROM `sav00_sava0400_dbf` emissao BETWEEN '1999-12-01' AND '2000-12-31' ?? ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query slow
luiz Rafael wrote: SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) OR (1999 = YEAR(`emissao`) AND 12 MONTH(`emissao`)) ORDER BY emissao ASC Are you sure this is what you really want? MONTH() is never greater than 12, so your query is equal to: SELECT * FROM `sav00_sava0400_dbf` WHERE 2000 = YEAR(`emissao`) ORDER BY emissao In order to make your query work faster you should create an index on `emissao` and rewrite it using date ranges. So you query will become something like this (if you actually meant December'99 and whole year 2000): SELECT * FROM `sav00_sava0400_dbf` WHERE `emissao` BETWEEN '1999-12-01 00:00:00' AND '2000-12-31 23:59:59'; -- BR, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
On Wed, 21 Jun 2006 11:12:40 -0400, Kevin Old wrote: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin -- Kevin Old [EMAIL PROTECTED] Doesn't mysql have transactions? If it does you could just start a transaction before you do anything. This is what I do with PostgreSQL. As I recal oracle's sqlplus does this by default. begin; -- or something -- do some stuff -- if you did something dumb rollback; -- or something -- otherwise commit; -- or something -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need to speed up deletes
I need to delete 200,000 rows from an 8 million row MyISAM table using an index. It's taking 20 minutes which is quite slow. Is there any way to speed it up? TIA Mike MySQL 4.1.10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The Practical SQL Handbook
I have the subject book and I want to learn SQL from this book. Alas the bookbiz.sql file on the cd-rom was last updated in 1996 :-) 10 years ago. I tried to load it on my version 4 mysql and it would not get far at all. It appears that the software has changed since 1996. I did the testing and found first dates were now -mo-day and in 1996 it was mo/day/yy and so I had to change all those. Under all the INSERT INTO titles VALUE entries the use of '' was not complete. And the sentenses were too long. It took about 6 hours to correct the file but it's done. It loads with just a couple of warnings on my version 4.1.10. 73 Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to speed up deletes
Is the table heavily indexed? Indexes cause inserts and deletes (and updates under certain conditions) to slow down. Also, since you are deleting approx 2.5% of your rows, your delete-statement must have a where-clause. You might want to do an explain-plan on it to make sure it's not an optimization issue. I would look at your disk drives and their interface (ATA, SATA, SCSI). I'm sure there is a fair bit of disk-writing going on. You can try to drop some un-needed indexes (if there are any) and see if that helps. You could also drop all indexes except the one with the least cardinality in the where-clause of your delete script. That might not be feasible, however. Finally, try converting the table to InnoDB (you'll need to do some configuration in your my.cnf) - it tends to perform better in circumstances such as yours. I would do this on a test server first. David mos wrote: I need to delete 200,000 rows from an 8 million row MyISAM table using an index. It's taking 20 minutes which is quite slow. Is there any way to speed it up? TIA Mike MySQL 4.1.10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About the leftmost index prefixes using nounique index
Thank you very much for your understandable representation, Mr.PREDA and Mr.Vegerin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The Practical SQL Handbook
At 07:15 PM 6/21/2006, Karl Larsen wrote: I have the subject book and I want to learn SQL from this book. Alas the bookbiz.sql file on the cd-rom was last updated in 1996 :-) 10 years ago. I tried to load it on my version 4 mysql and it would not get far at all. It appears that the software has changed since 1996. I did the testing and found first dates were now -mo-day and in 1996 it was mo/day/yy and so I had to change all those. Under all the INSERT INTO titles VALUE entries the use of '' was not complete. And the sentenses were too long. It took about 6 hours to correct the file but it's done. It loads with just a couple of warnings on my version 4.1.10. 73 Karl Karl, That's a good book, and converting those scripts was quite a job. You would do a lot of people a favour if you could post the MySQL compliant version. Please keep in mind the provided SQL was for Sybase SQLAnywhere (Ver 6?) and data conventions are still not standard, and in this case I believe it's MySQL, with its European roots, that goes against the grain. Another great book is SQL for Smarties by Joe Celko. A number of his articles and exercises can be found on the web -- v. instructional. Cheers and have fun - Miles -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.9.1/369 - Release Date: 6/19/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
This sounds awfully like doing an rm -rf somefile. * (with an accidental space in between the . and the *). Most unix/linux geeks I know (including myself) only ever do this once. Humans like to learn the hard way, it seems :-) -- boof On Thursday 22 June 2006 03:24, Harrison Fisk wrote: Hi, On Jun 21, 2006, at 12:24 PM, Kevin Old wrote: On 6/21/06, Barry [EMAIL PROTECTED] wrote: Kevin Old schrieb: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin On this one use LIMIT. If you want to delete specific rows alway use LIMIT. even if you f**k up you just have deleted one row. If you are luck it is an old one and easy restoreable. Hi Barry, So if I understand you correctly, I'd do the following: delete from tablename where id - 12345 limit 1; Is that correct? That still will delete one row, so you still might need a backup to get back that row. Another option you might want to look into is using the --safe- updates option to the command line client. This will prevent you from doing DELETEs and UPDATEs that don't use an index properly. For example, in your case deleting the entire table would have been prevented, whereas the correct id = 12345 would be allowed (assuming id is the PK or index). I generally always use that option on a production machine. It does a few other things as well (LIMIT 1000, max_join_size), so make sure you check it out before using it. It used to be called --i- am-a-dummy mode (that option works too), so you might see it referred to as that in some places. http://dev.mysql.com/doc/refman/5.0/en/safe-updates.html Regards, Harrison -- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/ packaged/cluster.html -- brendan bouffler Architect, HPC New Technology APJ ESS Competency Lab x: Sydney, Australia, v: +61 404 097 837 mtb: 2003 Tassajara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: About mysqldump
On 6/21/06, Jørn Dahl-Stamnes [EMAIL PROTECTED] wrote: On Wednesday 21 June 2006 17:17, Barry wrote: Jørn Dahl-Stamnes schrieb: Is it possible to get mysqldump to include rights that has been GRANTED to a database or to tables in the database that is being dumped? Dump the Grant tables? I have though of it. Currently I am dumping the mysql database, but there is a lot of tables that I probably don't need. Based on a dump of the mysql database, I guess that the following tables are the one I need to dump: mysql.db mysql.table_privs mysql.user Comments? Isn't the hosts table needed too?! -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
More fulltext problems
I'm obviously getting myself into more and more trouble with fulltext..
I have the following SQL statement;
SELECT indexIdentity, webpageIdentity, content, MATCH (content) AGAINST
('gallery' ) as score FROM db_com_pacificmomentum.tbl_index
where MATCH (content) AGAINST ('gallery' );
Which returns the following error: Can't find FULLTEXT index matching the
column list
While I know there is a fulltext index on the column, when I run
SELECT indexIdentity, webpageIdentity, content, MATCH (content) AGAINST
('gallery' in boolean mode) as score FROM db_com_pacificmomentum.tbl_index
where MATCH (content) AGAINST ('gallery' in boolean mode);
It works fine.
Kind regards,
Taco Fleur
Free Call 1800 032 982 or Mobile 0421 851 786
Pacific Fox http://www.pacificfox.com.au/ http://www.pacificfox.com.au an
industry leader with commercial IT experience since 1994 .
*
Web Design and Development
*
Accept online Credit Card payments www.commerceengine.com.au
http://www.commerceengine.com.au/
*
SMS Solutions, including developer API
*
Domain Registration, .COM for as low as fifteen dollars a year,
.COM.AU for fifty dollars two years!
RE: Full-Text problems
Is there any way to override this functionality? It becomes a real pain, I have to start doing weird stuff to overcome this limitation, if it would just return the results whether its more than 50% or not, I would be fine. Is there no way to do this? Thanks. Kind regards, Taco Fleur Free Call 1800 032 982 or Mobile 0421 851 786 Pacific Fox http://www.pacificfox.com.au an industry leader with commercial IT experience since 1994 . * Web Design and Development * Accept online Credit Card payments www.commerceengine.com.au * SMS Solutions, including developer API * Domain Registration, .COM for as low as fifteen dollars a year, .COM.AU for fifty dollars two years! -Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Thursday, 22 June 2006 2:07 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Full-Text problems It's not necessarily the word appearing in more than 50% of thre records. If you search result returns more than 50% of the records, mysql considers the result irrlevant and doesn't return anything. You can kind of test it by using LIKE. SELECT count(*) from table where field like %dealer% or field like %contact% Remember, by default full text will find records that contain any of the words you are searching on. If you want to find only records that contain all the words, you need to do full text boolean search. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to RESET @@session.error_count system variable
Hello guys... I have stored procedures and I want when a calling program call my stored procedure it will return a response that the stored procedure execute successfuly or return an error code to the calling program. I found a @@session.error_count system variable but if theres an error this variable store error count occur. My problem is I can't RESET the value of the @@session.error_count variable coz it says this is a read only variable. Or is there any way functions I can use to handle error?. Tony -- View this message in context: http://www.nabble.com/How-to-RESET-%40%40session.error_count-system-variable-t1827840.html#a4986142 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
