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.
22,091
questions
0
votes
0
answers
16
views
Mysql system tables showing incorrect cardinality for columns
I’m new to mysql. I have encountered a weird problem on our prod database. We have a table with primary key a and an index on some other b column. Below’s the query:
select b from my_table where a ...
- 348
0
votes
2
answers
34
views
Reducing the number of MySQL processes to save memory
I have just started the MySQL server on my Ubuntu machine, and I see this in htop:
There are many mysql processes, each of which takes 4.2% memory. Since I am short on memory, I would like to reduce ...
-1
votes
0
answers
26
views
Tricks or Tips to Deal with Table Metadata Lock
In our database, when I execute an alter query, I get "Waiting for table metadata lock". But we have 2,000 connections in the database. What I am doing is executing this to find long-running ...
0
votes
0
answers
10
views
mysql_install_db - How to access the MySQL after installation?
I've installed the mysql 5.6 for a poc purpose.
After install using mysql_install_db, how to access it (when not using the --random-passwords option)?
Here is what mysql_install_db says:
[root@...
- 550
0
votes
1
answer
19
views
migrate mysql dbs from old linux drive
I have an intact file system with a nonworkable old installation of linux. All the mysql pieces are there fine, I can see all the data files, but I can't figure out how to access them. I've tried a ...
- 101
0
votes
1
answer
25
views
A Table dilemma
My friend (programmer) and me (designer) are doing a relatively simple database in MySQL to store client data and the products they own for yearly maintenance' sake. The problem is that in the ...
- 1
-2
votes
2
answers
84
views
Which databases implement a difference between index scan and index seek?
Let's say I have a table:
CREATE TABLE transactions (
id NUMERIC(20, 0) PRIMARY_KEY,
account_id VARCHAR(512),
...
transaction_date TIMESTAMP
);
So with SQL Server, an index:
CREATE ...
- 169
1
vote
2
answers
110
views
AWS Aurora performance degradation after upgrade from MySQL v5.6 to 8.0
Kindly suggest which parameters to look for..
Following is the Performance Insights for our MySQL 8.0 AWS Aurora cluster..
We are consuming 3000+ queries per second and most queries return immediately....
- 111
0
votes
0
answers
30
views
Noobie needs help on designing database for membership management tool
I have searched through some of the database examples, but have yet to wrap my head around the logic of the relations between databases, so appreciate some support. Here is what I am trying to do: for ...
0
votes
0
answers
31
views
I have a log file, I want latest second row for each PC name
I have a authentication log file, I want latest second repeated row for each PC name. The log file contains timestamp,PC Name and username.
Timestamp data S is oldest and W is newest.
2nd last for ...
- 11
3
votes
2
answers
88
views
Different Percona XtraDB nodes use different indexes for the same query
We have three Percona XtraDB nodes arranged in a master-master replication topology.
One of our databases contains a heavily utilized table. This table has multiple indexes, as well as a few foreign ...
- 81
1
vote
2
answers
30
views
data with underscore in it is being selected rather than being excluded
I hope someone could help me to clarify some doubts on this question.
I have got a table with this schema:
CREATE TABLE IF NOT EXISTS `entities` (
`id` int(6) unsigned NOT NULL,
`...
1
vote
1
answer
29
views
Is there a way to identify with which DB creds a row was modified?
I am newbie to MySQL and would like to know if there is a way to identify with which DB creds a change was made to a table/row.
We have given access to multiple users like the DBA, Support Team, Live ...
0
votes
0
answers
24
views
errno: 150 "Foreign key constraint is incorrectly formed" Can someone edit this?
Hello I am new to all this could someone explain and edit why I am getting error on creating this?
CREATE TABLE IF NOT EXISTS `gangs_grade` (
`gradeId` int(11) NOT NULL AUTO_INCREMENT,
`gangId` ...
- 1
0
votes
0
answers
17
views
MySQL 8.1 does not let me login to my client after I disabled the SSL
I was facing some issues in my project due to the SSL being enabled so I followed the instructions given in other stack exchange questions to edit my my.ini file and add (# Disable SSL) skip_ssl but ...
- 1
0
votes
0
answers
45
views
How to SELECT a column that is not in GROUP BY [duplicate]
I need to select the customer's name along with the phone number.
I have tried INNER JOIN with DISTINCT SELECT, but the problem is, that a customer enters a different name each time. Thus, DISTINCT ...
- 9
0
votes
0
answers
31
views
MySQL 8.0.34 Community Server Can't open shared library '/usr/lib64/mysql/plugin/audit_log.so' undefined symbol: my_charset_utf8_general_ci)
I am a newbie on MySQL security-related things particularly, a few months ago I got to know about the MySQL audit plugin to audit the events. I did some research and I got to know that it's for the ...
0
votes
1
answer
39
views
Help with SQL Query: Is there a cost-efficient way to join multiple tables to one main table?
I have a main table set up like this:
id
table_to_join
table_item_id
1
products
123
2
products
577
3
products
344
4
products
1230
5
images
14
6
images
42
7
video
555
8
products
400
9
video
9
...
- 33
0
votes
1
answer
54
views
Is Innodb Cluster synchronous?
I was reading the InnoDB Cluster page, but I did not find any information stating whether it's synchronous or not. Is it configurable to work in either synchronous or asynchronous mode?
I currently ...
- 550
1
vote
2
answers
73
views
Slow query in a created view. How can I fix it?
I made quite a complex View on my MySQL database, and I realized that some queries are slow. My first idea was to add indexes but it's not possible to do on a view so I'm lost on how to improve the ...
- 85
0
votes
1
answer
28
views
GROUP BY with multiple counts
I'm querying an Invoice table in a MySQL 8.0.28 database that is a child to the Customer table (FK = CustomerId) and I want to be able to get multiple counts based on different criteria over the last ...
- 103
1
vote
1
answer
40
views
How to Update Columns on Replica Only
I am using MySQL 8.0.26 and I have a primary-replica setup. My goal is to automatically update a column in a replica to the current timestamp when a new row is inserted or a row is updated on the ...
0
votes
0
answers
17
views
UPDATE AND SELECT INSIDE THE SAME TRANSACTION
I have a transaction that updates a table and then does a SELECT check on that table that was changed, but the data does not follow the update made by the previous UPDATE, there is something I should ...
1
vote
1
answer
179
views
mysqld constantly shutdown when trying to upgrade from '80033' to '80034'
I left my ghost website running for a week on an ubuntu22.04 droplet on digitalocean and came back seeing mysql.service always in the status of "Server upgrade in progress".
root@...
GeefrytheBo
0
votes
1
answer
28
views
Doctor CRM database design
I am designing a doctor CRM system. Now I need to have a list of patients in the doctor panel. what I do is I find all the bookings which are related to doctors and extract user data from there. Also, ...
0
votes
2
answers
32
views
Changing int column to bigint on table with blob column
I have an aws hosted RDS production table called external_documents as defined below. It has about 35k rows and each blob is about 0.5 MB.
CREATE TABLE `external_documents` (
`id` bigint unsigned ...
- 156
0
votes
1
answer
21
views
mysql table tables_priv index is corrupted and the best possible way to repair it
I am using MySQL with XAMPP.
For sometime now I keep getting a server unavailable error and upon digging deeper I noticed that my mysql.tables_priv table index is corrupted (ARIA Database Type). I ...
1
vote
0
answers
27
views
Why doesn't mysql prompt password?
$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your ...
- 111
0
votes
1
answer
153
views
How do I change the Authentication Plugin for users mysql.sys, mysql.session, etc?
I use MySQL version 8.0.34 on a Debian server.
With the latest update I get the message that mysql_native_password is deprecated and I should rather use caching_sha2_password instead.
With my own ...
- 3
0
votes
0
answers
45
views
select * vs select column: different number of rows
Why are these two MySQL sentences returning different number of rows?
They only differ in the select.
Query #1: 4586 rows
select *
from `pedidos`
inner join `tipos_entrada` on `pedidos`.`...
- 113
0
votes
0
answers
28
views
How to alter table with ALGORITHM=INSTANT after dropping fulltext index
I have a table lets say my_table with the following fields:
id (int)
title (varchar)
content (text)
And a FULLTEXT index on title and on title + content Let's call those indexes index_text_1 and ...
- 113
1
vote
1
answer
28
views
How can I remove/replace mutiple strings using wildcard?
I have drafts in Wordpress (MySQL) that contain multiple img tags each, in the following format:
<img ... />
I want to remove all of those img tags entirely. I thought the REPLACE or ...
- 21
1
vote
0
answers
41
views
Database revision needed for habit tracking database?
I'm trying to write a clean, maintainable, and easy to query database design for a habit tracking application.
Users can have habits, which have a goal of X minutes or repetitions every Y interval, ...
- 11
1
vote
1
answer
31
views
Can't use performance_schema in my mysql server
I am using AWS RDS aurora mysql 5.7 serverless but in RDS I have enabled the performance_schema parameter, I have connected my RDS to mysql workbench version 6.3 in mysql workbench when I am checking ...
- 11
0
votes
0
answers
22
views
SQLYog/MariaDB, table cells are 'dropdown boxes', I can't edit the cell, but i can do an update query from the same user
Update: I didn't see anything in their JIRA issue tracker, so I rolled back to MariaDB 10.9 which I've been using on another server, and the issue went away. I'll definitely be posting to JIRA later. ...
- 101
0
votes
1
answer
31
views
Redirecting and converting MySQL queries to Postgres on the server side
The first thing I would like to say is that I'm not going to build a production on such an architecture. I am interested purely for the sake of experiments in home experiments and performance in the ...
- 1
1
vote
0
answers
55
views
Copy rows and change corresponding values
I have 4 tables
Tables are used to store product attributes and its values in different languages
I'm trying to copy attribute (copy in same table to new row and increment/change some values).
First ...
- 11
0
votes
1
answer
164
views
Tuning MySQL 8.0.33 - increasing innodb_buffer_pool_size not working?
I'm trying to tune a MySQL 8.0.33 on Ubuntu 22.04 (32G RAM, 8 cores). (The same machine is also a web server, with 4 apache/php-fpm virtual hosts running).
I'm not sure if this is the right thing to ...
- 1
0
votes
1
answer
37
views
Should I put the attribute in each table even if they are linked to increase performance?
Creating a schema for a workout app, currently building the logs for finished workout sessions.
Here's the part I'm wondering about:
Wondering if I should put date in each table. The reason is that I ...
0
votes
1
answer
33
views
Mysql disable master-master replication
I have two mysql server which are master-master replicated. One of them working updated without any problem. The other one gets slave replication error.
I want to disable master-master relation. How ...
0
votes
1
answer
23
views
Timestamp consistency of dependent system-version tables
I want to system-version some items that consists of data in forms of blobs as well as metadata.
I plan to use two tables, say Metadata and Data, to store them. Data has a metadata_id to refer to the ...
- 166
0
votes
1
answer
14
views
which way to increase InnoDB Buffer after more resources given
Sorry for re-treading the constant discussions of Buffer size and efficiently, we have a standalone server for a vendor system that has a database of 10GB on MySQL 8.0.28
the server itself is a bit ...
- 1
0
votes
0
answers
36
views
Trying to import large sql file in MySQL fails
I am trying to import an SQL file that is 685MB into MySQL.
First I tried it through phpmyadmin - it failed.
Then I tried through the command line with:
mysql -u root -p database-name < file.sql
...
- 101
0
votes
0
answers
10
views
Access MySQL remotely with terminal and PHPMyAdmin
First I must say that it's the first time I'm doing this kind of stuff, I'm a bit confused. I've uploaded a MySQL database to a website I bought few hours ago which I can access through PHPMyAdmin. I'...
- 1
0
votes
1
answer
30
views
What alternatives are there to generate correlative numbers instead of using primary keys or sequences into galera cluster?
I have galera cluster of three nodes (Mariadb 10.3.13). The server has a database called 'entities'. This database has a table called 'operations'. This table has the following columns:
id Int
...
- 129
0
votes
0
answers
83
views
MySQL 8.0.3 database not starting after power outage
I have a MySQL 8.0.3 database, which I can't start, after a power outage. Unfortunately I have been very stupid and made any backups (I know you are more than welcome to laugh at me).
The database is ...
- 1
0
votes
1
answer
32
views
json_merge_preserve on a column of JSON data
json_merge_preserve takes two or more JSON documents and merges them into one. However the function definition seems to only accept a fixed number of arguments.
So if I have a table like:
create table ...
- 207
0
votes
0
answers
110
views
Brew installed mysql stopped and says Can't connect to local MySQL server through socket '/tmp/mysql.sock
I believe my installation of mysql is corrupted (yes I tried reinstalling it)
when I execute brew services mysql start it starts and then stops right away
when I execute mysql_secure_installation, the ...
0
votes
2
answers
54
views
MySQL struggling to remove tmp tables
Windows Server 2016, MySQL 5.6, PHP 5.6, 64 GB of RAM.
My server is very busy, especially at certain times of the day. I get thousands of queries and sometimes hundreds of connections per second. ...
- 349
0
votes
1
answer
55
views
Algorithm of MySQL Master-Slave Replication
I've configured a master-slave structure for my database by following this tutorial. It works well and all is fine. I mean, any new change that happens in the master, will be applied to the slave in a ...
- 177