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

1
vote
2answers
58 views

Why database size is smaller in RDS

I am trying to move my database from MySQL to RDS MySQL. I do a simple mysqldump to transfer. I use this query: SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + ...
0
votes
0answers
29 views

“WSREP: MDL ticket” log entry

On a MySQL 3-node cluster, the error logs contain several occurrences of this metadata lock message: [Note] WSREP: MDL ticket: type: shared no read write space: TABLE (...) There's not much ...
2
votes
0answers
57 views

mysql trigger on a federated table

I have two MySQL(5.6) databases D1 and D2 on two different machines. There is a table T1 on D1 and there are two tables T2A and T2B on D2. I want to update T2B whenever T1 is updated. T2A is a ...
0
votes
1answer
52 views

MySQL Tuning and optimization

How to Tune and Optimize MySQL Database, as a reference we are using mysqltuner.pl and we got the below output of slave host which we created recently. our future plan is the perform this same action ...
0
votes
1answer
47 views

How to use COUNT with multiple columns?

How to use multiple columns with a single COUNT? Assume that there is a table demo with these data: id | col1 | col2 | -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* 1 |'alice' | 'book1'| 2 ...
1
vote
0answers
26 views

Mysql slow copying to tmp table to indexing

I try to indexing table. The table size is about 60GB. But indexing query is hang on 'copying to tmp table' 18 hours. I found advice setting 'myisam_sort_buffer_size' myisam_sort_buffer_size : 128M ...
2
votes
1answer
23 views

Search and Replace in multiple MySQL databases

I have multiple MySQL databases(50+) and I want to replace a string in all of them, is there anyway to search and replace in all the databases? Server is CentOS and I have SSH root access.
0
votes
1answer
40 views

Setting root password in fresh mysql 5.7 installation

I am trying to install mysql in a serving having CentOS Linux release 7.2.1511. Take a look to the process installation: # sudo yum install mysql-server Output: Dependencies Resolved ...
2
votes
3answers
39 views

Record inheritance in mySQL

I have been struggling with this for a little while. I want to have a set of calendar records. The top level will apply to a large group; the next level will apply to a sub-set and the final level ...
0
votes
1answer
26 views

Data impact on analyzing mysql table stats

I have a MySQL database that appears to be getting slower over time. After some googling, I determine that the database probably needs to be analyzed. This is because innodb_stats_on_metadata is set ...
0
votes
1answer
46 views

MySQL query too long to execute

I have one query that work pretty well: SELECT [MANY THINGS] FROM vehicule v0_ FORCE INDEX (PRIMARY) INNER JOIN categorie_vehicule c1_ ON v0_.idCategorieVehicule = c1_.idCategorieVehicule INNER ...
1
vote
3answers
40 views

Should a MySQL slave used for backup also be used to service read-only requests in a production environment?

Based on best practices, I'm wondering if we should have at least one slave that's used solely as a backup server, without servicing any forward-facing requests from users. For example, if we have a ...
0
votes
0answers
35 views

Cannot add foreign key constraint'?

