Questions tagged [mysql-5.7]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
4 views

How Do Multiple Innodb Buffer Pool Work in Mysql 5.7

Assume that I am using the default settings in Mysql 5.7 which has 8 buffer pools and each is allocated 1GB. Then assume that there are 3 tables, each table are 2GB each. Also, assume this is a test ...
0
votes
0answers
6 views

MySQL 5.7 - How to choose the value of innodb_online_alter_log_max_size

I've been attempting to Drop a column on a table for a few days now, but each time, it runs for a while before failing with: [Note] Aborted connection 26 to db: 'dbname' user: 'username' host: '1.2.3....
0
votes
0answers
3 views

Copying fields from first line item to header in MySQL (5.7 on Amazon)

We have a header table ("opportunities") and a line-item table ("opportunity_details"). Each header record has 7 fields of vendor information that get copied from the first (by record-ID) line-item ...
0
votes
0answers
11 views

Unknown suffix 'l' used for variable 'server_id' (value 'logBinIndex')

while i'm executing this command mysqld --initialize --user=mysql --datadir=/var/lib/mysql , i'm getting this error : 2019-07-11T10:07:04.599756Z 0 [ERROR] Unknown suffix 'l' used for variable '...
0
votes
0answers
11 views

MySQL 5.7: Replication of the master which is replicated from a different master

I have internal MySQL master in which as yet no one has recorded. It is temporarily replicated from another external master, which should soon be extinguished. Now I am trying to set up replication on ...
1
vote
1answer
21 views

Why does performing a query on a second environment, it takes forever to run, and scans hundreds of millions of rows?

I have a query, it is fairly big, but not THAT big. One the baseline environment, it runs in less than 1/3 of a second. When we moved the database to RDS in Amazon, it takes 10-15 minutes to run. I ...
0
votes
0answers
18 views

MySQL 5.7 using /tmp directory, and filling it up

We have recently upgraded from MySQL 5.5 to 5.7.26. When running the following query: INSERT INTO table1_archive SELECT * FROM table1 WHERE dc between "2019-06-01 00:10:00" and "2019-06-31 23:59:...
0
votes
0answers
19 views

Why does MySQL tend to avoid indexes with few non-NULL values, even when it has a large penalty?

Over the last upgrades (5.6 -> 5.7 -> 8.0), I've observed that MySQL increased its aversion to indexes with few non-NULL values. This caused some seriously underperforming queries that I needed to ...
0
votes
0answers
14 views

Mysql memory_global_total is smaller than memory usage in top

When I try top in my ubuntu the result top - 16:03:09 up 16 days, 21:24, 2 users, load average: 3.53, 2.72, 3.46 Tasks: 395 total, 1 running, 282 sleeping, 0 stopped, 0 zombie %Cpu(s): 10.0 ...
-1
votes
0answers
14 views

how calculate grand total and sub total from this table

I have 2 tables like the following: table list_acc: id_acc nama_acc parent_id_acc level_acc pattern_volume id_sat ------- ---------------- -------------- ---------- ------------...
0
votes
1answer
48 views

Why the difference in query time for these two queries?

I have a MySQL 5.7 database with a large table that has about 70,000,000 rows. There are 4 columns: Id, Time, Value, Monitor_Id. Id is the primary key, Time is indexed and Monitor_Id is a foreign key. ...
0
votes
0answers
22 views

How to manage big database in mysql

I have a database of analytic information. my database as is below. Table Name: display_page. here we store data about page when customer(of our user) create an order.(page has multiple sections). ...
1
vote
1answer
49 views

Mysql eat a lot of memory but innodb_buffer_pollsize is set to 50%

I don't understand why my mysql eat a lot of memory even though I set innodb_buffer_poll_size to around 50% here is my top user@Mulia-Abadi:~$ top top - 11:09:31 up 12 days, 16:30, 3 users, load ...
0
votes
1answer
17 views

Mysql Server always throwing unknown error

Sorry for the bad formatting since its the first time When i try to delete a row which has a foreign constraint, i'm expecting the Mysql server to give me the constraint key and the table to which ...
0
votes
1answer
20 views

How to MySQLDump with For Channel to set up multi-source replication?

I'm trying to set up multi-source replication. But, when I try and import the second database I get the error: ERROR 3079 (HY000) at line 28: Multiple channels exist on the slave. Please provide ...
0
votes
0answers
42 views

MYSQL 5.7 Note Got an error reading communication packets

