Все вопросы: [innodb]

104 вопросов

похожие теги:
0
голосов
2ответов
117 просмотров

Which tables will be locked with this query?

I am migrating data between two tables using the query below. I had table locking issues, so I am considering changing the table engines from MyISAM to InnoDB. Which tables do I need to do that for? Just the table I am writing to, or both the table I am writing to and the one I am reading from? ...

0
голосов
2ответов
5339 просмотров

during magento installation i got Database server does not support InnoDB

I am trying to install latest magento version on jaquarpc. When i entered the database name,username and password it throws an error saying Database server does not support InnoDB storage engine Database connection error I also referred to magento forums..http://www.magentocom...

53
голосов
10ответов
43391 просмотров

Hibernate: Create Mysql InnoDB tables instead of MyISAM

How can I get Hibernate (using JPA) to create MySQL InnoDB tables (instead of MyISAM)? I have found solutions that will work when using Hibernate to generate an SQL file to create the tables, but nothing that works "on the fly".

2
голосов
2ответов
1359 просмотров

MySQL Query Questions with a Foreign Key Database

Here's some background info. I have three MySQL tables (all InnoDB). The first table is used to store images records. The second table is used to store tags (to tag the images with. OMG). The third table is used to store relationships between the images and the tags. Yippee. Here's the structure ...

3
голосов
6ответов
8599 просмотров

Is InnoDB sorting really THAT slow?

I had all my tables in myISAM but the table level locking was starting to kill me when I had long running update jobs. I converted my primary tables over to InnoDB and now many of my queries are taking over 1 minute to complete where they were nearly instantaneous on myISAM. They are usually stuc...

1
голосов
2ответов
1294 просмотров

How to select the MySQL's engine name of some table by sql query?

How to select the MySQL's engine name of some table. MyISAM or InnoDB. Can we do it by a simple sql query?

1
голосов
2ответов
384 просмотров

Find out which tables were affected by Triggers

In MySQL (particularly InnoDB) is there any way to know which tables were affected (updated / deleted rows) by CASCADE UPDATES / DELETES or regular triggers?

9
голосов
3ответов
7014 просмотров

Is using char as a primary/foreign key a no no?

Consider that there is a bunch of tables which link to "countries" or "currencies" tables. For making data easier to read I'd like make CHAR field with country code (eg US, GB, AU) and currency code (USD, AUD) a primary keys in each of those 2 tables and all other tables will use this CHAR as a ...

1
голосов
7ответов
1845 просмотров

Use of MD5(URL) instead of URL in DB for WHERE

I have a big MySQL InnoDB table (about 1 milion records, increase by 300K weekly) let's say with blog posts. This table has an url field with index. By adding new records in it I'm checking for existent records with the same url. Here is how query looks like: SELECT COUNT(*) FROM `tablename` WH...

0
голосов
2ответов
87 просмотров

Database directory disappeared

One of my clients complained that his database disappeared without any reason. I guess he must have dropped the database by mistake. But I just want to make sure that this can not happen automatically. The ibdata and iblog files are there but the directory (for e.g. company) is missing.

93
голосов
9ответов
70988 просмотров

Fulltext Search with InnoDB

I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext search capabilities), but the thought of the entire table bein...

2
голосов
2ответов
3459 просмотров

Fork MySQL INSERT INTO (InnoDB)

I'm trying to insert about 500 million rows of garbage data into a database for testing. Right now I have a PHP script looping through a few SELECT/INSERT statements each inside a TRANSACTION -- clearly this isn't the best solution. The tables are InnoDB (row-level locking). I'm wondering if I ...

0
голосов
4ответов
121 просмотров

Should I be using InnoDB for this?

I am developing a personal PHP/MySQL app, and I came across this particular scenario in my project: I have various comment threads. This is handled by two tables - 'Comments' and 'Threads', with each comment in 'Comments' table having a 'thread_id' attribute indicating which thread the comment b...

4
голосов
2ответов
33655 просмотров

MySQL InnoDB tables corrupt -- how to fix?

While performing a conditional DELETE operation in one of my InnoDB tables, which apparently required some temporary table to be created in ibdata1, the hard disk filled up and mysql crashed. I couldn't get it to start again until I deleted the ibdata1 file (~30 GB). Now mysql starts again, but ...

0
голосов
2ответов
187 просмотров

Migrate InnoDB tables to PBXT

Is there any migration procedures or tools to migrate a Database (Liferay database to be exact) from InnoDB to PBXT?

1
голосов
1ответов
335 просмотров

Add index on column or not?

I have a table looking like this: CREATE TABLE `item` ( `id` int(11) NOT NULL auto_increment, `title` varchar(255), `my_number` int(10) unsigned default NULL ); There are a hundreds of thousands of items, and I usually order them by 'my_number'. Will adding an index on 'my_number' increase pe...

10
голосов
2ответов
6754 просмотров

