Re: Optimizing and more connections
On Thu, Jan 24, 2002 at 02:07:14PM -0600, Jay Paulson wrote: I'm trying to increase the maxium number of connections to my MySQL database but I am not sure how to do this. I've gone to the manual and it doesn't say too much (maybe i'm looking in the wrong spot?). The machine I'm running is an AMD 650 with 512 Ram on RedHat 7.1 and MySQL 3.23.41 so I found the following line in the manual: safe_mysqld -O key_buffer=64M -O table_cache=256 \ -O sort_buffer=4M -O record_buffer=1M However, it seems that I need to shut down the mysql db before I can run this. This still doesn't solve the problem of the max number of connections. I don't have a my.cnf file to change on the server so the only thing I can think of is to tack on -O max_connections=500 at the end of the line above. Is this correct? Yes, that ought to work. What do I need to do to maximize the preformance on my machine? Well, that's whole different question! What sort of bottlenecks are you seeing? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,010,325 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication for BDB tables
Hi, With all the recent talk about replication, just wondering if there are any others out there who have experience replicating BDB tables? My experience so far has been that the slave instance of MySQL has been crashing once in a while, when applying perfectly legitimate DELETE queries on the replicated BDB tables. MySQL then restarts after the crash, but not without spewing out various BDB Log Sequence errors etc in the log when trying to recover. On restarting, the same query causes an error on the affected table again, and the only way to get things going again is to convert BDB - MyISAM - BDB, then SLAVE START. This of course does not bode well on a production system where the slave acts as a hot backup just in case :) Just sharing my experience so far :) Anyone else got anything to share? Some other questions : 1. Does anyone know the behaviour of BDB recovery for such cases i.e. table crashes, log sequence errors etc? 2. What happens to BDB tables on shutdown/startup i.e. is the transaction log flushed etc? Any comments welcome! Thanks. Cheers, Geoffrey __ Geoffrey Soh, Software Architect Ufinity - http://www.ufinity.com Leading Enterprise Access Management Software! 9 Scotts Road, Pacific Plaza, #06-01, Singapore 228210 Tel : +65 830-0341 Fax : +65 737-0213 __ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innoDB confusion
On Tue, Jan 15, 2002 at 08:36:16PM +0200, Heikki Tuuri wrote: This brings up an interesting point. If you've lost your .frm files, are you totally screwed, or does InnoDB contain enough information to restore those .frm files? It's not a big deal if With some work, yes. innodb_table_monitor prints the internal schema of InnoDB. There are fewer column types inside InnoDB than in MySQL. For example, a DATE column will appear as an integer. Heikki, Is the list of column types that InnoDB suppots (as well as how they are mapped to MyISAM types) documented anywhere? I didn't see it in the InnoDB manual? I'd like to put it in the InnoDB section of my book if I can get the right information. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,398,369 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to check disk space used for a mysql account
On Wed, Jan 23, 2002 at 06:15:38PM -0500, Alex Shi wrote: How to check disk space been used for all of the tables of a specific mysql account? Depends what you mean. MySQL accounts don't necessarily map to tables or databases, so how can you properly account for shared data? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,492,730 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table overheading
hello anyone know why ooverhead happen? some of my table are overheading where a run delete and updates queries. --- Everton B Yoshitani [[EMAIL PROTECTED]] UF Communications, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Help needed !!!
hi, Yes, I was really thinking about the InnoDB Tables, Thank you very much for such a systematic and accurate reply, Thanks and regards, Chetan -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 1:10 PM To: Chetan Lavti Cc: [EMAIL PROTECTED] Subject: Re: Help needed !!! On Fri, Jan 25, 2002 at 01:05:12PM +0530, Chetan Lavti wrote: I am going to use the MySQL version 3.23.47 for as our database. The issue is which table type to use. I want that the database should be (memory-resident). I have tried with the MyISAM tables which doesn't solves my purpose as in this case the data are stored in the files. Right. I have also tried with the HEAP tables that uses a hashed index and are stored in memory. This makes them very fast, but if MySQL crashes I am loosing all data stored in that. As, I have created one table as a heap type but when I make my server down and start it again all rows created nowhere exists. Correct. It sounds like you might want to look at InnoDB tables. They are disk based, but the InnoDB table handler can use a significant amount of RAM (if you allow it to) to cache index *and* record data. MyISAM only caches index data in memory. The InnoDB buffer pool is where this cached data is stored. So, if anybody can suggest me any process by which I can populated HEAP table(es) from a duplicate table(es) (which is on secondary storage) at the time of startup. Also, any runtime modifications in the tables will need to be updated in both the tables (one in memory and another one on disk). If any other method by which I can achieve the same. There was talk last year of implementing a hybrid HEAP/MyISAM table type (originally motived by the DBA at Slashdot). Upon startup, the MyISAM table would be loaded into a RAM-based HEAP-table. Any changes to the HEAP table would get written to the underlying MyISAM table as well. Read-only queries, of course, would be run against the RAM-based HEAP table. I suspect that will never be implemented, since InnoDB and it's buffer pool go a long way toward solving the same problem. Hope that helps, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 513,100,530 queries (265/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: perl DBD::mysql question
On Thu, Jan 24, 2002 at 01:48:58PM +0100, Paul van den Berg wrote: Hello list, the regular mysql-client returns some useful information after batch-inserts like: Query OK, 11393 rows affected (0.47 sec) Records: 11393 Duplicates: 0 Warnings: 0 I can fetch the number of effected rows in perl/DBI with $sth-rows, but is it possible to fetch the count of duplicates and warnings, or even the time that the query took to process? The timing is done by the client (I'm pretty sure), so you might want to use Perl's Time::HiRes module to figure out how much time has elapsed. I also recall hearing that there are plan on the drawing board for making it easier to programmatically access warnings and errors after batch inserts. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,527,539 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--log-bin fails: Server id is not set - binary logging disabled
I'm trying to enable binary logging on my MySQL server (3.23.28-gamma-log), per the recommendations in the manual about properly backing up the database. In my /etc/my.cnf I have: [mysqld] log-bin=updates I had also tried just: [mysqld] log-bin But no update log file is being created in my data dir. Looking at the .err log reveals: 020125 03:16:38 mysqld started 020125 3:16:38 Server id is not set - binary logging disabled What does this refer to, and how can I fix it? Thanks, Ken - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication for BDB tables
On Fri, Jan 25, 2002 at 04:15:18PM +0800, Geoffrey Soh wrote: Hi, My experience so far has been that the slave instance of MySQL has been crashing once in a while, when applying perfectly legitimate DELETE queries on the replicated BDB tables. That's a bad sign. MySQL then restarts after the crash, but not without spewing out various BDB Log Sequence errors etc in the log when trying to recover. On restarting, the same query causes an error on the affected table again, and the only way to get things going again is to convert BDB - MyISAM - BDB, then SLAVE START. This of course does not bode well on a production system where the slave acts as a hot backup just in case :) Next time it happens, make a copy of the bad table and the query that caused the problem. Send it to the MySQL folks so they can track down (and fix) the problem. This shouldn't be happening at all. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,708,248 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table overheading
On Fri, Jan 25, 2002 at 05:23:23PM +0900, Everton B Yoshitani wrote: hello anyone know why ooverhead happen? some of my table are overheading where a run delete and updates queries. I'm not sure what you mean. Can you provide an example? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,814,881 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inner Join Delete
On Wed, Jan 23, 2002 at 11:25:31PM -0500, Jason Yates wrote: Heres an example scenario, say I have two tables table1 -id | name | | table2 |_ id address zip I inner join table1 and table2 on id. I want to delete all the records in table1 which have a zip of '90210'. Good choice. 90210 is first to go on my list, too. :-) I could create a script, run a select and loop through each id and delete the records in table1. If you're running MySQL 4.x, multi-table deletes: http://www.mysql.com/doc/D/E/DELETE.html may be what you need, if I understand you right. Otherwise, you've gotta write that loop. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,844,619 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table overheading
my usr_dat(user data) table overhead! this table are updated constant every time that a user request a web page on my site the php script update the lastseen field in this table so when i access the phpMyAdmin interface to manage the database the phpMyAdmin show some overhead bytes in this table i run the optimize command on this and after a time the overhead bytes back what can cause this? anyone have idea? * sorry my bad english - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Everton B Yoshitani [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 5:35 PM Subject: Re: table overheading On Fri, Jan 25, 2002 at 05:23:23PM +0900, Everton B Yoshitani wrote: hello anyone know why ooverhead happen? some of my table are overheading where a run delete and updates queries. I'm not sure what you mean. Can you provide an example? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,814,881 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --log-bin fails: Server id is not set - binary logging disabled
On Fri, Jan 25, 2002 at 03:30:11AM -0500, Ken wrote: I'm trying to enable binary logging on my MySQL server (3.23.28-gamma-log), per the recommendations in the manual about properly backing up the database. In my /etc/my.cnf I have: [mysqld] log-bin=updates I had also tried just: [mysqld] log-bin But no update log file is being created in my data dir. Looking at the .err log reveals: 020125 03:16:38 mysqld started 020125 3:16:38 Server id is not set - binary logging disabled What does this refer to, and how can I fix it? Drop this into your my.cnf file: server-id = 1 And consider upgrading. 3.23.28 has some bugs that are fixed in the stable releases. It will be painless upgrade. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 515,230,929 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld freebsd
Jeremy Zawodny wrote: On Thu, Jan 24, 2002 at 12:19:12PM +0200, Oleg Prokopyev wrote: Ken Menzel wrote: Hi Oleg, There is some sort of thread problem with freebsd but it usuually is not that bad. How did you compile MySQL? I would recommend using the ports version of Mysql (cd /usr/ports/databases/mysql-3.23-server) . Look at the makefile if you are still havbing threads problems you can do you mean MIT-threads options ? :( it does not want to compile No, he means the LinuxThreads option. hi i solved the problem i took sources from mysql.com and in file INSTALL-SOURCE i found the following : ...If you are using a recent version of *gcc*, recent enough to understand `-fno-exceptions' option, it is *VERY IMPORTANT* that you use it. Otherwise, you may compile a binary that crashes randomly. We also recommend that you use `-felide-contructors' and `-fno-rtti' along with `-fno-exceptions'. When in doubt, do the following: CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql --enable-assembler --with-mysqld-ldflags=-all-static On most systems this will give you a fast and stable binary. it helps :) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 21 days, processed 507,895,181 queries (267/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Regards, Oleg Prokopyev OAP4-RIPE - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table overheading
my usr_dat(user data) table overhead! this table are updated constant every time that a user request a web page on my site the php script update the lastseen field in this table so when i access the phpMyAdmin interface to manage the database the phpMyAdmin show some overhead bytes in this table i run the optimize command on this and after a time the overhead bytes back what can cause this? anyone have idea? * sorry my bad english - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Everton B Yoshitani [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, January 25, 2002 5:35 PM Subject: Re: table overheading On Fri, Jan 25, 2002 at 05:23:23PM +0900, Everton B Yoshitani wrote: hello anyone know why ooverhead happen? some of my table are overheading where a run delete and updates queries. I'm not sure what you mean. Can you provide an example? -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,814,881 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC Driver for MySQL3.23.47-1
Rahadul, Thursday, January 24, 2002, 8:45:31 PM, you wrote: RK hi, RK I recently installed MySQL3.23.47 and now I need the JDBC drivers for RK it. Does anyone know where can I find a Free JDBC driver that would work RK with MySQL 3.23.47-1? RK thanks so much You can find JDBC drivers, look at: http://www.mysql.com/downloads/api-jdbc.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql very slow --why???
Do I need to have a clean install on my machine. Right now, I've installed the mysql server on one machine(Windows 2000) and the developer is trying to access it using the VB application from his machine(client)which is a windows 98 pc using the mysql driver. Thanks alot - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
password column length
Rutledge, Friday, January 25, 2002, 12:19:58 AM, you wrote: Reoen A quick question. When creating a table that will hold values generated Reoen by the password() function, what kind of column should this be stored Reoen in...I am assuming VARCHAR(?). Could someone kindly tell me the length? Reoen I've skimmed the whole manual and I can't find any reference to this. Reoen Thank you! Aaron As far as I can see, the definition for table user tells us that password column type is char(16). -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: Bad count(*) perfermance on index.
Hello Jeremy, Friday, January 25, 2002, 11:00:43 AM, you wrote: Why query type select count(*) from table_name where key_field between 'min' and 'max' so slow? example: mysql select count(*) from Textes where ID between 7937 and 45061; +--+ | count(*) | +--+ |36360 | +--+ 1 row in set (0.42 sec) JZ How large is your key_buffer? key_buffer_size 8388600 JZ How many rows are in the table? not so many. 36362 rows. but big rows. table size 255Mb. mysql explain select count(*) from Textes where ID between 7937 and 45061; ++---+---+-+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---+---+-+-+--+---+-+ | Textes | range | PRIMARY | PRIMARY | 4 | NULL | 36361 | where used; Using index | ++---+---+-+-+--+---+-+ 1 row in set (0.00 sec) why explain much faster? JZ Because it doesn't actually run the query. why rows from expalain not exactly match count(*)? JZ Because it's just an esitmate which helps MySQL decide the fastest way JZ to execute the query. It's clean. But why index structure don't allow count exactly how many rows between keys? Will It's be fixed in 4.0? JZ Hard to say. We don't know what the problem is yet. AFAIK, in 4.0 index structure will be changed - for allow 'order by ... desc' optimization. Will new structre allow fast count exactly how many rows between keys? it very common query: select count(*) from Textes where path like '/os/%'; select count(*) from Textes where SectID=6; and many other. JZ Can you run ANALYZE TABLE Textes and see if that helps? of course: mysql ANALYZE TABLE Textes; +---+-+--+--+ | Table | Op | Msg_type | Msg_text | +---+-+--+--+ | NewWeb.Textes | analyze | status | OK | +---+-+--+--+ 1 row in set (0.93 sec) JZ Jeremy -- Best regards, Artemmailto:[EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help! Hanging mysql
I have problems with uptime on my Internet service with approx 1 million page-views per month(most of which involves several mysql queries), apparently because of something with MySQL. Involved products are: mysqld Ver 3.22.32 for pc-linux-gnu on i686 apache-perl 1.3.9-13.1- (Apache with mod_perl statically linked) perl version 5.005_03 built for i386-linux (I use the Debian linux distribution, and Debian is very strict about defining versions as stable enough to be recommended on production servers) The Problem: At random times, average 1 time per day, not necessarily during peek hours, the mysql server seems to stop responding to requests from the mod_perl/dbi scripts. More and more apache processes are started until the apache server reaches the MaxClients level which I have set to 80. mysqladmin processlist still works, and shows some 80 threads in status Sleep (so this is apparently not a locking problem). Also mysql sessions that does not come from mod_perl works. Once I have stopped and started the mysql server, everything works fine again, until next time it happens. When everthing is okay, the number of mysql threads in sleep is going up and down around 20. Any ideas? Rune Sandbakken Solfaktor.com Gran Canaria - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql on windows performing very slow
Hi,
If I look under task manager- Processes, mysqld-max-nt.e uses up 99%. For
simple queries, it seems to be fast, but for long and complex query, it
just hangs.
An example of aa query we're trying to run:
SELECT ADV.AMB_No AS ColCode, CD.Company_Name AS ColDesc, ADV.Account_Item_ID AS
RowCode, AI.Account_Item AS RowDesc,
ADV.Value AS Amount, ER.Exchange_Rate AS DefRate, UER.Exchange_Rate AS UseRate ,
AI.Is_Ratio, AI.Ratio_Calculation,
ADV.Year_End, TIL.Line_No, TIL.Hierarchy_Level, TIL.SubTotal_ID, AI.Negative_Polarity
FROM (Account_Data_Values AS ADV INNER JOIN Account_Items AS AI ON
ADV.Account_Item_ID = AI.Account_Item_ID)
INNER JOIN Company_Year_Ends AS CYE ON (ADV.Year_End = CYE.Year_End) AND (ADV.AMB_No =
CYE.AMB_No))
INNER JOIN Company_Details CD ON ADV.AMB_No = CD.AMB_No)
INNER JOIN Template_Item_Link TIL ON TIL.Account_Item_ID = ADV.Account_Item_ID)
LEFT JOIN EXCHANGE_RATES AS ER ON (CYE.YEAR_END = ER.DATE_REPORTED) AND
(CYE.REPORTED_CURRENCY = ER.COUNTRY_CODE))
LEFT JOIN USER_EXCHANGE_RATES UER ON UER.Country_Code = ER.Country_Code AND
UER.Date_Reported = ER.Date_Reported AND
UER.User_ID = 'code for test'
WHERE ADV.AMB_No In ('87200', '87201', '85646', '87202', '87204', '86478', '86248',
'86292', '87242', '87209',
'72216', '72178', '85333', '87246', '87247', '87862', '87210', '87211', '87212',
'85105', '85950', '87216', '87217',
'87682', '87220', '87221', '85546', '87222', '86158', '87223', '87679', '85547',
'84036', '87225', '86479', '85814',
'86505', '87250', '85550', '86164', '85203', '86162', '87490', '87530', '86163',
'87249', '87859', '87256', '87226',
'84153', '86285', '87227', '87963', '87863', '84204', '87228', '87229', '87230',
'84105', '87960', '87232', '87964',
'87275', '86954', '87800', '87253', '87254', '84158', '87243', '85491', '85657',
'84037', '86166', '87257', '87258',
'87259', '84114', '87930', '87260', '85572', '85564', '87261', '87262', '85548',
'87449', '87264', '87266', '87267',
'87933', '87269', '85266', '85909', '87357', '86524', '86250', '87290', '87270',
'87271', '85504', '87272', '87408',
'87273', '87274', '87277', '87278', '84120', '87279', '84254', '87280', '85860',
'87876', '87281', '87282', '85249',
'87283', '87284', '87801', '87285', '87693', '84203', '87873', '87292', '87293',
'87295', '87932', '87296', '87298',
'85595', '87860', '86389', '87872', '86373', '87300', '87301', '87378', '87303',
'87304', '87308', '87309', '87310',
'87311', '87312', '87313', '87314', '87966', '87316', '87317', '87319', '87318',
'87320', '87321', '87322', '85397',
'85813', '87325', '85561', '86286', '85083', '87327', '84205', '85511', '87332',
'85508', '87333', '87334', '86181',
'86463', '84119', '87336', '87337', '87338', '87339', '87340', '87341', '85602',
'87343', '86628', '87345', '87346',
'85832', '85812', '87348', '86695', '87349', '86513', '87350', '87351', '87352',
'87233', '84118', '87353', '85935',
'87436', '85650', '87442', '85574', '87354', '87355', '87356', '86653', '86655',
'87603', '84206', '86063', '86852',
'84195', '87931', '86629', '86483', '86596', '86597', '87363', '87364', '87365',
'87358', '87366', '86160', '87371',
'85368', '84143', '87434', '85211', '84207', '85649', '86507', '85207', '85209',
'87376', '87377', '87387', '86142',
'86143', '85551', '84162', '87381', '87382', '87861', '87383', '87385', '85084',
'87302', '87934', '84154', '87935',
'86912', '87976', '87265', '87391', '87392', '86999', '87461', '87397', '87396',
'84299', '87399', '87400', '87401',
'85515', '86129', '87558', '87405', '86485', '86486', '87949', '84163', '87952',
'86487', '87410', '87411', '85353',
'87412', '87413', '87414', '87415', '87416', '87422', '84279', '85941', '87423',
'87425', '84113', '87950', '86522',
'85357', '87429', '87431', '87958', '84297', '87864', '87516', '87642', '87234',
'85643', '85644', '84107', '87437',
'85106', '87438', '85362', '87439', '87440', '85354', '87444', '87445', '87446',
'87447', '87450', '87451', '87918',
'87948', '86301', '87452', '87596', '87454', '86291', '87456', '87457', '87458',
'87460', '87459', '85365', '86488',
'87463', '87465', '87494', '87466', '87467', '87468', '87947', '87471', '86284',
'87476', '85012', '85262', '85315',
'87478', '87479', '87480', '87481', '84156', '85086', '87475', '87482', '84196',
'87483', '87709', '87706', '87486',
'87956', '87954', '87488', '87489','87959', '86252', '87491', '87492', '85250',
'86137', '85265', '87498', '87499',
'84152', '85323', '87501', '87503', '85026', '85514', '87506', '87507', '87597',
'85367', '87510', '87951', '87512',
'87514', '85030', '85188', '85103', '85837', '86247', '87515', '85946', '87517',
'87519', '87520', '87965', '86154',
'87522', '87523', '87802', '87524', '87526', '84194', '87529', '87531', '87533',
'87957', '87538', '86307', '87539',
'87541', '87542', '87543', '85641', '85642', '87803', '87545', '87544', '87546',
'87578', '86126', '85516', '87548',
'87955', '87552', '87551', '87557', '86674', '84106',
Help with mySQL Connection string
Hey folks - Here is hoping someone can help once I get it down I promise I'll post all my work on this list so some other newbie can benefit. I'm simply trying to run a very very straightforward JDBC test with mySQL I keep getting the Server configuration denies access to data source error. But before you tell me that I must simply look at what my mysql.db mysql.user table allows - I'VE TRIED THAT. I realize that the error is probably there somewhere - but I can't see it. I've tried connecting with 2 different accounts - the provided root a user I created deep to 2 different databases the provided test testdb which I created) . Here is what mysql says about the two different databases mysql select * from db; Host | Db | User |... -++--- | % | test| | .. | % | test\_% | | | localhost | testdb | deep | Note that root should have access to test deep@localhost should have access to testdb right? So here are all the connection strings I have tried - all return the same error below Connection conn = DriverManager.getConnection( jdbc:mysql://localhost:3306/test, root, mypass //jdbc:mysql://localhost:3306/testdb, deep, mypass //jdbc:mysql://localhost/testdb?user=deeppassword=mypass //jdbc:mysql://localhost/testdb, deep, mypass // jdbc:mysql://localhost:3306/testdb?user=deep@localhostpassword=mypass //jdbc:mysql://localhost:3306/testdb?user=deeppassword=mypass //jdbc:mysql://localhost/testdb, deep@localhost, mypass //jdbc:mysql:///test, root, mypass //jdbc:mysql://localhost/test, root@localhost, mypass ); My Error: Exception in thread main java.sql.SQLException: Server configuration denies access to data source at org.gjt.mm.mysql.MysqlIO.init(MysqlIO.java:193) at org.gjt.mm.mysql.Connection.connectionInit(Connection.java:261) at org.gjt.mm.mysql.jdbc2.Connection.connectionInit(Connection.java:89) at org.gjt.mm.mysql.Driver.connect(Driver.java:167) at java.sql.DriverManager.getConnection(DriverManager.java:517) at java.sql.DriverManager.getConnection(DriverManager.java:199) at TestMySQL4.main(TestMySQL4.java:76) BTW: I am using MacOS 10.1 MySQL 3.23.42 The driver is: mm.mysql-2.0.6.jar Please help! 'deep -- Amandeep Jawa Worker Bee Software -- [EMAIL PROTECTED] 225A Dolores St. San Francisco, CA 94103-2202 Home: 415 255 6257 (ALL MALP) professional: http://www.worker-bee.com personal: http://www.deeptrouble.com political: http://www.sflcv.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select like 'alpha%' : fail to report alpha, charset=gbk, large db
Same pb with version 3.23.47 Description: Following is the test reproduced in several databases (tables of around 7 millions records). Mysql compiled with GBK charset. A select used with a like 'alpha%' condition fails to report exact matches. Even if the select is completed by an OR field = 'alpha', it does not work. This is shown afterwards on latin characters, but the problem is exactly the same on Chinese characters. Script started on Wed Jan 9 11:53:26 2002 [root@www bin]# mysql cyber Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4193 to server version: 3.23.37-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql describe addrs; +-++-+++--- | Field| Type| Null | Key | Default | Extra | +-++-+++--- | addrid | int(11) | | PRI | NULL| auto_increment | | sex | char(1) | | | || | cname| varchar(20) | | MUL | || | pcd | varchar(6) | | MUL | || | province | varchar(6) | | MUL | || | city | varchar(20) | | MUL | || | addr1| varchar(20) | | MUL | || | addr2| varchar(30) | | | || | rtimes | int(11) | | | 0 || +-++-+++--- 9 rows in set (0.00 sec) mysql show index from addrs; +--+---+-+-++--+- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi +--+---+-+-++--+- | addrs | 0 | PRIMARY |1 | addrid | A | | addrs | 1 | cname|1 | cname | A | | addrs | 1 | pcd |1 | pcd | A | | addrs | 1 | province |1 | province| A | | addrs | 1 | city |1 | city| A | | addrs | 1 | addr1|1 | addr1 | A | +--+---+-+-++--+- 6 rows in set (0.00 sec) mysql select count(*) from addrs; +- | count(*) | +- | 7418283 | +- 1 row in set (0.00 sec) mysql select * from addrs where cname = 'Mark' ; +++--+---+-+-+---+-- | addrid | sex | cname | pcd| province | city | addr1 | addr2 +++--+---+-+-+---+-- | 6561541 | | Mark || Õã½Ê¡ | ÅÍ°² | ÏØ | | 7416179 | M | Mark | 518007 | ¹ã¶«Ê¡ | ÉîÛÚ | Éϲ½Â·Î÷²©µçÄÔ¹«Ë¾ | +++--+---+-+-+---+-- 2 rows in set (0.00 sec) mysql select * from addrs where cname like 'Mark'; +++--+---+-+-+---+-- | addrid | sex | cname | pcd| province | city | addr1 | addr2 +++--+---+-+-+---+-- | 6561541 | | Mark || Õã½Ê¡ | ÅÍ°² | ÏØ | | 7416179 | M | Mark | 518007 | ¹ã¶«Ê¡ | ÉîÛÚ | Éϲ½Â·Î÷²©µçÄÔ¹«Ë¾ | +++--+---+-+-+---+-- 2 rows in set (0.00 sec) mysql select * from addrs where cname like 'Mark%'; Empty set (0.00 sec) mysql select * from addrs where cname = 'Mark' or cname like 'Mark'; +++--+---+-+-+---+-- | addrid | sex | cname | pcd| province | city | addr1 | addr2 +++--+---+-+-+---+-- | 6561541 | | Mark || Õã½Ê¡ | ÅÍ°² | ÏØ | | 7416179 | M | Mark | 518007 | ¹ã¶«Ê¡ | ÉîÛÚ | Éϲ½Â·Î÷²©µçÄÔ¹«Ë¾ | +++--+---+-+-+---+-- 2 rows in set (0.00 sec) mysql select * from addrs where cname = 'Mark' or cname like 'Mark%'; Empty set (0.00 sec) mysql select * from addrs where cname like 'Mark' or cname like 'Mark%'; Empty set (0.00 sec) mysql Bye [root@www bin]# mysqladmin variables; ++-- | Variable_name | Value | ++-- | ansi_mode | OFF | | back_log| 50| | basedir | /usr/local/ | | binlog_cache_size | 32768 | | character_set | gbk | | character_sets | gbk | |
Re: innoDB confusion
Hi!
Below is the column type mapping function from ha_innobase.cc.
But the internal column type inside InnoDB is not too important for the
user, because you can only see it in the output of innodb_table_monitor.
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
/**
Converts a MySQL type to an InnoDB type. */
inline
ulint
get_innobase_type_from_mysql_type(
/*==*/
/* out: DATA_BINARY, DATA_VARCHAR, ... */
Field* field) /* in: MySQL field */
{
/* The following asserts check that MySQL type code fits in
8 bits: this is used in ibuf and also when DATA_NOT_NULL is
ORed to the type */
dbug_assert((ulint)FIELD_TYPE_STRING 256);
dbug_assert((ulint)FIELD_TYPE_VAR_STRING 256);
dbug_assert((ulint)FIELD_TYPE_DOUBLE 256);
dbug_assert((ulint)FIELD_TYPE_FLOAT 256);
dbug_assert((ulint)FIELD_TYPE_DECIMAL 256);
switch (field-type()) {
case FIELD_TYPE_VAR_STRING: if (field-flags BINARY_FLAG) {
return(DATA_BINARY);
} else if (strcmp(
default_charset_info-name,
latin1) == 0) {
return(DATA_VARCHAR);
} else {
return(DATA_VARMYSQL);
}
case FIELD_TYPE_STRING: if (field-flags BINARY_FLAG) {
return(DATA_FIXBINARY);
} else if (strcmp(
default_charset_info-name,
latin1) == 0) {
return(DATA_CHAR);
} else {
return(DATA_MYSQL);
}
case FIELD_TYPE_LONG:
case FIELD_TYPE_LONGLONG:
case FIELD_TYPE_TINY:
case FIELD_TYPE_SHORT:
case FIELD_TYPE_INT24:
case FIELD_TYPE_DATE:
case FIELD_TYPE_DATETIME:
case FIELD_TYPE_YEAR:
case FIELD_TYPE_NEWDATE:
case FIELD_TYPE_ENUM:
case FIELD_TYPE_SET:
case FIELD_TYPE_TIME:
case FIELD_TYPE_TIMESTAMP:
return(DATA_INT);
case FIELD_TYPE_FLOAT:
return(DATA_FLOAT);
case FIELD_TYPE_DOUBLE:
return(DATA_DOUBLE);
case FIELD_TYPE_DECIMAL:
return(DATA_DECIMAL);
case FIELD_TYPE_TINY_BLOB:
case FIELD_TYPE_MEDIUM_BLOB:
case FIELD_TYPE_BLOB:
case FIELD_TYPE_LONG_BLOB:
return(DATA_BLOB);
default:
assert(0);
}
return(0);
}
-Original Message-
From: Jeremy Zawodny [EMAIL PROTECTED]
To: Heikki Tuuri [EMAIL PROTECTED]
Cc: Philip Molter [EMAIL PROTECTED]; [EMAIL PROTECTED]
[EMAIL PROTECTED]
Date: Friday, January 25, 2002 10:22 AM
Subject: Re: innoDB confusion
On Tue, Jan 15, 2002 at 08:36:16PM +0200, Heikki Tuuri wrote:
This brings up an interesting point. If you've lost your .frm
files, are you totally screwed, or does InnoDB contain enough
information to restore those .frm files? It's not a big deal if
With some work, yes. innodb_table_monitor prints the internal schema of
InnoDB. There are fewer column types inside InnoDB than in MySQL. For
example, a DATE column will appear as an integer.
Heikki,
Is the list of column types that InnoDB suppots (as well as how they
are mapped to MyISAM types) documented anywhere? I didn't see it
in the InnoDB manual?
I'd like to put it in the InnoDB section of my book if I can get the
right information. :-)
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.41-max: up 22 days, processed 514,398,369 queries (266/sec. avg)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
LIKE CLAUSE - bug
Hello, I have been successfully using the MYSQL database for the past 3+ months - however I have just encountered a bug with MYSQL that prevents my query from working. Basically I have a field in a table that describes an item, and I have written a program that allows a user to search for a word within this field. Here is a sample table structure: create TABLE my_table ( my_index int unsigned not null auto_increment, my_field varchar(256) ) The command I am issuing is as follows: select * from my_table where my_field like '%string%'; * where string is the value I am searching for. As previously mentioned my application has been working perfectly for the past 3 months - but to my suprise it stopped working recently. For example: select * from my_table where my_field like '%hello%'; The above query will fail, even though row 12000 contains the string hello within it. I did some experimentation and identified the corrupt record - I then executed a select statement searching for a record (in the same way as above) but for a record before the corruption: For example: select * from my_table where my_index 12000 AND my_field like'%hello%'; The above query will work! Also if the corrupt record is for example record 12001 (with my_index=12001) and I invoke the following select statement: select * from my_table where my_index=12000; The select statement will work. I simply cant understand why this is the case. I did try running some of the mysql repair tools but they failed. I Also tried doing a mysqldump and reading the table back in - however I noticed taht I lost about 100 records! Finally I wrote my own MYSQL repair command, basically it looped through each record in the table (obtaining each record doing a select on the primary key) then I dumped the data to an output file. I then read the table back into the database. This works - however one record would always be lost. Its really urgent that I get this bug fixed, and I would be exceptionally greatful if there is anyone who can advise me on how to resolve this problem. I am using MYSQL version: mysql Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686) Best regards, Spencer ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Access denied for user varlehti
LdS I'm experiencing the same problem. LdS But I sort of found a way around it (though I'm sure this is not really the right way). LdS When I remove the *password* for *root* and login again as root, I'm able to create new users using GRANT then flush priviliges. LdS Does this mean I have to indicate the password of root while using GRANT? LdS I can't find any info on this on the docs, please point us to where we can find the info for this. LdS thanks... Yes, using GRANT statment you can set password for user. Look at: http://www.mysql.com/doc/G/R/GRANT.html I still end up having a user with all privileges set to *N*, eventhough I created that user with mysql grant all privileges on dbase1.* to user1 identified by 'user1password'; What am I doing wrong? luie __ www.edsamail.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Linking problems
Guy-Maurice Lepoutre writes: Hello, I am using Visual C++ 6.0 and I have some linking problems while trying to run the example program MFC_ex.cpp included in the downloading files in the mysql.com website. Here are the errors I get: MFC_ex.exe - 18 error(s), 0 warning(s) Thanks a lot for your help Hi! You have to link in MySQL C API library too ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Unicode
Hery, Friday, January 25, 2002, 4:35:18 AM, you wrote: HY I need to know, how to configure MySQL can be support unicode like Chinese HY (simplified and Traditional), Croation,Chezh or whatever. There is no unicode support in MySQL. It will be probably supported in version 4.0.3. HY Hery -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tighly packed table
Christopher, Friday, January 25, 2002, 1:05:02 AM, you wrote: [max length of a string] CT you may want to do this programatically in a small C++ CT (or C, or whatever) program. It _may_ be faster. It may not, there'll be CT a lot of network traffic. SQL servers tend to be rather bad at string CT manipulation, something like this would be easy in C or in Perl. IMHO: I don't think so. Something that MySQL server runs internally differs a lot from something that is done outside, like retrieving of all records over a network and counting them on its own. I would rather tell that puddling the data traffic over a unix domain socket or even over a tcp connection will not improve a performance. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
other mailing lists?
Michael, Friday, January 25, 2002, 4:17:53 AM, you wrote: MO Are there any other good mailing lists out the about databases?.. If so, can MO you post the site where i can sign up for them? See other MySQL lists at: http://lists.mysql.com/ MO Mike -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
HOSTNAME-bin.001 ???
Jacob, Thursday, January 24, 2002, 7:49:54 PM, you wrote: JFL I found files whith names like localhost-bin.001, localhost-bin.002 in JFL /tmp/ JFL What are they ? These are log files. You can see more information about log files maintenance at: http://www.mysql.com/doc/L/o/Log_file_maintenance.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT LAST_INSERTID() very sloooow
Hi all, following query SELECT LAST_INSERT_ID() as auto_num FROM xrenders; fills our whole slow query log (500 kB a day). AUTO_INCREMENT field is int(11) type, primary key; table is of type MyISAM. We do the same operation on another similar table of the same db which is much larger without any performance problems. Affected table has 400.000+ records and mentioned query lasts tens or hunderds seconds. One record from the log: --- # Query_time: 223 Lock_time: 0 Rows_sent: 400633 Rows_examined: 400633 SET last_insert_id=400634; SELECT LAST_INSERT_ID() as auto_num FROM xrenders; --- This strange behavior appeared after upgrade from MySQL 3.23.32 to 3.23.47 on RedHat 6.2 There were never these problems before (many months). Affected table checked with CHECK .. EXTENDED passed OK. Any ideas? As I am not currently subscribed to this list, please reply to my e-mail as well. Thanks. -- MK - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT LAST_INSERTID() very sloooow
On Fri, Jan 25, 2002 at 02:22:12PM +0100, Marek Kustka wrote: following query SELECT LAST_INSERT_ID() as auto_num FROM xrenders; fills our whole slow query log (500 kB a day). Just do 'select last_insert_id() as auto_num'. The id isn't kept per table anyway, it is kept per session. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
seeking partner
TF Information Exchange (TF) Physical Address : Room 210, Building 2, Chegongzhuang Street No. 6, Xicheng District, Beijing, China Post Code: 100044 Tel: +86-10-6800-3112 Fax: +86-10-6800-1452 Web site: http://www.tangfeng.org E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Dear Sirs, As a direct result of the excellent reputation you enjoy here, we would like to build a cooperative relationship with you.TF is a senior investigative corporation in China. We are seeking international cooperation with investigative companies abroad. If you are in need of data from China, we are available to provide that information on consignment. We are an established authority in the field of research and information gathering in China. At the same time, we can also consign investigative missions to you when we need data from your country. In this manner we would hope to achieve a mutually beneficial arrangement exchanging needed information on a regular basis. Our services include: 1/ Credit and status investigations, including: Registration; corporate history; corporate structure; background of legal person and executives; financial profiles; banking relationships; operating situation; staff; products; facilities; profiles of affiliates; and more. 2/ professional market research, including: Advertising effectiveness; new product market research; and more. 3/ Investment services: Investment feasibility analyses; business partners' credit and status reports; agenting for foreign companies; comprehensive inquiry services; and more. 4/ Information protection: Inquiries on trademark and patent registration in China; knowledge property protection; trademark investigation in cases of trademark imitation; more. 5/ Information collection: Information about enterprises within China; information collection on policies, laws, current and historical business trends; and open profiles of various enterprises. 6/ Legal consultation: All-around legal consultation services for both enterprises and individuals. 7/ Criminal record searches. TF has built a large number of stable cooperative relationships with many governmental departments in China. For example, we have made successful arrangements with the Industry Trade Administrative Bureau of China, China Statistics Bureau, China National Economic Information Center, etc. The large investigative network of TF is made up of many economic specialists and professional investigators. We are interested in any opportunity of information exchanging. If our investigative abillities might be of assistance,please contact us. Awaiting your reply. TF China We are very apologized for the inconvenience arisen from this letter to you. We will delete your name from our maillist upon requirement. Thank you. ʹÓü«ÐÇÓʼþȺ·¢£¬ÎÞÐëͨ¹ýÓʼþ·þÎñÆ÷£¬Ö±´ï¶Ô·½ÓÊÏ䣬ËٶȾø¶ÔÒ»Á÷£¡ ÏÂÔØÍøÖ·£ºhttp://love2net.51.net/£¬¸ü¶àÃâ·ÑµÄ³¬¿áÈí¼þµÈÄãÀ´Ï¡¡ INFORMATION This message has been sent using a trial-run version of the TSmtpRelayServer Delphi Component. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT LAST_INSERTID() very sloooow
Fred van Engen wrote: On Fri, Jan 25, 2002 at 02:22:12PM +0100, Marek Kustka wrote: following query SELECT LAST_INSERT_ID() as auto_num FROM xrenders; fills our whole slow query log (500 kB a day). Just do 'select last_insert_id() as auto_num'. The id isn't kept per table anyway, it is kept per session. Regards, Fred. Oh, that was stupid bug. It was correct in the other case I mentioned. I haven't noticed the difference. The new server probably treated the statement differently than the previous one. Thanks much Fred! Virtual beer for you :) Marek -- MK - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ODBC - Access Denied -- Please help!!!!
Hi I am new to MySQL I have installed MySQL on windows 200 swerver and installed MyODBC. I created a db and added a user with the grant command and added select,insert,delete,update privileges for the user. mysql show grants for www; +--- + | Grants for www@% | +--- + | GRANT USAGE ON *.* TO 'www'@'%' IDENTIFIED BY PASSWORD '51d140c5511d64c5' | | GRANT ALL PRIVILEGES ON mycomax.* TO 'www'@'%' | +--- + 2 rows in set (0.00 sec) mysql show grants for www@'localhost'; +--- + | Grants for www@localhost | +--- + | GRANT USAGE ON *.* TO 'www'@'localhost' IDENTIFIED BY PASSWORD '51d140c5511d64 c5' | | GRANT ALL PRIVILEGES ON mycomax.* TO 'www'@'localhost' | +--- + 2 rows in set (0.00 sec) I have added a DSN for the DB. Now when I try to connect to the db via ODBC it gives me access denied *** [TCX][MyODBC] Acces denied for user: 'www@localhost' to database Mycomax *** if i don't insert a username and password it works - this must be the anonymous user. What am I doing wrong here? ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. a href=http://www.mycomax.com;www.mycomax.com/a ** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SOME ITEMS THAT YOU MAY BE INTERESTED IN OR BE ABLE TO ADVISE ME ON
And this nitwit also just spammed the SpamCop newsgroup! Must be out on a suicide mission... Once again: PLEASE make this list subscription-only! We really DON'T need spam here! At 15:53 2002-01-24, you wrote: These are the items that iam interested in selling.. Could you help me with some details on the goods, history, origin etc. are these worth anything and if so who would i contact with regards to selling them? and the best way to sell them ie auction etc APOLOGISE IF YOU HAVE ALREADY RECEIVED THIS E-MAIL Yes I have - in the SpamCop newsgroup! news:news.spamcop.net/spamcop Apologies are NOT accepted, though! G: database,sql,query,table -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: seeking partner
Good grief! Yet another SPAM on the same day. When will it filter through the apparently thick head that the so-called filtering DOES NOT WORK? Stop this sillyness already, please. SUBSCRIPTION only for this list is the only sensible solution! At 22:41 2002-01-25, you wrote: TF Information Exchange (TF) Physical Address : Room 210, Building 2, Chegongzhuang Street No. 6, Xicheng District, Beijing, China Post Code: 100044 Tel: +86-10-6800-3112 Fax: +86-10-6800-1452 Web site: http://www.tangfeng.org E-mail: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Dear Sirs, I'm not a sir either! As a direct result of the excellent reputation you enjoy here, we would like to build a cooperative relationship with you.TF is a senior investigative corporation in China. We are seeking international cooperation with investigative companies abroad. [snip] G: database,sql,query,table (just to get this through the non-functional filters...) -- Marjolein Katsma HomeSite Help - http://hshelp.com/ - Extensions, Tips and Tools The Bookstore - http://books.hshelp.com/ - Books for webmasters and webrookies - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqld freebsd
freebsd2# cd /usr/ports/databases/mysql323-server freebsd2# make pre-fetch You may use the following build options: WITH_CHARSET=charsetdefine the primary built-in charset (latin1); WITH_XCHARSET=list define other built-in charsets (may be 'all'); DB_DIR=directorySet alternate directory for database files (default is /var/db/mysql). WITH_LINUXTHREADS=yes Use the linuxthreads pthread library. This is _NOT_ recommended for production servers. Expect problems when enabled. SKIP_INSTALL_DB=yes Skip mysql_install_db (i. e. leave /var/db/mysql alone). This is useful for upgrades. Be sure to know what you are doing! SKIP_DNS_CHECK=yes don't run resolveip to do an additional DNS check before inserting local hostname to mysql database. Use if your machine has no offical DNS entry. BUILD_STATIC=yesBuild a static version of mysqld. BUILD_OPTIMIZED=yes Add -mcpu=pentiumpro -O3 to CFLAGS. This setting may produce broken code and thus is not recommended for production servers. freebsd2# You want to use the WITH_LINUXTHREADS option as well as DB_DIR (and whatever else you want) to build your mysql. Hope this helps. - Original Message - From: Oleg Prokopyev [EMAIL PROTECTED] To: Ken Menzel [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 5:19 AM Subject: Re: mysqld freebsd Ken Menzel wrote: Hi Oleg, There is some sort of thread problem with freebsd but it usuually is not that bad. How did you compile MySQL? I would recommend using the ports version of Mysql (cd /usr/ports/databases/mysql-3.23-server) . Look at the makefile if you are still havbing threads problems you can do you mean MIT-threads options ? :( it does not want to compile rest cut - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
quick questions about redhat and mysql
Hello, I am trying to decide which OS to use for a dedicated mysql server for my website. I need filesize 2GB support. I really wanted to use Solaris, but there are no drivers for my RAID adapter (long story). I was considering NetBSD, but I don't know it well. I was then back to thinking about Linux. So, I can't keep all the OS/kernel/requirements in my head... Can RedHat 7.1/7.2, out of the box (ie. no patches, kernel re-compiles, etc), support files 2GB? Will I be able to easily re-compile mysql to support large files? Should I stick with the version of mysql on the RH disto, or download/install the latest stable version? Can someone comment on the stability of mysql on redhat 7.1/7.2? Thanks! -Hank __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Linking problems
Hi, I did all that but I still have the linking problems: it seems that it doesn't find the MysqlConnection implementation. NOTES: -- 1) The main workspace and project for the mysql++ API are located in the root directory 2) This project, when built, will yield the static library mysql++.lib in the /lib directory 3) This library needs the header files in the /include and /mysql/include directory, and libmysql.lib in the mysql/lib directory 4) All programs using MySQL++ need libmysql.dll (located in the /mysql/lib directory) to be able to run. You have to put it into the same directory as the executable, or for example copy it into the windows/system directory 5) In the /example directory a workspace and project have been made for building the (non-MFC) examples 6) In the /example/MFC_example an example for using MySQL++ in an MFC project has been made PROBLEMS: - 1) Visual C++ doesn't provide full functionality for longlongs, such as strtoll and ostream operator , so all longlongs have been replaced with ints in the examples 2) The example custom4.cpp could not be compiled 3) There are still some problems with the functions for static result sets. They seem to work alright with vectors, but might not work for the other STL containers 4) In every project you make with MySQL++ support, you need to use the multithreaded DLL run-time library. This can be set in Project Settings, C/C++ tab, category Code Generation, Use run-time library listbox. If you don't want to change your run-time library, change the run-time library in the MySQL++ project and rebuild mysql++.lib (warning: this is not recommended and may yield linker errors in your project!) HOW TO RUN THE EXAMPLES --- 1) You should have mysql server installed on your localhost with user root and no password. 2) Run resetdb.exe example in order to create test database. 3) Run other examples HOW TO ADD MYSQL++ TO AN EXISTING VISUAL C++ PROJECT 1) Add mysql++.lib to the project 2) Go to Project Settings, C/C++ tab, category Preprocessor, and add the directories include and mysql/include to the Additional include directories edit box 3) In the source files, in which you want to use MySQL++, add the line #include mysql++ after all other included header files (especially those of MFC) 4) If errors occur, see the Problems section Thanks a lot for your help --- Sinisa Milivojevic [EMAIL PROTECTED] wrote: Guy-Maurice Lepoutre writes: Hello, I am using Visual C++ 6.0 and I have some linking problems while trying to run the example program MFC_ex.cpp included in the downloading files in the mysql.com website. Here are the errors I get: MFC_ex.exe - 18 error(s), 0 warning(s) Thanks a lot for your help Hi! You have to link in MySQL C API library too ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quick questions about redhat and mysql
Hank, 1. RH 7.1 supports files 2GB if you choose the Enterprise/SMP flavour (you'll be asked to choose which install you want to pursue when you put the install CD in) No kernel re-compile needed. 2. MySQL will support large files. Also, if you use Innodb tables, many of the large file concerns you have will be avoided, as Innodb filespace is not dependent on file sizes - you just create a number of files, and tables are distributed across as many large or small files as you want. Check out www.innodb.com for more on this. 3. Do not use the RH mysql version. Get it from the Mysql website, and use 3.23.47-max (which includes Innodb support) or higher. 4. Stability of Mysql on RH 7.1 is excellent. We haven't had a mysql crash in 6 months - basically since we upgraded to 3.23.XX mysql, and improved the configuration and memory usage of our database. We run RH 7.1 from the install CD, with SMP configuration which supports large memory, files 2GB, and so far, its performance has been excellent. I have become very impressed with the stability of MySQL on Linux. We have a pretty large database ( 65 million rows in one table, 3 or 4 others with 10 million+ rows, total db size 20GB to date ) and use this to dynamically generate webpages and emails for our 700,000 users. MySQL has totally made me not purchase Oracle. Cheers, John Director, Software Development Streetmail Inc. http://www.streetmail.com Henry Hank wrote: Hello, I am trying to decide which OS to use for a dedicated mysql server for my website. I need filesize 2GB support. I really wanted to use Solaris, but there are no drivers for my RAID adapter (long story). I was considering NetBSD, but I don't know it well. I was then back to thinking about Linux. So, I can't keep all the OS/kernel/requirements in my head... Can RedHat 7.1/7.2, out of the box (ie. no patches, kernel re-compiles, etc), support files 2GB? Will I be able to easily re-compile mysql to support large files? Should I stick with the version of mysql on the RH disto, or download/install the latest stable version? Can someone comment on the stability of mysql on redhat 7.1/7.2? Thanks! -Hank __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Null vs empty string and storage
Does null take up the same amount of storage as an empty string? If one has a table and stores an empty string in place of NULL for elements that were left blank on a Web form, does that increase storage requirements? Or does having the column already defined mean that storage was already allocated, and empty and NULL have the same storage requirements? I suppose it is different for CHAR, VARCHAR, TEXT and so forth.. -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LOAD DATA INFILE - Duplicate entry '' for key...
Bogdan, You solved your own problem - that's good (and the timestamps below hint that you have other abilities) Did you realise that you could specify a LINES TERMINATED BY '\r\n' clause and stick with LOAD DATA? Oh the joys of working with *nix and Windows... =dn - Original Message - From: Bogdan Stancescu [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 25 January 2002 00:22 Subject: Re: LOAD DATA INFILE - Duplicate entry '' for key... Just for the record, found the glitch: somehow, somebody on the way decided to change LF's into CRLF's (I remotely dumped the data and e-mailed it locally where I test the setup process, so maybe the mail agent?). I just had to replace 0x0d0a's into 0x0a's in a hex editor and everything works fine now. Bogdan On Friday 25 January 2002 01:56 am, Bogdan Stancescu wrote: Hi all! I bumped in a strange problem. I dumped a help table (i.e. only text) into a file. The table contains two fields: a topic (unique) field and a content field. Then performed a LOAD DATA INFILE on the resulting file in a different database with identical structure. Everything worked just fine. Later on, after some editing on the table via PHP, everything looked ok in PHP and I dumped the table once again. Guess what? LOAD DATA INFILE now says Duplicate entry '' for key 1. The table structures continue to be identical. The original data in the second database doesn't exist anymore (this is actually needed for a setup process, so the database and table are created from scratch with the same code). The dump file looks ok - similar in every way with the original - I even searched for TAB characters and there's no TAB at the beginning of any line! What gives? Did anyone else bump into this kind of problem? Thanks in advance! Bogdan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is faster VarChar(255) or Text?
At 02:31 AM 1/25/2002 , you wrote: On Thu, Jan 24, 2002 at 05:27:18PM -0600, BD wrote: I will be putting variable length text into a field (up to 255 characters but typically around 60 characters) and wonder what makes for faster retrieval? Or does it matter? A field defined as Varchar(255) or Text? Do you mean VARCHAR(255) or TINYTEXT? As seen here: http://www.mysql.com/doc/n/o/node_366.html TEXT columns can be much larger than 255. But they require an extra byte for the length portion of the record. So VARCHAR(255) will be ever so slightly faster than TEXT. Jeremy Jeremy, Thanks, I hadn't thought of TinyText. With flat file type databases that I used to use, if I put something in a memo field, it takes longer to retrieve the data because it is stored in a separate physical file. There is a noticeable lag on slow machines. I was wondering if using a Text (or TinyText) in MySQL exhibited noticeably slower record retrieval when hundreds of users are querying the database. Or is it too small to notice? Brent -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,632,289 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tighly packed table
Michael, Let's round it up to 3 million rows (I'm lazy at math too!) Let's say you currently allow 15 bytes per name. Let's say the longest name on file is 12 characters. The exercise would save 3 bytes/row multiplied by 3M rows, ie 9MB (yes, let's ignore binary-decimal differences too) If you had two name fields (first- and family-name). Woohoo that's a potential saving of 18MB I'm also generous (to a fault) so round it up to 20MB. If you go out to buy a small PC HDD today, the smallest catalog product might be 40GB (let's assume they quote formatted capacity - they don't, but there am I being half-full/-baked again) Thus we have the ability to save 0.0005% against total capacity of a new drive. Of course, the designer might have allowed way too much name-space (pun hah!) or the table may have other 'compressible' columns. Let's go for a saving of 0.001% A new drive costs how much? Your hourly rate is how much? How long will the job take you? How many cups of coffee is that? Can the client carry the cost of all that coffee? Won't your stomach rebel at the mistreatment? Mind you, most of the above is made up - I don't have any faults! Time for me to go refill my glass (with healthy fruit juice)! =dn PS after enjoying myself, let me point out that if the 'name' fields are currently defined as variable length, this exercise would allow you to make them fixed length. If you can 'wipe out' all the variable width columns in the table, performance will improve significantly! Hahaha. This is a static database. But you are right I don't know how much this will actually help. Hard disk isn't an issue. It was just an experiment...(that I have no time for anyway!) Thanks, Michael On Friday, January 25, 2002, at 06:19 PM, DL Neil wrote: ...and because no one has been really cynical... After that query runs, then prepare for a coffee overload whilst you perform the ALTER TABLE, then get ready because if you shorten the field to (say) 12 characters/bytes the very next day, someone with a 13 character name is going to try to register! I'm wondering just how much space this 'little' exercise is going to save, either as a ratio of the size of the db, or as a ratio of HDD size? My glass is half-empty! =dn - Original Message - From: Michael Stearne [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 24 January 2002 22:58 Subject: Re: Tighly packed table The problem is, this query really hurts (I don't know if it finishes) for unindexed field for 2.9 million rows. But I'm sure it will finish eventually. Michael Roger Karnouk wrote: select max(length(firstname)) from TableName; -Original Message- From: Michael Stearne [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 4:38 PM To: Christopher Thompson Cc: [EMAIL PROTECTED] Subject: Re: Tighly packed table Christopher Thompson wrote: At 04:10 PM 1/24/2002 -0500, Michael Stearne wrote: We have a somewhat large read-only table (2.9 million recs). I am wonder if there is a utility that will look at each row of each columns and come up with a summary of the largest field (in character length) for each column. For example, scan each row's firstname field and report that the longest first name is 12 characters. That way I can ALTER the firstname field to be a char or varchar of 12? What would be better BTW? I don't know if CHAR or VARCHAR is better for you but as to the query here, it would seem easiest to write a short program to query all the rows and programatically determine the longest column length. That said, you could probably set up a SQL statement for it. There's a LENGTH function in SQL, isn't there? The statement would look SIMILAR to the following: SELECT MAX(LENGTH(t1.FIRSTNAME)) AS fnamelength FROM TableFoo t1, TableFoo t2 WHERE LENGTH(t2.FIRSTNAME) = fnamelength; Looks good to me, thanks. Michael (Please note that my university SQL instructor pointed out that I wrote SQL statements backwards to anyone else he had ever taught. For that matter, I did Prolog backwards, too. :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
RE: Which is faster VarChar(255) or Text?
I belive the type of table you use here will be the only thing that will speed things up. If you use MyISAM tables and have 100's of users calling things from the DB and 100's of users INSERTING things into the same table, then it will be very slow due to MyISAM's table level locking. Using InnoDB tables will speed up access time due to ROW level locking. -My 2c - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: BD [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 9:42 AM To: [EMAIL PROTECTED] Subject: Re: Which is faster VarChar(255) or Text? At 02:31 AM 1/25/2002 , you wrote: On Thu, Jan 24, 2002 at 05:27:18PM -0600, BD wrote: I will be putting variable length text into a field (up to 255 characters but typically around 60 characters) and wonder what makes for faster retrieval? Or does it matter? A field defined as Varchar(255) or Text? Do you mean VARCHAR(255) or TINYTEXT? As seen here: http://www.mysql.com/doc/n/o/node_366.html TEXT columns can be much larger than 255. But they require an extra byte for the length portion of the record. So VARCHAR(255) will be ever so slightly faster than TEXT. Jeremy Jeremy, Thanks, I hadn't thought of TinyText. With flat file type databases that I used to use, if I put something in a memo field, it takes longer to retrieve the data because it is stored in a separate physical file. There is a noticeable lag on slow machines. I was wondering if using a Text (or TinyText) in MySQL exhibited noticeably slower record retrieval when hundreds of users are querying the database. Or is it too small to notice? Brent -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,632,289 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quick questions about redhat and mysql
On Fri, Jan 25, 2002 at 10:18:08AM -0500, John Kemp wrote: : Hank, : : 3. Do not use the RH mysql version. Get it from the Mysql website, and : use 3.23.47-max (which includes Innodb support) or higher. What's wrong with the RH mysql version? I've been using it for 5 months without a problem. It has built-in support for both BDB and InnoDB tables and doesn't appear to have any problems with enterprise-class systems. Then again, we're using InnoDB tables, so we're not limited by file-size. Is there a reason to use MySQL's version over RH's? The only difference I can see is that RH's compiles it to work with different (read: standard RH) filesystem layouts, uses the standard RH initialization files, and has some RH-tuned config files. They're not patching the source at all, and my understanding is that the compile options are verified for the specific RedHat version. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving a site
Hi all, We are moving our site and wanted to find out if anyone has done this and what is the best solution. We have a 2 web server farm now with 1 being Mysql that we are hsoting at an ISP. Want to know the best way to move the live database to our new server farm with 2 database servers. Hopefully not have the users miss anything or have the site down at all if possible. 1. Is it better to shut down the existing Mysql server and do a database dump and then use this to create the new database 2. Set up the new database to be a slave of the live database we have now so it gets replicated and then when we turn off the old site the slave can become the master for the new site. I will have to still get a dump of the data on the old server and also turn on logging on it so the new server can catch up. I am reading more about this process and need to find out if the lod db must be down for a bit while we get the dump. Thanks in advance. Joe - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
Alright then! database,sql,query,table Which field type do I use for a simply checkbox Y,N? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Tighly packed table
Hi, If your column is of type VARCHAR, you want save much space (at least not as much as DL Neil said). The specifications of tha varchar column type is that it uses as much bytes as the data in it. Of course this will make your indexes smaller (if this column is indexed). A few days before I decided to optimize one of my tables (5 milion rows) and altered a varchar(250) field to a varchar(100). The size of the MYD data file changed with less than 1Mb so you see that there was not much use of doing it. Dobromir Velev Software Developer http://www.websitepulse.com/ -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 26, 2002 5:39 PM To: Michael Stearne Cc: Michael Stearne; Roger Karnouk; [EMAIL PROTECTED] Subject: Re: Tighly packed table Michael, Let's round it up to 3 million rows (I'm lazy at math too!) Let's say you currently allow 15 bytes per name. Let's say the longest name on file is 12 characters. The exercise would save 3 bytes/row multiplied by 3M rows, ie 9MB (yes, let's ignore binary-decimal differences too) If you had two name fields (first- and family-name). Woohoo that's a potential saving of 18MB I'm also generous (to a fault) so round it up to 20MB. If you go out to buy a small PC HDD today, the smallest catalog product might be 40GB (let's assume they quote formatted capacity - they don't, but there am I being half-full/-baked again) Thus we have the ability to save 0.0005% against total capacity of a new drive. Of course, the designer might have allowed way too much name-space (pun hah!) or the table may have other 'compressible' columns. Let's go for a saving of 0.001% A new drive costs how much? Your hourly rate is how much? How long will the job take you? How many cups of coffee is that? Can the client carry the cost of all that coffee? Won't your stomach rebel at the mistreatment? Mind you, most of the above is made up - I don't have any faults! Time for me to go refill my glass (with healthy fruit juice)! =dn PS after enjoying myself, let me point out that if the 'name' fields are currently defined as variable length, this exercise would allow you to make them fixed length. If you can 'wipe out' all the variable width columns in the table, performance will improve significantly! Hahaha. This is a static database. But you are right I don't know how much this will actually help. Hard disk isn't an issue. It was just an experiment...(that I have no time for anyway!) Thanks, Michael On Friday, January 25, 2002, at 06:19 PM, DL Neil wrote: ...and because no one has been really cynical... After that query runs, then prepare for a coffee overload whilst you perform the ALTER TABLE, then get ready because if you shorten the field to (say) 12 characters/bytes the very next day, someone with a 13 character name is going to try to register! I'm wondering just how much space this 'little' exercise is going to save, either as a ratio of the size of the db, or as a ratio of HDD size? My glass is half-empty! =dn - Original Message - From: Michael Stearne [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 24 January 2002 22:58 Subject: Re: Tighly packed table The problem is, this query really hurts (I don't know if it finishes) for unindexed field for 2.9 million rows. But I'm sure it will finish eventually. Michael Roger Karnouk wrote: select max(length(firstname)) from TableName; -Original Message- From: Michael Stearne [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 4:38 PM To: Christopher Thompson Cc: [EMAIL PROTECTED] Subject: Re: Tighly packed table Christopher Thompson wrote: At 04:10 PM 1/24/2002 -0500, Michael Stearne wrote: We have a somewhat large read-only table (2.9 million recs). I am wonder if there is a utility that will look at each row of each columns and come up with a summary of the largest field (in character length) for each column. For example, scan each row's firstname field and report that the longest first name is 12 characters. That way I can ALTER the firstname field to be a char or varchar of 12? What would be better BTW? I don't know if CHAR or VARCHAR is better for you but as to the query here, it would seem easiest to write a short program to query all the rows and programatically determine the longest column length. That said, you could probably set up a SQL statement for it. There's a LENGTH function in SQL, isn't there? The statement would look SIMILAR to the following: SELECT MAX(LENGTH(t1.FIRSTNAME)) AS fnamelength FROM TableFoo t1, TableFoo t2 WHERE LENGTH(t2.FIRSTNAME) = fnamelength; Looks good to me, thanks. Michael (Please note that my university SQL instructor pointed out that I wrote SQL statements backwards to anyone else he had ever taught. For that matter, I did
Re: Tighly packed table
Wow, I feel like I wasted time just asking! :-) But my goal was not to save disk space it was to optimize the queries on the table as this is a test project for mySQL/OS X versus Unify/SCO or Unify/Linux or mySQL/Linux. I am kind of partial to the mySQL/OS X combo, so I wanted it to work as optimized as possible considering it is a 500Mhz iMac G3 going against a DP PII 850 Linux box. Like I said the results are very promising. Thanks, Michael DL Neil wrote: Michael, Let's round it up to 3 million rows (I'm lazy at math too!) Let's say you currently allow 15 bytes per name. Let's say the longest name on file is 12 characters. The exercise would save 3 bytes/row multiplied by 3M rows, ie 9MB (yes, let's ignore binary-decimal differences too) If you had two name fields (first- and family-name). Woohoo that's a potential saving of 18MB I'm also generous (to a fault) so round it up to 20MB. If you go out to buy a small PC HDD today, the smallest catalog product might be 40GB (let's assume they quote formatted capacity - they don't, but there am I being half-full/-baked again) Thus we have the ability to save 0.0005% against total capacity of a new drive. Of course, the designer might have allowed way too much name-space (pun hah!) or the table may have other 'compressible' columns. Let's go for a saving of 0.001% A new drive costs how much? Your hourly rate is how much? How long will the job take you? How many cups of coffee is that? Can the client carry the cost of all that coffee? Won't your stomach rebel at the mistreatment? Mind you, most of the above is made up - I don't have any faults! Time for me to go refill my glass (with healthy fruit juice)! =dn PS after enjoying myself, let me point out that if the 'name' fields are currently defined as variable length, this exercise would allow you to make them fixed length. If you can 'wipe out' all the variable width columns in the table, performance will improve significantly! Hahaha. This is a static database. But you are right I don't know how much this will actually help. Hard disk isn't an issue. It was just an experiment...(that I have no time for anyway!) Thanks, Michael On Friday, January 25, 2002, at 06:19 PM, DL Neil wrote: ...and because no one has been really cynical... After that query runs, then prepare for a coffee overload whilst you perform the ALTER TABLE, then get ready because if you shorten the field to (say) 12 characters/bytes the very next day, someone with a 13 character name is going to try to register! I'm wondering just how much space this 'little' exercise is going to save, either as a ratio of the size of the db, or as a ratio of HDD size? My glass is half-empty! =dn - Original Message - From: Michael Stearne [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 24 January 2002 22:58 Subject: Re: Tighly packed table The problem is, this query really hurts (I don't know if it finishes) for unindexed field for 2.9 million rows. But I'm sure it will finish eventually. Michael Roger Karnouk wrote: select max(length(firstname)) from TableName; -Original Message- From: Michael Stearne [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 24, 2002 4:38 PM To: Christopher Thompson Cc: [EMAIL PROTECTED] Subject: Re: Tighly packed table Christopher Thompson wrote: At 04:10 PM 1/24/2002 -0500, Michael Stearne wrote: We have a somewhat large read-only table (2.9 million recs). I am wonder if there is a utility that will look at each row of each columns and come up with a summary of the largest field (in character length) for each column. For example, scan each row's firstname field and report that the longest first name is 12 characters. That way I can ALTER the firstname field to be a char or varchar of 12? What would be better BTW? I don't know if CHAR or VARCHAR is better for you but as to the query here, it would seem easiest to write a short program to query all the rows and programatically determine the longest column length. That said, you could probably set up a SQL statement for it. There's a LENGTH function in SQL, isn't there? The statement would look SIMILAR to the following: SELECT MAX(LENGTH(t1.FIRSTNAME)) AS fnamelength FROM TableFoo t1, TableFoo t2 WHERE LENGTH(t2.FIRSTNAME) = fnamelength; Looks good to me, thanks. Michael (Please note that my university SQL instructor pointed out that I wrote SQL statements backwards to anyone else he had ever taught. For that matter, I did Prolog backwards, too. :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
At 10:58 AM -0500 1/25/02, Vernon A Webb wrote: database,sql,query,table Which field type do I use for a simply checkbox Y,N? How about CHAR(1)? -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AW: Replication-aware client...
On Thursday 24 January 2002 11:14 pm, Tobias Erichsen wrote: ? I?ve read an old message of yours on deja concerning the possiblity ? that the client can automatically select a master or slave in a ? replication-environment. ?Is this only available for linux, or does ? the libmysql.dll also support such feature? ? ?It should, although this has not been tested. As I understand it, I need the 4.0x version of MySQL for it - correct? Where can I find details in the docs/manuals to configure this feature? Yes, you need 4.0. Unfortunately, I have not yet documented the feature, but you can figure out how to use it by studing client/mysqltest.c. Search through the source for _rpl_ and try to follow the code. I?d like to set up a test-environment to see how good this works under Win32. ?If you like, I can send you the results I have gained... That would be very helpful. -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL Checkbox table field
VarChar Usually -Original Message- From: Vernon A Webb [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 9:58 AM To: [EMAIL PROTECTED] Subject: Re: MySQL Checkbox table field Alright then! database,sql,query,table Which field type do I use for a simply checkbox Y,N? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Equivalent of an Oracle SEQUENCE in mysql?
Is there the equivalent of a sequence in mysql? Does anyone have an example of emulating sequences? Thanks Rich Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Selecting the row with largest number in a column
Hello, I have a simple query, and a problem countless people must have had, I just cannot work it out at the moment, I am new to MySQL; I hope you can help. My current statement looking at the manual. SELECT * FROM contacts WHERE age=MAX(age); I started with: mysql select * from contacts where age=(select MAX(age) from contacts); In escence I am trying to ascertain the details of the person who is oldest. Any suggestions.? Richard - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Innodb funny error
Hi! HeikkiiH == Heikki Tuuri [EMAIL PROTECTED] writes: HeikkiiH Ken, HeikkiiH the 'connection lost' error suggests some bug in the client or HeikkiiH communication. Since you are running 4.0.1-alpha, it could be something with HeikkiiH the query cache. I think Sanja has already fixed some bugs there since 4.0.1 HeikkiiH was released. The query cache is suspect since the error did not crash the HeikkiiH server, and it was restricted to one table. HeikkiiH Well, the next time you get the problem you could try just inserting a HeikkiiH single additional row to the table. That should invalidate the query cache, HeikkiiH I think. ALTER TABLE certainly does that. If this is a cache problem, then any insert or 'reset query cache' would invalidate the cache. Note however that the query cache is not on by default; If Ken didn't enable it, it should not have caused by this problem. HeikkiiH I am forwarding this report to Monty and Sanja. HeikkiiH Best regards, It would have been nice to know if restarting MySQL would have helped. If not, then we would like to have had a copy of the InnoDB table space to be able to replicate the problem! Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql cache err with mysqldump? was: Innodb funny error
Hi! Heikki == Heikki Tuuri [EMAIL PROTECTED] writes: Heikki Sanja, Heikki maybe adding some debug code to the client or the server would help? After Heikki all, the symptom is easy to notice: the client claims the connection to the Heikki server is broken. Why does it claim that? This could happen if some client code set the 'thd-killed' flag. The main problem here is why we should get this in mysqldump, but not in 'mysql'. Ken, did you get any output from mysqldump (Ie, did you get the CREATE TABLE statement ?) Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting the row with largest number in a column
Richard Morton a écrit : Hello, I have a simple query, and a problem countless people must have had, I just cannot work it out at the moment, I am new to MySQL; I hope you can help. My current statement looking at the manual. SELECT * FROM contacts WHERE age=MAX(age); I started with: mysql select * from contacts where age=(select MAX(age) from contacts); In escence I am trying to ascertain the details of the person who is oldest. Any suggestions.? Richard hi, This request should work: SELECT * FROM contacts ORDER BY age DESC LIMIT 1; Hope this helps -- Joseph Bueno NetClub/Trader.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
In the last episode (Jan 25), Michael Collins said: At 10:58 AM -0500 1/25/02, Vernon A Webb wrote: database,sql,query,table Which field type do I use for a simply checkbox Y,N? How about CHAR(1)? The BIT type seems to be the best fit. It's currently a synonym for CHAR(1), but there's a TODO item: * Optimise `BIT' type to take 1 bit (now `BIT' takes 1 char). -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
foreign key?
Hi, I'm trying to update the values of Contact_ID and Volunteer_ID in a table called Contributors. The layout for Contributors is ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | auto_increment | | Name | varchar(100) | | | | | | Street_Address | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(5) | YES | | NULL| | | Zip| mediumint(8) | YES | | NULL| | | Contact_ID | tinyint(3) | YES | | NULL| | | Volunteer_ID | tinyint(3) | YES | | NULL| | ++--+--+-+-++ I'm using the command: update Contributors set Contact_ID = 1, Volunteer_ID = '13' where Name = Somebody Lastname; After having issued the command, I get Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 But then when I select * in Contributors where Name = Somebody Lastname; The values haven't been updated. They remain 0 and 0. What gives? Is this a foreign key problem? TIA. PS. Oh yeah, I'm using mysql 3.22 on debian 2.2. -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= The other day I... uh, no, that wasn't me. -- Steven Wright - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [ale] foreign key?
Do the update without quotes for 1 and 13.. Vaidhy On Fri, Jan 25, 2002 at 11:48:31AM -0500, David S. Jackson wrote: Hi, I'm trying to update the values of Contact_ID and Volunteer_ID in a table called Contributors. The layout for Contributors is ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | Contributor_ID | tinyint(3) | | PRI | 0 | auto_increment | | Name | varchar(100) | | | | | | Street_Address | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(5) | YES | | NULL| | | Zip| mediumint(8) | YES | | NULL| | | Contact_ID | tinyint(3) | YES | | NULL| | | Volunteer_ID | tinyint(3) | YES | | NULL| | ++--+--+-+-++ I'm using the command: update Contributors set Contact_ID = 1, Volunteer_ID = '13' where Name = Somebody Lastname; After having issued the command, I get Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 But then when I select * in Contributors where Name = Somebody Lastname;The values haven't been updated. They remain 0 and 0. What gives? Is this a foreign key problem? TIA. PS. Oh yeah, I'm using mysql 3.22 on debian 2.2. -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= The other day I... uh, no, that wasn't me. -- Steven Wright --- This message has been sent through the ALE general discussion list. See http://www.ale.org/mailing-lists.shtml for more info. Problems should be sent to listmaster at ale dot org. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tighly packed table
Michael: see also my PS comment Dobromir: Michael and I were joking between us, hence the silly comments appearing. I apologise if this did not communicate. IMHO the pragmatics of the exercise made it a waste of time/effort - even when I over-stated the savings at every opportunity! On a Friday afternoon a little speculation and humor is a good way to start the weekend! You are 100% correct, the disk space occupied by a table is not the sum of the the length of its data-rows. For example, there is always space left for expansion/INSERTions. However in this case, because it is a R/O table, it could be squashed right down. I cannot comment if a table containing varchar/variable length fields can be compressed more or less than a table with only fixed length fields. Basically varchar allows one to potentially 'trade' disk space savings for a degradation in query response times. Some do not realise that by removing variable length fields to a 'companion table', any queries which access the (fixed-length) table without needing to look at the variable-length field(s), will speed up significantly. Thanks for providing some 'real' numbers. That was of interest. =dn - Original Message - From: Dobromir Velev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 25 January 2002 16:11 Subject: RE: Tighly packed table Hi, If your column is of type VARCHAR, you want save much space (at least not as much as DL Neil said). The specifications of tha varchar column type is that it uses as much bytes as the data in it. Of course this will make your indexes smaller (if this column is indexed). A few days before I decided to optimize one of my tables (5 milion rows) and altered a varchar(250) field to a varchar(100). The size of the MYD data file changed with less than 1Mb so you see that there was not much use of doing it. Dobromir Velev Software Developer http://www.websitepulse.com/ -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 26, 2002 5:39 PM To: Michael Stearne Cc: Michael Stearne; Roger Karnouk; [EMAIL PROTECTED] Subject: Re: Tighly packed table Michael, Let's round it up to 3 million rows (I'm lazy at math too!) Let's say you currently allow 15 bytes per name. Let's say the longest name on file is 12 characters. The exercise would save 3 bytes/row multiplied by 3M rows, ie 9MB (yes, let's ignore binary-decimal differences too) If you had two name fields (first- and family-name). Woohoo that's a potential saving of 18MB I'm also generous (to a fault) so round it up to 20MB. If you go out to buy a small PC HDD today, the smallest catalog product might be 40GB (let's assume they quote formatted capacity - they don't, but there am I being half-full/-baked again) Thus we have the ability to save 0.0005% against total capacity of a new drive. Of course, the designer might have allowed way too much name-space (pun hah!) or the table may have other 'compressible' columns. Let's go for a saving of 0.001% A new drive costs how much? Your hourly rate is how much? How long will the job take you? How many cups of coffee is that? Can the client carry the cost of all that coffee? Won't your stomach rebel at the mistreatment? Mind you, most of the above is made up - I don't have any faults! Time for me to go refill my glass (with healthy fruit juice)! =dn PS after enjoying myself, let me point out that if the 'name' fields are currently defined as variable length, this exercise would allow you to make them fixed length. If you can 'wipe out' all the variable width columns in the table, performance will improve significantly! Hahaha. This is a static database. But you are right I don't know how much this will actually help. Hard disk isn't an issue. It was just an experiment...(that I have no time for anyway!) Thanks, Michael On Friday, January 25, 2002, at 06:19 PM, DL Neil wrote: ...and because no one has been really cynical... After that query runs, then prepare for a coffee overload whilst you perform the ALTER TABLE, then get ready because if you shorten the field to (say) 12 characters/bytes the very next day, someone with a 13 character name is going to try to register! I'm wondering just how much space this 'little' exercise is going to save, either as a ratio of the size of the db, or as a ratio of HDD size? My glass is half-empty! =dn - Original Message - From: Michael Stearne [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 24 January 2002 22:58 Subject: Re: Tighly packed table The problem is, this query really hurts (I don't know if it finishes) for unindexed field for 2.9 million rows. But I'm sure it will finish eventually. Michael Roger Karnouk wrote: select max(length(firstname)) from TableName; -Original Message-
Corrupted table?
I'm having a weird problem with my mysql data dump and wondering if anybody else has had a similar problem. I run my mysql dump program, no problem there, download the file, and view it with a text editor (an oldie: pfe - programmers file editor). Then one dark day pfe was about half way through reading a file and stopped dead. It tried it again, same thing. And it always seems to stop at the same point in the file. I can open that file up with no problem in Word. Could this be caused by a corrupted table? I've had no other problems with the database. It's a rather large website content database however and I'm wondering if I just haven't run into any other problems yet. I'm also having this same problem with another content database -- a very small one however, and I've found no other problems with it. Viewing database dumps in Word is rather painful :) I'd also like to head off any future problems and repair them now if necessary. Thanks, Rita. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
At 10:49 AM -0600 1/25/02, Dan Nelson wrote: Which field type do I use for a simply checkbox Y,N? The BIT type seems to be the best fit. It's currently a synonym for CHAR(1), but there's a TODO item: * Optimise `BIT' type to take 1 bit (now `BIT' takes 1 char). Are you thinking MS SQL Server? I am not sure there is a bit type in MySQL? Do you mean Char(0)? Anyhow, in MS SQL Server a bit type column cannot be indexed so you are better off with CHAR(1). -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL Error : Error Mapping Failed
Hi Buddy, Anybody knows the solution of my problem ? I've written a program with Kylix 1 and MySQL 3.23 . I am using DBGRID, DBNavigator, DataSource, ClientDataSet, DataSetProvider, SQLDataSet, SQLConnection objects for my program. It queries a table an put it into DBGRID table. I am using CHAR Fieldtype for the queried table at my MySQL. It ran properly from Kylix (I have not compiled it) but the problem is now it doesn't work anymore. I don't know why. Each time I activate the ClientDataSet I always find this error message : SQL Error : Error Mapping Failed and If I retry to activate the ClientDataSet than comes another error message, namely : Unable to execute query. Could anybody be so kind telling me the solution ? Thank you very much. __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting the row with largest number in a column
Hello Richard, I have a simple query, and a problem countless people must have had, I just cannot work it out at the moment, I am new to MySQL; I hope you can help. My current statement looking at the manual. SELECT * FROM contacts WHERE age=MAX(age); I started with: mysql select * from contacts where age=(select MAX(age) from contacts); In escence I am trying to ascertain the details of the person who is oldest. Any suggestions.? Use the ORDER BY clause to show the table's records in inverted age order, then require only the first row: SELECT * FROM contacts ORDER BY age DESC LIMIT 1 Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Equivalent of an Oracle SEQUENCE in mysql?
Richard, Friday, January 25, 2002, 6:21:29 PM, you wrote: RB REALFROM: Richard Bolen [EMAIL PROTECTED] RB HOUR: 2002012518 RB Is there the equivalent of a sequence in mysql? Does anyone have an example RB of emulating sequences? There is an 'auto_increment' attribute for the column. Look at: http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tighly packed table
Yeah, I think in the end what I will do is change a lot of the columns back to char from varchar. I was thinking this would save space making for a smaller faster DB, but the inherent overhead in a varchar field is not worth the space savings, which DL made crystal clear. Thanks, Michael DL Neil wrote: Michael: see also my PS comment Dobromir: Michael and I were joking between us, hence the silly comments appearing. I apologise if this did not communicate. IMHO the pragmatics of the exercise made it a waste of time/effort - even when I over-stated the savings at every opportunity! On a Friday afternoon a little speculation and humor is a good way to start the weekend! You are 100% correct, the disk space occupied by a table is not the sum of the the length of its data-rows. For example, there is always space left for expansion/INSERTions. However in this case, because it is a R/O table, it could be squashed right down. I cannot comment if a table containing varchar/variable length fields can be compressed more or less than a table with only fixed length fields. Basically varchar allows one to potentially 'trade' disk space savings for a degradation in query response times. Some do not realise that by removing variable length fields to a 'companion table', any queries which access the (fixed-length) table without needing to look at the variable-length field(s), will speed up significantly. Thanks for providing some 'real' numbers. That was of interest. =dn - Original Message - From: Dobromir Velev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 25 January 2002 16:11 Subject: RE: Tighly packed table Hi, If your column is of type VARCHAR, you want save much space (at least not as much as DL Neil said). The specifications of tha varchar column type is that it uses as much bytes as the data in it. Of course this will make your indexes smaller (if this column is indexed). A few days before I decided to optimize one of my tables (5 milion rows) and altered a varchar(250) field to a varchar(100). The size of the MYD data file changed with less than 1Mb so you see that there was not much use of doing it. Dobromir Velev Software Developer http://www.websitepulse.com/ -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 26, 2002 5:39 PM To: Michael Stearne Cc: Michael Stearne; Roger Karnouk; [EMAIL PROTECTED] Subject: Re: Tighly packed table Michael, Let's round it up to 3 million rows (I'm lazy at math too!) Let's say you currently allow 15 bytes per name. Let's say the longest name on file is 12 characters. The exercise would save 3 bytes/row multiplied by 3M rows, ie 9MB (yes, let's ignore binary-decimal differences too) If you had two name fields (first- and family-name). Woohoo that's a potential saving of 18MB I'm also generous (to a fault) so round it up to 20MB. If you go out to buy a small PC HDD today, the smallest catalog product might be 40GB (let's assume they quote formatted capacity - they don't, but there am I being half-full/-baked again) Thus we have the ability to save 0.0005% against total capacity of a new drive. Of course, the designer might have allowed way too much name-space (pun hah!) or the table may have other 'compressible' columns. Let's go for a saving of 0.001% A new drive costs how much? Your hourly rate is how much? How long will the job take you? How many cups of coffee is that? Can the client carry the cost of all that coffee? Won't your stomach rebel at the mistreatment? Mind you, most of the above is made up - I don't have any faults! Time for me to go refill my glass (with healthy fruit juice)! =dn PS after enjoying myself, let me point out that if the 'name' fields are currently defined as variable length, this exercise would allow you to make them fixed length. If you can 'wipe out' all the variable width columns in the table, performance will improve significantly! Hahaha. This is a static database. But you are right I don't know how much this will actually help. Hard disk isn't an issue. It was just an experiment...(that I have no time for anyway!) Thanks, Michael On Friday, January 25, 2002, at 06:19 PM, DL Neil wrote: ...and because no one has been really cynical... After that query runs, then prepare for a coffee overload whilst you perform the ALTER TABLE, then get ready because if you shorten the field to (say) 12 characters/bytes the very next day, someone with a 13 character name is going to try to register! I'm wondering just how much space this 'little' exercise is going to save, either as a ratio of the size of the db, or as a ratio of HDD size? My glass is half-empty! =dn - Original Message - From: Michael Stearne [EMAIL PROTECTED] To: Roger Karnouk [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: 24 January 2002 22:58 Subject: Re: Tighly packed table The problem is, this query really hurts (I don't know if it finishes) for unindexed field for 2.9 million
Re: MySQL Checkbox table field
In the last episode (Jan 25), Michael Collins said: At 10:49 AM -0600 1/25/02, Dan Nelson wrote: Which field type do I use for a simply checkbox Y,N? The BIT type seems to be the best fit. It's currently a synonym for CHAR(1), but there's a TODO item: * Optimise `BIT' type to take 1 bit (now `BIT' takes 1 char). Are you thinking MS SQL Server? I am not sure there is a bit type in MySQL? Do you mean Char(0)? Anyhow, in MS SQL Server a bit type column cannot be indexed so you are better off with CHAR(1). Why would the MySQL TODO mention an MS SQL feature? :) mysql create table test ( myfield bit primary key ); Query OK, 0 rows affected (0.01 sec) mysql desc test; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | myfield | tinyint(1) | | PRI | 0 | | +-++--+-+-+---+ 1 row in set (0.88 sec) OK, so it's actually a synonym for tinyint(1). -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to install JDBC driver!
mm.mysql-2.0.10-you-must-unjar-me.jar this is the JDBC driver for Mysql3.23.47. Can someone tell me what is the command to unjar this file as well as how to install this on linux. mysql is on /var/lib/mysql (by default) and JDK on /home/tomcat. thanks so much --rahad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to install JDBC driver!
Rahadul Kabir wrote: mm.mysql-2.0.10-you-must-unjar-me.jar this is the JDBC driver for Mysql3.23.47. Can someone tell me what is the command to unjar this file as well as how to install this on linux. mysql is on /var/lib/mysql (by default) and JDK on /home/tomcat. thanks so much --rahad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Checking Data Integrity for Replication
If this is on a Unix flavor, you can use 'cmp' to compare the data and index files (off-line, of course). Once you know they are identical, you can simply plot the difference between the slave's update log position and the master's update log position. As long as it's always 0, the two are in sync. You can periodically retest with cmp if you don't trust replication. jamesm On Fri, 25 Jan 2002, Mitsuru Hirai wrote: Hello. What would be the most effective way to compare 2 databases to see if they are identical? This is for the replication. We would like to check if a slave DB is identical to the primary DB. Thank you very much. Mitsur Hirai - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
At 11:32 AM -0600 1/25/02, Dan Nelson wrote: Why would the MySQL TODO mention an MS SQL feature? :) Because the MySQL team wants to add a feature that is found in another DBMS? OK, so it's actually a synonym for tinyint(1). I could not find a reference to the synonym in the MySQL documentation (in the data type section). Question remains, can one effectively index tinyint(1)? Als, back to the original question, in light of bit being equivalent to tinyint(1), I suppose storing Y/N is better achieved through the use of CHAR(1). -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
In the last episode (Jan 25), Michael Collins said: At 11:32 AM -0600 1/25/02, Dan Nelson wrote: Why would the MySQL TODO mention an MS SQL feature? :) Because the MySQL team wants to add a feature that is found in another DBMS? According to the Mysql manual, the bit type was added in 3.21.12. OK, so it's actually a synonym for tinyint(1). I could not find a reference to the synonym in the MySQL documentation (in the data type section). Under Column Types (http://www.mysql.com/doc/C/o/Column_types.html): `BIT' `BOOL' `CHAR' These three are synonyms for `CHAR(1)'. Question remains, can one effectively index tinyint(1)? You didn't read the message you replied to :) mysql create table test ( myfield bit primary key ); Query OK, 0 rows affected (0.01 sec) mysql desc test; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | myfield | tinyint(1) | | PRI | 0 | | +-++--+-+-+---+ 1 row in set (0.88 sec) Note the describe command shows the field type as tinyint(1), and there is a primary key on the column. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: foreign key?
On Fri, Jan 25, 2002 at 12:33:59PM -0500 Gurhan Ozen [EMAIL PROTECTED] wrote: Hi David, First of all, whever you have a primary key on your table, do all updates according to the primary key.. So whatever the primary key for the person you wanna do update, do something like: update Contributors set Contact_ID = 1, Volunteer_ID = 13 where Contributor_ID=number; Also since the columns are integer data type, don't use quotes around them. I hope this helps.. Yep, I removed the quotes, and it worked. Here's another problem: I think I'm doing an unintentional ambiguous select: mysql select Item.Item_Description, Item.Retail_Value, Item.Bid_Description, Contributors.Name from Item, Contributors where Contributor_ID 1; I get the error: ERROR 1052: Column: 'Contributor_ID' in where clause is ambiguous So, looking again at my definitions for Contributor_ID in both tables, I see they aren't exactly alike. Is there a way I can make Contributor_ID in Item refer back to the Index (Contributor_ID) in Contributors? I gather that just making the data types the same is not enough? TIA! -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= I'm going to live forever, or die trying! -- Spider Robinson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Efficiently storing md5
On 25 Jan 2002 07:05:32 +0800, [EMAIL PROTECTED] (Steven Roussey) wrote: Does anyone have a best practices for efficiently storing md5 hash values in MySQL? --snip-- Md5 hash-- 16 bytes. char(32) binary -- 32 bytes. BIGINT -- 8 bytes --snip-- Or you can use base64, which uses 22 bytes per hash. What I use is the last 8 bytes of the hash and store it as a bigint. I use the hash only for collision detection, 64 bits will allow over 4 billion entries before the odds of a single incorrect collision reaches 50%. Since my total database is in the 10's of millions I have very little to worry about. Note: This reply was originally posted to mailing.database.mysql instead of this list. FVA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to install JDBC driver!
On Fri, 25 Jan 2002, Rahadul Kabir wrote: Date: Fri, 25 Jan 2002 12:40:35 -0500 From: Rahadul Kabir [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: How to install JDBC driver! mm.mysql-2.0.10-you-must-unjar-me.jar this is the JDBC driver for Mysql3.23.47. Can someone tell me what is the command to unjar this file as well as how to install this on linux. mysql is on /var/lib/mysql (by default) and JDK on /home/tomcat. thanks so much It needs to live in the tomcat space. On our debian system its in /var/local/comcat/tomcat_home/lib Hope this helps. --rahad - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Sincerely, William Mussatto, Senior Systems Engineer CyberStrategies, Inc ph. 909-920-9154 ext. 27 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to unjar a package!
can some one please tell me how to unjar a package, like a package which comes with .jar extension (executable file). thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Linking problems
Guy-Maurice Lepoutre writes: Hello, I am using Visual C++ 6.0 and I have some linking problems while trying to run the example program MFC_ex.cpp included in the downloading files in the mysql.com website. Here are the errors I get: Configuration: MFC_ex - Win32 Debug Linking... MFC_ex.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::~MysqlConnection(void) (??1MysqlConnection@@QAE@XZ) MFC_ex.obj : error LNK2001: unresolved external symbol class std::basic_ostreamchar,struct std::char_traitschar __cdecl operator(class std::basic_ostreamchar,struct std::char_traitschar ,class mysql_ColDataclass const_string const ) Hi! You have to link in MySQL C API library, libmysqlclient. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
update a row without affecting timestamp-type column
Hello all, I have a question which just may very well be ridiculous. In one table, we have a column of type timestamp. In normal cases, we want any changes to this row to update this timestamp (hence the nature of this datatype). However, there is one case where we do NOT want the timestamp to update if we make a change to some data in that row. Is there any way to temporarily avoid updating a timestamp type? Thanks for your time. -Ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to unjar a package!
It's like tar. I do jar xvf file.jar man jar even works! :-) Michael Rahadul Kabir wrote: can some one please tell me how to unjar a package, like a package which comes with .jar extension (executable file). thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: update a row without affecting timestamp-type column
At 13:45 -0500 1/25/02, [EMAIL PROTECTED] wrote: Hello all, I have a question which just may very well be ridiculous. In one table, we have a column of type timestamp. In normal cases, we want any changes to this row to update this timestamp (hence the nature of this datatype). However, there is one case where we do NOT want the timestamp to update if we make a change to some data in that row. Is there any way to temporarily avoid updating a timestamp type? Sure. Set it to its current value. UPDATE tbl_name SET ts_col = ts_col, other_col = new_value; Thanks for your time. -Ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting the row with largest number in a column
At 17:13 + 1/26/02, DL Neil wrote: Hello Richard, I have a simple query, and a problem countless people must have had, I just cannot work it out at the moment, I am new to MySQL; I hope you can help. My current statement looking at the manual. SELECT * FROM contacts WHERE age=MAX(age); I started with: mysql select * from contacts where age=(select MAX(age) from contacts); In escence I am trying to ascertain the details of the person who is oldest. Any suggestions.? Use the ORDER BY clause to show the table's records in inverted age order, then require only the first row: SELECT * FROM contacts ORDER BY age DESC LIMIT 1 Regards, =dn Another method, which will give you different results if more than one row has the maximum values (and which may be desireable if you want to see all such rows rather than just one) is to use a SQL variable like this: SELECT @max := MAX(age) FROM contacts; SELECT * FROM contacts where age = @max; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Named Pipes and MySQL?
Jeremy Zawodny writes: Are there platforms other than Windows (OS/2, perhaps?) on which MySQL is able to use named pipes? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 511,767,544 queries (265/sec. avg) I do not think so ... And on Windows it is only NT and W2K. But unlike Unix socket files, named pipes can be used on those OS's for remote connections too ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Inner Join Delete
Jeremy Zawodny writes: On Wed, Jan 23, 2002 at 11:25:31PM -0500, Jason Yates wrote: Heres an example scenario, say I have two tables table1 - id | name | | table2 |_ id address zip I inner join table1 and table2 on id. I want to delete all the records in table1 which have a zip of '90210'. Good choice. 90210 is first to go on my list, too. :-) I could create a script, run a select and loop through each id and delete the records in table1. If you're running MySQL 4.x, multi-table deletes: http://www.mysql.com/doc/D/E/DELETE.html may be what you need, if I understand you right. Otherwise, you've gotta write that loop. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,844,619 queries (266/sec. avg) Or use multi-table delete feature from 4.0.1 ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to unjar a package!
At 13:32 -0500 1/25/02, Rahadul Kabir wrote: can some one please tell me how to unjar a package, like a package which comes with .jar extension (executable file). thanks Just as tar tars and untars, jar jars and unjars. :-) jar xf jarfile.jar - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql warnings when write locking compressed table
=?iso-8859-2?Q?Martin_MOKREJ=A9?= writes: Hi, when I'm trying to lock multiple tables and some of the are Compressed, mysql warn's me only about the first-one being Compressed. It either reports the first Compressed table only from the list, or it checks only the first-one in the list of tables to be locked: mysql use Escherichia_coli_O157_H7_EDL933 Database changed mysql lock table blast_data write; write, blimps_data write, cogs_data write, coils_data write, contig_data write, funcat_data write, intragenome_data write, known3d_data write, nonglob_data write, orf_data write, pfam_data write, prd_data write, pros_data write, prot_data write, scop1_data write, scop2_data write, seg_data write, tmhmm_data write; ERROR 1036: Table 'blast_data' is read only mysql `myisamchk -f -d ' reports on those tables their status, and most of them were Compressed. I don't think it make's sense to paste the output here. I don't know if it's really `ERROR', because I'm locking the tables to FLUSH TABLES and then in the backgroung run `myisamchk --unpack *.MYI'. ;)[B Linux 2.4.17, /usr/local/mysql/bin/mysqld Ver 3.23.42 for pc-linux-gnu on i686 -- Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs MIPS / Institute for Bioinformatics http://mips.gsf.de GSF - National Research Center for Environment and Health Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany tel.: +49-89-3187 3616 , fax: +49-89-3187 3585 Please do not run myisamchk while the server is running. Use CHECK TABLE to check tables ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql cache err with mysqldump? was: Innodb funny error
Hi Monty! I am combining two messages into one: Heikki server is broken. Why does it claim that? This could happen if some client code set the 'thd-killed' flag. The main problem here is why we should get this in mysqldump, but not in 'mysql'. Ken, did you get any output from mysqldump (Ie, did you get the CREATE TABLE statement ?) Yes, you do get a create table statement. It ends with the LOCK statement IE: -- -- Table structure for table 'terms' -- DROP TABLE IF EXISTS terms; CREATE TABLE terms ( trmID tinyint(2) unsigned NOT NULL auto_increment, trmPrct int(5) unsigned NOT NULL default '0', trmText char(30) NOT NULL default '', PRIMARY KEY (trmID), KEY trmTextidx (trmText), KEY trmPrctidx (trmPrct) ) TYPE=MyISAM; /*!4 ALTER TABLE terms DISABLE KEYS */; -- -- Dumping data for table 'terms' -- LOCK TABLES terms WRITE; If this is a cache problem, then any insert or 'reset query cache' would invalidate the cache. True, an update or insert or alter table type clears the problem, Note however that the query cache is not on by default; If Ken didn't enable it, it should not have caused by this problem. Yes, I do have query cache turned on, that is when the problem started happening. It would have been nice to know if restarting MySQL would have helped. If not, then we would like to have had a copy of the InnoDB table space to be able to replicate the problem! Yes, restarting MySQL clears the problem. The problem happens with BOTH myisam and innodb tables I can send either or both tables to the upload site. One other thought was it could be the LOCK TABLES statement that is causing this issue, I did not think to try that at the command prompt, I will check it but as I just restarted the server it will take a few hours before I see the problem again. Hope this helps, Ken Regards, Monty - Before posting, please check: - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: foreign key?
David S. Jackson wrote: On Fri, Jan 25, 2002 at 12:33:59PM -0500 Gurhan Ozen [EMAIL PROTECTED] wrote: Hi David, First of all, whever you have a primary key on your table, do all updates according to the primary key.. So whatever the primary key for the person you wanna do update, do something like: update Contributors set Contact_ID = 1, Volunteer_ID = 13 where Contributor_ID=number; Also since the columns are integer data type, don't use quotes around them. I hope this helps.. Yep, I removed the quotes, and it worked. Here's another problem: I think I'm doing an unintentional ambiguous select: mysql select Item.Item_Description, Item.Retail_Value, Item.Bid_Description, Contributors.Name from Item, Contributors where Contributor_ID 1; WHERE Item.Contributor_ID = Contributors.Contributor_ID and Item.Contributor_ID 1; I get the error: ERROR 1052: Column: 'Contributor_ID' in where clause is ambiguous So, looking again at my definitions for Contributor_ID in both tables, I see they aren't exactly alike. Is there a way I can make Contributor_ID in Item refer back to the Index (Contributor_ID) in Contributors? I gather that just making the data types the same is not enough? TIA! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Checkbox table field
At 12:02 PM -0600 1/25/02, Dan Nelson wrote: According to the Mysql manual, the bit type was added in 3.21.12. `BIT' `BOOL' `CHAR' These three are synonyms for `CHAR(1)'. I see that now in the online docs, I am using the pdf, guess it is not as up to date. Sorry if I spoke out of turn. So why is it that when you add a field with that type you get tinyint(1). -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: foreign key?
select Item.Item_Description, Item.Retail_Value, Item.Bid_Description, Contributors.Name from Item, Contributors where Contributors.Contributor_ID 1; -Original Message- From: David S. Jackson [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 12:14 PM To: Gurhan Ozen; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: foreign key? On Fri, Jan 25, 2002 at 12:33:59PM -0500 Gurhan Ozen [EMAIL PROTECTED] wrote: Hi David, First of all, whever you have a primary key on your table, do all updates according to the primary key.. So whatever the primary key for the person you wanna do update, do something like: update Contributors set Contact_ID = 1, Volunteer_ID = 13 where Contributor_ID=number; Also since the columns are integer data type, don't use quotes around them. I hope this helps.. Yep, I removed the quotes, and it worked. Here's another problem: I think I'm doing an unintentional ambiguous select: mysql select Item.Item_Description, Item.Retail_Value, Item.Bid_Description, Contributors.Name from Item, Contributors where Contributor_ID 1; I get the error: ERROR 1052: Column: 'Contributor_ID' in where clause is ambiguous So, looking again at my definitions for Contributor_ID in both tables, I see they aren't exactly alike. Is there a way I can make Contributor_ID in Item refer back to the Index (Contributor_ID) in Contributors? I gather that just making the data types the same is not enough? TIA! -- David S. Jackson[EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= I'm going to live forever, or die trying! -- Spider Robinson - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Double foreign key references?
I have a table with two fields that reference the same field in another table. Is this allowed (I'm not sure if it is). mysql 3.23.46 allows this, but apparently, mysql 3.23.47 does not. Create it with just one key and it's fine. Reference different tables and it's fine. mysql-3.23.47 InnoDB tables under Sparc Solaris 8 mysql create table test_base ( fld int not null ); Query OK, 0 rows affected (0.07 sec) mysql create table test_fk ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_base(fld), foreign key (fld2 references test_base(fld) ); ERROR 1064: You have an error in your SQL syntax near 'references test_base(fld) )' at line 1 mysql create table test_fk ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_base(fld), foreign key (fld2) references test_base(fld) ); ERROR 1005: Can't create table './test/test_fk.frm' (errno: 150) mysql * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: foreign key?
Hi David, First of all, whever you have a primary key on your table, do all updates according to the primary key.. So whatever the primary key for the person you wanna do update, do something like: update Contributors set Contact_ID = 1, Volunteer_ID = 13 where Contributor_ID=number; Also since the columns are integer data type, don't use quotes around them. I hope this helps.. Yep, I removed the quotes, and it worked. Here's another problem: I think I'm doing an unintentional ambiguous select: mysql select Item.Item_Description, Item.Retail_Value, Item.Bid_Description, Contributors.Name from Item, Contributors where Contributor_ID 1; I get the error: ERROR 1052: Column: 'Contributor_ID' in where clause is ambiguous So, looking again at my definitions for Contributor_ID in both tables, I see they aren't exactly alike. Is there a way I can make Contributor_ID in Item refer back to the Index (Contributor_ID) in Contributors? I gather that just making the data types the same is not enough? Check out: 3.3.4.9 Using More Than one Table Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Record-level locking
A couple hundred netapps? Man, you must have a big operation. What sort of business is it? I'm also interested on a technical level. I'm flogging a business plan right now that will involve installation of a lot of remote microservers, and a central facility that will maintain replication of all the databases on all those servers. This could end up being a big complicated system. GB Well, NetApp filers are really optimized for that kind of stuff. If you use them right, they rock. We have a couple hundred of 'em. :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 20 days, processed 456,980,559 queries (263/sec. avg) -- random quote: The New York Times is read by the people who run the country. The Washington Post is read by the people who think they run the country. The National Enquirer is read by the people who think Elvis is alive and running the country ... -- Robert J Woodhead - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysqld freebsd
WITH_LINUXTHREADS=yes Use the linuxthreads pthread library. This is _NOT_ recommended for production servers. Expect problems when enabled. How fresh or stale is this information? Are there in fact problems under heavy load using linuxthreads under FreeBSD? Anyone out there using this with success? Thanks Al -Original Message- From: Ken Menzel [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 9:46 AM To: [EMAIL PROTECTED] Cc: mysql@lists. mysql. com Subject: Re: mysqld freebsd freebsd2# cd /usr/ports/databases/mysql323-server freebsd2# make pre-fetch You may use the following build options: WITH_CHARSET=charsetdefine the primary built-in charset (latin1); WITH_XCHARSET=list define other built-in charsets (may be 'all'); DB_DIR=directorySet alternate directory for database files (default is /var/db/mysql). WITH_LINUXTHREADS=yes Use the linuxthreads pthread library. This is _NOT_ recommended for production servers. Expect problems when enabled. SKIP_INSTALL_DB=yes Skip mysql_install_db (i. e. leave /var/db/mysql alone). This is useful for upgrades. Be sure to know what you are doing! SKIP_DNS_CHECK=yes don't run resolveip to do an additional DNS check before inserting local hostname to mysql database. Use if your machine has no offical DNS entry. BUILD_STATIC=yesBuild a static version of mysqld. BUILD_OPTIMIZED=yes Add -mcpu=pentiumpro -O3 to CFLAGS. This setting may produce broken code and thus is not recommended for production servers. freebsd2# You want to use the WITH_LINUXTHREADS option as well as DB_DIR (and whatever else you want) to build your mysql. Hope this helps. - Original Message - From: Oleg Prokopyev [EMAIL PROTECTED] To: Ken Menzel [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 24, 2002 5:19 AM Subject: Re: mysqld freebsd Ken Menzel wrote: Hi Oleg, There is some sort of thread problem with freebsd but it usuually is not that bad. How did you compile MySQL? I would recommend using the ports version of Mysql (cd /usr/ports/databases/mysql-3.23-server) . Look at the makefile if you are still havbing threads problems you can do you mean MIT-threads options ? :( it does not want to compile rest cut - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
alternate authorization for mysql
Is it possible to authorize mysql from an alternative source, ie ldap, passwd file, etc... many thanks don smith - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Selecting the row with largest number in a column
The fastest way I found would be: SELECT MAX(age) FROM contacts good luck bb -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Saturday, January 26, 2002 12:14 PM To: Richard Morton; [EMAIL PROTECTED] Subject: Re: Selecting the row with largest number in a column Hello Richard, I have a simple query, and a problem countless people must have had, I just cannot work it out at the moment, I am new to MySQL; I hope you can help. My current statement looking at the manual. SELECT * FROM contacts WHERE age=MAX(age); I started with: mysql select * from contacts where age=(select MAX(age) from contacts); In escence I am trying to ascertain the details of the person who is oldest. Any suggestions.? Use the ORDER BY clause to show the table's records in inverted age order, then require only the first row: SELECT * FROM contacts ORDER BY age DESC LIMIT 1 Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql warnings when write locking compressed table
On Fri, 25 Jan 2002, Sinisa Milivojevic wrote: Hi, sorry, I didn't get it. As far as I know there's no way yet how to uncompress table my using SQL command. I have to uncompress using `myisamchk --unpack *.MYI`, right? =?iso-8859-2?Q?Martin_MOKREJ=A9?= writes: Hi, when I'm trying to lock multiple tables and some of the are Compressed, mysql warn's me only about the first-one being Compressed. It either reports the first Compressed table only from the list, or it checks only the first-one in the list of tables to be locked: mysql use Escherichia_coli_O157_H7_EDL933 Database changed mysql lock table blast_data write; write, blimps_data write, cogs_data write, coils_data write, contig_data write, funcat_data write, intragenome_data write, known3d_data write, nonglob_data write, orf_data write, pfam_data write, prd_data write, pros_data write, prot_data write, scop1_data write, scop2_data write, seg_data write, tmhmm_data write; ERROR 1036: Table 'blast_data' is read only mysql `myisamchk -f -d ' reports on those tables their status, and most of them were Compressed. I don't think it make's sense to paste the output here. I don't know if it's really `ERROR', because I'm locking the tables to FLUSH TABLES and then in the backgroung run `myisamchk --unpack *.MYI'. ;)[B Linux 2.4.17, /usr/local/mysql/bin/mysqld Ver 3.23.42 for pc-linux-gnu on i686 -- Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs MIPS / Institute for Bioinformatics http://mips.gsf.de GSF - National Research Center for Environment and Health Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany tel.: +49-89-3187 3616 , fax:+49-89-3187 3585 Please do not run myisamchk while the server is running. Use CHECK TABLE to check tables ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com -- Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs MIPS / Institute for Bioinformatics http://mips.gsf.de GSF - National Research Center for Environment and Health Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany tel.: +49-89-3187 3616 , fax:+49-89-3187 3585 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
