I start a transaction. Then I need to rollback it. Can I somehow get a list of the queries that get "discarded" this way? (ps: of course I can log them beforehand; I was wondering if this could be done in a more "natural" way) ...
InnoDB seems almost entirely superior but in what case should I go for MyIsam? Thanks in advance! ...
I want to migrate one of my database from myisam to innodb. The main reason is to allow my application to honor referential integrity. This is what I did Exported the current myisam database Dropped the database Switched to innodb database http://parasjain.net/2010/06/08/how-to-switch-to-innodb-database-in-mysql/ Verified the engine ...
Hi, I cannot delete/drop a crashed Innodb table. I get the following error: ERROR 1051 (42S02): Unknown table ‘accounts’ And if I want to create it I get the following error: ERROR 1005 (HY000): Can’t create table ‘accounts’ (errno: -1) This happens on my server after an accidental power failure. Regards ...
Somehow I managed to get two indexes named user_id, as shown below. Should I drop, rename and rebuild one of them, or is this no problem? SHOW INDEXES FROM core_item; +-----------+------------+-----------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_uniq...
I'm trying make a copy of a MySQL database on another server. I stop the server, tar up the mysql directory, copy it to the other server and untar it. I set all the permissions to match to the working server, and copied the my.cnf file so everything is the same except for server specific changes. However, when I try to startup the serve...
I have a project that requires us to maintain several MySQL databases on multiple computers. They will have identical schemas. Periodically, each of those databases must send their contents to a master server, which will aggregate all of the incoming data. The contents should be dumped to a file that can be carried via flash drive to ...
hey all, I have an innodb table that I'm trying to run an index on and I'm getting the following error: Deadlock found when trying to get lock; try restarting transaction The syntax is: ALTER TABLE mytable ADD INDEX (fieldtoindex); any ideas as to why that would throw a deadlock error? thanks! ...
I'm learning about the usage of datatypes for databases. For example: Which is better for email? varchar[100], char[100], or tinyint (joking) Which is better for username? should I use int, bigint, or varchar? Explain. Some of my friends say that if we use int, bigint, or another numeric datatype it will be better (facebook does it). ...
Assume I am going to emulate auto-increment in MySQL/InnoDB Conditions Using MySQL/InnoDB The ID field don't have unique index, nor it is a PK Is it possible to emulate only using program logics, without table level lock. Thanks. ...
i am using a simple database design and i think the best database example is e-commerce, because it does have a lot of problems, and its familiar to cms. USERS TABLE UID |int | PK NN UN AI username |varchar(45) | UQ INDEX password |varchar(100) | 100 varchar for $6$rounds=5000$ cr...
Consider I have two tables, t1 and t2 t1 = (id, name), t2 = (id, fid) The fid in t2 is a Foreign Key to t1. Steps to create rows as following. Insert a row in t1, get the id Use that id and insert as fid in t2. My problem is: Since t1's id is unknow when the transaction is not committed, so how to perform the insertion to t2?...
One part of my application has a race condition where multiple threads could end up creating the same persistent object. So I have implemented code that looks like this: from foobar.models import Bar def testomatic(request): bar = None tries = 0 while not bar: try: bar = Bar.objects.get(b=2) ex...
I'm developing a PHP web app that handles information about certain companies. So far, the DB design looks like this: CREATE TABLE `group` ( `group_id` int(10) unsigned NOT NULL auto_increment, `group_name` varchar(50) NOT NULL, PRIMARY KEY (`group_id`) ) ENGINE=InnoDB; CREATE TABLE `company` ( `company_id` int(10) unsigned NO...
I have two jobs pulling from a mysql table. They both want to get a row, do some work on it and update that row with the results, however I don't want them selecting the same row to update. Whats the best way using mysql/innodb engine to lock a row in the select so that it will be unavailable to other threads but allowing them to select ...
Right now, I'm debating whether or not to use COUNT(id) or "count" columns. I heard that InnoDB COUNT is very slow without a WHERE clause because it needs to lock the table and do a full index scan. Is that the same behavior when using a WHERE clause? For example, if I have a table with 1 million records. Doing a COUNT without a WHERE c...
Hi there, I have inherited a PHP project and the client is wanting to add some functionality to their CMS, basically the CMS allows them to create some news, all the news starts with the same content, and that is saved in one table, the actually news headline and articles are saved in another table, and the images for the news are save...
I get this error message: ERROR 1217 (23000) at line 40: Cannot delete or update a parent row: a foreign key constraint fails ... when I try to drop a table: DROP TABLE IF EXISTS `area`; ... defined like this: CREATE TABLE `area` ( `area_id` char(3) COLLATE utf8_spanish_ci NOT NULL, `nombre_area` varchar(30) COLLATE utf...
I'm doing a search on a table (few inner joins) which takes max 5 seconds to run (7.5 million rows). It's a MyISAM table and I'm not using full-text indexing on it as I found there to be no difference in speed when using MATCH AGAINST and a normal "like" statement in this case from what I can see. I'm now "suffering" from locked tables ...
Pretty straightforward question. I use InnoDB for everything else, for a couple of reasons. Is it a performance hit over MyISAM for a 'high-traffic' table? ...