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
0 answers
35 views

MySQL 8 host_cache returning wrong HOST for IP address

We're trying to troubleshoot a number of issues with the host_cache on a MySQL 8 server The issues are all tied to a recent update where we tightened our user "From Host" definitions from @&...
Dan Kelly's user avatar
  • 181
0 votes
0 answers
115 views

Moving from MySQL to MariaDB, slower performance with some query's

recenty we've have moved all of our server to new hardware and to MariaDB . I've installed a new server with VirtualMin for managing all of our company's applications. at a first glance most of the ...
Stefx's user avatar
  • 9
1 vote
1 answer
66 views

MySQL and PostgreSQL database table schema comparison

Is there any direct method to compare tables between MySQL and PostgreSQL.Comparing should include field name, types, default values, etc; As an example below, Below is a dump of PostgreSQL ...
chinthakaS's user avatar
0 votes
0 answers
49 views

Replication between two galera clusters

I currently have two 3 node mariadb (10.5.9) Galera clusters set up in different locations, I'm using standard replication to copy data between the primary and secondary. What I've recently noticed is ...
imbrian21's user avatar
1 vote
2 answers
51 views

Why does MySQL puts ANALYZE queries into `System Lock` state, when there is intense I/O

On MySQL 8.0.33, I'm experiencing a form of contention (reproduced on multiple instances) that is not explained by the documentation. As example: I boot an instance on a console client, I invoke a ...
Marcus's user avatar
  • 378
2 votes
1 answer
62 views

how percona hot backup works / innodb crash recovery works?

I am trying to understand the process of how percona does hot backup. from what in understand is, they use innodb crash recovery mechanism and execute the following steps. store/remember start LSN ...
Rajat Aggarwal's user avatar
0 votes
1 answer
94 views

Why does MySQL need double-write buffer if there is redo log and undo log?

Why does MySQL need double-write buffer if there is redo log and undo log? I read other posts that answer why MySQL needs double write buffer. They point to this article. http://dimitrik.free.fr/blog/...
user855's user avatar
  • 97
0 votes
1 answer
80 views

Database design, one to one relation split into multiple tables

Let's assume we have following database: Or in pseudo sql format (all columns are not null): create table road ( id int PK ) create table item_on_road ( id int PK road_id int FK road.id ...
ZiiMakc's user avatar
  • 125
-2 votes
1 answer
109 views

How to design database system of an online toy shop?

Hi i'm designing a database for a toy shop this shop offer many products with different categories. This is the project question. Can you please help me which tables and relationships should I add in ...
Galaxy's user avatar
  • 11
2 votes
1 answer
81 views

Insert gives error code 1366 even though charset is utf8mb4

I am trying to manually copy a table from one server to another. I used mysqldump to export the table. The import fails with the following error: ERROR 1366 (22007) at line 6: Incorrect string value: '...
Vongo's user avatar
  • 171
0 votes
1 answer
35 views

mysqldump on a 35GB table to a server in another region

I'm in the process of potentially migrating my entire website from a dedicated physical server located in Ireland to a DigitalOcean server located in America (New York). We'll also be moving from ...
GenesisBits's user avatar
0 votes
1 answer
41 views

Seeking Guidance on Migrating MySQL/MariaDB Data to Supabase

I find myself facing a challenge, and I'm reaching out for some guidance. My current project involves a rebuild for a client, and we're considering adopting the Supabase stack. However, our existing ...
Mathew Agustin Bella's user avatar
0 votes
0 answers
74 views

Upgrade from MySQL 5.6 and 5.7 to MariaDB

We have several servers with MySQL 5.6.51 and several with MySQL 5.7.39. What are the right steps to upgrade to latest MariaDB LTS 10.11? I think we can't go directly to 10.11 or? MySQL 5.6.51 > ...
lear1's user avatar
  • 69
0 votes
0 answers
4 views

how to solve problem of that IO thread in slave cannot read the correct position in master in mariadb 10.3 ? I tried to use GTID_based replication

I have a problem when using new style GTID_based replication on mariadb 10.3 in case of multi master and slaves replication ..I cannot use auto positioning in case of the IO thread in slave cannot ...
user avatar
0 votes
2 answers
106 views

Does the logical query processing change when indexes are involved?

I highly recommend this book. This book explains in a great way the behavior of a sql query, but my doubt is, How is a sql query processed when there are indexes, that is, would this behavior change? ...
jwa's user avatar
  • 1
1 vote
0 answers
50 views

MySQL crashed and process in zombie state

MySQL server crashed and was in a zombie state. Attempted to send kill -s SIGCHLD to the parent process but failed. Killing the parent was not an option since it was the init process and I was forced ...
supmethods's user avatar
-1 votes
3 answers
99 views

Should i care about field names in JSON datatypes?

In DBMSes like MySQL, MariaDB, PostgreSQL and MongoDB, data is saved as BSON, JSON or JSONB for JSON columns or document. All of these data types use space for key names in JSON. Does internal data on ...
Ehsan Chavoshi's user avatar
0 votes
2 answers
79 views

