Re: Nuke_
Looking at our dbase I noticed that a number of tables have been added The all start nuke_ I have no idea who added them, I can only assume my ISP added them, do they have a purpose, can I remove them etc The nuke_ tables are created automatically by a portal/conent management system called phpnuke. There are some variants and spin offs of this package which may or may not use the same table/database naming convention. Removing the tables would break the application. If it is no longer used/ you don't use it, you are probably safe in deleting them. -- Herbert Michael Kunzmann Binary Chaos Magician http://www.dreamstroke.com signature.asc Description: This is a digitally signed message part
Re: Cronjob / rights problem.
/usr/bin/mysqladmin: refresh failed; error: 'Access denied for user: '@localhost' (Using password: NO)' You can specify all the user host details that you want to use in the mysqladmin command. mysqladmin -u john --password=mypassword You will need to make sure that you use a user/host combination that has access. Granting access is done through adding records into the mysql database's tables. Depending on which type of access you intend on granting, you will need to enter records into different tables. -- Herbert Michael Kunzmann Binary Chaos Magician http://www.dreamstroke.com signature.asc Description: This is a digitally signed message part
Backup problem - disaster waiting to happen
I use mysqldump to dump my databases to file. I then write these files to tape. I was doing a test restore to a test server this weekend and found that for my largest database, I cannot restore from this file. I use mysql backup.script. It runs for a long time and creates most of the tables, but eventually comes up with a syntax error and stops processing the file. I have two questions: How do I get around this ? The error message is: .ERROR 1064 at line 78631: You have an error in your SQL syn s:v=\urn:schemas-microsoft-com:vml\\r\nxmlns:o=\u This data is xml data stored in one of the fields. If mysqldump created the syntax surely it should process back in correctly ? There's no way I can edit 2GB of incorrect entries in order to correct them. Secondly, how can I make the restore more fault tolerant ? If one call fails to continue with the next one ? Thank Ciao Herbert signature.asc Description: This is a digitally signed message part
Backup problem - disaster waiting to happen
I use mysqldump to dump my databases to file. I then write these files to tape. I was doing a test restore to a test server this weekend and found that for my largest database, I cannot restore from this file. I use mysql backup.script It runs for a long time and creates most of the tables, but eventually comes up with a syntax error and stops processing the file. I have two questions: How do I get around this ? The error message is: ERROR 1064 at line 78631: You have an error in your SQL syntax. Check the manual that c om:vml\\r\nxmlns:o=\u This data is xml data stored in one of the fields. If mysqldump created the syntax surely it should process back in correctly ? There's no way I can edit 2GB of incorrect entries in order to correct them. Secondly, how can I make the restore more fault tolerant ? If one call fails to continue with the next one ? Thank Ciao Herbert -- Herbert Michael Kunzmann Binary Chaos Magician signature.asc Description: This is a digitally signed message part
fulltext searching and query order question
Hi all.
I have a fulltext index on a table.
If I have the following fields:
field1,field2,field3,field4
Field4 being the fulltext field.
I have the following indices:
index1-field1,field2,field3
index2-fulltext field4
If I do a select:
select * from table where match(index2)
against ('word1 word2' in boolean mode);
I get a very fast result.
Essentially I want to do the following:
If I do a select field1,field2,field3 from table
where field1='something'
and field2='something_else'
and field3='something_more'
and match(field4) against ('word1 word2' in boolean mode)
order by field1,field2,field3
I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.
How do I get a fulltext search to be able to sort according
to a different field ???
Thanks for all assistance :-)
Regards,
Herbert
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index failure, cannot generate.
The answer is that a fulltext index can only be built on a TEXT field. Even though the mysql documentation describes MEDIUMTEXT and LONGTEXT fields as 'BLOB or TEXT field that can hold..', they can not be used. On Fri, 2003-06-06 at 09:36, H M Kunzmann wrote: Hi all. I am running RH9.0 with MySQL 4.0.13 I am trying to create a fulltext index on a 1.5GB table with 400 records. Whenever I do a create index (retried this a couple of times), the index size grows to only 8MB before the index completes. When using this index in fulltext searches, the search takes 1.5 Minutes. It seems to me like MySQL fails to generate the index correctly. Is this a bug ? Does anyone know what's going on ? Thanks in advance. Herbert -- Herbert Michael Kunzmann Binary Chaos Magician signature.asc Description: This is a digitally signed message part
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
The answer is that a fulltext index can only be built on
a TEXT field. Even though the mysql documentation describes
MEDIUMTEXT and LONGTEXT fields as 'BLOB or TEXT field that can hold..',
they can not be used.
On Thu, 2003-06-05 at 09:59, H M Kunzmann wrote:
Hello All.
I am using Redhat 9.0 with MySQL 4.0.12-0.
I've hit something of a dead-end with fulltext searching and I don't
know where to look next.
I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.
I've created a fulltext index on the table, with
alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...
If I do a fulltext search against this table, it takes about
1.5 minutes.
select filename from table2002 where match(data) against ('whatever')
If I do an explain on my query, it confirms that the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data| 0| | 1 |using where|
+--+-+-++---+---++---+
I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).
This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?
Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M
Should I set anything else ?
What can I do to get respectable return times ?
Please lend me a helping hand...
Thanks in advance.
H M Kunzmann
--
Herbert Michael Kunzmann
Binary Chaos Magician
signature.asc
Description: This is a digitally signed message part
RE: fulltext searching and query order question
I think I need to clarify :
The fulltext indexing searching here works great.
The search completes in good time, but then I want it in a different
order, as described by index1 down below. This resorting step is the one
that takes forever, not the fulltext search.
So essentially, a FORCE INDEX(index1) is more appropriate, but if I do
that, then the order is fast, but the fulltext search takes forever !
On Fri, 2003-06-13 at 18:32, Mike Hillyer wrote:
Have you tried adding force index on your fulltext index?
Something similar to this:
select field1,field2,field3 from table FORCE INDEX(index2)
where field1='something'
and field2='something_else'
and field3='something_more'
and match(field4) against ('word1 word2' in boolean mode)
order by field1,field2,field3
Regards,
Mike Hillyer
www.vbmysql.com
-Original Message-
From: H M Kunzmann [mailto:[EMAIL PROTECTED]
Sent: Friday, June 13, 2003 10:33 AM
To: [EMAIL PROTECTED]
Subject: fulltext searching and query order question
Hi all.
I have a fulltext index on a table.
If I have the following fields:
field1,field2,field3,field4
Field4 being the fulltext field.
I have the following indices:
index1-field1,field2,field3
index2-fulltext field4
If I do a select:
select * from table where match(index2)
against ('word1 word2' in boolean mode);
I get a very fast result.
Essentially I want to do the following:
If I do a select field1,field2,field3 from table
where field1='something'
and field2='something_else'
and field3='something_more'
and match(field4) against ('word1 word2' in boolean mode)
order by field1,field2,field3
I can't seem to get it right that the query can return
quickly, as it does a table scan to sort the table,
which takes forever.
How do I get a fulltext search to be able to sort according
to a different field ???
Thanks for all assistance :-)
Regards,
Herbert
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index failure, cannot generate.
I'm guessing blob data? ~1500MB / 400rows = ~3.75MB /row Perfect guess. Two columns: Filename (char len 20) and Text ~ 3.75MB/row. -- Herbert Michael Kunzmann Binary Chaos Magician signature.asc Description: This is a digitally signed message part
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
When mysql is indexing You can check your data file direcory and You ca see that one file (the index) is growing in size. I see it grow up to 8M and it stays there. Watching this, it grows very slowly. After 5 minutes of indexing, the file size has barely hit 2MB. The command I use is: create fulltext index Name on Table(field) So far I've only used the alter table call... I will use this call next and see if I get any problems. I've used the create index call now, as described above, and it does not change anything. The index still only grows to 8MB. I've read something of MySQL 4.0.12-0 having a fulltext index bug that converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs describes this in a bug fix entry. Perhaps this is a symptom ? I am busy downloading the newer version to find out. No change. Can your system handle files larger than 2GB? Maybe this is the problem. Can anyone give me any indication as to whether this may be the case ? Should I load the kernel that supports bigger filesystems ? Thanks for all help Ciao H M Kunzmann signature.asc Description: This is a digitally signed message part
index failure, cannot generate.
Hi all. I am running RH9.0 with MySQL 4.0.13 I am trying to create a fulltext index on a 1.5GB table with 400 records. Whenever I do a create index (retried this a couple of times), the index size grows to only 8MB before the index completes. When using this index in fulltext searches, the search takes 1.5 Minutes. It seems to me like MySQL fails to generate the index correctly. Is this a bug ? Does anyone know what's going on ? Thanks in advance. Herbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REPOST: FULLTEXT searching help needed. Please somebody help.
Hello All.
I am using Redhat 9.0 with MySQL 4.0.12-0.
I've hit something of a dead-end with fulltext searching and I don't
know where to look next.
I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.
I've created a fulltext index on the table, with
alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...
If I do a fulltext search against this table, it takes about
1.5 minutes.
select filename from table2002 where match(data) against ('whatever')
If I do an explain on my query, it confirms that the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data| 0| | 1 |using where|
+--+-+-++---+---++---+
I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).
This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?
Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M
Should I set anything else ?
What can I do to get respectable return times ?
Please lend me a helping hand...
Thanks in advance.
H M Kunzmann
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
I think your index is corrupted because I expect a 1.5 GB index and not 8M! You can see word list wit a utility (sorry I don't remember te name ft_dump). I agree with this :-) I suggest You to drop fulltext index, duplicate database and remove some rrecords. Then create index index again. I've done this a couple of times already with the same result. Some questions: Do You have disk space to index? I have 22GB free. Can your system handle files larger than 2GB? I'm not sure. Perhaps not with my current kernel. I am using the standard RH9 kernel. When mysql is indexing You can check your data file direcory and You ca see that one file (the index) is growing in size. I see it grow up to 8M and it stays there. The command I use is: create fulltext index Name on Table(field) So far I've only used the alter table call... I will use this call next and see if I get any problems. I've read something of MySQL 4.0.12-0 having a fulltext index bug that converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs describes this in a bug fix entry. Perhaps this is a symptom ? I am busy downloading the newer version to find out. Thank you for your help. I'll get back to you on whether any of the above helped. signature.asc Description: This is a digitally signed message part
detailed FULLTEXT index and search help needed
Hello All.
I have a table that is about 1.5GB with about 400 records.
As you can tell, every record is about 4MB, all of which is
text.
I've created a fulltext index on the table, with
alter table table2002 add fulltext data (data);
After this is done (takes about 20 minutes to do) I check
out the index size, which is 8,722 KB...
If I do a fulltext search against this table, it takes about
1.5 minutes.
select filename from table2002 where match(data) against ('whatever')
If I do an explain on my query, it confirms the index is used
+--+-+-++---+---++---+
|table |type |possible_keys|key |key_len|ref|rows|extra |
+--+-+-++---+---++---+
|table2002 |fulltext |data |data| 0| | 1 |using where|
+--+-+-++---+---++---+
I have another table, sized at only about 50MB, which I use the same
query and index on, which takes less than a second to do. One thing I
have noticed, is that the index size on this much smaller table is
larger than the bigger table (about 9MB).
This is a shot in the dark, but it seems to me that the index size is
_way_ too small and as such obviously cannot really help. If this is the
case, how do I increase it ?
Here are some of my mysql settings:
max_sort_length=16M
table_cache=256M
key_buffer=128M
sort_buffer=4M
read_buffer_size=1M
Should I set anything else ?
Please lend me a helping hand...
Thanks in advance.
H M Kunzmann
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
