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
1
vote
1answer
44 views

mysql tunning with larga database

soo.. I have a server in a VM with a large database in MySQL 5.7 (centos), and I was trying to tune a little bit the server (32gb RAM) and, so far I'm running this my.ch I did use the perl script ...
0
votes
0answers
32 views

UPDATE rows and SET ordering depending on level and parent

I'm trying to write a small UPDATE query that makes sure all rows are reordered considering the parent_id. Needs to work under MySQL 5.6 / MariaDB 10 This is a DB Fiddle made for testing. So this is ...
0
votes
1answer
51 views

Why does this sql join query take much longer using a reference to a row from the first table in the 'on' clause versus using a literal variable?

I have two tables, 'auth' and 'ips', which I'd like to join in a query. Table 'auth': column name data type Extra id int AI date varchar(6) - timestamp varchar(8) - result varchar(8) - ip ...
0
votes
1answer
28 views

Data too long for column when to alter column definition

The column definition is as below: `code` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL I want to alter another table(option)'s code field as same definition: ALTER TABLE `...
1
vote
1answer
18 views

Altering mysql database column causes error in another column

First of all sorry everybody if this question sounds too basic. I have a Wordpress database and a table wp_comments. In this table, I have two DATETIME fields, whose default value is 0000-00-00 00:00:...
0
votes
1answer
23 views

service mysql reload results 'Access denied', but service mysql start works

Using Debian 10.10. I have a MySQL problem. service mysql reload results 'Access denied' service mysql start executes correctly. All databases are working correctly after starting mysql. Strange, ...
3
votes
2answers
67 views

Apply nth occurrence number on each distinct value in a column

I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
0
votes
2answers
52 views

database changes are lost in the next session

We just move to Amazon RDS. Now all the CREATE, UPDATE, and INSERT commands are reverting in the next session. All the tables are InnoDB and query caching is on. What could be the problem?
0
votes
1answer
101 views

What does it mean when slave status shows SQL running but IO not running?

What exactly does this mean, when the SQL is running but the IO is not running on the Slave in a Master-Master replication setup? There are no other errors showing when running show slave status\G ...
2
votes
3answers
44 views

Preserve Autoincrement ID

I am working on an e-commerce webapp and here's what I am thinking about. To make it simple in this question, the user table contains two columns:id (int,PK,NN,AI),name(varchar(45),NN). I want the ...
1
vote
1answer
46 views

calculate working hours mysql based on in out punch

I have data of employees in and out punch in one mysql table and i want to calculate work time according to time between in and out punch. when employee's first punch found consider it as in and ...
-1
votes
1answer
128 views

How to resolve errno: 150 "Foreign key constraint is incorrectly formed" [closed]

CREATE TABLE `account_transfers` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `uuid` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, `from_account_id` bigint unsigned ...
-1
votes
2answers
80 views

Help Me For Implementing MySQL tuner suggesstions

Mine is Digitalocean with 1vCPU, 1GB -25GB SSD Ubuntu server with nginx, MariaDB, phpmyadmin and running a wordpress blog. Mysql is taking too much cpu and on running running MySqlTuner, I got these ...
0
votes
1answer
27 views

Adding Virtual Column after column x is very slow

In our environment, we usually add and drop generated columns every now and then. There is a very large table in the database with more than 3 million rows and adding/removing column was extremely ...
0
votes
3answers
53 views

Need Advises on Updating MySQL Database(Both software and hardware)

I have three in production MySQL database (5.7) with about 160GB of Data. The largest table is about 20GB, and the largest schema is about 40GB. I think all tables are innodb table. Currently I don't ...
0
votes
1answer
100 views

