Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

-1
votes
1answer
8 views

Create calculated column min value in mysql 5.7

How to compute a column: calculation is to have 1 or 0 If it is minimum value then 1 should be shown else 0
0
votes
1answer
61 views

database and table design for billions of rows of data

basically i got 2 tables: header, details tables. CREATE TABLE `header` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `RECORD_DATE` datetime DEFAULT NULL, `TICKER_ID` int(11) DEFAULT NULL, `...
0
votes
0answers
16 views

MySQL not honoring binlog_format setting

I have an application that listens to the MySQL 5.6/5.7 binary log. It requires binlog_format to be set to ROW. This is working perfectly on RDS but I can't get the setting to work in a docker ...
1
vote
1answer
23 views

MySQL Performance vs. Memory

We are running MySQL Ver 5.7.18 on an Amazon Lightsail Linux instance (2GHz Single Core CPU, 2GB RAM, 40GB SSD). Besides MySQL, we also run some Java services on this machine, which access the ...
0
votes
1answer
21 views

Error log flooding with aborted connections (Aurora 2.03 - MySQL 5.7.12)

Here's some contextual information: I have a multi-tenant, WordPress MU installation running on Elastic Beanstalk, scaled up to 2 instances minimum. These are connected to the RDS database and are ...
0
votes
0answers
16 views

Mysql - Failed to connect to MYSQL at localhost:3306

MySQL 5.7 was working well until I added a line in the my.ini. The default character set that will be used when a new schema or table is created and no character set is defined character-set-...
0
votes
1answer
19 views

MySQL replication much slower on MySQL 5.7 compared to MySQL 5.5

I have two databases setup for testing (so no live data or connections). The first is a MySQL 5.5.57 Database acting as the master. The second is a MySQL 5.7.23 Database acting as a slave. It is ...
0
votes
0answers
5 views

percona slave ERROR 1872 (HY000)

I have setup PXC on CentOS 7.5 and replication between PXC nodes is working just fine. However, when I try to set up a slave on one of the PXC nodes, I get this error when I run "START SLAVE". root@...
0
votes
0answers
4 views

Ndb cluster change variable for heartbeat and disconnect

I search for a variable that I can increase in the server that I don't get any more this error: Node 2 missed heartbeat 2 Node 3 Disconnected Node 2 Disconnected I don't find anything. Regards,
1
vote
0answers
4 views

MySQL server-id reset in CentOS

I am running MySQL 5.7 (Percona) on CentOS 7.5.x, and when I set the serser-id the config file, MySQL is somehow changing the server-id value to '4294967295'. This is happening on all my PXC nodes. On ...
1
vote
1answer
16 views

I want to Create a brand new slave for my MySql Percona server 5.7

I have a Master Master Replication setup using mysql Percona Server 5.7, but for some unknown reasons, seems like my passive server is broken "thats not the issue", the replication is bad and the data ...
3
votes
0answers
33 views

Why do two transactions making a read lock result in a DEADLOCK exception?

Let's assume a scenario that flows as follows: Transaction #1 makes this query: SELECT v.id, v.name FROM versions_history as vh INNER JOIN versions as v ON vh.version_id = v.id WHERE vh.project_id = ...
1
vote
1answer
46 views

What is the optimal setting for innodb_buffer_pool_instances for a mid level AWS RDS server with 6gb buffer pool?

We have a MySQL 5.7 AWS RDS instance running on a db.m4.large instance. The basic resources for that instance type are: 7.5gb RAM 2 VCPU RDS defaults to a calculation that allocates 75% of RAM to ...
1
vote
1answer
17 views

MySQL Enterprise Backup download site

I was given a sever to use for tests that has MySQL commercial version installed. I would like to test backups using MySQL MEB, but cannot find the download site. I have seen many blogs mentioning ...
0
votes
0answers
23 views

cannot create table with composite and foreign key

I tried to create table with this schema: CREATE TABLE firmware_image ( device_type TINYINT UNSIGNED NOT NULL, firmware_id VARCHAR(100) NOT NULL, FirmwareImage BLOB NOT NULL, uploader INT ...
0
votes
1answer
28 views

Changes between Mysql version 5.1 TO 5.7

I have recently migrated Mysql 5.1 to Mysql 5.7. DB Migration was successful but when the application started firing few selected queries, Mysql started taking hours to execute them for which ...
0
votes
0answers
22 views

mysql (5.7) replication fails after adding user

I got Master running mysql Enterprise (5.7.12 ) and two slaves running mysql community(5.7.23). The replication is part of the downgrade from enterprise to community. I created a user on the master ...
0
votes
1answer
40 views

Information_Schema.Tables reports different result in MySQL 5.5 and MySQL 5.7

I have started testing some MySQL 5.7 databases to replace our ageing 5.5 Databases, and there seems to be a significant change in the way the information_schema.tables Table records the data size. I ...
0
votes
1answer
23 views

How to identify what is causing unusual high CPU load in MySQL?

I have a MySQL 5.7 server that is since recently under very high CPU load, but no clue where this load comes from. Looking at the MySQL processlist, it does not look like the server is doing much. ...
1
vote
2answers
29 views

How to find MySQL users causing high load?

I have a MySQL server with multiple websites running on it. These sites are mostly the same software, but since recently MySQL is under very high CPU load. I'm pretty sure there is 1 MySQL user that ...
1
vote
1answer
30 views

When was the stored procedure created and altered?

I am about to update a MySQL server in operation with the changes made on a development server. I want to be sure that I don't miss any changes. Are there any logs telling things like when the stored ...
0
votes
1answer
21 views

Get count of intersected rows on the same table

I have the following table: ID user_id skill_id 1 8 1 2 8 3 3 2 3 4 5 3 5 2 ...
2
votes
2answers
22 views

What consequence deleting idb file will have on my mysql database?

I tried to check what is consumming my Disk Space on Centos 7, I found these 2 database file: /var/lib/mysql/pub/vues.ibd: 8.1G /var/lib/mysql/pub/log.ibd: 8.6G I would like to delete them. ...
0
votes
0answers
16 views

MySQL binary log uncontrolled growing

This problem starts after connect a new slave to my master replication server. All servers have Centos 7 MySQL 5.7.22 16 GB Ram available 12 GB RAM for innodb_buffer_pull_size my.cnf (only ...
1
vote
1answer
49 views

Functional Indexes in Oracle, compared to MySQL

Hoping you can help me understand a particular problem I'm facing when converting an Oracle 12c database to MySQL 5.7. For context, let's say we have a table that looks a bit like this: id ...
0
votes
1answer
23 views

How does DYNAMIC Row format handle NULL storage MYSQL 5.7?

As a newbie to Mysql I just can't understand how NULL values are stored in each row formats. As per docs(last bullet-in), NULLs for fixed length columns (BIGINTs) also take their data space reserved. ...
0
votes
0answers
6 views

Handling Many Text Fields in a Mysql Table

Specification : Mysql (5.7.23) , Baracuda file format, DYNAMIC row format. I am working in bootstrapping a product. I have a scenario where the user may require 1 - 500 Text Fields for a particular ...
0
votes
0answers
21 views

Exporting Mysqlbinlogs from RDS to EC2

We are trying to export Mysqlbinlogs from RDS to EC2 instance.We followed the steps as per the AWS guide (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html) ...
1
vote
2answers
42 views

MySQL refuses UPDATE with (1071, 'Specified key was too long; max key length is 1024 bytes') sometimes

I'm using MySQL 5.7.2.23. I need to copy some columns between two tables (u -> m) and I am using the following update command: UPDATE m JOIN u ON u.id=m.user_id AND u.package_name=m....
0
votes
1answer
161 views

“Error in query (1030): Got error 168 from storage engine” when creating table

I'm trying to create a table on a separate directory, like this: CREATE TABLE `abcdef` ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='/media/sdc/mysql/'; But it returns the error: ...
0
votes
1answer
42 views

in MySQL what is the query to show these student's that each marks less than 50?

I have this table marks in MySQL database I used a query to output this table that shown as below: it's my output: I want use a query to show these student's that each marks less than 50, output ...
1
vote
0answers
23 views

How do Master / Slave JDBC URL handle broken MySQL Slave?

I own the following classical MySQL 5.7 set-up : one master server one slave server The master => slave link is done though GTID. I'm monitoring the link by querying show slave status on the slave ...
1
vote
0answers
33 views

Delete query end state lasts tens of seconds in innodb cluster

I have an InnoDB cluster with three nodes running with 4GB ram and 80GB SSD on Linode. I am trying to delete around 240000 of rows from MySQL InnoDB cluster in one go. However, it stuck at query end ...
0
votes
0answers
34 views

mysql - help to understand where mysql using memory

Im running a server with 10GB memory. Ubuntu 16.04 LTS. I have configured innodb_buffer_pool size to 6GB. Today I got the alert from monitoring tool which says mysql using more memory. When I saw ...
1
vote
0answers
62 views

Which columns are off-paged (ROW_FORMAT=DYNAMIC)?

When going through the MySQL 5.7 manual, I found some contradictory facts. Fact 1: When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length ...
0
votes
0answers
11 views

MySQL SNMP Audit

i was looking for a way to make MySQL Enterprise Edition to send SNMP trap with audit log information. I've enabled the audit_log module and I've added some rules (for example i've decided to log ...
0
votes
1answer
130 views

innodb_table_stats length mi smatch

This is very strange. I just copied all the binary files from one server to another. The new server comes up just fine with one exception. I keep getting this error message in my log every couple ...
0
votes
1answer
79 views

enabling BINARY LOG on AWS RDS MySQL

Context I am trying to enable BINARY LOGs on a MySQL RDS instance. Issue I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error. And using CALL mysql....
1
vote
0answers
9 views

MySQL Shell Python Client library

Is there a python client library available to interact with MySQL Admin API and to do similar stuff that MySQL Shell does. From the documentation of MySQL Shell, I could see that there is a --py mode ...
1
vote
1answer
26 views

MySQL Partitioning based on addition

I cannot find a good keyword to search for this kind of question, so here you go: Suppose i have this table: CREATE TABLE test ( tr_uuid BINARY(16) NOT NULL, Customer VARCHAR(10) NOT NULL, Data ...
2
votes
2answers
29 views

Can one make MySQL cluster on a unique index rather than a primary key?

The MySQL docs states that: When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. But that's not the only possibility, you can cluster off of a unique index instead: ...
0
votes
1answer
15 views

Set InnoDB as default engine and set a default collation

What should I do to set InnoDB as the default database engine? And is possible to set a default collation when I create a schema and tables (e.g. utf8mb4)? I don't want to have to choose these ...
1
vote
3answers
38 views

MySQL SSL replication error 2026

I've been searching and testing for hours and still can't figure this out. MySQL version 5.7.23 on both slave and master. Replication over SSL is failing with the following error message: Slave I/...
1
vote
3answers
59 views

Mysql master-master: single table out of sync

I have a master-master MySQL 5.7, everything works fine except for one error which is rising from time to time. If I run a show slave status\G on server2 I have an error on a table: ... Last_Errno: ...
0
votes
1answer
12 views

Promoting MySQL slave with unknown replication delay

Consider I have a master on DC1 and slave on DC2. Now, DC1 network is completely down for indefinite time and I want to resume the service as soon as possible on DC2 by promoting the slave to master. ...
0
votes
0answers
17 views

NDB Cluster 5.7.23 crush

We have ndb cluster 5.7.23 , with 4 data node in 2 groups and 2 management node. All cluster go down. I check for network problem with amazon and in the logs, but nothing. Node 1-4: Data Node Node ...
1
vote
1answer
38 views

Queries logged in the 'Slow query log', and 'start transaction' vs 'set autocommit = 0'

Currently long_query_time is set to 5, and that works. However, many queries are part of a transaction, and the slow query log only keeps the final commit;. # Time: 2018-08-15T15:18:18.205309Z # ...
2
votes
1answer
36 views

Database Directory storage is full need to change link it to new Partition

My mysql Database Directory size is full. So I want to change it to another partition where there is large amount of space. The database version am using is MySQL 5.7.23 and OS is CentOS 7. After ...
-1
votes
1answer
47 views

MySQL ZERO warnings

I'm getting the error below in my MySQL log file. [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with ...
0
votes
1answer
17 views

Want to output post title with meta-tags at same time on MySQL of WordPress

I want to edit meta-tags of a blog page of my WordPress blog using SQL editting tool such as MySQL workbench. I want SQL output such as below. Can not? # post_title, metatag 'metadev', 'hoge' '...