MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
18 views

How to change the domain name or IP address in mysql database in every tables

I was using Openfire chat server in Ubuntu, whose IP address was 192.168.1.15. Then, I wanted to move it to other ip (192.168.1.55), so i took a backup of my database. The database I was using is ...
0
votes
2answers
67 views

MySQL - prevent OS cache for query

On high load production server I have many sql selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but this queries are still cached with OS cache. Server have 64G ram and used ram ...
1
vote
1answer
113 views

mysql replication missed quite a lot SQL statement

I have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status: Slave_IO_Running: Yes ...
0
votes
1answer
1k views

What is the actual difference between innodb_autoinc_lock_mode 0,1,2

I would like to understand the differences between innodb_autoinc_lock_mode options 0,1 and 2 when parallel load data infiles are given. I see in "0" option, it locks the entire table and does the ...
0
votes
2answers
888 views

InnoDB Tablespace critical error in great need of a fix

Link to screenshot : http://www.nouvellesduquartier.com/i/1/p/Munin_%20Critical_MySql_InnoDB_.JPG (The value reported is outside the allowed range - Byte free, free, gauge, warn, critic) Question: ...
0
votes
1answer
29 views

How to retrieve stored procedures from raw data

I have all the raw data from a database that I can't access through the mysql program anymore. I was able to get the tables by copying the .frm files over to the new installation of mysql, but I don't ...
0
votes
1answer
39 views

MyISAM recover updated row

The row (with type MEDIUMTEXT) has been accidentally updated with empty value. I can see previous data in MYD file of a table, but I can't get them neither from mysqldump or any mysql client. If I ...
3
votes
4answers
193 views

Best practice for a Table with private columns

I have a table with user data, where most of the columns can be public or private (so it will be shown or not on the app) and every user can decide which fields to hide. So far I had a couple of ...
0
votes
0answers
28 views

Moving datadir OK start, but socket error for mysql client

I'm trying to move my MySQL datadir from /var/lib/mysql over to /home/mysql (an ssd drive as I'm having iowait issues on /var/lib/mysql when MySQL is running on that drive). The OS is CentOS 5.6 ...
0
votes
1answer
31 views

MySQL add few column exception for TRIGGER UPDATE FOR EACH ROW

How to effectively add column id, name exception for trigger: CREATE TRIGGER updated BEFORE UPDATE ON table1 FOR EACH ROW SET NEW.date=NOW() so that date=NOW() will be affected on updating any ...
0
votes
1answer
183 views

Change MySQL database name in SQL file

I have a 2GB mysql backup file that has several databases in it and would like to change the name for one of the databases. What approaches can I use to tackle this issue ? I have used sed but it ...
0
votes
1answer
40 views

Can't find source code for building mysql

I'm trying to follow the instructions for building MySQL from source. They guide me to a link where I can download the latest GA. I end up at the downloads page, where I can download ...
2
votes
3answers
124 views

MySQL Explain has different row count than slow query log

I have this entry in slow query log: # User@Host: user[host] @ [ip] # Thread_id: 1514428 Schema: db Last_errno: 0 Killed: 0 # Query_time: 2.795454 Lock_time: 0.000116 Rows_sent: 15 ...
3
votes
1answer
493 views

Select * from statement execute very slowly, innodb io read speed is low

I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
3
votes
3answers
184 views

MySQL buffer pool usage + high load

We've got a quite powerful server: HP ProLiant DL360p Gen8, 2xIntel Xeon E5-2630 2.30GHz, 128GB of RAM. The whole data set should fit in memory: mysql> SELECT ...
1
vote
2answers
827 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 ...
2
votes
1answer
1k 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
0answers
9 views

Logging all user actions

I'm creating a community-driven project and, of course, I want to be protected against spam or evil users. In case of undesirable changes in the DB I would log all users' actions ...
0
votes
1answer
68 views

How to use on delete cascade and on update cascade

I want to ask how to use the various reference options for on delete cascade along with on update cascade. My delete cascade is working. That is child table records are getting deleted on deleting ...
0
votes
1answer
132 views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
0
votes
0answers
28 views

Result of query not showing in phpmyadmin

