Complex Query: Best way to write it?
| | MUL | 0 | | | country_region_id | mediumint(8) unsigned | | MUL | 0 | | | profile_city | varchar(80) | | | 0 | | | profile_heritage_id | tinyint(1)| | MUL | 0 | | | profile_faith_id | tinyint(1)| | MUL | 0 | | | profile_smoke_id | tinyint(1)| | MUL | 0 | | | profile_drink_id | tinyint(1)| | MUL | 0 | | | profile_pet_id| tinyint(1)| | MUL | 0 | | | profile_children_id | tinyint(1)| | MUL | 0 | | | profile_allow_rating | tinyint(1)| | | 0 | | +---+---+--+-+-+---+ mysql desc test_country; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | country_id | tinyint(3) unsigned | | PRI | NULL| auto_increment | | country_code | char(2) | | UNI | || | country_name | varchar(50) | | UNI | || +--+-+--+-+-++ mysql desc test_country_region; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | country_region_id | mediumint(8) unsigned | | PRI | NULL| auto_increment | | country_region_code | char(2) | | MUL | | | | country_region_name | varchar(100) | | | | | +-+---+--+-+-++ mysql desc test_country_link; +---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | country_id| tinyint(3) unsigned | | MUL | 0 | | | country_region_id | mediumint(8) unsigned | | MUL | 0 | | +---+---+--+-+-+---+ mysql desc test_profile_orientation; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | profile_orientation_id | tinyint(2) | | PRI | NULL| auto_increment | | profile_orientation_name | varchar(15) | | | | | +--+-+--+-+-++ mysql desc test_profile_orientation_link; ++---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-+---+ | uid| mediumint(8) unsigned | | MUL | 0 | | | profile_orientation_id | tinyint(2)| | MUL | 0 | | ++---+--+-+-+---+ mysql desc test_profile_gender; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | profile_gender_id | tinyint(1) | | PRI | NULL| auto_increment | | profile_gender_name | varchar(15) | | | || +-+-+--+-+-++ -- Keith Bussey Director of Operations - IwantU, Inc. ICQ: 38373031 Tel: +506-280-2284 ext. 108 Fax: +506-253-2143 URL: http://www.IwantU.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN types
Sorry, here are the EXPLAINS of a similar case, where what I did was switch the first two tables in the join, and make it LEFT not INNER (note: the query may look a little odd, as I took out some of the fields I was selecting, etc...to make it shorter ;p) mysql EXPLAIN SELECT t.profile_alias FROM iwantu_tables_2 t INNER JOIN iwantu_profile_2_1 p USING(uid) INNER JOIN iwantu_desired_2_1 d USING (uid) INNER JOIN profile_descriptions s ON (p.description_id = s.description_id) WHERE (p.profile_status IN (1,2)) ORDER BY p.profile_updated DESC LIMIT 30; +---+++-+-+ --+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra| +---+++-+-+ --+-+--+ | d | index | idx_uid| idx_uid | 3 | NULL | 1002592 | Using index; Using temporary; Using filesort | | p | ref| idx_profile_status,uid | uid | 3 | iwantu_new.d.uid | 1 | Using where | | t | eq_ref | PRIMARY| PRIMARY | 3 | iwantu_new.p.uid | 1 | | | s | eq_ref | PRIMARY| PRIMARY | 4 | p.description_id | 1 | Using index | +---+++-+-+ --+-+--+ 4 rows in set (0.00 sec) mysql EXPLAIN SELECT t.profile_alias FROM iwantu_profile_2_1 p LEFT JOIN iwantu_tables_2 t USING(uid) INNER JOIN iwantu_desired_2_1 d USING (uid) INNER JOIN profile_descriptions s ON (p.description_id = s.description_id) WHERE (p.profile_status IN (1,2)) ORDER BY p.profile_updated DESC LIMIT 30; +---++++-+- -++-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++++-+- -++-+ | p | range | idx_profile_status | idx_profile_status | 4 | NULL | 684324 | Using where; Using filesort | | t | eq_ref | PRIMARY| PRIMARY| 3 | iwantu_new.p.uid | 1 | | | d | ref| idx_uid| idx_uid| 3 | iwantu_new.t.uid | 1 | Using index | | s | eq_ref | PRIMARY| PRIMARY| 4 | p.description_id | 1 | Using index | +---++++-+- -++-+ 4 rows in set (0.00 sec) Also, I'm running MySQL 4.0.13-standard, STRAIGHT JOIN doesnt seem to exist for me ;p -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager +50 6 280 2284 ext.108 Quoting Matt W [EMAIL PROTECTED]: Hi Keith, I would assume it's because LEFT JOIN forced a change in the join order (in EXPLAIN). Does using STRAIGHT JOIN give the same result? So your query was this? SELECT a.field FROM table1 a LEFT JOIN table2 b USING (field2) ORDER BY b.field3 DESC If table1 is read first (which it should be), then I don't see how there's no temp table/filesort because the column(s) you're ordering by don't come from the first used table. Oh yeah, and is there an index on field2 in both tables? It'd be better to see the EXPLAIN output for the different queries. :-) It could also be an optimizer bug. What version of MySQL are you using? Matt - Original Message - From: Keith Bussey Sent: Monday, January 12, 2004 4:49 PM Subject: JOIN types Hey all, I've read the pages in the MySQL manual that explain the types of JOINs many times, but still think I'm missing something. I usually was always using INNER JOIN in all my join queries, and in a few cases LEFT JOIN (in cases I wanted the rows in one table that were not in the other one). I've started to discover, with the help of EXPLAIN, that the join type can seriously affect performance. For example, I had a query such as this: SELECT a.field FROM table1 a INNER JOIN table2 b USING (field2) ORDER BY b.field3 DESC It was using both filesort and a temporary table (in EXPLAIN) and took about 4.50 seconds to run. I switched the order of the tables in the join, putting table2 first, and nothing changed in my EXPLAIN. I then changed the join to LEFT JOIN, and suddenly I had no more filesort or temporary table, and the query took
JOIN types
Hey all, I've read the pages in the MySQL manual that explain the types of JOINs many times, but still think I'm missing something. I usually was always using INNER JOIN in all my join queries, and in a few cases LEFT JOIN (in cases I wanted the rows in one table that were not in the other one). I've started to discover, with the help of EXPLAIN, that the join type can seriously affect performance. For example, I had a query such as this: SELECT a.field FROM table1 a INNER JOIN table2 b USING (field2) ORDER BY b.field3 DESC It was using both filesort and a temporary table (in EXPLAIN) and took about 4.50 seconds to run. I switched the order of the tables in the join, putting table2 first, and nothing changed in my EXPLAIN. I then changed the join to LEFT JOIN, and suddenly I had no more filesort or temporary table, and the query took 0.05 seconds ! I compared that I got the same rows in my result both ways, and indeed I did. I'm positive I can improve many of my queries this way, but feel I need to understand the JOINs better. If someone can point me to a guide on this someplace, other than the mysql manual (as Ive already read it few times but it didnt explain their differences and uses very well), I'd greatly appreciate it =) Or, if you simply want to give the explanation yourself, that's fine too. Thanks, -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager +50 6 280 2284 ext.108 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: JOIN types
MyQuoting [EMAIL PROTECTED]: I'm suprised by your result too. Did you have an index on the join column? If not that might explain it. One place you might turn to learn more about how mysql optimizes joins is internals.texi. This file describes a lot of the internal workings of mysql and is found in the bk doctree and is also distributed in recent source distributions of mysql. I know its in the 5.0 source (in /Docs), but I don't think its in 4.1.1. My join column was indexed in both tables. I am currently running 4.0.13, however I cant locate the file internals.texi (I assume I'll probably need to download the source package to find it...). I will look for that, thanks for the tip. By the way, if you are using 5.0, do you think it's safe enough in Alpha to try in a production environment ? I like some of its added features over the 4.0 series... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using UNIX_TIMESTAMP() as default field value ?
Currently I am using UNIX_TIMESTAMP() in my INSERTS, just the idea came to me that maybe I could use a MySQL function as a default for a field. Too bad MySQL doesn't allow it though.. Thanks for your reply though =) Oh and yeah, I knew the int(10) was for the display length ;p -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager +50 6 280 2284 ext.108 Quoting Michael Stassen [EMAIL PROTECTED]: Sorry, my reply was incomplete, considering your question. You want Unix timestamp format, and you want it to default to the current time. Unfortunately, you can't do both. You can use a TIMESTAMP column, which will default to NOW (the default you want), or you can use an INT column and assign UNIX_TIMESTAMP() to it in your INSERT statement (the format you want). Note that the 10 in INT(10) is the display size, not the size of the integer. You probably knew that, but just in case... Otherwise, int(10) unsigned not null default 0 should be fine. Michael Keith Bussey wrote: Quoting Michael Stassen [EMAIL PROTECTED]: Defaults must be constants, not functions. Ahh. Thanks for the info. You want a TIMESTAMP column, not INT. See http://www.mysql.com/doc/en/DATETIME.html. I want to store my time in unix timestamp format though, not in a mmddyy type. Michael Keith Bussey wrote: Hi all, I was wondering if it is possible to have the default value of an integer field be the current UNIX timestamp ? MySQL doesn't allow me to use not null default UNIX_TIMESTAMP() . If anyone knows a way of achieving this, it would be great =) Also, I currently use int(10) unsigned not null default 0 for my unix timestamp fields, is there perhaps a more efficient field type I should be using? Thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL vs. MaxDB
Hola, I was wondering if anyone could point me to any articles or URLs that could give me an idea of the differences between MaxDB and MySQL, specifically if one were to use MaxDB instead of MySQL, what disadvantages would come with the advantages (which to me are basically some of the features MySQL doesnt have yet.) ?? Off the top of my head, I assume MySQL would be faster than MaxDB for web- based applications/sites, but thats just an assumption I'd like to find some facts. Thanks, -- Keith Bussey Mana Internet Solutions, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table is full error
Hey all, I have a table with the following size: -rw-rw 1 mysqlmysql4.0G Aug 31 01:01 email_body.MYD Thus if I try and insert one more row I get the error: ERROR 1114: The table 'email_body' is full Now I'm wondering why this is and if there is any options ot increase max table size ? I have other databases on other servers with much larger tables without problem. I'm running MySQL-standard 4.0.13 on FreeBSD 4.8-Stable Thanks! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
I'm not sure it has to do with the number of rows, but in any case this is what happened: mysql select count(*) from email_body; +--+ | count(*) | +--+ | 208853 | +--+ 1 row in set (0.00 sec) mysql alter table email_body MAX_ROWS=70; Query OK, 315 rows affected (0.23 sec) Records: 315 Duplicates: 0 Warnings: 0 mysql select count(*) from email_body; +--+ | count(*) | +--+ | 319 | +--+ 1 row in set (0.00 sec) Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Keith C. Ivey [EMAIL PROTECTED]: On 4 Sep 2003 at 10:13, Keith Bussey wrote: -rw-rw 1 mysqlmysql4.0G Aug 31 01:01 email_body.MYD Thus if I try and insert one more row I get the error: ERROR 1114: The table 'email_body' is full By default, MyISAM tables use 4-byte pointers to indicate positions in the data file. So if your data file gets bigger than 4 GB (or larger for fixed-length records, but that's not what you have), you get that error: http://www.mysql.com/doc/en/Full_table.html Figure out how many records you're likely to need and do ALTER TABLE email_body MAX_ROWS=whatever; -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
I do have another Database on the same machine which has a table that is a lot bigger (about 9 GIGs) and it wasn't created with any of the special table options suggested by Keith C. Ivey below. The difference is this table has many more fields, while the email_body one (the one with the problem) as only 2: an ID autonumber field, and a text field. Perhaps there is some bug/limitation in Mysql whereby a field can only have so much size ?? -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Colbey [EMAIL PROTECTED]: Most likely it's the 4GB OS limitation... My suggestion is to create a new table using mysql's built in raid option... span the table over multiple files to allow of much larger table growth... migrate all the rows over to the new spanned table.. On Thu, 4 Sep 2003, Keith C. Ivey wrote: On 4 Sep 2003 at 10:53, Keith Bussey wrote: Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) Yikes! Are you running into any file system limits? Have you dealt with files larger than 4 GB on that server before with no problems? If not, you may have run into a MySQL bug of some sort. An alternative way to get the table to have 5-byte pointers would be to create the new table (same CREATE TABLE query as for the old structure, except add MAX_ROWS=70 AVG_ROW_LENGTH=2 to the end) and then copy all the records into it: INSERT INTO email_body_NEW SELECT * FROM email_body; Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't matter, as long as their product is between 2**32 and 2**40 - 1. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
Quoting Colbey [EMAIL PROTECTED]: Hrm.. interesting.. I know I've personally hit the 4gb limit before and had to do a fix for it.. but never hit an internal tablesize limitation.. You didn't mention what version of mysql your running... I mentioned in my original email ;p.4.0.13-standard Also if the table has alot of columns and 1 of more of those columns has alot of data, perhaps considering looking at normalizing the table into a few? Could potentially speed up operations.. The table with a lot of columns (by alot I mean like 25 or so) is running great, it just has millions of records...but there are no text fields in it, there are fields that link to tables with id-text_field. The problem is one of these tables (2 column table) with ID - text_field. Not the easy fix your looking for but a thought... Try running: SHOW TABLE STATUS FROM database LIKE 'table_name'; It will check the max index/data sizes mysql is configured/allocated for... Running that shows me the following: mysql SHOW TABLE STATUS FROM email_tracking LIKE 'email_body_old'; ++++++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | ++++++-+-+--+---++-+-+-++-+ | email_body_old | MyISAM | Dynamic| 208853 | 20564 | 4294967292 | 4294967295 | 1820672 | 0 | 450438 | 2002-10-21 11:58:24 | 2003-08-31 01:01:41 | 2002-11-30 01:41:00 || | ++++++-+-+--+---++-+-+-++-+ 1 row in set (0.00 sec) On Thu, 4 Sep 2003, Keith Bussey wrote: I do have another Database on the same machine which has a table that is a lot bigger (about 9 GIGs) and it wasn't created with any of the special table options suggested by Keith C. Ivey below. The difference is this table has many more fields, while the email_body one (the one with the problem) as only 2: an ID autonumber field, and a text field. Perhaps there is some bug/limitation in Mysql whereby a field can only have so much size ?? -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Colbey [EMAIL PROTECTED]: Most likely it's the 4GB OS limitation... My suggestion is to create a new table using mysql's built in raid option... span the table over multiple files to allow of much larger table growth... migrate all the rows over to the new spanned table.. On Thu, 4 Sep 2003, Keith C. Ivey wrote: On 4 Sep 2003 at 10:53, Keith Bussey wrote: Your suggestion seemed to wipe out my rows ! (s'ok I got a backup ;p) Yikes! Are you running into any file system limits? Have you dealt with files larger than 4 GB on that server before with no problems? If not, you may have run into a MySQL bug of some sort. An alternative way to get the table to have 5-byte pointers would be to create the new table (same CREATE TABLE query as for the old structure, except add MAX_ROWS=70 AVG_ROW_LENGTH=2 to the end) and then copy all the records into it: INSERT INTO email_body_NEW SELECT * FROM email_body; Actually, the exact figures for MAX_ROWS and AVG_ROW_LENGTH shouldn't matter, as long as their product is between 2**32 and 2**40 - 1. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is full error
Quoting Keith C. Ivey [EMAIL PROTECTED]: On 4 Sep 2003 at 15:31, Keith Bussey wrote: I do have another Database on the same machine which has a table that is a lot bigger (about 9 GIGs) and it wasn't created with any of the special table options suggested by Keith C. Ivey below. Does the other table have fixed-length records (no VARCHAR, TEXT, or BLOB columns)? If so, the numbers for the pointers are in records, not bytes, so the table can reach 2**32 - 1 records before it's full. You can see what the maximum data length is with SHOW TABLE STATUS. For teh email_boy table, do you have any index other than the one for the primary key? The .MYI file isn't getting big, is it? The MYI for that table is 1.7M, and no the text field isn't indexed Other table has all fixed-length records except 2 varchar fields Thus, if I understand right, then because the field is text it uses more pointers than a larger table that has no text fields (in my case atleast) ? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixing Replication
Hi all, I was wodnering if someone has a better way of re-sync'ing a slave when replication fails. Here's my setup: 1 - MASTER 5 - SLAVES Now sometimes, one of the slaves will fail. Usually when it's the sql that stops running, I can fix it and get it back. However sometimes, I also get the master binlog corruption error and haven't found a way to fix that. What I do is: - stop mysql on the master and all the slaves - remove the server-bin files on the master - remove the mysql data dir on each slave - copy the master's datadir to each slave - restart mysql everywhere and everything is fine Now while this method works, it is extremely time-consuming and I was wondering if anyone knows a better solution to only fix the affected slave?? I have tried stopping just the affected slave and one other good slave, taking its datadir and renaming the bin files to the affected server's name. That method doesn't seem to work though. Thanks in advance! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very strange MySQL problem !
Hi, I am having a very weird problem with one of my database servers. Let's take the following setup: 10 load-balanced servers (I'll call them ServerA) 1 admin server (ServerB) 1 database server (DB1) Now ServerA has a function to insert a row into a table on DB1 whenever someone hits certain pages (for stats collection). There are probably about 1000 inserts being done per second. ServerB has a script that runs every 20 minutes, which does a very quick/simple select from DB1, then loops though the results and does updates/deletes on a different database server. Everytime the script on ServerB runs, all attempts to connect to DB1 from ServerA fail. This causes major problems like causing server load on ServerA to rise dramatically. I have checked when the script runs, everything on DB1 is normal. Server load, mysql processes, no locks, etc... I also have it logging the connection failures with mysql_error but mysql_error turns up blank. Can anyone try and help me figure out what's going on here!? Why won't it connect during the period the script runs and why is mysql_error blank? Webservers are FreeBSD 4.8-Stable, DB1 is Redhat 7.3 running MySQL-standard 4.0.13 Thanks!! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very strange MySQL problem !
Quoting [EMAIL PROTECTED]: Are you sure all connection attempts fail? not just insert attempts? Yes, i have it write to my log if the sock is empty (mysql_connect fails)...before it gets to the insert Server B does some updates/deletes... Chances are this causes some table locks, which makes Server A unable to perform it's inserts until Server B releases the lock. ServerB's updates/deletes are on a different database server though (as stated below ;p)...so it shouldn't factor in here at all. On Wed, 13 Aug 2003, Keith Bussey wrote: Hi, I am having a very weird problem with one of my database servers. Let's take the following setup: 10 load-balanced servers (I'll call them ServerA) 1 admin server (ServerB) 1 database server (DB1) Now ServerA has a function to insert a row into a table on DB1 whenever someone hits certain pages (for stats collection). There are probably about 1000 inserts being done per second. ServerB has a script that runs every 20 minutes, which does a very quick/simple select from DB1, then loops though the results and does updates/deletes on a different database server. Everytime the script on ServerB runs, all attempts to connect to DB1 from ServerA fail. This causes major problems like causing server load on ServerA to rise dramatically. I have checked when the script runs, everything on DB1 is normal. Server load, mysql processes, no locks, etc... I also have it logging the connection failures with mysql_error but mysql_error turns up blank. Can anyone try and help me figure out what's going on here!? Why won't it connect during the period the script runs and why is mysql_error blank? Webservers are FreeBSD 4.8-Stable, DB1 is Redhat 7.3 running MySQL-standard 4.0.13 Thanks!! -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very strange MySQL problem !
That's the problem, when the script runs, I can connect from serverA fine!...but seems the webpages can't ;/ -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting [EMAIL PROTECTED]: On Wed, 13 Aug 2003, Keith Bussey wrote: Are you sure all connection attempts fail? not just insert attempts? Yes, i have it write to my log if the sock is empty (mysql_connect fails)...before it gets to the insert But you mention mysql reports no connection errors... perhaps wait for an update, and from ServerA, use mysql client: mysql --user=asdf --password=asdf --host=DB1 see if you can get online.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost connection to MySQL server during query
Hello all, After changing RAM and the ethernet card of my DB server, the problem of getting frequent: Database error (error code 102) Could not connect to database server (Lost connection to MySQL server during query) Still occurs ;/ I've tried to eliminate all factors (like multiple servers, load-balancers, replication etc) so I have wrote a php script that only does the following: - opens db connection - does a simple select - closes db connection If I refresh this page over and over, every 6-7 times I get the above error. Anyone have any further solutions, ?? I've tried the software (re-installing mysql, upgrading, etc) and tried the hardware (RAM, ethernet card). Nothing seems to help. The DB server is Linux Redhat 7.3 with the 2.4.18-3smp kernel. I am running MySQL standard 4.0.13 Thanks -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT problem with mysql processes
Neither of these two machines send the request. I have 12 webservers, which send the requests. I have 5 database servers, 1 master(for updates/inserts/deletes) and 4 slaves(for selects). Machine A below was the current Master DB server, however it is old and was being replaced by machine B -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Nils Valentin [EMAIL PROTECTED]: Hi Keith, soory now I got confused. Which machine is sending the requests (client) and which machine is the mysql server (server). Do you use php or something like this (webinterface) on the client ? If yes, than my suggestions below apply. If no than I we will have to think the next step. Best regards Nils Valentin Tokyo/Japan 2003年 6月 27日 金曜日 11:29、Keith Bussey さんは書きました: Ok but that's not what I meant. I'll try to explain better. Machine A: IP = 192.168.1.71 Machine B: IP = 192.168.1.79 1) Scripts goto 192.168.1.71 - Everything is OK 2) Changed scripts to 192.168.1.79 - Site works at first, but processes pile up until server is killed 3) Halt machine B 4) Give machine A IP 192.168.1.79 (so now it has 2 IPs) 5) Problem persists even though it's a different machine, site works but then processes pile up and kill machine 6) Change scripts to use 192.168.1.71 again 7) Site works fine What I don't get is why do processes run normally with IP 192.168.1.71, but NOT with IP 192.168.1.79 even when they are on the same machine ? -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Nils Valentin [EMAIL PROTECTED]: Hi Keith, The wrong IP address could only avoid a client to connect to the database server. If you have the TCP/IP address specified in /etc/php.ini or for phpmyadmin in config.inc.php or which ever tool you use than of course it will try to connect to this IP address. Best regards Nils Valentin Tokto/Japan 2003å¹´ 6æ 27æ¥ éææ¥ 11:02ãKeith Bussey ããã¯æ¸ãã¾ãã: Hrmmmeaning if I do id mysql, that information (group etc..) ? I've halted that server and moved everything back to my original server for now, I had too much downtime. I did notice something else interesting though: The old mahcine's IP is 192.168.1.71 New machine's IP is 192.168.1.79 Now that it's halted, instead of changing the IP back to .71 in my pages/scripts I added .79 to the .71 machine so it has both Now that machine experienced the exact same problem. Switching my code to use .71 again however, and no problems. Could somehow there be a problem with the IP address 192.168.1.79?? It seems very strange, however tomorrow I will try putting the .71 on the new machine and see if it works or not -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT problem with mysql processes
No, the slaves replicate fine and I can see their entries in the processlist normally (as their hostname), not as unauthenticated user.. -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Nils Valentin [EMAIL PROTECTED]: Hi Keith, from your last e-mail I understand now finally that we are talking about replication here. Would have been a short cut to mention it in your first e-mail. The failed login attempts couldn't be from your slaves trying to login, but you havent set them up on the master yet ? Just a guess, so. 2003年 6月 27日 金曜日 09:05、Keith Bussey さんは書きました: | 106 | unauthenticated user | 192.168.1.59:1925 | NULL | Connect | NULL | | login NULL | | 115 | unauthenticated user | 192.168.1.53:2041 | NULL | Connect | NULL | | login NULL | | 118 | unauthenticated user | 192.168.1.56:4172 | NULL | Connect | NULL | | login NULL | | 119 | unauthenticated user | 192.168.1.56:4173 | NULL | Connect | NULL | | login NULL Best regards Nils Valentin Tokyo/Japan -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT problem with mysql processes
No, replication is fine I can see those processes as they should be. The problem processes all come from the webservers' requests. -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Dominicus Donny [EMAIL PROTECTED]: If it is the replication problem...:: Check the replication account on the master server: ...[EMAIL PROTECTED] identified by ... ...[EMAIL PROTECTED] identified by ... ...[EMAIL PROTECTED] identified by ... ... ...[EMAIL PROTECTED] identified by ... or perhaps simply set to ...replication_account@% identified by ... make sure the account has access to the specified db.tables, too. Me fail English? That's unpossible ###___Archon___### - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: Keith Bussey [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 27, 2003 1:30 PM Subject: Re: URGENT problem with mysql processes Hi Keith, from your last e-mail I understand now finally that we are talking about replication here. Would have been a short cut to mention it in your first e-mail. The failed login attempts couldn't be from your slaves trying to login, but you havent set them up on the master yet ? Just a guess, so. 2003年 6月 27日 金曜日 09:05、Keith Bussey さんは書きました: | 106 | unauthenticated user | 192.168.1.59:1925 | NULL | Connect | NULL | | login NULL | | 115 | unauthenticated user | 192.168.1.53:2041 | NULL | Connect | NULL | | login NULL | | 118 | unauthenticated user | 192.168.1.56:4172 | NULL | Connect | NULL | | login NULL | | 119 | unauthenticated user | 192.168.1.56:4173 | NULL | Connect | NULL | | login NULL Best regards Nils Valentin Tokyo/Japan -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT problem with mysql processes
Quoting Nils Valentin [EMAIL PROTECTED]: When you said you modified the scripts, are these the scripts on the 12 webservers (mysql clients) ? Yes, when I mention modifying my scripts, I mean the scripts/pages on the 12 webservers If not, then these mysql clients (webservers) would still be wanting to send to IP addres 192.168.1.71 as configured for php or in your client. Best regards Nils Valentin Tokyo/Japan 2003年 6月 27日 金曜日 14:58、Keith Bussey さんは書きました: Neither of these two machines send the request. I have 12 webservers, which send the requests. I have 5 database servers, 1 master(for updates/inserts/deletes) and 4 slaves(for selects). Machine A below was the current Master DB server, however it is old and was being replaced by machine B -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Nils Valentin [EMAIL PROTECTED]: Hi Keith, soory now I got confused. Which machine is sending the requests (client) and which machine is the mysql server (server). Do you use php or something like this (webinterface) on the client ? If yes, than my suggestions below apply. If no than I we will have to think the next step. Best regards Nils Valentin Tokyo/Japan 2003å¹´ 6æ 27æ¥ éææ¥ 11:29ãKeith Bussey ããã¯æ¸ãã¾ãã: Ok but that's not what I meant. I'll try to explain better. Machine A: IP = 192.168.1.71 Machine B: IP = 192.168.1.79 1) Scripts goto 192.168.1.71 - Everything is OK 2) Changed scripts to 192.168.1.79 - Site works at first, but processes pile up until server is killed 3) Halt machine B 4) Give machine A IP 192.168.1.79 (so now it has 2 IPs) 5) Problem persists even though it's a different machine, site works but then processes pile up and kill machine 6) Change scripts to use 192.168.1.71 again 7) Site works fine What I don't get is why do processes run normally with IP 192.168.1.71, but NOT with IP 192.168.1.79 even when they are on the same machine ? -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 Quoting Nils Valentin [EMAIL PROTECTED]: Hi Keith, The wrong IP address could only avoid a client to connect to the database server. If you have the TCP/IP address specified in /etc/php.ini or for phpmyadmin in config.inc.php or which ever tool you use than of course it will try to connect to this IP address. Best regards Nils Valentin Tokto/Japan 2003å¹´ 6æ 27æÂÂ¥ éÂÂæÂÂæÂÂ¥ 11:02ãÂÂKeith Bussey ãÂÂãÂÂã¯æ¸ãÂÂã¾ãÂÂãÂÂ: Hrmmmeaning if I do id mysql, that information (group etc..) ? I've halted that server and moved everything back to my original server for now, I had too much downtime. I did notice something else interesting though: The old mahcine's IP is 192.168.1.71 New machine's IP is 192.168.1.79 Now that it's halted, instead of changing the IP back to .71 in my pages/scripts I added .79 to the .71 machine so it has both Now that machine experienced the exact same problem. Switching my code to use .71 again however, and no problems. Could somehow there be a problem with the IP address 192.168.1.79?? It seems very strange, however tomorrow I will try putting the .71 on the new machine and see if it works or not -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT problem with mysql processes
Major problem! I've installed mysql-standard 4.0.13 (from binary) a new DB server, it is - quad-xeon (500mhz each cpu) - 3 gig RAM - Linux Redhat 7.3 When I start up mysql, processes looking like the following begin to pile up until it kills the server: | 106 | unauthenticated user | 192.168.1.59:1925 | NULL | Connect | NULL | login | NULL | | 115 | unauthenticated user | 192.168.1.53:2041 | NULL | Connect | NULL | login | NULL | | 118 | unauthenticated user | 192.168.1.56:4172 | NULL | Connect | NULL | login | NULL | | 119 | unauthenticated user | 192.168.1.56:4173 | NULL | Connect | NULL | login | NULL | I need to fix this immediately as my site cannot run ! Thanks in advance. I've seen this exact same situation happen to others (posted on various boards), however never saw any solutions ;/ -- Keith Bussey Wisol, Inc. Chief Technology Manager (514) 398-9994 ext.225 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
