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

How to find Rank of row in MySQL

I am new to MySQL. I know that ROW_NUMBER() OVER (PARTITION) will not work in MySQL. I want to convert the code from SQL Server to MySQL: SELECT *, ROW_NUMBER() OVER ( Partition By X....
0
votes
0answers
1k views

How to speed up a mysql restore from large dump file

I have a 350MB msyql dump file that takes over 3 minutes to load. I'd like to speed it up. I've tried using --opt with mysqldump (shaved off 30 seconds). The best I can achieve is 42 seconds faster. ...
0
votes
0answers
2k views

Cannot start MySQL cluster with single node

For some testing I have installed MySQL Cluster. I can get it to work with configuration suggested in Lunix guick start guide. My configuration: [ndb_mgmd] hostname=localhost datadir=/home/vmUser/...
0
votes
2answers
123 views

How can i use the same query for all codes?

I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) : I want to return the last price for the last date of product code ...
0
votes
1answer
210 views

enable log_bin_use_v1_row_events in mysql 5.6

I have a MYSQL master with two slaves also running MYSQL 5.6. I want to create another slave and that needs to be MYSQL 5.5. I was wondering if replication will work smoothly. I have ...
0
votes
1answer
760 views

ENABLE KEYS on a large table is crashing mysql

I am trying to copy our data over to a new server, and at the same time, testing our backup-and-restore processes. I have a 2.6GB file created by SELECT INTO OUTFILE, containing 19 million rows. I've ...
0
votes
1answer
263 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
1answer
152 views

Few Insert queries rollbacked due to system crash

Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked. I am sure that they were proper ...
0
votes
2answers
426 views

Should we explicitly lock MyISAM tables or is it implicit…?

Does MyISAM tables automatically lock a table for each select or insert statement .... I mean if I query the database as select * from tablename then does the MySQL engine first lock it for read ...
0
votes
1answer
52 views

Problem with foreign keys

I do ALTER TABLE bdt.auth ADD CONSTRAINT `depositor no.` FOREIGN KEY ( `depositor no.` ) REFERENCES bdt.depositors( `depositor no.` ) ON DELETE CASCADE ...
0
votes
1answer
557 views

How do I terminate a running SELECT query without restarting MySQL?

I have a MySQL select query that is not getting killed. Please help! And we have load avg building up on this server. | 64446 | root | localhost | dast-india | Killed | 3758 | Sorting result | ...
0
votes
1answer
392 views

Does MySQL or MariaDB support double literals (double precision)?

MySQL seems to support double precision The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values and eight bytes for double-precision ...
0
votes
1answer
1k views

MySQL - maximum of sum over different months [duplicate]

I have a table called commodities where I have nature_of_goodscolumn in it. nature_of_goods contains all the different types of goods like machinery, household etc. I have another column called as ...
0
votes
1answer
929 views

MySQL 5.1.73 to 5.6.17 structure + data export

Are there any known issues to export structure and data from MySQL 5.1.73 to 5.6.17? Nothing fancy and to export-import user will use phpMyAdmin. Thanks
0
votes
1answer
171 views

Mysql only using 10% of cache [duplicate]

I have a dedicated Mysql server that only uses 1.1 GB out of 16 GB ram (picture link 1). I belive this leads to my Disk I/O utilization beeing at 75% peaks (picture link1). How can i configure sql ...
0
votes
3answers
216 views

How to return last used record?

I have simple table called ips with 2 field ip and address, which schema is following CREATE TABLE IF NOT EXISTS `ips` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(255) ...
0
votes
2answers
1k views

Converting MySQL 5.6.10 - 32 bit to 64 bit version

I am currently using MySQL DB Version 5.6.10 for 32 bit running in 64 bit Window OS.I want to change it to 64 bit without taking any dump..Can anyone help in this?
0
votes
0answers
831 views

CREATE OLAP server

I have a MySQL database server along with replication server, i.e. OLTP system altogether. As I have very heavy data fetching queries for the decision making reports. I need to create data-ware house ...
0
votes
1answer
813 views

Executing delayed inserts with MySQL event scheduler

A table that I'm working on, has two triggers; one is after insert and the other is before delete. These triggers carry out calculations and copy over some rows to a backup table. This backup table is ...
0
votes
2answers
618 views

Converting spatial data from Oracle to MySQL

This is a followup to this question: Problem getting zips in raius via MySQL I have some spatial stuff in an Oracle table. MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),...
0
votes
1answer
216 views

Logic Value of Assignment Expression

I'm doing the leetcode SQL problems and kind of confused with the code below: select distinct Num from ( select Num, case when @prevNum = Num then @count := @count + 1 ...
0
votes
3answers
5k views

How to avoid deadlock while updating 2 rows in MySQL

This is a interview problem: There is a perl program that updates the database, and it could run in different processes. One process may execute a transaction like: update row A -> update ...
0
votes
1answer
103 views

Hashed and heap indexed object storage table insert and query performance

This question is partly to ask advice and assure this design is sane. I state the the desired properties upfront here, then go in more detail in the rationale and follow up with the design and example ...
0
votes
2answers
125 views

MySQL: speed query that uses the time intervals

