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

learn more… | top users | synonyms

0
votes
0answers
50 views

Mysql Replication break Error_code: 1032

Created new slave replication, it was working fine but suddenly after 2 days its giving me this error and the replication stopped. Both on Centos 6.7 Master Version ...
0
votes
0answers
44 views

limit of open files: Debian Jessies´ MySQL vs Community Oracle MySQL

I have just upgraded from MySQL Jessie 5.5 to MySQL Oracle Community Edition v5.6. I have a particular MySQL server, that has around 230 DBs, and > 32k files/tables. Whilst for two or three years, I ...
2
votes
0answers
34 views

MySQL Performance issue: ~360ms to fetch 120 rows with 10 columns

I have the following tables: CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'draft', `publish_at` datetime DEFAULT NULL, ...
2
votes
1answer
89 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
94 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 ...
1
vote
1answer
27 views

Is it possible to drop tablespace CASCADE in MySQL?

I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query: DROP TABESPACE ...
1
vote
1answer
212 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 *************************** ...
1
vote
1answer
126 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
41 views

mixing binlog formats between master and slave

I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb. Both my Master and Slave are setup in MIXED binlog format. Now I need my slave to act as a master to ...
0
votes
1answer
43 views

Fetching data from 3 tables

Wanna fetch data from 3 tables by mysql query to display in feed. But stack. My query is "SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, ...
0
votes
1answer
86 views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql ...
0
votes
0answers
54 views

MySQL performance issue after migration

I have a MySQL performance issue with a brand new server, and am somewhat of a beginner as a database administrator (I'm mostly a general-purpose Linux sysadmin). I'm looking for pointers where to ...
0
votes
1answer
35 views

Fast copy/rename database, not mysqldump

I would like to keep a "default" setup for a database on a testing server, which can be used to "restore" a "dirty" testing database back to the default state. mysqldump is very fast, done in a few ...
0
votes
0answers
51 views

Cannot restart mysql (installed natively) but possibly caused by a Homebrew package

I've previously been running mySql which I had installed with the dmg files without any issues, but I also have Homebrew installed and noticed some errors today which I fixed successfully but ...
-1
votes
2answers
42 views

MySQL: Why is ibdata file growing? How can we shrink it?

Even though we are using "innodb_file_per_table = 1" from day one. The ibdata file still continues to grow. Total size of our database is around 800GB and the ibdata file is currently around 50GB. All ...
0
votes
0answers
50 views

How to start mysql without recovery rollback

I started a delete operation on table "A" in mysql which ran for 12 hours (lots of triggers and cascading deletes) already before I decided to kill this transaction. I waited then for another 12 hours ...
0
votes
2answers
69 views

MySQL Binary Installation (mysql_install_db)

While installing from binaries. The below always gives error when we execute from 'scripts' directory [mysql@hostname ~]$ cd /mysql/product/5.6.12/scripts/ [mysql@hostname ~]$ . mysql_install_db ...
0
votes
0answers
21 views

MySQL 5.6.18 got signal 11

My MySQL Server 5.6.18 got signal 11 today very randomly. Has not happened before. This is the stack trace : stack_bottom = 7fdb9bcb1e0f thread_stack 0x40000 ...
0
votes
0answers
15 views

MySQL Delayed Replication - Reading events

Given MySQL is setup for master-slave replication, when delayed replication is set on the slave, does the slave still read the binlog while it is waiting for the delay period to pass? From MySQL ...
0
votes
1answer
47 views

MySQL optimizer difference between 5.6.27 and 5.5

We've just updated the MySQL version of the database of one of our clients from the 5.5 to the 5.6, everything is working fine except one simple query which is causing us troubles. Having the ...
0
votes
0answers
32 views

cannot install_db after deleting /var/db/mysql

I am trying to reset mysql5.6-server back to its installation defaults and blowout/nuke all the databases to start off fresh and clean. [root@yoda:~] # rm -fr /var/db/mysql [root@yoda:~] # mkdir ...
0
votes
0answers
44 views

ndb_restore issue

I'm facing following issue while trying to restore backup in mysql cluster. It will be great help for me if some body can share idea on these Backup Id = 2 Nodeid = 4 backup path = ./ Opening file ...
1
vote
0answers
40 views

Fetch items where related rows has different values

I have table in MySQL called attributes, and other table, attribute_values, one attribute can have multiple values, values identified by context attribute and has value_c attribute wich contain value. ...
1
vote
0answers
32 views

How to sync incomplete replication records in MySQL

if replication breaks and you end up with a couple of records not pushed out to the slave, is there a way to update the slave without stopping locks on the master and manually transferring a new ...
1
vote
0answers
29 views

Classic vs. GTID replication MySQL 5.6 - how to identity which one used from errors?

Is that true? In classic replication (SQL_SLAVE_SKIP_COUNTER=1) you never see UUIDs in logs and show slave status like 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5? When you see an UUID, there is GTID ...
1
vote
1answer
64 views

Store Mysql Index in separate .Ibd file?

Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If ...
1
vote
2answers
58 views

cannot login to MySQL remotely

users and root can log in locally with no problems. No user can login from a remote connection though from remote: [root@yoda:~] # mysql -v --host=r2d2.er.com --user=power --password='burp' power ...
1
vote
0answers
22 views

Syinc databases mySQL to Oracle

I'm new in "databaase administration" the problem I am facing is that I want to have an exact copy of a table, from MySQL database (website database) to oracle database (a local one). What I've done ...
1
vote
1answer
40 views

how could select more than 14 column

Not able to select morethan 14 columns in the list please find the link for reference, http://sqlfiddle.com/#!9/bb7a66/3 CREATE TABLE tbl_staff_details (`name` varchar(45), `FK_int_emp_id` int, ...
0
votes
1answer
270 views

Got fatal error 1236 Slave has more GTIDs than the master has, using the master's SERVER_UUID

We are testing replication using GTID's on two servers running MySQL 5.6. Replication was running fine, but now it is not functioning. When I run show slave status\G on the slave, I get the following ...
2
votes
1answer
218 views

Mysql Server 5.6 high memory consumption

I have a problem with my mySQL server. It consumes 9.7 GB ram. I have only a very limited amount of data on it (13 mb) I have been searching the internet and seval places a solution like this one ...
3
votes
2answers
55 views

How to handle temporal datatype columns when doing upgrade from 5.5 to 5.6

I want to upgrade my MySQL 5.5.40 server to 5.6.28. But as per the documentation we need to alter the tables having columns which are using date or datetime or timestamp data types as storage ...
0
votes
1answer
83 views

MySQL CONVERT_TZ returns null

MySQL CONVERT_TZ returns null, if timezone value is in plus. example: select CONVERT_TZ(now(),SUBSTRING(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')),1,5),'+00:00'); it returns ...
2
votes
1answer
33 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
51 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
2k 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
1answer
38 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
278 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 | ...
3
votes
2answers
75 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
39 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
61 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
124 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
176 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
118 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
42 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
41 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
37 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 ...
2
votes
1answer
45 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
33 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
112 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 ...