Got error 124 from storage engine
hi, all: i have met an question as below: table A1,A2 A1 has been *packed by myisampack, and rebuild the index by myisamchk* A2 is a noraml table, and the struct of A1 and A2 is exactlly same talbe A is the merge table of A1 and A2 while i use: * mysql select max(id) from A; ** ERROR 1030 (HY000): Got error 124 from storage engine +---+---+--+-+---++ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+---++ | id | bigint(20) unsigned | NO | MUL | NULL | auto_increment | *but when i try another table, the situation is as before, such as table B1,B2,B * mysql select max(id) from loot; +-+ | max(id) | +-+ | 110415 | +-+ 1 row in set (0.00 sec) * the only difference is (*table A the id Field is auto_increment and table B the id is not*): *+---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | id | bigint(20) unsigned | NO | MUL | NULL | | *and if i do not use myisampack/myisamchk, all are work fine, *our system is freebsd 7.2, the mysql version is 5.0.84 Server version: 5.0.84 Source distribution * thanks for your reply tiredboy ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Innodb + Large data set
Hi everyone, Is there any forseeable issue with having an extremely large data set, say 1 TB in size for a single database and doing a SELECT * FROM tbl WHERE constraints where constraints are super restrictive (in that they return only a few rows since only a few match) and the proper indexes are in place? This is on the InnoDB engine. I was curious if there are any problems where doing a read on a large dataset has huge problems down the road. Let's assume the server is a quad core with 4 GB of RAM. Surely it shouldn't have a *huge* effect? Sincerely, Suhail Doshi
Re: Innodb + Large data set
On Tue, Aug 25, 2009 at 3:16 PM, Suhail Doshi digitalwarf...@gmail.comwrote: Hi everyone, Is there any forseeable issue with having an extremely large data set, say 1 TB in size for a single database and doing a SELECT * FROM tbl WHERE constraints where constraints are super restrictive (in that they return only a few rows since only a few match) and the proper indexes are in place? This is on the InnoDB engine. I was curious if there are any problems where doing a read on a large dataset has huge problems down the road. Let's assume the server is a quad core with 4 GB of RAM. Surely it shouldn't have a *huge* effect? Sincerely, Suhail Doshi Maybe this article will help give you some idea; http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/ -- Muhammad Subair
Re: MySQL Encryption - Third-party tools
On Mon, 24 Aug 2009, Mike Scully wrote: Hello, all. =20 Can any of you share with me the names of any third-party tools or appliances that you are using to encrypt your MySQL databases? I am doing a search and would like to narrow down the initial search list. Thanks! =20 Mike I use ccrypt from http://ccrypt.sourceforge.net to encrypt databases before storing them on removable media for offsite storage. Platform is a Sun Ultra 45 running Solaris 10 and the command looks something like, mysqldump db_name | bzip2 | ccrypt -e -k keyfile | ... (Note the bzip2 in the pipeline. This reduces the size of the files by a factor of between 5-8.) It's certainly more than fast enough for my needs. I suspect most of the time is taken up by mysqldmp and writing the ouput to the external media. -- TTFN Philip Riebold, p.rieb...@ucl.ac.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Innodb + Large data set
At 03:16 AM 8/25/2009, you wrote: Hi everyone, Is there any forseeable issue with having an extremely large data set, say 1 TB in size for a single database and doing a SELECT * FROM tbl WHERE constraints where constraints are super restrictive (in that they return only a few rows since only a few match) and the proper indexes are in place? This is on the InnoDB engine. I was curious if there are any problems where doing a read on a large dataset has huge problems down the road. Let's assume the server is a quad core with 4 GB of RAM. Surely it shouldn't have a *huge* effect? Sincerely, Suhail Doshi Suhail, I think your biggest problem is getting the 1TB of data inserted into an InnoDb table. It is going to take an extremely long time. Why does it have to be InnoDb? Are you running transactions on the table the same time you're reading from it? If you don't need transactions or RI, may I suggest creating several MyISAM tables and then use a Merge table on that. It has worked really well for me. I have each table holding 1 year's worth of data. This allows me to rebuild any one table quite quickly and I can have separate processes loading data for each year simultaneously without locking problems. Once the data is loaded, I can then run a query on the merge table to pull data from all of the tables. Also there are other 3rd party engines that may be better depending on what type of data you are storing. Take a look at InfoBright at http://www.mysql.com/news-and-events/generate-article.php?id=1180 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Natural Join Issue: column names are equal but doesn't work anyways
Hi Deviad,
NATURAL JOIN uses all column names that are the same between both tables as
conditions.
select * from rappresentanti NATURAL JOIN clienti;
is the same as:
select * from rappresentanti r JOIN client c ON r.cognome=c.cognome AND
r.nome=c.nome AND r.vita=c.vita AND r.citta=c.citta AND r.prov=c.prov AND
r.cap=c.cap AND r.CodRappr=c.CodRappr;
Regards,
Gavin Towey
-Original Message-
From: Deviad [mailto:dev...@msn.com]
Sent: Monday, August 24, 2009 6:27 PM
To: mysql@lists.mysql.com
Subject: Re: Natural Join Issue: column names are equal but doesn't work anyways
Hi again,
since I'm not someone who gives up easily, I have restyled that old code
(actually is from an example back of my teacher into 2002, I saw that
she changed a bit her way to code this stuff), I restyled the code just
to be sure it isn't some parsing issue or whatever.
http://pastebin.com/f50d77dcf
On that database, this query works:
select CodCliente, Cognome, Nome from Ordini NATURAL JOIN Clienti where
Data='2002-09-05';
whereas this one does not:
select *
from rappresentanti NATURAL JOIN clienti;
I pasted the database in there.
Deviad ha scritto:
Hello,
I have been training for a test on Database Design and MySQL.
The following is inside a file we have to run before starting to code
what the excercises require us to.
Call the file as you wish and run it if it helps to understand the
reason behind my problem.
---
DROP DATABASE IF EXISTS premiere;
CREATE DATABASE premiere;
USE premiere;
create table if not exists Articoli(
NroArt char(4) primary key,
descrizione char(20),
giacenza int,
categoria char (2),
PrezzoUnitario decimal(8,2)
) TYPE=INNODB;
create table if not exists Rappresentanti(
CodRappr char(2) primary key,
cognome char(10),
nome char(8),
via char (15),
citta char(15),
prov char(2),
cap char (5),
TotProvv decimal(8,2),
PerProvv decimal(8,2)
) TYPE=INNODB;
create table if not exists clienti(
CodCliente char(3) primary key,
cognome char(10),
nome char(8),
via char (15),
citta char(15),
prov char(2),
cap char (5),
saldo decimal(8,2),
fido decimal(8,2),
CodRappr char(2) not null references
Rappresentanti(CodRappr)
) TYPE=INNODB;
create table if not exists Ordini(NroOrdine char(6) primary key,
data date,
CodCliente char(3) not null
references Clienti(CodClienti)
) TYPE=INNODB;
insert into articoli
values ('AX12','ferro da stiro',104,'cs',24.95);
insert into articoli
values ('AZ52','freccette',20,'sp',12.95);
insert into articoli
values ('BA74','pallone',40,'sp',29.95);
insert into articoli
values ('BH22','tritatutto',05,'cs',24.95);
insert into articoli
values ('BT04','forno',11,'el',149.49);
insert into articoli
values ('BZ66','lavatrice',52,'el',399.99);
insert into articoli
values ('CA14','setaccio',78,'cs',39.99);
insert into articoli
values ('CB03','bicicletta',44,'sp',299.99);
insert into articoli
values ('CX11','frullino',142,'cs',22.95);
insert into articoli
values ('CZ81','tavola pesi',68,'sp',349.95);
insert into Rappresentanti
values('03','Jones','Mary','123 Main','Grant','MI','49219',215,5);
insert into Rappresentanti
values('06','Smith','William','102
Raymond','Ada','MI','49441',49412.5,7);
insert into Rappresentanti
values('12','Diaz','Miguel','419 Harper','Lansing','MI','49224',2150,5);
insert into clienti
values
('124','Adams','Sally','481Oak','Lansing','MI','49224',818.75,1000,'03');
insert into clienti
values
('256','Samuel','Ann','215Pete','Grant','MI','49219',21.5,1500,'06');
insert into clienti
values
('311','Charles','Don','48College','Ira','MI','49034',825.75,1000,'12');
insert into clienti
values
('315','Daniels','Tom','914Charry','Kent','MI','48391',770.75,750,'06');
insert into clienti
values
('405','Williams','Al','519Watson','Grant','MI','49219',402.75,1500,'12');
insert into clienti
values
('412','Adams','Sally','16Elm','Lansing','MI','49224',1817.5,2000,'03');
insert into clienti
values
('522','Nelson','Mary','108Pine','Ada','MI','49441',98.75,1500,'12');
insert into clienti
values
('567','Dinh','Tran','808Ridge','Harper','MI','48421',402.4,750,'06');
insert into clienti
values
('587','Galvez','Mara','512Pine','Ada','MI','49441',114.6,1000,'06');
insert into clienti
values
RE: Got error 124 from storage engine
Which version of mysql are you using? In mysql 4, you could get away with some differences between the definition of the merge table and the underlying tables. As you've discovered, the structure and index definitions must now be exactly the same, otherwise you will get errors. Regards, Gavin Towey -Original Message- From: stutiredboy [mailto:stutired...@gmail.com] Sent: Tuesday, August 25, 2009 12:23 AM To: mysql@lists.mysql.com Subject: Got error 124 from storage engine hi, all: i have met an question as below: table A1,A2 A1 has been *packed by myisampack, and rebuild the index by myisamchk* A2 is a noraml table, and the struct of A1 and A2 is exactlly same talbe A is the merge table of A1 and A2 while i use: * mysql select max(id) from A; ** ERROR 1030 (HY000): Got error 124 from storage engine +---+---+--+-+---++ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+---++ | id | bigint(20) unsigned | NO | MUL | NULL | auto_increment | *but when i try another table, the situation is as before, such as table B1,B2,B * mysql select max(id) from loot; +-+ | max(id) | +-+ | 110415 | +-+ 1 row in set (0.00 sec) * the only difference is (*table A the id Field is auto_increment and table B the id is not*): *+---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | id | bigint(20) unsigned | NO | MUL | NULL | | *and if i do not use myisampack/myisamchk, all are work fine, *our system is freebsd 7.2, the mysql version is 5.0.84 Server version: 5.0.84 Source distribution * thanks for your reply tiredboy ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
More ways to debug mysql slowness..?
Hello, We have a BSD box with the following installed: mysql-client-5.0.77 multithreaded SQL database (client) mysql-server-5.0.77 multithreaded SQL database (server) p5-DBD-mysql-4.010 MySQL drivers for the Perl DBI php5-mysql-5.2.8mysql database access extensions for php5 We are experiencing intermittent slowdowns on the queries made with PHP to mysql to the point where pages take a lot of time to load, upon further investigation with mytop we observe that it only keep an average of 1-2 simultenaous threads and a query time of avg 2-3 seconds. During which the mysqld process reaches 99% continously for minutes. We have repaired and optimized the tables, and the DB is 200mb. The storage engine is MyISAM. I understand that further optimization can be done to my.cnf , that has been done a lot but with the same results.. andbefore I go to that path again my question is: Iam wondering what other tools exist to load test the mysql daemon, or how to better debug this situation... more tools must exist out there? Perhaps there must be a PHP/DB that I can load... and run a stress test like you would test network issues with speedtest.net just a thought.. I know you dont compare apples to oranges. Thanks David
Basic SQL Query Help Needed
I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
recovery help needed
We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
You have to reset the permissions. http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Carlos On 8/25/2009 7:12 PM, Joe wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Basic SQL Query Help Needed
SELECT * FROM ORDER o INNER JOIN ORDER_LINE_ITEMS o_l ON (o.id=o_l.id) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. To: mysql@lists.mysql.com From: c...@hosting4days.com Subject: Basic SQL Query Help Needed Date: Tue, 25 Aug 2009 16:21:45 -0700 I have a basic invoice table with related line items table Goal :I'd like to get ALL the related line items - for ALL the 'open' invoices... -- this should get a list of open (unpaid) invoices $query_invoice = SELECT DISTINCT ID from invoices where status = 'open' - -- then I'd like to get ALL the line items - in ALL these 'open' invoices - so how do I write the next SQL statement : $query_items = ??? SELECT ID, NAME from lineitems where --xx??? xx-- ??? Thanks, c...@hosting4days.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ With Windows Live, you can organize, edit, and share your photos. http://www.windowslive.com/Desktop/PhotoGallery
Fail to change MySQL data directory on ubuntu
Hi there, I am using MySQL on ubuntu 8.04. I followed this link http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive to change the data directory of MySQL. After stopping MySQL: sudo /etc/init.d/mysql stop I make a new directory: sudo mkdir /media/disk/MySQL_data then change the ownership of new directory, sudo chown mysql:mysql /media/disk/MySQL_data and copy all data to the new directory, cp -r -p /var/lib/mysql/* /media/disk/MySQL_data/ and deleted all files like ibdata1, ib_logfile0, and ib_logfile1. I then edit /etc/mysql/my.conf and update the datadir to my new directory. I also update /etc/apparmor.d/usr.sbin.mysql so that news lines with /var/lib/mysql replaced by /media/disk/MySQL_data are added. However, after sudo /etc/init.d/apparmor reload I try sudo /etc/init.d/mysql start I got * Starting MySQL database server mysqld [fail] If I change the datadir line in /etc/mysql/my.conf back to the original one, I can start MySQL successfully. I think I have done everything needed to change MySQL data directory. Why am I still getting this error? Where can I start to look for the causes? Thanks. Jia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: More ways to debug mysql slowness..?
David, At 03:28 PM 8/25/2009, David Taveras wrote: Hello, We have a BSD box with the following installed: mysql-client-5.0.77 multithreaded SQL database (client) mysql-server-5.0.77 multithreaded SQL database (server) p5-DBD-mysql-4.010 MySQL drivers for the Perl DBI php5-mysql-5.2.8mysql database access extensions for php5 We are experiencing intermittent slowdowns on the queries made with PHP to mysql to the point where pages take a lot of time to load, upon further investigation with mytop we observe that it only keep an average of 1-2 simultenaous threads and a query time of avg 2-3 seconds. That is extremely slow. Look at your slow query log to see which queries are slow. You can post them here and maybe someone can help you to optimize the query. How much memory does the server have? How large are the tables? During which the mysqld process reaches 99% continously for minutes. We have repaired and optimized the tables, and the DB is 200mb. The storage engine is MyISAM. I understand that further optimization can be done to my.cnf , that has been done a lot but with the same results.. andbefore I go to that path again my question is: Iam wondering what other tools exist to load test the mysql daemon, or how to better debug this situation... more tools must exist out there? Perhaps there must be a PHP/DB that I can load... and run a stress test like you would test network issues with speedtest.net just a thought.. I know you dont compare apples to oranges. Thanks David Take a look at MONyog from http://webyog.com/en/. It will monitor the MySQL server. They have a trial download available. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MySQL Encryption - Third-party tools
You might consider a full disk/volume/partition encryption maybe? http://truecrypt.com/ I use this for my private files and it's awesome. Cross platform and can do virtual partitions on an actual physical drive. -Original Message- From: philip [mailto:phi...@livenet.ac.uk] Sent: Tuesday, August 25, 2009 2:08 AM To: Mike Scully Cc: mysql@lists.mysql.com Subject: Re: MySQL Encryption - Third-party tools On Mon, 24 Aug 2009, Mike Scully wrote: Hello, all. =20 Can any of you share with me the names of any third-party tools or appliances that you are using to encrypt your MySQL databases? I am doing a search and would like to narrow down the initial search list. Thanks! =20 Mike I use ccrypt from http://ccrypt.sourceforge.net to encrypt databases before storing them on removable media for offsite storage. Platform is a Sun Ultra 45 running Solaris 10 and the command looks something like, mysqldump db_name | bzip2 | ccrypt -e -k keyfile | ... (Note the bzip2 in the pipeline. This reduces the size of the files by a factor of between 5-8.) It's certainly more than fast enough for my needs. I suspect most of the time is taken up by mysqldmp and writing the ouput to the external media. -- TTFN Philip Riebold, p.rieb...@ucl.ac.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
reverting to passwd-less root w/out --skip-grant-tables
I'm trying to get back to an earlier state where we started mysqld withOUT --skip-grant-tables but the root user had no password. Yes, insecure, but we're in restoration mode here. How do I reset/revert the root password to no password without running with --skip-grant-tables? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fail to change MySQL data directory on ubuntu
First, check the error log, if you can't find it, start mysql from the cmd line by running safe_mysqld it should print errors to console. If it is a permission issue, it might be caused be selinux, you'll need to change the object type od that new directory to mysqld- something. I can't recall the command. A google search on selinux and mysql should produce good results. On Tuesday, August 25, 2009, chen jia chen.1...@gmail.com wrote: Hi there, I am using MySQL on ubuntu 8.04. I followed this link http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive to change the data directory of MySQL. After stopping MySQL: sudo /etc/init.d/mysql stop I make a new directory: sudo mkdir /media/disk/MySQL_data then change the ownership of new directory, sudo chown mysql:mysql /media/disk/MySQL_data and copy all data to the new directory, cp -r -p /var/lib/mysql/* /media/disk/MySQL_data/ and deleted all files like ibdata1, ib_logfile0, and ib_logfile1. I then edit /etc/mysql/my.conf and update the datadir to my new directory. I also update /etc/apparmor.d/usr.sbin.mysql so that news lines with /var/lib/mysql replaced by /media/disk/MySQL_data are added. However, after sudo /etc/init.d/apparmor reload I try sudo /etc/init.d/mysql start I got * Starting MySQL database server mysqld [fail] If I change the datadir line in /etc/mysql/my.conf back to the original one, I can start MySQL successfully. I think I have done everything needed to change MySQL data directory. Why am I still getting this error? Where can I start to look for the causes? Thanks. Jia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fail to change MySQL data directory on ubuntu
I run sudo /etc/init.d/mysql start and check the syslog by running sudo tail -f /var/log/syslog This is what I get Aug 25 22:18:06 chenj-desktop mysqld_safe[10934]: started Aug 25 22:18:06 chenj-desktop kernel: [11083.933531] type=1503 audit(1251253086.020:43): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop kernel: [11083.933581] type=1503 audit(1251253086.020:44): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=0 name=/home/chenj/MySQL_data/chenj-desktop.lower-test pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] option 'thread_stack': unsigned value 128 adjusted to 131072 Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 [Warning] Can't create test file /home/chenj/MySQL_data/chenj-desktop.lower-test Aug 25 22:18:06 chenj-desktop mysqld[10937]: 090825 22:18:06 InnoDB: Operating system error number 13 in a file operation. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: The error means mysqld does not have the access rights to Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: the directory. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File name ./ibdata1 Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: File operation call: 'create'. Aug 25 22:18:06 chenj-desktop mysqld[10937]: InnoDB: Cannot continue operation. Aug 25 22:18:06 chenj-desktop kernel: [11083.962674] type=1503 audit(1251253086.048:45): operation=inode_create requested_mask=a:: denied_mask=a:: fsuid=110 name=/home/chenj/MySQL_data/ibdata1 pid=10936 profile=/usr/sbin/mysqld Aug 25 22:18:06 chenj-desktop mysqld_safe[10944]: ended Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists! Aug 25 22:18:20 chenj-desktop /etc/init.d/mysql[11094]: Does this message tell me any thing about the problem? Thanks. Best, Jia Johnny Withers wrote: First, check the error log, if you can't find it, start mysql from the cmd line by running safe_mysqld it should print errors to console. If it is a permission issue, it might be caused be selinux, you'll need to change the object type od that new directory to mysqld- something. I can't recall the command. A google search on selinux and mysql should produce good results. On Tuesday, August 25, 2009, chen jia chen.1...@gmail.com wrote: Hi there, I am using MySQL on ubuntu 8.04. I followed this link http://www.ubuntu-howto.info/howto/how-to-move-mysql-databases-to-another-location-partition-or-hard-drive to change the data directory of MySQL. After stopping MySQL: sudo /etc/init.d/mysql stop I make a new directory: sudo mkdir /media/disk/MySQL_data then change the ownership of new directory, sudo chown mysql:mysql /media/disk/MySQL_data and copy all data to the new directory, cp -r -p /var/lib/mysql/* /media/disk/MySQL_data/ and deleted all files like ibdata1, ib_logfile0, and ib_logfile1. I then edit /etc/mysql/my.conf and update the datadir to my new directory. I also update /etc/apparmor.d/usr.sbin.mysql so that news lines with /var/lib/mysql replaced by /media/disk/MySQL_data are added. However, after sudo /etc/init.d/apparmor reload I try sudo /etc/init.d/mysql start I got * Starting MySQL database server mysqld [fail] If I change the datadir line in /etc/mysql/my.conf back to the original one, I can start MySQL successfully. I think I have done everything needed to change MySQL data directory. Why am I still getting this error? Where can I start to look for the causes? Thanks. Jia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: recovery help needed
On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Got error 124 from storage engine
Gavin Towey wrote: Which version of mysql are you using? In mysql 4, you could get away with some differences between the definition of the merge table and the underlying tables. As you've discovered, the structure and index definitions must now be exactly the same, otherwise you will get errors. Regards, Gavin Towey -Original Message- From: stutiredboy [mailto:stutired...@gmail.com] Sent: Tuesday, August 25, 2009 12:23 AM To: mysql@lists.mysql.com Subject: Got error 124 from storage engine hi, all: i have met an question as below: table A1,A2 A1 has been *packed by myisampack, and rebuild the index by myisamchk* A2 is a noraml table, and the struct of A1 and A2 is exactlly same talbe A is the merge table of A1 and A2 while i use: * mysql select max(id) from A; ** ERROR 1030 (HY000): Got error 124 from storage engine +---+---+--+-+---++ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+---++ | id | bigint(20) unsigned | NO | MUL | NULL | auto_increment | *but when i try another table, the situation is as before, such as table B1,B2,B * mysql select max(id) from loot; +-+ | max(id) | +-+ | 110415 | +-+ 1 row in set (0.00 sec) * the only difference is (*table A the id Field is auto_increment and table B the id is not*): *+---+---+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+-+-+---+ | id | bigint(20) unsigned | NO | MUL | NULL | | *and if i do not use myisampack/myisamchk, all are work fine, *our system is freebsd 7.2, the mysql version is 5.0.84 Server version: 5.0.84 Source distribution * thanks for your reply tiredboy ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. thanks Gavin yes, of course, the structure are exactly the same our mysql Server version: 5.0.84 Source distribution if i do not pack and re creat the index ,everything work fine may be after i myisampack/myisamchk , the index been changed ? thanks all -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: reverting to passwd-less root w/out --skip-grant-tables
Is there a specific reason you cannot do it with --skip-grant-table? You should theoretically also be able to overwrite the files user.* (there should be 3) in /var/lib/mysql/mysql/ (replace everything up to and including teh first mysql in that path with your mysql data dir) when the server is stopped with a copy from a fresh install. That will wipe all users in your database though, and might have unforeseen consequences depending on what you had defined before. Backup first though! Walter On Wed, Aug 26, 2009 at 03:33, Joemysql@bluepolka.net wrote: I'm trying to get back to an earlier state where we started mysqld withOUT --skip-grant-tables but the root user had no password. Yes, insecure, but we're in restoration mode here. How do I reset/revert the root password to no password without running with --skip-grant-tables? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Hi Query Optimization steps
Then which DB engine is better. Thanks On Mon, Aug 24, 2009 at 8:16 PM, mos mo...@fastmail.fm wrote: At 05:01 AM 8/22/2009, you wrote: Hi all Tell Me few Query Optimization Tips For example.. some people saying that , avoid using more of and condition in the query Huh? You need to carefully plan your table structure and use indexes sparingly on the columns you are using in the Where clauses. An EXPLAIN in front of the Select statement will show you which indexes it is using. This sort of very basic things please tell me ... Also tell me , in the optimization view ,, which database engine is best If you need transactions or RI then you have to use InnoDb. Otherwise MyISAM is quite fast. Again it depends on the type of queries you are executing. Thanks Bharanikumar There is no magic wand to getting MySQL to run faster except planning and knowledge. It will take some effort on your part but it will be fun. Take a look at High Performance MySQL 2nd Edition. Some of the first edition is online at http://books.google.com/books?id=sgMvu2uZXlsCprintsec=frontcoverdq=high+performance+mysql#v=onepageq=f=false You can get the second edition at Amazon. Also you may need to read other MySQL books like MySQL Cookbook http://books.google.com/books?id=aC3blgT3D3YCprintsec=frontcoverdq=mysql+cookbook#v=onepageq=f=false The 2nd edition is at Amazon. or MySQL by Paul DuBois is also quite good. These will get you started on the right foot. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bharanikumariyer...@gmail.com -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
R: Re: recovery help needed
When you are in without the flag , issue the following: Select current_user(); It should return root. Then do this: Grant all privileges on *.* 'root'@'%' identified by 'letmein' It should work If you did not mess too much with grant tables. Claudio Il giorno 26 ago, 2009 4:36 m., Todd Lyons tly...@ivenue.com ha scritto: On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible... So connect to it from somewhere other than localhost. :-) Specifically from the bogus IP you set it to. Let's say you used the bogus IP of 10.200.100.20. 1. Give the mysql server the IP 10.200.100.10 and a netmask 255.255.255.0. 2. On some other machine on the same LAN, give it the bogus IP with the same netmask. 3. No need to worry about routes, it's on the same LAN. 4. On the other machine, connect using 'mysql -h10.200.100.10 -uroot -p'. When you enter the correct password, it should let you in. * I don't know if you'll need to restart mysql for it to bind to the new IP. I don't think so, but then again I have not tested it. -- Regards... Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: h...
