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
3answers
119 views

Can't add Foreign Key Constraint

As far as I can tell it should work, I went through other similar questions but it seemed to be a different thing causing the same error. CREATE TABLE Job ( JobNumber INT NOT NULL, JobName ...
1
vote
1answer
15 views

MYSql Full Text Boolean search and relevancy score improvements

I am using a mysql BOOLEAN full text search as shown in the query below and am getting what I think to be bad results that could be more relevant. Query: SELECT phppos_items.name,MATCH (phppos_items....
1
vote
1answer
19 views

Where do i change the my.cnf on mysql cluster

I'm trying to change the max_allowed_packet setting on mysql cluster (version 7.4.8) - i have 5 nodes 2x SQL Node 2x Data Node 1x Managament Node I have updated the my.cnf on all of the servers -> ...
-1
votes
2answers
62 views

Is there an SQL statement that I can run in MySQL that returns the location of my database(s) on my server? [closed]

I did a bit of research online and it looks like I need to have a column (or something) in my database called "sys.master_files" but I don't know what that consists of or how to create it.
1
vote
0answers
26 views

Mysql replication, master_log_pos sync but tables not update

First, my replication config on Master: replicate-do-db = mydb binlog-do-db = mydb expire_logs_days = 2 max_binlog_size = 100M binlog_format = MIXED log-...
1
vote
1answer
21 views

Is it possible to reorder tables in phpMyAdmin?

By default, database tables are ordered in alphabetical order. Can this be reordered? For example: default alphabetical: company, profile, user common User Flow: user, profile, company Maybe the ...
1
vote
2answers
43 views

Slave_IO_Running: Connecting in Master-Slave Replication

I was trying to implement Master-Slave Database Replication on two systems. I followed the steps from the following links https://www.youtube.com/watch?v=DmQWcU2INqQ and Master Slave Replication ...
1
vote
0answers
44 views

MySQL table filed with text datatype inserting some script value automatically and ignored user input

In my MY-SQL database table having with text datatype. In that field having some unwanted script tag added automatically. If I given that any value also ignored. and also I tried to edit that value ...
1
vote
2answers
75 views

High CPU usage from sorting result queries

I have two machine have two master-master mysql replicate, only problem i have that i got massive queries like "sorting result" queries and i don't know how to fix these queries . mysql process cpu ...
2
votes
1answer
21 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
68 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
51 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
57 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
48 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
29 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
36 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
13 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
31 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
22 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
37 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
33 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
24 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
64 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
18 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
68 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
88 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
45 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
48 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 ...