Re: mysql installation
Caulfield, Linda wrote: Hello, I am trying to setup mysql and need some guidance - thanks Linda Caulfield Software Quality Engineer Global Hawk Program Rancho Bernardo X4680 Phone:858.618.4680 Linda - MySQL, 2d Ed., by Paul Dubois is hard to beat. It provides a lot of guidance. - offbyone -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and the dreaded lost root password dilemma
Thanks Brent, your solution is the one that worked for me. In 4.0.20 there was no 'Super_priv' column however. ? On Fri, 17 Sep 2004 15:20:43 -0400, Brent Baisley [EMAIL PROTECTED] wrote: There probably is a root user, but it's not called root. You can name the root user whatever you want. You probably just don't have a user named root, which is why you can change the password for user root. You want to start MySQL with the skip grant tables options, just like in the documentation. But instead of reseting the password, you want to launch the mysql client, which you should be able to do without providing a user name or password since there are no grant tables loaded. Once in mysql, switch to the mysql database and do a select on the user table to see what user name are set. There is a column called Super_priv, any record with a Y in that column is probably a valid root user. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query takes terribly long
Hello,
I have this MyISAM table story (52MB):
+-+-+--+-+-++
| Field | Type| Null | Key | Default |
Extra |
+-+-+--+-+-++
| id | int(10) unsigned| | PRI | [NULL] |
auto_increment |
| sequelof| int(10) unsigned| | | 0
||
| prevsequel | int(10) unsigned| | | 0
||
| userid | int(10) unsigned| | MUL | 0
||
| title | varchar(255)| YES | | [NULL]
||
| topcategory | tinyint(3) unsigned | | | 1
||
| category| tinyint(3) unsigned | | | 1
||
| rated | tinyint(1) unsigned | | | 0
||
| language| tinyint(3) unsigned | | | 0
||
| font| int(10) unsigned| | | 1
||
| fontsize| varchar(4) | | | 2
||
| story | mediumtext | | |
||
| note| text| | |
||
| adddatetime | datetime| | | -00-00 00:00:00
||
| putdatetime | datetime| | MUL | -00-00 00:00:00
||
| put | tinyint(1) | | MUL | 0
||
| putby | int(10) unsigned| | | 0
||
| approvedby | int(10) unsigned| | | 0
||
| blockbot| tinyint(1) unsigned | | | 0
||
| front | tinyint(1) | | | 1
||
| selection | tinyint(1) | | | 0
||
| timesread | int(10) unsigned| | | 0
||
| ipnumber| varchar(20) | | |
||
| words | int(11) | | | 0
||
| review | tinyint(1) unsigned | | MUL | 0
||
| avgscore| int(10) unsigned| YES | | [NULL]
||
| numvotes| int(10) unsigned| YES | | [NULL]
||
| numreacts | int(10) unsigned| YES | | [NULL]
||
+-+-+--+-+-++
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
This query sometimes takes up to 10 seconds!!!
I must admit that the cache is disabled at this moment (I want to see
realistic timings), but still I think 10 seconds is terribly long to
retrieve 30 id's!
Explain tells me that it is using the index
{put,front,topcategory,approvedby} and narrows the query to 5475 rows
(of the total of 18818).
The table will certainly grow a lot in the future so I am very worried
about the performance.
What can I do about this? Is there any way to improve this?
Enabling the cache is not an option as the data in the table is altered
a lot.
Anyone?
Dirk.
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query takes terribly long
On Sat, 18 Sep 2004 22:25:12 +0200, Dirk Schippers
[EMAIL PROTECTED] wrote:
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
I couldn't help but notice that your query isn't specifying that the
records you want are approved. Does your query time improve if you
add approvedby to the query?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query takes terribly long
No, it doesn't improve the speed. I think that is because almost all
rows in that table are approved.
Are you also convinced that this is a very long time for such a query?
Or is it normal?
Andrew Kreps wrote:
On Sat, 18 Sep 2004 22:25:12 +0200, Dirk Schippers
[EMAIL PROTECTED] wrote:
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
I couldn't help but notice that your query isn't specifying that the
records you want are approved. Does your query time improve if you
add approvedby to the query?
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
Re: Query takes terribly long
Hi,
You can try to add an index on (put,front,topcategory,putdatetime) to avoid
MySQL has to do a filesorting on the data returned. (I assume you're using
MySQL 4.x)
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 10:25 PM
Subject: Query takes terribly long
Hello,
I have this MyISAM table story (52MB):
+-+-+--+-+-+
+
| Field | Type| Null | Key | Default |
Extra |
+-+-+--+-+-+
+
| id | int(10) unsigned| | PRI | [NULL] |
auto_increment |
| sequelof| int(10) unsigned| | | 0
||
| prevsequel | int(10) unsigned| | | 0
||
| userid | int(10) unsigned| | MUL | 0
||
| title | varchar(255)| YES | | [NULL]
||
| topcategory | tinyint(3) unsigned | | | 1
||
| category| tinyint(3) unsigned | | | 1
||
| rated | tinyint(1) unsigned | | | 0
||
| language| tinyint(3) unsigned | | | 0
||
| font| int(10) unsigned| | | 1
||
| fontsize| varchar(4) | | | 2
||
| story | mediumtext | | |
||
| note| text| | |
||
| adddatetime | datetime| | | -00-00 00:00:00
||
| putdatetime | datetime| | MUL | -00-00 00:00:00
||
| put | tinyint(1) | | MUL | 0
||
| putby | int(10) unsigned| | | 0
||
| approvedby | int(10) unsigned| | | 0
||
| blockbot| tinyint(1) unsigned | | | 0
||
| front | tinyint(1) | | | 1
||
| selection | tinyint(1) | | | 0
||
| timesread | int(10) unsigned| | | 0
||
| ipnumber| varchar(20) | | |
||
| words | int(11) | | | 0
||
| review | tinyint(1) unsigned | | MUL | 0
||
| avgscore| int(10) unsigned| YES | | [NULL]
||
| numvotes| int(10) unsigned| YES | | [NULL]
||
| numreacts | int(10) unsigned| YES | | [NULL]
||
+-+-+--+-+-+
+
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
This query sometimes takes up to 10 seconds!!!
I must admit that the cache is disabled at this moment (I want to see
realistic timings), but still I think 10 seconds is terribly long to
retrieve 30 id's!
Explain tells me that it is using the index
{put,front,topcategory,approvedby} and narrows the query to 5475 rows
(of the total of 18818).
The table will certainly grow a lot in the future so I am very worried
about the performance.
What can I do about this? Is there any way to improve this?
Enabling the cache is not an option as the data in the table is altered
a lot.
Anyone?
Dirk.
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
--
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: Query takes terribly long
Hello,
I added the index you said, but mysql (yes 4.x) still prefers using the
other index {put,front,topcategory,approvedby}.
And indeed, he uses the filesort. When I force into using your index, it
seems to be even slower (more rows but no filesort as you said)
But I have a question about that, why would your index avoid a filesort?
I don't understand that.
Any other suggestion on how to speed up?
Anyway, thanks for all the help,
Dirk.
Jocelyn Fournier wrote:
Hi,
You can try to add an index on (put,front,topcategory,putdatetime) to avoid
MySQL has to do a filesorting on the data returned. (I assume you're using
MySQL 4.x)
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 10:25 PM
Subject: Query takes terribly long
Hello,
I have this MyISAM table story (52MB):
+-+-+--+-+-+
+
| Field | Type| Null | Key | Default |
Extra |
+-+-+--+-+-+
+
| id | int(10) unsigned| | PRI | [NULL] |
auto_increment |
| sequelof| int(10) unsigned| | | 0
||
| prevsequel | int(10) unsigned| | | 0
||
| userid | int(10) unsigned| | MUL | 0
||
| title | varchar(255)| YES | | [NULL]
||
| topcategory | tinyint(3) unsigned | | | 1
||
| category| tinyint(3) unsigned | | | 1
||
| rated | tinyint(1) unsigned | | | 0
||
| language| tinyint(3) unsigned | | | 0
||
| font| int(10) unsigned| | | 1
||
| fontsize| varchar(4) | | | 2
||
| story | mediumtext | | |
||
| note| text| | |
||
| adddatetime | datetime| | | -00-00 00:00:00
||
| putdatetime | datetime| | MUL | -00-00 00:00:00
||
| put | tinyint(1) | | MUL | 0
||
| putby | int(10) unsigned| | | 0
||
| approvedby | int(10) unsigned| | | 0
||
| blockbot| tinyint(1) unsigned | | | 0
||
| front | tinyint(1) | | | 1
||
| selection | tinyint(1) | | | 0
||
| timesread | int(10) unsigned| | | 0
||
| ipnumber| varchar(20) | | |
||
| words | int(11) | | | 0
||
| review | tinyint(1) unsigned | | MUL | 0
||
| avgscore| int(10) unsigned| YES | | [NULL]
||
| numvotes| int(10) unsigned| YES | | [NULL]
||
| numreacts | int(10) unsigned| YES | | [NULL]
||
+-+-+--+-+-+
+
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
This query sometimes takes up to 10 seconds!!!
I must admit that the cache is disabled at this moment (I want to see
realistic timings), but still I think 10 seconds is terribly long to
retrieve 30 id's!
Explain tells me that it is using the index
{put,front,topcategory,approvedby} and narrows the query to 5475 rows
(of the total of 18818).
The table will certainly grow a lot in the future so I am very worried
about the performance.
What can I do about this? Is there any way to improve this?
Enabling the cache is not an option as the data in the table is altered
a lot.
Anyone?
Dirk.
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Schippers Dirk
Zaakvoerder Frixx-iT
http://www.frixx-it.com
Bezoek ook http://www.verhalensite.com voor uw literaire tekortkomingen.
Re: Query takes terribly long
Hello,
Now this I don't understand, now the query takes only 0.05s on any of
the two indexes.
I didn't do anything! Not even the analyze table I was going to try!
I'll keep you posted, thank you for the explanation, I understand the
reason for the filesort now.
Dirk.
Jocelyn Fournier wrote:
Hi,
Well that's strange MySQL says more rows will be returned.
How many times does the query takes ?
And if you run ANALYZE TABLE on your table, does this change anything about
rows statistics ?
MySQL do not need filesort with this kind of index, because it can use the
index to retrieve the row in the right order directly.
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: Jocelyn Fournier [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 11:37 PM
Subject: Re: Query takes terribly long
Hello,
I added the index you said, but mysql (yes 4.x) still prefers using the
other index {put,front,topcategory,approvedby}.
And indeed, he uses the filesort. When I force into using your index, it
seems to be even slower (more rows but no filesort as you said)
But I have a question about that, why would your index avoid a filesort?
I don't understand that.
Any other suggestion on how to speed up?
Anyway, thanks for all the help,
Dirk.
Jocelyn Fournier wrote:
Hi,
You can try to add an index on (put,front,topcategory,putdatetime) to
avoid
MySQL has to do a filesorting on the data returned. (I assume you're
using
MySQL 4.x)
Regards,
Jocelyn Fournier
www.presence-pc.com
- Original Message -
From: Dirk Schippers [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, September 18, 2004 10:25 PM
Subject: Query takes terribly long
Hello,
I have this MyISAM table story (52MB):
+-+-+--+-+-+---
-
+
| Field | Type| Null | Key | Default |
Extra |
+-+-+--+-+-+---
-
+
| id | int(10) unsigned| | PRI | [NULL] |
auto_increment |
| sequelof| int(10) unsigned| | | 0
||
| prevsequel | int(10) unsigned| | | 0
||
| userid | int(10) unsigned| | MUL | 0
||
| title | varchar(255)| YES | | [NULL]
||
| topcategory | tinyint(3) unsigned | | | 1
||
| category| tinyint(3) unsigned | | | 1
||
| rated | tinyint(1) unsigned | | | 0
||
| language| tinyint(3) unsigned | | | 0
||
| font| int(10) unsigned| | | 1
||
| fontsize| varchar(4) | | | 2
||
| story | mediumtext | | |
||
| note| text| | |
||
| adddatetime | datetime| | | -00-00 00:00:00
||
| putdatetime | datetime| | MUL | -00-00 00:00:00
||
| put | tinyint(1) | | MUL | 0
||
| putby | int(10) unsigned| | | 0
||
| approvedby | int(10) unsigned| | | 0
||
| blockbot| tinyint(1) unsigned | | | 0
||
| front | tinyint(1) | | | 1
||
| selection | tinyint(1) | | | 0
||
| timesread | int(10) unsigned| | | 0
||
| ipnumber| varchar(20) | | |
||
| words | int(11) | | | 0
||
| review | tinyint(1) unsigned | | MUL | 0
||
| avgscore| int(10) unsigned| YES | | [NULL]
||
| numvotes| int(10) unsigned| YES | | [NULL]
||
| numreacts | int(10) unsigned| YES | | [NULL]
||
+-+-+--+-+-+---
-
+
with indexes: id = primary, putdatetime, userid,
{put,front,topcategory,approvedby} and review.
If I want to know the 30 most recently added and approved items, I do
the following simple query:
SELECT id FROM story WHERE put=1 AND front=1 AND topcategory=1 ORDER BY
putdatetime DESC LIMIT 0,30
This query sometimes takes up to 10 seconds!!!
I must admit that the cache is disabled at this moment (I want to see
realistic timings), but still I think 10 seconds is terribly long to
retrieve 30 id's!
Explain tells me that it is using the index
{put,front,topcategory,approvedby} and narrows the query to 5475 rows
(of the total of 18818).
The table will
Replication between 4.0.18 and 3.23.36 ??
Hi I have been running a master slave replication setup for the last few years on mysql versions 3.23.36 (which was bundled with redhat 7.1 back in the day). Anyway, I finally convinced our boss to purchase a new server and we've ended up installing mandrake 10 which comes complete with mysql 4.0.18 The new machine is to take over the role as the master, and I want to keep the old slave but bind it to the new machine. So far I've done the following: -- configure mysql [4.0.23] on new master -- stop mysql on old master and old slave. -- copy database across from /var/lib/mysql on old slave to new master /var/lib/mysql -- reconfigure my.cnf on old slave so that master-id is the server-id of the new master -- enable bin log etc on new master -- restart new master -- and grants for replication user on new master -- restart mysql on new master and slave Unfortunately the slave doesn't seem to happy with the new master and I'm getting the following messages: 040919 8:55:29 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'dengue-bin-004' at position 4 040919 8:55:29 Error reading packet from server: Could not find first log file name in binary log index file (read_errno 0,server_errno=1236) And sure enough, the new master (4.0.xx) doesn't have the expected dengue-bin.index file in /var/lib/mysql At the moment I'm assuming this is a difference between 4.0x and 3.23x versions. Does anyone know if its possible to reconfigure the master to observe the old replication setup? Or do I need to look at upgrading the slave to version 4 (which leads to the next question: does anyone fancy my chances of getting mysql 4 built and running under redhat 7.1 :) ??) Cheers Tim -- Tim Wood Predictive Technologies ph +61 3 9667 0236 (BH) +61 413 845 317 (M) This is clearly another case of too many mad scientists, and not enough hunchbacks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
