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'
'...
