Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 views

Date missing from timestamp in mysql log

Currently, my log entries look like: time message 03:53:08 PM ERROR 05:40:32 PM ERROR ... ... I want the date included, like this: time message 06/30/2022 03:53:08 PM ERROR 06/30/2022 05:40:32 ...
user avatar
  • 11
-2 votes
0 answers
24 views

ERROR 1833: Cannot change column 'idParts': used in a foreign key constraint

I was trying to put a auto increment on my primary key but i kept on having this error.Help on how to solve this issue thank you ERROR 1833: Cannot change column 'idParts': used in a foreign key ...
user avatar
0 votes
1 answer
18 views

Stuck at importing data directly from the staging machine to the development machine in MySQL/MariaDB

My development database is exactly similar to the staging (and production) database. Table by table, field by field. I know that I can use this command to connect to the remote database: mysql -u user ...
user avatar
1 vote
0 answers
18 views

How can I prevent overload of Mariadb server?

I'm running MariaDB 10.2.27 on Ubuntu 16.04 (10 cores, 40 GB RAM). We have a queued message handling system (using Symfony Messenger) that normally has 20 workers consuming messages from the queue. ...
user avatar
  • 101
-1 votes
0 answers
14 views

MySQL query returns string instead of integer

I am attempting to complete a problem on Leetcode that requires the output to be an integer or a string 'null'. My attempt is to use a CASE statement that returns the appropriate response depending on ...
user avatar
  • 1
0 votes
1 answer
18 views

MySQL query missing data

I'm trying to query data from 3 MySQL tables in 1 database, filtered by fixed street name (Lucas st.), phone type (home/office/mobile) and address type (home/office). Also have to include rows with ...
user avatar
  • 1
1 vote
1 answer
15 views

Mysql View replace only one column with static valiue?

CREATE VIEW `myview` AS select * from mytable Is it possible to replace just one column with a static value inside the view? Eg mytable.myvalue = 'view'? Or would I then have to list all columns in ...
user avatar
0 votes
0 answers
16 views

MySQL Creating Unnecessary Sort Index When an Aggregate Query is a Subquery

I have a complicated aggregate query, using the functions SUM and GROUP_CONCAT, that starts returning results in a reasonable amount of time by itself. But when this query is wrapped in another query, ...
user avatar
0 votes
1 answer
16 views

wrong index in composite PRIMARY KEY in MyISAM

