Re: MYSQL Scalability on SMPs
> MyISAM performance is limited right now by a global lock in the key > cache. However, I believe there is work going on to fix that in the > 4.1 tree. Really? I thought it was going to be fixed in the 5.1 tree, which will be years away from production quality. 4.1 would be really cool, but it seems so soon (non-InnoDB)... --steve- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with data import from text file
Please excuse a relative amateur worrying a problem. I got a nice reply from Victoria Reznichenko yesterday on a difficulty loading data from a text file. She said 4.0.12 had a bug and, I as understood her answer, I should therefore enable "load local infile". Instead I uninstalled 4.0.12 and loaded 4.0.14 after long download assuming that would deal with the bug. It set up nicely. I used MySQLFront - a GUI - to try and import data. It produced this: LOAD DATA LOCAL INFILE 'C:/My Documents/EPSRsearchbase/1173-1195edited.txt' INTO TABLE epsr_content.headlines FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' (epsr_id,headline,paper_date,PaperName_ID,author_id) and once more imediately got this: Error: 1148 - The used command is not allowed with this MySQL version --- which was the problem in the first place. [I get the same result using PHPMyAdmin 2.5 via a local Apache server running on my Windows ME PC.] I assume if I use a command line I will also get the same. Perhaps I misunderstood and whatever version I have I still need to enable Load local infile? I do not know how to do that. Can someone explain what is probably a very simple matter or is something still not happening correctly? Or should I revert to version 3 for my fairly simple training purposes? I am puzzled because previously this did work. Thank you. Regards Adrian Greeman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication
That configuration doesn't seem to work if I'm reading it right. A slave cannot have more then one master unless mysql more then 1 mysqld process running on different ports is used. -->For example, if a person places an order on our site, the update is sent -->to -->the master server. Can that update be replicated to the slaves before the -->browser is taken to a reciept page (which is queried against the slaves)? Also guaranteeing an event has occurred on a slave and building application logic dependent on this necessity is a tough problem to solve and you might as well query the master since you need to verify the event is there anyway as part of the conditional. The reason for my last statement, I've notice in 3.23.5x that replication lag can occur in many situations: - The master is loaded and is not able to stream the binary log file as fast. - Locks on the slave preventing updates to occur - Load on the slave is high - Invalid packet errors where the slave needs to reconnect - Some sort of error that stops replication With all of the above examples, I would not have application logic dependent on slave events. Instead code your applications based on the known limitations. For example a search engine. It does not necessarily need an up to date all of the time index of words and matches... -- Dathan -->-Original Message- -->From: Ian Neubert [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 11:27 AM -->To: [EMAIL PROTECTED] -->Subject: MySQL Replication --> -->Hello all, --> -->Can anyone share their experience with replication? In particular I'm -->wondering how fast (or how slow?) replication occurs. Could I pull off -->the -->configuration below? --> --> -->http://linux.ianneubert.com/images/mysql_failover.png or -->http://linux.ianneubert.com/images/mysql_failover.pdf --> -->Thanks for your insight! --> -->... -->Ian Neubert -->Director of IS -->TWAcomm.com, Inc. -->http://www.twacomm.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]
Functions as default values
Hi All, I am trying to use a function as a default value for a column but do not seem to get the desired result. I want to use the NOW() function for a last_updated column, here is my code... CREATE TABLE test_table ( last_updated datetime NOT NULL default `NOW()` ) TYPE=MyISAM; This gives an error; CREATE TABLE test_table ( last_updated datetime NOT NULL default 'NOW()' ) TYPE=MyISAM; Now the table shows a default value of -00-00 00:00:00, when I add a new row the value of last_updated is also -00-00 00:00:00. I am using MySQL 3.23.37, can anyone help? TIA Phil Ewington - Technical Director -- 43 Plc 35 Broad Street, Wokingham Berkshire RG40 1AU T: +44 (0)118 978 9500 F: +44 (0)118 978 4994 E: mailto:[EMAIL PROTECTED] W: http://www.43plc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 3.23 vs. 4.0
Currently, I'm running 3.23.51 on Red Hat 7.1, and I'm contemplating upgrading to MySQL 4.0, but I'm not sure what to expect. I don't know if MySQL 4.0 is fully backwards-compatible with 3.23.x versions, or if something is going to break if I upgrade. What are the main advantages of upgrading to 4.0? Speed? Features? I can go through the whole changelog if need be, but I'd prefer to hear what actual users are reporting as far as benefits and disadvantages. - Jonathan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB crash - recover + bug
Greetings all. I have a bit of a problem here, a database i'm administering was somehow corrupted, and i'm unable to recover it in any way. Is there any way at all to recover a corrupt InnoDB database? (I read on innodb.com that it is impossible, but hope it is not) When I run a query from any InnoDB table in the database MySQL crashes with the following stack trace and errors. I'm running a GNU/Linux system and MySQL 4.0.13 from the Debian unstable. Error: trying to access field 4294967295 in rec 030807 13:53:24 InnoDB: Assertion failure in thread 180234 in file rem0rec.c line 111 InnoDB: Failing assertion: 0 ... thd=0x86e3990 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=0xbe7fe898, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8102bc3 0x401ad75a 0x82b9a60 0x8230d50 0x822e42c 0x816952f 0x8169c84 0x816bf6a 0x816c2be 0x815e77f 0x8178c60 0x810f8e8 0x8112a15 0x810db3d 0x810d6cc 0x810d059 0x401a7d53 0x4038a3f7 New value of fp=(nil) failed sanity check, terminating stack trace! ... 0x8102bc3 mysql_binlog_send__FP3THDPcUxUs + 1419 0x401ad75a _end + 936375294 0x82b9a60 _tr_flush_block + 640 0x8230d50 page_cur_delete_rec + 5780 0x822e42c page_copy_rec_list_end_to_created_page + 392 0x816952f yyparse + 3855 0x8169c84 yylex + 1572 0x816bf6a opt_search_plan_for_table + 742 0x816c2be opt_search_plan_for_table + 1594 0x815e77f row_upd_clust_step + 431 0x8178c60 btr_compress + 3852 0x810f8e8 srv_master_thread + 172 0x8112a15 innobase_start_or_create_for_mysql + 1297 0x810db3d srv_sprintf_innodb_monitor + 425 0x810d6cc srv_suspend_mysql_thread + 1372 0x810d059 srv_table_reserve_slot_for_mysql + 473 0x401a7d53 _end + 936352247 0x4038a3f7 _end + 938328219 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unix date problems
While I don't know for sure, my guess is that it would have something to
do with 32 bit as the magic number, but also...being that this won't
become a problem until
mysql> select from_unixtime(2147483647);
+---+
| from_unixtime(2147483647) |
+---+
| 2038-01-18 21:14:07 |
+---+
1 row in set (0.00 sec)
And the 64bit processors and OS's are going to start coming out soon...
As for your other issues...mine doesn't quite wrap like yours does.
mysql> select from_unixtime(2147483648);
+---+
| from_unixtime(2147483648) |
+---+
| 1901-12-13 14:45:52 |
+---+
1 row in set (0.00 sec)
and if we look at the start of time...
mysql> select from_unixtime(0);
+-+
| from_unixtime(0)|
+-+
| 1969-12-31 18:00:00 |
+-+
1 row in set (0.00 sec)
This was done in 3.23.56-log on RH7.3
On Thu, 2003-08-07 at 09:27, Andy Jackman wrote:
> Hi,
> 1) I was investigating the unix_timestamp routine in mysql (version
> 3.23.46-nt) and for some reason the unix epoch (1-1-1970) was returned
> with a value of -3600. Then, as you can see, it suddenly corrected
> itself. I can't replicate this error, but it caused me to shudder.
> Attached is the output from the command line client.
> 2) The function from_unixtime(n) wraps on my server after 2^31 seconds.
> e.g. select from_unixtime(2147483648) returns "1900-01-00 00:00:00". I
> suspect this is caused by the underlying time_t size that mysql was
> compiled with. Is this so? Are any versions of mysql compiled with a
> larger time_t?
> Regards,
> Andy
>
> +---+
> | from_unixtime(2147483648) |
> +---+
> | 1900-01-00 00:00:00 |
> +---+
> 1 row in set (0.00 sec)
>
>
> mysql> select unix_timestamp();
> +--+
> | unix_timestamp() |
> +--+
> | 1060262715 |
> +--+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:00');
> +---+
> | unix_timestamp('1970-01-01 00:00:00') |
> +---+
> | -3600 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:01');
> +---+
> | unix_timestamp('1970-01-01 00:00:01') |
> +---+
> | -3599 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select from_unixtime(0);
> +-+
> | from_unixtime(0)|
> +-+
> | 1970-01-01 00:00:00 |
> +-+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:00');
> +---+
> | unix_timestamp('1970-01-01 00:00:00') |
> +---+
> | -3600 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:01:00');
> +---+
> | unix_timestamp('1970-01-01 00:01:00') |
> +---+
> |60 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:02:00');
> +---+
> | unix_timestamp('1970-01-01 00:02:00') |
> +---+
> | 120 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:10');
> +---+
> | unix_timestamp('1970-01-01 00:00:10') |
> +---+
> |10 |
> +---+
> 1 row in set (0.02 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:09');
> +---+
> | unix_timestamp('1970-01-01 00:00:09') |
> +---+
> | 9 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:08');
> +---+
> | unix_timestamp('1970-01-01 00:00:08') |
> +---+
> | 8 |
> +---+
> 1 row in set (0.00 sec)
>
> mysql> select unix_timestamp('1970-01-01 00:00:07');
> +---+
> | unix_timestamp('1970-01-01 00:00:07') |
> +---+
> | 7 |
> +---+
> 1 row in set (
Re: reindexing
what is the command to update InnoDB table indexes? jeff Brent Baisley wrote: > > You are referring to fragmentation. In this case I'm pretty sure it's > just a matter of updating the indexes so that MySQL makes the correct > assumptions about the distribution of data. MySQL assumes data values > have an equal distribution in a database, updating the indexes will > force MySQL to update it's assumptions and thus do a better job of > optimizing searches. > > On Wednesday, August 6, 2003, at 12:17 PM, Jeff Mathis wrote: > > > I was under the impression that InnoDB tables took care of this for > > you. > > You only need to be concerned if you add/delete repeatedly from > > anywhere > > but the end rows of the table. > > > > I'd like to know if I'm wrong about this. > > > > > > Adam Nelson wrote: > >> > >> I just did a major insert of new data and now all my selects have > >> slowed > >> down. The table is innodb. Is there a way to reindex everything > >> without having to drop anything. Otherwise, I suppose I will have to > >> drop the indexes and remake them. > >> > >> -- > >> MySQL General Mailing List > >> For list archives: http://lists.mysql.com/mysql > >> To unsubscribe: > >> http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > > Jeff Mathis, Ph.D.505-955-1434 > > The Prediction Company[EMAIL PROTECTED] > > 525 Camino de los Marquez, Ste 6 http://www.predict.com > > Santa Fe, NM 87505 > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1 query lock all others query
but one guy in #mysql said: [quote] with myisam tables if an update is running everything else is blocked, multiple selects are allowed to run at the same time, selects and non interfering inserts can run at the same time [/quote] so i ask in mailinglist because i can't get my multiple selects run at the same time. From: "Dathan Vance Pattishall" <[EMAIL PROTECTED]> To: "'xuefer tinys'" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: 1 query lock all others query Date: Thu, 7 Aug 2003 10:59:46 -0700 For MYISAM tables selects cause table locks while INNODB does row level locking. Now if your select is taking a long time you might want to look at your index schema. If that does not work for you then split up your data. -->-Original Message- -->From: xuefer tinys [mailto:[EMAIL PROTECTED] -->Sent: Thursday, August 07, 2003 10:24 AM -->To: [EMAIL PROTECTED] -->Subject: 1 query lock all others query --> -->when i show processlist -->1 of my queries is "sending data" (SELECT ...) -->while others queries (SELECT/UPDATE) is "locked" -->what's up? --> -->_ -->与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn --> --> -->-- -->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] _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FROM_DAYS doesn't take timestamp?
At 14:35 +0100 8/7/03, Andy Jackman wrote:
Hi,
I'm using v 3.23.46-nt and the C API.
The manual says under 'Changes in release 3.20.17' that:
Change FROM_DAYS() and WEEKDAY() to also take a full TIMESTAMP or
DATETIME as
argument. Before they only took a number of type MMDD or YYMMDD.
I'm confused since this doesn't seem to be the case.
If I do SELECT from_days(731799); I get today's date. But if I try and
pass a 'timestamp' I get '-00-00' e.g.
SELECT from_days(now());
1) Is this a doc bug or am I not passing the 'timestamp' in the right
way?
Looks like a doc bug to me. The change note probably should say
TO_DAYS() rather than FROM_DAYS(). FROM_DAYS() takes a value in days,
usually obtained by passing a date or datetime or timestamp to TO_DAYS().
2) I'm trying to get/set the date and time in a datetime column without
having to use formatted date strings ('-mm'dd hh:mm:ss'). I hoped to
use a long long integer to store the datetime as the number of seconds
(or any smaller unit) since the epoch. If from_days()/to_days() aren't
the functions to do this are there similar functions in mysql which
handle the time as well as the date? I don't want to use from_unixtime()
as I think the dates screw up in 2038.
TO_DAYS()/FROM_DAYS() don't use seconds in any case, so you may
not want to use them for this.
Thanks,
Andy.
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Searching on Two Keys with OR?
You're saying that when you try my example, it does use the composite index? Even with an extra column in the table that isn't being searched on (q below)? If so, do you know of anything in version 4.0.13 that could cause this bad behaviour? i'm using the default configuration unchanged. On Tue, 5 Aug 2003, Alexander Keremidarski wrote: > Joshua, > > Joshua Spoerri wrote: > > On Tue, 5 Aug 2003, Alexander Keremidarski wrote: > > > >>MySQL will never use any index for small tables. With just few rows using index > >>adds overhead only. Table scan is faster in such cases. This is docummented > >>behaviour. > > > > > > is 100,000 rows small? my simple OR queries take longer than a second. > > No. It is not! > > I referred to your test case in your previous email: > > > Oddly, it works, but the following does not: > > mysql> create temporary table x (y int, z int, q int, index (y, z)); insert into x > > values (1,2,3), (3,4,5), (5,6,7); explain select * from x where y = 1 or z = 1; > > > > > mysql> create temporary table x (y int, z int, index (y, z)); > > insert into x select f1,f2 from myrealtable; > > alter table x add q int; > > explain select * from x where y = 1 or z = 1; > > > > Query OK, 0 rows affected (0.00 sec) > > > > Query OK, 101200 rows affected (1.95 sec) > > Records: 101200 Duplicates: 0 Warnings: 0 > > > > Query OK, 101200 rows affected (1.61 sec) > > Records: 101200 Duplicates: 0 Warnings: 0 > > > > +---+--+---+--+-+--++-+ > > | table | type | possible_keys | key | key_len | ref | rows | Extra > > | > > +---+--+---+--+-+--++-+ > > | x | ALL | y | NULL |NULL | NULL | 101200 | Using > > where | > > +---+--+---+--+-+--++-+ > > 1 row in set (0.00 sec) > > > Same table: > > mysql> explain select * from x where y = 1 or z = 1; > +---+---+---+--+-+--+---+-+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > +---+---+---+--+-+--+---+-+ > | x | index | y | y| 10 | NULL | 85971 | where used; > Using index | > +---+---+---+--+-+--+---+-+ > > Note that I am using MySQL 3.23.57, 4.0.14 and 4.1.0 > > > Best regards > > -- > Are you MySQL certified? -> http://www.mysql.com/certification > For technical support contracts, visit https://order.mysql.com/?ref=msal > __ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski <[EMAIL PROTECTED]> >/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer > /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria > <___/ www.mysql.com > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by question
SELECT * FROM kf_gallery WHERE gallery_id IN ( 3, 1, 2 ) ORDER BY ? What I'm trying to do is get the results in the order specified in the "IN" clause, i.e. (3, 2, 1). Is this possible? (I'm having trouble searching the mail archives). Thanks! -m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dropping primary key/unique key
Update: 2003年 8月 7日 木曜日 17:06、Nils Valentin さんは書きました: > [Warning: LNG TEXT - enjoy ;-)] > > Hi Victoria, > > sorry for the misunderstanding. I just tried to reproduce the sample. While > I have records what I did I did not write down all commands exactly as they > are. > > The problem(s) occured on Redhat 8 or 9 and Mysql server-max 4.0.13 (tar > format) from mysql homepage. I had to reinstall a machine to reproduce the > problem,this is why it took so long. > > > First the original problem > Redhat 9 + Mysql server-max 4.0.13 (tar format from mysql.com homepage) Reproducable on Redhat 8 and Redhat 9 Table types tested :MyISAM and Innodb > > create table uksample2 (id int unique not null ,name char(30),tel > char(20))type=innodb; > Query OK, 0 rows affected (0.01 sec) > > mysql> alter table uksample2 add primary key (tel); > Query OK, 0 rows affected (0.04 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > mysql> desc uksample2; > +---+--+--+-+-+---+ > > | Field | Type | Null | Key | Default | Extra | > > +---+--+--+-+-+---+ > > | id| int(11) | | UNI | 0 | | > | name | char(30) | YES | | NULL| | > | tel | char(20) | | PRI | | | > > +---+--+--+-+-+---+ > 3 rows in set (0.00 sec) > > mysql> alter table uksample2 drop primary key; > Query OK, 0 rows affected (0.03 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > mysql> desc uksample2; > +---+--+--+-+-+---+ > > | Field | Type | Null | Key | Default | Extra | > > +---+--+--+-+-+---+ > > | id| int(11) | | | 0 | | > | name | char(30) | YES | | NULL| | > | tel | char(20) | | PRI | | | > > +---+--+--+-+-+---+ > 3 rows in set (0.00 sec) > > > Unique key was dropped. Why not Primary key, why no error message??? > > -- > 2nd example where it complains about that no two primary keys are > allowed,but lets me create them first ;-) This time on Redhat 8. > Redhat8 and 4.0.13 server-max (tar format from mysql.com homepage) > > create table uksample2 (id int unique not null auto_increment,name > char(30),tel char(20)); > Query OK, 0 rows affected (0.01 sec) > > mysql> desc uksample2; > +---+--+--+-+-++ > > | Field | Type | Null | Key | Default | Extra | > > +---+--+--+-+-++ > > | id| int(11) | | PRI | NULL| auto_increment | > | name | char(30) | YES | | NULL|| > | tel | char(20) | YES | | NULL|| > > +---+--+--+-+-++ > 3 rows in set (0.00 sec) > > mysql> alter table uksample2 add primary key (tel); > Query OK, 0 rows affected (0.02 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > mysql> desc uksample2; > +---+--+--+-+-++ > > | Field | Type | Null | Key | Default | Extra | > > +---+--+--+-+-++ > > | id| int(11) | | UNI | NULL| auto_increment | > | name | char(30) | YES | | NULL|| > | tel | char(20) | | PRI | || > > +---+--+--+-+-++ > 3 rows in set (0.00 sec) > > mysql> alter table uksample2 drop primary key ; > ERROR 1075: Incorrect table definition; There can only be one auto column > and it must be defined as a key > mysql> alter table uksample2 drop primary key (id); > > Note that also "alter table uksample2 drop index id;" will give me the same > error. > > - > > On a Suse Linux system 8.1 with mysql server max 4.1 -alpha (rpm format > from mysql.com homepage) I get the below message, which I believe is > correct. > > mysql> create table uksample2 (id int unique not null auto_increment,name > char(30),tel char(20)); > Query OK, 0 rows affected (0.07 sec) > > mysql> alter table uksample2 add primary key (tel); > ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL > in a key, use UNIQUE instead > mysql> drop table uksample2; > Query OK, 0 rows affected (0.00 sec) > > > > Best regards > > Nils Valentin > Tokyo/Japan > > 2003年 8月 6日 水曜日 19:22、Victoria Reznichenko さんは書きました: > > "Nils Valentin" <[EMAIL PROTECTED]> wrote: > > > Hi Victoria, > > > > > > Victoria Reznichenko wrote: > > >>"Nils Valentin" <[EMAIL PROTECTED]> wrote: > > >>>I have a problem understanding why MySQL is deleting a unique key > > >>> instead of a primary key. > > >>> > > >>>from Documentation: DROP PRIMARY KEY drops the primary index. If no > > >>> such index exists, it drops the first UNIQUE index in the table. > > >>> > > >>>When I do it then I get this: > > >>> > > >>>mysql> desc uksample4; > > >>>+---+--+---
Re: select count(*) Problem in PowerBuilder Thru MySQL ODBC 3.51
You are not doing "select count(*)"
You are doing "select count ( *)"
Get rid of the spaces before the "("
Fatt Shin wrote:
Hi,
I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB
Connector 3.51.
I'm facing a problem where whenever I issue a SELECT COUNT(*) statement
from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer
to ODBC Trace I captured below).
metrohouse af8-b94 ENTER SQLExecDirect
HSTMT 014D2360
UCHAR * 0x020A0EA2 [ -3] "select count ( *) from code
\ 0"
SDWORD-3
metrohouse af8-b94 EXIT SQLExecDirect with return code -1
(SQL_ERROR)
HSTMT 014D2360
UCHAR * 0x020A0EA2 [ -3] "select count ( *) from code
\ 0"
SDWORD-3
DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]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 '( *) from code'
at line 1 (1064)
metrohouse af8-b94 ENTER SQLErrorW
HENV014D12A0
HDBC014D14B0
HSTMT 014D2360
WCHAR * 0x0012E65C (NYI)
SDWORD *0x0012E6A8
WCHAR * 0x0012E25C
SWORD 512
SWORD * 0x0012E6B0
metrohouse af8-b94 EXIT SQLErrorW with return code 0
(SQL_SUCCESS)
HENV014D12A0
HDBC014D14B0
HSTMT 014D2360
WCHAR * 0x0012E65C (NYI)
SDWORD *0x0012E6A8 (1064)
WCHAR * 0x0012E25C [ 208] "[MySQL][ODBC 3.51
Driver][mysqld-4.0.13-max-nt]You have an error in your SQL syntax.
Check the manual t"
SWORD 512
SWORD * 0x0012E6B0 (208)
The same statement actually working fine whether I ran it using mysql or
sql yog or even using the same ODBC connector thru Microsoft Access.
(Refer to ODBC
Trace below)
MSACCESSfd4-ff4 ENTER SQLExecDirectW
HSTMT 09BB18C8
WCHAR * 0x0B431048 [ -3] "SELECT
COUNT(* ) FROM `code` \ 0"
SDWORD-3
MSACCESSfd4-ff4 EXIT SQLExecDirectW with return code 0
(SQL_SUCCESS)
HSTMT 09BB18C8
WCHAR * 0x0B431048 [ -3] "SELECT
COUNT(* ) FROM `code` \ 0"
SDWORD-3
Anybody have any idea what may cause the error here ???
Thanks a lot.
Regards,
FattShin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Logging and encryption
If logging is turned on, is there a way to disable it for a given query?
IE. If I query --> select AES_ENCRYPT("This is very important data",
"lessthansecretkey");
Then my key and the value I'm trying to encrypt is visible in the log
files.
KJW
newbie needs help: can I "order by" before "grouping"?
I'm having trouble ordering my results before grouping them. I don't want to go into too much detail, so my simplified query without any grouping or ordering results in: ++---++ | jobnum | milestone | shipdate | ++---++ | 96-655 | 4 || | 96-655 | 2 || | 96-655 | 0 | 2003-12-04 | | 98-308 | 0 | 1973-05-11 | | 98-308 | 4 || | 98-316 | 0 | 1973-06-11 | | 98-316 | 4 || | 98-500 | 2 || | 98-327 | 4 || | 98-327 | 0 | 1973-08-11 | ++---++ when I add "group by jobnum order by milestone" I get: ++---++ | jobnum | milestone | shipdate | ++---++ | 98-308 | 0 | 1973-05-11 | | 98-316 | 0 | 1973-06-11 | | 96-655 | 4 || | 98-327 | 4 || ++---++ but what I really want is this: ++---++ | jobnum | milestone | shipdate | ++---++ | 98-308 | 0 | 1973-05-11 | | 98-316 | 0 | 1973-06-11 | | 96-655 | 0 | 2003-12-04 | | 98-327 | 0 | 1973-08-11 | | 98-500 | 2 || ++---++ Clearly, mysql is grouping first and then ordering. I suppose that's reasonable, but I really want to order the results internally first and then group so that the displayed row from each group is the smallest value (and then maybe do some more sorting on shipdate, but that's another issue). Is there a way to do this? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table size in mysql
* nm
> Is it possible to MERGE innodb tables?
No, MERGE is for MyIsam tables only. InnoDb tables are stored in
tablespaces, the problem with file size does not apply. You simply use
multiple tablespaces when the data outgrows the OS limits.
> Can't find docs on mysql.com
hm... mysql.com seems to be down right now... try this:
http://darkstar.ist.utl.pt/mysql/doc/en/ >
http://darkstar.ist.utl.pt/mysql/doc/en/Table_types.html >
http://darkstar.ist.utl.pt/mysql/doc/en/MERGE.html >
http://darkstar.ist.utl.pt/mysql/doc/en/InnoDB.html >
> In replication. I guess I can update the slave if the master is not
> responding, right?
Replication can be done in several ways, for instance, both your servers
could be masters/slaves for eachother. Read about replication here:
http://darkstar.ist.utl.pt/mysql/doc/en/Replication.html >
> Shall I 'stop slave' before , in case the
> master is down?
I don't understand this question, sorry! :)
> Also will this create problems when the master comes back and the slave
> copies updates from the master log file?
Hopefully not. I have never used replication in mysql, but I have seen some
people with problems on this list. My impression is that there are rearly
bugs in the replication code, but it is a bit complex to deal with the setup
and recovery after crashes. You should read the manual carefully.
> application/web server
>||
>||
> master -- slave
>
> This way I would write master and read on slave as default.
That is a common and sensible solution.
> But I would like
> to use slave as mater automatically if the first server is down.
This would be implemented in your middle layer application/web application.
This example is python code:
try: wconn = connect(primary_write_host)
except:
admin_warning("primary_write_host is down!")
try: wconn = connect(secondary_write_host)
except:
admin_alert("site is down!")
fail("Sorry, database servers are down!")
try: rconn = connect(primary_read_host)
except:
admin_warning("primary_read_host is down!")
try: rconn = connect(secondary_read_host)
except:
admin_alert("site is down!")
fail("Sorry, database servers are down!")
(The admin_warning()/admin_alert() functions are flood-protected, so you
won't get one email/SMS per user click... ;))
> Also I would use a few innodb tables.
You can combine MyIsam and InnoDb tables in the same database.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select count(*) Problem in PowerBuilder Thru MySQL ODBC 3.51
Hi, I'm running MySQL 4.0.13, connecting from PowerBuilder 9 using ODCB Connector 3.51. I'm facing a problem where whenever I issue a SELECT COUNT(*) statement from PowerBuilder, I always get SQL syntax error back from MySQL. (Refer to ODBC Trace I captured below). metrohouse af8-b94 ENTER SQLExecDirect HSTMT 014D2360 UCHAR * 0x020A0EA2 [ -3] "select count ( *) from code \ 0" SDWORD-3 metrohouse af8-b94 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 014D2360 UCHAR * 0x020A0EA2 [ -3] "select count ( *) from code \ 0" SDWORD-3 DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]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 '( *) from code' at line 1 (1064) metrohouse af8-b94 ENTER SQLErrorW HENV014D12A0 HDBC014D14B0 HSTMT 014D2360 WCHAR * 0x0012E65C (NYI) SDWORD *0x0012E6A8 WCHAR * 0x0012E25C SWORD 512 SWORD * 0x0012E6B0 metrohouse af8-b94 EXIT SQLErrorW with return code 0 (SQL_SUCCESS) HENV014D12A0 HDBC014D14B0 HSTMT 014D2360 WCHAR * 0x0012E65C (NYI) SDWORD *0x0012E6A8 (1064) WCHAR * 0x0012E25C [ 208] "[MySQL][ODBC 3.51 Driver][mysqld-4.0.13-max-nt]You have an error in your SQL syntax. Check the manual t" SWORD 512 SWORD * 0x0012E6B0 (208) The same statement actually working fine whether I ran it using mysql or sql yog or even using the same ODBC connector thru Microsoft Access. (Refer to ODBC Trace below) MSACCESSfd4-ff4 ENTER SQLExecDirectW HSTMT 09BB18C8 WCHAR * 0x0B431048 [ -3] "SELECT COUNT(* ) FROM `code` \ 0" SDWORD-3 MSACCESSfd4-ff4 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS) HSTMT 09BB18C8 WCHAR * 0x0B431048 [ -3] "SELECT COUNT(* ) FROM `code` \ 0" SDWORD-3 Anybody have any idea what may cause the error here ??? Thanks a lot. Regards, FattShin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
Eben Goodman wrote: I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? alter your field to UNSIGNED ZEROFILL -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB crash - recover + bug
Mads, please post the WHOLE .err log. Or is there sensitive information there? If you have taken backups and the .err log does not show any crashes in them, how is it possible that the backups are now corrupt? How did you take the backups? I assume that you did not simply copy the ibdata files from a running server, which very probably creates corrupt backups? Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, foreign keys, and a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html ... Subject: RE: InnoDB crash - recover + bug From: Mads Jørgensen Date: Thu, 7 Aug 2003 16:06:27 +0200 >> Greetings all. >> >> I have a bit of a problem here, a database i'm administering was somehow = >> corrupted, and i'm unable to recover it in any way. > >what happened? A power outage? You deleted the ib logfiles? Modified my.cnf? >Hard disk broke? Thats the weird thing, nothing abnormal happened, i just saw the mysqld using a lot of resources and shut it down. I suppose it must have been a query, or the database beeing to large or something. >What does uname -a say about the Linux kernel in Debian-unstable? It says its running a 2.4.19 kernel, i686 on GNU/Linux >> Is there any way at = >> all to recover a corrupt InnoDB database? (I read on innodb.com that it = >> is impossible, but hope it is not) > >You should always take backups of valuable data, and also keep the MySQL >binlog so that you can replay the modifications after the backup. So i understand, i'm used to running MyISAM tables, and have never had any problems with data corruption before now. Nothing a good myisamchk couldent fix anyhow. I do have a backup, just not old enough. I've been on vacation, so therefore the data got rotate out the system and overwritten. I only have corruptet backups. >> When I run a query from any InnoDB table in the database MySQL crashes = >> with the following stack trace and errors.=20 > >Did you resolve the stack trace with the right mysqld.sym file? The trace >below is nonsensical. I think so, but i'll have to get back to you with that. >What is the query? What query? The one that triggers the segfault below is any SELECT, SHOW TABLE STATUS or what ever reads the files. >What is the complete .err log? I cannot find any entry in the error before i restartet the mysql process, then it complained the below. > I'm running a GNU/Linux system and MySQL 4.0.13 from the Debian = > unstable. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection
You don't say what your problem is. aaldrik groenewold wrote: I just installed mysql 4.0 .There were many problems with upgrading from 3.23 to 4.0 so I deleted 3.23 and made a fresh start with 4.0. Everything works fine now but the only problem to connect to the server is via the /usr/local/mysql/bin directory. Is there a way to connect if I am root. thanks for your help. -aaldrik __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I enable unicode and japanese support?
> I am using cold fusion to make a small custom website using > mysql to store the information a user inputs. I want it to > be able to save japanese and unicode text, but whenever I > enter any it comes back as garbage. Is there anything > special I need to do to configure mysql to use unicode? Have you looked at section 8 of the MySQL manual? http://www.mysql.com/doc/en/Charset.html -- Joel Rees, programmer, Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table error 127
On Tuesday 05 August 2003 08:58 am, Jonathan Patton wrote: > Walt, > > Thanks for the suggestion. I ran myisamchk on the table and it said it was > corrupted. So I ran myismachk on the table with the -r and it said the > table was fixed. I then ran the update queries I was running before and > received the same 127 error. The update queries were: > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_1 = discussion_categories.category_id > where discussion_categories1.`parent_1_text` = discussion_categories.name; > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_2 = discussion_categories.category_id > where discussion_categories1.`parent_2_text` = discussion_categories.name; > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_3 = discussion_categories.category_id > where discussion_categories1.`parent_3_text` = discussion_categories.name; > > update discussion_categories discussion_categories1, discussion_categories > set discussion_categories1.parent_4 = discussion_categories.category_id > where discussion_categories1.`parent_4_text` = discussion_categories.name; > > > So I shut the mysql server down, ran mysqlchk again and all the tables were > okay. I had a backup of the database, so I just dropped the whole thing and > imported from the backup. (The backup comes from another mysql server). The > errors still persisted. The table in question only has 167 rows in it. I > had a text file as well with the data in it, so I deleted all the data from > the file and loaded the data with the load data infile command. I did get > 1300 some warnings. Could the data being loaded in cause a table > corruption? I'm going to go through the 169 rows being loaded to see if I > can find out the problem or at least eliminate that problem from this > problem. Also, could it be the backup is corrupted? > > Jonathan > Jonathan, Have you tried running each query separately and then checking the table after each one? -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 "If it's not broketweak it" CONFIDENTIALITY NOTICE The information contained in this email may contain legally privileged and confidential information intended only for the use of the individual noted above. If you are not the intended recipient or employee or agent of the entity listed above, you are hereby notified that any reading, disclosure, distribution, or copying of this email communication in any way, or the taking of any action in relation to this communication, is strictly prohibited. If you have received this email in error, please immediately notify the sender and contact our Privacy Officer at (800) 782-5150 ext: 1601. If you were not the intended recipient, please delete it from your files. Thank you for your compliance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Insert bug into MyISAM table (Linux + MySQL 3.23.56)
Hi list,
I've got a little bug with MySQL.
I can insert a row into my table but this row will not appear in the table
:(
Server is under linux redhat, MySQL is 3.23.56 installed from binary tar.gz
from MySQL team.
Table Description :
mysql> desc Log_Forums;
+--+--+--+-+-+--
--+
| Field| Type | Null | Key | Default | Extra
|
+--+--+--+-+-+--
--+
| Pseudo | char(16) | | PRI | |
|
| Date | timestamp(14)| YES | PRI | NULL|
|
| Numero | smallint(5) unsigned | | PRI | NULL|
auto_increment |
| Type_message | enum('0','1')| | | 0 |
|
+--+--+--+-+-+--
--+
4 rows in set (0.00 sec)
And some code :
mysql> select * from Log_Forums where Date="2003080611";
Empty set (0.08 sec)
mysql> insert into Log_Forums (pseudo,date,Type_Message) values
("doss08",2003080611,"0");
Query OK, 1 row affected (0.00 sec)
mysql> select * from Log_Forums where Date="2003080611";
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
mysql> insert into Log_Forums (pseudo,date,Type_Message) values
("coss08",2003080611,"0");
Query OK, 1 row affected (0.00 sec)
mysql> select * from Log_Forums where Date="2003080611";
++++--+
| Pseudo | Date | Numero | Type_message |
++++--+
| doss08 | 2003080611 | 1 | 0|
++++--+
1 row in set (0.08 sec)
As you can see, i can't find "coss08" in my table :(
Table check is ok :
mysql> check table Log_Forums;
+--+---+--+--+
| Table| Op| Msg_type | Msg_text |
+--+---+--+--+
| jeuxvideo.Log_Forums | check | status | OK |
+--+---+--+--+
1 row in set (0.15 sec)
Table have got lots of records with pseudo="coucou".
It seems now that i can't insert any row which pseudo < "coucou"
Can someone help me ?
Thanks
David
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting count query (newbie alert)
Ryan A wrote: Hi, This is my first post here so forgive me if its too simple or not right. basically what i do is: SELECT COUNT(*) FROM table1 where cno=3; (and dumping it into a variable $theResult - I am using PHP) and it gives me the result, but now i need to do this in 5 tables... I can of course run 5 count statements but was wondering if I can do the whole thing in one statement? A friend of mine told me to try "union" but when i go to the documentation of union I cant understand much and it says "UNION is implemented in MySQL 4.0.0" I have no idea what version i have. SHOW VARIABLES LIKE 'version' The 5 tables are pretty much the same and have the same "cno" field that i need and are named table1,table2,table3,table4,table5 SELECT COUNT(*) FROM table1 WHERE cno = 3 UNION SELECT COUNT(*) FROM table2 WHERE cno = 3 UNION SELECT COUNT(*) FROM table3 WHERE cno = 3 UNION SELECT COUNT(*) FROM table4 WHERE cno = 3 UNION SELECT COUNT(*) FROM table5 WHERE cno = 3 this will return 5 rows in result, f.e.: COUNT(*) 12 10 9 23 7 if you want all COUNTS as one row then you have to use f.e. MERGE table type (http://www.mysql.com/doc/en/MERGE.html) SELECT COUNT(*) FROM tablemerge WHERE cno = 3 this would return f.e: COUNT(*) 61 Can anybody help me please? Thanks in advance. cheers, -Ryan We will slaughter you all! - The Iraqi (Dis)information ministers site http://MrSahaf.com -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Problem, Confused by Left Join.
John Wards wrote: I have this query: SELECT * FROM news_category LEFT JOIN news_x_cat ON news_category.id = news_x_cat.cat_id WHERE ( news_x_cat.news_id = 9 OR news_x_cat.news_id IS NULL ) Which gives me this output: id title perm show news_id cat_id 1 About Us 1 1NULL NULL 2 Learn About Your Hair 1 1 NULL NULL 3 Press Room 0 0 9 3 4 Research News 0 0 9 4 Its Padding out with NULLs fine for the first 2 but missing out a few other records from news_category. What I want the query to do is display all the news_categorys if they are mentioned in news_x_cat or not and if they don't have any data with in news_x_cat I need this bit padded out with NULLs. Any ideas where I am going wrong? yes, you use a WHERE if you want all, dont use this WHERE! with your WEHRE you get only this news_category which have a news with the id 9 or no news at all -- Sebastian Mendel www.sebastianmendel.de www.tekkno4u.de www.nofetish.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: column privilege problem--Solved
FYI Note on this. Using column privs you take a performance penalty on reads / writes. -->-Original Message- -->From: Tiffany Wilkes [mailto:[EMAIL PROTECTED] -->Sent: Tuesday, August 05, 2003 3:48 PM -->To: mysql -->Subject: column privilege problem--Solved --> --> I solved the problem--I needed ( ) around the column list. I think -->the manual should make that more clear. --> --> Original Message -->Subject: column privilege problem -->Date: Tue, 05 Aug 2003 15:39:54 -0700 -->From: Tiffany Wilkes <[EMAIL PROTECTED]> -->To: [EMAIL PROTECTED] --> --> --> -->Hi, --> -->I am having problems granting column privileges. I want to grant the -->update privilege (only) to a column (called pass) in a table (called -->Acct). Here's what I get: --> -->mysql> grant update pass on practicedb.Acct to [EMAIL PROTECTED]; -->ERROR 1064: You have an error in your SQL syntax near 'pass on -->practicedb.Acct to [EMAIL PROTECTED]' at line 1 --> -->I don't understand how the syntax is wrong. I think I followed the -->instructions in the manual. I don't have any problems granting table -->privileges--just column. --> -->Tiffany Landry --> --> -->-- -->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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: v4 user privileges
At 22:27 -0400 8/6/03, Keith Schuster wrote: Can someone tell me why the columns ssl_cipher, x509_issuer, x509_subject in the user table are REQUIRED? If they are required what is the default value? They are used to support the SSL-related clauses of the GRANT statement (and hence, secure connections). The default value is the empty string for all of them. I am having a heck of a time working with the mysql gui's and adding records to this table Generally, it's easier to use the GRANT statement than to add records to the user table directly. Thanks --- Keith Schuster Schuster & Company LLC ph:704-799-2438 fx:704-799-0779 iChat/AIM:FSHSales WWW.FlagShipHosting.com WWW.Schusterandcompany.com WWW.Vsheet.net -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DB Performance - Celeron vs. P4
>>Jonathan Hilgeman wrote: >> >>>Currently, I have a Celeron 1.2 Ghz server with 512 RAM, and I'm >>>considering moving to a P4 2 Ghz with the same amount of RAM. I have >>>a few specific tables with several million rows of data, and it >>>takes quite a long time to process that data on my current server. >>>Does anyone have a good idea of the type of performance increase I'd >>>see if I moved to a P4 server? >> >>It's going to depend of in your queries are disk bound or processor >>bound. Check the processor usage when a big query is running (and >>not much else is running). >> >>If the CPU usage is at 80-100% the query is processor bound, so >>you'll see some improvement (though I can't tell you how much, other >>than the clock rate scaling). >> >>On the other hand, if the CPU usage is low, you are likely disk >>bound, so a faster processor won't help much. It would be better to >>add RAM, tune your queries or get faster disks (or all three) in this >>case. [EMAIL PROTECTED] wrote: >scuse the ignorance are u saying high cpu usage is better used on one >query or better be freed up for other queries ? ram is always an issue >as the queries get buffered I'm saying that if your current machine has the mysqld CPU utilization at 100% then what's keeping your queries from going faster is that you've run out of CPU capacity, so adding more (via a faster processor) will make the query go faster. If the mysqld CPU utilization is at 25% then the speed of the processor is not what's making the query go slow (you still have processor capacity left to use, but you can't utilize it because something else is slowing you down). In this case a faster processor won't help much...the processor isn't the limiting factor. This assumes you're doing the benchmarking with nothing else running, obviously. If MySQL is at 25% but you have other programs running heavily it doesn't tell you much, but if it's at 25% and the rest of the capacity is idle then the CPU isn't the problem. --Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
