MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
1k views

What should one do if the auto increment primary key exceeds the maximum?

I have a primary key for a table with the following structure, **Field** **Type** nid int(10)unsigned I know the maximum limit of int(10) is 4294967295. But What ...
1
vote
1answer
639 views

ORDER BY on aggregate field on a closure table giving unexpected results

I am updating my companies product database and I'm looking into different ways to do design our faceted navigation "selector" tables. The idea of faceted navigation might look like this: It's ...
3
votes
1answer
413 views

Find least recently used mysql table index

I am cleaning up duplicate indices from (innodb)tables in a mysql database. The database has about 50 tables. While I can check for duplicate keys using pt-duplicate-key-checker, I was wondering if ...
2
votes
1answer
372 views

myisampack running really slow for large DBs

I was going through compressing a bunch of archival myisam tables using myisampack It was making decent progress until it got to the most recent tables. The overall database is a set of archives for ...
2
votes
2answers
139 views

(MySQL) How to change the results of a view according to the user that's viewing it?

I have a table, named servers, with a single column id. This table is accessed by various machines each with a different username for the database. I want to make it so each server does not see all ...
4
votes
2answers
3k views

How to store formula method in the table

I am trying to figure out how to store formula method in the table (I could create category_formula table). Each category will have different way of formula method to calculate the point and I need ...
3
votes
1answer
765 views

Does this make my XtraDB Cluster hang?

After seeing the following in the error log on the second node: 121003 7:16:06 [Note] WSREP: Member 0 (joiner) synced with group. 121003 7:16:06 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 0) ...
0
votes
1answer
122 views

What is stored in mysqld-slow.log?

My MySQL log directory contains the following files: 7.9K mysqld.err-old 4.0G mysqld-slow.log 94 mysql.log 93M mysqld.err mysqld-slow.log contains a long list of queries with some ...
2
votes
1answer
906 views

Create a custom log table in MySQL

Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. ...
7
votes
1answer
3k views

How should I migrate a large MySQL database to RDS?

I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here: ...
1
vote
1answer
172 views

Access Drizzle databases through MySQL

This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot ...
4
votes
1answer
1k views

Resolving MySQL Process ID to page that ran it?

I asked this question on stackoverflow and was directed to ask this question here. I have a couple of scripts in PHP that run MySQL queries. On occasion they cause the server to seize up or become ...
5
votes
1answer
461 views

MySQL year 2038 vulnerable

I was studying Drupal and asked this question: http://drupal.stackexchange.com/questions/45443/why-timestamp-format-was-chosen-for-users-created-field/45446#45446 I decided to check, how the CMS ...
1
vote
2answers
187 views

Is there a way to get slow-query-log-like profiling information on a single query?

Specifically, I'm looking for this information: # Query_time: 0.011922 Lock_time: 0.000088 Rows_sent: 107 Rows_examined: 663 Rows_affected: 0 Rows_read: 107 # Bytes_sent: 4152 Tmp_tables: 3 ...
4
votes
3answers
1k views

How do you structure a database that organizes post by location like craigslist?

I'm new to databases and web development so I'm just looking for a laymen explanation. No need for specific code, just theory. I understand OneToMany and ManyToMany relationships, so no need to ...
2
votes
3answers
971 views

Move Oracle data to and from MySQL with the help of PHP?

My application is in PHP which by default uses MySQL db. I have connected MySQL & Oracle dbs with PHP. Now I want to move data from Oracle db into Mysql db through PHP. Is it possible to do? Is ...
3
votes
3answers
10k views

ERROR Cannot find or open table?

MySQL version: 5.5.24 Due to the following problem: mysql> desc reportingdb.v3_zone_date_cpm7k; ERROR 1146 (42S02): Table 'reportingdb.v3_zone_date_cpm7k' doesn't exist ...
1
vote
5answers
4k views

Tuning MySQL to consume less memory

I have a VM which has 2GB Ram, (full specs) And I am setting up a site which has one table in particular with over a million records. There's little or no usage of this particular database (perhaps ...
1
vote
0answers
498 views

How to customize Place Order Functionality In Magento? [closed]

In Magento, when any customer buys product from front-end, then at last customer goes through order placing. According to my need, I have created one CUSTOM TABLE in Magento database. So my ...
12
votes
1answer
9k views

Scalability limitations of PostgreSQL and MySQL

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB. I suspect that limits as such do exist as one would not come up with Netezza, ...
0
votes
4answers
2k views

How to do a mysqldump with a use database in the dump

So the command I normally use to get a database sent to another server is: mysqldump -u user -p --add-drop-tables database *file* But I always have to go into the file and add a line at the top: use ...
0
votes
0answers
309 views

mysql high load [closed]

as you can see from the screen print below mysql is using a lot of cpu, when there isnt much going on. i have had to restore to a default my.conf so i might need some settings changing. could be wrong ...
1
vote
3answers
146 views

Database schema - performance v correctness information

I have a database that stores information about the users of my website (username, password, etc). I also store information about their activity on the site. For example, I have a large number of ...
2
votes
1answer
2k views

How do triggers affect last_insert_id()?

I am unable to test this right now, however I'm curious about the answer. If I have a trigger on table A that writes a new record to table B, will LAST_INSERT_ID() tell me the updated ID for A or B? ...
0
votes
1answer
1k views

Create second instance of mysqld on windows xp

I am trying to install the new version of the mysql on the windows7 and windows xp . In few of the computers already a new version of mysql are installed. Here I just want to create a second instance ...
0
votes
2answers
1k views

Some queries slower in MySQL 5.5 than 5.0?

