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
1answer
20 views

Selecting a value from a row where another column is max

I have the following SQL query: SELECT bug.`id`, Max(report.`date`), Count(report.`id`), Max(version.`code`), bug.`id` FROM `bug` LEFT OUTER JOIN `stacktrace` ...
1
vote
1answer
16 views

In mySQL, I shouldn't use semicolon when runing a source command. Why?

I'm learning SQL by myself. I've set up a mySQL server, and I was trying to import the sakila example database, and the textbook I'm using states that for that effect I should use source pathway; and ...
2
votes
1answer
23 views

Optimizing cloud server (php and mysql)

I am running a vBuletin forum as a hobby and I was using a shared hosting service until a couple of days ago. Because the amount of visitors grew and the forum seemed to be slower and slower every day,...
0
votes
1answer
26 views

Am I breaking something if I set an autoincrement PK to 0?

I have this table CREATE TABLE `test_table` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB; ...
0
votes
0answers
20 views

Using a trigger to check if a row exists in mysql — why so slow?

I am using a trigger to check if a row exists. The code below works, but it is very very slow. I suspect that it has to do with the way I am handling NULLs. Any suggestion to optimize it? DELIMITER $$ ...
0
votes
0answers
12 views

mysqldump causing database to get not saved records

I wish there was a safe way to backup mysql database. The database itself is getting not saved records if the records was about to be saved duding the dump. It is happening occasionally, maybe when ...
1
vote
1answer
41 views

MySQL Database Schema recommendations?

Hello, all! I have several needs that I'm attempting to accomplish with my database implementation. List number of wins / losses for a particular user Keep track of the amount of coins a user has ...
0
votes
0answers
12 views

Restore MySQL database partially

I am using WordPress to run my website, along with the traditional MySQL Databases. Whilst seeking to speed up my website, I decided to clear some of the old and unused tables with WP-Optimizer. Sadly,...
0
votes
1answer
31 views

MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I've a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I've done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables: 2020-10-17 0:51:18 ...
-1
votes
0answers
19 views

Issues with store procedure and system [closed]

I take this opportunity to send you a greeting and I comment you, Im working in a store procedure using system inside of MySQL but in workbench, it appears me, missing ;, extraneous input. These are ...
0
votes
2answers
24 views

In mysql, how to add a primary key and a composite key to a table so that it forbids duplicate rows, when there are null values within the columns?

In the following table (8 mi rows), every column has Null values. However, there can never be two identical rows (there will be always at least one column with a different value for each row). How can ...
0
votes
1answer
10 views

Why MyBatis query big table timeout but fast in Sequel Pro in MySQL 5.7

I am using MySQL 5.7, today this is a long time query stuck me. this is my table DML: CREATE TABLE `h_round_pump_record` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '', `tenant_id` ...
2
votes
1answer
28 views

Query works manually, not from stored procedure - Safe update error

I have a relatively simple update query that works fine when I manually execute it from a Mysql Workbench query window, but will not work when I call it as a stored procedure. The stored procedure in ...
1
vote
1answer
25 views

Why index on low selectivity column hurts query performance

