Hello again!
I am trying to convert my tables to InnoDB, and i am getting an error...
Error: 1075
Incorrect table definition; there can be only one auto column and it must be
defined as a key
Now, I converted a table in my sandbox earlier this morning to do some
testing, and it worked fine
If you have a column defined as auto_increment, there must be a key on it.
This is true both in myisam and innodb.
If you need further help, please show us the full structure of the real table
you're operating on (not the one from your sandbox), the statement you run, and
the error message
: create table (myid int unsigned not null
auto_increment., unique key (myid));
but this is effectively a primary key
Only mostly true :-)
It *is* the same for MyISAM, but for InnoDB the primary key is special, as
that is the one that stores the data inline (clustered index). Additional
key.
mysql create table t (i int not null auto_increment, index(i)) engine
innodb;
Query OK, 0 rows affected (0.45 sec)
On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote:
Right, I saw the docs. I'm fine with creating an index on it, but the
only way I've successfully created a table
In innodb, is it possible to have an auto_increment field without
making it a (part of a) primary key? Why is this a requirement? I'm
getting the following error. Thanks in advance.
ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key
it's not an innodb thing:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Note
There can be only one AUTO_INCREMENT column per table, it must be indexed, and
it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if
it contains only positive values. Inserting
...@thefsb.org wrote:
it's not an innodb thing:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
Note
There can be only one AUTO_INCREMENT column per table, it must be indexed,
and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly
only if it contains only positive
2010/1/25 Yang Zhang yanghates...@gmail.com:
Right, I saw the docs. I'm fine with creating an index on it, but the
only way I've successfully created a table with auto_increment is by
making it a primary key. And I still don't understand why this
requirement is there in the first place.
The requirement is that it be indexed. The index need not be a primary key.
mysql create table t (i int not null auto_increment, index(i)) engine innodb;
Query OK, 0 rows affected (0.45 sec)
On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote:
Right, I saw the docs. I'm fine with creating an index
:
In innodb, is it possible to have an auto_increment field without
making it a (part of a) primary key? Why is this a requirement? I'm
getting the following error. Thanks in advance.
ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined
Hello!
I got all data files (ibdata1, ib_logfile, etc) recovevered from mine
old Debian 3.1 box (and i dont know MySQL version :( ). I want to get
that DB running again.
Can i copy these files to newer version of MySQL, and if i can - how?
Any commands, any parameters?
--
MySQL General Mailing
Hi Johny
Do you have the my.cnf configuration file ?? that can simplify things.
Carlos
On 1/20/2010 3:32 AM, Johny Brawo wrote:
Hello!
I got all data files (ibdata1, ib_logfile, etc) recovevered from mine
old Debian 3.1 box (and i dont know MySQL version :( ). I want to get
that DB running
Hi John,
The data files will give you some informations like log_file_size, mutliple
tablespace is being used or not. Although my.cnf can help you a lot. With
the above information, use it with newer version of mysql.
Krishna
On Wed, Jan 20, 2010 at 3:02 PM, Johny Brawo lydyh...@gmail.com
Hi Ortis,
How abt the hits or load i.e ( DML, DDL ) to the server.
My initial assessment after looking at you cnf file is
1) Calculate and place an appropriate value for innodb_buffer_pool_size
2) Reduse the innodb_thread_concurrency to 4 or 8.
and how about the no. of tables in the database and
Hi, I have a fairly small (data dir is 1.2GB) InnoDB database managed
by MySQL 5.4.3-beta on an 8-core x86_64 Linux box with 16GB RAM. I'd
like to use as much of the memory as possible, but despite specifying
(e.g.) --innodb-buffer-pool-size=30, mysql only ever takes up
374M of resident
2009/12/23 Ryan Chan ryanchan...@gmail.com:
Hey.
Back to few years ago, InnoDB require table level locking when
inserting auto-increment PK to the table, and Heikki said there will
be a fix.
Is this problem still exist now?
If you refer to this bug:
http://bugs.mysql.com/bug.php?id=16979
Hey.
Back to few years ago, InnoDB require table level locking when
inserting auto-increment PK to the table, and Heikki said there will
be a fix.
Is this problem still exist now?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http
uses in your system you can lower it a
little, but I think you are using this box only for MySQL and a 'good'
practice is to use 70/80% of system Ram ONLY for innodb buffer pool.
Cheers
Claudio
On 18 dec 2009 06:34, machiel.richards machiel.richa...@gmail.com wrote:
Good Morning all
Thank you very much.
This now explains a lot.
From: Claudio Nanni [mailto:claudio.na...@gmail.com]
Sent: 18 December 2009 10:05 AM
To: machiel.richards
Cc: mysql@lists.mysql.com
Subject: Re: RE: Innodb buffer pool size filling up
Machiel,
That is how it is supposed
-Original Message-
From: machiel.richards [mailto:machiel.richa...@gmail.com]
Sent: Friday, December 18, 2009 12:33 AM
To: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up
Good Morning all
QUOTE: We have a MySQL database where
.
Regards
Machiel
-Original Message-
From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com]
Sent: 01 December 2009 10:04 PM
To: 'machiel.richards'; 'Claudio Nanni'
Cc: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up
-Original Message-
From
von Baron Schwartz
Gesendet: Montag, 14. Dezember 2009 22:57
An: Lukas C. C. Hempel
Cc: mysql@lists.mysql.com
Betreff: Re: InnoDB Corrupted databases (innodb_force_recovery not working)
Lukas,
If you can't get innodb_force_recovery to work, then you might have to try
to recover the data
Hey there,
I have recently imported the database files from a crashed server and I am
currently trying to get the new server running with the old data.
However, after starting the MySQL Server, I only get the following error
message:
091214 20:51:46 mysqld started
InnoDB: The user
Lukas,
If you can't get innodb_force_recovery to work, then you might have to
try to recover the data with these tools:
http://code.google.com/p/innodb-tools/
Regards
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com
That is basically its use,
the buffer pool is the collection of all mysql innodb buffers,
and after warm up it goes to keep all cacheable data.
How big is your INNODB_BUFFER_POOL_SIZE ?
Cheers
Claudio
2009/12/1 machiel.richards machiel.richa...@gmail.com
There are no errors in the logs
...@gmail.com]
Sent: 01 December 2009 01:12 PM
To: machiel.richards
Cc: mysql@lists.mysql.com
Subject: Re: Innodb buffer pool size filling up
That is basically its use,
the buffer pool is the collection of all mysql innodb buffers,
and after warm up it goes to keep all cacheable data.
How big is your
The Innodb Buffer Pull usually follow a growth over time that resembles an
horizontal asintot (
http://www.maecla.it/bibliotecaMatematica/go_file/MONE_BESA/grafico.gif)
This to leverage all its size!
So should not be a problem!
Cheers
Claudio
2009/12/1 machiel.richards machiel.richa
-Original Message-
From: machiel.richards [mailto:machiel.richa...@gmail.com]
Sent: Tuesday, December 01, 2009 6:17 AM
To: 'Claudio Nanni'
Cc: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up
The size was at 2Gb and was recently changed to 3Gb in size during the last
Machiel:
We have a MySQL database where the
INNODB_BUFFER_POOL_SIZE
keeps on filling up.
Are you getting any errors or just noticing the buffer
pool is full?
I saw some error messages about the buffer pool size
becoming a problem if the fscync is slow. Do you see
any more
...@jammconsulting.com]
Sent: 01 December 2009 08:55 AM
To: mysql@lists.mysql.com
Subject: RE: Innodb buffer pool size filling up
Machiel:
We have a MySQL database where the
INNODB_BUFFER_POOL_SIZE
keeps on filling up.
Are you getting any errors or just noticing the buffer
pool is full?
I saw
I am using the innodb storage engine for a table that is used for a lot
of SELECT's on columns that are defined as indexes. I have not enabled
the query cache as of now since the innodb buffer pool already caches
data and index information for InnoDB tables. So my question is - is
the query
individual query once and only once, you'll reap the benefits of it.
On Thu, Nov 12, 2009 at 10:00 AM, Aveek Misra ave...@yahoo-inc.com wrote:
I am using the innodb storage engine for a table that is used for a lot of
SELECT's on columns that are defined as indexes. I have not enabled
Hello,
I am getting an error
#HY000Incorrect string value: '\xE9l\xE9tra...' for column '
Not quite sure why. hex E9 is a lower case e acute found in CP1252
and googling seems to tell me that the latin1 charset that I have set for the
table should display this OK.
mysql V 5.0.54a on Redhat
Raj,
Yup. It's that bug.
I got the row size to below 8K and the insertion takes place fine.
Thanks for pointing me in the right direction.
Kyong
On Wed, Oct 14, 2009 at 10:31 AM, Raj Shekhar rajl...@rajshekhar.net wrote:
Kyong Kim kykimdba at gmail.com writes:
For sure all of our columns
Kyong Kim kykimdba at gmail.com writes:
For sure all of our columns combined do not exceed 64K. We're using
latin 1 character set.
I don't think we would be running into the 8K limit on row length
since the culprit seems to be data being inserted into VARCHAR(255)
column.
Can you show us
We have an InnoDB table on MySQL 5.0.
We recently encountered an this error during a multirow insert(200 rows).
We identified the data causing it and it's a a series of long strings
exceeding the VARCHAR(255) columns into which they're being inserted.
I've been looking at the InnoDB restriction
InnoDB Internals: InnoDB File Formats and Source Code Structure
http://forge.mysql.com/wiki/InnoDB_Internals:_InnoDB_File_Formats_and_Source_Code_Structure
This Thursday (October 1st, 14:00 UTC - one hour later than usually),
Calvin Sun will give a session on InnoDB Internals: InnoDB File Formats
perform some
SELECT operation, but not to manipulate data - just to check for existence.
These threads act every 2 minutes.
The purging thread issues a DELETE (which, to my understanding, JPA
translates directly to a DELETE statement consistent with the underlining
db. In my case, MySQL InnoDB tables
Maybe one of you experts know the answer.
I have a Innodb database that I want to back up. Is there a free tool to
do this?
mysqlhotbackup is a paid tool, is that the only one available?
If I do a mysqldump of the innodb databse, will I be avail to uploaded into
a myisam
database
for
it!
-Original Message-
From: Néstor rot...@gmail.com
Date: Mon, 14 Sep 2009 07:44:25
To: mysql@lists.mysql.com
Subject: HOW TO Backup a mysql innodb on windows?
Maybe one of you experts know the answer.
I have a Innodb database that I want to back up. Is there a free tool to
do
If I may,
If you have foreign keys on your InnoDB, you can still import your
data to MyISAM but foreign keys will be lost. Otherwise, the data
will load just fine.
- michael dykman
On Mon, Sep 14, 2009 at 11:14 AM, Todd Lyons tly...@ivenue.com wrote:
On Mon, Sep 14, 2009 at 7:44 AM, Néstor
: Mon, 14 Sep 2009 07:44:25
To: mysql@lists.mysql.com
Subject: HOW TO Backup a mysql innodb on windows?
Maybe one of you experts know the answer.
I have a Innodb database that I want to back up. Is there a free tool
to
do this?
mysqlhotbackup is a paid tool, is that the only one available
-
From: Néstor rot...@gmail.com
Date: Mon, 14 Sep 2009 07:44:25
To: mysql@lists.mysql.com
Subject: HOW TO Backup a mysql innodb on windows?
Maybe one of you experts know the answer.
I have a Innodb database that I want to back up. Is there a free tool to
do this?
mysqlhotbackup is a paid tool
On Mon, Sep 14, 2009 at 7:44 AM, Néstor rot...@gmail.com wrote:
Maybe one of you experts know the answer.
I have a Innodb database that I want to back up. Is there a free tool to
do this?
mysqlhotbackup is a paid tool, is that the only one available?
You can also use the free tool from
On Mon, Sep 14, 2009 at 8:28 AM, Michael Dykman mdyk...@gmail.com wrote:
If I may,
If you have foreign keys on your InnoDB, you can still import your
data to MyISAM but foreign keys will be lost. Otherwise, the data
will load just fine.
Very good point. My comment was based on the possibly
have a Innodb database that I want to back up. Is there a free tool to
do this?
mysqlhotbackup is a paid tool, is that the only one available?
If I do a mysqldump of the innodb databse, will I be avail to uploaded into
a myisam
database and will it work?
Thanks,
Nestor
--
- michael
2009/9/13 Dan Nelson dnel...@allantgroup.com
In the last episode (Sep 12), Arthur Meeks Meeks said:
I have a database with about 1000 tables and 150GB. I have done a simple
for f in $(cat tables); do mysql -uuser -ppassword database_name -e
alter table $f engine=InnoDB; ; done
I took
Hello,
I have a database with about 1000 tables and 150GB. I have done a simple
for f in $(cat tables); do mysql -uuser -ppassword database_name -e alter
table $f engine=InnoDB; ; done
I took about 3 hours and everything went fine, but I just realised that the
same database in another server
In the last episode (Sep 12), Arthur Meeks Meeks said:
I have a database with about 1000 tables and 150GB. I have done a simple
for f in $(cat tables); do mysql -uuser -ppassword database_name -e
alter table $f engine=InnoDB; ; done
I took about 3 hours and everything went fine, but I just
are in place?
This is on the InnoDB engine.
I was curious if there are any problems where doing a read on a large
dataset has huge problems down the road. Let's assume the server is a quad
core with 4 GB of RAM. Surely it shouldn't have a *huge* effect?
Sincerely,
Suhail Doshi
(in that they
return
only a few rows since only a few match) and the proper indexes are in
place?
This is on the InnoDB engine.
I was curious if there are any problems where doing a read on a large
dataset has huge problems down the road. Let's assume the server is a quad
core with 4 GB of RAM
a few match) and the proper indexes are in place?
This is on the InnoDB engine.
I was curious if there are any problems where doing a read on a large
dataset has huge problems down the road. Let's assume the server is a quad
core with 4 GB of RAM. Surely it shouldn't have a *huge* effect?
Sincerely
Hi, Martijn, Gavin.
SHOW INNODB STATUS gave me helpful messages like following:
LATEST FOREIGN KEY ERROR
090821 12:53:18 Error in foreign key constraint of table test_fk/tbl1:
FOREIGN KEY (`col1` , `col2` , `col3` )
REFERENCES
Hi,
I can't create InnoDB table with foreign key constraints using more than 3
colmuns.
When I create table `test_fk`.`tbl1`, it gives me:
Can't create table 'test_fk.tbl1' (errno: 150)
why? CREATE TABLE syntax looks perfectly right to me.
Any suggestions are welcome.
Thank you,
wabi
Hi, All,
I can't create InnoDB table with foreign key constraints using more than 3
colmuns.
When I create table `test_fk`.`tbl1`, it gives me:
Can't create table 'test_fk.tbl1' (errno: 150)
why? CREATE TABLE syntax looks perfectly right to me.
Any suggestions are welcome.
Thank you
Run:
SHOW ENGINE INNODB STATUS \G
And look for the LATEST FOREIGN KEY ERROR section. It'll explain the reason
for the (errno: 150) message.
Regards,
Gavin Towey
-Original Message-
From: wabiko.takuma [mailto:wab...@sysrdc.ns-sol.co.jp]
Sent: Friday, August 14, 2009 3:35 AM
To: mysql
key updates.
It's definitely a tradeoff.
We're reasonably certain that we'll see a lot of ordered bulk inserts.
It ran counter to the results that we were seeing so I had to verify
that InnoDB always clusters by primary key regardless of the position
of the auto increment column in the primary
We have a multi-column primary key with an auto-increment column as
the 3rd column in the primary key in InnoDB.
Is there a requirement to have the auto-increment column as the
leftmost column in the primary key in order for InnoDB to cluster by
the multi-column primary key?
I don't believe
.
We're reasonably certain that we'll see a lot of ordered bulk inserts.
It ran counter to the results that we were seeing so I had to verify
that InnoDB always clusters by primary key regardless of the position
of the auto increment column in the primary key.
Kyong
On Thu, Jul 30, 2009 at 7:08 PM
inserts.
It ran counter to the results that we were seeing so I had to verify
that InnoDB always clusters by primary key regardless of the position
of the auto increment column in the primary key.
Kyong
On Thu, Jul 30, 2009 at 7:08 PM, Michael Dykmanmdyk...@gmail.com wrote:
InnoDb storage
Hi, Guys
I think I got puzzled about the transaction isolation level and the
InnoDB consistent read implementation.
My understanding about the ANSI isolation level are:
1. READ-COMMITTED is to protect against Lost Updates, Dirty Reads, and
NOT protect against Nonrepeatable Reads and Phantoms.
2
Hi.
I think innodb will split these into many small pieces and then merge them
to execute.
On Thu, Jun 18, 2009 at 1:52 PM, Krishna Chandra Prajapati
prajapat...@gmail.com wrote:
Hi guys,
On MIS (management information system) server we have 16GB of physical
memory. 10GB has been allocated
Thanks, I am looking answer internally how the thinks work.
Regards,
Krishna
On Fri, Jun 26, 2009 at 2:33 PM, Moon's Father yueliangdao0...@gmail.comwrote:
Hi.
I think innodb will split these into many small pieces and then merge them
to execute.
On Thu, Jun 18, 2009 at 1:52 PM, Krishna
We have a composite primary key consisting of
column a, column b, column c. We don't have a lot of variation on
column a and it makes sense for us to cluster by a.
Our queries are
SELECT column c FROM table WHERE column a=something and column e=something.
By creating a composite secondary index on
Hello List,
how can i get table update_time using innodb engine?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
It's often said that NOT NULL column is preferable in terms of index
performance.
I was wondering exactly why and how this is so specifically to InnoDB.
It would be great if someone can shed light on this matter in some detail.
Kyong
--
MySQL General Mailing List
For list archives: http
Gainty wrote:
Mike-
MySQL should ALWAYS perform a commit or rollback e.g.
exec()
commit() or rollback()
The reason for this is from Page 419 of the
MySQL 5.0 Certification Study Guide bullet point #3:
During the course of a transaction, InnoDB may acquire row locks
AS IT DISCOVERS THEM
.
Part of me believes if I understood the circumstances of the deadlock, I
might be able to solve this issue (in part or in whole) at the DB level.
Any tips? I've included below data from the Innodb status output.
Thanks,
Mike
LATEST DETECTED DEADLOCK
Hi all ,
Am not much knowledgeable person in mysql , but i know the query and all,
But dont know the history and all about mysql ,
But i like to know ,
Can u please tell me ,
Here is difference what i know ,
innodb = suport concurrency , row level locking , rollback, commit
myisam = support
nik...@doppelganger.com (Nikita Tovstoles) writes:
We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED.
We use Hibernate and optimistic concurrency, so periodically concurrent write
attempts cause app-level Exceptions that trigger rollbacks (and then we retry
tx
We have a java-based webapp that talks to MySQL 5.1 INNODB in READ_COMMITTED.
We use Hibernate and optimistic concurrency, so periodically concurrent write
attempts cause app-level Exceptions that trigger rollbacks (and then we retry
tx). We've added app-level caching and turned down our tomcat
was with myisam but with a lot of concurrent queries (all
SELECTs) i get too many table locks.
so i changed it to an innodb table.
works great most of the time.
sometimes it seems to be too much, starting at about 500 concurrent queries
i see a huge amount of processes
taking about 3 minutes
Once your tables' engine are all of innodb, your configuration file has to
be changed to fit innodb's feature, not myisam.
On Tue, Apr 21, 2009 at 2:09 PM, living liquid | Christian Meisinger
c.meisin...@livingliquid.com wrote:
ah sorry... there are a few UPDATEs too but most is SELECTs
Hi there.
I've a small table with my daily banner hits.
1. version was with myisam but with a lot of concurrent queries (all SELECTs) i
get too many table locks.
so i changed it to an innodb table.
works great most of the time.
sometimes it seems to be too much, starting at about 500 concurrent
but with a lot of concurrent queries (all
SELECTs) i get too many table locks.
so i changed it to an innodb table.
works great most of the time.
sometimes it seems to be too much, starting at about 500 concurrent queries
i see a huge amount of processes
taking about 3 minutes to finish 'sending
problems, you
are doing more than just a lot of SELECTs. How many inserts, updates,
deletes are you doing? If you are doing a lot of updates, even InnoDB
will block if you are trying to update the same record across queries.
If you have a lot of querying in sending data state, check which
ones
hello,
assume the following table:
CREATE TABLE t (
id INT UNSIGNED auto_increment PRIMARY KEY,
c1 INT UNSIGNED NOT NULL,
c2 INT UNSIGNED NOT NULL,
c3 INT UNSIGNED,
UNIQUE (c1, c2, c3)
) engine = InnoDB;
Our first issue is that the UNIQUE constraint on (c1,c2,c3) does not
work in the case
， Shuly Avraham 写道：
Hi,
I need to rename a database having InnoDB tables.
MySQL version is: 5.0.24-standard - so I cannot use the 'mysqladmin
rename' option.
What would be the best approach for doing this?
Thanks,
Shuly.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com
Hi shuly,
1 Use innodb_file_per_table.
2 Create new database.
3 Take the dump of old database.
4 Restore in new database.
5 Drop old database.
On Mon, Apr 6, 2009 at 9:50 PM, Shuly Avraham sh...@cshl.edu wrote:
Hi,
I need to rename a database having InnoDB tables.
MySQL version is: 5.0.24
luck.
-
Ding Hao/Fire9 DB Architect
Emailmsngtalk: fire9di...@gmail.com
My Blog:http://www.fire9.cn
My Twitter: http://twitter.com/fire9
在 2009-4-7，上午12:20， Shuly Avraham 写道：
Hi,
I need to rename a database having InnoDB tables.
MySQL
Hi,
I need to rename a database having InnoDB tables.
MySQL version is: 5.0.24-standard - so I cannot use the 'mysqladmin
rename' option.
What would be the best approach for doing this?
Thanks,
Shuly.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
locks tables in Innodb
2009/3/12 Carl c...@etrak-plus.com:
I am still a little puzzled about how we could have a relatively large
set
of records (100,000+) and yet not cause any table to be locked as the
server
has only 8GB of memory.
What's the relationship you're implying between memory
Hi,
I am using your procedure on MyISAM tables now and works but RENAME does not
work with locked tables,
(anyway it is already an atomic operation)
=BARON
Try something like this:
create table new_table like old_table;
alter table new_table add
concurrency doesn't necessarily mean the older versions
that are being read from have to be entirely in memory.
InnoDB will lock on a query that doesn't use an index.
It shouldn't lock on a SELECT query, regardless of the indexes involved.
- Perrin
--
MySQL General Mailing List
For list
Dear Sir,
I am using Mysql 5 in windows system.
I have formatted my system and copy the data directory before formatting the
system.
After formatting the system I have installed Mysql5 and resorted the data
directory with the older one.
When I browsing the database then innodb
suggestions also.
Carl
- Original Message -
From: Brent Baisley brentt...@gmail.com
To: Carl c...@etrak-plus.com
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb
Ok, so you have 687 unique organization serial numbers. That's not
very unique
...@etrak-plus.com
Sent: Thursday, March 05, 2009 1:12 PM
Subject: Re: Select query locks tables in Innodb
Ok, so you have 687 unique organization serial numbers. That's not
very unique, on average it will only narrow down the table to 1/687 of
it's full size. This is probably the source of your
The book “High Performance MySQL” states the following about using LVM
snapshots with innodb tables: “All innodb files (InnoDB tablespace files
and InnoDB transaction logs) must be on a single logical volume
(partition).” Here is portion of a df command performed on one of our
hosts:
/dev
-Original Message-
From: Paul McCullagh [mailto:paul.mccull...@primebase.com]
Sent: Monday, March 09, 2009 6:34 PM
To: Mattia Merzi
Cc: MySql
Subject: Re: InnoDB deadlocks
Hi Mattia,
On Mar 9, 2009, at 6:21 PM, Mattia Merzi wrote:
Hi everyone,
I've got some problems with deadlocks
Hi there,
well, thanks for the hints regarding transaction-serialization
performance but, if you read my very first e-mail, I didn't mention
any kind of performance trouble, I just sometimes (once a *month*)
have to re-issue some db commands because of these
deadlocks, but 99.9% of the time I
Hi everyone,
I've got some problems with deadlocks on InnoDB tables.
On paragraph 13.6.8.10. How to Cope with Deadlocks
of the mysql 5.1 version, the last sentence states:
--
Another way to serialize transactions is to create an auxiliary
“semaphore” table that contains just a single
Hi Mattia,
On Mar 9, 2009, at 6:21 PM, Mattia Merzi wrote:
Hi everyone,
I've got some problems with deadlocks on InnoDB tables.
On paragraph 13.6.8.10. How to Cope with Deadlocks
of the mysql 5.1 version, the last sentence states:
--
Another way to serialize transactions
@lists.mysql.com
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb
I don't think it locks the tables. The behavior may be similar, but I
seriously doubt that's what's happening. Take a snapshot of SHOW
INNODB STATUS while this is going on. And use
ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
wrote:
A SELECT will/can lock
Message -
From: Baron Schwartz ba...@xaprb.com
To: Brent Baisley brentt...@gmail.com
Cc: Carl c...@etrak-plus.com; mysql@lists.mysql.com
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
2009/3/4 Carl c...@etrak-plus.com:
However, when I had all the pieces in the query
(copy attached), I could easily see it was locking tables using the Server
Monitor in Navicat.
I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS
query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley brentt...@gmail.com
wrote:
A SELECT will/can lock a table. It almost always does in MyISAM (no
insert/updates), almost never does in InnoDB. There is an exception to
every rule. The problem is most likely in the 107488 rows
I did check that all tables are Innodb.
I was using the Navicat Server Monitor because I know that when I see the
monitor reporting a status of locked during an attempted query, that user
comes to a complete halt until the lock is cleared (usually by the bad query
finishing.)
I will check
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect
Carl,
Locked status in SHOW PROCESSLIST and a table being locked are
different. There is a bug in MySQL that shows Locked status for
queries accessing InnoDB tables in some cases. What version of MySQL
are you using?
The table is not really locked, you're just seeing that as a side
effect
501 - 600 of 5209 matches
Mail list logo
