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
2 answers
60 views

How to handle variable-length arrays in a relational database

I'm creating a booking system for a shuttle service, that allows users to book shuttles for employees, specifying an origin, waypoints, and a destination. The waypoints are the aspect I'm particularly ...
-1 votes
1 answer
29 views

Help with tables normalization for delivery system

i have a task to design a SQL database to represent a "delivery". I've been able to solve most of the needs, however i have doubts with one relationship: A delivery has an origin and a ...
  • 1
0 votes
1 answer
14 views

Database specific volume

I know that mariadb "datadir" will let me move the server default volume location. That is not my question. Is it possible to create each database with its own associated volume and locate ...
  • 1
0 votes
0 answers
33 views

Unable to Recover MySQL Database [Missing FILE_CHECKPOINT]

Using MariaDB 10.7.3 via Homebrew on MacOS Monterey 12.6.1 While trying to recover the database after a system crash, I am stuck with the following situation. [Note] mysqld (server 10.7.3-MariaDB) ...
  • 1
0 votes
0 answers
16 views

edit privileges not showing

The Action column of the PhpMyAdmin is not displaying the Edit Privileges. I did not get the solution for this problem. I even tried to upgrade and degrade the PhpMyAdmin version, but nothing worked. ...
-2 votes
1 answer
20 views

what am i doing wrong?

I keeping getting this error during table creation: Order is not valid at this position expecting an identifier. I looked it up on the Web but I can't figure out how to fix this. What does the error ...
  • 1
1 vote
0 answers
27 views

WHERE IN () doesn't use index after upgrading MariaDB 10.2 to 10.6

A table with more than 650,000,000 rows does not use an index after updating MariaDB from 10.2 to 10.6 CREATE TABLE `parts` ( `mainid` int(10) UNSIGNED NOT NULL, `pid` bigint(20) UNSIGNED ...
  • 21
1 vote
1 answer
37 views

Saving output from INNER JOIN into a new table in MySQL

So I have a query in MySQL with an INNER JOIN which goes as follows: SELECT * FROM `distributors` INNER JOIN `manufacturers` WHERE distributors.manufacturer_id = manufacturers.manufacturer_id How do ...
  • 123
0 votes
0 answers
14 views

how to well replicate PhpMyAdmin well

I want to upgrade my actual server with 25 databases. My actual server with 25 databases are working full time with big data (each database has almost 5GB) and have a lot of new data each minutes and ...
  • 1
0 votes
2 answers
55 views

mysql 8.0.30 installation on Windows Server 2022 very slow, what's wrong?

We are moving from a VM running Window 2012 and MySQL 5.7 with 8G of ram and one processor. We are testing a mySQL 8.0.30 on a new Windows Server 2022 VM installation. It is a VM with 8G of ram , one ...
-3 votes
1 answer
84 views

Customer ledger

I have two tables viz., customer deposits amount - table name is "Deposits" with column names as deposit_date, deposit_amount, cust_name ; and sales to that particular customer - table name ...
1 vote
1 answer
15 views

SQL query - left join between two tables a,b to pull out data from a which doesn't exist in b since last x days

Let's say we're dealing with a simple social network where we have two tables Students Posts I'm trying to pull out data of those Students who haven't made any post in past X days - given I have a ...
  • 11
0 votes
2 answers
36 views

How to use foreign key, to remove subentries?

I try to delete all the subcategories and entries within this subentries, when i delete the main categorie from within one table. If i had multiple tables, i would use a foreign key with ON DELETE ...
  • 13
0 votes
0 answers
44 views

/etc/my.cnf conflict when trying to install mysql

I am pretty new with Linux, so please be patient :) Ran into database connection problem and decided to reinstall/update mysql Now I get the following error message: Transaction check error: file /etc/...
0 votes
0 answers
17 views

Lost connection through DDNS to MySQL server after installing Microsoft IIS on Windows Server 2012

I will try to explain the problem as clear as I can. I have a desktop application that used to connect remotely to a MySQL server (on a Windows Server machine) through a DDNS server and everything was ...
0 votes
2 answers
58 views

MYSQL - Can the difference between Charset and Collation cause any problem?

I have a Schema on a MySQL Version 8 and I should migrate it to a MariaDB Version 10.3. (with Mysqldump) The Schema has Charset = utf8mb4 and collation = utf8mb4_0900_ai_ci The MariaDB server has ...
  • 159
0 votes
0 answers
23 views

Mysql Query need to optimize