I have a 500,000 row database that I am running a reasonably complex query on: SELECT yfog, (SELECT AVG(yds) FROM pbp_yards_gained p2 WHERE p2.yfog=p1.yfog AND type = ...
0
votes
2answers
135 views

MYSQL Master-Slave replication not working with PHP

My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database. My Master database ...
0
votes
1answer
454 views

multi-master to single-slave replication at table level with PostgreSQL or MySQL

Here is my scenario Master1 hosting DB1 Master2 hosting DB2 ... MasterN hosting DBN replicate to: Slave1 hosting DB1,DB2... DBN I've read similar questions and they recommend to start different ...
0
votes
1answer
31 views

How to compare all rows in a table and extract data using triggers

I have two table one table stores the gps data of various users another table stores the idle time of the users based upon the gps data of latitude and longitude values and timestamp difference. ...
1
vote
2answers
125 views

Optimizing large MySQL SELECT WHERE IN clauses

How can the following SELECT WHERE IN case be optimized? I have a table with over 100 million rows with only 3 columns. The primary key (col1) is a 127 varchar. I am performing a SELECT col1 WHERE ...
0
votes
1answer
90 views

Mysql - Select To XML

I'm Building Audit Trail with Trigger, and want to convert the inserted / updated / deleted value into XML and store it into another table. my trigger look like this: DELIMITER $$ CREATE ...
0
votes
0answers
14 views

Pasting into PHPMyAdmin

This is probably really simple but I have had a look on the web but can't seem to find an answer. I am experimenting with MySQL + PHPMyAdmin on a WAMPserver stack and was saving queries in Excel When ...
0
votes
3answers
39 views

Select rows where 2 cells not the same

I have the following table: +----+-------+---------+---------------+ | id | owner | variant | EAN | +----+-------+---------+---------------+ | 1 | 101 | black | 1111111111111 | ...
0
votes
1answer
2k views

In place upgrade from MySQL 5.5 to 5.6.11 removes all users from user table

On Windows, I upgraded from 5.1 to 5.5 no problem. Copied my 5.1 data folder into my 5.5 instance Started mysqld skipping grants Ran mysql_upgrade All good, but going from 5.5 to 5.6: Copied ...
3
votes
2answers
216 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
0
votes
0answers
16 views

What is safe way to restore mysql database?

When database is online. I want to restore dump file to it. Here is my plan: set global read_only=ON use root with super_priv to drop all databases do restore all databases set global read_only=OFF ...
0
votes
1answer
195 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
0
votes
2answers
49 views

Which files in the “MySQL-5.0.27-win32” folder do i need to backup?

Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7). Here is the current structure of of mysql-5.0.27-win32: In ...
0
votes
1answer
76 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
2
votes
1answer
174 views

Best Practices for Creating and Deleting Records in MYSQL

I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database. The crawler is frequently writing and deleting records from the database ...
4
votes
2answers
1k views

UNION is slow but both queries are fast in separate

Dunno what else to do about this one. I have one table that has has a start and a stop columns and I want to return the results of it joined both by start and by stop and I want a clear distinction ...
2
votes
1answer
42 views

MySQL Replication - Master Master - Single Master write

I am looking to improve our fault tolerance for our persistent store. After reading several online docs about how to set-up master <> master replication, I'd still like to ask the experts about the ...
0
votes
0answers
15 views

How does database connection actually works? [duplicate]

I am wondering, on a practical level : what is a database connection? What does it represent for PHP/Java/Ruby/etc to have an opened connection for instance? What about the MySQL deamon? Does it ...
2
votes
6answers
877 views

How to store schema-less data efficiently in a relational database?

I'm trying to get a nice balance of searchability and speed. I'm experimenting with different methods. Option 1: Serialize the array as JSON/PHP serialized array and store it in a 'meta' column. ...
0
votes
1answer
29 views

How to sum the buy order and sell order in one row per each ticker

I have a table containing some trading information of several tickers and I would like to divide the data into sum(Amount) for buy and sell separately, grouping by each ticker. I have one table ...
0
votes
1answer
44 views

Cannot execute statement: impossible to write to binary log?

When I try to create a user story/issue in my my percona xtradb cluster I am getting below error. How to fix it? Error creating issue: Could not create workflow instance: root cause: while ...
-1
votes
0answers
45 views

How to select record with biggest value of column [migrated]

Columns: numberIWant, value I would like to get numberIWant number of records, which have biggest value. select max(value) from myTable only returns the max value. I want to find numberIWant that ...
1
vote
2answers
29 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
2
votes
3answers
477 views

Mysql datatype for username of maximum 15 characters

I currently have a database with the "user" table having username as one of the columns. I allow a maximum of 15 characters for the username in my system... Is it fine to have the username column as ...
0
votes
0answers
27 views

SOLVED:MySql: how do I insert a large data set into existing rows automatically?

I'm revising a static website and striping via grep all links, titles, etc. to put into my mysql database. Since it's 400+ links/titles, I just load the text file of titles and then links with a ...
5
votes
3answers
2k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?
0
votes
1answer
87 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
0
votes
0answers
14 views

mysqld: page allocation failure. order:3, mode:0x20

I've been running a mysql server in production for some time, lately the db has been locking up at around 10:30 every day. There's no cron jobs running at that time or anything like that, and I'm at a ...
0
votes
1answer
117 views

innodb completely crashed after interruption

I am having a major crash on mysql affecting a lot of websites. We had a crash on our SAN storage. Since that time, mysql refuse to start. I tried to reinstall mysql, but still no luck. I think ...
3
votes
2answers
61 views

mysql - is it possible to create a partition expression based on two keys / indexes?

if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like: CREATE TABLE `t1` ( id_1 bigint unsigned not null, ...