Can someone please correct this code for me? When I click enter it says 'cannot add foreign key constraint'? CREATE TABLE entries ( Subject_id smallint auto_increment, Student_id VARCHAR(45) ...
3
votes
1answer
58 views

Simple database structure advice

Newbie to dba. I'm creating a php/mysql inventory list and would like some advice before I start, on structuring my table(s) and database. The inventory database will eventually contain hundreds of ...
0
votes
1answer
22 views

MYSQL after update trigger

I am new to database trigger. I really have no idea why i cant update the Label if the Quantity more than equal to 1000 CREATE TRIGGER TRG_UPDATE_STATUS AFTER UPDATE ON product FOR EACH ROW BEGIN ...
0
votes
0answers
15 views

How to lock tables in a stored program if I can’t say LOCK TABLES? (in an ancient version of MySql)

MySQL does not allow LOCK TABLES in stored programs. But this is my problem: I have a large stored function to control “phases”. The function advances to next phase, if needed (rarely so), and ...
0
votes
0answers
22 views

Is the Aria storage engine really crash safe?

I maintain a PHP/MySql app for a big customer. Many of the tables were on MyISAM b/c that was the default when I started. On a newer server running MariaDB, some of those tables kept crashing. ...
6
votes
1answer
59 views

Is it useful to split a table into a frequently used and a archive table?

I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom. Is it useful to split this table into a frequently used table ...
0
votes
1answer
16 views

MySQLi Tutorials [closed]

I have just finished building my site, and now it's time to finally put a database in it and actually connect it to a database. So far I have downloaded Xampp and installed everything and tested it. ...
3
votes
2answers
44 views

Idempotent user and database creation script with MySQL

I have written a shell script which creates a database and user on a MySQL server. The user and database are to be used by a web application and generally this task only be performed once per website ...
0
votes
1answer
14 views

aggregate on join latest value

This is a problem I often get stuck in and I hope there is a solution. Let's build an example: Table products: product_id, product_name Table Prices: product_id, valid_since, Price Lets say I ...
0
votes
2answers
20 views

how to insert all the data to table1 from another table2, if table1 has some extra columns than table2 ? It needs to be done in mysql

i want to use insert into select * to insert data into table1 from table2 but i have some extra columns added to table1. These columns are distributed around the table like 2 are at top and 2 are at ...
1
vote
2answers
55 views

Get sum of amount monthly

I do not know English, sorry :( example table: CREATE TABLE IF NOT EXISTS `__example__` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `amount` decimal(10,2) DEFAULT NULL, `fromDate` datetime DEFAULT ...
1
vote
1answer
58 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
1answer
14 views

Sharing Databases and Tables between users in MySQL

I want to know how it is possible to share a database tables between multiple users/user groups, I mean, SQL Server has a security feature that we can create user groups and assign users to them, then ...
0
votes
0answers
19 views

MySQL replication Got fatal error 1236

I am getting this error when starting a new slave replication from master. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded ...
3
votes
1answer
62 views

MySQL replication - invalidating query cache entries (table)

We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of: invalidating ...
0
votes
2answers
27 views

Commands out of sync for no apparent reason

I have a simple table and a stored routine to handle phases. CREATE TABLE faza ( numer INT PRIMARY KEY, czasUstawienia DATE )// CREATE FUNCTION obecnaFaza() RETURNS INT NOT DETERMINISTIC BEGIN ...
1
vote
1answer
23 views

Same statement yields NULL when in a stored function, and non-NULL valid result otherwise

I have the following stored function for calculating ELO ranking: CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT) RETURNS DOUBLE DETERMINISTIC BEGIN DECLARE wynik DOUBLE; SET wynik = ...
0
votes
1answer
32 views

Accidentally executed reset master on the live server

Turns out I have called RESET MASTER; accidentally on the live server. The slaves stopped replication now because they can not find the expected bin log files on the master. Is there a way to continue ...
1
vote
1answer
41 views

How to get overall rank based on data regardless of scope (all items, user specific, etc.)?

Table schemas: servers - id - user_id - game_id - slug ... server_votes - id - server_id - ip_address - created_at ... So, I have a two tables: servers and server_votes, and I want to get the ...
0
votes
2answers
34 views

MySql not using Buffer Pool

I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than ...
0
votes
3answers
46 views

MySQL: can I put the database into a maintenance mode? (i.e. no user connections)

I'm going to be doing some complicated maintenance on our MySQL servers and I'd like to keep users out during the work. I'd prefer not to drop and re-create all of the user accounts, but that's the ...
0
votes
1answer
35 views

MySql Workbench Data Import .csv

Hi I am trying to use to MySql Workbench Data Import facility to import a .csv file (thousands of records). However it stops importing the first 5 records. After studying the .csv file I could see ...
2
votes
1answer
95 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
0
votes
1answer
33 views

Mysql Slow Log when using UNION ALL?

I have following query coming up in mysql-slow log, however i am not sure how to optimized this query and make it much faster. Any ideas on this ? I have already used indexes in following in the user ...
1
vote
1answer
50 views

Slow query performance on large table with no unique keys

I have a table that has approximately 20 million records and I'm having a very hard time improving the performance of the following query. It's taking well over a minute to run. SELECT ...
1
vote
0answers
17 views

Minimizing downtime during MariaDB Replication Setup

I have the task of setting up MariaDB (10.0) replication from a master running in a datacenter to a slave running out in an AWS VPC. Reading up on the instructions on Setting Up Replication - MariaDB ...
0
votes
2answers
27 views

Delete duplicate with same ID PHP/PDO/MYSQL

I'm new to databases and I need your help I'm working on a website and I have few problems. I'm using PDO. There is a user table, with unique ID, name and password. User enters his username and ...
0
votes
1answer
23 views

Lost connection to mysql while restoring dump. Is it safe to resume?

I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS. The dump was created with the following command: mysqldump --all-databases \ --single-transaction \ ...
0
votes
1answer
22 views

What to do when I get “mysqld dead but subsys locked” in my amazon ec2 micro instance?

I have an Amazon ec2 micro instance that holds a WordPress site. Since yesterday, my mysql db keep crashing saying: [ec2-user@ip-xxx-xx-xx-xxx ~]$ sudo service mysqld status mysqld dead but subsys ...
0
votes
1answer
36 views

can't update table in stored function/trigger because [closed]

I have referred to many questions, like here and also created a question on SO(No response there), but am not able to understand why it's happening in my case as am not even doing anything in the ...
0
votes
1answer
42 views

Add ranking to query output

I'm new at programming with PHP/mySQL and I have got a little problem here. This is how my code looks like: SELECT COUNT(*) AS Number , Name FROM table GROUP BY Name ORDER BY Number DESC ...
4
votes
2answers
140 views

MySQL: sum time ranges exluding overlapping ones

I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office: Query has to: Exclude overlapping ranges (8:00 - 10:00) Exclude ...
4
votes
2answers
58 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
1
vote
2answers
46 views

SELECT…FOR UPDATE - Will only an UPDATE release the lock?

I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement. I may or may not need to execute the update depending on the results of the SELECT. If I execute a rs.close(); or pStmt.close(); without ...
1
vote
1answer
30 views

Why is MySQL choosing that index?

I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order: IDX_E93438363DAE128B, ...
0
votes
2answers
35 views

Pull entire row per user based on MAX(Column) MySQL

I am trying to pull whole record from database for the highest "Score" column per user. TestTable structure ------------------ ID | Name | Score| ------------------ 1 | john | 10 2 | mark | 20 3 ...
1
vote
2answers
16 views

How to get readonly access to mysql files via g+w permission 750 or 640?

The data-dir itself has permissions 700 thereby limiting access to the mysql user. I want other folks who I add to the mysql group to be able to read binlogs and download the *.frm, *.MYI, *.MYD ...
0
votes
1answer
33 views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...