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

0
votes
0answers
10 views

MySQL Workbench Synchronization False Positives

I have synchronized a table between a model and a live server database in Workbench, but every time I rerun the "Synchronize With Any Source..." wizard, Workbench detects (falsely) that there is a ...
2
votes
1answer
33 views

How can I implement a viewed system for my website's posts?

Here is my current structure: // posts +----+--------+----------+-----------+------------+ | id | title | content | author_id | date_time | +----+--------+----------+-----------+------------+ | 1 ...
0
votes
1answer
46 views

How to organize MySQL DB to allow an arbitrary counter for clicks, visits, etc in past 24 hours?

If you're familiar with it, I used to use count.io which has been permanently taken down. I basically want to create my own version for my own use. Main question How do I structure/organize my ...
1
vote
0answers
45 views

How to reference Foreign Key(s) to the parent table where data goes on increasing?

Hello i am very new to learning PHP and MySQL. I have just begun learning MySQL. As the question mentioned above, will be kind if anyone can give a solution. Please see the example of a simple ...
2
votes
1answer
38 views

Using GROUP BY WITH ROLLUP on column that contains NULL

I have the following table (see it on SQL Fiddle) (I created to break down my problem): | ID | word | ---------------- | 5 | "Hello" | | 6 | NULL | | 7 | "World" | | 8 | "World" | Now I ...
-3
votes
0answers
17 views

MySQL Password Lost [duplicate]

I have set a 50 character password for my database in MySQL. I stored the password in a file which is lost. Now I have forgotten the password. How do I recover the password? Is it possible? If yes, ...
3
votes
1answer
48 views

User variable in nested subquery not getting picked up - in complex sort of 2 tables with gaps

I have just recently learned about user variables (ie, @myvar) and am trying to create a complex sorting query that takes 2 tables where the second is missing a column which then gets filled in by a ...
1
vote
0answers
40 views

MySQL 5.5 Continuously getting crashed

I have MySQL 5.5.44 running in RHEL server. It is a standalone server used for testing and offlate not much used. The instance is currently down. Hence, I tried to start the service. It got started ...
1
vote
1answer
38 views

Using a subquery in place of a SELECT fieldname …or how to achieve the same result?

My goal is to get a collection of ID's from Table 1 based on a query, a collection of ID's from Table 2 based on a query, and insert a new record into Table 3 for each permutation. Basically, this: ...
0
votes
1answer
31 views

Cumulative Subtraction from external input

I have an external value 20. I need 1 to be subtracted from 20 (=> 19) and then 2 to be subtracted from 19 (=> 17) and so on. Data: Serial No | Value1 | External value(20) A | 1 | ...
1
vote
2answers
24 views

Schema design for metered billing depending on account usage

Sorry if this has been asked somewhere else, but so far I haven't found any questions like this, or can't find the words to search for. TL;DR: What's the best way to implement counters in the ...
1
vote
1answer
82 views

Query for a month works faster then for a week

