Re: InnoDB table creation

[Mulugeta Maru]

I am sorry. I did not remove TYPE=INNODB in the middle
of the statment. You are right. I am now getting a
different error message;

ERROR 1005 at line 33: Can't creat table
'.\enrollment1\enrolls.frm' (errno: 150)

All the tables except enrolls is created. What am I
doing wrong again?

Regards.

Maru
--- Michael Stassen <[EMAIL PROTECTED]>
wrote:

> 
> Mulugeta Maru wrote:
> 
> > --- Michael Stassen <[EMAIL PROTECTED]>
> wrote:
> > 
> >>Mulugeta Maru wrote:
> >>
> >>
> >>>Hi Michael,
> >>>
> >>>I made some changes to the sql statments to
> create the
> >>>ENROLLS table as follows:
> >>>
> 
> ** Did you read this part? **
> 
> >>>CREATE TABLE ENROLLS
> >>>(
> >>>  courseID SMALLINT NOT NULL,
> >>>  sectionID SMALLINT NOT NULL,
> >>>  studentID SMALLINT NOT NULL,
> >>>  grade SMALLINT)TYPE=INNODB;
> >> ^
> >>You made one too many changes.  Your error is now
> >>here.  You've ended the 
> >>CREATE TABLE statement when you weren't really
> done.
> 
> ** *** **
> 
> >>>  PRIMARY KEY(courseID,sectionID,studentID),
> >>>  INDEX(courseID),
> 
> 
> 
> >>>  FOREIGN KEY(courseID) REFERENCES
> COURSES(courseID) 
> >>>ON UPDATE CASCADE ON DELETE CASCADE,
> >>>  INDEX(sectionID),
> >>>  FOREIGN KEY(sectionID) REFERENCES
> SECTIONS(sectionID)
> >>>ON UPDATE CASCADE ON DELETE CASCADE,
> >>>  INDEX(studentID),
> >>>  FOREIGN KEY(studentID) REFERENCES
> STUDENTS(studentID)
> >>>ON UPDATE CASCADE ON DELETE CASCADE
> >>>)TYPE=INNODB;
> 
> 
> 
> > I removed the index and still get the following
> error
> > message:
> > 
> > The error message is as follows:
> > ERROR 1064 at line 38: You have an error in your
> SQL
> > syntax; check the manual that corresponds to your
> > MySQL server version for the right syntax to use
> > near 'PRIMARY KEY (countID, sectionID, studentID),
> > FOREIGN KEY(courseID) REFERENCES COUR' at line 1
> 
> The same error as before.  As I said, the index was
> not the error.  It 
> appears that you still didn't remove the extraneous
> ")TYPE=INNODB;" in the 
> middle of your statement.  "PRIMARY KEY..." makes
> sense as part of a CREATE 
> TABLE statement, it does not make sense as the
> beginning of a separate 
> query.  Did you try copying and pasting the query I
> gave you?
> 
> > Any further thought on this error.
> > As you can see from the sql statement the three
> fields
> > courseID,sectionID, studentID are primery keys in
> > tables COURSES, SECTIONS, and STUDENTS
> respectively.
> > What I am trying to do is to make the three fields
> > together to be primary key in the ENROLL table and
> to
> > make each of them foreign keys. At the same time I
> > would like also to have cascade update and delete.
> 
> No problem with that.  You just have to get the
> syntax right.
> 
> > Regards,
> > 
> > Maru
> 
> Michael
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
>
http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 





__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OT Gmail

[Transcend Development]

Beware guys!

When I sent this message, I think google got a hold of it and I can't
receive  any mail at this address anymore.  If you get an invite and
you get to invite friends - DON'T SELL THEM!!!  It is against their
AUP.

You've been warned!

Cheers!

Richard

