FAQ Database Discussion Community


Should this two tables be related? [closed]

php,mysql,foreign-keys,primary-key,innodb
I have an account_table and a news_table. A news can be created by an admin account. The account_table has 4 columns which are: 1. account_id 2. username 3. password 4. user_type The news_table has 5 columns which are: 1. news_id 2. title 3. message 4. created_by 5. created_on If I...

create table without partitions

python,mysql,innodb
I am trying to create a copy of a table through a python script that has all the qualities of the original except for the partitions. I want to do this multiple times in my script (through a for loop) because I want to mysqldump daily files of old data...

How do I add a column to an existing FULLTEXT index?

php,mysql,innodb,full-text-indexing
I created a FULLTEXT index on a table in a MySql database, and left out a column when I did so. I have been trying to figure out how to add this column so that it can be searched against along with the other columns in this table that have...

MariaDB/MySQL: Get checksum of InnoDB table

mysql,innodb,mariadb,information-schema
How do I get a reliable checksum on an InnoDB table? I want something like the CHECKSUM TABLE foo QUICK; that MyISAM has, and it needs to be fast. I'm developing a tool to reliably monitor and swiftly react to changes in my database. InnoDB tables don't update UPDATE_TIME in...

Error Starting MySQL after Crash

mysql,innodb,mamp
I was merrily installing wordpress with MAMP pro when my Mac crashed. Now I cannot start MySql. Most stuff I read online tells me to throw some terminal commands at it, trying to kill the process. killall -9 mysqld But terminal report "No matching processes belonging to you were found"...

MySQL 5.6.10 InnoDB cannot create table as table already exists

mysql,innodb
I have a very interesting case. Several of my InnoDB tables got somehow corrupted. The .frm files of the tables disappeared and only the .ibd files were left. I thought no problem, I will restore these tables from the backup, so I deleted the .frm files and try to create...

How to speed up slow MySQL UPDATE queries with InnoDB tables

php,mysql,innodb,mysql-slow-query-log
I have a very simple MySQL update query on my InnoDB table. UPDATE `players_teams` SET t_last_active=NOW() WHERE t_player_id=11225 AND t_team_id=6912 AND t_season_id=2002 LIMIT 1 My table is structured as so: CREATE TABLE `players_teams` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `t_player_id` int(11) DEFAULT NULL, `t_team_id` int(11) DEFAULT NULL, `t_league_id` int(11)...

Do I have to drop the primary key or reload the table data for partitioning in MySQL InnoDB?

