Converting from myisam to innodb
I am looking to convert my myisam mysql database to an innodb. I seem to keep coming up with conflicting ideas on how to do this or even if I should! What are your thoughts? ...
innodb
Unexpected MySQL table engine change?
I've been adding tables to MySQL databases by copying & pasting from a file into the mysql command-line app. My procedure is to copy the CREATE TABLE statement from the file and paste it into my development database and then also paste it into the test database on another machine. I'm the only one using the test database. My CREATE TA...
Necessity of foreign key in this case(innoDB/mysql)
Hi, i recently migrated my whole DB from myisam to innodb. I am fairly new to all this so i have a question regarding the use of the foreign key. Lets say i have two tables: users, messages. users id (pr_key) name messages id (pr_key) user_id message The id fields are both auto incremented. So now in my queries i join th...
Missing InnoDb Engine
I just went to look over one my site's databases and noticed that all of the tables had been converted to MyISAM (they used to be InnoDB). What's more is that InnoDB seems to missing, along with BerkeleyDB, Federated, and others. A few months ago I upgraded from MySql 5.0 to 5.1.38. I can't imagine that I wouldn't have noticed if Inno...
I cannot convert myISAM to innodb.
I did a wipe and restore. I backedup my current innodb tables. (mysqldump) I loaded it into the database. For some reason...the tables are now all myisam instead of innodb...weird! I try to do: ALTER TABLE xxx ENGINE=innodb; And it doesn't do anything to any table. "Show table status" still is "MyISAM" mysql> alter table auth_use...
mysql innodb: describe table does not show columns references, what does show them?
CREATE TABLE accounts ( account_name VARCHAR(100) NOT NULL PRIMARY KEY ); CREATE TABLE products ( product_id INTEGER NOT NULL PRIMARY KEY, product_name VARCHAR(100) ); CREATE TABLE bugs ( bug_id INTEGER NOT NULL PRIMARY KEY, bug_description VARCHAR(100), bug_status VARCHAR(20), reported_b...
Making joomla use innoDB and support transaction
I am new to joomla and have just discovered that it uses myISAM database engine by default. I already searched the joomla forum and extension directory, and even in google but got no useful result. Since joomla is such a big community, I am not sure why nobody seems to have an answer to it. I would like to know if there is any well-kno...
How does MySQL store rows on disk?
I'm looking for documentation on how MySQL stores data on disk, in particular InnoDB and MyISAM. I took a database course back in college (and wrote a primitive database) so I have a general idea. I could ask a bunch of questions here about how various types are stored in rows, and how rows are stored on disk, or go whole-hog and just ...
Foreign Key reference in mysql innodb
Simply put: I have a table with 2 columns: one is username and other is nickname. I have another table where I have 2 columns: one is username and other is countNicknames. I want countNicknames to contain the number of nicknames each user has (and whenever I insert a new nickname to the table1, the value under table2.countNicknames will...
Should I always prefer MySQL InnoDB over MyISAM?
Someone just told me that InnoDB is much better than MyISAM. So when I create a table, should I always try to use InnoDB Engine instead of MyISAM? Or do both have it's big benefits? ...
Can I use InnoDB and MyISAM tables in ONE database?
Obviously both have their benefits. MyISAM is fast but may get currupted easily, InnoDB is slow but is more stable thanks to transactions and foreign keys. So it could be good to mix both engines in one database. If that's possible? ...
Auto_increment values in InnoDB?
I've been using InnoDB for a project, and relying on auto_increment. This is not a problem for most of the tables, but for tables with deletion, this might be an issue: AUTO_INCREMENT Handling in InnoDB particularly this part: AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB exe...
MySQL UPDATE statement batching to avoid massive TRX sizes
I am often writing datascrubs that update millions of rows of data. The data resides in a 24x7x365 OLTP MySQL database using InnoDB. The updates may scrub every row of the table (in which the DB ends up acquiring a table-level lock) or may just be scrubbing 10% of the rows in a table (which could still be in the millions). To avoid ...
Converting MyISAM to InnoDB. Beneficial? Consequences?
We're running a social networking site that logs every member's action (including visiting other member's pages); this involves a lot of writes to the db. These actions are stored in a MyISAM table and since something is starting to tax the CPU, my first thought was that it's the table locking of MyISAM that is causing this stress on the...
How can I add a column to the primary key of a MySQL InnoDB table?
I have tables foo and bar: create table foo(a int, b varchar(10), primary key (a)); create table bar(a int, c int, d int, primary key (a,c), foreign key(a) references foo(a)); Now I have a new column e that needs to participate in the primary key of bar. How can I do this? It seems I...
Is it possible to issue a select query in mysql without taking any read locks?
It seems that mysql select content (as opposed to e.g. count) queries always take at least a table read lock on myisam tables and a row read lock on innodb tables. Is there a way to issue a select content query in mysql (I could change table type if that's required) without having it to grab any locks? I don't mind if the data returned i...
Can´t INSERT values with Foreign Key in MySQL
I have a MySQL (5.1.42 on OsX) running. I added a Foreign Key with this sql statement: ALTER TABLE `Portal`.`Mitarbeiter_2_BlackBerry` ADD CONSTRAINT `fk_Blackberry` FOREIGN KEY (`id` ) REFERENCES `Portal`.`Blackberry` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `fk_Blackberry` (`id` ASC) But when i try to i...
Is it important to use InnoDB and transactions for an auction
I wanted to know how important it is to use InnoDB table and transactions for an auction website. I know that when there is payment involved the transactions are important, but in this case, there are only bids placed. The bids that are placed however are placed very quickly, maybe a few every second. I was wondering if i couldn't ju...
What's the impact of NULL on MySQL tables? (InnoDB)
Hello guys, Recently, I've been thinking if it's worth to have 1 table with perhaps a lot of NULL columns or if it's better to have more tables with no NULLs. I've heard that NULL isn't stored on InnoDB, so I was wondering if there is any downside or problem with having a bunch of rows with a lot of NULLs. I have always heard that commo...
InnoDB row level locking performance - how many rows?
I just read a lot of stuff about MyISAM and InnoDB as I have to decide which type to use. There was always mentioned 'row level locking'-support for InnoDB. Of course this only makes sense at a certain amount of rows. How many would that (circa) be? EDIT: Apparently I mis-worded my question. I know what table locking and row locking m...