Innodb Settings - repost
** I am reposting. ** I am looking for some general rule to determine the innodb_buffer_pool_size and innodb_log_file_size based on number of innodb tables, transactions, etc. Setting these values based on how much of the server resources I am allotted is not adequate. Thanks ORIGINAL POST Hello I am looking to determine the best Innodb Settings for our MySQL Instances. Specifically, the settings for innodb_buffer_pool_size and innodb_log_file_size. I have read the manual and understand how they work. I am looking for additional information that would help me determine their values instance by instance. In other words, if I only have 2 Innodb tables with low data and xaction rates, then I should have them set to x. If there is some additional determining factors beside the number of tables, rows and expected transaction rate, I would like to know them as well. Thanks in advance! Gabe /ORIGINAL POST -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Settings
Hello I am looking to determine the best Innodb Settings for our MySQL Instances. Specifically, the settings for innodb_buffer_pool_size and innodb_log_file_size. I have read the manual and understand how they work. I am looking for additional information that would help me determine their values instance by instance. In other words, if I only have 2 Innodb tables with low data and xaction rates, then I should have them set to x. If there is some additional determining factors beside the number of tables, rows and expected transaction rate, I would like to know them as well. Thanks in advance! Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test - please ignore
Just a test - thanks There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No Longer Receiving Emails
Hi I have not received anything from this list for a couple of days. I just tried to re-register, and that did not help. If anyone reads this, please reply to ME so I can determine if the problem is with my account or with the list. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: No Longer Receiving Emails
Thanks - now I am getting emails from the list.. Gabe -Original Message- From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED] Sent: Friday, May 27, 2005 2:39 PM To: Tucker, Gabriel Subject: Re: No Longer Receiving Emails You are sending messages without error, it would appear. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC on UNIX and NFS
Hello All I am about to set up ODBC for MySQL (iODBC) on our UNIX systems. We have many machines that will need to use this installation. I would like to leverage a NFS mount that is available to all the machines. We currently leverage the NFS mount for a MySQL Client. I am wondering if anyone has this type of implementation or any comments? Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: ODBC on UNIX and NFS
Actually - I have some more details to make this clearer: We will be using UnixODBC - already installed on a NFS mount, and the MySQL drivers. So, could we install the MySQL drivers on the NFS Mount? Thanks again - Gabe -Original Message- From: [EMAIL PROTECTED] Sent: Wednesday, May 04, 2005 10:30 AM To: Mysql General (E-mail) Subject: ODBC on UNIX and NFS Hello All I am about to set up ODBC for MySQL (iODBC) on our UNIX systems. We have many machines that will need to use this installation. I would like to leverage a NFS mount that is available to all the machines. We currently leverage the NFS mount for a MySQL Client. I am wondering if anyone has this type of implementation or any comments? Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb.status.#### files
Hello All I have an MySQL instance running that is generating the innodb.status. files. However, there is no innodb_monitor table in any of the databases. How is this happening? Thanks - Gabe 4.0.20-max-log SunOS 5.8 There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Monitoring replication in mysql
Anil Write a script that does a slave status and check if either of the threads are running. You could further check for error numbers and descriptions. This is what we do. Gabe -Original Message- From: Anil Doppalapudi [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 21, 2004 6:05 AM To: [EMAIL PROTECTED] Subject: Monitoring replication in mysql Hi, we have no of mysql replication setups in our setup. how to monitor those replication setups.my aim is if any slave goes down my script should immediately send an alert mail to me. if anybody having already developed scripts please let me know otherwise just give me an idea what to monitor in in replication setup. Thanks Anil DBA -- 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]
Production instances down - MySQL got signal 11
Hi all I am on Solaris 8 using mysql.4.0.16 standard. My instances crashed with the following in the .err file: 041103 07:44:16 mysqld started 041103 7:44:17 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 4 3285189691 InnoDB: Doing recovery: scanned up to log sequence number 4 3285189691 InnoDB: Fatal error: cannot allocate 2213656 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 371642468 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 041103 07:44:18 mysqld ended I have about 2 gig of filesystem space left. I don't believe that to be an issue. However, I was able to get it up for about 15 minutes when I purged some old binary logs. Any help would be greatly appreciated. Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Production instances down - MySQL got signal 11
All I have discovered that I had a memory problem on this server which caused the crash. I reduced the initial memory requirements for Innodb and now it is running. I am curious why it crashed in the first place. If anyone has insight on this, I world appreciate hearing it... Thanks - Gabe -Original Message- From: [EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 8:58 AM To: Mysql General (E-mail) Subject: Production instances down - MySQL got signal 11 Hi all I am on Solaris 8 using mysql.4.0.16 standard. My instances crashed with the following in the .err file: 041103 07:44:16 mysqld started 041103 7:44:17 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 4 3285189691 InnoDB: Doing recovery: scanned up to log sequence number 4 3285189691 InnoDB: Fatal error: cannot allocate 2213656 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 371642468 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 041103 07:44:18 mysqld ended I have about 2 gig of filesystem space left. I don't believe that to be an issue. However, I was able to get it up for about 15 minutes when I purged some old binary logs. Any help would be greatly appreciated. Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- 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: Production instances down - MySQL got signal 11
All - I got my answers. Thanks. -Original Message- From: [EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 10:18 AM To: Mysql General (E-mail) Subject: RE: Production instances down - MySQL got signal 11 All I have discovered that I had a memory problem on this server which caused the crash. I reduced the initial memory requirements for Innodb and now it is running. I am curious why it crashed in the first place. If anyone has insight on this, I world appreciate hearing it... Thanks - Gabe -Original Message- From: [EMAIL PROTECTED] Sent: Wednesday, November 03, 2004 8:58 AM To: Mysql General (E-mail) Subject: Production instances down - MySQL got signal 11 Hi all I am on Solaris 8 using mysql.4.0.16 standard. My instances crashed with the following in the .err file: 041103 07:44:16 mysqld started 041103 7:44:17 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 4 3285189691 InnoDB: Doing recovery: scanned up to log sequence number 4 3285189691 InnoDB: Fatal error: cannot allocate 2213656 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 371642468 bytes. Operating system errno: 11 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. 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=16777216 read_buffer_size=131072 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 041103 07:44:18 mysqld ended I have about 2 gig of filesystem space left. I don't believe that to be an issue. However, I was able to get it up for about 15 minutes when I purged some old binary logs. Any help would be greatly appreciated. Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- 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]
mysqldump and binary logs
Hi All I have databases that are writing binary logs that I back up daily using mysqldump. I was wondering what is the best way to determine which binary logs have been created after the mysqldump, in the situation where I might do some restore that will involve the logs. Thanks Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Two versions on same server?
Yes -Original Message- From: Jim McAtee [mailto:[EMAIL PROTECTED] Sent: Thursday, September 23, 2004 3:44 PM To: [EMAIL PROTECTED] Subject: Two versions on same server? Can I run two different versions of MySQL on the same server? I've got a commercial application for which the vendor will only support MySQL 3.x and makes no guarantees if running MySQL 4. But I'd like to migrate a number of our own web applications to MySQL 4. My choices are take my chances with the other app, run MySQL on a second machine (not an option at the current time) or else run two instances of MySQL, different versions, both on the same machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Message Lock wait timeout exceeded; Try restarting transaction
Hello All
V4.0.16 on Sun Sparc 5.8
Innodb_lock_wait_timeout = 50.
A application using jboss got this error a few days ago. The SQL being used, AFAIK,
was a simple delete from table where date = {date}.
I am trying to determine why this happened. Searching back in the MySQL lists
archives, I was unable to find anything that I thought could help.
Any ideas of troubleshooting this problem would be appreciated.
Thanks - Gabe
There are no problems, only solutions.
Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Message Lock wait timeout exceeded; Try restarting transaction
DVP - Thanks...
I created a cron job to run show innodb status every minute and dump it to a file. In
additional I turned on innodb_lock_monitor. Hopefully, if this happens again, I will
have a snapshot of the situation.
It's still a bit frustrating not being able to see what was the cause when it happened
originally.
-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
Sent: Monday, September 13, 2004 2:49 PM
To: Tucker, Gabriel; 'Mysql General (E-mail)'
Subject: RE: Innodb Message Lock wait timeout exceeded; Try restarting
transaction
Basically the application can get this message because another process has a
lock on the rows that the delete needs to cover for a time period then your
lock_wait_timeout. Is there some cron process? Is the table index properly?
What your average query transaction?
Active your slow query log to see what queries are taking a long time to
execute and are covering many rows-look for table scans.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: Monday, September 13, 2004 9:35 AM
To: Mysql General (E-mail)
Subject: Innodb Message Lock wait timeout exceeded; Try restarting
transaction
Hello All
V4.0.16 on Sun Sparc 5.8
Innodb_lock_wait_timeout = 50.
A application using jboss got this error a few days ago. The SQL being
used, AFAIK, was a simple delete from table where date = {date}.
I am trying to determine why this happened. Searching back in the MySQL
lists archives, I was unable to find anything that I thought could help.
Any ideas of troubleshooting this problem would be appreciated.
Thanks - Gabe
There are no problems, only solutions.
Gabe Tucker
Bloomberg LP
(609) 750 6668 - P
(646) 268 5681 - F
--
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]
Backing up directly to tape.
Hello All I have been searching the archives and was unable to find an answer. I need the ability to backup MySQL instances directly to a tape device. Currently, I run a mysqldump to disk and have legato pick up the file. As I get to some larger databases, hundred's of gigs, and higher transaction rates, I will need an online solution that goes directly to a tape device. Currently we are using MySQL table types, though this may change. Thanks - Gabe There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Remotely dumping data to a file
Hi All I would like to dump certain data remotely from a table. I am unable to use mysqldump because I do not want to dump the entire table. The select into outfile will not work when the connection is remote. I saw in the manual that mysql -e would do what I want. However, that is not true. The output from this is formatted, I want it unformatted, like the mysqldump command. I could take the output from the mysql -e command and formatted the way I would like. Before doing this, I wanted to know if there is another option. Thanks! There are no problems, only solutions. Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Dual Master Configuration
Hi Your cnf file just needs to have log-bin and unique server-id's. Then use the CHANGE MASTER TO commands on each. So each will be a slave to the other. That's a brief summary. Good luck. Gabe -Original Message- From: Free Grafton - CCB [mailto:[EMAIL PROTECTED] Sent: Monday, May 24, 2004 3:29 PM To: [EMAIL PROTECTED] Subject: Dual Master Configuration Can someone show me how a configuration file (my.cnf) would look to have dual MySQL masters. I know it can be done, as I have read about others using it. I simply can't figure out how to configure it myself. There is nothing on the MySQL site about having a server be a master and a slave to another master and I have been through about 2 hours of Google searches with nothing yet helpful. I don't need a lecture on the dangers of running dual masters. 99.4% of the data transfer on our servers are for read requests. Not too worried about data being written at the same time to different masters and getting out of sync. Thanks for your help. Free Grafton -- 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]
mysqldump and auto_increment
Hello I am using MySQL v4.0.18. I am using the mysqldump program to do backups. I was wondering how, if at all, does mysqldump preserver the auto_increment value? When using phpmyadmin, there is an option to preserve the auto_increment value and the resulting file has something like: ) TYPE=MyISAM COMMENT='Defines assignments' AUTO_INCREMENT=15 ; in the create table definition. When I run mysqldump and look at the resulting file, I do not see anything like this. Nor, can I find documentation on what phpmyadmin is doing. Thanks in advance! Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--read-only startup
Hello all: I am trying to setup a multi-master environment that looks like a master-slave. Let me explain. My system will have all the clients talk to one server. If that server goes down, then it will fail to another. I want the setup to be multi-master so that upon fail-over, the other servers in the chain will get the changes. I have no problem setting up the multi-master environment. I want to take it a step farther. I would like to set up the slaves in this multi-master as read-only. Then, once they become the master, I want to drop the read-only attribute. I see, however, that in order to make a slave read-only, I have to start the server with the --read-only switch. I then assume that if I want to drop this attribute, I must stop the instance and then start it without the --read-only. I am wondering if anyone has more insight into this dilemma? Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: --read-only startup
Sorry for the second post, I realized that my first post might not be clear... I want the failover and the switch from read-only to non read-only to be dynamic, no downtime. Thanks -Original Message- From: [EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 4:07 PM To: Mysql General (E-mail) Subject: --read-only startup Hello all: I am trying to setup a multi-master environment that looks like a master-slave. Let me explain. My system will have all the clients talk to one server. If that server goes down, then it will fail to another. I want the setup to be multi-master so that upon fail-over, the other servers in the chain will get the changes. I have no problem setting up the multi-master environment. I want to take it a step farther. I would like to set up the slaves in this multi-master as read-only. Then, once they become the master, I want to drop the read-only attribute. I see, however, that in order to make a slave read-only, I have to start the server with the --read-only switch. I then assume that if I want to drop this attribute, I must stop the instance and then start it without the --read-only. I am wondering if anyone has more insight into this dilemma? Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB - Foreign Key - Error 150.
Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Marvin I believe that is the problem with the restore. When I create the archive file using the mysqldump command and options previously listed, I get the create table in the order listed below and thus, the foreign key constraint is created on table cur_reject_tk_sum before the object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am using mysqldump? [see commands below] Or, is this a problem with how I am restoring the database? [Which, I create a default mysql database on its own port and then run from the prompt mysql --port= --socket= -p archive_file.sql Thanks again Gabe -Original Message- From: Marvin Wright [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 9:59 AM To: Tucker, Gabriel; Mysql General (E-mail) Subject: RE: InnoDB - Foreign Key - Error 150. Hi, Are you creating them in the correct order ? object_type must exist before you can create cur_reject_tk_sum otherwise the foreign key will give errors. Marvin -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: 04 May 2004 14:46 To: Mysql General (E-mail) Subject: InnoDB - Foreign Key - Error 150. Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List
RE: InnoDB - Foreign Key - Error 150.
Luciano I am confused... As far as I can tell, the set foreign_key_checks=0; is used with the load data infile command. I am not using this command to restore the database. The mysqldump command creates a file with the data and schema. I restore it to a new instance that just has the mysql database using: unix$ mysql --port=port --socket=socket -p archive.sql So, I am not sure where I would insert this line nor if it would work. Should I insert it in the archive.sql from the previous example? Can I use the load data infile to restore the file I generated? Also, in the mysqldump command I used the --disable-keys command, believing this would correct the problem. Do you know why it does not? I know I asked a bunch of questions, thanks for whatever you can offer! Gabe -Original Message- From: Luciano Barcaro [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:03 AM To: Tucker, Gabriel Subject: Re: InnoDB - Foreign Key - Error 150. Put in your script: set foreign_key_checks=0; in the first line. mysqldump dumps tables in alphabetical order. Tucker, Gabriel wrote: Hello All: I am having a problem with both V4.0.16 and 4.0.18. Let me explain: I have a database with two InnoDB tables in v4.0.16. I backup up this database every night using the following command: mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt --port=$1 --socket=/bb/bin /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p /bb/bin/mysql/backups/archive_$1.sql I went to restore it the other day and I got this error: ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 150) I thought this might be a version issue, so I copied all the database files to another location, and started it using v4.0.18. I then dropped the foreign key constraint (no errors) and recreated it (no errors). I backed up the database. And when I went to restore it, I got the same error. The schema [after I deleted and re-added the constraint]: CREATE TABLE cur_reject_tk_sum ( cur_reject_tk_id int(11) NOT NULL auto_increment, contrib_swift_cd char(4) NOT NULL default '', object_id char(8) binary NOT NULL default '', tick_date date NOT NULL default '-00-00', object_type_cd smallint(6) NOT NULL default '0', num_received int(11) default NULL, num_rejected int(11) default NULL, num_spikes_contrib int(11) default NULL, num_spikes_compos int(11) default NULL, num_spread int(11) default NULL, num_filter int(11) default NULL, num_delayed int(11) default NULL, num_maybe int(11) default NULL, num_diff int(11) default NULL, num_bid_gt_ask int(11) default NULL, num_ask_no_bid int(11) default NULL, num_double_bid_ask int(11) default NULL, time_first_reject time default NULL, time_last_reject time default NULL, PRIMARY KEY (cur_reject_tk_id), UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id), UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date), KEY xif1cur_reject_tk_ (object_type_cd), KEY xie1cur_reject_tk_ (tick_date), KEY xie2cur_reject_tk_ (object_id), CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES `object_type` (`object_type_cd`) ) TYPE=InnoDB; -- -- Table structure for table `object_type` -- CREATE TABLE object_type ( object_type_cd smallint(6) NOT NULL default '0', descr varchar(254) default NULL, PRIMARY KEY (object_type_cd), UNIQUE KEY xpkobject_type (object_type_cd) ) TYPE=InnoDB; Any help would be appreciated! Thanks - Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB - Foreign Key - Error 150.
Oooops - I sent that last email before I read this one, please disregard. This appears that it will solve my problem. I will give it a try. Thanks for all that replied! Gabe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB - Foreign Key - Error 150. Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: InnoDB - Foreign Key - Error 150.
Victoria That seemed to work well, thank you. However, I received another error that I am not sure how to troubleshoot during the restore: ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The table 'cur_reject_tk_sum' is full What can I do here? Thanks - Gabe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB - Foreign Key - Error 150. Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: InnoDB - Foreign Key - Error 150.
Hooray! The last problem was b/c I did not have the same InnoDB settings in my cnf file. Again, thank you all for your time in this matter! Gabe -Original Message- From: [EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 11:29 AM To: Victoria Reznichenko; [EMAIL PROTECTED] Subject: RE: InnoDB - Foreign Key - Error 150. Victoria That seemed to work well, thank you. However, I received another error that I am not sure how to troubleshoot during the restore: ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The table 'cur_reject_tk_sum' is full What can I do here? Thanks - Gabe -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 04, 2004 10:35 AM To: [EMAIL PROTECTED] Subject: Re: InnoDB - Foreign Key - Error 150. Tucker, Gabriel [EMAIL PROTECTED] wrote: Marvin I believe that is the problem with the restore. When I create the = archive file using the mysqldump command and options previously listed, = I get the create table in the order listed below and thus, the foreign = key constraint is created on table cur_reject_tk_sum before the = object_type table has been created. Now my questions are: [1] Is the above scenario my problem? [2] Is so, how can I correct it? Is this a problem with the way I am = using mysqldump? [see commands below] Or, is this a problem with how I = am restoring the database? [Which, I create a default mysql database = on its own port and then run from the prompt mysql --port=3D = --socket=3D -p archive_file.sql Add to the beginning of the dump file command: SET FOREIGN_KEY_CHECKS = 0; and then restore tables. Or in the mysql client execute the following commands: SET FOREIGN_KEY_CHECKS = 0; SOURCE archive_file.sql; SET FOREIGN_KEY_CHECKS = 1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and NFS
Hi All I have read through some previous threads on this topic and I was unable to find any to answer my question... Can I install MySQL on a NFS mount and use its executables on multiple machines simultaneously? The data will be NOT be on a NFS mount, just the install. Thanks Gabe * The jokes on him, I'll be dead by then - HJS Gabe Tucker Bloomberg LP P 609 750 6668 F 646 268 5681 * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Privileging
Hello I have a user that is granted all privileges for their database. When they run a select * on a table in that database, the request fails after about 1000 records. When I run the same query as root, I have no problems. Consequently, I believe this is a permissioning problem. Since I have granted this user all privileges on their database, I am not sure what could be wrong. I appreciate any assistance. 4.0.16 - Unix Thanks - Gabe * The jokes on him, I'll be dead by then - HJS Gabe Tucker Bloomberg LP P 609 750 6668 F 646 268 5681 * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Privileging
Yes - the same machine... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 11:03 AM To: Tucker, Gabriel Cc: [EMAIL PROTECTED] Subject: Re: Privileging Are you running this test from the same machine for both users? Original Message On 2/27/04, 9:55:03 AM, Tucker, Gabriel [EMAIL PROTECTED] wrote regarding Privileging: Hello I have a user that is granted all privileges for their database. When they run a select * on a table in that database, the request fails after about 1000 records. When I run the same query as root, I have no problems. Consequently, I believe this is a permissioning problem. Since I have granted this user all privileges on their database, I am not sure what could be wrong. I appreciate any assistance. 4.0.16 - Unix Thanks - Gabe * The jokes on him, I'll be dead by then - HJS Gabe Tucker Bloomberg LP P 609 750 6668 F 646 268 5681 * -- 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: MAX_ROWS
Ok I think I am a bit confused on how the MAX_ROWS works... This is the result of SHOW TABLE STATUS \G: Name: gabe_test Type: MyISAM Row_format: Fixed Rows: 33 Avg_row_length: 5 Data_length: 165 Max_data_length: 327679 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2004-02-24 14:34:30 Update_time: 2004-02-24 14:37:43 Check_time: NULL Create_options: max_rows=3 Comment: What values of MAX_ROWS and AVG_ROW_LENGTH would I need so that I could limit this table to 3 [or n] number of records? How do I calculate this? Additionally, is there a better way, not using the OS, to limit the size of MyISAM tables? Thanks Gabe -Original Message- From: Keith C. Ivey [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 24, 2004 5:11 PM To: [EMAIL PROTECTED] Subject: Re: MAX_ROWS On 24 Feb 2004 at 22:01, Alison W wrote: Yes: MAX_ROWS is a *guidance* to the system in setting up the table and not a *limit* in any way. Well, it is a limit in one way. MySQL uses it (in MyISAM tables) to calculate the size of the pointer used for positions within the data file. If the data file becomes larger than can be handled by that size of pointer, then you can't add any more records to the table (unless you increase MAX_ROWS or AVG_ROW_LENGTH so that the pointer size is increased). -- 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]
MAX_ROWS
Hello OS: Unix MySQL:4.0.16 OS: Linux MySQL:4.0.17 I am trying to limit the size of the MyISAM tables in a MySQL database. As far as I can tell, my only option is using the MAX_ROWS parameter on a table. I am not looking to do this limit at the OS level. I wanted to test how the max_rows parameter works. I set it to 3 on a table. And, I was able to add 33 records [I stopped at this point]. It never prevented me from adding more records. The result is NOT what I expected. I expected that upon attempting to add the 4th record, I would have received some sort of error message. Am I mis-using this parameter? TIA Gabe * The jokes on him, I'll be dead by then - HJS Gabe Tucker Bloomberg LP P 609 750 6668 F 646 268 5681 * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