mariadb table partition and index tables

I have been reading about the table partitioning in mariadb. I know it will not give any direct performance gains. But what about the index tables? Are they also partitioned? Let's say a table is ...
clarkk's user avatar
  • 147
0 votes
1 answer
44 views

Two queries to retrieve user profile and tweets, or join and get user data from first column?

Let's say I have two tables: users and tweets. I want to display a full user profile, and latest tweets by id. Is it better to fetch them in two separate, possibly concurrent/parallel calls? in JS it ...
wefad12292's user avatar
0 votes
2 answers
41 views

What sql statements is mysqldump executing and how does it affect database performance?

I noticed a SELECT * FROM query running on a big table from time to time and i wonder if this could be, because of mysqldump. We have mysqldump backups running for some tables. Does somebody know what ...
Lorem ipsum's user avatar
1 vote
1 answer
43 views

Is there any difference between install MySQL using Generic Binaries or from source (compiling it)

What is the difference between installing MySQL from generic binaries and from source (compiling it in the kernel)? Are there any differences in a productive environment?
Astora's user avatar
  • 703
0 votes
1 answer
35 views

OS Upgrade Path for DB and Application

Are there any best practices which define which Operating System Upgrade Path we should choose and any rational behind it? Path 1: Upgrade Database OS then Upgrade Application OS. Path 2: Upgrade ...
Veeru Sharma's user avatar
1 vote
1 answer
56 views

MySQL - Slow Performing Subquery when DISTINCT Is Used

