MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
861 views

How to configure my.cnf for Mysql 5.1, which is using too much CPU?

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system ...
0
votes
2answers
222 views

MySQL incremental backups per database (not global)

While searching for free/open-source MySQL incremental backups solutions I have found a number of ways to do incremental backups and all of them seem to either rely on "logical" way of backups/restore ...
2
votes
2answers
47 views

Can't select 2 tables at the same time

I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and ...
0
votes
1answer
272 views

MYSQL Timezone support

We are having a shared hosting plan and they are saying that do provide MYSQL Timezone support in a shared hosting plan. I can create timezone related tables in our database and populate them with ...
0
votes
1answer
105 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
0
votes
0answers
59 views

Best way to restore 400GB+ worth of data in MySQL 5.6

So I'm trying to figure what the best/quickest way would be to refresh a massive amount of data. We're wanting to copy 3 months worth of data from a replication server to a QA server for product ...
0
votes
3answers
440 views

Mysql - How to Optimize Retrieval Time in a Table

I have query like this! which has 200 million Records in a single table.. I am using BTree Indexes in my table... mysql> select COUNT(DISTINCT id) from [tablename] where [columname] >=3; ...
0
votes
1answer
22 views

SQL Error (2027): Malformed packet

I have a mysql database (Version 5.6.23-log running on windows) that is set up for use with Teamcity. When running a command against it using HeidiSQL I am getting an error: SQL Error (2027): ...
0
votes
1answer
113 views

How to restore data after MySQL Upgrade?

I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench. I also see that I ...
0
votes
1answer
21 views

How can I delete all users except 1 [on hold]

