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
0answers
41 views

Need help in optimizing a Slow Query

I am using userpoints module to provide points for different actions that they do on my site. mysql> desc userpoints; +-------------+---------+------+-----+---------+----------------+ | Field ...
0
votes
0answers
2k views

mysql takes too long on “checking permissions”

We have about 20 databases with about 8000 tables each. Modifying the software is not an option (it is bought), and it seems most of the cpu is consumed by "checking permissions". While the s/w ...
0
votes
0answers
30 views

MySql cannot get started after increasing buffer_pool_size and log_file_size in my.ini

I try to change buffer_pool_size to 256 MB and log_file_size to 64 MB in my configuration file. After the changes made, I was unable to start MySQL service. System RAM size is (1.99 GB usable)4 GB. ...
0
votes
0answers
294 views

Huge load on CPU because of mysql process [closed]

I am getting a huge load on my linux servers because of this query. CPU is on 500% load & this query is causing load. /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql ...
2
votes
2answers
1k views

MySQL-NT is crashing frequently

MySQL is crashing frequently and there is no error in the err log file. The following error message was found in the event viewer: faulting application mysqld-nt.exe, version 0.0.0.0, faulting ...
4
votes
2answers
16k views

How are auto_increment keys handled in INSERT (SELECT * FROM…)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id. If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows ...
-6
votes
2answers
117 views

MySQL stores 0.00 as 0? [closed]

How can we store 0.00. The column is of float datatype. It automatically converts 0.00 to 0
0
votes
2answers
3k views

How to maintain MySQL performance at high concurrency

The following has been observed using "mysqlslap". mysqlslap -u root -p --create-schema=matrix --query=monthly_kpi.sql --iterations=1 --concurrency=1 --delimiter=";" Benchmark Average number ...
3
votes
1answer
3k views

MySQL crashes frequently

I am facing frequent MySQL crash problem. Not sure how to fix the problem. Attaching the mysql error log. Suggest me a solution. Server Config Digitalocean SSD server 1GB RAM CentOS 6.5 64-bit ...
3
votes
1answer
5k views

Getting unique names when the IDs are different (DISTINCT ?)

I've got this table: id | name 1 | John 2 | John 3 | Peter 4 | Mike 5 | Mike And I want to get the unique names, getting the latest one in the list. I could go for: SELECT DISTINCT name ...
0
votes
0answers
22 views

MySQL crashes frequently [duplicate]

I am facing frequent MySQL crash problem. Not sure how to fix the problem. Attaching the mysql error log. How can I improve the performance? Suggest me a solution. Server Config Digitalocean SSD ...
0
votes
0answers
201 views

Mysql DB recovery

Mistakenly I have deleted "ibdata1" file in mysql server. Now my database is not working. All the tables are corrupted. But still I have "ib_logfile0" and "ib_logfile1" files.Also I have a recent ...
0
votes
0answers
243 views

MySQL SSL encryption

Does setting MASTER_SSL to 1 in change master ensure encryption without specifying options MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY ? After setting MASTER_SSL to 1, show ...
2
votes
2answers
18k views

Declaring and using variables in triggers

I have been testing something with triggers today, but I am stuck with it and I do not have any idea what is causing my problem. DELIMITER $$ USE `pucko`$$ CREATE TRIGGER `after_delete_Alien` AFTER ...
0
votes
2answers
83 views

Which database for survey app [closed]

I need to build a very simple mobile survey app. The app will ask for different questions where people can give one of 3 possible answers, which will be always "yes", "no" or "maybe". To save ...
0
votes
0answers
225 views

MySQL: after query optimization the CPU is still over 100%