Deadlocks on MySQL deleting rows

We have a (currently InnoDB) table which contains roughly 500,000 rows. This represents a queue of tasks to run. It is stored in a MySQL database. An a continual basis, at least once per second but sometimes more frequently, we select data from it and subsequently update some rows. Once per d...

11
голосов
4ответов
6151 просмотров

Why remove django DATABASE_OPTIONS's "init_command set engine=INNODB" after table creation?

Docs on creating your database tables says: Another option is to use the init_command option for MySQLdb prior to creating your tables: DATABASE_OPTIONS = { "init_command": "SET storage_engine=INNODB", } This sets the default storage engine upon connecting to the database. After your ...

0
голосов
1ответов
78 просмотров

Altering constraint delete action

I did this: ALTER TABLE `db`.`my_table` ADD CONSTRAINT FOREIGN KEY (`my_second_table_id`) REFERENCES `my_second_table` (`id`); I should have done this: ALTER TABLE `db`.`my_table` ADD CONSTRAINT FOREIGN KEY (`my_second_table_id`) REFERENCES `my_second_table` (`id`) ON DELETE SET NULL; ...

4
голосов
3ответов
8633 просмотров

MySQL table with only a varchar as a foreign key

I have a table with a single unique VARCHAR(512) field. I want to have another table hold a foreign key reference to this first table. Both tables use InnoDB. If I add a VARCHAR(512) key to the second table and add a foreign key constraint on it will the 512 bytes long data be held twice? If so,...

7
голосов
5ответов
17775 просмотров

Issue changing innodb_log_file_size

I haven't done much tweaking in the past so this might be relatively easy however I am running into issues. This is what I do: Stop MySQL Edit my.cnf (changing innodb_log_file_size) Remove ib_logfile0/1 Start MySQL Starts fine however all InnoDB tables have the .frm file is invalid error, the...

141
голосов
7ответов
140731 просмотров

MySQL InnoDB not releasing disk space after deleting data rows from table

I have one MySQL table using the InnoDB storage engine; it contains about 2M data rows. When I deleted data rows from the table, it did not release allocated disk space. Nor did the size of the ibdata1 file reduce after running the optimize table command. Is there any way to reclaim disk space ...

4
голосов
1ответов
1421 просмотров

MySQL PBXT vs InnoDB

I'm wondering what are are the pros/cons of the new PBXT Engine vs the good old one InnoDB? Where can I find a list of features and maybe some benchmarks comparing these two DB engines?

3
голосов
3ответов
880 просмотров

Best way to implement search functionality using innoDb

I need to implement something like a full text search on a couple if index's in a large products table, using innodb, MyISAM is not an option due to its lack of transactions and relationship support. I can think of a couple of ways of doing this, plugin, stored procedure, search table with keys ...

4
голосов
2ответов
1895 просмотров

Why does my InnoDB table have a weird value for record count?

Below is the ammount of rows for 2 tables in my mysql DB 1 is myisam the other innodb, can someone tell me why the innodb one has this ~ in front of the number? These numbers came from phpmyadmin 10,308 MyISAM ~118,011 InnoDB

5
голосов
2ответов
18685 просмотров

What is the InnoDB equivalent of MyISAM's key_buffer_size?

When using MyISAM the configuration setting key_buffer_size defines the size of the global buffer where MySQL caches frequently used blocks of index data. What is the corresponding setting for InnoDB?

1
голосов
1ответов
2996 просмотров

Changes to a MySQL InnoDB table - which files are involved and why?

When changing the content of an InnoDB MySQL table the following files on the file system appears to be involved/changed: /path/to/mysql/data/[database]/[table].ibd (due to innodb_file_per_table) /path/to/mysql/data/data/ib_logfile0 /path/to/mysql/data/data/ib_logfile1 /path/to/mysql/data/data/...

3
голосов
4ответов
4525 просмотров

whats the quickest way to get row count of innodb tables, in mysql 4.0?

MySQL 4.0 doesn't have information_schema and 'show table status from db' only gives approximate row count for innodb tables. So whats the quickest way to get the count of innodb tables, of course other than count(*), which could be slower with big tables.

0
голосов
2ответов
562 просмотров

SQL Server Migration To Refactored MySQL w/ InnoDB Key & Constraint Issues

I am redeveloping a web application and its infrastructure originally using SQL Server 2005, ASP.NET & Windows 2003 to a LAMMP (extra M for memcached of course) stack and since the schema is heavily refactored (with very good reason to do so) I must write a custom migration app. The problem...

9
голосов
3ответов
13227 просмотров

Optimal MySQL-configuration (my.cnf)

The following is my default production MySQL configuration file (my.cnf) for a pure UTF-8 setup with InnoDB as the default storage engine. [server] bind-address=127.0.0.1 innodb_file_per_table default-character-set=utf8 default-storage-engine=innodb The setup does the following: Binds to loc...