mysqld got signal 11;
Hi... i have a mysql server with: Mysql version: 4.0.14-standard-log Redhat: 7.2 Kernel: 2.4.18 (Highmem) 4 GB de ram. Well mysql dies with this in the logs: --- InnoDB: Apply batch completed InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 44772490, file name kine-bin.5404 InnoDB: Last MySQL binlog file position 0 42520648, file name ./queule-bin.18027 070207 13:00:41 InnoDB: Flushing modified pages from the buffer pool... 070207 13:00:41 InnoDB: Started 070207 13:00:41 Failed to open log (file './queule-relay-bin.352', errno 2) 070207 13:00:41 Failed to open the relay log (relay_log_name='./queule-relay-bin.352', relay_log_pos=42192790 070207 13:00:41 Could not open log file 070207 13:00:41 Failed to initialize the master info structure /usr/sbin/mysqld: ready for connections. Version: '4.0.14-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex f04a5800020802009557a44dfce180fd88d880d85fcb5443502f495020283737362951006000f0586000f0580018f05801009a99014078e6410831001800f0581800f058202020202020202020202020202020202020202020202020202020010400984b2235a44dfcc521000235a44dfcc680114108f100705b1f09e800f63d775f9268a520773073656c2e6300a608d800d8004000442c414342616a61206c61206dfa7369636120656e20747520456e74656c20504353212044657363617267612072696e67746f6e657320706f6c6966f36e69636f732c207265616c746f6e65732079206261636b746f6e657320617175ed2e2056616c6f72204e61766567616369f36e3a205472e16669636f2047505253206f20736567fa6e20626f6c736120636f6e74726174616461611800f058f00021000235a44dfcc6; asc ...J..X..W.M_.TCP/IP (776)[EMAIL PROTECTED] ..K.. ..5.M..!..5.MA.p[...=w_.h. InnoDB: Scanning backward trying to find previous allocated mem blocks Freed mem block at - 14712, file mysql.c, line 303 Freed mem block at - 14904, file 0pcur.c, line 28 Freed mem block at - 15096, file 0pcur.c, line 28 Freed mem block at - 15568, file mysql.c, line 303 Freed mem block at - 15784, file mysql.c, line 303 Freed mem block at - 21280, file x0trx.c, line 78 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=323 max_connections=800 threads_connected=55 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3666809 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. --- Could this crash be caused because of this: InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex i found in the mysql web site this: If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like the one following in the .err log. The workaround is to make innodb_additional_mem_pool_size big enough to hold all memory allocation. Use SHOW INNODB STATUS to determine that there is plenty of free space available in the additional mem pool, and the total allocated memory stays rather constant. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex I have 4 GB RAM, and the value of this variable was: 1 MB, i increased to 500 MB, but i am not sure if this could solve the problem. If somebody can help me with this, it would be great! Thanks!!! Michael.- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld got signal 11;
Hi, Michael Fernández M. wrote: key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=323 max_connections=800 threads_connected=55 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3666809 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. I have 4 GB RAM, and the value of this variable was: 1 MB, i increased to 500 MB, but i am not sure if this could solve the problem. Do you use 64 or 32bit Kernel? I think with 32bit a single process like MySQL can only allocate about 2GB of RAM in total. You might want to check that. How big is your InnoDB buffer pool? regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld got signal 11;
El mar, 20-02-2007 a las 13:16 +0100, Nils Meyer escribió: Hi, Michael Fernández M. wrote: key_buffer_size=402653184 read_buffer_size=2093056 max_used_connections=323 max_connections=800 threads_connected=55 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3666809 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. I have 4 GB RAM, and the value of this variable was: 1 MB, i increased to 500 MB, but i am not sure if this could solve the problem. Do you use 64 or 32bit Kernel? I think with 32bit a single process like MySQL can only allocate about 2GB of RAM in total. You might want to check that. How big is your InnoDB buffer pool? i use 32 Bits kernel. innodb_additional_mem_pool_size = 500 MB. innodb_buffer_pool_size = 8 MB Before the innodb_additional_mem_pool_size was 1 MB, (the default value) and yesterday i increased to 500MB. But i did not touch the innodb_buffer_pool_size this still have 8 MB. Mysql have around of 400 transactions per second- Regards! Michael.- regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld got signal 11;
Hi, Michael Fernández M. wrote: i use 32 Bits kernel. Remember that you have to stay under 2GB total memory allocation! I think you hit that limit. innodb_additional_mem_pool_size = 500 MB. innodb_buffer_pool_size = 8 MB Before the innodb_additional_mem_pool_size was 1 MB, (the default value) and yesterday i increased to 500MB. But i did not touch the innodb_buffer_pool_size this still have 8 MB. Your buffer pool is a bit small, if you use innodb heavily. The innodb_additional_mem_pool is way to large I think the size could be lowered to something around 30MB. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld got signal 11;
El mar, 20-02-2007 a las 13:38 +0100, Nils Meyer escribió: Hi, Michael Fernández M. wrote: i use 32 Bits kernel. Remember that you have to stay under 2GB total memory allocation! I think you hit that limit. Sorry, but why do you say that?, because of the 32 bits kernel? innodb_additional_mem_pool_size = 500 MB. innodb_buffer_pool_size = 8 MB Before the innodb_additional_mem_pool_size was 1 MB, (the default value) and yesterday i increased to 500MB. But i did not touch the innodb_buffer_pool_size this still have 8 MB. Your buffer pool is a bit small, if you use innodb heavily. which is the recommended value for: innodb_buffer_pool_size? in my case? The innodb_additional_mem_pool is way to large I think the size could be lowered to something around 30MB. ok, i will decrease it. Thanks! regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld got signal 11;
Michael Fernández M. wrote: Remember that you have to stay under 2GB total memory allocation! I think you hit that limit. Sorry, but why do you say that?, because of the 32 bits kernel? Yes exactly. Depending on kernel version you can allocate something between 2 or 2.7GB. Until 2GB it's safe, over 2GB there be dragons. If you have lots of MyISAM tables the remaining memory can still be used for the OS file cache. which is the recommended value for: innodb_buffer_pool_size? in my case? It's difficult to suggest a value here as I'd have to know full table schemas and queries. I have a server with a mixed InnoDB/MyISAM setup and 4GB RAM and set the buffer pool to 512M. You can fine-tune that with SHOW ENGINE INNODB STATUS; which will tell you how much of the buffer pool is in use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Key buffer partially unused - why
Hi, I have allocated 500MB to key_buffer_size, but only 324MB is in use (64%). Am I right to assume that this can mean one of the following: (1) all indexes have already been cached and together they are just 324 MB, (2) there is a limiting variable (open_files, inodb_open_files, for example) that prevents the key buffer to be more fully utilized. Key buffer usage is often hitting 100%, and just 0.01 tables are opened per second. Number of queries/second is 269. Setup: 5.0.18-standard-log Solaris 8 2 CPU, 4GB RAM Regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Borland C++ Builder 2006 DLL Woes
Thanks, Mark. We did the same thing - used the ADO Class with MyOBDC. I put a call in to the sales rep. Maybe we'll get an answer someday. David | We have settled on the ado.net connector www.mysql.com after dbExpress | pains. Although apparently dbExpress does work. | What BDS 2006 service pack are you on? Keep us all posted if you solve it. | | Mark | | [EMAIL PROTECTED] wrote: | We just purchased The Borland Developer Studio 2006 IDE and are having | significant problems using dbExpress objects to communicate with MySQL | servers (both 4 and 5). Curiously, we can perform inserts but not selects, | even though identical code in C++ Builder 6 worked just fine. The DLL in | C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues? | | Thanks, | | David | | David P. Giragosian, Psy.D. | Database and Software Developer | MD Anderson Cancer Center | Houston, TX | 713-792-7898
Re: Key buffer partially unused - why
On 2007-02-20 Martijn van den Burg wrote: Hi, I have allocated 500MB to key_buffer_size, but only 324MB is in use (64%). Am I right to assume that this can mean one of the following: (1) all indexes have already been cached and together they are just 324 MB, Do find /var/lib/mysql -name *.MYI -ls (if you're using only MyISAM) to get a first hint on how much indices you actually have. (2) there is a limiting variable (open_files, inodb_open_files, for example) that prevents the key buffer to be more fully utilized. You can check at least open_files in SHOW STATUS IIRC. Key buffer usage is often hitting 100%, and just 0.01 tables are opened per second. Number of queries/second is 269. BTW, try mysqlreport to find other bottlenecks. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: row count inconsistency
At 6:23 PM -0500 2/19/07, Marty Landman wrote: Hi, I've got a very large table set up and have defined the id as auto_increment. No rows have been added, deleted, or replaced since the initial load so I'd expect the row count to equal the max(id) since mysql describe fidcid; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | fId | smallint(5) unsigned | NO | MUL | || | cId | mediumint(8) unsigned | NO | MUL | || | ring | tinyint(3) unsigned | NO | | || ++---+--+-+-++ 4 rows in set (0.38 sec) But this is not the case, as seen below: mysql select count(*) from fidcid; +---+ | count(*) | +---+ | 100480507 | +---+ 1 row in set (0.09 sec) mysql select max(id) from fidcid; +---+ | max(id) | +---+ | 100537311 | +---+ 1 row in set (0.22 sec) mysql Any ideas on what might've happened to explain this? Had the table been used before? The auto_increment counter is normally not reset, for example: mysql create table test (id int unsigned auto_increment not null primary key); Query OK, 0 rows affected (0.03 sec) mysql insert into test values (null),(null),(null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from test; ++ | id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) mysql delete from test; Query OK, 3 rows affected (0.00 sec) mysql insert into test values (null),(null),(null); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql select * from test; ++ | id | ++ | 4 | | 5 | | 6 | ++ 3 rows in set (0.00 sec) You can either drop/recreate the auto_increment field or explicitly reset it using an alter table tablename auto_increment=1 statement. See http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html for more info. steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Borland C++ Builder 2006 DLL Woes
At 09:42 AM 2/13/2007, [EMAIL PROTECTED] wrote: We just purchased The Borland Developer Studio 2006 IDE and are having significant problems using dbExpress objects to communicate with MySQL servers (both 4 and 5). Curiously, we can perform inserts but not selects, even though identical code in C++ Builder 6 worked just fine. The DLL in C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues? Thanks, David David P. Giragosian, Psy.D. Database and Software Developer MD Anderson Cancer Center Houston, TX 713-792-7898 I've been using CoreLab MySQL components for Delphi for the past few years and they work great. They also have components for accessing MySQL via .Net too. Support is excellent. It's faster than using ODBC. http://www.crlab.com/ Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Hi.
Using mySQL 4.1.22 on Linux, I got this error message suddenly this
morning (it worked ok yesterday):
-
[EMAIL PROTECTED] /usr/local/mysql/bin/mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
--
I created a '/root/root.sql' file:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xx');
And killed the process and restarted:
[EMAIL PROTECTED] ~]# /usr/local/mysql/bin/mysqld_safe --init-file=/root/root.sql
[1] 2494
[EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from
/usr/local/mysql/var
But I get the same error message :(
In my '/usr/local/mysql/var/server.err' file there's no error message:
--
070220 07:03:40 mysqld started
070220 07:03:40 InnoDB: Started; log sequence number 0 3758734
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.22' socket: '/tmp/mysql.sock' port: 3306 Source distribution
---
What am I doing wrong?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Hi.
Using mySQL 4.1.22 on Linux, I got this error message suddenly this
morning (it worked ok yesterday):
-
[EMAIL PROTECTED] /usr/local/mysql/bin/mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
--
I created a '/root/root.sql' file:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xx');
And killed the process and restarted:
[EMAIL PROTECTED] ~]# /usr/local/mysql/bin/mysqld_safe --init-file=/root/root.sql
[1] 2494
[EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from
/usr/local/mysql/var
But I get the same error message :(
In my '/usr/local/mysql/var/server.err' file there's no error message:
--
070220 07:03:40 mysqld started
070220 07:03:40 InnoDB: Started; log sequence number 0 3758734
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.22' socket: '/tmp/mysql.sock' port: 3306 Source distribution
---
What am I doing wrong?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1045 (28000): Access denied for user 'root'@'localhost'
Fixed:
[]# kill `cat /usr/local/mysql/var/server.pid`
[]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
[]# /usr/local/mysql/bin/mysql
mysql update user set password = password('xxx') where user =
'root' and host='localhost';
On 2/20/07, thomas Armstrong [EMAIL PROTECTED] wrote:
Hi.
Using mySQL 4.1.22 on Linux, I got this error message suddenly this
morning (it worked ok yesterday):
-
[EMAIL PROTECTED] /usr/local/mysql/bin/mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
--
I created a '/root/root.sql' file:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xx');
And killed the process and restarted:
[EMAIL PROTECTED] ~]# /usr/local/mysql/bin/mysqld_safe --init-file=/root/root.sql
[1] 2494
[EMAIL PROTECTED] ~]# Starting mysqld daemon with databases from
/usr/local/mysql/var
But I get the same error message :(
In my '/usr/local/mysql/var/server.err' file there's no error message:
--
070220 07:03:40 mysqld started
070220 07:03:40 InnoDB: Started; log sequence number 0 3758734
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.22' socket: '/tmp/mysql.sock' port: 3306 Source distribution
---
What am I doing wrong?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Gaining statistics from MySQL
I'm looking to find a way to determine the number of transactions that a particular database is processing each min/hour/day/month/year and would like to know of a known MySQL best practices for determining this information. I'm hoping that this can be determined at the database level, but am not currently aware of how to move for forward. Release: 4.1.20 OS: Solaris 10 Thanks in advance, *** Clyde Lewis Database Administrator General Parts, Inc. 919-227-5100 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple nistances at boot with mysqld_multi or other
I am trying to get a few instances of mysql running at boot time - and I have come across the command mysqld_multi. Seems to have a lot of documentation about kicking those off via command line - but not much on setting it up to start at boot. anyone have any insight into this? Either using mysqld_multi or a different startup script? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table - adding constraints?
I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks!
Re: MySQL Daylight Savings Time Patch
Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David If you're using NTP then what's the problem? Sync to one of the ntp pools, boom your clocks are updated, MySQL uses system time and yay. I'm fairly sure you could sync 500 server this way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table - adding constraints?
Jay Paulson wrote: I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks! 1) Are both tables InnoDB? 2) Do you have data in either tables? 3) Are the data types of both columns exactly the same ( int(20) unsigned and int(20) will fail, the unsigned has to be there)? you can also run SHOW INNODB STATUS as root to find out exactly what the error is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch
NTP won't solve this problem for you. NTP, as well as most computer clocks, know nothing about daylight savings time, or about time zones. What they know is how many seconds have elapsed since the epoch. The epoch, in the case of most UNIX-based OSes, is midnight January 1, 1970. I think Windows is the same. The original Mac OS was 1/1/1904, for example. It's up to the operating system to apply rules that determine that X number of seconds (as reported by the clock hardware) since the epoch translates to some human time, based on local settings for time zone and with any daylight savings time rules for that time zone applied. My understanding is that MySQL needs no patch, but your underlying OS most likely does. I know there have been patches issued for Solaris 2.x, 9 and 10, Windows XP, and Mac OS X 10.4, and almost certainly others. HTH, Dan On 2/20/07, Chris White [EMAIL PROTECTED] wrote: Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David If you're using NTP then what's the problem? Sync to one of the ntp pools, boom your clocks are updated, MySQL uses system time and yay. I'm fairly sure you could sync 500 server this way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
Thanks. Below is the notes from the version 5 manual, does that mean after I patch my OS, I may need to reload the timezone tables ? How I can determine that I have to reload the timezone tables, not might need? Or will it hurt anything if I just reload the tables anyway? Thanks. Note Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. For example, the rules for Daylight Saving Time in the United States, Mexico, and parts of Canada changed in 2007. When such changes occur, applications that use the old rules become out of date and you may find it necessary to reload the time zone tables to keep the information used by your MySQL server current. See the notes at the end of this section. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 20, 2007 5:03 PM To: Chris White Cc: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch NTP won't solve this problem for you. NTP, as well as most computer clocks, know nothing about daylight savings time, or about time zones. What they know is how many seconds have elapsed since the epoch. The epoch, in the case of most UNIX-based OSes, is midnight January 1, 1970. I think Windows is the same. The original Mac OS was 1/1/1904, for example. It's up to the operating system to apply rules that determine that X number of seconds (as reported by the clock hardware) since the epoch translates to some human time, based on local settings for time zone and with any daylight savings time rules for that time zone applied. My understanding is that MySQL needs no patch, but your underlying OS most likely does. I know there have been patches issued for Solaris 2.x, 9 and 10, Windows XP, and Mac OS X 10.4, and almost certainly others. HTH, Dan On 2/20/07, Chris White [EMAIL PROTECTED] wrote: Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David If you're using NTP then what's the problem? Sync to one of the ntp pools, boom your clocks are updated, MySQL uses system time and yay. I'm fairly sure you could sync 500 server this way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
At 4:17 PM -0600 2/20/07, Paul DuBois wrote: At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. I should mention also: For those of you running 4.1.3 or later, to get your MySQL server to know about the new DST rules, you should make sure your OS is updated with the new zoneinfo files, and then reload those files into MySQL with mysql_tzinfo_to_sql. See: http://dev.mysql.com/doc/mysql/en/time-zone-support.html Particularly the Note in the middle of the page and the last few paragraphs. You may have previously loaded your system's zoneinfo files into MySQL, but when those zoneinfo files are updated, the changes do not automatically propagate to MySQL's time zone tables. You must reload the tables to update them. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
At 5:08 PM -0500 2/20/07, Sun, Jennifer wrote: Thanks. Below is the notes from the version 5 manual, does that mean after I patch my OS, I may need to reload the timezone tables ? How I can determine that I have to reload the timezone tables, not might need? Or will it hurt anything if I just reload the tables anyway? Thanks. Just go ahead and reload them. If you look at the output of mysql_tzinfo_to_sql, you'll notice that the first thing is does is TRUNCATE TABLE for the time zone tables, so that they start out in a pristine state before the new information gets loaded into them. Note Loading the time zone information is not necessarily a one-time operation because the information changes occasionally. For example, the rules for Daylight Saving Time in the United States, Mexico, and parts of Canada changed in 2007. When such changes occur, applications that use the old rules become out of date and you may find it necessary to reload the time zone tables to keep the information used by your MySQL server current. See the notes at the end of this section. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch
Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Paul DuBois wrote: At 4:17 PM -0600 2/20/07, Paul DuBois wrote: At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. I should mention also: For those of you running 4.1.3 or later, to get your MySQL server to know about the new DST rules, you should make sure your OS is updated with the new zoneinfo files, and then reload those files into MySQL with mysql_tzinfo_to_sql. See: http://dev.mysql.com/doc/mysql/en/time-zone-support.html Particularly the Note in the middle of the page and the last few paragraphs. You may have previously loaded your system's zoneinfo files into MySQL, but when those zoneinfo files are updated, the changes do not automatically propagate to MySQL's time zone tables. You must reload the tables to update them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch
At 4:40 PM -0600 2/20/07, Ryan Stille wrote: Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Yes, reload them. :-) After that, they're current! If you really want a non-reload test, you can do something like this (Credits: This example comes from Peter Gulutzan) Find out the time zone id for the region: mysql select * from mysql.time_zone_name - where Name = 'America/Edmonton'; ++--+ | Name | Time_zone_id | ++--+ | America/Edmonton | 100 | ++--+ 1 row in set (0.97 sec) Find out the transition dates for that zone in this year: mysql select * from time_zone_transition - where Time_zone_id = - and Transition_time between 1167634800 and 1199170799 - order by Time_zone_id,Transition_time; +--+-++ | Time_zone_id | Transition_time | Transition_type_id | +--+-++ | 100 | 1175418000 | 1 | | 100 | 1193558400 | 2 | +--+-++ 2 rows in set (0.00 sec) Find out what '1175418000' and '1193558400' mean: mysql select from_unixtime(1175418000); +---+ | from_unixtime(1175418000) | +---+ | 2007-04-01 03:00:00 | +---+ 1 row in set (0.00 sec) mysql select from_unixtime(1193558400); +---+ | from_unixtime(1193558400) | +---+ | 2007-10-28 01:00:00 | +---+ 1 row in set (0.00 sec) Diagnosis: this database thinks the switch is on April 1, which is wrong. Cure: update your operating system files, check the MySQL Server Time Zone Support section of the manual, and update the table. So, as you can see, it's probably easier just to reload the files. Paul DuBois wrote: At 4:17 PM -0600 2/20/07, Paul DuBois wrote: At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. I should mention also: For those of you running 4.1.3 or later, to get your MySQL server to know about the new DST rules, you should make sure your OS is updated with the new zoneinfo files, and then reload those files into MySQL with mysql_tzinfo_to_sql. See: http://dev.mysql.com/doc/mysql/en/time-zone-support.html Particularly the Note in the middle of the page and the last few paragraphs. You may have previously loaded your system's zoneinfo files into MySQL, but when those zoneinfo files are updated, the changes do not automatically propagate to MySQL's time zone tables. You must reload the tables to update them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table - adding constraints?
1) both tables are InnoDB. 2) both tables have data in them. 3) both table are the exact same data types. On 2/20/07 3:51 PM, Chris White [EMAIL PROTECTED] wrote: Jay Paulson wrote: I really don¹t know what to do because I keep getting this error. Any ideas? SQL query: ALTER TABLE pl_reports ADD CONSTRAINT fk_region FOREIGN KEY ( region ) REFERENCES Region( id ) ON UPDATE CASCADE ON DELETE CASCADE MySQL said: Documentation #1005 - Can't create table './survey_localhost/#sql-113_f8.frm' (errno: 150) Thanks! 1) Are both tables InnoDB? 2) Do you have data in either tables? 3) Are the data types of both columns exactly the same ( int(20) unsigned and int(20) will fail, the unsigned has to be there)? you can also run SHOW INNODB STATUS as root to find out exactly what the error is. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter table - adding constraints?
Jay Paulson wrote: 2) both tables have data in them. This is most likely your issue then, depending on the table size, go through and make sure that anything in the referenced column matches the referencing column. You should also be able to use SHOW INNODB STATUS to see what's possibly failing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Year - Field type
Thanks for the advice Its actually just a year of birth and the year type field is perfect apart from the fact that it wont work with anything pre 1900 Regards John B -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: 20 February 2007 00:19 To: [EMAIL PROTECTED] Cc: Mike Blezien; mysql@lists.mysql.com Subject: RE: Year - Field type Why not keep the date of birth as a standard date field and extract the fields you need using the DATE_FORMAT function? eg. DATE_FORMAT(date_of_birth, '%Y') mysql \u test Database changed mysql create table test_dates (a int, mydate date); Query OK, 0 rows affected (0.29 sec) mysql describe test_dates; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | a | int(11) | YES | | NULL| | | mydate | date| YES | | NULL| | ++-+--+-+-+---+ 2 rows in set (0.03 sec) mysql insert into test_dates SET a=1, mydate=1887-10-12; Query OK, 1 row affected (0.10 sec) mysql select * from test_dates; +--++ | a| mydate | +--++ |1 | 1887-10-12 | +--++ 1 row in set (0.00 sec) mysql insert into test_dates SET a=1, mydate=1987-10-12; Query OK, 1 row affected (0.10 sec) mysql select * from test_dates order by mydate; +--++ | a| mydate | +--++ |1 | 1887-10-12 | |1 | 1987-10-12 | +--++ 2 rows in set (0.00 sec) mysql select DATE_FORMAT(mydate,'%Y') as year FROM test_dates ORDER BY year; +--+ | year | +--+ | 1887 | | 1987 | +--+ 2 rows in set (0.00 sec) mysql Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: John Berman [mailto:[EMAIL PROTECTED] Sent: Tuesday, 20 February 2007 9:59 AM To: 'Mike Blezien'; mysql@lists.mysql.com Subject: RE: Year - Field type Mickalo I gave that a go now I have another issue I can only enter years 1900 onwards so when I enter 1887 it changes the value to 0 John B -Original Message- From: Mike Blezien [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 23:10 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Year - Field type have you try using the datatype YEAR for you table field/column ? Mickalo - Original Message - From: John Berman [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 19, 2007 11:45 AM Subject: Year - Field type Hi Using mysql4 Sure this is an easy one a field in my dbase is year of birth, its always a 4 digit number, for some reason Im failing to sort by the field in my results, it was originally a varchar field so I updated it to int but still no luck. Pointers appreciated. Regards John Berman -- 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] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.2/692 - Release Date: 18/02/2007 -- 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 Daylight Savings Time Patch
Running 4.1.22, on windows 98, I'm having trouble getting the time zone
tables to actually work. I've loaded the tables as per:
http://dev.mysql.com/downloads/timezones.html, and followed the diagnostics
as per: http://lists.mysql.com/mysql/205115
.
It appears that my results from said diagnostic indeed prove that my tables
are correct. However, I don't see those tables being used. The following
statements yield varying results on my linux/windows machines (I run about
ten servers).
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-4-1 00:00:00') +
2*60*60),'%H');
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-3-11 00:00:00') +
2*60*60),'%H');
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-4-2 00:00:00') +
2*60*60),'%H');
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-3-12 00:00:00') +
2*60*60),'%H');
The linux machine -- with empty timezone tables is correct. 2007-3-11 and
2006-4-2 result in midnight plus 2 hours being 3am -- correct in the past
and correct in the future. On my modern XP machines, the future is correct
and the past is incorrect. On my older XP/98 machines, the past is correct
and the future is incorrect. It is on my 98 server that I am now playing,
having loaded timezone tables, and going crazy. I have upgraded my win98
timezones via a registry update.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch
Same exact issue on v5.0.27.
I installed another windows patch, that did nothing, but then changed my
system clock to march 12th, and then back to present day. Now the DST
shifts are correct in the future and incorrect in the past. I've destroyed
the timezone tables, and they made no difference. Which means that they
were never being used.
Any ideas?
- Original Message -
From: Bryan S. Katz [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, February 20, 2007 7:50 PM
Subject: MySQL Daylight Savings Time Patch
Running 4.1.22, on windows 98, I'm having trouble getting the time zone
tables to actually work. I've loaded the tables as per:
http://dev.mysql.com/downloads/timezones.html, and followed the
diagnostics
as per: http://lists.mysql.com/mysql/205115
.
It appears that my results from said diagnostic indeed prove that my
tables
are correct. However, I don't see those tables being used. The following
statements yield varying results on my linux/windows machines (I run about
ten servers).
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-4-1 00:00:00') +
2*60*60),'%H');
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2007-3-11 00:00:00') +
2*60*60),'%H');
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-4-2 00:00:00') +
2*60*60),'%H');
SELECT DATE_FORMAT(from_unixtime(unix_timestamp('2006-3-12 00:00:00') +
2*60*60),'%H');
The linux machine -- with empty timezone tables is correct. 2007-3-11 and
2006-4-2 result in midnight plus 2 hours being 3am -- correct in the past
and correct in the future. On my modern XP machines, the future is
correct
and the past is incorrect. On my older XP/98 machines, the past is
correct
and the future is incorrect. It is on my 98 server that I am now playing,
having loaded timezone tables, and going crazy. I have upgraded my win98
timezones via a registry update.
--
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]
Using Triggers to Maintain a Table to prevent complex join statements...
We have a statement that joins 8 different tables to pull multiple rows from each table. I have heard of people using triggers to create, update and delete records in a table based on statements in other tables. The obvious result of this would be to SPEED up results right? :) I'd love to find someone with experience with this that I can pick info from. lol There isn't really a 'best practices' guide that I can find beyond what is in the normal documentation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
