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

If you continue to send queries to mysql, mysql will degrade after one minute

If you continue to send queries to mysql, mysql will degrade after one minute. I have prepared 30000 queries. All 30000 queries are identical except for the date range. During the first minute, ...
0
votes
0answers
7 views

Add data from rows to different columns if more than 1 row have a partial match

I tried to find a solution but do not find anything to add multiple columns from partially matching rows. Data:(Maximum of 3 programs per person) name surname program Jan Peters ...
0
votes
1answer
6 views

Mysql Right join with mutiple condtion not working

i have two table table1 and table2 and when right joining the table2 with table1 and i am not getting the row. here i have attached the fiddle link also CREATE TABLE `table1` ( `orderid` int(11) NOT ...
0
votes
1answer
16 views

Unknown column 'empresa' in 'where clause'

I have a problem with my Query and I have tried multiple ways to make it works and I can't wrap it around. Basically I have 2 script, the first script takes all the column in the companies on ...
0
votes
0answers
11 views

Convert rows into columns MySQL DataBase

I have two tables and I would like to generate a query relating the tables. Until now I have this query: SELECT Table1.*, table2.* FROM Table1 INNER JOIN table2 ON table2.FK_ID = Table1.ID but I ...
0
votes
3answers
22 views

What will happen when we lost connection with DB (MySQL or SQL Server) during huge insert?

Sorry, that I take your time but I worried after get this question, maybe you can help me. Question: What will happen when we lost connection with DB (MySQL or SQL Server) during huge insert. For ...
0
votes
1answer
28 views

Is this query too slow regarding the job it is doing?

It takes around 6 seconds for the following query to execute on a MacBook Air with 1,8 GHz Dual-Core Intel Core and 8 GB 1600 MHz DDR3, running mysql Ver 14.14 Distrib 5.7.27. I would like to know if ...
0
votes
1answer
19 views

MySQL Optimization on my server

I have an active server running around 80-100 active websites. I would like to optimize the server and possibly reduce the amount of RAM and optimize my MySQL settings. I have 30 GB of RAM assigned ...
0
votes
1answer
26 views

Why does this UPDATE statement no work?

UPDATE hwdb1.tblBearbeiter, (SELECT tbltmp.tmp_pnr, tbltmp.tmp_bez FROM tbltmp) AS relevant SET tblBearbeiter.b_pnr = relevant.tmp_pnr WHERE tblBearbeiter.b_pnr = 0 AND tblBearbeiter....
0
votes
0answers
7 views

lock-ddl-per-table option not working as expected in Xtrabackup

I've recently been getting the following errors on some of my Percona XtraBackup backups: InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have ...
0
votes
0answers
17 views

Delete from table one row with 2 variable with limit

I'm developing a PHP script who store session of users in a Database. When an user logout from server, remove only one row ( because same user can login more than one time ) When server reboot, ...
0
votes
1answer
23 views

Split database per pool

I need to split a database of users by X pool and each pool need to have nearly the same number of users based on their email provider... For explain to you better, if my database content: 100 users ...
-1
votes
0answers
16 views

MySQL - Which options in the configuration file have an impact on memory usage?

I've been wondering how to manage MySQL memory usage since by default it takes up to 350MB while idle on my machine, I don't have memory issues, I honestly just wondered how could it be done. I found ...
0
votes
0answers
16 views

How to prevent 'table is mark as and last (automatic) repair failed'?

This is the second time within the last 4 days that one of the tables in my database got corrupted. The is the error that I have seen in my apache log: PHP Fatal error: Uncaught exception '...
-1
votes
0answers
19 views

MySQL Get Root Password

I have created adatabase on https://db4free.net/ Now After I login. It tells me that my user has no privilages. Access denied for user 'myroot@localhost' (using password: YES) (SQL: select count(*)...
-1
votes
0answers
22 views

How does MySQL store the row data internally?

Does a RDBMS store the row data one after another in a data block? Once data block is full, is the new block assigned continuous? Per my understanding when table is created, DBMS vendor must be ...
0
votes
0answers
14 views

TiDB vs Vitess - How should I base my decision?

I need a scalable MySQL-compatable solution that can scale to multiple installs across a network, handling millions of records on several tables with tens of thousands of concurrent requests per-...
0
votes
0answers
17 views

How to punctuate an INSERT statement that uses FROM_UNIXTIME

In a Python environment, I've got the following variable: post_time_ms="1581546697000" which is a Unix-style time with milliseconds. In my table, "created_date_time" is defined as a datetime column....
0
votes
0answers
13 views

database structure for ecommerce (laravel) [closed]

I'm creating an E-Commerce website. and I'm stuck with the shopping cart and payments and orders ... I don't know how to build a reliable and safe structure. you can see my attempt in this picture: ...
0
votes
1answer
17 views

Exporting mysql query result to .txt file

I want to export query result into .txt file The path of .txt file and query is dynamic DELIMITER // create procedure test(filepath text , _query text) BEGIN SET @sql = CONCAT(_query, ' into ...
1
vote
3answers
33 views

Unique over 2 columns

I have 2 columns email1 and email2. Is it possible to define a constraint so as to be unable to add the same email twice in these columns for the same record, using only indexes?
0
votes
0answers
49 views

Slow query when matching columns from second table in JOIN with GROUP BY

I've been unable to optimize this MySQL query satisfactorily. It takes about 1.2 seconds to execute, which is surprising to me because it is a simple join with not a huge quantity of records. 🤔 If I ...
0
votes
1answer
15 views

MariaDB Can't Create New User

Fresh install of MariDB 10.4 (fedora 31). I login into mysql as root: MariaDB [mysql]> select User, Host,Password from user; +-------+-----------+-------------------------------------------+ | ...
0
votes
1answer
15 views

How to correlate data between 2 tables?

I have 2 tables that I am dealing with, table 1 has identity information (II), columns ID and Name. Table 2 has data collection (DC) information, InfoId, DCId, data, timestamp. The DC table has time ...
0
votes
0answers
33 views

What kind of complications would I face downgrading from MySQL 8 to MySQL 5?

Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter. The docs say, The only supported alternative ...
0
votes
1answer
15 views

Percona Server - binlog not automatically purge with mysqladmin flush-logs

I have a little problem and I need your help please. Here is the story : I have Percona Server in version 8.0.13 with one slave and the old binlogs on master are not deleted automatically when flush-...
-1
votes
0answers
17 views

-bash: PURGE: command not found

I want to delete binary log files following are my binary log files binary2.000001 binary2.000002 i want to delete binary2.000002 mysql> PURGE BINARY LOGS TO 'binary2.000002'; It shows me:...
0
votes
0answers
13 views

MySQL ERROR 1064 (42000) insert via bash script (RHEL)

MySQL Server: 8.0.16 Commercial OS: RHEL 7.5 I have written a simple script, to insert certain information into a database table. Following is my script # cat ~mysqldba/bin/connections.sh #!/...
-2
votes
0answers
13 views

Setting remote access permission Error

I am looking to allow another developer remote access on his machine to the database I am running. I have logged onto MySQL database via the command prompt and have entered the following command ...
1
vote
0answers
51 views

Why does MySQL take so long to execute this query on a large dataset?

Been banging my head against the wall on this one for a while now and hoping someone can help me out. The SQL query: SELECT `elements`.`id` AS `elementsId`, `elements_sites`.`id` AS `...
0
votes
2answers
28 views

create a related row if no related row exists

I have two mysql tables with a one-to-many relationship. I want to find all rows in the 'one' table which do not have a row in the 'many' table and add a single row in the 'many' table for each one. ...
-1
votes
2answers
25 views

UPDATE with too much values at IN state takes too long

I have the next query: UPDATE plates_owners SET deleted=1 WHERE plate_id=4748 AND branch_id IN (23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,42,43,46,47,48,64,79,82,83,102,103,130,169,209,...
0
votes
0answers
18 views

Most efficient way to store data for aggregation queries [closed]

Say I have a table with the columns category_id, sub_category_id, time. I want to be able to do various aggregate queries like SELECT COUNT(*) FROM table WHERE category_id IN (1, 2) AND ...
0
votes
2answers
30 views

Finding rows in a table with a foreign key pointing back at itself where the referenced row differs in a certain column

I can't formulate my question, so I will try to explain here and hopefully change the title later on. I have a table Organisations ( id, platform_id, parent_id ) Here is the tricky part: ...
-1
votes
0answers
13 views

Does a unique clustered index give array-like lookup performance?

Suppose I have a MySQL table with a unique integer index id that is auto-incremented for each row. id | PlanetName ---+----------- 0 | Mercury 1 | Venus 2 | Neptune Suppose that I am primarily ...
0
votes
0answers
18 views

Insert select - using selected value in ON DUPLICATE KEY UPDATE

In the following query, oversimplified from my actual scenario but close enough: INSERT INTO foo(bar_id, baz) SELECT id, 5 AS new_baz FROM bar ON DUPLICATE KEY UPDATE baz=new_baz I get ...
0
votes
1answer
16 views

How to restart MariaDB Galera cluster to change my.cnf

I have 3 dbs servers with mariadb galera cluster. I need few my.cnf change but each time I try to just restart with mysql restart command , replication gets affected. So can anybody let me know ...
1
vote
2answers
30 views

Mariadb Delete with Join query stuck sending data

I am using Mariadb (10.4.10-MariaDB-log) with Galera Cluster (3 Nodes). I am running a large data queries with joins , but No idea why delete with join is not working. DROP TEMPORARY TABLE IF ...
0
votes
0answers
8 views

How to take output of MySQL SHELL utility in file

I am running util.checkForServerUpgrade() function in JS mode in MySQL Shell utility. I need output of mentioned function in file instead of command-line. Here is the exact syntax which I am using: ...
-1
votes
1answer
57 views

Return a ranking points of User follow week, month, year from multiple table with mySQL

Here is the structure of my tables: User |--------|------------| | id | name | |--------|------------| | 1 | Name1 | | 2 | Name2 | | 3 | Name3 | |--------|----...
0
votes
0answers
20 views

Prevent failure in conditional insert in mysql database

The infrastructure of our system looks like this. An AWS lambda function receives requests such as (accountId, .....). It creates an entry in the MySQL database using a newly generated UUID as caseId....
1
vote
1answer
16 views

OUTER JOIN To Include Rows When No Matches MySQL

I need some help fixing my query to return data when there are no matching rows between tables. I understand the concept of outer joins to accomplish this but I am having difficulty getting it to ...
-1
votes
0answers
30 views

InnoDB temp tables filling up disk completely

So I am maintaining a mysql server, and every couple of days in irregular intervals the boot disk fills up almost completely in a matter of moments (often into the 99,X% range), before emptying soon ...
0
votes
0answers
15 views

Loading a mysql info into a droplist on a html form [closed]

So here is my conundrum: I am trying to create a form that will have a drop down list that is populated with information from a mysql data base. The code for the form file is: <!doctype html> &...
0
votes
1answer
47 views

How can I divide COUNT by 6?

So, probably spelled my question wrong, anyway. I'm trying to run a test scenario where some values are duplicated, so they should be 6 but if duplicated they are 12 or 18 and so on, Currently I'm ...
0
votes
0answers
24 views

unknown variable 'log-bin=binary.log' [closed]

I am new to mariadb server. I added the following binary log files to my.cnf: log-bin = binary.log log-bin-index = bin-log.index max_binlog_size = 10M When I view binary file with the following ...
0
votes
1answer
24 views

Select multiple columns in IF clause conditionally?

Is it possible to select different fields inside a SELECT conditionally? Pseudocode: CREATE VIE myview AS SELECT id, IF(signal_field == 1) firstname as first, lastname as last ELSE ...
0
votes
0answers
18 views

Sort by similar words?

So, I have a single column that would have different string values, is it possible to sort it based on similarities in the string? e.g. table.name would be '3d audio' , but then we would have table....
1
vote
1answer
34 views

Optimizing index performance for mysql

The mysql version that I use is : 'aurora_version','1.17.98' 'innodb_version','1.2.10' 'protocol_version','10' 'slave_type_conversions','' 'version','5.6.10-log' 'version_comment','MySQL Community ...
-1
votes
2answers
56 views

Help me to tune MySQL

I have a server that is giving me CPU spikes and and is slow in serving pages: Server information Processor: X3440 (2.53GHz Quad Core + HT) Memory Type: 8GB DDR3-1333 REG ECC Memory information ...

1 2 3 4 5 366