MySQL version 5.6 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

2
votes
1answer
30 views

Mysql RDS migration problem

I'm trying to migrate my MySQL RDS to a different machine. The reason is that I need smaller disk, and you can't make disks smaller for RDS servers, only bigger. I'm trying to migrate with this ...
1
vote
2answers
48 views

With MySQL 5.6, Is there any way to keep a replication slave X seconds behind_the_master?

It'd be really nice for disaster recovery to be able to set this up. We are doing snapshots, but this seems like it'd be a really nice backup to have. For example, I'd like to always keep a ...
0
votes
0answers
742 views

mysqld upgrade 5.5 -> 5.6 mysql Error: Failed while fetching Server version! Could be due to unauthorized access

After updating mysql with yum to 5.6 I am getting such an error message when trying to run mysql [root@host rpm]# mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck ...
0
votes
0answers
41 views

How to minimize the InnoDB page evictions in a MySQL full in-memory db?

I have a MySQL (Percona 5.6) database, on a server which has enough memory to contain the entire dataset. Specifically: Total memory allocated 98901688320 Innodb_buffer_pool_bytes_data 79246950400 ...
0
votes
1answer
35 views

Point in time restore MySQL

I want to perform a point in time restore on MySQL. I want to change log_bin value and ensure that log files are created. I am following this article and am working on a Windows platform. Log ...
2
votes
1answer
91 views

Convert MySQL database from latin1 to utf8mb4 - and take care of German umlauts

I am using 5.6.28 MySQL Community Server to host WordPress + ProPhoto blog in German language at CentOS 6.7 Linux server: mysql> show tables; +-----------------------+ | Tables_in_blog | ...
4
votes
2answers
56 views

MySQL for Oracle DBA

I am a newbie to MySQL, but understand Oracle databases well. I need a quick help to understand the architectural differences. Currently I am working on a Windows platform. 1) In Oracle we have to ...
1
vote
0answers
25 views

After disabling replication, should I disable binary logging?

I have a master-slave setup currently but am going to remove the replication DB server. The DBs are running on MySQL 5.6. I have a couple of questions about removing the replication: 1) I have read ...
0
votes
2answers
54 views

MySQL Change in execution plan after upgrade

We upgraded the MySQL 5.0 to MySQL 5.6 last week. After the upgrade most of the DMLs are very slow. Especially the inserts and updates using JOINs. There is a difference in execution plan of these ...
1
vote
3answers
85 views

why in explain in mysql row count is very high

i am using mysql, here is query- mysql> select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59'; +----------+ | count(*) | ...
1
vote
1answer
41 views

How to set slow query log in MariaDB my.cnf?

It should be easy, but when I put these lines: log_slow_queries = 1 long_query_time = 1 log-slow-queries = /var/log/mysql/slow_query.log log_queries_not_using_indexes in [mysqld], MariaDB fails to ...
0
votes
0answers
62 views

“mysqld got signal 11” error

I have a MySQL 5.6.22 InnoDB database and just now, suddenly out of nowhere, when I try to start it up using /etc/init.d/mysqld start, I see this error in the error log and the server won't start up : ...
1
vote
1answer
23 views

Can I use table aliases in a MySQL DELETE statement?

This does not work: DELETE FROM topics AS t , posts AS p USING t INNER JOIN p WHERE t.id=p.topic_id AND t.id = '5599'; whereas this does: DELETE FROM topics, posts USING topics INNER JOIN posts ...
3
votes
1answer
25 views

Data truncated for column - return fatal error

I get this error in my error log. Warning: 1265 Data truncated for column ... How to configure mysql to return a fatal error on this kind of warnings? Mysql 5.6.27 cnf sql_mode ...
2
votes
1answer
31 views

Changing system time on MySQL server

I've discovered that my MySQL server is 10 minutes behind the current time because NTP was not running for some reason. It is a single node and running MySQL version 5.6.15-56. I know that ...
1
vote
1answer
28 views

Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

MySQL does not support declaring constants which can be used between many stored procedures. For me, it seems that a deterministic no sql function can be used as an alternative for a constant. ...
1
vote
1answer
18 views

