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
0 answers
8 views

Organize IDB tables in subfolders in MySQL data folder

I can't seem to find the answer elsewhere, and i apologize if the question has been done before, but is it a way to force a MySQL server (community edition) to organize the idb files into subfolders ? ...
user avatar
  • 1
0 votes
1 answer
109 views

Is it okay to turn off new flags in optimizer-switch setting for making a higher version of mysql behave like a lower version?

We have been using MySQL 5.6 on Amazon Aurora RDS but are now forced to upgrade to a higher version due to end of life. We noticed that MySQL 5.7 and 8 were not performing as well as 5.6. For some of ...
user avatar
0 votes
0 answers
14 views

Is it possible to capture the output of SHOW OPEN TABLES into a table?

I need to periodically capture the output of different SHOW commands in mysql, for example SHOW OPEN TABLES. I know I can use a cron job to do something, but I'd prefer to do it internally, if ...
user avatar
  • 221
0 votes
0 answers
38 views

Change Column size of a table

We have a mysql 5.6 cluster, 1 master 5 slaves. Requirements:- We have to modify the size of a column varchar from 20 to 35 for 2 databases. What is the best approach for doing this accross the ...
user avatar
0 votes
0 answers
14 views

select into outfile skips rows?

I have a simple table that I have dump into a csv files like this: select * from game_action into outfile '/var/lib/mysql-files/dump/game_action.csv' fields terminated by ',' enclosed by '"' ...
user avatar
  • 221
1 vote
1 answer
111 views

Mysql 5.6 Memory usage keeps increasing till it is killed by OOM killer

we have a production mysql 5.6.46, which is under heavy load (2000 Queries per second). The memory allocation to Mysql keeps on increasing over a period of time (sometimes in 1 day, sometime in 1 week)...
user avatar
  • 11
0 votes
1 answer
36 views

InnoDB Buffer Pool and Buffer Log

I have been reading too many information about InnoDB parameters, but I still have a doubt. Should "innodb_log_buffer_size" be larger than "innodb_buffer_pool_size"?
user avatar
1 vote
1 answer
36 views

WHERE + GROUP BY + HAVING to SELECT only the rows where I have different column ID's

I am trying to select only the rows where I have different columns of the PartnerID column. I have 1 simple table which contains 3 main columns - ID, Acct and PartnerID. I am trying to GROUP BY Acct ...
user avatar
  • 121
0 votes
0 answers
25 views

Does a multirow batch insert in Aurora MySQL count as 1 insert?

We modified our application from single row INSERT ... ON DUPLICATE KEY UPDATE to batch 500 records at a time. I notice in the AWS console, the Insert Throughput shown for our Aurora MySQL 5.6 DB has ...
user avatar
  • 414
0 votes
1 answer
25 views

In which case Optimizer rearranges the conditons in mysql?

