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

2
votes
1answer
53 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) ...
1
vote
0answers
16 views

MySQL Error 111 caused by remote connection

This database has been running for years, such that all connections have came from the localhost address. Recently, I have been using xampp to develop new software, and have been connecting to the ...
-1
votes
0answers
10 views

Getting error 1005(HY000) cant create table in MYsql [on hold]

I tried to create the below three tables: CREATE TABLE category ( cat_id INT NOT NULL AUTO_INCREMENT, cat_name varchar(100), PRIMARY KEY(cat_id), UNIQUE(cat_name) ); create table product ( ...
0
votes
0answers
12 views

MySQL Database delay [on hold]

I've made my TV into a clock using PHP, a dedicated machine, and recently am adding MySQL to the equation to allow editing the settings from my phone. I've gotten this working, however, there is an ...
1
vote
1answer
27 views

Select rows until a condition is met

Given: CREATE TABLE T ( x int not null primary key , y int not null ); INSERT T(x,y) VALUES (1, 0) ,(2, 0) ,(3, 0) ,(4, 0) ,(5, 3) ,(6, 0) ,(7, 4) ,(8, 0) ,(9, 0); The ...
0
votes
1answer
54 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 ...
0
votes
0answers
8 views

T-SQL to MySQL conversion for PIVOT [duplicate]

I made a garbage table with garbage data and a PIVOT on it in T-SQL format and I would like to convert it into MySQL but I can't figure out how to get it to work. Table and data: CREATE TABLE ...
0
votes
1answer
24 views

Display the the result of a substraction in a MySQL query (1 table)

I've made a table that looks like this: stop (table name) -cityName {PK} -schedule {PK} -price -routeNo Basicly the customer wants to know what's the cost difference between 2 different stops by ...
2
votes
1answer
68 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
1answer
26 views

import TSV file into mysql table with SET datatype column

Is it possible to use LOAD DATA INFILE to load data into a mysql/mariadb table, where one column uses the SET datatype? If so, how do I specify the separator for the individual set elements?
0
votes
1answer
27 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
12 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 ...
1
vote
2answers
22 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 ...
1
vote
1answer
38 views

InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better?

Say I have a table ( person_id INT AUTO_INCREMENT, birthday_timestamp BIGINT, country, state, ..., PRIMARY KEY (person_id), KEY (birthday_timestamp) ) where people are added the instant ...
0
votes
3answers
36 views

Mysql query larger than tmpfs (ramdisk)

I recently set up a ramdisk tmpfs on our mysql server and the performance improvement has been quite noticeable. I've allocated 24 out of 32 GB's of ram for the ramdisk. My only concern is what ...
-1
votes
1answer
42 views

Server settings with 36GB handling app+data

I'm setting up a server to handle a multi-site configuration on a beta application for a partner in a new business venture. Trying to assess sane values for MySQL. Currently I have set: ...
1
vote
0answers
30 views

Access denied when trying to use Mysqlimport Utility (using Root Credentials)

We are trying to import large CSV files through Mysqlimport as described in This & This article. But even though I am using root credentials I keep getting the following error: mysqlimport: ...
0
votes
0answers
25 views

MySQL Return average for every unique id per day

I have a table: Unique ID, DeviceID, Temperature, Epoch And 3 sensors (DeviceID's) sending temperature every minute to the database. I would like to select to return AVG(Temperature) for every ...
3
votes
2answers
74 views

Need help improving sql query performance

I have this table: CREATE TABLE `property_ads_history` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `property_ad_id` int(10) unsigned NOT NULL, `advertiser_type` varchar(255) COLLATE ...
0
votes
1answer
20 views

Result consisted of more than one row in procedure

i confused with this i try not using cursor and same get error so i back using cursor but before this day my query success and can generate but this get error please help me i already look other ...
1
vote
1answer
47 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
47 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
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
27 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
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, ...
-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
45 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
57 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
25 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
53 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
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.
0
votes
0answers
59 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
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 ...
-1
votes
0answers
25 views

Error: MySQL shutdown unexpectedly [closed]

I have installed Joomla on the XAMPP server. When I first installed, it started and worked fine. But after the next time I tried running it, it gave that "404 Not found" on the browser. The error on ...
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 ...
2
votes
1answer
66 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
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 ...
3
votes
0answers
34 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
14 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
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
16 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
22 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
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 ...
1
vote
1answer
48 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?