MySQL : Open-Source, Relational Database Management System
0
votes
1answer
42 views
How can I modify this trigger with loops and concat?
I have this trigger below which inserts new values in the an emplog table. I want to have my trigger insert in the description column the event that took place. For example, if I changed the last_name ...
1
vote
1answer
23 views
Is it better to add an index and search via the index or add a unique constraint and let an insert fail?
I have a table with over 2.5 million records in production that keeps track of if a specific user has already viewed an job:
mysql> DESCRIBE job_views;
...
0
votes
1answer
43 views
Database migration, how to
I have two databases DB1 and DB2. In both databases exists this two tables mo_sms and mt_sms. This is the structure of those tables:
CREATE TABLE IF NOT EXISTS `mo_sms` (
`id_MO` int(11) unsigned ...
1
vote
2answers
37 views
Live backup without lock
In our company we have a lot of MySQL databases. Some of these are critical and we want to take a back-up of all databases. These databases can't be locked because the application can change these ...
1
vote
1answer
33 views
mysqldump error on bash script
I'm trying to run a bash script to backup my database with Cronjob.
# Backup and compress each database
for database in $mysql_databases
do
if [ "${database}" == "information_schema" ] || [ ...
1
vote
1answer
34 views
Will OPTIMIZE TABLE have any impact on my data?
I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.
But before doing so, I would (of course) like to know if ...
0
votes
0answers
42 views
Which fork of MySQL has better performance? [closed]
Since Oracle bought the rights on MySQL, there are some forks of MySQL out now.
I wonder which fork I should choose for my application.
Is MariaDB the only choice?
3
votes
1answer
113 views
Switch MySQL lag slave to new master and keep lagging (5.1)
We have the following scenario:
Set of slaves replicate from the master, separate standby master (a.k.a slave with binlogs) replicates from the same master; lag slave, powered by pt-slave-delay, ...
2
votes
1answer
33 views
Optimize UNION query in MYSQL
I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, > 35 ...
0
votes
1answer
20 views
How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
I have innodb_file_per_table set and just today my ibdata1 file jumped from 59M to 323M after I made several changes to an 800M table to reduce it to about 600M. That particular table's .ibd file was ...
0
votes
2answers
36 views
Is there slowdown inserting into an InnoDB table that has no index set?
I have an old application with lots of InnoDB tables, that have no indexes at all, not even a primary ID or such.
Those tables only contain a few thousand rows.
Would it be faster to INSERT data ...
0
votes
1answer
41 views
Generic SQL Job Scheduler for multiple RDBMS's?
I have been searching for an answer to this, but can't seem to find anything. So my problem is this - we have an environment with MS SQL Server 2008, MySQL, and RedShift, and have some complex ...
0
votes
1answer
21 views
Proper names for SUM(col) fields
I have worked on MySQL databases for a long time, but one question I have never been able to get an answer to that seemed formal is: In the query SELECT *, SUM(col1) FROM tbl, what is the proper name ...
0
votes
1answer
77 views
mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES
I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command:
mysqldump -uroot -p drupaSite > drupaSite.sql
I get the following error:
mysqldump: Got ...
0
votes
2answers
24 views
A query for a latest version of data
MySQL.
Let there is a table with fields startdate and kind.
Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is ...
0
votes
1answer
51 views
Cannot Utilize Maximum CPU and Memory Usage for MySQL
Good day.
I know this may be a duplicate of other questions however I have applied all the suggestions in many of the threads, but I remain with the same problem.
I have a single stored procedure ...
0
votes
0answers
32 views
How Can I do and INNER JOIN USING (2 columns)?
Suppose we have:
CREATE TABLE cscart_products_prices_temp LIKE cscart_products_prices;
ALTER TABLE `cscart_products_prices_temp`
DROP `percentage_discount`,
DROP `lower_limit`,
LOAD DATA LOCAL ...
5
votes
1answer
96 views
Start/Stop MySQL
I'm seeking help to understand what happens when the following command line is executed:
root@prodn$ service mysqld stop
Yes, it shuts down the MySQL server so access to it is no longer available ...
0
votes
0answers
41 views
Converting data from MSSQL Server to MySQL using MySQL Workbench and preserving special characters
I am trying to convert a MSSQL Server database with collation SQL_Latin1_General_CP1_CI_AS into a MySQL database using MySQL Workbench. I can successfully complete the process without any issues being ...
0
votes
1answer
19 views
Simple query not using indexes
My query is not using indexes.
It is a simple query.
Can someone explain why that's the case?
The query:
select TIME_FORMAT(start_time,'%H:%i') start_time,
TIME_FORMAT(end_time,'%H:%i') ...
3
votes
1answer
31 views
Is it possible to select internal InnoDB columns?
I have read that InnoDB uses a few fields internally for all rows, such as the hidden columns DB_TRX_ID and DB_ROW_ID. Is there any easy way to see these values, such as within a select statement?
0
votes
1answer
47 views
Why would SequelPro only import 23k rows out of 130k?
I use SequelPro for mysql on a Mac OS X -- and I used the import function to upload a 130k .csv file to my database. Everything seems to work fine, then I get the message File Read Error: An error ...
0
votes
1answer
51 views
Why some of mysql system variables are readonly
There are almost more or less 277 mysql server system variables .
In these variables some of the variables are dynamic and few are not.
I don't under stand what is the reason behind read only ...
1
vote
1answer
29 views
High IO when switched to InnoDB
We have query that worked well in MyISAM. When we switched to InnoDB, the writes I/O raised up to 50 times.
The query is type of INSERT ... ON DUPLICATE KEY UPDATE
I don't know if the problem is ...
1
vote
1answer
47 views
MySQL replication — issue with mysql.proc during replication from 5.0 to 5.5 host
We are replicating from a mysql 5.0.45 host to a 5.5.26 host over WAN. Every once in a while one of our databases does not accept a query and halts the replication.
"'Column count of mysql.proc is ...
0
votes
1answer
28 views
MySQL server crashed.
Help! I managed to crash MySQL last night. I am on a Mac using the native version that came with Mountain Lion. I was upgrading from 5.5 to 5.6. I have followed instructions in this forum to delete ...
1
vote
1answer
51 views
mysql ibdata1 file corrupted or increased unusually
We are using MySQL 5.2 version all InnoDB tables.
I have observed that ibdata1 file got increased very unusually like occupied 500 GB in 2 days.
In last 6 months, it had taken only 6 GB though ...
2
votes
1answer
77 views
/usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?
One of my databases has been shutting down abnormally, but the log said "normal":
130422 13:23:01 [Note] /usr/libexec/mysqld: Normal shutdown
130422 13:23:01 [Note] Event Scheduler: Killing the ...
1
vote
2answers
57 views
Restoring a database backup to a local machine in MySQL
I have downloaded my .sql file of my database server. It is in the D: drive of my Windows machine.
I want to restore the back up in my this machine. I used:
mysql database -u root < ...
7
votes
4answers
359 views
How to create a database for unknown kinds of data?
I am in the process of designing a database for a new PHP/MySql-based application.
My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
3
votes
1answer
90 views
Simple MySQL query randomly takes forever
I'm having an issue on my server with a simple PHP send emails script that runs every minute via a cronjob. The script contains a MySQL query that runs very fast most of the time, but randomly will ...
1
vote
2answers
71 views
Is there a way to stop MySQL Replication on the master?
I want the master to stop replicating data to the slave. I know I can do that on the slave with STOP SLAVE;, but I wonder if there is a way to do it in the master.
One possible solution might be to ...
0
votes
1answer
46 views
Is there a way to recover this database (MySQL)
I did a "cold" (db offline, all filesystem files) backup of a MySQL db.
I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default.
I didn't realize that until attempting to ...
1
vote
3answers
100 views
Help with modeling this table
Thanks for reading my post...I'm new at setting up databases and am working on a simple practice app.
I have a table called PhoneNumbers, which hold a list of phone numbers (duh). I have two other ...
0
votes
2answers
69 views
High Mysql Load , over 700% CPU
I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz ,
Alot of quiers in sending data mode
Here is mysql status
...
0
votes
1answer
50 views
How do I stop a query from running if the row is locked?
I have a section of code that locks the database, updates it and then confirms.
This is all working fine, if another user attempts to update the same row they cannot and their changes are discarded.
...
0
votes
0answers
88 views
Unable to import exported Wordpress DB - #2006 - MySQL server has gone away
I'm having a nightmare trying to clone a live Wordpress site into a local dev environment using MAMP Pro and BackupBuddy.
I have no problem with setup. The problem is that there seems to be some ...
1
vote
1answer
49 views
Tool to export data with all relational data?
Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design?
The purpose is to ease migrations of bits of data ...
2
votes
1answer
32 views
MySQL Full Text search increase relevance for exact matches?
I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching ...
2
votes
1answer
64 views
Replacing a PHP loop with a MySQL Query
I have a MySQL database table where I store a user_id and a hash each time someone logs in. The hash is stored in a persistant cookie so it doesn't change as users log in and out of the site.
The ...
-1
votes
0answers
44 views
How to join value of two tables [closed]
I want join two tables value. here is my table structure:
table 1
----|--------
id | p_value
1 | 2000
2 | 3000
3 | 4000
4 | 5000
------------
table 2
----|--------
id | pro_value
1 | ...
0
votes
0answers
21 views
ERROR while doing coalesce partition Mysql Cluster
My table contains 3299000 rows. My Mysql Cluster contains 2 storage nodes with NoOfReplicas=2. By default it contains 2 partitions and after that i added 3 partitions more one by one using command ...
2
votes
1answer
83 views
MYSQL score by rank
I am using MYSQL to create a rating system to implement my database. What I want to do is to rate each attribute by its percentage. Here is the example database:
ID, value
1, 3
2, 5
3, 2
4, 5
The ...
5
votes
1answer
260 views
MySQL hogging memory
An installation of MySQL 5.6.10 on a virtualized Ubuntu 12.04 is exhibiting massive memory hogging. The mysqld process claims the entire available memory within a couple of hours of uptime and forces ...
1
vote
1answer
49 views
Equivalent of MRG_MYISAM in databases other than MySQL? [closed]
Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)?
I know about fragmenting, but this is not quite the same AFAIK. We're using ...
1
vote
1answer
38 views
MySQL: logging queries which would execute without using indexes
I am trying to use log_queries_not_using_indexes = 1 to find queries which are not executing optimally on a MySQL server. However, I find the resulting log file of rather limited value. Apparently, ...
2
votes
1answer
69 views
mysql: need help to optimize my query/table
I'm wondering if someone could help me optimize my tables/query to speed up a query. It is currently running ridiculously slow. I think a well-thought out index could help me. Any help would be really ...
1
vote
2answers
52 views
Loading data in mysql using LOAD DATA INFILE, replication safe?
I am trying to load data into mysql database form CSV file. I found that we could use LOAD DATA INFILE command to do it. But as per the mysql documentation it is not replication safe. (See here)
Is ...
2
votes
1answer
75 views
MySQL Tables Require Daily Repairs - Server, Table or HD?
I've been experiencing a weird issue with one of MySQL DBs. Every day, sometimes 2-3 times per day, I'll need to repair the tables. The MySQL DB has 25 tables with 5.6m rows in total.
The bigger ones ...
0
votes
1answer
32 views
Reinstall MySql but keep database tables and data
Please help!
There are server issues and MySql is no longer running on our server (Ubuntu). The service is not recognized and needs to be reinstalled. Unfortunately, the database has not been ...
