MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
0answers
142 views

Most efficient way to sort data fields into SQL

I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives: Device X sends a text string "name=value&name2=value&name=value" On arrival that ...
1
vote
0answers
1k views

“Lost connection to MySQL server during query” error

I have MySQL 5.5.20 and this table: mysql> desc country; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
0answers
2k views

'Incorrect datetime value: for column 'last_login' at row 1'

i am getting below error: Last_SQL_Errno: 1292 Last_SQL_Error: Error 'Incorrect datetime value: '2013-03-10 02:00:00' for column 'last_login' at row 1' on query. Default database: 'platform'. ...
1
vote
0answers
155 views

Replication issue - CREATE SELECT alternative?

I've an MySQL 5.1 slave for our BI team. They need to make some CREATE SELECT with big select queries (several million lines). As CREATE SELECT is a DDL, if the replication attempts to update some ...
1
vote
0answers
130 views

mysql update unique values from 2 different tables based on 2 index fields

i have 2 tables with the same unique id. i want to change table1 unique_id sequence from (1-100) to random 8 digit ids (43342359 - 83083224) and cascade that to table2. what i have: - table1 id1 ...
1
vote
0answers
634 views

mysql remote connect

Having troubles with this and I'm not sure why.. I'm trying to access a mysql database from another server. I changed the my.cnf file (uncommented the bind-address to access from all): port=3306 ...
1
vote
0answers
713 views

Migrating from Oracle to MySQL using WorkBench Migration Tool

I am currently migrating from Oracle to MySQL and I am going to use the migration tool in workbench to help me with the procedure. I have entered my source connection details as follows: Database ...
1
vote
0answers
161 views

MySQL Workbench and Optional Many's

I have used workbench before to make databases code wise and I just started looking into its EERD functions. I created a small database for a test project and found some odd things about how MySQL ...
1
vote
0answers
42 views

Is there a canonical list of MySQL defaults which should be changed

I'm setting up a new mysql server as we're building a new app at work. Most of the developers/operations guys are familiar with mysql. Many of mysql's settings (eg strict mode) are set badly by ...
1
vote
0answers
166 views

Calculate lower and upper quartile of SQLiteDB via query

I want to calculate the upper and the lower quartile of the value what I call "diffs" in these fiddle with my DB and the query How can I do this with SQLite? There's an extension on the ...
1
vote
0answers
133 views

TMD auto create

I used mysql as part of a project and found it to be a very practical tool. I have a question about mysql in regards to how it creates temporary table types TMD in /var/lib/mysql/. This partition ...
1
vote
0answers
356 views

What's complexity of select * from table order by id desc limit a, b?

I'm displaying about 1,000,000 records separated by 10 records per page. select * from table order by id DESC limit a, b; In this query id is indexed, and b is 10. The querying time is fast when ...
1
vote
0answers
136 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
1
vote
0answers
231 views

big mysql database optimization

My Joomla mysql database has about 3.4 million rows in content table. It is just static article. Very slow and often use out of resources. To speed up it, I segmented it into 6 sub-tables and added ...
1
vote
0answers
74 views

pt-table-checksum generates no output

I've been using pt-table-checksum on our staging server successfully, but when I run ./pt-table-checksum h=localhost,u=user --ask-pass --lock-wait-time=50 --databases prod It just sits there. No ...
1
vote
0answers
118 views

Why is this table structure so slow to CRUD?

I have a table that I am using for queuing emails. Unfortunately, adding to or reading from this table becomes horrendously slow after adding about 50 records. -- Table "mail_queue" DDL CREATE TABLE ...
1
vote
0answers
169 views

Database instance keeps restarting

From the past few days, our MySQL database instance has crashed and had to be restarted a few times. My MySQL version is 5.1.57-log. The CPU and memory usage was way below 50% during the crash time. I ...
1
vote
0answers
53 views

memcached+mysql cluster maximum memory

I'm deploying a system with memcached and mysql cluster. The command line is: memcached -E lib/ndb_engine.so -e "connectstring=maddy:1186;role=dev" I can't specify the -m argument since I used -E ...
1
vote
0answers
103 views

How to create multi volume file backup using symantec backup.exec

I am using symantec backup .exec for backup and restore in mysql server . Now I am in need to take larger database backup in tape .But it exeeds the tape storage and I am trying to create a multi ...
1
vote
0answers
82 views

Mysql: Large Query with subqueries optimization

