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
1answer
50 views

MySQL - What to store in orders or product_order

I am building my first e-commerce website and this is my current (relevant) database schema. I am very confused about what to store in the orders table or in the pivot table (order_product). I ...
1
vote
1answer
26 views

How can I do an incremental `mysqldump` without dropping table?

I'd like to fetch the last 100 rows of a table via a mysqldump, but I already have the rest of the table. How can I only fetch the last 100 rows for a mysqldump and then replace (or overwrite) any ...
0
votes
1answer
45 views

Main Purpose of FOREIGN KEY

When I did my first interview, I faced this confusing question. By using join we can join the tables, but what is the main purpose of FOREIGN KEY in mysql? Please suggest me the best answer.
0
votes
0answers
42 views

MySQL apostrophes suddenly handled differently

I have 2 production servers with exact same MySQL version (mysql Ver 14.14 Distrib 5.6.26-74.0, for Linux (x86_64) using 6.0). In one, a stored procedure continues to run properly off of a ...
1
vote
0answers
29 views

Redis along with Mysql

I'm currently working on a mobile app alike Google Maps or Wave, but also including parking. The application its running with MySQL primarily. With at this stage most of our data is loaded by python ...
-3
votes
1answer
44 views

Why different connections in database.? [closed]

I know we can use a single connection for different databases but why an option of multiple connection is provided, in other words, when is the correct situation to use multiple connections when we ...
0
votes
1answer
43 views

How to set “On Update” for uuid()

The syntax to define an update value for timestamp is the following: CREATE TABLE Example1 ( ... `RowVersion` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ... ); I ...
0
votes
3answers
50 views

Performance of updating row if content has changed

I'm trying to make a website where there is a form that contains input type fields that are editable. In fact I get the elements from one of my tables (containing an id column and a string column) ...
0
votes
1answer
21 views

Replicate View different servers

