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

How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?

I want to cache sqlalchemy,i have discovered that using keyword SQL_CACHE in mysql can be helpful in caching queries on demand. But how do i do it in sqlalchemy ? Is it possible ?
1
vote
0answers
33 views

How do Locks Work in MySQL or MariaDB

There is a case which makes me a bit confused. As far as I understand deadlocks should appear if there are two query statements (including subqueries) in request (each transaction). So the ordering of ...
0
votes
0answers
13 views

Issue with fetching data from mysql according to recent timestamp ASC ordering

I am having trouble with mysql query to fetch the data according to the recent timestamp in ASC order. it means the recent updated one record will be first shown. The Query is grouping with the ...
1
vote
0answers
21 views

How to optimize query on MYSQL with multiple group by clause

I have a web application where users can view data of performance. There will be around 200 to 250 users. Each user will have like 100,000 rows of data per day. It is only table and here is ...
4
votes
2answers
2k views

How to enable queries logs in MariaDB

I'm running mysql Ver 15.1 Distrib 5.5.46-MariaDB in CentOS 6.7 server. I'm trying to get queries logged and I'm doing by redirecting log to files as shown below: #/etc/my.cnf.d/server.cnf # this is ...
1
vote
2answers
4k views

Optimize IN clause in where query with order by - MySQL

I am trying to optimize a query that using IN clause in WHERE to avoid file sorting. To make it easy , I created the following sample which shows the problem. Here is my query: SELECT * FROM `test` ...
3
votes
1answer
77 views

Insert data from multiple rows into multiple columns

I cannot find, after much searching, how this would be done. My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The ...
0
votes
4answers
35 views

Selecting data from another table using a foreign key

I have two tables A & B. Table A id | name | num Table B id | date | roll id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id ...
0
votes
1answer
37 views

Transaction Identifier - possible with MySQL?

I have quite complex code, which at the beginning starts transaction. After that several user queries are performed (more or less without my control) and at the end the transaction is committed if ...
0
votes
0answers
26 views

MariaDB: inner join searching for 1 in TINYINT column not working

Since yesterday I've been trying to make a query work. I have two tables: image where I store information about images and imagehit where I store information about how many times those images are ...
9
votes
3answers
12k views

Cache MySQL database in memory

I am having problems with a website with 600MB of MySQL database. The website is way too slow. I noticed the bigger the MySQL database gets, the slower it goes. When it was 5MB, the website was very ...
0
votes
1answer
204 views

Mysqldump without slow query

Situation: I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query. Question: ...
0
votes
1answer
192 views

indexable JSON datatype in MySQL/MariaDB

In PostgreSQL 9.4 there is a JSONB datatype which allows you to index on fields in the stored JSON objects. Does MySQL/MariaDB have an equivalent? I'm specifically interested in the MariaDB Galera ...
0
votes
1answer
436 views

How to automatically Reset Query Cache for improve table cache hit in MySQL?

