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.
17,552
questions
0
votes
1answer
10 views
How to set malloc-lib in mysql 5.7 in ubuntu 18.04 LTS
I recently install jemmaloc 3.6.0-11 using apt-get install libjemalloc1
on my ubuntu server 18.04
the installation is succesfull
but when I check using
pt-mysql-summary | grep -A5 -i "memory ...
0
votes
0answers
23 views
mySQL query to create a matrix of a join table [duplicate]
I have two tables and a many to many join table, I would like to use mySQL to create a matrix counting the intersection.
CREATE TABLE `wp_app_schools` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`...
0
votes
0answers
17 views
Is querying by a PRIMARY KEY in MySQL faster than other queries?
I have two queries that return on a dataset of 200,000 records with apparently the same performance. The first looks up a primary key, and the second a varchar(32) with no special constraints.
Am I ...
0
votes
1answer
21 views
MySQL Full Text Search Excerpt
I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for.
I ...
0
votes
1answer
20 views
Optimize query between two MySql tables
I currently have a query that works on two tables of expense and income. This is an example of what the structure of both tables looks like since they have the same columns:
| id| date | ...
0
votes
0answers
21 views
Optimising InnoDB configuration in MySQL80 Server
I have a WAMP production environment, currently running Apache24, MySQL57, PHP73 on two Windows Server 2016 servers - an application server and a dedicated database server - and I'm toying with the ...
0
votes
0answers
14 views
Which characters are used as letters inside a fulltext index of MySQL?
I can add a fulltext index to MySQL tables. This will index all words of the related fields inside the table.
But what are "words"? Indeed they incloses letters and digits (A-Z, a-z, 0-9) and maybe ...
0
votes
0answers
29 views
How do I migrate mysql .frm and .ibd files to sqlite?
Mysql seems to not work anymore on my machine. I have spend way too much time trying to fix it but without success.
But I found out that sqlite does all the stuff I need so why not just change to it. ...
0
votes
1answer
21 views
Get the last row that was left outside of a timeframed SQL query
Is there a way to write a SQL query with a timeframe condition to include the latest row that is outside of the timeframe (besides solutions like counting the size of the result set and querying for ...
1
vote
1answer
172 views
Converting SELECT subquery to LEFT JOIN doesnt return same value
as title says i have an issue converting SELECT subquery to LEFT JOIN doesnt return same value :(
I need to change this as subquery really slows down query.
Here are my original query (gross_end_out ...
0
votes
0answers
9 views
MySQL hangs when I query specific tables after truncate
I've observed this a couple of times now.
I truncated a table, and now anytime I do any SQL operation on that table, MYSQL client will stop responding. I can open a new client and query any other ...
0
votes
1answer
29 views
Order By First Condtition and If multiple matches, order by second condition
I was practicing SQL problems at hackerrank.com when I came across the following question:
Query the Name of any student in STUDENTS who scored higher than
75. The Marks field in the table stores ...
0
votes
1answer
36 views
Incomplete dumps using mysqldump
I am running Mysql server with master and slave replication. From past few days i found that backups on my master server are incomplete.
When i ran manually on master it's returning the below error
...
1
vote
1answer
22 views
Add new columns on existing or auxiliary table?
I have a database with the following table:
stores
- ID
- name
- phone number
- email address
After adding around 4000 stores to the database we figured that we will need to allow store owners to ...
0
votes
0answers
16 views
mysql.rds_history query locked for 70 seconds
SELECT count(*)
from mysql.rds_history
WHERE action = 'disable set master'
GROUP BY action_timestamp,called_by_user,action,mysql_version,
master_host,master_port,master_user,...
0
votes
0answers
11 views
Mysql query take more time when use sub query [duplicate]
CREATE TABLE `tbl_temp_numbers` (
`win_digit` varchar(10) NOT NULL,
`amount` int(11) NOT NULL,
`session` varchar(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for ...
0
votes
1answer
15 views
Least recently used replacement strategy - where is pin count stored?
I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.
The two popular replacement startegies mentioned are least recently used - LRU and ...
0
votes
0answers
26 views
MySql 5.7 master-master (restore a master by replication vs dump)
We have MySql 5.7 running in Centos 7 in cloud environment.
We have the master-master working properly. One master gets the writes which replicates to the other master. The second master is a hot ...
0
votes
0answers
17 views
Percona: Analyze table doesn't work
I have a Percona 5.6.28 servers with a=some TokuDB tables. The biggest table has ~120 million rows. Statics keeps changeing in the past hald year:
At first. Analyze table with recount model toke ...
0
votes
0answers
36 views
Set slave_exec_mode value for RDS
I am trying to figure out a way to set slave_exec_mode to idempotent on Amazon RDS.
I've checked the parameters associated with created DB instance on Amazon Console, but this parameter is not exit ...
0
votes
1answer
39 views
VARCHAR automatically changes to VARBINARY
I am new to MySQL. When I declare a column as VARCHAR, why does it automatically change to VARBINARY? How should I avoid this?
Database changed
...
0
votes
1answer
21 views
MySQL To Show Month Over Month Data By Year
How can I change this query to show data in this format
01-18 2000
01-19 4000
02-18 3000
02-19 1000
This is the syntax I have but since it's union I can't use an order by clause
SELECT ...
0
votes
1answer
16 views
Does MySql event will start if the previous event doesn't finish yet?
Example I have an event that will run every 5 seconds, then the first instance of the event is triggered but more than 5 seconds later the instances still not done executing. What will happen to the ...
0
votes
0answers
15 views
mysql db data folder restore in 5 days old backup [duplicate]
recently I had the need to install a driver on a server and because of the destination I finished uninstalling XAMPP in which I had a database that I need to recover.
The fact is that after ...
0
votes
2answers
40 views
Finding indexes that aren't used often or at all
Is there a facility or a recommended practice for determining which indexes are being used and which ones can be "purged"? At the minimum, I'm looking for a log file that over time can be analyzed for ...
0
votes
1answer
46 views
Finding the cause of a stuck “Sending Data” query in MySQL
I'm running MySQL on a 1gb DigitalOcean droplet, and I have a number of scheduled tasks through the Laravel framework that run. One task in particular (seemingly) randomly causes MySQL to sit at 100% ...
1
vote
1answer
49 views
Get stopwords of a InnoDB fulltext index
I can set stopwords for a MySQL fulltext index with SET innodb_ft_user_stopword_table ... before creating the fulltext index.
But how can I figure out the existing stopwords of before created ...
0
votes
1answer
29 views
Best way to put date range in a table in mySQL
I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in ...
0
votes
1answer
28 views
Direct database interaction with memory - how it works?
I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.
I've noticed that the DBMS interacts directly with memory, as opposed to asking OS to ...
0
votes
1answer
28 views
MySql Galera Multi site high availability
We have a datacenter with 3 percona glera nodes
We have a store that wants to be in sync with our datacenter(multi master)
our store's internet connection is not reliable ( may be down for 30 min), ...
0
votes
0answers
30 views
How to copy entire database to another?
I am looking for a solution to copy the entire database by using MYSQL query.
I am developing a system where admin can create b2c and b2b stores from backend, and for both types of websites, I have a ...
0
votes
0answers
27 views
MySQL8 crash recovery from IBD files with import tablespace
Our database MySQL8 crashed due to no space left on the data directory. After we arranged more space the database kept crashing with the error
[ERROR] [MY-013183] [InnoDB] Assertion failure: ...
0
votes
1answer
70 views
MariaDB slave replication stopped after slave upgrade to 10.1.41
After the recent MariaDB 10.1.41 update which happened on 01/Aug/2019, few of our slaves have stopped syncing relay logs from master which is on 10.1.40. Slaves which got automatically updated to 10.1....
0
votes
1answer
14 views
How to get the customer payment count for each row in MYSQL
I have a payment system, and want to display the counter of each customer payment (first, second, third ...) and if it's the fist payment, display (New). How can i do this in one query ?
SELECT c....
0
votes
0answers
7 views
innodb corrupt database - recover events and functions
There is plenty of articles how to restore the data but can't find anything about the scheduled events and functions recovery.
The db crashed due to running out of disk space (plesk). I managed to ...
1
vote
1answer
404 views
MySQL Replication stuck at 'preparing'
My MariaDB replication stuck at 'preparing'.
This happend after an update and restart of the server yesterday. Since then, replication is not working.
Setup: 2 MariaDB servers with master-master ...
0
votes
0answers
29 views
MySQL Count rows of multiple tables
CREATE OR REPLACE VIEW organizedevent AS
SELECT o.organizerID AS "Organizer ID",
o.organizerName AS "Name",
o.contact AS "Contact",
COUNT(e.eventID) AS "...
0
votes
0answers
32 views
How do I recover an innodb database when I have ibdata1 intact and a subset of the original .ibd/.frm pairs of files?
Originaly it runned on mysql Server version: 5.6.28-0ubuntu0.15.04.1-log (Ubuntu) now it runs on Server version: 5.7.27-0ubuntu0.18.04.1-log (Ubuntu)
I can get the structure by generating a .sql file ...
0
votes
1answer
9 views
I need to make a structure for a mysql database
I have a table that saves users:
Email || Username || age || password
and I need to save the series they have seen and the ones they are watching and as I have read it is not correct to insert two ...
0
votes
1answer
25 views
Selecting consecutive rows with same values and one that differs
So there is this question that explains how to do it but what I want is to find the duration from idle to active for a given userID and status for example for Brown it should show something like this:
...
3
votes
1answer
88 views
Selecting rows conflicting values in WHERE clause
I am having trouble selecting rows in MySQL. I do not want some rows to be selected, but they are being selected.
My query:
SELECT name,url
FROM additions
WHERE enabled = 1
AND ...
0
votes
3answers
28 views
How to maintain unique sequence across table columns?
I hava two tables registed_products and unregisted_products. Both have autoincrement primary key which can be overlapping which if 100 id exist reg table,
it can exist in unreg table as well. Insert ...
0
votes
0answers
29 views
AWS RDS mysql deadlock
Everything in our production environment was working well. Yesterday, suddenly out of no where we were getting "lock wait" on any transaction related to a specific row. Same transaction on another row ...
1
vote
2answers
34 views
MySQL EXPLAIN gives more rows then the COUNT with WHERE on the indexed col
I have a database with a Symbol col (among a few others).
If I run:
mysql> explain select count(*) from ABT where Symbol='AFMD';
+----+-------------+-------+------------+------+---------------+---...
1
vote
1answer
32 views
Looping through MySQL Query progressively gets slower
I have a MySQL Query, which as part of a batch job, loops through about 4,000 records - running the same queries against each of the 4,000 records.
As it moves through the records, it gets ...
0
votes
0answers
31 views
How to retrieve all children of a parent record?
I have one MySQL table with this columns:
id
father
son
street
city
I want to obtain all descendants of a father recorded, how I can do this?
I tried this:
WITH RECURSIVE filhos AS (
select login, ...
1
vote
1answer
34 views
Optimization on table selected where primary key
I have this query:
SELECT *
FROM wp_dev_posts, wp_dev_postmeta, wp_dev_term_relationships
WHERE ((post_type = 'projects') OR (post_type = 'post_cost_codes'))
AND (wp_dev_posts.ID = ...
0
votes
1answer
26 views
MySQL queries not being executed for certain DB [closed]
I have a server that running multiple DBs, one of these DB just imported via mysqldump (.sql file). But the issue is I can't run any query on this DB, Also I can't even select it in normal mode. I ...
0
votes
0answers
41 views
Database table design for product with multiple options and various options offer more options
im having a hard time coming up with a database design for products that have multiple options however in some cases one option may even lead to a new option to appear.
Say i have Backpack as a ...
0
votes
1answer
27 views
Identify MySQL create version from data files
Someone has given me a copy of a database full of FRM, MYD, and MYI files. Trying to open it in my newer MySQL version gives an error that I need to run mysql_upgrade. I don't want to change the ...