How can I delete all users except 1 in mysql database in phpmyadmin.(it's a joomla 1.5 website database)
0
votes
1answer
17 views

MySQL “SLEEP” command throws syntax error

The 'SLEEP' command in MySQL is throwing a syntax error: mysql> sleep(5); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
0
votes
1answer
254 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
1answer
26 views

mysql Quiz leaderboard filter by points, time taken

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. ...
1
vote
1answer
25 views

MySQL: Why is inserting data with transactions faster then without them

I am using stored procedure to generate sequence consisting of prefix and number in given range (etc sequence "AB100", "AB101", ..., "AB199", "AB200") The stored procedure includes REPEAT loop, in ...
1
vote
2answers
1k views

Which one to use? InnoDB Replication or MySQL Cluster?

I am building a url shortener and I use InnoDB as the storage engine for link data. I will start with a single VPS containing both application instance and MySQL database instance on the same Virtual ...
2
votes
1answer
6k views

MySQL: Error reading communication packets

I get this warning in mysql, [Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets) I have been through few topics in google ...
2
votes
1answer
649 views

How to normalize my db schema for a quiz application?

I am putting together a quiz application. (I'm calling each quiz a "challenge") I have a "challenge" table and a "challenge_question" table which is pretty straightforward. challenge table: id ...
2
votes
2answers
1k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
0
votes
1answer
162 views

Processlist show most of the queries are sending data for select query

I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and ...
3
votes
5answers
2k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
2
votes
1answer
328 views

InnoDB forum DB design and Composite Primary Key (Cluster Primary Key)

I am designing forum database using InnoDB and got few questions , Could any one please help me to clarify them? Posts and Comments table design below CREATE TABLE `my_posts` ( `forum_id` ...
0
votes
1answer
202 views

MySQL is inserting “” as 0 in decimal fields. How to stop that?

The fields are nullable, so they do allow nulls. Why would MySQL convert "" to 0 instead of NULL? Is there a way to force it to be NULL instead?
1
vote
1answer
56 views

MySQL Index Creation Internals

Right now there are two ways to build an index of a table in MySQL: Create the table structure first, then import the data and add indexes later. Create the table structure with indexes and then ...
0
votes
1answer
168 views

mysql ERROR 1248: Every derived table must have its own alias

-- -------------------------------------------------------------------------------- -- Routine DDL -- Note: comments before and after the routine body will not be stored by the server -- ...
4
votes
2answers
182 views
+100

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
1
vote
1answer
44 views

How to speed up query containing concat() in WHERE clause?

In my company we use a commercial software running with mysql 5.5 (MyISAM) that often executes a query of this form: SELECT table1.attrib1 FROM table2, table1 WHERE table2.status = 'Active' ...
0
votes
0answers
17 views

Ideas for Database schema for Automotive web app

For the last couple of days I have been searching all over Google for database schema for my automotive web application[like a marketplace of sort](in Laravel 5 PHP). I have tried many models some I ...
0
votes
0answers
45 views

Outer left join with WHERE loses row

The line below MUST be in query or the null rows when joining are dropped. Why is this? //If I don't add this line; I lose rows where items.name IS NULL $this->db->or_where('items.name ...
2
votes
1answer
123 views

Randomizing the Auto Increment in Mysql

We have a database, where we insert data into table whenever we receive an order from our services. We use auto_increment to generate the ID and assign the order with the same. This is usually same ...
0
votes
0answers
47 views

MySql Innodb Database Slow INSERT Query to Remote Server

I have searched thoroughly on how to improve general performance of my database which is hosted on Hostgator. I have mostly come across performance tuning strategies which are catered to only local or ...
2
votes
1answer
2k views

Error Code: 1133. Can't find any matching row in the user table

Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this: mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password'; Query OK, 0 ...
0
votes
1answer
188 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
2
votes
1answer
277 views

Bi-directional replication for the same MySQL table

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA ...
0
votes
1answer
132 views

How to check mysql password change and other events logs

Recently someone change my phpmyadmin password and drop database I think it was sql injecting. Now I want to find information about this like when, how and who change my password Please tell me ...
1
vote
1answer
153 views

Generic Trigger on MYSQL to replicate data on other table

I'm currently doing a history table that basically logs every single modification in a history table, and adding the Action executed. CREATE TABLE `competencies` ( `id` int(11) NOT NULL ...
0
votes
1answer
115 views

Unresponsive database, sleeping processes

my mysql database suddenly became unresponsive: OperationalError: (2003, "Can't connect to MySQL server on '<IP_address>' (110)") After killing the processes: ...
-1
votes
1answer
51 views

Grouping based on the status, then count its product and the total based on its category ID

I have a table with product ID, category ID and status of the product. +---------+-------------+-------------+ | id | cat_id | status | +---------+-------------+-------------+ | 1 ...
0
votes
0answers
632 views

MySQL server: Unable to connect with root because of skip-name-resolve option

We had our MySQL server on an old server. We decided to move all of the databases to our new server. Old MySQL Server engine 5.5.31 New MySQL Server engine 5.5.38 We created one general dump file ...
0
votes
1answer
104 views

ubuntu - Pm2 mysql high cpu usage

Not sure what is going on here but would love some help if any can. I am running on digital ocean 1GB ram ubuntu server a node.js application that is is launched with pm2 and that uses a mysql ...
0
votes
1answer
244 views

MySQL is using wrong index

This is my table: - user_id INT (foreign key on `user` with the reference `id`) - question_id INT (foreign key on `question` with the reference `id`) - option_id INT (foreign key on `option` with the ...
2
votes
1answer
213 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
1
vote
1answer
24 views

Can you enforce uniqueness in MySQL based on column in foreign table?

I am refactoring one of my database tables and I've come up with an issue. Specifically, I have a table with four columns. We'll call the first three my.a, my.b and my.c. I have a uniqueness ...
0
votes
0answers
18 views

MYSQL - number of rows returned equals number of connections?

Does my query get sent to the database once and I get a list of all the results in one shot which I then loop through, or do I have to request the next row from the DB each time? Essentially, does ...
0
votes
1answer
38 views

MySQL command “SHOW DATABASES” returns invalid databases

I have a MySQL server with a number of databases on it - 16 if I'm not mistaken. When I run the SHOW DATABASES command I get 30 rows. I would have expected 17 rows: The 16 databases and ...
0
votes
1answer
766 views

#1044 - Access denied for user '#username'@'10.%' to database '#database'

I'm trying to connect my webpage to mySQL with ipage hosting. When I copy the snippet: <?php $link = mysql_connect('username.ipagemysql.com', 'username for mySQL', 'password for mySQL'); if ...
3
votes
4answers
741 views

MySQL replication - slave update

I have a master slave setup of MySQL and my question is that if I make any changes in the slave database: 1. Will it mess up the sync in any way 2. Will the changes get overwritten from the master ...
2
votes
1answer
294 views

Is this amount of disk IO expected with MySQL?

I am running MySQL 5.5.34 on an Amazon EC2 micro instance with EBS (elastic block storage). The instance has been running for around 10 days and according to Amazon I've accumulated around 8.5 ...
2
votes
1answer
35 views

How MySQL Queries and Questions status variables are calculated?

I have a mysql server running on 5.6.19 version and I got confused with the status variables Queries and Questions. show global status like 'Queries%'; | Queries | 28058738 | ...
0
votes
1answer
23 views

How to insert UNIQUE IDs values from several tables into events table

I have a table called events defined as follow: rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables. event_type should be kind of array containing this values: [ ...
0
votes
1answer
145 views

MySQL data transfer to different schema

We have two servers: one for the developper (me) and one for the tester. While working on the app, I sometimes make little changes in tables's schemas. (Like a new field, or a field type) I'm trying ...