Questions tagged [mysql-5.6]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
31 views

ERROR 1064 (42000) You have an error in your SQL syntax during import

New to MySql. I have an export file from server version 5.6.34. I am importing this on a server with the same version. I get an error like: ERROR 1064 (42000) at line 4343: You have an error in your ...
0
votes
0answers
15 views

Best way to restore a MySQL 5.6 database from just its folder and file data? [duplicate]

I had a 5.6 MySQL Server instance that was hosted on a computer that suffered a very bad lightning related power outage. For whatever reason, MySQL 5.6 Server does not want to start/stay running now. ...
1
vote
2answers
594 views

MySQL 5.6.36 crashed suddenly - Assertion failure. How to track it?

Yesterday my database crashed and then restarted; after the automatic crash recovery started normally and it's working fine; is there a way to know what caused it? I tried searching in the binlogs for ...
0
votes
1answer
27 views

How to select rows after a certain row using auto increment temporary columns

I have a table that has ID varchar(255) as PK. I have managed to create an auto increment temporary column rowNumber to use with a SELECT, as follows: SELECT (@cnt := @cnt + 1) AS rowNumber, ID ...
0
votes
1answer
84 views

Mysqldump backup size issue - 5.6

I'm using MySQL 5.6 with about 5 databases on linux. I have scheduled daily full backups using mysqldump. All databases use InnoDB engine. What I observe is that the backup size seems to vary ...
2
votes
1answer
107 views

MySQL users not visible after import

I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After ...
0
votes
1answer
27 views

Difference in MYSQL Master Slave DISK SPACE

I am using percona_mysql_5.6, both master and slave are in sync as terms of replication and table wise records are also same. However there is a difference is the disk space consumed. The slave is ...
0
votes
2answers
109 views

Refresh (replicate) MySQL database from server to server

I'm new to MySQL but I have experience with other major RDBMS like Oracle and MSSQL. I'm trying to find out what are some of the widely used methods (or best practices) when it comes to refreshing one ...
2
votes
1answer
245 views

Getting past corrupted binary log “Error in Log_event::read_log_event():”

I have a binary log that mysqlbinlog chokes on with the error in the title. The file itself has much more activity after the cited position. Doing some basic confirmation it's not all garbage by ...
8
votes
3answers
7k views

MySql Gap Lock Deadlock on Inserts

I'm getting Deadlocks from gap locks on a table when inserting into it frequently from multiple sources. Here is an overview of my processes. START TRANSACTION UPDATE vehicle_image SET active = 0 ...
0
votes
1answer
140 views

What index(es) and queries for multiple column ranges search?

Based on a simple innodb mysql 5.6 database schema like this: What is the optimal indexing strategy for querying the "info" table where most of the search criteria will be IN and BETWEEN conditions? ...
0
votes
2answers
113 views

lot of relay log formation in mysql5.6

Reason for lot of small relay log file formation. I Have setup a slave and it relay log rotates to soon and creates thousands of relay log file.whats the reason for more relay log formation.
0
votes
0answers
12 views

MySQL: is root a special user?

That may sound like a silly question, but let me explain - I wanted to log on to MySQL as user root from several other systems, so I created this: mysql> create user root@`10.254.%.%` identified by ...
0
votes
1answer
120 views

how to minimize the data scanned on my database using index?

