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
0answers
22 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 ...
0
votes
0answers
15 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 ...
0
votes
1answer
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, `...
0
votes
0answers
37 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 ...
0
votes
1answer
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 ...
0
votes
1answer
48 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, ...
0
votes
0answers
15 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 ...
-1
votes
1answer
83 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. ...
1
vote
0answers
10 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 ...
1
vote
1answer
33 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 ...
-2
votes
2answers
35 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 ...
1
vote
1answer
79 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;...
0
votes
1answer
163 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 ...
0
votes
0answers
12 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 ...
0
votes
1answer
24 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 ...
0
votes
2answers
21 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 ...
0
votes
1answer
66 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 ...
-1
votes
2answers
74 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 ...
0
votes
2answers
2k 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?
0
votes
1answer
686 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 ...
0
votes
1answer
26 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 ...
0
votes
0answers
13 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. ...
0
votes
0answers
59 views

MySQL 5.6 - Running mysqldumpslow on Windows

I am trying to run mysqldumpslow on Windows server with the command mysqldumpslow.pl -t 5 -s at H:\MySQL\slow.log > H:\MySQL\summaryslowlog.txt I have installed ActivePerl on the server, but when ...
2
votes
2answers
59 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....
1
vote
0answers
80 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 = ...
1
vote
1answer
55 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 ...
0
votes
0answers
18 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....
0
votes
1answer
49 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....
0
votes
1answer
58 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) ...
0
votes
0answers
17 views

MySQL: duplicate partitions?

MySQL 5.6 on Debian 9: I have a master replicating to a slave. On the master I ran an alter table ... to add more ranged partitions: alter table game_action reorganize partition pMAX into ( ...
1
vote
4answers
491 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 ...
0
votes
0answers
23 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 ...
-1
votes
3answers
65 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 ...
0
votes
1answer
30 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`.`...
6
votes
3answers
1k 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 ...
0
votes
1answer
54 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 ...
4
votes
1answer
143 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, ...
1
vote
2answers
610 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` ...
0
votes
1answer
373 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. ...
1
vote
1answer
294 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 ...
1
vote
1answer
305 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. +---------------------------------------------------...
0
votes
1answer
29 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 ...
0
votes
0answers
59 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 ...
0
votes
1answer
86 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: ...
1
vote
1answer
147 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 ...
1
vote
1answer
229 views

Why does our MySQL server become non-responsive with the processlist showing tons of processes waiting in STATE=init, INFO=commit?

I work for a company that's been around for a while and has a large MySQL (5.6.48) monolith running in RDS. Recently, the database has starting going unresponsive for 10-30 minutes at a time during ...
0
votes
0answers
153 views

Rolling back transaction with trx_mysql_thread_id of 0 create deadlocks on update

I'm using MySQL 5.6.41 on AWS RDS. I have seen, recently, lot of transactions ending as deadlocks. Using SELECT * FROM information_schema.innodb_trx; I found that a transaction was always there. +-----...
0
votes
1answer
17 views

MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly

I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table. JobTable (...
0
votes
0answers
623 views

AWS RDS is showing very high wait/synch/mutex/sql/ values and EXPLAIN statements in performance insights

I'm running a CRON script which checks the database for work and executes anything that needs to be done. It does this across ~500 customers per minute, but we are using AWS RDS with a 16 vCPU ...
1
vote
0answers
49 views

Mysql “memory usage” keeps increasing

I have 32core CPU 204GB RAM Mysql 5.6 server, hosted on GCP VM. I keep getting RAM high usage issues. I have around 200+ tables, most of which are Innodb. At any time, i see arnd 1k+ active ...

1
2 3 4 5
21