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
3answers
96 views

MySQL LIKE IN with Input Parameter of stored procedure Comma Separated Values

I am creating a stored procedure to search user's Towns. From the User Interface user will select multiple towns that will be sent to the stored procedure. Sample input of the towns parameter for the ...
0
votes
1answer
35 views

Mariadb started giving erroneous query results for 1 view

10.1.44-MariaDB-0ubuntu0.18.04.1 Have a view volv_action which does a few joins which has been working for years and was not changed. But last night Mariadb started giving erroneous results if its ...
0
votes
0answers
19 views

Algorithm to match two distant data sets for best performance in SQL

I have the following data sets: A list of users. Tom, Lisa, Karen These users can manage any shop (one or more): Barba, Rentraum, Kisabran, Tespot and Lowvel These shops can provide any services/...
1
vote
1answer
15 views

Advice on table processing for invoice-allocation-payment structure for invoices of zero amount

Please may I have some advice/opinions on the best process to use in the following situation. To deal with subscription payments in a small charity I use a cash matching system comprising Table ...
1
vote
1answer
31 views

Rollback is not working if the storedproce call another storedproc

Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit? example: pseudo procedure proc1 create procedure proc1(in var1 int, out var2 int) ...
1
vote
0answers
26 views

Migrated a database to mySQL 8.0.17, unable to upgrade or start service

I recently setup a new host with mySQL ver 8.0.17 and moved an old mySQL database (ver 5.1.73) to it using mysqldump -u root -p --all-databases > somefile.sql and then importing it with mysql -u ...
2
votes
1answer
31 views

Less obvious “The table 'mytable' is full” error

Error in mysqld.log: 2020-02-02T01:17:32.133974Z 3069 [ERROR] /usr/sbin/mysqld: The table 'IS_DUT_IFID' is full None of the many threads I've found about this error have helped me to resolve this ...
0
votes
0answers
21 views

MySQL protection by iptables. Data was stolen

My MySQL data is losed.. second time. I configure iptables correctly and mysql users. Users protected by static ip of dedics and only my ip for acces is by CIDR diapason. Iptables configuring for ...
1
vote
2answers
35 views

MAX(id) function in query with indexed condition

I have a table create table test ( id int auto_increment, createdAt TIMESTAMP not null, primary key (id) ); create index createdAt on test (createdAt); And here is my query SELECT MAX(id) ...
0
votes
1answer
25 views

How do I handle a database for a form who's structure will likely change in the future?

I need to build a database to record form data from a custom tool. This form consists of a fairly basic set of checkbox/radio button fields so the data itself doesn't present much of a problem. User ...
1
vote
1answer
29 views

MySQL replication not finished on next site call

I have 3 MySQL8.0 instances. Two of them are slaves. Each site call I‘m reading the slave till the first write Request is made. Afterwards Only the Master is used during the request. Now, if I write ...
0
votes
1answer
22 views

Data Lake : Data Catalog and Data Schema definitions

I'm trying to understand the definition of data catalog and data schema. After several articles I read, I'm getting even more confused! I realized when the article is talking about data lake, the ...
2
votes
2answers
17 views

Is there a way to construct SQL statement to return desired value only if there is a single row found, and return 0 otherwise?

Consider below: SELECT customer.contact.id FROM customer.contact WHERE customer_office_id = [INTEGER] If only a single row is returned, I want the result set to return ...
1
vote
3answers
65 views

Is it ok to add a table years in a Relational DataBase?

How can i explain my coworker that this is unnecessary complexity and redundant data? He wants to have a table of years because many tables have the 'year' value, and he also want to have relation ...
1
vote
0answers
21 views

Is there any such thing as an https storage engine?

Maybe I have too much time on my hands, and this may be a stupid idea, but... I have recently had to download data in table-form from websites, and I got to thinking that it might have been easier, ...
0
votes
1answer
40 views

How to implement UNIX like ACL for table rows

EDIT: I can't create or use native MySQL users. I have to use users stored in table with their password hash. I am new sql stuff so please forgive me for asking dumb question. I am creating my first ...
1
vote
1answer
25 views

MariaDB handling composite unique indexes that are foreign keys at the same time [closed]

