Move couple files to another directory/hard drive
Hi. I have extra hdd only for mysql /var/lib/mysql directory. I have one big database and ten different tables into it. I had to move three of them into another directory on different hdd becouse lack of disk space at /var/lib/mysql directory. Is it possible and how can I do that ? Regards. -- Grzegorz Paszka -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange problem with index
Hi. I use MySQL 4.0.17 from rpm. When I want insert new row by perl script to one of my table I get such error : DBD::mysql::st execute failed: Duplicate entry '- Modified the spec file provided by ...' for key 3 Table looks : mysql desc spak; +--++--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--++--+-+-+---+ | id_lacz | int(11)| YES | MUL | NULL| | | selektor | int(4) | YES | MUL | NULL| | | data | mediumtext | YES | MUL | NULL| | +--++--+-+-+---+ 3 rows in set (0.01 sec) mysql show index from spak; +---++---+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+-+---+-+--++--++-+ | spak | 1 | spak_id_lacz |1 | id_lacz | A | 206999 | NULL | NULL | YES | BTREE | | | spak | 1 | spak_selektor |1 | selektor| A | 5 | NULL | NULL | YES | BTREE | | | spak | 1 | spak_data |1 | data| A | 344999 | 200 | NULL | YES | BTREE | | | spak | 1 | spak_fdata|1 | data| A | 1034998 |1 | NULL | YES | FULLTEXT | | +---++---+--+-+---+-+--++--++-+ 4 rows in set (0.04 sec) mysql select count(*) from spak; +--+ | count(*) | +--+ | 1034998 | +--+ 1 row in set (0.00 sec) Data length in data column is rather big; I did myisamchk -r , optimize table, repair table, mysqldump and insert it again, but problem still exists. I read that such error occurs when I insert no unique value to column with unique property. But as you can see I haven't UNIQUE key anywhere. I need help becouse I don't know what to do. Regards. -- Grzegorz Paszka -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize table vs. dump and insert
Hi. I wonder if optimize table results are equal to dump such table and insert it back ? Regards. -- Grzegorz Paszka sql,mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT index on two tables and many columns
On Thu, Feb 27, 2003 at 09:27:41AM -0500, Brent Baisley wrote: Maybe you should rethink your whole database structure. In your simplified example, you really only have two pieces of data, the text and a qualifier (a, b, c, d, e, ...). So instead of separating your text into different columns, keep all your text in one column and add another column that acts as the qualifier. You could even merge your tables since they would both end up having the exact same structure. You can then do self joins instead of your cross table joins. Finally, this structure allows you to create unlimited qualifiers without having to modified the structure of your tables or indexes. Your idea is very interesting. Of course, this is all based on your simplified example. Now I must think over if I am able to implement it in my more complex situation :) Thanks. -- Grzegorz mysql,sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FULLTEXT index on two tables and many columns
Hi. I know that for fulltext index are some limitations as: All parameters to the MATCH() function must be columns from the same table that is part of the same FULLTEXT index, unless the MATCH() is IN BOOLEAN MODE. But A boolean full-text search can also work even without a FULLTEXT index, although it would be SLOW. I've such situation: create table abc ( a text, b text, c text ); create table de ( d text, e text ); Size of database files is greater than amount of RAM. Twice. I want execute query with fulltext search through columns a and b, c and d, a and b and c and d and e . So on. There are 31 combinations. First problem: creating fulltext index on columns from two tables. Second problem: IMHO 31 fulltext indexes is too much. Even I merge this two tables. I still must create 31 indexes... My suggestion is: In cases when there is need to perform search on many columns than I should create 5 indexes on a,b,c,d and e. These should to be enough. Maybe search won't be so fast as with 31 indexes but should be faster than search without 31 indexes. Regards. -- Grzegorz mysql,sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Escape char in fulltext search
On Tue, Nov 26, 2002 at 02:30:41PM +0100, Sergei Golubchik wrote: On Nov 25, Grzegorz Paszka wrote: Hi. Could You explain me for what is macro misc_word_char ? A word must have true_word_char on both ends, and there should not be two adjacent misc_word_char's in the word. E.g. (assuming HYPHEN_IS_DELIM is NOT defined) -asdf'-- the word is asdf as-df--- the word is as-df 'a's-f-- the word is a's-f as--df-- two words, as and df. I think, boolean operators '+' and '-' can be misc_word_char's but not true_word_char's. But to be on the safe side, you can go to ft_static.c and change ft_boolean_syntax variable, changing '+' and '-', to, e.g., '' and '!' ... This is a good idea, but in ft_boolean_syntax I see more chars which should not be true_word_char. So what I should do when I have such string : --~(+) And I want to find it by fulltext search. I know that looks strange :) But i've idea: Redefine true_word_char (misc_word_char is null) #define true_word_char(X) (isgraph(X)) and mysql parser should understands escaping char. So string --~(+) I can search by typing : \\-\-\~\\\(\+\) Is any chance to implement escaping char feature ? Maybe there is another solution for my problem ? Best regards. P.S. I posted this mail to [EMAIL PROTECTED] but nobody replaed, mabye here I'll have more luck. -- Grzegorz mysql,sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FULLTEXT search and ORDER BY
Hi.
I've such problem.
Is ORDER BY works with MATCH AGAINST ?
i.e.
create table test (
id_test int11,
message text
);
SELECT id_test, message FROM test WHERE WHERE MATCH (message) AGAINST
('example' IN BOOLEAN MODE) ORDER BY id_test
For me in mysql 4.0.3beta this select doesn't work as I want. Results are still
sorted by score from MATCH AGAINST phrase. I know that such order by breaks
philosophy of MATCH AGAINST, but when I want sort results in different way I
should be able to do this.
How can I solve this problem and force mysql to do what I want ? :)
Regards.
--
Grzegorz
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Strange FULLTEXT search results in 4.0.2alpha
I've compiled mysql 4.0.2-alpha from source.
I've created table:
create table test (
id_test int4,
body text);
Next:
create fulltext index test_body on test (body);
insert into test (id_test,body) values (1,'test rpm');
insert into test (id_test,body) values (2,'test rpm');
insert into test (id_test,body) values (3,'test rpm');
insert into test (id_test,body) values (4,'test rpm');
And now:
mysql select * from test where match (body) against ('+rpm' IN BOOLEAN MODE);
Empty set (0.00 sec)
But:
mysql select * from test where match (body) against ('+test' IN BOOLEAN MODE);
+-+--+
| id_test | body |
+-+--+
| 1 | test rpm |
| 2 | test rpm |
| 3 | test rpm |
| 4 | test rpm |
+-+--+
4 rows in set (0.00 sec)
I read http://www.mysql.com/doc/F/u/Fulltext_Fine-tuning.html and see there :
... search using IN BOOLEAN MODE instead, which does not observe the 50% threshold.
This is right for test word but not for rpm word. Why ?
I've done even more:
insert into test (id_test,body) values (5,'test');
insert into test (id_test,body) values (6,'test');
insert into test (id_test,body) values (7,'test');
insert into test (id_test,body) values (8,'test');
insert into test (id_test,body) values (9,'test');
insert into test (id_test,body) values (10,'test');
And still:
mysql select * from test where match (body) against ('+rpm' IN BOOLEAN MODE);
Empty set (0.00 sec)
Am I doing something wrong ? I checked ft_static.c file and there isn't rpm word.
Regards.
--
Grzegorz
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem: myisamchk: error: Checksum for key....
On Mon, Nov 12, 2001 at 07:21:07PM +0200, Sinisa Milivojevic wrote: Grzegorz Paszka writes: Yes, I say more, that I created new database and filled it by perl script from data source and I have the same situation. I think that is the best way of rebuild index file :) See in our manual what to do when index file is not there, so try to follow those instructions. Backup your table first !! Before I wrote to this list I read manual. Especially chapter 4.4 . I've done what You suggested but it didn't help. (Stage 3: Difficult repair). That's one problem. Another is as I wrote above. New database with content from data source is also broken. I know that is sounds strange but it's true. I'll try to set up database on another computer and I'll see if it's data related problem or hardware (I don't believe) or configuration. If I will have more information I'll write on list and to You. If You have another suggestions don't hesitate to write it to me. Best regards. -- Grzegorz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem: myisamchk: error: Checksum for key....
On Mon, Nov 12, 2001 at 03:22:31PM +0200, Sinisa Milivojevic wrote: Grzegorz Paszka writes: Hi. I've problem with mysql. I had i386 RH71 (kernel 2.4.13, 1.5GB RAM, single procesor) with mysql 3.23.36 (now I have 3.23.41 and problem still exists). I run myisamchk on table zawartosc: [root@tygrys rpm]# myisamchk -c -i -v -w zawartosc Checking MyISAM file: zawartosc Data records: 6995929 Deleted blocks: 0 - check file-size - check key delete-chain block_size 1024: - check record delete-chain No recordlinks - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed:0% Max levels: 4 - check data record references index: 2 Key: 2: Keyblocks used: 65% Packed: 60% Max levels: 7 Total:Keyblocks used: 69% Packed: 56% - check record links myisamchk: error: Checksum for key: 2 doesn't match checksum for records Record blocks: 6995929Delete blocks: 0 Record data: 398291567Deleted data: 0 Lost space:5259802Linkdata: 26232723 MyISAM-table 'zawartosc' is corrupted Fix it using switch -r or -o Of course I used switch -r and problem still exists. Second I used switch -o and problem still exists. Hi! I hope you have not run myisamchk while server was running Of course. If yes, then that would explain things. Try using CHECK TABLE SQL command instead. I did something like that: mysql check table zawartosc; +---+---+--+ + | Table | Op| Msg_type | Msg_text | +---+---+--+ + | rpm.zawartosc | check | error| Checksum for key: 2 doesn't match checksum for records | | rpm.zawartosc | check | error| Corrupt | +---+---+--+ + 2 rows in set (2 min 56.04 sec) mysql repair table zawartosc; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | rpm.zawartosc | repair | status | OK | +---++--+--+ 1 row in set (14 min 10.89 sec) mysql check table zawartosc; +---+---+--++ | Table | Op| Msg_type | Msg_text | | +---+---+--++ | rpm.zawartosc | check | error| Checksum for key: 2 doesn't match checksum for |records | | rpm.zawartosc | check | error| Corrupt | | +---+---+--++ 2 rows in set (1 min 58.75 sec) mysql repair table zawartosc EXTENDED; +---++--+--+ | Table | Op | Msg_type | Msg_text | +---++--+--+ | rpm.zawartosc | repair | status | OK | +---++--+--+ 1 row in set (14 min 4.54 sec) mysql check table zawartosc ; +---+---+--++ | Table | Op| Msg_type | Msg_text | | +---+---+--++ | rpm.zawartosc | check | error| Checksum for key: 2 doesn't match checksum for |records | | rpm.zawartosc | check | error| Corrupt | | +---+---+--++ 2 rows in set (2 min 0.96 sec) Do you want to say that there is no way that you can repair MyISAM table ?? Yes, I say more, that I created new database and filled it by perl script from data source and I have the same situation. I think that is the best way of rebuild index file :) So what I should do in this case ? -- Grzegorz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
