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

MySQL syntax for order by not accepted in SQL Server

I'm working through the SQL snippets for nested sets in this article by Mike Hillyer. The article is using MySQL, but I'm using SQL Server. I get the following error reported when attempting to ...
0
votes
1answer
287 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 ...
0
votes
1answer
34 views

DDL doesn't work in MySQL master to master replication while DML can work?

I have two master in replication left master -> 5.5.47-log MySQL Community Server (GPL) rigth master ->5.5.47-log MySQL Community Server (GPL) "left master" mysql> create table test.left ...
0
votes
2answers
2k views

How to resolve “Error log file size is increasing in mysql 5.5” issue w/o restarting mysql

I have an Ubuntu server with MySQL 5.5 installed. In /var/lib/mysql a .err file is kept on updating with these warnings only: [Warning] IP address '234.22.11.221' could not be resolved: Temporary ...
2
votes
2answers
1k views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
1
vote
0answers
36 views

howto read/interpret “show engine innodb mutex” output?

We use a 3 node Galera cluster with version 5.5.41-MariaDB-wsrep MariaDB [(none)]> show engine innodb mutex; +--------+---------------------------------+------------------+ | Type | Name ...
0
votes
1answer
551 views

my.ini configuration for a huge server

I have developed a web based app in PHP MySQL and Flash Builder. It was running fine until a few days ago when the client began to connect a lot of users. Then the app started to freeze and sent ...
0
votes
2answers
112 views

MySQL - installed version 5.5 but mysql --version says 5.0.95

I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.5.45 MySQL ...
2
votes
1answer
4k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
0
votes
1answer
112 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
1answer
34 views

OFFSET using a GUID kind of ID

I would like use the OFFSET in a SELECT query so I can use pagination in my webapplication. The only problem is I only know the ID of the row from which I would like to start, not the actually OFFSET. ...
2
votes
2answers
172 views

Slow subquery in SELECT statement

Having tried many different methods, I can't get my query taking shorter than 40 seconds to run with a dataset of ~11000 rows. The table is a list of hotel bookings, containing (most relevant) ...
1
vote
1answer
266 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning. Actually the tuning of my.cnf is like below: My database engine is MyISAM | Mysql 5.1.73 key_buffer = ...
1
vote
0answers
51 views

MySQL is using swap memory while I have plenty of free RAM

I have a 6 GB buffer pool size for my database; however, mysqld process is moving to swap while there is plenty of RAM available. I think pool size and pages along with pool of reads and pool of read ...
0
votes
1answer
131 views

mysql database migration of schemas with updated data and structures

I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema. Assuming the source schema is defined by ...
0
votes
0answers
31 views

Building MySQL InnoDB clustered indexes