On Sun, 29 Aug 2004 09:30:01 -0700, Transcend Development
<[EMAIL PROTECTED]> wrote:
> As I have received many more than 3 requests I thought I should post this:
> 
> You can get invitations to gmail for about 99 cents on ebay!
> 
> That's where I got mine.  I then sold a couple, but it's too much
> hassle for too little money!
> 
> Anyway I hope some of you can get yours there, as I have not received
> any more quote from Google.
> 
> Regards,
> --
> R. Whitney
> Transcend Development
> "Producing the next phase of your internet presence"
> http://xend.net
> Premium Quality Web Hosting
> http://hosting.xend.net
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
> 310-943-6498
> 602-288-5340
> "The day this country abandons God is the day God will abandon this country"
> 


-- 
R. Whitney
Transcend Development
"Producing the next phase of your internet presence"
http://xend.net
Premium Quality Web Hosting
http://hosting.xend.net
[EMAIL PROTECTED]
[EMAIL PROTECTED]
310-943-6498
602-288-5340
"The day this country abandons God is the day God will abandon this country"

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OT Gmail

[Friedhelm Betz]

On Sunday 29 August 2004 19:24, Mike Wexler wrote:
> Miles Keaton wrote:
> >I have 7 invitations to give away.  Feel free to ask, but include a
> >sentence about why you want it.
>
> I have 6 invitations to give away.
> Available to the first 6 who ask.

It might be possible to take this off list, no? ;-)

Thx
Friedhelm

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OT Gmail

[Mike Wexler]

Miles Keaton wrote:
I have 7 invitations to give away.  Feel free to ask, but include a
sentence about why you want it.
 

I have 6 invitations to give away.
Available to the first 6 who ask.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB table creation

[Michael Stassen]

Mulugeta Maru wrote:
--- Michael Stassen <[EMAIL PROTECTED]> wrote:
Mulugeta Maru wrote:

Hi Michael,
I made some changes to the sql statments to create the
ENROLLS table as follows:
** Did you read this part? **
CREATE TABLE ENROLLS
(
 courseID SMALLINT NOT NULL,
 sectionID SMALLINT NOT NULL,
 studentID SMALLINT NOT NULL,
 grade SMALLINT)TYPE=INNODB;
^
You made one too many changes.  Your error is now
here.  You've ended the 
CREATE TABLE statement when you weren't really done.
** *** **
 PRIMARY KEY(courseID,sectionID,studentID),
 INDEX(courseID),

 FOREIGN KEY(courseID) REFERENCES COURSES(courseID) 
   ON UPDATE CASCADE ON DELETE CASCADE,
 INDEX(sectionID),
 FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID)
   ON UPDATE CASCADE ON DELETE CASCADE,
 INDEX(studentID),
 FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID)
   ON UPDATE CASCADE ON DELETE CASCADE
)TYPE=INNODB;

I removed the index and still get the following error
message:
The error message is as follows:
ERROR 1064 at line 38: You have an error in your SQL
syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use
near 'PRIMARY KEY (countID, sectionID, studentID),
FOREIGN KEY(courseID) REFERENCES COUR' at line 1
The same error as before.  As I said, the index was not the error.  It 
appears that you still didn't remove the extraneous ")TYPE=INNODB;" in the 
middle of your statement.  "PRIMARY KEY..." makes sense as part of a CREATE 
TABLE statement, it does not make sense as the beginning of a separate 
query.  Did you try copying and pasting the query I gave you?

Any further thought on this error.
As you can see from the sql statement the three fields
courseID,sectionID, studentID are primery keys in
tables COURSES, SECTIONS, and STUDENTS respectively.
What I am trying to do is to make the three fields
together to be primary key in the ENROLL table and to
make each of them foreign keys. At the same time I
would like also to have cascade update and delete.
No problem with that.  You just have to get the syntax right.
Regards,
Maru
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: root without permissions...

[jose usoz]

On Sun, 29 Aug 2004 12:26:25 -0400
Wesley wrote:

>  Exit mysql. OK, then that would seem to mean that you don't have a
>  root user with 'localhost' as an allowable host. As long as you can
   ^^
>  log in as jusoz, see if you can issue this:


Thanks !!! localized the problem. My user "root" is configured as -h
delfin.cromosfera , not as -h localhost as i suposed (localhost and
delfin.cromosfera is the same machine). Thas is the problem. I accessed
as:

mysql -u root -h delfin.cromosfera -pxxx