The table is simple: CREATE TABLE `t1` ( `ID` int NOT NULL AUTO_INCREMENT, `name` int DEFAULT '1', `LastName` int DEFAULT '0', UNIQUE KEY `idx_ID` (`ID`) USING BTREE, KEY `idx_name` (`name`) ...
0
votes
1answer
19 views

Incorrect string value: '\\xD0\\xA2\\xD0\\x9E\\xD0\\x9E…' for column Username in MySQL DataGrip specific

I know that such error can be caused by incorrect character set applied to some table, but in my case it's a bit different: I have some procedure with select, create temporary table and cursor... The ...
1
vote
2answers
21 views

Why MySQL tends to use more and more swap while there is enough RAM?

I have a server with 64GB RAM and 64 GB swap. It's running MySQL 8.0.19 and few php scripts. Here is my config: innodb_buffer_pool_size=40G innodb_buffer_pool_instances=4 innodb_buffer_pool_chunk_size=...
-2
votes
0answers
52 views

SQL Trigger question (update and delete) [closed]

There are 3 tables, bookstore, book, and time log, How would I create an update trigger to record/insert the price into the price_logs table before updating the book table? Also record the user who ...
1
vote
0answers
13 views

MySQL Transform multiple rows into a single row in same table (reduce by merge group by)

Hy, i want reduce my table and updating himself (group and sum some columns, and delete rows) Source table "table_test" : +----+-----+-------+----------------+ | id | qty | user | ...
1
vote
2answers
36 views

Does MySQL use its index metadata to optimize COUNT(*) queries?

Let's consider a user_actions table with a standard indexed primary key user_action_id. I want to know the number of user actions up to a certain key (say 100 millions): SELECT COUNT(*) AS `...
-2
votes
0answers
22 views

MySQL Shutdown Unexpectedly and it would not start please help me [duplicate]

I am using a windows 8 machine, and I am having a problem with running MySQL on Xampp, I need it to stop saying error messages but it just won't stop displaying: 11:15:27 AM [mysql] Status change ...
0
votes
1answer
13 views

mysql (mariadb) won't start after power outage (MacOS)

/var/log/system.log says (over and over) Oct 13 19:34:01 Data-Server com.apple.xpc.launchd[1] (com.mariadb.server[2128]): Service exited with abnormal code: 1 Oct 13 19:34:01 Data-Server com.apple.xpc....
-1
votes
0answers
16 views

MySQL is corrupt and will not start [closed]

I am trying to run MySQL on Xampp. And no I have to use Xampp for this project no other application. And I seem to run into an unknown problem, that I have tried to solve, but google is not helping me....
-3
votes
0answers
15 views

MYSQL Slow Query WARNING [closed]

I am running FiveM (Grand Theft Auto 5 Multiplayer Game Modification) server, which uses MYSQL as database. When I have a lot of data stored in the database, the data starts executing very slow and In ...
2
votes
1answer
16 views

What is the difference between different utf8mb4 binary collations?

What is the difference between utf8mb4_0900_bin vs utf8mb4_bin binary collations?
0
votes
1answer
23 views

Replicate mysql DB to redshift [closed]

I have a situation where I have a server with multiple databases on a server all with the same schema. I need to replicate this to redshift for reporting and bi type purposes but due to the number of ...
0
votes
0answers
14 views

MySQL 5.7 change system tablespace

I have a Percona MySQL 5.7.31 instance fresh from install on CentOS 7. Some of my collegues noticed some performance problems during some activity on it with a Drupal site, so I was checking another ...
0
votes
0answers
22 views

How to store hundreds of thousands of entries that are extremely similar to each other for the purpose of reading interactions between them? [closed]

Setup: PHP 7.3, 10.4.11-MariaDB. I'm faced with having to populate hundreds of thousands of items so that I can ask the database intricate questions, but as of now, it mostly just filtering by key. An ...
0
votes
2answers
9 views

innodb_force_recovery

I did an innodb force recovery. I went from 1-> and on 5 MySQL booted up. I was able to do mydqldump. Customer is now saying some of data is wrong. (References to another table) Is this possible?
0
votes
1answer
27 views

Examples of JOIN is a specific database [closed]

I have trouble with reasoning about data, particularly when it comes to JOIN statements, and I want to understand better. For the database described below, what would be some meaningful relationships ...
0
votes
0answers
20 views

How to store very very big Array in MySQL (separator / json_encode / serialize)?

I have project with saving timestamps from SCADA system to localhost MySQL-DB. All works fine but thinking about storing datas (read many threads and array storing isnt most efficiency method). Every ...
-1
votes
1answer
18 views

Error 1062 duplicate entry key 1 for key primary [closed]

I have a problem when I try to import data create table song ( artist_id int not null, album_id int not null, song_name varchar(75), tracknum int not null, primary key (artist_id, ...
0
votes
1answer
11 views

GRANT ROLE TO USER in MySQL

I created one role QI_RO, granted all privileges on all schemas on the server and I was trying to grant role privilege to another user but throwing error. GRANT QI_RO to test_user@'%'; Error Message: ...
0
votes
0answers
41 views

MySql, Starting XA crash recovery

Mysql server crashed on my ec2 server, Sometimes it works without problems and sometimes it crashes suddenly, which causes bugs in my project. My Server Details Ubuntu 20.04.1 LTS 1 GB Ram This is ...
0
votes
1answer
16 views

MySQL: How to match data with regular expression string

I have a postcode column in my table with values like AB45*, RD4*, ADB567 etc. Now I want to match my data with this column. The * means that a string starting with AB45 should match with this column. ...
0
votes
1answer
29 views

Subquery in INSERT works in SQL but not PDO

I want to insert a new dataset into a MySQL table tab with external data, but also with data from another table otherTab using the others' table primary key and another condition. However, it could be ...
0
votes
2answers
25 views

Retrieving the top 5 users with the most views on their posts in total

I have 3 tables: users posts visits Each time a post gets visited, a new visit will be inserted in the following format: | id | post_id My post table has: | id | text | user_id ... I'm trying to ...
2
votes
2answers
33 views

Mysql dump as small bulk insert

In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql. I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, ...
2
votes
1answer
28 views

why am I getting a warning of Depreciation of INT(11) in latest version of MySQL?

When I try to create a table with a column of type INT, it automatically gets allocated as INT(11) and this Int length will get depreciated in the latest version of MySQL. How can I declare the column ...
-1
votes
0answers
20 views

Digital wallet and other payment method for single amount payment [closed]

I have created following tables. digitalwallet ($30 balance) digitalwalletTransaction table usercards for payment gateway payments /* store payment details */ So an amount of $100 user need to pay. ...
1
vote
2answers
34 views

How to order by COUNT(*) of unique entries in GROUP BY?

I search through a tag_map as SELECT ArticleID,COUNT(*) AS C FROM WordMap WHERE WordID IN( SELECT WordID FROM Words WHERE Word IN ('word1', 'word2') ) GROUP BY ArticleID ORDER BY C DESC I want to ...
2
votes
2answers
64 views

What is the best configuration for a MySQL instance with a lot of databases and lot of tables?

I have a MySQL database instance with more than 3000 databases in it. Each database contains more than 200 tables. Total data of all theses database comes around 100 GB. I am using Windows Server ...
0
votes
1answer
90 views

Slow query, indexes are in place. Help!

Here is the query: EXPLAIN SELECT f.transaction_id, a.first_name FROM gateway_fees_stripe f INNER JOIN payments_credit_card a ON a.`transaction_id` = f.transaction_id WHERE f.organization_id IN (262,...
0
votes
0answers
15 views

How do I pass organized subsets of data to java-script efficiently?

Disclaimer: I'm a newb to sql, And This is a more general question than I would prefer to ask. So I have a table with a list of English words and a column labeled votes with a bigint value ...
0
votes
0answers
16 views

What are best practices in designing tables for dynamic mathematical inputs?

I am taking a first stab in designing a system that gives ratings to our assets based on various criteria. So to simplify what I am doing - it will be this times 10... I have an asset tables (docs/...
-3
votes
0answers
8 views

How do I do these three basic queries and function calls in raw PHP + MySQL(i)/MySQL-PDO? [closed]

I am experienced in PHP with PostgreSQL. For complicated reasons, I now have no choice but to only have PHP + MySQL database support with my webhost. I last used MySQL in the early 2000s and no longer ...
0
votes
1answer
32 views

count of rows in table day wise

I need to get a count of rows in a table day wise. The table looks like below: Table ABC: f_id|reg_date 1|2020-09-08 2|2020-09-12 3|2020-10-01 4|2020-09-07 5|2020-09-08 6|2020-09-09 Expected output ...
1
vote
1answer
18 views

mysql percona 5.7 not releasing memory

we are using percona mysql 5.7 version. MySQL not releasing the memory after operation complete. we are very much grateful if anyone can guide us to resolve issue. Thanks
1
vote
1answer
31 views

What is the best way to store product size and color for an Ecommerce database?

I am trying to find the best way to store product size, colour for an sports store website, something similar to what amazon does, where you can choose a shoe colour and number. The problem is that a ...
0
votes
1answer
18 views

How to get sum of the count of rows till date from table in MySQL

I have a table as below: TABLE A: id| on_date |off_date 1 |2020-09-10| 2 |2020-09-09| 3 |2020-09-08| 4 |2020-09-09|2020-09-10 5 |2020-09-08| I want to get the count of ids which are in on mode till ...
-2
votes
1answer
30 views

How to replace and update a current value in SQ? [closed]

Every Item Number starts with Item followed by a number so the Item Number database has Number field stored as: Item1 Item2 Item3 Item4 I am wanting to update the above table and replace every value ...

1
2 3 4 5
389