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

1
vote
1answer
56 views

Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona?

I develop an application that uses MySQL and explicitly uses InnoDB table engine. In a few cases we have clients using this application with MariaDB or Percona. I understand that MariaDB and Percona ...
1
vote
1answer
57 views

When to disable MySQL Query Cache

I have a high volume MariaDB 10 server thousands of databases of with over a million tables. Currently we are only hitting around 20% query cache hits. We are already employing Memcache and other ...
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) ...
1
vote
3answers
38 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
31 views

Redundant MySql Configuration

I'm trying to figure out the best way to configure MySql so that I can have redundancy - so for example - I would have two servers, both through the same host, but at different datacenters in the ...
2
votes
1answer
51 views

Can I avoid a join for my select procedures?

Here is a really simple example of a select procedure I'd like to create, although it's only partly done (it's pretty self-explanatory): delimiter // CREATE PROCEDURE mysql.selectTables (_first_name ...
0
votes
1answer
25 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, ...
-6
votes
1answer
30 views

SQL Simple Question [closed]

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

How do I guarantee inventory numbers are unique across all tables?

I have an assortment of physical objects which need to be inventoried and each type of object has it's own table. For two example objects, let's use 'swords' for the table containing different swords ...
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
4answers
70 views

How can I use MySQL variables in subqueries?

I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why. Here's the query: SELECT * FROM animals WHERE name IN(SELECT ...
1
vote
1answer
52 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
54 views

Simple SQL question [closed]

I'm try to resolve this but I don't understand? My query Select FirstName, City FROM Customer WHERE City NOT IN ('Paris') Error Your answer does not contain the expression <>
0
votes
1answer
61 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
47 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.
0
votes
0answers
66 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 ...
3
votes
3answers
38 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
56 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 ...
2
votes
1answer
68 views

Unique key based on many-to-many pivot table

I have to manage artists and albums tables: | artists | | albums | | album_artist | +--------------+ +--------------+ +--------------+ | id | | id | | id ...
0
votes
1answer
41 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 ...
3
votes
0answers
43 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: ...
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 ...
0
votes
1answer
18 views

MySql crashes in VPS

I have a VPS in GoDaddy, which is crashing MySql service at frequent times. Here is the log file that may help to help me. 160425 09:24:53 mysqld_safe mysqld from pid file ...
1
vote
2answers
81 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
24 views

Connecting to MySQL VM remotely (Azure Resource Manager)

I have a VM setup in Azure (running Windows Server 2012 R2) with MySQL 5.7 installed. I am trying to access this using MySQL Workbench - which when running on the server works perfectly. However, ...
0
votes
2answers
25 views

Execute query using daily timestamp (mysql event)

I was trying to find a way for give users a daily "point" per online day. So my plan was setup a MySQL event to run this query: UPDATE users SET points=points+1 WHERE timestamp > "X" (Where X ...
0
votes
1answer
29 views

Get distinct count from row and display in sepearte column

I am trying to get count of section type for each section id and course id My sections table ╔════╤════════════╤═════════╗ ║ id │ course_id │ name ║ ╠════╪════════════╪═════════╣ ║ 1 │ 1 ...
0
votes
2answers
36 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 ...
1
vote
1answer
53 views

MySQL INSERT … SELECT Performance

I know that this query will lock table2: UPDATE table1... SELECT .. FROM table2 How about this query ? INSERT INTO table1... SELECT .. FROM table2 Does this query also create a lock on table2?
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
64 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 ...
3
votes
1answer
30 views

does merging tables always increases performance?

Consider we have data from visitors in a web site. And you are trying to create a database design for data mining. There are some common fields which users have such as browser,ip, etc... . But there ...
1
vote
1answer
39 views

mysql getting column data from one table to line up correctly with column data from another table

I need the query to produce this: person_id last_name first_name region_idregion name year month amount_sold 1 barnum phineas 1 maricopa 2016 1 800000 1 barnum phineas 1 maricopa 2016 ...
0
votes
1answer
24 views

How to return null values from a joined table if the matching row is missing?

Here is what I need to achieve: person_id last_name first_name region_id region name 1 barnum phineas 1 maricopa 2 loman ...
0
votes
0answers
12 views

Beginner explanation on MySQL cluster? [duplicate]

I am a beginner on MySQL and so far I have a web application with only one MySQL server running. I want to add more MySQL servers and started to read about MySQL clustering, but have a problem with ...
1
vote
1answer
82 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` ...
0
votes
1answer
99 views

MySQL Performance Tuning for MyISAM

I need to performance tune a mysql server and need help. I have a 16G server dedicated to MySQL with 1 MyISAM table with about 2 million rows that gets a significant amount of traffic. There are ...
0
votes
1answer
20 views

How can I disable xb_doublewrite?

Getting error while service restart. [ERROR] InnoDB: A parallel doublewrite file /test/mysql/xb_doublewrite found on startup.
0
votes
1answer
22 views

MySQL - A Count with INNER JOINS - that multiplies the result by 8

The following SELECT with INNER JOINS works, but does not count it right. I have a tests table: Tests TestNumber (int primary key) TestComplete (int 0 = not complete, 1 = complete) ...other stuff ...
1
vote
1answer
43 views

Run multiple MySQL instances in Windows

Which is a better way to install multiple instances of MySQL / MariaDB / etc on a single Windows box? Manually setting different ports? Docker? Virtual Box? Something else? (Sandbox, mysql_multi, ...
-1
votes
1answer
55 views

Got error while reverting mysql 5.7 to mysql 5.6

Got error while doing mysql service restart. Googled a lot, but nothing fix my issue. [ERROR] Fatal error: mysql.user table is damaged or in unsupported 3.20 format. 2016-04-23 11:10:38 22185 ...
0
votes
0answers
39 views

How to recover from a wrong update query and its cascading influence

Once a mis-DML happens, the backup set and binlog may help to get back the orginal record. However, how can I eliminate the cascading influence resulting from the wrong operation at the start? For ...
0
votes
1answer
43 views

Generating 50k rows of unix time entries auto-incremented 5 mintes each

I am relatively new at using mysql, or any DB for that matter, so let me know if I'm doing this in a round-about way. I have 4 similar tables with two rows columns each - unixtime + count. They all ...
-3
votes
1answer
81 views

MySQL - GROUP BY clause recursively? [closed]

Under MySQL I can not group the three separate data columns via GROUP BY clause. This sample contains four brands, each brand has 2 models and 4 versions of engines. I need to extract 4 vehicles the ...
1
vote
1answer
39 views

Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files

Trying to migrate individual tablespaces back into the shared ibdata tablespace. MariaDB version 10.0.16 SET innodb_file_per_table to off in MySQL, restated mysql and new tables are in fact going ...
0
votes
1answer
51 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 ...
0
votes
1answer
44 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 ...
0
votes
1answer
41 views

MySQL InnoDB constraint does not work

I stumble upon strange behavior with innoDB constraint, and cannot find cause of it. I have tables with data. Below listed their structures: CREATE TABLE `contents` ( `id` int(10) unsigned NOT ...
0
votes
1answer
60 views

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
58 views

Subquery returns more than 1 row

I'm executing a query to return the products of a specific sale in Magento database. It's working fine for a sale that contains only one product, however, I'm having Subquery returns more than 1 row ...