We have an exceptionally inefficient process (1.3 million selects, 232k updates) that I'm now recoding to be 1 select and 50 updates. In the meantime, though, today's process ran. We recently updated ...
1
vote
0answers
166 views

phpMyAdmin slow for small database

I have a small database (<100 KB when dumped) that will hang in phpMyAdmin when I try to sort by various columns. Sometimes it hangs when I try to edit columns by directly clicking on their ...
2
votes
0answers
462 views

Incorrect data when we using the CONVERT_TZ function on DST boundaries

We have a problem in converting time zone from "America/New_York" to "UTC". This problem occurs only on times representing 2.00 AM during the daylight saving. Timezone conversion with correct output ...
0
votes
2answers
2k views

Time to import an sql dump

I'm currently uploading one of Wikipedia's dump files. It has 1GB compressed and ~7GB uncompressed. It has been importing it for 6 hours already. The size of the imported table in MySQL is at the ...
2
votes
1answer
3k views

How do I completely remove MySQL from a 64-bit windows 7 machine?

I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine. I've decided to attempt to completely remove MySQL from my machine and to restart the ...
2
votes
1answer
138 views

MySQL full text search result problem

I have this query that does not return any results: SELECT MATCH (e.firstname, e.lastname, e.tc_identity) AGAINST ('Baha') AS Score , e.* FROM employees AS e WHERE MATCH (e.firstname, ...
0
votes
1answer
1k views

How to protect mysql database from sql-injection?

I am new to databases and security. I am planning to put up a website. I've read that sql-injection vulnerabilities can be very dangerous since the server might leak user passwords and ...
3
votes
1answer
9k views

MySQL: Optimize UNION with “ORDER BY” in inner queries

I just set up a logging system which consists of multiple tables with the same layout. There is one table for each data source. For the log viewer, I want to UNION all the log tables, filter them ...
3
votes
1answer
147 views

mysqlservice command syntax

I'm new to MySQL. I've found myself in the position of having two different versions running on one host. I'm a little unsure of how these are working together. Do I issue >service ...
2
votes
1answer
85 views

Replicate from master-master setup

I have MySQL Master-Master replication on 2 servers. I want to replicate to another slave server. When I configure the slave to replicate from master1, only data added on master1 is replicated. ...
9
votes
2answers
23k views

MySQL Set UTC time as default timestamp

How do I set a timestamp column whose default value is the current UTC time? MySQL uses UTC_TIMESTAMP() function for UTC timestamp: mysql> SELECT UTC_TIMESTAMP(); +---------------------+ | ...
1
vote
2answers
598 views

Count consecutive null rows from a joined table

I have the following tables: members: ---------------------------------------------- | member_id | member_name | member_join_date | ---------------------------------------------- events: ...
2
votes
2answers
2k views

Looking for an efficient way to fix “Could not parse relay log event entry…” error

I have a corrupt relay log in MySQL 5.0 and looking for the steps to fix replication without having to reprocess all the binary logs from the master again. Here is the full error message if you are ...
2
votes
0answers
3k views

'Access Denied' in MySQL on AWS RDS

I'm trying to use an ETL (in my current case, the free version of Talend). I've successfully set up a MySQL on Amazon RDS, logged in through the MySQL Workbench for Mac, and uploaded my company's ...
1
vote
1answer
902 views

Does TRANSACTION/COMMIT/ROLLBACK only work on certain MySQL engines such as InnoDB?

Are transaction functions only relevant for particular database engines on MySQL, or are transactions supported under all MySQL engines, including MyISAM and InnoDB? someone says here that ...
2
votes
1answer
1k views

ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121)

I get an error when I try to create a table: ERROR 1005 (HY000): Can't create table 'phact_greenland.cache' (errno: 121) CREATE TABLE IF NOT EXISTS `cache` ( `cid` varchar(255) NOT NULL ...
3
votes
1answer
877 views

InnoDB “corruption”

I use: 5.1.63-0ubuntu0.11.10.1 Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.63 The system is continuously displaying the following error during reading data from a MySQL database: ...
2
votes
1answer
275 views

How do I switch master with slave and vice versa on MySQL?

I have two instances of MySQL, one master and one slave. How can I switch them to make the slave the master and vice-versa while avoiding downtime?
7
votes
2answers
16k views

What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

I am very new to Database Administration. I face a lot of problems while setting up mysql master-slave replication. I also face regular mysql replication troubleshooting issues. Can anybody helps ...
2
votes
5answers
260 views

How to shard 14 gb of data for archival database?

We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state. We now want to store this in a database. There would be ~130 million records. The ...
1
vote
1answer
185 views

Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included

I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I ...
1
vote
0answers
96 views

How to profile individual queries using PERFORMANCE_SCHEMA

I've been looking at SHOW PROFILES, which seems ideal for what I am doing. However, I've noticed a warning that SHOW PROFILES is being deprecated and that PERFORMANCE_SCHEMA should be used instead. ...
1
vote
1answer
121 views

How to get non-deleted messages for one user_id if the same user_id appears in two tables at same time?

I'm trying to get non-deleted (deleted = 0) messages for one user_id if the same user_id appears in two tables at same time, but something is wrong because I am not getting the correct or expected ...
4
votes
1answer
6k views

SELECT LIMIT 1 per column value?

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 | ...
1
vote
1answer
213 views

Sudden MySQL connection issue

We have almost 60 network guys working via a proxy server, developing on one Linux-based in-house server. Suddenly, it is saying to everyone: Warning: mysql_connect() [function.mysql-connect]: Too ...