Server just hangs when executing a query
We had a bit of a scare yesterday when one of our scripts just hung indefinitely. We nailed it down to a query in the script. When we executed the query manually, it hung as well. We ended up having to restart MySQL which for some reason fixed it. Some background: We're running MySQL 4.0.25. Shortly before this we shut down many of our scripts because we were taking a snapshot for a replication slave. Does anyone have any ideas as to why this happened, and why restarting the server fixed it? I'm wondering if when we shut down our scripts one of them had a lock open and the lock just stayed open. This sounds far fetched but I don't really have any other theories as of now :/ thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication shattered
We had to rebuild the slave on our slave DB. After the raid got rebuilt replication broke. We tried to rebuild it from scratch by doing the following: - RESET MASTER (on master) - mysqldump -e --master-data --single-transaction --databases db1 db2 > dbout - on the slave: STOP SLAVE, RESET SLAVE, DROP DATABASE db1 and db2 - mysql < dbout - CHANGE MASTER TO... master info - SLAVE START The problem is now the slave is saying there are duplicate key entries. Im not sure how this is possible. Any thoughts ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld server crashed - UPDATED
Gleb, thanks for the forcing-recovery pointer, thats what we ended up using to recover the data for the corrupted table. After that we dropped the table and recreated it, and imported the data and everything seems fine for now. Here are the OS/MySQL details: brand new 64bit dual xeon w/ 6gigs of ram, running RHEL 3.2 64bit mysql 4.0.25 EMT 64bit thanks, M Gleb Paharenko wrote: Hello. Please provide information about MySQL and operating system versions. Include your configuration file. This link might be helpful in case you want to save your data: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html Mayuran Yogarajah wrote: Mayuran Yogarajah wrote: Here is a small portion of the error log: InnoDB: Error: trying to use a corrupt InnoDB: table handle. Magic n 13459851911327004931, table name 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=67108864 read_buffer_size=1044480 max_used_connections=1 max_connections=160 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1539454 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Is this equation out of date? It differs from the formula in the current documentation. This one doesn't take into consideration innodb_buffer_pool size or binlog_cache_size. M Initially I thought I was dealing with a memory problem but it seems now that some tables are corrupt. It looks like the trouble began at around 6am, which is when our backup script (which calls mysqldump to dump the databases) also runs. Has anyone had similar problems? Why would mysqldump cause corruption in the database? M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld server crashed - UPDATED
Mayuran Yogarajah wrote: Here is a small portion of the error log: InnoDB: Error: trying to use a corrupt InnoDB: table handle. Magic n 13459851911327004931, table name 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=67108864 read_buffer_size=1044480 max_used_connections=1 max_connections=160 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1539454 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Is this equation out of date? It differs from the formula in the current documentation. This one doesn't take into consideration innodb_buffer_pool size or binlog_cache_size. M Initially I thought I was dealing with a memory problem but it seems now that some tables are corrupt. It looks like the trouble began at around 6am, which is when our backup script (which calls mysqldump to dump the databases) also runs. Has anyone had similar problems? Why would mysqldump cause corruption in the database? M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld server crashed
Here is a small portion of the error log: InnoDB: Error: trying to use a corrupt InnoDB: table handle. Magic n 13459851911327004931, table name 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=67108864 read_buffer_size=1044480 max_used_connections=1 max_connections=160 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1539454 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Is this equation out of date? It differs from the formula in the current documentation. This one doesn't take into consideration innodb_buffer_pool size or binlog_cache_size. M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stoppin mysql/gave up waiting
We have MySQL running on a Redhat server (RHEL 3.2). We issued a service mysql restart yesterday and for some reason MySQL didn't shut down properly. The init script said it "gave up waiting" and deleted the PID file anyway. Since we issued a restart, I suspect a second copy of MySQL got started. InnoDB complained that it was not shut down normally and started doing recovery. We let the recovery process finish, stopped the second instance of mysql and killed the first instance (using kill -9). The server seems OK now (luckily its not in production yet), but I don't know what would cause MySQL to not shut down properly. Has this happened to anyone else? Please let me know if you have any theories or suggestions. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
certified binaries
Does anyone know if there are certified binaries for 4.0.x ? I think I read somewhere that they will have it for 4.1 and later for 5 only. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql x86 64bit?
Mayuran Yogarajah wrote: Is it just me or are there no RPMS for x86 64bit? Does this mean I am stuck using the 32bit version? This machine has 16gigs of ram and the 32bit version won't be able to make use of all of it. thanks, M Sorry Ignore this, Intel EM64T rpm works just fine. M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql x86 64bit?
Is it just me or are there no RPMS for x86 64bit? Does this mean I am stuck using the 32bit version? This machine has 16gigs of ram and the 32bit version won't be able to make use of all of it. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show innodb status
Does anyone how to interpret the output of 'SHOW INNODB STATUS' ? It prints quite a bit of stuff but I haven't been able to find any documentation explaining what everything means. Specifically: Total memory allocated 462835256; in additional pool allocated 1385472 Buffer pool size 24576 Free buffers 24512 Database pages 64 How does it get these numbers? I've set innodb_buffer_pool_size to 384M and 20M for additional_mem_pool_size. thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB memory usage clarification
The following are from the InnoDB configuration page: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is < 2GB *Warning:* On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. | glibc| may allow the process heap to grow over thread stacks, which crashes your server. Can someone please explain what this means. We have a 32bit Linux x86 server with 16gigs of ram. Because it is 32bit and not 64bit we cant really make much use of all the ram. I am wondering which values I can safely increase without crashing the server. Here are some of the parameters we are using in our conf file: thread_concurrency = 16 table_cache = 512 innodb_buffer_pool_size = 1000M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 100M innodb_log_buffer_size = 8M From SHOW INNODB STATUS: BUFFER POOL AND MEMORY -- Total memory allocated 462835472; in additional pool allocated 3569664 Buffer pool size 24576 Free buffers 0 Database pages 23956 Modified db pages 11531 Free buffers is 0. Someone mentioned that because its a quad xeon each CPU would have 2gigs of ram to work with. Does this mean that I can set the innodb buffer pool much higher ? any feedback is welcome. thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql table structures
I am looking for an application that can connect to a mysql db or use an sql file and create html documents describing tables in a database and their column types, foreign keys, primary keys etc... Does anyone know of such an app ? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
show status - questions variable
Questions: The number of queries that have been sent to the server. Is this the number of queries since the mysql installation, or the number of questions since the last reboot ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT on duplicate UPDATE?
Yves Goergen wrote: Hi, I can vaguely remember there was something like "INSERT... on duplicate key UPDATE..." in MySQL, but the documentation search is almost as useful as I'm used to - it cannot tell me anything about this. Can you please? How does this work, what's the syntax? Is this ANSI-SQL standard (or compatible to other DBMS)? Or is it even documented? Its described in the INSERT syntax, and is available as of v4.1.1. http://dev.mysql.com/doc/mysql/en/INSERT.html M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Huge InnoDB data files (again)
Currently we have one way master to slave replication setup. The master has 2 innodb data files, the second has now grown to 50gb+. The slave's innodb data files are less than 2 gigs. How is this possible? They are both storing the exact same data. Is there some way to trim the 50gb+ file down ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
huge innodb data files
Our DB in production currently has 2 innodb data files, the second one (which is marked autoextend) has now grown past 26 gigs. We are experiencing weird speed problems with one of the tables. Even though there are no rows in this table, performing any kind of select takes about 2 minutes to execute. Has anyone had a similar problem before ? What can I do to speed up queries to this table ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb data file question
This example is from the manual: innodb_data_file_path=ibdata1:10M:autoextend:max:500M My question is, what happens when ibdata1 extends and hits 500M? If that is the only data file configured, will MySQL crash ? thanks, Mayuran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
release all mysql locks
Is there any command I can issue to release ALL locks held by any/all transactions ? I know that restarting the server does this, but is there a way to do this without restarting ? thanks, Mayuran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb table definitions
From the MySQL docs: Each |MyISAM| table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition. The data file has an `.MYD' (MYData) extension. The index file has an `.MYI' (MYIndex) extension. My question is, why does MySQL create a .frm (table definition) for a table if that table is of type InnoDB. For example, consider the following: use test; CREATE TABLE mytest ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL ) TYPE=InnoDB; This created inside the 'test' directory: mytest.frm Can anyone provide a reason for this. thanks, Mayuran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql...transaction question
bruce wrote:
hi..
i'm trying to understand if there's a difference/better reason for doing
transactions using either of the following psuedo approaches...
approach 1 does the commit inside the eval block, whereas approach 2 has the
commit outside the eval block... i've seen sample code with transactions
handled both ways...
approach 1:
eval
{
$dbh->do("do something");
# got this far means no errors
# commit
$dbh->commit();
};
# check errors/rollback
if ($@)
{
$dbh->rollback();
}
approach 2:
eval
{
$dbh->do("do something");
};
# check errors/rollback
if ($@)
{
$dbh->rollback();
}
else
{
# commit
$dbh->commit();
}
any comments/criticisms/thoughts/etc...
thanks...
-bruce
I think the commit needs to be inside the eval, because
thats when problems might arise. Also, I assume that you
at some point set AutoCommit = 0. I usually do transctions
like this:
sub test {
eval { $dbh->begin_work };
if ($@) {
print "begin transaction failed: $@";
return;
}
eval {
.. do some stuf ..
$dbh->commit;
};
if ($@) {
eval { $dbh->rollback };
print "transaction failed: $@";
} else {
print "data committed successfully.\n";
}
}
hope this helps,
Mayuran.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb - next key locking
I have a perl script which fork()'s many children and each child is updating a table, and each child is inserting/updating DIFFERENT rows - I split up the work so that no two children try to update the same row so that no child has to wait for any locks to be released. The problem is, I am getting lock wait timeout's still, its not consistant - sometimes it happens sometimes it does not. It might be due to next key locking. Anyhow, has anyone tried to do something similar before ? How can I have all the children update/insert without running into lock problems. I am using the latest production version of MySQL and all the tables are InnoDB. Any input is welcome. thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb log
When I do a SHOW INNODB STATUS i see a query which is
waiting for a lock to be released, but innodb status
doesnt show the whole query, the end of it got truncated.
it looks something like:
INSERT INTO test(col1, .., col10) VALUES ('9',
and just stops.
is it possible to see the entire query, im having some
lock wait timeout issues and it would help alot if I can
see the entire query.
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHERE clause problem
This is my table: mysql> desc testing; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | date | date | YES | | NULL| | +---+--+--+-+-+---+ Here are the values: mysql> select *from testing; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | | 2004-01-01 | ++ Here is my question: The following query returns incorrect rows and I dont understand why. mysql> SELECT * FROM testing WHERE MONTH(date) = (MONTH(NOW()) OR MONTH(NOW())-1); ++ | date | ++ | 2004-01-01 | ++ I wanted the query to return the rows whose months are from this month or last month. This query however, returns the correct rows: mysql> SELECT * FROM testing WHERE MONTH(date) = MONTH(now()) OR MONTH(date) = MONTH(NOW())-1; ++ | date | ++ | 2004-04-10 | | 2004-04-15 | ++ Why does the first one not work? its shorter to type :) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb lock information
How can I go about getting information about lock information? I looked at the InnoDB status screen but it doesnt say a whole lot. Im getting alot of problems with lock wait timeouts. What I want to know is, what is obtaining the locks, what user is obtaining the locks and with what query/update/insert statement. I also had alot of problems with deadlocks but that is solved now. This all came up because I had to fork() and have many children update the database at the same time. I searched the web alot for examples of how to do DB interactions while forking but I didn't find much information/examples. Any advice is welcome. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql/innodb configuration
I would like to optimize the configuration settings for this beast of a machine, here are the specs: Quad Xeon 3ghz (4x2 = 8 cpus), 512 cache 16 gigs ram running Redhat Enterprise 3.0 AS All tables are InnoDB. I read this warning in the MySQL documentation: *Warning:* On GNU/Linux x86, you must be careful not to set memory usage too high. |glibc| will allow the process heap to grow over thread stacks, which will crash your server. But at the same time it says: # Set buffer pool size to 50-80% of your computer's memory, # but make sure on Linux x86 total memory usage is < 2GB Does this mean that MySQL wont make use of the 16gb it has total ? I had to set the value to 1G to make it even start up. What other parameters can I tweak in the conf for maximum performance ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: case sensitivity of table names
Paul DuBois wrote: At 12:22 -0500 12/17/03, Mayuran Yogarajah wrote: I am using case sensitive table names when I create tables like : CREATE TABLE MyTest; If I want to do a select from this table, I have to do SELECT * FROM MyTest, not SELECT * FROM mytest. How can I make it so that the table name is still MyTest but selects work with mytest ? You can set the lower_case_table_names server variable to 1. Then table names will not be treated as case sensitive, and you can write them in any lettercase in your queries. Two points to note, though: - Before setting the varable, rename all your tables to lowercase. Otherwise they won't be recognized properly when you set the variable. (The way it works is that if causes the server to lowercase the names of new tables when they are created.) - Although you will be able to refer to tables using any lettercase, *within a given query*, you must refer to the table consistently. See http://www.mysql.com/doc/en/Name_case_sensitivity.html for an example. Is it possible to change the variable lower_case_table_names from mysql commandline? I tried to change it by doing this : mysql> SET lower_case_table_names=1; and got the error : ERROR 1193: Unknown system variable 'lower_case_table_names' Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
case sensitivity of table names
I am using case sensitive table names when I create tables like : CREATE TABLE MyTest; If I want to do a select from this table, I have to do SELECT * FROM MyTest, not SELECT * FROM mytest. How can I make it so that the table name is still MyTest but selects work with mytest ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key errors
Mayuran Yogarajah wrote: Mayuran Yogarajah wrote: The following is a transaction from MySQL: smysql> show tables; Empty set (0.00 sec) mysql> CREATE TABLE Userlist ( -> UserID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -> Status VARCHAR(1) NOT NULL, -> EmailAddress VARCHAR(64) NOT NULL, -> Password VARCHAR(32) NOT NULL, -> FirstName VARCHAR(64) NOT NULL, -> LastName VARCHAR(64) NOT NULL, -> CompanyName VARCHAR(64) NOT NULL, -> Address1 VARCHAR(128) NOT NULL, -> Address2 VARCHAR(128) DEFAULT '', -> City VARCHAR(128) NOT NULL, -> Zip VARCHAR(10) NOT NULL, -> Country VARCHAR(2) NOT NULL, -> Phone VARCHAR(24) NOT NULL, -> Fax VARCHAR(24) DEFAULT '', -> LastLogin DATE NOT NULL -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE Sitelist ( -> UserID MEDIUMINT UNSIGNED NOT NULL, -> SiteID INT UNSIGNED NOT NULL AUTO_INCREMENT, -> Status CHAR(1) NOT NULL, -> Name CHAR(64) NOT NULL, -> URL VARCHAR(255) NOT NULL, -> Description VARCHAR(255) NOT NULL, -> MonthlyUnique INT UNSIGNED NOT NULL DEFAULT 70, -> FrequencyCap MEDIUMINT UNSIGNED NOT NULL, -> INDEX (UserID), -> FOREIGN KEY (UserID) REFERENCES Userlist(UserID) ON UPDATE CASCADE ON DELETE CASCADE, -> PRIMARY KEY (SiteID, UserID) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE Filters ( -> SiteID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -> INDEX (SiteID), -> FOREIGN KEY (SiteID) REFERENCES Sitelist(SiteID) ON UPDATE CASCADE ON DELETE CASCADE -> ) TYPE=InnoDB; ERROR 1005 (HY000): Can't create table './test/AdvertiserFilter.frm' (errno: 150) Does anyone know why this is happening? As far as I can tell there is no syntax errors in my table declarations. Any input is much appreciated. Thank you Sorry, that last error should read: ERROR 1005 (HY000): Can't create table './test/Filters.frm' (errno: 150) Thanks. Nevermind, it was conflicting column types :( Sorry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key errors
Mayuran Yogarajah wrote: The following is a transaction from MySQL: smysql> show tables; Empty set (0.00 sec) mysql> CREATE TABLE Userlist ( -> UserID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -> Status VARCHAR(1) NOT NULL, -> EmailAddress VARCHAR(64) NOT NULL, -> Password VARCHAR(32) NOT NULL, -> FirstName VARCHAR(64) NOT NULL, -> LastName VARCHAR(64) NOT NULL, -> CompanyName VARCHAR(64) NOT NULL, -> Address1 VARCHAR(128) NOT NULL, -> Address2 VARCHAR(128) DEFAULT '', -> City VARCHAR(128) NOT NULL, -> Zip VARCHAR(10) NOT NULL, -> Country VARCHAR(2) NOT NULL, -> Phone VARCHAR(24) NOT NULL, -> Fax VARCHAR(24) DEFAULT '', -> LastLogin DATE NOT NULL -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE Sitelist ( -> UserID MEDIUMINT UNSIGNED NOT NULL, -> SiteID INT UNSIGNED NOT NULL AUTO_INCREMENT, -> Status CHAR(1) NOT NULL, -> Name CHAR(64) NOT NULL, -> URL VARCHAR(255) NOT NULL, -> Description VARCHAR(255) NOT NULL, -> MonthlyUnique INT UNSIGNED NOT NULL DEFAULT 70, -> FrequencyCap MEDIUMINT UNSIGNED NOT NULL, -> INDEX (UserID), -> FOREIGN KEY (UserID) REFERENCES Userlist(UserID) ON UPDATE CASCADE ON DELETE CASCADE, -> PRIMARY KEY (SiteID, UserID) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE Filters ( -> SiteID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -> INDEX (SiteID), -> FOREIGN KEY (SiteID) REFERENCES Sitelist(SiteID) ON UPDATE CASCADE ON DELETE CASCADE -> ) TYPE=InnoDB; ERROR 1005 (HY000): Can't create table './test/AdvertiserFilter.frm' (errno: 150) Does anyone know why this is happening? As far as I can tell there is no syntax errors in my table declarations. Any input is much appreciated. Thank you Sorry, that last error should read: ERROR 1005 (HY000): Can't create table './test/Filters.frm' (errno: 150) Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
foreign key errors
The following is a transaction from MySQL: smysql> show tables; Empty set (0.00 sec) mysql> CREATE TABLE Userlist ( -> UserID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -> Status VARCHAR(1) NOT NULL, -> EmailAddress VARCHAR(64) NOT NULL, -> Password VARCHAR(32) NOT NULL, -> FirstName VARCHAR(64) NOT NULL, -> LastName VARCHAR(64) NOT NULL, -> CompanyName VARCHAR(64) NOT NULL, -> Address1 VARCHAR(128) NOT NULL, -> Address2 VARCHAR(128) DEFAULT '', -> City VARCHAR(128) NOT NULL, -> Zip VARCHAR(10) NOT NULL, -> Country VARCHAR(2) NOT NULL, -> Phone VARCHAR(24) NOT NULL, -> Fax VARCHAR(24) DEFAULT '', -> LastLogin DATE NOT NULL -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE Sitelist ( -> UserID MEDIUMINT UNSIGNED NOT NULL, -> SiteID INT UNSIGNED NOT NULL AUTO_INCREMENT, -> Status CHAR(1) NOT NULL, -> Name CHAR(64) NOT NULL, -> URL VARCHAR(255) NOT NULL, -> Description VARCHAR(255) NOT NULL, -> MonthlyUnique INT UNSIGNED NOT NULL DEFAULT 70, -> FrequencyCap MEDIUMINT UNSIGNED NOT NULL, -> INDEX (UserID), -> FOREIGN KEY (UserID) REFERENCES Userlist(UserID) ON UPDATE CASCADE ON DELETE CASCADE, -> PRIMARY KEY (SiteID, UserID) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE Filters ( -> SiteID MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -> INDEX (SiteID), -> FOREIGN KEY (SiteID) REFERENCES Sitelist(SiteID) ON UPDATE CASCADE ON DELETE CASCADE -> ) TYPE=InnoDB; ERROR 1005 (HY000): Can't create table './test/AdvertiserFilter.frm' (errno: 150) Does anyone know why this is happening? As far as I can tell there is no syntax errors in my table declarations. Any input is much appreciated. Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication/binary log
Diana Soares wrote:
Use "PURGE {MASTER|BINARY} LOGS TO 'log_name'" instead of "RESET
MASTER".
From the manual:
"
Deletes all the binary logs listed in the log index that are strictly
prior to the specified log or date. The logs also are removed from this
list recorded in the log index file, so that the given log now becomes
the first.
(...)
You must first check all the slaves with SHOW SLAVE STATUS to see which
log they are reading, then do a listing of the logs on the master with
SHOW MASTER LOGS, find the earliest log among all the slaves (if all the
slaves are up to date, this will be the last log on the list), backup
all the logs you are about to delete (optional) and purge up to the
target log.
"
http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html
That worked quite nicely, thank you :) Is there some reason why
MySQL keeps these log files ? Why wouldn't it delete them as a
new one got created?
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication/binary log
We are running MySQL 3.23 in production, and have replication setup in the following manner: There are two machines (m1 and m2). Replication is setup in a circular way. Both machines are master and slave, more specifically, m1 is master to m2 and m2 is master to m1. I checked today and saw that one of the machines had a bunch of binary log files (see below). I read in the MySQL documentation that you can delete the logs by issuing a RESET MASTER command. I am wonder how this will affect replication. Is this going to break replication in any way? Is it safe to simply delete the binary log files manually ? (Id prefer to do this). Any input would be helpful. Thank you. MySQL data directory: total 4388640 drwx--2 mysqlmysql4096 Aug 17 19:24 mysql drwx--2 mysqlmysql4096 Aug 17 19:46 test -rw-rw1 mysqlmysql 445 Aug 17 20:42 mw01-bin.001 drwxr-xr-x8 root root 4096 Aug 17 23:51 .. -rw-rw1 mysqlmysql 111 Aug 24 04:02 mw01-bin.003 -rw-rw1 mysqlmysql 308 Aug 24 04:02 mw01-bin.002 -rw-rw1 mysqlmysql 111 Aug 31 04:02 mw01-bin.005 -rw-rw1 mysqlmysql 244491 Aug 31 04:02 mw01-bin.004 -rw-rw1 mysqlmysql 111 Sep 7 04:02 mw01-bin.007 -rw-rw1 mysqlmysql 28177 Sep 7 04:02 mw01-bin.006 -rw-rw1 mysqlmysql7947 Sep 13 23:59 mw01-bin.008 -rw-rw1 mysqlmysql 111 Sep 14 04:02 mw01-bin.010 -rw-rw1 mysqlmysql3513 Sep 14 04:02 mw01-bin.009 -rw-rw1 mysqlmysql 111 Sep 21 04:02 mw01-bin.012 -rw-rw1 mysqlmysql30791885 Sep 21 04:02 mw01-bin.011 -rw-rw1 mysqlmysql 111 Sep 28 04:02 mw01-bin.014 -rw-rw1 mysqlmysql111270867 Sep 28 04:02 mw01-bin.013 -rw-rw1 mysqlmysql12105202 Sep 28 19:18 mw01-bin.015 -rw-rw1 mysqlmysql 111 Oct 5 04:02 mw01-bin.017 -rw-rw1 mysqlmysql38094517 Oct 5 04:02 mw01-bin.016 -rw-rw1 mysqlmysql 111 Oct 12 04:02 mw01-bin.019 -rw-rw1 mysqlmysql276605852 Oct 12 04:02 mw01-bin.018 -rw-rw1 mysqlmysql61917421 Oct 12 23:48 mw01-bin.020 -rw-rw1 mysqlmysql 111 Oct 19 04:02 mw01-bin.022 -rw-rw1 mysqlmysql101760652 Oct 19 04:02 mw01-bin.021 -rw-rw1 mysqlmysql 111 Oct 26 04:02 mw01-bin.024 -rw-rw1 mysqlmysql579578833 Oct 26 04:02 mw01-bin.023 -rw-rw1 mysqlmysql 479 Nov 2 04:02 mw01-bin.026 -rw-rw1 mysqlmysql844900359 Nov 2 04:02 mw01-bin.025 -rw-rw1 mysqlmysql 111 Nov 9 04:02 mw01-bin.028 -rw-rw1 mysqlmysql869670836 Nov 9 04:02 mw01-bin.027 drwx--2 mysqlmysql4096 Nov 10 21:15 Viper -rw-rw1 mysqlmysql 111 Nov 16 04:02 mw01-bin.030 -rw-rw1 mysqlmysql700865150 Nov 16 04:02 mw01-bin.029 -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.032 -rw-rw1 mysqlmysql 111 Nov 23 04:02 mw01-bin.031 -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.034 -rw-rw1 mysqlmysql 111 Nov 30 04:02 mw01-bin.033 -rw-rw1 mysqlmysql 86 Dec 7 01:23 mw01-bin.035 srwxrwxrwx1 mysqlmysql 0 Dec 7 01:25 mysql.sock -rw-rw1 mysqlmysql 570 Dec 7 01:30 mw01-bin.index -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.037 -rw-rw1 mysqlmysql 111 Dec 7 01:30 mw01-bin.036 drwxr-xr-x5 mysqlmysql4096 Dec 7 01:30 . -rw-rw1 mysqlmysql 63 Dec 7 01:49 master.info -rw-rw1 mysqlmysql861518654 Dec 8 17:53 mw01-bin.038 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb transactions
I did some tests earlier where I inserted 100,000 rows into a table (table definition below). First, I did it without using transactions and it took 243 seconds approximately. Then, I did the same test using transactions, and it took 28 seconds. I am using MySQL v4. Here is the table definition: CREATE TABLE users ( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, num1 FLOAT(9,2) NOT NULL DEFAULT 0.0, num2 FLOAT(9,2) NOT NULL DEFAULT 0.0, ) TYPE=InnoDB; I guess my question is, how can this be? I was lead to believe that using transactions would slow things down but the opposite appears to be happening. Can anyone offer an explanation as to why it took so much longer to do the inserts when not using transactions ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Send someone a database
michael johnson wrote: Dear All I want to send a MySQL database to a client by email. Which is the best way to do it? Michael Johnson Director BPEnet Humphrey Consulting Limited 13 Austin Friars London EC2N 2JX Tel +44(0)870 922 0247 Fax +44(0)1323 419554 email [EMAIL PROTECTED] URL www.bpenet.net Also in Dublin & Luxembourg Why not take a mysqldump of the database and email him that :P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL log files
How are you starting mysql? as a service? I dont think it keeps a log file by default. If you are not starting mysql as a service, start it with the --log-error=/var/log/mysqld --log-warnings flags. If you are starting it as a service, add those flags to your mysql file inside /etc/rc.d/init.d (line 148) Hope that helps. Admin-Stress wrote: Hello, How can I enable MySQL 4.0.16 log ? I just compiled and installed it in my RedHat 9.0 system, but I cant see any log files. I use default /etc/my.cnf from my-large.cnf. Sorry, if this question is too newbie, but I cant understand clearly reading MySQL manual. Thanks for helping me :) __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CHECK constraint
I created a table as follows: mysql> CREATE TABLE test ( -> age INT(3), -> CONSTRAINT CHECK (age > 0) -> ); Query OK, 0 rows affected (0.00 sec) I then inserted -1 into the table, which it shouldn't have let me do. mysql> insert into test values (-1); Query OK, 1 row affected (0.00 sec) mysql> select *From test; +--+ | age | +--+ | -1 | +--+ 1 row in set (0.01 sec) How can I enforce the CHECK constraint ? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Primary key
getting_out wrote: Good evening people. I'm trying to create a simple table via MySQl Navigator. The table il structured in this way dt_amtDateNot NullPrimary Key operINTNot NullPrimary Key amountDecimal(3,3)Null--- but when I choose "fire" it shows me a "multiple primary key defined" message and doesn't make me create the table. Isn't MySql able to handle multiple primary key or it's a navigator bug? In the same day I can receive data from different operators. Thanks in advance D. Ive never used MySQL navigator, but I think you should assign the primary keys in the same line, like so: dt_amtDateNot Null operINTNot Null amountDecimal(3,3)Null primary key (dt_amt, oper) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replicating blob
This is my current setup, two MySQL servers. One master, the other slave. Suppose I created a table with one column named 'image', which is of type BLOB. Now, if I inserted binary data from an image file (using perl or something) into that column on the master, how will replication be handled? because the data is binary. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
converting tables MyISAM to InnoDB
Hi, I recently upgraded from MySQL v3.23 to MySQL 4, and converted my tables from MyISAM to InnoDB by using the ALTER TABLE command. I did this for 16 tables. I know that MyISAM creates seperate data/index files for each table and InnoDB uses a single file for data and log. My question is, once Ive converted the tables from MyISAM to InnoDB, can I delete the .MYI and .MYD files for the tables ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
