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
34 views

Cannot add foreign key constraint'?

Can someone please correct this code for me? When I click enter it says 'cannot add foreign key constraint'? CREATE TABLE entries ( Subject_id smallint auto_increment, Student_id VARCHAR(45) ...
3
votes
1answer
58 views

Simple database structure advice

Newbie to dba. I'm creating a php/mysql inventory list and would like some advice before I start, on structuring my table(s) and database. The inventory database will eventually contain hundreds of ...
0
votes
1answer
20 views

MYSQL after update trigger

I am new to database trigger. I really have no idea why i cant update the Label if the Quantity more than equal to 1000 CREATE TRIGGER TRG_UPDATE_STATUS AFTER UPDATE ON product FOR EACH ROW BEGIN ...
0
votes
0answers
15 views

How to lock tables in a stored program if I can’t say LOCK TABLES? (in an ancient version of MySql)

MySQL does not allow LOCK TABLES in stored programs. But this is my problem: I have a large stored function to control “phases”. The function advances to next phase, if needed (rarely so), and ...
0
votes
0answers
20 views

Is the Aria storage engine really crash safe?

I maintain a PHP/MySql app for a big customer. Many of the tables were on MyISAM b/c that was the default when I started. On a newer server running MariaDB, some of those tables kept crashing. ...
6
votes
1answer
58 views

Is it useful to split a table into a frequently used and a archive table?

I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom. Is it useful to split this table into a frequently used table ...
0
votes
1answer
14 views

MySQLi Tutorials [closed]

I have just finished building my site, and now it's time to finally put a database in it and actually connect it to a database. So far I have downloaded Xampp and installed everything and tested it. ...
3
votes
2answers
41 views

Idempotent user and database creation script with MySQL

I have written a shell script which creates a database and user on a MySQL server. The user and database are to be used by a web application and generally this task only be performed once per website ...
0
votes
1answer
13 views

aggregate on join latest value

This is a problem I often get stuck in and I hope there is a solution. Let's build an example: Table products: product_id, product_name Table Prices: product_id, valid_since, Price Lets say I ...
0
votes
2answers
18 views

how to insert all the data to table1 from another table2, if table1 has some extra columns than table2 ? It needs to be done in mysql

i want to use insert into select * to insert data into table1 from table2 but i have some extra columns added to table1. These columns are distributed around the table like 2 are at top and 2 are at ...
1
vote
2answers
54 views

Get sum of amount monthly

I do not know English, sorry :( example table: CREATE TABLE IF NOT EXISTS `__example__` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `amount` decimal(10,2) DEFAULT NULL, `fromDate` datetime DEFAULT ...
1
vote
1answer
56 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
0
votes
1answer
13 views

Sharing Databases and Tables between users in MySQL

I want to know how it is possible to share a database tables between multiple users/user groups, I mean, SQL Server has a security feature that we can create user groups and assign users to them, then ...
0
votes
0answers
18 views

MySQL replication Got fatal error 1236

I am getting this error when starting a new slave replication from master. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded ...
3
votes
1answer
51 views

MySQL replication - invalidating query cache entries (table)

We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of: invalidating ...
0
votes
2answers
26 views

Commands out of sync for no apparent reason

I have a simple table and a stored routine to handle phases. CREATE TABLE faza ( numer INT PRIMARY KEY, czasUstawienia DATE )// CREATE FUNCTION obecnaFaza() RETURNS INT NOT DETERMINISTIC BEGIN ...
1
vote
1answer
22 views

Same statement yields NULL when in a stored function, and non-NULL valid result otherwise

I have the following stored function for calculating ELO ranking: CREATE FUNCTION spodziewanyWynik(mój INT, przeciwnika INT) RETURNS DOUBLE DETERMINISTIC BEGIN DECLARE wynik DOUBLE; SET wynik = ...
0
votes
1answer
32 views

Accidentally executed reset master on the live server

Turns out I have called RESET MASTER; accidentally on the live server. The slaves stopped replication now because they can not find the expected bin log files on the master. Is there a way to continue ...
1
vote
1answer
37 views

How to get overall rank based on data regardless of scope (all items, user specific, etc.)?

Table schemas: servers - id - user_id - game_id - slug ... server_votes - id - server_id - ip_address - created_at ... So, I have a two tables: servers and server_votes, and I want to get the ...
0
votes
2answers
33 views

MySql not using Buffer Pool

I'm checking into a buffer pool issue on a MySql database. According to the query to get database size shown here, the total database size is about 11GB. Unfortunately I know I have much less RAM than ...
0
votes
3answers
44 views

MySQL: can I put the database into a maintenance mode? (i.e. no user connections)

I'm going to be doing some complicated maintenance on our MySQL servers and I'd like to keep users out during the work. I'd prefer not to drop and re-create all of the user accounts, but that's the ...
0
votes
1answer
32 views

MySql Workbench Data Import .csv

Hi I am trying to use to MySql Workbench Data Import facility to import a .csv file (thousands of records). However it stops importing the first 5 records. After studying the .csv file I could see ...
2
votes
1answer
94 views

Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5

I try to join 3 tables (products, prices, categories) together to get a result of latest & lowest prices from the joined tables. I was able to get the expected result on MySQL 5.5, but after ...
0
votes
1answer
33 views

Mysql Slow Log when using UNION ALL?

I have following query coming up in mysql-slow log, however i am not sure how to optimized this query and make it much faster. Any ideas on this ? I have already used indexes in following in the user ...
1
vote
1answer
50 views

Slow query performance on large table with no unique keys

I have a table that has approximately 20 million records and I'm having a very hard time improving the performance of the following query. It's taking well over a minute to run. SELECT ...
1
vote
0answers
15 views

Minimizing downtime during MariaDB Replication Setup

I have the task of setting up MariaDB (10.0) replication from a master running in a datacenter to a slave running out in an AWS VPC. Reading up on the instructions on Setting Up Replication - MariaDB ...
0
votes
2answers
22 views

Delete duplicate with same ID PHP/PDO/MYSQL

I'm new to databases and I need your help I'm working on a website and I have few problems. I'm using PDO. There is a user table, with unique ID, name and password. User enters his username and ...
0
votes
1answer
20 views

Lost connection to mysql while restoring dump. Is it safe to resume?

I was in the process of restoring an sql dump to a newly created RDS mysql instance on AWS. The dump was created with the following command: mysqldump --all-databases \ --single-transaction \ ...
0
votes
1answer
18 views

What to do when I get “mysqld dead but subsys locked” in my amazon ec2 micro instance?

I have an Amazon ec2 micro instance that holds a WordPress site. Since yesterday, my mysql db keep crashing saying: [ec2-user@ip-xxx-xx-xx-xxx ~]$ sudo service mysqld status mysqld dead but subsys ...
0
votes
1answer
33 views

can't update table in stored function/trigger because [on hold]

I have referred to many questions, like here and also created a question on SO(No response there), but am not able to understand why it's happening in my case as am not even doing anything in the ...
0
votes
1answer
41 views

Add ranking to query output

I'm new at programming with PHP/mySQL and I have got a little problem here. This is how my code looks like: SELECT COUNT(*) AS Number , Name FROM table GROUP BY Name ORDER BY Number DESC ...
4
votes
2answers
136 views

MySQL: sum time ranges exluding overlapping ones

I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office: Query has to: Exclude overlapping ranges (8:00 - 10:00) Exclude ...
4
votes
2answers
58 views

MySQL - Order by closest value on multiple columns

I have a table for a game lobby. In this lobby a user gets put in their connection and their level. In my SQL statement I am getting all the users in the lobby which are between +5/-5 of the user's ...
1
vote
2answers
42 views

SELECT…FOR UPDATE - Will only an UPDATE release the lock?

I'm using a SELECT ...FOR UPDATE in a Java PreparedStatement. I may or may not need to execute the update depending on the results of the SELECT. If I execute a rs.close(); or pStmt.close(); without ...
0
votes
0answers
26 views

using zookeeper for store states for fabric on fabric death [closed]

I am using fabric and want ha for ht fabric node. I have fabric script and the fabric mysql DB that stores the states. I also use python fabric aware connector. Rather than going the route of ...
1
vote
1answer
30 views

Why is MySQL choosing that index?

I have a large indexed table with over 35 million rows. Here are the indexes, with a naming convention that shows their sequential column order: IDX_E93438363DAE128B, ...
0
votes
2answers
35 views

Pull entire row per user based on MAX(Column) MySQL

I am trying to pull whole record from database for the highest "Score" column per user. TestTable structure ------------------ ID | Name | Score| ------------------ 1 | john | 10 2 | mark | 20 3 ...
1
vote
2answers
16 views

How to get readonly access to mysql files via g+w permission 750 or 640?

The data-dir itself has permissions 700 thereby limiting access to the mysql user. I want other folks who I add to the mysql group to be able to read binlogs and download the *.frm, *.MYI, *.MYD ...
0
votes
1answer
32 views

mysqldump collation issues

I have tried my hand at building a shell script to backup our wordpress database using tar balls and chron jobs on a Debian environment. The problem I am facing now is that our wp database after ...
0
votes
1answer
15 views

Mysql Replication backup and restoration failed multipal time

I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason. I have 2 host Master & Slave with below details. Server1_Master os-centos6.7 mysql- ...
0
votes
2answers
40 views

Pull entire row based on MAX(Column) MySQL

I am trying to pull whole record from database for the highest "Score" column. TestTable structure ------------------ ID | Name | Score| ------------------ 1 | john | 10 2 | mark | 20 3 | ted | ...
2
votes
1answer
25 views

Can't access MySQL database with created user through dll

I have a web service that connects to a MySQL database from a dll, both of them are on the same machine. If I use the root user and password it will connect no problem but if I use a user that I ...
1
vote
1answer
46 views

Prepare MySQL for big UPDATE query on production server

Surprisingly I have not found anything relevant on this subject. What are best practices to prepare production MySQL server for a big update query? The question is broad but I'd like to know details ...
1
vote
2answers
22 views

Fulltext searching on shared hosting - 2000 Entires with around 500 words each

I'm fairly new to db administration and would appreciate some help. I want to host a site which requires full text search on around 2000 items, each with around 500 words each. I need this to be ...
2
votes
1answer
27 views

How to compare MySQL privileges granted to privileges used?

I have inherited a MySQL server that has a user with far too many privileges granted but I believe not all of these are required. Is there any way to compare the MySQL privileges granted to the MySQL ...
2
votes
2answers
37 views

Set variable with dynamic name in trigger (MySQL)

I need to create some kind of generic trigger that will alter value of columns which names are stored in another table. So basically I wonder if is possible to do something like this: delimiter ;; ...
1
vote
1answer
57 views

Select ONE most recent post for each author

I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry. I use recent versions of PostgreSQL and MySQL. I have 2 tables: CREATE ...
1
vote
1answer
45 views

Can't start mysql on Ubuntu 14.04

When I try to start sudo service mysql start start: Job failed to start Then I look the /var/log/mysql/error.log 160122 8:43:42 [Warning] Using unique option prefix myisam-recover instead of ...
0
votes
1answer
15 views

Fabric - Operation CREATE USER failed for 'fabric_server'@'%''

I am finding the use of fabric to be tottalyh unstable. Here is what I do and the below is the result: mysqlfabric group add druid 192.34.59.181:3306 mysqlfabric group add druid 192.34.56.66:3306 ...
0
votes
0answers
20 views

mysql returns FUNCTION .LAST_INSERT_ID does not exist

I'm new to MySQL. I have created two tables. create table Shopping( Trans_ID int not null auto_increment primary key, date date, item varchar, quantity double, rate ...