134359 [Note] Got an error reading communication packets Appears in logs every 5 minutes, I have seen a few similar posts on here however none actually match the error I'm seeing here. MySQL 5.7 - ...
0
votes
0answers
8 views

ERROR 1064: Cannot execute while loop in mysql using variables [duplicate]

I'd like to run the below query to insert into a table in a loop: SET @maxid = ( select max( length( id ) ) from article ) ; SET @x=1; WHILE @x<= @maxid DO INSERT INTO `article_responder` (...
0
votes
0answers
12 views

All rows not being exported in MySQL dump

I have a MySQL instance (5.7) which is just a single instance. It is not setup to have slaves/replicate. I've updated my stack to have slaves. In the process of moving to this newer stack, I needed to ...
0
votes
0answers
33 views

Migration from MySQL 5.7.10 to Aurora (MySQL)-5.6.10a Serverless and switching sql_mode

I am looking into migrating my locally managed MySQL 5.7.10 database to Amazon Aurora Serverless. I am tired of managing my database manually and I would like to use an auto-scaling feature of Aurora ...
0
votes
0answers
14 views

MySQL Replicated table: How can I prevent a trigger chain?

I've written a trigger to log updates on a replicated table. The trigger inserts into a separate table. We use MIXED replication which defaults to statement-based. The trigger spins around and ...
0
votes
1answer
35 views

How to link customers and suppliers in a database

Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier ...
0
votes
0answers
14 views

How to take mysqldump with Generated column?

I am getting below error while importing the dump, ERROR 3105 (HY000) at line 82: The value specified for generated column 'column_name' in table 'table_name' is not allowed. When I describe the ...
0
votes
1answer
29 views

Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?

MySQL server version: mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL)) MariaDB mysqldump CLI version: mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64) I am ...
0
votes
0answers
14 views

using Federated tables for different data sources

We are planning to create a standalone MySQL server specifically for Federated tables that points to 4 DB instances. Sample table: federated_db1_users federated_db1_transactions federated_db2_users ...
2
votes
2answers
114 views

How to optimize a MySQL joining to itself?

I need to optimize a MySQL (5.7.12) query. I've been reading the docs about optimization in MySQL docs but It's been hard for me to comprehend it. I have two tables: vehicles CREATE TABLE `vehicles` ...
0
votes
0answers
35 views

Mysql 5.7.25 crash on first query to corrupted innodb table

Setup: Ubuntu 18.04.1 LTS 4.15.0-34-generic #37-Ubuntu Docker: 18.06.1-ce build e68fc7a mysql:5@sha256:e47e309f72c831cf880cc0e1990b9c5ac427016acdc71346a36c83806ca79bb4 mysql --version = Ver 14.14 ...
0
votes
2answers
38 views

Select users that are in the same team

We have these 3 tables: USERS userid name email TEAMS teamid name options (0,1,2 or 3) TEAMUSER id teamid userid isadmin (0 or 1, if they are administrator of team) Let's say we have user 1, 2 ...
0
votes
1answer
16 views

In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!. The problem is that I have a cursor that is crossed, but believe ...
0
votes
1answer
55 views

How to implement many-to-many relationship with one table

