All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

2
votes
0answers
18 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
58 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
287 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
44 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
69 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
45 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
214 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
73 views

Why is our 1000 MB database file not uploading completely via phpMyAdmin?

We have an e-commerce Magento site and a large database of 1000 MB for that site. When we try to import the database through phpMyAdmin, it's not uploading the full databse, a lot of tables will be ...
2
votes
0answers
131 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
84 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, 1:...
2
votes
0answers
79 views

Merging Databases with Same Schema

I have been developing a (MySQL) database in the cloud for the past few months to link in to a web based service. Following a change to the roadmap the initial clients will be running on standalone ...
2
votes
0answers
114 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
186 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
29 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
243 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
177 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
381 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
356 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 op_adv_effects....
2
votes
0answers
182 views

(VS Comm./VB6) How to Select from 2 different data sources/formats in one query?

I have two working TableAdapterQueries in my project, one selects from a MySQL database on a virtual server I own, the other is an Oracle PL/SQL database on a separate server. The data (and datatypes)...
2
votes
0answers
240 views

Finding missing_index_details in MySQL

MS SQL Server apparently can tell you what indexes would be useful for your query. Is there anything similar in MySQL? This article (scroll to "Find missing indexes") says that in Microsoft SQL ...
2
votes
0answers
68 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
572 views

MySQL SSL using MariaDB JDBC Driver

We have the following setup: Ec2 instance running a jboss server with mariadb (1.1.7) datasources setup to connect to an rds instance running MySQL 5.6.21. We would like to turn on SSL on for this ...
2
votes
0answers
95 views

mysql workbench connection type tcp/ip over ssh with libpam-google-authenticator enabled

I'm attempting to connect to my MySQL Database using the Connection Method "Standard TCP/IP over SSH". I filled out all the SSH and MySQL connection details correctly, however I get the message: "...
2
votes
0answers
349 views

How can I correct MySQL replication while using federated tables?

This is an overview of what I have going I have 2 server A (10.0.4.22) AND B (10.0.4.29) A is a replication of B and B is a replication of A (yes it is a ring replication) ON A I have 2 table uat and ...
2
votes
0answers
42 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
148 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
124 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
54 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
2k views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
2
votes
0answers
82 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders (...
2
votes
0answers
77 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
670 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
42 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
5k views

How do I access a result set from a StoredProcedure… in another StoredProcedure

I have a non-trivial SELECT statement and I don't want to write it twice (standard SW development considerations). I want to use the results in two stored procedures. I am imagining something like ...
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
603 views

Upgrading from MySQL 5.5 to 5.6 - timestamp issue?

I'm looking at upgrading my Percona Server setup from 5.5 to 5.6. Reading the upgrade docs from MySQL sounds mostly straightforward except for anything involving time/date columns. As far as I can ...
2
votes
0answers
86 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
100 views

mysql vs mongodb cache/load balancing

I have a system where users can 'like' content. There will likely be many hundreds of these likes going on at once. I'd like it to be AJAX driven so you get an immediate response. At the moment I ...
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
2k views

Analyzing MySQL crash due to corrupt InnoDB page. Failed page checksum

Lately we have been pestered by infrequent sporadic database crashes on one of our MySQL instances. Each time it is due to a failed page checksum. It happens on a specific database but spans multiple ...
2
votes
0answers
106 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
185 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
128 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
90 views

designing mysql for large tables

Currently I've been assigned the task to research ways to speed up an existing project. It's going to be rebuild so we can start from scratch, the current implementation has been build by another ...
2
votes
0answers
237 views

How to index for a group by with columns from different tables?

Is it possible to create an index from columns of 2 different tables? If not, how do we optimize a query that does a group by on columns of 2 different tables? For example: the following ...
2
votes
0answers
3k 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
837 views

How do I safely import MySQL data in a directory to a running MariaDB server?

We're integrating another office into ours. That includes some of their data. I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
2
votes
0answers
932 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
488 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). ...