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
2answers
32 views

How to check if a record is linked with two or more values in a left join

In MySQL, I have a table of products connected with a different table with categories. I often show the list by making a LEFT JOIN and GROUPING the results: e.g. LEFT JOIN gives me: Name | Category ...
3
votes
3answers
4k views

ibdata1 grows exponentially when innodb_file_per_table is configured

I Have installed a MySQL Cluster with InnoDB (innodb_file_per_table enabled subsequently), but since I switched to innodb_file_per_table, the file ibdata1 grows (2GB at month). Is My my.cnf file is ...
2
votes
1answer
134 views

MySQL running fine while data files (disk) are not present. Data folder nowhere to be found

I'm running MySQL server 5.6 on Windows 2012 R2, and since the installation didn't go as planned, I had to re-install it to change the data directory path. The installer for some reasons never gave ...
2
votes
1answer
112 views

Should I use utf8_unicode_ci or utf8_slovak_ci?

I was using utf8_slovak_ci collation until now, but I read that utf8_unicode_ci sorts very wide range of languages. Does it mean, I do not need to use separate collation for every language and I can ...
0
votes
2answers
27 views

MySQL : How do I partition based on Month of date

I have a table having id as primary key and date column. I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that ! I am using mysql ...
0
votes
0answers
17 views

MySQL along with Firebase for data sync

I'm currently working on a mobile app powered by a web platform, and the mobile app will have some offline capabilities. The application has a small amount of DB entities, and only about 3 of them ...
1
vote
2answers
138 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
4
votes
1answer
2k views

Stuck outside MySQL / XAMPP

I have been using XAMPP (on Windows XP/XAMPP 1.7.4 /PHP 5.3.5) for quite a while now, and last morning I started to get some weird messages when I was developing my website. It happened yesterday and ...
2
votes
3answers
521 views

Cannot ALTER TABLE

I have a particular table that I cannot add a key for: mysql> ALTER TABLE tasks ADD KEY `fruitful_user_count` (`user_id`, `is_fruitful`); ERROR 1034 (HY000): Incorrect key file for table 'tasks'; ...
1
vote
1answer
64 views

How can I create a MongoDB based on MySQL schema?

How can I create a MongoDB based on MySQL schema? This is my schema: productCode VARCHAR(15) productName VARCHAR(70) productLine VARCHAR(50) productScale VARCHAR(10) productVendor VARCHAR(50) ...
76
votes
7answers
47k views

How can I monitor the progress of an import of a large .sql file?

I am importing a 7 GB foobar.sql to restore a table in a local database. $ mysql -h localhost -u root 'my_data' < foobar.sql $ mysql --version /usr/local/mysql/bin/mysql Ver 14.12 Distrib ...
3
votes
1answer
338 views

Stuck in “query end” status every 5 minutes

I'm sick of seeing data in the slow query log, so I decided to check out what the fuss was all about. An UPDATE query that is running up to tens of times per second is sometimes really slow. Here is ...
2
votes
2answers
121 views

How to get data from three different tables

This question is continuing of this issue: How to get data from two different tables? Now I need to connect the table Sessions with 2 tables: Input Downloads When I use this query with real ...
0
votes
0answers
12 views

MYSQL hangs in sending data state

I have MySQL query which hangs in sending data phase. When analyzing slow query logs Rows_sent: 176975081 Rows_examined: 408406766 These numbers of rows are greater than the number of rows ...
0
votes
0answers
18 views

Seeking Alternative To TEMPORARY Table For Limited Permissions [closed]

The following query is valid but I do not have permission to create tables. I am seeking an alternative with equivalent results. CREATE TEMPORARY TABLE temp_id(temp_name VARCHAR(50),id INT); INSERT ...
0
votes
1answer
71 views

MySQL - Compare record value against previous [closed]

Rewording my question: This is how my data looks like: Member Group Status Start_date End Date 1 2 Active 4/12/2011 11:19 NULL 1 2 Active ...
-1
votes
0answers
27 views

