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

0
votes
1answer
108 views

How to recover performance_schema after restoring from partial innobackupex backup?

I am taking a partial backup from a Master server, which needs to be restored on a to-be slave server. I only need one of the databases, and the mysql database. I am following instructions on ...
1
vote
2answers
2k views

Can't create file /var/lib/mysql/user.lower-test

MySQL database has been problem free for a few months. Today when I checked for upgradeable packages using the Synaptic Package Manager it came up with the various MySQL components (-common, -client, ...
3
votes
1answer
199 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
0
votes
2answers
109 views

MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?

Here's what I'm trying to achieve: use a trigger to divide an update query which updates more than 1 columns into several update query and each of the query updates just 1 columns. For example, the ...
1
vote
1answer
118 views

MySQL After Update Trigger apparently not running

I have the following After Update trigger on a MySQL table: CREATE DEFINER = 'root'@'localhost' TRIGGER phpbb_rathermenacing.phpbb_bbdkp_memberlist_AUPD AFTER UPDATE ON ...
-1
votes
0answers
18 views

Backup whole database including procedures [on hold]

I'm using MariaDB 10.1. I backup my database using mysqldump. My stored procedure, view, function and event definitions are not backed up. How to backup full database including tables, functions, ...
0
votes
1answer
127 views

What is the best way to update a Production database with Test instance changes

I have deployed a Mysql database into Production. As I work on the next release of software I am making changes to a test database. I now need to update our Production system without losing the data ...
0
votes
1answer
145 views

Replication Issue - Changing the replication-do-db filter

I have a server that someone has setup which are acting as a master on both database. Since they have been configured as both master on the same database, changes one on end has caused all sorts of ...
0
votes
1answer
131 views

How to/Where to find MySQL templates for Toad Data Modeler

I have recently install Toad Data Modeler 5.3.4 and it only has Oracle data model templates. Where can I find and import the MySQL data templates so that I can create MySQL database models. I have ...
0
votes
0answers
23 views

Query that runs lighting fast in Postgres send MySQL to 100% CPU and takes forever [on hold]

I have a query that runs against a database that an industry association puts out every month. The query is complex in that it joins together 26 tables and brings back only 15 rows from among tens of ...
6
votes
1answer
11k views

UPDATE table based on the same table

I have a table with product descriptions, and each product description has a product_id and a language_id. What I want to do is update all of the fields with a language_id of 2 to be equal to the same ...
1
vote
1answer
190 views

MySQL query to create a Matrix view using pivot

I have a table with the following fields: p1 p2 s1 s2 where p1 -> player1, p2 -> player2, s1 -> game1, s2 -> game2 The number of players can vary from 3 - 10. The table have the results between ...
0
votes
0answers
11 views

Connect to MySQL Webserver (not localhost)

I have a problem. And I hope someone can help me or point me in the right direction. I am working on a project and need to sync a MySQL database (running on PHPmyAdmin) with a MS SQL Server. I ...
3
votes
1answer
658 views

What does “cleaning up” mean in MySQL InnoDB transaction status and how to prevent it?

In our database (MySQL 5.6.22 on RDS) we have a lot of transactions (~500) in a state "cleaning up" under the load: > SHOW ENGINE innodb STATUS; ... ------------ TRANSACTIONS ------------ ... ...
0
votes
0answers
15 views

Copy data from one table into other on some condition

I have two table as follows. Table : city city_id city_name state_id 1 Cachar 1 2 Darrang 1 3 Nicobar 1 Table 2 : locality pincode Address ...
0
votes
1answer
129 views

Error code 1064 when trying to add anything manually

So i tried adding via php and i noticed i got a error , so i went into phpmyadmin and tried adding it manually by clicking the SQL in phpmyadmin and write in INSERT INTO `test`(`IDtest`, ...
0
votes
0answers
7 views

Recursive Conditional trigger for insert in table MYSQL

user[ user_id, parent_user_id] user_id parent_user_id 1 null 2 1 3 1 4 1 5 1 user_assigned_role [user_assigned_role_id, role_id, ...
1
vote
3answers
627 views

Fastest way to do bulk import in MYSQL

I have got a question about the fastest way to copy a large number of records (around 10 million) from 1 table to another in MySQL. The target table has 1 primary key, 4 indexes and 3 foreign key ...
1
vote
1answer
256 views

Handling SQL updates for production deployments

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
0
votes
1answer
581 views

inno_db_buffer_pool_size and key efficiency

I have the following metrics from mysql workbench After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size. While the used buffer ...
0
votes
1answer
273 views

Debugging lock time out with show engine innodb status

I am keeping getting this lock wait time out. Below is my current settings. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to ...
2
votes
1answer
556 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 ...
1
vote
2answers
196 views

join same table twice and get the results of rows

i have table like wp_term_relationships object_id term_taxonomy_id term_order 194 2 0 194 7 0 194 8 0 194 ...
0
votes
1answer
464 views

Shell script for backup binlog files for every 8 hours in MySQL

I'm writing a shell script for point in time recovery for MySQL Instance. Our requirement is backing up Binlog for every 8 Hours (Daily 3 times) using mysqlbinlog and it should backup binlog starting ...
0
votes
1answer
19 views

PostgreSQL vs MariaDb On Read/Write when table has about 200M Rows [on hold]

I want to build a new web application with my collected data, but I don't know witch DBMS is best choose for my Project, MariaDB or PostgreSQL there is 2 table : categories and data more than 200 ...
-2
votes
1answer
118 views

How to store complex data using MySQL and JSP [on hold]

At present I am creating a small web application called slam book. We know slam page contains so many fields. By using MySQL tables it takes too many columns and leads to complexity for accessing the ...
1
vote
1answer
169 views

Using index on datetime field not working really

Here is my query select msisdn, sum(score) as score from contest_player_update_history_ideacontest6_35 where request_date >='2015-01-14 00:00:00' and request_date <= '2015-02-03 23:59:59' ...
0
votes
3answers
72 views

What is the performance impact of indexing a frequently updated column?

I just want to know if I'm doing this right. I've searched google for hours already and I can't find a straight answer. I have a table with potentially a very large amount of inserts and updates. Any ...
3
votes
1answer
54 views

Why would MySQL request a shared-lock after setting transaction level to READ UNCOMMITTED

Running a processing job in multiple parallel batches which basically reads big chunks of rows to update a few - it's OK here to use a lower transaction level since I know the relevant values won't be ...
3
votes
3answers
67 views

How to format a phone number in a SELECT statement?

I have a database with table name student. I would like to display the register_number and phone_number of the student. The phone_number should be in the following format: +91-123-456-7890 and if ...
0
votes
1answer
68 views

Delete query does not end

I have a following table with 1 million records : CREATE TABLE `activity` ( `id` int(9) unsigned NOT NULL, `user_id` int(10) unsigned NOT NULL, `subscription_id` int(10) unsigned NOT NULL, ...
0
votes
1answer
441 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
0
votes
0answers
13 views

Transaction on delete is committed, but for a brief time the entries can still be read?

I have a Java application that uses Spring JDBC and HikariCP for pooling MySQL connections. There is a high level of concurrency where a thread creates a unique entry for a given task in a single ...
0
votes
2answers
768 views

MYSQL Row Count Limits?

What is the maximum row count for a table in MYSQL? I have a system with around 600,000,000 rows in three tables. Average Data Length (from data_length column) for these tables is 58,000,000,000 ...
0
votes
1answer
130 views

Update records that match like regex from another table

I have two tables - ProductCategories and Products ProductCategories --- productid categoryid 1 2 3 4 Products -------- productid productname 1 Apple 2 ...
1
vote
1answer
17 views

MySQL 5.6 Online DDL lead to to much table-metadata-locks when I just add index

It's normal that table-metadata-lock shows up for a short time when ALTER TABLE t add index ... starts and ends. But I have encountered all DML operations blocked in Waiting for table metadata lock, ...
1
vote
1answer
16 views

MySQL trigger to update a key if record exists

I'm trying to build a trigger in MariaDB to follow this logic: 1. If the primary key val exists, increment the counter column 2. Else, insert the primary key into the table Here is my prosed ...
1
vote
1answer
18 views

How did you setup MySQL Replication with autofailover that is app transparent?

Recently told MySQL is shelving MySQL Fabric. Interested how others have implemented a MySQL replicated environment that is app transparent. I am considering using HA Proxy to host virtual IP ...
0
votes
1answer
115 views

InnoDB compression: memory usage

InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on ...
5
votes
3answers
14k views

How do I get the current and next greater value in one select?

I have a InnoDB table 'idtimes' (MySQL 5.0.22-log) with columns `id` int(11) NOT NULL, `time` int(20) NOT NULL, [...] with a compound unique key UNIQUE KEY `id_time` (`id`,`time`) so there can ...
0
votes
0answers
10 views

MySQL: database setup to properly deliver non-ascii characters

I'm facing the following problem: I have a MySQL database which contains non-ascii characters in some records. Characters like "ñ", for example. What kind of setup do I need to perform at the ...
0
votes
1answer
204 views

mysql Replication and Point in time Recovery

I have a master and a slave mysql database (version 5.1). Is it possible to dump the database only on the slave side? If I do a dump with mysqldump I have a fulldump. Which files can I use for a point ...
1
vote
0answers
30 views

Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Background I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
6
votes
2answers
15k views

MySQL: Error reading communication packets

I get this warning in mysql, [Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets) I have been through few topics in google ...
3
votes
2answers
3k views

Problem with new MySQL Workbench connection

I'm working on Debian 8 and wanted to create database using MySQL Workbench. I did everything in this tutorial, but when I created a new MySQL Connection, the program returned: Failed to Connect ...
0
votes
1answer
200 views

How to recover a dropped mysql database with its procedures?

I had some databases on a server and MySqlserver on this server has been uninstalled accidentally by someone. I had some backups on this server and could recover all tables and their data successfully ...
0
votes
1answer
21 views

Mysql scaling up vs scaling out

I have an application where each client gets their own database. This is for security, backup ease, and portability. It provides great flexibility and performance has been fine. I am up to 300+ ...
0
votes
0answers
7 views

Linked Server Mysql Workbench

Is it possible to use 2 MySql Workbench at a time by connecting it using Linked Server? Or is there any other alternative? By the way, I'm working on a web application with jsp and java.
-1
votes
0answers
45 views

What are the general practices considered to constitute a good user id? [closed]

I could not settle on a good pattern to generate my user ids for an upcoming project that could scale up in a few months. The project is about ordering food online and I need to store user ids and ...
1
vote
3answers
685 views

Why does this procedure raise a privilege error?

I am having trouble getting the following procedure to run on a remote MySQL database - the given error is privilege based (#1227). Locally, the procedure runs fine. QUESTIONS Could somebody help ...