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.
-1
votes
0answers
5 views
Preventing duplicate entries based on a query
If I want to insert some values into a table how do I know if its a duplicate or not?
Structure of table
id(AUTO_INCREMENT PRIMARY KEY)--name--message--date(year"2010".. never mind)
So the data ...
1
vote
1answer
6 views
Adding a second Group By field slows query down (Sending data)
I do have the following query:
SELECT 'TEXT', 'TYPE', 'DESTINATION', 'HEADLINE', 'COUNTRY_ID', 'COUNTRY_CODE'
UNION ALL
SELECT Data_1.TEXT, Data_1.TYPE, Data_2.Destination, Data_2.HEADLINE, ...
1
vote
1answer
11 views
Storing undetermined amounts of user information (MySQL)
I've been looking into the best way of storing an undetermined amount of information submitted by a user. A friend of mine suggested using nested tables, however these don't appear to be a thing in ...
0
votes
1answer
14 views
Join MySQL Lookup tables
I have the following MySQL tables:
games
genres
game_genres
Games can have many genres (action, fighting, horror), so they're in a lookup table with game_id and genre_id. When I pull in my list of ...
1
vote
0answers
26 views
Data Model for Online Examination - Matching Questions
Good day. Forgive me if this question is a bit too open ended. I'm designing a module for a client that allows them to administer certification examinations via their website to seminar attendees. ...
1
vote
1answer
13 views
Securing MySQL backup process
I'm running my MySQL backups through cron scheduled every night, but the thing is in cron the username and password is given for it run, is there any other way I can securely run the backups? where I ...
1
vote
2answers
27 views
Insert into specific table if the user session group
I am trying to insert data into specific table by user session and group.
The tables are users, workers, help.
Structure of table "users":
id | username | password | group
...
1
vote
1answer
29 views
I have two tables and need to join them
First table:
id | username | firstname | surname | address
---+----------+-----------+-----------+---------
1 | johnie | john | something | blabla
2 | alice | something | something | ...
1
vote
0answers
9 views
How to import Sqlite database into another Sqlite database?
Hello everyone I am developing Desktop Application and using the WebSql Database
this database is created into this path: C:\Users\Admin\AppData\Local\Google\Chrome\User ...
0
votes
0answers
21 views
Create a mysql database with scheduled queries
I want to create a system where a database only returns the most recent select query every 20ms,and otherwise returns the most recent one in memory.
I am not sure how this can be achieved.
I am using ...
2
votes
1answer
34 views
MySQL Crashed every hours
MySQL Server crashed every hours and this logs submited.
MySQL Version : 5.6 + cPanel
my.cnf :
Ram of SERVER : 128GB
Cpu : 2x 8core with thareds : 32Core
Hard Disk : SSD Pro
Logs :
...
0
votes
0answers
21 views
How does lock works when delete rows through secondary index in InnoDB?
step1: InnoDB Transaction Isolation Level
Repeated Read.
step2: Table schema
CREATE TABLE `follows` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`followable_type` varchar(255) DEFAULT NULL,
...
0
votes
1answer
15 views
Normalising a database for monitoring stations
I have data about monitoring stations. For now it is stored in Excel tabs. I have decided to try to make a database. It will be my first contact with databases. Here is an example how it looks now in ...
0
votes
0answers
12 views
MySQL import a sql file but login for which is different than localhost db
I have a SQL file which I want to import into MySQL using the command line but get an error saying that the username does not have access to it.
The file is too big, like 35GB, so I can't open it and ...
0
votes
1answer
29 views
Please point me in the right direction
I have been helping my wife with her work as a therapist with the dept of education.
I have been completely taken by surprise by just how unorganized the whole process is.
paper work is either ...
0
votes
0answers
24 views
MySQL keep crashing over and over on reading ibd files
My MySQL instance keeps crashing over and over again, I am running it with innodb_force_recovery = 3 innodb_purge_threads=0 I have tried it with force_recovery = 1 as well. It has happened before but ...
0
votes
0answers
17 views
Searching/selecting database content and smart quotes
I have a utf8 encoded mysql table where the content has been entered using smart quotes.
My client is complaining that when users search the site content, the relevant results don't show up. This is ...
3
votes
1answer
63 views
Optmizing ORDER BY
I'm new to MySQL and have written the following query (see below).
PROBLEM:
The query returns accurate data but takes about 40 seconds to execute.
However, When I remove the ORDER BY part, it takes ...
0
votes
0answers
19 views
inno_db_buffer_pool_size and key efficiency
I have the following metrics from mysql workbench
After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size.
While the used buffer ...
0
votes
1answer
25 views
FLUSH TABLES WITH READ LOCK not working?
I use this command to "lock" my database, flush logs , take a LVM snapshot and release it.
Testing the recover + binlogs , appear it's not working.
(I discovery this when during the recover, applying ...
0
votes
0answers
23 views
MySQL output out of order
I have a bash script which read locks all tables of two databases of a MySQL server, which have the same structures, flush the database and rsync the MYI, MYD and FRM files from one of the locked ...
0
votes
0answers
14 views
What happens if there is missing data in the slave MySQL server
At the data center there was electricity outage. And it appears that at that time my replication stopped syncing because it said something like this; "I cannot find the next position. Last proper ...
0
votes
1answer
38 views
Optimization of complicated sums and counts in related tables
I have a categories table, which have two columns, id and name.
I have a types table, which have three columns, id, category_id and points.
I have a products table, which have three columns, id, ...
2
votes
1answer
22 views
Corrupted InnoDB table, mysqlcheck and mysqldump crash server
I manage a Percona XtraDB Cluster that uses a network storage with a flapping connection. Periodically we experience a high iowait with crashes and remount of the fs in read-only. Replacing the ...
1
vote
0answers
19 views
MySQL GROUP_CONCAT Query, is there a better way?
I have the below Data / Query. Is this really the most efficient way of doing this? As it seems like a lot of work to get the result.
I've tried reducing it / combining the parts but this is the ...
0
votes
0answers
18 views
this is my table i want to replace test4 from attribute to sec_attribute in place of test5,test6,test7; can any one tell me the sql query
+---------+-----------+-----------+---------------+-----
| taxo_id | data_type | attribute | sec_attribute |
+---------+-----------+-----------+---------------+-----
| 1 | Key | test2 ...
0
votes
1answer
15 views
Prevent MySQL to connect Master
We switched a slave to master today and after restarting the new master it tries to connect to the old master.
Even with stop slave;, it tries to connect after restarting.
0
votes
0answers
40 views
How to rollback a multi-update/insert transcation if one of the operations fails or now rows are inserted/updated?
I have a situation I would like to update or insert into more than one table in a SQL clause and roll back all changes if one fails. I have done basically the following in SQL Server (2000 and newer, ...
1
vote
2answers
49 views
MySQL: Why is this UPDATE query so incredibly slow?
So I have a landing table for my data called XMLImport, and a products table called ActiveProducts. When there are changes to a product in the XMLImport table I need it to update the ActiveProducts ...
0
votes
1answer
25 views
Ideal database to store/query data submitted through dynamic user created forms
I am designing a web application that allows administrators to create public facing forms. The fields that can be included in the forms are anything from text fields to radio buttons.
I need a ...
1
vote
1answer
12 views
Forcing IP Whitelisting Globally
We utilize a Software that when creating a new user, automatically makes their host a wildcard (%), and we have no method to change this.
Is there anyway to enforce IP Whitelisting via MySQL ...
0
votes
1answer
39 views
Flexibility vs Performance question
I'm in the middle of designing a database, and have a question for the veterans in regards to scalability.
Currently, I have table A, which represents some object, and table B, which contains ...
0
votes
0answers
26 views
Is there a way to increase the max number of columns in mysql? [on hold]
I would like to know if it's possible to increase the "max number of columns" in a mysql table to avoid getting the error "Too many columns". I know I can't say "I will have XXXX columns max" and it ...
0
votes
1answer
26 views
Prevent replication of ALTER commands
I am using MariaDB 10.0 multi-source replication for a specific use case.
For security reasons, I would like to prevent ALTER commands on master to replicate (such as CREATE, ALTER, DROP...) whatever ...
0
votes
0answers
19 views
Sequelize field association does not work [on hold]
I have two tables. How to link field in first table with id field in second table in Sequelize?
First table:
tables.comments = sequelize.define('comments', {
id: {
type: ...
1
vote
1answer
30 views
Constrain/limit certain records in sql table definitions
Let's say I have posts on which users can leave a comment. And I want to limit the amount of comments to a certain post to 10, is it possible to add such constraint/restriction to the table?
My guess ...
0
votes
0answers
28 views
mysql uses too much memory
Current configuration:
+------------------------------------------+--------------------+
| key_buffer_size | 64.000 MB |
| query_cache_size | ...
0
votes
1answer
10 views
how to Import MySql Dump file in MySQLWorkBench
I have a MySQL Dump file for few objects of a database and I have to import only one table from that file in MYSQL Work Bench.
My question is
How can I import a dump file in MySQL Work Bench for ...
0
votes
0answers
10 views
events not getting completely written in binary log file MySQL 5.0.32
referring this question :
Records not getting replicate from Mysql 5.5.32 to MySQL 5.6
i found that binary log files which has been created at master database server , i see following surprising ...
1
vote
0answers
35 views
how to group transaction entries?
I have this transactions table on my dvd store app:
Transactions
+----+-----------+--------------------+---------+------------------------+-----------------------------------------+
| id | member_id ...
0
votes
0answers
56 views
need help in optimising query
I need help in optimising the below query, I am not a dba and have no experience/knowledge of explain plan . Please suggest on which columns can indexes be created to improve the below query
SELECT
...
-2
votes
0answers
23 views
Inserting Tamil Strings in MySQL Using Java [on hold]
I am not able to store/retrieve Tamil strings in MySQL tables using Java.
Regarding the suggestion to use UTF8 as the table character set:
We have already tried this but it is not working. If ...
0
votes
0answers
14 views
MySQL - How to group data by Day and Hour
I have some problem in getting the query for this sample format. My idea is select hours on that day and group it by day or select all the 7 days and group it by hours?
here is the format:
3
votes
1answer
41 views
Migrating database from old software to new software
I am a young web developer and I am facing a problem I cannot solve on my own and need some help and/or orientation.
My client has an agenda (Lotus), which is a very old software, and I have ...
0
votes
0answers
21 views
Missing statement information from 'LATEST DETECTED DEADLOCK' InnoDB status output
I'm learning how to interpret the LATEST DETECTED DEADLOCK section in the output of SHOW ENGINE INNODB STATUS, and had a question about the output. Some sample output of SHOW ENGINE INNODB STATUS ...
1
vote
1answer
17 views
Export and Import MongoDB?
I come from a bash and mysql environment. I'm trying to get use to mongodb. I see there are different ways to export and import mongodbs. Does anyone know the bash and mongodb equivalent of
...
-1
votes
0answers
22 views
Removing Records Which Have More Than One of the Same Column Value But Keep One [duplicate]
I've searched on SE for a solution to the below issue. So far I have the SQL to retrieve the L_ListingID's for the below issue but not the SQL to remove them.
select * from ...
1
vote
2answers
53 views
How to get the value of column that the same for few rows by selecting values from other columns?
Since I am not a MySQL expert, I am having trouble determining how to ask a search engine for advice on this topic.
I have the following table:
+------------+--------------+-------------------+
| ...
1
vote
0answers
29 views
Is there a way to optimize sorting by columns of joined tables?
This is my slow query:
SELECT `products_counts`.`cid`
FROM
`products_counts` `products_counts`
LEFT OUTER JOIN `products` `products` ON (
`products_counts`.`product_id` = `products`.`id`
)
...
0
votes
0answers
11 views
MySQL Workbench Ground To A Virtual Halt
I have been using MySQL Workbench for a while for both backups and to develop a database and associated queries. Over that time I have noticed that it slows noticeably if the SQL Editor has to many ...