mysql logical ranking based on score

I have a table like this that keeps a total score of a history exam for students. +----+---------------+-------+---------+- | id | name | history | rank | ...
0
votes
1answer
41 views

How to optimize MySQL SUM query even after adding index

The below query is taking 11 min to return the result set even after adding indexes and changing the table row format to Compressed. The 2 tables in the below query each have Table ...
0
votes
1answer
50 views

MySQL - Force replication to statement for INSERT to a table to fire trigger on slave

We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that ...
0
votes
1answer
35 views

How move MySql table to another disk?

According to this it is impossible to use alter table for moving tables. So if I understand correctlly - to move table it is neccessary to do: Create new table Copy all row from old one drop old ...
0
votes
0answers
43 views

Why MySQL is still “using temporary” when update by primary key?

I have a partitioned mysql table, table size is about 100M rows: CREATE TABLE `nearby` ( `id` int(10) unsigned NOT NULL, `nearby_1` varchar(750) NOT NULL DEFAULT '', `nearby_2` varchar(750) NOT NULL ...
0
votes
2answers
29 views

MySQL show all grants

I have over 100 system and admin users in my db server. Does anyone know how to show their grants by running a single query? Thank you very much.
0
votes
0answers
20 views

Semi synchronous Replication passes no data

My master is running MySql 5.6.19, one slave is 5.6.27 and the other is 5.6.21. Everything seems to indicate that replication is running properly, but data on the slaves is not updated. My status ...
0
votes
1answer
66 views

Syntax error with concat in trigger near signal statement

Please help me with syntax for MySQL 5.6. For some reason I get the error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
1
vote
1answer
80 views

MySQL 5.6 - Creating an event inside a stored procedure

I am trying to create a procedure which create an event in MySQL. For example, something similar to this: delimiter // CREATE PROCEDURE schedule_my_db_job() BEGIN CREATE EVENT MY_DB_JOB ...
0
votes
1answer
35 views

MySQL user privileges lost on reboot

On occasion our MySQL 5.6 server loses some user privileges when restarted. The CREATE USER and GRANT commands are run through phpMyAdmin and function for as long as the server is running. After a ...
0
votes
1answer
46 views

MySQL lower_case_table_names - Tables vs Views

I've set lower_case_table_names=2 in my.ini. When I create tables, the case is respected. However, when creating views, everything is changed to lower case. Also, when I generate my scripts using ...
0
votes
0answers
29 views

Can i install mysqlbinlog 3.4 standalone?

I'm running several Linux servers (RHEL & Debian families) with Mysql 5.5 which ships with mysqlbinlog 3.3. I'd like to utilize the new --raw and --stop-never features of mysqlbinlog 3.4 without ...
1
vote
2answers
82 views

wordpress site kills mysql

I move client wordpress sites from one VPS to another, it all went smooth until I moved a site that kills mysql (and, consequently, all other wp sites). It used to work fine on the previous VPS. ...
0
votes
1answer
16 views

MySQL Replication Query

Imagine I have three servers. x.x.x.31 - master x.x.x.32 - slave x.x.x.33 - slave If I run a simple update in master. What does the slave servers done. run the update command or copy the updated data ...
0
votes
1answer
42 views

Very slow query, not sure if MySQL index is being used

I have a query which includes a join and I have an index on the tables: SELECT e.entry, e.user_id from entries e left join users u on e.user_id=u.user_id where e.tenant_id=? AND u.active=1 AND ...
1
vote
1answer
76 views

Slow MySQL searching from millions record

My table looks like this. id | hash | date I've an index on hash which is in binary mode. I have about 3 million rows right now. But if I query with the following query, it takes about 4 seconds. ...
2
votes
2answers
83 views

Query with WHERE NOT and NOT IN with subquery

MySQL Version: 5.6.27 I am using the following two tables TABLE: oc_product product_id int(11) model varchar(64) tax_class_id int(11) and TABLE: oc_product_to_category ...
0
votes
1answer
20 views

Problem With Loading txt File in mySql 5.6 Command Line Client

I have run this to enable local infile: SHOW GLOBAL VARIABLES LIKE 'local_infile'; SET GLOBAL local_infile = 'ON'; SHOW GLOBAL VARIABLES LIKE 'local_infile'; And indeed after that the local infile ...
1
vote
1answer
36 views

Not able to connect to local MySql database “only” when connected to VPN on VM [closed]

Setup: MySql installed on my Mac using Xammp VM installed using VMware Fusion running win XP. I am able to connect to the mysql database through my VM normally. As soon as I connect to a VPN it ...
1
vote
0answers
13 views

Variables for Merging MySQL InnoDB Tables

I have several thousand InnoDB tables in MySQL 5.6 that I am trying to merge together into a single table. A simplified form of the queries I am executing (one for each of the tables to merge) is ...
8
votes
5answers
319 views

How to update 10 million+ rows in MySQL single table as Fast as possible?

Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64. I have ...
0
votes
1answer
58 views

Mysql slave use more memory than master server

I have used top command to check memory usage on both mysql master and slave server. Master is using only 12% of allotted memory but slave is using 82%. configuration wise both are same. can anyone ...
0
votes
2answers
60 views

how to create 'root'@'localhost', after disappearing

for some unknown reason my 'root'@'localhost' account has disappeared from MySQL server. So in order to recreate it, I used my command line and logged in with a user called 'john91'. First, I checked ...
0
votes
1answer
29 views

my root account has been deleted for unknown reason, how to recover?

i have a program called heidiSQL on my computer that enables me to edit data inside my database with querys. recently tried to create a new user through this program while signed in as the root ...
0
votes
1answer
42 views

MySQL swapping too often

I'm doing a corrective measure in a server, especially in MySQL. So, the server has 32GB RAM and Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz. In that server I have MySQL (slave) and Apache (httpd) ...
0
votes
0answers
65 views

rds db.m3.large unexplainable low performance

I'm really hoping someone could help me figure this out. We run mysql 5.6 on a db.m3.large instance, with Storage TypeStorage Provisioned IOPS (SSD), 300G/3000IOPS and encrypted storage. Our current ...
1
vote
2answers
145 views

MySQL - Slow InnoDB Write Speeds on Windows 10

I have Windows 10 bootcamped. I've installed the latest version of MySQL community server. I've also installed WordPress onto IIS. The MacBook has 16Gb of memory. Here is my config file [client] ...
1
vote
2answers
72 views

How to determine the best index on a table with 3 nullable columns?

I am examining a users table similar to this: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `password` ...
0
votes
1answer
20 views

Get Master Server Information from Slave instance

We need to get Master server information such as server IP and Port, we know this information is dumped into master.info file by default but what if it required within MySQL prompt as we are using ...
1
vote
2answers
65 views

MySQL replication - database size is different

I have enabled MySQL master-master replication with 2nd master acting as read-only. Recently I checked the out the size of the databases in both master and slave and I found that database size in two ...
0
votes
0answers
11 views

with innodb engine, connection timed out & MySQL server has gone away error occured [duplicate]

I have created chat application which ping server every sec for messages. Currently I have done load test with httperf, with 500 request/sec. "Connection timed out & MySQL server has gone away ...
1
vote
0answers
20 views

Connection timed out & MySQL server has gone away with innodb engine

I have created chat application which ping server every sec for messages. Currently I have done load test with httperf, with 500 request/sec. "Connection timed out & MySQL server has gone away ...
0
votes
0answers
68 views

dropping index in a table that has 8 million entries

I am trying to execute the following: DROP INDEX idx_deletedAt_isLocked_testi ON app_instagram_shop_picture; DROP INDEX idx_id_deletedAt_isLocked ON app_instagram_shop; currently I am using mysql ...
0
votes
1answer
28 views

MySQL 100+ instances on one Windows server

Please don't ask why I need 100+ instances on one server because I cannot say; suffice to say that I'm not impressed. They will be very small databases and not definitely not under any great load ...