Is there anyone how to minimize the data scanned on my database?? please show me.. using index? query SELECT IF (e.channel LIKE 'IAX2%', e.calldate, IF(d.channel LIKE '...
0
votes
1answer
78 views

Replicate update statement as Insert in Mysql 5.6

I want to replicate all update statement from Master as a Insert at Slave end. Is it possible like in SQL Server? If so, how? I want to do it because, if any record on Master is updating and if same ...
1
vote
1answer
299 views

How to pivot a column showing its content as new columns

I'm working with MySQL 5.6 and I'm having headaches with this issue. I've been looking in several groups the result, but I think this isn't very common. I have a table like the following one: direc ...
1
vote
1answer
28 views

Why Am I Getting Inconsistent LEFT JOIN Results in MySQL

Background I have a database in 3 different locations with the same exact static data (nothing updates it, it was a one-time import from another system). In 2 of the 3 locations, a query similar to ...
-1
votes
0answers
9 views

mysql 5.6 can't open on redhat 6 [duplicate]

[root@master mysql]# mysql -u root -p Enter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) and [root@master mysql]# /etc/init.d/mysql.server ...
-3
votes
1answer
35 views

How to get data from tables selected by reference from information_schema with some criteria in table name

I am trying to get data like count or distinct from tables matching criteria in information_schema SELECT count(A.searchstring) FROM (SELECT table_name FROM ...
1
vote
0answers
32 views

Non-existent table that actually exists?

I've been tasked to solve a very difficult and interesting problem. Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10. We have an ...
2
votes
2answers
191 views

website performing slow once mysql connection RDS reached 50

Below the rds parameter group config, please help me to resolve this issue. I am using 4 web server having 2 CPU and 8 GB ram and rds having 4 cpu and 16 GB ram. Once the connection reached 50 site ...
1
vote
1answer
18 views

Query To Show YTD Total Summing Each Month Prior To Current

I need a query to give a YTD total that should sum each month before it. For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would ...
0
votes
1answer
112 views

MySQL logging max_questions resource

How would I know in MySQL if the user has exceeded his max_queries per hour? I know that I can see this error on the client side, but I just want to know that before client is reporting to me, ...
0
votes
1answer
486 views

mysqldump vs mysqlbackup

I am using MySQL v5.6.x. and using mysqldump takes a long time to dump and restore the db and does not seem practical for DB size larger than 50GB. It looks like the mysqlbackup utility that comes ...
0
votes
1answer
90 views

Multiple select for single row insert

I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables. insert into tab(id,name,date) values ((select max(id)+1 from tab),(...
1
vote
1answer
33 views

How to check if column exists in case statement?

I think a case statement would be the best approach to this but am open to any other suggestions. What I am trying to do is this. The query is generated based on a users input regarding the type ...
0
votes
1answer
174 views

MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

I am a developer so was hoping someone with DBA experience could help me out here. I am using a MySQL database (MYSQL 5.6). I came across a situation where a particular column stores Ids generated ...
0
votes
0answers
37 views

MySql Group By Query Taking More 5 seconds On An Indexed Table

I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table CREATE TABLE IF NOT EXISTS `affiliate_analytics` ( `...
0
votes
1answer
113 views

AWS RDS MySQL database created but cannot create indexes or change collation

AWS RDS MySQL 5.6.x issue: Created a MySQL database on the AWS RDS platform. Created a dozen tables, imported data to all tables and can SELECT from all tables. Granted all privileges to user on ...
0
votes
1answer
215 views

enable log_bin_use_v1_row_events in mysql 5.6

I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly. I have ...
0
votes
2answers
18 views

mysql 5.6 FEDERATED table and mysql.servers correlation

I have no expirence with FEDERATED tables in MySQL. I have database with FEDERATED tables defined and I'm wonder if this tables are used in current env (I have no access to application/developers to ...
0
votes
2answers
39 views

How to remove references to performance_schema.user_variables_by_thread in mysql 5.6?

I have a Jira 6.4.7 instance running on MySQL 5.6.44. I'm in the progress of updating the Jira instance to 7.0.5, which still supports MySQL 5.6. I accidentally hooked up a newer Jira 7.0.5 to the ...
0
votes
2answers
1k views

MySQL Cluster setup, receiving error “Specify node id”

I am attempting to set up a MySQL Cluster. I have the following setup: One management node Two data nodes One SQL node Below is the config.ini file: [ndbd default] # Options affecting ndbd processes ...
1
vote
1answer
17 views

How to find error rows in bulk insert?

MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I ...
0
votes
1answer
11 views

Copying a MySQL 5.6 server settings for a new environment?

I have a MySQL 5.6 Server running on a Windows 8 VM on a steel case server. It's been having issues (The Windows 8, which is then causing the MySQL to have issues). Therefore I was tasked with ...
0
votes
1answer
34 views

SQL delete where not in (…) query not behaving as expected

We have the following query which returns the most recent fares from our database : SELECT f1.id FROM ( SELECT * FROM fares ) AS f1 INNER JOIN ( SELECT origin_id , destination_id ...
0
votes
1answer
414 views

MYSQL Patching -Roll back

MYSQL patching. I took all database backups before starting patching. If we patch mysql 5.6.30 to 5.6.32 and if we want to rollback to 5.6.30 due to some issues. Can we do that? if so should we have ...
0
votes
1answer
14 views

Why mysql-noinstall5.6.x.zip includes ibdata file?

English is not my main language, please feel free to correct me or ask for clarification or a different explanation. I've been messing around with MySQL for Windows and trying to understand how it ...
0
votes
0answers
16 views

How to perform database archieving?

I am using mysql 5.6 in my project since last 10 years. The problem is, database size has reached to over 14GB and so system performance is getting lower day by day. Since I only need to keep only ...
0
votes
2answers
372 views

MySQL: wait_timeout and lock_wait_timeout not triggered, commit take more then the timeouts to run

I'm using MySQL 5.6 and executing a transaction on 3 tables (from ~300.000 to ~3 million rows). I'm doing some SELECTs and some INSERTs (less than 20) and then a COMMIT. I'm creating a new book, its ...
0
votes
0answers
11 views

mysql < dump.sql or source dump.sql consume all system memory

I am trying to restore a large dump (~300GB MyISAM) into a Mysql server version 5.6.48, but if using: mysql < file.sql or mysql> source file.sql In both cases, after starting the process ...
0
votes
1answer
412 views

Mysql: select from information_schema.STATISTICS hanging or slow? never saw a result

I'm trying to check statistics with select from information_schema.STATISTICS The query never ends. I have to stop it. I checked innodb_stats_on_metadata=0 like in this post Solving ...
0
votes
0answers
6 views

MySQL: A user cannot see insert to a table that happen after log in_

I have created a kind of primitive batch processor in MySQL, using scheduled events, which runs a stored procedure. Each time the procedure runs, it inserts a log message into a table. All this works ...
0
votes
1answer
11 views

DR For MySQL multi master nodes

I’ve planned to build MySQL master-master replication and need DR for this setup. I was thinking for replication from any of the node as a slave. Please help, which option is best and how to switch ...
0
votes
1answer
135 views

How to query when one ID has more than one name and find the total count of unique names, id

Same id having more than one name and the count of the unique names which are associated with the unique id
2
votes
1answer
171 views

Can I stop replication of create/drop index statement on one slave of Mysql 5.6?

I have this cluster where master and slave uses same indexes but one of the slaves has some extra indexes (large machine which runs some analytics queries). So I have to maintain two sets of indexes. ...
0
votes
0answers
21 views

MySQL - How to optimize multiple OR conditions in a query on a large table

I have a table with the following structure: Configuration ( id: binary(16) Primary key, htId: binary(16), raId: binary(16), date, amount ); I have already created these indexes: ...
0
votes
1answer
162 views

Procedure for tree view

I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows. Condition: every parent has two child. DROP ...
2
votes
1answer
795 views

Unable to downgrade MySQL from version 8 to 5.6 nor start MySQL (MySQL Error Log Included)

I run a Plesk VPS (OS: Centos 6/Linux (Final)), whereby I recently tried to upgrade the MySQL from version 5.5.55 to version 5.6. Being relatively new to this area, I strictly followed this Plesk ...
1
vote
1answer
350 views

MySQL Load Balancing what do i need Replication or Cluster?

So My MySQL database server is reaching it's limits with the number of queries selects, updates, inserts etc it can concurrently take resulting in high CPU usage more than what a single machine can ...

1
2 3 4 5
21