I have strange situation when query for a month works faster then for a week: There is a request for a month (2016-06-01 - 2016-07-08) that works quickly and uses proper index: SELECT `t1`.* FROM `...
0
votes
1answer
21 views

Installing time zone data on MariaDB 10.0

Yesterday, I set the time zone on our DB server (as explained here). Doing so required using the mysql_tzinfo_to_sql executable. Fortunately, this already existed on the server, and conversion of ...
2
votes
1answer
58 views

Multiple column partitioning

I have a very large table that needs to be partitioned by two columns. One column is an ID and partitioning is simple enough. The second partitioning should be done daily on the date column. I need to ...
0
votes
1answer
35 views

MyBB forum MySql Internal Error :1366

I'm using MyBB forum on my Ubuntu 16.04 LTS server, using LAMP, mySQL Improved to be specific. When I try to install a plugin (by putting it in the mybb plugin folder) and hit install plugin in my ...
0
votes
1answer
20 views

Transfer Data from One Table to Another or else Update the Data

Good Afternoon. I have two Tables and they are Table: GeneralInventory WareHouseID | ItemCode | Qty Null Null Null Note: as of now it is empty and Table: Receiving RINo ...
1
vote
1answer
47 views

three-table JOIN query on a MySQL Cluster has become unusably slow

I have a select query called by a script to our MySQL Cluster that has gone from running in a second or two to taking well over a minute per query (and it has to run this query 24000 times). Nothing ...
0
votes
0answers
18 views

ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist

I am using cmd. CREATE OR REPLACE VIEW trips_flights_airports (trip_id,booking_reference,flight,flight2,dep_date,weight_restriction,size_restriction, type_restriction,travellr_id,cost,status,...
0
votes
1answer
39 views

store procedure variable storage takes long time to execute

i have a store procedure This procedure take a endless time for execution BEGIN set @my_user_id=null; select user_id into @my_user_id from office_user where username like my_username ...
0
votes
0answers
14 views

WSREP: turning message relay requesting on, nonlive peers

i am getting "WSREP: turning message relay requesting on, nonlive peers" error in my percona xtradb cluster(3 nodes) node error logs. i am using 5.6.24-25.11.1 version on centos 5. My questions are ...
0
votes
2answers
37 views

How to not replicate index creation on ignored tables in MySQL/MariaDB

I have a setup with two MariaDB masters, each being the slave of the other. Some tables are ignored, with the configuration option : replicate-ignore-table = moodle.mdl_temp_% My problem is when a ...
0
votes
1answer
22 views

How to get list of dates between From Date and To Date in MySQL [closed]

I have a table Emp_UnAvailablity_ID`, `PersonID`, `Is_UnAvailable`, `From_Date`, `To_Date` FROM `emp_unavailablity. I want to get list of all dates between From_Date and To_Date. I have tried this ...
0
votes
1answer
22 views

Merge sub-queries to a single query

I am trying to merge the nested MYSQL queries into one First of all I fire this query select names , city , category from names_data group by names , city ,category ; this produces a result of ~...
1
vote
1answer
24 views

Get relation data on comma separated values

I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table. I have one table "Form table" +------+----------...
5
votes
3answers
105 views

Normalize repeated indefinite ongoing dates for an event

I am designing a calendar database that has a lot of booking and billing information. The database itself consists of form records, which are editable, and only when an event is confirmed by staff it ...
0
votes
0answers
17 views

update data to table from another table and avoiding null values

i have two tables in mysql. Tbl1 - has product_id and tax_id fields. Tbl2 - has product_id and tax_id_w fields. Tbl1 needs to be updated (only values for tax_id field) from Tbl2 values of tax_id_w ...
0
votes
0answers
37 views

MySQL query much slower after conversion from latin1/myisam to utf-8/innodb

I converted one table from latin1 to utf-8 and changed the engine from MyISAM to InnoDB. After that conversion the same select query is 6x times slower than before. I converted the table with the ...
2
votes
1answer
49 views

How to recover a lost password in MySQL 5.7.11? (for Windows)

I am running MySQL 5.7.11 on Windows 10 I am unable to login to root through the command line client since I lost my password. How do I change/reset the old password? Please help, and thanks in ...
0
votes
1answer
48 views

MySQL2::Error: BLOB/TEXT column can't have a default value

I am trying to run a rails mysql migration on Amazon Elastic Beanstalk. It fails with this error message: Mysql2::Error: BLOB/TEXT column 'description' can't have a default value: ALTER TABLE ...
0
votes
0answers
44 views

MySQL server keeps crashing on startup

MySQL keeps crashing on start with the following message. It crashed after I was trying to batch upload data into a table and now it can't start since. This is an already existing database so it ...
0
votes
2answers
28 views

SELECT multiple times based on changed dates

I'm trying to select different types of data depending on a payment method which is a simple intersection table with columns "id" (INT PK) , "customer_id" (INT FK), "payment_method_id" (INT FK) and "...
0
votes
1answer
79 views

Is it beneficial to include ORDER BY fields in the list of SELECT fields? [closed]

I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in ...
0
votes
0answers
16 views

Need to run mysql script from monit

I wrote a shell script for mysql alerts which need to run under monit. The script is as below: #! /bin/bash -x tmp="/root/$$.txt" emailid="xyz@xyz.com" subject="Running Processes" echo "Running ...
0
votes
2answers
66 views

MySQL: LEFT JOIN not working as expected

I have 2 tables: Employees, attendanceIn When I query using LEFT JOIN SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees` LEFT JOIN attendanceIn ON employees....
0
votes
0answers
15 views