How can I create a mysql database (if it doesn't exist) or replace it (if it exists)?

My dump: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql My restore mysql --host=...
1
vote
1answer
23 views

Can I order/group on a column and show a limited number of the results at the top and then show the remainder in a second sort order?

I have a simple table for product data with a column called special which can be 1 or 0, indicating if the product is special and should be highlighted in listings of products that I need to generate. ...
1
vote
1answer
17 views

How to restore one mysql database with mysqldump

to make a backup I run: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt mydatabase > out.sql Is it possible to do the ...
1
vote
1answer
37 views

Optimizing a distinct on a parent table in a multi-level join in MySQL 5

I have the following tables: in a MySQL 5 database: projects - 1,000 rows tasks (FKed to projects) - 10,000 rows task_tags (FKed to tasks) - 350,000 rows task_comments (FKed to tasks) - 750,000 rows ...
0
votes
1answer
32 views

JOIN only latest row through a pivot table in one to many relation - MySQL [closed]

I have 3 tables and I only need to compare data between 2 of them but only taking in mind the latest row. The structure of the table is as follows Table Places id brand phone email 1 Place A 000000 ...
0
votes
2answers
18 views

MySQL slowlog optimsing for Having and Group By Keywords

I have below entry in the slow-log, i just wonder how can i optimise this below query as it looks very simple query that, i don't see any way of optimising it, # Query_time: 32.797535 Lock_time: 0....
0
votes
2answers
32 views

Group_concat with joining multiple tables

I have the following scheme users: researches: researchyear: uid | name rid| titleAr rid | uid | ---+-------- ---+------------ -----...
1
vote
1answer
25 views

Need to select data in every nth time 30 min, 60 min, 180 min interval

I have data in mysql which is stored in every 15 minutes. e.g. 2021-08-24 01:00:01 2021-08-24 01:15:01 2021-08-24 01:30:01 2021-08-24 01:45:01 Now I want to select data with every 60 (...
0
votes
0answers
23 views

Creating a foreign key on an empty MySQL InnoDB table takes a very long time

I have created a table called asset_versions: CREATE TABLE "asset_versions" ( "asset_id" int NOT NULL, "version" varchar(50) NOT NULL, "created_at" ...
0
votes
0answers
17 views

rotate error log file of MySQL 8.0 server over windows

we have MySQL 8.0 installed over windows and my err log file (.err) is very large (11 GB), so we need to rotate the error loge so it writes to a new file when the current file is 200 MB we tried to ...
0
votes
1answer
48 views

MySQL table + partitioning + spatial data

We have a table that currently has every postal address for a single state, currently around 13 mil records. Among postal addresses, it also contains spatial coordinates for each address along with ...
1
vote
2answers
138 views

Under what conditions is it safe to use innodb_flush_method = O_DIRECT_NO_FSYNC?

There seems to be some contention about whether one should use innodb_flush_method = O_DIRECT_NO_FSYNC on XFS or EXT4, and MySQL 8 even turns this on as part of the innodb_dedicated_server=y option. ...
0
votes
0answers
26 views

How to restore mysql database schemas with its data in ubuntu?

Accedednlty one of my employees ran a script into the production server instead of the development server. so he dropped 7 columns with its data. i everyday backup all my server files into another ...
0
votes
0answers
20 views

Foreign key on another foreign key && Pivot Table

I need to design a web app that function as poll system. It should have multiple polls with only 2 contestants which can be repeated in other polls too. For each contestant in the poll I need to count ...
1
vote
1answer
29 views

Multiple Joins With A Count

I have several tables like this A posts table +------------+-----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------...
0
votes
2answers
50 views

Default 0000-00-00 00:00:00 DateTime values in InnoDB

I have changed the MyISAM tables to InnoDB. During the conversion, I changed SQL_MODE (SET SQL_MODE='ALLOW_INVALID_DATES';) because in some tables I had DateTime records with the default value of '...
0
votes
0answers
21 views

Detail about communication packets error log

On MySQL error log /var/log/mysql/error.log I'm getting this error couple of times during the day: 2021-08-23T07:30:17.660730Z 455148 [Note] Got an error reading communication packets My question is ...
3
votes
1answer
16 views

Clarification: Replication Filters for MySQL Group Replication

As I understand it, replication filters in MySQL are not allowed for MySQL Group Replication. Is this correct? Does this imply that all databases among the group replication instances must have the ...
1
vote
2answers
61 views

Error: Dynamic SQL is not allowed in stored function or trigger

Im trying to convert multivalued column in Table A into rows of Table B(The data type of the column is varchar). For ex: Table A id value ae [1,2,4,5] ac [1,6] Table B id value ae 1 ae 2 ae ...
2
votes
0answers
24 views

mySQL cluster with Active Active DC

Let me give you a bit of background before jumping into the discussion. Our company is running two DC's in active active scenario, we spent a lot of money on getting specialized hardware to be ...
-2
votes
1answer
20 views

Mysql is shutdown unexpectdly

I am running magento in xampp, but mysql is not starting , here is the log file here is the mysql config file # Example MySQL config file for small systems. # # This is for a system with little ...
2
votes
1answer
168 views

MARIADB default users

I just installed mariadb 10.6 and can't understand... SELECT Host,User,Password FROM mysql.user; mariadb.sys@localhost password is empty. root@localhost password hashed. mysql@localhost password has &...
0
votes
1answer
45 views

Recover MySQL from copied /var/lib/mysql

I have spent most of the day trying to recover from a crash followed by a couple of bad decisions. While copying a table from DB1 to DB2, using phpmyadmin, MySQL crashed and from that point, I just ...
0
votes
1answer
70 views

MySQL keeps crashing - Error establishing a database connection

I had installed wordpress on ubuntu 20.04 on 2GB/2CPU and I have a problem with my MySQL server that keeps crashing. Everyday we are getting below error : error establishing a database connection and ...
2
votes
1answer
30 views

Conditional aggregate on existing query

I have following table structure (I'm using MySQL 8): CREATE TABLE `listings` ( `id` bigint unsigned NOT NULL, `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `...
0
votes
2answers
39 views

MySQL Transport Protocol Used

How can I find out which Transport Protocol MySQL is using? I know how I can change it but I don't know how to show the current one.
2
votes
0answers
26 views

How to; Schema for fitness data ("Analytics") [closed]

I'm currently working in renewing my fitness synchronisation product, and with it- the database logic/structure. I'm hoping to gain some expertise in the matter and how to think when this database ...
0
votes
2answers
24 views

Removing large amounts of rows from MySQL 5.7 table + ibdata increase question

I'm working with MySQL 5.7 on Windows. I've got several tables to clean data from. Largest table's ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows ...
0
votes
1answer
26 views

MySQL tables statistics over time

I used MySQL WorkBench to get the most used tables in the MySQL database. I went to Performance > Performance Reports > Database Schema Statistics > Schema Tables Statistics and saw all ...
1
vote
1answer
33 views

Aurora MySQL: how do I keep binlogs around after reboot?

We have a request to enable binlogs on an Aurora MySQL instance and to keep those logs around for a certain amount of time. To test, we enabled binlogging with binlog_format=ROW and set our retention ...
0
votes
0answers
30 views

MySQL: Migrate MyISAM table with multicolumn primary index with autoincrement to InnoDB

I have a large (10M rows) MyISAM table with a primary index on 2 different columns where one of them is auto_increment. How can I migrate it to InnoDB in MySQL 5.7? The table is something like: CREATE ...
0
votes
1answer
48 views

How bad is to prefix table names to order them exactly as I want?

When using an interface like phpmyadmin/heidisql or others, I have this tables: account account_post account_post_status account_post_type account_status account_type Ideal for me is this: account ...
1
vote
1answer
34 views

Can I split text from one column into to two existing columns in My SQL [closed]

SELECT `Appointment`, SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 1), ' ', -1) AS Date, SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1) AS Time FROM go_applicants ...
0
votes
1answer
40 views

How to count id up in sql? [closed]

I want to create a small quiz and I have all the questions in my sql which kinda looks like this : id question 1 how long...? 2 how many...? I want to start with question with id=1 and after a ...
0
votes
2answers
75 views

MySQL 8.0 Hanging and Disconnecting At Random Times - Slow Downs - Need Help

Yesterday, I noticed in my web logs that most traffic disappeared for 2 hours. I decided to do a routine Ubuntu 20.04 upgrade yesterday that I haven't done in 1-2 months, and it seems like a lot of ...

1
3 4
5
6 7
415