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
7 views

Increment a value if another value exists, if not create a new row

Below is what my table looks like, how would I go about increasing the "record" value of a row if the "first" and "last" match a given value and if there is no matching "first" and "last" value then ...
0
votes
1answer
11 views

I have account_id on all of the tables in the database that belong to an account. Could this be why our database is using a lot of memory?

The application makes heavy use of data. It is a Real Estate Lead Management System each lead could make our users thousands of dollars, and accidentally returning other people's leads would be a sure-...
0
votes
0answers
6 views

mysql 8 high availability with drbd, corosync and pacemaker

I would like to implement High Availability for MySQL8, is this version supported by drbd, heartbeat, corosync, pacemaker? I've looked around online to that regard but there isn't much information ...
0
votes
0answers
5 views

MySQL tables_priv corrupted

I have a mysql database and i need to create an user for the login. This user need the privileges to only select on only one table where the login info are. on phpmyadmin when i use the gui i receive ...
0
votes
0answers
20 views

How to trigger Mysql parallel query function to start?

Please help,how to trigger Mysql parallel query function to start? MySQL version is 8.0.19. mysql> show variables like 'innodb_parallel_read_threads'; +------------------------------+-------+ | ...
0
votes
1answer
23 views

Approaching database design

I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start ...
0
votes
1answer
43 views

How is it possible that some people are able to juggle several different SQL variations in their heads at once? [closed]

I have spent the last 20 years dealing with first MySQL (for about 6 years), then 14 years old PostgreSQL. In 2006, I naively thought that it was going to be painless, "because both use SQL"... man, I ...
0
votes
1answer
25 views

How do I know if an index has been successfully created in MySQL?

I use Capistrano to deploy a Rails app. Capistrano handles running database migrations via essentially a shell script that runs from my local computer. About 24 hours ago I started a migration that ...
0
votes
1answer
18 views

Column Terminology

I'm a MySQL student here trying to learn the proper way to call things in the SQL language. CREATE TABLE tbl_name ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, width SMALLINT ...
1
vote
0answers
7 views

MySQL 8.0.18 - ST_Area returns invalid value

I have a problem with ST_Area function in MySQL 8.0.16. The result of the procedure call returns a different result than any library used to calculate the area of a polygon. Example: I have a ...
-1
votes
1answer
38 views

Why is the order of `OFFSET` and `LIMIT` treated differently between PostgreSQL and MySQL?

I have a schema like this (works for both MySQL8 and PostgreSQL12) # 1. Build schema DROP TABLE IF EXISTS Employee; CREATE TABLE Employee ( Id INT, Salary DECIMAL(6, 2), PRIMARY KEY(Id) );...
0
votes
1answer
12 views

After change to 13306, Galera logs error: Slave I/O: error connecting to master 'repuser@<master node ID>:3306'

Working with Galera 25.3.23 on RHEL 7.3 Galera works good before. After changing the MySQL server port from 3306 to 13306, one of the node report error, after restart: Slave I/O: error connecting to ...
0
votes
1answer
20 views

MySQL: Should I keep a running total column in one table instead of summing a second table?

I have 3 tables named users, achievements, and userAchievements. The users table contains information like username, password, e-mail, etc... and the achievements table contains a list of possible ...
0
votes
0answers
30 views

How to reconcile databases after split-brain?

We run three on-premise datacentres with several databases. The network between the three datacentres is not constant and goes down several times a year for lengthy periods of time, but the ...
1
vote
3answers
25 views

mysqldump interrupts always with same file size

mysqldump -h127.0.0.1 -u user -p'password' -t db mail --where 'status=3' > /var/www/project/storage/web/dump2.sql I try it on ubuntu 18.04, mysql 5.7.29. And the file is always 141.8MB. Data just ...
0
votes
0answers
24 views

Cannot truncate a table referenced in a foreign key constraint even with no child data?

With a ON DELETE RESTRICT restriction, I understand that you can't delete a row in the parent table if a reference exist in the child table, but is it expected behavior that even if the child table is ...
0
votes
1answer
12 views

AWS RDS : Setting Create and other grants only for single Database is not working

We are using AWS RDS Mysql, and we are trying to give SELECT access to all databases i.e RO only, but for a single database, we want to give all permissions i.e CRUD. It's not working out ...
0
votes
4answers
37 views

It's possible to order alphabetically all columns in tables of a mysql db?

It's a large db and it's really messy, I would like to order all columns in all tables, without resorting to manual drag and drop... EDIT I know the order doesn't matter. It's a design think. It will ...
0
votes
0answers
10 views

MYSQL :- finding the rank of the player based on score and x? [closed]

column name :-id score x ,return the rank based on the id compare score and value of x
0
votes
1answer
34 views

Select rows from table having more than two specific values

I have a table having details such that Userpin product Time1 contains 4556 23 7:30 A 4557 23 7:30 B 4558 23 7:30 C ...
0
votes
1answer
26 views

Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns id auth old_auth device_id_fk This error is showing Table contains 4 indexes inside InnoDB, ...
0
votes
0answers
8 views

Is it possible for a weak entity to be related to another weak entity

In this case, can payment and Fees be related despite being weak entities.
0
votes
1answer
20 views

mysql complex procedure debugging

I have below stored procedure which i would like to debug but its not throwing any errors into error table.This stored procedure is run by event manager on every minute. Here is the process : I ...
0
votes
1answer
14 views

Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries

¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot. This tables can be Table A: Employees ------------------------- | Name | ID | ...
0
votes
2answers
19 views

