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

4
votes
2answers
109 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
30 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
37 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
50 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
54 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
13 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
91 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
13 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 ...
0
votes
1answer
30 views

Why does phpMyAdmin execute all queries with an apostrophe?

For example, if I use the GUI to perform an INSERT, the console will log the following execution: Why does each item have apostrophes (aside from NULL)? I know that I can do a literal INSERT via ...
0
votes
0answers
26 views

how to fixed this mysql 5.5 crash?

[root@DB-Server-master bin]# mysqld_safe --user=mysql 160429 12:10:55 mysqld_safe Logging to '/data/mysql/data/log/mysql-error.log'. 160429 12:10:55 mysqld_safe Starting mysqld daemon with databases ...
1
vote
1answer
51 views

How can I better model the relationships in this project?

I'm developing an internal tool (as an intern). We deal with a lot of hardware, which is all custom and managers have a hard time knowing who is developing on what machine. The three main things to ...
0
votes
1answer
43 views

How to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;?

When I use mysqldump to export mysql database, it always produce a dump.sql containing ...some other things... /*!40101 SET character_set_client = utf8*/; ...some other things... This is the ...
0
votes
1answer
44 views

Cant initialize MySQL. “mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended”

I am doing a fresh install of MySQL on Fedora 22. After I run mysql_install_db I try to start the daemon using mysqld_safe but it does not work. The system says: mysqld_safe Logging to ...
-3
votes
2answers
27 views

pick data from multiple tables

I am new to MySQL. My question is how to make a filter like amazon product. I made a matrimony site with filter. I wrote a query to access data from multiple tables. I give foreign key to another ...
0
votes
1answer
12 views

Could not connect to the specific instance. MySQL error number 1045. Access denied for user 'root'@'my public ip' (using password:YES)

I was using MySQL query Browser in my system and type the IP address of the remote Server and log in. I tried to create a store procedure and scheduled an event to call the procedure every two ...
0
votes
0answers
50 views

MySql Error : Database page corruption on disk

Recently Ive setup a xampp in my store location. on Windows 7 32 bit, 2GB RAM - Pentium PC. (XAMPP) xampp-win32-5.5.19-0-VC11 PHP application is Point of Sale. With latest mysql server and PHP 5.5. ...
1
vote
1answer
30 views

MySQL datetime to just date

Currently I have a table containing a column of type datetime. I have no need for the use of the HH:MM:SS that trail the variables stored in this column. I just need the YYYY-MM-DD. Is there any way ...
0
votes
1answer
27 views

MySQL 5.6 performance_schema not showing queries from application

I'm seeing a strange problem with perfromance_schema... it seems queries are not getting logged as I expect them to be. For example, this is what I get: mysql> SELECT `SCHEMA_NAME`, `DIGEST_TEXT`, ...
1
vote
1answer
39 views

Justified multi-value field in MySQL?

I have a table of data that contains 10 point values based on a score for an event (0 - 9 points). If a time is within the range, the point is awarded. There are 20 possible events. Is this a valid ...
-4
votes
0answers
51 views

Query for columns

I need help writing a query. I have a table named "schedule", with columns "start time" and "end time", both with the time data type. Student have today discipline "Networks" from 11:00 (in start ...
0
votes
0answers
36 views

What can cause the the table cache in MySQL to consume much memory?

I have a server with a table_open_cache of ~2000, and a query cache of ~128M. We have ~350 InnoDB (no MyISAM) tables; innodb_file_per_table is disabled; there is no partitioning; MySQL is 5.6. After ...
0
votes
1answer
30 views

error 1146 when importing mysql DB

I'm trying tu dump and re-import a database, I'm using the following bash script to export (I have reasons to export each table separately) for x in `mysql --skip-column-names -h [host] -u [user] ...
0
votes
1answer
20 views

need to join Vertical row as per values in Horizontal rows

TABLE 1 +----+--------+-----+ | id | userId |state| +----+--------+-----+ | 1 | 1 |AZ | | 2 | 1 |AK | | 4 | 1 |AL | | 5 | 1 |CO | | 6 | 1 |CA | | 7 | 2 ...
0
votes
1answer
58 views

MariaDB - cannot set max_connections and wait_timeout through my.cnf

I am struggling to set max_connections and wait_timeout parameters in /etc/my.cnf but MariaDB does not seem to read the parameters from the file (it reads some of the others, I haven't checked all). ...
0
votes
0answers
17 views

Completely reset innodb logs and files

So long story short, we've been dealing with a corrupt innodb installation on a MariaDB 10.0 server after a power outage, specifically a log in the future error. Rather than try and fake the log ...
0
votes
1answer
57 views

Slow search by LIKE and other values (1 million rows+)

I'm currently developing a kind of search website where one can search for business companies in my country. The database table has 1 million rows and quite a few colums (42 columns). But I find it ...
0
votes
2answers
26 views

Complex MySQL query through relational tables

I am generating a query that returns a set of profiles that meet all the conditions posted below I am receiving the data like so in Ruby, then making a dynamic MySQL query based on the contents - ...
1
vote
0answers
29 views

MySQL/JDBC: Can a commit on server get missed on client?

I am wondering about when conducting a transaction of one or two updates/inserts from a JDBC client to MySQL Server over the network... In the event of losing the network during the commit, can I get ...
2
votes
3answers
110 views

Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

Goal: select 10 most recent rows from posts which are related to a specific user. There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows). posts columns: ...
0
votes
0answers
12 views

How to use column result as column name with MySQL [duplicate]

At the moment I have sample table as such doc_name | processed_date | doc_version ---------------------------------------- doc_1 | 2015-01-01 | 1.0 doc_1 | 2015-01-02 | 1.1 doc_1 | ...