MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

5
votes
0answers
34 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 ...
4
votes
0answers
30 views

Counting show variable result in mysql

I am reading this article and there doesn't seem a way to do something like this: SELECT COUNT(*) WHERE SHOW VARIABLES WHERE Variable_name = 'innodb_ft_cache_size' I can do the below but this ...
4
votes
0answers
53 views

Find Shoe Size Selection

I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature. The ...
4
votes
0answers
40 views

Running Triggers on Slave with Row-based Events in MySQL >=5.6

We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys If one uses statement-based ...
4
votes
0answers
307 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
76 views

query optimization of a left join

I have the following query written into the slow log (mysql-slow.log). I don't have much idea how to optimize this query. I already have indexes for: mode, f.user_id... etc but doesn't seem to ...
3
votes
0answers
29 views

Create an SSL connection with ODBC driver to MySQL(5.6) using openSSL

I want to create an asymmetric SSL connection from MySQL to an ODBC driver but don't fully know how to generate the keys and certificates (I'm using openSSL as recommended here: ...
3
votes
0answers
47 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
231 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
116 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
197 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 ...
3
votes
0answers
68 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 ...
3
votes
0answers
5k 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
630 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 ...
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
89 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 ...
3
votes
0answers
5k views

How to use SUBSTRING using REGEXP in MySQL

I have the following situation. I have to substring regular expression from description using MySQL. Description: Lorem D9801 ipsum dolor sit amet Where D9801 is REGEXP. Every strong text ...
3
votes
0answers
417 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
342 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
125 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
12 views

Join integration query involving multiple associations

I'd like some assistance in fixing this query. Let me explain the background on what it currently does, and then what I need to integrate. First, here are my current tables. users (performers) table ...
2
votes
0answers
15 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
2
votes
0answers
16 views

Issues with mysql enterprise backup, assertion failure on restore

I'm trying to restore a backup to a new server, but having some issues with corruption. The source server has a 2.5T table which is very active. I copied the latest backup over to a new server and ...
2
votes
0answers
16 views

selecting data from joomla back end mysql

I have developed a front-end that call a php server side service that adresses the mysql. On my local everything works perfectly, but on distant 1and1 machine, it only works for tables created by ...
2
votes
0answers
50 views

How can I organize complex queries?

I am analysing a relatively large (25GB compressed MySQL dump) static data set. Some of the queries I'm writing are several tens of lines long, which makes them difficult to understand, modify, and ...
2
votes
0answers
20 views

Caching impression counts to avoid mysql inserts

We currently get a lot of mySQL inserts/updates on duplicate daily (millions) and we're looking at speeding up the application by using some form of counter with memcached and then clearing it on ...
2
votes
0answers
57 views

How to store data where various subcategories have specific columns?

I'm new to database designing. I'll use MySQL and PHP. I'm designing a database in which I have a lot of categories and subcategories like given below: Product Table p_id p_title category_id ...
2
votes
0answers
99 views

Two queries usually run fast but get slow when joined

There are those 2 tables CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `user` int(11) NOT NULL, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
2
votes
0answers
35 views

MySQL memory is keep on increasing , any reason?

On one of our production host MySQL memory is keep on increasing day-by-day. I tried reboot of MySQL instance but still no avail. System has 16Gb of RAM. TOP output top - 13:05:13 up 35 days, ...
2
votes
0answers
50 views

Suggestion To Improve My.cnf INNODB

I'm after suggestions on how to increase mysql performance on my server, any help would be greatly appreciated. CPU - 4 core RAM - 16GB Databases - 14 Innodb databases with around 20 tables in each ...
2
votes
0answers
59 views

Flexible and robust way to cull older data?

I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes ...
2
votes
0answers
58 views

mysqldump restore error

I am getting the error below, while restoring a dump to RDS MySQL 5.6. Dump is taken from MySQL 5.5 originally. I tried restoring twice but same error. Can anyone help ERROR 1064 (42000) at line ...
2
votes
0answers
27 views

Tuning Issue / Temporary Tables

I've got a MySQL database (MyISAM) that handles a lot of read/writes and uses temporary tables quite frequently. Recently it's started having massive load spikes at peak load, and noticed that there ...
2
votes
0answers
60 views

Mysql high cpu usage - cpanel

After an update from whm, I see a high CPU usage from MySQL (5.6). my.cfg: [mysqld] slow_query_log=1 slow_query_log_file="/var/lib/mysql/slow-query.log" long_query_time=1 skip_name_resolve ...
2
votes
0answers
86 views

Mysql Trigger use inserted value as column name for update After Insert

I have 2 tables: One for storing order_products and another one for keeping inventory levels for multiple stores The order_products table keeps the ID for the store in the store_id column. ...
2
votes
0answers
90 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
45 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
135 views

Sync a local MySQL database and a live server MySQL database

I want to store data in my local MySQL database and after a time interval or when system connected to internet it will update my live MySQL database remotely. Is it possible? I have an inventory ...
2
votes
0answers
140 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
49 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
118 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
53 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
35 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
135 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
104 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
52 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
49 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
70 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
418 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
241 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 ...