Assuming that you have a table like CREATE TABLE `t1` ( `search_id` int(11) NOT NULL, `provider_id` bigint(20) unsigned NOT NULL, `status` text NOT NULL, `imported_at` int(11) NOT ...
0
votes
1answer
14 views

Why doesn't MySQL installation on an Ubuntu linux 16.04 OS require user to specify LD_LIBRARY

Why doesn't MySQL installation on ubuntu 16.04's linux require the user to specify LD_LIBRARY_PATH to resolve its runtime dependencies? I tried googling to find the answer to this question, but I ...
0
votes
1answer
151 views

Why do the queries slow down after a few minutes, when trying to build a DB?

First - I am a complete newb to DBA. Please be gentle. (And I am fairly new to python as well.) Oh, and I have no idea how to change any MySQL settings or the like, so all the MySQL DB settings are ...
1
vote
1answer
223 views

MongoDB to MySQL Replication Automatically

i have Mongodb server and mysql server. i want to use mongodb for "write" and mysql for "read". so i am looking for some tool or some process by which we can tranfer mongodb to mysql server on ...
1
vote
1answer
53 views

Cache query that is not stored in QueryCache

One of our Customers has an online-shop running xt-commerce 4.2. This version on the e-commerce-software has a very expensive Query for the category-tree. The query adds about 3 seconds delay and is ...
0
votes
1answer
42 views

how to decide whether table segregation is benificial or not

I have an input data feed that is significant in size. Presently, I have one table with all entries input in it. I want to find out how to decide whether splitting the table into smaller tables would ...
0
votes
1answer
137 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 ...
2
votes
1answer
210 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: ...
1
vote
2answers
171 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
1answer
2k views

How to update a (very) large table without locking in MySQL

I have a large table (58+ million records) which represents a relation between two records (player and target) in a second table. Unfortunately, whoever designed our schema didn't think things ...
12
votes
4answers
24k views

Modify DEFINER on Many Views

I have am having problems backing up my databases after an update. I have been poking around on my system trying to figure out why. One query I ran returned this result. Got error: 1449: The user ...
0
votes
0answers
46 views

Using the same tables for multiple many to many relationships

I currently have a project where I need multiple Many to Many relationships some of them are roughly and I started to wonder if using an extra column on the table to specify the table it's related to ...
1
vote
2answers
480 views

Vague replication error code 2005

2015-04-07 13:49:53 23758 [ERROR] Slave I/O: error connecting to master 'replication'@master-host:3306' - retry-time: 60 retries: 1, Error_code: 2005 This was after attempting to do a change master ...
1
vote
2answers
3k views

Cannot Add Foreign Key Constraint

Can anyone tell me why there are errors such as: Cannot Add Foreign Key Constraint And am I on the right track? My schema looks like this: DDL: DROP DATABASE ...
-1
votes
1answer
13 views

append field to previous record [closed]

I have a table with 3 columns that I imported data from an excel file. Some of the records have data carried over to the next row in the excel file, when this happens the first column is blank and ...
1
vote
1answer
13 views

MySQL innodb_log_file_size change is not being recognized

I am trying to set the innodb_log_file_size option in a MySQL 14.14 (distrib. 5.6.30) installation on Ubuntu 15.10 to solve an issue (importing a database with large BLOB fields) however the log size ...
0
votes
1answer
110 views

Cannot set character_set_database and character_set_server to utf8mb4

I'm trying to convert my MySQL server and its already existing schemas to the utf8mb4 character set. Preliminary Notes: I'm using Xampp (with MySQL 5.6.24). Default options are read from the ...
0
votes
1answer
52 views

Unable to install MySQL 5.7.12 in RedHat in a separate directory

I am trying to install the MySQL version 5.7.12 into RedHat in a non default data path. The installation syntax used /app/mysql/5.7/bin/mysqld --initialize --user=mysql --basedir=/app/mysql/5.7 ...
0
votes
0answers
13 views

What is the 'auth_string' in 'GRANT' 'ON' SQL command in MySQL/MariaDB?

In the typical command to grant privileges: GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname" IDENTIFIED BY "password"; What is the "password" part? The documentation says ...
0
votes
0answers
35 views

Recover innodb mysql 5.6 database WITH ib_logfile0,ib_logfile1, and ibdata1 but WITHOUT .idb files

I had a customer's computer shutdown/crash due to power going out and when mysql tried to boot they got. After I looked in data folder for database and there was just .frm files and no .idb files. ...
1
vote
1answer
701 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 ...
0
votes
0answers
40 views

How to handle HUGE 16GB SQL file?

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements. I'm looking for option to store it in SQL Server Database. Couple of ...
0
votes
2answers
82 views

What is difference between JOIN and simple query

I have two tables in database one is Orders and another one is customers Orders Table Contains : OrderID , CustomerID and OrderDate Customers Table Contains: CustomerID , CustomerName , ContactName ...
0
votes
2answers
5k views

query to select data between todays date as well as tomorrows date time upto specific time

I have database "raj" with table "pagination" In table pagination have "id", "actualtime" and "created" field id - auto_increment name- varchar created- datetime I want to display all rows which is ...
1
vote
2answers
17 views

MySQL Front - Workbench

We have MySQL Front installed in our server, I can connect to it with no problems, there is a db with tables, everything fine. Now I installed MySQL workbench (client) in the same server, but can ...
0
votes
0answers
32 views

How to check the age of a user using triggers

Im new to databases so sorry in advance for maybe obvoius questions. I have a database where i want to block new user registrations if the age of the user is less than 18. How to count the age? ...
3
votes
0answers
304 views

MySQL 5.7 + Ubuntu 14.04 eating up my RAM

I've recently installed MySQL 5.7.11 in my Ubuntu 14.04.3 system following these instructions: http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/ Being totally empty and not being called by any ...
2
votes
2answers
885 views

How to speed up audit trigger?

The task: write a trigger to log all updates within tbl1 table for future audit users' actions. The tbl1 table: id INT(11) y SMALLINT(6) country SMALLINT(6) -- really here ~20 fields but only some ...
0
votes
2answers
204 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 ...
0
votes
0answers
8 views

Django Auth user as well as custom users table?

So we've been using djangos database authentication for a while now and have recently decided we want to implement a completely new login system for our website. We are keeping the django ORM as we ...
1
vote
1answer
62 views

Hoursworked/day computation via Bio Logs

This is my checkinout table: USERID CHECKTIME CHECKTYPE ------ ------------------- --------- 404 2016-02-12 07:24:03 I 404 2016-02-12 17:01:34 O 404 2016-02-15 07:24:21 I ...
2
votes
1answer
90 views

Opening MySQL connection is sometimes very slow

I have a very weird issue with an older MySQL server (v5.0.45) and a Java application. From time to time getting a connection from the database takes 7 seconds, 15 seconds, 30 seconds etc (most of ...
3
votes
2answers
36 views

SELECT other rows based on one matching row

I am trying to get related rows in a tag table based on one tag, where "related" means "having the same area_id". Example: tag area_id -------- ------- turkey 1 stuffing 1 carrots 1 ...
0
votes
1answer
197 views

How to manage very large amount of data in mysql

I have a table in MySQL in which I write logs of users in a specified format. It have just 2 columns:id (auto-increment), log. Currently data is very less, so I'm using this. Now there is a ...
0
votes
0answers
14 views

Grand read privilege to the user but is not allowed to grant the same privilege to another user?

i am a noob and this is how far i got so far, thanks and also how would you do it if you want to allow user to grant the same privilege to another user. GRANT SELECT ON DATABASE_NAME.* TO USER;