I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that. I ...
0
votes
0answers
28 views

Select all entries where range of two DATETIME fields includes given date

I'm looking for a way to get all entries from my reservations table to be shown in a calendar. Now I know that I will have to iterate over all days of the current week and run a database query to ...
0
votes
2answers
66 views

SQL: How can you release or prevent update/insert locks while remaining in the transaction?

Current situation: We're using mysql (percona) 8.0, with InnoDB tables We're using read-committed transaction isolation, although we can switch to read-uncommitted because: We don't use transactions ...
0
votes
1answer
38 views

Select one item per matching ids(value)

I have a mysql table(trips) with records about cars. I wish to query the data and return one item per carId. But am unable. Below is the query example getting 50 records, which should also have 50 ...
-1
votes
1answer
29 views

MySql LEFT JOIN returns wrong first in PHP page, but correct results in MYSQL command line

We added two columns to our main MySQL table (named objects), a column for quantity and a column for id of the units. If the quantity and/or units are unknown, those columns are set to NULL. We ...
1
vote
1answer
35 views

SELECT gives different results with/without FOR UPDATE in same transaction

I'm sorry but I havent't found a better wording for the title. Given the table a: CREATE TABLE a ( id INT NOT NULL auto_increment PRIMARY KEY, ref VARCHAR(10) NOT NULL, val INT NOT NULL )...
1
vote
1answer
22 views

Setup innodb_io_capacity

I want to configure the innodb_io_capacity option for my MariaDB server. In the documentation I found the following: Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit ...
0
votes
1answer
36 views

Unable to understand this mysql deadlock log

I am getting a good number of these deadlocks (low % of total requests, but still high in absolute errors). For all the deadlocks, the pattern is very similar; but I am unable to understand why there ...
0
votes
1answer
54 views

INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query

I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id ...
0
votes
2answers
35 views

Encoding issue mysql

I'm using the MD5 hash function of MySQL to hash some data I import into the database from a CSV file. The issue is that some lines have an incorrect hash (but not all of them). When I use a query: ...
1
vote
1answer
48 views

Is it realistic to expect a database of size over 50 GB (some 300+ million records) to be imported within 3-4 hours on a single server (MySQL)?

I have hundreds of millions of rows in a text/csv file (genomics database btw - each record is less than 255 characters long...). Ideally I'd like to make them searchable since right now my best bet ...
0
votes
1answer
24 views

Count missing values across columns and join back to original table

Here is the table: I want to count the missing values across each row for t1, t2, t3,..., and create another column in the same table with the results as shown in the picture. I can easily do this ...
0
votes
3answers
23 views

How many bytes does character takes if I created database with utf8 in mysql

Suppose if create a database with utf8 character set and created table and inserted data. Here my doubt is if the character is 1 byte then it will occupy only 1 byte or it will occupy 3 bytes ...
1
vote
1answer
14 views

Master-Master Replication: Missing PK Values [duplicate]

I have configured master-master replication in mysql 5.7.22 and its working fine. I am using below settings for PK values. I found issue in their ID sequence and pk values missing their sequence ...
0
votes
1answer
18 views

Duplicate key on delete and update of unique key

I have a table with 10^7 records. I do the following: Delete a record using a unique key to identify it Update another record's unique key to the one I have just deleted MySQL 5.7 complains about ...
0
votes
1answer
36 views

Compare running / cumulative total with static target

I have some daily data and monthly data. I want to compare these two datasets, with Hive and perform a payout when the sum of actual for a month passes that respective months target. In the above ...
0
votes
0answers
15 views

Paste unicode into mysql-client

I'm trying to paste some unicode into the mysql-client command line in order test something, but it either doesn't insert anything or inserts a number of plus symbols (depending on content/length). I'...
0
votes
0answers
21 views

Update mysql Database - am I doing it right?

I have about 15 tables that are linked together in my database (mariadb - parent-child with FK). If I now update something with VBA (over ADODB, mysql-connector), I update first with my code the ...
0
votes
1answer
35 views

Mysql 8 accidentally lost ROOT user all privileges in localhost wampserver 3.2

