Re: Huge temporary file
In infinite wisdom Jerry Schwartz [EMAIL PROTECTED] spoke thus: I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. MySQL creates the tmp tables in memory if the size of the table matches these thresholds max_heap_table_size tmp_table_size Whichever of these two values is smaller is the one that applies. Is the datatype of consolidated_customer_data.stage_name varchar(15)? (There are other conditions too, but since you can fit the table by removing one column, I am assuming you are hitting size threshold http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html) -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
delaying replication
Hi All, If we want the replication (slave) to lay by 8 hrs from master, is there any parameter that i can use in the slave, so that it apply changes happened only 8 hrs back. regards anandkl
Re: delaying replication
Hi, You do this with mk-slave-delay from the Maatkit toolkit. http://www.maatkit.org/tools.html This tool implements delayed replication on the slave by sampling binlog positions, then starting and stopping the slave as needed to make the slave lag its master by a specified amount. It does not read binlogs (directly or indirectly), so it has very little overhead. Ewen On Tue, Aug 26, 2008 at 9:39 AM, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, If we want the replication (slave) to lay by 8 hrs from master, is there any parameter that i can use in the slave, so that it apply changes happened only 8 hrs back. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trapping error code and error message.
Hi All, In oracle we have these two bulit in's SQLCODE and SQLERRM, which helps use to find the error code and error message. Does mysql also provide any thing like this. I want to use this in my stored proc to find out if stored proc throws any exception apart from NO_DATA_FOUND regards anandkl
Re: Error stops replication
tsa is actually the database. It's running the function AddSchool(), and it appears that something went wrong in that situation. The actual table affected inside the function is school, and that table does exist on both master and slave. If by general log, you mean the log named machine.err, I've checked that file on both master and slave, and there is no reference there at all to a AddSchool( function error. Other than that, I don't see that any other logs are activated (other than the binary logs, of course). Jesse - Original Message - From: Ananda Kumar To: Jesse Cc: MySQL List Sent: Tuesday, August 26, 2008 12:55 AM Subject: Re: Error stops replication does tsa table exists on both master and slave. If you have enabled general log, then u can see most of the errors. I am suspecting that this table is not present in slave On 8/26/08, Jesse [EMAIL PROTECTED] wrote: I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error stops replication
general log is different from machine.err log, it stores all logs of the db. On 8/26/08, Jesse [EMAIL PROTECTED] wrote: tsa is actually the database. It's running the function AddSchool(), and it appears that something went wrong in that situation. The actual table affected inside the function is school, and that table does exist on both master and slave. If by general log, you mean the log named machine.err, I've checked that file on both master and slave, and there is no reference there at all to a AddSchool( function error. Other than that, I don't see that any other logs are activated (other than the binary logs, of course). Jesse - Original Message - *From:* Ananda Kumar [EMAIL PROTECTED] *To:* Jesse [EMAIL PROTECTED] *Cc:* MySQL List mysql@lists.mysql.com *Sent:* Tuesday, August 26, 2008 12:55 AM *Subject:* Re: Error stops replication does tsa table exists on both master and slave. If you have enabled general log, then u can see most of the errors. I am suspecting that this table is not present in slave On 8/26/08, Jesse [EMAIL PROTECTED] wrote: I have been getting an error which stops replication on my slave server. The error is as follows: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1' on query. Default database: 'tsa'. Query: 'SELECT `tsa`.`AddSchool`(_latin1'Grenada High School',_latin1'2',' The thing is that I'm not getting an error on the master server, to my knowledge. When I check the error log, there is no such error in there. Does the Machine.err file log these sort of errors? If not, where can I look to see if it is happening on the master server? I'm running version 5.0.67-community-nt on the slave and version 5.0.51a-nt-log on the master. If anyone has any ideas on what might be causing this, please let me know what you think. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Huge temporary file
From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 12:09 AM To: Jerry Schwartz Cc: mysql Subject: Re: Huge temporary file what is the value for tmpdir parameter in you my.cnf. [JS] I don't have one. The /tmp file system is what is being consumed. I should add that the temporary space being used is many times the size of any of the tables involved. My entire /etc/my.cnf file looks like this: [mysqld] set-variable = max_connections=500 set-variable = ft_min_word_len=3 set-variable = character_set_server=utf8 safe-show-database regards anandkl On 8/26/08, Jerry Schwartz [EMAIL PROTECTED] wrote: Awhile back I was having trouble with an INSERT ... SELECT taking up an enormous amount of temporary file space. I've narrowed down, in fact eliminated, my problem by making a minor change. Here is my original INSERT command: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(giiexpr_db.topic.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id LEFT JOIN giiexpr_db.topic ON cust_topics.topic_id = giiexpr_db.topic.topic_id GROUP BY customers.customer_id; When I removed the field `stage_name` from both the query and the `consolidated_customer_data` table, the operation stopped using temporary files altogether! I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. The `stage` table is very small, it has only 9 rows. CREATE TABLE `stage` ( `stage_id` int(11) NOT NULL auto_increment, `stage_name` varchar(15) default NULL, PRIMARY KEY (`stage_id`) ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 I can supply the structures of the other tables, but I wanted to keep this post reasonably short. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Huge temporary file
-Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 2:36 AM To: mysql@lists.mysql.com Subject: Re: Huge temporary file In infinite wisdom Jerry Schwartz [EMAIL PROTECTED] spoke thus: [JS] My wisdom use to be infinite, but infinity has gotten bigger since I was a teenager. I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. MySQL creates the tmp tables in memory if the size of the table matches these thresholds max_heap_table_size tmp_table_size Whichever of these two values is smaller is the one that applies. [JS] I presume that I should set these to something above the default, then, since it is real file space that is being consumed. Our database is small, so I've never had to give much thought to tuning. Is the datatype of consolidated_customer_data.stage_name varchar(15)? [JS] It was, yes. Most of the other fields were varchar as well, with the exception of one text field. (There are other conditions too, but since you can fit the table by removing one column, I am assuming you are hitting size threshold http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html) [JS] I'll read that article and see if it applies. Thanks. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Huge temporary file
From: Johnny Withers [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 9:37 AM To: Jerry Schwartz Subject: Re: Huge temporary file You are using the defaults then, I'm not sure what they are, but you can view the location of the temp directory (probably /tmp) and the maximum size of any temporary table by issuing the command: show variables like '%tmp%'; You can view the maximum size of heap tables by: show variables like '%heap%'; I find a setting of 256MB for both of these works well for me (8gb ram). [JS] I suspected that would be the one to tinker with, but MySQL is eating up over 900MB and I'm not sure that I should set my heap that big. My real question is, why does this need such an enormous amount of space? -johnny On 8/26/08, Jerry Schwartz [EMAIL PROTECTED] wrote: From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 12:09 AM To: Jerry Schwartz Cc: mysql Subject: Re: Huge temporary file what is the value for tmpdir parameter in you my.cnf. [JS] I don't have one. The /tmp file system is what is being consumed. I should add that the temporary space being used is many times the size of any of the tables involved. My entire /etc/my.cnf file looks like this: [mysqld] set-variable = max_connections=500 set-variable = ft_min_word_len=3 set-variable = character_set_server=utf8 safe-show-database regards anandkl On 8/26/08, Jerry Schwartz [EMAIL PROTECTED] wrote: Awhile back I was having trouble with an INSERT ... SELECT taking up an enormous amount of temporary file space. I've narrowed down, in fact eliminated, my problem by making a minor change. Here is my original INSERT command: INSERT INTO consolidated_customer_data SELECT customers.customer_id, account.account_name, customers.email, customers.email_status, customers.dm_status, customers.status, customers.last_name, customers.first_name, customers.sal, customers.company, customers.address_1, customers.address_2, customers.address_3, customers.country, customers.zip, customers.input_source, customers.interest_category, customers.interest_subcategory, CONCAT(|, GROUP_CONCAT(giiexpr_db.topic.topic_code SEPARATOR |), |) AS topic_list, stage.stage_name FROM customers JOIN account ON account.account_id = customers.account_id JOIN stage ON customers.stage_id = stage.stage_id LEFT JOIN cust_topics ON customers.customer_id = cust_topics.customer_id LEFT JOIN giiexpr_db.topic ON cust_topics.topic_id = giiexpr_db.topic.topic_id GROUP BY customers.customer_id; When I removed the field `stage_name` from both the query and the `consolidated_customer_data` table, the operation stopped using temporary files altogether! I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. The `stage` table is very small, it has only 9 rows. CREATE TABLE `stage` ( `stage_id` int(11) NOT NULL auto_increment, `stage_name` varchar(15) default NULL, PRIMARY KEY (`stage_id`) ) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 I can supply the structures of the other tables, but I wanted to keep this post reasonably short. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - Johnny Withers 601.209.4985 [EMAIL PROTECTED]
RE: Huge temporary file
-Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 2:36 AM To: mysql@lists.mysql.com Subject: Re: Huge temporary file In infinite wisdom Jerry Schwartz [EMAIL PROTECTED] spoke thus: I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. MySQL creates the tmp tables in memory if the size of the table matches these thresholds max_heap_table_size tmp_table_size Whichever of these two values is smaller is the one that applies. Is the datatype of consolidated_customer_data.stage_name varchar(15)? (There are other conditions too, but since you can fit the table by removing one column, I am assuming you are hitting size threshold http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html) [JS] According to that article, it is the presence of a TEXT field that is forcing the use of a temporary table. The JOIN to the `stage` table must have been causing the temporary table to disk. That doesn't explain why the without the `stage` table, the temporary table fits in the (default) heap size of 16777216 and (default) tmp_table_size of 33554432; but with the `stage` table MySQL needs more than 973M. I might have to just give up on this and put it in the X file. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL
Simple answer: Of course you can. This will bring out the true power of C. So - YES, you can work with C and MySQL. In fact, you work with MySQL, much like any other databases/SQL, with *many*programming languages. For C I suggest you use Google and/or check out the following resources: http://www.ucl.ac.uk/is/mysql/c/ http://www.tutorialized.com/tutorials/C-and-Cpp/1 http://www.planetmysql.org/entry.php?id=14437 http://www.cyberciti.biz/tips/linux-unix-connect-mysql-c-api-program.html MySQL itself is in fact in written in C and C++! I hope this helps some. Cheers, Craig Huffstetler #mysql | Freenode On Mon, Aug 25, 2008 at 3:32 AM, Vicente Moreno [EMAIL PROTECTED]wrote: Hi all I have a little question, have you ever work C MYSQL??? all about that is new for me, if anybody has some info, help me!!! __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! Regístrate ya - http://correo.yahoo.com.mx/ -- Ogden Nash - The trouble with a kitten is that when it grows up, it's always a cat.
TEXT(n)
The 5.0 documentation that I have says that you can specify a length for a TEXT column, and that this will affect the storage space used by the column data. When I specify TEXT(n), however, n is ignored. It won't be shown in a SHOW CREATE, nor in any other way. Is this a feature that came and went? Was the documentation wrong? (I downloaded a compiled Windows help file.) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com http://www.giiexpress.com www.giiexpress.com www.etudes-marche.com
Normalization vs. Performance
Hi, here my table which stores multiple trees with nested sets: CREATE TABLE `posts` ( `posting_id` int(11) unsigned NOT NULL auto_increment, `root_id` int(11) unsigned NOT NULL, `lft` int(11) unsigned NOT NULL, `rgt` int(11) unsigned NOT NULL, `subject` varchar(400) collate latin1_german1_ci NOT NULL, `posting_date` datetime NOT NULL, -- some additional fields PRIMARY KEY (`posting_id`), UNIQUE KEY `id_und_lft` (`posting_id`,`lft`), KEY `root_id` (`root_id`), KEY `username` (`username`), KEY `root-id_und_lft` (`root_id`,`lft`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci; I can select all trees with the following SELECT: SELECT a.posting_id, a.root_id, a.username, a.subject, count(*) AS level FROM posts AS a JOIN posts AS b ON (a.lft BETWEEN b.lft AND b.rgt) AND (b.root_id = a.root_id) GROUP BY a.posting_id ORDER BY a.root_id,a.lft Works fine, uses indexes nicely. Now I want to display those trees in reverse chronological order. (The date of a tree is determined by the `posting_date` field of its root) That's not a big problem since the joining is already done correctly. SELECT a.posting_id, a.root_id, a.username, a.subject, count(*) AS level FROM posts AS a JOIN posts AS b ON (a.lft BETWEEN b.lft AND b.rgt) AND (b.root_id = a.root_id) GROUP BY a.posting_id ORDER BY b.posting_date DESC, a.root_id,a.lft But here's the problem. Since the results are now ordered by fields from more than one table, indexes no longer work and filesort is used. So how bad is this? The mentioned query will be the query which is used the most in my application (yes, it is going to be a forum). Should I break normalization and save the date of the root in each node row? Greets, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
alter merge table doesn't work as documented (?)
I have a merge table and dropped one of the tables that was in the union. I then tried to alter the merge table to use only the remaining tables. Here is some simple test code representing what I was doing. create table t1 (x int); create table t2 (x int); create table t3 (x int); create table t_merge (x int) engine=merge union=(t1,t2,t3); drop table t1; alter table t_merge union=(t2, t3); ## As I read the doc, this ought to work, leaving me with a merge table with only 2 tables in the union. The output I got was (the error message is at the end): -- create table t1 (x int) -- -- create table t2 (x int) -- -- create table t3 (x int) -- -- create table t_merge (x int) engine=merge union=(t1,t2,t3) -- -- drop table t1 -- -- alter table t_merge union=(t2, t3) -- ERROR 1146 (42S02) at line 16: Table 'jlyons.t1' doesn't exist Is this a bug or expected behavior? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
RE: alter merge table doesn't work as documented (?)
You are better off running it this way: create table t1 (x int); create table t2 (x int); create table t3 (x int); create table t_merge (x int) engine=merge union=(t1,t2,t3); drop table t1; drop table t_merge; create table t_merge (x int) engine=merge union=(t2,t3); -Original Message- From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 4:41 PM To: mysql Subject: alter merge table doesn't work as documented (?) I have a merge table and dropped one of the tables that was in the union. I then tried to alter the merge table to use only the remaining tables. Here is some simple test code representing what I was doing. create table t1 (x int); create table t2 (x int); create table t3 (x int); create table t_merge (x int) engine=merge union=(t1,t2,t3); drop table t1; alter table t_merge union=(t2, t3); ## As I read the doc, this ought to work, leaving me with a merge table with only 2 tables in the union. The output I got was (the error message is at the end): -- create table t1 (x int) -- -- create table t2 (x int) -- -- create table t3 (x int) -- -- create table t_merge (x int) engine=merge union=(t1,t2,t3) -- -- drop table t1 -- -- alter table t_merge union=(t2, t3) -- ERROR 1146 (42S02) at line 16: Table 'jlyons.t1' doesn't exist Is this a bug or expected behavior? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: alter merge table doesn't work as documented (?)
true, but I'm more interested in if the error message I received is documented behavior or not. Thanks, Jim On Tue, Aug 26, 2008 at 4:11 PM, Rolando Edwards [EMAIL PROTECTED]wrote: You are better off running it this way: create table t1 (x int); create table t2 (x int); create table t3 (x int); create table t_merge (x int) engine=merge union=(t1,t2,t3); drop table t1; drop table t_merge; create table t_merge (x int) engine=merge union=(t2,t3); -Original Message- From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 4:41 PM To: mysql Subject: alter merge table doesn't work as documented (?) I have a merge table and dropped one of the tables that was in the union. I then tried to alter the merge table to use only the remaining tables. Here is some simple test code representing what I was doing. create table t1 (x int); create table t2 (x int); create table t3 (x int); create table t_merge (x int) engine=merge union=(t1,t2,t3); drop table t1; alter table t_merge union=(t2, t3); ## As I read the doc, this ought to work, leaving me with a merge table with only 2 tables in the union. The output I got was (the error message is at the end): -- create table t1 (x int) -- -- create table t2 (x int) -- -- create table t3 (x int) -- -- create table t_merge (x int) engine=merge union=(t1,t2,t3) -- -- drop table t1 -- -- alter table t_merge union=(t2, t3) -- ERROR 1146 (42S02) at line 16: Table 'jlyons.t1' doesn't exist Is this a bug or expected behavior? Thanks, Jim -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Upgrade from 4.0.26 to 5.0.67
Use mysqldump on the old version and import the data into new version in my opinion. On Fri, Aug 22, 2008 at 1:29 AM, Andy Shellam [EMAIL PROTECTED]wrote: FYI the manual for 5.0 recommends upgrading to 4.1 first. As a general rule, we recommend that when upgrading from one release series to another, you should go to the next series rather than skipping a series. If you wish to upgrade from a release series previous to MySQL 4.1, you should upgrade to each successive release series in turn until you have reached MySQL 4.1, and then proceed with the upgrade to MySQL 5.0. For example, if you currently are running MySQL 3.23 and wish to upgrade to a newer series, upgrade to MySQL 4.0 first before upgrading to 4.1. It also says to run the mysql_upgrade program to convert your table formats and grant tables. There have been plenty of changes as you'd expect, including numerous incompatible changes to the SQL parser, so make sure you read the following manual page first to see if your applications are affected: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html. If in any doubt, a dump from the old server and reload into the new server would probably be a better upgrade method. Andy Quoting Nanu Kalmanovitz [EMAIL PROTECTED]: Hi! I wish to upgrade the MySQL on a web server (Novell 6.5 sp6 - Apache 2, MySQL ver. 4.0.26, PHP 5.2.3) to 4.1.2 or 5.0.67. Is there any possibility to upgrade directly from MySQL 4.0.26 to 5.0.67, without upgrading first to the intermediate versions? TIA Nanu -- 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] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Lost connection
Only increase max_allowed_packet is ok. On Tue, Aug 19, 2008 at 5:49 PM, Warren Young [EMAIL PROTECTED] wrote: Mad Unix wrote: During the update of the MySQL DB (delete/insert), I keep getting the following message Lost connection to MySQL server during query... By default, the MySQL server drops a connection after 8 hours of receiving no queries on that connection. This can happen in an application that keeps its connection open constantly, and people don't use it overnight or over a weekend. You can either increase the timeout in my.cnf, or you can ping the connection occasionally with mysql_ping(). Or, you can add code to your applications to detect this, and reestablish the connection and retry the command. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Time Zone Support
Hi, We need to add support for time zone to our existing PHP Board Member Extranet applications. Could those of you with experience supporting a time zone field in your MySQL data tables offer some advice? Would you use a single field? Would you use just an offset value? How would you represent the information on presentation data? How would you list the time zone options in your form dropdowns? Would you use a separate time zone table to support the offset values, an abbreviated time zone, a full time zone name and a daylight savings time value? We greatly appreciate any advice you can offer. Thank you for taking the time to share your knowledge... Keith
Re: Huge temporary file
do, show varaibles like '%tmp%'. show variables like '%tmp%'; | tmpdir| /tmp/ This is case of your tmp file system, running out space. You can change this parameter to a different file system having more space, and then your job should run fine. regards anandkl On 8/26/08, Jerry Schwartz [EMAIL PROTECTED] wrote: -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Tuesday, August 26, 2008 2:36 AM To: mysql@lists.mysql.com Subject: Re: Huge temporary file In infinite wisdom Jerry Schwartz [EMAIL PROTECTED] spoke thus: I'm at a loss as to why, other than that I must have hit some threshold. If anyone can tell me what I need to change in my configuration, I'd appreciate it. MySQL creates the tmp tables in memory if the size of the table matches these thresholds max_heap_table_size tmp_table_size Whichever of these two values is smaller is the one that applies. Is the datatype of consolidated_customer_data.stage_name varchar(15)? (There are other conditions too, but since you can fit the table by removing one column, I am assuming you are hitting size threshold http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html) [JS] According to that article, it is the presence of a TEXT field that is forcing the use of a temporary table. The JOIN to the `stage` table must have been causing the temporary table to disk. That doesn't explain why the without the `stage` table, the temporary table fits in the (default) heap size of 16777216 and (default) tmp_table_size of 33554432; but with the `stage` table MySQL needs more than 973M. I might have to just give up on this and put it in the X file. -- raj shekhar facts: http://rajshekhar.net opinions: http://rajshekhar.net/blog I've never made anyone's life easier and you know it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