Any query optimization gurus in the house?? Anyway, I am trying to attack this on two fronts, one being to code a way around it but secondly, I'm thinking some real optimization might help in the ...
1
vote
0answers
79 views

MySQL: Assessing performance of a query by using EXPLAIN

It is said that the number of rows are scanned is smaller the performance is higher when use Explain to optimize query. So I wonder When the EXPLAIN output includes many rows, is there any formula ...
1
vote
0answers
103 views

MS SQL to MySQL Conversion

Is there a tool available that converts queries written as triggers, stored procedures and functions written in MS SQL 2008 to MySql? We switched databases and now I need to rewrite a bunch of queries ...
1
vote
0answers
528 views

MySQL: Writing log entries from stored procedure

Is there a standard way to write log messages in MySQL stored procedures? I can write log messages to a designated log table, but I prefer the standard log mechanism where each message is coupled ...
1
vote
0answers
115 views

What would make a MySQL database create tables in a different collation other than its default?

I am adding tables to a Wordpress database on my localhost and it is changing my collation from utf8_general_ci to latin1_swedish_ci. I'm kinda stumped because I'm not familiar with working directly ...
1
vote
0answers
58 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
1
vote
0answers
111 views

MySQL: Fixing Unique key constraint violation

I have a database with an unique key constraint. When I import this database from a dump created by mysqldump I get '14698-3' for key 'UNQ_CATALOGS EARCH_FULLTEXT_PRODUCT_ID_STORE_ID' But when I ...
1
vote
0answers
692 views

multiple child records in a single row by join

I have one master table (teacher) structured like teacherId Name Class 1 Praveen 10 2 John 9 and having a child table (student) structured like studentId ...
1
vote
0answers
99 views

migrations within transactions

I have a bash script to run migrations on our MySQL databases. Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line? ...
1
vote
0answers
148 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 ...
1
vote
0answers
131 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
0answers
87 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
0answers
65 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 ...
1
vote
0answers
226 views

Improving database design for category based site

I'm working on a site that gets about 30k visits a day and it makes use of a lot of queries which uses UNION's over about 45 different tables, these tables generally range from about 15k rows to about ...
1
vote
0answers
266 views

MySQL GROUP BY and ORDER BY giving inconsistent results

I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the ...
1
vote
0answers
166 views

Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id

I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
1
vote
0answers
909 views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
1
vote
0answers
107 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE ...
1
vote
0answers
225 views

mysql server is not sending result set back after query execution to client and staying in sending data state

When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application. When I execute the same query in SQLYog directly, it ...
1
vote
0answers
957 views

Tables don't exist after MySQL restart

I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior: I have some tables with data in them I made ...
1
vote
0answers
171 views

Enforce a hard limit on write execution time during Amazon RDS write stalls

I am trying to recover from the situation where Amazon RDS write operations stall (some discussion on why this is here). If I show full processlist, I will see something like this: 318263 myapp1 ...
1
vote
0answers
838 views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
1
vote
0answers
801 views

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2?

Why can I not start mysql server on ubuntu after change the innodb_flush_log_at_trx_commit value to be 2 ? updated to answer the 3 questions: mysql> show variables like 'version%'; ...
0
votes
0answers
2 views

How to allow remote connections to mysql from specific IPs only?

I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only? I ...
0
votes
0answers
6 views

MYSQL Out of memory, table crashing

an specific table on my DB keeps crashing. I suspect to be a bad buffer setting on my.conf, it show this error before crashing (most of the time it needs to be repaired): 18:26:59 [ERROR] ...
0
votes
0answers
7 views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
0
votes
0answers
10 views

How to store highly configurable relationships based on multiple conditions

I'm trying to devise a schema that needs to be extremely configurable when necessary, but also minimal wherever possible. There is a large number of condition types that would lead to a number of ...
0
votes
0answers
13 views

MySQL on Server side PRIMARY KEY taking automatically random Id

I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values. inserted id's like 1, 2, 35, 67, ...
0
votes
0answers
13 views

Mysterious LOAD DATA LOCAL INFILE

This is a piece of log file for loading ~19m rows via CSV into a mysql myisam partitioned table already containing ~90m rows: 01:46:17 Keys for two_legs disabled. 03:45:46 Data copied from ...
0
votes
0answers
11 views

MySQL: #126 - Incorrect key file for table '/tmp/#sql_9dc_1.MYI'; try to repair it

I've been reading possible solutions to this problem but until this time, those solutions don't work with my case. Activity: In phpMyAdmin, the following error pops up while opening a View. Error: ...
0
votes
0answers
13 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 ...