recently I have added index "new_index" on this table. CREATE TABLE `y` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `shipping_provider_id` int(10) unsigned DEFAULT NULL, `...
user avatar
  • 89
0 votes
0 answers
38 views

MySQL replication out of AWS RDS lagging

I have a very large database in RDS - just under 3 TB. I have set up a replication slave in an EC2 instance (t2.xlarge with io1 disk volume for data). OS is Debian 9, DB is MySQL 5.6. The problem is ...
user avatar
  • 221
0 votes
1 answer
36 views

How can I get MySQL to use the right index

I have a query on a table with almost 1.8M rows. I created an index new_index which includes all columns in the WHERE and ORDER clauses in order to improve performance. Now I issue the query: select ...
user avatar
  • 129
0 votes
1 answer
174 views

MySQL not using index as expected with a WHERE IN clause

I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, ...
user avatar
  • 129
0 votes
0 answers
42 views

insert does not work in aurora serverless?

This is in continuation of Aurora Serverless - tables are empty after successful insert, which I asked yesterday. I create a separate question to highlight what seems to be the real problem: I have ...
user avatar
  • 221
-1 votes
1 answer
116 views

Error in Master/Master replication between Mysql 8 and Mysql 5.6

I've setup master to master replication Between a Mysql 5.6 and Mysql 8.0 server. I can get both slaves to run on both servers. Except when there is a change made on the Mysql 8.0 server (e.g. ...
user avatar
  • 1
1 vote
0 answers
15 views

mysql mirroring

I have 2 mysql server 5.6.34 running, one on aws RDS other locally. Is it possible to set up mirroring from RDS to local one? I know that later version like 8 support replication and mirroring via ...
user avatar
1 vote
1 answer
50 views

Inconsistent results between MySQL and MariaDB when using variables and order

I'm building an accounting table and I need to show the current balance on each transaction. Everything is working fine in my local environment (Windows 10 + MySQL 5.7.19), but the same query gives me ...
user avatar
  • 113
-2 votes
2 answers
42 views

MySQL Upgrade Procedure in a production environment [closed]

Am new to the role and employers have asked me to come up with a plan to upgrade the MySQL 5.6 on the production server to 5.7. It has partitions and MASTER-MASTER replication setup and has around ...
user avatar
1 vote
1 answer
117 views

Table is full even with innodb_file_per_table

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
user avatar
0 votes
1 answer
362 views

How to fix file format in tables after upgrade of mysql 5.6 -> mysql 8.0.23 in AWS RDS?

I upgraded mysql 5.6 -> mysql 8.0.23 in AWS RDS. There is a warning: Creating indexes larger than 767 bytes on tables with redundant row format might cause the tables to be inaccessible. You have ...
user avatar
0 votes
0 answers
13 views

AWS RDS MySQL: Strange difference between monitoring and actual processes?

I have an RDS Mysql instance with a reader and a writer. A while ago I set up a scheduled event, which copies the contents of information_schema.processlist to a log table, adding a timestamp. Now I ...
user avatar
  • 221
0 votes
1 answer
50 views

Elusive problem on AWS RDS MySQL

I have a provisioned, single-master RDS DB (5.6.mysql_aurora.1.23.0) with a writer and a reader. The clients are a bunch of jetty applications with a pool of up to 250 connections each. I have for ...
user avatar
  • 221
0 votes
2 answers
24 views

Is it possible to use a default partition for all reads?

I have a legacy application which queries one of its database (MySQL on AWS Aurora) tables extensively. Lots of queries and lots of query types. 90% of the rows in this table are outdated and are ...
user avatar
  • 129
0 votes
1 answer
113 views

How to Update Date in each row with different dates in MySQL

I want to update date column of the table with dates in the increment of 1 for all rows. How do I do it in MySQL? Table: tbl_question_of_the_day Column: date So the output that I want is: Row 1: Today ...
user avatar
  • 101
-1 votes
2 answers
113 views

How to make transactions atomic in Mysql 5.6

How do we make the following transactions atomic? conn - is a connection to the old primary, which is being reset as replica now. Python code conn.execute('unlock tables') conn.cursor().execute('stop ...
user avatar
0 votes
2 answers
3k views

How to install mysql server 5.6 on ubuntu 18.04 or 20.04 system?

What are the steps to install MySQL 5.6 on Ubuntu 18.04 or 20.04 system? I know that MySQL 5.6 version is not available in 18.04 and 20.04 apt repos. Any other way to install this particular version?
user avatar
0 votes
1 answer
1k views

an error in your SQL syntax; check the manual that corresponds to your MySQL server version [closed]

I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6 while creating a table it is showing me this error ERROR 1064 (42000) at line 25 in file: 'name.sql': You have an error in your SQL ...
user avatar
  • 69
0 votes
1 answer
59 views

migrate mysql DB between two servers using replication

I need to perform some steps, I have to migrate our company live DB to a new server, if I use normal way such as lock table, mysqldump, scp, mysql restore, it take around 5-6 hours, so I want to make ...
user avatar
  • 1
0 votes
0 answers
17 views

Problem with connect mysql databases of remote

I'm getting error with connect remote mysql database. Firstly; 1-) I use windows 10 machine and my remote server also windows 10 machine. 2-) My database at locale at remote windows machine with root. ...
user avatar
  • 1
2 votes
2 answers
79 views

Select maximum of a count in a grouped clause

I have the following tables: Vehicles(v͟i͟n͟, model,category) Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date) vehicleSold(saleID,v͟i͟n͟,salePrice) When I join these tables using: select YEAR(Sales....
user avatar
1 vote
0 answers
122 views

Faster way to restore MySQL innoDB dump

I have a below MySQL configuration. # The MySQL server [mysqld] .... key_buffer = 384M max_allowed_packet = 5M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = ...
user avatar
1 vote
1 answer
99 views

Find users having grants on a table in MySQL

We grant access to users with the following grant select, insert, update, delete on product_events to 'product_admin'@'%' ; Now, like this there are a lot of tables, and a lot of users. I want to ...
user avatar
0 votes
0 answers
20 views

Store Procedure Two different database and join help

I try to write store procedure that ı have a question.Firstly I have two db in same local server.One of them is hfcenta_test db has agency old datas and it has country code column with country name....
user avatar
  • 1
0 votes
1 answer
53 views

How can we implement "A student can enroll in up to 4 units in a semester "

I have a practice problem on Database implementation and am confused. For this database, I need to store enrollment information on students. Information such as which units a student has enrolled into....
user avatar
0 votes
1 answer
66 views

Mysql slow query on large table

With Mysql 5.6.10, I have a table like this: CREATE TABLE `es_user_action` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(11) unsigned NOT NULL, `company_id` bigint(11) ...
user avatar
1 vote
4 answers
841 views

Mysql 8.0.21 long lived prepared statements have a memory leak ? or are we doing something weird

-- updated the question because it was easy to initially assume I was asking for mysql tuning tips, although some of the tips did help me narrow down the problem -- -- further update. After doing ...
user avatar
0 votes
0 answers
28 views

mySQL service stops after a number of hours

I am still pretty new to running databases, but after my last upgrade from mySQL 5.5.62-0+deb8u1 to 5.6.48 on my Debian machine, it appears that the mySQL service stops running a number of hours after ...
user avatar
  • 1
-1 votes
3 answers
67 views

Optimal Indexing Strategy

For the sake of discussion, please refer to the simplified structure of our ecommerce database below (running on MySQL 5.6 using InnoDB engine). At this point in time, the transactions and the ...
user avatar
0 votes
1 answer
32 views

Handling the foreign keys in data entry

I have two tables (Order and equipment) and they should be linked together by the order No. the SQL script is as the following: -- ----------------------------------------------------- -- Table `rr`.`...
user avatar
6 votes
3 answers
2k views

