Re: help converting tables to excel format
if you want to use excel i suppose you have also windows with more than 3 GO free disk. install win32 mysql put the files in the data directory of one existing database connect and use that database export data to an outfile by : select * from yourtable into outfile csv fields terminated by ';' open the csv with excel. Mathias Selon Tom Beidler [EMAIL PROTECTED]: I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's the optimal db design choice for my 400 000 entries?
Hi all you mysql gurus, I have 400 000 unique strings where each and every one of these strings are associated with 1 - 50 (appr.) integer values. Now, pretty simple for you guys I guess, but how will I design my database to make a search interface against this data as rapid as possible? My first guess for a table whould be TABLE list string int ==== string1234 string16323 string1343 string2313 string29055 ... string434 5445 string434 12 ... But I come to a grinding halt when I realize this table will be 400 000 rows big, times the sum over the number of associated integers for each unique string... let's say each unique string always have 10 associated integers. Then we have a table of 400 000 x 10 = 4 000 000 rows. Is this really the best approach? A search would appropriately look like this Search: string434 and produce the following output Result: 5445 12. I guess the sql could look like this: SELECT int FROM list WHERE string = 'string434' What would be the optimal select query if I would like to return ints for strings that begin with say 'str'? And what if I wanted to search for strings where I only know the mid part or the end of the string? How then would a performance stable sql command look? If the above design suggestion is the optimal one, then I guess the internal database workings do not contain duplicates of the string values as they do in the table...? I'm kinda hoping a binary tree is built or something similar. Though I read somewhere that in order for the db to optimize and create btrees and the like, some data must be indexed... but how? I can't very well make the string column 'primary' since it isn't...? Very, very, very interested in hearing what you pros have to say! Thx a lot for this forum! /Tommy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
column permission for user
I tried the SHOW GRANTS statement SHOW GRANTS FOR 'dtk10mv'@ 'localhost' and got this: Grants for [EMAIL PROTECTED] GRANT SELECT, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '08862e71234184bc' GRANT UPDATE (column1) ON database1.table1 TO 'user1'@'localhost' GRANT UPDATE (column1) ON database1.table1 TO 'user1'@'localhost' There is no permission to update column2, but the user can do so. What can I do? I use MySQL 4.0.3-beta-nt. Perhaps this version is too old? (In http://dev.mysql.com/doc/mysql/en/show-grants.html there is an evidence.) To the answer from Jigal: When I delete Column_priv: update in tables_priv the user1 can´t update any column in the table. There is no exact explanation in documentation (http://dev.mysql.com/doc/mysql/en/grant.html). Regards, Wolfgang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table names in result set
Hello, I was wondering if there is any way to get SELECT result with `table_name.col_name` column names. It seems to me that I have had such result set before (Not with my current installation/configuration / :4.0.23_Debian-4). ( I know about column aliases, but is this a the best or the only way?) Perhaps anybody knows what does it depends on? I failed to find anything about it and would be gratefull for any tip. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storing in HEX format.
Hello,
I am writing a program for packet sniffing in Linux platform using C
language. I am using MYSQL as my database for storing packet information like
IP, port, packet payload, etc. I have created a table using the following query:
create table idsmatch(sip text,sport integer,dip text,dport integer,payload
longtext);
And, i have written a query for insertion to the table like this:
sprintf(query,INSERT INTO
idsmatch(sip,sport,dip,dport,payload)VALUES('%s',%d,'%s',%d,'%s'),
inet_ntoa(ip-saddr),dbsport,inet_ntoa(ip-daddr),dbdport,temp);
I am able to store all values properly except 'temp'.
'temp' is a char array which holds the packet payload. What I want is to store
the value of 'temp' in 'HEX' format in the table 'idsmatch'. Please tell me
what is to be done for that.
Thanks in advance.
Preeth.
OS error 1453
Dear Fellows, How to deal with the following error: regards mysqld-nt --console 050608 11:12:42 InnoDB: Operating system error number 1453 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 1453 means 'Unknown error'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.html InnoDB: about operating system error numbers. InnoDB: File name .\ibdata1 InnoDB: File operation call: 'aio read'. InnoDB: Cannot continue operation. ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WebHosting with MySQL.......
Ashok Kumar wrote: Sorry. I think i gave some unwanted info in my query. C-CGI is nothing but - CGI is scripting lang like JScript and its mainly for web related applications. we can use this scripting in any languages. i had chosen VC++ - Console appln. In this i used MySQL C-API to achieve the C-MySQL Connectivity and in my system(win 2000) i'm running the DB as a service. In my pgm i didn't mention any host name(I gave null to that). I want to totally setup this pgm to any one server and after, that pgm must be independent of my system. for that i want the clarification. (say the remote server is www.coolgoose.com). Your script being a client program implies that you have to tell it which host it should be looking for a mysql server on. In other words, if your server is not running on the same host as your program (and even then), you MUST enter a host name or ip address. Is it neccessary that the mysql server there and run as a service( In my system, if the mysql service is stopped means, i can't connect the DB as well as i can't open 'mysql command line client'. It is not necessary to run mysql as a service, however it is necessary to run it somehow, that being as a service or standalone application. The command line client is still a client, thus it depends on the mysql server being accessible. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - still confused
Wrong path, you are referring to an uri, not a path. Way off topic to starting explaining basic file system stuff here. You should be the same path you used when your uploaded the file. Something like: /home/chris/datafile.txt Frank At 10:06 PM 6/7/05, Chris wrote: Well, in fact I have read the documentation several times before posting this note. My problem arises because I don't know what is meant by full file path. If you mean: 'http://www.mydomain.com/datafile.txt' that produces the error: Can't get stat of 'http:/www.mydomain.com/datafile.txt' (Errcode: 2) Also, using a php pre-defined variable such as $_SERVER['DOCUMENT_ROOT'] creates the same error. Oh, yes I do know about file permissions in the entire path. If I only knew how to find the path, life would be much easier. Frank Bax [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] At 03:59 PM 6/7/05, Chris wrote: I have a simple php script which runs the following query: LOAD DATA INFILE 'datafile.txt' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which generates the error: File './mydabasename/datafile.txt' not found (Errcode: 2) Didn't bother to read the manual did you? http://dev.mysql.com/doc/mysql/en/load-data.html file named as myfile.txt is read from the database directory of the default database Specify the full path to your input file. Life will be much easier. -- 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: Storing in HEX format.
Hi,
snprintf(query_length,query,
INSERT INTO
idsmatch(sip,sport,dip,dport,HEX(payload))VALUES('%s',%d,'%s',%d,'%s'),
inet_ntoa(ip-saddr),dbsport,inet_ntoa(ip-daddr),dbdport,temp);
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's the optimal db design choice for my 400 000 entries?
Tommy Svensson \(InfoGrafix\) [EMAIL PROTECTED] wrote on 06/07/2005 04:49:09 PM: Hi all you mysql gurus, I have 400 000 unique strings where each and every one of these strings are associated with 1 - 50 (appr.) integer values. Now, pretty simple for you guys I guess, but how will I design my database to make a search interface against this data as rapid as possible? My first guess for a table whould be TABLE list string int ==== string1234 string16323 string1343 string2313 string29055 ... string434 5445 string434 12 ... But I come to a grinding halt when I realize this table will be 400 000 rows big, times the sum over the number of associated integers for each unique string... let's say each unique string always have 10 associated integers. Then we have a table of 400 000 x 10 = 4 000 000 rows. Is this really the best approach? A search would appropriately look like this Search: string434 and produce the following output Result: 5445 12. I guess the sql could look like this: SELECT int FROM list WHERE string = 'string434' What would be the optimal select query if I would like to return ints for strings that begin with say 'str'? And what if I wanted to search for strings where I only know the mid part or the end of the string? How then would a performance stable sql command look? If the above design suggestion is the optimal one, then I guess the internal database workings do not contain duplicates of the string values as they do in the table...? I'm kinda hoping a binary tree is built or something similar. Though I read somewhere that in order for the db to optimize and create btrees and the like, some data must be indexed... but how? I can't very well make the string column 'primary' since it isn't...? Very, very, very interested in hearing what you pros have to say! Thx a lot for this forum! /Tommy You are correct in your analysis that you would have four million strings in your table if you only created one table. The solution to your problem is the design technique called normalization which in its simplest explanation is the process of preventing data duplication. Create a table for your strings. Since they are all unique, this table will top out at 40 rows. CREATE TABLE StringMaster ( id int unsigned auto_increment primary key , stringvalue varchar(255) , UNIQUE (stringvalue) ); And a second table to associate the string values with their 1-50 integer values CREATE TABLE StringInteger ( StringMaster_ID int unsigned not null , IntegerValue int not null , PRIMARY KEY(StringMaster_ID, IntegerValue) , KEY (IntegerValue, StringMaster_ID) ) The primary key prevents the same integer value from being assigned to the same string more than once (in case you don't want that restriction just delete the word PRIMARY from the declaration). The second key is a covering key or covering index to do reverse lookups (tell me all of the strings associated with the integer 36). The other index will nearly double the storage requirements for this table. The payoff is that it will make your lookups VERY fast and it really won't take up that much room as integer values only use about 4 bytes per value. So let's estimate disk space: StringMaster: (255 chars + 4 bytes) * 40 rows = (appx) 10360 bytes (around 99 MB) max size StringIntegers: 8 bytes * 40 string values * 10 integers per string = 3200 bytes (about 30 MB) data plus an additional 30 MB or so for the extra index Even with another index on StringMaster you are looking at less that 250MB total. This is trivial for MySQL to handle (even on a P-II with almost no memory) and you should get great results. There are working MySQL databases with BILLIONS of entries containing hundreds of gigabytes of data and with the right equipment and design, they are performing very nicely. You asked about how to write a query that searches for only part of a string. For that I kindly refer you to the excellent manual. It can tell you so much more about the LIKE function and the RLIKE function and all of the other things MySQL can do with strings than I have time to go over. (It's pretty easy once you actually try it out and get a feel for it) http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html http://dev.mysql.com/doc/mysql/en/regexp.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Seriously.. When are we going to get subqueries?!
On Tuesday 07 June 2005 04:22 pm, Kevin Burton wrote: Subqueries in 4.1 are totally broken. They don't use indexes. They're evil. We're told we have subqueries but there's no way anyone on earth could use them. To make matters worse a lot of developers are TRICKED into using them and assume that mysql would do the right thing but its a HUGE performance hit. Well, have you filed a bug? I just looked and didnt see one.. It wouldnt be the first time, however, a bug search function didnt find something that was there.. So... 1. When will subqueries that actually use indexes be implemented? We've been promised this feature since 4.0 it was one of the biggest feature wins of 4.1. 2. If they won't be in 5.0 could you please abandon a feature for 5.0 and concentrate on subqueries? 3. If they won't be in 5.0 could you at least be honest and remove this feature since in the best case its useless and in the worse case its dangerous (god forbid someone should ship code that uses this)? Not trying to be obnoxious here but I really want this feature and the current implementation is very.. evil. Well, your not helping anything ranting in here, filing a bug is the best way to get this fixed... Jeff pgpWnixubL90D.pgp Description: PGP signature
Re: Storing in HEX format.
Uh-oh ;-)
I think you put the HEX in the wrong part. Shouldn't it be in the VALUES
clause?
snprintf(query_length,query,INSERT INTO
idsmatch(sip,sport,dip,dport,payload)
VALUES('%s',%d,'%s',%d,HEX('%s')),
inet_ntoa(ip-saddr),dbsport,inet_ntoa(ip-daddr),dbdport,temp);
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Philippe Poelvoorde [EMAIL PROTECTED] wrote on 06/08/2005
09:24:04 AM:
Hi,
snprintf(query_length,query,
INSERT INTO
idsmatch(sip,sport,dip,dport,HEX(payload))VALUES('%s',%d,'%s',%d,'%s'),
inet_ntoa(ip-saddr),dbsport,inet_ntoa(ip-daddr),dbdport,temp);
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: column permission for user
Hello. I can see such behavior in test database because mysql.db table has records which allows updates to any user, and database privileges ORs with global privileges. Send us the contents of your privileged tables. See: http://dev.mysql.com/doc/mysql/en/request-access.html Upgrade to the latest release 4.1.12 (4.0.24). Wolfgang Gliese [EMAIL PROTECTED] wrote: I tried the SHOW GRANTS statement SHOW GRANTS FOR 'dtk10mv'@ 'localhost' and got this: Grants for [EMAIL PROTECTED] GRANT SELECT, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '08862e71234184bc' GRANT UPDATE (column1) ON database1.table1 TO 'user1'@'localhost' GRANT UPDATE (column1) ON database1.table1 TO 'user1'@'localhost' There is no permission to update column2, but the user can do so. What can I do? I use MySQL 4.0.3-beta-nt. Perhaps this version is too old? (In http://dev.mysql.com/doc/mysql/en/show-grants.html there is an evidence.) To the answer from Jigal: When I delete Column_priv: update in tables_priv the user1 can´t update any column in the table. There is no exact explanation in documentation (http://dev.mysql.com/doc/mysql/en/grant.html). Regards, Wolfgang -- 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: 5.0.6: SP mysqldump
Hello. It seems not at the moment. See discussion at: http://bugs.mysql.com/bug.php?id=9056 Juri Shimon [EMAIL PROTECTED] wrote: Hello mysql, Is mysqldump from 5.0 dumping a stored procedures? If it isn't, then when (if planned)? -- 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: OS error 1453
Hello. According to: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/debug/base/system_error_codes.asp It is ERROR_WORKING_SET_QUOTA. 1453Insufficient quota to complete the requested service. Check your disk quota. A Z [EMAIL PROTECTED] wrote: Dear Fellows, How to deal with the following error: regards mysqld-nt --console 050608 11:12:42 InnoDB: Operating system error number 1453 in a file operation. InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: Error number 1453 means 'Unknown error'. InnoDB: See also section 13.2 at http://www.innodb.com/ibman.html InnoDB: about operating system error numbers. InnoDB: File name .\ibdata1 InnoDB: File operation call: 'aio read'. InnoDB: Cannot continue operation. ___ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com -- 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: help converting tables to excel format
No, I don't have Windows. I need some one to load my tables, export to excel and email me the excel docs. On Jun 7, 2005, at 11:54 PM, [EMAIL PROTECTED] wrote: if you want to use excel i suppose you have also windows with more than 3 GO free disk. install win32 mysql put the files in the data directory of one existing database connect and use that database export data to an outfile by : select * from yourtable into outfile csv fields terminated by ';' open the csv with excel. Mathias Selon Tom Beidler [EMAIL PROTECTED]: I recently received some old database files from my ISP. I'm trying to convert the documents to excel format. When I try to load the .frm, .MYI and .MYD files on my OS X MySQL databases I get the following error. #5 - Out of memory (Needed 3024898224 bytes) Unfortunately I can't work with the tables. I'm wondering if it's a platform issue. The information was from a pilot log I setup for some paraglider friends. I am trying to get the information in excel format so they can keep the info. Can someone help convert these tables to Excel format? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing in HEX format.
[EMAIL PROTECTED] wrote:
Uh-oh ;-)
I think you put the HEX in the wrong part. Shouldn't it be in the VALUES
clause?
snprintf(query_length,query,INSERT INTO
idsmatch(sip,sport,dip,dport,payload)
VALUES('%s',%d,'%s',%d,HEX('%s')),
inet_ntoa(ip-saddr),dbsport,inet_ntoa(ip-daddr),dbdport,temp);
Indeed :-)
Thanks for the correction
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing Performance Problem
Hi. I need to index about 300 million 20-byte records, but it takes forever (it isn't finished yet, after almost 24 hours, so I don't have actual numbers). I'm using RHEL, kernel 2.6.9, Mysql 4.1.11, MyISAM table, on a dual Xeon with 4GB RAM and IDE disks. I'm using the following values from /my-huge.cnf: 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 I also added bulk_insert_buffer_size = 256M. What am I doing wrong and how can I improve it? Also, are there any progress indicators, so I can know how much time is left until indexing finishes? --Thanks, Roi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing implementation
Hi, Another question, to help me better understand MySQL indexing: In MyISAM, does DISABLE INDEX followed by insertions and then ENABLE INDEX freeze the original index and batch-updates it, or does it drop it completely and recreate it from scratch? --thanks, Roi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN?
Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? TIA Angelo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
In the last episode (Jun 08), Jeff Smelser said: On Tuesday 07 June 2005 04:22 pm, Kevin Burton wrote: Subqueries in 4.1 are totally broken. They don't use indexes. They're evil. We're told we have subqueries but there's no way anyone on earth could use them. To make matters worse a lot of developers are TRICKED into using them and assume that mysql would do the right thing but its a HUGE performance hit. Well, have you filed a bug? I just looked and didnt see one.. It wouldnt be the first time, however, a bug search function didnt find something that was there.. There are actually quite a few bugs already filed on subquery performance: 4975, 7830, 8086, 8414, 9021, 9090, 10309, 10312, 10989, from a quick search on +subquery +index. Most have been set to To be fixed later. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
Angelo Zanetti [EMAIL PROTECTED] wrote on 06/08/2005 12:06:51 PM: Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? TIA Angelo Yes, the LEFT join is the correct choice for your query: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; It's that last term (and bl.userID is null) that detects the non-matched users across the LEFT JOIN. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Seriously.. When are we going to get subqueries?!
On Wednesday 08 June 2005 10:56 am, you wrote: In the last episode (Jun 08), Jeff Smelser said: On Tuesday 07 June 2005 04:22 pm, Kevin Burton wrote: Subqueries in 4.1 are totally broken. They don't use indexes. They're evil. We're told we have subqueries but there's no way anyone on earth could use them. To make matters worse a lot of developers are TRICKED into using them and assume that mysql would do the right thing but its a HUGE performance hit. Well, have you filed a bug? I just looked and didnt see one.. It wouldnt be the first time, however, a bug search function didnt find something that was there.. There are actually quite a few bugs already filed on subquery performance: 4975, 7830, 8086, 8414, 9021, 9090, 10309, 10312, 10989, from a quick search on +subquery +index. Most have been set to To be fixed later. Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. Jeff pgpAsEbArMgRn.pgp Description: PGP signature
RE: Seriously.. When are we going to get subqueries?!
[snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
Angelo Zanetti [EMAIL PROTECTED] wrote on 08/06/2005 17:06:51: Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? LEFT JOIN sounds right to me: SELECT a.* FROM a LEFT JOIN b ON a.userID = b.userID WHERE a.isactive = 1 AND b.buddyID IS NULL ; All A A's which are active and do not have a buddy. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist. so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. [EMAIL PROTECTED] wrote: Angelo Zanetti [EMAIL PROTECTED] wrote on 06/08/2005 12:06:51 PM: Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? TIA Angelo Yes, the LEFT join is the correct choice for your query: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; It's that last term (and bl.userID is null) that detects the non-matched users across the LEFT JOIN. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Indexing Performance Problem
I would increase your myisam_sort_buffer_size considerably just for this operation. You've got your key_buffer set high, but your sort buffer is comparatively low for creating a big index. One way you can tell how far along the index is, is to look at how quickly the index file is growing and how big it is. Of course, you don't know what the end size is going to be, but at least you'll be able to tell it's progress. On Jun 8, 2005, at 10:45 AM, roi h wrote: Hi. I need to index about 300 million 20-byte records, but it takes forever (it isn't finished yet, after almost 24 hours, so I don't have actual numbers). I'm using RHEL, kernel 2.6.9, Mysql 4.1.11, MyISAM table, on a dual Xeon with 4GB RAM and IDE disks. I'm using the following values from /my-huge.cnf: 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 I also added bulk_insert_buffer_size = 256M. What am I doing wrong and how can I improve it? Also, are there any progress indicators, so I can know how much time is left until indexing finishes? --Thanks, Roi. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Single quotation mark' makes different results for 'explain select'
By the way, I am using 4.0.18-log on i686.linux2.6.10. Greetings, I did the following two explain select ... According to whether I put the single quotation mark or not, I will get totally different results: 1. Without single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE *T1.STUDID = 099 AND T1.Prog_link = *; +---+++-+-+-+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+-+---+-+ | T1| ALL| idx_test | NULL|NULL | NULL| 91230 | Using where | +---+++-+-+-+---+-+ 2. With single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE *T1.STUDID = '099' AND T1.Prog_link = ''*; +---+++--+-+-+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++--+-+-+--+-+ | T1| ref| idx_test | idx_test | 7 | const | 27 | Using where | +---+++--+-+-+--+-+ Note: . create index idx_test on test(studid, prog_link); . in test table, studid is varchar(7), prog_link is varchar(4). Thanks a lot! Emi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Wednesday 08 June 2005 11:16 am, you wrote: [snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless First, *I* wasnt requesting anything.. So you need to reply to the person who was complaining in the first place. I just thought it was funny.. Them fixing has no care in the world in my mind since I never use subqueries.. So take your comments elsewhere..Or direct them to the right person.. Jeff pgpSieE9hrblQ.pgp Description: PGP signature
'Single quotation mark' makes different results for 'explain select'
Greetings, I did the following two explain select ... According to whether I put the single quotation mark or not, I will get totally different results: 1. Without single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE *T1.STUDID = 099 AND T1.Prog_link = *; +---+++-+-+-+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+-+---+-+ | T1| ALL| idx_test | NULL|NULL | NULL| 91230 | Using where | +---+++-+-+-+---+-+ 2. With single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE *T1.STUDID = '099**' AND T1.Prog_link = ''*; +---+++--+-+-+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++--+-+-+--+-+ | T1| ref| idx_test | idx_test | 7 | const | 27 | Using where | +---+++--+-+-+--+-+ Note: . create index idx_test on test(studid, prog_link); . in test table, studid is varchar(7), prog_link is varchar(4). Thanks a lot! Emi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 'Single quotation mark' makes different results for 'explain select'
In the last episode (Jun 08), Ying Lu said: By the way, I am using 4.0.18-log on i686.linux2.6.10. I did the following two explain select ... According to whether I put the single quotation mark or not, I will get totally different results: 1. Without single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE *T1.STUDID = 099 AND T1.Prog_link = *; +---++---+-+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+-+-+--+---+-+ | T1| ALL| idx_test | NULL|NULL | NULL | 91230 | Using where | +---++---+-+-+--+---+-+ This is a numeric comparison, so it has to convert STUDID to a number to compare. If you have 3 rows with the following STUDIDs, they will all match: 099, 99, 99. It can't do index lookups on all possible string values that convert to 99, so it does a full table scan. 2. With single quotation mark: explain SELECT * FROM test T1 force index (idx_test) WHERE T1.STUDID = '099' AND T1.Prog_link = ''; +---++---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+---+--+-+ | T1| ref| idx_test | idx_test | 7 | const | 27 | Using where | +---++---+--+-+---+--+-+ Here you're doing a string comparison, so only the exact value 099 can match. It can use the index to look up the matching rows directly. Note: . create index idx_test on test(studid, prog_link); . in test table, studid is varchar(7), prog_link is varchar(4). Solution: either convert your columns to INTEGER, or use string comparisons and ensure that the fields have a consistent format. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
[snip] On Wednesday 08 June 2005 11:16 am, you wrote: [snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless First, *I* wasnt requesting anything.. So you need to reply to the person who was complaining in the first place. I just thought it was funny.. Them fixing has no care in the world in my mind since I never use subqueries.. So take your comments elsewhere..Or direct them to the right person.. [/snip] Easy there boss, I was just responding to the thread and meant no offense. I saw the whole thing as funny. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
[snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless MySQL isn't free. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
On Wednesday 08 June 2005 11:49 am, you wrote: Easy there boss, I was just responding to the thread and meant no offense. I saw the whole thing as funny. Oh.. email sucks that way.. My apologies as well.. Jeff pgpvHgJLsGjaw.pgp Description: PGP signature
Re: LEFT JOIN?
Angelo Zanetti wrote: thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist. so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. I'm not sure Shawn's query does what you originally asked. His was SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; which returns active users with no buddies. As I understood it, you wanted active users who aren't on another user's buddy list. I believe that would require joining on buddyId rather than userID, like this: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID WHERE u.isactive =1 AND bl.userID is null; Adding your new requirement that we only look at a single user's buddy list should be simple: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Does that do what you want? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
Jay Blanchard wrote: [snip] On Wednesday 08 June 2005 11:16 am, you wrote: [snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless First, *I* wasnt requesting anything.. So you need to reply to the person who was complaining in the first place. I just thought it was funny.. Them fixing has no care in the world in my mind since I never use subqueries.. So take your comments elsewhere..Or direct them to the right person.. [/snip] Easy there boss, I was just responding to the thread and meant no offense. I saw the whole thing as funny. I personally find the idea that just because a product is free that people are not allowed to, or should not complain when they find shortcomings in that product, to be more than a bit short sighted. It is still VERY arguable as to wether Mysql is or is not the best open source DB on the market today. I am sure the Postgresql people would not agree, and they could likely make a very compelling argument. If Mysql wants to have the best product, then they NEED people to use their product, and to complain when something is not correct. And, they need to listen to those complaints from their users. For most any large open source project, there is more of a need for users and testers than there is for developers. Those people who use the free version of Mysql are arguably testers (those w/ support contracts are customers, and are arguably even more entitled to complain). Granted, Kevin's tone was a bit harsh, but his sentiments should be encouraged (frustration w/ a lack of feature). The concept that people should be happy with what they get for a free product only serves to keep the quality of free products below what they could be. The performance of sub-queries in mysql when used in an IN() clause is embarassingly slow. They are in most cases, unusable because they do not use indexes correctly. This is a legitimate complaint, and one that I personally hope Mysql looks into and repairs. I echo Kevin's thoughts and I rarely even have a use for sub-queries (given I got used to them not being available). Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
Let me rewrite what it's meant to do as i mgiht not have been clear. ok for a single user I want to get all the users (from the user table) that aren't a buddy for that user. users 1 bob 2 tom 3 mike buddylist 1 2 1 3 2 1 2 3 3 1 //therefore if i searched for mike it would return tom as he is not listed as a buddy for mike but bob is. The query is half working but doesnt seem to be working for an individual user. thanks to those who have helped so far... Michael Stassen wrote: Angelo Zanetti wrote: thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist. so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. I'm not sure Shawn's query does what you originally asked. His was SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; which returns active users with no buddies. As I understood it, you wanted active users who aren't on another user's buddy list. I believe that would require joining on buddyId rather than userID, like this: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID WHERE u.isactive =1 AND bl.userID is null; Adding your new requirement that we only look at a single user's buddy list should be simple: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Does that do what you want? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE - what is the path to file?
Chris wrote: Thank you for your detailed response. You're welcome. It seems my problem is trying to define the path to my data file and this is where I seem to be missing something. No, the problem is permissions. As I explained previously, a relative path (one without a leading /) means a location under mysql's data directory, not a location relative to your script. That's why you get a No such file or directory error when you tried a relative path. Hence, to load a file not under mysql's data directory, you must use an absolute path (one starting with /), as you did originally. Note that with the absolute path, you got a Permission denied error, not a No such file or directory error. Mysql could not read the path you gave it. Permissions on all directories in the path are by default set to 755 except for the director at the top of the directories in my hosting account public_html which is set to 750. As I said, *every* directory in the path must be readable by mysql. Your top directory has 750 permissions, so, unless it is owned by user mysql or in the mysql group, mysql cannot access it. Either set it to 751, or change it to group mysql. If I look at: LOAD DATA INFILE '/tmp/phpyxCoes' INTO TABLE LocationTEMPSR12 FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' which works from my tmp directory. The tmp directory is at the same level as public_html and has permissions 700. I'm sorry, but I just don't believe that. Permissions are user, group, world, in that order, so 700 means only the owner can access /tmp. If mysql can access /tmp, either /tmp is owned by mysql, which would be strange, or it doesn't have 700 permissions. Do this cd / ls -aFl and include the lines for . and tmp and public_html in your next post. So I guess I don't know why I can't specify the location of my data file from '/public_html/path_to_my_file/datafile.txt' You can, if you can fix the permissions. Thanks, Chris P.S. It might be a good idea `man chmod` to review file permissions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
Angelo Zanetti [EMAIL PROTECTED] wrote on 06/08/2005 01:38:42 PM: Let me rewrite what it's meant to do as i mgiht not have been clear. ok for a single user I want to get all the users (from the user table) that aren't a buddy for that user. users 1 bob 2 tom 3 mike buddylist 1 2 1 3 2 1 2 3 3 1 //therefore if i searched for mike it would return tom as he is not listed as a buddy for mike but bob is. The query is half working but doesnt seem to be working for an individual user. thanks to those who have helped so far... Michael Stassen wrote: Angelo Zanetti wrote: thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist. so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. I'm not sure Shawn's query does what you originally asked. His was SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; which returns active users with no buddies. As I understood it, you wanted active users who aren't on another user's buddy list. I believe that would require joining on buddyId rather than userID, like this: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID WHERE u.isactive =1 AND bl.userID is null; Adding your new requirement that we only look at a single user's buddy list should be simple: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Does that do what you want? Michael Michael's last answer: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Should do all of what you want except exclude the original user (so that the user cannot become their own buddy). To do that I would change it to read SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null and u.UserID != '$userid'; Can you show us some sample data and the result of either of these queries and explain what's wrong? I agree with Michael that this should work for what you need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Seriously.. When are we going to get subqueries?!
[snip] I personally find the idea that just because a product is free that people are not allowed to... [/snip] I was just trying to fling a little humor on to the situation, not start a flame war about who is right and what rights they do or do not have. I too believe that criticism, in the proper light, helps the open source software to continually improve. In this particular case the issue was not with the criticism, but the manner in which the critcism was offered. As evidenced by those who pointed out bug reports and features lists, this issue is known and the OP could/would/should have learned more before lighting the fire. And I agree, the PostGres folks might not agree...but this isn't their list, is it? :) Once again, I apologize if anyone's feelings were hurt by my comments. I was just trying to lighten things up a bit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
[EMAIL PROTECTED] wrote: Michael's last answer: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Should do all of what you want except exclude the original user (so that the user cannot become their own buddy). To do that I would change it to read SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null and u.UserID != '$userid'; Can you show us some sample data and the result of either of these queries and explain what's wrong? I agree with Michael that this should work for what you need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I was just writing to say the same thing. I would only add that since userId is numeric, we should all drop the quotes around $userid. SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = $userid WHERE u.isactive =1 AND bl.userID is null AND u.UserID != $userid; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Are partial searches possible?
Using MySQL 4.0.24 with PHP 4.3.11
I have the following code:
if($queryID = mysql_query(SELECT * FROM WhInventory WHERE Match(Booking)
AGAINST (' . mysql_escape_string($form['booking']) . ' IN BOOLEAN MODE)
ORDER BY Booking,$dbLink)) {
Which works fine if I have an exact entry but fails for a partial entry. For
example. If I have an entry where Booking is 'TSIN15' and I search on
the string 'TSIN15', it is found.
How can I get it to find a partial match if the string I enter is only
'15' ?
Thanks,
Don
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
Although it was nice to hear a few versions ago that they were thinking about adding this, I did not think it was absolutely necessary. There are many other ways to handle embedded queries. You can also offload some of the server side work by doing so. Depends on how you use it I suppose. As far as 'complaining' goes Unproductive. Extremely. Asking for a feature, sure. Submitting a bug report, sure. But 'complaining' about a feature is not only unproductive as an open source user, it's flat out wrong. The ole saying 'Givem an inch, they want a mile' comes into play. MySQL does have a development plan. Maybe not the same as everyone would like, but I'm sure that they will likely stick to that plan. Anyhow.. Much longer than I intended, but these guys do work. Let them do it. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 1:06 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Seriously.. When are we going to get subqueries?! Jay Blanchard wrote: [snip] On Wednesday 08 June 2005 11:16 am, you wrote: [snip] Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. [/snip] The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless First, *I* wasnt requesting anything.. So you need to reply to the person who was complaining in the first place. I just thought it was funny.. Them fixing has no care in the world in my mind since I never use subqueries.. So take your comments elsewhere..Or direct them to the right person.. [/snip] Easy there boss, I was just responding to the thread and meant no offense. I saw the whole thing as funny. I personally find the idea that just because a product is free that people are not allowed to, or should not complain when they find shortcomings in that product, to be more than a bit short sighted. It is still VERY arguable as to wether Mysql is or is not the best open source DB on the market today. I am sure the Postgresql people would not agree, and they could likely make a very compelling argument. If Mysql wants to have the best product, then they NEED people to use their product, and to complain when something is not correct. And, they need to listen to those complaints from their users. For most any large open source project, there is more of a need for users and testers than there is for developers. Those people who use the free version of Mysql are arguably testers (those w/ support contracts are customers, and are arguably even more entitled to complain). Granted, Kevin's tone was a bit harsh, but his sentiments should be encouraged (frustration w/ a lack of feature). The concept that people should be happy with what they get for a free product only serves to keep the quality of free products below what they could be. The performance of sub-queries in mysql when used in an IN() clause is embarassingly slow. They are in most cases, unusable because they do not use indexes correctly. This is a legitimate complaint, and one that I personally hope Mysql looks into and repairs. I echo Kevin's thoughts and I rarely even have a use for sub-queries (given I got used to them not being available). Greg -- 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]
Suggestions on db server configuration - Replication load balancing or Clustering??
I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't connect to Local Mysql database server
Hi All, I had posted a query few days back that I couldn't connect to mysql database from a network machine. So for that I added two entries in my firewall exceptions. I added the Port 3306 and mysqld, so firewall should allow any connection to database from external computer. My problem was solved after adding those entries. I was able to access DB server from other machines. But the problem now is I can access the Db server from a network and not from the local machine where it is installed. Whenever I try to connect I get error number 2013. I tried to connect with localhost and with IP too. I even removed the two entries from firewall exception and it still didn't work. I have installed Norton AntiVirus and Microsoft Spyware y'day. Does anyone of these block access to Mysql. I'll appreciate any help on this problem. Thanks, Reema Duggal Troiana Senior Software Developer BitArmor Systems, Inc. 357 North Craig Street Ground Floor Pittsburgh, PA 15213 [TEL] 412-682-2200 Ext 314 [FAX] 412-682-2201
RE: Are partial searches possible?
You can try
if($queryID = mysql_query(SELECT *
FROM WhInventory
WHERE Booking like ('%15%')
ORDER BY Booking,$dbLink))
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 1:44 PM
To: mysql@lists.mysql.com
Subject: Are partial searches possible?
Using MySQL 4.0.24 with PHP 4.3.11
I have the following code:
if($queryID = mysql_query(SELECT * FROM WhInventory WHERE
Match(Booking)
AGAINST (' . mysql_escape_string($form['booking']) . ' IN BOOLEAN
MODE)
ORDER BY Booking,$dbLink)) {
Which works fine if I have an exact entry but fails for a partial entry.
For
example. If I have an entry where Booking is 'TSIN15' and I search
on
the string 'TSIN15', it is found.
How can I get it to find a partial match if the string I enter is only
'15' ?
Thanks,
Don
--
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: Are partial searches possible?
Tried that but no luck.
What I want is a search that will look for '15' (for example) in the
field BOOKING.
So if I enter '1' in my search field, it will find all instances of BOOKING
with a '1' in it.
Conversely, if I enter '12345' in my search field, it will find all
instances of BOOKING with a '12345' in it.
Hence, partial matches
Thanks,
Don
-Original Message-
From: Gordon Bruce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 3:31 PM
To: Don; mysql@lists.mysql.com
Subject: RE: Are partial searches possible?
You can try
if($queryID = mysql_query(SELECT *
FROM WhInventory
WHERE Booking like ('%15%')
ORDER BY Booking,$dbLink))
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 1:44 PM
To: mysql@lists.mysql.com
Subject: Are partial searches possible?
Using MySQL 4.0.24 with PHP 4.3.11
I have the following code:
if($queryID = mysql_query(SELECT * FROM WhInventory WHERE
Match(Booking)
AGAINST (' . mysql_escape_string($form['booking']) . ' IN BOOLEAN
MODE)
ORDER BY Booking,$dbLink)) {
Which works fine if I have an exact entry but fails for a partial entry. For
example. If I have an entry where Booking is 'TSIN15' and I search on
the string 'TSIN15', it is found.
How can I get it to find a partial match if the string I enter is only
'15' ?
Thanks,
Don
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Are partial searches possible?
Maybe a good question to ask is where can I find documentation on:
MySQL WHERE where_definition
Specifically, all the options available for the 'where_definition'
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 3:40 PM
To: mysql@lists.mysql.com
Subject: RE: Are partial searches possible?
Tried that but no luck.
What I want is a search that will look for '15' (for example) in the
field BOOKING.
So if I enter '1' in my search field, it will find all instances of BOOKING
with a '1' in it.
Conversely, if I enter '12345' in my search field, it will find all
instances of BOOKING with a '12345' in it.
Hence, partial matches
Thanks,
Don
-Original Message-
From: Gordon Bruce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 3:31 PM
To: Don; mysql@lists.mysql.com
Subject: RE: Are partial searches possible?
You can try
if($queryID = mysql_query(SELECT *
FROM WhInventory
WHERE Booking like ('%15%')
ORDER BY Booking,$dbLink))
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 1:44 PM
To: mysql@lists.mysql.com
Subject: Are partial searches possible?
Using MySQL 4.0.24 with PHP 4.3.11
I have the following code:
if($queryID = mysql_query(SELECT * FROM WhInventory WHERE
Match(Booking)
AGAINST (' . mysql_escape_string($form['booking']) . ' IN BOOLEAN
MODE)
ORDER BY Booking,$dbLink)) {
Which works fine if I have an exact entry but fails for a partial entry. For
example. If I have an entry where Booking is 'TSIN15' and I search on
the string 'TSIN15', it is found.
How can I get it to find a partial match if the string I enter is only
'15' ?
Thanks,
Don
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Are partial searches possible?
[snip] Tried that but no luck. What I want is a search that will look for '15' (for example) in the field BOOKING. So if I enter '1' in my search field, it will find all instances of BOOKING with a '1' in it. Conversely, if I enter '12345' in my search field, it will find all instances of BOOKING with a '12345' in it. [/snip] SELECT * FROM WhInventory WHERE Booking LIKE '%15%' ORDER BY Booking Should work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Are partial searches possible?
[snip] Maybe a good question to ask is where can I find documentation on: MySQL WHERE where_definition Specifically, all the options available for the 'where_definition' [/snip] http://www.mysql.com/select should do it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Are partial searches possible?
Here are the string functions. LIKE and RLIKE are useful for partial
string searches:
http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html
RLIKE uses a variation of regular expressions. Here is the syntax MySQL
uses:
http://dev.mysql.com/doc/mysql/en/regexp.html
This section describes FULLTEXT searches (what you can and cannot do):
http://dev.mysql.com/doc/mysql/en/fulltext-search.html
Finally, here is that part of the book that covers WHERE clauses:
http://dev.mysql.com/doc/mysql/en/select.html
Basically it says that you can use any valid function (other than the
aggregate functions) or expression in the WHERE clause of a query.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Don [EMAIL PROTECTED] wrote on 06/08/2005 03:45:55 PM:
Maybe a good question to ask is where can I find documentation on:
MySQL WHERE where_definition
Specifically, all the options available for the 'where_definition'
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 3:40 PM
To: mysql@lists.mysql.com
Subject: RE: Are partial searches possible?
Tried that but no luck.
What I want is a search that will look for '15' (for example) in the
field BOOKING.
So if I enter '1' in my search field, it will find all instances of
BOOKING
with a '1' in it.
Conversely, if I enter '12345' in my search field, it will find all
instances of BOOKING with a '12345' in it.
Hence, partial matches
Thanks,
Don
-Original Message-
From: Gordon Bruce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 3:31 PM
To: Don; mysql@lists.mysql.com
Subject: RE: Are partial searches possible?
You can try
if($queryID = mysql_query(SELECT *
FROM WhInventory
WHERE Booking like ('%15%')
ORDER BY Booking,$dbLink))
-Original Message-
From: Don [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 08, 2005 1:44 PM
To: mysql@lists.mysql.com
Subject: Are partial searches possible?
Using MySQL 4.0.24 with PHP 4.3.11
I have the following code:
if($queryID = mysql_query(SELECT * FROM WhInventory WHERE
Match(Booking)
AGAINST (' . mysql_escape_string($form['booking']) . ' IN BOOLEAN
MODE)
ORDER BY Booking,$dbLink)) {
Which works fine if I have an exact entry but fails for a partial entry.
For
example. If I have an entry where Booking is 'TSIN15' and I search
on
the string 'TSIN15', it is found.
How can I get it to find a partial match if the string I enter is only
'15' ?
Thanks,
Don
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innoDB and referential action
Dear All, I have two tables entity1 and entity2; the second one should contain only data with the same PLZ as listed in table entity1. Unfortunately, I am able to insert in table entity2 data in the field PLZ, different from those in the field PLZ in the table entity1. What is going wrong? Many thanks, Jan Create table Entity1 ( PLZ Char(20) NOT NULL, Ort Char(20), UNIQUE (PLZ), Primary Key (PLZ) ) ENGINE = InnoDB ROW_FORMAT = Default; Create table Entity2 ( PLZ Char(20) NOT NULL, Stra_e Char(20), Primary Key (PLZ), Constraint Relationship1 Foreign Key (PLZ) references Entity1 (PLZ) on delete restrict on update restrict ) ENGINE = InnoDB ROW_FORMAT = Default; Virus checked by G DATA AntiVirusKit Version: AVK 15.0.4951 from 19.05.2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
A long time ago when I was doing support for Lotus Notes, I was told that the customer who 'complains' about legitimate bugs may be the most valuable type of customer of all. This is because they care enough to vent. Who knows how many unhappy customers one has if none ever complain? What if all those who submitted bugs against subquery performance didn't care enough about the product to submit them? You may have a dwindling customer base and you might never know why. And today's 'free' MySQL user could very well end up being tomorrow's paid support customer if they feel the product is good and their voice will be heard. Positive, professional venting always seems to help though :) -Just my $.02 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to Local Mysql database server
Hello. Error 2013 means: Lost connection to MySQL server during query. See: http://dev.mysql.com/doc/mysql/en/gone-away.html rtroiana wrote: Hi All, I had posted a query few days back that I couldn't connect to mysql database from a network machine. So for that I added two entries in my firewall exceptions. I added the Port 3306 and mysqld, so firewall should allow any connection to database from external computer. My problem was solved after adding those entries. I was able to access DB server from other machines. But the problem now is I can access the Db server from a network and not from the local machine where it is installed. Whenever I try to connect I get error number 2013. I tried to connect with localhost and with IP too. I even removed the two entries from firewall exception and it still didn't work. I have installed Norton AntiVirus and Microsoft Spyware y'day. Does anyone of these block access to Mysql. I'll appreciate any help on this problem. -- 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]
ORDER BY and ENUM -- not alphabetical
Please tell me there is a way to fix this bug in mysql Ver 12.22 Distrib
4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Access')
If I SELECT, and ORDER BY Type, it is ordering in the order defined by the
the ENUM, not _alphabetically_ as a sane person would expect. UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY and ENUM -- not alphabetical
It's not a bug at all. You just hit one of the features of enum :)
If you want to order alphabetically as you describe cast the enum name
to a string like this
select col from t order by concat(my_enum);
-Eric
Daevid Vincent wrote:
Please tell me there is a way to fix this bug in mysql Ver 12.22 Distrib
4.0.18, for pc-linux-gnu (i686)
I have a column defined like so:
Type
enum('Schedule','Report','Admin','Search','General','License','Access')
If I SELECT, and ORDER BY Type, it is ordering in the order defined by the
the ENUM, not _alphabetically_ as a sane person would expect. UGH!
Please tell me there is a fix or work around.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
microsoft sequel server
Is anyone familiar with how to dump a database from Microsoft sequel server to mysql? I know nothing about Microsoft products and am looking for a utility or similar to do the conversion. Maybe Microsoft has something built in? Although I doubt it. TIA Kirk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: microsoft sequel server
Kirk wrote: Is anyone familiar with how to dump a database from Microsoft sequel server to mysql? I know nothing about Microsoft products and am looking for a utility or similar to do the conversion. Maybe Microsoft has something built in? Although I doubt it. There are tools around - some are linked from the MySQL web site. I avoid these. It's a much better idea to do the migration yourself. Any mistakes with using the wrong field types can lead to data loss, and I've noticed a lot of migration tools using 'interesting' choices of field types. Get your documentation for both servers out, study your table definitions, and then set up your destination tables in MySQL. You can then use a front-end like MS Access to link tables from the source destination databases, and use Access' append queries to move the data. Alternatively, you can use SQL Server's data transformations to connect to remote ODBC data sources and insert the data that way. Which ever way you do it, watch for errors and do some testing of the data to compare source and destination tables, eg do count queries on each table, do sum queries on numeric fields, and of course test your application linked to the new MySQL database *before* doing the actual migration. When everything appears to looks OK, truncate all the MySQL tables, backup the SQL Server database, and do the actual migration. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: microsoft sequel server
Import via odbc ? I used an app called sqlyog to import via odbc. On 09/06/2005, at 9:45 AM, Kirk wrote: Is anyone familiar with how to dump a database from Microsoft sequel server to mysql? I know nothing about Microsoft products and am looking for a utility or similar to do the conversion. Maybe Microsoft has something built in? Although I doubt it. TIA Kirk -- 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: Seriously.. When are we going to get subqueries?!
Jeff Smelser wrote: Thats funny.. looks like it will be added to 5.1.. Dunno why they think fixing it is adding a feature.. WOW! That's just insane! This seriously has to be fixed in 5.0 or sooner... The thing is that MySQL has both promised this feature and is claiming that 5.0 is now a REAL database and not having this is much higher than triggers, foreign keys, and all the other bells and whistles in 5.0 that I'll never use.. :-) Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
Greg Whalin wrote: Granted, Kevin's tone was a bit harsh, but his sentiments should be encouraged (frustration w/ a lack of feature). The concept that people should be happy with what they get for a free product only serves to keep the quality of free products below what they could be. It was 1/2 frustration and 1/2 humor.. Then again I have a strange sense of humor. I just thing that the MySQL developers are moving really fast and that their priorities are really wrong on this one. Again I think that if they're going to wait so long to fix this issue that they whould remove the feature. Its just going to shoot people in the foot and claiming that you support subqueries isn't actually correct since they're not usable. The performance of sub-queries in mysql when used in an IN() clause is embarassingly slow. They are in most cases, unusable because they do not use indexes correctly. This is a legitimate complaint, and one that I personally hope Mysql looks into and repairs. I echo Kevin's thoughts and I rarely even have a use for sub-queries (given I got used to them not being available). Thanks... Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
I think MySQL has a little ways to go yet before I would subjectively call it best. I posted twice to the list with questions about porting my application that runs on (SQL Server, Oracle, PostgreSQL, Sybase SQL Anywhere, MS Access, and DB2) to MySQL. No one on the mysql list, or the internals list responded to my pretty basic issues: 1) Why can't I declare a datetime field with DEFAULT NOW() 2) Since the SQL standard states that identifiers are not case sensitive, how can I use the DB without case sensitivity, when I don't have authority to change the system wide lowercase setting? I wouldn't have authority to change the setting in a hosted environment. I have to say, MySQL still looks like a tinker-toy to me. George Sexton MH Software, Inc. http://www.mhsoftware.com/ Voice: 303 438 9585 -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 10:16 AM To: Jeff Smelser; mysql@lists.mysql.com Subject: RE: Seriously.. When are we going to get subqueries?! The best open-source database on the market today? Free Constant improvements to database? Free Ability to complain when we don't get what we want? Priceless -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: WebHosting with MySQL.......
Ashok Kumar wrote: Hi Peter, If i give the localhost/IP addr, How can i set the security previleges in my server/system( i have installed apache server to run the scripts). as i told earlier i'm using win2000 os, its expecting lot of securitie prevleges. i can't find those settings. if u know abt apache server means pls tell something abt that. It is not the apache server that accesses the mysql server, but the scripting engine of your choice. I tried to access my system's mysql db and as well as the scripts in the apache server from other system, it's asking for the username and password. I didn't give any username and pwd to my system(i disabled sys pwd) and also to db. Why it's asking for the username and pwd. i tried all the username and pwd combinations. but no result. If u have any idea pls share with me. When connecting to the mysql server in your script, you must use three parameters: 1. Host name / ip address 2. Username 3. Password I am not familiar with the scripting engine of your choice, but the command to set up a connection to a mysql server would be something like: // Establish connection myConnection = mysql_connect (myHostName, myUserName, myPassword); // Select database on server using connection myDatabase = mysql_select_db( myDatabaseName, myConnection); Check the documentation for the scripting engine for the right syntax and commands. The username is the name of a mysql user etc. Now, consider you have created a database called 'myDatabaseName' you have to set up the user (myUserName) to allow access to this database: 1. Enter the mysql command line client mysql GRANT ALL PRIVILEGES ON myDatabaseName.* TO 'myUserName'@'HostNameOrIPAddressOfApacheServer' IDENTIFIED BY 'myPassword'; 2. Flush the mysql server user privilege cache mysql FLUSH PRIVILEGES; Now go read the manual. Peter Normann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL newsgroup proposal
ANNOUNCEMENT: A RFD (REQUEST FOR DISCUSSION) has been posted for the creation of a new Usenet newsgroup: comp.databases.mysql The proposal and related discussion can be read in the Usenet group news.groups ... feel free to weigh in and make any suggestions you may have. Message-ID: [EMAIL PROTECTED] Link: http://makeashorterlink.com/?Z1061363B -- Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show innodb status doesn't work
I use Mysql 4.0.22. When I press show innodb status,I got the error message: ERROR 1105: Unknow error what's the problem? Is the innodb enable in 4.0.22 by default? How can I check if the innodb enable or not? _ MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
