Re: 4.1.4-gamma cannot compile
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sat, 4 Sep 2004, Paul DuBois wrote: Yes, it works for me, but we shouldn't have to do that, right? Right. I'm trying to gather information about the problem so it can be fixed. Sorry about that - I uploaded mysql-4.1.4a-gamma.tar.gz last Friday, that should resolve these compile problems. See BUG#5353 and BUG#5319 for more details on what was fixed. I will update the 4.1.4 NEWS section accordingly. Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFBPAWWSVDhKrJykfIRAiBTAJ98R4VZ4hAqGjYZL2cQxeAUXOavqgCfQzTK ljqCgF/MfOXZQFOVmEmFp/0= =MvwB -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What is bulk update ??
Hi all just a simple question ! what is bulk update anyway ? I already googling aroung , and cant find satisfying answer I'm interesting in it because my application use intensively update/insert/select real-timely .. i think this 'bulk update' can improve perfomance/throughput also an example how to do a bulk update/insert would be nice :) thanks in advance bayufa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Storing foreign characters in DB
From: [EMAIL PROTECTED] Date: Sun, 5 Sep 2004 22:39:39 -0500 (CDT) Subject: Re: Storing foreign characters in DB I am not using 3.xx versions anymore, but if I remember correctly they only allow a limited control for setting character sets. In order to be able to give you any advice you need to be more specific. Which character sets are set now for the server and the client as default ? Are the foreign characters in the same character set ? Are you attempting to store the foreign character together with your default characters in the same table / same DB ? Nils Valentin - Tokyo/Japan Nils, thanks for your response. I've done some reading recommended by you and Rhino (this is all new to me), and have some more specific questions now. First off, I am using MySQL 3.23.58 with the following setup: character set: latin1 character sets: latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620 Here's what I believe is happening: foreign characters entered into a form input field on a website are transmitted to my PHP script with UTF-8 encoding. When this form data is stored in the database as-is, I'm seeing the strange double-characters when I pull it back out of the database because MySQL is set for Latin1 encoding, which is a subset of ISO-8859-1 (right?). So, I think what I need to do is convert the character encoding of the data sent by the HTML form to ISO-8859-1 first *before* I store it in the database. When I do that and then retrieve it, foreign characters display properly. Fortunately, PHP has a character-encoding translation function called iconv() which does exactly this. Do I have this figured out correctly? I suppose if I had a newer version of MySQL with Unicode support, I would be able to store data from HTML forms directly without the translation. ? I mostly just need to support foreign characters used in French, German and Spanish mostly. So, I presume that Latin1 is all I need for now. Also, I'm storing this data all in the same database. Monty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tuning suggestion for large query
Sun, Jennifer [EMAIL PROTECTED] wrote: l consuming all my RAM and swap and being killed with error=20 'VM: kill= ing process mysql __alloc_pages: 0-order allocation failed (gfp=3D0x1d2/= 0)' I would like to find a startup parameter either for client or serv= er to limit per thread memory usage. =20 First, ensure that key_buffer is reasonable and takes, say, 50% of ram. Then for each new thread MySQL will try to allocate bulk_insert_buffer_size, read_rnd_buffer_size and sort_buffer_size. See also http://dev.mysql.com/doc/mysql/en/Server_system_variables.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad too many connections error (os x)
Michael Winston [EMAIL PROTECTED] wrote: Okay, so the first thing to try is obviously enlarge the max_connections. Have you tried this? Yes. It's set to 400 (a number we will never reach unless there's some sort of logjam). max_connect_errors is set to 200. Is it a webserver backend database? Yes. PHP-generated pages. pconnect? Apache limits? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up directly to tape.
Tucker, Gabriel [EMAIL PROTECTED] wrote: I have been searching the archives and was unable to find an answer. I need the ability to backup MySQL instances directly to a tape device. Currently, I run a mysqldump to disk and have legato pick up the file. = As I get to some larger databases, hundred's of gigs, and higher = transaction rates, I will need an online solution that goes directly to = a tape device. Currently we are using MySQL table types, though this = may change. tar? mysqlhotcopy? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL compiled with OpenSSL support in XP(not works)
Osmin Castillo [EMAIL PROTECTED] wrote: I'm having some problems connecting to the server under Winx XP with the openssl certificates. Checking the have_openssl variable says YES. When i try to connect with a user with the REQUIRE SSL grant I can't loggin and receive this error: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' Does your mysql client support SSL connections? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing MySQL 4.1 from RPM on Fedora2
Danesh Daroui [EMAIL PROTECTED] wrote: I have installed Server, Client, Benhcmark, Share, Embeded and = Compact-Share RPMs version 4.1 on a Fedora2 system. The have been = installed successfully but when I type: Shell mysql -u root it says that it can not open socket and I think it is because mysqld is = not alive. When I type: Shell mysqld it returns a fatal error and refers me to Security notes about = installation. Also, I thought that an user account which name is mysql = should be created automatically when RPMs are installed, but there is no = user account with name mysql !!! Please help me to fix the problem and = install mysql on my linux system. Maybe, you haven't removed the older mysql that comes with fedora? Let's clean your system. Save your data in a safe place, we will later remove the MySQL data directory. Issue rpm -qa | grep -i mysql And see what are the packages with mysql in it. Remove everything that has relation to MySQL itself (but of course, not packages like postfix-sasl-mysql or mod_php_mysql, etc). Ignore dependencies. After that, clean your system: remove /var/lib/mysql and delete the mysql user: userdel mysql Again, don't forget to save your data! Then do a fresh install of MySQL.com's downloaded packages. Install it like that: rpm --install -v -h MySQL*4.1.4*rpm That should help. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant tables update backward compatibility
[EMAIL PROTECTED] wrote: I have been using mysql 3.23.58, and I want to upgrade to 4.0.20. My question is this: after I run the script to upgrade the grant tables to support the new privilgeges, can I then revert back to 3.23.58 seamlessly or will I need to readjust the grant tables. Thanks in advance. You can revert back to 3.23. MySQL will just ignore the new columns. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is it possible to have Undeletable Records?
zzapper [EMAIL PROTECTED] wrote: Is it possible to have undeletable/unmodifiable records in a table of otherwise modifiable deleteable createable records? At present I do it at the Update/Delete level where I have clauses which prevent certain records being changed. I suppose I could also have an extra boolean field Record Read only, but is there anyway to specify at the data level? No, no such feature. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb space - Different Question
Stuart Felenstein [EMAIL PROTECTED] wrote: InnoDB free: 10240 kB Does this mean the actual space provided for the records it can hold ? Approximately. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two Instances
Hi Everyone. My problem is that i want to create 2 instances of a sub-query. For Example: SELECT DISTINCT V4.* FROM (SELECT v2.* FROM cell v1,cell v2 WHERE v1.name = 'reviews' AND v2.sal = v1.sal ) v3 , v4 This query will gives an error as V4 is not allowed, i.e. not more than 1 instance of a sub-query can be created. Is there a way around this problem. I would not like to make this sub-query again to get another instance of it. I need it very much, and if anyone can give me a way to do it, i would be highly grateful. Thanks in advance. Rahul Thathoo -- If real is characterized by what you see and what you can feel, then real is only electromagnetic impulses which run through you brain. -- Cheers! RAHUL THATHOO B.TECH(IT), 5th SEMESTER IIIT - ALLAHABAD PHONE: +91+532 2552505(ask for room 201) eHome: http://profile.iiita.ac.in/rthathoo_02 RYZE home: http://www.ryze.com/go/thathoo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems Compiling
Andrew Wheeler [EMAIL PROTECTED] wrote: I am not sure that this is the correct list. I did not see any list that seemed more appropriate. I have RedHat 8: gcc-3.2-7 You'd better take the binary distribution from www.mysql.com Compiling MySQL is absolutely not a configure; make; make install job. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.4 RPM Distribution startup problem
Igor Zinkovsky [EMAIL PROTECTED] wrote: error log: 040902 21:47:53 mysqld started 040902 21:47:53 [ERROR] Warning: Asked for 196608 thread stack, but got 126976 /usr/sbin/mysqld: Can't create/write to file '/root/tmp/ibezxsoi' (Errcode: 13) mysql does not have permission to write to /root/tmp Why do you have the temp directory set here? I've wrote that I have following lines in my.cnf: [mysqld] tmpdir=/var/mysqltmp Then temporary give a+rw on /root/tmp (don't forget to +x /root) and see what's mysqld_safe gonna write there. Maybe it's a bug in the script. Also please check the environment for TMPDIR. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
Ari, what's the result of
mysql -N -e SHOW VARIABLES; | grep 'ft_min_word_len'
? Default is 4 but you need to reduce this to 3 (or even to 2, if you
want to match first_name against('Al')).
Regards, Thomas Spahni
On Sun, 5 Sep 2004, Ari Denison wrote:
Hello list -
I'm trying to do a full text search for the the string May using
the following query:
SELECT * FROM students WHERE MATCH(first_name) AGAINST(May);
There are a number of students in that table wit the first_name of May.
And, yes, I've removed may from the stopwords list and have reindexed
the field but still return no records.
Any idea how to get my May students to show in full text search results?
Thanks,
Ari Denison
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select
MySQL 4.0.14 Trying to run a select statement on field (varchar) containing values (e.g. ':', '/', '\') returns blank recordset although records exist. select * from table1 where field1 = 'c:\folder\filename.extension' Is there any particular rule to follow. I inserted the same row twice and run a Distinct() returned only one row. regards ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group_concat
Hello, I have a strong need/desire to use group_concat but I don't/can't run 4.1 right now. There is MyGroupConcat http://www.codeproject.com/useritems/MyGroupConcat.asp And I have no problem creating a .so on FreeBSD but I have a server running OSX and I'm not familiar with shared code on this platform. Anybody done this? Rob
Re: Select
A Z [EMAIL PROTECTED] wrote: Trying to run a select statement on field (varchar) containing values (e.g. ':', '/', '\') returns blank recordset although records exist. select * from table1 where field1 = 'c:\folder\filename.extension' try select * from table1 where field1 = 'c:\\folder\\filename.extension' -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please explain why this query isn't optimized
Dave Dyer [EMAIL PROTECTED] wrote: Before I post it as a bug, perhaps someone can explain why this query is not optimized to use the index (it has to examine all 287k rows). mysql explain SELECT MAX(changed) FROM archived_stats where changed0; | table | type | possible_keys | key | key_len | ref | rows| Extra| | archived_stats | range | changed | changed | 4 | NULL | 2878820 | Using where; Using index | There are 2.878k rows, not 287k. And it's optimized as much as possible, the key is used. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is bulk update ??
bayufa [EMAIL PROTECTED] wrote:
also an example how to do a bulk update/insert would be nice :)
insert into tralivali (field1,field2) values
(1,2),
(3,4),
('smoke','on'),
('the','water);
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
- Original Message - From: Ari Denison [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 06, 2004 1:15 AM Subject: Full Text Stopwords Hello list - I'm trying to do a full text search for the the string May using the following query: SELECT * FROM students WHERE MATCH(first_name) AGAINST(May); There are a number of students in that table wit the first_name of May. And, yes, I've removed may from the stopwords list and have reindexed the field but still return no records. Any idea how to get my May students to show in full text search results? If the number of students named May is more than 50% of the rows, I believe May automatically becomes a new stopword. This is apparently standard behaviour for MySQL. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Database Workbench 2.5.0 released
Ladies, gentlemen, Upscene Productions is happy to announce the next version of the popular database development tool: Database Workbench 2.5.0 has been released today! Download a trial at: http://www.upscene.com Features and fixes: http://www.upscene.com/news/20040906.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, MSDE 1 2 ) - MySQL 4, 4.1 Also check Bill Todds review in the Delphi Informant: http://www.delphizine.com/productreviews/2004/01/di200401tb_p/di200401tb_p.asp If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] Version 2.5 includes new features like: * Schema Compare (also cross-database-engine) * Custom SQL statement as source in the DataPump * New Test Data Generator options * Indices, Triggers and Constraints in the Table Editor * Trace Into functionality in the Stored Procedure debugger * New image-formats support in the BLOB Editor * Shared SQL/Code Catalogs * (Basic) MS SQL Server 6.5 support and MS SQL Server owner-awareness * MySQL 4.1 authentication support Thank you your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.4 RPM Distribution startup problem
- Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 06, 2004 1:21 PM Subject: Re: MySQL 4.1.4 RPM Distribution startup problem error log: 040902 21:47:53 mysqld started 040902 21:47:53 [ERROR] Warning: Asked for 196608 thread stack, but got 126976 /usr/sbin/mysqld: Can't create/write to file '/root/tmp/ibezxsoi' (Errcode: 13) mysql does not have permission to write to /root/tmp Why do you have the temp directory set here? I've wrote that I have following lines in my.cnf: [mysqld] tmpdir=/var/mysqltmp Then temporary give a+rw on /root/tmp (don't forget to +x /root) and see what's mysqld_safe gonna write there. Maybe it's a bug in the script. Also please check the environment for TMPDIR. After this mysql started successfully but it didn't write anything in /root/tmp. #mysqladmin -u root variables | grep tmp | tmpdir | /var/mysqltmp | # stat /var/mysqltmp/ File: `/var/mysqltmp/' Size: 48 Blocks: 1 IO Block: 131072 directory Device: 306h/774d Inode: 29292 Links: 2 Access: (0755/drwxr-xr-x) Uid: ( 72/ mysql) Gid: ( 72/ mysql) Access: 2004-09-06 17:54:33.0 +0400 Modify: 2004-08-17 12:17:13.0 +0400 Change: 2004-09-06 17:55:21.0 +0400 Does MySQL need rights to /root/tmp to work well? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
per user database size quota
Hello, I would like to limit database sizes on my server on a per user basis. I haven't really found any information about this in the manual, neither in the list archive or on the web. Is there a way to do this? If mysql doesn't support this natively, is there maybe a third party patch? Thanks. Balazs Miklos. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up directly to tape.
I have seen people dump the database with mysqldump and then use a cpio command to write the files to the tape device... -Erich- -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Monday, September 06, 2004 4:35 AM To: [EMAIL PROTECTED] Subject: Re: Backing up directly to tape. Tucker, Gabriel [EMAIL PROTECTED] wrote: I have been searching the archives and was unable to find an answer. I need the ability to backup MySQL instances directly to a tape device. Currently, I run a mysqldump to disk and have legato pick up the file. = As I get to some larger databases, hundred's of gigs, and higher = transaction rates, I will need an online solution that goes directly to = a tape device. Currently we are using MySQL table types, though this = may change. tar? mysqlhotcopy? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
Ari Denison wrote: Hello list - I'm trying to do a full text search for the the string May using the following query: SELECT * FROM students WHERE MATCH(first_name) AGAINST(May); There are a number of students in that table wit the first_name of May. And, yes, I've removed may from the stopwords list and have reindexed the field but still return no records. Any idea how to get my May students to show in full text search results? Thanks, Ari Denison Others have already pointed out a couple reasons this might not work. Do I understand correctly that you have a full-text index on a single column which holds a single name per row? If so, my question is, why are using a full-text index? They aren't really designed for that situation. In other words, why not simply do SELECT * FROM students WHERE first_name = 'May'; and just put a regular index on first_name? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please explain why this query isn't optimized
Well, actually, there are 2.878 Meg rows, or 2878k. What's happening is that it's using the index to find all of the rows where changed 0, then scanning for the maximum. If you just look for the maximum, then discard it if it's not greater than 0, it will be much faster. The following with InnoDB tables. (MyISAM has the maximum pre-calculated, so it should be even faster.) mysql SELECT MAX(changed) FROM archived_stats where changed0; +--+ | MAX(changed) | +--+ |8 | +--+ 1 row in set (0.21 sec) mysql SELECT MAX(changed) as maximum FROM archived_stats having maximum 0; +-+ | maximum | +-+ | 8 | +-+ 1 row in set (0.00 sec) mysql explain SELECT MAX(changed) FROM archived_stats where changed0; ++---+---+-+-+--+--- +--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| ++---+---+-+-+--+--- +--+ | archived_stats | range | changed | changed | 4 | NULL | 52802 | Using where; Using index | ++---+---+-+-+--+--- +--+ mysql explain SELECT MAX(changed) as maximum FROM archived_stats having maximum 0; +--+ | Comment | +--+ | Select tables optimized away | +--+ You could also use the following. The idea is to get MySQL to start from the highest thing in the index, not the lowest. mysql SELECT changed FROM archived_stats where changed0 order by changed desc limit 1; +-+ | changed | +-+ | 8 | +-+ 1 row in set (0.00 sec) mysql explain SELECT changed FROM archived_stats where changed0 order by changed desc limit 1; ++---+---+-+-+--+--- +--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| ++---+---+-+-+--+--- +--+ | archived_stats | range | changed | changed | 4 | NULL | 52802 | Using where; Using index | ++---+---+-+-+--+--- +--+ 1 row in set (0.00 sec) == original message follows == From: Egor Egorov [EMAIL PROTECTED] Date: Mon, 06 Sep 2004 13:02:11 +0300 Subject: Re: please explain why this query isn't optimized To: [EMAIL PROTECTED] Dave Dyer [EMAIL PROTECTED] wrote: Before I post it as a bug, perhaps someone can explain why this query is not optimized to use the index (it has to examine all 287k rows). mysql explain SELECT MAX(changed) FROM archived_stats where changed0; | table | type | possible_keys | key | key_len | ref | rows | Extra| | archived_stats | range | changed | changed | 4 | NULL | 2878820 | Using where; Using index | There are 2.878k rows, not 287k. And it's optimized as much as possible, the key is used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FK reference PK in same table
[EMAIL PROTECTED] wrote: Hello, I have two questions: 1. I have a table defined as follows * create table MATABLE ( ID INTEGER not null, FK INTEGERnot null, constraint PK_MATABLE primary key (ID) ); alter table MATABLE add constraint FK_MATABLE_REFERENCE_MATABLE foreign key (FK) references MATABLE (ID); * I understand the FK reference the PK in the same table. My first question is can anybody give me a practical example of having the above architecture/relation. A sample of a few lines together with a short explanation would be great. An employees table where one of the fields is the ID of that employee's manager. The manager is someone else (a different row) in the employees table. 2. The above script has been generated for Oracle. Can anyone tell me how to make it work for MySQL 4? CREATE TABLE matable ( id INTEGER NOT NULL PRIMARY KEY, fk INTEGER NOT NULL, INDEX fk_idx (fk), FOREIGN KEY (fk) REFERENCES matable (id) )TYPE=InnoDB; See the manual for the details http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html. Thanks in advance, Julien. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Backup / Restore
We have built an alternative save restore process. First we take all of the .frm files and build select * into outfile '/path/tablename.txt' from tablename statements. We also dump the structure only and put it in the same directory. This runs much faster than myysqldump and every table is in its own file. The real advantage for us is that for many user errors we can quickly load the few tables into a 2nd database on the server and then reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7 seconds to save or load. We have a mixture of MyISAM and INNODB tables and this process works for both. The primary reason we went to it was we were using mysqldump and encounterd a case where the output file grew to 2GB. Restores worked fine until one day we had to do a restore and for some reason the file was corupted about half way through. The tables we were trying to get back happened to be at the end of the file and we could not find a tool to bypass the corupted data. Our database is ~ 3 GB with data and indexes and the backup takes a few minutes to run in total. I would be iterested if anyone on the list sees any issues with this as a backup/restore methodology assuming we still do lock tables and use the binary log. This approach does take some additional admin effort if we add or drop tables.
Re: 1 database; 2 scripts; different results
Michael Stassen wrote:
Amer Neely wrote:
Both scripts, which reside in the same directory on the server, are
run through the web browser. And they do access the same database, and
the same tables.
So, same web server, same mysql server, same connection settings. Good.
Does phpmyadmin connect to mysql as the same user as your admin script?
Yes.
Here are the relevant snippets. Three tables have to be accessed in
order for a record to be deleted.
$sth=$dbh-do(DELETE FROM $TITLES WHERE ArtistID = $ThisArtistID AND
TitleID = $ThisTitleID);
$sth=$dbh-do(DELETE FROM $TRACKS WHERE TitleID = $ThisTitleID AND
ArtistID = $ThisArtistID);
$sth=$dbh-do(DELETE FROM $ARTISTS WHERE ArtistID = $ThisArtistID);
Your table names are in variables?
Are you checking for errors? Also, $dh-do returns a result value, not
a statement handle. So, I'd suggest something like (using the last one
as an example):
$query = DELETE FROM $ARTISTS WHERE ArtistID = $ThisArtistID;
eval {$rows = $dbh-do($query) };
if ($@)
{ # got an error
print $query failed with error:\n;
print [EMAIL PROTECTED];
}
else
{ # query succeeded
print Deleted $rows rows from table $ARTISTS\n;
}
That is a good idea. I'll implement that and see how things shape up. Thanks.
A query in phpMyAdmin to 'select * from Artists where artistid=124' is
the query I ran.
Before I used phpMyAdmin to delete the row, it was still there, after
supposedly being deleted by my script. And yet, that same script acted
as though the row (and in fact complete record) was deleted. A query
for that particular artist, title, or any tracks came back empty.
Have you tried deleting the row in phpmyadmin? If you can, then we
can be sure it's a script problem. If you cannot, then it may be a
mysql problem.
Yes, I was able to delete the row with phpMyAdmin. And it finally
stopped appearing in both scripts.
This proves you can delete rows from your table (via phpmyadmin), and it
proves that your admin script failed to do so, despite *apparently*
working. Of course, phpmyadmin is php, while your scripts are perl. I
think that leaves a problem in your script or a problem with perl.
My bet is there's something in your admin script. I reason thusly:
1) Using phpmyadmin, you've verified mysql is working correctly, and
that you can delete rows.
2) The admin script fails to delete rows. (Every other method shows
they are still there.)
3) The admin script claims that rows that haven't been deleted are no
longer there.
Point 3 is especially troubling. Presumably, you select a row in your
admin script to delete and then you submit the form. The resulting page
from the script says the row is gone. Calling up the script later says
the row is still gone, yes? Even if you call it from a different
browser? It is as if the data is gone from the perl variables in your
script, and further runs of the script don't refresh the variables (by
getting them from mysql). That is, variables would have to persist from
one invocation to the next. So, is it mod_perl?
I don't beleive mod_perl is active. Would it should up with phpinfo()? I didn't see it. But what
you're saying describes what's happening. And yes it is indeed troubling. I've never run into this
before.
If FLUSH TABLES fixed the problem, that would mean that your mysql
server was improperly caching the row in question after it was
deleted. In other words, that would mean mysql was broken. But we
know that is not the case. If mysql were keeping the row in the
cache even though it had been deleted, it would show up in all your
clients, not all but one.
I can't run FLUSH TABLES, and the row was showing up in 1 of the 2
scripts, so I don't know how to respond to that. It seems to be a
paradox.
I think not. If mysql were improperly caching a deleted row, FLUSH
TABLES might be a workaround, but in that case the row would still show
up in ALL clients prior to running FLUSH TABLES. It doesn't, so mysql
caching is not the problem.
So, as I said, without seeing the relevant parts of your scripts, it
is impossible to do more than guess. I'll go ahead and take a shot.
There have been about 3 threads similar to this recently in which it
turned out that one script connected to the production server while
the other pointed to the development server. Have you double-checked
your connection strings to make sure they are identical?
Yes, as mentioned in this response, both scripts reside in the same
directory, and there is only 1 database they are accessing. I checked
to make sure I didn't have 2 sets of tables (upper-case, lower-case)
but that isn't the case either. I made a mistake earlier about the
mysql version. It is running on a Linux box, so case is important.
The connection code for each is identical, because one script is a
saved-as copy of the other. Only differences being in the action of
some of the form submit buttons.
OK, so that's a dead-end. Can
Re: Full Text Stopwords
the ft_min_word_length is now set to 3. That did the trick. Many thanks.
I forgot to mention that I need to be able to perform boolean searches
on the first_name field.
If it's possible to do boolean searching on a normal index, should I be
doing that instead of using a fullttext?
Thanks for the help!
Ari
On Sep 6, 2004, at 2:26 AM, Thomas Spahni wrote:
Ari, what's the result of
mysql -N -e SHOW VARIABLES; | grep 'ft_min_word_len'
? Default is 4 but you need to reduce this to 3 (or even to 2, if you
want to match first_name against('Al')).
Regards, Thomas Spahni
On Sun, 5 Sep 2004, Ari Denison wrote:
Hello list -
I'm trying to do a full text search for the the string May using
the following query:
SELECT * FROM students WHERE MATCH(first_name) AGAINST(May);
There are a number of students in that table wit the first_name of
May.
And, yes, I've removed may from the stopwords list and have reindexed
the field but still return no records.
Any idea how to get my May students to show in full text search
results?
Thanks,
Ari Denison
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
Ari Denison wrote: the ft_min_word_length is now set to 3. That did the trick. Many thanks. I forgot to mention that I need to be able to perform boolean searches on the first_name field. If it's possible to do boolean searching on a normal index, should I be doing that instead of using a fullttext? Thanks for the help! Ari That depends on what you mean by boolean searching in this context. Could you give examples? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting Data From two tables
John Nichel wrote: Hi, I'm trying to select data from a couple of tables with the same query. Easy right? Well it's driving me crazy. I'm passing an integer via an html form, using php to grab it, and insert it into the query (albumSongs.albumID = 1), to where I want to grab all the data from the first table where albumID equals the number I pass, and the data from songNames for each row that matches in albumSongs (songNamesID = songNamesID) The query executes without error, but no data is returned (the data is there, and should return if my query is right). I'm lost trying to find my mistake. mysql show columns from albumSongs; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | albumID | int(3) | | | 0 | | | songNamesID | int(4) | | | 0 | | | discNumber | tinyint(1) | | | 0 | | | subSong | int(4) | | | 0 | | | trackLength | varchar(5) | | | | | | orderBy | int(3) | | | 0 | | +-++--+-+-+---+ 6 rows in set (0.00 sec) mysql show columns from songNames; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | songNamesID | int(4) | | | 0 | | | songNamesName | varchar(196) | | | | | +---+--+--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT albumSongs.*, songNames.* FROM bytor.albumSongs, bytor.songNames WHERE albumSongs.albumID = 1 albumSongs.SongNamesID = songNames.songNamesID; Empty set (0.01 sec) I assume bytor is the name of the db. I don't understand exactly what you're doing (1 song has multiple names?), but that shouldn't matter. I would have written that query like this: SELECT * FROM bytor.albumSongs s, bytor.songNames n WHERE s.SongNamesID = n.SongNamesID AND s.albumID = 1; but that's just style. Your query is equivalent. That is, there's nothing wrong with your query that I can see. Many times (if not most), this sort of problem is because php isn't sending precisely what you think it is to mysql. That can be difficult to see in the coded query with variable names, but often jumps out at you when you see the string the way mysql does. Maybe you already did this, but my first suggestion would be to load the query into a string and print it, before using the string in mysql_query(). If that doesn't help, the only other thing I can think of is to verify that there are rows in albumSongs with albumID = 1, and verify that there is at least one row in songNames with songNamesID = a value you found in albumSongs with albumID = 1. Here's a thought. Try SELECT * FROM bytor.albumSongs s LEFT JOIN bytor.songNames n ON s.SongNamesID = n.SongNamesID WHERE s.albumID = 1; The LEFT JOIN will give you results for any row in albumSongs where albumID=1, regardless of whether or not there are matching rows in songNames. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
Sticking with the May example... I would like to be able to return results for only students named May or Maya, but not Mayra, Jessica-May, or Maylita. The other example would be simple wild cards. Something like steve* returns steve, steven, cody-steven, Stevenmikel, steve-allen. Thanks for all the help Ari On Sep 6, 2004, at 10:11 AM, Michael Stassen wrote: Ari Denison wrote: the ft_min_word_length is now set to 3. That did the trick. Many thanks. I forgot to mention that I need to be able to perform boolean searches on the first_name field. If it's possible to do boolean searching on a normal index, should I be doing that instead of using a fullttext? Thanks for the help! Ari That depends on what you mean by boolean searching in this context. Could you give examples? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: Backup / Restore
What do you do in the situation where your data has spaces in it?
Since select into outfile space seperates columns, any columns you
have with spaces in the data will not import correctly. Exampl:
mysql create table t (name varchar(100), address varchar(100));
Query OK, 0 rows affected (0.01 sec)
mysql insert into t (name, address) values ('My Name', '1243 Street st.');
Query OK, 1 row affected (0.00 sec)
mysql select * into outfile '/tmp/testdump' from t;
Query OK, 1 row affected (0.01 sec)
cat /tmp/testdump
My Name 1243 Street st.
How does your import script decide where to stop the name column and
begin the address column?
-Eric
On Mon, 6 Sep 2004 10:22:48 -0500, Gordon [EMAIL PROTECTED] wrote:
We have built an alternative save restore process. First we take all of the
.frm files and build
select * into outfile '/path/tablename.txt' from tablename
statements. We also dump the structure only and put it in the same
directory. This runs much faster than myysqldump and every table is in its
own file. The real advantage for us is that for many user errors we can
quickly load the few tables into a 2nd database on the server and then
reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7
seconds to save or load. We have a mixture of MyISAM and INNODB tables and
this process works for both. The primary reason we went to it was we were
using mysqldump and encounterd a case where the output file grew to 2GB.
Restores worked fine until one day we had to do a restore and for some
reason the file was corupted about half way through. The tables we were
trying to get back happened to be at the end of the file and we could not
find a tool to bypass the corupted data.
Our database is ~ 3 GB with data and indexes and the backup takes a few
minutes to run in total.
I would be iterested if anyone on the list sees any issues with this as a
backup/restore methodology assuming we still do lock tables and use the
binary log. This approach does take some additional admin effort if we add
or drop tables.
--
Eric Bergen
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing foreign characters in DB
Not sure whether this is applicable to your version of mysql, or to PHP. I had the same problem using Macromedia's Coldfusion, and adding this: useUnicode=truecharacterEncoding=UTF-8 to the db connection string solved the problem chris -Original Message- From: MySQL [mailto:[EMAIL PROTECTED] Sent: 05 September 2004 07:06 To: MySQL Subject: Storing foreign characters in DB I'm having a problem figuring out how to deal with foreign characters in text that was copied from an MS Word document and pasted into a form field, then stored in a MySQL DB. (I have MySQL 3.23.58 running). I'm not how sure how these characters are being stored in the MySQL database, but, when I retrieve the text and run it through PHP's htmlentities() function, each foreign character is converted into 2 other foreign characters that don't at all represent the original. For example, a lowercase u with an umlat over it (ü) is somehow displayed as an uppercase A with an umlat over it followed by the 1/4 symbol after parsed by htmlentities(). A lowercase o with an ulmat displays as an uppercase A with an umlat over it followed by the paragraph symbol. It seems that the uppercase A w/umlat is a constant, and the next character changes. How are these foreign characters being stored in the DB? Do I need to do something in order to store these characters properly, or is this something I need to somehow do on the PHP side of things?? Thanks! Monty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Segfault in mysql_real_escape_string
Hello
I'm getting a segmentation fault in the mysql function mysql_real_escape_string
and I don't have a clue why. What am I missing?
#include mysql.h /* Headers for MySQL usage */
#include stdio.h
#include stdlib.h
#include string.h
// #define INSERT_STATEMENT INSERT INTO patient (idno,first,last,medrec)
VALUES(NULL,?,?,?)
static MYSQL clinical_db;
/* static MYSQL_STMT *stmt; */
/* static MYSQL_BIND cols[3]; */
static my_ulonglong affected_rows;
/*static int param_count; */
static unsigned long str_length;
char str_data[1024];
/* static my_bool is_null; */
int main(int argc, char **argv){
int insert_id;
char *encdata, *query, *end, *value;
int datasize;
int param_count;
MYSQL_RES *res; /* To be used to fetch information into */
MYSQL_ROW row;
mysql_init(clinical_db);
if(mysql_real_connect(clinical_db, localhost, pharmacy, show22case,
clinical, 0, NULL, 0)){
fprintf(stderr, No Connection: %s\n, mysql_error(clinical_db));
exit(0);
}
printf (Hey this worked\n);
/* Prepared statements not supported in this version of MYSQL */
/* stmt = mysql_stmt_init(clinical_db); */
// if(!stmt){
// fprintf(stderr, mysql_stmt_init(), out of memory\n);
// exit(0);
// }
// if(mysql_stmt_prepare(stmt, INSERT_STATEMENT, strlen(INSERT_STATEMENT))){
// fprintf(stderr, mysql_stmt_prepare(), INSERT failed\n);
// fprintf(stderr, %s\n, mysql_stmt_error(stmt));
// }
/* Get parameter count and hope it is right */
//param_count = mysql_stmt_param_count(stmt);
//fprintf(stdout, total params are %d\n, param_count);
/* Let's try to add something to the database */
query = malloc(2048);
value = malloc(2048);
encdata = malloc(2048);
strcpy(query,INSERT INTO patient VALUES (NULL,);
str_length = strlen(query);
end = query + str_length + 1;
printf(\nFirst Name ==);
fgets( value, 26, stdin);
mysql_real_escape_string(clinical_db,encdata,value,strlen(value)); ===
Segmenetation Fault
/*end += mysql_real_escape_string(clinical_db,end,value,strlen(value));*/
*end++ = ',';
printf(\nLast Name ==);
fgets( value, 26, stdin);
end += mysql_real_escape_string(clinical_db, end,value,strlen(value) - 1);
*end++ = ',';
printf(\nMedical Record Number==);
fgets(value,12,stdin);
end += mysql_real_escape_string(clinical_db, end,value,strlen(value) - 1);
end++;
strcpy(end,));
printf(\nResulting Query %s, query);
return 0;
} /* End of main() */
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Linking Mysql tables across databases
Is there a way to create a linked mysql table? I have one master database, which has a table of data I would like to use with three other projects, I want to try to avoid copying the data between three databases, or putting everything in one database, (which could have data protection problems). I know I can do this with MS access, but can I do it in a proper database? Thanks Rob Keeling -- -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 database; 2 scripts; different results
Michael Stassen wrote:
Amer Neely wrote:
Both scripts, which reside in the same directory on the server, are
run through the web browser. And they do access the same database, and
the same tables.
So, same web server, same mysql server, same connection settings. Good.
Does phpmyadmin connect to mysql as the same user as your admin script?
Here are the relevant snippets. Three tables have to be accessed in
order for a record to be deleted.
$sth=$dbh-do(DELETE FROM $TITLES WHERE ArtistID = $ThisArtistID AND
TitleID = $ThisTitleID);
$sth=$dbh-do(DELETE FROM $TRACKS WHERE TitleID = $ThisTitleID AND
ArtistID = $ThisArtistID);
$sth=$dbh-do(DELETE FROM $ARTISTS WHERE ArtistID = $ThisArtistID);
Your table names are in variables?
Are you checking for errors? Also, $dh-do returns a result value, not
a statement handle. So, I'd suggest something like (using the last one
as an example):
$query = DELETE FROM $ARTISTS WHERE ArtistID = $ThisArtistID;
eval {$rows = $dbh-do($query) };
if ($@)
{ # got an error
print $query failed with error:\n;
print [EMAIL PROTECTED];
}
else
{ # query succeeded
print Deleted $rows rows from table $ARTISTS\n;
}
OK, I implemented your suggested error-trapping above, and now the script seems to be working fine.
A deleted record really is deleted - confirmed with phpMyAdmin. In 2 different browsers. In all 3
tables. Which of course raises the question, why didn't the records get deleted when presented the
other way?
Just so you have some context:
The 'public' script: http://www.softouch.on.ca/cgi-bin/cd/mycd-07a.pl
Thank you for tearing this apart and putting it back together correctly. I don't know if we'll ever
know what was going on with my original code.
A query in phpMyAdmin to 'select * from Artists where artistid=124' is
the query I ran.
Before I used phpMyAdmin to delete the row, it was still there, after
supposedly being deleted by my script. And yet, that same script acted
as though the row (and in fact complete record) was deleted. A query
for that particular artist, title, or any tracks came back empty.
Have you tried deleting the row in phpmyadmin? If you can, then we
can be sure it's a script problem. If you cannot, then it may be a
mysql problem.
Yes, I was able to delete the row with phpMyAdmin. And it finally
stopped appearing in both scripts.
This proves you can delete rows from your table (via phpmyadmin), and it
proves that your admin script failed to do so, despite *apparently*
working. Of course, phpmyadmin is php, while your scripts are perl. I
think that leaves a problem in your script or a problem with perl.
My bet is there's something in your admin script. I reason thusly:
1) Using phpmyadmin, you've verified mysql is working correctly, and
that you can delete rows.
2) The admin script fails to delete rows. (Every other method shows
they are still there.)
3) The admin script claims that rows that haven't been deleted are no
longer there.
Point 3 is especially troubling. Presumably, you select a row in your
admin script to delete and then you submit the form. The resulting page
from the script says the row is gone. Calling up the script later says
the row is still gone, yes? Even if you call it from a different
browser? It is as if the data is gone from the perl variables in your
script, and further runs of the script don't refresh the variables (by
getting them from mysql). That is, variables would have to persist from
one invocation to the next. So, is it mod_perl?
snip
If FLUSH TABLES fixed the problem, that would mean that your mysql
server was improperly caching the row in question after it was
deleted. In other words, that would mean mysql was broken. But we
know that is not the case. If mysql were keeping the row in the
cache even though it had been deleted, it would show up in all your
clients, not all but one.
I can't run FLUSH TABLES, and the row was showing up in 1 of the 2
scripts, so I don't know how to respond to that. It seems to be a
paradox.
I think not. If mysql were improperly caching a deleted row, FLUSH
TABLES might be a workaround, but in that case the row would still show
up in ALL clients prior to running FLUSH TABLES. It doesn't, so mysql
caching is not the problem.
So, as I said, without seeing the relevant parts of your scripts, it
is impossible to do more than guess. I'll go ahead and take a shot.
There have been about 3 threads similar to this recently in which it
turned out that one script connected to the production server while
the other pointed to the development server. Have you double-checked
your connection strings to make sure they are identical?
Yes, as mentioned in this response, both scripts reside in the same
directory, and there is only 1 database they are accessing. I checked
to make sure I didn't have 2 sets of tables (upper-case, lower-case)
but that isn't the case either. I made a mistake earlier about the
Re: Question concerning lack of binaries with OpenSSL support
At 01:50 PM 9/4/2004, you wrote: This is an issue I have seen many people ask over the last year or two, but I can't say I've ever seen a comprehensive answer (searched the archives heavily, too). I realize there are no binaries available directly from MySQL with OpenSSL support compiled in. I am curious as to why (I'm sure there is a very good reason for this as I have no doubt such binaries would be available otherwise). Under most circumstances, I would not consider compiling MySQL from scratch, based upon the number of people (MySQL developers and gurus) who have counseled against going so (citing that the precompiled binaries will almost always offer better performance and stability). However, I am faced with some security requirements from some financials customers who require that our installation of MySQL offer encryption of client connections (and slave connections as well once we migrate to 4.1). This leaves me in a position where I am going to need to go down the path of compilation (and before someone mentions it yes I've looked at doing this via stunnel etc but it has been decided that native SSL support is going to be needed to meet all the audit requirements). I suppose my questions are this: 1. What issues are preventing MySQL from distributing binaries that have OpenSSL support compiled in? 2. Are these issues something that may be resolved in the (hopefully near) future? 3. If the answers to 1 and 2 are negative, what would be the advice for compilation of MySQL (compiler version, etc) that would ensure the most successful compilation (i.e. I want to avoid sacrificing performance and/or stability). Many thanks, Doug Doug, This is only a guess, but if SSL encryption was in binary form, then MySQL could face import/export restrictions in certain countries. As it stands, each user has to recompile SSL into MySQL according to the laws of their own country. So the onus on following the law is now up to the user, not MySQL AB. At least this is my thinking. Unfortunately you can't trust a 3rd party to compile the SLL into MySQL because what if they accidentally or deliberately compiled a backdoor into SSL. Without the source you would never know. So compiling SSL directly from the open source ensures the algorithm has not been tampered with. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
time complexity
Hello. My dilemma is: which of the two is more complex(i mean the time complexity) 1 single join of n tables having t tuples each amounting to t^n tuples preceded by one Select * statement; OR n joins of 2 tables having t tuples each amounting to n*t^2 tuples n Select * statements Question ::: Whether the time complexity depends on the no of tuples in the joined table or the no. of joins and the no. of select statements? Thanks in advance. -- If real is characterized by what you see and what you can feel, then real is only electromagnetic impulses which run through you brain. -- Cheers! RAHUL THATHOO B.TECH(IT), 5th SEMESTER IIIT - ALLAHABAD PHONE: +91+532 2552505(ask for room 201) eHome: http://profile.iiita.ac.in/rthathoo_02 RYZE home: http://www.ryze.com/go/thathoo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best-performing CPU + platform for MySQL now? Opteron? OpenBSD? SuSE?
If my company wants to get the best-performing fastest platform for a MySQL server, what would it be these days? Opteron? Dual? Quad? And on a related note... If a 64-bit CPU, then I'm assuming it would need an operating system designed for that 64-bit CPU, to get best performance, right? I know that OpenBSD has an amd64 version and that the OpenBSD developers seem to say that Opteron is their favorite (and most-currently-developed) CPU. I've used OpenBSD in the past and like it a lot. Is anyone here using MySQL on OpenBSD+Opteron in a high-load situation? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1.4 gamma - Install error
Hi, I'm trying to upgrade my mysql 4.1.0 to 4.1.4 For this purpose I've uninstalled the server and client and after installing the new version I've had this error on the log 040902 21:47:53 mysqld started 040902 21:47:53 [ERROR] Warning: Asked for 196608 thread stack, but got 126976 Despite that message, everything seems to be working just fine. Is that error something I should take care of? what is the cause ? Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slave replication problem
did you get an answer to your problem yet? If not I may be able to help.. Andrew From: Jeff McKeon [mailto:[EMAIL PROTECTED] Sent: Fri 03/09/2004 15:29 To: [EMAIL PROTECTED] Subject: Slave replication problem Hello all, We had a power outage this morning and before we could shut down our main MySQL server, power to it was lost. Bad UPS battery. Long story. We replicate this server to two others. On one I get the following error... Jeff McKeon IT Manager*** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.022 Read_Master_Log_Pos: 2223919 Relay_Log_File: DB02TC07927-relay-bin.286 Relay_Log_Pos: 16361931 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_counter: 0 Exec_master_log_pos: 85068331 Relay_log_space: 18604700 1 row in set (0.00 sec) On the other I'm getting... *** 1. row *** Master_Host: 10.32.1.10 Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: DB01TC07927-bin.021 Read_Master_Log_Pos: 85079027 Relay_Log_File: mis02tc07927-relay-bin.106 Relay_Log_Pos: 4 Relay_Master_Log_File: DB01TC07927-bin.021 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 85079027 Relay_log_space: 4 1 row in set (0.00 sec) From the server.err log I have: 040903 14:00:01 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'DB01TC07927-bin.021' at position 85079027 040903 14:00:01 Error reading packet from server: Client requested master to start replication from impossible position (server_errno=1236) 040903 14:00:01 Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log 040903 14:00:01 Slave I/O thread exiting, read up to log 'DB01TC07927-bin.021', position 85079027 How do I fix these problems? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can not find file *.MYI
Hi keep getting this error ERROR 1017: Can't find file: *.MYI. What i did is simple , archived all the database files et placed them on a new server (i should have done a dump) Now i can do a desc of all the table but i can't select any data from my tables. Is there any way to recreate all the MYI files. I don't really care about the content, but i dont feel like recreating all the structure. I did look in the documentation, but it does not give any sugesiton on how to solve this issue. Thanks. Yann Larrivée pgpfVzdzbSaXh.pgp Description: signature
RE: Linking Mysql tables across databases
Hey Rob, If all the databases are on the same server you can point to every table just with DB_name.Tablename if you have the permissions. Best Regards Alejandro -Mensaje original- De: Rob Keeling [mailto:[EMAIL PROTECTED] Enviado el: Lunes, 06 de Septiembre de 2004 01:22 p.m. Para: [EMAIL PROTECTED] Asunto: Linking Mysql tables across databases Is there a way to create a linked mysql table? I have one master database, which has a table of data I would like to use with three other projects, I want to try to avoid copying the data between three databases, or putting everything in one database, (which could have data protection problems). I know I can do this with MS access, but can I do it in a proper database? Thanks Rob Keeling -- -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can not find file *.MYI
Your index files appear to have disappeared during your archive. You can rebuild them though. Have a look at the Table Maintenance and Crash Recovery section of the manual: http://dev.mysql.com/doc/mysql/en/Table_maintenance.html Pay particular attention to the REPAIR TABLE syntax. That will give you want you need. Cheers, --V Yann Larrivee wrote: Hi keep getting this error ERROR 1017: Can't find file: *.MYI. What i did is simple , archived all the database files et placed them on a new server (i should have done a dump) Now i can do a desc of all the table but i can't select any data from my tables. Is there any way to recreate all the MYI files. I don't really care about the content, but i dont feel like recreating all the structure. I did look in the documentation, but it does not give any sugesiton on how to solve this issue. Thanks. Yann Larrivée -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Without grant option?
I would prefer that 'jdoe'@'192.168.%' not be able to grant DELETE to other users...but I do want him to be able to grant SELECT. Is that possible? - - - - - - - - - - - - - - - - - - - - GRANT SHOW DATABASES ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY 'uhoh'; GRANT SELECT, INSERT, UPDATE ON test.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION; GRANT FILE ON *.* TO 'jdoe'@'192.168.%'; GRANT DELETE ON test.* TO 'jdoe'@'192.168.%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; /* --Grants for [EMAIL PROTECTED] GRANT FILE, SHOW DATABASES ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '14592d223aea00a7' GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION */ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
- Original Message - From: Mark C. Stafford [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, September 06, 2004 6:42 PM Subject: Without grant option? I would prefer that 'jdoe'@'192.168.%' not be able to grant DELETE to other users...but I do want him to be able to grant SELECT. Is that possible? - - - - - - - - - - - - - - - - - - - - GRANT SHOW DATABASES ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY 'uhoh'; GRANT SELECT, INSERT, UPDATE ON test.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION; GRANT FILE ON *.* TO 'jdoe'@'192.168.%'; GRANT DELETE ON test.* TO 'jdoe'@'192.168.%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; /* --Grants for [EMAIL PROTECTED] GRANT FILE, SHOW DATABASES ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '14592d223aea00a7' GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION */ Sure, simply write two grants: GRANT SELECT, INSERT, UPDATE ON test.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION; GRANT DELETE ON test.* TO 'jdoe'@'192.168.%'; Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
Sure, simply write two grants: GRANT SELECT, INSERT, UPDATE ON test.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION; GRANT DELETE ON test.* TO 'jdoe'@'192.168.%'; Rhino Thanks, Rhino...but the only difference between what you suggest and what I did seems to be where the linefeed appears in the second grant. What do you get when you run these commands after those grants? FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: please explain why this query isn't optimized
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bill, et al -- ...and then Bill Easton said... % % Well, actually, there are 2.878 Meg rows, or 2878k. FYI, you're both right. Americans write numbers as x,xxx,xxx.xx while Europeans typically write them as x.xxx.xxx,xx (dot as thousands separator and comma as decimal separator). HTH HAND :-D - -- David T-G [EMAIL PROTECTED] http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (FreeBSD) iD8DBQFBPPGHGb7uCXufRwARAllpAKCzmDQpbLcVa5q9MvXlo8ZxkHjM1wCfQGAb Pt1RA0WbAYLhtgjPvvU/qGE= =0wwp -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
- Original Message - From: Mark C. Stafford [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, September 06, 2004 7:11 PM Subject: Re: Without grant option? Sure, simply write two grants: GRANT SELECT, INSERT, UPDATE ON test.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION; GRANT DELETE ON test.* TO 'jdoe'@'192.168.%'; Rhino Thanks, Rhino...but the only difference between what you suggest and what I did seems to be where the linefeed appears in the second grant. What do you get when you run these commands after those grants? FLUSH PRIVILEGES; SHOW GRANTS FOR 'jdoe'@'192.168.%'; I don't know. I didn't try it, I just suggested it as something that would probably work. My main database is DB2 and that *would* work in DB2; since MySQL imitates much of what happens in DB2 - due to using the same SQL standards, not because they are trying to imitate DB2 - I just assumed it might work the same in MySQL. Regardless of what appears in the Grant tables, do the permissions work the way you want them to if you give the privileges the way I suggested? I gave some privileges to a new userid yesterday (in MySQL) and found that the User table showed the userid but had all the grant columns set to N, even for the privileges I granted. Despite that the userid worked when I used it. I suspect that the Grant tables don't always reflect reality in MySQL Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can not find file *.MYI
On September 6, 2004 18:33, V. M. Brasseur wrote: Your index files appear to have disappeared during your archive. You can rebuild them though. Have a look at the Table Maintenance and Crash Recovery section of the manual: http://dev.mysql.com/doc/mysql/en/Table_maintenance.html Pay particular attention to the REPAIR TABLE syntax. That will give you want you need. Cheers, As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use it if the `.MYI' index file is missing or if its header is corrupted. This seems to be my situation, but all my tables used to be innodb plus i am using 4.0.18 at this moment. I did change the permissions to the proper group but it did not change anything to my situation. Is there any other way to fix this without upgrading ? Thanks Yann Larrivée -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
On Mon, 6 Sep 2004 19:53:17 -0400, Rhino [EMAIL PROTECTED] wrote: I just assumed it might work the same in MySQL. That's where I started as well, Rhino. Here is a simplified version what I did, line for line: mysql /* never heard of him, right? */ - SHOW GRANTS FOR 'jdoe'@'192.168.%'; ERROR 1141: There is no such grant defined for user 'jdoe' on host '192.168.%' mysql mysql /* WITH GRANT OPTION */ - GRANT SELECT, INSERT, UPDATE - ON test.* - TO 'jdoe'@'192.168.%' - IDENTIFIED BY 'uhoh' - WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) mysql mysql /* not spreading the GRANT OPTION, right? */ - GRANT DELETE - ON test.* - TO 'jdoe'@'192.168.%'; Query OK, 0 rows affected (0.00 sec) mysql mysql /* applying the changes */ - FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql mysql /* WTF?, over */ - SHOW GRANTS FOR 'jdoe'@'192.168.%'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '14592d223aea00a7' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION | +--+ 2 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Stopwords
Ari Denison wrote:
Sticking with the May example... I would like to be able to return
results for only students named May or Maya, but not Mayra, Jessica-May,
or Maylita.
With a definite list, you can
SELECT * FROM students WHERE first_name IN ('May', 'Maya');
This would use a simple index on first_name.
The other example would be simple wild cards. Something like steve*
returns steve, steven, cody-steven, Stevenmikel, steve-allen.
Wildcards are slightly trickier. With full-text searching, the wildcard can
only come on the right, so
SELECT * FROM students
WHERE MATCH (first_name) AGAINST ('steve*' IN BOOLEAN MODE);
would match everything in your list, though perhaps not always the way you
may think. For example, cody-steven only matches because the - is a
word boundary character. That is, cody-steven is two words, cody and
steven. On the other hand, this query would not match a first_name of
Mikelsteven or esteven.
Without a full-text index, you can put wildcards on either side, but you can
only use the index if the wildcard is on the right. You'd use queries such as
SELECT * FROM students WHERE first_name LIKE 'steve%';
which would select rows where first_name *starts* with 'steve'. This will
use the index.
SELECT * FROM students WHERE first_name LIKE '%steve';
would select rows where first_name *ends* with steve, and
SELECT * FROM students WHERE first_name LIKE '%steve%';
or
SELECT * FROM students WHERE first_name RLIKE 'steve';
would select rows where first_name *contains 'steve'. These last 3 can't
use an index on first_name.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
- Original Message - From: Mark C. Stafford [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, September 06, 2004 11:11 PM Subject: Re: Without grant option? On Mon, 6 Sep 2004 19:53:17 -0400, Rhino [EMAIL PROTECTED] wrote: I just assumed it might work the same in MySQL. That's where I started as well, Rhino. Here is a simplified version what I did, line for line: mysql /* never heard of him, right? */ - SHOW GRANTS FOR 'jdoe'@'192.168.%'; ERROR 1141: There is no such grant defined for user 'jdoe' on host '192.168.%' mysql mysql /* WITH GRANT OPTION */ - GRANT SELECT, INSERT, UPDATE - ON test.* - TO 'jdoe'@'192.168.%' - IDENTIFIED BY 'uhoh' - WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) mysql mysql /* not spreading the GRANT OPTION, right? */ - GRANT DELETE - ON test.* - TO 'jdoe'@'192.168.%'; Query OK, 0 rows affected (0.00 sec) mysql mysql /* applying the changes */ - FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql mysql /* WTF?, over */ - SHOW GRANTS FOR 'jdoe'@'192.168.%'; +--- ---+ | Grants for [EMAIL PROTECTED] | +--- ---+ | GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '14592d223aea00a7' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION | +--- ---+ 2 rows in set (0.00 sec) I can't make out from the output whether the grants worked or not. The real test though is whether the GRANTs actually *work*. If you try doing a delete on the table and it works but if you can't grant the DELETE privilege to someone else, I'd say your GRANTs are working correctly regardless of what the MySQL privilege tables show. (Of course, that assumes that the SELECT, INSERT, and UPDATE also work and *can* be granted to others.) If you aren't getting the desired results, you might be better to ignore me; I may simply be wrong. [Perhaps the GRANT command works differently in MySQL than it does in DB2.] In that case, you may need to post your question again; I'll stay out of the discussion then and let people with more experience help you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
Rhino wrote: snip Regardless of what appears in the Grant tables, do the permissions work the way you want them to if you give the privileges the way I suggested? I gave some privileges to a new userid yesterday (in MySQL) and found that the User table showed the userid but had all the grant columns set to N, even for the privileges I granted. Despite that the userid worked when I used it. I suspect that the Grant tables don't always reflect reality in MySQL The grant tables do reflect reality, if you know how to read them. Only GLOBAL privileges go in the mysql.user table. DB privileges go in the db and host tables, table privileges go in tables_priv, and column privileges go in columns_priv. See the manual http://dev.mysql.com/doc/mysql/en/Privileges.html for the details. Rhino Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Without grant option?
Mark C. Stafford wrote: On Mon, 6 Sep 2004 19:53:17 -0400, Rhino [EMAIL PROTECTED] wrote: I just assumed it might work the same in MySQL. That's where I started as well, Rhino. Here is a simplified version what I did, line for line: mysql /* never heard of him, right? */ - SHOW GRANTS FOR 'jdoe'@'192.168.%'; ERROR 1141: There is no such grant defined for user 'jdoe' on host '192.168.%' mysql mysql /* WITH GRANT OPTION */ - GRANT SELECT, INSERT, UPDATE - ON test.* - TO 'jdoe'@'192.168.%' - IDENTIFIED BY 'uhoh' - WITH GRANT OPTION; Query OK, 0 rows affected (0.01 sec) mysql mysql /* not spreading the GRANT OPTION, right? */ - GRANT DELETE - ON test.* - TO 'jdoe'@'192.168.%'; Query OK, 0 rows affected (0.00 sec) mysql mysql /* applying the changes */ - FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) You do not need FLUSH PRIVILEGES when you use GRANT and REVOKE. They take care of that automatically. This doesn't hurt anything, but you only need it when you edit the grant tables directly. mysql mysql /* WTF?, over */ - SHOW GRANTS FOR 'jdoe'@'192.168.%'; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT USAGE ON *.* TO 'jdoe'@'192.168.%' IDENTIFIED BY PASSWORD '14592d223aea00a7' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'jdoe'@'192.168.%' WITH GRANT OPTION | +--+ 2 rows in set (0.00 sec) I'd suggest a quick trip to the manual, instead of speculating: | The WITH GRANT OPTION clause gives the user the ability to give to | other users any privileges the user has at the specified privilege | level. You should be careful to whom you give the GRANT OPTION | privilege, because two users with different privileges may be able to | join privileges! | | You cannot grant another user a privilege you don't have yourself; the | GRANT OPTION privilege allows you to give away only those privileges | you possess. | | Be aware that when you grant a user the GRANT OPTION privilege at a | particular privilege level, any privileges the user already possesses | (or is given in the future!) at that level are also grantable by that | user. Suppose that you grant a user the INSERT privilege on a database. | If you then grant the SELECT privilege on the database and specify WITH | GRANT OPTION, the user can give away not only the SELECT privilege, but | also INSERT. If you then grant the UPDATE privilege to the user on the | database, the user can give away INSERT, SELECT, and UPDATE. http://dev.mysql.com/doc/mysql/en/GRANT.html If you have GRANT, you can give away any privilege you have, so if jdoe should not be able to grant the delete priv, he can't have the delete priv. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb long sempahore wait
Hello all. We're having a few problems with mysql, innodb seems to be stalling and then causing a restarts at random times. I've looked through some of the old posts and seen stuff similar to this with causes ranging from incorrect memory setting to kernel problems. So I was hoping that someone with more knowledge than me would be able to take a look at the details and see if they can point me in the right direction. Below is our setup, my.cnf and the error log. I've included the start and end of the error log below because the full thing is quite long. The full log can be seen at http://equillia.net/mysql/mysql.error.txt as I've seen this asked for in several of the previous posts about this sort of problem. Thanks in advance. Toro select version(); 4.0.20-standard-log /proc/version Linux version 2.4.26-ow1 ([EMAIL PROTECTED]) (gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5)) #3 Wed Apr 28 13:39:23 NZST 2004 --- resolve_stack_dump -s /tmp/mysqld.sym -n mysql.stack 0x80720d4 handle_connections_sockets + 476 0x8250d48 inflate_blocks + 2920 0x81ed044 ha_insert_for_fold + 124 0x80f9148 innobase_shutdown_for_mysql + 104 0x824e4fc send_all_trees + 204 0x828452a _dl_signal_error + 250 --- /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock innodb_data_file_path = ibdata1:10M:autoextend:max:1900M innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 innodb_buffer_pool_size=200M innodb_additional_mem_pool_size=50M skip-locking max_user_connections=20 max_connections=100 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_cache_size=8 key_buffer=64M max_allowed_packet=16M table_cache=1024 sort_buffer=2M record_buffer=2M thread_cache=8 thread_concurrency=8 myisam_sort_buffer_size=64M server-id = 1 query_cache_size=100 query_cache_type=1 log-slow-queries=mysql-slow-query.log long_query_time=5 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/lib/mysql/mysql.error pid-file=/var/lib/mysql/mysql.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] key_buffer=256M sort_buffer=256M read_buffer=2M write_buffer=2M [myisamchk] key_buffer=256M sort_buffer=256M read_buffer=2M write_buffer=2M [mysqlhotcopy] interactive-timeout /var/lib/mysql/mysql.error 040906 10:38:00 mysqld started 040906 10:38:01 Warning: Asked for 196608 thread stack, but got 126976 040906 10:38:01 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.20-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 InnoDB: Warning: a long semaphore wait: --Thread 565262 has waited at btr0cur.c line 401 for 241.00 seconds the semaphore: S-lock on RW-latch at 0x44b394 created in file buf0buf.c line 438 number of readers 0, waiters flag 0 Last time read locked in file btr0sea.c line 767 Last time write locked in file buf0buf.c line 1401 wait is ending InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 = 040906 11:44:09 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 408, signal count 406 --Thread 565262 has waited at btr0cur.c line 401 for 245.00 seconds the semaphore: S-lock on RW-latch at 0x44b394 created in file buf0buf.c line 438 number of readers 0, waiters flag 0 Last time read locked in file btr0sea.c line 767 Last time write locked in file buf0buf.c line 1401 wait is ending --Thread 614421 has waited at btr0cur.c line 401 for 181.00 seconds the semaphore: S-lock on RW-latch at 0x4708ac created in file buf0buf.c line 438 number of readers 0, waiters flag 0 Last time read locked in file btr0pcur.c line 228 Last time write locked in file buf0buf.c line 1401 wait is ending Mutex spin waits 287, rounds 3190, OS waits 12 RW-shared spins 705, OS waits 349; RW-excl spins 47, OS waits 47 TRANSACTIONS Trx id counter 0 1599098 Purge done for trx's n:o 0 1599020 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1599004, ACTIVE 182 sec, process no 25262, OS thread id 614421 fetching rows, thread declared inside InnoDB 27 mysql tables in use 2, locked 0 MySQL thread id 1347, query id 11895 localhost outreach Sorting result SELECT logparams_s2.value as host, UNIX_TIMESTAMP(log.dt) as datetime FROM log INNER JOIN logparams_s2 ON (log.param2key=logparams_s2.param2key) WHERE (((log.user='1735' AND log.type='S'))) AND (log.user='1735') ORDER BY datetime desc LIMIT 0,2 Trx read view will not see trx with id = 0 1599005, sees 0 1598963 ---TRANSACTION 0 1598963, ACTIVE 245 sec, process no 25134, OS thread id 565262 fetching rows, thread declared inside InnoDB 70 mysql tables in use 2, locked 0 MySQL thread id 1318, query id 11735 localhost outreach Sorting result
