Diff between restore from tar and recover from power outage
Hi When restoring a DB from a tar file, I had to repair some of the tables. Whereas when the DB is started after a power failure, everything works fine. I noticed this on RedHat linux and MySQL5. Any thoughts? Thanks Murthy - Building a website is a piece of cake. Yahoo! Small Business gives you all the tools to get online.
Re: Should the db be shutdown before backing up?
Gerald L. Clark [EMAIL PROTECTED] wrote: Mathieu Bruneau wrote: murthy gandikota a écrit : Hi I did a hot backup a while ago. When I had to restore from backup there were several error messages. So I ran mysqlcheck for every table with --auto-repair option. Is this the best way? Thanks Murthy - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos. Yes you should shutdown the database before doing a mysqlhotcopy. However you could use the flush tables with read lock so that your tables are consistent (and will still allow for select to go through the db) Best luck mysqlhotcopy does not require a database shutdown. If it did, it would not be a hot copy. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] I am sorry for not being clear. I make a tar ball of all the files. I've not used mysqlhotcopy. I hope it creates a backup on the NFS mount. Thanks - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
Should the db be shutdown before backing up?
Hi I did a hot backup a while ago. When I had to restore from backup there were several error messages. So I ran mysqlcheck for every table with --auto-repair option. Is this the best way? Thanks Murthy - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
Should the db be shutdown before backing up?
Hi I did a hot backup a while ago. When I had to restore from backup there were several error messages. So I ran mysqlcheck for every table with --auto-repair option. Is this the best way? Thanks Murthy - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
Re: Is an index on varchar column efficient?
mos [EMAIL PROTECTED] wrote: At 06:23 PM 4/15/2007, you wrote: Hi, I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column? BTW, here is the scenario: create table `test` (`phonenumber` varchar(100)); insert into test (phonenumber) values(`0001234567'); and so on 2 million times Indexes are the least of your worry. The code you've posted is extremely inefficient. The fastest way to load the data is using a Load Data Infile in which you could load the data in a few minutes. As it stands, it could take a few hours or even a couple of days. Every time the Insert statement is executed, it will lock the table, preventing others from reading from it. So the speed of accessing the table during the inserts is going to be very very slow. You'd be better off inserting the data into a temporary table and then if the data looks ok, insert it into the production table a few thousand rows at a time so it doesn't lock out people that are using Select statements. Use something like: insert into table1 select * from tmptable limit [offset,] 1 and keep bumping up the offset each time its run. If no one else is using the table1, then just load all the data at once without the limit clause. alter table test add index (phonenumber) Also, I can't modify the code that calls the SQL. It was written in VB and the developer has left the company with the source. It's a good thing that programmer left the company, because writing code like this would get him canned. If your company paid for the source, contact a lawyer and hunt him down for theft of company property. It shouldn't be too hard to figure out where he works. His behavior should also impress his new boss too. :) I would like to get your feedback before I actually do this because I don't want to create a disruption when the DB is in production. If you can't first load the data into a temporary table, then you'd better expect a lot of down time as it tries to load 2 million rows, 1 row at a time. If it were me, I'd rewrite the code from scratch. Make sure you have sufficient memory for key_buffer_size because building an index will take less time if it fits in memory. Good luck, and remember to make lots of coffee because it could take a while. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thank you Mike for your insightful comments. I've used the insert for illustration purposes. The actual production table gets inserted in batches. For instance, 1000 records on Monday, 1000 records on Saturday, etc. - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
Re: Is an index on varchar column efficient?
Baron Schwartz [EMAIL PROTECTED] wrote: murthy gandikota wrote: Hi, I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column? BTW, here is the scenario: create table `test` (`phonenumber` varchar(100)); insert into test (phonenumber) values(`0001234567'); and so on 2 million times alter table test add index (phonenumber) An index is probably beneficial. You can find the cardinality of the proposed index (number of distinct values the column has) with COUNT(*) queries. If you divide this and the number of rows in the table, you get the index's selectivity. In general you want a selectivity greater than 1/3 for the index to be useful. Higher is better, all other things being equal. Supposing you have 2 million records, SELECT COUNT(DISTINCT phonenumber)/200 AS selectivity FROM test; Now, you may be able to index just a prefix of the column and still get as much or almost as much selectivity. Try this: SELECT COUNT(DISTINCT LEFT(phonenumber,5))/200 AS selectivity FROM test; Play with the number of characters until you find a good balance between the selectivity and the space the index will take. The smaller the prefix, the smaller the index. In the end you will have to profile the queries you're going to run to figure out exactly what will be the best for your situation. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thank you Baron for your kind suggestion. Guess I have to bite the bullet and do the indexing based on your suggestion. Thanks again. - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
Is an index on varchar column efficient?
Hi, I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column? BTW, here is the scenario: create table `test` (`phonenumber` varchar(100)); insert into test (phonenumber) values(`0001234567'); and so on 2 million times alter table test add index (phonenumber) Also, I can't modify the code that calls the SQL. It was written in VB and the developer has left the company with the source. I would like to get your feedback before I actually do this because I don't want to create a disruption when the DB is in production. Thanks Murthy - Ahhh...imagining that irresistible new car smell? Check outnew cars at Yahoo! Autos.
Re: LEFT/RIGHT Joins not working
Many thanks for your input. Looks like the older version of mysql, preceding
5.0 has some problems. I've to upgrade mysql now. :-(
Thanks again.
Mogens Melander [EMAIL PROTECTED] wrote:
Well, trying your example gives me the expected result.
select cust.ssn, disposition.disposition, source.source
from cust LEFT JOIN source ON (cust.source=source.id)
LEFT JOIN disposition ON (cust.disposition=disposition.id);
'123456789', 'dispo2', 'source1'
'123456780', 'dispo1', 'source2'
'123456781', null, null
On Wed, April 4, 2007 00:03, murthy gandikota wrote:
Hi Mike
Thanks for your input. I read the page before and it caused more
confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL,
NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT
JOIN source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL | NULL |
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT
JOIN source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL | NULL |
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than
satisfactory. The RIGHT join does not show where disposition is NULL.
The LEFT join shows dispositions as NULL where they shouldn't be. Also
the LEFT join generates more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN
disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource
on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as
SSN, disposition.disposition as DISPOSITION, leadSource.source as
SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as
CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN
disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get
LEFT/RIGHT Joins not working
I tried the following 2 SQL's and the results are less than satisfactory. The RIGHT join does not show where disposition is NULL. The LEFT join shows dispositions as NULL where they shouldn't be. Also the LEFT join generates more dupes. Any way to fix this? select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on (cust.disposition=disposition.id) RIGHT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS, cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN, disposition.disposition as DISPOSITION, leadSource.source as SOURCE, cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT, cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on (cust.disposition=disposition.id) LEFT JOIN leadSource on (cust.source=leadSource.id) where agentCode=11 and newPayment 0 and cust.disposition 0 order by SOURCE, DISPOSITION The MYSQL has the following version mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686) Thanks for your help Murthy - Don't get soaked. Take a quick peek at the forecast with theYahoo! Search weather shortcut.
Re: LEFT/RIGHT Joins not working
Hi Mike
Thanks for your input. I read the page before and it caused more confusion.
May be an example would clarify this:
create table `cust` (`ssn` varchar(10), `source` int(3) , `disposition`
int(3));
insert into cust (ssn, source, disposition) values ('123456789', 1, 2);
insert into cust (ssn, source, disposition) values ('123456780', 2, 1);
insert into cust (ssn, source, disposition) values ('123456781', NULL, NULL);
create table `source` (`id` int(3), `source` varchar(10));
insert into source(id, source) values (1, 'source1');
insert into source(id,source) values (2, 'source2');
create table `disposition` (`id` int(3), `disposition` varchar(10));
insert into disposition (id, disposition) values (1, 'dispo1');
insert into disposition(id,disposition) values (2, 'dispo2');
Now I run the sql:
select cust.ssn, disposition.disposition, source.source from cust LEFT JOIN
source ON (cust.source=source.id) LEFT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I don't want this cos the ssn 123456780 is shown twice
select cust.ssn, disposition.disposition, source.source from cust RIGHT JOIN
source ON (cust.source=source.id) RIGHT JOIN disposition ON
(cust.disposition=disposition.id)
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456780 | dispo1 | source2 |
+---+-+-+
This has the same problem.
All I want is
+---+-+-+
| ssn | disposition | source |
+---+-+-+
| 123456789 | dispo2 | source1 |
| 123456780 | dispo1 | source2 |
| 123456781 | NULL| NULL|
+---+-+-+
I'd appreciate your help.
Thanks
Murthy
Michael Dykman [EMAIL PROTECTED] wrote:
a left join and a right join are 2 very distinct things... It is not
clear from your text what it is you exactly are going for here but I
doubt that applying either LEFT or RIGHT to ALL of your (many) joins
is going to give it to you. You need to stop and examine the
relationships between the tables in this query and determine which
class of JOIN you will need (and there are more than just these 2).
The description you gave of your results using RIGHT and LEFT
universally are consistent with what I would expect from those types
of joins. I suggest that you read this page very carefully before you
continue:
http://dev.mysql.com/doc/refman/5.1/en/join.html
- michael
On 4/3/07, murthy gandikota wrote:
I tried the following 2 SQL's and the results are less than satisfactory. The
RIGHT join does not show where disposition is NULL. The LEFT join shows
dispositions as NULL where they shouldn't be. Also the LEFT join generates
more dupes. Any way to fix this?
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN,
disposition.disposition as DISPOSITION, leadSource.source as SOURCE,
cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT,
cust.newPayment as NEWPAYMENT from cust RIGHT JOIN disposition on
(cust.disposition=disposition.id) RIGHT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
select cust.first as FIRST, cust.last as LAST, cust.address AS ADDRESS,
cust.city as CITY, cust.state as STATE, cust.zip as ZIP, cust.ssn as SSN,
disposition.disposition as DISPOSITION, leadSource.source as SOURCE,
cust.loanBalance AS LOANBALANCE, cust.currentPayment as CURRENTPAYMENT,
cust.newPayment as NEWPAYMENT from cust LEFT JOIN disposition on
(cust.disposition=disposition.id) LEFT JOIN leadSource on
(cust.source=leadSource.id) where agentCode=11 and newPayment 0 and
cust.disposition 0 order by SOURCE, DISPOSITION
The MYSQL has the following version
mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Thanks for your help
Murthy
-
Don't get soaked. Take a quick peek at the forecast
with theYahoo! Search weather shortcut.
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
-
8:00? 8:25? 8:40? Find a flick in no time
with theYahoo! Search movie showtime shortcut.
What does NOW() return: linux time or something else?
Hi I tried to look up the time functions specifically for the disparity between NOW() and the linux time obtained with 'date'. Why are they different? How can I set the database time? Thanks Murthy - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Disk parition full
Hi I have looked up earlier postings on disk partitions and mysql and couldn't find any discussion threads. Mysql is storing data in the /usr partition of Redhat 9. The /usr partition is about 50% full. Within a year it will outlast the available space on /usr. The /var partition on the same machine has lots of free space. I am thinking if it is possible to store the data in the /var paritition, that would be a temporary fix until I can move the whole operation on to a bigger disk which could take a year (given the company bottom line :). Can someone please tell me how to manage multiple disk partitions in mysql? BTW, the mysql is version 4 or something like that. Thanks Murthy - Don't be flakey. Get Yahoo! Mail for Mobile and always stay connected to friends.
Re: JSP/JSTL problem
Many thanks for your kind response. I was using a form that was submitted twice
for any given new ssn. I have also benefited from your comments on how to write
a better java code. Turns out my html and javascript skills need improvement.
Many thanks again.
Regards
David Griffiths [EMAIL PROTECTED] wrote:
This is a little dangerous as well; if an exception gets thrown, the
statement doesn't get closed. My sample code is below.
That said, your query looks fine. Add logging to your code to figure out
what's going on. Run your query by hand against your database; I suspect
you have an issue with your data.
One final note before the source code - don't use values that have
real-world meaning as primary keys. If the format of the SSN changes,
and you create a foreign key back to the cust table, you've added a
headache. Create auto-increment primary keys that are just value-less
numbers, and create a unique index on the ssn column.
PreparedStatement stmt = null;
ResultSet rs = null;
try
{
try
{
// Check the SSN to make sure it's a number before you start
preparing statements
Integer iSSN = new Integer(ssn);
}
catch (NumberFormatException e)
{
out.println(The ssn is not a valid number: + ssn);
return;
}
stmt = conn.prepareStatement(SELECT ssn, first, last FROM cust
WHERE ssn = ?);
stmt.setInt(1, iSSN.intValue());
rs = stmt.executeQuery();
if (rs.next())
{
out.println(Customer exists: + rs.getString(1));
}
else
{
out.println(The SSN could not be found in the database: + id);
}
}
catch (Exception e)
{
// Log it, or whatever
}
finally
{
// The statements here will always be called, exception or no. By
wrapping the .close() statements in a try-catch, you guarantee that
// each resource will have close called on it before returning
try
{
rs.close();
}
catch (Exception e) {}
try
{
stmt.close();
}
catch (Exception e) {}
try
{
conn.close();
}
catch (Exception e) {}
}
David
murthy gandikota wrote:
I tried everything you suggested. 'Think it is the way I have set up
the table in MYSQL. This is the table
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| ssn | int(9) | | PRI | | |
| submitdate | date | YES | | NULL | |
| submitto | int(3) | YES | | NULL | |
| first | varchar(30) | YES | | NULL | |
| last | varchar(30) | YES | | NULL | |
| loanAmt | decimal(10,2) | YES | | NULL | |
| company | int(3) | YES | | NULL | |
| fee | decimal(10,2) | YES | | NULL | |
| appType | int(3) | YES | | NULL | |
| appSource | int(3) | YES | | NULL | |
| appStatus | int(3) | YES | | NULL | |
| dateStatus | date | YES | | NULL | |
| fundedAmt | decimal(10,2) | YES | | NULL | |
++---+--+-+-+---+
Hassan Schroeder wrote: On 1/2/07, murthy
gandikota wrote:
ps = con.prepareStatement(select first, last from cust where ssn=?);
int ssnint = Integer.parseInt(ssn.trim());
ps.setInt(1, ssnint);
ResultSet rs=ps.executeQuery();
if ( rs.next()) {
rs.close();
out.println(Customer already exists + Integer.parseInt(ssn));
return;
}
I get the message customer already exists for EVERY ssn that I
tried.
Not sure how you're actually running this, but it looks dangerous -- if
rs.next() is false, you're not closing that ResultSet object. And the
`return`
seems pointless here. What happens if you change that 'if' to 'while',
and
print out the first, last, ssn results? (and for good measure change
that
SELECT statement to 'SELECT ssn, first, last').
For comparison, here's some simple code similar to yours, which works
exactly as expected: if messageId doesn't exist in the DB, it prints
out
the not a valid id message.
stmt = conn.prepareStatement(SELECT messageText FROM messages WHERE
messageId = ?);
stmt.setInt(1, id);
rs = stmt.executeQuery();
if ( rs == null )
{
out.println(null ResultSet
);
// never happens :-)
}
if (rs.next())
{
out.println(rs.getString(1) +
);
}
else
{
out.println(not a valid id);
}
rs.close();
stmt.close();
conn.close();
HTH,
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: JSP/JSTL problem
Here is the code:
ps = con.prepareStatement(select first, last from cust where ssn=?);
int ssnint = Integer.parseInt(ssn.trim());
ps.setInt(1, ssnint);
ResultSet rs=ps.executeQuery();
if ( rs.next()) {
rs.close();
out.println(Customer already exists + Integer.parseInt(ssn));
return;
}
do insert (not shown)
I get the message customer already exists for EVERY ssn that I tried.
Hassan Schroeder [EMAIL PROTECTED] wrote:
On 12/30/06, murthy gandikota wrote:
I am getting the same result in Java code, i.e. the resultset returned is
non-null even though the primary key value is not found in the table.
Without seeing code, it's hard to say -- but I'll just guess that you're
assuming an *empty* ResultSet is a null object, and that's simply not
true.
HTH,
--
Hassan Schroeder [EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: JSP/JSTL problem
I tried everything you suggested. 'Think it is the way I have set up
the table in MYSQL. This is the table
++---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
++---+--+-+-+---+
| ssn| int(9)| | PRI | | |
| submitdate | date | YES | | NULL| |
| submitto | int(3)| YES | | NULL| |
| first | varchar(30) | YES | | NULL| |
| last | varchar(30) | YES | | NULL| |
| loanAmt| decimal(10,2) | YES | | NULL| |
| company| int(3)| YES | | NULL| |
| fee| decimal(10,2) | YES | | NULL| |
| appType| int(3)| YES | | NULL| |
| appSource | int(3)| YES | | NULL| |
| appStatus | int(3)| YES | | NULL| |
| dateStatus | date | YES | | NULL| |
| fundedAmt | decimal(10,2) | YES | | NULL| |
++---+--+-+-+---+
Hassan Schroeder [EMAIL PROTECTED] wrote: On 1/2/07, murthy gandikota wrote:
ps = con.prepareStatement(select first, last from cust where ssn=?);
int ssnint = Integer.parseInt(ssn.trim());
ps.setInt(1, ssnint);
ResultSet rs=ps.executeQuery();
if ( rs.next()) {
rs.close();
out.println(Customer already exists + Integer.parseInt(ssn));
return;
}
I get the message customer already exists for EVERY ssn that I tried.
Not sure how you're actually running this, but it looks dangerous -- if
rs.next() is false, you're not closing that ResultSet object. And the `return`
seems pointless here. What happens if you change that 'if' to 'while', and
print out the first, last, ssn results? (and for good measure change that
SELECT statement to 'SELECT ssn, first, last').
For comparison, here's some simple code similar to yours, which works
exactly as expected: if messageId doesn't exist in the DB, it prints out
the not a valid id message.
stmt = conn.prepareStatement(SELECT messageText FROM messages WHERE
messageId = ?);
stmt.setInt(1, id);
rs = stmt.executeQuery();
if ( rs == null )
{
out.println(null ResultSet
);
// never happens :-)
}
if (rs.next())
{
out.println(rs.getString(1) +
);
}
else
{
out.println(not a valid id);
}
rs.close();
stmt.close();
conn.close();
HTH,
--
Hassan Schroeder [EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: JSP/JSTL problem
I am getting the same result in Java code, i.e. the resultset returned is non-null even though the primary key value is not found in the table. Hassan Schroeder [EMAIL PROTECTED] wrote: On 12/29/06, murthy gandikota wrote: I am posting the relevant JSTL code. Just to clarify: you're *not* using JSTL -- you appear to be using the deprecated Jakarta DBTags taglib. Might want to rewrite it in JSTL and see if that helps :-) FWIW, -- Hassan Schroeder [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
JSP/JSTL problem
Hi I have created a table with the following specs: create table `cust` ( `ssn` int(9) NOT NULL PRIMARY KEY, `submitdate` date, `submitto` int(3), `first` varchar(30), `last` varchar(30), `loanAmt` decimal(10,2), `company` int(3), `fee` int(3), `appType` int(3), `appSource` int(3), `appStatus` int(3), `dateStatus` date, `fundedAmt` decimal(10,2) ) When I try to do select (first, last) where ssn=notthere I get a Result Set that is non null even though the ssn is not in the table. My logic is as follows: a) if ssn is found returnsql:connection id=conn1 sql:urljdbc:mysql://trig2:3306/appsubmit?user=bsteinpassword=siket281/sql:url sql:drivercom.mysql.jdbc.Driver/sql:driver /sql:connection !-- jsp:getProperty name=conn1 property=closed/ -- sql:preparedStatement id=stmt conn=conn1 sql:query select first, last from cust where ssn=? /sql:query sql:setColumn position=1%= ssn %/sql:setColumn sql:resultSet id=rset sql:getColumn colName=first to=fname / sql:getColumn colName=last to =lname/ /sql:resultSet sql:wasEmpty sql:preparedStatement id=stmt2 conn=conn1 Applicant successfully added sql:query insert into cust (ssn, submitdate, submitto, first, last, loanAmt, company, fee, appType, appSource) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) /sql:query sql:execute sql:setColumn position=1 %= Integer.parseInt(ssn) % /sql:setColumn sql:setColumn position=2 %= + - + mm + - + dd % /sql:setColumn sql:setColumn position=3 %= Integer.parseInt(finance) % /sql:setColumn sql:setColumn position=4 %= first % /sql:setColumn sql:setColumn position=5 %= last % /sql:setCosql:setColumn position=9 %= Integer.parseInt(apptype) %/sql:setColumn sql:setColumn position=10 %= Integer.parseInt(appsource) %/sql:setColumn /sql:execute /sql:preparedStatement /sql:wasEmpty sql:wasNotEmpty Applicant already present/sql:wasNotEmpty /sql:preparedStatement sql:closeConnection conn=conn1/ lumn sql:setColumn position=6 %= Float.parseFloat(loanAmt) %/sql:setColumn sql:setColumn position=7 %= Integer.parseInt(company) %/sql:setColumn sql:setColumn position=8 %= Integer.parseInt(fee) %/sql:setColumn b) if ssn is not found then insert I am posting the relevant JSTL code. I'd appreciate your kind comments. Thanks. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Can Primary Key be added with ALTER?
Hi I have a table with no primary key. I'd like to make one of the table columns as the primary key. Is it possible do it with ALTER? Is there any other way? Many thanks for your help. Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Hi Can someone tell me where in the file system to look for the logs? Mysql has been crashing once every hour. Memory is not the issue. This is how I start the mysql: nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/ local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/admin.scholasticfun dinggroup.com.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock I'd appreciate your help. Thanks - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta.
Re: Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Can someone please help me figure out what's in this mysqld output before crashing: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=101 max_connections=100 threads_connected=101 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfffa3ac, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8166bc0 0xb748cdf8 0xb7356f1b 0x815e8af 0x815f55b 0x8166765 0x816a3f5 0x8167b7d 0x8167774 0xb72fb748 0x80e52d1 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. murthy gandikota [EMAIL PROTECTED] wrote: Hi Can someone tell me where in the file system to look for the logs? Mysql has been crashing once every hour. Memory is not the issue. This is how I start the mysql: nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/ local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/admin.scholasticfun dinggroup.com.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock I'd appreciate your help. Thanks - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta. - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta.
Re: Crashing mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686)
Looks like I have too many connections to the database. I made sure all the connections are closed after use. Hope this is the fix. Anyone please confirm. Thanks murthy gandikota [EMAIL PROTECTED] wrote: Can someone please help me figure out what's in this mysqld output before crashing: mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=101 max_connections=100 threads_connected=101 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfffa3ac, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8166bc0 0xb748cdf8 0xb7356f1b 0x815e8af 0x815f55b 0x8166765 0x816a3f5 0x8167b7d 0x8167774 0xb72fb748 0x80e52d1 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. murthy gandikota wrote: Hi Can someone tell me where in the file system to look for the logs? Mysql has been crashing once every hour. Memory is not the issue. This is how I start the mysql: nohup /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/ local/mysql/var --user=mysql --pid-file=/usr/local/mysql/var/admin.scholasticfun dinggroup.com.pid --skip-locking --port=3306 --socket=/var/lib/mysql/mysql.sock I'd appreciate your help. Thanks - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta. - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta. - Do you Yahoo!? Get on board. You're invited to try the new Yahoo! Mail Beta.
ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
I'm getting the following error ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1) This is the output from mysqld 060613 19:59:34 InnoDB error: Cannot find table sfgnew/agent from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem Can anyone please help me figure this out? Here are some pertinent facts: a) stopped mysqld b) copied files from sfg to sfgnew in the var directory c) set the permissions to 777 (read, write, execute) for all the files in sfgnew d) restarted mysqld Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
Hi Dan
I've set the permissions to 777 for all the files and directories. Basically
I did chmod -R 777 sfgnew. Also changed the ownership to mysql using chown -R
mysql:mysql sfgnew.
Thanks!
Dan Buettner [EMAIL PROTECTED] wrote:
Murthy, do you have appropriate permissions (777 works, or 775/770 if
owned by mysql user) on your new directory ('sfgnew')? If you don't
have execute permission for the mysql user on that dir, MySQL can't list
the contents ...
Dan
murthy gandikota wrote:
I'm getting the following error
ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
This is the output from mysqld
060613 19:59:34 InnoDB error:
Cannot find table sfgnew/agent from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem
Can anyone please help me figure this out?
Here are some pertinent facts:
a) stopped mysqld
b) copied files from sfg to sfgnew in the var directory
c) set the permissions to 777 (read, write, execute) for all the files in
sfgnew
d) restarted mysqld
Murthy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
Hi Dan
The table was never dropped. I removed the agent.frm file and tried. Here is
the output:
mysql desc agent;
ERROR 1146 (42S02): Table 'sfgn.agent' doesn't exist
Dan Buettner [EMAIL PROTECTED] wrote:
Is the 'agent' table a current table, or one that got dropped at some point?
See
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html
The last half seems relevant - it's pointing you to removing the .frm
file manually. Personally, I'd mv it and not rm it.
Dan
murthy gandikota wrote:
Hi Dan
I've set the permissions to 777 for all the files and directories.
Basically I did chmod -R 777 sfgnew. Also changed the ownership to mysql
using chown -R mysql:mysql sfgnew.
Thanks!
*/Dan Buettner /* wrote:
Murthy, do you have appropriate permissions (777 works, or 775/770 if
owned by mysql user) on your new directory ('sfgnew')? If you don't
have execute permission for the mysql user on that dir, MySQL can't
list
the contents ...
Dan
murthy gandikota wrote:
I'm getting the following error
ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
This is the output from mysqld
060613 19:59:34 InnoDB error:
Cannot find table sfgnew/agent from the internal data dictionary
of InnoDB though the .frm file for the table exists. Maybe you
have deleted and recreated InnoDB data files but have forgotten
to delete the corresponding .frm files of InnoDB tables, or you
have moved .frm files to another database?
Look from section 15.1 of http://www.innodb.com/ibman.html
how you can resolve the problem
Can anyone please help me figure this out?
Here are some pertinent facts:
a) stopped mysqld
b) copied files from sfg to sfgnew in the var directory
c) set the permissions to 777 (read, write, execute) for all the
files in sfgnew
d) restarted mysqld
Murthy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
I can't get mysqldump to work. I typed
mysql -uuser -ppassword sfg sfgdump.sql
The error is
ERROR 2013 (HY000): Lost connection to MySQL server during query
phpninja [EMAIL PROTECTED] wrote:
I prefer the mysqldump utility for transfering data. Its pretty fast (if
your tables are not huge gigabytes in size).. and preserves you tables/data
just as they were when you moved them. As far as I know, copying data on the
filesystem level in mysql does not work. You cannot copy data files
somewhere else and change the path and expect it to work. Use mysqldump or
somthing similar to recreate your tables and move them. HTH.
-phpninja
-Original Message-
From: Dan Buettner
[*mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 14, 2006 8:12 AM
To: murthy gandikota
Cc: mysql@lists.mysql.com
Subject: Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
Murthy, doing some quick reading on InnoDB table definitions, it appears
that MySQL itself keeps some info in the .frm file, while InnoDB keeps some
info within the tablespace.
While you can easily move MyISAM table files about amongst MySQL database
directories (mind you stop the server and that MySQL access permissions
won't follow), the same appears to not be true for InnoDB.
I'm not sure what the best way to move or copy an InnoDB table or tables
from one database to another is ... anyone?
Dan
murthy gandikota wrote:
Hi Dan
The table was never dropped. I removed the agent.frm file and tried. Here
is the output:
mysql desc agent;
ERROR 1146 (42S02): Table 'sfgn.agent' doesn't exist
Dan Buettner wrote:
Is the 'agent' table a current table, or one that got dropped at some
point?
See
*http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict*
.html The last half seems relevant - it's pointing you to removing the
.frm file manually. Personally, I'd mv it and not rm it.
Dan
murthy gandikota wrote:
Hi Dan
I've set the permissions to 777 for all the files and directories.
Basically I did chmod -R 777 sfgnew. Also changed the ownership to
mysql using chown -R mysql:mysql sfgnew.
Thanks!
*/Dan Buettner /* wrote:
Murthy, do you have appropriate permissions (777 works, or 775/770 if
owned by mysql user) on your new directory ('sfgnew')? If you don't
have execute permission for the mysql user on that dir, MySQL can't
list the contents ...
Dan
murthy gandikota wrote:
I'm getting the following error
ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
This is the output from mysqld
060613 19:59:34 InnoDB error:
Cannot find table sfgnew/agent from the internal data dictionary of
InnoDB though the .frm file for the table exists. Maybe you have
deleted and recreated InnoDB data files but have forgotten to delete
the corresponding .frm files of InnoDB tables, or you have moved
.frm files to another database?
Look from section 15.1 of
*http://www.innodb.com/ibman.html*how
you
can resolve the problem
Can anyone please help me figure this out?
Here are some pertinent facts:
a) stopped mysqld
b) copied files from sfg to sfgnew in the var directory
c) set the permissions to 777 (read, write, execute) for all the
files in sfgnew
d) restarted mysqld
Murthy
--
MySQL General Mailing List
For list archives:
*http://lists.mysql.com/mysql*To
unsubscribe:
*http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
*http://mail.yahoo.com*
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
*http://mail.yahoo.com*
--
MySQL General Mailing List
For list archives: *http://lists.mysql.com/mysql*
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
Error 2013 went away when I typed create database sfg at the mysql client
prompt before loading the dump. I have asked this before. Let me try again.
How can I take the dump of a database db1 and load it into another database db2
on the same host? If this looks like a backup operation, please note that I am
only interested in preserving the schema without the data. Is it possible to
just transfer the schema to a new database?
Thanks
murthy gandikota [EMAIL PROTECTED] wrote:
I can't get mysqldump to work. I typed
mysql -uuser -ppassword sfg sfgdump.sql
The error is
ERROR 2013 (HY000): Lost connection to MySQL server during query
phpninja
wrote:
I prefer the mysqldump utility for transfering data. Its pretty fast (if
your tables are not huge gigabytes in size).. and preserves you tables/data
just as they were when you moved them. As far as I know, copying data on the
filesystem level in mysql does not work. You cannot copy data files
somewhere else and change the path and expect it to work. Use mysqldump or
somthing similar to recreate your tables and move them. HTH.
-phpninja
-Original Message-
From: Dan Buettner
[*mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 14, 2006 8:12 AM
To: murthy gandikota
Cc: mysql@lists.mysql.com
Subject: Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
Murthy, doing some quick reading on InnoDB table definitions, it appears
that MySQL itself keeps some info in the .frm file, while InnoDB keeps some
info within the tablespace.
While you can easily move MyISAM table files about amongst MySQL database
directories (mind you stop the server and that MySQL access permissions
won't follow), the same appears to not be true for InnoDB.
I'm not sure what the best way to move or copy an InnoDB table or tables
from one database to another is ... anyone?
Dan
murthy gandikota wrote:
Hi Dan
The table was never dropped. I removed the agent.frm file and tried. Here
is the output:
mysql desc agent;
ERROR 1146 (42S02): Table 'sfgn.agent' doesn't exist
Dan Buettner wrote:
Is the 'agent' table a current table, or one that got dropped at some
point?
See
*http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict*
.html The last half seems relevant - it's pointing you to removing the
.frm file manually. Personally, I'd mv it and not rm it.
Dan
murthy gandikota wrote:
Hi Dan
I've set the permissions to 777 for all the files and directories.
Basically I did chmod -R 777 sfgnew. Also changed the ownership to
mysql using chown -R mysql:mysql sfgnew.
Thanks!
*/Dan Buettner /* wrote:
Murthy, do you have appropriate permissions (777 works, or 775/770 if
owned by mysql user) on your new directory ('sfgnew')? If you don't
have execute permission for the mysql user on that dir, MySQL can't
list the contents ...
Dan
murthy gandikota wrote:
I'm getting the following error
ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
This is the output from mysqld
060613 19:59:34 InnoDB error:
Cannot find table sfgnew/agent from the internal data dictionary of
InnoDB though the .frm file for the table exists. Maybe you have
deleted and recreated InnoDB data files but have forgotten to delete
the corresponding .frm files of InnoDB tables, or you have moved
.frm files to another database?
Look from section 15.1 of
*http://www.innodb.com/ibman.html*how
you
can resolve the problem
Can anyone please help me figure this out?
Here are some pertinent facts:
a) stopped mysqld
b) copied files from sfg to sfgnew in the var directory
c) set the permissions to 777 (read, write, execute) for all the
files in sfgnew
d) restarted mysqld
Murthy
--
MySQL General Mailing List
For list archives:
*http://lists.mysql.com/mysql*To
unsubscribe:
*http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
*http://mail.yahoo.com*
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
*http://mail.yahoo.com*
--
MySQL General Mailing List
For list archives: *http://lists.mysql.com/mysql*
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: ERROR 1016 (HY000): Can't open file: 'agent.ibd' (errno: 1)
Hi Micheal
Thanks for your suggestions. I use mysqldump to restore the DB's. Apparently
mysqldump doesn't create forward declarations for views. So I had to manually
feed the views in correct order to avoid errors. Also to import the DB sfg into
sfgnew I had to substitute all of the `sfg` patterns with `sfgnew`. As you may
guess, I don't intend to this often. Why can't we have a tool that copies one
DB into another? BTW, I tried create table with select and it didn't work.
Michael Stassen [EMAIL PROTECTED] wrote:
murthy gandikota wrote:
Here are some pertinent facts:
a) stopped mysqld
b) copied files from sfg to sfgnew in the var directory
c) set the permissions to 777 (read, write, execute) for all the files in
sfgnew
d) restarted mysqld
Dan Buettner wrote:
Murthy, do you have appropriate permissions (777 works, or 775/770 if
owned by mysql user) on your new directory ('sfgnew')? If you don't
have execute permission for the mysql user on that dir, MySQL can't list
the contents ...
murthy gandikota wrote:
Hi Dan
I've set the permissions to 777 for all the files and directories.
Basically I did chmod -R 777 sfgnew. Also changed the ownership to
mysql using chown -R mysql:mysql sfgnew.
Thanks!
No, no, no! That's using a shotgun to kill a gnat. This idea of fixing a
problem by setting permissions on mysql's data to wide open keeps coming up.
It may work, but it's not a good idea.
There is no reason that normal users should have *any* access to mysql's data
directory and files. They most certainly should not have write access! You may
not even need group access to the data directory, unless you specifically want
to give a user or users other than mysql the ability to perform specific tasks
(read the error log, for instance), in which case you should give the group the
minimum permission needed.
For typical use, permissions should be 750 for the data directory and its
subdirectories, and 660 for files in the directories. That's sufficient and
relatively safe. Never set your permissions more open than that unless you are
certain you know what you are doing.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
ERROR 2013 (HY000): Lost connection to MySQL server during query
Hi, I am getting this error. Can anyone please help? mysql create table sfgbackup.advEmail select * from sfg.advEmail; ERROR 2013 (HY000): Lost connection to MySQL server during query Bye [EMAIL PROTECTED] mgandikota]$ mysql -V mysql Ver 14.7 Distrib 5.0.2-alpha, for pc-linux (i686) Thanks Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Can't CREATE table using SELECT
Hi The following happened when I tried to create table. Can anyone please help? mysql create table sfgbackup.advEmail select * from sfg.advEmail; mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=3 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x899b700 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0x964e199c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8166bc0 0xb748cdf8 0x82010c8 0x817d1b5 0x8183237 0x817b158 0x817acae 0x817a2c5 0xb7486dac 0xb73c09ea New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8955658 = create table sfgbackup.advEmail select * from sfg.advEmail thd-thread_id=4 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. ERROR 2013 (HY000): Lost connection to MySQL server during query mysql __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
MYSQLDUMP uses database name in the SQL statements?????!!!!!!!!!!!
I've checked the dump file I created with mysqldump on a database sfg, and it has statements like the following: DROP VIEW IF EXISTS `advAPI`; CREATE ALGORITHM=UNDEFINED VIEW `sfg`.`advAPI` AS select `sfg`.`advertiser`.`advertiserid` AS `advertiserid`,count(`sfg`.`cust`.`custid`) AS `api` from (`sfg`.`advertiser` left join `sfg`.`cust` on((`sfg`.`cust`.`advertiserid` = `sfg`.`advertiser`.`advertiserid`))) where (`sfg`.`cust`.`statusCurrent` in (20,21,22,23)) group by `sfg`.`advertiser`.`advertiserid`; Now if I want to import this dumpfile into another database sfgbackup, it won't work because the sql is referring to sfg database. I think this is the reason why I'm facing the problems (sorry for multiple threads). Can anyone confirm my suspicion and suggest a fix? I'm thinking manually changing all the entries. Is that the only way? Thanks for your help Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
TABLE and VIEW have same IDENTIFIER: PLEASE HELP
I created a sql file for database sfg as follows: mysqldump -uuser -ppassword sfg backup.sql Then I tried to reload it in another database sfg2 mysql -uuser -ppassword sfg2 backup.sql I get the error message saying the table already exists. I traced the error to the lines where the views have the same identifier as the tables. I don't know if it is possible to have views and tables the same identifier. I didn't create these views. Can someone please help me figure this out? Thanks Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Copying tables sans data from one database to another
How can I copy tables from one database to another on the same host? Thanks for your help Murthy __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
