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.

learn more… | top users | synonyms

1
vote
1answer
47 views

Retrieving specific results from a query

I have the following database table Firstname Varchar(40) Surname Varchar(40) Department Binary JobCode Char(1) Normally, pulling the records of a particular department is no problem. The ...
1
vote
1answer
29 views

How do I select the next row in SELECT FOR UPDATE?

I am modifying my web crawler program to use SELECT FOR UPDATE to avoid multiple crawlers (with multiple children) pulling the same row to process. How do I do this? select * from visited where ...
0
votes
1answer
40 views

Rank students based on their average in MySQL

I have been researching and trying various queries to get this to work. What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will ...
1
vote
1answer
58 views

Error code 1054: Unknown column when inserting data into a table [closed]

I have been trying to join data from two separate tables with distinct columns into a third table, but Error code 1054: Unknown Column 'Company' in field list keeps popping up. My current ...
0
votes
1answer
19 views

MySQL AlphaID Function

While trying to optimize a few queries to not do with PHP something I could bring ready-to-use from database, I tried to convert two routines originally created in PHP to encode an integer in its ...
1
vote
1answer
23 views

How do MariaDB and MySQL differ in character set handling?

In MySQL/MariaDB, I set the connection character set to UTF-8: SET NAMES 'utf8mb4'; SET CHARACTER SET 'utf8mb4'; There are some tables with a latin1_swedish_ci collation and latin1 encoded data in ...
0
votes
0answers
14 views

Merging 3 tables in Access

I have three tables in Access. I want to merge them together removing all duplicates however there are many blank records under the FirstName and LastName tab. The common columns between the three ...
0
votes
1answer
19 views

Make a boolean false after every n number of minutes in MySQL

I have a table with a boolean value, if that value is not explicitly set to anything in the database for a few minutes by the back-end (django) then it must automatically be set to false. How do I do ...
0
votes
0answers
17 views

How to store non-numeric data for a numeric field

I am looking for an SQL or NOSQL solution for the following problem: In a MySQL database (which I have no control over), I have some numeric fields in which numeric data is stored. But for some ...
1
vote
0answers
16 views

Modeling Multiaccounts + content interactions [closed]

I'm having trouble to model a scalable database schema with performance in mind. The requirements are wide, as such that I need a schema that supports; user can swith between multiple types of ...
0
votes
0answers
42 views

Find Intermediate Bus Stop and Source and Destination Bus

I have a table which consists of 4 Columns: PKID, Bus_ID, Stop_Position and Stop_ID. Data: BUS_ROUTE_TABLE PKID BUS_ID STOP_POSITION STOP_ID ------------------------------- 1 1 1 ...
1
vote
1answer
64 views

Optimize mysql database performance