Mysql 8 root user all privileges lost.I changed some permissions from phpmyadmin. . After that all tables disappeared. I found this answer Lost All Privileges in MYSQL.but did'nt help me. I am using ...
0
votes
2answers
31 views

MySQL query duplicates from 2 columns [closed]

I need to query duplicates in a table from 2 columns (agents_name) and (estates_name) based off the (display_name) column. I tried the following: SELECT id, display_name, author_table.agents_name, ...
0
votes
2answers
28 views

MySQL: SELECT more data than necessary or use multiple SELECT queries?

I'm wondering if it would be better to use a single query and SELECT more data than I might need, or use multiple queries and SELECT only what I need when I need it. For example when the client ...
0
votes
2answers
31 views

Query on large database much slower on HDD

I have the following table: CREATE TABLE `Geo1` ( `id` int(8) UNSIGNED NOT NULL, `nameRef` int(8) DEFAULT NULL, `placeRef` mediumint(7) UNSIGNED DEFAULT NULL, `category` enum(...) COLLATE ...
0
votes
2answers
40 views

How to dump a large database?

I try to take a backup of a huge MySQL database via mysqldump. The database has millions of rows with a total size of 1.3TB. CREATE TABLE PageContents ( ID int(11) unsigned, Added date, Text ...
0
votes
0answers
16 views

Mysql truncate take a long time

I have a MySQL Database with a very very large table. I couldn't do the the select count(*), it was taking too long. I wanted to clean up and I have done a truncate of that table 24hr ago. When I do ...
0
votes
1answer
24 views

Add constraint on number of entries of certain type in referenced table

As a personal project I am working on a database to keep track of cars as they are manufactured and then sent to dealerships. One of my constraints is that certain dealerships can only have a certain ...
0
votes
2answers
23 views

How to “select * from table from database” with -e?

I want to select and show 1 row form a table with the mysql -e. I was trying out the following but no luck, mysql -u root -e “select * from table1 from db01 limit 1” Actually I am trying to dump a ...
0
votes
0answers
22 views

Use nullable fields to choice the behavior of Controller

My simple ERP provides three ways to add an item to an invoice: a row from a delivery note a row from an order a custom entry Because for all three cases I may need to display an order reference, I'...
-1
votes
0answers
26 views

mysql reduce size of ibdata but information_schema not exist

my client has an old version of MySql (4.1.15) and his server is getting full. I looked at it and saw the ibdata file of his mysql is getting to big. I looked at this article: What is the best way to ...
0
votes
1answer
21 views

MySQL COUNT based on a condition [duplicate]

I have a MariaDB database, in which I have this table: ID | Item Name | Item replaced | ----------------------------------------- 0 | Item 1 | Replaced 1 | Item 2 | 2 | Item 3 | ...
0
votes
1answer
16 views

MYSQL item in select value where value is enum

I have some hard time with a select, i think i used in past but i cant remember where. SELECT * FROM tableX WHERE id in ( select ids from tableY ); in tableY i have: (ids is a string field with ...
1
vote
1answer
14 views

Which databases does `mysqlpump` ignore by default?

If I run the following command: mysqlpump > mysqlpump.sql Is there any documentation, a SQL query that can be run, or perhaps a config file that contains a complete list of databases that ...
0
votes
1answer
23 views

ALTER TABLE WITH VALUES FROM SELECT

I have a table1 with fluctuating number of columns. (colname1,colname2,colname3...colnamen) I want to : ALTER TABLE table2 CHANGE COLUMN `columns` `columns` ENUM('colname1','colname2','colname3' .......
0
votes
1answer
23 views

ROUTINE TO SELECT FOM A TABLE AND UPDATE ANOTHER TABLE [closed]

I do not understand how to update a table from another table after a table has been modified. I have a table with 3 columns like id(INT), distname(VARCHAR)UNIQUE and score(INT), and I have another ...
0
votes
2answers
21 views

Does reordering table rows reduce the time for subsequent ordering with the ORDER BY clause?

Is there any run-time advantage gained by changing the order of a table's rows to match the expected ordering of the ORDER BY that is in a slow select? (assuming unique alphanumeric index and no auto-...