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

Which is better in a master-slave setup to use local queue or write changes to remote instantly?

This is more of a a design question I have a local db (mysql if relevant) for reads and remote master for writes but wondering if it's better to.. 1) Write changes to a local queue and bulk insert ...
1
vote
2answers
71 views

How to decide that a new table is needed?

This might seem a vague question but I am going through a bit of a dilemma here. How do you people decide when a new table will be need? For example, when creating a table for storing user ...
2
votes
1answer
53 views

How to identify consonant/vowel pattern matches in MySQL?

Evening all, I'm having a bit of trouble with a MySQL table full of vocabulary words today. I need to be able to quickly retrieve words with the same arrangement of consonants and vowels, so my ...
1
vote
1answer
58 views

what's practical example / use case SELECT SLEEP() for DBA

I don't know how to use SELECT SLEEP(). It looks like a dirty hack. I found an interesting question How and when to use SLEEP() correctly in MySQL?. The answer is developer oriented: If you don't ...
1
vote
1answer
41 views

Use trigger to populate another column

I have two counters in a mysql table. I would like to have another column that contains the difference between counter column 1 and counter column 2. Could I use a trigger after counter column 2 is ...
1
vote
2answers
74 views

Help combining two MySQL queries to improve performance

I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query: SELECT d.field_id_105 AS code, title FROM ...
1
vote
1answer
33 views

trigger to delete records with same value from another table after inserting record in another table

i have two tables normal and abnormal,i want to create a trigger that will be able to delete records from normal table once i insert the same details in the abnormal table for example if a person is ...
0
votes
0answers
16 views

php mysql insert time

I am getting time values from another application in this sample format "2016-12-25 13:43:15" but when they get to my database they change to "0000-00-00 00:00:00" for the date am using DATETIME mysql ...
2
votes
2answers
54 views

Importing from CSV file, is crashing mysql server

I am facing a problem with importing large data into single mysql table. Basically I assume it is happening because limited machine resources but, I am having troubles to proof it. this is my CSV ...
0
votes
1answer
30 views

MySQL: JOIN and Conditional Statement

Can comeone help me to decipher the right logic of code needed for my query? I just can't find solutions about this or maybe my search queries are just vague. Lol. I made a query and thought before ...
2
votes
0answers
37 views

Mirror data on local SSD storage to conserve EBS I/O?

I have an AWS instance with local (ephemeral) SSD storage backed by a permanent EBS storage. Is it possible to configure the local storage to act as a mirror/cache for row data in MySQL? I do not wish ...
0
votes
0answers
14 views

On Duplicate Update Multiple Keys (not or) With And

I have friends table user_id_1 | user_id_2 | points 1 2 99 ADD UNIQUE (user_id_1, user_id_2) works perfectly, but i run this query; INSERT INTO friends (user_id_1,user_id_2) ...
1
vote
1answer
36 views

Order by varchar mysql

Is there anyother alternative for order by varchar clause? At worst case consider I want to order by varchar column that is not indexed. When I tested a query with loads of data it took many times to ...
2
votes
3answers
194 views

Why does a query with a LEFT JOIN run for a long time?

I have this query, it works fine and takes over 2.5021 secs: SELECT c.id AS 'id', count(c.id) as orders, o.created_at as last_order FROM customer c, order_2016 o WHERE o.customer_relacional_id = c....
3
votes
1answer
54 views

Create index for multiple WHERE EXISTS subqueries

I have to find an efficient way to write a very specific query for an application that I'm developing. The idea is the following: Find all the genetic markers for which there exists at least one ...
1
vote
1answer
33 views

I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?

I'm quite new to the process of importing/exporting databases. I'm using Maria DB, and HeidiSQL to run queries, etc. I exported an entire database from our dev server, and loaded it into our live ...
2
votes
2answers
50 views

which time period covers date string over datetime column

My question is a restlessness I been having for a time. So, here it goes: If I have a datetime column and perform a date search without the time 'yyyy-mm-dd' in a between statement. Which period of ...
3
votes
2answers
33 views