I have a VPS running CentOS 6.4 and 2.5GB RAM, Intel(R) Xeon(R) CPU. Its MySQL version is Server version: 5.5.35-cell. I have two active wordpress sites and two inactive sites (inactive sites use for ...
0
votes
1answer
21 views

Unique identifiers for the same type of object in the same request

This question is about your opinion and common approach for relational databases. So please, do not vote me down. I really need to solve this problem.. I remove this question once I meet the answer. ...
3
votes
2answers
188 views

Database design of Room Booking system in a Hotel

Intro and System Description I am currently designing a Database for a Room Booking system in a Hotel. Client fills a Form / Request with following information about Room: amount of people in ...
0
votes
0answers
17 views

Better configuration for a In-Memory MySQL database?

I have a local MySQL database for the only purpose of execute some PHP tests. To improve the speed of the execution, I created the database in memory using the ENGINE=InnoDB statement in every table ...
2
votes
2answers
144 views

MySQL Escape Characters

I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP. This is an example: The name to ...
0
votes
1answer
9 views

Incremental column depening on column value

I would like to have a column which has an incremental integer depending on the column value after sorting the table. E.g. after sorting by state: Nr | State | Counter ...
0
votes
1answer
122 views

How to group records in mysql query

I have a table (user_access) with the following columns in mysql database id int user int access_time datetime I am trying to run a query that gives me the number of times users accesses ...
15
votes
4answers
111k views

MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

I use the root account created the account 'a'@'%'. But I can't use the account to connect to MySQL server when I specify the host parameter. I can successfully connect without the -h parameter. ...
0
votes
2answers
25 views

mysql mutual friends and friends you may know

This is my Friends Relationship Database Design: INSERT INTO `relationship` (`user_one_id`, `user_two_id`, `status`, `action_user_id`) VALUES (1, 2, 0, 1) Please how do I develop mutual ...
0
votes
0answers
5 views

Changing global variables in MySQL RDS instance

I have a MySQL RDS instance that I created. I need to set the net_write_timeout variable in the instance so it is available globally. Can this be done ?
0
votes
1answer
3 views

What are the minimal MySQL grants for the debian-sys-maint user?

What are the minimal privileges that debian-sys-maint@localhost needs for the mysql service to work correctly? This answer on ServerFault suggests granting RELOAD only, but this is incorrect, as it ...
0
votes
0answers
24 views

simple math with SELECT - no resulting row

Simple math with SQL don't give a result. There is written 1 row in set, but I don't see the result/row. MariaDB [(none)]> select 5242880 / 1024 / 1024 as current; 1 row in set (0.01 sec) ...
2
votes
2answers
5k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
0
votes
0answers
13 views

Is it safe to sync MySQL binlogs?

I am working on a strategy to replicate my MySQL binlogs to Amazon s3. I have looked into mysqlbinlog --remote-server but it seems like adding a file watcher on /var/log/mysql which triggers an aws ...
0
votes
1answer
22 views

Import large DB 300 GB from ubuntu command line [on hold]

My DB Size is 300 GB , I have 2 SQL Dump file , one is 100 GB and Other one 200 GB , When I import the database it is disconnected due to some problem. almost 90 GB data’s are imported from both the ...
2
votes
1answer
36 views

Mysql LOAD DATA INFILE 100,000 csv records perfect BUT 300,000 records - 0 rows inserted

The following query imports 100,000 perfectly, but larger files nothing happens. No errors, no rows loaded. Any clues you can offer are appreciated. I spent hours looking into max file and memory ...
0
votes
1answer
239 views

How to use explode mysql function in where condition with cakephp

I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 . Now my question is that how can i check if new posted payment method id is available in ...
1
vote
1answer
154 views

Sync MySQL database and Quickbooks Database

I use Quickbooks as an accounting software. I also have a MySQL database where I record data on orders, customers, inventory, employees and etc. Is there a way for me to sync the two databases? For ...
2
votes
1answer
33 views

Increase date by 1

I have a query that fetch whole year record and sums up the count.The query is as follows select count(*) as t, m.machine1 from sap_prod_data s left join sap_machine_speed m on s.mch_desc = ...
0
votes
1answer
40 views

Fetching data from 3 tables

Wanna fetch data from 3 tables by mysql query to display in feed. But stack. My query is "SELECT f.leaderId, f.followerId, c.ccId, c.ccTitle, c.ccCity, c.ccCountry, c.ccFlag, c.uId, ...
0
votes
0answers
13 views

Identify MySQL Connection timeout

I am just trying to identify when MySQL drops a connection . For this I initiated a connection to MySQL Port using nc with a timeout value of 7200. $ time nc -w 2 car-005 3306 N ...
6
votes
1answer
183 views

Single table with more columns vs multiple tables with fewer columns

What would be a better database design for a social network website? A single table with more columns and fewer rows, or multiple tables with fewer columns but more rows? For example: A user can post ...
1
vote
1answer
21 views

MySQL: SELECT and JOIN with not exists values?

How can i SELECT data from two tables using 'JOIN' operator, if some values with cat_idx and tar_id from categories (tar_id = 87) does not exists in time_discounts table. (I need to print '0' in this ...
1
vote
1answer
34 views

Different types of users in one table, How to add data off different users

I have 5 type of users: Super Admin Coordinator Supervisor Student External Examiner All of them have some common attributes like ID, name, email etc. I have inserted them in one table user ...
0
votes
2answers
10 views

How to set query_cache_type=2 on mac?

I want to enable query_cache_type and change its value to DEMAND. When i am doing it via terminal its giving this error:- ERROR 1651 (HY000): Query cache is disabled; restart the server with ...
2
votes
1answer
24 views

MySQL best performance settings [on hold]

I am going to do some performance tests on MySQL server as I want to evaluate how MySQL behaves in some situations. MySQL is the only (additional) software installed on my server (Windows Server ...
0
votes
1answer
197 views

What's the approach to figure out reason for mysql crash?

The database I'm using crashes a lot recently. And I can't find a decent way to dig into the issue. I looked into mysql error log, but don't know how to start. I know there was a "load data infile" ...
0
votes
2answers
165 views

How can delete an orphaned temporary table on Mysql Innodb?

I was issuing an Alter table statement and suddenly the server crashed, Now i can not run any alter table to that table because it shows me the error 1050 and it says that a table named #sql1504d ...
1
vote
1answer
2k views

MySQL SELECT query hangs - Opening Tables INNODB

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even ...
0
votes
1answer
105 views

stress-testing MySQL slave with master's queries

A MySQL host in a slave configuration is in the testing phase before being put into production as a primary server for a specific workload. To make an estimate about how it is going to perform, the ...
2
votes
1answer
164 views

Services don't have MySQL Server. How to run it?

I need to run MySQL Server on a new for me laptop. There's Windows 7. And MySQL have been installed on it. In order to verify this, I opened the Programs and Features window and saw it there: ...
2
votes
1answer
287 views

MySQL: Merge Same Database on Several Location into Single Location

I have registration form, which exactly same for several location. Each of them has REG column, which this field is unique for each row. Basically each data input have different REG number for it, ...
-1
votes
0answers
21 views

How to import multiple .csv files as a batch?

How can I load multiple .csv files to a remote MySQL database? For example I want to load all these CSV files C:\csvFiles\file1.csv C:\csvFiles\file2.csv C:\csvFiles\file3.csv C:\csvFiles\file4.csv ...
0
votes
0answers
27 views

One bitmask column vs multiple bit columns [duplicate]

I have many tables with a column storing a set of days of week. I used to use MySQL and data type "set" worked well. For example: [worker] id weekday_mask (data type: ...
1
vote
0answers
3 views

Ways to recover a corrupted Wordpress database?

As a result of increasing PHP_Memory Limit our wordpress data base has apparently crashed and been corrupted. Our host, Rackspace, attempted crash recovery but all attempts failed. What commands or ...
3
votes
1answer
57 views

Search between dates where all dates appear

I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate. Tables columns are: id house_id date status house_id + date are unique the closest I could ...
0
votes
1answer
66 views

How to optimize database for specific queries?

Hello I have built this queries thanks answers to my questions: How to get data from three different tables and How to show rows with 0 without any records in database But now I have performance ...