MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
1k views

shopping cart database schema

I need help creating the schema for a database to hold information relative to a web user's shopping cart/session. Each session will have a session ID, a time stamp holding time of creation, an expiry ...
2
votes
1answer
3k views

MySQL / InnoDB: Does daily optimization improve performance?

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, ...
1
vote
1answer
987 views

mysql show processlist hanging processes;

We needed to move services from one co-location patner to another so we had to copy some files from main server to back up server so these services can now be run on back up server at the new ...
0
votes
2answers
2k views

MySQL how to release memory used up by SELECT

I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C. INSERT IGNORE INTO sid(sid) SELECT sid ...
4
votes
1answer
3k views

Suddenly have to rebuild indexes to prevent site from going down

I have been having this problem for a while: http://stackoverflow.com/questions/7770695/mysql-query-slow-until-reindex-column It seems all is good and when I suddenly get a lot of writes to the table ...
2
votes
2answers
2k views

mysql 5.5 corrupt - missing temporary table

One of our mysql instances crashed and failed to start: 120422 10:04:13 InnoDB: Error: trying to open a table, but could not InnoDB: open the tablespace file './databasename/#sql-29f6_45.ibd'! ...
1
vote
1answer
1k views

MySQL flush logs, unknown error

I gave been trying to solve this for few days now, and cant seem to figure this out. I have been trying to set log rotation for mysql slow using linux logrotate feature, and my script seems to get ...
2
votes
1answer
1k views

SQL Query - Set Date Variable by Table/Column Reference

How do I set the @DATE variable by using a table/column reference? Right now I've set it manually as you can see below with the opening: SET @DATE = '2012-03-31 01:00:00'; The table from which I ...
1
vote
1answer
2k views

MYSQL Social Networking website - Database Design, Data Growth and Hardware

Introduction I have a system around one million users (community based). It has around 6000 groups (fixed , no change). But there is no discussion board for these groups yet. Building discussion ...
1
vote
3answers
424 views

Mysql error while creating FK