Inherit INNER JOIN fields in MySql SELECT QUERY

I want to know how can i inherit one field of an INNER JOIN table in a SELECT QUERY. Here's what i got so far: In this query my result would be NameA, NameB and idC, but i want to inherit tableA and ...
0
votes
1answer
17 views

problem to import load data infile

I am trying to load data into mysql database form text file. LOAD DATA LOCAL INFILE 'P:\file.txt' INTO TABLE table_january FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ...
0
votes
0answers
13 views

Restoring MySQL databases from var directory files backup?

I have no known sqldumps of these databases but have the files recovered from my AMPPS installation on a crashed computer. I cannot recover them in AMPPS on this one as I have Laravel Valet installed (...
1
vote
0answers
29 views

Mysql full text search with other indexed data

After getting some mysql optimization advice in this question. Was able to get the performance to the acceptable levels (when performing lookups using EventType and event_log_domain_id combo). To ...
0
votes
0answers
18 views

Which DB for dozens of billions of rows, 2 columns, to search with the fastest way any “pattern” ? MySQL / MongoDB / HBase / Elastic Search? [closed]

The user search for 'cowboy', if this string is found anywhere, it returns the content. i.e: Description / Name 'super cowboyis me' / 'foo' 'lorem lipsum...' / 'bar' -> FOUND: 'super cowboyis ...
0
votes
1answer
29 views

MySQL: Does the order of the ON condition of a LEFT JOIN matter?

I have two tables, employees and unionMembers. The people in unionMembers are a small subset of the people in employees. I want to do a SELECT query using a LEFT JOIN like so: SELECT wage, ...
0
votes
1answer
30 views

Database Design for Pokémon Review

I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some ...
-2
votes
2answers
25 views

how to hide the columns and data in table based on the header and data values by using php and mysql [closed]

I have two queries for table header and table data so first query is table header I am fetching and second query is table data,my queries are like this, select a1,a2,a3,a4,a5 from test1 select b1,b2,...
0
votes
0answers
13 views

Is Mysql faster if on separate VPS (with cores dedicated to it) or on the same but faster VPS [closed]

Note: This is not a question about capacity planning The problem: Lately traffic on my wordpress site grew and so did my cpu usage. mainly by mysql (roughly 2/3 of the cpu is used by mysql and the ...
0
votes
1answer
12 views

Best practices relational design

I'm creating tables in a database currently and one of the tables has a status column. the status column has 4 different statuses that it could potentially have. I've made a separate table that has ...
0
votes
1answer
22 views

how to show combined results of two tables?

I want to show the result as combination of two. Table 1 SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID` FROM au.`invoice` WHERE au.`invoice`.`InvoiceID` > 11126 -- total results 757 ...
-1
votes
1answer
13 views

Compare 2 column- Column A and Column B , result if the column A has the string of Column B get the output [closed]

Sl no Column A Column B 1 Dell Laptop Laptop 2 Dell Mouse Laptop 3 Dell Acc Laptop 4 Dell Laptop 1 Laptop Result Output Sl no Column A ...
0
votes
1answer
16 views

Select rows from table having specific values depending on another values in the same table

I need to be able to select all Userpin that have tr_type 7 only but if there is tr_type 7 and 8 in other row ignore 7 and 8 example: Userpin TrDate Time1 Tr_type 4555 12-02-2020 ...
0
votes
0answers
21 views

Is it practical to use entity-attribute-value (EAV) model to support custom user fields and avoid bloating of tables?

I am currently working on a medium-sized web app project that is already in production using Laravel framework and MySQL 5.6.40. My clients have been asking for the ability to add additional ...
0
votes
1answer
17 views

i have error in query [closed]

i am not getting what is the issue in the query probably i am not following the correct way to put the string and char sign , i am inserting the data in c# to local host with where clause please check ...
0
votes
1answer
27 views

Using subquery within IF condition

Hello i am having a weird problem currently i have the following query SELECT * FROM `txt_campaign_message` `cm` LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id LEFT JOIN `...
0
votes
0answers
61 views

Why simple mysql query takes too long?

I really give up. Mysql server suddenly got working very slowly. I have mysql server 5.7.29-0ubuntu0.18.04.1 - (Ubuntu). The machine is pretty powerful, 50 cores, 300GB RAM. I did not have problems, ...
0
votes
1answer
17 views

Mysql max connections and event scheduler

Is there a limit to how many "instance" of a scheduled event MySQL can run? I have set this up: CREATE TABLE IF NOT EXISTS t (c1 INT UNSIGNED NOT NULL) ENGINE=INNODB; SELECT * FROM t; DELIMITER $$ ...
0
votes
0answers
11 views

mysqljs batch insert placeholder for mysql-function

using mysqljs/mysql how to correctly use placeholder when doing batch insert where one or more value is using mysql-functions? e.g: UUID(), UNHEX(), NOW(), DEFAULT, FROM_UNIXTIME(), etc example: ...
0
votes
0answers
13 views

MySQL 5.6 to 8.0 (Windows)

I need to upgrade MySQL 5.6 to 8.0 on a Windows community server (install version). Do I need to dump and import to 5.7 then to 8.0 ? or a simple dump of all databases from 5.6 is enough ? thanks ...
1
vote
1answer
10 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
14 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
21 views

Unknown column 'empresa' in 'where clause' [closed]

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
1answer
17 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 ...
1
vote
1answer
44 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 ...
1
vote
2answers
41 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 ...

1 2 3 4 5 366