Counting outdated records

I would like to know if I am able to add a subquery that will sum/count outdated ( FROM zlec ). The field that contains the date is zlec_do, and the type is datetime as such 0000-00-00 00:00:00. The ...
0
votes
1answer
26 views

MySQL workbench alter table is incomplete

I have just upgraded MySQL 5.6 to 5.7 and workbench from 6.2 to 6.3.6 to have the latest and do some modelling. The upgrade went fine and the app runs perfectly but something is different and I can't ...
0
votes
0answers
14 views

MySQL Trigger Replication between Master and Slave

we are running a rather highly loaded MySQL Application replicating between Master and two Slave instances. We are using Statement-based replication. Recently, we have upgraded our hardware and a ...
0
votes
1answer
15 views

How to fix MySQL anonymous account security issue?

How to fix this security issue on MySQL? [!!] User '@localhost' is an anonymous account.
2
votes
3answers
80 views

MySQL query takes over a minute to complete

I am trying to pull calls associated with website visits from our huge database... I have got SQL that works, pulls correct results it just takes over minute to complete... Here is the SQL SELECT ...
0
votes
1answer
164 views

MySQL partitioning table by date and hour

I have a table with a timestamp field. This table will have millions of rows every day so the idea is to create partitions by date and hour. Every day I will delete partitions older than 10 days and ...
1
vote
1answer
212 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
1
vote
1answer
27 views

mysql workbench migration changed my database. Can I trust it?

I'm migrating a large production database(s) from mysql 5.5 to 5.6. Used Mysql Workbench Migration Wizard which creates a shell script to run on source and taget machines. The process worked well but ...
3
votes
2answers
40 views

MySQL: Soft Delete post likes

I have implemented a like/unlike features for posts in one of my projects. The like table is normalized and has a column visible. The schema is as follows: Column Type Null Default ...
0
votes
3answers
4k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
0
votes
0answers
15 views

MySQL Enterprise Backup

Hi I've been looking for MEB version 3.12. But it seems the Oracle's eDeliver does not have this version. The available version is 4.0.xxx. I need this older version because my MySQL server version in ...
-1
votes
0answers
13 views

bonfire and codeigniter installation error [closed]

When installing bonfire i am getting below error...and even if i create this table manually then it says that incorrect timestamp 1454485603. Error Number: 1146 Table 'me.bf_ci3_sessions' doesn't ...
1
vote
1answer
158 views

Using file .log.3.gz

I have a file which I need to look at for errors, I got it from someone its extension is mysql.log.3.gz and when I uncompressed it, it has mysql.log.3, It is a log file? And if I open it in text ...
0
votes
1answer
160 views

SET sql_log_bin = 0 in RDS MYSQL 5.6

I have moved into RDS MYSQL 5.6 and now I have a problem. I have 5 read replicas and a/z master. I am trying to alter a table and want to change a column value and also on some tables I want to add a ...
0
votes
2answers
244 views

Connect MySQL workbench with MySQL container

I pulled MySQL container from Dockerhub and it is running on my macbook pro as you can see as follow CONTAINER ID IMAGE COMMAND CREATED STATUS ...
0
votes
0answers
21 views

How can I restore a MySQL dump faster on Windows?

I am restoring some InnoDB and MyISAM .sql files that are on the order of GBs (ranging from 1 GB to ~ 10GBs) and they are taking a day or longer to restore. The command I am using currently is: use ...
0
votes
1answer
42 views

Copy table form MS-SQL server to MySQL, is it possible? [closed]

I have a procedure, say "proc1", inside a MS-SQL server. I would like to create a table (copy of proc1) inside a MySQL server. So far what I do is this: 1-Create a connection to MS-SQL Server 2- run ...
0
votes
1answer
664 views

Can't Delete Orphaned MySQL Temp Table