my first time here! First of all I want to say that I'm not a DBA and I have a very little experience with MySQL but I like to study and understand what I'm doing. I have a virtual machine (2gb RAM, ...
1
vote
1answer
139 views

Performance issues when extracting scatter plot data

First off: Sorry for the title, couldn't think of anything better. Feel free to change it though. Ok, assume I have the following table: ...
0
votes
1answer
167 views

How to index for multiple inequality WHERE search conditions?

I am thinking on the problem, if there is a multiple columns on which search must work as fast as possible. For example, see the table TEST(COL_A, COL_B) with a (tree) index TEST_IDX(COL_A, COL_B). ...
0
votes
2answers
22 views

Joining on 3 different keys as they're set

I have these tables: TABLE vessels ID int PK organization_id int FK fleet_id int FK name VARCHAR TABLE settings ID int PK organization_id int NULL FK fleet_id int NULL FK vessel_id int NULL FK ...
1
vote
1answer
197 views

Concurrency Issues when multiple users uses Portal (MySQL and PHP)

We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no ...
2
votes
1answer
449 views

MySQL with slow queries

I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page. I ...
0
votes
3answers
141 views

Add new admin user to MySQL

I have inherited a MySQL instance currently being used by an enterprise software suite. I have no idea what the credentials are for any account, but have full admin rights to the server that the ...
1
vote
2answers
2k views

Freeing up disk space in 'mysql\data'

I am trying to free up some space used by a MySQL instance, but I know pretty much nothing about MySQL. I notice that in the mysql\data folder there are lots of localhost-bin.0001** files, as ...
0
votes
1answer
79 views

How can I write queries as fast as possible in MySQL? [closed]

I'm looking for two things: Awesome manual about writing fast MySQL queries. Tool which will decompose my query into discrete steps, and give estimations of each step. I've seen the next tool, ...
2
votes
1answer
77 views

Peer review database design for various reports

I'm currently trying to redesign a poorly thought out database for a peer review system that we use internally at my workplace. Employees can log on and submit a review about another employee who ...
0
votes
1answer
111 views

MySQL TIMESTAMP isn't behaving as advertised

I'm using MySQL 5.5.34. The 5.5 Documentation says I can create a table like this: CREATE TABLE t1 ( ts1 TIMESTAMP DEFAULT 0, ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE ...
0
votes
1answer
52 views

Problem JOINing three tables

I want to show all the categories and the items inside that each category which should store in items_this_category. Note, I have created an associative table item_categories because one item can be ...
2
votes
1answer
480 views

inserting large quantity of data into mysql table

I need to insert about 400 000 000 rows of test data into the InnoDB table. Table is quite simple: CREATE TABLE IF NOT EXISTS `tgt` ( `id_1` int(10) unsigned NOT NULL, `id_2` int(10) unsigned NOT ...
0
votes
1answer
385 views

MySQL pivot table via a view

Suppose the following schema CREATE TABLE `log_apache` ( `date` datetime NOT NULL, `count` int(10) unsigned NOT NULL, `input` bigint(20) unsigned NOT NULL, `output` bigint(20) unsigned NOT ...
0
votes
0answers
276 views

Upgrade from mysql 4.1 to mysql 5.5

I was going through upgrade process of mysql server. It is mentioned that to upgrade from 4.1 to 5.5 we need to upgrade step by step like first to 5.0,5.1 ...5.4 and then 5.5. I have some questions : ...
1
vote
0answers
84 views

Querying number of tries since last successful log, checking IP as well as user

I asked this question before on stackoverflow, but it might fit better here, since it is mainly about database performance. The actual question With performance in mind, how would you go about ...
0
votes
0answers
320 views

How to set up MySQL 5.6 Master- Slave Replication step by step

I am new to Mysql replication. I want to have 2 MySQL servers, a Master and a Slave, running on two Ubuntu 14.04 Computers. Their IP addresses will be 10.0.0.1 (Master) and 1.0.0.02 (Slave). I need to ...
3
votes
2answers
5k views

“on duplicate key update” all the non-key fields mentioned in the insert

How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion? insert into tablename set keyname = 'keyvalue', fieldname = ...
1
vote
0answers
208 views

Migrate from Interbase to MySQL

I need to migrate a big database from Interbase to MySQL, I'd like to have suggestions about free and non-free tools to perform this task. I've found this one: ...
2
votes
0answers
21 views

MySQL slow query log not logging all slow queries [duplicate]

I am trying to analyze the hibernate query performance in our application. I tried logging all hibernate queries which take more than 100ms execution time from application side as well as using mysql ...
1
vote
2answers
5k views

MySQL on Windows or GNU/Linux?

I've just read this article: http://dev.mysql.com/tech-resources/articles/move_from_microsoft_SQL_Server.html There it says that MySQL on Windows is a good choice. (or is what I've understood) In ...
0
votes
0answers
134 views

MYSQL backup options using mysqldump and mysqlbackup

I'm currently using mysqldump to backup mysql database. I heard that mysqlbackup offers a much much better performance. Unfortunately it requires MySQL enterprise version which is pricy. A few ...
-1
votes
1answer
1k views

My auto increment primary key is not automatically incrementing

I have the following database Whenever I try inserting values in the database I get the following error. Duplicate entry '0' for key 'PRIMARY' This means ID is not incrementing automatically. I ...
-1
votes
1answer
119 views

Lookup tables and performance

I am using 6 lookup tables in my mysql database. There will be frequent insert,update and delete queries in the db. Will that badly affect the performance. Do I need to save the data as it is without ...
0
votes
1answer
34 views

Privileges discrepancy

When I am logged in as rds_user and have following privileges: GRANT SELECT, INSERT, UPDATE, DROP ONnews.* TO 'rds_user'@'%' My code runs INSERT command just fine, but when it gives ProgrammingError ...
0
votes
1answer
537 views

How can I Automate MongoDB schema design from existing MySQL Database and Logs?

I am working on the project which will automatically generate sample MongoDB schema analyzing existing MySQL database and logs. I am looking for help regarding the below questions. Please, share your ...
0
votes
1answer
508 views

In MySQL Workbench 6.1 (OS X), can you make the Result Grid taller?

In MySQL Workbench 6.1 (OS X), can you make the Result Grid taller? I know the region expands to fill the space vacated by Action Output/Text Output/History, should you choose to hide that. But I'd ...
3
votes
2answers
2k views

MySQL encryption

I am thinking about encrypting some fields in MySQL database using MySQL's encryption functions. I can't find any good sources on how to properly implement it. Has anyone done this and have a good ...
-1
votes
1answer
346 views

Mysql Queries cause high load in server

I have problem in the database ,here is phpmyadmin status queries --------------------+ ...
1
vote
2answers
132 views

MySQL TEXT Disk Space

I have a plain text file with 3 million articles divided with \n. The file is 58G When I'm adding the articles to an InnoDB table one per row INT id TEXT article_content the .ibd file of this ...
1
vote
1answer
1k views

Fetch all parents from child id

I created a table with name "vegs" and inserted values just like this. id name parent -------------------------------------- 1 fruits 0 2 Apple 1 3 ...
1
vote
1answer
7k views

forcing innodb recovery of a corrupted database

A poorly designed script selected records from two existing tables and added records to a newly created table for 24 hours until I killed it by terminating the MySQL 5.6 command line client and ...
1
vote
1answer
454 views

User Table Design

I am designing for an ecommerce application, I need to know how to implement the following task. There are 2 options, Unregistered Users (Free Listing). Registered Users. In this the unregistered ...
1
vote
2answers
58 views

Is it possible to get the information of which user changed what in a database?

I'm trying to create a log of changes in a given table in a database. To do this, I'm going to get the info about all the fields in the original table and create a new one using a function. In ...
2
votes
2answers
235 views

Do Inactive MySQL Databases Consume Memory?

I have inactive databases that are on the server just for archive purposes. I know I can do mysqldump for all of them, but I am not 100% sure they are inactive. When I started my system I didn't ...