RE: Why does mysql drop index very very slow in a large table?
A workaround is to use mysqlhotcopy to snapshot the table and also only copy the header to the MYI file. Then delete the original and rename the copy back to the original. This will effectively drop all indexes and should take no more time than what the disk takes to copy the .my* files. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's the PHP equivallent of mysql mydb somefile.sql
Could you be more specific? What is SOURCE? Where do I use that? I tried to search, but I find a lot of hits related to source code. -Original Message- From: Ligaya A. Turmelle [mailto:[EMAIL PROTECTED] Sent: Monday, October 09, 2006 6:57 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: What's the PHP equivallent of mysql mydb somefile.sql I don't know if it will work - but have you tried using SOURCE in the mysql query? -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:01 AM To: mysql@lists.mysql.com Subject: What's the PHP equivallent of mysql mydb somefile.sql Currently I run an 'updater' script to run through a directory of .sql files using something like this in PHP: $COMMAND = mysql .$OPTION['db_prefix'].$db. .$mydir.$filename; system($COMMAND, $ret); What would be the equivallent way to to this in a PHP mysql_query(); way? I see LOAD, but that only works for data it seems. http://dev.mysql.com/doc/refman/5.0/en/load-data.html These scripts I use have ALTER statements and CREATE and all other types of SQL in them and almost no data actually. mySQL 5.0.15 ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: hello everyone
You'll probably need to create a database first (to host the table). This can be done most easily from the GUI MySQL Administrator tool, or from phpMyAdmin if you have it, or from a mysql command line: http://dev.mysql.com/doc/refman/5.0/en/create-database.html To create a table use the CREATE TABLE command in MySQL, either from a mysql command line or from the GUI MySQL Query Browser:, or from the GUI MySQL Administrator tool, or from phpMyAdmin: http://dev.mysql.com/doc/refman/5.0/en/create-table.html To load the table with data use the INSERT command (either from the mysql command line, the Query Browser, or from php code using an explicit command string or a stored procedure): http://dev.mysql.com/doc/refman/5.0/en/insert.html To access it using an appropriate SELECT statement either passed as a string or a stored procedure, php has a full set of functions that start mysql_, or mysqli_. You'll prefer the latter if you're into object orientation. This page explains about the mysql_ fuctions and has links to all the individual ones: http://dev.mysql.com/doc/refman/5.0/en/insert.html This page is the equivalent one for mysqli http://uk.php.net/manual/en/ref.mysqli.php HTH Peter Ysgrifennodd alan: my name is alan madsen. while i am very well grounded in complex systems and database management that is archaic by today's standards, i am looking at a creating a server-side php/mysql environment for a very simple database application with only the experience of recent light reading and knowing that i've successfully installed a wamp serverkit on windows 2k (uniform server) that includes mysql5: The Uniform Server is a lightweight server solution for running a web server under the WindowsOS. 5.79MB! It includes the latest versions of Apache2, Perl5, PHP5, MySQL5, [and] phpMyAdmin http://sourceforge.net/projects/miniserver/ running on a laptop, i've seen this installation's instance of apache serve web pages to the net. very nice. i'd like to create, load, and maintain, a mysql database table containing fewer than 8,000 records, each with 5 fields (rows?) - lengths ranging from 10 bytes to 80 bytes - of character data, one field of which would be used as data and as a isamkey (com- pound keys would be nice, but they are not necessary). assuming a working installation of mysql5 and that a csv data file exists, will someone outline what steps are necssary to: 1. create such a table, 2. load it, and 3. the mysql methodology to access it via php/mysql_isam? any comment would be appreciated. regards, --Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's the PHP equivallent of mysql mydb somefile.sql
Read the section on the manual on the Client and Utility programs, specifically mysql. The syntax is essentially the same as you have in your code something like this from the command line mysql -u dbUser -pMyPassword dbName SomeFileWithLotsOfSQLCommands.sql Note there is no space between the '-p' and the password. Daevid Vincent wrote: Could you be more specific? What is SOURCE? Where do I use that? I tried to search, but I find a lot of hits related to source code. -Original Message- From: Ligaya A. Turmelle [mailto:[EMAIL PROTECTED] Sent: Monday, October 09, 2006 6:57 PM To: Daevid Vincent; mysql@lists.mysql.com Subject: RE: What's the PHP equivallent of mysql mydb somefile.sql I don't know if it will work - but have you tried using SOURCE in the mysql query? -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:01 AM To: mysql@lists.mysql.com Subject: What's the PHP equivallent of mysql mydb somefile.sql Currently I run an 'updater' script to run through a directory of .sql files using something like this in PHP: $COMMAND = mysql .$OPTION['db_prefix'].$db. .$mydir.$filename; system($COMMAND, $ret); What would be the equivallent way to to this in a PHP mysql_query(); way? I see LOAD, but that only works for data it seems. http://dev.mysql.com/doc/refman/5.0/en/load-data.html These scripts I use have ALTER statements and CREATE and all other types of SQL in them and almost no data actually. mySQL 5.0.15 ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
validating, filtering price value for a decimal column from various strings
Hi list subscribers, i am inserting millions of product rows from csv files via LOAD DATA INFILE. Every product has a price, but this price-strings vary heavily. The main difference between them, is the decimal format. Sometimes it is european like this: 1.000.000,00 sometimes its american like this: 1,000,000.00 so some examples for these strings are: EUR 1,00 (meaning decimal 1.00) 1.00 euro; (meaning decimal 1.00) 1.000,00 EUR (meaning decimal 1000.00) EUR 1.000,00 (meaning decimal 1000.00) 1.000 EUR (meaning decimal 1000.00) 1,000 EUR (meaning decimal 1000.00) and now, i want to filter/validate that directly within the query. I think some RegEx could do the trick, but this is to much for me. Ive searched for some RegEx for validating decimals, but they allways use just one notation of a decimal. I think a good strategy for that, to filter all chars but [0-9\.,], and than to do some logic like to check how man chars after the last dot or comma (if its two you know its something like 0.00). No my problem is, i don't know where to start. Maybe with a stored procedure or something like that? I don't wanna use another language for this, because i would have to do some comprehensive update work then (selecting every row, checking the price, updating the price), and these rows get written every day, so i would have to do these updates once a day on a couple of million rows. Anyone get me in the right direction? Thanks in advance Regards Benjamin Bittner - Was ist Glück? Schlafen Fische überhaupt? Die Antworten gibts auf Yahoo! Clever.
Re: validating, filtering price value for a decimal column from various strings
Benjamin, any chance you can pre-process the data with an external
script prior to loading into your database?
Doing this sort of manipulation in SQL may be possible, but it surely
will be tricky. RegEx support in MySQL is present, but in my
(limited) experience with it, it's really only good for determining
whether a string matches a pattern, not manipulating strings, which is
what you need to do.
An alternative would be to filter in your reporting language, if
you're using something like perl, ruby, PHP etc to display the data.
Problem would be that performing calculations would also have to be
external - no use of SUM etc in SQL since these aren't currently
numbers.
In either case, you're on to the same approach I would take, I think.
I'd use a series of regex's, like so
# remove all characters that are not digits commas periods
s/[^\d,\.]//g
# replace all commas with periods
s/,/\./g
# remove periods not used to indicate decimal place, looking for .ddd pattern
# will break if you have 3 digit (or more) decimal precision!
1 while s/^(.*)\.(\d{3})(.*)/$1$2$3/
Now you should have a number that looks like one of the following:
100.00
100
1
1.00
1000.00
1000
and is therefore suitable for storage in a numeric column in MySQL.
Hopefully all your currency units are the same!
Dan
On 10/10/06, Benjamin Bittner [EMAIL PROTECTED] wrote:
Hi list subscribers,
i am inserting millions of product rows from csv files via LOAD DATA INFILE.
Every product has a price, but this price-strings vary heavily.
The main difference between them, is the decimal format. Sometimes it is
european like this:
1.000.000,00
sometimes its american like this:
1,000,000.00
so some examples for these strings are:
EUR 1,00 (meaning decimal 1.00)
1.00 euro; (meaning decimal 1.00)
1.000,00 EUR (meaning decimal 1000.00)
EUR 1.000,00 (meaning decimal 1000.00)
1.000 EUR (meaning decimal 1000.00)
1,000 EUR (meaning decimal 1000.00)
and now, i want to filter/validate that directly within the query.
I think some RegEx could do the trick, but this is to much for me. Ive searched
for some RegEx for validating decimals, but they allways use just one notation
of a decimal. I think a good strategy for that, to filter all chars but
[0-9\.,], and than to do some logic like to check how man chars after the last
dot or comma (if its two you know its something like 0.00).
No my problem is, i don't know where to start. Maybe with a stored procedure or
something like that? I don't wanna use another language for this, because i
would have to do some comprehensive update work then (selecting every row,
checking the price, updating the price), and these rows get written every day,
so i would have to do these updates once a day on a couple of million rows.
Anyone get me in the right direction?
Thanks in advance
Regards Benjamin Bittner
-
Was ist Glück? Schlafen Fische überhaupt? Die Antworten gibt's auf Yahoo!
Clever.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does mysql drop index very very slow in a large table?
Chris, Please read this in its entirety !!! I learned why 2 years ago while using MySQL 4.1 for Windows and looking at the folder which contains the .MYDs and .MYIs while watching 'ALTER TABLE ... DROp INDEX' in action: If a table T is a MySQL table having four indexes (ndx1,ndx2,ndx3,ndx4) and you want to 'alter table T drop index ndx3;' here is exactly what happens under the hood: 1) MySQL copies T.MYD to a temp table, i.e., S.MYD and a zero byte S.MYI. 2) MySQL does 'alter table S add index ndx1 (...); 3) MySQL does 'alter table S add index ndx2 (...); 4) MySQL does 'alter table S add index ndx4 (...); 5) MySQL deletes T.MYD and deletes T.MYI 6) MySQL renames S.MYD to T.MYD, and renames S.MYI to T.MYI That's it. Suppose you wanted to drop all four indexes, you would actually be performing this series of steps 4 times, you would actaully be doing an 'alter table T add index ...' 6 times. Pass 1, 3 indexes builds Pass 2, 2 indexes builds Pass 3, 1 index build Pass 4, 0 indexes builds The reverse is even worse. Given the same MyISAM table T having four indexes (ndx1,ndx2,ndx3,ndx4) and you want to 'alter table T add index ndx5 (...);' here is exactly what happens: 1) MySQL copies T.MYD to a temp table, i.e., S.MYD and a zero byte S.MYI. 2) MySQL does 'alter table S add index ndx1 (...); 3) MySQL does 'alter table S add index ndx2 (...); 4) MySQL does 'alter table S add index ndx3 (...); 5) MySQL does 'alter table S add index ndx4 (...); 6) MySQL does 'alter table S add index ndx5 (...); 7) MySQL deletes T.MYD and deletes T.MYI 8) MySQL renames S.MYD to T.MYD, and renames S.MYI to T.MYI In fact, let take the worst possible scenario: Given the same table T with no indexes and you want to add 5 indexes, here is exactly what happens (brace yourself, seriously): 1 MySQL copies T.MYD to a temp table, i.e., S1.MYD and a zero byte S1.MYI. 2 MySQL does 'alter table S1 add index ndx1 (...); 3 MySQL deletes T.MYD and deletes T.MYI 4 MySQL renames S1.MYD to T.MYD, and renames S1.MYI to T.MYI 5 MySQL copies T.MYD to a temp table, S2.MYD and a zero byte S2.MYI. 6 MySQL does 'alter table S2 add index ndx1 (...); 7 MySQL does 'alter table S2 add index ndx2 (...); 8 MySQL deletes T.MYD and deletes T.MYI 9 MySQL renames S2.MYD to T.MYD, and renames S2.MYI to T.MYI 10 MySQL copies T.MYD to a temp table, S3.MYD and a zero byte S3.MYI. 11 MySQL does 'alter table S3 add index ndx1 (...); 12 MySQL does 'alter table S3 add index ndx2 (...); 13 MySQL does 'alter table S3 add index ndx3 (...); 14 MySQL deletes T.MYD and deletes T.MYI 15 MySQL renames S3.MYD to T.MYD, and renames S3.MYI to T.MYI 16 MySQL copies T.MYD to a temp table, S4.MYD and a zero byte S4.MYI. 17 MySQL does 'alter table S4 add index ndx1 (...); 18 MySQL does 'alter table S4 add index ndx2 (...); 19 MySQL does 'alter table S4 add index ndx3 (...); 20 MySQL does 'alter table S4 add index ndx4 (...); 21 MySQL deletes T.MYD and deletes T.MYI 22 MySQL renames S4.MYD to T.MYD, and renames S4.MYI to T.MYI 23 MySQL copies T.MYD to a temp table, S5.MYD and a zero byte S5.MYI. 24 MySQL does 'alter table S5 add index ndx1 (...); 25 MySQL does 'alter table S5 add index ndx2 (...); 26 MySQL does 'alter table S5 add index ndx3 (...); 27 MySQL does 'alter table S5 add index ndx4 (...); 28 MySQL does 'alter table S5 add index ndx5 (...); 29 MySQL deletes T.MYD and deletes T.MYI 30 MySQL renames S5.MYD to T.MYD, and renames S5.MYI to T.MYI MySQL Copied T.MYD 5 times MySQL Copied T.MYI 5 times MySQL Created an Index 15 times In fact, for table T with no indexes and you want to add N indexes MySQL will copy the MYD N times MySQL will copy the MYI N times MySQL will run 'alter table add index' N(N+1)/2 times if adding an index MySQL will run 'alter table drop index' N(N-1)/2 times if dropping an index Here is a chart on the number of 'alter table drop or add index' commands are executed: N Number of ALTER TABLE ADD INDEX Number of ALTER TABLE DROP INDEX - --- 1 10 2 31 3 63 4 106 5 15 (Count from past example) 10 6 21 15 7 28 21 8 36 28 9 45 36 10 55 45 11 66 55 12 78 66 13 91 78 14105 91 15120 105 16136 120 17153
optimizing mySQL
Hi, I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Thanks a lot. -- Surendra Singhi http://ssinghi.kreeti.com, http://www.kreeti.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advice on multilingual databases?
I'd like some advice on setting up databases that contain entries for the same item in more than one language. For instance, here's what I currently do for a table that contains the same topics translated into English and Arabic: CREATE TABLE `TOPIC` ( `TopicID` int(11) NOT NULL auto_increment, `Topic-en` text NOT NULL, `Topic-ar` text character set utf8 collate utf8_unicode_ci NOT NULL, `ParentTopicID` int(11) NOT NULL default '0', PRIMARY KEY (`TopicID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Topics for text-based hierarchies' AUTO_INCREMENT=76 ; In this table, 'Topic-ar' is the Arabic translation of the English 'Topic-en.' If this were required to also be in Spanish and French, I'd add columns for 'Topic-es' and 'Topic-fr' similar to 'Topic-en' above. I'm wondering if there are any other ways to store and access multilingual data. Can anyone suggest other ways they've dealt with this task, and the pros and cons of their approach compared to mine? Thank you in advance for your advice and suggestions. -Kevin Kevin Zembower Internet Services Group manager Center for Communication Programs Bloomberg School of Public Health Johns Hopkins University 111 Market Place, Suite 310 Baltimore, Maryland 21202 410-659-6139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.26 has been released (part 2)
Hi, MySQL 5.0.26, a new version of the popular Open Source Database Management System, has been released. This is part 2 of the related announcement, listing the changes of 5.0.25 over 5.0.24a which was the last published release of 5.0. As 5.0.25 was not generally released, most users will get these changes only with 5.0.26. Please, see the other mail for the changes in 5.0.26 (over 5.0.25). Changes in release 5.0.25 (15 September 2006) This is a bugfix release for the current production release family. This version was released as MySQL Classic 5.0.25 to commercial customers only. Functionality added or changed: * For the mysql client, typing Control-C causes mysql to attempt to kill the current statement. If this cannot be done, or Control-C is typed again before the statement is killed, mysql exits. Previously, Control-C caused mysql to exit in all cases. (Bug#17926: http://bugs.mysql.com/17926; see also Bug#1989: http://bugs.mysql.com/1989) * For mysqlshow, if a database name argument contains wildcard characters (such as `_') but matches a single database name exactly, treat the name as a literal name. This allows a command such as mysqlshow information_schema work without having to escape the wildcard character. (Bug#19147: http://bugs.mysql.com/19147) * If a DROP VIEW statement named multiple views, it stopped with an error if a non-existent view was named and did not drop the remaining views. Now it continues on and reports an error at the end, similar to DROP TABLE. (Bug#16614: http://bugs.mysql.com/16614) * Table comments longer than 60 characters and column comments longer than 255 characters were truncated silently. Now a warning is issued, or an error in strict mode. (Bug#13934: http://bugs.mysql.com/13934) * The bundled yaSSL library was upgraded to version 1.3.7. * The bundled yaSSL library licensing has added a FLOSS exception similar to MySQL to resolve licensing incompatibilities with MySQL. (See the extra/yassl/FLOSS-EXCEPTIONS file in a MySQL source distribution for details.) (Bug#16755: http://bugs.mysql.com/16755) * The server now issues a warning if it removes leading spaces from an alias. (Bug#10977: http://bugs.mysql.com/10977) * The VIEW_DEFINITION column of the INFORMATION_SCHEMA VIEWS table now contains information about the view algorithm. (Bug#16832: http://bugs.mysql.com/16832) * For a successful dump, mysqldump now writes a SQL comment to the end of the dump file in the following format: -- Dump completed on -MM-DD hh:mm:ss (Bug#10877: http://bugs.mysql.com/10877) * The mysqld and mysqlmanager manpages have been reclassified from volume 1 to volume 8. (Bug#21220: http://bugs.mysql.com/21220) * configure now defines the symbol DBUG_ON in config.h to indicate whether the source tree is configured to be compiled with debugging support. (Bug#19517: http://bugs.mysql.com/19517) * The MySQL distribution now compiles on UnixWare 7.13. (Bug#20190: http://bugs.mysql.com/20190) * The mysql client used the default character set if it automatically reconnected to the server, which is incorrect if the character set had been changed. To enable the character set to remain synchronized on the client and server, the mysql command charset (or \C) that changes the default character set and now also issues a SET NAMES statement. The changed character set is used for reconnects. (Bug#11972: http://bugs.mysql.com/11972) * mysql_upgrade no longer reads the [client] option file group because it is not a client and did not understand client options such as host. Now it reads only the [mysql_upgrade] group. (Bug#19452: http://bugs.mysql.com/19452) * MySQL now can do stack dumps on x86_64 and i386/NPTL systems. (Bug#21250: http://bugs.mysql.com/21250) * TIMESTAMP columns that are NOT NULL now are reported that way by SHOW COLUMNS and INFORMATION_SCHEMA. (Bug#20910: http://bugs.mysql.com/20910) Bugs fixed: * Security fix: On Linux, and possibly other platforms using case-sensitive filesystems, it was possible for a user granted rights on a database to create or access a database whose name differed only from that of the first by the case of one or more letters. (CVE-2006-4226 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2006-4226), Bug#17647: http://bugs.mysql.com/17647) * Security fix: A stored routine created by one user and then made accessible to a different user using GRANT EXECUTE could be executed by that user with the privileges of the routine's definer. (CVE-2006-4227 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2006-4227), Bug#18630: http://bugs.mysql.com/18630) * CREATE TABLE ... SELECT statements that selected GEOMETRY values resulted in a table that contained BLOB columns, not
SQL statement work in mysql4 but not mysql5
I am going to upgrade my database from version 4 to 5. However I found some of my web application doesn't work on MySQL5 e.g following statement works in 4 but not 5 SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as cat_state, c.name as cat_name, c.description as cat_desc, c.image, c.url, m.member_name as mod_name, m.member_id as mod_id, m.is_group, m.group_id, m.group_name, m.mid FROM ibf_forums f, ibf_categories c LEFT JOIN ibf_moderators m ON (f.id=m.forum_id) WHERE c.id=f.category ORDER BY c.position, f.position Error: Unknown column 'f.id' in 'on clause' The alias seem not working? What should I do, I dont want to rewrite all my sql statement Thanks. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL statement work in mysql4 but not mysql5
I had this problem here and i change my query to: SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as cat_state, c.name as cat_name, c.description as cat_desc, c.image, c.url, m.member_name as mod_name, m.member_id as mod_id, m.is_group, m.group_id, m.group_name, m.mid FROM ibf_forums f INNER JOIN ibf_categories c on f.id_cat=c.id_cat LEFT JOIN ibf_moderators m ON (f.id=m.forum_id) WHERE c.id=f.category ORDER BY c.position, f.posit It works fine to me. Jason Chan [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] I am going to upgrade my database from version 4 to 5. However I found some of my web application doesn't work on MySQL5 e.g following statement works in 4 but not 5 SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as cat_state, c.name as cat_name, c.description as cat_desc, c.image, c.url, m.member_name as mod_name, m.member_id as mod_id, m.is_group, m.group_id, m.group_name, m.mid FROM ibf_forums f, ibf_categories c LEFT JOIN ibf_moderators m ON (f.id=m.forum_id) WHERE c.id=f.category ORDER BY c.position, f.position Error: Unknown column 'f.id' in 'on clause' The alias seem not working? What should I do, I dont want to rewrite all my sql statement Thanks. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.26 has been released
Hi, MySQL 5.0.26, a new version of the popular Open Source Database Management System, has been released. The Community Edition is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production release family. This section documents all changes and bug fixes that have been applied since the last official MySQL release. If you would like to receive more fine-grained and personalized update alerts about fixes that are relevant to the version and features you use, please consider subscribing to MySQL Network (a commercial MySQL offering). For more details please see http://www.mysql.com/network/advisors.html. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. We welcome and appreciate your feedback! Changes in release 5.0.26 This is a bugfix release for the current production release family. Functionality added or changed: * The source distribution has been updated so that the UDF example can be compiled under Windows with CMake. See Section 24.2.4.5, Compiling and Installing User-Defined Functions. (Bug#19121: http://bugs.mysql.com/19121) * The LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section 13.6.2.2, LOAD DATA FROM MASTER Syntax, for recommended alternatives. (Bug#18822: http://bugs.mysql.com/18822) * mysqldump now has a --flush-privileges option. It causes mysqldump to emit a FLUSH PRIVILEGES statement after dumping the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restoration. (Bug#21424: http://bugs.mysql.com/21424) * The number of InnoDB threads is no longer limited to 1,000 on Windows. (Bug#22268: http://bugs.mysql.com/22268) Bugs fixed: * Deleting entries from a large MyISAM index could cause index corruption when it needed to shrink. Deletes from an index can happen when a record is deleted, when a key changes and must be moved and when a key must be un-inserted because of a duplicate key. This can also happen in REPAIR TABLE when a duplicate key is found and in myisamchk when sorting the records by an index. (Bug#22384: http://bugs.mysql.com/22384) * Conversion of values inserted into a BIT column could affect adjacent columns. (Bug#22271: http://bugs.mysql.com/22271) * The URL into the online manual that is printed in the stack trace message by the server was out of date. (Bug#21449: http://bugs.mysql.com/21449) * PROCEDURE ANALYSE() returned incorrect values of M FLOAT(M, D) and DOUBLE(M, D). (Bug#20305: http://bugs.mysql.com/20305) * Join conditions using partial indexes on utf8 columns of InnoDB tables incorrectly ignored rows where the length of the actual value was greater than the length of the partial index. (Bug#19960: http://bugs.mysql.com/19960) * On an INSERT into an updatable but non-insertable view, an error message was issued stating that the view was not updatable. Now the message says the view is not insertable-into. (Bug#5505: http://bugs.mysql.com/5505) * INSERT DELAYED did not honor SET INSERT_ID or the auto_increment_* system variables. (Bug#20627: http://bugs.mysql.com/20627, Bug# 20830) * For character sets having a mbmaxlen value of 2, any ALTER TABLE statement changed TEXT columns to MEDIUMTEXT. (Bug#21620: http://bugs.mysql.com/21620) * A query that used GROUP BY and an ALL or ANY quantified subquery in a HAVING clause could trigger an assertion failure. (Bug#21853: http://bugs.mysql.com/21853) * For an ENUM column that used the ucs2 character set, using ALTER TABLE to modify the column definition caused the default value to be lost. (Bug#20108: http://bugs.mysql.com/20108) * mysql_com.h unnecessarily referred to the ulong type. (Bug#7: http://bugs.mysql.com/7) * Incorporated some portability fixes into the definition of __attribute__ in my_global.h. (Bug#2717: http://bugs.mysql.com/2717) * Linking the pthreads library to single-threaded MySQL libraries caused dlopen() to fail at runtime on HP-UX. (Bug#18267: http://bugs.mysql.com/18267) * In the package of pre-built time zone tables that is available for download at http://dev.mysql.com/downloads/timezones.html, the tables now explicitly use the utf8 character set so that they work the same way regardless of the system character set value. (Bug#21208: http://bugs.mysql.com/21208) * The build process incorrectly tried to overwrite
Re: hello everyone
MySQL 5 has a CSV storage engine You can read Page 639 of the MySQL Administrator's Guide and Language Reference (2nd edition) ISBN 0-672-328700-4 Try this: Step 1: CREATE TABLE NewDataCSV (firstname varchar(30),lastname varchar(30)) Engine=CSV; This should create NewDataCSV.frm and NewDataCSV.csv in the datadir of MySQL You can insert into the Table with INSERT INTO NewDataCSV VALUES (...); CSV tables do not support indexes. All queries against this table is a full table scan every time. You may want some speed when searching. So, create another table with same structure as the CSV table, like the following: Step 2: CREATE TABLE NewData (firstname varchar(30),lastname varchar(30)) Engine=MyISAM; Now you can create indexes against NewData. ALTER TABLE NewData ADD INDEX name (lastname,firstname); Step 3: If you already have a CSV text file called alanmadsen.csv on a floppy, just do this after you ran 'CREATE TABLE NewDataCSV ...': copy A:\alanmadsen.csv NewDataCSV.csv Step 4: After doing all this, now load NewData from NewDataCSV as follows: INSERT INTO NewData SELECT * FROM NewDataCSV; If you want to load a larger CSV file a month later, do this: DROP TABLE NewDataCSV; DROP TABLE NewData; Then repeat Steps 1-4 As far as the methodology to access the CSV file in PHP it works the same way as with other table engines. Make all requests for data from NewData not NewDataCSV. - Original Message - From: alan [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, October 9, 2006 10:35:54 PM GMT-0500 US/Eastern Subject: hello everyone my name is alan madsen. while i am very well grounded in complex systems and database management that is archaic by today's standards, i am looking at a creating a server-side php/mysql environment for a very simple database application with only the experience of recent light reading and knowing that i've successfully installed a wamp serverkit on windows 2k (uniform server) that includes mysql5: The Uniform Server is a lightweight server solution for running a web server under the WindowsOS. 5.79MB! It includes the latest versions of Apache2, Perl5, PHP5, MySQL5, [and] phpMyAdmin http://sourceforge.net/projects/miniserver/ running on a laptop, i've seen this installation's instance of apache serve web pages to the net. very nice. i'd like to create, load, and maintain, a mysql database table containing fewer than 8,000 records, each with 5 fields (rows?) - lengths ranging from 10 bytes to 80 bytes - of character data, one field of which would be used as data and as a isamkey (com- pound keys would be nice, but they are not necessary). assuming a working installation of mysql5 and that a csv data file exists, will someone outline what steps are necssary to: 1. create such a table, 2. load it, and 3. the mysql methodology to access it via php/mysql_isam? any comment would be appreciated. regards, Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advice on multilingual databases?
Here is my suggestion, but like every other thing I post here I urge you to take it with a grain of salt. Set up the following tables (described in rough terms, not in SQL): topic_index topic_id autonumber ... Whatever else you need to keep track of that identifies a topic topic_detail topic_id long integer primary index topic_language indexed (perhaps) parent_topic_id indexed topic_language_text (utf-8, collate utf8_unicode_ci) ... Whatever else you need that is specific to this language / topic combo Now, whenever you add a topic you make an entry for a new topic, you make a record in topic_index that identifies the topic uniquely. Make corresponding entries in topic_detail using the topic_id that was just assigned to topic_index's topic_id: there will be one such record for each language. Now you can locate the topic itself in the topic_index table and quickly pull all of the corresponding records for the different languages from topic_detail. You can also easily find all topics that have entries in a particular language, or that do not have entries in a particular language, etc. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Zembower, Kevin [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 9:36 AM To: mysql@lists.mysql.com Subject: Advice on multilingual databases? I'd like some advice on setting up databases that contain entries for the same item in more than one language. For instance, here's what I currently do for a table that contains the same topics translated into English and Arabic: CREATE TABLE `TOPIC` ( `TopicID` int(11) NOT NULL auto_increment, `Topic-en` text NOT NULL, `Topic-ar` text character set utf8 collate utf8_unicode_ci NOT NULL, `ParentTopicID` int(11) NOT NULL default '0', PRIMARY KEY (`TopicID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Topics for text-based hierarchies' AUTO_INCREMENT=76 ; In this table, 'Topic-ar' is the Arabic translation of the English 'Topic-en.' If this were required to also be in Spanish and French, I'd add columns for 'Topic-es' and 'Topic-fr' similar to 'Topic-en' above. I'm wondering if there are any other ways to store and access multilingual data. Can anyone suggest other ways they've dealt with this task, and the pros and cons of their approach compared to mine? Thank you in advance for your advice and suggestions. -Kevin Kevin Zembower Internet Services Group manager Center for Communication Programs Bloomberg School of Public Health Johns Hopkins University 111 Market Place, Suite 310 Baltimore, Maryland 21202 410-659-6139 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL statement work in mysql4 but not mysql5
Jason following statement works in 4 but not 5 SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as cat_state, c.name as cat_name, c.description as cat_desc, c.image, c.url, m.member_name as mod_name, m.member_id as mod_id, m.is_group, m.group_id, m.group_name, m.mid FROM ibf_forums f, ibf_categories c LEFT JOIN ibf_moderators m ON (f.id=m.forum_id) WHERE c.id=f.category ORDER BY c.position, f.position Look up joins in the 5.0 or 5.1 manual. As of 5.0.12, MySQL implemented ANSI/ISO compliance, breaking comma joins of the sort you use above. You need explicit JOIN ... ON | USING syntax to remove referential ambiguities. PB - Jason Chan wrote: I am going to upgrade my database from version 4 to 5. However I found some of my web application doesn't work on MySQL5 e.g following statement works in 4 but not 5 SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as cat_state, c.name as cat_name, c.description as cat_desc, c.image, c.url, m.member_name as mod_name, m.member_id as mod_id, m.is_group, m.group_id, m.group_name, m.mid FROM ibf_forums f, ibf_categories c LEFT JOIN ibf_moderators m ON (f.id=m.forum_id) WHERE c.id=f.category ORDER BY c.position, f.position Error: Unknown column 'f.id' in 'on clause' The alias seem not working? What should I do, I dont want to rewrite all my sql statement Thanks. Jason -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.1/470 - Release Date: 10/10/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 SP question: can I use parameter in LIMIT clause?
I want to write a sp return paging of recordset. CREATE PROCEDURE `sp_GetJobsDetails`(Page INT, PageSize INT) BEGIN DECLARE RecordBegin INT; DECLARE tmpPageSize INT; SET RecordBegin = Page * PageSize - PageSize; SET tmpPageSize = PageSize + 1; SELECT JOB_ID FROM JOB LIMIT RecordBegin, tmpPageSize; - this line cause error, does it supported? END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting char in a column
I'm running MySQL 5.0.15 on Windows system. How do I count how many specific char is there in a column, for example finding 'c' in lowercase string of Characteristics would total to 3. -- `Twas brillig, and the slithy toves Did gyre and gimble in the wabe: All mimsy were the borogoves, And the mome raths outgrabe.
Re: Which AMD Dual Core Processor?
On Fri, 01 Sep 2006 16:35:08 -0500, [EMAIL PROTECTED] wrote: At 02:59 PM 9/1/2006, you wrote: mos wrote: AMD Athlon 64X2 3800+ Dual Core S939 Manchester (2x512K cache) AMD Athlon 64X2 4200+ Dual Core S939 Manchester (2x512k cache) AMD Athlon 64X2 4400+ Dual Core S939 Toledo (2x1MB cache) AMD Athlon 64X2 4600+ Dual Core S939 Manchester (2x512k cache) I would think, as a blind guess, that the Toledo processor (twice the cache) would be the hands-down winner in this list. I just wanted to know if anyone actually has used the chip and has seen a difference using 1mb cpu cache compared to the 512k cache. TIA It would be meaningless unless you ran the identical programs. The classic problem with the big cache is too many out-of-cache hits causing cache dumps then reloads. In a small cache, that same problem takes less time to resolve. The problem gets ugly when various tricks (i.e. more lost time) get pulled to minimize the 'outs in a big cache. The short answer is that if big caches _really_ made a difference, every processor would have one. Specific applications can benefit, but the general result is bigger is not better. As for hyperthreading, my experience differs. -- Reply-To email is ignored. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Yup, innodb_locks_unsafe_for_binlog=1 fixes the problem and so does your suggestion of using a JOIN instead of a subselect. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 8:54 AM To: Baron Schwartz; Rick James Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Locks
It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals
RE: Innodb Locks
It probably uses a single lock to handle a JOIN, and two locks to handle a sub-SELECT. I doubt that it helps, but if I'm right it will change what you see when you poking around. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 2:42 PM To: Baron Schwartz Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after
RE: Innodb Locks
Right, as I understand it the query optimizer in 5.2 will simply rewrite these sub selects as joins when possible. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:45 AM To: Robert DiFalco; 'Baron Schwartz' Cc: 'Rick James'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks It probably uses a single lock to handle a JOIN, and two locks to handle a sub-SELECT. I doubt that it helps, but if I'm right it will change what you see when you poking around. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 2:42 PM To: Baron Schwartz Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED]
purging bin logs
Hi All, I have a question on purging some old bin-logs. whats the best way to do it? This is a fairly old version - 4.0.18-standard-log. I have 128 1 GB files out there, going back 8 months. I think the correct syntax is : PURGE BINARY LOGS TO 'mysql-bin.010'; but from what the previous admin who I inherited this from says, this locks up the whole database while its purging the logs. Are there any low-impact solutions? This is a fairly high traffic DB, so locking up the database really is not an option. Thanks! George Law glaw at ionosphere.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
java.util.Date versus java.sql.Date
Hello Besides some obvious differences in implementations between util.Date and sql.Date are there any other issues/advantages or disadvantages using one versus the other? Thanks
RE: Innodb Locks
Oops, I should have emphasized that the TEMPORARY TABLE should be MyISAM or
Memory so as to avoid locking on it. (This _assumes_ that it is ok to split
the SELECT and DELETE into separate transactions. Often the semantics of
such a move allow such. YMMV)
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:50 AM
To: Jerry Schwartz; Baron Schwartz
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Innodb Locks
Right, as I understand it the query optimizer in 5.2 will
simply rewrite
these sub selects as joins when possible.
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:45 AM
To: Robert DiFalco; 'Baron Schwartz'
Cc: 'Rick James'; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Innodb Locks
It probably uses a single lock to handle a JOIN, and two
locks to handle
a sub-SELECT. I doubt that it helps, but if I'm right it will change
what you see when you poking around.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 2:42 PM
To: Baron Schwartz
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: RE: Innodb Locks
Then I guess I am not understanding why re-writing the
statement as a
JOIN alleviates that need.
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:35 AM
To: Robert DiFalco
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
Subject: Re: Innodb Locks
It's not a bug in InnoDB. There are far more knowledgeable people
than I on this list, but it should get a share-mode lock on
anything
it selects from, otherwise there might be inconsistencies
as it tries
to serialize different transactions into the binary log for
replication.
If the statement reads different values from Elems on the
master and
slave, there'll be different results. So locking is necessary for
replication to work right.
If I'm glossing over the finer points too much, it's
because I don't
know them.
Someone else can surely correct me :-)
Baron
Robert DiFalco wrote:
I think what is strange to me is that InnoDB is locking on the
subquery table at all. Here's another example:
DELETE
FROM Vers
WHERE (
Vers.elementID IN (
SELECT Elems.ID
FROM Elems
WHERE (Elems.nodeID = ?)))
Disregarding whether performance would be better or worse
with a JOIN,
what I find odd is that this DELETE statement on Vers seems to be
putting locks on Elems. Might this be a bug in InnoDB?
Innotop has
this to say:
Locks Held and Waited For
Txn What Mode DB Tbl Index Heap Special
Ins Intent
1 waits_for Xte elems PRIMARY2 rec but not gap
0
Not that Txn 1 is an UPDATE on a single row of the ELEMS
table and it
is waiting for the LOCK from the above DELETE FROM Vers to be
released. I'm not sure why the DELETE statement is locking the
subquery table ELEMS which is simply being queried. Do I
*really* need
to change all of these to write the subquery to a temporary
table in
order to gain better concurrency?
R.
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 03, 2006 1:39 PM
To: Rick James
Cc: Robert DiFalco; mysql@lists.mysql.com;
[EMAIL PROTECTED]
Subject: Re: Innodb Locks
There is a detailed write-up on how locking works in the manual:
http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html
If you are not doing replication, you might check out
innodb_locks_unsafe_for_binlog as mentioned in
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter
Z also wrote an article on this:
http://www.mysqlperformanceblog.com/category/replication/
You may get better performance from using a JOIN instead
of an IN()
subquery. You will have to test. Sometimes it is much better,
sometimes worse. Usually better in my experience. Making the
long-running query as short as possible is probably a good idea.
Maybe you can break it up into several queries so it
doesn't try to
lock so many rows at once. There could be many other
approaches too,
it just depends on your needs and data.
Without altering how locks are handled with startup options, the
temporary table approach will avoid the locks only if you
COMMIT after
the CREATE... SELECT. The other subquery approach will not avoid
them.
I'm not sure if I should be replying to both the 'internals' and
RE: purging bin logs
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html DÆVID -Original Message- From: George Law [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:56 AM To: mysql@lists.mysql.com Subject: purging bin logs Hi All, I have a question on purging some old bin-logs. whats the best way to do it? This is a fairly old version - 4.0.18-standard-log. I have 128 1 GB files out there, going back 8 months. I think the correct syntax is : PURGE BINARY LOGS TO 'mysql-bin.010'; but from what the previous admin who I inherited this from says, this locks up the whole database while its purging the logs. Are there any low-impact solutions? This is a fairly high traffic DB, so locking up the database really is not an option. Thanks! George Law glaw at ionosphere.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: java.util.Date versus java.sql.Date
A java.sql.Date does not have time information - just the day-month-year. A java.util.Date has date and time information. If you need date and time, use java.sql.Timestamp. It's not very pretty moving from one to the other. David -Original Message- From: Feliks Shvartsburd [mailto:[EMAIL PROTECTED] Sent: October 10, 2006 12:04 PM To: mysql@lists.mysql.com Subject: java.util.Date versus java.sql.Date Hello Besides some obvious differences in implementations between util.Date and sql.Date are there any other issues/advantages or disadvantages using one versus the other? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AND-conjunction of rows
Hi list, I have three tables: product (ID, name) product2attribute (ID, product, attribute) attribute (ID, name) Product - Attribute is an n:m relation, so one product can have two or more attributes and of course there can be many products with an attribute. I want to select alle products that have attribute A and attribute B. The only query that came to my mind was something like SELECT * FROM product WHERE EXISTS (SELECT * FROM product2attribute WHERE attribute = ...) AND EXISTS (SELECT * FROM product2attribute WHERE attribute = ...). Is this the correct and only query to accomplish that? Regards, André -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing mySQL
Surendra Singhi wrote: Hi, I am using mySQL 5.0 and I have 2 tables with few hundred millions of records. To optimize things, I am using MyISAM tables, using the smallest possible data type and have set indexes. Now, the problem which I am facing is that mySql process is wasting lot of time in disk access the CPU and memory utilization is very low. Most of the table access are simple queries, with very few write operations. What can I do optimize things and make queries faster? My CPU is 64bit Athlon 3000, with 950 MB of RAM running Ubuntu Edgy Eft. I don't mind making mysql eat lot of CPU and RAM. Just want things to be much faster, and loaded into memory instead of slow disk access. The my.cnf file contains: key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 16777216 query_cache_type= 1 [isamchk] key_buffer = 16M I am considering increasing these values, please advise on what should I set them. Also, any other tips will be extremely helpful. Completely depends on the queries you are running and the context of which they are run. A query that runs once a day won't matter if it's slow, a query that runs every time you do something will matter a lot. This page might give you some ideas: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Enable the slow query log in mysql and triple check that you have the proper indexes in place. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does mysql drop index very very slow in a large table?
1) create table T1 like T; This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4. 2) alter table T1 drop index ndx3; This drops index ndx3 on the empty T1, which should be instantaneous. 3) insert into T1 select * from T; This will populate table T and load all three(3) indexes for T1 in one pass. Insert millions of rows into table should be very slow, and obviously be not efficient. Can I use `cp -f T.MYD T1.MYD` instead ? 4) drop table table T; 5) alter table T1 rename to T; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting char in a column
Hi,
MySQL dosen't have built-in function for counting substring. But we can
create user-defined functions for this.
Like,
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);
Then try,
SELECT substrCount('Characteristics', 'c') as count;
which returns 3.
For more reference
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Thanks,
ViSolve DB Team.
- Original Message -
From: Scott Hamm [EMAIL PROTECTED]
To: 'Mysql ' mysql@lists.mysql.com
Sent: Tuesday, October 10, 2006 10:58 PM
Subject: Counting char in a column
I'm running MySQL 5.0.15 on Windows system.
How do I count how many specific char is there in a column, for example
finding 'c' in lowercase string of Characteristics would total to 3.
--
`Twas brillig, and the slithy toves
Did gyre and gimble in the wabe:
All mimsy were the borogoves,
And the mome raths outgrabe.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