mysql,innodb,database-performance,database-partitioning,pruning
I have to partition a few tables by date range. Do I have to drop the primary or reload the complete data to be able to partition these tables? Some of the tables do contain a lot more than 50 million rows. alter table temp_table_test1 partition by range (unix_timestamp(created_at)) (...

Change query to allow one type of column value per row

mysql,sql,innodb
I am trying to remove the third row from the results table above. The query which returns the data including the third row (exactly the table of results above) is this: SELECT service.code, line, service.region FROM service INNER JOIN pattern ON pattern.service = service.code The above query needs to...

MYSQL innoDB SELECT FOR UPDATE with LEFT JOIN

sql,mysqli,innodb
Can somebody please tell me what happens when you LEFT JOIN tables for a SELECT FOR UPDATE using an innoDB storage engine. Do all the rows from all the joined tables get locked, or is only the primary table rows? For example, if I do.. SELECT userID, countryID FROM user...

How can low latency not = high throughput?

mysql,database,mongodb,innodb,myisam
The graph above shows the latency of read and update operations while the column shows the throughput(operations/second) I am unsure how MyISAM can perform operations with such low latency but still be level with InnoDB in terms of throughput? MongoDB also dominates in terms of throughput but has a...

MySQL/InnoDB - order of PK in Leaf Node

mysql,indexing,innodb
I'm using InnoDB. If I have data being like [PKID : ?] col1 --------------- [PKID : 1] 7 [PKID : 2] 7 [PKID : 3] 5 [PKID : 4] 5 [PKID : 5] 7 and has B+Tree index of col1. It would be like this: 5(PKID : 3 or 4)...

Slow performance on joining table

mysql,sql,performance,query-optimization,innodb
I am trying to optimize a query as below , I have study how to add index to improve peformance , but the result is still slow. The query below took 20 sec to run , Transaction contains around 100k records and joining table TransactionDetail that contains around 500k records....

How do I fix InnoDB corruption locking a table name from creation (errno: -1) on AWS RDS?

mysql,amazon-web-services,innodb,amazon-rds,errno
TIP: Do not run ALTER statements in MySQL Workbench for a "Standard TCP/IP over SSH" connection. It is much better to shell into the server and run the ALTER from there. That way, if you lose connection to the server, the ALTER should still finish its job. I'm trying to...

Date comparison from VARCHAR column value

php,mysql,compare,innodb,varchar
I have a SQL table where one of the columns 'creator' is a VARCHAR data type. That column stores values like this one: Mike Jones|05-17-2015 1:21 pm consisting of the person that created the record, the date and time it was created. I need to use the date portion of...

MYSQL Reset Auto Increment improve performance

php,mysql,innodb,auto-increment
I have a question about MYSQL databases will resetting the primary key improve performance? I use auto increment as key (NumID) see below but the count is in the 42 million mark now and lately I notice up and down performance. The database size is 8GB and there is 450,000...

MyISAM vs InnoDB for Logging

mysql,logging,innodb,myisam
I am optimizing a database with almost no knowledge for my bachelor thesis. In no way i want to let you do the work for me, but i have some questions which no one could answer so far. Table Structure: data_inc, CREATE TABLE 'data_inc' ( 'id' bigint(20) NOT NULL AUTO_INCREMENT,...

Error 1067: The process terminated unexpectedly [InnoDB]

mysql,innodb
I am facing the following error when i try to restart the MySQL service after an unexpected shutdown of the database server last night. Could not start the MySQL service on Local Computer. Error 1067: The process terminated unexpectedly. When i check the .err log file under MySql data folder,...

Centos - Can't start MySQL

mysql,shell,centos,innodb
I installed ioncube loader, and now i cant start my SQL Server. Error: service mysqld start MySQL Daemon failed to start. Starting mysqld: [FAILED] this is my log 150507 20:01:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150507 20:01:03 InnoDB: Initializing buffer pool, size = 8.0M 150507 20:01:03 InnoDB:...

Auto-increment - automatic reset for each year

mysql,innodb,auto-increment
MySQL/InnoDB In my case my receipts should be counted on yearly basis; 1/2015, 2/2015 ... 556/2015 and so on. When next year comes, the counter should start from 1 again and receipts should be counted as 1/2016, 2/2016 ... How to define auto_increment field which will reset itself on yearly...

PHP/MySQL Critical section

php,mysql,transactions,innodb,critical-section
I'm using PHP with PDO and InnoDB tables. I only want the code to allow one user-submitted operation to complete, the user can either cancel or complete. But in the case that the user posts both operations, I want one of the requests to fail and rollback, which isn't happening...

InnoDB Error on MySQL Startup

mysql,innodb
When launching MySQL, I get the following InnoDB errors. mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead. [Warning] 'record_buffer' is deprecated and will be removed in a future release. Please use 'read_buffer_size' instead. [Note]...

SQL - Aggregate Function for DATEDIFF and WHERE/HAVING

python,mysql,innodb,mysql-python
I'm trying to query a DB to find the closest date to the parameter I passed in. I have a problem I've been working on in IRC and lists for a while and can't find a solution. I'm using a python DBAPI (sqlalchemy) and am accessing the data both through...

1 auto_increment with 2 primary keys

mysql,primary-key,innodb,auto-increment,myisam
My intention is to create a table with 2 primary keys with one of those autoincrementing and the other specified when inserting and when I create a new field for this table it must start the recount if the not incremented primary key changes. This is what I had: I...

SQL Syntax error foreign key

mysql,sql,database,innodb
I've a problem, i try to connect 2 db table, and if I make the query for make a DB structure the answer is Syntax Error. I paste the code here, somebody can help me ? DROP TABLE IF EXISTS album; CREATE TABLE `album` ( `id` int(11) unsigned NOT NULL...

Reduce row scan of update query without using index

mysql,sql-update,innodb,explain
I am using MySQL InnoDB engine. The result of below explain update query is EXPLAIN UPDATE H_M_SAMP SET NEW_M_ID=17 WHERE M_ID IN(363) Primary key of H_M_SAMP table is H_M_ID. And there is no foreign key relation. Is it possible to reduce number of rows scan by the update query without...

Mysql-Python complex query resulting in empty set

python,mysql,innodb
We have a script that was running comfortably for past 4 years. It pulls a lot of data from a SugarCRM application and then prints some reports. I am using Python-MySQLDB to pull the data. db = mdb.connect('localhost', db_user, db_pass, db_name) c = db.cursor() statement = " select a.email_address ,...

Laravel 5: Heavy Select Query

laravel,eloquent,innodb,laravel-5
I have about 25.000 rows in my DB table 'movies' (InnoDB, 17.5 mb) And when I try to get them all to display in my admin panel, nothing happens. Just 5-8 seconds pending and white screen. No displayed errors, just nothing. (max execution time is 3600 seconds, because it's on...

Simple UPDATE query takes too much time in InnoDB than MyISAM

mysql,database,innodb,myisam
Quick description: In my application (VC++/Windows 8) I am issuing simple update queries to increase value of a field in a InnoDB table in MySQL database. It takes way too long than the same with MyISAM table. Details: I am creating a table DEMO_TABLE having columns MyID and MyCounter (both...

how many records can be deleted using a single transaction in mysql innodb

mysql,innodb
I wanted to delete old records from 10 related tables every 6 months using primary keys and foreignkeys. I am planning to do it in a single transaction block, because in case of any failure I have to rollback the changes. My queries will be somethign like this DELETE FROM...

declare two foreign key that are also primary key

mysql,foreign-keys,innodb
i'm facing a strange problem by declaring two foreign key on a table, that are also a primary key in my table. This is my sql code: CREATE TABLE IF NOT EXISTS guest ( id_guest varchar(50) NOT NULL, PRIMARY KEY(id_guest) ) ENGINE=InnoDb DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS product...

Dissecting MySQL InnoDB record format to restore from raw disk

mysql,innodb,file-format,raw-data
I had a mysql database stored on a USB thumb drive which has irreparably lost its file allocation table. Therefore, I cannot get to the ibdata1 file as a whole. I can, however locate the record pages which were used using a hex editor. All the data is there, but...

innodb_lock_wait_timeout increase timeout

php,mysql,database,timeout,innodb
I am using MySQL database and trying to update records just after insert so I am getting following error ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction. So I decrease timeout by following query:- set GLOBAL innodb_lock_wait_timeout=1 So my question is:- is it ok to do that? Will it lead...

Are double reads a possibility in InnoDB

mysql,database,innodb
Can concurrency effects called "Missing and Double Reads Caused by Row Updates" and mentioned here https://msdn.microsoft.com/en-us/en-en/library/ms190805.aspx be relevant for Innodb engine? ex.: Transactions that are running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. Transactions that...

MySQL's EXPLAIN INSERT unavailable: how to understand if INSERT is slow or not?

php,mysql,performance,innodb,mysql-slow-query-log
Initially I'm trying to find slow queries among my scripts. And one of the things that bothering me is INSERT queries as I have couple of indexes in the table (as I can understand insert will not be instantaneous in this case as the indexes should be recalculated each time)....

What the sql query can generate 100,000 mysql rows with random data? [closed]

mysql,sql,innodb
I have table with 12 columns, How by sql query i can insert 100 random symbol in each column of row to generate 100 000 rows

Convert from innodb to MyISAM for fulltext

mysql,sql,full-text-search,innodb,myisam
I want to use fulltext in a big table, and I can't update mysql for support fulltext in innodb or use external search engine. So I think I have 2 option here. 1.Using innodb and search database with LIKE 2.Convert my table to MyISAM I don't know what is the...

How would I apply foreign keys to this specific requirement

php,mysql,database,innodb
I'm currently making a payslip system wherein there are two tables, the account_table and the payslip_table. I'm currently having trouble with the database design, I'm torn between creating a relationship between two tables or not creating one at all. I guess I'll first explain how this system works: Admin logs...

ERROR 1452 (23000)

mysql,foreign-keys,innodb
I'm getting this error on my script: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (hbm248_BucherFilms1.films, CONSTRAINT films_ibfk_1 FOREIGN KEY (country) REFERENCES countries (country)) I want to use INNODB and dont' understand what is causing that error. All my foreign keys line up....

ERROR 1064 (42000) caused by PAGE_CHECKSUM

mysql,innodb
My sql file: SET NAMES 'utf8'; set FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `wp_icl_core_status`; CREATE TABLE IF NOT EXISTS `wp_icl_core_status` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `rid` bigint(20) NOT NULL, `module` varchar(16) NOT NULL, `origin` varchar(64) NOT NULL, `target` varchar(64) NOT NULL, `status` smallint NOT NULL, PRIMARY KEY (`id`),...

Is this MySQL behavior allowed?

java,mysql,jdbc,innodb,isolation-level
I setup two JDBC connections to a testing MySQL database and am using InnoDB. The connections start transactions in different isolation levels and I test which modifications are visible to which connection after which actions: public static void main(String args[]) { final Connection con1 = DriverManager.getConnection(url, user, passwd); con1.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); con1.setAutoCommit(false);...

Faster way of performing this SQL query

php,mysql,sql,optimization,innodb
I have a large SQL table that stores an added item id that the user added to their profile and the rating they gave it. The thing is, I need to quickly count how many times an item has been added AND also need to get the total ratings it...

Performing a transaction across multiple statements in phpMyAdmin

mysql,phpmyadmin,innodb
I'm not sure if this is an issue with phpMyAdmin, or that I'm not fully understanding how transactions work, but I want to be able to step through a series of queries within a transaction, and either ROLLBACK or COMMIT based on the returned results. I'm using the InnoDB storage...

MySQL/MariaDB slow UPDATE on PRIMARY KEY

mysql,sql,query-optimization,innodb,mariadb
Query: UPDATE `cart` SET `user_id` = NULL, `completed` = 0 WHERE `id` = 6948; Query OK, 0 rows affected (1.21 sec) Rows matched: 1 Changed: 0 Warnings: 0 0 rows affected, but it took 1210ms. SELECT of this row by id always took 0ms. Table size is (6,354 rows). >...

MySQL Auto-Increment with several different user-input words

php,mysql,database,innodb,auto-increment
I'm currently working on a species cataloging code written on HTML + PHP using a MySQL InnoDB database. The code allows the user to enter data of animals and store that on the database for further search (ID(used for indexing),Specie, order, family, name - these are the fields on the...

InnoDB Concurrent Read-Write

mysql,locking,innodb,read-write
I wanted to understand how InnoDB handles multiple simultaneous reads/write operations. Consider the below scenario: You are performing a pretty intense batch write. Any read request that will come in will have to wait till the batch write gets completed. During this time, write request are also requested on the...

MySQL - storage in varchar for length 1000

mysql,innodb,varchar
Is it a good idea to use varchar if I have so store data of length 1000? Below is my script and column SR_NOTES is the one I a worried about. I user utf8 and the engine is INNODB. Would it be wiser to go with say for example, TINYTEXT?...

How to enable “Relation view” feature in PHPMyAdmin

php,mysql,database,phpmyadmin,innodb
I am trying to establish Foreign Key relationships between a number of tables but cannot find the "Relation view" option. I have ensured my database and the relevant tables are using InnoDB. I am using a Mac with MAMP and using PHPMyAdmin to manage my database. I have tried a...

Combining two MySQL tables that are loosely connected

php,mysql,database,innodb,normalization
So I have two MYSQL tables in a DB which aren't connected (two different data sources) [around 500,000 rows in each]. They have things like Business Name, Business Address, City, State, along with some specific information in each that I hoped to populate a third table with once I've figured...

mysql “KEEP ONLY” command?

mysql,sql,innodb
I know that there is a DELETE FROM <table> WHERE <exprs> command in mysql that deletes tuples from the specified table if the expressions are valid. However, it becomes a burden always using demorgan's law in taking the complement of keep only expressions. My question is, is there a KEEP...

How can I rebuild indexes and update stats in MySQL innoDB?

mysql,innodb
I have experience with MS SQL server where it is possible and useful to update statistic and rebuild indexes. I can't find such option in MySQL innoDB, is there such option? If not, how MySQL database create an execution plan? Does the MySQL update indexes and statistic with every UPDATE...