Questions tagged [mysql]

All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

Filter by
Sorted by
Tagged with
2
votes
1answer
116 views

open mysql on os x backup disk image

I have several databases I set up using mamp. Recently, my mac died of old age and I got a new one. Fortunately I have a made of bootable backup of my hard drive every day. Unfortunately, I cannot ...
2
votes
2answers
369 views

How much will computer hardware affect the speed of a “load data local infile” query?

I have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has ...
1
vote
2answers
814 views

MySQL Sharding tables/servers or other methods to reduce table sizes?

I'm currently working with a table exceeding 12 million rows (at about 3 GB when exported with mysqldump) and am curious how huge a table can realistically get without any serious performance impact. ...
12
votes
4answers
15k views

No NULLs, yet invalid byte sequence for encoding “UTF8”: 0x00

I've spent the last 8 hours trying to import the output of 'mysqldump --compatible=postgresql' into PostgreSQL 8.4.9, and I've read at least 20 different threads here and elesewhere already about this ...
4
votes
3answers
259 views

How to design this simple DB?

How to design this simple DB? I need to add some things to a DB I use to keep track of servers. Table looks something like this. Matrix | Name | Description | Etc | Etc | ----------...
3
votes
1answer
1k views

How to do MySQL User Accounting

I can't seem to find any information on this anywhere. I found a question here called Monitor MySQL activity per database? but that's not quite the answer either. I'd like to be able to log the ...
4
votes
2answers
10k views

How to make CREATE INDEX in InnoDB table faster

I have platform RHEL 5 with 8G RAM and MySQL version 14.12 distrib 5.0.86. I want to create an index in an InnoDB table, but it takes a long time: Time | State | Info 2291 | copy to tmp ...
3
votes
7answers
24k views

Mysql thinks Master & Slave have the same server-id

I've been setting up a master->slave_a->slave_b relationship and having a little trouble. I currently have the master->slave_a replication working fine. Each of the two slaves have exactly ...
5
votes
2answers
1k views

character encoding issues in mysql

I have some legacy mysql files of the format frm, MYI and MYD that i wish to export to csv. Using sequel pro on OSX, i created a new database and copied those files over. All the data can be accessed ...
13
votes
5answers
42k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
2
votes
2answers
1k views

How to eradicate Mysql Slave Replication delays when huge updates from master

At my environment we had slave replication with mysql server version 5.1.47 enterprise version.When ever there is huge updates from master, slave is lagging behind the master. What is the best ...
2
votes
1answer
453 views

mysql emits BINLOG rows even though binlog_­format=­STATEMENT

We have MySQL 5.5 installed, we have the binlog_format=STATEMENT (the default) According to the MySQL Documentation, the binary log should contain SQL statements only, but we still see BINLOG rows in ...
2
votes
1answer
9k views

I want to count all the rows for todays / yesterdays date based on a DATETIME - mysql+php

I have database "db2" with table "menjava" In table menjava have "id", "author" and "date_submitted" field id - auto_increment author - int(11) date_submitted - datetime I want to count all the ...
4
votes
1answer
7k views

mysql table very large size although it doesn't have a lot of records

I have drupal website, one of the tables is cache_form. I have a cron to DELETE FROM cache_form where expire < UNIX_TIMESTAMP(NOW()); which works fine. The size of cache_form.myd is 4.9G ...
1
vote
4answers
1k views

MySQL Replication monitoring [closed]

Im looking for a tool that could help me and my team monitor our MySQL Replication. I would love for a tool that could alert me if Seconds_Behind_Master passes a threshold Slave_SQL_running is false ...
2
votes
2answers
466 views

Does mysqlhotcopy block SELECT statements from different clients?