and no problem. It's time to make order in my list of users - hosts
names.

Thanks and saludos,
jose.



-- 
Jose Usoz / Cromosfera
http://www.cromosfera.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OT Gmail

[Miles Keaton]

I have 7 invitations to give away.  Feel free to ask, but include a
sentence about why you want it.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Access denied for root user

[Michael Stassen]

MueR wrote:
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Subj: Re: Access denied for root user

MueR wrote:
Hello,
I've recently had my HD crash, and have reinstalled everything.
Everything seems to work fine, apart from one thing. My MySQL server
has decided to randomly set a root password.
No, it didn't.  I know it seems that way, but I'm sure that's not what
happened.
I have tried logging in with no password, tried it with my old
password. Tried restarting the server with the --skip-grant-tables
command, I've tried to update the user table manually.
Everything seems to work out just fine, until the moment I attempt to
login using the root account. It will give me the error # 1045
(access denied for user [EMAIL PROTECTED]).
That's not the full error message, and you've tried a lot of things, so
it's hard to diagnose the problem.
The full error message also includes the using password...
Yes.  It helps to know whether it said "YES" or "NO", and which answer came 
with which attempt.

Nothing seems to work. Has anyone ever had this problem? Do you know
a solution to it? I am using MySQL v 3.23.49, on a Debian system.
I'd suggest carefully following the directions in the manual 
.  If
that doesn't work, paste what happens into your next post.
 
After starting the server with --skip-grant-tables:

utserver1:/var/run/mysqld# mysql -u root
Welcome to the MySQL monitor.
mysql> UPDATE mysql.user 
-> SET Password=PASSWORD('x')
-> WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0`
OK, this worked.  You have 3 (why 3?) root users, based on "Rows matched". 
They all have the same password, which didn't change because it is already 
the same as what you tried to set it to here, based on "Changed".

You need to FLUSH PRIVILEGES or restart for this to take effect, though. 
Did you do so?  Did you test?

Or, the other way..
utserver1:/# mysqladmin -u root flush-privileges password "x"
This appears to have worked, as well (for user [EMAIL PROTECTED]).
utserver1:/# mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
NO)
Good.  You set a password, so you can't get in without it.
utserver1:/# mysql -u root -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)
So none of the two ways described seem to work?
Hmmm...  Something is wrong.  The password you entered here doesn't match 
the password in the table.  You last set it with mysqladmin.  Does your 
password contain any special characters which your shell would have done 
something with?  A '$', for example?  If so, you'll have to escape them. 
Better to do it the first way (in the client), I think, so as to avoid this 
issue.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB table creation

[Mulugeta Maru]

--- Michael Stassen <[EMAIL PROTECTED]>
wrote:

> 
> Mulugeta Maru wrote:
> 
> > Hi Michael,
> > 
> > I made some changes to the sql statments to create
> the
> > ENROLLS table as follows:
> > 
> > CREATE TABLE ENROLLS
> > (
> >   courseID SMALLINT NOT NULL,
> >   sectionID SMALLINT NOT NULL,
> >   studentID SMALLINT NOT NULL,
> >   grade SMALLINT)TYPE=INNODB;
>^
> You made one too many changes.  Your error is now
> here.  You've ended the 
> CREATE TABLE statement when you weren't really done.
> 
> >   PRIMARY KEY(courseID,sectionID,studentID),
> >   INDEX(courseID),
> 
> This is not an error, but it is redundant.  You
> don't need a separate index 
> on CourseID, because it is the left-most (first)
> column in your PRIMARY KEY. 
>   Unneeded indexes waste space and slow INSERTs. 
> See the manual for details 
>

> and 
>
.
> 
> >   FOREIGN KEY(courseID) REFERENCES
> COURSES(courseID) 
> > ON UPDATE CASCADE ON DELETE CASCADE,
> >   INDEX(sectionID),
> >   FOREIGN KEY(sectionID) REFERENCES
> SECTIONS(sectionID)
> > ON UPDATE CASCADE ON DELETE CASCADE,
> >   INDEX(studentID),
> >   FOREIGN KEY(studentID) REFERENCES
> STUDENTS(studentID)
> > ON UPDATE CASCADE ON DELETE CASCADE
> > )TYPE=INNODB;
> 
> Change this to
> 
> CREATE TABLE ENROLLS
> (
>courseID SMALLINT NOT NULL,
>sectionID SMALLINT NOT NULL,
>studentID SMALLINT NOT NULL,
>grade SMALLINT,
>PRIMARY KEY(courseID,sectionID,studentID),
>FOREIGN KEY(courseID) REFERENCES
> COURSES(courseID)
>  ON UPDATE CASCADE ON DELETE CASCADE,
>INDEX(sectionID),
>FOREIGN KEY(sectionID) REFERENCES
> SECTIONS(sectionID)
>  ON UPDATE CASCADE ON DELETE CASCADE,
>INDEX(studentID),
>FOREIGN KEY(studentID) REFERENCES
> STUDENTS(studentID)
>  ON UPDATE CASCADE ON DELETE CASCADE
> )TYPE=INNODB;
> 
> > The error message is as follows:
> > ERROR 1064 at line 38: You have an error in your
> SQL
> > syntax; check the manual that corresponds to your
> > MySQL server version for the right syntax to use
> near
> > 'PRIMARY KEY (countID, sectionID, studentID),
> > INDEX(courseID),
> > FOREIGN KEY(courseID' at line 1
> > 
> > I do not know what wrong with my syntax. Please
> help.
> > I am using MySQL version 4.1.3B.
> 
> Yes, your formatting choices make it hard to see
> errors.  You should 
> consider using indentation and line breaks to
> logically organize your 
> queries for readability, and you might consider
> using something other than 
> all caps for table names so as to distinguish them
> from SQL.  There are two 
> benefits to this:  First, there are several experts
> on the list who won't 
> read and respond to a message where the query is
> difficult to read.  In 
> other words, you improve your chances of getting
> useful help if you make it 
> easy for us to read your queries.  Second, you may
> find that formatting 
> helps you catch problems yourself, without having to
> resort to the list. 
> Just a suggestion.
> 
> > Regards,
> > 
> > Maru
> 
> Michael
> 

I removed the index and still get the following error
message:

The error message is as follows:
ERROR 1064 at line 38: You have an error in your SQL
syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use
near 'PRIMARY KEY (countID, sectionID, studentID),
FOREIGN KEY(courseID) REFERENCES COUR' at line 1

Any fruther thought on this error.
As you can see from the sql statement the three fields
courseID,sectionID, studentID are primery keys in
tables COURSES, SECTIONS, and STUDENTS respectively.
What I am trying to do is to make the three fields
together to be primary key in the ENROLL table and to
make each of them foreign keys. At the same time I
would like also to have cascade update and delete.


Regards,

Maru






__
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OT Gmail

[Olivier Salzgeber]

Transcend Development wrote:
 

--
From: Transcend Development[SMTP:[EMAIL PROTECTED]
Sent: Sunday, August 29, 2004 6:30:01 PM
To: [EMAIL PROTECTED]
Subject: RE: OT Gmail
Auto forwarded by a Rule
  
As I have received many more than 3 requests I thought I should post 
this:

You can get invitations to gmail for about 99 cents on ebay!
That's where I got mine.  I then sold a couple, but it's too much
hassle for too little money!
Anyway I hope some of you can get yours there, as I have not received
any more quote from Google.
Regards,
 

I have 2 invitations left.
The first two who send me a message win :-)
Regards,
Olivier
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB table creation

[Michael Stassen]

Mulugeta Maru wrote:
Hi Michael,
I made some changes to the sql statments to create the
ENROLLS table as follows:
CREATE TABLE ENROLLS
(
  courseID SMALLINT NOT NULL,
  sectionID SMALLINT NOT NULL,
  studentID SMALLINT NOT NULL,
  grade SMALLINT)TYPE=INNODB;
  ^
You made one too many changes.  Your error is now here.  You've ended the 
CREATE TABLE statement when you weren't really done.

  PRIMARY KEY(courseID,sectionID,studentID),
  INDEX(courseID),
This is not an error, but it is redundant.  You don't need a separate index 
on CourseID, because it is the left-most (first) column in your PRIMARY KEY. 
 Unneeded indexes waste space and slow INSERTs.  See the manual for details 
 and 
.

  FOREIGN KEY(courseID) REFERENCES COURSES(courseID) 
ON UPDATE CASCADE ON DELETE CASCADE,
  INDEX(sectionID),
  FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID)
ON UPDATE CASCADE ON DELETE CASCADE,
  INDEX(studentID),
  FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID)
ON UPDATE CASCADE ON DELETE CASCADE
)TYPE=INNODB;
Change this to
CREATE TABLE ENROLLS
(
  courseID SMALLINT NOT NULL,
  sectionID SMALLINT NOT NULL,
  studentID SMALLINT NOT NULL,
  grade SMALLINT,
  PRIMARY KEY(courseID,sectionID,studentID),
  FOREIGN KEY(courseID) REFERENCES COURSES(courseID)
ON UPDATE CASCADE ON DELETE CASCADE,
  INDEX(sectionID),
  FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID)
ON UPDATE CASCADE ON DELETE CASCADE,
  INDEX(studentID),
  FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID)
ON UPDATE CASCADE ON DELETE CASCADE
)TYPE=INNODB;
The error message is as follows:
ERROR 1064 at line 38: You have an error in your SQL
syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'PRIMARY KEY (countID, sectionID, studentID),
INDEX(courseID),
FOREIGN KEY(courseID' at line 1
I do not know what wrong with my syntax. Please help.
I am using MySQL version 4.1.3B.
Yes, your formatting choices make it hard to see errors.  You should 
consider using indentation and line breaks to logically organize your 
queries for readability, and you might consider using something other than 
all caps for table names so as to distinguish them from SQL.  There are two 
benefits to this:  First, there are several experts on the list who won't 
read and respond to a message where the query is difficult to read.  In 
other words, you improve your chances of getting useful help if you make it 
easy for us to read your queries.  Second, you may find that formatting 
helps you catch problems yourself, without having to resort to the list. 
Just a suggestion.

Regards,
Maru
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: OT Gmail

[Transcend Development]

As I have received many more than 3 requests I thought I should post this:

You can get invitations to gmail for about 99 cents on ebay!

That's where I got mine.  I then sold a couple, but it's too much
hassle for too little money!

Anyway I hope some of you can get yours there, as I have not received
any more quote from Google.

Regards,
-- 
R. Whitney
Transcend Development
"Producing the next phase of your internet presence"
http://xend.net
Premium Quality Web Hosting
http://hosting.xend.net
[EMAIL PROTECTED]
[EMAIL PROTECTED]
310-943-6498
602-288-5340
"The day this country abandons God is the day God will abandon this country"

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: slow select... where... order by

[Michael Stassen]

Dave,
It's best to keep threads on the list.  On average, you'll get better and 
faster responses when all the experts can see your question.  Also, others 
can benefit from the answers when they are on the list.

See my comments below.
Critters wrote:
Thanks for your response, here is some more detail.
The table is like this:
| id  | name | dateTime | award | FP | ip | doodleid | votes  | score  |
voted  | compid | viewed | views  | doodle
11,000 records 380 meg
id, award, fp, doodleid, compid are indexed
Are these all single column indexes, or did you use multiple column indexes? 
 Depending on your typical queries, multiple column indexes may help.

they are all int except "name" and "voted" which are varchar 255 length and
doodle is longtext
ANALYZE TABLE doodlebug
+---+-+---+--+
| Table   | Op | Msg_type  |
Msg_text |
+---+-+---+--+
| desktopcreatures2.doodlebug   | analyze   | status| OK |
+---+-+---+--+
SELECT id FROM `doodlebug` WHERE compid = 34
12 rows in 0.33sec
SELECT id, name FROM `doodlebug` WHERE compid = 34
12 rows in 29.67sec
With an index on compid, these should have been about the same time.  The 
second one definitely looks like a full-table scan, though even the first 
seems slow to me.

EXPLAIN SELECT id FROM doodlebug where compid = 34
+---+---+---+-+-++---+--
+
| table | type  | possible_keys | key | key_len | ref| rows  |
Extra|
+---+---+---+-+-++---+--
+
| doodlebug | index | [NULL]| PRIMARY |  20 | [NULL] | 10977 |
Using where; Using index |
+---+---+---+-+-++---+--
+
Does this shead any light on the problem?
Yes.  EXPLAIN says the only index it can use is the PRIMARY index.  The 
key_len is 20, which tells me that the PRIMARY index is a multiple column 
index. The number of rows to be examined is 10,977, the whole table, which 
tells me that compid is not the first column in the index.  I'm guessing you 
have something like PRIMARY KEY (id, award, fp, compid, doodleid).  A 
multiple column index can be used to satisfy WHERE clauses using any 
left-most combination of the columns in the index.  Thus, with only one 
column in the WHERE clause, it would need to come first in the index for the 
index to be used to satisfy the WHERE clause.

The index is still being used for this query, however.  Because you only 
want the value of id, and id is in the index, mysql reasons that it would be 
quicker to scan the relatively small index for the matching rows than to 
scan the much larger table.  That is, you got a full-indexx scan.  That's 
why there's a difference between this and your second query,

  SELECT id, name FROM `doodlebug` WHERE compid = 34
In that case, name is not part of the index, so the full table is scanned. 
That takes a while, as you saw.

The solution is to add an index which starts with compid.  Whether that 
should be a single or multiple column index depends on what other indexes 
you have and what your typical queries look like.

You should read through the manual sections "Multiple-Column Indexes" 
 and "How 
MySQL Uses Indexes" . 
 Then, if you still have questions, feel free to post them.  It would 
probably help in that case to include the output of

  SHOW CREATE TABLE doodlebug;
Along with a representative sample of typical queries.
--
Dave
Then Critters wrote:
Even thought I am only asking for the "id" and "name" fields is the speed
affected by the "doodle" field which is about 90k for each record and makes
up the majority of the tables 380meg? If i moved the "doodle" field to
another table that just had "id", "doodleid", "doodle" would that make the
current table faster?
--
Dave
Probably. There are 2 factors here:  As your current query does a full-table 
scan, table size is definitely an issue.  This should be fixed by the 
addition of an index on (or starting with) compid.  You may find that gives 
acceptable results.  If not, consider factor 2.

The second factor is the nature of the rows in your table.  Fixed length 
rows are faster than variable length rows in MyISAM tables (the default). 
With fixed-length rows, you can calculate the starting position in the file 
of any row.  With variable-length rows you have to guess and search.  To 
make your table fixed-length, you'd have to do something with the 3 
variable-length columns, name, doodle, and voted.  You've already hit the 
solution for the text column, doodle -- move it to a separate table.  Your 
n

Re: InnoDB table creation

[Mulugeta Maru]

Hi Michael,

I made some changes to the sql statments to create the
ENROLLS table as follows:

CREATE TABLE ENROLLS
(courseID SMALLINT NOT NULL,
sectionID SMALLINT NOT NULL,
studentID SMALLINT NOT NULL,
grade SMALLINT)TYPE=INNODB;
PRIMARY KEY(courseID,sectionID,studentID),
INDEX(courseID),
FOREIGN KEY(courseID) REFERENCES COURSES(courseID) ON
UPDATE CASCADE ON DELETE CASCADE,
INDEX(sectionID),
FOREIGN KEY(sectionID) REFERENCES SECTIONS(sectionID)
ON UPDATE CASCADE ON DELETE CASCADE,
INDEX(studentID),
FOREIGN KEY(studentID) REFERENCES STUDENTS(studentID)
ON UPDATE CASCADE ON DELETE CASCADE)TYPE=INNODB;

The error message is as follows:
ERROR 1064 at line 38: You have an error in your SQL
syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near
'PRIMARY KEY (countID, sectionID, studentID),
INDEX(courseID),
FOREIGN KEY(courseID' at line 1

I do not know what wrong with my syntax. Please help.
I am using MySQL version 4.1.3B.

Regards,

Maru



__
Do you Yahoo!?
Yahoo! Mail is new and improved - Check it out!
http://promotions.yahoo.com/new_mail

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to turn on auditing for mysql

[adam]

Is there any way to log all the activity in a mysql database?

I would like to know every single delete insert or select on all tables for
my database.

 

Regards, Alex

RE: Access denied for root user

[MueR]

From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Subj: Re: Access denied for root user

> MueR wrote:
>
>> Hello,
>>  
>> I've recently had my HD crash, and have reinstalled everything.
>> Everything seems to work fine, apart from one thing. My MySQL server
>> has decided to randomly set a root password.
>
>No, it didn't.  I know it seems that way, but I'm sure that's not what
>happened.
>
>> I have tried logging in with no password, tried it with my old
>> password. Tried restarting the server with the --skip-grant-tables
>> command, I've tried to update the user table manually.
>> 
>> Everything seems to work out just fine, until the moment I attempt to
>> login using the root account. It will give me the error # 1045
>> (access denied for user [EMAIL PROTECTED]).
>
>That's not the full error message, and you've tried a lot of things, so
> >it's hard to diagnose the problem.

The full error message also includes the using password...

>> Nothing seems to work. Has anyone ever had this problem? Do you know
>> a solution to it? I am using MySQL v 3.23.49, on a Debian system.
>
>I'd suggest carefully following the directions in the manual 
>.  If
that 
>doesn't work, paste what happens into your next post.

After starting the server with --skip-grant-tables:

utserver1:/var/run/mysqld# mysql -u root
Welcome to the MySQL monitor.
mysql> UPDATE mysql.user 
-> SET Password=PASSWORD('x')
-> WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0`