I have this query that runs very slowly when DISTINCT is used; SELECT COUNT(*) FROM (SELECT DISTINCT query_text FROM search_query WHERE (store_id IN (1)) AND (num_results > 0) ORDER BY popularity ...
Marisa's user avatar
  • 23
-1 votes
1 answer
273 views

How do i fix "InnoDB Plugin initialization aborted with error Generic Error"

i am running a Nextcloud Server on a Ubuntu 20.04 which was running fine. I wanted to upgrade the Nextcloud Version and make a backup before. This is what i did: plugged in a external harddrive (/dev/...
user avatar
0 votes
0 answers
55 views

Simple update query slow in php, quick in phpmyadmin

I have a very interesting issue. A super simple update takes about 400 ms. The website server waiting (TTFB) is usually around 150 ms, but with this update included it becomes 600 ms. mysqli_query($...
Phoenixy's user avatar
0 votes
0 answers
16 views

Does AWS Aurora MySQL Load Balance Reads Across Availability Zones?

I am running an AWS Aurora MySQL multi-AZ cluster with four nodes in two availability zone reader A and writer C are in one AZ, and reader B and D are in another AZ. It seems like reader A is ...
db_guy's user avatar
  • 1
2 votes
1 answer
40 views

Lost Root privelages in MariaDB 10.6

So I can't even remember how this happened, I believe I was worried about how running mysql -u root Was allowing the root user to login without a password or even asking for one... So I went down a ...
JohnDoe's user avatar
  • 31
0 votes
0 answers
70 views

MariaDB locking

I have a table W with a primary key, and a table T with a status column and an FK to W. I am observing some random locking which I don't know where it originated: T1 does the following: select where ...
Miguel Ping's user avatar
0 votes
1 answer
41 views

How would you implement Rollback when you need to make sure that 2 updates and 2 inserts either all happen or are all rolled back?

I have a MySQL stored procedure. Cut down, it looks like this. START TRANSACTION SELECT some_columns SET some_variables UPDATE row_in_balance_table UPDATE row_in_entry_table INSERT ...
T M's user avatar
  • 11
1 vote
0 answers
41 views

MariaDB, MySQL - aggregated count in group

Suppose there is a table: first second third 1 1 1 1 1 1 1 2 2 1 1 2 2 3 1 And by query: SELECT first, count(*) AS cnt FROM table GROUP BY first We get result: first cnt 1 4 2 1 How to ...
holden321's user avatar
  • 111
1 vote
1 answer
51 views

Will Dropping a corrupt table free up space in MySQL

We have a MyISAM Table in our database which has around 97 Gb worth of data with resulted in 95% disk utilization. Due to this we started clearing the data from the table by deleting the rows but this ...
Umang Pachaury's user avatar
0 votes
0 answers
38 views

Keep both non-overlapping intervals and overlapping ones with a larger id MySQL

I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique....
Alex.Kh's user avatar
  • 101
0 votes
0 answers
49 views

Uncaught mysqli_sql_exception: Data too long for column 'Data' at row 1

When I try to enter some very long data, (response from ChatGPT API) it doesn't get saved into sessions data which Im handling manually by storing in a MySQL table. $_SESSION[$propertyName."-&...
anjanesh's user avatar
  • 279
0 votes
0 answers
87 views

mysqldump on replica for backup and GTID

I use replica instead source database for backups. After enabling GTID mode I am not sure how I should use mysqldump to make correct dump. This backup is for source recovery of course. mysqldump --...
deem's user avatar
  • 113
0 votes
1 answer
26 views

MySQL Data not flushing to disk

Lets start with what I did... I copied a table via PHPmyadmin operations/ copy table/(temp1) structure and data. nothing should make any changes to this copy.phpmyadmin shows 1185199 entries I copy ...
tek-dragon's user avatar
0 votes
0 answers
78 views

Master-Slave replication in mysql error connecting to master 'replicator@xxx:3306' Can't connect to server on 'xxx' (111 "Connection refused")

Hi i am trying to make a connection between my Primary Server and backup server to have Master-slave mysql connection this is my Primary server ip = xxx and this is my Backup server ip = yyy both of ...
Matthew's user avatar
0 votes
0 answers
354 views

MySQL80 service starts and stops automatically on Windows: How to troubleshoot?

I'm encountering an issue with the MySQL80 service on my Windows machine. The service starts but then stops automatically shortly after, displaying an error message indicating that it stops if not in ...
user avatar
0 votes
0 answers
72 views

Getting duplicated primary key entry in mysqldump file

We have a mysql 5.1.73 server and a myisam table (we're migrating to later version of mysql and innodb as part of this eventually). We do a mysqldump every night of a table. Basic table structure is ...
Ian's user avatar
  • 135
0 votes
0 answers
10 views

ERD Design Question

I am currently working on my database module assignment and have completed the initial design of my ERD (Entity-Relationship Diagram). I would greatly appreciate your insights and feedback on the ...
Hamidreza Maleki's user avatar
3 votes
3 answers
255 views

Do you need to explicitly include the primary key in a composite index?

I recently read that when creating a composite index in MariaDB that the primary key is appended to the end of the index "under the covers". I access my data many times in order by these ...
scott8035's user avatar
  • 131
3 votes
2 answers
111 views

MySQL chooses a slow execution plan for selecting rows by range condition with ORDER BY, LIMIT and OFFSET clauses

I have a table "client_data" with 2 indexes: CLIENT_USER_CREATED (CLIENT, USER, CREATED) CLIENT_CREATED (CLIENT, CREATED) The primary key is (ID, CLIENT, CREATED) The table is partitioned ...
Vladimir Yanchenko's user avatar
1 vote
0 answers
23 views

Column 0 of table 'sitemanager.b_agent' cannot be converted from type 'bigint' to type 'int(18)'

I have a Percona MySQL server with 1 master & 1 slave I'm having this kind of issue on my slave part Slave_IO_State: Waiting for master to send event Master_Host: ...
Site Admin's user avatar
0 votes
2 answers
52 views

Find all C.id where none of the rows of B have is_true='Y'?

There are two tables C and B. They're connected by C.id=B.cid. I need to find all the C.id es where: None of the rows corresponding to cid has is_true='Y' I've tried various queries but can't wrap ...
barnyard9's user avatar
3 votes
1 answer
369 views

Explanation of Mysql SSL connection for users created with "REQUIRE SSL"

I'm trying to understand the working of SSL while trying to connect to a MySQL server (via mysql-connector-python). I have MySQL (v5.7) and I have 2 users. One (root) is the default user and the other ...
Jim isaac's user avatar
  • 133
0 votes
0 answers
65 views

Having the same charset and collation between Redshift and MySQL

I looking to migrate data between Redshift table and MySQL that has a string column. There are a few differences between each engine: Redshift is case sensitive, and by default MySQL isn't. MySQL, ...
Nir's user avatar
  • 513
1 vote
1 answer
61 views

Alternatives to MySQL Utilities

Are there alternatives to these MySQL utilities? These look valuable but then are on Python 2.7 which I cannot even run now. I think it is related to this which is already archived.
Brian Estrada's user avatar
1 vote
1 answer
29 views

Modeling a database for business that require several roles and privileges

Part I I have a database model with members and organizations with each organization has many members, so it is clear that is a one-to-many relationship. The difficulty is that among those members ...
AmineBinOthman's user avatar
0 votes
0 answers
16 views

How to migrate from a MySQL master-master setup to a master-master setup with as little as possible downtime

I have to conduct a migration of a few databases from two MySQL VMs with master-master replication to another 2 VMS that would be also master-master replication. How would you do it with minimal ...
Zexxxx's user avatar
  • 1
0 votes
0 answers
47 views

ALTER USER Authentication Options in MySQL

According to MySQL documentation on ALTER USER statement (https://dev.mysql.com/doc/refman/8.0/en/alter-user.html), I want to try the following statement copied from that page (changed the host to '%' ...
techfan's user avatar
0 votes
0 answers
56 views

Mysql: Compare 2 types of JSON data ["a", "b", "c"] vs {“a”: true, “b”: true, "c":true}

I want to create a field like unordered_set (unique elements) in C++. In mysql, I use JSON field, there are 2 ways to do: tags = [“a”, “b”, “c”, …], application layer will ensure about uniqueness. ...
kietheros's user avatar
  • 145

1
3 4
5
6 7
447