Questions tagged [mysql-5.7]
MySQL version 5.7 - please also tag with mysql for search purposes.
567
questions
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":...