I have below query having proper indexes (all columns in where clause), But still query is very slow. Can anyone help me to optimize this mysql query? SELECT centerID, compID, ...
0 votes
0 answers
20 views

Unable to connect to sql server on Ubuntu after MySQLAlchemy connection

Context: I have installed mysql-server on Ubuntu and was able to setup password for root account and also created a few accounts with all privileges. I was able to connect to any user or the root user ...
-1 votes
1 answer
36 views

mysql.connector.errors.ProgrammingError: 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

I am working on a python project with MySQL connectivity. I have MySQL as a shell, i.e.,I need to use mysqlsh command in the Terminal to use MySQL(I am using MacOS Mojave 10.14.6). I needed to use a ...
0 votes
0 answers
27 views

is it possible to show the hidden column of table in mysql 8

I am read the implemention of MVCC of mysql 8 that told me there contains some hidden column: RowID DB_TRX_ID DB_ROLL_PTR is it possible to show the hidden column?
  • 571
0 votes
0 answers
26 views

What is the best way to migrate a multi-TB MySQL database from on-prem to gcloud Cloud SQL?

I have several multi-TB on-prem MySQL databases I need to migrate into Google Cloud's managed MySQL offering, Cloud SQL. I have migrated two of size ~1TB so far with mysqldump but this method is far ...
  • 113
0 votes
0 answers
19 views

how to mysql make sure redo log and undo log flush to disk success when update

