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
2answers
46 views

Should MySQL database be in dedicated server?

The application has about 10 databases. Both application and databases are in the same server since is client does not want any time delay that could possibly occur if the database is in a dedicated ...
0
votes
1answer
12 views

MySQL Proxy on Current User

I'm trying to grant permissions to the current user logged in depending on their role, but it doesn't seem possible to have a select statement at the end of the GRANT PROXY # TO # command The code I ...
-1
votes
1answer
23 views

systems hangs after specific time

I am having 2 applications (Java and PHP). Both are sharing same DB Server. After specific time, both systems stops working due to "Too many connections". I tried so many settings (increased ...
0
votes
1answer
47 views

mysql crashed, innodb fails to recover

On Mysql 5.0 on CentOS 5.11, mysqld service has crashed and fails to start. Adding innodb_force_recovery=3 in /etc/my.cnf, creates the following error, which repeats with all table names of database ...
2
votes
2answers
53 views

Migrate SQL Server backup to MySQL for a Wordpress site

I have a client with an old .Net site that used SQL Server. For lots of reasons they decided to switch to Wordpress. They had a bunch of news articles on that old site that they want turned into posts ...
0
votes
0answers
33 views

MySQL: Calculations and Triggers across multiple tables

I'm new to sql and I'm having trouble with a calculation involving two of my tables. I have a table, payroll, which looks something like this: CREATE TABLE payroll ( id INT(6) UNSIGNED AUTO_INCREMENT ...
0
votes
0answers
30 views

Mysql checksum for data only

I have some groups of two tables, each group table having the same structure. (dev & prod server) Their data will never change from user action, only from admin when a change occurs. I want to ...
0
votes
1answer
31 views

Writing a query to COUNT all the services involving a particular

Learning PHP and SQL on an online MOOC, this is a homework that I've got to do. I need to write a query in SQL to count all the services which involve a particular person in my database. The ...
3
votes
2answers
560 views

Which one is an ER diagram?

I have started to study ER diagrams. When I browse through the ER diagram tutorials, I found something like figure 1: Figure 1 I tried to create a sample ER Diagram in MySQL Workbench like: ...
0
votes
1answer
29 views

Storing revisions and relations between revised items in 1:N and N:N scenarios for audits

I have the several of the following situations in a MySQL schema that I design for a custom CRM/ERP solution. 1:N related tables SO and SO_ITEMS (SO is just one example I have several) that both ...
0
votes
0answers
58 views

Mysql failed to start, InnoDB Error: page log sequence number is in the future

I had issue in starting mysql after machine crash. Looking in mysqld.log, I found below errors: 160511 09:50:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 160511 9:50:40 ...
1
vote
1answer
41 views

varchar datatype accepting integer values

below is the test table created. As data type is set to varchar it shouldn’t accept int values. desc c\G *************************** 1. row *************************** Field: t Type: int(6) ...
1
vote
2answers
40 views

Insert and select the row autoincremented id

What I want to do is insert a row and than select its auto incremented id number, In a multhi-threaded enviroment. I know how to do it with a Stored Procedure. Will this line of code work: BEGIN ...
0
votes
1answer
28 views

How to use FULLTEXT in mysql innoDB?

Sorry, if my question is naive, but it has puzzled me, and cannot benchmark the performance. I want to use the newly introduced feature of innoDB for FULLTEXT index. I wonder what is the difference ...
0
votes
0answers
20 views

Loading Data from url

I am trying to create a plan for data load and wanted to bounce off best practice ideas and learn about amazon RDS. I have multiple url sources of data and a RDS MYSQL instance to store them. I want ...
0
votes
1answer
54 views

InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0. We decided to change ...
1
vote
2answers
23 views

MySQL stored procedure returns null

I wrote a simple procedure to modify the whole column to a set of meaningless values. However, when I tried call NewProc('tableName','colName') it returned 1064 - You have an error in your SQL ...
0
votes
1answer
35 views

table is — is full mysql

MySQL 5.7 on windows'12 server, table which I am getting error crossed 2 GB in size, server memory is 4 Gb, MySQL used about 90% of memory.. Now any insertion into this table causing table full Once ...
0
votes
0answers
37 views

Unable to change innodb_log_file_size in MariaDB Galera Cluster 10.0

I have a MariaDB Galera cluster (v. 10.0.19) with 3 nodes. I want to increase the innodb_log_file_size (i.e. the size of the InnoDB redo log files) on one of the nodes to match what it is on the other ...
0
votes
2answers
22 views

check when last password changed in my user db/table

i have radius billing system with mysql as DB. I want to know which user 'password' was changed in last 5 minutes So that I can fetch such user mobile number from the DB and send SMS Alert about the ...
0
votes
0answers
25 views

mysql duplicate key error during restoration through mysqldump data

I have a mysql database which is having one table for geo location and saves latitude and longitude values of users. The fields are as followed CREATE TABLE `USER_LOCATION` ( `LATITUDE` float NOT ...
1
vote
1answer
28 views

MySql LIMIT + JOIN is stopping data from being returned?

I originally posted this question on stackoverflow, but then remembered there's a stackexchange specifically for databases! I've got two tables and am trying to return all profiles that are assigned ...
4
votes
2answers
110 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
0
votes
0answers
31 views

Set private key password for mysql using ssl

2016-05-09T20:05:05.772747Z 0 [ERROR] SSL error: Unable to get private key from 'server-key.pem' 2016-05-09T20:05:05.772764Z 0 [Warning] Failed to set up SSL because of the following SSL library ...
0
votes
2answers
28 views

How to Add AFTER Delete Trigger in My case?

I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables 1) The Patients which are currently ...
0
votes
1answer
32 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
0
votes
0answers
15 views

Is MySQL Database Replication Single Path or Multi-Path?

I read here that NIC Bonding is only useful when the IO is multi-path. Is MySQL Database Replication single or multi-path?
0
votes
1answer
38 views

Database convert character_set_server latin1 to utf8

I am writing about a problem on the character encoding that i have on my site with Magento. In practice, the special characters do not take utf8 and gives me the classic non-coded characters: Ã. ...
-1
votes
0answers
11 views

Sequilize association for 2 foriegn key of same model [migrated]

I need to execute below query using sequelize SELECT * FROM haulerrfquotes LEFT JOIN quotes ON quotes.jobId = haulerrfquotes.jobId AND haulerrfquotes.haulerId = quotes.haulerId WHERE ...
0
votes
1answer
30 views

Mysql Query: how to get last login time of each ID through IN clause

How to get last login time of selected ID's. Table | user_id | lastlogin | 100 | 2016-03-05 | 110 | 2016-03-21 | 100 | 2016-03-29 | 100 | 2016-04-03 | 110 | ...
1
vote
2answers
55 views

MySQL 5.6 Aborted_connects increase without error log

MySQL server version: 5.6.26 Linux: Linux 2.6.32-431.11.7.el6.ucloud.x86_64 #1 SMP Wed Jan 7 23:42:27 CST 2015 x86_64 x86_64 x86_64 GNU/Linux Situation: Aborted_connects increase 5-10 every minutes ...
2
votes
1answer
70 views

MySQL running delete trigger on truncate

I have a table that has an on delete trigger that updates another table. THe table I'm trying to truncate has 130,000 rows. I have duplicate schema and data on my dev server as well as the client's ...
1
vote
1answer
30 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
0
votes
1answer
40 views

Is this the correct way of moving MySQL data directory, (on Centos)?

I need to move my data directory to a bigger drive on the same server. # stop mysql /etc/init.d/mysql stop # copy the data over cp -R -p /var/lib/mysql /home/mysql #edit my.cnf # and add a ...
1
vote
2answers
55 views

Calculate Growth rate

I'm trying to calculate purchase/profit growth rate in different time periods, This is the query i've come up with (View in SQLFiddle): Schema: CREATE TABLE `profit_log` ( `market_id` smallint(6) ...
0
votes
1answer
14 views

How to run Trigger only on certain updates

I have the following two tables: CREATE TABLE IF NOT EXISTS `Product` ( `ProductID` VARCHAR(16), `ProductName` TINYTEXT NOT NULL, `CategoryID` VARCHAR(3) REFERENCES Category (CategoryID) ...
0
votes
0answers
51 views

Query issue to get total

All of the post time save in DB Table in UTC timezone. I want to get total in PST timezone for today, tomorrow, this week and this month. Here is my SQL Query. Today Query: SELECT COUNT(*) FROM ...
0
votes
1answer
48 views

How to enrich event based data per row based on future events

I'm trying to figure out a not too terribly hacky way to solve the following issue. This data is currently available both in MySQL and RedShift, so a solution for either is fine though RedShift is ...
1
vote
0answers
17 views

Conceptual data model for warehousing objects [duplicate]

I'm student and new to databases. I must conceptualize a database model for warehousing video games using a relational database management system (MySQL). Every objects in the warehouse have common ...
2
votes
0answers
51 views

Why does score for mysql boolean match give different score?

I am attempting to use MySQL's fulltext search using Boolean match on multiple columns, with multiple search phrases. I cannot understand the score returned though. The score returned is typically an ...
0
votes
0answers
40 views

How to give a prefix to all columns of a JOINed table in MySQL?

I have a data schema like this: car id type -> ENUM('Tesla', 'AstonMartin', 'Bmw') common_attribute car_tesla car_id specific_attribute_a specific_attribute_b ...
2
votes
1answer
47 views

How to JOIN a table with a name that equals to a determined value in MySQL?

The model of the (PHP & MySQL based) Zend Framework 2 application I’m currently working on contains a inheritance similar to the one described here. To map this to the database I’m using the ...
1
vote
1answer
94 views

MYSQL refuses to set password for new user

I'm trying to create a MySQL user/password. And every time I create the user - mysql does not honor my password. It's just blank. mysql> select Host, User, password_expired, Password from ...
-1
votes
0answers
42 views

Inner Join two select statements

I try to apply inner join on two select statments. 1st Select fetches 8K records 2nd Select fetches 8.2k records When apply inner join it fetches 300K something records. I don't know whats wrong ...
0
votes
0answers
23 views

Specify what columns to display in a SHOW COLUMNS statement

Does MySQL offer any way to specify which columns to display in a SHOW COLUMNS FROM my_table statement? From this : Field Type Null Key Default Extra ------ ...
1
vote
0answers
22 views

hosting mysql server on the cloud or local network pc

I have a question on whether to host a mysql server on the cloud or a local network pc. In the current work environment all users log on to the cloud and work from the cloud. All the work ...
0
votes
3answers
33 views

Formatting out a column value - mm:ss.hh

I have a time in seconds and I want to convert it to the following formats possibilities: 121.17 -> 2:01.17 | 34.28 -> 34.28 | 133.56 -> 2:13.56 I am using the following: IF(Time > 60, ...
1
vote
0answers
15 views

MariaDB strange user privileges issue

Server version: 5.5.47-MariaDB-log MariaDB Server / CentOS Linux 7 x86_64 I have two users: user@% and user@localhost with the same password but apparently different privileges (some privileges the ...
0
votes
1answer
25 views

database design help with multiple table relationships

Been scratching my head on how to set this up and looking for your help. Trying to create an API and not 100% how my tables should relate to one another. So lets say I have users, food, and food ...
0
votes
0answers
30 views

MySQL server refuses remote connection when under high load

When my MySQL server is under high load from the local machine it doesn't accept any remote connections. When load is normal it connects without any issues, but under high load neither a MySQL nor ...