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
1answer
953 views

mysql cluster table full error

I installed a new mysql cluster on windows (for evaluation). I created a table using command below. After I insert just over 1 million records into the table it gives me the "table datadump is full" ...
3
votes
1answer
81 views
+100

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 ...
1
vote
1answer
13 views

Get table sizes without SELECT permission

I want a scheduler to run a simple SQL script to collect the sizes of tables in our MySQL database: SELECT table_name, data_length, index_length FROM information_schema.TABLES The user the ...
0
votes
1answer
201 views

How to convert database mysql4 to mysql5

I have mysql dump file named booster.dump. It was export from 4.0.27-standard mysql server. Now I want to convert this database to mysql 5.1.73-cll. How can I do it? I search about this topic in ...
2
votes
1answer
241 views

InnoDB internals - checkpoint, LSN, dirty pages

I'm looking into InnoDB internals regarding buffer pool, checkpoint and dirtypage. However there isn't enough information to answer my question. When I run show global status, I can get ...
0
votes
0answers
19 views

MySQL - design help for scale

I will be using Amazon Web Services RDS service to host a MySQL 5.7 server (Currently AWS do not support 5.7, but they have announced they are working on implementing this version and it will be out ...
2
votes
2answers
145 views

Find all users who rated all the movies rated by a specific user

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
1
vote
1answer
266 views

Configuring slave from EC2 master to Amazon RDS slave

I am configuring replication from EC2 master to Amazon RDS instance. After starting the slave, I don't see any errors but I see slave_IO: _thread is connecting. Master version:5.6.23 Slave ...
4
votes
1answer
50 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
30 views

SQL Query; Admin Log System; Foreign Keys; Retriving multiple rows form different tables at once [duplicate]

Table A (users) id | name | ip 52433 Yashas 1.1.1.1 512 Admin 5.6.7.8 Table B (admin_record) id | admin | time | date | reason | action 52433 512 2312352 ...
4
votes
1answer
9k views

How do you tell which host is close to being blocked in MySQL?

I have a two part question: In cases were this error comes up, is there a way to see if a host is close to being blocked? And fire off an alert? PDOException: SQLSTATE[HY000] [1129] Host `hostname` ...
3
votes
4answers
4k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
1
vote
1answer
44 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 ...
1
vote
1answer
37 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 ...
1
vote
1answer
473 views

MySQL: SELECT GET_LOCK slow/timeout

I am using "Zebra Session" as a replacement for PHP's built-in session management. Zebra Session stores sessions in a MySQL database, and uses "session locking" in order to ensure that data is ...
6
votes
5answers
5k views

How to find dependencies on a table in mysql 5.0?

I am trying to drop a table in MySQL but its complaining about "Cannot delete or update a parent row: a foreign key constraint fails"... Questions Is there a way to find out all the table ...
1
vote
2answers
108 views

ALTER DATABASE script preventing restore of MySQL database in another server

While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines. /*!50003 DROP FUNCTION IF EXISTS `f_my_function` */; ALTER ...
0
votes
0answers
16 views

MySQL 5.6 Server start fails: InnoDB Operating system error number 23

I use a local MySQL 5.6 Server (InnoDB) on a Windows 7 machine to store several schemas of a research project. Until yesterday the system ran without problems. During a query, MySQL Workbench ...
0
votes
1answer
246 views

How to calculate MySQL Transactions per second

How can I calculate MySQL Transactions per second. I tried to use formula (global com_commit+global com_rollback)/uptime since flush. I had also tried (global insert+ global delete+ global ...
1
vote
0answers
14 views

Cardinality of an index in a MySQL table is equal to table's row size

I have just included an index to a MyISAM table and the cardinality appears to be 1 million something which is exactly the number of rows in the table. In fact it needed to be 315 because distinct ...
0
votes
0answers
22 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=MEMORY statement in every table ...
0
votes
1answer
184 views

Is there any relation between BSON storage for mongoDB and MySQL storage?

I am working on an application where I need to make analysis that what will be the storage size of BSON structure if same data is stored which is stored in MySQL. Basically I am comparing MongoDB and ...
0
votes
0answers
3 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 ?
0
votes
0answers
25 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 ...
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` ...
1
vote
4answers
46 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
42 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
31 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
205 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
193 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
22 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
189 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 ...
2
votes
2answers
146 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
112k 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
29 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
6 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
4 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
26 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
16 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
23 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 ...
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
35 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, ...