Or, the other way..

utserver1:/# mysqladmin -u root flush-privileges password "x"
utserver1:/# mysql -u root
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
NO)
utserver1:/# mysql -u root -p
Enter password:
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)

So none of the two ways described seem to work?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: root without permissions...

[jose usoz]

> It looks like you can log in to the mysql client as root. Do you have
> permission to change the password once you're logged in?
> 
> [EMAIL PROTECTED] jusoz]$ mysql -u root -p
> Enter password:
> Welcome to the MySQL monitor...
> 
> mysql>USE mysql;
> Database changed.
> 
> mysql>UPDATE user SET Password = PASSWORD( '' ) WHERE User =
> 'root'; Query OK, 0 rows affected (0.03 sec)
> Rows matched: 2  Changed: 0  Warnings: 0
> 
> mysql>FLUSH PRIVILEGES;
> Query OK, 0 rows affected (0.05 sec)
> 
> mysql> quit;
> 
> Then see if logging in as expected works:
> [EMAIL PROTECTED] jusoz]$ mysql -u root -p
> 
> Wes


Hello Wes,

thanks for your aid. I have the access denied... :

[EMAIL PROTECTED] jusoz]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> USE mysql
ERROR 1044: Access denied for user: '@localhost' to database 'mysql'

I can access to the databae mysql as another user, "jusoz". Then i can
play the steps as you sugest, but when i log as "root" all is the same:

[EMAIL PROTECTED] jusoz]$ mysql -u root -p
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)


**These are the databases i can access ->

[EMAIL PROTECTED] jusoz]$ mysqlshow -u root
+---+
| Databases |
+---+
| test  |
+---+
[EMAIL PROTECTED] jusoz]$ mysqlshow -u jusoz
+--+
|  Databases   |
+--+
| backup_mySQL |
| datos-mySQL  |
| mysql|
| test |
| tmp  |
+--+

Thanks for your sugestions,
jose.

-- 
Jose Usoz / Cromosfera
http://www.cromosfera.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: root without permissions...

[Wesley Furgiuele]

Jose:

It looks like you can log in to the mysql client as root. Do you have
permission to change the password once you're logged in?

[EMAIL PROTECTED] jusoz]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor...

mysql>USE mysql;
Database changed.

mysql>UPDATE user SET Password = PASSWORD( '' ) WHERE User = 'root';
Query OK, 0 rows affected (0.03 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql>FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.05 sec)

mysql> quit;

Then see if logging in as expected works:
[EMAIL PROTECTED] jusoz]$ mysql -u root -p

Wes



On Sun, 29 Aug 2004 14:26:10 +0200, jose usoz <[EMAIL PROTECTED]> wrote:
> Hello,
> these are my first days with mySQL. Yesterday all is well. Today i
> changed some permissions in the databases and groups. I think i have
> returned to the original permissions, but i not have any permission with
> the user root. Is possible to restore this user?:
> 
> **login with my passw ->
> [EMAIL PROTECTED] jusoz]$ mysql -u root -p
> ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
> YES)
> 
> **login without passw->
> [EMAIL PROTECTED] jusoz]$ mysql -u root -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> Your MySQL connection id is 15 to server version: 4.0.18
> 
> **changing the passw ->
> [EMAIL PROTECTED] jusoz]$ mysqladmin -u root -h localhost password ""
> mysqladmin: unable to change password; error: 'Access denied for user:
> '@localhost' to database 'mysql''
> 
> Thanks,
> jose.
> 
> --
> Jose Usoz / Cromosfera
> http://www.cromosfera.com
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

