No replicated data even though master log matches on slave
I am having very strange problems with the slave not replicating master data but the slaves log matches the masters at all times. I am running mysql 4.0.9 on a Win XP Pro (Master) and Win XP Home (Slave). Here is my procedure for setting up the replication: Initially both the master and slave server are shut down. 1. Delete all err, log and master files on both master and slave. 2. Start the master server. 3. create the database and tables on the master server. 4. Now I load some data into the master tables. I load 3 rows into a table. 5. Show master status and record the offset. 6. Flush tables with read lock on the master. 7. Zip up the database folder. 8. Unlock tables on master. (No other writes are being performed because I am the single user) 9. Unzip database on the slave. 10. Start the slave server (not the thread). 11.Execute the Change Master to on the slave with the log offset from the master. 12.Start the slave thread with slave start. 13.Show slave status do a select on the table .everything looks fine .the 3 rows are there. 14.Now, here is the weird part: I insert some more rows into the master table. This should now be replicated...right? I do a show master status and a show slave status .the master log position on both slave and master are identical and have moved forward to the same position .HOWEVER, no new data has been inserted into the slave table! I look in the slave-relay-bin.xxx log and the inserts are there...but no data in the slave table other than what was unzipped along with the initial database structure?! Both error logs report no errors. What is going on? Anyone? Am I doing something out of order? Also, I understand I can use LOAD DATA FROM MASTER since I am using version 4+. Where in this procedure should that call be made on the slave so that everything works fine. The MySQL docs are very vague as to where exactly in all this that command should be issued. Thanks for any help. _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Socket Error
Good Morning Anthony , your question was: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (38) I've been reading and searching the documentation for hours and I know this is simple to fix but I can't figure it out. I've been at it for our hours. The mysql directory above does not exist but if I create it and make a file called mysql.sock that does not work either. Could someone point me in the right direction? Please Looks like your mysql-daemon doesn't run at all or/and isn't installed in the directory your client expected it. In order to help you there's some information needed: Which operating system do you use ? How did you install (e.g. rpm) ? Which version of mysql did you try to install ? How did you start the server ? If you don't know the answers to some of that questions , describe what you did. prosit Klaus - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is the difference?
Octavian, seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT. I tried this with MyISAM and InnoDB, and it doesn't seem to be table handler dependent. Below is the output of my test. --- mysql insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.11 sec) Datensõtze: 2 Duplikate: 0 Warnungen: 0 mysql select * from a; ++--+ | id | name | ++--+ | 1 | one | | 2 | two | ++--+ 2 rows in set (0.03 sec) mysql select last_insert_id(); +--+ | last_insert_id() | +--+ |1 | +--+ 1 row in set (0.02 sec) mysql insert into a values(null, 'three'); Query OK, 1 row affected (0.05 sec) mysql select last_insert_id(); +--+ | last_insert_id() | +--+ |3 | +--+ 1 row in set (0.00 sec) -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Octavian Rasnita [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Tuesday, January 21, 2003 5:52 PM Subject: Which is the difference? Hello all, I've tried the following sql queries: mysql create table a(id int unsigned not null auto_increment primary key, name text); Query OK, 0 rows affected (0.01 sec) mysql insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select last_insert_id() from a; +--+ | last_insert_id() | +--+ |1 | |1 | +--+ 2 rows in set (0.01 sec) #I've tried a second time: mysql select last_insert_id() from a; +--+ | last_insert_id() | +--+ |1 | |1 | +--+ 2 rows in set (0.00 sec) #I've tried to put a limit clause to see the last inserted ID only once: mysql select last_insert_id() from a limit 1; +--+ | last_insert_id() | +--+ |1 | +--+ 1 row in set (0.00 sec) #I've tried a second time and the value is still 1 mysql select last_insert_id() from a limit 1; +--+ | last_insert_id() | +--+ |1 | +--+ 1 row in set (0.00 sec) #Now I've tried to find the last inserted ID by using where id is null but ... mysql select id from a where id is null; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) #The first trial was successfully, but the second not: mysql select id from a where id is null; Empty set (0.01 sec) #And from this point on, I get only empty responses. Please tell me why. And BTW, if I insert more records in a single query, how can I find the real last one? Is the only solution counting the number of new entered records, and adding this number to the number returned by the last_insert_id() function? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 4.1 features
Daniel, you can find a list of the new features here: http://www.mysql.com/doc/en/News-4.1.x.html Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Daniel Kiss [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:55 AM Subject: Re: 4.1 features On Wed, Jan 22, 2003 at 06:44:11AM +0100, Daniel Kiss wrote: Hi all, Can anyone tell where can I find some documentation about the new features released in MySQL 4.1? The features aren't all there, but here's what I've seen (and used) so far: * mixing of character sets in a table * sub-queries * spatial indexes I'm sure you'll hear more about it as the development evolves. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 3 days, processed 89,206,568 queries (311/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table joins are slow things to deal with. . .
Steve, ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); First thing that comes into mind is: You said you indexed the join fields (i.e. CaseNumber), but what about Defendants.Defendant? Okay, you did. Hmm. Next thing would be to check if the appropriate keys were used (CaseNumber, Defendant). Yes, they were used. Next thing would be to check if the optimizer chose a good join type: | Defendants | range | CaseNumber,Defendant | Defendant | 30 | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | The join types are range and eq_ref, that's fine. The number of examined rows in Defendants seems okay, too: rows | Extra | 82756 | where used | Okay, I leave this one to the gurus :-/ Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Steve Quezadas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 12:32 AM Subject: Table joins are slow things to deal with. . . Maybe I'm dumb for saying this, but sql joins seems expensive to do in terms of performance (yes, I indexed the joined fields). If I do a query search of a 2,600,000 record defendant table WITHOUT a join (SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%;). Performance is generally zippy at .53 seconds (which ends up pulling about 34,000 rows). HOWEVER, once I join this table with the much smaller [Cases] table (about 140,000 rows), performance plummets to 8.79 seconds! Quite a drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%);'. I get about 10,500 rows returned here, but man, what an increase in time. So my main question is: Is this degradation in performance to be expected with a join? What I am thinking about doing is perhaps doing two smaller queries instead of one larger one. My first query can just query the very large Defendants table without a join and create a temporary table of CaseNumbers (takes about .53 seconds). Then I can join that temporary table into the much smaller [Cases] table and then run that part of the query (which takes about 1.23 seconds). So bottom line: Results of big query with one join: 8.79 seconds Results of creating two smaller queries: 1.76 seconds (.53 seconds + 1.23 seconds). So I am thinking about optimizing the query in my PHP program to make two smaller queries instead of one bigger query. Is this a standard thing to do to increase speed? Or am I doing something stupid and inefficient in the join that I am not aware of? - Steve PS Here is some information about my tables and indexes: ([Defendant] Query WITHOUT a join) - .53 seconds. EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%; ++---+---+---+-+--+- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+---+-+--+- --+-+ | Defendants | range | Defendant | Defendant | 30 | NULL | 82756 | where used; Using temporary | ++---+---+---+-+--+- --+-+ ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+-- -+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+-- -+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL | 82756 | where used | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | Defendants.CaseNumber | 1 | where used | +++--+-+-+-- -+---++ Table and index information Cases Table - 140,000 records +---+---+--+-+-+---+ | Field
Re: Indian Languages Support
Dear Velmani, what do you mean with Indian? AFAIK there are 36 languages spoken in India. Do you mean English? ;-) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: N-Velmani [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:38 AM Subject: Indian Languages Support Hi All, Could any one tell me how to get the Indian Languages Support in MySQL??? Regards, N.Velmani - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Indian Languages Support
Dear Stefan, I'm trying to build a multilingual website for Indian languages. For this, I wanted to store my strings in Hindi and Tamil, in mysql. Now, could u tell me how can i achieve this? Thanks in advance, Regards, N.Velmani On Wed, 22 Jan 2003, Stefan Hinz, iConnect (Berlin) wrote: Dear Velmani, what do you mean with Indian? AFAIK there are 36 languages spoken in India. Do you mean English? ;-) Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: N-Velmani [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:38 AM Subject: Indian Languages Support Hi All, Could any one tell me how to get the Indian Languages Support in MySQL??? Regards, N.Velmani - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Joins are slow
On Wed, 2003-01-22 at 03:18, Steve Quezadas wrote: ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+---+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+---+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL | 82756 | where used | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | Defendants.CaseNumber | 1 | where used | +++--+-+-+---+---++ Would a combined index on CasesNumber and Filed help ? eg. create index CasesFiled on Cases (CasesNumber, Filed); The query might work out the 'Filed = 1999-01-01' without consulting the table then. == Martin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Access denied database privilege question
Hi list, I've a question about the database privilege and the Mysql.db table. If i do something like this : GRANT SELECT ON MYDB.* TO [EMAIL PROTECTED]; ( i'm using MyISAM tables ... ) And if MYDB contains something like 100 tables, could this make me problems ? In fact, i have errors like this : Failed to connect to database: 1044 Error: Access denied for user: '[EMAIL PROTECTED]' to database 'MYDB' 2006 : MySQL server has gone away All is working well, except sometimes i have such errors each day during a small period of time and all is going back well later ... I see that restarting the serveur could help me but i don't know why ... All connections are through TCP / IP on a private 100Mb LAN. Extract of my.cnf : [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve skip-host-cache [...] Here : http://www.mysql.com/doc/en/Access_denied.html I've find something : Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using It's true, i'm using mysql-3.23.54a-pc-linux-i686.tar.gz and the system is Red Hat 7.2 with red hat buggy glibc. This could be my problem ? Please, i need to solve my problem ... Perhaps i'm not alone having this problem ... Did someone hear ( and understand ) me ? Thanks David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question - Query
First of all, thanks for your help. I have discovered that the result from: SELECT bank, SUM(unit_price_us * order_cbm) FROM lcopen GROUP BY bank; is different to the query you provided (row OPEN). How can I solve it? Terence Ng --- [EMAIL PROTECTED] wrote: Hello Terence, I posted this to: Newsgroups: A HREF=http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8group=mailing.database.mysql;mailing.database.mysql/A Date: 2003-01-08 01:16:04 PST Subject: Question - Query Perhaps you didn't see it:). Use a CASE statement to conditionally SUM the amount_us values, ie. SUM only when lcreceive.due_date current_date is true. If false a null will be summed which SUM excludes. SELECT lcopen.bank, SUM(lcopen.unit_price_us*lcopen.order_cbm) AS open, SUM(CASE WHEN lcreceive.due_date current_date THEN lcreceive.amount_us END) AS newamount FROM lcopen, lcreceive WHERE lcopen.id=lcreceive.id GROUP BY lcopen.bank; You could also use IF instead of CASE. But there could be a problem with both SUM's.MySql returns a 0 when all values of expr in SUM(expr) are null instead of a null value.This behavior is different from Min,Max and Avg which all return null and could lead to misleading results/interpretations. A workaround for returning a null instead of 0 for SUM in the case of all null values is to first check for all nulls using an aggregate that will return a null in this case.First check for a null using an appropriate aggregate then compute the sum or return null.Here MAX is used to check for null in both SUM expressions. SELECT lcopen.bank, CASE WHEN MAX(lcopen.unit_price_us*lcopen.order_cbm) IS NOT NULL THEN SUM(lcopen.unit_price_us*lcopen.order_cbm) ELSE NULL END * 7.8 AS open, CASE WHEN MAX(CASE WHEN lcreceive.due_date current_date THEN lcreceive.amount_us END) IS NOT NULL THEN SUM(CASE WHEN lcreceive.due_date current_date THEN lcreceive.amount_us END) ELSE NULL END AS newamount, FROM lcopen, lcreceive WHERE lcopen.id=lcreceive.id GROUP BY lcopen.bank; You could also use count(expr) for testing nulls and test for 0 instead of IS NOT NULL. You could also use IF instead of CASE.There are many different ways of writing the logic. MySql hopefully will fix the SUM function in the case of all nulls. All enterprise rdbms return null in this case:). www.rac4sql.net ___ Do You Yahoo!? Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Need Help
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [1]+ Exit 1 ./bin/mysqld_safe --user=mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Designing Categories
Greets Folks, I cant tell if this is a dumb question or not, so please bear with me if it is... But here goes... You know how when you visit a website and they have Categories, like in an online store or maybe an auction sites... or maybe categories would be used in a bulletin board system for different types of discussions, or art galleries for different types of galleries... ? I am wondering how the backend logic structures looks to that tablewise... I can handle SQL for relational databasing, but I am trying to understand this more from a design point of view... Thanks, Will _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql Digest 22 Jan 2003 08:37:14 -0000 Issue 2280
I guess you just need to take the rows from the people table, not from the epeople table, and put the condition about the event id in the join, not the main where. Like so: select people.id, concat(lastname, , , firstname) as name from people left join epeople on epeople.pid=people.id and epeople.eid=2 where people.id is null; Hope it works. --- Asunto: join from this subselect Fecha: Tue, 21 Jan 2003 11:51:13 -0800 De: Josh L Bernardini [EMAIL PROTECTED] A: [EMAIL PROTECTED] I can't come up with the join syntax to mimic this subselect query to list people not attending an event (*epeople.eid == event.id): select people.id as pid, concat(lastname, , , firstname) as name from people where people.id not in (select epeople.pid from epeople left join people on epeople.pid=people.id where epeople.eid=2); Thought it would be: select epeople.pid, concat(lastname, , , firstname) as name from epeople left join people on epeople.pid=people.id where epeople.eid=2 and people.id is null; but I get an empty set. The following lists people attending an event: select epeople.pid, concat(lastname, , , firstname) as name from epeople left join people on epeople.pid=people.id where epeople.eid=2; tia, jb PS is 4.1 available anywhere so I could just use the subselect. Under 4.1, are subselects or joins more efficient? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Indian Languages Support
On Wednesday 22 January 2003 11:37, N-Velmani wrote: I'm trying to build a multilingual website for Indian languages. For this, I wanted to store my strings in Hindi and Tamil, in mysql. Now, could u tell me how can i achieve this? MySQL doesn't support character sets for Tamil or Hindi. Since 4.1 MySQL will support unicode. Currently you can take a look at the section of the manual 4.6.3 Adding a New Character Set: http://www.mysql.com/doc/en/Adding_character_set.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: create mysql user?? how
On Wednesday 22 January 2003 03:16, M A wrote: would someone please tell me how to create mysql user? and how to set the mysql root password? Please, check the following sections of the manual. There are a lot of info concerning your question: http://www.mysql.com/doc/en/Passwords.html http://www.mysql.com/doc/en/Default_privileges.html http://www.mysql.com/doc/en/Adding_users.html http://www.mysql.com/doc/en/GRANT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Need Help
On Wednesday 22 January 2003 12:10, Haydar KOCAK wrote: ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) [1]+ Exit 1 ./bin/mysqld_safe --user=mysql Check with ps ax | grep mysqld that MySQL server is running. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: re: foreign key problem
On Wednesday 22 January 2003 06:03, you wrote: Hi Victoria Even when I used InnoDB, it doesn't support foriegh key. It allows me to insert data into child table without being entered to parent table. Check that your both tables are InnoDB with SHOW TABLE STATUS command. Show me an example of your queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: resize InnoDB's log file
Ha-nyung, - Original Message - From: Chung Ha-nyung [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, January 22, 2003 6:48 AM Subject: resize InnoDB's log file Currently I have three InnoDB log files, whose size is 256MB and main memory is 2GB. Since I use 1GB of InnoDB buffer pool, I think log files'd better get bigger to some extent; possibly 1GB. You know, only changing the value in my.cnf cause mysqld not to start due to error. I'm wondering if deleting log files after mysqld is stopped and starting mysqld is safe. yes. You can read the procedure from the manual from http://www.innodb.com/ibman.html#Adding_and_removing: If you want to change the number or the size of your InnoDB log files, you have to shut down MySQL and make sure that it shuts down without errors. Then copy the old log files into a safe place just in case something went wrong in the shutdown and you will need them to recover the database. Delete then the old log files from the log file directory, edit my.cnf, and start MySQL again. InnoDB will tell you at the startup that it is creating new log files. Note the following IMPORTANT bug fix: http://www.innodb.com/ibman.html#InnoDB_history: MySQL/InnoDB-3.23.55, January xx, 2003 ... Fixed a bug: if the combined size of InnoDB log files was = 2 GB in a 32-bit computer, InnoDB would write log in a wrong position. That could make crash recovery and InnoDB Hot Backup to fail in log scan. This fix is already in 4.0.9. Also note that the recommendation is that the combined log file size should be 50 % of the buffer pool size and always 4 GB. 3 GB is too much.for a buffer pool of 1 GB. I tried this and got some look-so-serious error(or warning?) messages like following: 030121 10:18:49 mysqld ended 030121 10:27:07 mysqld started 030121 10:27:11 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 1024 MB InnoDB: Database physically writes the file full: wait... 030121 10:28:05 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 1024 MB InnoDB: Database physically writes the file full: wait... 030121 10:29:02 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 1024 MB InnoDB: Database physically writes the file full: wait... 030121 10:29:58 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3205932556 InnoDB: Doing recovery: scanned up to log sequence number 0 3205932556 InnoDB: Last MySQL binlog file position 0 13846, file name ./lacrimosa- bin.003 030121 10:29:58 InnoDB: Flushing modified pages from the buffer pool... 030121 10:29:59 InnoDB: Started /austin/local/mysql/bin/mysqld: ready for connections These are the normal messages when you resize the log files. InnoDB always does a 'crash recovery' at a startup. In a regular startup these messages are suppresses and you do not see them. query. sql -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: foreign key problem
Saju, - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, January 21, 2003 4:27 PM Subject: re: foreign key problem On Tuesday 21 January 2003 09:56, Saju Pappachen wrote: In my MySQL I have 3 tables like this and I have a pblm. in setting the foreign key.Pls. help [skip] create table employee_skill_details(EMP_ID int(10) not null references employee_details(EMP_ID), SKILL_ID varchar(5) not null references skill_master(SKILL_ID),primary key(EMP_ID,SKILL_ID)); Now based on this I should not be able to add an element to employee_skill_details table if the curresponding EMP_ID is not present in employee_details and SKILL_ID is not present in skill_master, because I have set references for it.i.e. foreign key. But still I am able to add data in employee_skill_details directly eventhough those data is not present in the other two tables. What is the problem? FOREIGN KEY is supported only on InnoDB tables: http://www.mysql.com/doc/en/SEC449.html In your case REFERENCES do nothing, so MySQL doesn't check if corresponding rows are existing in the parent table: http://www.mysql.com/doc/en/example-Foreign_keys.html please look the foreign key syntax and its usage from: http://www.innodb.com/ibman.html#InnoDB_foreign_keys -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AW: Socket Error
Hi Franz, I'm running OpenBSD 3.2. I installed it with pkg_add. Version 3.23.49. I try starting it with mysql_safe or mysql. It's trying to connect with this socket that doesn't exist. How do I create it? Thanks, Anthony. --- Franz, Fa. PostDirekt MA [EMAIL PROTECTED] wrote: Good Morning Anthony , your question was: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (38) I've been reading and searching the documentation for hours and I know this is simple to fix but I can't figure it out. I've been at it for our hours. The mysql directory above does not exist but if I create it and make a file called mysql.sock that does not work either. Could someone point me in the right direction? Please Looks like your mysql-daemon doesn't run at all or/and isn't installed in the directory your client expected it. In order to help you there's some information needed: Which operating system do you use ? How did you install (e.g. rpm) ? Which version of mysql did you try to install ? How did you start the server ? If you don't know the answers to some of that questions , describe what you did. prosit Klaus __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ReiserFS vs Ext3
Nicholas Gaugler wrote: said that ReiserFS handled large amounts of files much better than ext2 or ext, but what about a MySQL database situation with a very limited numbers of files. Such as 300, or less than 100 even? Is Reiser a better FS than ext3 for MySQL when you have a very limited number of files, and when the files are larger in size, such as 2-3GB each? Or is there not much I would E-mail the reiserfs list and ask for whether ReiserFS would be better for a MySQL-only partition than ext3 and see what answers you get. In the worst case, you'll have some extreme resierfs is always better responses that don't help you decide. In the best (and what I think will happen), someone will tell you the differences in CPU usage or fast file access speeds or some such value that will mean something, or even that it is not actually going to make a difference. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table joins are slow things to deal with. . .
Steve Quezadas wrote: PS Here is some information about my tables and indexes: Maybe I missed it, but where's the EXPLAIN on the JOIN query? -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rank Amateur Back for More
Stephen Tiano wrote: Observation: I guess there's no way to just give myself a 'free pass' to be able to load data into any database. It appears I have to name a particular database right at the start. A database is a database; there is nothing _outside_ a database. If you load the mysql shell without specifying a database, you are in limbo. What's up with that? I checked the folder 'menagerie' in the Sites folder. pet.txt is most definitely there, and it contains comma delimited rows to go with the tutorial table I'm working at. LOAD DATA is executed by the server, therefore within the server's directory namespace. Please read http://www.mysql.com/doc/en/LOAD_DATA.html -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ReiserFS vs Ext3
-Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] I would E-mail the reiserfs list and ask for whether ReiserFS would be better for a MySQL-only partition than ext3 and see what answers you get. In the worst case, you'll have some extreme resierfs is always better responses that don't help you decide. In the best (and what I think will happen), someone will tell you the differences in CPU usage or fast file access speeds or some such value that will mean something, or even that it is not actually going to make a difference. If Nicholas does this, I hope he'll report back here with what he finds out. I'm facing the same choice very soon, for a database that will eventually have millions of entries (but each individual entry very small.) I'm trying to decide whether to go with reiserfs or ext3. ext3's my current favorite, but only because it's more of a known, stable quantity. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AW: Socket Error
Here is what I got out of the log file: 030122 02:59:51 mysqld started 030122 2:59:51 Can't start server : Bind on unix socket: Address already in use 030122 2:59:51 Do you already have another mysqld server running on socket: /var/run/mysql/mysql.sock ? 030122 2:59:51 Aborting But mysqld is not running, here is the output from top: ID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND 11347 root 280 728K 1376K run - 0:01 0.00% sshd 19398 root 20 316K 904K idle select 0:00 0.00% sftp-server 8678 root 20 356K 864K idle select 0:00 0.00% sshd 14804 www20 1468K 2592K sleep select 0:00 0.00% httpd 22335 www20 2672K 3232K idle netcon 0:00 0.00% httpd 27374 www20 2668K 3228K idle netcon 0:00 0.00% httpd 23309 www20 2668K 3224K idle netcon 0:00 0.00% httpd 6726 www20 2668K 3216K idle netcon 0:00 0.00% httpd 19186 root 20 228K 456K sleep select 0:00 0.00% cron 11450 www20 2668K 3240K idle netcon 0:00 0.00% httpd 19456 root 180 344K 296K idle pause 0:00 0.00% csh 24644 root 20 100K 380K idle select 0:00 0.00% syslogd 15108 www20 1468K 1172K idle netcon 0:00 0.00% httpd 14034 root 280 144K 716K run - 0:00 0.00% top 26192 ad 30 376K 288K idle ttyin 0:00 0.00% sh 1 root 100 336K 200K idle wait 0:00 0.00% init 7933 root 180 324K 240K idle pause 0:00 0.00% csh 26396 root 30 48K 420K idle ttyin 0:00 0.00% getty 17248 root 30 48K 420K idle ttyin 0:00 0.00% getty 26494 root 30 48K 420K idle ttyin 0:00 0.00% getty 1790 root 30 48K 420K idle ttyin 0:00 0.00% getty 10937 root 20 64K 348K idle select 0:00 0.00% inetd 12757 www20 1468K 584K idle netcon 0:00 0.00% httpd --- Franz, Fa. PostDirekt MA [EMAIL PROTECTED] wrote: Good Morning Anthony , your question was: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (38) I've been reading and searching the documentation for hours and I know this is simple to fix but I can't figure it out. I've been at it for our hours. The mysql directory above does not exist but if I create it and make a file called mysql.sock that does not work either. Could someone point me in the right direction? Please Looks like your mysql-daemon doesn't run at all or/and isn't installed in the directory your client expected it. In order to help you there's some information needed: Which operating system do you use ? How did you install (e.g. rpm) ? Which version of mysql did you try to install ? How did you start the server ? If you don't know the answers to some of that questions , describe what you did. prosit Klaus __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Issues with InnoDB Replication
Perhaps I have mis-read the documentation, or I have not set my configuration options correctly: Any assistance much appreciated! I have two db servers, one set as a Master and one set as a Slave. Both are identically configured (RH7.3, MySQL-Max 3.23.54) to use InnoDB. However, there are occasions where I need to bulk load data to the Master, which I do using a SQL script, the first command of which is SET FOREIGN_KEY_CHECKS = 0. You can see what's coming... When I load the data to Master, it works perfectly, but the replication chokes because of foreign key constraint violations. I have two work-arounds to this: (a) Make sure my bulk load data is set up in the correct order so as to remove any FK errors (too tedious!), or (b), create Slave with no FK constraints -- which will be an issue when I add new tables to Master with FK's, as these will replicate. So what do I do? Is there some configuration setting I can tweak to make the 'SET-FK_CHECK' parameter to replicate as well? Or is this a feature? Michael. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Need help installing mysql - Thanks a lot
What does mysqld.log say? Manuel Velasco wrote: I'm getting the following messages after running safe_mysqld (as root): leo:/usr/bin # ./safe_mysqld [1] 4798 leo:/usr/bin # Starting mysqld daemon with databases from /var/lib/mysql 030121 19:38:15 mysqld ended [1]+ Done./safe_mysqld leo:/usr/bin # cd /var/lib/mysql leo:/var/lib/mysql # ls .leo-bin.003 leo-bin.007mysql.sock .. leo-bin.004 leo-bin.008mysqld.log leo-bin.001 leo-bin.005 leo-bin.index mysqld.log-20030114.gz leo-bin.002 leo-bin.006 mysql test leo:/var/lib/mysql # mysql ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Socket error - help from gurus
Hi, I'm new to MySql and to this list. I'm confused and lost. I installed MySQL in a Mandrake box and with no problem at all, just followed the installation procedures. Then I created a database, tables etc. All just fine. Then I connected the database with a Domino application, again everything fine, MySQL as the data repository and Domino application as the data entry. Suddenly, the communication broke! Domino was saying that was not possible to connect with the external system. I tested the data source in my Win2000 server, the connection was OK; I did I few queries manually, fine. Then I thought that the problem must be with Domino, but I decided, just in case, to quit the MySQL server, reboot the Linux and I'm not able to start the MySQL server, mysqladmin says that cannot connect through socket, I don't find the my.conf. server.start, server.stop and mysql.sock anywhere, anymore!! It seems to me that MySQL simply vanished from the box after the reboot!! Why? Any ideas? What can I do now? I'm running Mandrake 9.0, MySql 3.23.54a (RPM) I appreciate any help from you. I have no idea what it is going on and how to fix it. TIA Pedro - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems: Configuring from source.
You need gnu tar. Froin Laven wrote: Solaris 8 (sparc) gcc 3.2 Configuring from source version mysql-3.23.54a 2 problems: 1. I get a checksum error when untar'ing the tgz file found on any of the download servers. 2. config.status: error: cannot find input file: readline/Makefile.in The directory exists, but there are no files. Any help would be appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqld getting signal 14
Hi all- I'm running mysql 3.23.42 on multiple machines (Solars 8, 32 bit) and on a couple of the machines I see the following in the .err log: .. 030120 9:11:10 Warning: Got signal 14 from thread 4 030120 9:11:10 Warning: Got signal 14 from thread 4 030122 2:20:09 Warning: Got signal 14 from thread 4 030122 2:20:09 Warning: Got signal 14 from thread 4 .. It only occurs on a few machines, although all do the same type of processing. On one machine, mysqld crashed and restarted (it had been running for several months). It's worth noting that on most machines, the .err log is error-free and mysqld has been up for 6-9 months in most cases. What is the cause of this SIGALRM? And it's always thread 4, on every machine that it occurs?. Is this a known bug or problem fixed in a later release (and yes, I went through the list of fixes and did not see this explicitly)? Thanks for any help, R - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ReiserFS vs Ext3
David Brodbeck wrote: If Nicholas does this, I hope he'll report back here with what he finds out. I would recommend that as well. I'm facing the same choice very soon, for a database that will eventually have millions of entries (but each individual entry very small.) I'm trying to decide whether to go with reiserfs or ext3. ext3's my current favorite, but only because it's more of a known, stable quantity. If I may cause a flame-war here (j/k), reiserfs is a very known, very stable quantity. I've been using reiserfs for years now and it has never failed me except in the case of a physical drive failure (and it still saved me most of my files). Reiserfs is _very_ fast compared with the ext filesystems; I can't imagine running my /home and /var partitions on anything else ever again (and the rest are ext3 only because the RedHat installer wouldn't do reiserfs during the install). For what its worth, reiser4 (the new version of reiserfs that seems almost complete) as well as a patched version of reiserfs (version 3) have hooks in them to allow software (currently Squid) to use the internal tree and storage routines and bypass the filesystem layer altogether. This would make a lot of sense for a program like the mysql daemon (much as InnoDB probably does for its raw storage already) and I'd be very interested in the speed with which InnoDB can open and find records vs. the tree structure speeds in reiser4. Reiser4 is also supposed to have full filesystem transaction support, which is a great feature for databases in the first place. If you want an idea of how ReiserFS is built to work, you'd probably (untested) get quite good performance (for a specific group of test cases) out of writing a MySQL backend that stored tables as directories under their database directories with row values as files under row directories, with directories named by their primary index values (randomly otherwise?). It would make an interesting project at any rate. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: AW: Socket Error
How are you starting the MySQL server? (e.g. command) What does you cnf file look like? -Original Message- From: Zion Inc. [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 22, 2003 8:14 AM To: Franz, Fa. PostDirekt MA Cc: [EMAIL PROTECTED] Subject: Re: AW: Socket Error Here is what I got out of the log file: 030122 02:59:51 mysqld started 030122 2:59:51 Can't start server : Bind on unix socket: Address already in use 030122 2:59:51 Do you already have another mysqld server running on socket: /var/run/mysql/mysql.sock ? 030122 2:59:51 Aborting But mysqld is not running, here is the output from top: ID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND 11347 root 280 728K 1376K run - 0:01 0.00% sshd 19398 root 20 316K 904K idle select 0:00 0.00% sftp-server 8678 root 20 356K 864K idle select 0:00 0.00% sshd 14804 www20 1468K 2592K sleep select 0:00 0.00% httpd 22335 www20 2672K 3232K idle netcon 0:00 0.00% httpd 27374 www20 2668K 3228K idle netcon 0:00 0.00% httpd 23309 www20 2668K 3224K idle netcon 0:00 0.00% httpd 6726 www20 2668K 3216K idle netcon 0:00 0.00% httpd 19186 root 20 228K 456K sleep select 0:00 0.00% cron 11450 www20 2668K 3240K idle netcon 0:00 0.00% httpd 19456 root 180 344K 296K idle pause 0:00 0.00% csh 24644 root 20 100K 380K idle select 0:00 0.00% syslogd 15108 www20 1468K 1172K idle netcon 0:00 0.00% httpd 14034 root 280 144K 716K run - 0:00 0.00% top 26192 ad 30 376K 288K idle ttyin 0:00 0.00% sh 1 root 100 336K 200K idle wait 0:00 0.00% init 7933 root 180 324K 240K idle pause 0:00 0.00% csh 26396 root 30 48K 420K idle ttyin 0:00 0.00% getty 17248 root 30 48K 420K idle ttyin 0:00 0.00% getty 26494 root 30 48K 420K idle ttyin 0:00 0.00% getty 1790 root 30 48K 420K idle ttyin 0:00 0.00% getty 10937 root 20 64K 348K idle select 0:00 0.00% inetd 12757 www20 1468K 584K idle netcon 0:00 0.00% httpd --- Franz, Fa. PostDirekt MA [EMAIL PROTECTED] wrote: Good Morning Anthony , your question was: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (38) I've been reading and searching the documentation for hours and I know this is simple to fix but I can't figure it out. I've been at it for our hours. The mysql directory above does not exist but if I create it and make a file called mysql.sock that does not work either. Could someone point me in the right direction? Please Looks like your mysql-daemon doesn't run at all or/and isn't installed in the directory your client expected it. In order to help you there's some information needed: Which operating system do you use ? How did you install (e.g. rpm) ? Which version of mysql did you try to install ? How did you start the server ? If you don't know the answers to some of that questions , describe what you did. prosit Klaus __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unicode
I noticed that in version 4.1, there would be support for sorting with unicode. I have a few questions regarding unicode in MySQL: 1) Is it used just like a regular Char-set? Will this work just the same? SELECT name FROM mytable WHERE name='michelle' SELECT name FROM mytable WHERE name='name using russian alphabet' 2) Is it going to affect anything regarding performance etc? 3) I read some of the things on unicode.org, but this is only the pros. Is there any cons for using unicode? When should I use it? Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
binary of MySQL 4.1 for windows already available somewhere?
Hello I'm working on my diploma - project (very big hyperdatabase for a big search engine). I need the new features of MySQL 4.1, but I don't have the time to compile all, because i must install all the tools and perhaps it is not so easy Does anyone have a running version of MySQL 4.1 (Windows)? Thank you very much for your help Patrick - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Fwd: MYsql won't run
At 14:09 21/01/2003 -0500, FlashGuy wrote: Well...I uninstalled mysql and installed it again and I still can't get the service to run!!!??? So, first try to start it with: mysqld-nt --standalone --console and verify what are the error messages printed. ==BEGIN FORWARDED MESSAGE== From: FlashGuy [EMAIL PROTECTED] To: FlashMX [EMAIL PROTECTED] Date: Tue, 21 Jan 2003 09:05:52 -0500 Reply-To: FlashGuy [EMAIL PROTECTED] Priority: Normal X-Mailer: PMMail 2000 Standard (2.20.2502) For Windows 2000 (5.1.2600;1) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit Subject: MYsql won't run Hi, I had MYsql running on a nt4 server and w2k server. I uninstalled it a few weeks ago and just recently tried installing it again. The install went fine but when I start the service I keep getting an error no maater what I try. Using localhost and administrator. Whats going on? If I install it on a fresh pc (never had it installed before) it works OK. --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ===END FORWARDED MESSAGE=== --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
query cache not working with alter table rename as...
Hi List, Anyone know what the problem is with the mysql query cache not flushing cache properly (see snip below)? Is this a bug or am I missing something here? I'm using Mysql Standard 4.0.9-gamma on Linux (x86, libc6) Any help would be appreciated, Cheers, Andrew -start snip- mysql select * from andrew; +--+++---++--+--++-- +--+ | event_id | title | venue_name | price | pc | x| y| wibble | lon | lat | +--+++---++--+--++-- +--+ | NULL | wibble || || NULL | NULL || NULL | NULL | +--+++---++--+--++-- +--+ 1 row in set (0.05 sec) mysql alter table andrew rename as andrew_blah; Query OK, 0 rows affected (0.02 sec) mysql select * from andrew; +--+++---++--+--++-- +--+ | event_id | title | venue_name | price | pc | x| y| wibble | lon | lat | +--+++---++--+--++-- +--+ | NULL | wibble || || NULL | NULL || NULL | NULL | +--+++---++--+--++-- +--+ 1 row in set (0.00 sec) --end snip- Query,sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mandatory server downtime?
David T-G wrote: - do you have redundant networking to my machine, both to handle a failed switch and a backhoe accident? ... and do you have the ability to get such incidents repaired quickly ... our one upstream ISP had their entire fibre bundle torn out of the ground by a construction crew and had the entire bundle spliced back together the same day (6 hours). I almost poured coffee on myself when they called to say it was back online. Speaking of which -- find out what networks they're connected to and check those companies' current SEC filings :-) - do you store your tapes in a disaster-proof facility? ... and who is responsible for doing so, and calculate how far back this means you have to go. If they move tapes to another site every night, it may still mean a 48 hour rollback in the case of a flood or fire, depending on how tapes are handled. PS, there's no reason not to buy cheap space at another ISP that allows SSH and simply run a secure tunnel to your primary ISP and run it as a replication client to save your MySQL data. I'm sure David, myself or any other ISP person in this list would be happy to quote you on such a job. - what is your expected restore time for a file or db table on my machine? - what is your expected recovery time for a complete disaster on my machine? I have a client who cancelled their support contract with us and still does their regular backups but has absolutely no-one on staff who knows how to do a proper restore. Don't be in that situation. (We'd do one if they paid us, of course ...) and go from there; if any of the answers don't sound right, then ask more questions about that topic. If they tell you its a stupid question, that's another good hint :-) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unicode
I noticed that in version 4.1, there would be support for sorting with unicode. I have a few questions regarding unicode in MySQL: 1) Is it used just like a regular Char-set? 2) Will these querys work just the same? SELECT name FROM mytable WHERE name='michelle' SELECT name FROM mytable WHERE name='name using russian alphabet' 3) Is it going to affect anything regarding performance etc? 4) I read some of the things on unicode.org, but this is only the pros. Is there any cons for using unicode? When should I use it? Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unicode
I noticed that in version 4.1, there would be support for sorting with unicode. I have a few questions regarding unicode in MySQL: 1) Is it used just like a regular Char-set? 2) Will these querys work just the same? SELECT name FROM mytable WHERE name='michelle' SELECT name FROM mytable WHERE name='name using russian alphabet' 3) Is it going to affect anything regarding performance etc? 4) I read some of the things on unicode.org, but this is only the pros. Is there any cons for using unicode? When should I use it? Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ReiserFS vs Ext3
What about XFS? I'm considering using XFS instead of ext3 since it seems to be said that XFS is more stable and efficient than any other linux's journaling fs in the environment of large and not-so-many files. If it is true, I guess that XFS would be the best choice with InnoDB. -- Chung Ha-nyung alita@[neowiz.com|kldp.org] SayClub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: David Brodbeck [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 22, 2003 11:10 PM To: Nicholas Gaugler Cc: [EMAIL PROTECTED] Subject: RE: ReiserFS vs Ext3 -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] I would E-mail the reiserfs list and ask for whether ReiserFS would be better for a MySQL-only partition than ext3 and see what answers you get. In the worst case, you'll have some extreme resierfs is always better responses that don't help you decide. In the best (and what I think will happen), someone will tell you the differences in CPU usage or fast file access speeds or some such value that will mean something, or even that it is not actually going to make a difference. If Nicholas does this, I hope he'll report back here with what he finds out. I'm facing the same choice very soon, for a database that will eventually have millions of entries (but each individual entry very small.) I'm trying to decide whether to go with reiserfs or ext3. ext3's my current favorite, but only because it's more of a known, stable quantity. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Socket error - help from gurus
Pedro Leite wrote: Hi, I'm new to MySql and to this list. I'm confused and lost. I installed MySQL in a Mandrake box and with no problem at all, just followed the installation procedures. Then I created a database, tables etc. All just fine. Then I connected the database with a Domino application, again everything fine, MySQL as the data repository and Domino application as the data entry. Suddenly, the communication broke! Domino was saying that was not possible to connect with the external system. I tested the data source in my Win2000 server, the connection was OK; I did I few queries manually, fine. Then I thought that the problem must be with Domino, but I decided, just in case, to quit the MySQL server, reboot the Linux and I'm not able to start the MySQL server, mysqladmin says that cannot connect through socket, Mysqladmin can not start the server. I don't find the my.conf. server.start, server.stop and Where did you get these file names? They don't exist on any system. mysql.sock anywhere, anymore!! It exists only when the server is running. It seems to me that MySQL simply vanished from the box after the reboot!! Why? Any ideas? What can I do now? Start the server. I don't know Mandrake. login is root and run service mysql start If that does not work, find the program mysql.server. Go to the directory that contains this file and run ./mysql.server start I'm running Mandrake 9.0, MySql 3.23.54a (RPM) I appreciate any help from you. I have no idea what it is going on and how to fix it. TIA Pedro - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump questions
I have 2 questions concerning mysqldump. 1) What grants does the user need to have to perform a dump: if I want to create a user which is only used to perform database dumps (in scripts), which priviliges does it need ? 2) I'm using innoDB tables to be able to use foreign keys. Unfortunatly mysqldump seems to dump the tables in alphabetical order, which results in error when one tries to restore a database from this dump (due to references to tables which don't exist yet). This is easily fixed for a small database (with a few cut and pastes), but is the database gets complex, it causes some headache. Is there a manner in which this can be avoided ? I'm using mySQL 3.23.53 on a Windows 2000 environnement. thanks in advance, Glenn Ticket - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AW: Socket Error
Top does not show all processes. try: ps x | grep mysqld or ps -ef | grep mysqld Zion Inc. wrote: Here is what I got out of the log file: 030122 02:59:51 mysqld started 030122 2:59:51 Can't start server : Bind on unix socket: Address already in use 030122 2:59:51 Do you already have another mysqld server running on socket: /var/run/mysql/mysql.sock ? 030122 2:59:51 Aborting But mysqld is not running, here is the output from top: ID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND 11347 root 280 728K 1376K run - 0:01 0.00% sshd 19398 root 20 316K 904K idle select 0:00 0.00% sftp-server 8678 root 20 356K 864K idle select 0:00 0.00% sshd 14804 www20 1468K 2592K sleep select 0:00 0.00% httpd 22335 www20 2672K 3232K idle netcon 0:00 0.00% httpd 27374 www20 2668K 3228K idle netcon 0:00 0.00% httpd 23309 www20 2668K 3224K idle netcon 0:00 0.00% httpd 6726 www20 2668K 3216K idle netcon 0:00 0.00% httpd 19186 root 20 228K 456K sleep select 0:00 0.00% cron 11450 www20 2668K 3240K idle netcon 0:00 0.00% httpd 19456 root 180 344K 296K idle pause 0:00 0.00% csh 24644 root 20 100K 380K idle select 0:00 0.00% syslogd 15108 www20 1468K 1172K idle netcon 0:00 0.00% httpd 14034 root 280 144K 716K run - 0:00 0.00% top 26192 ad 30 376K 288K idle ttyin 0:00 0.00% sh 1 root 100 336K 200K idle wait 0:00 0.00% init 7933 root 180 324K 240K idle pause 0:00 0.00% csh 26396 root 30 48K 420K idle ttyin 0:00 0.00% getty 17248 root 30 48K 420K idle ttyin 0:00 0.00% getty 26494 root 30 48K 420K idle ttyin 0:00 0.00% getty 1790 root 30 48K 420K idle ttyin 0:00 0.00% getty 10937 root 20 64K 348K idle select 0:00 0.00% inetd 12757 www20 1468K 584K idle netcon 0:00 0.00% httpd --- Franz, Fa. PostDirekt MA [EMAIL PROTECTED] wrote: Good Morning Anthony , your question was: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (38) I've been reading and searching the documentation for hours and I know this is simple to fix but I can't figure it out. I've been at it for our hours. The mysql directory above does not exist but if I create it and make a file called mysql.sock that does not work either. Could someone point me in the right direction? Please Looks like your mysql-daemon doesn't run at all or/and isn't installed in the directory your client expected it. In order to help you there's some information needed: Which operating system do you use ? How did you install (e.g. rpm) ? Which version of mysql did you try to install ? How did you start the server ? If you don't know the answers to some of that questions , describe what you did. prosit Klaus __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Issues with InnoDB Replication
Michael, a good point. SET FOREIGN_KEY_CHECKS is currently not replicated properly. I have to fix this by wrapping the SQL statement in the binlog inside SET FOREIGN_KEY_CHECKS=0; statement here SET FOREIGN_KEY_CHECKS=1; if thd-options OPTION_NO_FOREIGN_KEY_CHECKS is true for the connection. I hope the fix makes it to 4.0.10 or 4.0.11. Thank you, Heikki Innobase Oy Subject: Issues with InnoDB Replication From: Michael Merwitz Date: 22 Jan 2003 09:16:42 -0500 Perhaps I have mis-read the documentation, or I have not set my configuration options correctly: Any assistance much appreciated! I have two db servers, one set as a Master and one set as a Slave. Both are identically configured (RH7.3, MySQL-Max 3.23.54) to use InnoDB. However, there are occasions where I need to bulk load data to the Master, which I do using a SQL script, the first command of which is SET FOREIGN_KEY_CHECKS = 0. You can see what's coming... When I load the data to Master, it works perfectly, but the replication chokes because of foreign key constraint violations. I have two work-arounds to this: (a) Make sure my bulk load data is set up in the correct order so as to remove any FK errors (too tedious!), or (b), create Slave with no FK constraints -- which will be an issue when I add new tables to Master with FK's, as these will replicate. So what do I do? Is there some configuration setting I can tweak to make the 'SET-FK_CHECK' parameter to replicate as well? Or is this a feature? Michael. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Which is the upper limit?
Hi all,
I've tried the repeat function for testing some tables, but it cannot
create very big strings.
Do you know which is its upper limit?
I've tried the following query:
mysql select length(repeat('abracadabra', 10));
+---+
| length(repeat('abracadabra', 10)) |
+---+
| NULL |
+---+
1 row in set (0.00 sec)
If I use smaller numbers, it works.
Thank you.
Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ReiserFS vs Ext3
You might be interested in this presentation from Linux World Conference August, 2002, which gave a comparison of Linux Journaling file system types based on io tests run at the Open Source Development Lab: http://www.osdl.org/presentations/lwe-jgfs.pdf On Wed, 2003-01-22 at 07:01, Michael T. Babcock wrote: David Brodbeck wrote: If Nicholas does this, I hope he'll report back here with what he finds out. I would recommend that as well. I'm facing the same choice very soon, for a database that will eventually have millions of entries (but each individual entry very small.) I'm trying to decide whether to go with reiserfs or ext3. ext3's my current favorite, but only because it's more of a known, stable quantity. If I may cause a flame-war here (j/k), reiserfs is a very known, very stable quantity. I've been using reiserfs for years now and it has never failed me except in the case of a physical drive failure (and it still saved me most of my files). Reiserfs is _very_ fast compared with the ext filesystems; I can't imagine running my /home and /var partitions on anything else ever again (and the rest are ext3 only because the RedHat installer wouldn't do reiserfs during the install). For what its worth, reiser4 (the new version of reiserfs that seems almost complete) as well as a patched version of reiserfs (version 3) have hooks in them to allow software (currently Squid) to use the internal tree and storage routines and bypass the filesystem layer altogether. This would make a lot of sense for a program like the mysql daemon (much as InnoDB probably does for its raw storage already) and I'd be very interested in the speed with which InnoDB can open and find records vs. the tree structure speeds in reiser4. Reiser4 is also supposed to have full filesystem transaction support, which is a great feature for databases in the first place. If you want an idea of how ReiserFS is built to work, you'd probably (untested) get quite good performance (for a specific group of test cases) out of writing a MySQL backend that stored tables as directories under their database directories with row values as files under row directories, with directories named by their primary index values (randomly otherwise?). It would make an interesting project at any rate. -- Mary Edie Meredith [EMAIL PROTECTED] Open Source Development Lab - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:mysql: field type of double without scientific notation
You probably have created your table without specifying the format of the double... the following statement would be more appropriate to your expectation: mysql create table testTable (id int, val double(12, 9)); this will set the display width of the double to 12 and the number of digits following the decimal point to 9. You can ALTER the column definition to avoid re-entring the data.. -Nasser -- Philipp Sutter [EMAIL PROTECTED] writes: Return-Path: [EMAIL PROTECTED] Received: from mx05.lax.untd.com (mx05.lax.untd.com [10.130.24.65]) by maildeliver03.nyc.untd.com with SMTP id AAA9C4WJSAMUQ6QJ for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Tue, 21 Jan 2003 08:54:24 -0500 (EST) Received: from web.mysql.com (web.mysql.com [213.136.49.183]) by mx05.lax.untd.com with SMTP id AAA9C4WJRAQCPDZJ for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Tue, 21 Jan 2003 05:54:23 -0800 (PST) Received: (qmail 19865 invoked by uid 7797); 21 Jan 2003 13:36:40 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] Received: (qmail 19846 invoked from network); 21 Jan 2003 13:36:40 - Subject: mysql: field type of double without scientific notation From: Philipp Sutter [EMAIL PROTECTED] To: [EMAIL PROTECTED] In-Reply-To: [EMAIL PROTECTED] References: [EMAIL PROTECTED] Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 21 Jan 2003 16:40:53 +0100 Message-Id: 1043163653.3899.7.camel@sutter Mime-Version: 1.0 I have a Mysql-table with a field of type double. when i retrieve very small numbers from this table I get them allways in a scientific notation: i.e. 8.34e-05. what i have to do, that i get the same number in normal notation: 0.834? thank you for any hints in advance! philipp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can't seem to use a different datadir....
Hi. Anyone know the trick to successfully using a different directory for database files? I've tried changing my.cnf and also using the command line option. When I run mysqld_safe it says it's using the new directory but then writes mysqld ended and quits. Mysql never comes up. I changed owner and group of the new directory and files to be mysql. What can be going wrong? It just doesn't seem to work! Thanks!! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unicode
Michelle de Beer wrote: 3) I read some of the things on unicode.org, but this is only the pros. Is there any cons for using unicode? When should I use it? If you do primarily english but want other language support, UTF-8 is _exactly_ the same as ASCII for the first 127 characters. If you store UTF-8 in binary fields you're fine -- and you can compare and sort it properly, since its in proper language search order for the most part already (accents and combination characters excluded). Full Unicode support in MySQL will best be described by the team, but will probably just mean that it knows how to combine characters for comparisons and sorting. Hopefully, they support UTF-8 and not just UCS-16 (like Microsoft does). UCS-16 is more efficient for storage but its a pain to rewrite code to use it (I convert to UTF-8 immediately for internal processing and any I/O I do in Unicode. UCS-16 is useful for storing the data on disk in only specific circumstances; like a database). -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:Trying to update data in a table - getting an error
try 'substring' instead of 'substr'. -- Don [EMAIL PROTECTED] writes: Return-Path: [EMAIL PROTECTED] Received: from mx11.lax.untd.com (mx11.lax.untd.com [10.130.24.71]) by maildeliver04.nyc.untd.com with SMTP id AAA9C453SATSGSQA for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Tue, 21 Jan 2003 11:02:56 -0500 (EST) Received: from web.mysql.com (web.mysql.com [213.136.49.183]) by mx11.lax.untd.com with SMTP id AAA9C453RAZK776S for [EMAIL PROTECTED] (sender [EMAIL PROTECTED]); Tue, 21 Jan 2003 08:02:55 -0800 (PST) Received: (qmail 3943 invoked by uid 7797); 21 Jan 2003 15:35:06 - Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm (http://www.ezmlm.org) List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Subscribe: mailto:[EMAIL PROTECTED] Delivered-To: mailing list [EMAIL PROTECTED] Received: (qmail 3784 invoked from network); 21 Jan 2003 15:34:55 - Message-ID: 008001c2c162$96e4fba0$c889cdcd@enterprise From: Don [EMAIL PROTECTED] To: msql list [EMAIL PROTECTED] Subject: Trying to update data in a table - getting an error Date: Tue, 21 Jan 2003 10:34:20 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Hi, RESENT DFUE TO TYPO I want to change a field in a table. Currently the data is in theformat -- 4818.50.00 I want to remove the decimal points so that it is like -- 48185000 I tried the sql code below but am getting a syntax error. Can someone please point out the error of my ways? UPDATE DocComms SET ItemHSCode = concat(substr( ItemHSCode, 1, 4 ), substr( ItemHSCode, 6, 2 ), substr( ItemHSCode, 9, 2 )) Thanks, Don --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.443 / Virus Database: 248 - Release Date: 1/10/2003 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NOT NULL Fields
Hi, Creating a table with a not null field, and then trying to insert it doesn't raise an error 'Column XXX cannot be null' Well, this was the steps (sql to bypass filter) i did: 1. create table TEST ( id_test integer, name varchar(50) not null ); 2. insert into TEST (id_test, name) values (1, 'ppl1'); (ok it works) 3. insert into TEST (id_test, name) values (2, NULL); (ok raise an error) 4. insert into TEST (id_test) values (3); (insert a row with name = '') This is correct? A bug? Or i'm to worried about raising errors? :) TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
deleting records
Hi, I've inherited a busted database and need to clean it up. I can't figure out how to do something which seemingly should be simple. For example, I've got two tables: Table One id int primary key auto_increment not null ...other data Table Two id int primary key auto_increment not null ...other data I'd like to delete all of the records from Table Two which have an id that is not found in Table One. I could do it programatically in C or PHP or ... but is there some kind of SQL statement than can do the job? Thanks, Bill -- Bill Rausch, Software Engineer UNIX for programming, Mac for productivity, Windows for solitaire... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table joins are slow things to deal with. . .
Hello. On Wed 2003-01-22 at 09:13:20 +0100, [EMAIL PROTECTED] wrote: Steve, ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); First thing that comes into mind is: You said you indexed the join fields (i.e. CaseNumber), but what about Defendants.Defendant? Okay, you did. Hmm. Next thing would be to check if the appropriate keys were used (CaseNumber, Defendant). Yes, they were used. Next thing would be to check if the optimizer chose a good join type: | Defendants | range | CaseNumber,Defendant | Defendant | 30 | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | The join types are range and eq_ref, that's fine. The number of examined rows in Defendants seems okay, too: rows | Extra | 82756 | where used | Good check list. :-) Two things I noticed: Using a 30-byte wide index (CaseNumber) is not fastest. Probably it would help to create an additional INT AUTO_INCREMENT with key and join via that. That reduces the amount of data to be read from disk for the index by the factor 4.25 ((30+4)/(4+4)), and also cuts down the time for comparisions and improves relative effictivness of the key cache. Whether this the main reason for the slowness, I don't know, but it cannot hurt to try. Another thing that I would try is to to create a combined index on (Filed,CaseNumber) and (CaseNumber,Filed). Drop the one that does not get used afterwards. This should enable MySQL to resolve the join only using the index: currently it has to load the data records in order to check whether Filed is in range (i.e. it has to load 34,000 records although only 10,500 are returned afterwards that is about 24,000 seeks and reads without need). An index on (Defendend,CaseNumber) will also help. Of course, if you introduce an id (the INT) column as suggested above, use that instead of CaseNumber in the combined indexes. And at last, be sure to run OPTIMIZE and ANALYZE on the tables (of, course, you can also start with that :-). Please check speed after each change and report back. I am very interested to hear whether any of the suggestion had real effect in your case (the EXPLAIN for each would also be nice). Btw, what size is your key_cache? And how many reads/misses does it have? HTH, Benjamin. Okay, I leave this one to the gurus :-/ Not sure that I'd call me like this, but I chose to add my 2 cents anyhow. ;) [...] Maybe I'm dumb for saying this, but sql joins seems expensive to do in terms of performance (yes, I indexed the joined fields). If I do a query search of a 2,600,000 record defendant table WITHOUT a join (SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%;). Performance is generally zippy at .53 seconds (which ends up pulling about 34,000 rows). HOWEVER, once I join this table with the much smaller [Cases] table (about 140,000 rows), performance plummets to 8.79 seconds! Quite a drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%);'. I get about 10,500 rows returned here, but man, what an increase in time. [...] ([Defendant] Query WITHOUT a join) - .53 seconds. EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%; ++---+---+---+-+--+- --+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+---+-+--+- --+-+ | Defendants | range | Defendant | Defendant | 30 | NULL | 82756 | where used; Using temporary | ++---+---+---+-+--+- --+-+ ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+-- -+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+-- -+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL
Re: Which is the difference?
Hello. On Tue 2003-01-21 at 18:52:06 +0200, [EMAIL PROTECTED] wrote: Hello all, I've tried the following sql queries: mysql create table a(id int unsigned not null auto_increment primary key, name text); Query OK, 0 rows affected (0.01 sec) mysql insert into a values(null, 'one'), (null, 'two'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql select last_insert_id() from a; +--+ | last_insert_id() | +--+ |1 | |1 | +--+ 2 rows in set (0.01 sec) Well, that doesn't really make sense, because LAST_INSERT_ID() is not bound to rows like this. Just use SELECT LAST_INSERT_ID(); without any reference to a table. What you did is like SELECT SIN(5) FROM a; i.e. it will simply return the constant value for each row found. #I've tried a second time: mysql select last_insert_id() from a; The result of LAST_INSERT_ID() does not change from selects (except for the ODBC case below). #I've tried to put a limit clause to see the last inserted ID only once: mysql select last_insert_id() from a limit 1; That's the wrong way to query LAST_INSERT_ID(). See above. #Now I've tried to find the last inserted ID by using where id is null but ... mysql select id from a where id is null; id IS NULL is supported for compatibility with ODBC. Don't use it in your programs, except if you have to. #The first trial was successfully, but the second not: mysql select id from a where id is null; Empty set (0.01 sec) #And from this point on, I get only empty responses. Please tell me why. I presume that is the behaviour ODBC expects. Since it's mainly for ODBC compatibility, don't wonder about it. It would have been made to return random results if that was what ODBC expected. So your question should rather be: why does ODBC expect it this way. I don't know (and I don't care). And BTW, if I insert more records in a single query, how can I find the real last one? Is the only solution counting the number of new entered records, and adding this number to the number returned by the last_insert_id() function? Yes. As http://www.mysql.com/doc/en/Miscellaneous_functions.html explains, LAST_INSERT_ID() returns the first inserted row to make it easier to reproduce the same insert. HTH, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Left join returns records it shouldn't
Description:
The following silly query (silly because it should return no
records since it is impossible for a.type to have two different
values simultaneously), returns two records when it should
return none with the enclosed test data.
select a.*, b.* from dns_rec a left join dns_rec b
on (a.zone = b.zone and
a.rightside = b.rightside and
a.type = 'A' and b.type = 'A' and
a.leftside = '' and b.leftside != '')
where b.zone is null and a.type = 'MX'
;
How-To-Repeat:
Restore following dump, containing 19 records. Run above query.
If you drop the index right_idx, you get the correct result, but
this makes the REAL intended query (the above query without the
and a.type = 'MX' part) run much MUCH slower than is tolerable
(I've never had time to let it finish) for the full database
(about 200,000 records). You get the same wrong answer if
right_idx is an index on rightside only.
This data is from a DNS database. The fields zone and rightside
have been replaced with a hash, but the problem is still
reproducable with the hash. The intent of the query is to
find all the top-level (name is the same as name of the zone,
e.g. foobar.com) A records which do not have a non-top-level
A record pointing at the same place (e.g. www.foobar.com).
I get back almost all the records.
I have tried checking indexes and dropping the table and reloading.
Corrupted tables or indexes do not seem to be an issue.
-- MySQL dump 8.22
--
-- Host: mysql.burditt.orgDatabase: p1
-
-- Server version 3.23.54-log
--
-- Table structure for table 'dns_rec'
--
CREATE TABLE dns_rec (
zone char(191) NOT NULL default '',
leftside char(63) NOT NULL default '',
class char(2) NOT NULL default 'IN',
ttl int(11) default NULL,
type char(6) NOT NULL default '',
mxpri int(11) default NULL,
rightside char(255) NOT NULL default '',
mod_user char(8) default NULL,
mod_time datetime default NULL,
id int(11) NOT NULL auto_increment,
dorev enum('Y','N') NOT NULL default 'Y',
PRIMARY KEY (id),
KEY zone_key (zone,leftside),
KEY right_idx (rightside,leftside)
) TYPE=MyISAM;
--
-- Dumping data for table 'dns_rec'
--
INSERT INTO dns_rec VALUES
('b718178a4a43a90edc77c32edc357c41','www','IN',86400,'A',NULL,'78668248f0026f1dee9f3d3615c6a3c1','gordonb','2003-01-20
17:12:25',2,'Y');
INSERT INTO dns_rec VALUES
('76649f82176bede3d63adda4b15b3f6c','216-118-17-94','IN',86400,'A',NULL,'b5272e80685c0d44a2efdcca42915e23','gordonb','2003-01-20
17:12:25',3,'Y');
INSERT INTO dns_rec VALUES
('c7ad2afab901a4b7725cc9236f612186','','IN',43200,'MX',10,'b83a886a5c437ccd9ac15473fd6f1788','gordonb','2003-01-20
17:12:25',100,'Y');
INSERT INTO dns_rec VALUES
('76649f82176bede3d63adda4b15b3f6c','216-118-35-215','IN',86400,'A',NULL,'1cd28f7acc17907519923f290fa8bd90','gordonb','2003-01-20
17:12:25',4,'Y');
INSERT INTO dns_rec VALUES
('10f95129f591c33d569825d519ac','87','IN',86400,'PTR',NULL,'cac23788d6426b77981900cccfa97344','gordonb','2003-01-20
17:12:25',5,'Y');
INSERT INTO dns_rec VALUES
('93b473baaea76836872b456eccf133f6','98','IN',NULL,'A',NULL,'b620fc599397b9bcf3197f8b5720c786','gordonb','2003-01-20
17:12:25',308,'Y');
INSERT INTO dns_rec VALUES
('ecf4d945c6e819af75f4612609b0f1de','wrangler','IN',86400,'CNAME',NULL,'678fac0aa4714c742c6341d07ee6a347','gordonb','2003-01-20
17:12:25',10,'Y');
INSERT INTO dns_rec VALUES
('287c097c2069bc00305c93f6a0cc214c','mail','IN',86400,'A',NULL,'83fcc28f1c5857ba69d7c5e055714d7e','gordonb','2003-01-20
17:12:25',11,'Y');
INSERT INTO dns_rec VALUES
('ba1c0f8033b7700750fcd44e99258442','cf9e7efe','IN',NULL,'A',NULL,'8ef3484a73acce6c80c2cd9d8be20ec1','gordonb','2003-01-20
17:12:25',98,'Y');
INSERT INTO dns_rec VALUES
('1ca3c3722a33ec06c7ab889e2308634c','97','IN',NULL,'A',NULL,'5ebcd38d80575ab790f0e86c92a7af23','gordonb','2003-01-20
17:12:25',106,'Y');
INSERT INTO dns_rec VALUES
('6f065a6b44888e4f43f2ec31411b2f49','pppte04-422','IN',NULL,'A',NULL,'4133269be2eb34a5c82d964a64ddbefb','gordonb','2003-01-20
17:12:25',285,'Y');
INSERT INTO dns_rec VALUES
('b36b250e8c66b2b3b133219b80dbe079','','IN',NULL,'MX',10,'d59ceba889986c4fdfbe27c3c8a919d6','gordonb','2003-01-20
17:12:25',201,'Y');
INSERT INTO dns_rec VALUES
('e2777c044daf17127184f8033c751249','28','IN',NULL,'A',NULL,'bc35afd157ffa6c8729557a6fb62810d','gordonb','2003-01-20
17:12:25',234,'Y');
INSERT INTO dns_rec VALUES
('00105a3bcac286c9f9487dfe896ad220','0b','IN',NULL,'A',NULL,'d982c79ede90bc888c2ec9757f143e9d','gordonb','2003-01-20
17:12:25',290,'Y');
INSERT INTO dns_rec VALUES
('eb74f350c3ac84314caca073ec778f2a','54','IN',NULL,'A',NULL,'72ccd8a4558c99fa55fca52f96585d51','gordonb','2003-01-20
17:12:25',297,'Y');
INSERT INTO
Re: Which is the difference?
Hello. On Wed 2003-01-22 at 08:53:23 +0100, [EMAIL PROTECTED] wrote: seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT. Yes, that is the documented behaviour: http://www.mysql.com/doc/en/Miscellaneous_functions.html Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Joins are slow
Try a straight join with the cases table first. You have to play with your queries to get the best results. Creating a temporary, as you mentioned, table might be a very good option in this case, but you need to make sure that your programming takes into account that your page might be hit more than once at a single time so your queries don't collide, i.e. someone is looking for 1999 cases and someone else is looking for 2000 cases so both are in your temp table and you're joining without a where or something, it seems obvious but I've seen it not accounted for. Another thing that might get you is populating your temp table via select into can cause MySQL to use temp tables on disk instead of memory if the data set is very large and your server is not optimised for it, that will take speed way down. -Original Message- From: Steve Quezadas [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 21, 2003 7:19 PM To: [EMAIL PROTECTED] Subject: Joins are slow Maybe I'm dumb for saying this, but sql joins seems expensive to do in terms of performance (yes, I indexed the joined fields). If I do a query search of a 2,600,000 record defendant table WITHOUT a join (SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%;). Performance is generally zippy at .53 seconds (which ends up pulling about 34,000 rows). HOWEVER, once I join this table with the much smaller [Cases] table (about 140,000 rows), performance plummets to 8.79 seconds! Quite a drop! The SQL statement is: 'SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%);'. I get about 10,500 rows returned here, but man, what an increase in time. So my main question is: Is this degradation in performance to be expected with a join? What I am thinking about doing is perhaps doing two smaller queries instead of one larger one. My first query can just query the very large Defendants table without a join and create a temporary table of CaseNumbers (takes about .53 seconds). Then I can join that temporary table into the much smaller [Cases] table and then run that part of the query (which takes about 1.23 seconds). So bottom line: Results of big query with one join: 8.79 seconds Results of creating two smaller queries: 1.76 seconds (.53 seconds + 1.23 seconds). So I am thinking about optimizing the query in my PHP program to make two smaller queries instead of one bigger query. Is this a standard thing to do to increase speed? Or am I doing something stupid and inefficient in the join that I am not aware of? - Steve PS Here is some information about my tables and indexes: ([Defendant] Query WITHOUT a join) - .53 seconds. EXPLAIN SELECT DISTINCT CaseNumber FROM Defendants WHERE Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%; ++---+---+---+-+--+---+- + | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+---+-+--+---+- + | Defendants | range | Defendant | Defendant | 30 | NULL | 82756 | where used; Using temporary | ++---+---+---+-+--+---+- + ([Defendant] Query WITH a join - 8.79 seconds! EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE Cases.CaseNumber = Defendants.CaseNumber AND Filed = 1999-01-01 AND (Defendant LIKE owen% OR Defendant LIKE pitts% OR Defendant LIKE general%); +++--+-+-+-- -+---++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +++--+-+-+-- -+---++ | Defendants | range | CaseNumber,Defendant | Defendant | 30 | NULL | 82756 | where used | | Cases | eq_ref | CasesNumber,Filed| CasesNumber | 30 | Defendants.CaseNumber | 1 | where used | +++--+-+-+-- -+---++ Table and index information Cases Table - 140,000 records +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | CaseNumber| char(30) | | PRI | | | | County| char(30) | | | | | | CaseName | char(120) | | | | | | Court | char(30) | | | | | | Filed |
Re: Which is the difference?
Benjamin, Yes, that is the documented behaviour: http://www.mysql.com/doc/en/Miscellaneous_functions.html (If you insert many rows at the same time with an insert statement, LAST_INSERT_ID() returns the value for the first inserted row.) Gee, I just copied this sentence into my new (German) book (short reference of MySQL) about two weeks ago blush Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Benjamin Pflugmann [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] Cc: Octavian Rasnita [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 7:29 PM Subject: Re: Which is the difference? Hello. On Wed 2003-01-22 at 08:53:23 +0100, [EMAIL PROTECTED] wrote: seems like LAST_INSERT_ID() will not always return the correct value. If you use ANSI-SQL INSERT, the function works fine. If you use MySQL extended INSERT (i.e. with more than one record per insert statement), the function will return the ID of the _first_ record inserted with an extended INSERT. Yes, that is the documented behaviour: http://www.mysql.com/doc/en/Miscellaneous_functions.html Regards, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: question about update sql
-Original Message- From: Norris, Joseph Sent: Wednesday, January 22, 2003 10:56 AM To: Mysql_List (E-mail) Subject: question about update sql Hello, I have the following in my SQL: update call_center set call_center.who_for_id = activeemployees.hr_emp_no where call_center.who_for_id = activeemployees.id; This is something I think that I should be able to do, but I get an error. Is there a way to do what I am trying to do here? Thanks to all. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimizing Ext3 for MySQL
Steven Roussey wrote: Might also look at: vmstat 1 `vmstat 1` is my favorite instant-info server debugging tool. I wouldn't mind the same program for MySQL (where's that mytop author anyhow? j/k) Blocked processes (second column) is a very useful piece of info too. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: three table join
Tab Alleman wrote: SELECT SomeStuff FROM Table1 AS T1 LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1 RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK WHERE T1.PK=999 Either right-join it or reverse the table order (because you're not asking for data from t1 that is like t2 that is like t3 that is like 999, you're asking for data from t3 which has data ... in t2 which has data ... in t1 which has pk 999 You have to think of the queries 'in order', left to right ... so that your LEFT join means what it should mean :). I rarely do right joins in small queries; it makes more sense to just reverse the query. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
solution for opposite of this join / join from this subselect
thanks to brent, bob and M wells for their contributions to this solution and to m especially who seems to have put in a lot of time and nailed it. This query returns a list of people not attending a particular event, given an events table, a people table, and a many-many epeople table between them. You can modify the where clause to show all the people attending a particular event, all the events a person isn't/is attending - most of what you might need in most many - many relationships. But I still haven't figured out the importance of left joins vs. right joins. can anyone explain why this statement requires a right join to work? could it be rewritten to use a left join? mysql SELECT lastname, firstname, title, event - FROM people p - LEFT JOIN epeople ep on p.id = ep.pid - right join events e ON e.id = ep.eid - WHERE ep.pid IS NULL - and ep.eid is null - and e.id=2 - ORDER BY e.id; I reworte the statement like this and from doing so understand that, OK epeople as the pivot point between the two tables needs to be on the outside of the join - really placing it between the two tables. we're looking for people where there is no record match for epeople. SELECT lastname, firstname, title, event FROM events e LEFT JOIN epeople ep on e.id = ep.eid right join people p ON p.id = ep.pid WHERE ep.pid IS NULL and e.id=2 ORDER BY e.id; now whats the functional difference between this SELECT lastname, firstname, title, event FROM people p LEFT JOIN epeople ep on p.id = ep.pid right join events e on ep.eid = e.id WHERE ep.pid IS NULL and e.id = 2 ORDER BY ep.eid; and this SELECT lastname, firstname, title, event FROM people p LEFT JOIN epeople ep on p.id = ep.pid right join events e on ep.eid = e.id WHERE ep.pid IS NULL and ep.eid=2 ORDER BY e.id; as written the difference is in the and statements but in my result set ep.eid == e.id == 2 so why can't you use the second statement interchangably with the first? thanks for any insights, jb - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Secure Replication
Ivan Hoo wrote: how do you secure the replication link between the master and the slave using SSH. i understand that you can do that over mysql client and its server. pls enlighten me coz i m looking high and low for a solution on this issue. On the slave: ssh --local-forward(?) 3307:localhost:3306 foreignhost ...then set up the info for master (on the slave) to be localhost:3307. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: join from this subselect
How about: select people.id ... from people left join epeople on epeople.pid=people.id and epeople.eid=2 where epeople.pid is null; The left join gives you: (1) rows for people who attended event 2, with epeople columns populated (2) rows for people who did not attend event 2, with nulls in epeople columns Then the where restricts to (2). More generally, select ... from L left join R on CONDITION gives you: (1) the result of the inner join, select ... from L, R where CONDITION (2) the rows from L that were not used in (1), with nulls for the R columns Then, you can apply an additional WHERE clause to that. hth Subject: join from this subselect To: [EMAIL PROTECTED] From: Josh L Bernardini [EMAIL PROTECTED] Date: Tue, 21 Jan 2003 11:51:13 -0800 I can't come up with the join syntax to mimic this subselect query to list people not attending an event (*epeople.eid == event.id): select people.id as pid, concat(lastname, , , firstname) as name from people where people.id not in (select epeople.pid from epeople left join people on epeople.pid=people.id where epeople.eid=2); Thought it would be: [...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL for Netware
Hi Guys, The recent launch of MySQL for Netware is ideal for our application ... We had to do some tests before switching to this version. We installed MySQL V4 on Netware. Have done speed-comparison tests on MySQL v/s Microsoft. For some reason; if we query a record on a non-indexed table, the access time is much slower on Netware than on Windows. Netware = 1.22sec MS = 025sec Is any Netware Settings neccessary with regard to running MySQL? Will appreciate any help ... Thank you! Rupert Open Sesame!: sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrading
Greets, Newbie here. I was trying to upgrade my mysql, so what I did was stop mysql daemon, recompile it with the new package, and then start mysql again, however it doesn't seem to update the version of it. Any help would be appreciated. thanks for your time. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't seem to use a different datadir....
If you leave the parameter innodb_data_home_dir empty, you can specify absolute file paths in the parameter innodb_data_file_path: innodb_data_home_dir = innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend See http://www.mysql.com/doc/en/InnoDB_start.html Harmen. Quoting [EMAIL PROTECTED]: Hi. Anyone know the trick to successfully using a different directory for database files? I've tried changing my.cnf and also using the command line option. When I run mysqld_safe it says it's using the new directory but then writes mysqld ended and quits. Mysql never comes up. I changed owner and group of the new directory and files to be mysql. What can be going wrong? It just doesn't seem to work! Thanks!! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't seem to use a different datadir....
Well, what does the log say? You did copy the mysql database into the new directory? [EMAIL PROTECTED] wrote: Hi. Anyone know the trick to successfully using a different directory for database files? I've tried changing my.cnf and also using the command line option. When I run mysqld_safe it says it's using the new directory but then writes mysqld ended and quits. Mysql never comes up. I changed owner and group of the new directory and files to be mysql. What can be going wrong? It just doesn't seem to work! Thanks!! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is the upper limit?
Wouldn't that be greater than the default maximum packet length of 1M?
Octavian Rasnita wrote:
Hi all,
I've tried the repeat function for testing some tables, but it cannot
create very big strings.
Do you know which is its upper limit?
I've tried the following query:
mysql select length(repeat('abracadabra', 10));
+---+
| length(repeat('abracadabra', 10)) |
+---+
| NULL |
+---+
1 row in set (0.00 sec)
If I use smaller numbers, it works.
Thank you.
Teddy,
Teddy's Center: http://teddy.fcc.ro/
Email: [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can't seem to use a different datadir....
If you haven't done this: run mysql_install_db to create the privilege tables db, host, user in mysql directory below the new directory. The script 'll create the test directories too. Regards Salam [EMAIL PROTECTED] wrote: Hi. Anyone know the trick to successfully using a different directory for database files? I've tried changing my.cnf and also using the command line option. When I run mysqld_safe it says it's using the new directory but then writes mysqld ended and quits. Mysql never comes up. I changed owner and group of the new directory and files to be mysql. What can be going wrong? It just doesn't seem to work! Thanks!! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
remote database
I am trying to remotely connect to a MySQL database but I'm having problems so I tried this on the same machine as the database. It doesn't work when I give it -h but it works without. # mysql -h 10.0.0.5 -D visitor ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) but # mysql -D visitor works. -- Sent from Mozilla and GNU/Linux. Powered by an AMD processor. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
On Wed, Jan 22, 2003 at 02:21:34PM -0500, Michael T. Babcock wrote: Steven Roussey wrote: Might also look at: vmstat 1 `vmstat 1` is my favorite instant-info server debugging tool. I wouldn't mind the same program for MySQL (where's that mytop author anyhow? j/k) Hmm. mytop 1.4 will have a feature that may help you. It'll sorta like a vmstat the watches the output of SHOW STATUS, mostly the Com_* counters. You'll be able to get the data refreshed at whatever rate you like, just like vmstat. Initially, I was going to do it as a full-screen sort of display, but now that you mention it I think a vmstat-style let-it-scroll-by version might be helpful. Maybe I could rig it up so that if you call it as mysqlstat (via a symlink) it'd automatically start in that mode. Hmm. Thoughts? Now, I just have to remember to push out mytop 1.3 so that folks can shake out any new bugs I've introduced... Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 4 days, processed 118,983,374 queries (342/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation woes
Not being able to solve my prior problem (Error 2002) I went ahead and removed mysql and reinstalled it. I'm running OpenBSD 3.2 Here is how it went: point# pkg_add mysql-server-3.23.49.tgz === Creating mysql group for MySQL === Creating mysql user for MySQL useradd: Warning: home directory `/nonexistent' doesn't exist, and -m was not specified Sorry, the host 'point' could not be looked up. Please configure the 'hostname' command to return a correct hostname. The name of this machine is point and the domain is spotz.bb. This is not a real domain I'm using. Then I try to start it: point# ./safe_mysqld [1] 11688 point# touch: /var/mysql/point.err: No such file or directory chown: /var/mysql/point.err: No such file or directory Starting mysqld daemon with databases from /var/mysql ./safe_mysqld[218]: cannot create /var/mysql/point.err: No such file or directory ./safe_mysqld[234]: cannot create /var/mysql/point.err: No such file or directory tee: /var/mysql/point.err: No such file or directory 030122 13:59:57 mysqld ended tee: /var/mysql/point.err: No such file or directory Why doesn't mysql create these directories when installing? What do I need to do based upon the output above? I may have missed some steps so be gentle please. Thanks, Anthony. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Secure Replication
On Wed, Jan 22, 2003 at 02:27:40PM -0500, Michael T. Babcock wrote: Ivan Hoo wrote: how do you secure the replication link between the master and the slave using SSH. i understand that you can do that over mysql client and its server. pls enlighten me coz i m looking high and low for a solution on this issue. On the slave: ssh --local-forward(?) 3307:localhost:3306 foreignhost ...then set up the info for master (on the slave) to be localhost:3307. You mean 127.0.0.1:3307, right? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 4 days, processed 119,323,583 queries (341/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Install on WIN2k
Hello, I have been running mysql on windows 2000 for about 8 months. Now I have to install it on a new win2k box. I get everything installed and go to get into mysql and I am getting this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Any and all help appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.54a crash when connect through TCP/IP, Help pls!
I installed MySQL 3.23.54a from source on a AIX 5.1 powerpc. It works fine when I tried to connect as localhost through unix sock. However, everytime I tried to connect using TCP/IP (./bin/mysql -u root -h myhost -p), the Mysql server crashed and restarted (running safe_mysqld). The system gave a segmentation error. Even when I tried to 'telnet myhost 3306', the port is open, but it caused the mysql server crashed. I also tried the binary version of mysql server (compiled for AIX 4.0...), it has the same problem. Any suggestion? Here is my configurations: setenv CC /usr/local/bin/gcc -pipe -Wa,-many setenv CXX /usr/local/bin/g++ -pipe -Wa,-many setenv CXXFLAGS -felide-constructors -fno-exceptions -fno-rtti ./configure --enable-static --disable-shared \ --prefix=/usr/local/mysql \ --localstatedir=/newdisk/mysql/data \ --sbindir=/usr/local/sbin \ --libexecdir=/usr/local/sbin \ --enable-thread-safe-client \ --enable-large-files \ --with-mysqld-user=mysql \ --with-innodb Thanks! Yong - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
Hmm. mytop 1.4 will have a feature that may help you 1.4? I'm still on 1.0. Guess I'm behind the curve. Jeremy, can you add something to protect against binary data coming across in a query and messing up the terminal window? Leave it running a while and all of a sudden it is a big mess. Yikes! It'll sorta like a vmstat the watches the output of SHOW STATUS, mostly the Com_* counters Poor man's version: watch mysqladmin extended-status Sincerely, Steven Roussey http://Network54.com/ query,sql,stuff,cool - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Left join returns records it shouldn't
The outer join part of the silly query should return (1) All pairs a,b for which (a.zone = b.zone ... and b.leftside != '') (2) A row for each a that is not used in (1), with null for the columns of b The where clause then narrows these down to elements of (2) with a.type = 'MX' No reason there shouldn't be any--and there are some. Your query does not ask for records with a.type='A' and a.type='MX', it asks for records of the left join with a.type='MX'. In fact, for your real query, without the MX part, you should get a result row at least for each row of dns_rec for which leftside != ''. Sounds like you should get a lot of them, and it should take a while. More generally, select ... from L left join R on CONDITION gives you: (1) the result of the inner join, select ... from L, R where CONDITION (2) the rows from L that were not used in (1), with nulls for the R columns Then, you can apply an additional WHERE clause to that. Date: Wed, 22 Jan 2003 12:28:48 -0600 (CST) From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Left join returns records it shouldn't Description: The following silly query (silly because it should return no records since it is impossible for a.type to have two different values simultaneously), returns two records when it should return none with the enclosed test data. select a.*, b.* from dns_rec a left join dns_rec b on (a.zone = b.zone and a.rightside = b.rightside and a.type = 'A' and b.type = 'A' and a.leftside = '' and b.leftside != '') where b.zone is null and a.type = 'MX' ; [...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Low Impact Security Exposure in MySQL Monitor
Greetings, I have discovered an exposure in the history recall functionality of MySQL Monitor. When a user uses MySQL monitor authenticated as the database root user to issue commands, such as changing user passwords or database table creation, that history can be recalled by a database user of lesser privileges. This exposes passwords and table structure, which may not want to be exposed. This happens because the MySQL Monitor history is stored in the invoking Unix user's home directory. Likewise, that Unix user can simply cat the history file (cat .mysql_history) and see the commands, like this: aaron:~ $ cat .mysql_history select * from user; select Host,User,Password from user; update user set Password=password(secret1) where User=root; select Host,User,Password from user; Tying the MySQL Monitor history to a particular Unix user may be thought to be secure, but it is not. Many companies use specific ID's to perform specific work. In these examples, the user will log in under their own ID, and su over to the surrogate ID to perform a task which that ID is allowed to conduct. If a users decides to perform MySQL administration (as MySQL root user) while logged into that surrogate ID, anyone else who has access to that surrogate ID now has access to all of the MySQL commands issued by that Unix user. Granted, this exposure only manifests itself in situations where previously existing security practices are poor, however, why should MySQL reveal information needlessly? The history should be tied to the MySQL user who has authenticated to MySQL, not the Unix user account accessing mysql. I wish I could say nobody engaged in the above practice, but, I know better. I tried this on MySQL-Max 3.23.54 and 3.23.54a under Slackware 8.1 and SuSE 8.1. It happens only when logging into the database server itself. Using mysql and connecting to a remote MySQL server puts the history into the local Unix user's .mysql_history file. I rate this as a low impact security exposure because it is a downstream consequence of improper security practices. Thank you for your consideration. Regards, George Toft - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
help w join query
Hello, I have these two tables, CREATE TABLE `items` ( `name` varchar(100) NOT NULL default '', `img` varchar(50) NOT NULL default '', `year` year(4) NOT NULL default '', `item_id` smallint(5) unsigned NOT NULL auto_increment, PRIMARY KEY (`item_id`) ) TYPE=MyISAM; CREATE TABLE `units` ( `name` varchar(100) NOT NULL default '', `item1_id` smallint(5) default NULL, `item2_id` smallint(6) default NULL, `item3_id` smallint(5) default NULL, `item4_id` smallint(5) default NULL, `item5_id` smallint(5) default NULL, `unit_id` smallint(5) unsigned NOT NULL auto_increment, PRIMARY KEY (`unit_id`) ) TYPE=MyISAM; I'm trying to write a query that will pull all the items from the items table where items.item_id does not appear in units.item1_id and units.item2_id and so on. So far I have not even come close to getting the results I would like. Thanks in advance for any help, Mike sql,mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transaction Support in mysql13.23.54
hi, i have installed mysql3.23.54 on windows and have followed all instructions as required for using mysqld-max to have transaction support. i have created a table using TYPE=INNODB, but cannot use rollback on it. the error i get in my jsp is transactions not supported. please advice me on how i can use commit - rollback on mysql database tables. I am connecting to the mysql database thru my java web-app. thank you in advance. regards, nirmal (23 jan 03) __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: remote database
I think it's allowing 'root@localhost' but not '[EMAIL PROTECTED]'. Select * from user (in mysql db) will show you all the access privileges. Add '[EMAIL PROTECTED]' to that as a user. Bhavin. - Original Message - From: Bob Lockie [EMAIL PROTECTED] To: MySQL Mailing List [EMAIL PROTECTED] Sent: Wednesday, January 22, 2003 5:07 PM Subject: remote database I am trying to remotely connect to a MySQL database but I'm having problems so I tried this on the same machine as the database. It doesn't work when I give it -h but it works without. # mysql -h 10.0.0.5 -D visitor ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) but # mysql -D visitor works. -- Sent from Mozilla and GNU/Linux. Powered by an AMD processor. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Designing Categories
Check this and ask more if needed http://www.webreference.com/perl/xhoo/php1/ also might want to check out perlHoo based on your language preference http://www.webreference.com/perl/xhoo/ olinux --- Will K. [EMAIL PROTECTED] wrote: Greets Folks, I cant tell if this is a dumb question or not, so please bear with me if it is... But here goes... You know how when you visit a website and they have Categories, like in an online store or maybe an auction sites... or maybe categories would be used in a bulletin board system for different types of discussions, or art galleries for different types of galleries... ? I am wondering how the backend logic structures looks to that tablewise... I can handle SQL for relational databasing, but I am trying to understand this more from a design point of view... Thanks, Will _ The new MSN 8: smart spam protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
On Wed, Jan 22, 2003 at 06:06:51PM -0800, Steven Roussey wrote: Hmm. mytop 1.4 will have a feature that may help you 1.4? I'm still on 1.0. Guess I'm behind the curve. Well, 1.3 is sitting in my CVS tree. I just need to update the docs and changelog. I already have some patches pending for 1.4... Heh. Jeremy, can you add something to protect against binary data coming across in a query and messing up the terminal window? Leave it running a while and all of a sudden it is a big mess. Yikes! Oh, good idea. I'd never run into that, but I can see how that'd be a prolbem. I'll make sure there's a binary filtering option added. It'll sorta like a vmstat the watches the output of SHOW STATUS, mostly the Com_* counters Poor man's version: watch mysqladmin extended-status One of my favorite Borg quotes is: Crude but effective. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 4 days, processed 123,404,209 queries (335/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: remote database
mysql -h10.0.0.5 -u[username] -Dvisitor -p[password] should get you in - hcir On Wednesday, January 22, 2003, at 01:07 PM, Bob Lockie wrote: I am trying to remotely connect to a MySQL database but I'm having problems so I tried this on the same machine as the database. It doesn't work when I give it -h but it works without. # mysql -h 10.0.0.5 -D visitor ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) but # mysql -D visitor works. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: remote database
On Wednesday, January 22, 2003, at 01:07 PM, Bob Lockie wrote: I am trying to remotely connect to a MySQL database but I'm having problems so I tried this on the same machine as the database. Thanks, to all who replied. I have it working. -- Sent from Mozilla and GNU/Linux. Powered by an AMD processor. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqlstat (WAS Re: Optimizing Ext3 for MySQL)
One of my favorite Borg quotes is: Crude but effective. :-) I like that. ;) This got me thinking again about a feature I'd like to see in mysqld. I'd like to add something like SQL_STATISTICS to SELECT/UPDATE/INSERT statements. The idea would be that if this keyword was used, then mysqld would calculate additional statistics for that query (which you could get later with SHOW SQL_STATISTICS or something). The statistics would include actual numbers from the processing of the query: # of index records read # of data section records read # bytes requested from file system (read write) [this being the most important] # CPU time # Disk read, write, and wait times etc... Such information would be extremely valuable. For example, it wasn't until I put fulltext on separate server that I would discover what a disk read hog it was: Main server: 3000 q/s Disk read: 540 KB/sLoad: 1 FTS server: 2 q/s Disk read: 7600 KB/sLoad: 12 (taken from http://marc.theaimsgroup.com/?l=mysqlm=104042853614294w=2) Sadly, I will have to write my own FTS system soon. I'll be using mysql to do it, so hopefully Serg will be able to port whatever ideas I settle on back inside the mysqld engine (assuming they worth porting!!) I'll start by moving the ideas of the cosine vector search from C to SQL. Then I can try a lot of different things without constant re-compiling. I'll also get the advantage of having it work with several languages on a record basis, rather than a table basis... Now if only I had a paying job, I could focus on it and get it done quicker... Sincerely, Steven Roussey http://Network54.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Aborted connections.../mysqladmin flush-hosts
Dear sql query, Since updating from 4.0.7 to 4.0.9 I have noticed mysqld having problems with connections from our webserver, eventually denying access until I issue a mysqladmin flush-hosts. Shutting down the server and restarting tends to work for a longer period of time. I can't say for sure if the issue lies with the version change or just coincident with it. Can anyone confirm this behavior? Sincerely, Steven Roussey http://Network54.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.0 + PHP 4
Anyone out there have anything to share as to using PHP4+MySQL4. I've been hearing good things on the MySQL list about v4.0 and I'm thinking of upgrading, since I run a rather small server. Thanks! --Joe ps. PHP list responders please respond directly. I'm not currently subscribed. -- Joe Stump [EMAIL PROTECTED] http://www.joestump.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP] MySQL 4.0 + PHP 4
Anyone out there have anything to share as to using PHP4+MySQL4. I've been hearing good things on the MySQL list about v4.0 and I'm thinking of upgrading, since I run a rather small server. There's really no change from the PHP side. PHP just sends the data and receives something back The only change in PHP is that mysql_connect() now supports additional flags to connect over SSL. ---John Holmes... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP] MySQL 4.0 + PHP 4
On Wednesday 22 January 2003 18:52, 1LT John W. Holmes wrote: There's really no change from the PHP side. PHP just sends the data and receives something back The only change in PHP is that mysql_connect() now supports additional flags to connect over SSL. Nope, I only forgot to remove a constant. SSL support will be available in PHP 5 (new mysql extension). Regards Georg - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: [PHP] MySQL 4.0 + PHP 4
On Wednesday 22 January 2003 18:52, 1LT John W. Holmes wrote: There's really no change from the PHP side. PHP just sends the data and receives something back The only change in PHP is that mysql_connect() now supports additional flags to connect over SSL. Nope, I only forgot to remove a constant. SSL support will be available in PHP 5 (new mysql extension). Regards Georg Ah, man... Now you made my article in the last issue of PHP|Architect incorrect! :) Oh well, I guess we'll have to wait. ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
