I'm working on a project that requires lots of database inserts. I also need to be able to use something like full-text-search to retrieve the data. Is is possible to use 2 tables, with the same exact data, oen MyIsam and one InnoDb to achieve this effect? Write on the InnoDb, and somehow read off of the MyIsam? ...
Hi there, does anyone know a way to write a Doctrine migration to change the Table Engine of a MySQL Database from MyISAM to InnoDB? Can this be managed via Migrations or do I have to this the old way via SQL-Statements? ...
I am trying ti change the type in two tables of an innodb. The problem is that the values are a key and a foreign key. When I try to make the change I get the error #1025 error on rename Do I need to drop the foreign keys and then make the changes and then reapply the foreign key? ...
I'm working with a huge table which has 250+ million rows. The schema is simple. CREATE TABLE MyTable ( id BIGINT PRIMARY KEY AUTO_INCREMENT, oid INT NOT NULL, long1 BIGINT NOT NULL, str1 VARCHAR(30) DEFAULT NULL, str2 VARCHAR(30) DEFAULT NULL, str2 VARCHAR(200) DEFAULT NULL, str4...
I am forking some php scripts and I need to update a mysql row to track some progress. I read that I should use InnoDB for this but I coulnd't find any complete example I can understand. A friend told me he uses this php code: mysql_query("SET AUTOCOMMIT=0;"); mysql_query("START TRANSACTION;"); mysql_query("SET TRANSACTION ISOLATION LEV...
I have a complex query I need to run on a MySQL-server. On my home testing box, a mac, the query takes a reported 0.00 seconds and returns the reslut instantly. On my server however, an ubuntu box, the same query takes around 3.30 minutes, using the same data. The database uses InnoDB tables. The ubuntu box has a 64-bit kernel, and m...
I have some information that's stored like this: DROP TABLE IF EXISTS `demographics`; CREATE TABLE IF NOT EXISTS `demographics` ( `row_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `city_id` int(10) unsigned NOT NULL, `until_year` int(10) unsigned DEFAULT NULL, `population` int(10) unsigned NOT NULL, PRIMARY KEY (`row_id`) ) E...
Ok need some help, I have a system I'm creating that will allow users to purchase site credits and earn them, and with these credits order products, and access features that may be for sale. My issue is creating the orders and transactions tables. I have 2 separate order tables one for site credits (sitecredit_orders) and one for produc...
I have a table containing a foreign key and I know the column name of that foreign key. Is it possible, using this information, to determine the table to which that key links? The reason for this is that the foreign key is dynamic and so I cannot determine the linked table in advance. ...
We are doing an import of Magento-products into a CakePHP environment over SOAP. We have like 3.000 products and of course we want to keep the database clean of duplicate entrys. We use InnoDB as product-databse in CakePHP. We had different approaches: We set the validate var on the product model and want the product-id to be unique....
Is there any way to make MySQL tell you what version of the InnoDB plugin it's running? I know you can look at the documentation, but I'm trying to verify a potential bug if the plugin jives with the mysql version installed. mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_nam...
I need to use full text search but the tables that I am using in my web application use the INNODB engine, from what I read JDBC does not have support for Mysql triggers and I am supposed to deploy the project on the course server so installing tools such as sphinxs is not possible, could someone please suggest a method to perform a full...
I have a table with for columns, a smallint, an integer and two floats. Expected memory for each row is 14bytes (2+4+2*4). I have around 700,000 rows in my table. I've set no primary keys and indices. While the MyIsam type takes around 10MB (average of 15b for each row), the InnoDB type takes more than 30Mb (average of 44b). What am...
I am trying to add a self relation in an existing Innodb table here is table structure Table person person_id int (10) primary key not null auto increment, parent_id int (10) NULL default null, name varchar(30) When I use this command ALTER TABLE `person` ADD FOREIGN KEY ( `parent_id` ) REFERENCES `person` (`person_id`) ...
I have a "Lock wait timeout exceeded" error from MySQL that I can't reproduce or diagnose. I'm sure it's deadlock (as opposed to a transaction grabbing a lock then twiddling its thumbs), because my logs show that another process started at the same time, also hung, then continued when the first timed out. But normally, InnoDB detects d...
SHOW INNODB ENGINE STATUS shows me this and calls it deadlock: LATEST DETECTED DEADLOCK 100923 22:29:21 * (1) TRANSACTION: TRANSACTION 0 5335752, ACTIVE 0 sec, OS thread id 7992 inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 3 MySQL thread id 26, query id 14422 lo...
Does having separate file for each table improve InnoDB performance in MySQL. Are there any such performance tuning tips for MySQL ...
i have two question about msyql performance with my db using MyISAM engine : 1) what is smart way to solve the problem that when INSERT or UPDATE some rows in one table, many SELECT queries be hang on. 2) Is this easy to change from MyISAM to InnoDB with database is current running? 3) Why myISAM is still be default option of mySQL w...
Hi, I am currently trying to move my DB tables over to InnoDB from MyISAM. I am having timing issues with requests and cron jobs that are running on the server that is leading to some errors. I am quite sure that transaction support will help me with the problem. I am therefore transitioning to InnoDB. I have a suite of tests which ...
Hello all! http://img839.imageshack.us/img839/8241/stackoverflow.png I created a new databese. There are 2 tables class and teacher. There is a field class_leader in the table class related with teacher_id from teacher. [1] In PHPMyAdmin relationship looks like: [2] Table teacher contains some records. [3] When I add new records at ...