Are multiple indexes on MYSQL table reason for slow UPDATES and INSERTS?

The performance of my (LAMP stack) site has degraded significantly over the last couple of days despite no code updates. It seems that only inserts, updates, and deletes of a particular MySQL table ...
user avatar
  • 61
0 votes
1 answer
55 views

A strange problem with SELECT

I have a query that produces zeros when it shouldn't: mysql> select > count(game_action_id) ga_count, > count(distinct(game_action_id)) count_distinct, > count(case ...
user avatar
  • 221
4 votes
1 answer
148 views

Same Join Constraints on Multiple Columns

We have a situation that I have been able to recreate with the following simple example. I have the following two sample tables: CREATE TABLE contact_info ( id INT UNSIGNED AUTO_INCREMENT, ...
user avatar
  • 235
1 vote
2 answers
960 views

Creating a table with CHECK constraints in MySql workbench

I created a table ORDER using Mysql Workbench as follows: CREATE TABLE IF NOT EXISTS `rr`.`ORDER` ( `Order No.` INT UNSIGNED NOT NULL COMMENT 'The order of the inspected equipment', `Order_Type` ...
user avatar
0 votes
1 answer
582 views

innodb_autoinc_lock_mode = 2, real danger if not using AUTO_INCREMENT columns

I've got a MySQL RDS database that uses pure generated GUIDs for key columns. The current value of the innodb_autoinc_lock_mode is 1, and due to some performance bottlenecks we want to change it 2. ...
user avatar
1 vote
1 answer
513 views

Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?

We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for ...
user avatar
1 vote
1 answer
544 views

Confused by GRANT ALL PRIVILEGES causing Access Denied for database 'mysql'

I thought I understood grants in mysql, but this particular problem has me stumped. I am logged in as the 'dbadmin' user on a mysql 5.6.27 database. +---------------------------------------------------...
user avatar
  • 111
0 votes
1 answer
42 views

Mysql - Shink table after delete

I have some tables with millions of rows (up to 6MLN) and I need to execute a delete to reduce the amount of rows. I created a procedure with a loop that delete 10k record each execution. What I did ...
user avatar
  • 103
0 votes
0 answers
92 views

Mysql trying to drop table and how to recover data?

I have on lamp setup where suddenly I see all the database files are missing and gone and when I checked the error logs I can see few of this lines. InnoDB: Error: table `com`.`#sql` does not exist in ...
user avatar
  • 177
0 votes
1 answer
602 views

GRANT ROLE TO USER in MySQL

I created one role QI_RO, granted all privileges on all schemas on the server and I was trying to grant role privilege to another user but throwing error. GRANT QI_RO to test_user@'%'; Error Message: ...
user avatar
  • 358
1 vote
1 answer
256 views

MySQL takes huge memory by ignoring buffer pool size

I am facing a very strange problem, looks like on mysql startup, mysql is taking so much memory than 4x of buffer pool size. I am using Ubuntu VM (4 Core 8 GB) with MySQL 5.6.33. /etc/mysql/my.cnf is ...
user avatar
  • 111

1
2 3 4 5
21