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
votes
1answer
13 views

MySQL Upgrade Procedure in a production environment

Am new to the role and employers have asked me to come up with a plan to upgrade the MySQL 5.6 on the production server to 5.7. It has partitions and MASTER-MASTER replication setup and has around ...
0
votes
1answer
18 views

How to migrate a MySQL server database from an older version to a newer?

I am trying to import a database from an older MySQL server (v 5.0) to a newer (8.0). So I exported the dump file from the original database, and tried to import in the newer system, but then it's ...
0
votes
0answers
20 views

MySql service won't start - Ubuntu

I hope someone can help me. I tried to run mysql and got an error. Does anyone know how to fix this? I use ubuntu 20.04 Try to start mysql `senad@senad-X540SC:/$ sudo service mysql start [sudo] ...
0
votes
1answer
10 views

Is a connection from my SSL-secured server to an RDS instance automatically encrypted when using mysqli_connect?

I'm trying to determine if I need to use mysqli_ssl_set to encrypt my db layer. I have a standard Amazon RDS instance that I'm connecting to with MySQL user and pass, but I'm unsure if that connection ...
0
votes
0answers
12 views

Corrupted ibdata1 and can't run server

I am running a local dev environment on OSX. I restarted the machine earlier, and since then, I cannot load mysql server. I am running mysql 8.0.23 I get the following message when I try to load ...
-2
votes
1answer
13 views

MYSQL Error Code: 1442. Can't update table in stored function/trigger because it is already used by statement which invoked

DELIMITER $$ CREATE trigger Tr_Trip_insert AFTER insert on map_vehicle_person for each row begin declare master_trip_id varchar(30); set master_trip_id = (select DATE_FORMAT(sysdate(),'%Y%m%d%h%m%s'))...
0
votes
0answers
22 views

Letsencrpyt components for MySQL?

So, these are the three pieces MySQL looks for in the .cnf file: [mysqld] ssl_ca=ca.pem ssl_cert=server-cert.pem ssl_key=server-key.pem And I'm using Letsencrypt for SSL cert management. How do I ...
0
votes
1answer
15 views

Sync tables with selections

I have a problem that I don't know how I could solve it. I have two tables in two different databases on two servers. Server 1 (Master) has the data and server 2 is the slave. But Server 2 only needs ...
0
votes
2answers
25 views

GRANT command not working on MySQL 8.0.25

I'm attempting to grant a user privileges within a database, and I'm receiving the error #1410 - You are not allowed to create a user with GRANT I'm executing the following: GRANT ALL PRIVILEGES ON `...
1
vote
1answer
28 views

UPDATE SET N + 1 equal to a row (with iteration)

