MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

4
votes
0answers
285 views

RDS MYSQL instance rebooted and won't load INNODB table

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error ...
3
votes
0answers
69 views

Why the stored procedure is much slower than a temporary table?

I want to do the updating of my database every day and I will like to use stored procedures but the time of execution of the temporary table is faster than the stored procedure. How can I optimize my ...
3
votes
0answers
21 views

How does mydumper's ''--use-savepoints" option reduce metadata locking

MyDumper 0.6.1 add a new option --use-savepoints. From the manual, it means: Use savepoints to reduce metadata locking issues, needs SUPER privilege I don't understand. How does it "reduce ...
3
votes
0answers
36 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
3
votes
0answers
128 views

How to get the best write throughput during a MySQL import with Amazon RDS?

tl;dr - How can we get the maximum EBS throughput for a db.r3.2xlarge instance? We're only seeing about half of what it can supposedly do. Apologies for the long length of this question. We've had ...
3
votes
0answers
83 views

Is it a waste to set innodb_buffer_pool_instances greater than the # of CPUs?

I set the innodb_buffer_pool_size to 20GB on a server with 12 CPU cores. My full database is 11gb, however most of it is archived tables that are almost never used. The total queried data is around 3 ...
3
votes
0answers
4k views

Why does CAST(1 AS SIGNED INTEGER) return a BIGINT on MySQL?

If I do CAST(1 AS SIGNED INTEGER) I always end up getting a BIGINT returned, for example: $ mysql -u root -p --column-type-info Enter password: --- Copyright and help message snipped for brevity --- ...
3
votes
0answers
1k views

Query getting periodically stuck in 'copying to tmp table' state, never completes

I am running Wordpress on a dedicated server with a MySQL backend. I have a query that usually takes <1 second to execute, but periodically, this query will get stuck in a 'copying to tmp table' ...
3
votes
0answers
307 views

Storing history of full/partial tables

I'm building a web-application with Django and MySQL (InnoDB) and am currently pondering over how to manage historical changes on various tables. I wonder if it's efficient to store a lot of rows ...
3
votes
0answers
389 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
3
votes
0answers
318 views

“InnoDB: error clustered record for sec rec not found” MySQL 5.5.28

We have a high-end server, 128GB RAM, 32 Core , Xeon, SSD RAID 10 - running Ubuntu 12.04 with MySQL 5.5.28. During high load, randomly we got the below error. MySQLcheck, innochecksum shows no ...
3
votes
0answers
124 views

Once in a while synchronization of the independently updated mysql databases problem

I have a production database and a test database running on MySQL with MyISAM and InnoDB tables, which are updated independently. The Production database is updated by system and sometimes by a user. ...
2
votes
0answers
39 views

Storing phone number in E.164

TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field? Have been reading up on format/storage of ...
2
votes
0answers
39 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
0answers
105 views

Why is this MySQL query “using where” instead of “using index”?

Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join ...
2
votes
0answers
41 views

After restarting Mysql replication, Slave commands are executed

up vote 0 down vote favorite I have a Mysql Master Slave Replication which is working fine. Unfortunately I got some serious problems on my slave and the only chance I saw was to restart the whole ...
2
votes
0answers
82 views

Combined UUID Index + AI PK Insert Performance

