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
0answers
33 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
42 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
76 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
49 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
36 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
43 views
+50

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
55 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
48 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
48 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
29 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
11 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
31 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
15 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
21 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
14 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
34 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
33 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
20 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 ...
0
votes
0answers
30 views

mysqldbcompare SELECT command denied to user

I am trying to run mysqldbcompare to see the difference between two tables: mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests ...
3
votes
3answers
80 views

What does having the primary key as the last column in a composite secondary index in an InnoDB table do?

Say I have a 1-to-N relationship (person_id, pet_id). I have a table where pet_id is the primary key. I understand that an InnoDB secondary index is essentially a B-tree where the values are the ...
1
vote
2answers
20 views

How to grant permissions to users based on table information?

Say I have a table: +------------------------+ | Employees | +------+--------+--------+ | Dept | Name | Salary | +------+--------+--------+ | 1 | John | 10000 | | 1 | Mary | ...
0
votes
0answers
25 views

connecting to MySQL Database on local network

I'm trying to connect to my SQL database on my beagle bone black (Debian 3.8.13 MySQL version: 5.5.47) from my raspberry pi (Debian Jessie 4.1.13 MySQL version: 5.5.44). Both are on the same local ...
2
votes
1answer
36 views

Possible causes that a MySQL/MariaDB query is slow under some table conditions

I am having a strange issue with a slow query under certain conditions: I have made a few tests and was able to isolate the issue to one single table called products_description (all the tables are ...
0
votes
1answer
36 views

How to delete rows from the mid of the table without any where condition?

I have a table testing with two columns id: auto_increment name: varchar(20) I fired a query select * from testing limit 1, 2 which gave me 2nd and 3rd record. Now, I want to delete these records ...
1
vote
1answer
25 views

Is there anyway that I can manually create a PID for the MySQL instance?

I am running MySQL on a linux server. I have taken a copy-back mysql enterprise backup from another linux full backup. Ever sense then I have had problems getting MySQL to start. The problem is ...
0
votes
1answer
34 views

DB Design - 1 column for each dataset OR dataset_name AND dataset_value?

I am creating a database table for storing numeric time-series data sets regarding cities that will be accessed via a web app. Primarily annual, quarterly and monthly data will be stored in it. ...
0
votes
0answers
45 views

Can I at least get MySQL errors to give line numbers in the correct query?

We all know MySQL errors are pretty unhelpful. I understand that due to query optimisation it would be in-principle very difficult to unwind errors to their exact location in the source text and that ...
0
votes
0answers
15 views

Trying to fix collation mess

In our MySQL database we have set several collations. e.g. Connection collation utf8_unicode_ci Server configured default collation utf8_unicode_ci Database default collation utf8_general_ci Table ...
0
votes
0answers
49 views

RDS MySQL memory usage question

I have an instance of RDS of MySQL 5.7.10 I am on a package: db.t2.micro which grants me 1vCPU and 1Gib of memory. I am using Monitoring window to monitor the CPU/Memory usage. I do not understand ...
0
votes
1answer
28 views

How to write a SQL query to find the range from column values?

I have some fields in my database which contain values like 2483-5802 and 1245-3325. Now I have a user input and I have to find it from this column. Example if I have to search 2000 which will come ...
1
vote
1answer
29 views

Why is innodb strict mode not enabling?

I tried adding this line in /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu) innodb_strict_mode = 1 This is within the [mysqld] section, where I have seen this line in other examples. I know this config ...
0
votes
2answers
32 views

MySQL - An UPDATE from a SELECT

The more I learn about about the great things you can do with mysql, the more I keep pushing to learn. I have two tables: Tests TestNumber (int primary key) InactiveTestSlotBitwise (int) ...
-3
votes
1answer
41 views

Update huge array of data in MySQL

I've came across the situation, where I need to select huge amount of data (say 100k records, each of them looks somewhat like ID | {"points":"9","votes":"2","breakdown":"0,0,0,1,1"}), process it in ...
0
votes
1answer
27 views

Storing mail in database, data type

I've found pretty poor informations about mail databases. My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but ...
0
votes
1answer
19 views

mysqli importer file - disable foreign keys

I am using this script to import a mysql file but I want to set foreign key checks to 0 when he will do the import. https://github.com/davcs86/php-mysqlimporter/blob/master/php-mysqlimporter.php ...
0
votes
0answers
17 views

MySql 5.6: How to create a slave using gtid for an existing database, and fail over?

There seem to be very few guides on setting up mysql replication for failover (and failback) for 5.6 using gtid. The official manual shows how to do it for an existing replicated setup, but not for a ...
0
votes
2answers
57 views

MySql: how to remove special characters from column in query

What i get value in php $subject = preg_replace('/[^A-Za-z0-9]/', '', 'abc - 123'); // abc123 Is it possible to use preg_replace in my mysql query? I want to something like this. want to ...
1
vote
1answer
50 views

Cumulative Sum by distinct contact_ID by year-month

I've seen some posts here regarding the SUM OVER function to calculate the cumulative sum. But the issue which I have is slightly different. I need to keep a running total of distinct inactive clients ...
0
votes
2answers
21 views

Matching string with LIKE derived from sub-query

Here is my query Where I want to match a string with a subquery output. But it is returning wrong result. What I am doing wrong? The concat part is not working as I try to run Concat('"', (SELECT ...
0
votes
0answers
13 views

Restarting Galera MySQL after proper Shutdown

I already have read and followed dozens of documents describing a split brain situation and how to restart the cluster after then. However, I am less in this situation as in the situation of shutting ...
1
vote
1answer
28 views

Design db schema in correct way for parent child kind of requirement

I am creating schema of tables. But not able to understand best way to do it. Following are the tables: Car Model - It contains all car models with types. For eg. Mercedes(brand) - GLA ...
0
votes
1answer
31 views

Table/database design question

Suppose you have these fields to keep track of in records: (first_name, last_name, state_id, stateid_status, day, month, year, id_status, id, criminal_status, charge, plea, sentence, create_time, ...
3
votes
1answer
22 views

MySQL binary log reporting execution time as UINT_MAX

Looking through the MySQL Binlogs, I see some data that just does not make sense. There are queries that say they have been running for 4294967295 seconds (which also happens to be UINT_MAX on this ...