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
15 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
21 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
24 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
23 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
18 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
41 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
26 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
36 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 ...
0
votes
1answer
47 views

Database design for a lot of rows with data archive

i'm new to database design on larger scale. I have some knowledge and experience with MySQL but now i have some performance issues. Currently i'm using MySQL with 3 databases: History Snapshot ...
1
vote
1answer
45 views

Relationships mess

Say I have a table ads, and each ad has several medias - held in a medias table. Each media can be one of several types. Types are in a media_types table. So, for example, an ad has two medias: one ...
0
votes
1answer
18 views

How to enable symbolic link in mysql?

How can I enable symbolic link support in my MySQL Server. When I run show variables like 'have_symlink'; I got result as disabled.
1
vote
1answer
27 views

MySQL: How to perform aggregate functions on rows where a column is duplicate, for all values of said column

I have a table where one of the columns may have duplicate entries, and I want to condense each set of duplicates into a single row, with an aggregate function applied to the other columns. I cannot ...
0
votes
1answer
32 views

Select after an Uncommited Update in Python-MySQLdb

May be this is a newbie question but want to confirm as this is important for my app: I'm using MySQLdb on Python and my code looks like: db = MySQLdb.connect("localhost","root","",test) cursor = ...
1
vote
1answer
27 views

Does TokuDB need any regular cleanup/maintenance?

PostgreSQL has vacuum and InnoDB has purge. Does TokuDB have any required maintenance/cleanup operations? I couldn't find anything in the online docs: ...
0
votes
1answer
30 views

How to prevent locking and possible performance issue [closed]

I'm working on a website that lets users and admins to generate reports, and those reports require many database queries - they can take over one minute to complete. I noticed when these reports are ...
0
votes
1answer
28 views

is there a max number of rows in a SQL Server 2008/2012 and MySQL table? [closed]

Im evaluating a RDBMS for a bank app solution and i need to be sure about these performance parameter. There is a lot of info on internet that is confusing me :O. Max number of concurrent ...
0
votes
2answers
42 views

Relate few records in table to eachother

I need to relate groups of records, like I have 100 records in table, 3 of them should be known they are related, and other 5 are related to own group. I don't need any group IDs or anything like ...
0
votes
0answers
10 views

Changed Windows MySQL db to Ubuntu MySQL database [duplicate]

I am using a MySQL database in my project. Now I changed my Windows OS to Ubuntu. My problem is the query table name is present in upper case but the MySQL database table name is lower case, so I ...
0
votes
1answer
40 views

One table with 2 foreign key mapping same primary key

SELECT status, schedule_switch.Switch_Employee_ID, schedule_switch.Requesting_Employee_ID, employee_info.FirstName, DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate, ...
0
votes
3answers
16 views

replicate data from one db to another db server using automated process

I have an scenario, I have two DB server running on two different machines. I have to build an automated process by which the data will replicate from one DB to another DB server on every insertion ...
0
votes
0answers
18 views

Restoring a mysql database from raw files found in a mysql folder

My webhost screwed me. The dedicated server I had had a failed hdd and I lost the drive. I backed up all files but not the databases. I was able to save some MYI and MYD files but no able to do a ...
0
votes
3answers
43 views

How to get currently inserted rows and sum them within Mysql trigger before insert

I have a mysql trigger that fires BEFORE INSERT. My php application is inserting multiple rows at once like so INSERT INTO table1(id,name,price) VALUES(1,'LH32400', 1990),(2,LH32401,2000); The idea ...
2
votes
2answers
45 views

Is it worth making a separate DATE table or not?

If I have a table like this: my_table ======================= id date some_data Does it make any sense to pull the date out to it's own table? The date field would use the date data type. So ...
0
votes
0answers
18 views

MySQL server v5.7 error 2013 and 2006 when system date is changed

I have a problem with my MySQL server v5.7 when I would perform a query and the system date is the current date everything works. But when I change the system date and perform the query again in MySQL ...
0
votes
0answers
13 views

Change in mysql structure while backup is being created

I'm using amazon's RDS services and I wonder what happens if while a snapshot is being created I change the db structure? Will it corrupt the snapshot? Will it disallow me to make the change? I ...
1
vote
1answer
46 views

Accessing impact of deleting ibtmp1 file in MYSQL

There is a file called ibtmp1 and it is consuming 138 GB. I understand, this stores uncompressed data. Do we really need these files or can we delete it? if deleting is fine, then could you please ...
1
vote
0answers
43 views

How can I migrate a WordPress site from MySQL to SQL Server Express?

I have developed a web portal in WordPress using xampp server. I have my database in MySQL. Now I need to deploy this WordPress portal on other system with SQL Server Express Edition. So how can I ...
0
votes
0answers
13 views

PHP+MySQL error HY000/2002: buffer memory insufficient or query queue full

I'm developing a small project in PHP(mysqli)+MySQL using only prepared statements. I've got a table related to 4 other tables in 1...n cardinality. I wrote a script with a simple for loop from 1 to ...
0
votes
2answers
40 views

percona-online-schema-change recommendation

I am currently setting up percona-online-schema-change to do a ALTER on a table with 300m+ rows. I've verified that percona-online-schema-change can make the required ALTER. The source table ...
3
votes
2answers
68 views

Inserting rows with WHERE condition

Structure 1 table named: scan 2 columns named: id, and date Problem I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the ...
0
votes
1answer
24 views

mysql partition enabled or not?

When I run show variables like 'have_partitioning'; it return empty record. When I run show plugins I got this result partition | ACTIVE | STORAGE ENGINE|NULL | GPL| My MySQL version is 5.6.29 and ...
1
vote
3answers
64 views

Fastest way to add new column in MySQL

What is the fastest way to add a new column to an existing table in MySQL? I have tried ALTER TABLE tbl ADD COLUMN ..., but that takes quite a while since there are many rows out there.
3
votes
1answer
45 views

mysqldump inconsistencies

I'm planning to take a dump of a table with the --single-transaction option enabled. I know that if we execute any DDL statements during the dump process we might get an inconsistent dump. Will the ...
3
votes
0answers
39 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
1
vote
1answer
60 views

InnoDB MVCC vs Locking

From what I understand you InnoDB supports Row Level Locking MVCC (Multiversion Concurrency Control) Row Level Locking Locking is for when multiple writers are trying to update the same ...
0
votes
1answer
46 views

Can't import .sql file

I have a 1.4GB .gz backup file(which is 8GB unzipped) I have to import to the server but it isnt working whatever method I have tried. 1st I tried Workbench, after 2 days and still no progress I ...
0
votes
1answer
31 views

MySQL BETWEEN command - performance (order)

I want to create a table which will contain approx 20,000 records of three fields each. Two of the fields will contain numeric values. I want to be able to issue a SELECT BETWEEN command. One record ...
0
votes
1answer
19 views

sql update query for two connected tables [closed]

UPDATE rooms set r.vacant=true FROM reservation s,rooms r WHERE r.room_no = s.room_no AND s.cus_id=1 I created above sql queryto update room status.I want to set vacant as true when customer_id ...
0
votes
2answers
49 views

Insert the same time, it is possible to happen?

Good evening. I have a doubt about inclusion in the database. A common example is a user account where the user names must be unique. In the following logic: User enters data into a form. Data are ...
0
votes
0answers
31 views

How to Know Why Foreign Key Constraint Fails in MySQL

I'm using Laravel's Query Builder to put together some tables. I am trying to set up a foreign key, but it's failing with the following error: [PDOException] SQLSTATE[HY000]: General error: 1215 ...