I would like to create a backup of different MyISAM tables on a server using mysqlhotcopy. These tables are about 6GB in size. I would like to know, does mysqlhotcopy block users from SELECTing (...
2
votes
1answer
104 views

How to insert fields to different tables at once?

5 Main Tables: TEMP, PAYMENT, CUSTOMER, PURCHASE, and PRODUCT. TEMP: TempID, Email, Name, Product1, Product2, Date, Amount PAYMENT: PaymentID, Status, Amount, Date CUSTOMER: CustomerID, Email, ...
6
votes
3answers
45k views

INSERT INTO without duplicates

I want to do this INSERT INTO AdminAccounts (Name) SELECT Name FROM Matrix But I don't want to create duplicates. ie, I ran this a few weeks ago and need to update the data.
1
vote
2answers
2k views

Update Large Table

I have these two tables: user_profile: user_profile_id_int int primary key user_profile_id varchar user_activity: user_profile_id_int int key user_profile_id varchar both fields in user_profile ...
1
vote
1answer
3k views

How to set up triggers in MySQL for copying a field

I have an in-house application that I've put together, and I'm VERY new to SQL in general. I have a table that contains most of our data. Let's pretend it looks like this. Matrix |Name | ...
23
votes
4answers
33k views

check constraint does not work?

I have the following table. create table test ( id smallint unsigned AUTO_INCREMENT, age tinyint not null, primary key(id), check (age<20) ); The problem is that the CHECK constraint ...
3
votes
1answer
2k views

How to make MySQL replication reliable?

Master version: 5.5.13-1 Slave version: 5.5.14-1 Binary log format: MIXED My Slave database (~ 40GB) has been out of sync from the Master. I cannot find anything interesting in the error log. Google ...
1
vote
1answer
2k views

View metadata refresh in MySQL

Like other RDBMS, view meta data is stored at view creation time. From CREATE VIEW in the MySQL docs The view definition is “frozen” at creation time, so changes to the underlying tables ...
4
votes
3answers
10k views

“Could NOT find Curses” error when installing MySQL from source

I am installing MySQL Server from the source distribution which I downloaded from: http://dev.mysql.com/downloads/mysql/#downloads. I have downloaded the file (mysql-5.5.19.tar.gz). I am using ...
3
votes
3answers
12k views

Deleting duplicates with group by and count

What is the fastest method to convert the following query: SELECT COUNT(*) as c FROM tbl_fields WHERE fieldnotes IS NULL GROUP BY fieldno,fieldserial,id,fielddate,fieldsid HAVING COUNT(*) > 1; .....
3
votes
1answer
3k views

What does the base64 BINLOG statements in mysqlbinlog output mean?

I have looked over the mysqlbinlog command as a candidate for rollback method from MySQL 5.5 to MySQL 5.0. When I ran the command on one of the bin-log files in the MySQL 5.5 server, I've noticed ...
6
votes
2answers
3k views

MySQL deadlock - cannot restart normally?

MySQL version 5.5.13-1 A snippet from the SHOW ENGINE INNODB STATUS\G: LATEST DETECTED DEADLOCK ------------------------ 111218 10:22:34 *** (1) TRANSACTION: TRANSACTION 1318D95B, ACTIVE 0 sec ...
2
votes
1answer
629 views

'packet too large' error with packet smaller than max_allowed_packet

Some time ago we started storing serialized data in our MySQL database and immediately ran into the well known 'packet too large' error. We increased max_allowed_packet to 64M and all was well. This ...
3
votes
1answer
2k views

MySQL Config for Large MyISAM tables

I have to do some updates on myisam tables which have around 20 M records. Our server has 24GB of ram and 2 quad core xeon with 24 MB of cache. I want to know how I can change the my.ini file so I get ...
22
votes
6answers
54k views

Fastest way to check if InnoDB table has changed

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance. My ...
3
votes
1answer
516 views

What's the purpose of /*!*/; in mysql binlogs?

The title pretty much sums up the extent of my question. They seem to appear after every statement. Sometimes on a new line following. At other times, at the end before the ;. What's the point?
7
votes
1answer
5k views

What are the primary differences between NuoDB and MySQL?

Since NuoDB can be used as a storage engine for MySQL, what are the primary differences between using the NuoDB as a server and using MySQL as the server? In addition, recently I've received a ...
2
votes
3answers
9k views

Improve `Update` performance (rows locking issue)

I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database. When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for 10-...
1
vote
1answer
406 views

MySQL full text search ranking

I found in this article the formula that MySQL use to ranking in FTS w = (log(dtf)+1)/sumdtf * U/(1+0.0115*U) * log((N-nf)/nf) Where dtf is the number of times the term appears in the ...
3
votes
2answers
14k views

Table compression in InnoDB?

I have read that table index compression happens just in MyISAM, but I came across an article in dev.mysql that said: InnoDB supports table and index compression! So now what is the pros of MyISAM? ...
2
votes
1answer
4k views

Is there a PPA for MySQL 5.5 for ubuntu 10.10 and 11.10?

I want to install MySQL server 5.5 on ubuntu 10.10 and 11.10 servers but have not yet found a problem free PPA or installation method as of yet. Has anyone managed to do this with the said versions of ...
1
vote
3answers
143 views

Quantity of entries

I have the below tables.The first one has posts with a foreign key to a category table.I'm looking for a query that gives me the amount of posts on every category. The post table. select pstOccuId,...
4
votes
2answers
532 views

Is index_type ignored on MySQL primary keys in create table?

I have seen a create table whose primary key had an identifier where it should have an index_type, something like that: create table a (foo INTEGER, PRIMARY KEY foo_id (foo)); The thing is that ...
2
votes
1answer
1k views

How to upgrade MySQL to a New version..?

I have installed mysql using the package management system.I am using ubuntu machine. After that i have very well configured the server for InnoDB and other server related parameters. I am using the ...
7
votes
1answer
19k views

Best way to setup master to multi master replication

ORIGINAL QUESTION: I have multiple servers that all need to act as masters, as if one fails the next server kicks in and takes over. I'm just wondering what the best way to replicate is. All servers ...
2
votes
2answers
2k views

Which first: upgrade mysql version or convert storage engine?

I inherited a production mysql server at 5.0. It uses MyISAM exclusively. It is not a high volume database but it does have some large datasets. I am planning on: upgrading to 5.5 converting ...
1
vote
2answers
5k views

Where should I look for my.cnf?

I want to add innodb_file_per_table statement to my.cnf file. Where is my.cnf? Could I change this file when I have an online site?
7
votes
3answers
953 views

Combining InnoDB with MyISAM [duplicate]

Possible Duplicate: Is it common practice to mix InnoDB and MyISAM tables on same server? I have a movie clips and photos table in an application; these tables contain a tag column, which allows ...
3
votes
1answer
54 views

Is time to deliver data included in time measured for a Slow Query

I'm looking at some horrible numbers in a SLOW_QUERY_LOG (one example) Count: 2563 Time=23.21s (59498s) Lock=0.00s (1s) Rows=21.4 (54857) This is for a simple SELECT statement that runs for less ...
8
votes
4answers
7k views

MySQL Cluster RAM requirement

From MySQL 5.1, the data doesn't need to be entirely in memory anymore. I've read that the indexed columns (I think the entire index structure) must still be in memory (MySQL High Availability, 2010, ...
4
votes
1answer
7k views

Mysql Master-Master Replication Topologies on >2 machines

I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote. Let's call my main server A, the secondary server B, and a remote, development ...
6
votes
2answers
13k views

MySQL Restore Large Dump

I have a dump file of a single table which is 7GB. I try to restore it using mysql -u root -p DB_NAME < .SQL FILE But I don't see any activities in task manager. CPU is 00 and RAM is 5,700 K. I ...
431
votes
4answers
801k views

How do you mysqldump specific table(s)?

How can I dump a specific table or set of tables without including the rest of the db tables?
4
votes
1answer
4k views

Drop auto increment hack w/o alter table?

I was looking around for a quick way to remove an auto increment from the definition of a primary key. As best I can tell the only way to do it is w/ an alter table or dumping all the data into a new ...
2
votes
2answers
223 views

mysqldump seems to copy excessively

I have an old_server with 50 databases. I want to create new_server with just 3 databases. When I try using mysqldump, it seems like all the database directories (although they are empty on ...

1
361 362
363
364 365
376