I am reading the mysql 8.0 source code and found the mysql delay some seconds to flush log to disk innodb_flush_log_at_trx_commit=1. the source code like this: /* Flush the redo log buffer to the ...
  • 571
0 votes
1 answer
23 views

Why im getting error 1114 the table is full?

This query works fine SELECT MAX(customers.created_at) lst_trx_date, business_infos.id, business_infos.name as business_name, users.name as owner_name FROM users INNER JOIN business_infos ON ...
0 votes
1 answer
19 views

Name Conversion

I have a stored procedure in a table where column name is coming in the parameter as string - varchar I need to convert this to a column name to query in the select/insert/update as column name should ...
1 vote
2 answers
35 views

Automating MySQL replication using snapshots on AWS RDS (or Linux)

I'm currently working on a BASH script to build a new Aurora cluster based on the latest snapshot of an existing cluster, then set up MySQL replication from the old one to the new. In the past I've ...
  • 131
0 votes
1 answer
31 views

Column-wise UNION, or ordering two columns of SELECT/JOIN query with distinct orderings

I have a table, employee, and one of the columns is last_name, which stores strings. Using a SELECT query, I am trying to get the last_name column, ordered alphabetically, as one column, and another ...
  • 101
0 votes
1 answer
25 views

Using a port different than 3306 for MySql server does not work? [closed]

I installed MySQL community server and installed MySQL Server 5.7 for Windows 10. With default port 3306, I can, remotely, connect to that server. My client is a linux box. I edited the my.ini file to ...
  • 101
1 vote
1 answer
70 views

How to disable binlog_format in AWS RDS?

I created RDS on AWS and created MySQL v5.7 instance. I tried to find the binlog_format settings in parameter group but it seems to me that there are only three possible settings - like the screenshot ...
  • 115
0 votes
0 answers
13 views

how about the legacy data when add a new node in the consistent hash database

When I add a new node for the consistent hash https://www.wikiwand.com/en/Consistent_hashing database cluster(MySQL), how the legacy data migration to the new added database node? or should I ...
  • 571
0 votes
0 answers
36 views

Unable to Create Foreign Key, Error Code 1822

I am trying to create a foreign key called P_Location in table Players that references the key Location in table Locations. My code is below: CREATE TABLE Locations( id INT NOT NULL AUTO_INCREMENT ...
0 votes
0 answers
15 views

Feedback on foodplatform-order schema

I'm making a platform similar to Just Eat, Takeaway or Uber Eats. On the platform, different restaurants can list their menu and users can order items from the restaurant's menu. I have made a sketch ...
  • 103
0 votes
1 answer
52 views

mysql slow on simple query

I have a simple query select * from table1 limit 5 on a large table but it stucks at "sending data". There is only one connection. Here's the describe query: +----+-------------+-------------...
  • 13
-1 votes
1 answer
34 views

Is one large innodb buffer better and multiple smaller ones?

I have a MySQL Server with roughly 1.8TB of data and 128GB of installed RAM. Would I be better off with one large innodb_buffer_pool that's say 80% of available RAM or would say 16 that are 8GB each ...
user avatar
0 votes
1 answer
48 views

How to group by week/month/year and time frame 8hr/11hr/14hr/17hr/20hr in MySQL

I need to group 2 tables (Key = analysis_id <=> id) where my output should show the last week or month or year, grouped by a Time range. I have around 4000 inputs from users. Example of tables ...
  • 27
0 votes
0 answers
27 views

MYSQL migration and Backup automation in windows scheduler from mssql

I am planning to migrate databases from MSSQL to MYSQL. The total number of databases is 40 all together will be 60 GB in size running on express. Would like to get your suggestion for migration to ...
3 votes
1 answer
206 views

Valid for WITH clause to use existing table name?

Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
  • 133
0 votes
0 answers
22 views

Joining on a column where the value can be NULL in MySQL

I'm trying to unary join a table using an ID which can be NULL (here it's MID). Here's my code so far (names shortened for convenience): SELECT s1.SName, s2.SName FROM S s1 JOIN S s2 ON s1.MID = s2....
  • 1
2 votes
1 answer
64 views

Select rank of single row

CREATE TABLE Player ( id BIGINT NOT NULL PRIMARY KEY, xp BIGINT NOT NULL ); INSERT INTO Player (id, xp) VALUES (1, 100); INSERT INTO Player (id, xp) VALUES (2, 150); INSERT INTO Player (id, xp) ...
  • 123
0 votes
1 answer
53 views

Exporting FoxPro DBF Files

I am attempting to export data stored by a client's Visual FoxPro database to be used in a MySQL context. The data given is a folder of .dbf, .cdx, and .tbk files, as well as a few .dct, .dcx, and ....
  • 101
0 votes
2 answers
39 views

Terrible performance on WP-generated query migrating from MySQL to MariaDB

I'm trying to migrate a website from a WP-focused hosting provider that uses Percona for their DB node to a Jelastic-based provider that offers MariaDB for its DB nodes. There's one query in ...
0 votes
0 answers
72 views

Can't connect to Dockerized MariaDB ColumnStore database from host machine

I have setup a MariaDB ColumnStore Database as described in the MariaDB ColumnStore Quick Start guide. The guide is missing some steps so these are the exact steps I took: docker run -d -p 3306:3306 --...
0 votes
1 answer
55 views

Error 1236 in MySQL GTID Slave due to mistaken 'reset master' on MySQL 5.7 GTID master

Sequence of events: Disabled binary logs in MySQL 8 slave with the following relevant parameters in mysqld.cnf: log-replica-updates = OFF gtid-mode = ON enforce-gtid-consistency = ON master-info-...
0 votes
0 answers
10 views

Need to Add new parameter to Working stored procedure in MYSQL

This SP is used in front end it has 7 parameters need to add one more parameter(as i have done) so that call proc_123(2018-01-10, 2022-10-07 ,-1, 1, 50,-1 ,-1,'airport');
  • 1
0 votes
1 answer
25 views

Normalizing an existing table with strings data

I have a table that has two string columns that repeat a lot, and in the next stage, some counts and conditions need to be done, lets say they are called company_name and person_name. The table has ...
0 votes
1 answer
34 views

Why cannot I reference the second alias from a subquery containing a UNION?

Why cannot I use Qty2 as an alias in my query? I receive this error: Error Code: 1054. Unknown column 'qty2' in 'field list' SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty ...
0 votes
1 answer
73 views

How to find out what kind of database( SQL vs No-SQL) is better to use for a specific service/server? [closed]

I am trying to learn microservices and write a project based on microservices, but I don't know how to choose the right database for each microservice? Based on my researches MySQL and PostgreSQL are ...
  • 109
0 votes
1 answer
32 views

MySQL DB Connection Issue

I am trying to connect to mysql db using Airflow MySqlOperator operator. It is giving an error: MySQLdb._exceptions.OperationalError: (1045, "Access denied for user 'username'@'hostname1' The ...
  • 1
1 vote
2 answers
94 views

Select all orders which have the same order items

I need to fetch all orders which have the same cart (same list of order items and quantity). Here are my tables: orders (I think columns are not important, the primary key is id) | id | | -- | | 1 | |...
0 votes
0 answers
46 views

I want to return a different query instead of null

SELECT sc.id AS cargo_id, sc.NAME AS cargo_name, cp.size AS package_name, cp.min AS min_range, cp.max AS max_range, cpp.price AS price, sc.contract FROM ...
0 votes
2 answers
30 views

Determine new/changed data(rows) on a table during a table clone operation

I'm trying to alter some heavy data tables tables, this operation may cause some impact on our production service, so to minimize outages the plan is: create a copy table that inherits table ...