Re: restore from mysqldump file
On Fri, 2 Jul 2004, Chuck Barnett wrote: > Hello, I have a huge problem that you guys may be able to help me with. > > I did a mysqldump all databases into a sql71.sql file. > > when I try and I get the following error when trying to restore > > ERROR 1050 at line 204528: Table 'columns_priv' already exists > > I'm sure that this is for the mysql database. > > Im doing this to restore > mysql --user=root -p < sql71.sql > > please give me some help on thisthe .sql file is 170+megs in size. It's best to backup databases individually and then you can restore all of them except for the mysql control database, which is clearly present in your case. About the only thing I can think of is to open your sql71.sql dump and edit out the mysql database. This will fix the problem but you will need to use a system with a lot of free memory to do the edit. Hope this helps. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy normalization how-to?
I'm learning more about SQL all the time--I'm new to things now but I'm trying to read. I now realize the importance of normalizing my data, but what I can't figure out is what the easy way is of getting it done now that I already have a database set up. Suppose I have a table "books", consisting of "id", "title", and "publisher"; the "publisher" is redundant. I'd like to create a "publisher" table consisting of "id" and "publisher", and then I want my original "books" table to use its "publisher" column to hold the value of "publisher.id" instead of the publisher's name. Is there a way I can do this with an existing table full of data, other than going through it line-by-line with a scripting language? Thanks! Jen __ 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: Parent-Child Relationship Question
Try these: http://www.sitepoint.com/article/1105 http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/ olinux > > -Original Message- > > From: David Blomstrom > [mailto:[EMAIL PROTECTED] > > Sent: 02 July 2004 03:13 > > To: [EMAIL PROTECTED] > > Subject: Re: Parent-Child Relationship Question > > __ 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: 16-Page Database Table Limit?
In the last episode (Jul 02), David Blomstrom said: > Rhino <[EMAIL PROTECTED]> wrote: > > 2. What happens when you do 'select count(*)' on your new table? > > I'm not familiar with that command. If I type select count (*) into > the "query" box on phpMyAdmin, it says count(*) = 0 Or is this a type > of table query I do on my webpage? First thing to try is a direct connection to mysql via the commandline client. Don't use phpmyadmin to troubleshoot problems, unless you're trying to debug phpmyadmin itself. > I didn't realize there was a problem until today. At first, I was > struck by the fact that MySQL can somehow squeeze 3,000 rows into 16 > pages! Are you talking about 16 disk pages? That's reasonable, if your records average 40 bytes in length and you have 8k disk blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restore from mysqldump file
Hello, I have a huge problem that you guys may be able to help me with. I did a mysqldump all databases into a sql71.sql file. when I try and I get the following error when trying to restore ERROR 1050 at line 204528: Table 'columns_priv' already exists I'm sure that this is for the mysql database. Im doing this to restore mysql --user=root -p < sql71.sql please give me some help on thisthe .sql file is 170+megs in size. Thanks, Chuck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 16-Page Database Table Limit?
--- Rhino <[EMAIL PROTECTED]> wrote: > I would try to track the problem down as follows: > > 1. Are there any error messages from the import? If > yes, what do they say? No. I import my data as csv files using Navicat, and I fix any errors until they import with 0 errors. > If the import > actually says that it has only loaded 480 rows and > rejected the rest for > Reason X, have you done anything to eliminate Reason > X? For example, if the > import says it can only load 480 rows because the > *disk* is full, have you > tried locating your table on a different disk? I don't recall seeing messages of this nature. > 2. What happens when you do 'select count(*)' on > your new table? I'm not familiar with that command. If I type select count (*) into the "query" box on phpMyAdmin, it says count(*) = 0 Or is this a type of table query I do on my webpage? Does it say > 480; if it does, you should tend to believe phpAdmin > that the table really > does contain only 480 rows and then double-check the > import. Does it say > 3000; if it does, you may be misunderstanding > phpAdmin's display and are > confusing the number of *pages* of data with the > number of rows of data. Or > maybe phpAdmin is misreporting the amount of data in > the table; it is a > program like any other and could have a bug in it. > Do you have any tables at > all in your MySQL database that have more than 480 > rows? If yes, you've > proven that MySQL tables can contain over 480 rows. > Look at the phpAdmin > display for that table and see if they are > "truncated at 16 pages" too. > > If the count(*) really shows 480 rows in the table > and there are no error > messages in your import, or if you can't understand > the messages, you should > post the import statement you've used, a few sample > rows of your input, the > definition of your table, and the messages, if any. > Someone here can > probably figure out what you're doing wrong. I checked two tables and found that each end with the row below (row #480): "al01103","Morgan","cou","al01103","us-al","Alabama","sta","al"480 This would be row #481: "al01105","Perry","cou","al01105","us-al","Alabama","sta","al"481 The fields are all char(3) or varchar(4-255), except for one int(5). All the Null values are Yes/Null except for the first and last fields, which are No. There is no index. I didn't realize there was a problem until today. At first, I was struck by the fact that MySQL can somehow squeeze 3,000 rows into 16 pages! Thanks. __ 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: slow response time
Hi, Just following up with some more information... I've been seeing a bit better behaviour by going from LinuxThreads to FreeBSD native threads. My hunch is also that this is somehow related to qmail; something of a contention issue between qmail and mysql. As I said, system load remains low. BUT, there are processes blocking, and vmstat shows that context switching is where I'm getting hit. Does that information help at all? Interactive performance is good, qmail is happy, but it seems like mysql gets "the short end of the stick" in this situation. Thanks, Charles On Thu, 1 Jul 2004, Charles Sprickman wrote: > Hi, > > I have (what I thought) was a fairly small mysql setup. It's backing a > vpopmail installation, and basically just handling alot of SELECTs. > > In the process of delivering mail, the db gets hit a few times: > > -initial check that the user exists during smtp conversation > -lookup for homedir during delivery > -optional lookup again if doing spam filtering > > Additionally, everytime someone authenticates via smtp-auth, pop or imap, > there's a SELECT to grab the password. > > So I haven't really done much to optimize things, as this seems like a > fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux > Threads). > > However, I'm starting to have problems. The various vpopmail programs > that perform all these lookups are timing out, which leads to login > failures (and worse). I see a number of simple things like "PINGs" from a > local cronjob that graphs queries/sec timing out. > > Server load can swing from .30 during a calm period to brief spikes of > 10.0-15.0 (generally during a spam run). > > I'm not sure what exactly to look at. I've bumped my max connections up > to 1000, and based my my.cnf on the "my-large.cnf" file and I'm still > seeing plenty of slow queries. I'm not swapping to disk at all, I'm not > really taxing the CPU, and iostat shows that the raid array isn't > struggling. > > It's been at least 4 years since I had to get very in-depth with MySQL, > and it seems plenty has changed (for the better). If anyone can give me a > push in the right direction, it would be much appreciated. > > Thanks, > > Charles > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing to an MySQL Database
On Fri, 2004-07-02 at 09:05, Michael Mason wrote: > It’s the “Data Capture section that’s causing issues. I keep getting > an error telling me there’s an “Unexpected $” on a line that’s > actually outside even the HTML tag. Errors like "unexpected $" that are output by a code or statement parser are sometimes related to the parser running off the end of its input. "$" is tokening parser parlance for "end of input/file". So "unexpected $" really means "unexpectedly reached the end of the file". As others have pointed out, this is because you were missing a quote that closes a string. -- Andy Bakun: a killer headache <[EMAIL PROTECTED]> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing to an MySQL Database
Michael, you're missing a quote just before the last semi-colon. PB - Original Message - From: Michael Mason To: 'MySQL Mailing List' Sent: Friday, July 02, 2004 9:05 AM Subject: Writing to an MySQL Database Im confused and obviously missing something really fundamental here I would appreciate it if you could glance at my code for the above. Basically Im trying to write information to the database now I have managed to resolve the connection issue. Its the Data Capture section thats causing issues. I keep getting an error telling me theres an Unexpected $ on a line thats actually outside even the HTML tag. Michael Mason Business Support Services Arras® People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt. -- MySQL General Mailing ListFor list archives: http://lists.mysql.com/mysqlTo unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error 1005 when adding a Foreign Key
Hi, Check if column PlanName is the primary key (and the only one) in table testplans. If so, check if both fields PlanName in testplans and PlanName in runload_list are exactly of the same type and size. Regards, Hector -- Ing. Hector Maldonado Melgar Dpto. Desarrollo de Software TCI S.A., Lima-Peru [EMAIL PROTECTED] Of.: 421-3222 Cel: 9503-9205 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64 Bit Support
On Fri, Jul 02, 2004 at 10:27:04AM -0700, David Griffiths wrote: > > Sorry - didn't read your email closely enough. The Windows version is > not native - runs under Cygwin. Is there a version of Cygwin for the > Itanium 2? The Windows version of MySQL doesn't require Cygwin. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to write a query to return records has the closest date to end of month
Hi, guys I would like to write a query to return all records which has the closest date to the end of month. (Assume that date will be 6/30/2004) | id | product_id | price | snapshot_date | ++++---+ | 1 | 1 | 99.95 |2004-06-23 | | 2 | 1 | 99.74 |2004-06-27 | * | 3 | 2 | 101.52 |2004-06-25 | | 4 | 2 | 101.85 |2004-07-01 | * | 5 | 3 | 100.00 |2004-06-29 | | 6 | 3 | 100.50 |2004-07-01 | * ++++---+ *if there is a tie, it always pick the one after the end of month The 2, 4, 6 entries shall be return by the query. Is this possible to write this in pure sql? Thanks in advance. Lam The best thing to hit the Internet in years - NetZero HiSpeed! Surf the Web up to FIVE TIMES FASTER! Only $14.95/ month -visit www.netzero.com to sign up today! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error 1005 when adding a Foreign Key
Bartis, Robert M (Bob) wrote: I recently added a column and Index to an existing table. I wanted to also add a Foreign Key. I have done this before defining the commands outside MySQL and souring the file in for new tables, but would prefer to not have to dump the current table just for the modification. I tried to add one based on the users guide, see command/response below, without success. Obviously I am missing something. Any suggestions? mysql> alter table runload_list add foreign key (PlanName) references testplans (PlanName) on update cascade on delete restrict; ERROR 1005: Can't create table './mydb/#sql-3ebd_430.frm' (errno: 150) From the manual at http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message string refers to errno 150, this means that the table creation failed because a foreign key constraint was not correctly formed. Similarly, if an ALTER TABLE fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display a detailed explanation of the latest InnoDB foreign key error in the server. So, you should check "show innodb status" for some more about this error it seems. Also, one of the more common errors is a lack of an index on testplans.PlanName, so make sure you have one of those too. Regards, Josh Josh Chamas, Founder| NodeWorks - http://www.nodeworks.com Chamas Enterprises Inc. | NodeWorks Directory - http://dir.nodeworks.com http://www.chamas.com | Apache::ASP - http://www.apache-asp.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTEL vs. SPARC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi andrew, I don't have a vast experience of MySQL on Solaris, but I suspect this is more down to the operating system than any hardware issues. Which version of Solaris are you running on the SPARC box? it's Solaris 9 - SunOS 5.9 64bit. the mysql is a 32bit-version. cu mac -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (Darwin) iD8DBQFA5bd5vkHn/oGTPXURAk+hAJ9HsMq4smLeJ5efK4/+L8wdM7BmcgCfYjMH gLOf+KCDirJtaTYnTlFpSJY= =PuhY -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
16-Page Database Table Limit?
I'm working on some tables with more than 3,000 rows that I import from csv files and notice that they're consistently truncated at 16 pages in phpMyAdmin. In other words, my table apparently features about 480 rows. Why can't I insert the remaining 2,600 rows? __ 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: Display of "?" and Hex conversion
On Fri, Jul 02, 2004 at 01:52:06PM -0500, Boyd E. Hemphill wrote:
> We discovered a rather odd situation where some space characters where
> being displayed as "?".
>
> In tracking this down, it was determined that the server had stored the
> hex value "A0" rather than "20" by using this query:
'A0' is the code for a non-breaking space, assuming you're using the
iso-8859-1 (or related) character encoding.
> update Location
>set NameLn = replace(hex(NameLn), 'A0' , '20')
> where hex(NameLn) like '%A0%'
Better would have been:
UPDATE Location
SET NameLn = REPLACE(NameLn, CHAR(0xA0), ' ')
WHERE NameLn LIKE CONCAT('%',CHAR(0xA0),'%')
> Now for the NameLn field I have the hex string (arrg my data has
> been hexed!!! :-)
>
> So, my questions are:
> 1. How do I go back from the hex string to characters?
You can use the UNHEX() function.
> 2. Has anyone else seen this problem? At this point I can say the
> diplay issue only appears on some browsers.
The likely culprit for this sort of thing, in my experience, is users
using cut-and-paste from an application like Microsoft Word into their
browser. That often introduces similar issues with characters like
curly-quotes and em-dashes.
Jim Winstead
MySQL AB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1005 when adding a Foreign Key
I recently added a column and Index to an existing table. I wanted to also add a Foreign Key. I have done this before defining the commands outside MySQL and souring the file in for new tables, but would prefer to not have to dump the current table just for the modification. I tried to add one based on the users guide, see command/response below, without success. Obviously I am missing something. Any suggestions? mysql> alter table runload_list add foreign key (PlanName) references testplans (PlanName) on update cascade on delete restrict; ERROR 1005: Can't create table './mydb/#sql-3ebd_430.frm' (errno: 150) Robert M. Bartis Lucent Technologies ¢ Room HO 1C-413A (HO) / 1B-304 (WH) ( 732.949.4565 (HO) / 973.386.7426 (WH) * [EMAIL PROTECTED]
Display of "?" and Hex conversion
All: We discovered a rather odd situation where some space characters where being displayed as "?". In tracking this down, it was determined that the server had stored the hex value "A0" rather than "20" by using this query: select LocationId, LevelId, Hex(NameLn),NameLn from Location where LevelId = 3 order by NameLn; So, I issued this statement to fix it (using 4.0.16) update Location set NameLn = replace(hex(NameLn), 'A0' , '20') where hex(NameLn) like '%A0%' Now for the NameLn field I have the hex string (arrg my data has been hexed!!! :-) So, my questions are: 1. How do I go back from the hex string to characters? 2. Has anyone else seen this problem? At this point I can say the diplay issue only appears on some browsers. Thanks Boyd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Parent-Child Relationship Question
Essentially, I think, you are asking about organizing hierarchical data. This is something which I always find tricky to wrap my head around, but, one approach I seen used successfully is to use (initially) two tables: 1. A table such as 'Places' which contains the actual data (or Locations, any suitable name really) 2. A second table which contains the 'meta-data' - in this case, the organization of the hierarchy. So. Places might be: PlaceID, Type, Name, Description Places_Hierarchy might be: PlaceHierarchyID, PlaceID, ParentID Thus, for the example below.. Places: 1 state Arizona .. stuff .. 2 country USA .. stuff .. 3 country Japan .. stuff .. Places_Hierarchy: 1 1 2 2 2 NULL 3 3 NULL Then, you can join the two together and organize things that way. There are a number of other things you can try - like moving the 'type' out into a lookup table and storing the ID in the hierarchy (allowing you to retrieve all places of a certain type, for instance). This is a situation in which views are (for me, anyway) sorely missed! Cheers, Matt > -Original Message- > From: David Blomstrom [mailto:[EMAIL PROTECTED] > Sent: 02 July 2004 03:13 > To: [EMAIL PROTECTED] > Subject: Re: Parent-Child Relationship Question > > And here's a follow up question... > > After looking at my database from a fresh perspective, > I'm now thinking of combining tables "area" and > "family" into a single table. > > If I do that, it would make life so much simpler if I > had TWO name fields, like this: > > ID | Name | ParentID | Parent Name > > az |Arizona|us| United States > us | United States |kna | North America > jpn | Japan |keu | Eurasia > > I could then slap a $mycode = 'az" on a page and > easily fill in its name and the name of its parent > without fiddling with queries, joins, unions, etc. > > I know that duplicating names in two fields isn't the > most elegant solution, but would create any major > problems? > > Thanks. > > > > __ > Do you Yahoo!? > Read only the mail you want - Yahoo! Mail SpamGuard. > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTEL vs. SPARC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi andrew, Are you running Linux or Solaris on these servers? sparc: solaris 9 intel: debian-linux (2.4.21-kernel) with regards klaus -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (Darwin) iD8DBQFA5acJvkHn/oGTPXURAlG4AJ9iAHBuX3n/RhCFzN9uj1ssC2VRAgCfcHql n9Tu0EJQUAqk7YsegQxw0So= =YaKf -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTEL vs. SPARC
Are you running Linux or Solaris on these servers? Andrew. - Original Message - From: "mac" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, July 02, 2004 4:42 PM Subject: INTEL vs. SPARC > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > hi there, > > hope this haven't been discussed in that kind of detail in older topics: > > the facts in short: > - - we have the same mysql-version (4.0.18) for an intel-machine and a > sparc-machine. > - - we have a table with about 5,500,000 rows > - - we do a "realtivly" simple select on a varchar-50-field (with an > index of course) > - - the statement takes 4s on the intel- and 24s on the sparc-machine > - - we played around with some caching-features on the sparc-side but > there was no significant increase of speed > > the hardware: > sparc: > Sun Fire V480 > 4 x UltraSPARC III Cu Processor 900 MHz > 16GM RAM > > intel: > Compaq DL380R02 > 2 x Pentium III 1.1 GHz > 4 GB RAM > > the installation on the sparc was done with a precompiled version. the > intel-one was compiled by our self (but no special configue-options). > > > we also ensured that the index on the sparc side is in good shape. > we also tested it with the same setup on an older enterprise 450 - same > bade timing. > > > if you need more detail i can deliver them. > but i am more interested in a general question: > > does the speed of mysql depends more on things like the processor than > other resources? > if so: what kind of general suggestions can be made about using > select-statements on huge tables to be "fast" over different platforms? > > i am also aware of the hints about not using to huge logtables for > statistical output rather then creating small statistic tables. > this is something we will do, but we also need sort of guidelines for > future projects. > > > thanks in advance for your suggestions. > > with regards > mac > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.2.3 (Darwin) > > iD8DBQFA5YJVvkHn/oGTPXURApvwAKDBBcLtRH+S1+tBLsrFNmimtSki+gCgkQA6 > MEQxsqwYzRjQx+lN+epJtao= > =2VNS > -END PGP SIGNATURE- > > > -- > 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: 64 Bit Support
Sorry - didn't read your email closely enough. The Windows version is not native - runs under Cygwin. Is there a version of Cygwin for the Itanium 2? The Windows versions are quite a bit slower than the Linux/Unix versions, so you might not get the benefits from that fast 64-bit processor. Daivd Nawal Lodha wrote: Thanks David. But I think the Itanium binaries are available only for HP-UX or Linux but not for Windows 2003. -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Friday, July 02, 2004 12:10 PM To: [EMAIL PROTECTED] Subject: Re: 64 Bit Support The download page @ MySQL.com (http://dev.mysql.com/downloads/mysql/4.0.html) shows a wack of Itanium binaries, so I would guess it does. David Nawal Lodha wrote: Can someone tell me if MySQL 4.1.3-beta supports 64 Bit Itanium 2 machines with Windows 2003? Thanks, Nawal Lodha. -- 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]
INTEL vs. SPARC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi there, hope this haven't been discussed in that kind of detail in older topics: the facts in short: - - we have the same mysql-version (4.0.18) for an intel-machine and a sparc-machine. - - we have a table with about 5,500,000 rows - - we do a "realtivly" simple select on a varchar-50-field (with an index of course) - - the statement takes 4s on the intel- and 24s on the sparc-machine - - we played around with some caching-features on the sparc-side but there was no significant increase of speed the hardware: sparc: Sun Fire V480 4 x UltraSPARC III Cu Processor 900 MHz 16GM RAM intel: Compaq DL380R02 2 x Pentium III 1.1 GHz 4 GB RAM the installation on the sparc was done with a precompiled version. the intel-one was compiled by our self (but no special configue-options). we also ensured that the index on the sparc side is in good shape. we also tested it with the same setup on an older enterprise 450 - same bade timing. if you need more detail i can deliver them. but i am more interested in a general question: does the speed of mysql depends more on things like the processor than other resources? if so: what kind of general suggestions can be made about using select-statements on huge tables to be "fast" over different platforms? i am also aware of the hints about not using to huge logtables for statistical output rather then creating small statistic tables. this is something we will do, but we also need sort of guidelines for future projects. thanks in advance for your suggestions. with regards mac -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (Darwin) iD8DBQFA5YJVvkHn/oGTPXURApvwAKDBBcLtRH+S1+tBLsrFNmimtSki+gCgkQA6 MEQxsqwYzRjQx+lN+epJtao= =2VNS -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bugzilla not working now after MySQL re-installed
Yes it does, as it is a 'special' file (it's a socket), notice the 's' in the file definition. Link this to /tmp/mysql.sock and you should be ok -Original Message- From: Nguyen, Long P (Mission Systems) [mailto:[EMAIL PROTECTED] Sent: 01 July 2004 21:14 To: Yiannis Mavroukakis; [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed The 'mysql.sock' file is at /var/lib/mysql/mysql.sock with a size of 0 Does this look right? [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# ls -l mysql.sock srwxrwxrwx1 mysqlmysql 0 Jul 1 11:41 mysql.sock -Original Message- From: Yiannis Mavroukakis [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 12:23 PM To: [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed The original error might be slightly more cryptic than it intends. It is saying that it cannot connect to your db through /tmp/mysql.sock. Check if your mySQL installation has installed the socket file in another place. If it has, a quick and dirty fix is to soft link the socket file wherever it is to /tmp/mysql.sock i.e. ln -s /usr/local/mysql/mysql.sock /tmp/mysql.sock Change /usr/local/mysql/mysql.sock to your file. In some distributions it can be found in /var/lib/mysql/mysql.sock. Yiannis. -Original Message- From: Nguyen, Long P (Mission Systems) [mailto:[EMAIL PROTECTED] Sent: 01 July 2004 17:07 To: Hassan Schroeder; [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:52 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: > I tried this and get a 0 affect below.. > > mysql> GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON > bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; > Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. Note:__ This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Jaguar Freight Services and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. Note:__ This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any
Privilege question
It it possible to allow access to all tables in a database *except* one... What I was hoping to do was say: GRANT ALL ON mydb.* TO [EMAIL PROTECTED] ; REVOKE ALL ON mydb.secrettable TO [EMAIL PROTECTED] ; -- but it doesn't work like that... I can only revoke stuff that's previously been specifically granted. I *could* add the privs table at a time if it's the only option, but I'd like a better way, as the tables in 'mydb' change frequently. - I'd like the relevant users to be allowed access to all of them except one (which never changes, but needs to be in the same database), and I'd prefer not to have to update the privs for all the users, every time the tables change too. Can it be done? Thanks, Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] mysql limit
If I want 20 rows to display, I ask for 21. If I get 21, I know to ask for another page. Bob Lockie wrote: Moved from a PHP list to a MySQL list. :-) On 06/30/2004 09:55 PM John W. Holmes spoke: Bob Lockie wrote: If I select rows with a limit clause I need to know if there are more rows than the limit. Either do a SELECT COUNT(*) prior to your LIMIT query to see how many total rows there are, or use SQL_CALC_FOUND_ROWS and FOUND_ROWS() (more info here: http://dev.mysql.com/doc/mysql/en/Information_functions.html) Is found_rows standard SQL? I want to easily portable code. Maybe I should use a select count first. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing to an MySQL Database
> It's the "Data Capture section that's causing issues. I keep getting an
> error telling me there's an "Unexpected $" on a line that's actually
outside
> even the HTML tag.
You forgot to end the $sql = ".. with a closing quote. The line now ends
with ); while you probably want it to be )";
BTW it's not necessary or even wise to quote every variable you use:
mysql_connect ("$host", "$user", "$password")
would preferrably be:
mysql_connect ($host, $user, $password)
Regards, JIgal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Writing to an MySQL Database
You're missing a closing " at the end of your sql on the last line which may be throwing up an error. Cheers, Matt On 2 Jul 2004, at 15:05, Michael Mason wrote: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iptables and mysql...
bruce wrote: we disagree on this... properly configuring/securing the mysql app is both a linux/mysql issue... and reading the manual is an admin issue *sigh*... do so... http://dev.mysql.com/doc/mysql/en/Starting_server.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing innoDB
On 07/01/2004 11:48 PM [EMAIL PROTECTED] spoke: I have MySQL 3.23 installed, which comes with RedHat ES3. I find that innoDB is not enabled. Anybody knows can I enable it? Thanks, Joseph Recompile the binary or upgrade to one that includes it. I don't think there is a runtime switch. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] mysql limit
Moved from a PHP list to a MySQL list. :-) On 06/30/2004 09:55 PM John W. Holmes spoke: Bob Lockie wrote: If I select rows with a limit clause I need to know if there are more rows than the limit. Either do a SELECT COUNT(*) prior to your LIMIT query to see how many total rows there are, or use SQL_CALC_FOUND_ROWS and FOUND_ROWS() (more info here: http://dev.mysql.com/doc/mysql/en/Information_functions.html) Is found_rows standard SQL? I want to easily portable code. Maybe I should use a select count first. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: debug
On 06/30/2004 02:46 PM Andrew Pattison spoke: The way I do this is within PHP is to echo the value stored in mysql_error after each SQL statement. If you're not using PHP then this probably doesn't help though ;-) I'm not using PHP. I'm loading tons of data with SQL statements from the command line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Writing to an MySQL Database
I’m confused and obviously missing something really
fundamental here…
I would appreciate it if you could glance at my code for the
above. Basically I’m trying to write information to the database now I
have managed to resolve the connection issue.
It’s the “Data Capture section that’s
causing issues. I keep getting an error telling me there’s an
“Unexpected $” on a line that’s actually outside even the
HTML tag.
Michael Mason
Business Support Services
Arras® People
Tel: 01706 342310
Mobile: 07793
782287
Fax: 01706 642754
Member of the Recruitment Employment Confederation
(00052055)
The views expressed in this mail are entirely those of the
sender, and do not necessarily represent the views or position of Arras
Services Ltd. The information contained in this communication is confidential
and may be legally privileged. It is intended solely for the use of the
individual or entity to whom it is addressed and others authorised to receive
it. If you are not the intended recipient you are hereby notified that any
disclosure, copying, distribution or taking any action in relation to the
contents of this information is strictly prohibited and may be unlawful.
Neither the sender nor the represented institution is liable for the correct
and complete transmission of the contents of this e-mail, or for its timely
receipt.
<><>";
echo "UserID: \t\t";
echo "";
echo "Password: \t \t";
echo "";
echo "a little about me: \t";
echo "";
echo "more about me: \t";
echo "";
echo "\t\t\t";
echo "";
/* Data Capture */
$NewUserID=$_POST['TXT_UserID'];
$NewUserPassword=$_POST['TXT_UserPassword'];
$NewUserComments=$_POST['TXT_Comments'];
$NewUserFurtherComments=$_POST['TXT_FurtherComments'];
$sql = "INSERT INTO RegisteredMembers
(TXT_UserID,TXT_UserPassword,TXT_Comments,TXT_FurtherComments)
VALUES('$NewUserID','$NewUserPassword','$NewUserComments','$NewUserFurtherComments');
?>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: iptables and mysql...
we disagree on this... properly configuring/securing the mysql app is both a linux/mysql issue... it's an issue that would be useful to anyone running a mysql box, who's interested/needs security... we're looking for actual iptable examples, for both mysql server box, as well as client boxes that would talk to the server... peace.. -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, July 02, 2004 6:56 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; 'General Red Hat Linux discussion list' Subject: Re: iptables and mysql... This is not a mysql question, and please do not cross post. Any good book un Linux networking will cover this. Open port 3306. bruce wrote: >hi... > >i'm investigating what needs to be done to allow mysql on a server to be >used remotely by client machines. each machine is running iptables. so i'm >wondering what has to be in the iptables for the machine being used as the >mysql server, as well as the client machines that will be communicating with >the mysql box... > >also, how would i test that the iptable/mysql configuration setup is working >properly... > >i have rh8.0 and mysql v11.18-dist3.23.54 > >any pointers/comments/criticisms or actual iptable configs illustrating this >would be extremely helpful!!! > >thanks > >-bruce > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iptables and mysql...
This is not a mysql question, and please do not cross post. Any good book un Linux networking will cover this. Open port 3306. bruce wrote: hi... i'm investigating what needs to be done to allow mysql on a server to be used remotely by client machines. each machine is running iptables. so i'm wondering what has to be in the iptables for the machine being used as the mysql server, as well as the client machines that will be communicating with the mysql box... also, how would i test that the iptable/mysql configuration setup is working properly... i have rh8.0 and mysql v11.18-dist3.23.54 any pointers/comments/criticisms or actual iptable configs illustrating this would be extremely helpful!!! thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange server crashes with large table and myisamchk
Try this:
dd if=/dev/hda of=/dev/null
This will exercise the entire drive.
You should see lots of errors if your drive is failing.
Another possibility is a bad cable. Cables don't usually go bad if
they are
not disturbed. Drives do.
A failing IDE contoller is another unlikely possibility.
I would put my money on the drive.
40G is tiny these days, and cheap.
Hanno Fietz wrote:
Yes, I was suspecting that as well, but: Why do I get these messages
whenever I run myisamchk and (almost) never at any other time? Is
myisamchk using the hd more extensively than e. g. MySQL itself? Can
the rather large demand for temporary disk space account for that?
Thanks,
Hanno
gerald_clark wrote:
It is telling you that your hard drive is failing.
Replace it.
Hanno Fietz wrote:
Hello everybody,
I'm experiencing problems with a 4.0.15 MySQL-Server running on a
SuSE Linux 8.2 box with 512 MB RAM, some one-point-something GHz CPU
and 40 GB IDE Harddisk.
We have a database with some administrative tables and one large
data table (now ~ 30 M rows, ~ 1GB index file and ~ 800 MB data
file) that we insert new rows into on a per-minute basis. Read /
Write ratio probably is around 1 : 2 or 1 : 3. To achieve good
performance despite the size of the table, we run "myisamchk -r" and
"myisamchk -R 1" every night as a part of the backup routine. The
server is taken down for that purpose.
For the last two weeks now, we are getting these syslog messages
when running the optimization:
Jul 2 03:10:28 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:28 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316864
Jul 2 03:10:28 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316864
Jul 2 03:10:28 t56 kernel: klogd 1.4.1, -- state change
--
Jul 2 03:10:30 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:30 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316872
Jul 2 03:10:30 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316872
Jul 2 03:10:32 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:32 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316880
Jul 2 03:10:32 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316880
Jul 2 03:10:33 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:33 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316888
Jul 2 03:10:33 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316888
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316896
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316896
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316904
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316904
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316912
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316912
Jul 2 03:12:17 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:12:17 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=159072, sector=46592
Jul 2 03:12:17 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 46592
Jul 2 03:12:19 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:12:19 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=159072, sector=46600
Jul 2 03:12:19 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 46600
Jul 2 03:13:14 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:13:14 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=285328, sector=172864
Jul 2 03:13:14 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 172864
Jul 2 03:13:16 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:13:16 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=285328, sector=172872
Jul 2 03:13:16 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 172872
Occasionally (not always!!), the MySQL-Server won't some up again
after optimization, sometimes myisamchk even leaves the table
corrupted and has to be run again. To make it even more confusing:
sometimes I get server crashes during shutdown, due to signal 11
(SEGV). I included a resolved stack dump below:
0x8071f64 handle_seg
Re: iptables and mysql...
On Fri, 2 Jul 2004 06:34:39 -0700 "bruce" <[EMAIL PROTECTED]> wrote: > i'm investigating what needs to be done to allow mysql on a server > to be used remotely by client machines. each machine is running > iptables. so i'm wondering what has to be in the iptables for the > machine being used as the mysql server, as well as the client > machines that will be communicating with the mysql box... Assuming tcp communications in mysqld are not disabled and grant permissions are setup to allow remote connections, you should just be able to just allow your firewall to accept port 3306 (the default MySQL port) I use this on my FORWARD chain as I NAT it - you'll probably use it on the INPUT chain if you are running the firewall on a local machine: $IPTABLES -A FORWARD -p tcp -j ACCEPT --dport 3306 -m state --state NEW The --state NEW is used in conjunction with: $IPTABLES -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT which says to accept all connections that have already been established. $IPTABLES is just a var with the path to the iptables command. > also, how would i test that the iptable/mysql configuration setup is > working properly... Best way to test is to actually try it I guess. Find a remote computer and see if your iptables rules allows you to connect to mysql. If you are able to connect to mysql and input your username/password but get a permission denied or something then most likely your GRANTs are incorrect. Make sure connections are still denied by your firewall on ports you have closed, turn on logging, etc. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iptables and mysql...
bruce wrote: i'm investigating what needs to be done to allow mysql on a server to be used remotely by client machines. each machine is running iptables. so i'm wondering what has to be in the iptables for the machine being used as the mysql server, as well as the client machines that will be communicating with the mysql box... The MySQL standard port is 3306/tcp. So your iptables on the MySQL server are supposed to allow communication on this port for the clients. i have rh8.0 and mysql v11.18-dist3.23.54 huh! Either one is quite old. Think about upgrading - it is a security issue on the one hand - on the other hand, it could prevent a lot of headache using current versions. Anyway, some manual reading could help answering your questions. Greetz Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get count(id) when count is zero?
Hi, > It sounds like a simple query but I have spent quite a few hours already, and still do not have a solution. Would greatly appreciate your response. > > Here is what I am trying to do: > --- > create table C (cId tinyint(4) NOT NULL); > insert into C values (1), (2), (3), (4), (5); > > create table D (id tinyint(4) NOT NULL, catId tinyint(4) NOT NULL); > insert into D values (1,2), (2,2), (3,2), (4,1), (5,1); > > select C.cId, count(C.cId) from C, D where C.cId = D.catId group by C.cid; > > This returns: > - > > | cId | count(C.cId) | > +-+--+ > | 1 |2 | > | 2 |3 | > +-+--+ > 2 rows in set (0.01 sec) > > > What I would like to see is: > > > | cId | count(C.cId) | > +-+--+ > | 1 |2 | > | 2 |3 | > | 3 |0 | > | 4 |0 | > | 5 |0 | I'm unsure why Count(C.cID) should be 0 if you're counting C.CIDs... This returns what you want, but it counts catID in D: select C.cId, count(D.catId) from C left outer join D on C.cId = D.catId group by C.cid With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange server crashes with large table and myisamchk
Yes, I was suspecting that as well, but: Why do I get these messages
whenever I run myisamchk and (almost) never at any other time? Is
myisamchk using the hd more extensively than e. g. MySQL itself? Can the
rather large demand for temporary disk space account for that?
Thanks,
Hanno
gerald_clark wrote:
It is telling you that your hard drive is failing.
Replace it.
Hanno Fietz wrote:
Hello everybody,
I'm experiencing problems with a 4.0.15 MySQL-Server running on a
SuSE Linux 8.2 box with 512 MB RAM, some one-point-something GHz CPU
and 40 GB IDE Harddisk.
We have a database with some administrative tables and one large data
table (now ~ 30 M rows, ~ 1GB index file and ~ 800 MB data file) that
we insert new rows into on a per-minute basis. Read / Write ratio
probably is around 1 : 2 or 1 : 3. To achieve good performance
despite the size of the table, we run "myisamchk -r" and "myisamchk
-R 1" every night as a part of the backup routine. The server is
taken down for that purpose.
For the last two weeks now, we are getting these syslog messages when
running the optimization:
Jul 2 03:10:28 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:28 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316864
Jul 2 03:10:28 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316864
Jul 2 03:10:28 t56 kernel: klogd 1.4.1, -- state change
--
Jul 2 03:10:30 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:30 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316872
Jul 2 03:10:30 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316872
Jul 2 03:10:32 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:32 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316880
Jul 2 03:10:32 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316880
Jul 2 03:10:33 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:33 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316888
Jul 2 03:10:33 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316888
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316896
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316896
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316904
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316904
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316912
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316912
Jul 2 03:12:17 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:12:17 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=159072, sector=46592
Jul 2 03:12:17 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 46592
Jul 2 03:12:19 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:12:19 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=159072, sector=46600
Jul 2 03:12:19 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 46600
Jul 2 03:13:14 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:13:14 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=285328, sector=172864
Jul 2 03:13:14 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 172864
Jul 2 03:13:16 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:13:16 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=285328, sector=172872
Jul 2 03:13:16 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 172872
Occasionally (not always!!), the MySQL-Server won't some up again
after optimization, sometimes myisamchk even leaves the table
corrupted and has to be run again. To make it even more confusing:
sometimes I get server crashes during shutdown, due to signal 11
(SEGV). I included a resolved stack dump below:
0x8071f64 handle_segfault + 420
0x82916c8 pthread_sighandler + 184
0x8188a9f btr_search_drop_page_hash_index + 5359
0x8188e1a btr_search_drop_page_hash_when_freed + 138
0x81dbbea fseg_free_extent + 746
0x81dc7fa fseg_free_step + 2458
0x815c3ba btr_free_but_not_root + 122
0x8100efe dict_drop_index_tree + 94
0x814969a row_upd_clust_step + 538
0x81499fa row_upd + 106
0x8149c62 row_upd_step + 322
0x811c7b
How to get count(id) when count is zero?
Hello List, It sounds like a simple query but I have spent quite a few hours already, and still do not have a solution. Would greatly appreciate your response. Here is what I am trying to do: --- create table C (cId tinyint(4) NOT NULL); insert into C values (1), (2), (3), (4), (5); create table D (id tinyint(4) NOT NULL, catId tinyint(4) NOT NULL); insert into D values (1,2), (2,2), (3,2), (4,1), (5,1); select C.cId, count(C.cId) from C, D where C.cId = D.catId group by C.cid; This returns: - | cId | count(C.cId) | +-+--+ | 1 |2 | | 2 |3 | +-+--+ 2 rows in set (0.01 sec) What I would like to see is: | cId | count(C.cId) | +-+--+ | 1 |2 | | 2 |3 | | 3 |0 | | 4 |0 | | 5 |0 | I am using mysql Ver 12.21 Distrib 4.0.15, for mandrake-linux-gnu (i586) on Mandrake Linux, 9.1. Thanks, Nishi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
iptables and mysql...
hi... i'm investigating what needs to be done to allow mysql on a server to be used remotely by client machines. each machine is running iptables. so i'm wondering what has to be in the iptables for the machine being used as the mysql server, as well as the client machines that will be communicating with the mysql box... also, how would i test that the iptable/mysql configuration setup is working properly... i have rh8.0 and mysql v11.18-dist3.23.54 any pointers/comments/criticisms or actual iptable configs illustrating this would be extremely helpful!!! thanks -bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
MaFai wrote: >Dear ALL: > >Some time the slave would crushed by I/O error.It happen at the server with the lower >CPU.( per 45 days ) >All of the slave database has block the binary log,it reduce the CPU loading. >The database would replicate text,int,varchar but no blob. >Network connection hasn't drop frequently,it seems hard to avoid that. > What does this mean? It does or does not drop frequently? If you have a poor network connection, you will never get this working. >Do any way make the slave query more time but not just idle and wait for the data? >Since we can't bear the slave need 10 minutes to synchronize the data with master. > > > >Here the master status >Uptime: 2592393 Threads: 24 Questions: 214644229 Slow queries: 197 Opens: 580 >Flush tables: 1 Open tables: 254 Queries per second avg: 82.798 > > > At 2004-07-01, 22:06:24 you wrote: > >What does the network setup look like? What type of data is being replicated >varchar, text, blob? Is the network connection being dropped or are there >any errors being logged anywhere? > >-Original Message- >From: MaFai >To: [EMAIL PROTECTED] >Sent: 6/30/04 10:34 PM >Subject: Replication Performance > >Dear, [EMAIL PROTECTED], > >We have set up 1 master and 4 slave as replication. >Sometime,the slave need 4~10 minutes to synchronize the data with >master database. >Do any way to tune the performance? >Or any other way to reduce the time to replicate? > > >Best regards. > >MaFai >[EMAIL PROTECTED] >2004-07-01 > > >= = = = = = = = = = = = = = = = = = = = = = >Best regards. > >MaFai >[EMAIL PROTECTED] >2004-07-02 > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Purge master logs
SHOW MASTER STATUS will show you where you currently are on the master. You should connect to each slave, and do a SHOW SLAVE STATUS to find where the slaves are, and delete the logs lower than the lowest one in use by the slaves. Luis Mediero wrote: Hi, I'm looking for an automatic method to purge the master logs. I know I can do it with "PURGE MASTER LOGS TO 'mysql-bin.010';", but I need know the last log file to automate it. Somebody know the way. TIA Luis Mediero -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange server crashes with large table and myisamchk
It is telling you that your hard drive is failing.
Replace it.
Hanno Fietz wrote:
Hello everybody,
I'm experiencing problems with a 4.0.15 MySQL-Server running on a SuSE
Linux 8.2 box with 512 MB RAM, some one-point-something GHz CPU and 40
GB IDE Harddisk.
We have a database with some administrative tables and one large data
table (now ~ 30 M rows, ~ 1GB index file and ~ 800 MB data file) that
we insert new rows into on a per-minute basis. Read / Write ratio
probably is around 1 : 2 or 1 : 3. To achieve good performance despite
the size of the table, we run "myisamchk -r" and "myisamchk -R 1"
every night as a part of the backup routine. The server is taken down
for that purpose.
For the last two weeks now, we are getting these syslog messages when
running the optimization:
Jul 2 03:10:28 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:28 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316864
Jul 2 03:10:28 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316864
Jul 2 03:10:28 t56 kernel: klogd 1.4.1, -- state change
--
Jul 2 03:10:30 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:30 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316872
Jul 2 03:10:30 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316872
Jul 2 03:10:32 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:32 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316880
Jul 2 03:10:32 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316880
Jul 2 03:10:33 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:33 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316888
Jul 2 03:10:33 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316888
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316896
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316896
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316904
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316904
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=429367, sector=316912
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 316912
Jul 2 03:12:17 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:12:17 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=159072, sector=46592
Jul 2 03:12:17 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 46592
Jul 2 03:12:19 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:12:19 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=159072, sector=46600
Jul 2 03:12:19 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 46600
Jul 2 03:13:14 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:13:14 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=285328, sector=172864
Jul 2 03:13:14 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 172864
Jul 2 03:13:16 t56 kernel: hda: dma_intr: status=0x51 {
DriveReadySeekComplete Error }
Jul 2 03:13:16 t56 kernel: hda: dma_intr: error=0x40 {
UncorrectableError }, LBAsect=285328, sector=172872
Jul 2 03:13:16 t56 kernel: end_request: I/O error, dev 03:02 (hda),
sector 172872
Occasionally (not always!!), the MySQL-Server won't some up again
after optimization, sometimes myisamchk even leaves the table
corrupted and has to be run again. To make it even more confusing:
sometimes I get server crashes during shutdown, due to signal 11
(SEGV). I included a resolved stack dump below:
0x8071f64 handle_segfault + 420
0x82916c8 pthread_sighandler + 184
0x8188a9f btr_search_drop_page_hash_index + 5359
0x8188e1a btr_search_drop_page_hash_when_freed + 138
0x81dbbea fseg_free_extent + 746
0x81dc7fa fseg_free_step + 2458
0x815c3ba btr_free_but_not_root + 122
0x8100efe dict_drop_index_tree + 94
0x814969a row_upd_clust_step + 538
0x81499fa row_upd + 106
0x8149c62 row_upd_step + 322
0x811c7be que_run_threads + 334
0x8136132 row_drop_table_for_mysql + 2114
0x80cf4ce delete_table__11ha_innobasePCc + 270
0x80c5c8c ha_delete_table__F7db_typePCc + 60
0x80d3bf1 mysql_rm_table_part2__FP3THDP13st_table_listbT2 + 497
0x80d38c1 mysql_rm_table__FP3THDP13st_table_listc + 177
0x807e6f1 mysql_execute_command
Re: Unexpected behaviour: SELECT with OR returns empty set
I am not sure what you are trying to do. Do you want a join, or a union. Your query is neither, and the results are correct as written. Dave Gibson wrote: Hi, I'm getting some unexpected behaviour from a query and was hoping someone could shed some light on whether it's a user error or something else. I have two tables: customers, oems which both have a companyID field (marked as a foreign key). When I do: SELECT * FROM customers WHERE customers.companyID = 1509 I get 4 rows returned. However, when I do: SELECT * FROM customers,oems WHERE ((customers.companyID = 1509 ) OR (oems.companyID) = 1509) I get 0 rows matched. Investigation showed that the oems table contains no rows. If I add a rown to it, even one that doesn't match the query above, I get the correct result. ring any bells with anyone? Thanks, Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unexpected behaviour: SELECT with OR returns empty set
Hi, I'm getting some unexpected behaviour from a query and was hoping someone could shed some light on whether it's a user error or something else. I have two tables: customers, oems which both have a companyID field (marked as a foreign key). When I do: SELECT * FROM customers WHERE customers.companyID = 1509 I get 4 rows returned. However, when I do: SELECT * FROM customers,oems WHERE ((customers.companyID = 1509 ) OR (oems.companyID) = 1509) I get 0 rows matched. Investigation showed that the oems table contains no rows. If I add a rown to it, even one that doesn't match the query above, I get the correct result. ring any bells with anyone? Thanks, Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange server crashes with large table and myisamchk
Hello everybody,
I'm experiencing problems with a 4.0.15 MySQL-Server running on a SuSE
Linux 8.2 box with 512 MB RAM, some one-point-something GHz CPU and 40
GB IDE Harddisk.
We have a database with some administrative tables and one large data
table (now ~ 30 M rows, ~ 1GB index file and ~ 800 MB data file) that we
insert new rows into on a per-minute basis. Read / Write ratio probably
is around 1 : 2 or 1 : 3. To achieve good performance despite the size
of the table, we run "myisamchk -r" and "myisamchk -R 1" every night as
a part of the backup routine. The server is taken down for that purpose.
For the last two weeks now, we are getting these syslog messages when
running the optimization:
Jul 2 03:10:28 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:28 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316864
Jul 2 03:10:28 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316864
Jul 2 03:10:28 t56 kernel: klogd 1.4.1, -- state change --
Jul 2 03:10:30 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:30 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316872
Jul 2 03:10:30 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316872
Jul 2 03:10:32 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:32 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316880
Jul 2 03:10:32 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316880
Jul 2 03:10:33 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:33 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316888
Jul 2 03:10:33 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316888
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316896
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316896
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316904
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316904
Jul 2 03:10:39 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:10:39 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316912
Jul 2 03:10:39 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316912
Jul 2 03:12:17 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:12:17 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=159072, sector=46592
Jul 2 03:12:17 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 46592
Jul 2 03:12:19 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:12:19 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=159072, sector=46600
Jul 2 03:12:19 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 46600
Jul 2 03:13:14 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:13:14 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=285328, sector=172864
Jul 2 03:13:14 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 172864
Jul 2 03:13:16 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete Error }
Jul 2 03:13:16 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=285328, sector=172872
Jul 2 03:13:16 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 172872
Occasionally (not always!!), the MySQL-Server won't some up again after
optimization, sometimes myisamchk even leaves the table corrupted and
has to be run again. To make it even more confusing: sometimes I get
server crashes during shutdown, due to signal 11 (SEGV). I included a
resolved stack dump below:
0x8071f64 handle_segfault + 420
0x82916c8 pthread_sighandler + 184
0x8188a9f btr_search_drop_page_hash_index + 5359
0x8188e1a btr_search_drop_page_hash_when_freed + 138
0x81dbbea fseg_free_extent + 746
0x81dc7fa fseg_free_step + 2458
0x815c3ba btr_free_but_not_root + 122
0x8100efe dict_drop_index_tree + 94
0x814969a row_upd_clust_step + 538
0x81499fa row_upd + 106
0x8149c62 row_upd_step + 322
0x811c7be que_run_threads + 334
0x8136132 row_drop_table_for_mysql + 2114
0x80cf4ce delete_table__11ha_innobasePCc + 270
0x80c5c8c ha_delete_table__F7db_typePCc + 60
0x80d3bf1 mysql_rm_table_part2__FP3THDP13st_table_listbT2 + 497
0x80d38c1 mysql_rm_table__FP3THDP13st_table_listc + 177
0x807e6f1 mysql_execute_command__Fv + 8561
0x8080565 mysql_parse__FP3THDPcUi + 149
0x807bac3 dispatch_command__F19enum_server_commandP3THD
4.1.3 and time zones
Hi all- I just downloaded and installed 4.1.3 on a WinXP environment. Does anyone happen to have the data that should be loaded into all the new time zone catalog tables? And the doc that describes the convert_tz() function and a little more on general use of the time zone capability? My time zone tables are empty - I don't see any scripts or SQL to load them. Thanks. R
Purge master logs
Hi, I'm looking for an automatic method to purge the master logs. I know I can do it with "PURGE MASTER LOGS TO 'mysql-bin.010';", but I need know the last log file to automate it. Somebody know the way. TIA Luis Mediero -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
Javier Diaz <[EMAIL PROTECTED]> wrote on 02/07/2004 10:28:32: > We have some tables to record different data of the activity in our website. > The number of INSERT, DELETE and UPDATE operations in these tables is huge > (it could be more than 3000 a second). So far we don't have any speed > problems, all these tables are optimised using the right indexes and > everything is working ok. But now we are writing a Report Tool to get some > stats and figures from these tables. We have been doing a few tests and any > SELECT query taking more than one second or a few simultaneous SELECT, and > we have a real mess, lots of LOCKS. We definitely can not afford to slow > down the web site, and we have been thinking in a few possible solutions > > 1- Create a duplicate once a day of each of the tables we need to connect > from the Report Tool. We can do this in the period of less activity in the > site. > This a safest solution because we will be running all the SELECT's > against the duplicates and there are no risks to cause problems in the site > but we will > have the inconvenience that we can not get latest figures only the > previous days. Sounds like a nasty kludge to me. I really wouldn't be keen on it at all. > > 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good > enough It strikes me that this is what InnoDB is designed for. In your situation, this is the first thing I would try. > 3- Replication of these tables to another server ?? This would also work: it just uses more resources (another computer of nearly equal power) and more development time (scripts to stop and start replication. If you can borrow the replicated machine, you could test the InnoDB solution on that: change the tables on the replicated server to InnoDB and try running your report generator on that. If the replications doesn't fall behind, the main server will probably handle it. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Replication Performance
Dear ALL: Some time the slave would crushed by I/O error.It happen at the server with the lower CPU.( per 45 days ) All of the slave database has block the binary log,it reduce the CPU loading. The database would replicate text,int,varchar but no blob. Network connection hasn't drop frequently,it seems hard to avoid that. Do any way make the slave query more time but not just idle and wait for the data? Since we can't bear the slave need 10 minutes to synchronize the data with master. Here the master status Uptime: 2592393 Threads: 24 Questions: 214644229 Slow queries: 197 Opens: 580 Flush tables: 1 Open tables: 254 Queries per second avg: 82.798 At 2004-07-01, 22:06:24 you wrote: What does the network setup look like? What type of data is being replicated varchar, text, blob? Is the network connection being dropped or are there any errors being logged anywhere? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 6/30/04 10:34 PM Subject: Replication Performance Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01 = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-02
Concurrency Question
Hi everyone We have some tables to record different data of the activity in our website. The number of INSERT, DELETE and UPDATE operations in these tables is huge (it could be more than 3000 a second). So far we don't have any speed problems, all these tables are optimised using the right indexes and everything is working ok. But now we are writing a Report Tool to get some stats and figures from these tables. We have been doing a few tests and any SELECT query taking more than one second or a few simultaneous SELECT, and we have a real mess, lots of LOCKS. We definitely can not afford to slow down the web site, and we have been thinking in a few possible solutions 1- Create a duplicate once a day of each of the tables we need to connect from the Report Tool. We can do this in the period of less activity in the site. This a safest solution because we will be running all the SELECT's against the duplicates and there are no risks to cause problems in the site but we will have the inconvenience that we can not get latest figures only the previous days. 2- Use InnoDB instead of MyISAM, but we are not sure is this will be good enough 3- Replication of these tables to another server ?? We would appreciate a lot any ideas Thanks a lot Javier This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: making lower case then first char to upper case?
On Wed, 30 Jun 2004, Aaron Wolski wrote: > Hi Guys, > > I'm trying to figure out of this is possible. I know I could do it in > PHP but I am dealing with a ton of records and would rather put the > processing on the DB than PHP/client side. > > Question is. can I do a SELECT query on a column that changes all the > results to lower case and THEN changes the first character of each > result to an upper case? > > Example: > > Currently in DB: AARON > to Lowercase: aaron > to Uppercase: Aaron > > > Any idea on if I can do this and how I might approach it? what about: SELECT CONCAT( UPPER( LEFT(field,1) ), LOWER( SUBSTRING(field,2) ) ) AS Something FROM ... Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: referential integrity for MyIsam
From: "starofframe" <[EMAIL PROTECTED]> > CanIt Vote for ID 62298InnoDB still has the major issue about the slow access of database... > I use mysql database for website.. For a new application we're building we performed some tests to decide upon MyISAM/InnoDB and two table lay-outs. We ran two processing which constantly did inserts, deletes and updates and two processes which performed various queries. We tried to run the test until we reached 500,000 records and wanted to find out the speed of the various queries versus the number of records in the database. We started most test in the afternoon and had to terminate the MyISAM tests in the following morning with only approx. 50.000 records in the database. The InnoDB versions reached the 500,000 limit without a problem. The results showed that InnoDB is slower than MyISAM for very small record sets (which we knew, but we had to know how much slower/faster with our servers) or when the read/write ratio is either very high or very low. For our situation with high concurrency (equal amounts of reads/writes) and a reasonably big recordset InnoDB won hands down... > From: Daniel Kasak > starofframe wrote: > >I've read that MyIsam type table doesnt have the "referential integrity" function... Correct. > >I hav tried to find other 3rd party s/w that can solve the issue. The program / script that uses MySQL data can manage the integrity issues. > >Finally I read from PhpMyAdmin documentation that "recently PhpMyAdmin can check referential Integrity" > >but I still dont know how to do it after reading the documentation for some times I gues they mean that PhpMyAdmin supports referential integrity for InnoDB tables? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
File name of Binary Log
Hi, I have enabled the binary log on MySQL (4.0.20). The name of the binary log is hostname-bin.001, hostname-bin.002, etc ... What will happen if the suffix become 999? Will error occurs when flush logs is executed? Regards, Michael --- KMB E-mail Disclaimer This e-mail may contain confidential, proprietary or legally privileged information and is intended for the attention and use of the addressee(s) only. If you are not the intended recipient of this message, you must not copy, use or disclose any part of its contents. Please notify the sender immediately and delete this message from your system. The KMB Group and each of its affiliates and the sender of this message shall not be responsible or liable for any errors or omissions in the contents of this message as secure or error free e-mail transmission cannot be guaranteed. Information sent via e-mail could arrive late or contain viruses or be intercepted, corrupted, lost, destroyed, or incomplete. Unless otherwise stated, any information given in this message is indicative only and is subject to our formal written confirmation.
Re: referential integrity for MyIsam
CanIt Vote for ID 62298InnoDB still has the major issue about the slow access of database... I use mysql database for website.. - Original Message - From: Daniel Kasak To: starofframe ; [EMAIL PROTECTED] Sent: Friday, July 02, 2004 11:08 AM Subject: Re: referential integrity for MyIsam starofframe wrote: >Hi All, > >I've read that MyIsam type table doesnt have the "referential integrity" function... > >I hav tried to find other 3rd party s/w that can solve the issue. > >Finally I read from PhpMyAdmin documentation that "recently PhpMyAdmin can check referential Integrity" > >but I still dont know how to do it after reading the documentation for some times > >So anyone ever face such issue?? > >Thanx >Flame > > Don't like InnoDB? -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- -- The following links have been inserted by the NUS Consulting internal spam filter and are for NUS Consulting staff only Spam Not spam Forget previous vote
