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

3
votes
3answers
35 views

Call to default values (MySQL) in a procedure

For the sake of an experiment, we're working with the following procedure (same DB, multiple tables): delimiter // CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name ...
3
votes
2answers
54 views

Is it a good approach to duplicate data in a database?

I have a program that needs to store traceroutes (among other data). Here is a diagram that depicts the business scenario: My use case right now is to store the traceroutes using the following ...
0
votes
2answers
34 views

Stored Procedure Is Slower Than Query Due to Collation

I'm running two servers with MySql installed. I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development ...
0
votes
2answers
25 views

Good reason to convert LONGTEXT column to smaller width if field is smaller?

We have a database that uses a LONGTEXT type for a column that does not need that much space. The maximum row size currently has about 6500 characters and that's with a few million inserts so it's ...
4
votes
2answers
3k views

Ansible: How to change MySQL server root password by reprovisioning the server

I have provisioned my server with Ansible playbook. I've used the root/bedrock-Ansible playbook. One of the tasks was to set up mysql server together with mysql root user password. Now I urgently ...
0
votes
1answer
108 views

How to store complex data using MySQL and JSP

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 ...
0
votes
1answer
129 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
32 views

ON DELETE CASCADE doesn't work even storage table is InnoDb

I tear my hair with the ON DELETE CASCADE Clause. In fact I have two tables I'm on linux and I have installed lamp with phpmyadmin. That's the tables : CREATE TABLE a( Idatable bigint(20) ...
0
votes
2answers
708 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
2answers
116 views

How does InnoDB access disk?

I know that block(multiple disk sectors) is the basic unit of data transfer for the virtual file system so, if NTFS uses 4 KB cluster size for files how does InnoDB access disk if it uses 8 KB page ...
2
votes
0answers
26 views

Align auto increment index to next thousand

I am using MySQL. When applying a big change to a production database, I would like to change the next auto-increment index of all my tables to align them to the next available thousand. In other ...
1
vote
1answer
129 views

How to speed up queries in MySQL on a table with ~50 million rows

The table has 4 columns of type datetime, smallint(6), tinyint(4) and decimal(15,5). The first 3 columns are PK. There are four indexes on the table: one with all the PK columns, and one with each ...
1
vote
1answer
177 views

MySQL Create Table with Partition Very Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz , ...
1
vote
2answers
80 views

How can I remove 'Using temporary ' and speed up my query

Hi I was working for hours to speed up this query using indexes, but its useless.. im tyring to optimize by eliminating 'Using temporary' and using only indexes. I wonder if there is another ...
0
votes
1answer
79 views

Need help improving query performance

I have this simplified version of my database: http://sqlfiddle.com/#!9/4b903/2/0 I have a million rows in history and my problem is the query is so slow. It would take more or less 2 minutes. ...
1
vote
1answer
161 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 ...
1
vote
1answer
73 views

Load Balancing multiple databases in a multi-tenant setup

We have a multi-tenant setup where each client of ours runs a DB that is identical to every other client. We were using Mysql sofar and with CentOS 7.x ,switched to MariaDB. If we want to distribute ...
1
vote
1answer
29 views

Minimizing downtime during MariaDB Replication Setup

I have the task of setting up MariaDB (10.0) replication from a master running in a datacenter to a slave running out in an AWS VPC. Reading up on the instructions on Setting Up Replication - MariaDB ...
2
votes
1answer
983 views

using Aria vs InnoDB vs MyISAM

I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and ...
0
votes
1answer
435 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 ...
2
votes
1answer
184 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
1answer
82 views

mysql trigger on a federated table

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a ...
3
votes
1answer
69 views

MySQL optimizing - use ram instead of CPU?

I am on a dedicated server with Intel Xeon D D-1520 4/8t 2,2 / 2,6 GHz, 32 GB of RAM DDR4 ECC 2133 MHz, and 2 x480GB of SSD SOFT. The problem I am encountering is that MySQL eats up all of my CPU ...
0
votes
1answer
198 views

Too much IO to MySQL InnoDB log files

I am using MySQL version: 5.5.38 I have dedicated HDD to InnoDB log files using innodb-log-group-home-dir option. atopsar -d 30 shows big load (>50%) to this HDD The thing is that I have only 75 ...
0
votes
1answer
117 views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...
2
votes
1answer
100 views

Difference beetween mysql 5.5 and 5.6

I have a big performance problem beetwen my local mysql instance and the server. I have this request : DELETE a FROM a LEFT OUTER JOIN ( SELECT MAX(id) as id, `v_id`, `v_statut`, `c_controle`, ...
0
votes
1answer
20 views

Can I load a CSV into a stored procedure that inserts into multiple tables?

OK, an example is often easier to follow, so here is my procedure: delimiter // CREATE PROCEDURE mysql.insertTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, ...
1
vote
3answers
666 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 ...
0
votes
0answers
90 views

Access a remote mysql from a remote node application

I'm approaching MySQL for the first time in life. I have a machine with a MySQL and Wordpress environment correctly working (let's say at 1.1.3.4) but I want to access it from a remote node.js ...
-6
votes
1answer
30 views

SQL Simple Question [closed]

This looks correct to me, however I receive a table where figures exceed $40.0?
4
votes
1answer
188 views

Query to determine start and end dates based on time overlap

Given the following data: id | user_id | started | closed | dead ------------------------------------------------------------------------------------------- 7714 ...
1
vote
1answer
25 views

(SOLVED) Mysql bug decimal comparison

I'm currently writing unit tests for a stored procedure that calculates the amount of taxes and the tax rate based on the country/province the customer lives in. Somehow, MySQL considers that ...
0
votes
1answer
262 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model. I've got 4 tables: ...
1
vote
1answer
26 views

Managing ibtmp1 File Growth on MySQL 5.7

We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where ...
0
votes
1answer
37 views

MySQL - Finding Gaps of Date Ranges - Overlapping Dates

I have been struggling for a long time with generating gaps in date ranges, according to a set. CREATE TABLE `terms` ( `id` int NOT NULL PRIMARY KEY, `term_begin` date NOT NULL, `term_end` date ...
3
votes
0answers
35 views

MySQL queries waiting for other queries to finish

We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day. Currently we use InnoDB Engine for our tables. The problem: ...
2
votes
1answer
549 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 ...
2
votes
3answers
3k views

Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error

Using MariaDB 5.5.36. Here's what I got while trying to start MariaDB: # syslog Dec 14 09:40:52 vps1497 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Dec 14 09:40:52 vps1497 ...
0
votes
1answer
60 views

MySQL - NOT IN … vs LEFT JOIN

I am trying to understand in terms of performance which approach is better: Query 1: SELECT * FROM table1 WHERE col1 NOT IN(SELECT col1 FROM table2) Query 2: SELECT * FROM table1 LEFT JOIN table2 ...
-1
votes
1answer
46 views

Why and how to index a MySQL database [closed]

How to do indexing and what is the use? Please explain in such way so I can use it practically.
1
vote
1answer
616 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
1
vote
1answer
58 views

Insert all dates from a month into a table

I'm confused. How to get all dates from a month and insert them into a table in a database? I want to do this with a trigger so when I make a period, ex April 01-04-2016 / 30-04-2016, then in table ...
0
votes
1answer
194 views

Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs). I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those ...
0
votes
2answers
31 views

MySQL encryption for 'database at rest' on cloud services

We are migrating our system to a cloud environment (AWS), and have been given new client requirements for having our MySQL data-at-rest encrypted. Currently it is unclear how far this should go. It ...
0
votes
1answer
304 views

Tracking the update or changes in mysql table

I need to stream the changes to elasticsearch to keep in sync with my database(mysql). What will be best way to do this ? I have thought for below approaches Using trigger( but my table will have ...
0
votes
3answers
253 views

Run MySQL replication by schedule or on demand

I have a master-slave replication currently configured. I know this setup runs real time. Is it possible to sync only the databases on schedule (daily or weekly) or by demand? Thanks!
1
vote
1answer
74 views

Why using where in indexed field is slow?

we have a big table. the table structure is : CREATE TABLE `visit_logs` ( `id` int(10) NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, `ref_id` int(10) NOT NULL DEFAULT '0', `link_id` ...
3
votes
1answer
136 views

How to run OPTIMIZE TABLE on a 3-node cluster?

I've my hands on a 3-node Percona XtraDB Cluster where, according to mysqlcheck, some tables are corrupted (some indexes contain the wrong number of entries): mydb.mytable Warning : InnoDB: Index ...
0
votes
1answer
50 views

Phpkb Query Optimization

Running a phpkb CMS on a ubuntu lamp server. mysql - 5.5.28-0ubuntu0.12.04.3-log The tables in this database are not large in fact I have not found one over 10k rows. Every five min I get a 'copy ...
1
vote
2answers
37 views

Find duplicate rows violating unique constraint

Recently We meet a mysql error on slave, because a transaction can not be executed on slave. We found two records have the same unique in master, but inserted successfully. I have post two ...