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
3answers
43 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
30 views

Why does this UPDATE statement no work? [closed]

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
15 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
19 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
2answers
30 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
1answer
20 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
1answer
23 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
24 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
1answer
25 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
17 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
18 views

How to punctuate an INSERT statement that uses FROM_UNIXTIME [migrated]

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
15 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
21 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
4answers
46 views

Unique over 2 columns

I have 2 columns col1 and col2 INITIAL QUESTION: is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes? The Question has been edited ...
0
votes
1answer
56 views

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

Update: so, as mentioned in the comments thread, it seems the ORDER BY might be the most direct cause of slow execution. So, the question should be: how can this query use ORDER BY score and execute ...
0
votes
1answer
20 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
16 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 ...
2
votes
0answers
69 views
+250

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
20 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
1answer
18 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
1answer
20 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
14 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 ...
2
votes
1answer
67 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
33 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
26 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
19 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
32 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: ...
0
votes
1answer
21 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
21 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
19 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
38 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
10 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
58 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
1answer
21 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
17 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
31 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
1answer
49 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
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
19 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
49 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
62 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 ...
0
votes
0answers
17 views

Finding similar/complimentary values

I'm trying to find similar values for a single column. I have a column 'name' and I've used a query to find duplicates, however I'm fairly certain that there are similar...entries? Not duplicates per ...
0
votes
1answer
19 views

select table and replace column fields by value from multiple tables in mysql

I am not more familiar with MySQL.i have been working in mssql. currently, we have a requirement to select table column fields replaced by values from multiple tables. I tried out with some queries ...
0
votes
0answers
13 views

PHPMyAdmin #2002 error - fixed by restarting mysql but it occurs again

I have a LAMP server running MySQL and PHPMyAdmin. Yesterday I got the following error trying to login to PHPMyAdmin: #2002 - No such file or directoryThe server is not responding (or the local ...
0
votes
1answer
25 views

Users Export From Mysql Version 8.0 to 5.7

I want to exports all users from mysql version 8.0 to 5.7. Can you guide me for this activity. I want same password and privs should migrate.
0
votes
1answer
32 views

How to efficently handle large amounts of insert statements

I have a file containing a large amount of insert statements roughly 30 GB worth of them. The insert statements themselves are rather simple and contain 5 different values. I now have the requirement ...
-2
votes
0answers
33 views

Need DB design for 15*100 matrix data [duplicate]

The data is all Boolean and I need to retrieve a sub set of the 15 which are true(1) for any number of given user inputs from the 100. Also the 100 becomes 101 as frequently as 3-4 times a year.For ...
0
votes
2answers
42 views

Handling large DB tables that have millions of records

So I have a large table(Table1), with about 5 million records. I have a second table(Table2) with about 108 million records. I am using a key value from Table1 to find any matching records in Table 2( ...
0
votes
1answer
47 views

How to daily copy selected data from MySQL and PostgreSQL servers to another PostgreSQL server

I have two source servers, a MySQL 5.7 and a PostgreSQL 9.6, respectively. I would like to daily update some tables from each source to another target PostgreSQL 11 server (via a cron-job). On the ...
0
votes
2answers
31 views

Updating table with many rows

I have 2 tables: CREATE TABLE `users_ids_mapping` ( `current_user_id` bigint(20) NOT NULL, `new_user_id` bigint(20) NOT NULL AUTO_INCREMENT, UNIQUE KEY `current_user_id` (`current_user_id`), KEY `...