One of my servers had a hick-up yesterday and decided to leave temporary table while (while running alter table add indexes). ls -l sql -rw-rw---- 1 mysql mysql 8570 Mar 13 12:05 #sql-ib32694.frm ...
1
vote
0answers
34 views

Does mysql's `SHOW PROCESSLIST` appreciably slow down other queries?

I'm just wondering if my measurement of the processes is affecting the speed of the processes. I was testing alternative methods of copying a database. 1.) mysqldump followed my mysql mydb < ...
1
vote
1answer
200 views

How can I remove references to tables after removing .frm files?

So I had a bunch of csv files in my /var/lib/mysql/imports directory for importing into mysql tables I had created. To clean up the folder I deleted all the files in the directory (incorrectly ...
0
votes
1answer
31 views

Staging MySQL databases

I'm building a Web/Web service application based on a number of (MySQL) databases. The databases are all built from externally derived data from dozens to hundreds of sources. My general plan is to ...
2
votes
1answer
1k views

Issue when connecting to open shift via mysql workbench on Mac

I am trying to connect to open shift with mysql workbench on an iMac. A colleague has shown me his setup on Windows which looks exactly as follows (other than the usernames / passwords changing) ...
0
votes
0answers
13 views

Reliability of MySQL Events Table

Is there any limitation of usage of MySQL Events table. Let us say i will have 10K of entries which will be executed across 3k schemas. They all are not same time but at-least 3K events will be ...
3
votes
1answer
76 views

mysql custom query logging for inter-software data exchange

Some 2-3 years I had written some triggers (one per table) in SQL server allowing me to log queries in our main software. ALTER TRIGGER [dbo].[SUIVI_REQUETE_CAT_CATBRUT] ON [dbo].[CAT_CATBRUT] AFTER ...
1
vote
1answer
110 views

Delete rows where matched value is twice as much

I'm trying to match two tables with two columns, table1 and table2. Each has id and price columns. I want the query to match each id and compare the price, then delete the row if the price in the ...
3
votes
1answer
7k views

Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don't work?

I have a Django web application that stores data in a MySQL InnoDB database. There is a particular page that is accessed a lot on the django admin, and the query is taking a long time (~20 seconds). ...
2
votes
1answer
179 views

Can Unicode columns include non-Unicode values in SQL Server?

I need to convert Unicode column values from UTF16-LE to UTF-8 to then import into MySQL. Is it safe to assume columns of NVARCHAR, NTEXT, NCHAR, BIT, INT, DECIMAL, FLOAT, and DATETIME all must be ...
1
vote
1answer
317 views

Speeding up a simple select query on Mariadb

I have a 100k rows table with the following structure: +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
54 views

Does INFORMATION_SCHEMA always contain up-to-date metadata?

This query prints the size (in terms of data and indexes) of the tables in mydatabase: SELECT table_name "Table name", round(((data_length)/1024/1024),2) "Data size", ...
3
votes
1answer
141 views

How to get data from two different tables? [closed]

I have these two MySQL tables: tab1: +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
1k views

Mariadb on Centos 7 not changing max connections based on my.cnf

The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly. [mysqld] ...
0
votes
1answer
502 views

Mysql can't write to filesystem

I need to output some queries as csv files. The directory is in the webroot and I want it to be writable by apache too, so I'm trying to add mysql to the www-data group (on Ubuntu). I thought this ...
0
votes
1answer
4k views

How to UPDATE a column to SET previous row value to NULL cells?

I have a table as id value 1 music 2 3 movie 4 5 6 book 7 8 Some values are missing, and I want to assign the value of previous row to produce id value 1 music 2 music 3 movie 4 ...
1
vote
2answers
45 views

mysql query gets slower with increasing page number

I use laravel 5.2 with mariadb (ARIA engine) and found that my query for creating a gallery gets slower with increasing page number. select `pics`.`id`, `pics`.`title`, `pics`.`hits`, ...