Select only rows with at least 2 non-missing values

I'm pretty new to MySQL (used to programming in R/Stata), and getting stuck on this - I'm probably going about it the wrong way. Any pointers would be appreciated! I have a large database (a couple ...
1
vote
0answers
29 views

MySQL basic query question

I have a table as follows: k | Ecode | Typ | Score | ---+------+-----+-----------+ 1 | 1 | NYS | 104.17 | 2 | 2 | NYS | 201.55 | 3 | 3 | NYS | 213.22 | 4 | 1 |...
0
votes
1answer
29 views

Get previous field's value in MySQL trigger

I am using a MySQL database and I have this kind of table. IDRefHandler 1 x    299       2 x    300   &...
1
vote
0answers
28 views

Best approach to store and match multiple data set against another set [duplicate]

I am developing a match making system where a user has certain data associated with his profile information. he has set of preferences to match against other users in the system. I am in search for ...
0
votes
0answers
19 views

Innodb Change Buffer merge

I have been testing the performance of my innodb mysql. I ran a script which inserts and selects one after the other for 100000 times. So according to the change buffer concept, the inserted ...
1
vote
0answers
30 views

Fetching equal records of all options MySQL [closed]

there are 4 columns which I need in conditions. age, city, accommodation, sex I want to export total number of 1000 records in the following criteria: I choose ages between 31-35 years old with both ...
1
vote
0answers
34 views

INSERT MySQL stored procedure executes twice when it contains a select statement as well [closed]

I have a very simple MySQL stored procedure as shown below: BEGIN INSERT INTO listing_offers (lo_active, lo_amount, lo_amount_number, lo_contract_reference,lo_created, lo_currency, ...
1
vote
1answer
23 views

Will a mysql db import be interrupted if my ssh session times out?

I have a large-ish (several GB) mysql DB that I'm planning to import via mysql -u root -p mydb < mydb.sql in a SSH session. What would happen if my SSH connection drops? Is it safer to run ...
1
vote
0answers
28 views

“SHOW TABLES” command returning tables not in the schema

I was debugging a backup script at work and noticed that for some of our bigger tables (greater than 5GB), I'm noticing an odd discrepancy. If I run the command mysql -h $host -u $user -p $pass -e '...
0
votes
1answer
41 views

how to update information_schema.tables in mysql [duplicate]

i have a table A and i want to update the creation time of this table. I understand i cant simple update ( using update query) as it is not any table. Is there any way i can update it. for example ...
0
votes
2answers
22 views

Remove String from column based on character

i have table : table : mytable id name 1 abcd ( dljdf 2 kjlkjkldjf ( ( ( ;dfld ) I want to remove string starting from character '('. Result would look like id name 1 abcd 2 ...
1
vote
1answer
14 views

Cannot create schema : permission issue

I am trying to create a schema. But I am getting this error : mysql> create schema myBase; ERROR 13 (HY000): Can't get stat of './myBase' (Errcode: 13 - Permission denied) When I do a ls -l on ...
1
vote
0answers
35 views

How to export views and import it in other DB?

I am trying to export the database and everything is fine but I am not able to export the VIEWS. Is it possible to export the views from sql queries and import it?
0
votes
1answer
25 views

Mysql columns mutual exclusion

I want to create a table which has 2 columns that would only allow one of them to have value in the same row. I tried with CHECK constraint but it not working correctly Ex: SQL Fiddle schema ...
3
votes
1answer
66 views

MySQL applying second incremental backup causes LSN in the ibdata files to be higher than LSN in the ib_logfiles

Using the backup procedure described below, it gives warnings. These warnings start to appear from the 2nd till 6th (last) incremental backup that is being applied to the base backup. How to solve ...
1
vote
2answers
33 views

guessing unused junk space in table. (innodb mysql/mariadb )

We have in every site (a dozen) one big database with lots of tables and record, using innodb as default and with innodb_file_per_table. from time to time we have space issue on some site and I am ...
1
vote
0answers
26 views

How to compare three columns on WHERE condition? [closed]

I need to compare three columns in a single table. Please help me clarify this problem. $pro_qry1 = "SELECT * FROM orders WHERE customer_id ='$id' AND item_id='$pro_id' AND status='0'"; $pro_run1 = ...
1
vote
0answers
19 views

Repair or force delete corrupt tables

I have an database with the name "build", and when I try to execute "USE BUILD" the MySQL server just crashes, it also crashes when I try to show the tables with phpMyAdmin and MySQL Workbench. I do ...
1
vote
0answers
52 views

What does a database administrator really do? [closed]

I am planning out a website project and have a limited amount of knowledge and understanding, which are two completely different things. I hire freelancers to do my actual work, but for that to work ...
3
votes
1answer
71 views

Best practices, MySQL table with 150+ columns, many null

I have a table storing property data for thousands of properties, all coming from a feed. (updating at least once a day). I'm certainly not an expert with databases and would appreciate some guidance ...
1
vote
2answers
90 views

MySQL doesn't log queries anymore

Some time ago I updated my MySQL server to the v5.6: SELECT VERSION(); 5.6.30-0ubuntu0.14.04.1 Now the query logging isn't working though the configs in the /etc/mysql/my.cnf seem to be correct: [...
-1
votes
0answers
46 views

MySQL Quesries give me a High CPU usage - howto fix it?

I use automatic wordpress plugin that keep my site feeding by using cronjobs -every minute. I use 30 cronjobs on every single minute. Now i have a super slow server and a high cpu usage because of ...
1
vote
1answer
49 views

Distinguish between unknown and non-present foreign keys

I'm in the situation of importing data from multiple other applications. The data is stored in multiple tables that contain foreign keys. Some of these applications don't provide all the needed data ...
1
vote
1answer
33 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
0
votes
1answer
36 views

Updating records from the past with MySQL query

I've built an application that fetches reports from an external source. It downloads report data once a day. Each report covers the time from now to 30 days ago. E.g. let's say it's 1 May and report ...
0
votes
0answers
55 views

Get rank of users based on dynamic points

I have this db structure for my ranking system. users table id username 1 alex 2 john points table id name description value 1 foo bar 100 2 bar foo ...
1
vote
1answer
58 views

How to restore database with ib_log0, ib_log1 and ibdata1 files in MySQL

We have a Mysql5.5 running on Windows server 2008 from past 4 years. Recently we have moved ib* files to new locations because of space constraint in c drive. When we started the Mysql it created ...
1
vote
0answers
31 views

MySQL partition on 2 tables one with millions of rows the other with 100's of millions

I have two mostly de-normalized tables in Amazon Aurora (MySQL). The first table had mostly dimensions that are used for selecting the proper data. Examples might be station, program name, operator, ...
0
votes
1answer
71 views

Memory(buffer pool) not populating on mysql start on Percona 5.6.28

We are testing Percona 5.6.28 at the moment, during our testing we found out even though the innodb_buffer_pool_populate is set to ON, it doesn't populate the memory(buffer pool) during mysql start. ...
0
votes
1answer
32 views

Mysql read only engine type and indexes

I have a mysql table. This table has only three columns. Two are varchar (5),and one is varchar (32). There will only be one query run against table. Select (column 1),(column 2) from mytable where ...
0
votes
0answers
38 views

re-sync Replication Mysql Slave after down server

i need some help to restablish my slave on mysql. A few days ago my slave machine stop replicating with this error. *************************** 1. row *************************** ...
0
votes
1answer
45 views

MySQL Bug reference in release notes, but where?

This may be a daft question, but on the MySQL release notes, a lot of them are followed by BUG Reference numbers. But most of these BUGS don't exist when you search for them at bugs.mysql.com. ...
0
votes
0answers
27 views

can tables be created in a loop on mysql

i want to create multiple table with different names(based on numbers mostly) which will have same structure. In C++ I used pointers when i wanted to create variables in this manner. Is there a ...