innodb

InnoDB deadlock with read-uncommited! - Java - Glassfish - EJB3 (JPA/Hibernate)

Hello I'm new here and need some help :) It's been several days that i got deadlock issues on a Java application with Glassfish - EJB3 with Mysql InnoDB Config: Mysql InnoDB: Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 Application server: Glassfish v2.1 Persistance with EJB3 - JPA - Hibernate To make i...

Is there a noticable difference between innodb and XtraDB?

Today on serverfault I read about XtraDB. I read that it was a fork of innoDB and was woundering how big are the differences? Would I notice the change or do you think its just wasted time if I would try it out? ...

temporary tables within stored procedures on slave servers with readonly set

Hi, We have set up a replication scheme master/slave and we've had problems lately because some users wrote directly on the slave instead of the master, making the whole setup inconsistent. To prevent these problems from happening again, we've decided to remove the insert, delete, update, etc... rights from the users accessing the slave...

How do I fix this MySQL/Innodb Deadlocking issue in a non-blocking environment?

Hey, We're using MySQL with Innodb Engine storage. We have an "evented" environment that sends multiple concurrent requests on a table. Basically, it works like this: We have a find_or_insert function that does that: - find() -> on result, if empty -> insert -> on result find() We're using a non-blocking MySQL driver, so basically, wh...

mysql performance

We are developing our database in MySql with innoDB engine. The database contains a column that is of varchar type with each entry containing about 3000 characters. We are to provide search on this column. For speeding up purpose, we need to add index on this column. Can you put in some information in this regard? Which type of index do...

Quickest way to delete enormous MySQL table

I have an enormous MySQL (InnoDB) database with millions of rows in the sessions table that were created by an unrelated, malfunctioning crawler running on the same server as ours. Unfortunately, I have to fix the mess now. If I try to truncate table sessions; it seems to take an inordinately long time (upwards of 30 minutes). I don't c...

How do you use MySQL InnoDB tables on OS X?

I had trouble using InnoDB MySQL 5.0 on OS X because of a my.cnf settings issue. Listing the gotchas in case it helps anyone: If InnoDB is disabled, check the error log (hostname.err, in the data directory). I had an empty setting which I changed: innodb_data_file_path = ibdata1:10M:autoextend Hope this helps someone. I was running ...

Using results from parent queries in nested select

I'm sure this is a fairly trivial problem, but I'm not sure what to google to find the solution. I have a table that looks like this: CREATE TABLE IF NOT EXISTS `transactions` ( `name` text collate utf8_swedish_ci NOT NULL, `value` decimal(65,2) NOT NULL, `date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swe...

Designing SQL alternative?

I'm thinking of designing & implementing my own SQL-equivalent (DDL+DML) that is... a pure relational algebraic language, and has an elegant Lisp-/Scheme-like syntax Assuming an RDBMS such as MySQL, where exactly would I need to start my language design work? On top of the storage engine layer such as InnoDB? I don't know what all R...

Ambiguous `id` Field

I have the following two tables: system - id - systemName - idOrganization organization - id - officeSymbol I am running the following query and receiving an id is ambiguous error: SELECT system.systemName, organization.officeSymbol FROM system LEFT JOIN (organization) ON (system.idOrganization = organization.id) As you can see, I...

MySql Foreign Key index

Hi, I am using a Mysql table products which has a foreign key category_id on the categories table the foreign key constraint is defined (Innodb engine). I noticed that when I run EXPLAIN SELECT * from products where category_id=1; it uses the foreign key, so I see type=Range and Key: my_foreign_key But when I run EXPLAIN SELECT * f...

MySQL query randomly takes "forever"

...

MyISAM Tables getting Corrupt

sometimes i get an error like "table is marked as corrupt and shld be repaired". that DB (tables) is using MyISAM. recently that keeps happening. what could be the causes? most recently i am executing a batch insert INSERT INTO table (..., ..., ...) VALUES (...), (...), (...) ... and it just hung. or took very long to complete it seem...

How should mysql be optimized/tuned for lots of writes and few reads (100:1). Read perf reqd.

I have simple db with 4 tables with a few millions rows each, and several indexes. I'm performing several hundred update and inserts on them per minute. The reads are much less frequent, but they need to be fast - for a web app. The reads should have priority - I can delay writes, if it helps improve the snappiness of reading. Curren...

How do i add foreign keys to two Innob tables so that they autoupdate each other?

I have two tables users and lead_contacts which have similar data. When somebody buys a product they become a user. How should I modify the two create staements below so: that the leads table receives a new entry with first_name, last_name, company and email, when a user is created. the first_name, last_name, company and email in use...

Should one steer clear of adding yet another field to a larger MySQL table?

I have a MySQL-InnoDB table with 350,000+ rows, containing a couple of things like id, otherId, shortTitle and so on. Now I'm in need of a Bool/ Bit field for perhaps a couple of hundreds or thousands of those rows. Should I just add that bool field into the table, or should I best create a new table referencing the IDs of the old table ...

Is it possible to use Mysql foreign keys in InnoDB tables for inverse lookup?

Hi! I wonder if it is possible to use FK's in Mysql (InnoDB) for inverse lookup. The reason - I want to read an XML-like structure from the db (using one table per "layer"), but be able to do this dynamically. I want to be able to update the xml structure by adding a new table and setting a FK constraint. To clarify, say we have a tab...

Applying DATABASE_OPTIONS when testing Django project (or make it to use InnoDB for MySQL)

As the title says, I want to apply DATABASE_OPTIONS settings when I run my tests via ./manage.py test. In django/db/backends/creation.py, it does not consider this option at all in both create_test_db() and _create_test_db(). This breaks a test with a view that uses transaction.rollback function with InnoDB. It seems that test databases...

For innodb table in MySQL, which is faster: varchar(255) or tinytext?

I am optimizing some innodb tables in MySQL, so I ran procedure analsye() to see what the recommendations were. The results recommended tinytext instead of varchar(255) for all the fields that were previously set up as varchar(255) Is there a performance gain to be had by using tinytext? I am only concerned about speed here, not size. ...

Resetting databases with having OneToOneField crossing different applications in Django

I'm getting the following error when I run ./manage.py reset app1: Error: Error: app1 couldn't be reset. Possible reasons: * The database isn't running or isn't configured correctly. * At least one of the database tables doesn't exist. * The SQL was invalid. Hint: Look at the output of 'django-admin.py sqlreset app2'. That's the S...