MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

-1
votes
0answers
16 views

MySql Store procedures is better for large web application [closed]

i make a large web application using php pear concept and MySql Stored procedures for better performance please tell i am right or not . and also comment about advantage and disadvantage of this ...
0
votes
1answer
100 views

mysqldump 5.5.25 use swap instead of memory

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory. # free -m total used free shared buffers ...
0
votes
0answers
5 views

Enable general_log in Mysql 5.0.96

I want to enable general_log in MySql 5.0.96. I have set log variable but it isn't working. [mysqld] log=/var/log/mysql.log and restart mysql with following command /etc/init.d/mysql restart But ...
1
vote
1answer
381 views

MySQL relay log rotates too soon and too often

I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small: 4:00 PM ...
0
votes
1answer
26 views

Mysqldump throwing error 'Couldn't execute 'ROLLBACK TO SAVEPOINT sp':

Can any one please explain what is happening with our DB dumps? We are executing dump as below mysqldump -v --single-transaction --quick --lock-tables=false --skip-triggers -uXXX -pXXX ...
0
votes
2answers
718 views

How to run a cold backup with Linux/tar without shutting down MySQL slave?

I run the following before tar-ing up the data directory: STOP SLAVE; FLUSH TABLES WITH READ LOCK; FLUSH LOGS; However, tar will sometimes complain that the ibdata* and ib_logfiles* files are ...
0
votes
1answer
217 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
0
votes
1answer
561 views

Proper procedure for migrating a MySQL database to another Debian machine?

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser ...
0
votes
2answers
7k views

Xampp 3.2.1 error: MySQL shut down unexpectedly

I logged into Xampp today and it wouldn't launch MySQL. It said there was an error and it shut down unexpectedly. Here are the errors from the error log. Hoping someone has seen this before, ...
0
votes
0answers
20 views

MySql backup for certain database contents

So i have a database containing custom functions and cleanup procedures and obviously content under tables which i want to back up using automating script which works but doesn't create a proper ...
1
vote
2answers
58 views

return my.conf values to sane levels (total memory footprint = 220GB)

I recently took over administration responsibilities for one of our databases. The existing my.cnf has some crazy values. I should mention that one table has 1.8 billion rows of mostly unneeded ...
0
votes
1answer
490 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
1
vote
0answers
20 views

MySQL - Transactions and causality

I'm new to MySQL's LOCK and TRANSACTION and have a question about different isolation levels of a transaction: Given two transactions executing the same code (auto-commit is false): (1) get data of ...
0
votes
1answer
23 views

Mysqldump without slow query

Situation: I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query. Question: ...
0
votes
0answers
18 views

MySQL: migration huge tables between two databases

Hello guys (and sorry for my English). I have 2 DB's - DB1 (staging) and DB2 (production), each day some contend added/changed in some tables in DB1 and should be moved to DB2. Right now it works ...
0
votes
0answers
8 views

Is it proper to make a grand-parent key, a primary key, in its grand-child, in a multi-level identifying relationship?

I'm modeling a DB for a conference ticketing system. In this system attendees are members of an attendee group, which belong to a conference. These relationships are identifying, and therefore FKs ...
0
votes
2answers
29 views

MongoDB/MySQL - auto increment vs object ID

I am in the process of evaluating MongoDB and writing some basic PHP applications around it. Typically in my PHP/MySQL applications, I would create an ID field and set it to auto-increment on each row ...
0
votes
1answer
73 views

Simple SELECT query doesn't return FALSE if no results

This is probably a simple fix, but I can't figure it out. This is the MySQL query I have: SELECT (`clockcards`.`date` IS NOT NULL) AS `clockedin` FROM `users` JOIN `clockcards` ON ...
1
vote
1answer
62 views