I have a table: CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE IF NOT EXISTS emp_list( id INT UNSIGNED NOT NULL AUTO_INCREMENT primary key, first_name VARCHAR(30) NOT NULL, last_name ...
0
votes
2answers
68 views

innodb high i/o , some parameters make mysql crash

i have a windows 2012 server with 16 core cpu and 64 gig of ram and 3*146gig sas 15k that have been used as a database server and just have mysql installed as a part of xampp. the mysql version is 5....
1
vote
2answers
24 views

Get the Largest Count of Consecutive Value

I have this table that records win and lose. Let us say this is my table create table test( col1 int not null )Engine=InnoDB; it has 10 rows : 0,1,1,1,0,1,1,0,0,1 1=Win and 0=Lose. I want to ...
0
votes
1answer
49 views

Mysql - generating trends of rank

I've a table of students score ID_STUDENT | SCORE ------------------ 1 | 90 2 | 80 3 | 99 4 | 80 5 | 70 ...
0
votes
0answers
18 views

MySQL(5.7.23) starting automatically with skip-grant-tables

I recently installed MySQL 5.7.23 and started mysql service later ran mysql_secure_installation but while changing the password ran into any issue where it says that i cannot change the password as ...
0
votes
0answers
35 views

Replacement to JSON_ARRAYAGG in MySQL v5.7.12

I have to group by some data from JSON fields, but how do I do that if I can't upgrade to 5.7.22? I can't do that because of requirement to use AWS Aurora which lags behind and only supports 5.7.12 ...
0
votes
1answer
47 views

“Cannot drop index 'unique_token_data': needed in a foreign key constraint” in MySQL without a foreign key

When I try to remove a unique index, MySQL Ver 5.7.26-0ubuntu0.19.04.1 says that it is needed in a foreign key constraint: mysql> ALTER TABLE project_erc20token DROP INDEX unique_token_data; ERROR ...
0
votes
1answer
28 views

select top 5 accounts in each region in an month per sales

My data is in table Sales ACCT_ID |REGION | DATE_FILTER | SALES 1001 | TEXAS | 2018-01 | 10 1002 | DENV | 2018-01 | 30 1004 | WDC | 2018-01 | 50 1001 | TEXAS | 2018-02 |...
0
votes
1answer
18 views

Installed mysql 8 on Mac then mysql 5.7 now now they wont work

I have a brand new Macbook pro with Mojave. I installed mysql 8 using the dmg file but realized that Sequel Pro is not playing nice with it and after researching I decided to install mysql 5.7. This ...
1
vote
1answer
35 views

MySQL Case Sensitive Join on Varchar

I have two tables, both with identical data types, character sets, and collation explicitly specified. CLERK CHAR(3) CHARACTER SET latin1 COLLATE latin1_bin NULL For a case-sensitive join on the ...
0
votes
0answers
19 views

Composite index to cover sum of columns

Is it possible to create an index in MySQL that will help with a query like this: SELECT a, b, c FROM `table` WHERE a + b + c > 0 I've tried adding a composite index on all a, b, and c to no ...
0
votes
1answer
33 views

Very slow JOIN + ORDER BY query only in dev environment

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking 75 ...
3
votes
0answers
28 views

Will my database index be more performant with a smaller varchar size?

We're using Mysql 5.7 We have an index over a varchar(255) column in a table of ~ 500M rows. select distinct varchar_column from table yields seven results, with the max length 21. A DBA consultant ...
1
vote
0answers
57 views

How does mysql optimizer works?

I have two tables t_core_blueprints,t_ci. CREATE TABLE `t_core_blueprints` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `last_modified_on` ...
0
votes
1answer
15 views

Concern regarding auto_increment in MySQL InnoDB

I'm developing and implementing CFR Part 11 compliant system. In each table I create a primary key that is used for audit trail. I implement triggers fired on create, update and delete operations. I ...
1
vote
1answer
40 views

Amazon RDS MySQL Troubleshooting High Sessions

I just migrated my forums application to AWS. I'm in the middle of tweaking things for performance and I noticed that the number of sessions for my RDS instance (t3.large) is consistently exceeding 2....
0
votes
1answer
34 views

MySQL Internals: SQL Execution Flow [closed]

This is a descriptive question regarding MySQL SQL flow architecture. I need a flow chart on the complete steps regarding MySQL SQL flow using the following components. eg the sequence an UPDATE query ...
1
vote
1answer
31 views

MySQL XOR produces incorrect results

Why does the expression conv('9000084e703720f9', 16, 10) ^ conv('801A93A5A18A773F', 16, 10) produce wrong result? In a step by step explanation: SELECT conv('9000084e703720f9', 16, 10); Result: ...
0
votes
0answers
15 views

MySQL 5.7 Replication Lag

I am facing a wierd situation regarding Replicatio between two 5.7 , my replication is lagging by hours and the Exec_Master_Log_Pos and Relay_Log_Pos won't change and got struck , the issue is with ...
0
votes
0answers
23 views

When does autocommit take place when many rows are inserted as a single statement?

I have some problems with performance of insertion of about 50000 rows into a table. As an optimization it's advised to start a transaction explicitly to avoid committing on each row. However, I ...
1
vote
1answer
44 views

Mysql: Insert into JSON column type with GROUP_CONCAT

I'm trying to insert a list of words into a column with the JSON type: create table my_table ( ... synonyms JSON ... ) My query looks like this: INSERT into my_table (id, synonyms) ...
0
votes
1answer
25 views

Mysql 5.7 - select where json

I have some JSON data stored in a filters column. An example from one row: name | filters ---------+---------------------------------------------------------- John Doe | [{"Year":2004, "SportID":...