I posted this in stackoverflow but realised it probably belongs here instead. Imagine the following table: CREATE TABLE `orders` ( `ORDERID` int(11) unsigned NOT NULL AUTO_INCREMENT, ...
2
votes
0answers
43 views

How to query for additions and deletions between two sets of rows (mysql)

I have tried several variant queries for finding additions/deletions between two sets of rows. Purely algorithmically, there is a straightforward algorithm that is O(N) for composing a list of ...
2
votes
0answers
81 views

ORA-00937 when calculating a sub-value in an aggregate select statement

The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question. I want to use an ...
2
votes
0answers
32 views

See indices implicitly created for foreign key constaints in MySQL

When I add a foreign key constraint to my table, SHOW INDEX doesn't show any new indices, but the overall space used by the indices in that table increases. Apparently, the index for the field I put a ...
2
votes
0answers
125 views

Almost identical queries but large time difference in execution

Exhibit A SELECT SUM(value) FROM Credits RIGHT JOIN calendar ON datefield = DATE(FROM_UNIXTIME(created_at)) WHERE confirm = 1 && reason = 1 && country = 'it' && created_at ...
2
votes
0answers
94 views

Performance difference between two MariaDB slaves

I have a pool of MariaDB servers like this : master (5.5.38-MariaDB-log) slave1 (5.5.38-MariaDB-log) slave2 (5.5.38-MariaDB-log) The two slaves servers run on the exact same hardware (Dell ...
2
votes
0answers
51 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
2
votes
0answers
47 views

Sample a Mysql table to increase performance

we are storing all our clients traffic data in a MySQL 5.1.73 database (MyISAM) and then we use it to generate user and internal statistics/graphs, always searchinng for a client id and a date/hour. ...
2
votes
0answers
165 views

utf8mb4 in SQL Server Express

We have some web applications based on databases using utf8mb4 as character set and utf8mb4_Standard as collation. We saw that we can use any character we want with this setting. In SQL Server ...
2
votes
0answers
68 views

Denormalizing to speed up inserts

I have a MySQL InnoDb table with approximately 40 million rows and the following columns: param1 (int), param2 (int), param3 (int), geo (char2), value1 (bigint), value2 (bigint) I have an ...
2
votes
0answers
362 views

Recover innodb database from ibdata1 and *.frm files only

I have a problem on my server. I had a hard disk error on my server, and I restarted it. After that, I started wamp server software, but mysql services were not started. I checked all mysql data, ...
2
votes
0answers
202 views

Single database won't load MySQL

When I turned off my MySQL server, and turned it back on, it wouldn't read my database anymore. Just a single database, everything else in mySQL was fine. When I try looking at any of the tables it ...
2
votes
0answers
1k views

How to do While Loops?

How can we use while loops in MySQL? My test script: BEGIN SELECT 0 INTO @n; WHILE @n < 10 DO SELECT @n; SET @n := @n +1; END WHILE; END; But it has syntax errors. I've also ...
2
votes
0answers
28 views

Is it possible for unique key to break master-master replication in mysql?

I have a master-master mysql setup with 2 servers running the exact same application making writes to such a table: CREATE TABLE `metric` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, ...
2
votes
0answers
2k views

MySQL - high CPU usage (MyISAM tables)

We have a server running LAMP stack on CentOS release 5.6 (Final) MySQL is having high CPU usage constantly which causes server to lag as shown top - 13:50:47 up 80 days, 13:53, 1 user, load ...
2
votes
0answers
61 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
2
votes
0answers
65 views

Count how many locks required for a MySQL (InnoDB) query

I'm performance tuning a very data-heavy mysql application. It has a purge system that often seems to cause lock timeout errors for users. Based on my research I have a few adjustments to the query to ...
2
votes
0answers
2k views

MySQL Queries taking too long to execute

I have a database table, with 300,000 rows and 113.7 MB in size. I have my database running on Ubuntu 13.10 with 8 Cores and 8GB of RAM. As things are now, the MySQL server uses up an average of 750% ...
2
votes
0answers
101 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
2
votes
0answers
118 views

Does it make a difference to add the DEFAULT keyword in CREATE TABLE?

The MySQL manual says: CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name] ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] ...
2
votes
0answers
1k views

Mysql slow writes

i have a strange problem with mysql. My selects goes fast. My insert/create/delete/drop/update go very slow. I have done some tests on 2 machines. My old machine and my new machine. Old is worst ...
2
votes
0answers
117 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
2
votes
0answers
459 views

Getting distinct list of records based on the MAX of a column

I have a status table (tblTestActionStatus) which has three columns ID_TestAction: references some test action ID_Status: a look up table reference for the variety of possible statuses for the ...
2
votes
0answers
2k views

How to prevent “ibdata files do not match the log sequence number”?

I am dealing with a very large set of databases that are all innodb. I've enountered this on mysql restart too many times for my comfort: ibdata files do not match the log sequence number But I've ...
2
votes
0answers
428 views

MySQL replication error 1146

Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total): Last_SQL_Error: Error 'Table ...
2
votes
0answers
74 views

Table Structure: 2 Related Entities Share the Same Entity

I was hoping I could get some advice on my table structure. Im writing a simple workout tracker for me and my buddies. These are the requirements: Multiple users A user can have multiple workouts A ...
2
votes
0answers
693 views

The nature of the relationship between two tables in MySQL

Good day. I'm designing a database for a research project where we're predicting whether or not young female athletes will rupture their ACL(s) based on the nature of a test jump recorded with a ...
2
votes
0answers
322 views

Ignore foreign keys in mysql workbench model synchronization

I've made an ERD in MySQL Workbench, where I added foreign keys to visualize the relationships. In the actual database I want to work without the foreign keys (I'll organize that in my application). ...
2
votes
0answers
100 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
2
votes
0answers
138 views

Changing Charset to utf8 for all the fields and tables in mysql

How should I use the mysql and awk to change the charset for all the fields and tables for a specific database? I did not include username or password, so for sure it will not work. Should I run it ...
2
votes
0answers
80 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
674 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
2
votes
0answers
478 views

Incorrect data when we using the CONVERT_TZ function on DST boundaries

We have a problem in converting time zone from "America/New_York" to "UTC". This problem occurs only on times representing 2.00 AM during the daylight saving. Timezone conversion with correct output ...
2
votes
0answers
3k views

'Access Denied' in MySQL on AWS RDS

I'm trying to use an ETL (in my current case, the free version of Talend). I've successfully set up a MySQL on Amazon RDS, logged in through the MySQL Workbench for Mac, and uploaded my company's ...