Determine last time a mysql table was accessed (i.e. SELECT'ed)

In my mysql DB schema there are old tables that are no longer used. I want to come up with a systematic way to show my developer a list of tables and last time they were read (SELECT'ed, not ...
0
votes
1answer
966 views

How can I get Performance Statistics to work in MySQL Workbench?

I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema. The EXPLAIN plan is ...
2
votes
1answer
5k views

Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded

Ever since we've moved to Amazon RDS, we've had some pretty crazy performance issues, and today we started having locking issues. Because of that, I figured it was just a timeout issue, and went to ...
2
votes
3answers
23k views

select rows where column contains same data in more than one record

I have a table that has a column called article_title. Let's say the table name is articles. I need to find out the records where the article_title data is the same on more than one record. Here's ...
0
votes
1answer
27 views

Mysql select using regexp

I have some user input i am receiving from some forms exactly in this format Names#Age#Gender#City The problem is,some forms come with incomplete data which is acceptable during the filling of the ...
2
votes
1answer
403 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 ...
0
votes
1answer
56 views

Optimize this query

SELECT `permission_tbl`.`service_id` AS `service_id`, `permission_tbl`.`shop_id` AS `shop_id`, `permission_tbl`.`easy_id` AS `easy_id`, `permission_tbl`.`permission_type` AS ...
5
votes
1answer
431 views

Tools for cnf management, generation? [closed]

Does anyone know of any good tools for cnf configuration management? It's often the case I have a group of machines that has a very similar config except for a few differences such as server id or ...
2
votes
3answers
748 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
1answer
125 views

Duplicate Relationships in Rails/MySQL Healthcare Application

Overview of the Application I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
3
votes
1answer
759 views

MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3

I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew brew install mysql Everything seemed to go smoothly, but...not really, as it turns out. I cannot add a new user (neither ...
0
votes
1answer
166 views

MySQL - Indexes deleted, reduced column size but table size increases

Here is the original table definition which contains 107547934 (~107.5M) rows CREATE TABLE `twitter_tweets` ( `id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ...
0
votes
1answer
602 views

Can't change root password: “The system cannot find the file specified.”

I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message: ...
0
votes
0answers
44 views

Slow MySQL Query when Ordering

This query takes around 2 seconds when the order by is included, without the order it takes less than a tenth of a second. The field that is doing the sorting, qc_products.addedDate, is an index and ...
3
votes
1answer
132 views

MySQL Available Cars With multiple owners

I have a complex SQL question. We are building a car rental system. We have a car model table, a owner table and a link_model_owner table. In addition our bookings are kept in another table. Scheme ...
0
votes
1answer
21 views

Mysql pagination without offset

Let's assume I have got a table with these data: I want to order it by createdAt column DESC, and id column DESC select id, createdAt from post order by createdAt desc, id desc Now it looks like ...
0
votes
1answer
15 views

Storing Page View Statistics

This is a rather open-ended question, but only because I'm unable to find much information on it... That being said, how would you efficiently track unique page views? From what I've read, you'd have ...
0
votes
1answer
22 views

MySQL #1005 - Can't create table 'startet' (errno: 150)

Can anyone figure out why phpMyAdmin won't create the last table? It works fine if I drop the last foreign key, but I need it in there to get my assignment approved... CREATE TABLE nasjon ( ...
0
votes
1answer
135 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 ...
0
votes
1answer
34 views

Laggy slave OK to have only one system user in processlist?

In all the examples of a processlist on a mysql slave I've seen, there are two system users. I have a slave that is very far behind the master. When I do show a processlist, there is only one system ...
0
votes
2answers
35 views

How can I remove only consecutive duplicate fields when the id is not consecutive in MySQL

In this table, I have a price change history. The prices are checked regularly and as you can see, sometimes the price does not change. How can I take a specific sku and delete all cases where a ...
0
votes
1answer
100 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 ...
0
votes
1answer
74 views

MySQL - When Command column in Processlist shows “Prepare” or “execute”

Can someone please highlight when a "Command" shows "Prepare" or "Execute" for "Show Full Processlist" Any example which shows this would be helpful.
0
votes
1answer
232 views

Mysql query is very slow

I am developing a website like any other social networking site using mysql. I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...
0
votes
0answers
27 views

Statements in State “Copying to tmp table” disrupt performance

On a MySQL database (version 5.1.73) a Magento-Shop executes a statement (A) that uses distinct, see http://pastebin.com/A2sy45nF and therefore is "Using temporary" according to "explain". ...
1
vote
1answer
29 views

Localization: One table or multiple tables? (MySQL)

I need to be able to store string messages in various languages. I guess I need a column for each language string. For example my columns could be: ID | message_EN | message_DE | message_FR Now, I ...
0
votes
1answer
27 views

Multiple left join hangs server

i've got problem with left join query. It connect's 3 tables and calculate sales value for categories. Query: SELECT categories.name, categories.id, ...
0
votes
1answer
169 views

selecting records that are 3 days from expiring datetime

I have a table that contains a datetime timestamp. What I am looking for is to select records that are exactly 3 days from the 30 day expiration based on the datetime timestamp. Here is what I have ...
0
votes
1answer
150 views

How do I write this script?

I have a very large database and I want to select certain records and then set a column value such as: SELECT name, email FROM table WHERE zipcode = 12345 INTO outfile /tmp/file.csv; Then I ...
2
votes
3answers
4k views

Mysqldump tables excluding some fields

Is there a way to mysqldump a table without some fields? Let me explain: I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of ...
0
votes
1answer
37 views

Cannot drop databse

I am unable to either drop database, now create the database. Following is the output. How can I fix this? mysql> show databases; +--------------------+ | Database | ...
0
votes
1answer
149 views

How to get names form 3 different tables

I have 4 tables to proceed. In table A I have the parameters of columns in tables B,C,D and the names are there. TB_A ...