I use MyISAM for processing tables, as it is much faster to read large data from MyISAM as compared with InnoDB for JOINs. I create map tables for bulk INSERT, CREATE TABLE tag_map ( article_id int(11)...
user avatar
  • 4,261
0 votes
0 answers
13 views

Get combined checksum of first n rows in mysql

I want to get a checksum of supposing the first 100 rows combined checksum. Saw this mysql-rows-checksum, but this gives a checksum for every row. I want only one checksum for given n rows combined.
user avatar
  • 101
0 votes
0 answers
32 views

Is there a way to speed up/give more resources to a single query?

For 3 days I have the following query running: alter table pois modify column id bigint(12) not null; with the purpose to change the type from int(11) to bigint(12) and remove AUTO_INCREMENT on a ...
user avatar
1 vote
1 answer
40 views

How do I SELECT elements using WHERE with another table's record values in WHERE statement?

Problem summary Today part of my app crashed, but nothing else stopped with it. I need to find the first and last tracked message ids for each channel during the downtime. I log unix timestamps ...
user avatar
  • 13
0 votes
1 answer
24 views

Can't get this GROUP BY or DISTINCT right

On MySQL 5.7.34 I have this simple db structure. Important columns are domain name, domain total links in, but entries are duplicate (once for each found link from various sources), I need to select ...
user avatar
  • 133
0 votes
0 answers
15 views

Loading a csv file in to remote Mysql DB

I have a 70Mb csv with 1.5 million rows with 270 columns and I would like to put it into a MySQL database that is hosted on AWS RDS with the minimum layer. I try to do it with the MySQL Workbench ...
user avatar
1 vote
0 answers
53 views

How to create MySql witness in group replication?

We are considering migrating a single standalone MySql server on Google Cloud to a high availability setup using the "Hot standby with witness" architecture described in Google Cloud ...
user avatar
0 votes
1 answer
32 views

Amazon RDS change collate for mysql database in production without downtime

I saw a solution like this below: create a new table like your source table. alter that new table the way you want. insert your data into the new table. create indexes etc. as needed on the new table....
user avatar
  • 101
1 vote
1 answer
17 views

Is a correct way only copying files to take a mysql backup?

I'm trying to take a backup from my Mysql Server. Is it a correct way to shut down the Mysql and copy all files from the directory"/var/lib/mysql" and paste them on another directory?
user avatar
  • 11
0 votes
1 answer
33 views

Can MySQL use a where clause to inform a join?

For context, I'm using an ORM (ActiveRecord for Ruby On Rails), but having to dip down into MySQL to run some rather complex queries. As a result, it's more difficult to do some things than it would ...
user avatar
  • 103
0 votes
1 answer
20 views

How to create a table with an index that allows me to order by the time from now?

I have a table with a date time column. I have a query currently written as: SELECT * FROM foo WHERE is_relevant = true ORDER BY ABS(TIMESTAMPDIFF(SECOND, `foo`.`date`, NOW())) The goal is to return ...
user avatar
  • 103
0 votes
0 answers
17 views

MySQL Database Setup Script(s) - Limiting Created User Access

PREFACE: Per comments on my original post, this question will focus on the MySQL-specific elements of my goals. A separate question for the PostgreSQL version has been posted here: PostgreSQL Database ...
user avatar
0 votes
1 answer
18 views

MySQL partition failed

I do partition with 6.595.057 rows in MySQL with the Galera cluster. When I tried partition in server without galera, partition successfull. But when I tried partition in server with galera cluster, ...
user avatar
0 votes
1 answer
19 views

SQL: Joining 3 tables to generate report dashboard

I am trying to join 3 different tables that holds my test execution results as "PASS", "FAIL" and "SKIP". There are 2 common properties in these 3 tables on the basis of ...
user avatar
0 votes
0 answers
61 views

mysql on Mac Monterey not working regardless what I do

I am running a Mac on Monterey v 12.4 with an M1 Max. I tried to install mysql on my Mac but I never get it to work regardless what I do. The first thing I tried to do is: $ brew install mysql Then I ...
user avatar
0 votes
1 answer
37 views

Can Navicat for MySQL export ER Diagrams?

I am using Navicat for MySQL 16.0.14 - Standard, on macOS. I have used the ER Diagram view to create a useful diagram of an old database. Is there any way to export this diagram? I would like to share ...
user avatar
  • 101
0 votes
0 answers
18 views

MySQL shutdown unexpectedly after Windows update

MySQL will no longer run today after a windows 11 update. I am getting vey little information from the error log, see below. Any ideas ? 2022-07-13 17:45:57 0 [Note] InnoDB: Mutexes and rw_locks use ...
user avatar
1 vote
0 answers
31 views

How to recover corrupted innodb table from .ibd file?

I forgot to dump the databases before upgrading mysql from 5.5 to 5.7. After upgrade, I ran mysql_upgrade, which ended with the error: Error occurred: Error during call to mysql_check. It seems one ...
user avatar
  • 111
0 votes
1 answer
23 views

In MySQL can I create a view of the rows with the highest number from one field?

I have a view with SELECT downs_history_2022.`Asset Id`, downs_history_2022.Email, downs_history_2022.`User Id`, downs_history_2022.`Log Date`, downs_history_2022.Downloaded, downs_history_2022.`...
user avatar
  • 525
1 vote
2 answers
26 views

MySQL: Virtual Column based on JSON boolean value - convert boolean to tinyint possible?

I have a table with a json field attributes and a virtual tinyint (boolean) column flights with the following configuration: JSON_UNQUOTE(attributes->"$.flights") The flights property in ...
user avatar
  • 111
0 votes
1 answer
44 views

Drive space issue

Hello I have installed a Zabbix Server 3 months ago with 32GB space disk, I have 200 hosts supervised on this server. And I got this message from the "system information" part :   Error in ...
user avatar
0 votes
1 answer
13 views

Understanding slow queries from mysqldumpslow

I'm maintaining a system which sporadically (no exact time) but only at night causes the entire server to crash for a few seconds and the users seeing "Error Connection Refused" until the ...
user avatar
0 votes
1 answer
43 views

Count distinct actors an actor appeared with

I have a table: Movie Actor Supporting_actor M1 A1 A2 M2 A1 A3 M3 A2 A4 M4 A4 A5 M4 A4 A6 Calculate the total distinct actors an actor appeared with (either as an actor or a supporting actor). ...
user avatar
-3 votes
2 answers
34 views

When i run the source D:\file.sql; the following error occurs

mysql> source D:\source.sql; ERROR: Failed to open file 'D:\source;', error: 2
user avatar
1 vote
1 answer
25 views

Get first id of the latest 25000 entries?

I have a database with 200000 rows. Of those 200000 rows I want the latest 25000 and from those I need the oldest id (column entity_id). This is my query so far: select * from sales_flat_order order ...
user avatar
0 votes
1 answer
31 views

Best column type for huge numeric data

What's the best data type for storing numeric values (like 1656659676159881372) in MariaDB?
user avatar
  • 103
3 votes
2 answers
1k views

MySQL: How to optimize a certain SELECT statement which causes very high load?

Having a table with 25.000.000 entries, I have the following query which is started every ~ every 2 seconds which causes very high loads (up to 40 AAS in Amazon AWS). The execution takes 20 seconds up ...
user avatar
  • 195
0 votes
2 answers
40 views

How to make auto increment column which would identify inserts order in MySQL and PostgreSQL

I was expecting to use AUTO_INCREMENT index on MySQL database but found this answer: https://dba.stackexchange.com/a/306810/103306 A word of warning, here. The values generated in auto-increment ...
user avatar
0 votes
1 answer
27 views

MySQL8 - more cores vs higher cpu frequency? [closed]

Do you know any benchmarks which compare both cases? What is better?
user avatar
  • 113
0 votes
1 answer
67 views

AWS-RDS t3-medium instance CPU utilization reaching 100%

We are running a production workload that invloves standard EC2 RDS configuration. The RDS instance(with MYSQL 5.7) type is t3-medium which belongs to the burstable type (As the application peaks at ...
user avatar
0 votes
0 answers
25 views

Database design for a forum

I am trying to build a forum kind of like reddit. Discussions can have comments and people can comment on comments. My solution for such a hierarchical database is to use a closure table for my ...
user avatar
  • 1
1 vote
2 answers
113 views

Skip views when using mysqldump

I want to create an command line script that backs up just the tables from my database. Is there an option to do this? I thought about specifying just the tables using SELECT table_name FROM ...
user avatar
  • 133
0 votes
2 answers
25 views

Is it possible to run a query that locates which table contains a row that has over 1 million characters in a single column?

We have a MySQL database that has 701 tables. I'm trying to replicate this MySQL database into our data warehouse, Snowflake, using a data pipeline tool. Unfortunately, the pipeline tool is running ...
user avatar
0 votes
1 answer
38 views

I need to improve my model

I create a smaller app that has to be used for money transactions where it focuses on favor and expense from bank accounts (later I will expand other tables for admin, workers costs, credit ...
user avatar
0 votes
0 answers
24 views

Is there a MySQL equivalent of `DENY BACKUP DATABASE TO`?

In addition to this question: How to prevent a full database backup and for the sake of security, authorization and the principle of least privilige: Is there a MySQL equivalent of DENY BACKUP ...
user avatar
  • 101
0 votes
2 answers
31 views

Product alternatives query performance slow - Many to many tables and ROW_NUM(OVER PARTITION...) optimisation needed?

When the user selects a product in my web app, I run a query to search for related alternatives based on product categories linked to the product via a many to many relationship with a category ...
user avatar
  • 101
0 votes
0 answers
251 views

Replication instantly fails with 1032 error; Coordinator stopped because there were error(s) in the worker(s)

I have two databases, which I'm attempting to configure in a master-slave relationship (the eventual goal is to set them as master-master, but I'll settle for getting master-slave working at this ...
user avatar
0 votes
0 answers
17 views

SUM which adds together figures collected on the same date

For example all data collected on 1/1/22 would all added together and outputed as one result and data collected on 2/1/22 would all added together be another result. I will add my query bellow to help ...
user avatar
0 votes
0 answers
18 views

student scores ranking or positioning on mysqli

I have a MySQL table (named "scores") where I save student scores and I want to get each student Rank using total score column which I successfully did. Below is my code: SELECT studentRegNo,...
user avatar
0 votes
1 answer
24 views

MYSQL query a weeks worth of data each day during a certain time range

Currently the data i grab via my query is between a date AND time range however i want to change it so it querys a date range for a time range each day and output each days results(e.g 1/1/22 to 8/1/...
user avatar
1 vote
1 answer
51 views

How to exceed max_execution_time when running a mysqldump?

Is there any way to exceed max_execution_time when running mysqldump, without changing global max_execution_time? My database is in AWS RDS and I can't change these parameters for the sake of the ...
user avatar
0 votes
0 answers
28 views

Does it improve security to remove root@% from local mysql container?

I have a mysql docker container, when the volume is created on boot, the following initial script is called which creates a database, gives a user access to that database and deletes the root user ...
user avatar
  • 127