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
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
71 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
44 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
19 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
27 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
66 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
26 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
100 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
21 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
47 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
64 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
41 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
61 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 ...
0
votes
0answers
55 views

How I can reduce disk IO in MySQL 5.6.26?

I was using MySQL version 5.1.52 for my application but recently I have updated it to 5.6.26, but now I am facing very high disk IO. Following are system configuration: OS : Linux 2.6.39.4 RAM : ...
0
votes
1answer
53 views

How to convert postgresql dump file into mysql dump file

I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance
0
votes
0answers
36 views

how to get records of companies order by max comments and highest paid both

I have multiple companies with multiple branches into that.(Like 1 company ==> 5 branches) and so on. Companies(and its related Branches) are paid. And each branches have its own comments. So how to ...
1
vote
2answers
43 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 ...
0
votes
0answers
14 views

Database design advice for user access to certain options

I'm creating simple management system where I have 4 types of user access i.e. different permissions. I have created table users where I store each user and each user has user_role column where I ...
1
vote
2answers
64 views

Ignore empty rows while loading data into a table

I created a table (tbldemo) with 22 columns using a CREATE TABLE statement in MySQL. When trying to insert the data from a .csv file using a LOAD DATA INFILE statement, I get this error: Error ...
0
votes
0answers
16 views

Database compare and updating.

I have two MYSQL databases the old one and the new one. The old one lucks tables and columns that are in the new database. How can I make all the columns and tables that are in the new database be put ...
0
votes
3answers
22 views

Create Table Syntax [closed]

I am trying to create a table in MySQL but I keep getting an error of incorrect syntax. Below is my create table syntax, what is incorrect? CREATE TABLE [Test].[dbo].[Ite]( [ID] [INT] NOT NULL ...
0
votes
1answer
24 views

IF and CASE in insert query

INSERT INTO TARGET_TBL (KPIName ,KPILabel) SELECT `d`.`si_kpi_name` AS `KPIName`, IF(`t`.`kpi_val` = 0, WHEN (`v`.`TOIOS` >= `t`.`kpi1`) THEN 'Bad' WHEN (`v`.`TOIOS` >= ...
0
votes
1answer
20 views

column values do not return properly when column name called out specifically in select statement

I have a database with this table (version 5.0.95): > describe shift; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
0
votes
1answer
16 views

How to support custom fields for an entity and list them as columns?

I am currently working on a system which supports "custom fields" for users. I will illustrate this with a simplified example of this database schema: users -------- user_id name age custom_fields ...
1
vote
1answer
39 views

If replication is 600 seconds behind master will INSERT or UPDATE queries including `NOW()` be 600s different?

If slave is behind master by 600 seconds, will queries including NOW() also be 600s different? Example: master|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW()); ### 600s later, ...
1
vote
2answers
34 views

Mysql Query Take Longer To Prcoess On My Dedicated Server Than My Local Server

One of my MySQL queries takes a very long time to process on my live server compared with my development server. My live server (Windows dedicated server) is more powerful than my local (development) ...
2
votes
1answer
23 views

Count of range on datetime in MySql

I want to group table entries for each userId only if they are consecutive entries in the table. I have a table like this. id userId transactionTime 1 1 2016-04-13 22:15:44 2 1 ...
0
votes
1answer
26 views

Update my.cnf without restarting MySQL

I want to update my my.cnf without restarting mysql, is it possible? In fact I just want to set lower_case_table_names = 1, but I do not have the permission to restart MySQL.
0
votes
1answer
22 views

MySQL - Like with index

I found many question, But no solutions. An index can not be used to speed up queries where the search criteria starts with a wildcard: LIKE '%text%' An index can (and might be, depending on ...