Question: How would you iterate over the previous result (referenced below) over for each of the values in column b3? MariaDB Fiddle (this includes prior fiddle information to solve the previous ...
0
votes
1answer
25 views

Can I use assignment operator `:=` on a local variable in a SELECT statement?

From my understanding the only difference between user variables @var and local variables like var is the latter are scoped between the BEGIN and END statements of a procedure whereas user variables ...
3
votes
2answers
49 views

UPDATE SET N + 1 equal to a row

Problem: I need help implementing N+1 WHEN a2 = "Z" then check if the next row number (a1 + 1) has a2 = "Z" ...if this is the case THEN replace the (a1+1) row --> a2 = "Y&...
0
votes
0answers
12 views

MySQL Table structure and data export - Script sequence is not correct

When I export the data and structure of my MySQL database to a file, I notice that the table sequences in the output file are not correct. For example the script of a child table is put before the ...
0
votes
2answers
12 views

MySQL query cache stats - why Qcache_lowmem_prunes is high?

I have the following query cache settings query_cache_type = 1 query_cache_limit = 1M query_cache_size = 32M After running for one full day, I have the following stats mysql> show global status ...
0
votes
1answer
19 views

Finding rows where totals do not match

MariaDB MySQL Trying to do what seems like a basic query (join) on two tables. Receipts id date supplier total Parts id receipt_id partType subtotal Trying to get the row from the first table when ...
0
votes
0answers
27 views

Losing a connection during query execution [migrated]

Suppose this situation: I have an insert query which has an auto-increment key Insert into xxx Values(0, 1) What will happen if I call mysql_query(&sql, szQuery) but the connection to the server ...
2
votes
1answer
46 views

Improve MySql query performance in large table

I have a MySql database that is used to store events from an app, since we created it, we only inserted and selected data, we've never deleted any rows. I'm not a DB admin and there's no DB admin in ...
1
vote
0answers
17 views

Mariadb malloc configuration- tcmalloc vs tcmalloc_minimal

I am using mariadb 10.4. Initial sysbench test showing promising result with tcmalloc. But much better result(TPS & Latency) is obtained using tcmalloc-minimal which basically removes heap checker ...
-1
votes
0answers
30 views

hierarchical data structure with MySQL [closed]

is they'r a way to order this table : | EMPNO | MGR | ***************** |7839 |NULL | |7782 |7839 | |7521 |7839 | |7698 |7698 | *************** to be like this: | EMPNO | ...
0
votes
2answers
17 views

Join the SAME tables from DIFFERENT databases

I have an unusual situation where I have a bunch of backups of a mysql database and I am interested in selecting the distinct members of a given table from these backups (since some records might have ...
0
votes
1answer
24 views

Get the next datetime value

I have a MySQL table: SELECT sh.id, sh.DG_ID, sh.START_DATE FROM STATUS_HISTORIES sh order by DG_ID, START_DATE id DG_ID START_DATE 22 212 2021-04-20 14:59:58 66 212 2021-04-21 05:...
0
votes
1answer
20 views

Renaming all occurances of an HTML class name inside a table with SQL

In a MYSQL database of a CMS all occurances of a specific HTML class name should be pre-fixed. Let's say the table of interest is table_article and the HTML code inside this table is like this: <...
0
votes
3answers
34 views

Force MySQL index on OR query?

I have a query that could be resolved 99% of the time using an index but needs a table scan for a few queries: SELECT * FROM mytable WHERE idx=123 OR name like "%foo%" limit 1 Logically ...
0
votes
0answers
11 views

Variable string input in WHERE clause? [closed]

I'm trying to generalize a SQL command by putting it into a function and having the WHERE clause be a string variable that can be inputted by a user. This is from a Kaggle SQL tutorial, based on the ...
1
vote
1answer
33 views

Mysql showing 100% CPU usage

We are facing problem of high cpu usage for mysql process (almost 100%). Here is the information related to server Server Infos: VPS - CENTOS 7.9 kvm - 6 GB RAM - 4 Core CPU - 180 GB SSD - MariaDB And ...
0
votes
0answers
21 views

Storing User Information in DB [closed]

Currently, we have Mysql User Data with around 20 million entries in our DB. We are not using any sharding mechanism. This table is used for all purposes like login, and various other queries based on ...
0
votes
1answer
18 views

MySql Replication: Access Denied

I am trying to migrate between cloud providers, using MySql Replication to copy data across live so we can switch with no downtime. Current setup: 2x Windows servers with old provider, with MySql 8 ...
0
votes
1answer
21 views

How can I delete latest 2 binlog files from RDS MYSQL Aurora

I have created a snapshot of Aurora MYSQL database and then restore it. Let's call it clone Here is the output of the command SHOW BINARY LOGS on the master database Here is the output of the command ...
-3
votes
0answers
22 views

can this query considerably well in performance? [closed]

Those select statement are good with GROUP_CONCAT or can this be better improvised like in what areas. I am new to writing queries. Thanks in advance. SELECT c.*, o.orderId, concat('[', ...
0
votes
1answer
27 views

How do I correct InnoDB Multiple files found for the same tablespace ID?

Some time ago after an update of Ubuntu 20.10, I discovered that MySQL was not running. When I tried to restart it, the error message suggested that it had started, failed and stopped. A check of the ...
0
votes
1answer
13 views

How to alter AWS RDS tables in `mysql` schema?

I want to alter tables like mysql.rds_sysinfo, mysql.rds_monitor, mysql.rds_heartbeat2 which belong to mysql schema. The alter sql for that is: ALTER TABLE mysql.rds_sysinfo ROW_FORMAT=DYNAMIC; But ...
-2
votes
0answers
16 views

Compromised mysql installation [closed]

I just started learning mysql so I'm sorry for my ignorance. I've already written some queries and everything has gone well. After rebooting my system, everytime I try starting the server the ...
-4
votes
0answers
36 views

duplicate values with left join [closed]

I am getting more rows then I expect returned by my query. I believe it has something to do with my join statements. There are multiple tables that have different information in them from SAGE X3 ...
0
votes
2answers
36 views

Does changing the character set from utf8 to ascii improve consumed space of a CHAR field on mysql?

I have the following table: CREATE TABLE `tokens` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `s_id` int(10) unsigned NOT NULL, `a_token` char(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci ...
2
votes
1answer
175 views

Why am I not able to add a user?

I'm running command grant all privileges on *.* to 'username'@localhost identified by 'strong password';, but I get the error: ERROR 1064 (42000): You have an error in your SQL syntax; check the ...
1
vote
0answers
38 views

Is it possible to get a collection (array or object) from a SQL query containing a set of filters?

I'm used to other technos, I want to retrieve some info from a database, so to give the context: I have a "plaformname" column which is the name of each platform, a "populationname"...
0
votes
0answers
29 views

MySQL query is slower after index create [migrated]

At first i will write some information about my test table. This is books table with 665647 rows of data. Below you can see how it looks. I made 10 same queries for books with price equal select * ...
0
votes
0answers
11 views

php mysql create an entry into a field. (INSERT INTO) WHILE logged in currentuser/session [closed]

I did the php registration system and it works https://www.tutorialrepublic.com/php-tutorial/php-mysql-login-system.php but i created extra fields in the DB so the user can add some data to the ...
0
votes
1answer
20 views

Unable to sort Parent child hierarchy [closed]

Tried help from another post on StackOverflow. But somehow I am not getting the expected result. I would be very grateful if anyone could help. SELECT IF(child = '',parent,CONCAT('->',child)) as ...
2
votes
1answer
94 views

Can not drop virtual column | ERROR 1054 (42S22): Unknown column in 'GENERATED ALWAYS'

I'm just running a command: optimize table some_table_name_here; But it results with: Table | Op | Msg_type | Msg_text db.some_table_name_here | optimize | note | Table does not support optimize, ...
0
votes
2answers
26 views

MySQL Heap Table

I have learnt that table can be created with order, like adding primary key. On the other hand, heap table is unordered, as mentioned in this page: https://www.sqlshack.com/clustered-index-vs-heap. ...
0
votes
0answers
38 views

Insert into table with polymorphic association in database

I want to design a database where some tables have multiple foreign keys. I am looking for the best design approach. I found out that this kind of problem is called "polymorphic association" ...
0
votes
1answer
20 views

Update table column based on another table values

I have two tables: Table 1: ID TICKET_AGENT ORIGINAL_ID 1 0 5 2 0 3 3 0 1 4 0 2 And, Table 2: ID ...
-1
votes
0answers
33 views

converting from oracle to mysql [closed]

please i want to convert this codes from oracle to mysql (phpmyadmin) using only ( clauses like select, where, group by, having, order by , union, joint ) SELECT LEVEL, EMPNO, ENAME, JOB, MGR FROM ...
-2
votes
0answers
26 views

i want to answer some request using mysql (phpmyadmin) [closed]

please i want to answer on some requests using mysql My questions are : i want: 1_ the Hierarchical structure of employees (NAME, JOB, EMPNO, MGR). The employee at the top of the hierarchy does not ...
0
votes
1answer
18 views

MySQL Sharding with org id

I have a multi tenant application and each tenant will have an orgId. So, all tables will have orgid column. Each org will have clients, projects, ... Tables org client project Now, I am trying to ...
0
votes
0answers
9 views

MySQL on Ubuntu refusing to start/restart, missing socket? [closed]

We had an issue with a server where we ran out of disk space. One of my developers upped the size of our server on digital ocean, and we did the following: Powered off the server via SSH Started the ...
0
votes
0answers
23 views

why inner time effect the filter result in MySQL 5.7

Now I am store time using unix timestamp, I am filter the data using this sql: -- range of day but filter a minite select *,date_format(FROM_UNIXTIME(consume_time/1000), '%Y-%m-%d %h:%i') from ...
1
vote
1answer
44 views

Table is full even with innodb_file_per_table

I am trying to create an index to my table using alter query. My my.cnf file innodb_data_home_dir = /usr/local/mysql5/data innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
-2
votes
1answer
30 views

Running the loop stops after changing the Counter Variable [closed]

When I run the following code, it always returns a NULL value for the two variables inside the loop. SET @c = ''; SET @i = 4; REPEAT SET @c = CONCAT( ' -> ', ( SELECT ...

1
2 3 4 5
408