innodb performance issues
Hi, A few days ago i posted a quaestion about performace, I now have a little more info, hopefully someone can help. I have a table, tblShoppingCart with 3 fields, cartUid (int 11 auto increment) userUid (int 11, indexed) strCartHash (varchar 32) The table is innodb Nomally my server load is below 0.1 and everythings fine, I have a process that runs occasionally that pushes the load up to 1.5, when this happens inserts into the table seem to get blocked, ie taking up to 20 seconds, as soon as the load drops the inserts are fine again. Interestingly, if I convert the table to myisam I don't get this problem. However I really want to keep the table innodb as I use it in transactions latter. My my.cnf file is coppied from the default huge.cnf file, i have duel xeons with 4gb of ram and i'm running mysql 4.1.1 on red hat linux. Any pointers on where i can look further appreciated. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL 'clustering' query?
On Thu, 14 Jul 2005, Peter Brawley wrote: Dan, Hi, I remember reading about an SQL query type which did something like select all 'aircraft hangers' which contained exactly (or at least) some given set of aircraft. Or did it select the list of pilots qualified to fly all the airplanes in the hanger... Anyway, I forget the syntax (and the fancy name for this kind of query). I think the concept you're after is relational division. The aeroplane hangar was one of Celko's examples. Another from him is at http://www.artfulsoftware.com/queries.php#28, other examples at http://www.artfulsoftware.com/queries.php#22, http://www.artfulsoftware.com/queries.php#33, Cheers, I will try to rephrase my query below as 'RELATIONAL DIVISION' :) In the short term I solved my problem with group_concat_max_len system variable - For some reason I thought it would already be at the maximum lenght. I doubled it up and saw my warnings dissapear Warning: 1260 Thanks very much for the above links, Dan. PB Dan Bolser wrote: Hi, I remember reading about an SQL query type which did something like select all 'aircraft hangers' which contained exactly (or at least) some given set of aircraft. Or did it select the list of pilots qualified to fly all the airplanes in the hanger... Anyway, I forget the syntax (and the fancy name for this kind of query). What I want to do is the following, given this data... Table: ATTRIBUTE_LIST; IDATTRIBUTE W A W B W C X A X B X C Y A Y B Y C Y D Z E -- SQL MAGIC -- Table: CLUSTERS G_ID ID 1 W 1 X 2 Y 3 Z That is, to group together all ID's with the same 'set' of ATTRIBUTES. Currently I am doing this using 'GROUP_CONCAT', but my attribute list just went above the limit for the GROUP_CONCAT column... +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1260 | 7 line(s) were cut by GROUP_CONCAT() | +-+--+--+ My query looks roughly like this... SET @i:=0, @x:='', @row:=''; # DROPTABLE CLUSTERS; CREATE TABLE CLUSTERS (PRIMARY KEY (ID), INDEX (G_ID)) # SELECT ID, G_ID # FROM ( SELECT ID, # @x:= ATTR_LIST AS HIDDEN1, # IF(@row = @x, @i, @i:[EMAIL PROTECTED]) AS G_ID, # @row:= @x AS HIDDEN2 # FROM ( SELECT ID, GROUP_CONCAT(ATTRIBUTE) AS ATTR_LIST, FROM ATTRIBUTE_LIST GROUP BY ID # ) AS vt1 # ORDER BY -- This is very important for ATTR_LIST -- the overall query. # ) AS vt2; (And thats the highly simplified version!) I can't shake the feeling that this 'string based' approach (while quite speedy) is inherently messy, and that a proper 'set based' approach should exist, and shouldn't have the limitation in the number of attributes that the above method has. In general I would really like to (somehow) develop a suite of easy to use 'SQL CLUSTER' commands, as the data mining community needs that kind of thing in nice general (set based) abundance :) Anyway, thanks for any feedback on any of the above, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.0 to 4.1 migration and charset problems
Hello. It is not clear from you message what's the problem. From your calculations I can see that the length of int field in a flat file row (BTW please send a piece of your file) is different in mysqldump programs with different versions? How is the length of integer fields related to character set? Please provide more information about the problem and if it is possible send a test case. Vivian Wang [EMAIL PROTECTED] wrote: [-- text/plain, encoding 7bit, charset: us-ascii, 132 lines --] the version is 4.1.12. show variables like this, | character_set_client| latin1 | character_set_connection| latin1 | character_set_database | latin1 | character_set_results | latin1 | character_set_server| latin1 | character_set_system| utf8 | character_sets_dir | /usr/share/mysql/charsets/ | collation_connection| latin1_swedish_ci | collation_database | latin1_swedish_ci | collation_server| latin1_swedish_ci If the table is like test(name char(30), id1 int(4), id2 int(4)) When I use mysqldump mysql version 3.23.??, l have the flat file row length is 30+4+4=38. When I use mysqldump mysql verson 4.1.12, I have the flat file row length is 30+11+11=52. I tried mysqldump --set-charset=latin2, I still got a row length=52. What I should do? Gleb Paharenko wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ 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 forgets user passwords
Hello. PASSWORD '3446cb892d3dffdd' WITH GRANT OPTION | You're using passwords in old format. Is it possible that problem somehow related to this. Are you connecting using mysql command line client? What version it is? Chris Fonnesbeck [EMAIL PROTECTED] wrote: On 7/14/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. =20 =20 =20 =20 What does =20 show grants for 'chris'@'localhost'; =20 =20 reports when you're logged as root? =20 I get the following: | GRANT ALL PRIVILEGES ON *.* TO 'chris'@'localhost' IDENTIFIED BY PASSWORD '3446cb892d3dffdd' WITH GRANT OPTION | -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select based letter
Hello. What about: select topics from topics where topics like 'M%'; Sebastian [EMAIL PROTECTED] wrote: i have a text field column and i want to select the rows based on the first letter in this column, eg: - | topics | - Motherboard Hard Drives Memory Video Cards Monitors i want to select all the rows that begin with the letter M (Motherboard, Memory, Monitors) any suggestions? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BOOLEAN Vs NON-Boolean
Hello,
I've just upgraded to 4.1 for boolean search facilities, but I think I may
lose more than I 've gained.
I just really need the +keyword1 keyword2 functionality so all results
must contain keyword1, but I then need to order by relevance score which
I've lost.
Is it worth performing two non-boolean searches to get what I need or could
I use
SELECT id, keywords, MATCH (keywords) AGAINST (keyword1 keyword2')
AS score FROM table
WHERE MATCH (keywords) AGAINST ('+keyword1 keyword2' in boolean mode) order
by score desc;
Which approach would have the biggest overhead, an could there be a better
approach?
Cheers,
Lee
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting default character_set_results
Hi, I have a problem with character_set_results on MySQL 4.1. I have this in my my.cnf: [mysqld] default-character-set=ujis default-collation=ujis_japanese_ci [client] default-character-set=ujis [mysql] default-character-set=ujis If I connect from mysql client, and do 'show variables' I get the right (ujis) character_set_results. But, when I connect from Perl DBI, I get latin1 instead of ujis. The only way to force the character_set_results to ujis in Perl DBI is to do: $dbh-do(SET character_set_results=ujis'); Is there any way to set the character_set_results in my.cnf, so I don't need to do it from Perl DBI every time I connect? Regards, --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
VARCHAR changes to INT - UNION
Hi list Im new to the list so i hope it's the right place for my post. For a projekt i am combining different tables. Everything seems to work, but in some rows the Kode field VARCHAR(10) ends up as INT. I will try to explain with an example. Table1 46 47 48 67 Table2 BBEGYNDER BVILDIVAND 01ELITE 02SSKOLEN When i combine these i get 46 47 48 67 0 0 01 02 The desired result should be: 46 47 48 67 BBEGYNDER BVILDIVAND 01ELITE 02SSKOLEN == MySQL statements to create the combined table == === DROP TABLE IF EXISTS searchTemp; CREATE TABLE searchTemp ( `table` VARCHAR( 25 ) NOT NULL , `id` VARCHAR ( 10 ) NOT NULL , `headline` TEXT NOT NULL , `teaser` TEXT NOT NULL , `text` TEXT NOT NULL , `image_id` INT NOT NULL ); ALTER TABLE `searchTemp` ADD FULLTEXT ( `headline` , `teaser` , `text` ); INSERT INTO searchTemp ( SELECT 'content', id, headline, text, teaser, image_id FROM content ) UNION ALL ( SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline, Beskrivelse AS text, '', image_id FROM wesHoldKategori ); === Anyone who can explain why this is happening and how i can solve it? Thanks in advance Dennis Duggen -- Airconditioners and computers have one thing in common: Once you open windows everything fails. -- Firefox - Safer, Faster, Better http://www.mozilla.org/products/firefox/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Charset and collation question
Hi all, Which charset and collatio would be the most suitable for a french / english system? Thanks Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VARCHAR changes to INT - UNION
Dennis Duggen wrote: Hi list Im new to the list so i hope it's the right place for my post. For a projekt i am combining different tables. Everything seems to work, but in some rows the Kode field VARCHAR(10) ends up as INT. I will try to explain with an example. Table1 46 47 48 67 Table2 BBEGYNDER BVILDIVAND 01ELITE 02SSKOLEN When i combine these i get 46 47 48 67 0 0 01 02 The desired result should be: 46 47 48 67 BBEGYNDER BVILDIVAND 01ELITE 02SSKOLEN According to: http://dev.mysql.com/doc/mysql/en/union.html coresponding columns of statements' results should have the same type. Try to cnage order of SELECTs in your query.. I think you'll get what you want. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: VARCHAR changes to INT - UNION
Eugene Kosov wrote: Dennis Duggen wrote: Hi list Im new to the list so i hope it's the right place for my post. For a projekt i am combining different tables. Everything seems to work, but in some rows the Kode field VARCHAR(10) ends up as INT. I will try to explain with an example. Table1 46 47 48 67 Table2 BBEGYNDER BVILDIVAND 01ELITE 02SSKOLEN When i combine these i get 46 47 48 67 0 0 01 02 The desired result should be: 46 47 48 67 BBEGYNDER BVILDIVAND 01ELITE 02SSKOLEN According to: http://dev.mysql.com/doc/mysql/en/union.html coresponding columns of statements' results should have the same type. Try to cnage order of SELECTs in your query.. I think you'll get what you want. Right, the type of each column is determined by the first SELECT in the UNION. Hence, your VARCHARs are converted to INTs because they are in an INT column based on the first SELECT. You can fix this by making that column a string column of appropriate length. Changing the order would work: INSERT INTO searchTemp ( SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline, Beskrivelse AS text, '', image_id FROM wesHoldKategori ) UNION ALL ( SELECT 'content', id, headline, text, teaser, image_id FROM content ); Alternatively, you could keep the same order, but change the problem column to a string: INSERT INTO searchTemp ( SELECT 'content', CONCAT(id,''), headline, text, teaser, image_id FROM content ) UNION ALL ( SELECT 'wesHoldKategori', wesHoldKategori.Kode AS id, Navn AS headline, Beskrivelse AS text, '', image_id FROM wesHoldKategori ); Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DROP FUNCTION doesn't work
Hello. Please check, if weird behavior remains on the latest release (4.1.12). Try to run FLUSH PRIVILEGES after 'drop function' statement and cast a look if mysql.func table is clear. Do you use xxx_deinit in your udf? I mean, is it possible that your function has some problem during deinitialization, so it can't be removed. [EMAIL PROTECTED] wrote: I can successfully LOAD a UDF in mysql-4.1.10, but I can't DROP it. MySQL reports that the DROP FUNCTION was OK, but the function still shows up in the mysql.func table. The function no longer works, but I have to TRUNCATE the mysql.func table in order to be able to LOAD it again. I am doing this with the root account so I don't think it's a privileges problem. See below: mysql create function betatouni returns string soname 'libbeta2.so'; Query OK, 0 rows affected (0.12 sec) mysql drop function betatouni; Query OK, 0 rows affected (0.00 sec) mysql select * from func; +---+-+-+--+ | name | ret | dl | type | +---+-+-+--+ | betatouni | 0 | libbeta2.so | function | +---+-+-+--+ 1 row in set (0.00 sec) mysql create function betatouni returns string soname 'libbeta2.so'; ERROR 1026 (HY000): Error writing file 'mysql.func' (errno: 121) Thanks for any help you can provide, Jeremy -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting default character_set_results
Hello. Please check, if putting of these lines into my.cnf solves the problem: [dbdmysql] default-character-set = ujis I've found this suggestion at: http://lists.mysql.com/perl/2286 Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I have a problem with character_set_results on MySQL 4.1. I have this in my my.cnf: [mysqld] default-character-set=ujis default-collation=ujis_japanese_ci [client] default-character-set=ujis [mysql] default-character-set=ujis If I connect from mysql client, and do 'show variables' I get the right (ujis) character_set_results. But, when I connect from Perl DBI, I get latin1 instead of ujis. The only way to force the character_set_results to ujis in Perl DBI is to do: $dbh-do(SET character_set_results=ujis'); Is there any way to set the character_set_results in my.cnf, so I don't need to do it from Perl DBI every time I connect? Regards, --bk -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
Hello. If all your data has the same encoding it shouldn't be a problem. Just check that character_set_server and character_set_database variables have 'utf8' value for each database in which you're going to import data. Than invokes mysql with --default-character-set = 'encoding_of_your_data'. All tables should be created with default utf8 charset and data will be converted to utf8 during the import. See: http://dev.mysql.com/doc/mysql/en/charset-map.html Miles Keaton [EMAIL PROTECTED] wrote: I've got some years-old MySQL databases mostly in 4.0, but one server running 3.23 that are all using the default encoding. I want to update all their data to 4.1 with UTF-8 encoding. Anyone done this kind of dump-and-update?Any advice to share or good URLs you've seen with others' advice about this? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Charset and collation question
Hello. Similar question has been asked already. See: http://lists.mysql.com/mysql/179154 Stephane Savage [EMAIL PROTECTED] wrote: Hi all, Which charset and collatio would be the most suitable for a french / english system? Thanks Stephane -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure in MySQL 5.x
How effective is Stored Procedure in MySQL 5.x? -- Power to people, Linux is here.
Group By query optimization
Hi All, I have a mysql query which takes 8 seconds to run ona dual xeon 2.4, 3Gig ram box, SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; Explain select gives table typepossible_keys key key_len ref rowsExtra gamesessionsALL NULLNULLNULLNULL915522 Using where; Using temporary; Using filesort How can I optimize the query Thanks Kishore -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Tony, You said that you copied the my.cnf file from huge.cnf - not sure what version you are using (I missed your original post), but the my-huge.cnf in mysql 4.0.24 is for MyISAM. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. The my-huge.cnf allocates way to much memory to the MyISAM engine. All the innodb stuff is commented out. If you want help, you'll need to post your my.cnf file, the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). You'll need to also post the queries that are hitting the database while you're having these issues. David tony wrote: Hi, A few days ago i posted a quaestion about performace, I now have a little more info, hopefully someone can help. I have a table, tblShoppingCart with 3 fields, cartUid (int 11 auto increment) userUid (int 11, indexed) strCartHash (varchar 32) The table is innodb Nomally my server load is below 0.1 and everythings fine, I have a process that runs occasionally that pushes the load up to 1.5, when this happens inserts into the table seem to get blocked, ie taking up to 20 seconds, as soon as the load drops the inserts are fine again. Interestingly, if I convert the table to myisam I don't get this problem. However I really want to keep the table innodb as I use it in transactions latter. My my.cnf file is coppied from the default huge.cnf file, i have duel xeons with 4gb of ram and i'm running mysql 4.1.1 on red hat linux. Any pointers on where i can look further appreciated. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
I've got some years-old MySQL databases mostly in 4.0, but one server running 3.23 that are all using the default encoding. I want to update all their data to 4.1 with UTF-8 encoding. Anyone done this kind of dump-and-update?Any advice to share or good URLs you've seen with others' advice about this? Hi! We have been going through this process since January... the learning curve was steep and the resources hard to find. At the end of the day it's quite simple unless there are weird things already in your database... here's the process we use: On the original server using 4.0.n server and tools to match we run this: mysqldump --tab=/var/tmp/database database Then we move the directory /var/tmp/database to /var/tmp on the new server with 4.1 running... note this has the 4.1.n server AND the 4.1.n tools (such as mysql, mysqldump, mysqlimport and so on). Also note we have this line (amongst others) in our 4.1 my.cnf file: [mysqld] default-character-set=utf8 The upshot of this is that by default all new databases and tables will automatically create themselves with utf8 as the default character set unless told otherwise. For us this was important because we have MANY databases with MANY tables with MANY columns, and going through and setting the character set for each and every database/table/column was prohibitively expensive. HOWEVER... give some thought to this, the reality is for our setup there are maybe two or three columns in two or three tables in each database that really need to be UTF8 - most of the data doesn't need to be encoded this way, and there are some overheads to having everything encoded in utf8. But in the interests of time and quick conversion, we did it this way, we are reassessing it and may change things before we convert the rest of the databases. If you do decide to do this you may want to setup your mysql database using latin1 before setting the default for everything on the server to utf8... things like username/ hostname/password with 16 character varchar column type when converted to utf8 allows 16 bytes, and not 16 characters, and since utf8 allows multibyte characters you may only get 5 characters in your usernames etc... so there are little gotchas to setting the default character set for the whole server to utf8, and if you do, configure the mysql database separately on it's own. OK, having set the default character set for everything on the server to utf8 we go ahead and import our data into the server using this sequence of commands (we use a shell script, so that's what you get here). Call the shell script by giving it the database name as a flag (eg ./import database) - watch for differences in line breaks caused by email clients here, there are three lines of commands after setting DB=$1. #!/bin/sh # # LiveWorld's MySQL Import Script # Use for converting 4.0 databases to 4.1 UTF8 databases # Suitable for LiveWorld Servers only, use at your own risk # DB=$1 mysql --socket=/tmp/mysql.sock -e CREATE DATABASE $DB; cat /var/tmp/$DB/*sql | mysql --socket=/tmp/mysql.sock $DB mysqlimport --default-character-set=utf8 --socket=/tmp/mysql.sock $DB /var/tmp/$DB/*txt If you choose (probably wisely) not to set the default character set for the server to utf8 you can achieve the same result by making the first execution line of the above script to look like this: mysql --socket=/tmp/mysql.sock -e CREATE DATABASE $DB default character set utf8; Which will still have the affect of making your newly imported database use utf8 everywhere This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted on it's way in to 4.1, we don't loose anything along the way. Just again though I need to restate.. things like: username varchar(75) binary NOT NULL default '' take on a new meaning under utf8, it's no longer 75 characters, but 75 bytes, and utf8 encoded data takes more bytes. As well as potential data issues where you expect something to be 8 characters and it's really 24 bytes so having a varchar(8) may break new data inserts. There are also disk space issues that come out of this, and of course if you triple your disk usage there may also be new performance issues. Our recommendation is to do the import as above (this way you are sure to get your utf8 data in to the database the right way) and then go through (by script potentially) and convert the tables and columns that don't really need to be utf8 back to latin1... which is what we are looking at doing. Hope this has been a little helpful :-) Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BOOLEAN Vs NON-Boolean
you are doing it correctly.. you should be able to sort by relevance. it
know you can sort like that because my search engine does using boolean.
Lee Denny wrote:
Hello,
I've just upgraded to 4.1 for boolean search facilities, but I think I may
lose more than I 've gained.
I just really need the +keyword1 keyword2 functionality so all results
must contain keyword1, but I then need to order by relevance score which
I've lost.
Is it worth performing two non-boolean searches to get what I need or could
I use
SELECT id, keywords, MATCH (keywords) AGAINST (keyword1 keyword2')
AS score FROM table
WHERE MATCH (keywords) AGAINST ('+keyword1 keyword2' in boolean mode) order
by score desc;
Which approach would have the biggest overhead, an could there be a better
approach?
Cheers,
Lee
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure in MySQL 5.x
How effective? Very effective. Actually, I am testing it, and once I overcame a few syntax issues, it's working great. Procedures and functions both. I havent tested triggers yet. But so far I am using a mini-blog thing on my web site with them and I am loving the ease of Mysql combined (finally!) with the power of sprocs. Of course, it is beta right now, so I wouldnt stick it into anything you need to rely on yet, but get it going and try it for yourself. One thing to note, while the sprocs and functions work good on Windows, connecting with some client languages (python in my case) isnt working. Same goes for ODBC. If you use Linux, then it works fine. Oh, and another thing to note. If you use MySQL Query Browser to create the procecures, it has a bug and inserts the wrong delimiters in the drop if exists statement. That really messed me up at first. So, remove the // and insert the $$ and it will work great. Greg On 7/15/05, Scott Hamm [EMAIL PROTECTED] wrote: How effective is Stored Procedure in MySQL 5.x? -- Power to people, Linux is here. -- Greg Fischer 1st Byte Solutions http://www.1stbyte.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Hi David, On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote: Tony, - not sure what version you are using 4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and apache webserver and not much else. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. I can use this instead if it's going to help. If you want help, you'll need to post your my.cnf file, [client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M log = /var/log/mysql/mysql.log log-slow-queries= /var/log/mysql/mysql-slow.log set-variable= max_connections=250 server-id = 1 innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20 the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). tblCart | CREATE TABLE `tblCart` ( `intCartUid` int(11) NOT NULL auto_increment, `intUserUid` int(11) NOT NULL default '0', `tsCartCreated` datetime NOT NULL default '-00-00 00:00:00', `tsLastUpdated` datetime NOT NULL default '-00-00 00:00:00', `strCartHash` varchar(32) NOT NULL default '', PRIMARY KEY (`intCartUid`), KEY `intUserUid` (`intUserUid`), KEY `tsLastUpdated` (`tsLastUpdated`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 You'll need to also post the queries that are hitting the database while you're having these issues. # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=34475,timestamp=1121407309; INSERT INTO dbseThxWebOrders.tblCart (intUserUid,tsCartCreated,strCartHash) VALUES (0,now(),'4e5d105f7cd34268e1a5e160d479ed91'); is an example from my slow query log. All of the offending queries today were this same query. Thanks for you help Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
On 7/14/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. PASSWORD '3446cb892d3dffdd' WITH GRANT OPTION | You're using passwords in old format. Is it possible that problem somehow related to this. Are you connecting using mysql command line client? What version it is? I was trying from the command line and through python using MySQLdb. It is version 4.1.12 Thanks for the help, C. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create an Innodb database ?
I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb ? thanks, Darryl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create an Innodb database ?
Darryl Hoar wrote: I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb ? thanks, Darryl Hi Darryl, I am wondering my self about the question when to use innodb instead of myisam; because this engine is set to default. You need to check first if your MySql supports the InnoDB engine by invoking: SHOW ENGINES; I have read in the O'reilly book that you can overwrite the default myISAM engine by setting the mysqld-option: --default-storage-engine=type similair to --default-table-type=type in your .my.cnf file. So now also my question: When to use innodb instead of myisam? What performace advantages does this engine have? Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Tony, Your my.cnf file is set up for MyISAM, not InnoDB. MySQL allows you to allocate memory and resources to any and all storage engines. Yours is set up to give lots of resources to MyISAM, and none to InnoDB. Reducing MyISAM key_buffer = 384M - this is way too much - I'd set to to 2-16 meg, assuming that the only MyISAM tables you have are in the mysql database. query_cache_size = 32M - read up on the query cache - it's only useful for oft-repeated queries that hit tables in which the data rarely changes. We turn ours off The big variable in InnoDB (that affects performance the most) is the innodb_buffer_pool_size. Since you are running a xeon, I am guessing it's a 32-bit architecture. There is a limit on the max size of the process The amount of memory MySQL will use is: innodb_buffer_pool_size + key_buffer + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB You should make sure that stays under 2 gigabytes. If MySQL uses much more memory, it will crash. There are other tuning choices (including the thread-pool-cache). The best resource is the page on innodb performance tuning, and it can be found here: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html You might also want to consider High Performance MySQL. There is lots of good info in there on setup, tuning, replication, etc. David tony wrote: Hi David, On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote: Tony, - not sure what version you are using 4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and apache webserver and not much else. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. I can use this instead if it's going to help. If you want help, you'll need to post your my.cnf file, [client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M log = /var/log/mysql/mysql.log log-slow-queries= /var/log/mysql/mysql-slow.log set-variable= max_connections=250 server-id = 1 innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20 the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). tblCart | CREATE TABLE `tblCart` ( `intCartUid` int(11) NOT NULL auto_increment, `intUserUid` int(11) NOT NULL default '0', `tsCartCreated` datetime NOT NULL default '-00-00 00:00:00', `tsLastUpdated` datetime NOT NULL default '-00-00 00:00:00', `strCartHash` varchar(32) NOT NULL default '', PRIMARY KEY (`intCartUid`), KEY `intUserUid` (`intUserUid`), KEY `tsLastUpdated` (`tsLastUpdated`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 You'll need to also post the queries that are hitting the database while you're having these issues. # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=34475,timestamp=1121407309; INSERT INTO dbseThxWebOrders.tblCart (intUserUid,tsCartCreated,strCartHash) VALUES (0,now(),'4e5d105f7cd34268e1a5e160d479ed91'); is an example from my slow query log. All of the offending queries today were this same query. Thanks for you help Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create an Innodb database ?
Danny Stolle [EMAIL PROTECTED] writes: So now also my question: When to use innodb instead of myisam? What performace advantages does this engine have? Well, if you want to have transactions, you must use innodb... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create an Innodb database ?
At 03:09 PM 7/15/2005, you wrote: Darryl Hoar wrote: I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb ? thanks, Darryl Hi Darryl, So now also my question: When to use innodb instead of myisam? What performace advantages does this engine have? Darryl et al, As a general rule, you'd use InnoDb if you need transactions or if you expect to have more than 15-20 updates/sec to the same table. If all you're doing are Select statements or if only 4 or 5 people are updating the table at the same time, MyISAM may be all you need. MyISAM does very fast table locking whereas InnoDb does row locking. MyISAM imho is faster at executing general Select statements/joins and it has fulltext indexing which InnoDb does not yet have. The last I heard Heikki is working on fulltext indexing for InnoDb and it should be out in my lifetime.vbg Innodb is more robust than MyISAM when updating tables under heavy load. MyISAM will force users to wait for a table lock if there are a lot of people updating the same table. So if several people are hammering the same table with updates, InnoDb is the preferred engine to use. But you have to tweak InnoDb a lot more than MyISAM and it is difficult to get Selects to run as fast as MyISAM. That's my 2 cents worth. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow count(1) behavior with large tables
i'm trying to run this query: SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; MSGS has 9.5 million rows, and is indexed on BoardID MBOARD has 69K rows and is indexed on BaseType EXPLAIN shows: mysql explain SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; +---+--+--++-+---+---+-+ | table | type | possible_keys| key| key_len | ref | rows | Extra | +---+--+--++-+---+---+-+ | b | ref | PRIMARY,BaseType | BaseType | 1 | const | 48614 | | | m | ref | BoardIndex | BoardIndex | 4 | b.BoardID | 277 | Using index | +---+--+--++-+---+---+-+ the query takes several minutes to run. shouldn't this be a simple case of doing some math on index values? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow count(1) behavior with large tables
Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. Cheers, Andrew On 15/7/05 23:31, Jon Drukman [EMAIL PROTECTED] wrote: i'm trying to run this query: SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; MSGS has 9.5 million rows, and is indexed on BoardID MBOARD has 69K rows and is indexed on BaseType EXPLAIN shows: mysql explain SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; +---+--+--++-+---+---+ -+ | table | type | possible_keys| key| key_len | ref | rows | Extra | +---+--+--++-+---+---+ -+ | b | ref | PRIMARY,BaseType | BaseType | 1 | const | 48614 | | | m | ref | BoardIndex | BoardIndex | 4 | b.BoardID | 277 | Using index | +---+--+--++-+---+---+ -+ the query takes several minutes to run. shouldn't this be a simple case of doing some math on index values? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By query optimization
Hi, Put indexes on 'valid' and 'sessiontype' and all will be good. Cheers, Andrew On 15/7/05 18:26, Kishore Jalleda [EMAIL PROTECTED] wrote: Hi All, I have a mysql query which takes 8 seconds to run ona dual xeon 2.4, 3Gig ram box, SELECT gamename, MAX(score) AS score, COUNT(valid=1) AS played FROM gamesessions AS gamesessions WHERE valid=1 AND sessiontype IN (1,2) GROUP BY gamename; Explain select gives table type possible_keys key key_len ref rows Extra gamesessions ALL NULL NULL NULL NULL 915522 Using where; Using temporary; Using filesort How can I optimize the query Thanks Kishore -- 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: slow count(1) behavior with large tables
Andrew Braithwaite wrote: Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. MSGS.BoardID is indexed, and the EXPLAIN output I included in the original message shows that it is indeed being used: | m | ref | BoardIndex | BoardIndex | 4 | b.BoardID | 277 | Using index | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Switching legacy tables from 4.0 to 4.1 -AND- from default to UTF-8 .... ADVICE ANYONE?
On 7/15/05, Bruce Dembecki [EMAIL PROTECTED] wrote: This process has worked for us taking our latin1 4.0 databases and turning them into utf8 4.1 databases. UTF8 data we had already put in our 4.0 database despite it's latin1 encoding was correctly exported out of 4.0 and correctly converted on it's way in to 4.1 Wow! Really? This part amazes me. So the MySQL importing process seems to do the converting of the special characters into the newly-defined encoding format? (from latin1 to utf-8 in my case) See - we do webhosting for clients around the world, and right now our default-encoded MySQL 4.0 databases have Swedish and Hebrew characters in them. I'm concerned that if I dumped them as latin1/default in 4.0, but then imported as utf-8 in 4.1 that the non-ASCII characters would get imported as the wrong encoding. (Assuming, yes, that I would set our new 4.1 databases to do ALL utf-8 in the /etc/my.cnf and gladly take the small performance/size hit.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow count(1) behavior with large tables
Andrew Braithwaite wrote: Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. Cheers, Andrew He said, MSGS ... is indexed on BoardID. Did you look at the EXPLAIN output? The query is using the index on MSGS.BoardID. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow count(1) behavior with large tables
Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too? Cheers, A On 16/7/05 00:01, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, You're doing a join on 'BoardID' on the tables MSGS and MBOARD. Is the BoardID field indexed on the MSGS table too? If not then that may be your problem. Cheers, Andrew On 15/7/05 23:31, Jon Drukman [EMAIL PROTECTED] wrote: i'm trying to run this query: SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; MSGS has 9.5 million rows, and is indexed on BoardID MBOARD has 69K rows and is indexed on BaseType EXPLAIN shows: mysql explain SELECT COUNT(1) FROM MSGS m, MBOARD b WHERE b.BaseType = 0 AND m.BoardID = b.BoardID; +---+--+--++-+---+--- + -+ | table | type | possible_keys| key| key_len | ref | rows | Extra | +---+--+--++-+---+--- + -+ | b | ref | PRIMARY,BaseType | BaseType | 1 | const | 48614 | | | m | ref | BoardIndex | BoardIndex | 4 | b.BoardID | 277 | Using index | +---+--+--++-+---+--- + -+ the query takes several minutes to run. shouldn't this be a simple case of doing some math on index values? -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow count(1) behavior with large tables
Andrew Braithwaite wrote: Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too? yes, BoardID is the primary key. BaseType is also indexed. from the EXPLAIN output i can see that mysql is choosing to use BaseType as the index for MBOARD (as we know, mysql can only use one index per table.) i guess that means it has to do the join without an index. that might be why it's slow. i wonder if that can be worked around? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.9 build problem
Hello, This is my first post on this list, so forgive me, please, if this is the wrong forum for this question. While trying to build RPMs for 5.0.9-beta, I have run into this error: + make test cd mysql-test; \ ./mysql-test-run ./mysql-test-run --ps-protocol Installing Test Databases Removing Stale Files Installing Master Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/master-data --skip-innodb --skip-ndbcluster --skip-bdb --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/ Installing Slave Databases running ../sql/mysqld --no-defaults --bootstrap --skip-grant-tables --basedir=. --datadir=./var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb --language=../sql/share/english/ --character-sets-dir=../sql/share/charsets/ Manager disabled, skipping manager start. Loading Standard Test Databases Starting Tests TESTRESULT --- alias [ pass ] alter_table[ pass ] analyse[ pass ] analyze[ pass ] ansi [ pass ] archive[ skipped ] auto_increment [ pass ] backup [ pass ] [SNIP] mysql [ pass ] mysql_client_test [ fail ] Errors are (from /home/lamontp/rpmbuild/BUILD/mysql-5.0.9-beta/mysql-test/var/log/mysqltest-time) : mysql_client_test.c:3811: check failed: 'rc == 0' /home/lamontp/rpmbuild/BUILD/mysql-5.0.9-beta/client/.libs/mysqltest: At line 10: command $MYSQL_CLIENT_TEST failed (the last lines may be the most important ones) Aborting: mysql_client_test failed in default mode. To continue, re-run with '--force'. Examining the code for the test file (mysql_client_test.c) the test looks good. Also, the test database table being used in this particular test looks alright to me, too. There are other tests that were skipped. If that might be relevent, I could post the full output from the tests (~100). This kind of failure (this test in particular) *feels* odd to me. As I have not been into the code of MySQL 5 since the 5.0.0 days (even then, I only scratched the surface), I thought I should ask. TIA -- Lamont R. Peterson [EMAIL PROTECTED] Founder [http://blog.openbrainstem.net/peregrine/] OpenBrainstem - Intelligent Open Source Software Engineering P.S. The website is not up yet, but I'm working on it :). pgpJl5YCKRpOp.pgp Description: PGP signature
Re: slow count(1) behavior with large tables
Jon Drukman wrote: Andrew Braithwaite wrote: Sorry, I meant to say is the 'BoardID' field indexed on the MBOARD table too? yes, BoardID is the primary key. BaseType is also indexed. from the EXPLAIN output i can see that mysql is choosing to use BaseType as the index for MBOARD (as we know, mysql can only use one index per table.) i guess that means it has to do the join without an index. that might be why it's slow. i wonder if that can be worked around? No. Here is your query rewritten with an explicit JOIN: SELECT COUNT(1) FROM MSGS m JOIN MBOARD b ON m.BoardID = b.BoardID WHERE b.BaseType = 0; The only condition which restricts which rows to consider is the requirement b.BaseType = 0. Now look at the EXPLAIN output. MySQL is using the index on BaseType, as it should. Apparently, 48614 rows have BaseType=0. For each row found in MBOARD, MySQL is using the index on BoardID to find corresponding rows in MSGS. The EXPLAIN output suggests there are about 277 rows in MSGS for each row in MBOARD. That's a total of about 13,466,078 rows to count. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Chris Fonnesbeck wrote: Here is the startup script: standard script snipped The grant command was: grant all on *.* to [EMAIL PROTECTED] identified by 'my_password'; Thanks for the help, C. Chris, You only answered one of my questions. I'm hoping to narrow the problem by determining whether the problem is on the server or in the client. Unfortunately, you haven't given enough information to clear that up. I'll try again. 1) Right after you've issued the GRANT, while you can log in as chris, what does SHOW GRANTS FOR 'chris'@'localhost' give you? 2) When you say restart the server, what, exactly, do you mean? Are you restarting mysqld, or the whole machine? 3) After restarting, when you cannot log in as chris, what does SHOW GRANTS FOR 'chris'@'localhost' give you then? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Question
I have the following query which display every Cuisine in the database sorted by the WebsiteName. How can I modify this to get a COUNT of the number of records in each Cuisine in each WebsiteName? SELECT DISTINCT Restaurant.Cuisine, RestaurantWebsites.WebsiteName FROM Restaurant INNER JOIN RestaurantWebsites ON ( Restaurant.RestaurantID = RestaurantWebsites.RestaurantID ) WHERE RestaurantWebsites.WebsiteName = 'TOS' ORDER BY Cuisine ASC LIMIT 0 , 300 Thanks Jack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
