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
0answers
3 views

i can not load back table with new primary key

when i try to load table from dump file, primary key removed. My original file name is stream and db name is MYSql. I am using command prompt and trying to import from there. Can you give exact ...
0
votes
1answer
22 views

On the fly MySQL balance

Please excuse my English translation that makes by Google Translate I need to solve the following in a MySQL query without php or another language: In the “A” column I have a string value, in ...
0
votes
1answer
8 views

Monitor locks over long period of time

I am having some issues with one of my tables in a production server. I know there are many diffrent threads running on this table. I need to put some kind of monitoring tool to see how many locks ...
3
votes
2answers
11k views

How to use RESTRICT for Foreign Key in mysql?

In the database structure of CREATE TABLE Country ( name varchar(40) NOT NULL, PRIMARY KEY (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE City ( name varchar(40) NOT NULL, ...
0
votes
1answer
20 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: ...
0
votes
0answers
7 views

How to tune MySQL Data engine Innodb

Queries are taking a long time, so I figure out that maybe there is a tunning issue. SELECT CEILING(SUM(data_length+index_length)/power(1024,3)) Buffer_Pool_SIZE FROM information_schema.tables WHERE ...
4
votes
1answer
5k views

MySQL Index creation failing on table is full

UPDATE: tl;dr: The problem was MySQL uses the TMPDIR when creating indexes. And my TMPDIR was the one running out of disk space. Original Q: I'm trying to add an index to an InnoDB table, and ...
0
votes
0answers
8 views

How to use column result as column name with MySQL [duplicate]

At the moment I have sample table as such doc_name | processed_date | doc_version ---------------------------------------- doc_1 | 2015-01-01 | 1.0 doc_1 | 2015-01-02 | 1.1 doc_1 | ...
0
votes
1answer
804 views

MySQL failover - Master to Master Replication

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered ...
0
votes
1answer
15 views

Upgrading to MySQL 5.5 from 5.0 looks broken

I was using SLES SP3, and had earlier MySQL 5.0.67. I upgraded it to 5.5.48 (available with the CD) after upgrading my OS to SP4. I edited the /etc/my.cnf file, and replaced skip-locking to ...
0
votes
0answers
25 views

MySQL replication/cluster of multiple databases

I looking for a suitable MySQL replication technology. I'll try to explain our use case first. We have several applications: - 1 public web application - multiple customized enterprise web ...
0
votes
0answers
7 views

how to tune Mysql when storage engine is MyISAM

MyISAM queries results too slow,What changes should i make, if Ram 16GB DB 500GB mysql> SHOW STATUS LIKE 'key%' +------------------------+-------------+ | Variable_name | Value | ...
0
votes
1answer
7 views

How password can be prompted in percona xtrabackup

How password can be prompted while using percona xtrabackup.--ask-pass option is not working with xtrabackup. Thanks in advance.
0
votes
1answer
111 views

Locking because of triggers mysql

I have a memory table and I am updating it somewhere around 3000 updates per second. I setup a another table in Innodb and then created a Trigger so that all data can be send to the innodb table as a ...
0
votes
1answer
37 views
+50

Designing table for document annotation with future columns

I am working on a web application that allows students/researchers to annotate PDF documents shallowly by (manually) counting interesting phenomena. Right now, there is a total of about 45 of these ...
0
votes
1answer
234 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
0answers
7 views

Generate UUID v4 automatically in MySQL

Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key. Use Case Many systems will insert to the table, some may specify a UUID when ...
0
votes
1answer
270 views

WordPress MySQL writes during “Flush Tables With Read Lock” are rejected instead of being queued

I'm running into an odd issue when setting MySQL into a brief read-only mode for backup. Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to ...
0
votes
1answer
21 views

Export .MWB to working .SQL file using command line

We recently installed a server dedicated to unit tests, which deploys updates automatically via Jenkins when commits are done, and sends mails when a regression is noticed > This requires ...
5
votes
5answers
8k views

Field types for columns in a MySQL query

Is it possible to get the field type from a MySQL query, in the same way you can get it from a table with the SHOW COLUMNS command? edit: i should clarify that a bit. I am aware that I can get the ...
2
votes
2answers
61 views

Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen

I have a table with about 8.5m rows in it. The table is tokudb and it has the indexes described below. I'm experiencing dismal performance when trying to run update statements like the following: ...
1
vote
2answers
99 views

Recreate a table that was deleted during an ALTER statement (errno: -1)?

For the third time in the past 2-3 weeks I've gotten the following message during an ALTER statement: Error 2013: Lost connection to MySQL server during query SQL Statement: ALTER TABLE ...
0
votes
2answers
24 views

Mysqld crashes after hold f5 in browser [duplicate]

i have cloud vps server, cpu 512 mb. i have website. if i hold f5 in browser after 10-20 sec mysql server no responding and it gives me error SQLSTATE[HY000] [2002] Can't connect to local MySQL ...
1
vote
2answers
2k views

add column without metadata lock in mysql 5.6

I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the ...
0
votes
1answer
205 views

Suitable schema to record email sent and response received and invoicing in DB

When a form submission is made on a website: An email with form's details is sent to a set of recipients email addresses stored in a DB. The email contains a link for the recipient to show interest. ...
0
votes
1answer
27 views

See if an JSON array in MySQL contains an object whose key holds a specific date

i'm trying to find out if there is a row which contains a specific date inside an JSON array Lets say my data looks like this: Table `applications`: id | application_id | data # Rows 1 | 1 | ...
2
votes
1answer
47 views

Query with Indexes slower than without Indexes

I'm using MySQL 5.6 and my storage engine is InnoDB. I have a table with 1 million rows containing with the columns: ID (primary key) FirstName LastName foreign_key_id (foreign key, NOT NULL) ...
0
votes
1answer
11 views

Backup/Restore single database with MySQL Enterprise backup

From what I have found online is it is possible to backup/restore an entire mysql instance with MEB and also a single .ibd file (to my understanding is a single table). What I am trying to do is ...
0
votes
0answers
9 views

Tools & Requirements for MySQL Database Administration [duplicate]

I have searched a lot on google about tools and requirements for MySQL Database Administration,even though i didn't get clear answer but i came to know about some tools. Now my main question is that ...
1
vote
1answer
353 views

Master and slave tables with different character sets - will replication break?

We have some huge tables in our MySQL 5.6 database that have latin-1 as their character set, and want to convert them all to UTF-8 (along with converting the database's global character set). The ...
0
votes
1answer
35 views

Altering column data SQL [on hold]

I have a set of car data, one of the columns represents the registration year. I want to change this column to age. As the data is from 2010 I assume that the following statement should calculate the ...
0
votes
1answer
103 views

Find points within a distance using MySQL

I have a MySQL table with State Name,City Name,Zip Code, latitude and longitude of the user. I would like to get a list of user who are inside the circle or square of a given latitude and longitude ...
0
votes
0answers
10 views

Copy MySQL database between two Windows 10 laptops [on hold]

Using TOAD for MySQL Windows 10. I have a local database on a laptop that I want to copy to another Win 10 laptop with TOAD and MySQL installed on both machines. Is this possible with just copy ...
0
votes
1answer
200 views

How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true?

How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have ...
2
votes
2answers
163 views

Sudden MySQL freeze, with spike in dirty pages, but without obvious write queries

I'm systematically (1/2 times per day) experiencing a freeze in the DB server. Suddenly, all the COMMITs get stuck in 'init' state; they pile up (up to 20/30), an almost no other queries are executed ...
1
vote
2answers
2k views

Linux command to kill all MySQL sessions from a specific User?

How can i kill all sessions from a user in mysql using a linux command line command? I have a user that is using all on my database connections and insted of killing one by one i want to make a ...
1
vote
0answers
12 views

Wildcard in CREATE grant in MySQL

MySQL allows the use of wildcards for database names, in order to allow an user to operate only on a subset of databases: GRANT ALL PRIVILEGES ON `foobar%`.* TO 'user'@'%' IDENTIFIED BY ...
1
vote
1answer
14 views

mysql replication: slave is not getting data from master

here is my problem that all setup is done for mysql replication but the slave is unable to sync the master's data. to understand i am sharing link below please visit. mysql> SHOW SLAVE STATUS \G; ...
0
votes
1answer
122 views

Can I create a stored procedure in mysql using a txt file?

My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server: mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt The content of the .txt ...
0
votes
1answer
19 views

Get data from previous years dynamically

I have a table with columns id, fname, date_field. date_field's records are: 2011-03-01 2012-03-01 2013-03-01 2014-03-01 2015-03-01 2016-03-01 I want the data for the past 2 years for each record ...
0
votes
1answer
102 views

Restore InnoDB from .frm files

My database crashed and my PC and I now only have the .frm and .ibd files left. Is there any way to restore my database?
0
votes
0answers
54 views

After update triggers that deal with multiple column updates

I want to create a table for audit (audit in the sense of recording whatever a user has done in terms of data manipulation – e.g. update first name, delete contact etc.). For that I need to write a ...
0
votes
1answer
104 views

MySQLfabric — Manage setup problem

MySQL fabric does not get configured , getting these errors : [mysql@dev-dm-gitest101z /root]# mysqlfabric manage setup Error : [INFO] 1413953890.788408 - MainThread - Initializing persister: user ...
0
votes
1answer
245 views

indexable JSON datatype in MySQL/MariaDB

In PostgreSQL 9.4 there is a JSONB datatype which allows you to index on fields in the stored JSON objects. Does MySQL/MariaDB have an equivalent? I'm specifically interested in the MariaDB Galera ...
2
votes
1answer
1k views

How to restrict MySQL user to not import and export database

I have created a read only MySQL user but this user have an option to export the database through phpmyadmin. How can I disable the options Export and Import?
0
votes
1answer
50 views

MySQL Query fix and speed up

I'm newish to MySQL and a lot of my query is pieced together from this site and trial and error, so any help is appreciated. If I left out anything, or more detail is needed, I will be happy to give ...
1
vote
1answer
122 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
2
votes
1answer
67 views

Many to many between lots of tables?

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended? Each body type has its own table Each car in each body type table can has several ...
1
vote
3answers
36 views

Any script to auto test tune and benchmark mysql config?

I'm looking or willing to do a script to benchmark mysql config variations for a specific system. A script that would change certain mysql config values and do a sysbench, save results, change ...
1
vote
2answers
19 views

Can I remove binary log files not listed in mysql-bin.index?

We've had some problems with one of our more elderly MySQL servers (version 5.045). The database is back up and running OK but there were over twenty thousand binary log files in the mysql folder. I ...