How can I speed up this query that uses the time intervals: SELECT a2.field_a1, MAX(a1.field_a2) AS field_a2, MIN(a1.field_a3) as field_a3, a3.field_a4, SUM(a1....
0
votes
2answers
2k views

Can we disable binary logging for one specific session or user in AWS RDS

Currently we have a situation to disable binary logging for one set of queries on AWS RDS. We thought to set log-bin=0 for that specific session. As per Amazon, this is not possible. Is there any work ...
0
votes
2answers
758 views

Improve performance on write-only table?

I am using MySQL 5.5 with innoDB. The basis of my server is Netty, JDBC and BoneCP. I have a log table that contains user inputs(HTTP header, request body etc). This table will only be read very ...
0
votes
1answer
530 views

Where does mysql store data?

Where does mysql store data? I found out (by using mysql>SELECT @@datadir ) that it's in var/lib/mysql - but that can't be it. I have a quite big database (4 GB) called 'bot', but all the files in the ...
0
votes
2answers
3k views

MySQL - Creating an associative table

I have the following table structures: Cases: (PK) case_id | case_notes | time 1 | blah | timestamp 2 | blah | timestamp 3 | blah | timestamp . | . ...
0
votes
1answer
18 views

I'm looking for SQL to access some unusual data

I'm looking for an SQL-only solution for looking up the data in my local database. This is for a nonprofit charity. I have a MySQL database that contains one table. It has two columns, KY and VL, ...
0
votes
1answer
146 views

Variable value is not incrementing

Why is the country_rank value 1 for each row, which I assume is due to STRCMP returing false everytime. I think @current_country is not set correctly but dont know why. Table: sn country city 1 AAA ...
0
votes
2answers
255 views

MySQL Solution to Sync 3 servers data

I have 3 servers which there is a load-balancer in front of them and sends the requests to these servers randomly. The script is the same in all of these 3servers, but I need to sync the DB in all of ...
0
votes
0answers
756 views

Simple conditional update causing a deadlock hints at MySQL bug

I have a very simple "inventory management" update that under load causes InnoDB to deadlock and rollback one of the threads. There doesn't appear to be any ordering issue here, so I'm at a loss how ...
0
votes
1answer
340 views

create column name from other table rows mySQL

i have following code, for this i have two tables, one table contain questions and another table contain answers. answers table something like following : (ans_question_1 to ans_question_95) | ...
0
votes
1answer
184 views

How can I use function's parameter in it?

I have a function like this: DROP FUNCTION IF EXISTS user_repo // CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20) BEGIN DECLARE vote_value mediumint; DECLARE score mediumint; ...
0
votes
1answer
5k views

“Error in query (1030): Got error 168 from storage engine” when creating table

I'm trying to create a table on a separate directory, like this: CREATE TABLE `abcdef` ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='/media/sdc/mysql/'; But it returns the error: ...
0
votes
1answer
374 views

Unable to turn on compression using innodb

I am unable to create a new table using compression. I'm using the following options which from what I understand should allow me to use compression. default-storage-engine = InnoDB ...
0
votes
1answer
70 views

How to restore several databases with binlogs?

I need to restore 3 databases but I don't have the backups of the, I investigate that it can be done with the binlog files, but how does that work when you have several databases that need to be ...
0
votes
0answers
43 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
0
votes
2answers
896 views

How to most efficiently store possible combinations of categories?

I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of ...
0
votes
1answer
1k views

Is ANALYZE useful immediately after creating an InnoDB index?

Let's create a new index on an existing table: CREATE INDEX `IDX_some_index` ON `some_table` (`a_column`, `another_column`); Is this useful? ANALYZE TABLE `some_table`; Can anyone point to ...
0
votes
2answers
46 views

No index used warning in WHERE column IS NULL

I'm getting this warning: No index used in query/prepared statement SELECT * FROM mTable WHERE my_column IS NULL But the my_column is already indexed. MariaDB [mytest]> SHOW INDEX FROM mTable; ...
-1
votes
1answer
31 views

Mysql - How to delete a row with foreign key referencing multiple rows

I have a table F: CREATE TABLE IF NOT EXISTS `mydb`.`table_f` ( `id_f` INT NOT NULL AUTO_INCREMENT, `fk_1` INT(11) NOT NULL, `fk_2` INT(11) NOT NULL, `column_F` VARCHAR(45) NOT NULL, ...
-1
votes
1answer
3k views

what else to enable to make LOAD DATA INFILE work?

Anyone got an idea what to enable to make the LOAD DATA INFILE working? I have the global variable 'local_infile' set to ON, the database is answering queries just normaly, but LOAD DATA INFILE ...
-1
votes
2answers
2k views

SOLUTION - OOM Killer was killing mariadb every hour or so [closed]

The Out Of Memory killer was killing mariadb every hour or so but not regularly on time like a cron job to restart it. I found it doing a journalctl -n 200 -u mariadb to show the last 200 lines of ...
-1
votes
1answer
125 views

extended_status vs show variables, what to monitor?

I am stuck to monitor one important server MySQL. First what is the different between : extended_status vs SHOW VARIABLE at MySQL? Which one should we monitor? I need the actual status of most ...
-1
votes
2answers
402 views

How to store tax in the database when tax consists of multiple taxes

We need to design Tax table for Billing System. Lets say we have a SGST (State Tax) and CGST(Central Tax) and Final Intra-State Tax = SGST + CGST Tax table id name rate Tax table id | name ...
-1
votes
3answers
43 views

I need to migrate a db to a new db with foreign key. How do I check for constraint errors?

I need to migrate a lot of mysql MyISAM table to a new InnoDb version, adding foreign key constraints. I can dump and import, disabling first foreign key checks. But is there a way to check which ...
-2
votes
2answers
900 views

11 LEFT JOIN s make query very slow

I have this big query it contain 11 LEFT JOINS . when i run it. it get 12s to return result . is some one can assist me to make speed this query that big help. um new to ...
-2
votes
2answers
310 views

Database engine with counting b-trees for efficient paging support [closed]

Today I realized another fascinating thing about sql databases: They don't do paging well at all. By paging I mean selecting a slice of data defined by a starting record number and a record count ...
-3
votes
1answer
52 views

From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app?

I am developing an app where I need to store an undetermined number of properties for any given entity, for example: Entity || # of properties** Apples || 14 Oranges || 4 Pears |...