here is structure of both tables category table CREATE TABLE category ( `catID` bigint(20) NOT NULL AUTO_INCREMENT, `title` varchar(60) NOT NULL, `created_at` timestamp NOT NULL DEFAULT ...
1
vote
1answer
450 views

Merge 2 tables and fetch data one by one

I have two tables: product & offers. products has (prod_id, category_id, prod_name, brand, price, status) offers has (offer_id, prod_id, offer_type, discount_amt, validity) I want to display ...
1
vote
1answer
1k views

procedure is not executing

Problem I am getting when I execute this proc: call `sp_frm4_px_prc_calc`(); Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
2
votes
0answers
77 views

MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
2
votes
1answer
2k views

MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all ...
3
votes
1answer
203 views

Economic Database Architecture Possibilities w/ Data of Varying Length

I'm in the process of designing a MySQL database (InnoDB) that holds a large amount of economic data which our application scores. The size of the economic data varies a great deal: some measures, ...
1
vote
2answers
2k views

Mysql giving only 25 inserts per second

I'm using ubuntu 12.04 LTS Processor : Pentium(R) Dual-Core CPU E5200 @ 2.50GHz × 2 OS Type : 64-bit Memory : 3.9 GiB Disk : 101.1 GB I installed mysql and wanted to test insert performance. Here is ...
3
votes
1answer
116 views

Slow MySQL query, not sure why

I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect: create or replace view propertytaxessub0 as select ...
3
votes
3answers
337 views

Is this database fine for calculation?

I am doing an application called as Invoice Application.For that I have my database is like this. Now my problem comes when I am taking the tax part in my database.As I have made tax as an option ...
1
vote
0answers
153 views

How can I force myisamchk to modify the original data file in case of duplicate keys?

In the documentation for myisamchk, it says: --quick, -q Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to ...
1
vote
1answer
832 views

MySQL Master-Slave Replication Cross Database

I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and Replicate-wild-do-table=database_name.% ,But i am not able to get ...
2
votes
2answers
2k views

MySQL cluster - how many hosts are needed for full HA?

I need some clarification on MySQL-Cluster (NDB) setup I want to achieve full HA but I'm low on server budget and can't afford to rent a dozen of servers for simple solution that I'm working on. ...
0
votes
1answer
407 views

Stored procedure arguments get immediately truncated

I have a stored procedure with a single SELECT statement inside: DROP PROCEDURE `pos_neg_test`// CREATE PROCEDURE `pos_neg_test`(IN start_ts TIMESTAMP, IN end_ts TIMESTAMP) BEGIN SELECT ...
4
votes
2answers
4k views

MySQL Database Schema for Train Timetables

I am interested in what a MySQL database schema would look like for a train timetable. Generally this is presented in table format as the final result. Train No. 11111 22222 11111 Train Day ...
1
vote
1answer
403 views

What is the underlying representation of BLOB data when I export it?

When I export a blob object to an SQL file, I get some thing like this for a blob field: 0x4f3a383a227374644....., Questions I would like to know how the BLOB is actually converted to the above ...
1
vote
2answers
2k views

Similar function NOWAIT in MySQL

Select for update nowait doesn't work in MySQL 5x (innodb). Is there any function like NOWAIT in MySQL?
-1
votes
1answer
2k views

My master to slave replication stopped and is showing this error when I use SHOW SLAVE STATUS;

My master to slave replication stopped and is showing this error when I use SHOW SLAVE STATUS; | Waiting for master to send event | 172.16.10.37 | repl | 3306 | 60 | mysql-bin.000256 | ...
2
votes
1answer
907 views

Find which numbers in [1, 161] are not in the result set?

How to find which two number in [1, 161] is not in the result set? Can I find this two numbers using sql command? mysql> select blog_id from wp_blogs; ... | 149 | | 150 | | 151 | | ...
2
votes
1answer
4k views

execute() vs executeQuery() in MySQL Connector C++

With MySQL Connector C++, I can use the sql::Statement::execute() function if I am going to modify the database (INSERT, UPDATE, DELETE, MERGE), or the sql::Statement::executeQuery() function if I am ...
0
votes
1answer
3k views

Print table documentation with comments?

I have a MySQL database, and I have used MySQL Workbench to insert text into the "comment" fields of many of the columns and tables; the plan was to have the documentation stored in the database. ...
1
vote
1answer
133 views

Is MySQL replication suitable for a Write Mostly application?

I'm considering adding another server to my one-server setup application with 600GB MySQL DB. The db and the apache are both running from the same server. In order to avoid backup downtime and improve ...
2
votes
2answers
571 views

mysql myisamchk vs mysqlcheck

We have a mysql database running version 5.0. Every month we shutdown the database for maintenance. We run a myisamchk on the tables to fix data fragmentation, and this takes down the system for up to ...
1
vote
1answer
2k views

How do I perform a UNION query on a dynamic list of tables?

My database has an unknown amount of tables, named with the same prefix followed by a random string: _ct_<random_string>. Each of these tables has the same data structure. I also have another ...
4
votes
1answer
8k views

How To Optimize and Repair InnoDB tables? ALTER and OPTIMIZE table failed

I just switched my Wordpress databases to use the InnoDB engine. When using MyISAM, I used to run Optimize and Repair table. However, with InnoDB, these don't work. I manually ran OPTIMIZE TABLE ...
5
votes
1answer
4k views

How do I reduce the database size on AWS RDS MySQL?

I have an RDS MySQL database on Amazon Web Services. I want to reduce the size of the database in the control panel in order to save money. Q: How do I make it smaller? Their site says you have to ...
2
votes
0answers
945 views

MySQL Server hitting 100% unexpectedly (Amazon AWS RDS)

We've been struggling with this issue for months. This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database ...
5
votes
3answers
10k views

Resolving issue with mysql.proc after upgrading MySQL from 5.0.* to 5.1.*

Like many others, we upgraded our instance of MySQL from 5.0.* to 5.1.49. Since then, we've been running into an error that repeats itself when we perform certain actions: Attempting to view or run ...
2
votes
2answers
5k views

How does one change the default socket used by mysqladmin?

How does one change the default socket used by mysqladmin? I don't see a section for [mysqladmin] in /etc/my.cnf. It tries by default to connect to /tmp/mysql.sock, however, this is not the socket ...
1
vote
2answers
187 views

What MySQL storage engine should I choose?

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows. What engine should I choose for my database, InnoDB or MyISAM? There will only be about 1,000 new records ...
0
votes
0answers
59 views

Give privilege to new created databases in MySQL

I have user "U" who I want to give him full access to all the existing databases except "db1". I want him to only have select privilege for that db. What I did is I gave "U" all permissions for each ...
5
votes
1answer
2k views

How are multiple indexes used in a query by MySQL?

Imagine a table where there are separate indexes on columns a and b. The query runs somewhat like this: SELECT col1, col2 FROM `table` WHERE a = 'value_1 AND b = 'value_2'; Now, does this query ...
2
votes
1answer
2k views

change encoding of database from latin1_swedish_ci to utf8

I have an huge database in latin1_swedish_ci. Now i need to convert all data to utf8 collation. My question is about the consistency of the information. When I do this change it is possible corrupt ...
0
votes
1answer
211 views

Mobile Phone & Tariffs

I have designed database tables of Mobile Phones & Tariffs. It should support multiple mobile networks, sale type and the related affiliate. Points (for operators) are vary depending on the ...
2
votes
1answer
267 views

What logical structure is used for storing rows in Oracle?

From the logical data separation point, there are tablespaces that consist of segments that consist of extend that consist of oracle blocks that consist of OS blocks. What logical structure actually ...
4
votes
1answer
689 views

Does creating an index on BIT(n) attribute in MySQL make sense?

I am working on a very specific thing and I need to use BIT(n) attributes and efficiently search through them. n is generally not a power of 2. I've seen some mentions on the internet that BIT ...
5
votes
1answer
4k views

What should I set max_connections to for MySQL?

I have MySQL 5.1.35 on Linux Centos. The Linux server has 2GB RAM with 14GB of disk space. I have created some web services using the Restlet framework in Java that has 1,000+ users. What should I ...
4
votes
2answers
466 views

Is MySQL Clustering a viable option for a DB expecting 5TB of data in 5 years?

We plan to implement MySQL Clustering.The database should support 5 years of data and expected data/year is 1TB. When we created a test enviornment with the DB size as 200GB, NDB Cluster took more ...
6
votes
2answers
4k views

How can I improve the speed of a query on a 20 million+ row table?

I have a query that is used for getting internet traffic statistics of certain IP addresses. There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
1
vote
1answer
5k views

How to import Timestamp from a CSV file

I am trying to Import a CSV file with a column called timestamp to MySQl to a column with the structure timestamp but all i get are NULLS in the columns is there a way that i can import the timestamp ...
1
vote
1answer
256 views

query optimization delete nested query [closed]

Can the following query be optimized ? DELETE FROM COLOR_TAGS WHERE COLOR_PROJECTID = $id AND NoteID IN( SELECT ID FROM note_note WHERE BibID=$bid AND isdel=0);
5
votes
1answer
10k views

Why is LIKE more than 4x faster than MATCH…AGAINST on a FULLTEXT index in MySQL?

I'm not getting this. I've got a table with these indexes PRIMARY post_id INDEX topic_id FULLTEXT post_text Table has (only) 346 000 rows. I am trying to perform 2 queries. SELECT ...