Using MySQL, and related to this answer open in another thread (How to replicate MySQL table in different servers?), It arises me the idea of replicating only a View in different servers (through ...
1
vote
2answers
48 views

scaling options for mysql on linux vm (ubuntu) : how to scale when the RAID 0 is out of space

I was asked to give a scaling road map for our mysql database. Currently, I have a single mysql instance v5.6.28 running on an ubuntu VM on azure. It has a (software) RAID 0 disk made with two 1TBs. ...
1
vote
1answer
36 views

Mysql Update columns from same table columns

I have a table where payments are located with invoice_id and payment_id and also payment_due_date => payment receiving date and period_months that is total period of the payment I have added 2 ...
0
votes
1answer
27 views

MySQL read-write Master with many read-only Slaves over shared storage?

I've asked the same question on the MySQL forum but have also seen lots of great responses here as well so thought I would try my luck here too. We have a relatively large MySQL environment where we ...
0
votes
3answers
49 views

How to select mutiple values with like clause

SELECT * from table WHERE column LIKE "AAA%" OR column LIKE "BBB%" OR column LIKE "CCC%" I have more than 10 values to select, so it is not appropriate to write like the above example. Is ...
3
votes
1answer
49 views

What indexes to use for GroupWise max

I have a user table and group table, and a many to many relationship between them. I have a vote table (one to many relation between group and vote) And a subitem table (one to many between vote and ...
2
votes
5answers
141 views

Lots of Indexes - MySQL vs MongoDB - Migrating

So, I've never used MongoDB, I just read a lot about it and I think it's going to be good for my project. Also, I do not have lots of experience with MySQL and to be even more honest I have no clue of ...
0
votes
1answer
60 views

Hashed and heap indexed object storage table insert and query performance

This question is partly to ask advice and assure this design is sane. I state the the desired properties upfront here, then go in more detail in the rationale and follow up with the design and example ...
0
votes
1answer
54 views

Repair by Sort not working

I am trying to import a .sql file which has MyISAM engine, about 51 million rows with 18 columns. The file has 1 primary and 1 unique key. I have after lurking around this site and reading mysql ...
1
vote
1answer
50 views

MySQL - Deleting old data; Select with index taking time

I am a bit confused about 2 queries. First query I am running on a 50m rows table by index on a timestamp row with a limit, I would expect the query to be immediate regardless of the size of the ...
0
votes
0answers
26 views

How can I create a user that can only create databases and users?

What I want is a user (#1) that can create other users and databases (#2, #3, n). To clarify, user #1 should NOT be able to manipulate any databases. Even the ones created by himself. Users (#2, #3, ...
3
votes
2answers
58 views

MySQL is trimming my numbers and I can't figure out why

I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts. For some reason which I can't figure out one of them is trimming the value when either inserted or ...
1
vote
0answers
53 views

mysql replication takes 6 seconds

I am struggling trying to understand why MySQL 5.7.11 semi-sync replication using RBR takes around 6 seconds between 2 VMs on the same physical host. I have verified the network bandwidth b/w the 2 ...
0
votes
1answer
24 views

Can I reference a specific column in a remote table (MySQL)?

I have two databases (MySQL) on two different servers, and one table references a table in the other. However, I am unable to point the reference to a specific column in the remote table for some ...
1
vote
0answers
40 views

How can I partition records vertically across machines?

I recently asked two questions: (1) how to store and connect partial records in two or more machines, with the record halves correctly associated, and (2) how to determine the increase in time/space ...
0
votes
0answers
10 views

HA_ERR_FOUND_DUPP_KEY error after pt-OST optimize

I run a simple alter to optimize a table that had a lot of stale data using the pt-online-schema tool (wrapped for readability): pt-online-schema-change --alter='ENGINE=InnoDB;' --no-drop-old-table ...
0
votes
2answers
50 views

Memory leak on MySQL slave?

I have a Percona MySQL master-slave server configuration(5.6.20-68.0). The memory usage by MySQL on the slave server is constantly growing. Now it reached 68G despite on fact that ...
0
votes
1answer
49 views

Split data across databases/machines

I have two questions about how to partition data across servers. Can I create and connect two or more databases, with the records correctly associated, using only a mapping table stored in a third ...
1
vote
0answers
19 views

Point to MySQL Binary Logs

So we have two linux servers that have a shared backup folder. The one server that is doing all the backups is working perfectly fine and has point in time recovery set up, which requires binary log ...
1
vote
1answer
47 views

systemctl startup script gives “WSREP: Failed to recover position” - manual startup works

We have a Galera crash. 3 Nodes on RHEL7. I can start one node with this command # sudo -u mysql /usr/libexec/mysqld --wsrep-cluster-address='gcomm://' 160408 13:36:24 [Warning] Could not increase ...
0
votes
2answers
49 views

How to replicate MySQL table in different servers?

I am searching for some solution for the following problem: I have two MySQL Database servers in different locations. I need copy a specific table from Server A into the same table of the Server B ...
3
votes
1answer
32 views

How to migrate from a MySQL master-slave setup to a master-master setup without downtime

We have an existing master-slave MySQL replication setup where we would like to promote the second slave to a master in order to achieve a master-master setup with no or only minimal downtime. Is it ...
0
votes
1answer
47 views

using triggers to synchronize data

i've two database, Database1 and Database2! i'd like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I've used triggers to synchronise data and it ...
0
votes
0answers
21 views

Mysql query limiting number of items

I have two tables: collections and collection_items. I'm trying to figure out how can I select for each collection, get a maximum of 4 collection_items in the same query. Some collections may have ...
0
votes
0answers
16 views

Boosting select queries during INSERT ON DUPLICATE KEY UPDATE into 150Gb innoDb table

I have a single server with 2 innoDb tables: 35Gb (partitioned, 24M rows), 150Gb (without partitioning, 220M rows). 25% data, 75% index. 99% of select queries are SUM + WHERE + GROUP BY. 100% of ...
1
vote
1answer
22 views

MySQL Workbench fails to store password in vault

I am trying to access my SQL server through MySQL Workbench. So I click the + sign to create a new connection. At this point I choose a connection name and enter the hostname, username, and port. ...
0
votes
1answer
28 views

SUM 2 columns with 2 columns same name [closed]

I have this table id, teama, teamb, pointa, pointb. Columns teama and teamb containing the same names. I want to output the sum grouped by teama from teama where pointa and where teamb pointb. for ...
1
vote
1answer
18 views

Selecting a id from a pivot table

Trying to make a messaging system, similar to FB, people can message more than one person, when they do it creates a row for the user with the conversation id and user_id in conversation_user. ...
1
vote
2answers
25 views

Does MySQL updated index if index key is not updated?

Say I have a table with 3 columns: ID (PK), Name, Age. Say ID has an index on it, name has an index on it, and there is another index which combines id and name. Lets say I now have an update ...
1
vote
0answers
24 views

MySql Error 1451 [closed]

I am by no means a database professional, but I've come here to get some help from people who may understand what's going wrong with my database. The error occurs on this line: update team set ...
0
votes
1answer
19 views

ibdata1 grows big again after using innodb_file_per_table and having shrunk it

After having set innodb_file_per_table=1, dumping and dropping all db's, deleting ibdata1, and restarting mysql ... My ibdata1 file is small at last... However, directly after reading in my dump, and ...
1
vote
1answer
25 views

Join second table based on third table with conditions

I"m using Doctrine for a website that has 3 tables to connect together. In simplest terms, they look like this Contractor - id - name ContractorJob - id - contractor_id - job_id - status Job - id - ...
3
votes
1answer
64 views

MySQL Killed by OOM after Corrupted Database

One of our web-app servers recently had an issue where MySQL was taking more memory than we have physical RAM. This server has 64GB of memory and 8GB in swap, and MySQL was OOM-ed because it was ...
2
votes
1answer
43 views

Why is MYSQL not starting?

When running the command 'sudo service mysql start' this is the output. [FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! Here are the steps taken to troubleshoot ...
1
vote
0answers
24 views

MYSQL Created_tmp_disk_tables value increases on almost every query

Should I be concerned that tmp_disk_table # is growing even when I run a simple query like elect id from table with 1000 rows where id is an integer and is primary key) I've fiddled with other ...
2
votes
1answer
39 views

Filter rows on data saved as JSON arrays

My data is saved as _________________________________ id | category | other data etc -------------------------------- 1 | ["2","3"] |bla bla -------------------------------- 2 | ...
2
votes
1answer
29 views

MySQL replication stopped for over 10 days, will replication work correctly when expire_logs_days=10?

I'm foggy on exactly how replication works. Here's my scenario. Today (April 6th) I realized that our replication slave had an error caused by a failed "CREATE USER" command on March 20. Since ...
2
votes
2answers
37 views

Execute Mysql queries using the same file more than once

I have a file with .sql extension in which I write a query like insertions, deletions or updates, and execute it in toadformysql . I repeat the same action many times because I have lot of queries, so ...
0
votes
2answers
37 views

How to properly change “innodb_buffer_pool_size” value for mysql?

I have following environment: # cat /etc/redhat-release CentOS Linux release 7.2.1511 (Core) # rpm -qa | grep ^mysql-community- mysql-community-common-5.5.48-2.el7.x86_64 ...
0
votes
1answer
23 views

Performance penalty for using thousands of tables in MySQL

Background I'm using wordpress multisite in which each user will have separate blog. For each blog, additional 15 tables are created. The website will be used by thousands of users. That means that ...
0
votes
1answer
12 views

max_allowed_packet after mysql restart

I want to set max_allowed_packet parameter in runtime. The SET GLOBAL max_allowed_packet = 16777216 works for new connections, but after database server restart, it has previous value. Can value of ...
0
votes
1answer
37 views

Possible hidden limit reached in MySQL instance

I'm developing a node.js/sails.js application and host my MySQL database on a Google compute engine instance. During load testing it hits some limit at 1300 RPS. The instances hosting the application ...