root without permissions...

[jose usoz]

Hello,
these are my first days with mySQL. Yesterday all is well. Today i
changed some permissions in the databases and groups. I think i have
returned to the original permissions, but i not have any permission with
the user root. Is possible to restore this user?: 

**login with my passw ->
[EMAIL PROTECTED] jusoz]$ mysql -u root -p
ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password:
YES)

**login without passw->
[EMAIL PROTECTED] jusoz]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 4.0.18

**changing the passw ->
[EMAIL PROTECTED] jusoz]$ mysqladmin -u root -h localhost password ""
mysqladmin: unable to change password; error: 'Access denied for user:
'@localhost' to database 'mysql''


Thanks,
jose.


-- 
Jose Usoz / Cromosfera
http://www.cromosfera.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Data loading and foreign key constraints - help please

[Todd Cranston-Cuebas]

I'm a total newbie to mySQL, but was hoping someone could answer a 
question regarding adding a record into a database that has foreign key 
constraints. Remember, I'm a total newbie so I'm hoping I'm using the 
right words to express this. I'm taking a class that required us to use 
an ER diagramming tool. This tool generates SQL table create scripts in 
mySQL. After a little tweaking I got the scripts to work. An example is 
as follows:

# Create Table: 'Jobdesc'   Job Description for Requisition
# desccode:
# jobdescription  :
# levelcode   :  (references JobCode.levelcode)
# jobcode1:  (references JobCode.jobcode)
# jobcode :
# titlecode   :  (references JobCode.titlecode)
#
CREATE TABLE Jobdesc (
desccode   CHAR(8) NOT NULL UNIQUE,
jobdescription MEDIUMTEXT NOT NULL,
levelcode  CHAR(2) NOT NULL,
jobcode1   CHAR(8) NOT NULL,
jobcodeCHAR(8) NOT NULL,
titlecode  CHAR(7) NOT NULL,
PRIMARY KEY (desccode,jobcode),
INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode),
CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode)
REFERENCES JobCode (titlecode,jobcode,levelcode)
ON DELETE CASCADE
ON UPDATE CASCADE) TYPE=INNODB;
This is a create script for a job description table. Job descriptions 
are related to a jobcode table. That table in turn is related to 
joblevel and jobtitle tables (i.e., the job title and job level 
determine the job code). The jobcode is needed for each job 
description.

One problem I have is that the create scripts generated from the ER 
tool makes all fields in the job description entity NOT NULL. If I try 
to insert the description code (desccode), the job description 
(jobdescription) and the associated job code (jobcode) I get the 
following error:

#1216 - Cannot add or update a child row: a foreign key constraint fails
This happens if I just try to insert the desccode, the jobdescription, 
and jobcode data. I think this is happening because jobcode1, 
levelcode, and titlecode are NOT NULL so when I update the record it 
attempts to enter data (NULL) into these fields which are child rows. 
Can someone explain what I should do? Should I just change these fields 
of data into NULL? I'm literally just trying to populate the tables 
with enough data to run some test queries.

Any suggestions?
Todd
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]