I have a database with some relatively small tables and one big table with about 10 million rows (11 columns) that I would like to optimize its performance. The table looks like this: Id: (int 11, ...
1
vote
1answer
35 views

How can I set TIMESTAMP's default to future date?

I know I can set default value with current timestamp like this ... DEFAULT CURRENT_TIMESTAMP Is there any way to set the column with fixed amount of units based on current timestamp? ... DEFAULT ...
0
votes
2answers
61 views

How to select the same data for unique item?

I have this SQL table: +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+-...
0
votes
1answer
16 views

Using value of a column in another table for ENUM/SET values

I have two tables tbl_events with a column location_id. Then I have a table specifically to hold locations and their IDs,the columns in table tbl_locations are location_id and name. I want the ...
0
votes
0answers
25 views

Getting values from tables to string, combined one-to-many and many-to-many

I am stuck with one task. I have a database with one-to-many and many-to-many relationship. The whole problem is that I need to take from one table all persons in to a separate strings and count how ...
0
votes
0answers
13 views

What triggers/prevents MyISAM Flushing

I have a database that has between 10k and 400 CUD operations/s on MyISAM tables. Whilst the number is slightly lower at night, I see a recurring pattern night after night which causes a backlog to ...
0
votes
1answer
14 views

Table cache hit rate: 0%

I have an AWS RDS Mysql Server with Table cache hit rate's problem. If I do a query on phpmyadmin (also simple query) always do it without cache. Here the mysqltuner log: MySQLTuner 1.6.12 - Major ...
1
vote
2answers
57 views

mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

I am trying to add an index to a table, but only if one doesn't exist. I don't know what the index name is. In the mysql manual I found the alter ignore command: ALTER [IGNORE] TABLE tbl_name [...
0
votes
0answers
11 views

storing email address and associated data

I want to save email addresses and for every email address a group of tags. For example: "a1@temp.com", 2 "a1@temp.com", 3 "a1@temp.com", 7 "a2@temp.com", 2 "a1@temp.com", 12 ... What is the best ...
1
vote
3answers
28 views

Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3. use puppy; DELIMITER $$ CREATE PROCEDURE spGetSystemLicensing ( IN _licenseName varchar(...
0
votes
2answers
20 views

Innodb_buffer_pool explanation

The title might be a bit misleading, as i am fully aware of the concept of innodb buffer pool, yet want to understand something specific about it's calculation process. According to the mysql docs: ...
0
votes
2answers
38 views

How to improve query count execution with mySql replicate?

For my report I have to use count queries (name 'user_activity' for example) and then group by by another column (user_type) in another table (user table). And its really time consuming when it ...
0
votes
3answers
33 views

Unable to replicate a database

I setup a master slave replication, where the master has the following setup datadir = /mnt/DATADIR/ bind-address = 0.0.0.0 binlog-ignore-db = information_schema,...
0
votes
1answer
35 views

How to stop MySQL from replicating CREATE TABLE… on Slave for ignored databases

Mine is a simple MySQL Master-Slave setup, with --binlog-do-db option as discussed in this article. As per the documentation, logs are written for DDL statements for all databases irrespective of '--...
0
votes
0answers
23 views

Need Help Shell Script Connecting MySQL DB

I am running this script to connect to the MySQL DB and achieve the following goal. Run the script for a set period of time. Run the Script for a set number of iterations within the that time. ...
0
votes
2answers
19 views

How to convert from one type of relationship table to another type

I currently have a relationship table where it's: documentID, dataType, dataID Where documentID is the link to the document file. The dataType is if the data is a specific type of user, for example ...
0
votes
1answer
17 views

Mysql database .frm and .ibd file read to understand

I want to read .frm and .ibd files in mysql to understand how mysql database engine store data in files. Which format those file does have, since when I open those files it show non-human readable ...
0
votes
0answers
14 views

MySQL compression on Google Cloud SQL

As the Google Cloud SQL is a managed instance of MySQL under the hood, limited to an absolute max on 2nd generation machines of 100GB of RAM, should I be compressing some of my table and index data ? ...
0
votes
0answers
22 views

Database design for Lending and Servicing

I am designing Database for Gas Cylinder Filling Firm. The firm collects filling fee from customers. The firm can also lend filled and unfilled gas cylinders to each customer. My drafted database look ...
0
votes
1answer
38 views

MySQL reads Date columns in Excel as DateTime?

I'm trying to export Excel sheets to MySQL as new table using the plugin. The export fails on "MySQL error 1064" when I tried to change the data type to Date. I have a few columns in Excel that are ...
0
votes
2answers
33 views

MySQL used all drive space running an ALTER and now won't start

I tried to alter a table without paying attention to the size, and MySQL ate up all the free space on the drive. I deleted a million or so old records but the space was still being used. Googling ...
0
votes
1answer
20 views

how can I create an where in query that only check on field and ignore another fields?

query is something like this: but I can not figure out how to get phones from inner select and ignore c field. select * from users where phone in ( SELECT phone, COUNT( id ) AS c ...
0
votes
2answers
41 views

MySQL: Indexing by column which is also a part of a compound primary keys and

I have a table which has a compound primary key, but I would also like to query the table using one or more of the columns composing this primary key. For example: CREATE TABLE `user_visit_stats` ( ...
1
vote
1answer
14 views

Getting the syntax right for MySQL select with inner join

Using MySQL version 5.1.73 and phpmyadmin 4.0.10.16. I am unable to get this past the syntax checker: select * from courses as co , course_type as ct left join course_leaders as cl on cl....
0
votes
4answers
48 views

Getting rows in left table only if not present in the right table

I need to get only those rows which dont have the corresponding entry in the right table advlabel , so I am doing a full left outer join : SELECT newparsed.seqid,newparsed.wordindex,label,arg1,arg2,...
0
votes
0answers
14 views

optimize multiple match against and temporary tables

I have a table of movies filled with text data. The query is create temporary table t1 SELECT *, MATCH(keywords) AGAINST('$mk') as score1, MATCH(genres) AGAINST('$mg') as score2, ...
-1
votes
1answer
20 views

Reducing column size and data type in mysql

I have a mysql table adv(id(TEXT),index(TEXT) ) which I need to change it as follows ALTER TABLE adv MODIFY COLUMN id VARCHAR(80), MODIFY COLUMN index INT(11); will this preserve all the data ...
0
votes
1answer
25 views

INSERT INTO returns ERROR 1452

I'm trying to move data from one table to another, but having an error: mysql> INSERT INTO activities_article (category_id, created_at, title, promo, content, promo_image_url, coordinates, ...
0
votes
1answer
15 views

How can I create a view with self referencing table and a cyclic condition?

I have a self-referencing table. ID BIGINT NOT NULL PRIMAY KEY, DELETED TIMESTAMP NULL, PARENT_ID FK How can I create a view of this table which each row's DELETED is NULL and any of its parent's ...
0
votes
1answer
19 views

Ways to Upgrade Production MySQL db server from 5.5.42 to 5.7.13 with zero downtime

Our product is an enterprise application with MySQL Enterprise version 5.5.42 deployed in many customer datacenters. We're planning to migrate the MySQL db (bundled with our product) to version 5.7.13....
1
vote
3answers
20 views

Why is determining existence (or not) of rows matching certain criteria using a 'LIMIT 1' query on indexed columns examining more than 1 row?

Consider a table created like this: CREATE TABLE `someTable` ( `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `owner_id` bigint(11) unsigned NOT NULL, `device_id` bigint(11) unsigned NOT ...
0
votes
0answers
25 views

MySQL JOIN is fast, but selecting column is slow

My logs table has half a million rows. Each row has an indexed log_level_id column pointing to table log_levels (with only 5 rows). On log_levels, id is an auto-increment primary key and label has ...
1
vote
2answers
84 views

INSERT and DELETE in single operation (moving from one table to other)

Stemming from this question, I am going to use that complex sort's results to decide which rows' data to move from one table to another. Though I have not yet tried it, I think there should be no ...
1
vote
1answer
32 views

Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11

This question is related to the following 2 SQL errors (line breaks added by me) MySql.Data.MySqlClient.MySqlException (0x80004005): Expression #2 of ORDER BY clause is not in SELECT list, references ...
0
votes
1answer
17 views

MySQL: How to troubleshoot when simple queries are not returning anymore?

This InnoDB database has been up for a couple years now but in the last 24h it has experienced the same issue twice; I have to restart the mysqld service to make it respond to queries again. When it ...
0
votes
1answer
16 views

Migration of records to the table with different column names

I have two databases, and I need to move data from one to another. The catch is they have a table with different set of columns. Here is how I need to rename columns in migration: NULL -> ID (...
0
votes
1answer
13 views

mysql set auto increment to 1 with foreign key

I want to set auto increment to 1 by below command, but it doesn't works: ALTER TABLE tablename AUTO_INCREMENT = 1 I searched and found out that I should use TRUNCATE TABLE. But this command fails ...
1
vote
3answers
16 views

I try to use select union with order by and now i can select it but it seem like it not order by date with what i expect why?

SELECT * FROM ( SELECT sale.NAME AS "Name" ,sale.responsible AS "Responsible:data:200" ,sale.action_type AS "Action Type:data:200" ,sale.id_customer AS "Id_Customer" ...
0
votes
0answers
24 views

MySQL 5.7 JSON_EXTRACT: how to remove string quotes?

Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes? Or should this question ...