Check if MySQL enabled ssl without logging in

Is it possible to check if a MySQL server is SSL enabled without logging in, assuming we have port and IP?
0
votes
1answer
43 views

MySQL - Persistent values in my.cnf

I have a small doubt regarding making variables persistent in Percona Server 5.6 in Linux. I need to make log-bin-trust-function-creators persistent so that I need not make it to true everytime on ...
0
votes
2answers
40 views

Left Join 3 tables into ONE in Mysql

I have 3 tables: Employees, attendanceIn table, attendanceOut And My query is SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn, attendanceOut.attOutAS attout ...
0
votes
0answers
16 views

sum price of childs in other table mysql

i have two table one store data child and parent hierarchy and other paths and descendant +----------+------------+-----------+ | userid | parent | price | +----------+------------+--------...
0
votes
2answers
27 views

COUNTING results from self-referenced table recursively

I have a table called Clients. And at this table, I have an idFather to know who added this row to the table. Sample: id | user | idFather | createdAt ---+---------+----------+----------- 01 | ...
3
votes
5answers
983 views

When should I mark a column as the primary key?

I have a table like this: // cookies +----+---------+-------------------+------------+ | id | user_id | token | expire | +----+---------+-------------------+------------+ | 1 | 32423 ...
0
votes
1answer
26 views

Optimize finding events with overbooked attendees

I have the following strcture to store events and their attendees: CREATE TABLE `plan_event` ( `id` int(11) NOT NULL AUTO_INCREMENT, `start_datetime` datetime DEFAULT NULL, `end_datetime` ...
0
votes
1answer
36 views

Order by reduces performance of a grouped result set

I'm trying to optimize a query which uses a combination of GROUP BY and ORDER BY which together cause a big performance hit (~x15). If I remove either of the conditions, the query runs pretty fast. I ...
1
vote
1answer
26 views

Efficient way on updating multiple tags or categories

What's the efficient or best way on updating a multiple tags or categories? Currently I have posts and posts_meta tables. I have this structure on posts_meta table: +----+---------+----------+------...
0
votes
0answers
18 views

Write sql query to store alarm details for next 1 week with given user id [migrated]

I am making a scheduler where currently I am saving details for reminder set by client with specific user id. Now I want to add a button of Repeat weekly where the alarm will be repeated for next 7 ...
0
votes
3answers
29 views

Seperate Address Table for Patient and Emergency Contact Person?

Is it good to separate address table for patient and emergency contact person? Currently this are my tables Patients id first_name last_name status gender PatientAddresses id patient_id ...
0
votes
2answers
27 views

Storing email,phone,fax and url in same table

Is it ok to store email,phone,fax and url in same table? I want user to be able to add multiple email,phone,fax or website url on their profile. Here's my current table Contacts id user_id val (...
0
votes
3answers
49 views

MySQL accent insensitive word search

After spending almost all of my day searching a fix to this I come to you guys. I need to do an accent insensitive word search on my db(InnoDB), but I cannot get it to work. I have t1 table, the ...
0
votes
2answers
68 views

What happens when I reach the last id with MySQL InnoDB?

So perhaps I don't know the technical name of the 'id' column which has autoincrement so if a similar question exists, I cannot find it. I was wondering about the limit of the number of rows in an ...
1
vote
1answer
30 views

Incorrect key file for table error. How can I change location of MySQL's temp folder to another part of drive with more disk space?

I am using MySQL with Sequel Pro on a Mac Pro OSX Mavericks and am trying to run the code for a stored procedure in order to add two new columns to a table. The procedure does math operations on ...
0
votes
1answer
35 views

Windows MySQL 5.5 Cannot connect to localhost: ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

I can ping successfully from my Windows command line to localhost and to 127.0.0.1, but attempts to connect with mysql.exe fail... I found similar errors but their suggested solutions don't work/...