Re: Maria Engine and AMD and 5.1.23 binary version
Hi! On Feb 14, Mariella Petrini wrote: Hi All, Is there any plan to release a binary version for mysql-5.1.23-maria-alpha for Linux 64 for AMD ? Yes. Regards / Mit vielen Grüssen, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ Principal Software Developer/Server Architect /_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Dachauer Str. 37, D-80335 München ___/ Geschäftsführer: Kaj Arnö - HRB München 162140 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Streaming LOB Data
Hi! On Apr 29, Robert DiFalco wrote: Thanks Sergei, it's nice to know for sure. Do you know if there is any documentation on how memory is used to store LOB data? For example, is it a percentage of the total buffer pool size or is it allocated from available memory un-allocated to the buffer pool size? No, I don't. Sorry :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Streaming LOB Data
Hi! On Apr 28, Robert DiFalco wrote: It appears (for InnoDB at least) that while INSERTing a LOB that all LOB data must be loaded into memory before it is written to disk. Or is it just the size of the combined log files? Looking at Task Manager, it looks like it DOES try to load the whole thing into memory before streaming it to disk; which just seems wacky. The error I get is this: Out of memory (Needed xxx ... If the server crashes I get this: InnoDB: which exceeds the log group capacity 18870682. InnoDB: If you are using big BLOB or TEXT rows, you must set the InnoDB: combined size of log files at least 10 times bigger than the InnoDB: largest such row. 060427 15:26:53 InnoDB: Error: cannot allocate 539001144 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 52823786 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. How can I make the size of LOBs I insert NOT be memory constrained? No way. Unfortunately, it's the way MySQL works at the moment. Which does not mean it's not going to be changed in the future. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Kerpen, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
Hi! On Aug 09, Daniel Kasak wrote: Hi all. I've been testing out mysql-5.0.10 on my Powerbook ( Gentoo Linux PPC ), and I've hit an incredibly unfortunate bug. It is demonstrated adequately with the following: ... Is anyone interested in examining what went wrong? ie should I create a bug report? I suppose I'd have to upload a zipped copy of my /var/lib/mysql folder or something, since mysql isn't too keen on giving up any data voluntarily. Yes, please submit a bugreport. And try to explain there how we could repeat this bug. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fulltext/boolean search
Hi! Check the manual for ft_boolean_syntax variable. It defines what character is used for every operator. Space marks default operator - so you can change it. On Jun 06, Sebastian wrote: I created a search app with fulltext, boolean, etc. i have two forms, one that allows the user to just enter a basic search in a single input field and a more advanced form with additional input areas for advanced boolean searches.. now, when you do not specify a boolean operator, what does it default to? i am thinking it defaults to a search similar to using LIKE %string% Basically i want to know what default boolean operator i should use for a 'basic' search or should i not use a boolean for the 'basic' search? i want to provide decent search results without forcing the user to go to advanced mode. just looking for tips/suggestions to tweak the results it returns and anything else i should know. lastly... this might be more related to php, if anyone knows of a reliable function/class to highlight search terms, please let me know. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Behavior of ON DUPLICATE KEY
Hi! On May 15, Taisuke Yamada wrote: Hi. I found behavior of ON DUPLICATE KEY bit odd, and would like to know if it's a bug or feature. I'm trying to insert or add (not replace) data of one table to another table and came up with following statement: CREATE TABLE foo (id INT NOT NULL PRIMARY KEY, value INT); CREATE TABLE bar (id INT NOT NULL PRIMARY KEY, value INT); INSERT INTO bar VALUES (1, 100); INSERT INTO foo SELECT id, value FROM bar ON DUPLICATE KEY UPDATE value = value + VALUES(value); This INSERT statement works, but is not robust as column order of table foo is not specified. So I add one: INSERT INTO foo (id, value) SELECT id, value FROM bar ON DUPLICATE KEY UPDATE value = value + VALUES(value); Now, this one errors with with the message ERROR 1110 (42000): Column 'value' specified twice Why is this? Can someone tell me what is happening? Check http://bugs.mysql.com - there were several bugs in name resolution for INSERT ... SELECT ... ON DUPLICATE KEY UPDATE reported recently, I believe yours is one of those. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bulk_insert_buffer_size and InnoDB
Hi! On May 10, Jan Pieter Kunst wrote: Dear all, I read the following on this page http://dev.mysql.com/doc/mysql/en/server-system-variables.html: bulk_insert_buffer_size MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. Note: This cache is used only when adding data to a non-empty table. The default value is 8MB. This variable was added in MySQL 4.0.3. This variable previously was named myisam_bulk_insert_tree_size. I take this to mean that bulk_insert_buffer_size has no bearing on InnoDB tables, only on MyISAM tables. Is this correct? Yes. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems upgrading from 4.0 to 4.1
Hi! On Apr 11, Christian Hammers wrote: Hello Sergei On Thu, Apr 07, 2005 at 03:14:10PM +0200, Sergei Golubchik wrote: I've just run into deep troubles while upgrading from 4.0.24 to 4.1.10a using precomiled Debian packages on Debian Woody although I read the comments regarding upgrading on dev.mysql.com. After starting the new server and running mysqlcheck -r -v -A, I experienced the following problems on about 15% of my tables (seemed to be quite random, i.e. not only the biggest or most used ones): Christian, do you have any of that tables ? Can you upload one of them (preferably, the smallest one) to our ftp ? beta_hardware_verwaltung.hardware_log info : Found wrong packed record at 0 info : Found wrong packed record at 56 info : Found wrong packed record at 112 Did you found the files on the incoming/ dir and do you have any news on this case? If it's not something extremly stupid from my side, can you open a bug report to make the issue easier to track? Yes, I found the file and repeated this problem, thanks. I opened a bugreport #9824 for it. about 15% of my tables (seemed be quite random - it happens on the tables that have live checksum enabled (CHECKUM=1 in CREATE TABLE). workarounds - disable checksum (in 4.0, before upgrade) or copy the table with create ... select. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing a custom storage engine
Hi! On Mar 31, Homam S.A. wrote: I've been contemplating writing my own custom storage engine for MySQL. I haven't found much documentation on the subject besides these articles: http://netmirror.org/mirror/mysql.com/tech-resources/articles/creating-new-storage-engine.html http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html However, they refer to an example storage engine written for demo purposes that should be found under the /sql/examples folder in the source distribution. I downloaded the lates Win32 source (4.1.10a), but I couldn't find a /sql/examples folder. This is a bug. I fixed it, though I cannot promise the fix will get into 4.1.11 (it's almost done now). Where can I find the source for the example storage engine? In the unix source distribution (any recent one). Probably it'll be in the 4.1.11 windows source distribution too. And where can I find other documentation that provides pointers on the subject? Check other engines in the sql/examples directory. Ask your questions in the [EMAIL PROTECTED] Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't MySQL cache queries that start with parenthesis? (further info)
Hi! On Mar 28, Homam S.A. wrote: It seems that MySQL freaks out whenever it seems something that looks like a derive table and refuses it to cache. Even a non-UNION query like: SELECT * FROM (SELECT * FROM X WHERE A = 5) AS DerivedTable Won't be cached. It's a bug. Could you submit a test case to bugs.mysql.com ? Something like: create table t1 (a int); insert t1 values (1); show status like 'Qc%'; select * from ((select * from t1) union all (select * from t1) order by 1); show status like 'Qc%'; would be enough for a test case. (I could submit a bugreport myself, but then you won't be notified when the bug is fixed) I read a comment in the documentation that if you put SQL_CACHE in the SELECTs of the parenthesized queries, it will cache the individual queries: http://dev.mysql.com/doc/mysql/en/query-cache.html That's not true. It won't cache even the parenthsized queries, and the execution time is still the same with SQL_CACHE and not. That's not true. Comment is wrong. Query cache works at the very low level, basically it caches the result of the query as it is sent to the client. Raw data on the wire (almost). So it can only cache the complete query, not a part of it. By the way, there's no need to measure query execution time, you can do 'SHOW STATUS' and watch Qcache% variables - just like I did in the test case above. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help me: Boolean fulltext searches, AND instead of OR
Hi!
On Mar 23, Jessica Svensson wrote:
Is there any way i can get results with AND instead of OR?
Trying to search for black cat should only return records that contains
both black and cat.
I'm using the following code to get my result:
SELECT * FROM `searchtbl` WHERE MATCH (text) AGAINST ('black cat' IN
BOOLEAN MODE);
sure there must be an easy way to change the default word separator to AND
instead of OR?
There is. See ft_boolean_syntax variable - it defines what characters is
used for each operator. In particular it defines '+' for must be
present and a space ' ' for optionally present words.
You want to put the space first (for must be present words)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX_JOIN_SIZE error. 4.0 v 4.1
Hi! On Mar 10, Brett Harvey wrote: I have a query that worked just fine in Mysql 3.x and 4.0. It no longer works in 4.1. I receive the The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay error My max_join_size is set at the default 4294967295. It appears that is also the max, correct? When I set it lower, it goes lower. When I set it higher it reverts to max_join_size=4294967295. I've simplified the fields being returned for this example. Many more fields are returned. Additionally, in this case, only one record is being found. However, in most cases, one hundred or so are being returned. What, if anything, can I do? As I mentioned, it worked fine in Mysql 3.x and 4.0. It's not functioning in Mysql 4.1 You can submit a bugreport at http://bugs.mysql.com/ (but please provide a *readable* EXPLAIN output, using mysql -t) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Useful Relevance with FullText Boolean Mode - help
Hi! On Mar 02, Thomas Spahni wrote: Hi Sergei, I have used FT search on a collection of ~ 38'000 documents totalling 550 MB of data since 4.0.2. To my observation people have a strong tendence to search for a few words they expect to be found close together in the text. As far as this is technically possible it would be helpful to give some extra relevance to those hits where the 'distance' between words is small. Tnanks, good idea. (I'll see how it could be done) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Useful Relevance with FullText Boolean Mode - help
Hi! On Feb 26, leegold wrote: Is there any way to make relevance when using boolean mode more useful? If not, are there plans in the future Fulltext development todo for making it useful? Current relevance formula is described in internals.texi (see mysqldoc repository on mysql.bkbits.net). Plans - yes, if the current one is bad. But I don't know what to put instead. Feel free to suggest a better weighting scheme :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT .. SELECT ... ON DUPLICATE KEY UPDATE
Hi! On Feb 24, Tom Cunningham wrote: It appears you can't combine an insert-select with an on-duplicate-key-update. You can, since 4.1.10 (and there're some problems with name resolution there, so better wait for 4.1.11 - search bugdb for details) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hi! On Feb 21, HMax wrote: So this means we cannot combine both FULLTEXT and classical indexes if we want to use a LOAD INDEX INTO CACHE, and that we won't ever be able to ? No. FULLTEXT indexes now have small block size (1024) so they should load ok. Only long varchar indexes will be a problem (but not 'ever', see below :) How about being able to specify the indexes we want to load into the cache. It's supposed to work this way (but it is told in the doc it doesn't yet). This would solve the problem I believe, if we specify what index we want in cache. Right, it's in the TODO. Here's the problem: LOAD INDEX reads the complete MYI file sequentially, block after a block, and loads them in cache. If blocks would have different sizes it would be not possible, because block header does not store block size. Loading only a selected index does not work either, because block header does not store what index it belongs to. The only solution would be to traverse the index tree from the root - but it'd be slow, because it implies random reads from the index file :( Instead, we plan to store index number in every block, but it means incompatible change in MYI file format, so it's not for 4.1 (and not even for 5.0 which is almost frozen now). What I don't undestand is that when not cached using LOAD INDEX INTO CACHE, FULLTEXT indexes can be into cache, the ones on VARCHAR too, and this does not see to cause any trouble. See above, regular btree traversal is not a problem. Sequential MYI file access is. But using LOAD INDEX, it doesn't work. Is there really no workaround ? We have for about 1.5Go of fulltext indexes and if they were in cache, this would speed up things so much ! It's fixed in 4.1.8. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD INDEX INTO CACHE problem
Hi! On Feb 21, HMax wrote: Thank you for your answer Sergei, It's all clear now, and I'm glad to know where the problem comes from. Now if I understand correctly, my only solution is to manage to create indexes in my tables that ALL have the same block size (1024). This would mean reducing the size of the indexes on my Varchar fields, which I think I can. What is the max characters I should use when indexing my Varchar so that block size are 1024 ? (if possible of course). I have no idea how to calculate this. You'd better try with trial-and-error. myisamchk -dvv shows block size. It's enough to create an empty table and run myisamchk -dvv on it. The formula is in mi_create.c but I would spend more time unrolling all the defines and deriving max varchar langth, that you would do with trial-and-error :) And is there absolutly no way to force 1024 block size even for varchar ? No, but you can make it 2048 for normal indexes. Block length is a multiple of myisam_block_size, so if you set it to 2048, all indexes will use it. (of course it'll be suboptimal for everything but the long varchar keys. And even if you have an index over VARCHAR(255), actual values are usually shorter, right ?) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: global object-id
Hi! On Feb 10, Konrad Kieling wrote: hi, is there a simple way (ie without creating and deleting datasets in an extra table) to use an auto_increment id field in several tables. no table has to have all ids, so i cannot use the field of a master table in childs. it seems one can use a sequence in postgresql in different tables. is it possible in a similar way? Try UUID() function Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Federated Engine
Hi! On Dec 20, Luciano Barcaro wrote: Hi all, I downloaded the source from bk tree and I saw the federated engine. Taking a look into documentation (http://dev.mysql.com/doc/mysql/en/index.html) I didn?t find anything. So, where I can find some info about it ? It was added ony a few days ago - documentation is catching up still. For now - take a look at the mysql-test/t/federated.test file. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL non-portable with Oracle, MS-SQL, Sybase SQL statements due to ISO non-conformant backslash
Hi! On Dec 16, Ken Johanson wrote: Hi all, Does anyone know fo a way to run the server in more of a string-quoting standards compliant mode, so that a generic SQL string quoting function only has to escape single quotes, instead of also '', '\', and '\0'? I've tried the ANSI_QUOTES startup option, but that apparently only changes the treatment of double quotes, and requiring single quotes for strings... '\' for example still needs a non-standard treatment. What I'd like to be able to do is have \,\\,\0 characters treated as any other so that statements become portable. MySQL 5.0 NO_BACKSLASH_ESCAPES sql mode. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL non-portable with Oracle, MS-SQL, Sybase SQL statements due to ISO non-conformant backslash
Hi! On Dec 17, Sergei Golubchik wrote: On Dec 16, Ken Johanson wrote: Hi all, Does anyone know fo a way to run the server in more of a string-quoting standards compliant mode, so that a generic SQL string quoting function only has to escape single quotes, instead of also '', '\', and '\0'? I've tried the ANSI_QUOTES startup option, but that apparently only changes the treatment of double quotes, and requiring single quotes for strings... '\' for example still needs a non-standard treatment. What I'd like to be able to do is have \,\\,\0 characters treated as any other so that statements become portable. MySQL 5.0 NO_BACKSLASH_ESCAPES sql mode. I see, you found it yourself already :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comparing bug in 4.1.7
Hi! On Dec 02, Vlad Shalnev wrote: Looks like a bug. Could you submit a bugreport at http://bugs.mysql.com ? It happens after upgrade from 3.23.46. mysql create table a ( a int not null ); Query OK, 0 rows affected (0.00 sec) mysql select min( a ) is null or null from a; +--+ | min( a ) is null or null | +--+ | NULL | - Why ??? +--+ 1 row in set (0.00 sec) It is very important for me to solve this problem. Thanks for any help Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 - 4.1.7 LEFT JOIN problem
Hi! On Nov 22, Santino wrote: At 15:23 +0100 22-11-2004, Sergei Golubchik wrote: Hi! On Nov 22, Dilipan Sebastiampillai wrote: I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query using LEFT JOIN gives me different result. The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs sometimes and I don't want that. I think it comes from an 'optimization' how can I make a query without using the LEFT JOIN optimization of MySQL 4.1.7 ? What is the query ? Can you provide a repeatable test case ? I have a query that works fine on 4.0.20 but doesn't work in 4.1.7. Thanks for the test case. I could repeat the bug using the 4.1.7 distribution, but not the latest 4.1.8 tree - it means that the bug was apparently fixed since 4.1.7 release. ==CUT CREATE TABLE AULE ( AUL_ID int(11) NOT NULL auto_increment, PRIMARY KEY (AUL_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE OCCUPAZIONI ( OCC_ID int(11) NOT NULL auto_increment, OCC_ID_AUL int(11) NOT NULL, OCC_DATA date, PRIMARY KEY (OCC_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO AULE VALUES (1); INSERT INTO AULE VALUES (2); INSERT INTO AULE VALUES (3); INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10'); select Before index; select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; alter table OCCUPAZIONI add KEY OCC_ID_AUL (OCC_ID_AUL); select After Index; select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; ==CUT Bug #6307 I noticed that when I create a table with 2 index (primary + key) the Cardinality of the primary key is 0 but the Cardinality of the key is null. If I insert a record in the table the Cardinality of the primary key is 1 but the Cardinality of the key is null. If I do an analyze TABLE ... the Cardinality of both index is OK and the query works but with a truncate table ... the Cardinality of the key is null. If I create the table without the second index, add some records and add the second index the cardinality is null. At the moment I found that if I define a composite primary key ( old PRIMARY+KEY) the query works without the analyze. I am not sure I understand :( What do you mean query works ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 - 4.1.7 LEFT JOIN problem
Hi!
On Nov 23, Dilipan Sebastiampillai wrote:
Hi!
On Nov 22, Dilipan Sebastiampillai wrote:
I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query
using LEFT JOIN gives me different result.
The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs
sometimes and I don't want that.
I think it comes from an 'optimization' how can I make a query
without using the LEFT JOIN optimization of MySQL 4.1.7 ?
What is the query ?
Can you provide a repeatable test case ?
the answer is amazingly wrong !
have a look :
mysql SELECT hosts.name,hosts.hostId, tries.hostId, tries.status FROM
hosts LEFT JOIN tries ON tries.hostId=hosts.hostId AND tries.status
IN('running','waitkill','preemption') LIMIT 20;
+-+++-+
| name| hostId | hostId | status |
+-+++-+
| chimp13 | 1530 | 1393 | running |
| chimp13 | 1530 | 1485 | running |
| chimp13 | 1530 | 1418 | running |
| chimp13 | 1530 | 1499 | running |
I agree that it doesn't look right.
But the query alone is not enough for me to repeat the bug.
I need also both tables hosts and tries.
If they are big, you may try to remove unrelated rows, or create a
completely independent test case. Actually you can even upload big
tables if you don't want to spend time on a test case.
But only with a repeatable test case you can make sure that the bug
won't be present in 4.1.8.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 - 4.1.7 LEFT JOIN problem
Hi! On Nov 22, Dilipan Sebastiampillai wrote: I moved a db from 4.0.18 to 4.1.7 and I realise that the SAME sql query using LEFT JOIN gives me different result. The result from 4.0.18 does s real LEFT JOIN but 4.1.7 differs sometimes and I don't want that. I think it comes from an 'optimization' how can I make a query without using the LEFT JOIN optimization of MySQL 4.1.7 ? What is the query ? Can you provide a repeatable test case ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security requirements, can I still use MySQL?
Hi! On Nov 18, Jonas Ladenfors wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. In MySQL there's no concept of root user. If you make sure that no user on the system has select privileges on the underlying tables, than nobody will be able to select from them. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext insert/update
Hi! On Nov 15, John Smith wrote: I know that having a fulltext index on a table slows inserts and updates down (or I think I do...might have miss read something) I have a large table that uses fulltext which I plan to update nearly 90% of the rows (400,00+) and add new ones. It currently takes around 35 minutes to remove the index and then another 35 minutes to re index it. I don't need to use the index until after I have finised with the updateing and run myisamchk -rq --sort-index --analyze --sort-records=7 on the .MYI file. Is it worth over an hour of adding and removing the fulltext? Its just that my code takes 4 hours to finish and I am reluctant to add on another hour! No need to remove the index. Use ALTER TABLE ... DISABLE KEYS (and ... ENABLE when you're done with update) Yes, it is worth to wait 35 minutes when the index will be rebuilt. Maintaining live fulltext index is much slower. (at least, inserting lots of rows into a table with fulltext index could be up to 100 times slower than adding an index afterwards). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
Hi! On Oct 06, Christopher L. Everett wrote: I've also found a problem with myisamchk --sort-keys: I recently fixed one bug in myisamchk --sort-keys where fulltext indexes are present. Try 4.1.7 release. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
Hi! On Oct 06, Christopher L. Everett wrote: I have an application where I create a faily large table (835MB) with a fulltext index. One of our development workstations and our production server will run the script to load the table, but afterwards we have a pervasive corruption, with out of range index index pointer errors. Oddly, my development workstation doesn't have those problems. My box and the ones having the problems have the following differences: - my box runs ReiserFS, the problem boxes run XFS - my box has a nice SCSI HD subsystem, the problem boxes do IDE. All three boxes run Linux 2.6.x kernels, and my workstation and production server share the same mobo. Come to think of it, I saw similar corruption issues under 2.4.x series kernels and MySQL v4.0.x, it just wasn't the show stopper it is now. Could you try to repeat the problem with the smaller dataset ? Create a repeatable test case for us ? Also, on all three boxes, altering the table to drop an index and create a new one requires a myisamchk -rq run afterwards when a fulltext index either exists or gets added or dropped, which I'd also call a bug. Sorry, I don't understand. Could you elaborate ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi! On Oct 21, Mads Kristensen wrote: *snip* Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei *snip* You are right, B+Trees are always balanced but When you insert in increasing order all your inserts will be to the last leaf of the B+tree. This means that you can get some concurrency problems when updating the index since it is always the same part of the index that needs to be locked. I'm not quite sure how MySQL does it locking, but if it locks only the index leafs that it is updating this kind of insertion will give poor performance compared to random insertion. MyISAM uses table-level locks, so it always lock the complete index for writes. On the other hand, always writting to the last leaf, you constantly touch only few nodes - on the path from the root to the last leaf. Thus these nodes/keypages will be always cached and the performance should be somewhat better as compared to random accesses (assuming the index is too big to fit in cache completely). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi! On Oct 21, Phil Bitis wrote: From: Sergei Golubchik [EMAIL PROTECTED] But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. Hi again, does the key page size differ depending on the type of the column (BIGINT, INT, etc)? Is there any way I can work out the key page size, or configure it? It differs. No, I don't think it can be influenced. It is chosen automatically based on key length - so that longer keys get larger keypages. On the other hand, if you're thinking about new storage engine (and you're in programming, compiling MySQL, etc), you can as well modify MyISAM code to have any keypage size you like. Search in mi_create.c for keydef-block_length=. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: B-tree index question
Hi! On Oct 23, Phil Bitis wrote: Hello, We want to be able to insert records into a table containing a billion records in a timely fashion. The table has one primary key, which I understand is implemented using B-trees, causing insertion to slow by log N. Corect. But for auto_increment field (on BIGINT, I believe ?), you'll have hundreds of keys on one key page, so logarithm base will be few hundreds, and log N should be just 3-5. That is, it should be only ~3-5 times slower as compared to the table with one hundred rows. The key field is an auto_increment field. The table is never joined to other tables. Is there any way we could implement the index ourselves, by modifying the MyISAM table handler perhaps? Or writing our own? Hmm, MyISAM can only do B-tree indexes. It won't be easy to add a completely different index algorithm to it. Writing your own table handler could be easier. In our setup record n is always the nth record that was inserted in the table, it would be nice to just skip n * recordsize to get to the record. Right, assuming all records have the same length, you can just write nth record at the offest n * recordsize on inserts, and use the value of n as a key on reads. Of course, it's a very specialized storage engine, not that much of general use - but it's very specialized to handle your case, so it can be the fastest solution. Also, could someone shed some light on how B-tree indexes work. Do they behave well when values passed in are sequential (1, 2, 3, ...) rather than random values? Yes. B-tree is always balanced: http://www.nist.gov/dads/HTML/btree.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table name aliases in FULLTEXT and table locking
Hi!
On Oct 05, Ville Mattila wrote:
Hi there,
I have noticed a few things that cause problems when using table aliases
(SELECT ... FROM table1 t1, table2 t2):
1) Fulltext index queries don't work. I tried to complete a following query:
a)
SELECT p.*, c.name AS categoryname FROM products p, categories c WHERE
MATCH(p.name,p.description) AGAINST('keywords');
It will cause an error that there is no FULLTEXT index matching the query.
Hmm, I wasn't able to repeat it.
Could you submit the complete test case at http://bugs.mysql.com/ ?
b)
SELECT products.*, categories.name AS categoryname FROM products,
categories WHERE MATCH(products.name,products.description)
AGAINST('keywords');
works anyway well.
2) Table name locking doesn't work with table aliases - or works, but
eatch alias must be locked separately and the final queries must contain
same aliases. Examples a and b cause both an error that tables are not
locked. Examples c and d work well.
Right.
This is how, according to the manual, LOCK TABLES should work.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
Hi! On Sep 14, Yves Goergen wrote: Hi, I can vaguely remember there was something like INSERT... on duplicate key UPDATE... in MySQL, but the documentation search is almost as useful as I'm used to - it cannot tell me anything about this. Can you please? How does this work, what's the syntax? In the manual: http://dev.mysql.com/doc/mysql/en/INSERT.html Is this ANSI-SQL standard ? No. Or is it even documented? Of course. http://dev.mysql.com/doc/mysql/en/INSERT.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT .. ON DUPLICATE KEY UPDATE behaviour
Hi! On Oct 15, Jason McManus wrote: Good afternoon, I have had reason to use the new (as of 4.1.1) INSERT .. ON DUPLICATE KEY UPDATE syntax in MySQL. However, I am a bit confused as to the return value. Issuing the INSERT .. ON DUP KEY UP statement, upon finding a duplicate key and updating that record, mysql-client returns 2 rows affected; upon inserting a unique row, it returns the expected 1 row affected. I have theorized that the 1st row is referring to a row that temporarily existed during the insert, then was deleted, and the 2nd row is the row that was updated (or vice versa), but this is fairly dependent upon how this statement was implemented; normally a key constraint shouldn't actually succeed and should return an error before modifying the table (i.e. 0 rows affected). Thus I would like to address the developers and inquire about this particular behaviour. See http://bugs.mysql.com/bug.php?id=2709. The reasoning is that the current behaviour is more useful, as it gives more information about what the statement did (see the url above for the example). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.4a-gamma in production?
Hi! On Sep 13, Marten Lehmann wrote: Hello, 4.1 offers some interesting new features like more secure passwords and subqueries. However, this release is still labeled a-gamma; on the other hand it shall be used for future development. What's that a for (usually for alpha) if it's also stated gamma? a doesn't mean alpha. It's a bugfix release for 4.1.4 - we didn't call it 4.1.5 because only source distribution is affected (some complilation issues after we upgraded libedit library, that is used for mysql command line client), all gpl binaries are linked with readline and are not affected by this problem. That's why there're no 4.1.4a binaries, only 4.1.4a source tarball. Is gamme the last step until the 4.1.4 will be released? Yes. Or will rc's follow before? No. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL with Intel Compiler
Hi! On Aug 24, Santhanam wrote: Dear Friends, We want to use MySQL compiled with Intel Compiler to get superior performance. Our server is a rack mounted HP DL 380 server with Redhat Enterprise Linux Advanced Server 3.0. We have used mysql-standard-4.1.3-beta-pc-linux-i686-icc.tar.gz. But we are getting the following error : --- [EMAIL PROTECTED] mysql]# scripts/mysql_install_db --user=mysql --force Installing all prepared tables Fatal error: Can't change to run as user 'mysql' ; Please check that the user exists! 040824 17:29:49 Aborting 040824 17:29:49 ./bin/mysqld: Shutdown complete http://bugs.mysql.com/bug.php?id=4408 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug #3933
Hi! On Aug 21, Robert Nagy wrote: Hi. Can u point me to the fixed file(s) please? Or can u send me the diff if you have it? http://bugs.mysql.com/bug.php?id=3933 Roger, it is explained my reply to the bugreport, and in my reply to you. The bug is fixed in 4.0.21. According to the manual http://dev.mysql.com/doc/mysql/en/News-4.0.x.html and to our download site http://dev.mysql.com/downloads/mysql/4.0.html the last released version in 4.0.20. If you want to get the bugfix now, you have to follow the link that is mentioned in bugreport and my first reply http://www.mysql.com/doc/en/Installing_source_tree.html Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug #3933
Hi! On Aug 22, Robert Nagy wrote: Stardate [040822 00:26]. Sergei Golubchik of Borg wrote: Roger, it is explained my reply to the bugreport, and in my reply to you. Thanks I can read. But the question was not that. http://www.mysql.com/doc/en/Installing_source_tree.html I'd like to see the diff between the two revisions. But I couldn't find any info in connection with #3933 in the ChangSet. So can u please point me to a *diff*. Ah, ok. Here it is: http://mysql.bkbits.net:8080/mysql-4.0/[EMAIL PROTECTED] Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 10, Jeremy Zawodny wrote: On Wed, Aug 04, 2004 at 04:32:42PM +0300, Egor Egorov wrote: Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). Wouldn't it make more sense to use fsync() on just the .frm file? Or am I missing something here? Nope, you are right. MySQL does fsync(), not sync(). It was my mistake - I didn't know all these flavours of syncs so I wrote sync() in my email :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 04, Egor Egorov wrote: Sergei Golubchik [EMAIL PROTECTED] wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. Yes. Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). And note that the sync() call not only physically writes .frm file to disk, but also everything else which is in write cache. If the server is under load, sync() call may take seconds, tens of seconds or even hundreds of seconds. As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Time to add a NO_SYNC option to CREATE TABLE, Sergei ? :) There is --skip-sync-frm option. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE performance degradation from 4.0.17 - 4.0.20
Hi! On Aug 02, Tinley, Jeremy wrote: We're upgrading from 3.23.58 to 4.0.20 and found that that although the ALTER test results of sql-bench had been greatly improved, CREATE has shown nasty performance degradation. Just before needing to make the decision to revert back to 3.23.58, we found a post here where someone had a similar problem when using SAN storage. We see the problem using hardware RAID, shared storage or local SCSI disks. Yes. Since 4.0.17 MySQL sync()'s after it created an .frm file (in CREATE/ALTER TABLE). As one usually doesn't create tables at the huge rate, it is not a problem. Unfortunately, it is apparently a problem for sql-bench :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug-Report: mysqld 4.1.3 crashes on startup
Hi! On Aug 01, Helge Jung wrote: Description: When I start up my fresh compiled mysqld it crashes immediately, the error log file says: It was reported just a few hours ago at bugs.mysql.com (which is the recommended way to report bugs, by the way :) you may follow the progress using http://bugs.mysql.com/4844 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB 4.1.3: count(*) and number of rows does not match
Hi! On Aug 01, Keith Thompson wrote: Thanks Mike, I've always ignored CHECK TABLE because I always thought it was just for MyISAM. Then, I decided to run CHECK TABLE on all my tables (which for the ones with 125 million rows will probably be running for a while). The problem now is that all of my larger tables are reported as being corrupt--every single table with more than say 500,000 records is reported as corrupt. Wow! Could this be true? The tables all access fine and only these two smaller tables had these count(*) mismatch problems (and were the only two smaller tables that came up corrupt). How did this happen? I've never gotten an error in my .err file, never had a hardware access failure in the system logs, and have done very little with this server beyond initially loading it (by replaying mysqldump output in the first place) and letting it stay up to date with replication. Just a thought - if you upgraded, be sure to read all changelog entries carefully, there were few bugfixes that would require to dump/reload innodb tables (otherwise they'll be corrupted). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLcheck-scramble vulerability info
Hi! On Jul 28, Ben Ricker wrote: Our security guy ran across an exploit in all MySQL versions before the June releases. I am trying to find information on how to patch to fix this vulnerability but I cannot find anything on MySQL's site! It is kind of ridiculous. I searched for check_scramble, zero-length comparison and even vuneralibility (the latter only had 7 hits). Has anyone seen better information on how to patch 4.0.18 to fix this bug? Check out http://securitytracker.com/alerts/2004/Jul/1010645.html for more information. Did you notice at the above url: Version(s): 4.1 prior to version 4.1.3; also version 5.0 4.0.18 is NOT vunerable. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CIDR ranges in MySQL permissions?
Hi! On Jul 22, Andrew Braithwaite wrote: Hi All, Can I assume by the lack of any responses that the anwser to my question is no? The answer is yes: http://dev.mysql.com/doc/mysql/en/Connection_access.html Cheers, Andrew -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Tuesday 20 July 2004 16:44 To: [EMAIL PROTECTED] Subject: RE: CIDR ranges in MySQL permissions? Sorry - a /32 is a single ip - I meant a /27 :) A -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Tuesday 20 July 2004 16:16 To: [EMAIL PROTECTED] Cc: Karl Skidmore Subject: CIDR ranges in MySQL permissions? Hi All, Has anyone had any experience with using IP address ranges in MySQL permissions? It would be easy if you had a whole class C for example because you would be able to do: Grant all privileges on *.* to someuser@'192.87.12.%'; But if you only wanted to give permissions to a CIDR range (e.g. a /32, 192.87.12.1 to 182.87.12.32 etc..) in one go, and used the above method, it would leave your databases open to connection from others. Is there any way to do this in a single line (without having an entry for each IP address) ? Hope you can help. Regards, Andrew Mysql, query -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'x' on blob field when retrieving records
Hi!
On Jul 13, [EMAIL PROTECTED] wrote:
I have a query like this
select hex(blob_field) as myField from table
When the blob field is empty ('') it returns 'x'.
i'm using ADO with Visual Basic and MyODBC 3.51.
MySQL Version is 4.0.18 and SO is Win XP Pro (also happens on Windows 2K)
If you can provide a repeatable test case, please submit a bugreport at
bugs.mysql.com
So far I was not able to repeat it:
mysql create table a (b blob);
Query OK, 0 rows affected (0.01 sec)
mysql insert a values (NULL), (''), (0), ('aaa');
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql select hex(b) from a;
++
| hex(b) |
++
| NULL |
||
| 30 |
| 616161 |
++
4 rows in set (0.01 sec)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: 4.1 performance
Hi! On Jul 14, Hickey,Thom wrote: I was able to rerun my tests using mysqld (as opposed to safe_mysqld). I'm happy to report that the times now almost exactly match MySQL 3.23.58. It's VERY strange. It cannot have any affect on the speed. Are you sure you run the correct version of mysqld ? That is, I can suspect that either safe_mysqld used wrong .my.cnf or run wrong mysqld, or when you run mysqld directly you used wrong mysqld or it used wrong my.cnf. I could start all your tests with SELECT VERSION(); SHOW VARIABLES; - to be sure you are using correct mysqld and correct my.cnf. By the way, if you want to disable query cache you can turn it of with a command line switch :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 performance
Hi! On Jul 14, Lachlan Mulcahy wrote: Sergei, Thom.. I am interested in seeing this thread followed through. As developers at my work have experienced similar performance issues between 3.23.x and 4. Our database is also of similar size and a full optimize has been run. Could you provide a repeatable test case ? (if yes, you can submit it on bugs.mysql.com, instead of replying here - and be sure it will get a proper attention) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 performance
Hi! On Jul 12, Hickey,Thom wrote: I've been comparing the performance of 4.1 with the MySQL 3.23.58 that came with our Rocks cluster software. I'm finding that 4.1 is running approximately 1/3 slower (e.g. a 0.075 second query goes to 0.100 seconds) than 3.23.58. Size of buffers, etc. seems to have little effect. The database is fairly large with about 3 gig spread over a half-dozen tables. The largest table has 62 million rows. What kind of queires do you run ? What does EXPLAIN show ? Are all tables ANALYZE'd ? 4.1 can use more complex query transformations and join methods that should bring more performance. Of course if the statistical data are incorrect, the optimizer cannot jugde what is more and what it less, so new features may expose problems that were hidden in the old version. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in fulltext index creation on very huge sets of data?
Hi! On Jul 06, Vincent Bouret wrote: Hi, I got the following values: key_buffer_size = 256M myisam max extra sort file size = 8000M myisam max sort file size = 8000M myisam sort buffer size = 128M But that big table (MYD = 2397 MB), rows = 5 355 866 still won't index in full text. Disk space is enough (25GB of free space), no error reported in the log. Using MySQL 4.0.14, doesn't work better with 4.0.20 (running under Win32). Where should I look at? probably myisam_max_sort_file_size is too small for 2GB table. read the manual about this variable, and increase accordingly. check show processlist during indexing Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial not in current 4.1.3 build?
Hi! On Jul 06, Barend K?bben wrote: It seems the Spatial Extension is not in the 4.1.3 binary (for Win)...? You're right :( Sorry for this. We recently made GIS features optional, but ON by default. Unfortunately, this default is the value of ./configure switch and does not apply to Windows. You may consider it a bug, and we will correct it ASAP. The lack of the extensive test suite that works on Windows didn't let us to notice it before the release (we're porting our test suite to Windows, so hopefully slips like that will never happen again). This is frustating, as I am a GIS/Spatial database developer using until now succesfully the earlier alpha in my projects. I am not a programmer and certainly do not want to go into building my own binaries. Why this exclusion and why is it not mentioned clearly anywhere? It was not intentional. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair Table and USE_FRM
Hi! On Jun 25, Dan Cumpian wrote: Is there any harm in using the USE_FRM directive by default whenever repairing a table? Yes. See http://bugs.mysql.com/4252 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Extending mySQL
Hi! On May 25, Yiannis Mavroukakis wrote: Hello, I'm hoping this is the correct list to post my question ( I was also considering mysql internals), if not accept my apologies. Yes, internals@ would be better for these kind of questions. I wish to write a mySQL extension, that will do some filtering on all data before it is send out to the requesting clients. Is there an API that will allow me to do this for all outgoing data from the DB? PROCEDURE. See Adding New Procedures to MySQL in the manual. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GRANT temporarily removes passwods until FLUSH
Hi! On May 27, [EMAIL PROTECTED] wrote: Description: Using the GRANT command causes the password to be deleted until 'FLUSH PRIVILEGES' is executed. This is a security problem. Search the bugdb - http://bugs.mysql.com/ the bug is reported there and is already fixed. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with MySQL 4.0.20
Hi! On May 25, Steven Roussey wrote: We had some servers that were upgraded from 4.0.17/18 to 4.0.20 and had several problems thereafter: 1. Tables with FTS indices became corrupted, with queries on them causing segfaults on the servers. Hmm, I don't see any changes in ft-related files since 4.0.18 that could cause it (there were bugfixes, but they affect only *searching* - that is MATCH - and not *updating*). Can you create a test case ? 2. BinLog files were getting created with ownership of root, not mysql. Then Mysql complains that it can not read the file and so goes and creates another (which is fine and owned by mysql). All slaves to the master then die with corruption warnings about the master. I don't really understand how it can happen - I'll let others comment on it. 3. All servers suddenly have a lot of connection errors: Aborted connection 109 to db: 'xyz' user: 'aaa' host: `something.i' (Got timeout reading communication packets) I think, this is because --log-warnings was changed to be ON by default. Disable with --skip-log-warnings 4. Thread stack warnings: Warning: Asked for 196608 thread stack, but got 126976 Same here. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
Hi! On May 12, Larry Lowry wrote: A uniqueidentifier in MS SQL is basically a guid. I am generating them via System.Guid.NewGuid().ToString(N) in the Dot Net framework which now returns me a string of 32 characters (hex). Internally I understand it is a 128-bit integer. As an option I could store that in MySql. What data type would that be? How would one convert to binary char(16)? In what documentation would I find this? I do not know enough yet to write a UDF. In 4.1.2 you can use function UNHEX(): UNHEX(REPLACE(uuid, '-', '')) will convert uuid to 16-byte string There's no easy way to do a reverse conversion yet. HEX() will do, but you'll lose dashes. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Hi! On May 11, Nico Sabbi wrote: Alle Tuesday 11 May 2004 15:33, Nico Sabbi ha scritto: Alle Tuesday 11 May 2004 15:00, Egor Egorov ha scritto: Nico Sabbi [EMAIL PROTECTED] wrote: Hi, as the title says mysqldump 4.0.18 (and previous versions) doesn't want to dump data in the format insert into db.table values() not even using -e or -a. Is there any other cli switch that can do this? No, but if you use -B option of mysqldump USE db_name statements will be included in the output. I see, but this creates me a serious problem: I usually replicate my databases between a number of mysqld servers; when I want to copy a database db plus some additional metadata from the local server to the master I usually run mysqldump -B db -h local | mysql -h master that works correctly on the new_server, but totally messes up the current slaves that are configured to replicate-wild-do-table=db.% because there's no db.table syntax, so the slaves discard the insert. Maybe adding replicate-do-db=db (for all of my dbs) will do the trick? sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() First - sorry for confusion, according to http://dev.mysql.com/doc/mysql/en/Replication_Options.html replicate-do-db is not expected to do anything if you have replicate-wild-do-table. So you are right - it does not work as you expected. But replicate-wild-do-table should work, no matter whether you use insert into db.table values() or use db; insert into table values() Could you provide a repeatable test case to show that replicate-wild-do-table does not work ? If yes - please submit it at http://bugs.mysql.com/ Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I make index block size all the same?
Hi! On Apr 08, Haitao Jiang wrote: Why Mysql load index ... into cache require the same size index block when it creates indexes in different block sizes (1k or 2k)? I don't understand. It's limitation of the current implementation :( It reads MYI file successively by fixed chunks (multiple of block size) and inserts blocks into the keycache. It's much faster than traversing index tree. Buf if different indexes have different block sizes, this method does not work, as a block has no header and there is no way to find what its size and where the next block begins. In the future LOAD INDEX ... will be extended to allow preloading only some indexes, and not all the file. Then it will be possible to load indexes of different block lengths. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP with UTF-8
Hi! On Apr 07, Hirofumi Fujiwara wrote: From: Sergei Golubchik [EMAIL PROTECTED] Hi! On Apr 06, Hirofumi Fujiwara wrote: I am testing regular expression feature of MYSQL 4.1.1-alpha (REGEXP) with UTF-8 characters (Japanese). So far it doesn't seem to work. LIKE handles each Japanese character properly as one character, but REGEXP doesn't. I'd like to know if REGEXP handles UTF-8 characters. No it does not :( (we need to change regexp library that we use, to have it fixed) I wonder if REGEXP doesn't handle multi bytes character(big5,ujis, sjis,euckr,gb2312,gbk,utf8,ucs2) either, which means it handles only single byte character set (Maxlen=1). Unfortunately, it does not either :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP with UTF-8
Hi! On Apr 06, Hirofumi Fujiwara wrote: I am testing regular expression feature of MYSQL 4.1.1-alpha (REGEXP) with UTF-8 characters (Japanese). So far it doesn't seem to work. LIKE handles each Japanese character properly as one character, but REGEXP doesn't. I'd like to know if REGEXP handles UTF-8 characters. No it does not :( (we need to change regexp library that we use, to have it fixed) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT query format question
Hi!
On Mar 21, Shane Allen wrote:
I've read through the boolean mode fulltext docs, and they address all
my questions well except how searches containing exact phrases are
handled when there is more than one. I believe the following will work
as I expect, but was wondering if anyone can confirm it for me:
Given the following search strings, are the following MATCH AGAINST
(BOOLEAN) going to operate as the search strings suggest?
Based on a discussion with a coworker, we've determined in the old
search strings that OR has a higher precedence than AND (the goal of the
first being either ambulatory pediatrics or ambulatory obgyn, for
example)
obgyn OR pediatrics AND ambulatory:
MATCH (jobdescription) AGAINST('+(obgyn pediatrics) +(ambulatory)'
IN BOOLEAN MODE)
Yes. It could be simplified to
'+(obgyn pediatrics) +ambulatory'
Training AND Documentation OR Technical Writer:
MATCH (jobdescription) AGAINST('+(Training) +(Documentation
Technical Writer)' IN BOOLEAN MODE)
Correct. Or
'+Training +(Documentation Technical Writer)'
art OR graphic OR publishing:
MATCH (jobdescription) AGAINST('+(art graphic publishing)' IN
BOOLEAN MODE)
Yes. Or simply
'art graphic publishing'
web design AND web development AND webmaster:
MATCH (jobdescription) AGAINST('+(web design) +(web development)
+(webmaster)' IN BOOLEAN MODE)
Yes. Or
'+web design +web development +webmaster'
If you find that some of the above doesn't work as expected -
report at http://bugs.mysql.com :)
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text with JOIN
Hi! On Mar 21, Lorderon wrote: Hi, I also found that when you use JOIN with full-text, MySQL don't automatically sort the results by the coefficient of the full-text... when you use list of tables seperated by comma MySQL sorts it correctly... What does EXPLAIN say in each case ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text with JOIN
Hi! On Mar 20, Lorderon wrote: I have 3 tables to join when the last one is a Full-Text table (ft_table).. I do the next join: SELECT id,title FROM table1 LEFT JOIN table2 USING (id) INNER JOIN ft_table USING (id) WHERE ... But MySQL selects the primary key (id) to join the ft_table, which makes the query run a lot of time and gives wrong results according to the MATCH AGAINST search.. Please provide a complete repeatable test case for this. I found that making the join as this: SELECT id,title FROM table1,table2,ft_table WHERE table1.id=table2.id AND table2.id=ft_table.id AND ... gives the wanted results according to MATCH AGAINST, but leave out rows that don't exist in table2 (the join there was LEFT JOIN).. 1- Is there a way to join the full-text table and using the full-text index, so the query will not last long? You may use USE INDEX / IGNORE INDEX in the FROM clause (see the manual) 2- Is there a way to make something like LEFT JOIN using list of tables seperated by comma (table1,table2,..)? no. 3- Is there a performance difference between making INNER JOIN or by making list of tables seperated by comma (table1,table2,..) with using WHERE clause? no. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The index handles accented UTF-8 chars badly
Hi! On Mar 05, Rikard Bogren wrote: I have a problem with index on a UTF-8 based db/table and mySQL 4.1 (I have tried 5.0 too). I run the mysqld with the --default-character-set=utf8 flag and I'm certain that it's UTF-8 data that I'm putting into the database. To reproduce, follow these simple steps. #--- CREATE DATABASE `database` DEFAULT CHARACTER SET utf8; CREATE TABLE `table` ( `id` VARCHAR( 10 ) NOT NULL , `name` VARCHAR( 10 ) NOT NULL ) CHARACTER SET = utf8; ALTER TABLE `table` ADD INDEX ( `name` ) INSERT INTO `table` ( `id` , `name` ) VALUES ( '1', 'Rene' ); INSERT INTO `table` ( `id` , `name` ) VALUES ( '2', 'Ren?' Don't mind question mark, I know what was there and I used the proper character what I tested it :) ); # Now make some queries... SELECT * FROM `table` WHERE `name` = 'Rene'; SELECT * FROM `table` WHERE `name` = 'Ren?'; # These previous two lines work equally, which I reckon is wrong. This is how utf8_general_ci collation in MySQL is defined. In 4.1.2 or 4.1.3 we'll support more unicode collations, so you'll be able to chose how accents should be treated. SELECT * FROM `table` WHERE `name` LIKE 'Rene'; # Seems to work correctly. SELECT * FROM `table` WHERE `name` LIKE 'Ren?'; # This previous line gives no result, and I've understood it is because of how Rene and Ren? are somehow treated as equal. Works for me - returns 'Ren?' Apparently it's fixed already in 4.1.2 (to be out soon) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Iterating thru FullText Index
Hi! On Mar 05, Todd Burke wrote: Is there any way to iterate thru the keys in a fulltext index? This issue does not come up for other indexes since ORDER BY can achieve this: For example if table t is indexed on part_id SELECT DISTINCT part_id FROM t ORDER by part_id; will return all keys in index order Is there a way to do this for a fulltext index so that a query would return a list of all words in the index - this could be helpful to build dictionaries for ex. No, but you can use an external utility myisam_ftdump to get this functionality. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt full text index
Hi! On Mar 04, [EMAIL PROTECTED] wrote: Description: When updating a table with a fulltext index, the fulltext index becomes corrupted. The Error ERROR 1034 at line 76: Incorrect key file for table: 'test'; try to repair it is dislpayed. The error is not produced when there is no fulltext index. How-To-Repeat: Download sql script from http://www.kirkpatrick.me.uk/bug.sql.tar.gz Cannot repeat on 4.1.2 (current from bk). It should be out soon, try your query after upgrade. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange bug(?) with Phrase search in BOOLEAN MODE
Hi!
On Mar 01, William Au wrote:
Sergei, but this is a phrase search. The original query is:
match (keywords) against ('16-bit Touch' IN BOOLEAN MODE)
So shouldn't all words that are actually searched on be present
in a particular order?
Ok, sorry.
If the original query is '16-bit Touch' then it would be executed
functionally similar to
MATCH keywords AGAINST ('Touch' IN BOOLEAN MODE)
AND keywords LIKE '%16-bit Touch%'
Sergei Golubchik wrote:
Hi!
On Feb 27, Haitao Jiang wrote:
Thanks! That was what I guessed. But how to explain
16-bit Touch doesn't match records with 32-bit
Touch in the keywords? It just returned all the
records with 16-bit Touch, i.e. 16-bit seems does
count.
Because the presense of 16-bit substring is verified as a
post-processing. Similar to
MATCH ... AGAINST ('touch') AND ... LIKE '%16-bit%'
and if you'd have min_word_len=3 the query would be executed
similar to
MATCH ... AGAINST ('+touch +bit') AND ... LIKE '%16-bit%'
that is rows would be selected based on an index lookup for two words,
and as a post-processing a substring search would filter out rows
without 16-bit substring.
Regards,
Sergei
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Feature request related to COMPRESS and UNCOMPRESS functions
Hi! On Feb 27, Lester Hightower wrote: To whom it may concern at Mysql AB: I see from the Mysql 4.1.1 CHANGELOG that new COMPRESS(), UNCOMPRESS(), and UNCOMPRESSED_LENGTH() functions were added. That is great news, and something I have been very interested in for a long time, as evidenced by this mysql mailing list thread, dating back to 12/18/2001: http://marc.10east.com/?t=10086980305r=1w=2 The MARC system (marc.10east.com) was one of the primary reasons for me requesting that this feature be added to Mysql. There is one short-coming in the new COMPRESS()/UNCOMPRESS() functionality that I would like to point out, and request that you address. Here is the background: Anyone that is running a huge system like MARC that has millions of uncompressed blob records in huge tables, needs to be able to migrate, in real-time and without down-time, to compressed blobs. Therefore, we need a way to know if a given field is compressed or not. Running alter table on our tables, to add an am_i_compressed boolean, not only takes an excruciating amount of time and resources, but adds bits to each record that, from a disk-space perspective, we cannot afford. Instead, I would like to be able to run a query like: select IFCOMPRESSED(msg_body, UNCOMPRESS(msg_body), msg_body) from msg_bodies_200402 where clause Note that the IFCOMPRESSED() function is the key, and what I am requesting be added to future versions of Mysql. That function has to be possible. Hopefully you are storing a header with your compressed data, and if so, then the IFCOMPRESSED() is trivial to implement. If you are not storing a header with your compressed data, then this might be more complicated. Adding a header might be a possibility -- which is why I am trying to point this out _EARLY_ in the process before lots of people start using COMPRESS()/UNCOMPRESS(), or maybe zlib, lzo, or whatever library you are using can let you know if the data is compressed -- maybe they store a small header themselves. Anyway, that is the issue that I want to point out and ask for assistance on. Thank you very much for listening to the user community and adding COMPRESS()/UNCOMPRESS(), and please seriously consider this request. There is a header - but it only stores the length of the uncompressed data. And technically, no header can guarantee that the data are compressed. The check you can use is something like 1. test that UNCOMPRESSED_LENGTH returns something realistic, as you know how large a blob in your table can be - it should catch most of the uncompressed rows. 2. try to uncompress the rest - zlib puts crc in the compressed stream, and UNCOMPRESS returns NULL if uncompression fails. so the query could be something like select IF(UNCOMPRESSED_LENGTH(msg_body) 102400, msg_body, IFNULL(UNCOMPRESS(msg_body), msg_body)) ... Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange bug(?) with Phrase search in BOOLEAN MODE
Hi!
On Feb 27, Haitao Jiang wrote:
Thanks! That was what I guessed. But how to explain
16-bit Touch doesn't match records with 32-bit
Touch in the keywords? It just returned all the
records with 16-bit Touch, i.e. 16-bit seems does
count.
Because the presense of 16-bit substring is verified as a
post-processing. Similar to
MATCH ... AGAINST ('touch') AND ... LIKE '%16-bit%'
and if you'd have min_word_len=3 the query would be executed
similar to
MATCH ... AGAINST ('+touch +bit') AND ... LIKE '%16-bit%'
that is rows would be selected based on an index lookup for two words,
and as a post-processing a substring search would filter out rows
without 16-bit substring.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug on MYSQL-5.0 in statement OPTIMIZE TABLE
Hi! On Feb 23, Gelu Gogancea wrote: Hi, TABLE CREATE DESCRIPTION : CREATE TABLE T1(IDAP INT(16),TVAL INT(2),CH_VAL_SEC INT(16),INDEX(IDAP),INDEX(TVAL),INDEX(CH_VAL_SEC)); RUNNING CONDITIONS: -use persistent connection. What do you mean persistent connection ? Persistent PHP connections ? RUNNING SEQUENCE: 1-Open 1-st connection. 2-insert in table many values at once time with delayed option; insert delayed into T1 (IDAP,TVAL,CH_VAL_SEC) VALUES(1,-2,100),(2,-3,101),(3,-4,102); 3-I let 1-st connection open.Open 2-nd connection to execute the next 2 statement. 4-delete all data from table : delete from T1; 5-optimize the table : optimize table T1; MySQL daemon crash on this last statement if i use PERSISTENT CONNECTION, otherwise is OK. It doesn't crash for me when I use mysql command line client. What MySQL client do you use - PHP ? From the mysql daemon log: 040223 14:59:49 mysqld started 040223 14:59:50 InnoDB: Started; log sequence number 0 43634 040223 14:59:50 /mysql/mysql-5.0.bin/libexec/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 040223 14:59:50 Found 4.1 style password for user '[EMAIL PROTECTED]'. Ignoring user. You should change password for this user. /mysql/mysql-5.0.bin/libexec/mysqld: ready for connections. Version: '5.0.0-alpha' socket: '/mysql/mydatabase/mysql.sock' port: 3306 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=8388600 read_buffer_size=131072 max_used_connections=2 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x85b00a8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x41da4ee4, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x813c7c4 0x4004e618 0x40049dea 0x4202726c 0x40049dea 0x8185301 0x400492b6 0x420de407 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Could you please post a resolved stack trace as suggests the url above ? Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8584920 = O.H..B. thd-thread_id=4 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040223 15:01:37 mysqld ended Thank you! Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.1 Performance
Hi! On Feb 18, Brian Wintz wrote: I have begun working with MySQL 4.1.1 using the utf8 encoding to capture unicode data. I converted an existing MySQL 4.0 (latin1) database by doing a dump and load (with the new databases character set to utf8). I'm noticing that the performance on the new 4.1 database is about 5 times slower. Is this to be expected? If so, is there a plan to address this issue? No, I never heard about this problem. If you are currently not aware of this issue I am willing to try and put together a sample populated database and query that illustrates the problem. Please let me know if you would like me to do this. Yes, please do! Then, please, submit a bugreport at http://bugs.mysql.com/ Thank you for your help! Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tmpdir round-robin algorithm?
Hi! On Feb 17, Jeremy Zawodny wrote: On Tue, Feb 17, 2004 at 01:18:45PM -0800, lee wrote: We are trying to do some capacity planning, and we need to know how MySQL allocates the the filespace in the directories set by the tmpdir configuration parameter. The manual simply states: ...It may be useful if your default /tmp directory resides on a partition that is too small to hold temporary tables. Starting from MySQL 4.1, this option accepts several paths that are used in round-robin fashion. How is the work spread around amongst the different tmpdir 'segments' ( the directories enumerated by ':' in the tmpdir config. variable)? The manual says the directories are used in a round-robin fashion. I believe it's the first. Each new need for a temp file picks from the round-robin list. Yes, it's how it works. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ft_boolean_default
Hi! On Feb 13, Heath, Brad wrote: Is this a legitimate variable? It seems like it'd be useful, but when I tried it (on 4.0.12-nt and 4.0.17-nt) it didn't work. Anybody know what's going on? When it was added there was not possible to have a --server_variable that takes a string as a value. Now it's possible, but 4.0 is stable, so I cannot make ft_boolean_syntax a changeable variable there. But I just pushed this to 4.1. Should be in 4.1.2 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What does this error mean: Duplicate entry '273' for key 1
Hi! On Feb 05, Daniel R. Anderson wrote: I got the following error on a perl script: Duplicate entry '273' for key 1 I googled for it and found a thread telling me to DROP the table and re CREATE it. So I did, and my script is working fine. My question: why did this happen, why did dropping and recreating work, and what can I do in the future if this happens and I've got things in my database I don't want to DROP? The error means: CREATE TABLE test (a int primary key); insert test values (273); insert test values (273); If you got it for any other reason (which seems to be the case, as DROP+recreate helped) the answers would be: why did this happen - because you hit a bug (probably) what can I do in the future if this happens - submit a bugreport, including a repeatable test case on http:/bugs.mysql.com. This way you can make sure it won't happen for the third time :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [BUG] SHOW INDEX bug for fulltext indexes in MySQl 4.0.17
Hi! On Jan 23, Dave Rolsky wrote: Here's a recipe: create table foo (foo text, bar text); create fulltext index foo on foo (foo, bar); mysql show index from foo; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | foo | 1 | foo |1 | foo | A | NULL |1 | NULL | YES | FULLTEXT | | | foo | 1 | foo |2 | bar | A | NULL |1 | NULL | YES | FULLTEXT | | +---++--+--+-+---+-+--++--++-+ Sub_part should be NULL for both of these columns. The same thing happens for a single column fulltext index. Fixed. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crash on FreeBSD-Alpha (64 Bit)
Hi! On Jan 12, Holm Tiffe wrote: Description: mysqld 4.0.17 crash on FreeBSD 5.1-current-alpha How-To-Repeat: Any acces over IP (not domain socket) crashes mysqld: #/usr/local/bin/mysqladmin: connect to server at 'install' failed error: 'Lost connection to MySQL server during query' syslog: install mysqld[78066]: warning: can't get client address: Bad file descriptor All my tests shows that it crashes in libwrap. If I compile mysqld without libwrap it doesn't crash. Sorry, but it makes it a low-priority issue that I can probably look at later. As a workaround just compile MySQL without libwrap. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query the data of a fulltext index directly from index?
Hi! On Feb 02, Matt W wrote: Sergei, Any chance of getting a ft_dump Windows binary in the distribution? :-) Chances are good :) It was added to rpms and binary unix distributions 5 min ago, and it should be added to windows distro too. Note - the new name is myisam_ftdump. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query the data of a fulltext index directly from index?
Hi! On Feb 02, Alexander Bauer wrote: Hello, is there any way to get the fulltext index contents directly? I'm looking for a way to list all indexed words from a column to provide a filter selection. How can I access the index data without walking through all table rows, get the column and tokenize and collect words? Use the ft_dump utility program that comes from MySQL source distribution. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizer: GROUP BY unnessesary slow using ANSI notation for redundant group by's.
Hi! On Jan 24, Stefan Traby wrote: Hi ! For my forum system, I use the following query to generate the main-overview: (using 4.0.16-log) select f1.id as BoardId, f1.name as Board, f1.more as BoardDesc, f2.id as AreaId, f2.name as Area, f2.more as AreaDesc, count(distinct f3.id) as ThemenCount, count(distinct m1.ctime) as MessageCount, max(m1.ctime) as LastMessageStamp from forum as f1, forum as f2 left join forum as f3 on (f3.rid = f2.id) left join forum_msg as m1 on (m1.fid = f3.id) where f1.rid = 0 and f2.rid = f1.id group by AreaId -- note ANSI: group by AreaId, Area, AreaDesc order by BoardId, AreaId; ANSI requires to use group by AreaId, Area, AreaDesc instead of group by AreaId (which is a documented MySQL shortcut against this redundancy) but the ANSI notation is ~4 times slower. This performance penalty is really unnessesary because the optimizer could detect this kind of redundancy in many cases, especially this simple case because group by f2.id generates clearly the same results as group by f2.id, f2.name, f2.more does. Yes, you are right. But though MySQL doesn't have this optimization, I still cannot understand where this ~4 came from. How big are your tables (rows and bytes) ? What are typical values for ThemenCount and MessageCount in the result of this query ? Can you provide the results of SHOW CREATE TABLE for forum and forum_msg ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2nd Request Repair Table Hung?
Hi! On Jan 20, rmck wrote: Help. I have a REPAIR table command that has been running since 1/15... I dont know if its hung or what? Should I kill it ? Top shows that it seems to be running? No, it did not hang - Repair with keycache is slow, especially for 23G file. You should configure MySQL so that it'll use Repair by sorting. See the manual for details. mysql show processlist; ++--+---+-+-++--+---+ | Id | User | Host | db | Command | Time | State| Info | ++--+---+-+-++--+---+ | 10 | root | localhost | ip_logs | Query | 391630 | Repair with keycache | REPAIR TABLE Jan04 QUICK ++--+---+-+-++--+--- # ls -alh Jan04.* -rw-rw1 mysqlmysql8.6K Dec 29 08:19 /curipdb/ip_logs/Jan04.frm -rw-rw1 mysqlmysql 23G Jan 15 16:25 /curipdb/ip_logs/Jan04.MYD -rw-rw1 mysqlmysql 22G Jan 20 06:30 /curipdb/ip_logs/Jan04.MYI # top sorted by CPU: 06:32:07 up 5 days, 14:31, 4 users, load average: 1.46, 1.49, 1.39 102 processes: 101 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.2% user 0.1% system0.0% nice 0.0% iowait 99.2% idle CPU1 states: 12.0% user 0.4% system0.0% nice 0.0% iowait 87.0% idle Mem: 3874188k av, 3863468k used, 10720k free, 0k shrd, 33544k buff 2618824k actv, 563052k in_d, 89848k in_c Swap: 4289328k av, 527664k used, 3761664k free 3197660k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 5285 mysql 5 -10 501M 497M 452 S 12.5 13.1 1580m 0 mysqld 17002 root 16 0 1108 1108 820 S 0.3 0.0 0:13 0 top 728 root 16 0 2364 0 S 0.1 0.0 1:37 0 sshd Thanks Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql xa support
Hi! On Jan 16, angela wrote: I've just downloaded the J/Connector 3.0 but there is no MysqlXaDataSource in it (actually there is not com.mysql.jdbc.jdbc2.optional.xa package), even do in the connector documentation it's written about it. I would like to try using mysql 4 with j/connector 3.0 because something I got a lot of errors in JBoss, such as : could not enlist xa-resource ecc.. Where can I find MysqlXaDataSource class? Thanks in advance, sorry for bothering you... Angela Fogarolli. See http://bugs.mysql.com/bug.php?id=1737 Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: blacklist
Hi! On Jan 15, Stefaan Van Dooren wrote: Since some days I don't get any mail from this list anymore. After some investigation, I found that it's blacklisted and our mailserver refuses any mail from it. DNSBL/WARNING: bl.spamcop.net/213.136.52.31: IP is listed Thanks for alerting us! Can this be fixed ? It should be. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext creation on 4.1: ERROR 1034
Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table Sorry, I still cannot repeat this :( Could you try to create a smaller test case ? I would expect that you need only a few rows from your table for this bug to appear. (of course, finding these exact rows in your gigabytes could be not easy :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext creation on 4.1: ERROR 1034
Hi! On Jan 13, Kurt Haegeman wrote: Hi, When trying to create a fulltext index on my large table, I get the following error: ERROR 1034 (HY000): 121 when fixing table I'm using version 4.1.1-alpha of the MySQL database, a source-compiled version with the --with-raid option. I'm trying to build a newpaper article search engine. I've built an 'articles' table with the following DDL: CREATE TABLE articles ( filename varchar(40) default NULL, source varchar(30) default NULL, pubdate varchar(30) default NULL, text text ) TYPE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000 AVG_ROW_LENGTH=2366 RAID_TYPE=striped RAID_CHUNKS=16 RAID_CHUNKSIZE=2048; I've inserted 7806867 articles in dutch and french into it, which gives me a table of about 16Gb, leaving 75+Gb of free space on my Compaq Proliant DL380G2, 1.2Gb RAM. The kernel is compiled with HIGHMEM support, and the MySQL database is using a cnf based on my-huge.cnf. Somebody knows where to start looking? What is the exact command that generates en error ? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crash on FreeBSD-Alpha (64 Bit)
Hi! On Jan 12, Holm Tiffe wrote: Description: mysqld 4.0.17 crash on FreeBSD 5.1-current-alpha How-To-Repeat: Any acces over IP (not domain socket) crashes mysqld: #/usr/local/bin/mysqladmin: connect to server at 'install' failed error: 'Lost connection to MySQL server during query' syslog: install mysqld[78066]: warning: can't get client address: Bad file descriptor I suspect it is a an issue of KSE library (either a bug or some incompatibility with MySQL). Could you run mysqld with libc_r and see if it helps ? Unfortunately we don't have FreeBSD-5/Alpha to try this ourselves. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
Hi! On Jan 13, Joe Rhett wrote: Not a bug. In the manual, section Upgrading from Version 3.23 to 4.0, there is * To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables, you need to rebuild them with `REPAIR TABLE table_name USE_FRM'. Based on a guess, or did you analyze the data file I sent? Based on your data. There is a ft_dump utility program that comes from source distribution. I did ft_dump -d Notices 0|grep '\\(control\|pollution\)\' log then I noticed that entries are ordered by weight, not by rowid. It is what was changed in 4.0 to make boolean search to work, and it's what is fixed by `REPAIR TABLE table_name USE_FRM' And of course I tried this myself before writing to you :) And if so, may I suggest that the upgrade documentation REALLY needs to be broken into sections? 1. Table changes 2. Privilege changes 3. Configuration changes 4. API/result changes Yes, I agree. Not necesarily to these particular division, but to the fact that our upgrading sections are difficult to follow. I'm forwarding this request to our doc team. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
Hi!
On Jan 07, [EMAIL PROTECTED] wrote:
Description:
Boolean mode fulltext searching returns zero hits for valid queries.
How-To-Repeat:
Create a database with a Text column. Add a fulltext index on it.
Try to search for multiple words with AND or phrase syntax.
Here are examples:
mysql select Notice_ID from Notices where match (Text) against
('+pollution +control' in boolean mode);
Empty set (0.00 sec)
mysql select Notice_ID from Notices where match (Text) against
('pollution control' in boolean mode);
Empty set (0.02 sec)
mysql select Notice_ID from Notices where Text like '%pollution control%';
+---+
| Notice_ID |
+---+
|192090 |
+---+
1 row in set (5.00 sec)
Not a bug.
In the manual, section Upgrading from Version 3.23 to 4.0, there is
* To use `MATCH ... AGAINST (... IN BOOLEAN MODE)' with your tables,
you need to rebuild them with `REPAIR TABLE table_name USE_FRM'.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.1 FTS 2-level?
Hi! On Jan 11, Matt W wrote: Hi, - Original Message - From: Sergei Golubchik To: Steven Roussey Sent: Wednesday, December 10, 2003 7:44 AM Subject: Re: 4.1.1 FTS 2-level? Hi! On Dec 09, Steven Roussey wrote: Does Mysql 4.1.1 have the two level index system integrated into it for full text searches? What do you mean ? Is it used to optimize searches ? No. Still there could be some speedup because, e.g, MyISAM will use binary search in the key pages instead of linear one, etc. You're right. Wow! I assume you were comparing it to 4.0.x, not 4.1.0. Once the data rows are cached, the index search in boolean mode seems to be about *7 times* faster than 4.0. :-) 2 test searches on the same data went from 48s - 7 and 35 - 5. Nice! Cool :) So are these faster index searches only the result of binary vs linear search? (I don't know the exact difference, but binary sounds good. ;-)) The actual full-text code itself is NOT any more optimized than 4.0? As far as I remember - no. The difference is simple - if one has, say, a string or words like just,simply,words,zyx that is, comma-separated words in alphabetic order. Then to find a particular word one needs to compare word1 (just) with this particular word, then word simply, etc. It is because words - as a sequences of characters - may have different lengths. So the only way to find where in the string starts the word N is to read all the words before it. If it's an array of, say, integers int array[100] then i-th entry is simply array[i], and to find a word in a sorted array one can use binary search. These are two different types of pages of a btree in MyISAM index. If an index includes variable-length segments or(and) compression is used then different key entries on the page may have different lengths, MyISAM will use linear search for each key page, time is linear - O(N). (for keys starting from a char/varchar - like a fulltext index - MyISAM uses a special variant of the linear search, that does not unpacks each key for comparison, it's ~2-4 times faster than normal linear search, but still it's linear - O(N)) Otherwise MyISAM will use binary search, time is logarithmic - O(log N) In normal fulltext index key entry is varchar, float, offset In second level it's only float, offset - and MyISAM uses binary seacrh for the pages on the 2nd level. Are the 2-level indexes solely for FTS to use, or can MyISAM use them in general for any indexes? Fulltext only. Just wondering, since you said Is it used to optimize searches? No. Which sounds like it's being used for *storage*, just not the word count statistics for optimization, etc. Yes, for storage. But it also used for word count statistics, as new there is only one entry per word (well, for common enough words, rare words don't matter), and this entry is the place where per-word statistics is be stored. And my index file was reduced from 1.74G in 4.0 to 1.59G, so I thought maybe this is where some space was saved. Yes. MyISAM does compress fulltext indexes with prefix compression. That is for each word it stores only the difference from the previous word on this page. If the words are identical, second entry will take one or two bytes only (I don't remember exactly). And the first entry on the page is always not compressed. So, by converting all entries for some particular word to 2-level structure saves these one-two bytes per word and length(word)*(number_of_pages-1) Usually - with prefix compression - one get about ~250 entries on the page (I mean for the same common word), thus number_of_pages is, approximately number_or_rows_with_the_word/250 BTW, would (re)building the index be slower with 4.1 for any reason? I thought maybe it was, but I'd have to try again to be sure. I didn't compare :( But out of my head I don't know any reason why it should be. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in Boolean mode fulltext searching.
Hi! On Jan 07, [EMAIL PROTECTED] wrote: Description: Boolean mode fulltext searching returns zero hits for valid queries. How-To-Repeat: Create a database with a Text column. Add a fulltext index on it. Try to search for multiple words with AND or phrase syntax. I tried - works ok. So - examples are not enough, I need a repeatable test case (e.g. your table data). Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupt MYD table - can not repair
Hi!
On Jan 06, Mike Brickman wrote:
Hi,
I have a client who has managed to corrupt an MYD table so that it can
not be repaired. I have tried:
Myisamchk -re {table}
And this crashes out with:
myisamchk: error: Not enough memory for blob at 3960 (need 1090519040)
and then quits.
There is no complete backup of this file so I would like to recover as
much data as possible.
4) What exactly does the error mean?
It means that myisamchk tries to allocate 1090519040 bytes to read a
blob of this size - and malloc() fails.
3) Are the internal structures of MYD files documented anywhere
(url please)?
To my knowledge - no.
2) Why does myisamchk not fix the problem?
Because the problem is Out Of Memory error - this isn't something
myisamchk can fix :)
What happens is, myisamchk finds a something that looks like row header,
but in fact it is part of your data. Interpreting this row header
myisamchk thinks it belongs to a 1Gb blob.
It's unavoidable - when you ask to recover as much as possible,
myisamchk does it, literally, and you may get spurious rows in the
result.
The negative effect is that spurious row header can abort the repair
process. To fix it, in 4.1.1 we added a new command-line option to
myisamchk: --max-record-length. If you happen to know that no row in
your table is longer than N, you may tell myisamchk that all rows longer
than that are false matches and should be ignored.
1) Are there any tools which will allow me to salvage some data?
myisamchk as of 4.1.1.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed difference between boolean full-text searches and full-text searches
Hi!
Sorry for delayed answer - I was on vacations...
On Dec 08, Uros Kotnik wrote:
OK, I will give you more details.
Table CDS, have 1,053,794 rows, FT index on title,
Data 67,646 KB, Index 70,401 KB
Table ARTISTS, Rows 292,330, FT on name,
Data 8,096 KB
Index 17,218 KB
Table TRACKS, rows 13,841,930, FT on title
Data 625,360 KB
Index 646,672 KB
ft_min_word_len = 3
key_buffer_size 786432000
Explain for both SQLs gives same info :
table type possible_keys key key_len ref rows Extra
artists fulltext PRIMARY,ft_name ft_name 0 1 Using where
cds fulltext PRIMARY,artistIndex,ft_title ft_title 0 1 Using where
tracks ref PRIMARY,artistIndex PRIMARY 4 cds.cdId 13 Using where
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna' IN BOOLEAN MODE) AND
MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)
Time for second SQL : 1 sec.
SELECT artists.name, cds.title, tracks.title
FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND
MATCH ( artists.name ) AGAINST ( 'madonna' ) AND
MATCH ( cds.title ) AGAINST ( 'music' ) AND
MATCH ( cds.title ) AGAINST ( 'mix' ) AND
MATCH ( cds.title ) AGAINST ( '2001' )
Assuming,
SELECT @N=COUNT(*) FROM cds WHERE MATCH title AGAINST ('music');
SELECT @M=COUNT(*) FROM cds WHERE MATCH title AGAINST ('mix');
SELECT @K=COUNT(*) FROM cds WHERE MATCH title AGAINST ('2001');
SELECT @L=COUNT(*) FROM cds WHERE MATCH title AGAINST ('+music +mix +2001' IN BOOLEAN
MODE);
The first query will do N+M+K index lookups and read L rows from the
disk.
The second query will do N index lookups and read N rows from the disk.
Thus, if (N+M+K) is much greater than N (it usually is)
and L is close to N (it is usually not), than first query should be much
slower. Typically L is less than min(N,M,K), thus the goal is to reduce
the number of row reads.
I agree this optimization is not the best for all situations,
optimizing this type of queries is in the todo.
btw, it could be that my guess about the slowness was wrong :)
Compare these N,M,K,L numbers yourself.
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.0 has been released
Hi! On Dec 29, Thomas Spahni wrote: Hi, is there any change in data format for MySQL 5.0.0? Is it safe (for a user of stable 4.0.17) to test 5.0.0 on existing data and then go back to 4.0.17 for production? As far as I know it should be safe. But, of course, a backup is always recommended :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ADDING INDEXES TO A TABLE LEADS TO MYSQL CRASH, BUG!!
Hi!
On Dec 28, Derek J wrote:
Hello Every Body ,
I have been facing a strange problem, that i assume its a mysql bug of
some sort.
In my datadabase there are 2 tables, if i tried to add any index to them
,any INSERT INTO request status will be - in show processlist- update
and will freeze like this forever , and consequently all inserts ot this
table stays in the que, untill mysql stops responding.
If i drop the INDEX on those tables INSERTS works fine, once i create
ANY index except PRIMARY index, INSERTS fails.
ALL kind of tricks, like myisamchk, drop table and creating a new one,
even moving it to a new machine seems to cause the same problem.
Running Mysqld 4.0.16 / 4.0.17 ( tried both) on AMD opteron 2 gig ram
using MySQL Binaries on REDHAT AS3.
one of The table structures:
CREATE TABLE `keywordlog` (
`username` char(40) default NULL,
`country` enum('Unknown','Unknown','AD','Andorra','AE',**removed rest
of enumerate due to restricton on email size**,'ZW','Zimbabwe') NOT NULL
default 'Unknown',
`ip` char(15) NOT NULL default '',
`time_date` datetime NOT NULL default '-00-00 00:00:00',
`keyword` char(50) NOT NULL default '',
`refer_url` char(70) default NULL,
`request_url` char(70) default NULL,
`xmlstatus` enum('HTML','XML') NOT NULL default 'HTML',
`bid` char(6) NOT NULL default '',
`toolbar` int(1) default '0'
) TYPE=MyISAM;
What columns are you trying to index ? What is the index definition ?
What does SHOW PROCESSLIST show when there are freezed inserts ?
How big is your table ?
Can you create a repeatable test case ?
Regards,
Sergei
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ADDING INDEXES TO A TABLE LEADS TO MYSQL CRASH, BUG!!
Hi! On Dec 29, Derek J wrote: Hello, First, Yes I can create repeatable test case, I still have live server with the same problem. and another Dual opteron server, which i created the same database there as a test to make sure there are no hardware faliures casuing this probles, but i still had the same problem there too. I can give full access to any of MySQL team to check this problem. I had aleaddy 2 DBA's fail to Identify the problem in there. mysql show table status like 'keywordlog'; +-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++--++--- | keywordlog | MyISAM | Fixed | 518 |267 | 138306 | 1146756268031 |77824 | 0 | NULL | 2003-12-27 19:21:28 | 2003-12-28 02:40:00 | 2003-12-27 21:40:36 Great, table is 138306 bytes only - relatively small, that is. Then, the fastest way to get this fixed is to create a bugreport at http://bugs.mysql.com (just copy-paste your first mail and information from the your last reply) and to attach a (compressed) table dump to a bugreport using [Files] tab (mark it private if you want it to be visible to MySQL AB staff only). As far as I understand, the table in the dump should be created without indexes (this is your point, isn't it?), and if one adds to the end of the dump ALTER TABLE keywordlog ADD INDEX ...; INSERT keywordlog ... then mysql keywordlog.dump will hang. Did I understand you correctly ? Then, as I advised above, just create a bugreport and attach a dump (that ends with ALTER TABLE ... ; INSERT ...) to it - with a repeatable test case the bug will be fixed very quickly. No need to waste the time sending emails back and forth and speculating, when your test case is small enough :) Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How boolean full-text search finds matches?
Hi! On Dec 17, Matt W wrote: Hi, Just have a couple more full-text search inquiries here. :-) I'm not exactly clear on how matching rows are found when searching for 2 or more required words: '+word1 +word2'. I understand that it can't currently know which word occurs less, so that it can be searched first -- this optimization will come with 4.1's 2-level indexes. :-) I just want to know, when it finds a match for whichever word is tried first, how does it check if the other required word(s) are present in the same row? Say that word1 and word2 are each present in 100,000 rows. 1) Surely it doesn't check the 100,000 entries for word2 for EACH word1 match to see if they're in the same row, does it? No it does not :) 2) It *seems* the best way would be to do a lookup for (word2 + rowid for word1) and see if there's a match. Is this what's done? I'm not sure it's possible though with the way the index is structured... it is possible, but it is only sensible if word1 is much more rare than word1. This could be done with 2-level indexes :) 3) Or, and I'm thinking *maybe* this is how it's done from what I've heard, does it get all the matches for word1, then for word2, and then intersect them to find ones which are present in the same row? If so, how will the 2-level index optimization change things? Will it do #2? Yes to both questions, without the word then. First, one match is found for each word. Then read_next is called for the word with the lowest rowid, etc. The advantage is that matches are found and returned earlier - a user don't have to wait for the index scan to complete. Also LIMIT, if used, cuts off more work, that is LIMIT is more effective. But when one word is much more common than the second one, it is better to do #2, and it's what I'll probably do. Next question is... a few weeks ago I was doing some test searches like '+word1 +word2'. Actually, maybe I was only using 1 word, I can't remember, but I don't think it matters. Anyway, I happened to try changing the query to '+word1* +word2*' -- e.g. adding a wild-card to the end of the same word(s) -- and I was amazed at how much faster the query was! (And no, there's no query cache; and they were both run many times so the index was cached. :-)) Can't remember how much faster, but it wasn't insignificant. Then I tried adding a wild-card to the end of words in another search (the wild-card did not make more rows match as far as I know), but that made it a little slower (I'd expect that, if anything). Is there any explanation for why adding wild-cards would make a search faster? Yes :) Looking on the description above how +word1 +word2 works, you can see that it relies on the fact that key entries for each word are sorted by rowid. But it is how MyISAM indexes work, they are always ordered by all keyparts, and rowid is always the last keypart, so fulltext index is always ORDER BY word, rowid. But when the search is done for the word prefix only, this order breaks, the index is, of course, not ORDER BY LEFT(word,5), rowid. Consider the index aa, 5 aaabbb, 10 aaaccc, 3 The workaround was to exclude prefixes from index scan whenever possible (that is in +word +pref1* +pref2* ... prefixes are not looked up in the index at all, in +pref1* +pref2* ... only the first prefix is looked up in the index) and to add a row scan (in the LIKE sense and as you described in #1), to remove half-matched rows. Thus it is sometimes slower - as more row data are read from the disk, and sometimes faster - as there are less index scans performed. Obvious optimization is to choose the most rare prefix for the scan, not the first one. It is in the todo, of course :) P.S. Sergei, if you see this, in one of your replies to my full-text suggestions back in September ( http://lists.mysql.com/mysql/149644 ), you said Another reply will follow... I never saw another reply though. :-/ It's OK, I was just wondering what other interesting things you were going to say! :-) Oops, checking... Yes, sorry. But I have to admit, I absolutely do not remember what I had in mind when I wrote it and what should've followed :( Sorry for this. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.17 has been released
Hi! Well, I checked the code and looks like in 4.0.17 lower_case_table_names is NOT forced to 1 on case-insensitive file system, despite the changelog entry (that we will fix, by the way). Obviously a programming mistake, but for now only warning that lower_case_table_names is forced to 1 is issued, no actual assignement takes place. I think, as there are users that need lower_case_table_names=0 on Windows, we could set the *default* value based on filesystem case-sensitivity, but with the possibility to change it from the command line, and issue a BIG RED WARNING if you'll do. So don't do it unless you know what you are doing. To quote Monty: The 'bug' here is that if you set lower_case_table_names to OFF on a system where you have case insensitive file names (like windows) you WILL get table corruption if you access tables with different cases. This is becasue MySQL doesn't know that the tables 'name' and 'NAME' are in fact the same table and will cache and lock each of these separately. On Dec 17, Reverend Deuce wrote: I agree, 100%. We live in a mixed environment of UNIX and Windows and as such, we've assumed case insensitivity in our apps. I know that this is bad practice, but forcing this flag on us is and even worse practice. This should always, always be an option. I wont be able to upgrade until this is fixed. :( -- R - Original Message - From: Yves Goergen [EMAIL PROTECTED] To: Lenz Grimmer [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, December 17, 2003 1:12 PM Subject: Re: MySQL 4.0.17 has been released On Wednesday, December 17, 2003 1:58 PM CET, Lenz Grimmer wrote: Functionality added or changed: * `lower_case_table_names' is now forced to 1 if the database directory is located on a case-insensitive file system. (Bug #1812) Uh, _very_ bad. I know that my Windows filesystem is case-insensitive and that I cannot create tables only differing by case of the name, but I need to have this setting off to be able to dump my tables on Windows with the correct (and not lower-cased!!) table names to import them on my webhoster's Linux server. If the setting was enabled, I'd get all wrong table names and my application couldn't find its tables anymore (as 'bb1_GroupMembers' gets to 'bb1_groupmembers' and that's something else!). So please change this back again, I believe it's up to the server administrator to set this in a correct and reasonable way, don't you? Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8
Hi! On Dec 16, Igor Zinkovsky wrote: I can't find in MySQL 4.1.1 documentation anything about 'character_set_system' variable. It is setted up to 'utf8'. How can I change it to be able to use russian KOI8-R in mysql client? You cannot change it. But you don't need to either. It is character set for system usage - usernames, table, column names etc. To use KOI8-R in MySQL client you need to 1. Be sure you can actually type and see koi8-r characters - you need to setup you terminal program/console properly for this 2. Issue SET NAMES koi8r; from mysql command line client or start it with --default-character-set=koi8r (or put default-character-set=koi8r in ~/.my.cnf) - to inform the server that you send queries and want to receive results in koi8r charset. That's all! Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
