Re: ERROR 1062
In the last episode (Aug 27), Osman Omar said: below is my tables properties, and my last record id is 127; mysql desc emcs; ++-+--+-+++ | Field | Type| Null | Key | Default| Extra | ++-+--+-+++ | id | tinyint(6) | | PRI | NULL | auto_increment | From the manual: `TINYINT[(M)] [UNSIGNED] [ZEROFILL]' A very small integer. The signed range is `-128' to `127'. The unsigned range is `0' to `255'. What do you think happens with you hit 127? Replace that TINYINT with INTEGER. -- 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
2 bugs in mysqlaccess
Hello
First bug: The contact address is no longer existing:
BUGs can be reported by email to [EMAIL PROTECTED]
Second bug: perl does not substitute the '~' char to the home directory:
--- /tmp/mysqlaccess.orig 2002-08-27 08:25:23.0 +0200
+++ /usr/bin/mysqlaccess2002-08-27 08:20:40.0 +0200
@@ -13,7 +13,7 @@
$script = $1;
$script = 'MySQLAccess' unless $script;
$script_conf = $script.conf;
- $script_log = ~/$script.log;
+ $script_log = $ENV{'HOME'}./$script.log;
#
# information on MySQL
bye,
-christian-
Robot: php mysql
-
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: ERROR 1062
It's work thanks On Tue, 2002-08-27 at 14:24, Dan Nelson wrote: In the last episode (Aug 27), Osman Omar said: below is my tables properties, and my last record id is 127; mysql desc emcs; ++-+--+-+++ | Field | Type| Null | Key | Default| Extra | ++-+--+-+++ | id | tinyint(6) | | PRI | NULL | auto_increment | From the manual: `TINYINT[(M)] [UNSIGNED] [ZEROFILL]' A very small integer. The signed range is `-128' to `127'. The unsigned range is `0' to `255'. What do you think happens with you hit 127? Replace that TINYINT with INTEGER. -- 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
Joining Tables
Hi I would like to have a SQL query to couple all the five tables to accomplish my need. This is a search page and the user will be searching using the don_id Presently I am using a simple equi join which works fine if all the five tables contain data for user_id But if any of the tables have no value for that particular don_id, it is obvious that nothing is being displayed. I think that a left join will be better but can't get one to display results from all the five tables But since the don_id is not unique in the claim table, I don't know how to display this Also since the claim table can have same don_id any number of times, I would like to display all the claim details for that particular don_id Note: Also I may include more tables in the future.I am writing the sql query during the runtime by getting values from the user input and so I would prefer to have a single query to do this. 1.CREATE TABLE don(don_id char(20) primary key , buy_date date not null) ; 2.CREATE TABLE enduser( user_id char(20) primary key, user_name varchar(50) not null, user_addr_str varchar(150), don_id char(20) unique not null, pro_pd char(20) unique not null, maker varchar(150), cpu varchar(150)) ; 3.CREATE TABLE usersale(user_sale_id varchar(20) primary key, user_id varchar(20) not null, don_id varchar(20) unique not null, pro_pd varchar(16) unique not null) ; 4.CREATE TABLE ssale(shit_sale_id varchar(20) primary key, s_id varchar(20) not null, don_id varchar(20) unique not null , pro_pd varchar(16) unique not null); 5.CREATE TABLE claim(claim_id int auto_increment primary key, user_id varchar(20) not null, don_id varchar(20) not null, pro_pd varchar(20) not null, claim_date date not null, claim_det text not null) ; thanks in advance kayamboo suresh - 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: backup script
You need a combo of cron jobs (man crontab) and use either mysqldump or mysqlhotcopy. cron: * 3 * * * path_to_your_script script ./mysqldump --opt -A path_to_output_sql_file -u username this is just one of the many ways. - Original Message - From: Ilyas Keser [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 6:26 AM Subject: backup script Has anyone a shell script example to backup all mysql databases at 3 o'clock at the night? Thanks... ilyas - 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
Indexing question
Hi all, I would like to create an index to speed up the following query: SELECT Account, Status, count(*) From MessageStatus WHERE sentDate '(variable)' AND sentDate '(variable)' GROUP BY Account,Status ORDER BY Account sentDate is a timestamp(14), Account and Status are both varchars. The table also contains another couple of columns and already has an index on sentDate. So two questions: 1. What index should I create (my original guess was just sentDate, but now I am thinking sentDate(8),Account,Status) 2. Does the fact that I created the sentDate index, which is not being used as it does not speed up the query, detriment the performance significantly? Should I remove that index as it is not being used? The table currently has around 800,000 entries in it and grows by between 4,000 and 100,000 entries a day. Entries are deleted once they are 6 months old. Many thanks, Ben - 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
a problem about mysql-3.23.51
I compile the source code of mysql-3.23.51 on a system of solaris 8. The following error information was produced: make[2]: Entering directory `/user1/mals/chensong/share/mysql-3.23.51/libmysql' /bin/sh ../libtool --mode=link gcc -O3 -DDBUG_OFF -DHAVE_CURSES_H -I/user1/mals/chensong/share/mysql-3.23.51/include -DHAVE_RWLOCK_T -o conf_to_src conf_to_src.o -lz -lcrypt -lgen -lsocket -lnsl -lm gcc -O3 -DDBUG_OFF -DHAVE_CURSES_H -I/user1/mals/chensong/share/mysql-3.23.51/include -DHAVE_RWLOCK_T -o conf_to_src conf_to_src.o -lz -lcrypt -lgen -lsocket -lnsl -lm Undefined first referenced symbol in file main /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.1/crt1.o ld: fatal: Symbol referencing errors. No output written to conf_to_src collect2: ld returned 1 exit status make[2]: *** [conf_to_src] Error 1 make[2]: Leaving directory `/user1/mals/chensong/share/mysql-3.23.51/libmysql' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/user1/mals/chensong/share/mysql-3.23.51' make: *** [all-recursive-am] Error 2 v880:/usr3/mals/chensong/share/mysql-3.23.51cd /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.1/ What's the cause of the problem? Greetings - 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 4.0.2a Bind-Address problem
Hi all, I tried to use config. variable 'bind-address' for MySQL 4.0.2a (mysqld-max-nt) to assign a particular ip for the server. However, I got the following error: E:\mysql4.0.2a\binmysqld-max-nt --help Unknown suffix '.' used for variable 'bind-address' (value '192.168.0.176') mysqld-max-nt: Error while setting value '192.168.0.176' to 'bind-address' my .ini segment as follow: # The MySQL server [mysqld] port=3306 skip-name-resolve bind-address=192.168.0.176 Any help would be appreciated. Thank you. Regards, Michael - 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-select
Hello, I have a small(hopefully) sql-query problem. I have 2 table like this: Table member: Id number, Namevarchar, Table member_points: Id number Member__id number (fk from member_table) Round number Points number Now one example: In table one is a member called stefan. In table member_points are 5 sets for member stefan with his points for every round. Now I need one select, to give me his Name and all his points in _one_ row like this: Nameround 1 round 2 round 3 round 4 Stefan 10 15 8 12 Who can help me? Greetings and thanks, Stefan - 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
Slow select query, need some clues to speed it up please ...
Hi all, I've discovered that we have a select query that blocked all others query to this table. 99% of query used indexs, no join ... But this one is a problem : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John')' part is the problem. Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; select count(*) from MyTabe ; 4381036 Table is growing about 10 000 record a day ... So, what can i do to have better performance ? Create a big index for Field3,Field4, Field5 ? Not a very good idea i think because it'll be a very very big index and Field 3-5 are varchar or text ... But why not ... Perhaps doing 3 select, one with Field3, one with Field4 and the other with Field5 and then doing some code in the app for joining the 3 select results. Or did i miss something in the select syntax that can help me ? Thanks David - 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: sql-select
select m.Name, mp1.Points as Round1, mp2.Points as Round2, mp3.Points as Round3, mp4.Points as Round4, mp5.Points as Round5 from member m, member_points mp1, member_points mp2, member_points mp3, member_points mp4, member_points mp5 where m.Id = mp1.Member__id and m.Id = mp2.Member__id and m.Id = mp3.Member__id and m.Id = mp4.Member__id and m.Id = mp5.Member__id and m.Name = 'stefan' and mp1.Round = 1 and mp2.Round = 2 and mp3.Round = 3 and mp4.Round = 4 and mp5.Round = 5 Best regards, Mikhail. - Original Message - From: Stefan Sturm [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:31 PM Subject: sql-select Hello, I have a small(hopefully) sql-query problem. I have 2 table like this: Table member: Id number, Name varchar, Table member_points: Id number Member__id number (fk from member_table) Round number Points number Now one example: In table one is a member called stefan. In table member_points are 5 sets for member stefan with his points for every round. Now I need one select, to give me his Name and all his points in _one_ row like this: Name round 1 round 2 round 3 round 4 Stefan 10 15 8 12 Who can help me? Greetings and thanks, Stefan - 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: Slow select query, need some clues to speed it up please ...
As first step, try to optimize table with help of OPTIMIZE TABLE MyTable command. Any progress? Best regards, Mikhail. - Original Message - From: David BORDAS [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:34 PM Subject: Slow select query, need some clues to speed it up please ... Hi all, I've discovered that we have a select query that blocked all others query to this table. 99% of query used indexs, no join ... But this one is a problem : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John')' part is the problem. Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; select count(*) from MyTabe ; 4381036 Table is growing about 10 000 record a day ... So, what can i do to have better performance ? Create a big index for Field3,Field4, Field5 ? Not a very good idea i think because it'll be a very very big index and Field 3-5 are varchar or text ... But why not ... Perhaps doing 3 select, one with Field3, one with Field4 and the other with Field5 and then doing some code in the app for joining the 3 select results. Or did i miss something in the select syntax that can help me ? Thanks David - 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 4.0.2a Bind-Address problem
Michael, Tuesday, August 27, 2002, 12:40:31 PM, you wrote: MTI tried to use config. variable 'bind-address' for MySQL 4.0.2a MT (mysqld-max-nt) to assign a particular ip for the server. However, I got MT the following error: MT E:\mysql4.0.2a\binmysqld-max-nt --help MT Unknown suffix '.' used for variable 'bind-address' (value '192.168.0.176') MT mysqld-max-nt: Error while setting value '192.168.0.176' to 'bind-address' MT my .ini segment as follow: MT # The MySQL server MT [mysqld] MT port=3306 MT skip-name-resolve MT bind-address=192.168.0.176 MT It's a known bug and it is fixed in 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita 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: Slow select query, need some clues to speed it up please ...
ME As first step, try to optimize table with help of ME OPTIMIZE TABLE MyTable ME command. ME Any progress? Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable David sql,query - 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: Slow select query, need some clues to speed it up please ...
Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable And? No result? Mikhail. P.S. Can you send a copy to mysql-list, please. - Original Message - From: David BORDAS [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 1:30 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME As first step, try to optimize table with help of ME OPTIMIZE TABLE MyTable ME command. ME Any progress? Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable David - 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: Slow select query, need some clues to speed it up please ...
Check the query plan EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 1:34 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME As first step, try to optimize table with help of ME OPTIMIZE TABLE MyTable ME command. ME Any progress? Already done something like optimize : myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable David sql,query - 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: Slow select query, need some clues to speed it up please ...
DB Already done something like optimize : DB myisamchk -v -a -S --sort-records=1 ../data/jeuxvideo/MyTable ME And? No result? Some good result but nothing enough good :( I'll have a look to fulltext search, perhaps i'll find something good. I know that fulltext search only search for full word but perhaps it'll be enough ... ME P.S. Can you send a copy to mysql-list, please. Already done ... David - 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
max(id) from two tables
Can I create an integer field which automaticly look after in two tables to have the biggest id from this tables and increment this maxid and store it? thanks ilyas filter: mysql - 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: Slow select query, need some clues to speed it up please ...
From: Franz, Fa. PostDirekt MA [EMAIL PROTECTED] KF an Index wouldnt help much , because of the 'LIKE %... '. KF If a wildcart is at the beginning of the search-string , an Index KF cannot help much. Ok as i thought, index text field is a bad thing ... KF You have to think about why are there 3 fieds in your Table , KF in which it is possible that the same kind of data can appear. KF This structure forces you to query like this. KF If you cannot get rid of this , it might be helpfull to make another field KF for searching , where you concat Field 3,4 and 5 and just search on KF this field. I can't create a new field, this will increase dramatically my table growth ... KF I think it is better , if you change the way the data is inserted in your table , KF that you can search more specific - without LIKE '%...' Hmm i've think about it but is quite difficult to find a better way. All the app is well, except this search ... Thanks David sql,query - 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: Slow select query, need some clues to speed it up please ...
From: Andrew Izsof [EMAIL PROTECTED] DB I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE DB '%John')' part is the problem. AI The formula : '%John%' always sweeps through all of the records, because it AI can't utilize any indexes, sorting, etc. But if you leave the wildcard % AI from the beginning, then the optimizer quickly sorts out the relevant AI records. Of course this reduces the effectiveness of the search pattern, but AI makes the query ultra-fast. Hmm don't be enough for me :(. AI Another way to speed up the whole thing (I recommend this one, because of AI the big text areas) is using fulltext indexes. The only drawback with AI fulltext, that it can only search for full words. I'll try to have a look to this ... Thanks David - 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 newbie
* Mylin Campos I'm a newbie with mysql. I have a few question and would really appreciate it if you can help me. I'm creating a database for a payroll system. I actually finish with my entity relationship diagram. What should be the next step? I know I should create the database already but while reading the mysql documentation I've been encountering so many questions that I'm getting all confused on where to start. First, where does the priviledges come in? Specifically the alter, drop, select, insert (etc) priviledges come in? This depends on the requirements of the system you are making. If the system will have users with different roles, you can create different users in mysql with different privileges. I normally use only one mysql user, with all privileges, and then create a separate user subsystem within my application. All 'users' are accessing the database with the same priveleged mysql user account, and it is up to my application to differentiate between the individual users of the system. This is all fine when the mysql database is hidden behind an application, but if you need to give users direct access to the database (using the mysql client or phpadmin or similar), the lack of security might be a problem. If you need security on the database level, use the privilege system and create separate users for all the database user roles you can define, and then GRANT privileges to each role for each column in each table. If you can handle security on the application level, use a single privileged mysql user. (This is not The Only Way, it's just how I do it. YMMV.) Or does this concern the database administrator which is not my concern since I'm only the software developer. It depends. You should consult with your DBA. Should I create the tables and then what next? yes. It's easy to change them later, if you change your mind about something. Mysql does not a have a feature like access where you link one key to another by dragging one field to another. does this mean that linking the tables in mysql is done manually via the sql statements? That is correct, no special definition is needed. Thanks for the advice. You're welcome! :) -- Roger - 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 newbie
Hello, sorry for a late answer, but here it comes. On Tue, 27 Aug 2002, Mylin Campos wrote: I'm a newbie with mysql. I have a few question and would really appreciate it if you can help me. I'm creating a database for a payroll system. I actually finish with my entity relationship diagram. What should be the next step? I know I should create the database already but while reading the mysql documentation I've been encountering so many questions that I'm getting all confused on where to start. Right, if you have the root account or any other account that has enough privileges, the right way to start is to create your own database. For now stick with the default settings for that, they are OK. First, where does the priviledges come in? Specifically the alter, drop, select, insert (etc) priviledges come in? Or does this concern the database administrator which is not my concern since I'm only the software developer. Every time you run a query on MySQL it checks whether you have enough permissions to do so or not. If not, it returns an error. If you are the owner of your database (I suppose you are), you have all rights to that database, including ALTER, DROP, CREATE and so on. As the owner you also have SELECT, INSERT, UPDATE, DELETE at your disposal, along with others. If you read the manual beginner's section very carefully, I think you will grasp the permissions topic fairly well. It's important to remember that users are defined using username/hostname pairs. That is, for example, you can have different privileges, depending on whether you login from your work computer or your home computer, as they have different IP addresses and host names. Should I create the tables and then what next? Yep, create tables. Then insert/dump your data into them. Or do anything that suits your needs. :-) Mysql does not a have a feature like access where you link one key to another by dragging one field to another. does this mean that linking the tables in mysql is done manually via the sql statements? That's because MySQL is just a database backend, Access has the Jet engine that it uses as a separate DLL, MySQL clients are available but their current functionality is a bit limited in some cases. MySQLFront is a good one to start with. You can use Access, too, to use MySQL but all its graphical features won't work, though. phpMyAdmin is fairly efficient, but it requires you to have a web server running PHP. For most of your daily work you can use any graphical frontend you want to, but for the more advanced (mostly administrative) tasks you will want to learn enough pure SQL to accomplish those tasks more effectively. A URL for graphical MySQL frontends: http://www.mysql.com/downloads/gui-clients.html MyCC is pretty OK, but its development is still underway. Hope this helps, feel free to ask further questions as needed. Best regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - 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: Slow select query, need some clues to speed it up please ...
From: Mikhail Entaltsev [EMAIL PROTECTED] ME Check the query plan ME EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM ME MyTable WHERE ME Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR ME Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+---++-+-+-- -++ | MyTable | ref | ReplyTo_Numero,indexF | indexF | 8| const,const | 51145 | where used | 1 row in set (0.01 sec) Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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: Slow select query, need some clues to speed it up please ...
Pada Tue, 27 Aug 2002 12:34:32 +0200 David BORDAS [EMAIL PROTECTED] menulis : Hi all, I've discovered that we have a select query that blocked all others query to this table. 99% of query used indexs, no join ... But this one is a problem : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; I know that '(Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '%John')' part is the problem. have you try to remove the ORDER BY Field6 ? and compare the query time ? -- All language designers are arrogant. Goes with the territory... -- Larry Wall MySQL 3.23.51 : up 67 days, Queries : 358.251 per second (avg). -- Dicky Wahyu Purnomo - System Administrator PT FIRSTWAP : Jl Kapt. Tendean No. 34 - Jakarta Selatan 12790 Phone : +62 21 79199577 - HP : +62 8551044244 - Web : http://www.1rstwap.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
error on self join
I need to run the following query: SELECT WRK_ImplDisp_1.Livello, WRK_ImplDisp_1.Origine, WRK_ImplDisp.Padre, WRK_ImplDisp_1.Figlio, WRK_ImplDisp.QUnit*WRK_ImplDisp_1.QUnit AS Espr1, WRK_ImplDisp_1.DaAggregare, -1 AS Espr2 FROM WRK_ImplDisp INNER JOIN WRK_ImplDisp AS WRK_ImplDisp_1 ON WRK_ImplDisp.Figlio = WRK_ImplDisp_1.Padre WHERE (((WRK_ImplDisp_1.Origine)='SA1539T' And ((WRK_ImplDisp_1.Origine)=[WRK_ImplDisp].[Origine] Or (WRK_ImplDisp_1.Origine) Is Null)) AND ((WRK_ImplDisp.DaAggregare)=True) AND ((WRK_ImplDisp.Livello)=2)); but I receive an ODBC error : can't reopen table : wrk_ImplDisp1 error #1137 The table is temporary; the table is created with : CREATE TEMPORARY TABLE WRK_ImplDisp(IDIMPL INT(11) NOT NULL auto_increment,Origine CHAR(19) NOT NULL,Livello smallint NOT NULL, Padre char(19) NOT NULL,Figlio char(19) NOT NULL,QUnit REAL NOT NULL,DaAggregare tinyint NULL,FlagCompat smallint NULL,Nord int NULL, PRIMARY KEY (IDIMPL), KEY Origine (Origine), KEY Padre (Padre), KEY Figlio (Figlio), KEY OrigineLivello (Origine,Livello), KEY DaAggregare (DaAggregare),KEY FlagCompact (FlagCompat)); MySQL 3,23,52 -max ODBC 3.51 How I can solve this problem ? Today a query like this is running under MSAccess97 ; the only difference is about the temporary, which is impossible on MSAccess. Tks in advance Massimo Massimo Petrini c/o Omt spa Via Ferrero 67/a 10090 Cascine Vica (TO) Tel.+39 011 9505334 Fax +39 011 9575474 E-mail [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
Replication
Hallo all I am setting up replication. Now I am sure my servers can connect but I still have a problem. I setup my slave server to only replicate one table The error I get is copy'd underneath here. I am sure it is easey to solve I have just started thinking in loops now Tnx Mozzi +---+-+-+---++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos| Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +---+-+-+---+--+--+ | 192.168.2.1 | replicate | 3306| 60| my-rad02-bin.004 | 987471 | Yes | | | 1054 | error 'Unknown column 'status' in 'field list'' on query 'update radcheck set status = 'active' where username = 'myrad23419'' | 0| +---+-+-+---+--+--+ 1 row in set (0.00 sec) - 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: Slow select query, need some clues to speed it up please ...
Can you send result of the query, please: select * from MyTable PROCEDURE ANALYSE(); Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 2:13 PM Subject: Re: Slow select query, need some clues to speed it up please ... From: Mikhail Entaltsev [EMAIL PROTECTED] ME Check the query plan ME EXPLAIN SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM ME MyTable WHERE ME Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR ME Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; | table | type | possible_keys | key| key_len | ref | rows | Extra | ++--+---++-+-+-- -++ | MyTable | ref | ReplyTo_Numero,indexF | indexF | 8| const,const | 51145 | where used | 1 row in set (0.01 sec) Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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: Slow select query, need some clues to speed it up please ...
- Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] ME Can you send result of the query, please: ME select * from MyTable PROCEDURE ANALYSE(); Here you are : mysql select * from MyTable PROCEDURE ANALYSE(); +--+--+--++---+- --+-+--+ |Field_name |Min_length|Max_length|Empties_or_zeros|Nulls |Avg_value_or_avg_length|Std |Optimal_fieldtype | +--+--+--++---+- --+-+--+ |MyTable.Field1| 1| 7 | 0 | 0|3988722.5930 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field2| 1| 7 |573688 | 0|3212734.0355 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field3| 1| 47 | 204 | 0|8.2074 |NULL|VARCHAR(47) NOT NULL | |MyTable.Field4| 1| 50 |71 | 0|24.5052 |NULL|VARCHAR(50) NOT NULL | |MyTable.Field5| 1| 51723 | 1 | 0|174.5134 |NULL|TEXT NOT NULL | |MyTable.Field6| 12| 14 | 3809075 | 0|63688541485.2995 |0. |BIGINT(14) UNSIGNED NOT NULL | |MyTable.Field7| 1| 4 | 0 | 0|1646.5029 |2596.7715 |SMALLINT(4) UNSIGNED NOT NULL | +--+--+--++---+- --+-+--+ NB: Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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: Slow select query, need some clues to speed it up please ...
- Original Message - From: Dicky Wahyu Purnomo [EMAIL PROTECTED] DB SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE DB Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR DB Field5 LIKE '%John') ORDER BY Field6 LIMIT 0,20; DWP have you try to remove the ORDER BY Field6 ? and compare the query time ? Yes but don't change anything in most of case. Best i can do without the order by is 0.01s less than with ... Thanks David - 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
Query not returning all records
Hi,
I am using a query that returns around 13000 records from a mysql database.
The problem i am facing is, if i want to add any more columns in the query,
mysql does not return all the records. It returns only around 8000 records.
I somehow feel that it has something to do with the number of bytes returned
by a query. Is this the case ? How do i change the same ? Is there any mysql
setting?
I work on mysql 3.23.51 on WinNT.
Thanks,
Karthik
P.S. - The Queries are as under -
This is the query i run first -
select user.userid, user.name, plan.duration, plan.hour, plan.minute,
plan.status, plan.date, project.projectname,project.projectid from user,
plan, project where user.userid = plan.userid and project.projectid =
plan.projectid and plan.date = '2002-8-13' and plan.date='2002-12-31' and
user.userid in
('abhijitg','anand','anil_m','gopal','gouri','leena','maheshg','nitin','pras
ad','ramesh','rohit','samir_more','sarita','sunilp','uday','vijay','shubhada
','abhilash','aditi','ajayg','deepali','denise','nilanjana','nilashree','pra
jakta_p','priya','savio','shilpak','sindhu','usha','valiollah','geetanjalih'
,'saroop') group by plan.userid,plan.date,plan.hour
The abover returns around 13000 records
When i run the below query it only returns 8000 odd records. The only change
is adding of user.designation colum in the query.
select user.userid, user.name, user.designation, plan.duration, plan.hour,
plan.minute, plan.status, plan.date, project.projectname,project.projectid
from user, plan, project where user.userid = plan.userid and
project.projectid = plan.projectid and plan.date = '2002-8-13' and
plan.date='2002-12-31' and user.userid in
('abhijitg','anand','anil_m','gopal','gouri','leena','maheshg','nitin','pras
ad','ramesh','rohit','samir_more','sarita','sunilp','uday','vijay','shubhada
','abhilash','aditi','ajayg','deepali','denise','nilanjana','nilashree','pra
jakta_p','priya','savio','shilpak','sindhu','usha','valiollah','geetanjalih'
,'saroop') group by plan.userid,plan.date,plan.hour
-
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: Slow select query, need some clues to speed it up please ...
IMHO the problem is in this condition ...Field5 LIKE '%John'... Can you remove it from query and try again? Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 2:49 PM Subject: Re: Slow select query, need some clues to speed it up please ... - Original Message - From: Mikhail Entaltsev [EMAIL PROTECTED] ME Can you send result of the query, please: ME select * from MyTable PROCEDURE ANALYSE(); Here you are : mysql select * from MyTable PROCEDURE ANALYSE(); +--+--+--++---+- --+-+--+ |Field_name |Min_length|Max_length|Empties_or_zeros|Nulls |Avg_value_or_avg_length|Std |Optimal_fieldtype | +--+--+--++---+- --+-+--+ |MyTable.Field1| 1| 7 | 0 | 0|3988722.5930 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field2| 1| 7 |573688 | 0|3212734.0355 |0. |MEDIUMINT(7) UNSIGNED NOT NULL| |MyTable.Field3| 1| 47 | 204 | 0|8.2074 |NULL|VARCHAR(47) NOT NULL | |MyTable.Field4| 1| 50 |71 | 0|24.5052 |NULL|VARCHAR(50) NOT NULL | |MyTable.Field5| 1| 51723 | 1 | 0|174.5134 |NULL|TEXT NOT NULL | |MyTable.Field6| 12| 14 | 3809075 | 0|63688541485.2995 |0. |BIGINT(14) UNSIGNED NOT NULL | |MyTable.Field7| 1| 4 | 0 | 0|1646.5029 |2596.7715 |SMALLINT(4) UNSIGNED NOT NULL | +--+--+--++---+- --+-+--+ NB: Table desc : CREATE TABLE MyTable ( Field1 int(10) unsigned NOT NULL auto_increment, Field2 int(10) unsigned NOT NULL default '0', Field3 varchar(50) NOT NULL default '', Field4 varchar(50) NOT NULL default '', Field5 text NOT NULL, Field6 bigint(20) NOT NULL default '0', Field7 int(10) unsigned NOT NULL default '0', PRIMARY KEY (Field1), KEY ReplyTo_Numero (Field2,Field1), KEY indexF (Field7,Field2,Field6) ) TYPE=MyISAM PACK_KEYS=1; Thanks David - 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 over network error
Hi, i still have a little problem. When i do multiple connections to my database over network (via mysql) then i get strange errors. I don't know how to explain this so i'll give you an example. (it is part of my real prog so it isn't probably the simplest one) CREATE TABLE TR_TEMP (DELETE_INDEX INT NOT NULL,TMPKEY INT NOT NULL, TR_ID int NOT NULL, UNIT_ID INT UNSIGNED, DATUM DATE, START_TIME TIME, FINISH_TIME TIME, NODE_NAME varchar(20), CHECK_AMOUNT int, TAXA_SALES int, TAXA_RATE int, TAXB_SALES int, TAXB_RATE int, TR_TYPE_ID char(9)); CREATE TABLE TR_ITEMS_TEMP (DELETE_INDEX INT NOT NULL,TR_ID INT NOT NULL, PLU_ID int, SOL_PRICE int, VOIDED bool, DPH int); when you have this tables and then do something like this for i in `seq 10` ; do mysql -u username -ppassword -h someserverbutnotthesameasthisclient inserts.sql then it make some errors like this one ERROR 1064 at line 12001: You have an error in your SQL syntax near '@PE_ID) values (20700,162600,162800,020315,31205,115003,3303,207I0,0,In )' at line 1ERROR 1054 at line 3: Unknown column 'SR_ID' in 'field list' but it is crazy because it is ok on this line! test file should be for example this one http://honza.jikos.cz/sql_in.gz can you someone test it and tell me where is the problem or where this problem should be at least? Thanks a lot! Krata - 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 in making the query to use the index
Your query overloads expendituredate. Mysql now has to scqan the whole table to see if the new expenditure date will meet the where clause ( which it never will ) try DATE_FORMAT(expendituredate,%F=%b-%Y) as expdate and see what happens. kamesh jayachandran wrote: Hi all, I have a table named expenditure whose create statement is as follows, expenditure | CREATE TABLE `expenditure` ( `expenditureid` int(11) unsigned NOT NULL auto_increment, `expendituredate` date NOT NULL default '-00-00', `artifactid` smallint(5) unsigned NOT NULL default '0', `amount` float default NULL, `userid` int(11) NOT NULL default '0', PRIMARY KEY (`expenditureid`), KEY `searchbydate` (`expendituredate`), KEY `searchbyartifact` (`artifactid`) ) TYPE=MyISAM I have 988 records in this table. My query is select DATE_FORMAT(expendituredate,%d-%b-%Y) as expendituredate,sum(amount) as amount from expenditure where userid=11 and expendituredate between '2002-8-01' and '2002-8-31' group by expendituredate order by expendituredate; Total number of records that satisfy this date conditon is 283. I have index on the expendituredate but it is not used As my explain outputs the following +-+--+---+--+-+--+--+ -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+--+- + | expenditure | ALL | searchbydate | NULL |NULL | NULL | 988 | where used; Using temporary | +-+--+---+--+-+--+--+- + 1 row in set (0.06 sec) I don't know why the query goes for the full table scan.(As the total no.of rows that satisfy the where condition(date) is less that 30% of the total number of records. Can anyone enlighten me in making the query use the index searchbydate with regrads kamesh jayachandran Get your FREE web-based e-mail and newsgroup access at: http://MailAndNews.com Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser. - 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: Replication
Mozzi schrieb: Hallo all I am setting up replication. Now I am sure my servers can connect but I still have a problem. I setup my slave server to only replicate one table The error I get is copy'd underneath here. I am sure it is easey to solve I have just started thinking in loops now Tnx Mozzi +---+-+-+---++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos| Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +---+-+-+---+--+--+ | 192.168.2.1 | replicate | 3306| 60| my-rad02-bin.004 | 987471 | Yes | | | 1054 | error 'Unknown column 'status' in 'field list'' on query 'update radcheck set status = 'active' where username = 'myrad23419'' | 0| +---+-+-+---+--+--+ 1 row in set (0.00 sec) As far as I can see the message is quite obvious: On your replication machine the table radcheck has no column of the name 'status' -- Ralf Narozny Besuchen Sie uns auf der DMS-Expo. SAP, Dokumenten- management oder das komplette Office ins Portal einbinden? Wir zeigen es Ihnen - vom 3. bis 5.9. auf der Messe Essen Halle 3, Stand 3255 SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Slow select query, need some clues to speed it up please ...
From: Mikhail Entaltsev [EMAIL PROTECTED] ME IMHO the problem is in this condition ME ...Field5 LIKE '%John'... ME Can you remove it from query and try again? In fact it was Field5 LIKE '%John%', but Field5 LIKE '%John' and Field5 LIKE '%John%' don't change query speed at all except about 0.01s .. I try remove it and i was surprised, speed grow and do not decrease. Perhaps because mysql has to analyse more rows before to have enough rows ... Thanks David - 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 in making the query to use the index
Hi Gerald, I tried the following query, select DATE_FORMAT(expendituredate,%d-%b-%Y) as expdate,sum(amount) as amount from expenditure where userid=11 and expendituredate between '2002-8-01' and '2002-8-31' group by expendituredate order by expendituredate; which is not overriding the expenditure column. Still my query goes for the full table scan. With regards kamesh jayachandran Get your FREE web-based e-mail and newsgroup access at: http://MailAndNews.com Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser. - 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: Slow select query, need some clues to speed it up please ...
As I understand... After removing Field5 LIKE '%John' condition the query works much faster. But it is different query... :) I mean that queries return different results. On other hand you can't change type of Field5 because you have a row with 51723 symbols in Filed5. :( So... question: Do you actually need this condition? ;) Best regards, Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 3:31 PM Subject: Re: Slow select query, need some clues to speed it up please ... From: Mikhail Entaltsev [EMAIL PROTECTED] ME IMHO the problem is in this condition ME ...Field5 LIKE '%John'... ME Can you remove it from query and try again? In fact it was Field5 LIKE '%John%', but Field5 LIKE '%John' and Field5 LIKE '%John%' don't change query speed at all except about 0.01s .. I try remove it and i was surprised, speed grow and do not decrease. Perhaps because mysql has to analyse more rows before to have enough rows ... Thanks David - 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 in create
Can I also use a select statement in a create statement. Something like this: create table mytable ( id int not null primary key (select max(id) from mytable2), name varchar(255) ); is this possible or is there an other way to do something like this? ilyas filter: mysql - 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: mysqldump on tables which use auto_increment
Hi,
I have a database which a few tables which have a few fields set with
auto_increment and i'm having alot of trouble restoring the backup because
the backup recreates the table with the auto_increment field, but when it
tries to enter the data back into the table it cant because it is trying to
manually enter the value for the field with the auto_increment.
here's a sample of the backup sql...
CREATE TABLE ships (
ship_id bigint(20) unsigned NOT NULL auto_increment,
ship_name varchar(20),
ship_destroyed enum('Y','N') DEFAULT 'N' NOT NULL,
character_name varchar(20) DEFAULT '' NOT NULL,
password varchar(16) DEFAULT '' NOT NULL,
now here's a sample of the data it will try to enter into the table
# Dumping data for table 'ships'
#
LOCK TABLES ships WRITE;
INSERT INTO ships VALUES (1,'WebMaster','N','WebMaster','admin@xx');
UNLOCK TABLES;
if i try to restore the database from this data it always tells me there was
an error because it is trying to insert a duplicate value.
dos anyone know how to properly backup and restore a table with
auto_increment fields? or am i on the completely wrong track here?
any help appreciated.
-
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 in create
Can I also use a select statement in a create statement. Something like this: create table mytable ( id int not null primary key (select max(id) from mytable2), name varchar(255) ); is this possible or is there an other way to do something like this? ilyas filter: mysql create table ttt select a,b,c from abc; - 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
R: select in create
Hi, try this: create table mytable select max(id),space(1) name from mytable2 and then this: alter table mytable modify name varchar(255) Danilo Maurizio -Messaggio originale- Da: Ilyas Keser [mailto:[EMAIL PROTECTED]] Inviato: martedì 27 agosto 2002 15.56 A: [EMAIL PROTECTED] Oggetto: select in create Can I also use a select statement in a create statement. Something like this: create table mytable ( id int not null primary key (select max(id) from mytable2), name varchar(255) ); is this possible or is there an other way to do something like this? ilyas filter: mysql - 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: Slow select query, need some clues to speed it up please ...
From: Mikhail Entaltsev [EMAIL PROTECTED] As I understand... After removing Field5 LIKE '%John' condition the query works much faster. Nop, sorry i'm not clear, the query works mush slower ... But it is different query... :) I mean that queries return different results. Yep ... On other hand you can't change type of Field5 because you have a row with 51723 symbols in Filed5. :( I know that and that's a problem ... So... question: Do you actually need this condition? ;) I need it yes, but i'm thinking about doing 2 or 3 queries or modify some code of my app about this search ... Thanks David - 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: \T command in 4.0 broken?
Brad, Tuesday, August 27, 2002, 7:59:19 AM, you wrote: BB is this tee or \T command supposed to still work in mysql 4.x? [skip] BB should work similarly. but this is what happens: BB mysql tee /tmp/log.txt BB No outfile specified! BB the --tee=filename argument to the mysql client works fwiw... Thanks for bug report! Unfortunatly fixes doesn't come in 4.0.3, only in 4.0.4 ... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita 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: Re: Continuing LOAD LOCAL INFILE issues..
Andrew, Tuesday, August 27, 2002, 4:21:29 AM, you wrote: Stuart Low wrote: Have had continuing issues with trying to actually get LOAD LOCAL INFILE commands working At 11:45 26/08/02 +0300, Victoria Reznichenko wrote: Did you specified local-infile=1 for client, too? AM Hi, AM I'm a MySQL newbie and I've got a question on this too. AM I can't get MySQL to load a txt file, I'm using the latest Windows version. AM This may be a dumb question, but where exactly do I enter the AM local-infile=1 command? AM Do I add it into the my.ini file or does this have to be entered every time AM I start MySQL? You can do it both ways. If you want to specify local-infile=1 in my.cnf/my.ini file you can add this entry in the sections [mysqld] and [mysql] (if you use mysql command-line client) [mysqld] local-infile=1 [mysql] local-infile=1 Or you can run mysqld and client with --local-infile=1 option. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita 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 newbie
Mylin, Tuesday, August 27, 2002, 8:17:13 AM, you wrote: MC I'm a newbie with mysql. I have a few question and would really appreciate MC it if you can help me. I'm creating a database for a payroll system. I MC actually finish with my entity relationship diagram. What should be the MC next step? I know I should create the database already but while reading the MC mysql documentation I've been encountering so many questions that I'm MC getting all confused on where to start. MC First, where does the priviledges come in? Specifically the alter, drop, MC select, insert (etc) priviledges come in? Or does this concern the database MC administrator which is not my concern since I'm only the software developer. Look into database 'mysql'. It contains privilege tables. Description of tables you can find here: http://www.mysql.com/doc/en/Privileges.html Privileges for user could be granted on different levels: global, database, table, column. You can set up privileges using GRANT statement: http://www.mysql.com/doc/en/GRANT.html MC Mysql does not a have a feature like access where you link one key to MC another by dragging one field to another. does this mean that linking the MC tables in mysql is done manually via the sql statements? Yes. If you want to use more than one table you can do it like SELECT * FROM table1,table2 WHERE table1.id=table2.id http://www.mysql.com/doc/en/Multiple_tables.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 - 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 in create
Ilyas, Tuesday, August 27, 2002, 4:55:45 PM, you wrote: IK Can I also use a select statement in a create statement. Something like this: IK create table mytable IK ( IK id int not null primary key (select max(id) from mytable2), IK name varchar(255) IK ); Nope. IK is this possible or is there an other way to do something like this? What about INSERT .. SELECT ? INSERT INTO mytable SELECT MAX(id),'Some name' AS name FROM mytable2 GROUP BY name; -- 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 - 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: max(id) from two tables
Ilyas, Tuesday, August 27, 2002, 2:45:12 PM, you wrote: IK Can I create an integer field which automaticly look after in two IK tables to have the biggest id from this tables and increment this IK maxid and store it? Nope. -- 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 - 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 3.0 Driver
Does anyone know if there is a good JDBC 3.0 Driver for MySQL? I would like to use the getGeneratedKeys() method. Thanks. Mike __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.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: Allowing a whole class C to access mysql server?
The server that has the mysql DB, has a control panel adding and removing new mysql databases and mysql users all of the time. Is there any possible way to have one global grant table for all of the users coming from 192.168.1.%? I believe I attempted this a while ago, but I used an INSERT command on the host table. Not sure if that was the safest thing todo, actually it wasn't. So all valid dbs, coming from inside the network of 192.168.1.% should be allowed access to the main 192.168.1.4 mysql server. How would I just have it so ANY user can get into the server without adding a new grant for every user? Adam -Original Message- From: Lucas Saud [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 9:45 AM To: Adam Ryan Cc: [EMAIL PROTECTED] Subject: RE: Allowing a whole class C to access mysql server? Importance: High hi, How can I get this to allow access from any user with the correct passwd to the main mysql server? Any user for the 192.168.1% class with a correct passwd. It seems I can't have a wildcard for the user statement below? You could use: GRANT ALL ON *.* TO user@'192.168.1.%' hum, try to look at manual: http://www.mysql.com/doc/en/GRANT.html cheers, =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= | Lucas Marinho Saud | | E-Mail: lucas.marinho AT uol.com.br | | Phone: (+55) 21 62 282 6084 | | Location: Goiania, GO - Brazil | =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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: Slow select query, need some clues to speed it up please ...
As I understand... After removing Field5 LIKE '%John' condition the query works much faster. Nop, sorry i'm not clear, the query works mush slower ... :( That's strange. You removed condition (which can only _increase_ number of records in resultset) and query works much slower... May be occasionally anybody run another big query in parallel? in other case I have no idea... :( Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 4:28 PM Subject: Re: Slow select query, need some clues to speed it up please ... From: Mikhail Entaltsev [EMAIL PROTECTED] As I understand... After removing Field5 LIKE '%John' condition the query works much faster. Nop, sorry i'm not clear, the query works mush slower ... But it is different query... :) I mean that queries return different results. Yep ... On other hand you can't change type of Field5 because you have a row with 51723 symbols in Filed5. :( I know that and that's a problem ... So... question: Do you actually need this condition? ;) I need it yes, but i'm thinking about doing 2 or 3 queries or modify some code of my app about this search ... Thanks David - 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 update notification - Observer Pattern
Hi: If my MySQL server updates a table, does the ability exist to inform a client or clients (observers) that a table has changed. I do not want to replicate the data, I just want to know the name or names of the tables that have been updated. Thank you Phil Willemann - 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 Table size
Which Operating Systems limit MySQL to a 2GB table size? - 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 4.0 stable release
When will MySQL 4.0 be released as a stable version? - 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: Slow select query, need some clues to speed it up please ...
ME In this case... ME Can you try again? Sure. query 1 : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '% John%' ) ORDER BY Field6 LIMIT 0,20; query 2: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' ) ORDER BY Field6 LIMIT 0,20; Results : query | time 1 0.78s 2 1.20s 1 0.77s 2 1.21s 1 0.78s 2 1.22s IMHO there are 2 ways: 1. It will work much faster. 2. It won't change speed of execution significantly. Euh Third one ? Remove a condition slow the query ... Perhaps i've a problem somewhere, but where .. Perhaps with some cache variables ? I don't know. This is the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 Server have got 1Go and run only mysql ... Table have 4M rows and index. Mytable.MYD : 1109586816 bytes Mytable.MYI : 93065216 Thanks David - 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: Indexing question
Ben It would appear that the deletion of rows may be a problem. After deleting rows older than 6 months, do you optimize the table? As I understand it, mysql does not delete delete, only marks a bit for every row thats deleted. That way, delete speed is fast. However, it slows down queries and inserts. I've tested this my deleting half of the rows in a table. The before and after file size size's are equal. I don't think you need the ORDER BY Account clause. The GROUP BY Account will automatically sort it. I read it somewhere. David -Original Message- From: Ben Holness [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 1:20 AM To: Mysql z_mailing Subject: Indexing question Hi all, I would like to create an index to speed up the following query: SELECT Account, Status, count(*) From MessageStatus WHERE sentDate '(variable)' AND sentDate '(variable)' GROUP BY Account,Status ORDER BY Account sentDate is a timestamp(14), Account and Status are both varchars. The table also contains another couple of columns and already has an index on sentDate. So two questions: 1. What index should I create (my original guess was just sentDate, but now I am thinking sentDate(8),Account,Status) 2. Does the fact that I created the sentDate index, which is not being used as it does not speed up the query, detriment the performance significantly? Should I remove that index as it is not being used? The table currently has around 800,000 entries in it and grows by between 4,000 and 100,000 entries a day. Entries are deleted once they are 6 months old. Many thanks, Ben - 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: Slow select query, need some clues to speed it up please ...
Try to remove two 'LIKE' conditions. query 3: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND Field3 LIKE '%John%' ORDER BY Field6 LIMIT 0,20; What do you have now??? Mikhail. - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 5:16 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME In this case... ME Can you try again? Sure. query 1 : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '% John%' ) ORDER BY Field6 LIMIT 0,20; query 2: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' ) ORDER BY Field6 LIMIT 0,20; Results : query | time 1 0.78s 2 1.20s 1 0.77s 2 1.21s 1 0.78s 2 1.22s IMHO there are 2 ways: 1. It will work much faster. 2. It won't change speed of execution significantly. Euh Third one ? Remove a condition slow the query ... Perhaps i've a problem somewhere, but where .. Perhaps with some cache variables ? I don't know. This is the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 Server have got 1Go and run only mysql ... Table have 4M rows and index. Mytable.MYD : 1109586816 bytes Mytable.MYI : 93065216 Thanks David - 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: JDBC 3.0 Driver
Try MySQL Connector-J 3.0.0 (dev). You can download it from mysql.com. Regards, Michael -Original Message- From: Mike Duffy [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 7:41 AM To: [EMAIL PROTECTED] Subject: JDBC 3.0 Driver Does anyone know if there is a good JDBC 3.0 Driver for MySQL? I would like to use the getGeneratedKeys() method. Thanks. Mike __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.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
RE: MySql 4.0.2a Bind-Address problem
Hi Victoria, May I ask when 4.0.3 will be released? In addition, I found 4.0.x contains the max sqld as well. Are there any different between the 4.0.x and 4.0.x-MAX version? Regards, Michael -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 3:57 AM To: [EMAIL PROTECTED] Subject: Re: MySql 4.0.2a Bind-Address problem Michael, Tuesday, August 27, 2002, 12:40:31 PM, you wrote: MTI tried to use config. variable 'bind-address' for MySQL 4.0.2a MT (mysqld-max-nt) to assign a particular ip for the server. However, I got MT the following error: MT E:\mysql4.0.2a\binmysqld-max-nt --help MT Unknown suffix '.' used for variable 'bind-address' (value '192.168.0.176') MT mysqld-max-nt: Error while setting value '192.168.0.176' to 'bind-address' MT my .ini segment as follow: MT # The MySQL server MT [mysqld] MT port=3306 MT skip-name-resolve MT bind-address=192.168.0.176 MT It's a known bug and it is fixed in 4.0.3 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita 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 - 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: JDBC 3.0 Driver
Does anyone know if there is a good JDBC 3.0 Driver for MySQL? I would like to use the getGeneratedKeys() method. The mm.mysql driver has been taken over (with it's author), renamed (but not the author) to Connector/J, and enhanced to 3.0 level. It is still Beta at the moment, but supports getGeneratedKeys(). See the MySQL website - which seems inaccessible at the instant of writing, or I would give you a URL. Alec - 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: JDBC 3.0 Driver
Mike Duffy wrote: Does anyone know if there is a good JDBC 3.0 Driver for MySQL? I would like to use the getGeneratedKeys() method. Thanks. Mike __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.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 MM.MySQL (which has now become MySQL Connector/J) has this. See http://mmmysql.sourceforge.net/ for more information. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ 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: Inserting a text or Word file into mysql
On Mon, 26 Aug 2002, Donald J Miller wrote: Hello everyone, I am trying to create a mysql table to hold either the entire contents of text or WordPerfect files. I've read about BLOB or TEXT type columns so I've tried creating a table such as: CREATE TABLE filetest(fileid int(5) not null, file BLOB null); so far, so good ... it will not store more than 65535 bytes in file I 've created the table and have used LOAD DATA in order to populate the database however I get numerous errors. WORD documents are binary files. Using LOAD DATA you have to escape them properly. Have a look at the LOAD_FILE(file_name) function instead. Can someone shed some light as to how to do this correctly? What would be the proper query to retrieve this file out of mysql? SELECT file FROM filetest WHERE fileid = 12345; Regards, Thomas - 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 4.0 stable release
On Tue, Aug 27, 2002 at 09:17:46AM -0600, Scott Pippin wrote: When will MySQL 4.0 be released as a stable version? Where there have been no reported bugs for a sufficient amount of time. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 21 days, processed 426,301,880 queries (231/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
mysql auto-increment
I would like to know if it is possible to configure mysql auo increment feild to start from a specific number or configure it to have a specific character such as a letter inserted infront of it. Thanks I have no respect for reality as soon as it is acknowledged as such. I am interested in what I can do with unacknowledged reality. Elias Canetti - 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 Table size
AIX if you didnt set the Large File System on, and some Linux Kernels. However, they dont limit anything but any file in the system to that size. For instance, the same see the OS if you have a 2,5 GB (not allowed - too big) or a 2,5 db file (the same reason) thats why there are out there some work arounds, like the Innodb. I hope this will make you an idea. Leonardo Javier Beln. AFIP-AR. - Original Message - From: Scott Pippin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:17 PM Subject: MySQL Table size Which Operating Systems limit MySQL to a 2GB table size? - 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
How to repeat.
Dear Sir/Madam, In dos-prompt, I type at c:\mysql\binmysql for testing to see if mysql is connected successful but the error is : Error 2003: Can't connect to MySQL server on 'localhost' 10061 How to solve this problem? Hope to hear from you soon. Thank you. Regards Ellen - 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: NULL ?
On Tue, 27 Aug 2002, Will K. wrote:
hello,
Please dont tell me to RTFM on this one (cause I am already doing that), but
for clarity's sake... can someone tell me what it means when you use NULL
and NOT NULL in a query (specifically CREATE TABLE)? Also, when should I
use them?
Will,
let's consider this:
an UNSIGNED TINYINT may have any value from 0 ... 255
setting it to NULL is different from 0. It is sort of a 256th possible
state. This is normally used to indicate that no value at all has been
assigned, not even '0'. For strings it is the same.
Thomas
--
BTW: (from the manual)
`NULL' Values
.
The `NULL' value means no data and is different from values such as
`0' for numeric types or the empty string for string types. *Note
Problems with `NULL': Problems with NULL.
`NULL' may be represented by `\N' when using the text file import or
export formats (`LOAD DATA INFILE', `SELECT ... INTO OUTFILE'). *Note
`LOAD DATA': LOAD DATA.
Problems with `NULL' Values
---
The concept of the `NULL' value is a common source of confusion for
newcomers to SQL, who often think that `NULL' is the same thing as an
empty string `'. This is not the case! For example, the following
statements are completely different:
mysql INSERT INTO my_table (phone) VALUES (NULL);
mysql INSERT INTO my_table (phone) VALUES ();
Both statements insert a value into the `phone' column, but the first
inserts a `NULL' value and the second inserts an empty string. The
meaning of the first can be regarded as phone number is not known and
the meaning of the second can be regarded as she has no phone.
In SQL, the `NULL' value is always false in comparison to any other
value, even `NULL'. An expression that contains `NULL' always produces
a `NULL' value unless otherwise indicated in the documentation for the
operators and functions involved in the expression. All columns in the
following example return `NULL':
mysql SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
If you want to search for column values that are `NULL', you cannot use
the `=NULL' test. The following statement returns no rows, because
`expr = NULL' is FALSE, for any expression:
mysql SELECT * FROM my_table WHERE phone = NULL;
To look for `NULL' values, you must use the `IS NULL' test. The
following shows how to find the `NULL' phone number and the empty phone
number:
mysql SELECT * FROM my_table WHERE phone IS NULL;
mysql SELECT * FROM my_table WHERE phone = ;
Note that you can only add an index on a column that can have `NULL'
values if you are using MySQL Version 3.23.2 or newer and are using the
`MyISAM' or `InnoDB' table type. In earlier versions and with other
table types, you must declare such columns `NOT NULL'. This also means
you cannot then insert `NULL' into an indexed column.
When reading data with `LOAD DATA INFILE', empty columns are updated
with `'''. If you want a `NULL' value in a column, you should use `\N'
in the text file. The literal word `'NULL'' may also be used under some
circumstances. *Note `LOAD DATA': LOAD DATA.
When using `ORDER BY', `NULL' values are presented first. If you sort
in descending order using `DESC', `NULL' values are presented last.
When using `GROUP BY', all `NULL' values are regarded as equal.
To help with `NULL' handling, you can use the `IS NULL' and `IS NOT
NULL' operators and the `IFNULL()' function.
For some column types, `NULL' values are handled specially. If you
insert `NULL' into the first `TIMESTAMP' column of a table, the current
date and time is inserted. If you insert `NULL' into an
`AUTO_INCREMENT' column, the next number in the sequence is inserted.
Working with `NULL' Values
..
The `NULL' value can be surprising until you get used to it.
Conceptually, `NULL' means missing value or unknown value and it is
treated somewhat differently than other values. To test for `NULL',
you cannot use the arithmetic comparison operators such as `=', `', or
`'. To demonstrate this for yourself, try the following query:
mysql SELECT 1 = NULL, 1 NULL, 1 NULL, 1 NULL;
+--+---+--+--+
| 1 = NULL | 1 NULL | 1 NULL | 1 NULL |
+--+---+--+--+
| NULL | NULL | NULL | NULL |
+--+---+--+--+
Clearly you get no meaningful results from these comparisons. Use the
`IS NULL' and `IS NOT NULL' operators instead:
Clearly you get no meaningful results from these comparisons. Use the
`IS NULL' and `IS NOT NULL' operators instead:
mysql SELECT 1 IS NULL, 1 IS NOT NULL;
+---+---+
| 1 IS NULL | 1 IS NOT NULL |
+---+---+
| 0 | 1 |
+---+---+
Note that two `NULL' are compared as equal is when you do an `GROUP BY'.
In MySQL, 0 or `NULL' means false and anything else means
auotincrement
I would like to know if it is possible to configure mysql auto increment field to start at a specific number or to configure the auto increment field with a character such as a letter inserted infont of it(eg. r1, r2..) Thanks F belfon - 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 4.0 stable release
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tuesday 27 August 2002 17:17, Scott Pippin wrote: When will MySQL 4.0 be released as a stable version? As soon as it actually *is* stable :) On a more serious note, we are currently working on preparing the first release of 4.0 (4.0.3) that will be declared Beta instead of Alpha. That means that we will not add any new features to the 4.0.x code tree anymore, but instead focus on fixing the remaining bugs in this release. As soon as most bugs have been ironed out and it proves to run stable for most of our userbase, it will be declared gamma and then stable. See http://www.mysql.com/doc/en/Which_version.html for details. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9a6s4SVDhKrJykfIRAqbVAJ9t+N6RLGubeMx/nqzY7ORX42HXswCfYVkg w5QfgrNmRC/hUcwuXF6qsNU= =JtjI -END PGP SIGNATURE- - 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 do I recover a broken table?
Hi I have a table that returns the following error message as soon as I run a query that reads out more then 300 posts from the table ERROR 1030: Got error -1 from table handler The strange part is that the table works without any problem except when I try to select more then 300 posts. I have tried to use the analyze and recover commands but I only get the message that the table doesn't support these commands. Happy for any help I can get to try recover the table without having to read out all the data and recreate the table. /Magnus - 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
Fw: MySQL Table size
AIX if you didnt set the Large File System on, and some Linux Kernels. However, they dont limit anything but any file in the system to that size. For instance, the same see the OS if you have a 2,5 GB (not allowed - too big) or a 2,5 db file (the same reason) thats why there are out there some work arounds, like the Innodb. I hope this will make you an idea. Leonardo Javier Beln. AFIP-AR. - Original Message - From: Scott Pippin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 12:17 PM Subject: MySQL Table size Which Operating Systems limit MySQL to a 2GB table size? - 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 auto-increment
According to the book I'm working from right now, you can't do this directly. What it suggests is that you insert the first row in your table manually and insert the integer that you want to start at. AUTO_INCREMENT will then use this number as its starting point and continue upwards. I haven't tried this yet, so I don't know if it works... Gavin Alexander From: [EMAIL PROTECTED] Date: Tue, 27 Aug 2002 06:22:23 -0600 To: [EMAIL PROTECTED] Subject: mysql auto-increment I would like to know if it is possible to configure mysql auo increment feild to start from a specific number or configure it to have a specific character such as a letter inserted infront of it. Thanks I have no respect for reality as soon as it is acknowledged as such. I am interested in what I can do with unacknowledged reality. Elias Canetti - 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 Table size
Scott Pippin wrote: Which Operating Systems limit MySQL to a 2GB table size? - 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 hello, Linux 2.2.x at one time, but that may have changed. If you want to use Linux, use the 2.4.x kernel. I would recommend RedHat 7.2 or 7.3 (Oracle has been certified on 7.2 which helps give that warm fuzzy feeling). RedHat's kernels are also built from Alan Cox's kernel tree which contains misc. performance patches. walt - 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: Inserting a text or Word file into mysql
Thomas Spahni wrote: On Mon, 26 Aug 2002, Donald J Miller wrote: Hello everyone, I am trying to create a mysql table to hold either the entire contents of text or WordPerfect files. I've read about BLOB or TEXT type columns so I've tried creating a table such as: CREATE TABLE filetest(fileid int(5) not null, file BLOB null); so far, so good ... it will not store more than 65535 bytes in file I 've created the table and have used LOAD DATA in order to populate the database however I get numerous errors. WORD documents are binary files. Using LOAD DATA you have to escape them properly. Have a look at the LOAD_FILE(file_name) function instead. Can someone shed some light as to how to do this correctly? What would be the proper query to retrieve this file out of mysql? SELECT file FROM filetest WHERE fileid = 12345; Regards, Thomas - 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 Donald, I'd recommend just storing the filename (and possibly path) and using an application to fetch the file from the OS filesystem. There are several reasons. 1. Your database files become too large to effectivly manage (try backing up a 5 GB table every night). 2. Storing files on the OS filesystem allows you to backup only files that have changed. 3. If you store the files in MySQL and you later decide to switch DB software, you'll pull your hair out! 4. You can archive the files that don't change on CD. walt - 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: auotincrement
Frederick, ALTER TABLE will do the biz: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, =dn I would like to know if it is possible to configure mysql auto increment field to start at a specific number or to configure the auto increment field with a character such as a letter inserted infont of it(eg. r1, r2..) Thanks F belfon - 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: How do I recover a broken table?
Hello, If you're using MyISAM tables, shut down your server and run myisamchk For example, myisamchk \mysql\data\dbname\*.MYI See the MySQL Technical Reference for detailed syntax and usage information. However, it seems like your table is corrupted, indeed. Regards, Iikka On Tue, 27 Aug 2002 [EMAIL PROTECTED] wrote: Hi I have a table that returns the following error message as soon as I run a query that reads out more then 300 posts from the table ERROR 1030: Got error -1 from table handler The strange part is that the table works without any problem except when I try to select more then 300 posts. I have tried to use the analyze and recover commands but I only get the message that the table doesn't support these commands. Happy for any help I can get to try recover the table without having to read out all the data and recreate the table. /Magnus - 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 ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - 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 Table size in AIX
I found out my AIX box's maximum file size is 2GB. What can I do if I will have MySQL tables that are possibly over 2GB? - 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 4.0 stable release
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 27 August 2002 11:39 am, Lenz Grimmer wrote: As soon as it actually *is* stable :) On a more serious note, we are currently working on preparing the first release of 4.0 (4.0.3) that will be declared Beta instead of Alpha. The annoying thing about it, for me, is that MySQL 4.0 has been perfectly stable and perfectly usable for us until 4.0.3, which so far has yet to produce a usable client library. I keep pulling from CVS and recompiling, hoping that one day soon I'll be able to move beyond 4.0.2. ;) Clients in 4.0.3 simply lock up when they try to access a database (whether a 4.0.2 or 4.0.3 or 3.23 database), for me. Linux 2.4.19, gcc 3.1.1 and 3.2. But it looks like several things were changed for 4.0.3, so I'll wait it out, I suppose. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9a8MwRU3T/K5ORe8RArMOAKCnjNf49O7snEPNvhNJhgrLPTPPsQCeIqKN dDUPU2iwJ3bFFZPfOh6cF40= =i+W8 -END PGP SIGNATURE- - 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
Process Sleeping
The search function for our ecommerce site has worked great up until the other day, but now when a search is ran the whole system just hangs. Here is what I have found: A process is started (search.cgi) that stays open until all of the memory is used up (sometimes two same-named processes). Then, it fills up ALL of the swap space until the system is basically unsable. After about 30 seconds of filling up space and hanging the system, the process dies and the memory and swap are released, returning the system to normal condition. I ran mysqladmin processlist while the system was still responding (before all swap filled), and all I found was a new mysql process sleeping, but not doing anything. I tried the exact same code with a backed-up version of the database from a week ago, and the search worked fine. Something in our DB changed and messed stuff up, but so much is going on it's tough to figure out. My question is, does anyone have a suggestion for pinpointing the problem? Thanks in advance, Chad Arimura - 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 auto-increment
Elias, ALTER TABLE will do the biz: http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, =dn I would like to know if it is possible to configure mysql auo increment feild to start from a specific number or configure it to have a specific character such as a letter inserted infront of it. Thanks I have no respect for reality as soon as it is acknowledged as such. I am interested in what I can do with unacknowledged reality. Elias Canetti - 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 Table size in AIX
On Tue, 27 Aug 2002, Scott Pippin wrote: I found out my AIX box's maximum file size is 2GB. What can I do if I will have MySQL tables that are possibly over 2GB? Hello, There are basically two things you should consider: 1) Switch to InnoDB tables and create several data files just under 2GB each 2) Use MyISAM RAID option This requires you to compile your MySQL with --with-raid configuration parameter. Then MySQL will create separate 00, 01, 02, 03 and so on directories with a piece of your table in each of them. See Manual section 6.5.3 (CREATE TABLE syntax) for detailed coverage. You will want RAID_TYPE=STRIPED configuration since that facilitates for the spreading of the .MYD files. Regards, Iikka ** * Iikka Meriläinen * * E-mail: [EMAIL PROTECTED] * * Vaala, Finland * ** - 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: Process Sleeping
In the last episode (Aug 27), Chad Arimura said: The search function for our ecommerce site has worked great up until the other day, but now when a search is ran the whole system just hangs. Here is what I have found: A process is started (search.cgi) that stays open until all of the memory is used up (sometimes two same-named processes). Then, it fills up ALL of the swap space until the system is basically unsable. After about 30 seconds of filling up space and hanging the system, the process dies and the memory and swap are released, returning the system to normal condition. I ran mysqladmin processlist while the system was still responding (before all swap filled), and all I found was a new mysql process sleeping, but not doing anything. I tried the exact same code with a backed-up version of the database from a week ago, and the search worked fine. Something in our DB changed and messed stuff up, but so much is going on it's tough to figure out. My question is, does anyone have a suggestion for pinpointing the problem? Sounds like a problem in the CGI, where a particular field value or error condition is not handled correctly, and it starts allocating memory out of control. Add some printf()s to your code, or attach to the process with gdb and get a stack trace, or kill -9 the CGI and gdb the corefile. -- 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: MySQL Table size in AIX
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 27 August 2002 20:05, Scott Pippin wrote: I found out my AIX box's maximum file size is 2GB. What can I do if I will have MySQL tables that are possibly over 2GB? You could either use MERGE tables: http://www.mysql.com/doc/en/MERGE.html Or you could use the RAID_TYPE option: http://www.mysql.com/doc/en/CREATE_TABLE.html Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE9a87rSVDhKrJykfIRAv52AJ9Xm+4rStIcU4I3qNTbgLgmvSusswCdGvsT /xcpFFw4pc3gJ6dZhyU6nVc= =/xGx -END PGP SIGNATURE- - 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 4.0 stable release
Dean Ellis wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tuesday 27 August 2002 11:39 am, Lenz Grimmer wrote: As soon as it actually *is* stable :) On a more serious note, we are currently working on preparing the first release of 4.0 (4.0.3) that will be declared Beta instead of Alpha. The annoying thing about it, for me, is that MySQL 4.0 has been perfectly stable and perfectly usable for us until 4.0.3, which so far has yet to produce a usable client library. I keep pulling from CVS and recompiling, hoping that one day soon I'll be able to move beyond 4.0.2. ;) Clients in 4.0.3 simply lock up when they try to access a database (whether a 4.0.2 or 4.0.3 or 3.23 database), for me. Linux 2.4.19, gcc 3.1.1 and 3.2. But it looks like several things were changed for 4.0.3, so I'll wait it out, I suppose. -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9a8MwRU3T/K5ORe8RArMOAKCnjNf49O7snEPNvhNJhgrLPTPPsQCeIqKN dDUPU2iwJ3bFFZPfOh6cF40= =i+W8 -END PGP SIGNATURE- - 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 Dean, There has been some issues with gcc 3.x if I remember correctly. I know RedHat released another beta because of problems with gcc 3.x. Have you tried it on a box with gcc 2.9x ? walt - 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: Slow select query, need some clues to speed it up please ...
Hi, My opinion: Personally, i'm not agree with this model of data in a table...but it's your choice. Anyway, i think you can try this : select field1,field2,field3,field4,field5,field6,field7 from YOUR_TABLE WHERE field2=0 AND field7=15 AND (CASE WHEN field3 like 'john' THEN field3 like 'john' WHEN field4 like 'john' THEN field4 like 'john' WHEN field5 like 'john' THEN field5 like 'john' END); Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: David Bordas [EMAIL PROTECTED] To: Mikhail Entaltsev [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 27, 2002 6:16 PM Subject: Re: Slow select query, need some clues to speed it up please ... ME In this case... ME Can you try again? Sure. query 1 : SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' OR Field5 LIKE '% John%' ) ORDER BY Field6 LIMIT 0,20; query 2: SELECT Field1,Field2,Field3,Field4,Field5,Field6,Field7 FROM MyTable WHERE Field7=15 AND Field2=0 AND (Field3 LIKE '%John%' OR Field4 LIKE '%John%' ) ORDER BY Field6 LIMIT 0,20; Results : query | time 1 0.78s 2 1.20s 1 0.77s 2 1.21s 1 0.78s 2 1.22s IMHO there are 2 ways: 1. It will work much faster. 2. It won't change speed of execution significantly. Euh Third one ? Remove a condition slow the query ... Perhaps i've a problem somewhere, but where .. Perhaps with some cache variables ? I don't know. This is the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=128M set-variable= back_log=100 set-variable= record_buffer=1M set-variable= sort_buffer=2M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=256 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 Server have got 1Go and run only mysql ... Table have 4M rows and index. Mytable.MYD : 1109586816 bytes Mytable.MYI : 93065216 Thanks David - 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
job database with invoicing
I'm building a job database with simple invoicing. I have one table called JOBS and one called INVOICES. In INVOICES there's a foreign key column called job_id referencing INVOICES to JOBS.job_id. in a simplified way it looks like this: (there are a lot of other columns of course) table JOBS ( job_id int not null auto_increment, jobname char(30), primary key job_id ) and table INVOICES ( invoice_id int not null auto_increment, job_id int(11), foreign key job_id, primary key invoice_id ) Would this make sense if in the future I want to find out: #1 which jobs have and have not been invoiced #2 what invoices are linked to specific job What would the MySQL statement look like? I've tried to find out but I'm not sure if the relations between both tables make sense at all ... thanks for any help! K:) - 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: Re: MySQL 4.0 stable release
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 sql,query ... On Tuesday 27 August 2002 02:30 pm, walt wrote: There has been some issues with gcc 3.x if I remember correctly. I know RedHat released another beta because of problems with gcc 3.x. Have you tried it on a box with gcc 2.9x ? Yes... I have had identical behavior (the clients lock up immediately upon attempting to access a database) with 4.0.3 on two seperate machines, with two different Linux distributions and using gcc 2.95, 3.1.1 and 3.2. As soon as I cloned the 4.0.3 tree, the clients broke. The server itself is fine, and I am, in fact, using the 4.0.2 clients to interact with a 4.0.3 server without problems. strace did not provide any useful information, so basically I am not going to give it much attention until the official 4.0.3 binaries are out. One way or another, they'll help identify the problem. (Or, if it's a problem with MySQL and not, in fact, a problem with, say, my libc or something, I assume it will be corrected eventually.) -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9a93XRU3T/K5ORe8RAuSeAKCV6yckz9ntdA0xFKCpZhRPxRThQQCfddPm R3ViGB/QVuyG5sPALhCfNsk= =H1P2 -END PGP SIGNATURE- - 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 in making the query to use the index
your date strings are incorrect. As a string it should be '2002-08-01' but it can also be treated as a numeric. try: and expendituredate between 20020801 and 20020831 kamesh jayachandran wrote: Hi Gerald, I tried the following query, select DATE_FORMAT(expendituredate,%d-%b-%Y) as expdate,sum(amount) as amount from expenditure where userid=11 and expendituredate between '2002-8-01' and '2002-8-31' group by expendituredate order by expendituredate; which is not overriding the expenditure column. Still my query goes for the full table scan. With regards kamesh jayachandran Get your FREE web-based e-mail and newsgroup access at: http://MailAndNews.com Create a new mailbox, or access your existing IMAP4 or POP3 mailbox from anywhere with just a web browser. - 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: mysqldump on tables which use auto_increment
use the -f option to ignore the duplicates.
Chris Barnes wrote:
Hi,
I have a database which a few tables which have a few fields set with
auto_increment and i'm having alot of trouble restoring the backup because
the backup recreates the table with the auto_increment field, but when it
tries to enter the data back into the table it cant because it is trying to
manually enter the value for the field with the auto_increment.
here's a sample of the backup sql...
CREATE TABLE ships (
ship_id bigint(20) unsigned NOT NULL auto_increment,
ship_name varchar(20),
ship_destroyed enum('Y','N') DEFAULT 'N' NOT NULL,
character_name varchar(20) DEFAULT '' NOT NULL,
password varchar(16) DEFAULT '' NOT NULL,
now here's a sample of the data it will try to enter into the table
# Dumping data for table 'ships'
#
LOCK TABLES ships WRITE;
INSERT INTO ships VALUES (1,'WebMaster','N','WebMaster','admin@xx');
UNLOCK TABLES;
if i try to restore the database from this data it always tells me there was
an error because it is trying to insert a duplicate value.
dos anyone know how to properly backup and restore a table with
auto_increment fields? or am i on the completely wrong track here?
any help appreciated.
-
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
mysql 4 release
Does anyone have any idea when Mysql 4 will be classed as stable. - 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
Innodb deadlock printouts in .52
Hi, I'm running mysql 3.23.52 w/ innodb tables, and I started getting some deadlocks since upgrading from .51. When I do a show innodb status in prints out the following: 020826 19:22:15 LATEST DETECTED DEADLOCK: *** (1) TRANSACTION: TRANSACTION 0 16655549, ACTIVE 1 sec, OS thread id 87339022 inserting LOCK WAIT 6 lock struct(s), heap size 1024, undo log entries 4 MySQL thread id 21314, query id 7772397 10.1.0.1 10.1.0.2 pas update INSERT plx_contact_field (user_id, entry_id, field_id, value) values (8, 0, 0, name107788) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540698 n bits 272 table user/plx_contact_field index PRIMARY trx id 0 16655549 lock_mode X waiting Record lock, heap no 1 *** (2) TRANSACTION: TRANSACTION 0 16655527, ACTIVE 2 sec, OS thread id 87326732 inserting 9 lock struct(s), heap size 1024, undo log entries 5 MySQL thread id 21311, query id 7772401 10.1.0.1 10.1.0.2 pas update INSERT plx_contact_field (user_id, entry_id, field_id, value) values (7, 1983, 2, pubemail1-1958) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540698 n bits 272 table user/plx_contact_field index PRIMARY trx id 0 16655527 lock_mode X Record lock, heap no 1 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540698 n bits 272 table user/plx_contact_field index PRIMARY trx id 0 16655527 lock_mode X waiting Record lock, heap no 1 *** WE ROLL BACK TRANSACTION (2) It seems from the printout, that's what happening is that transaction 2 obtains a lock on a row, then tries to obtain it again, while transaction 1 is waiting for that lock, and this causes a deadlock to be detected. Is this the expected behavior? We are running in serializable. I would think that transaction 2 should first check to see if it already has the lock before it runs through and detects a deadlock. thanks for your help, 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 4 release
Matt Darcy wrote: Does anyone have any idea when Mysql 4 will be classed as stable. - 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 See http://www.mysql.com/doc/en/Which_version.html 4.0 will be declared stable when it _is_ stable. You can help with finding that point by testing 4.0 and reporting any bugs you find to the mysql developer team. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ 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 4 release
Sounds fair. I have played with it a bit and found it good. Just hoping it was be found stable soon as I am keen to use it in production. I'll get back to some testing. Thanks, Matt -Original Message- From: Mark Matthews [mailto:[EMAIL PROTECTED]] Sent: 27 August 2002 22:15 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: mysql 4 release Matt Darcy wrote: Does anyone have any idea when Mysql 4 will be classed as stable. - 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 See http://www.mysql.com/doc/en/Which_version.html 4.0 will be declared stable when it _is_ stable. You can help with finding that point by testing 4.0 and reporting any bugs you find to the mysql developer team. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ 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
looking for handler_read value
I'm trying to put together some statistics on our queries and am wondering what percentage of our queries involve a full table scan. The variable which kind of tracks this is Handler_read_rnd_next. However, since a query may cause multiple Handler_read_rnd_next events to occur to get all of it's data, I can't directly compare this to the Questions variable. However, if I knew how many total Handler_read requests are happening, I could compare that to Handler_read_rnd_next to get the kind of information I want. However, there is no Handler_read variable. Question: is Handler_read = Handler_read_first + Handler_read_key + Handler_read_next + Handler_read_prev + Handler_read_rnd_next? Thanks, Marc - 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
Info on 4.0.x release date
When will 4.0.x finally be released to beta? The facts as I understand them: - 4.0 went alpha in Nov or Dec of '01 - At the time, Monty and his gang projected stabilization by Jan or Feb - More than 6 months have passed since and the product is not yet stabilized - No meaningful information has been released about what a more realistic date might be - Anytime anyone dares to stick their head up and inquire about this issue, they get their hat handed to them with a glib when its ready response It is impossible for me to believe that I am the only one frustrated by this. I spent months evaluating the suitability of MySQL for our systems last summer/fall. At that time I made the determination that it would be quite well suited, but only with the addition of some functionality promised in 4.0 and 4.1. It is not my intention to put the MySQL team on the defensive about this -- I know they are working incredibly hard. I do not, however, feel it is unreasonable for the user community to ask for more information about upcoming releases. I know that you are probably thinking that I don't have the right to be demanding when the software is free. Well guess what, it isn't! Every company that uses MySQL spends money on it (remember, licensing represents only a small fraction of total cost of ownership). Furthermore, MySQL AB is a for-profit venture whose fortunes are tied to the continued spread and use of their flagship open-source product. So... How about cluing us in on what's left to do, what progress has been made and a best-guess as to what the release schedule might look like. I wouldn't worry too much if your dates slip over time (I think you will find a sympathetic group), but just keep us updated so we can adjust our own plans. Thanks Will French == NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Cadwalader, Wickersham Taft for any loss or damage arising in any way from its use. == - 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: Info on 4.0.x release date
On Tue, Aug 27, 2002 at 05:45:07PM -0400, Will French wrote: When will 4.0.x finally be released to beta? The facts as I understand them: - 4.0 went alpha in Nov or Dec of '01 - At the time, Monty and his gang projected stabilization by Jan or Feb - More than 6 months have passed since and the product is not yet stabilized - No meaningful information has been released about what a more realistic date might be Meaningful? You must have not read the responses others have posted. Mark and others have posted meaningful responses. - Anytime anyone dares to stick their head up and inquire about this issue, they get their hat handed to them with a glib when its ready response You got the truth. Would you prefer someone lie to you about it? It's simply the case that they won't declare the code stable until it is stable. That means people have to use it and not find any bugs. The more people who test it, the sooner the bugs are found. We've been running 4.0.x on some of our servers to try and track down the remaining bugs. We found a few. They've been fixed. We're *that much* closer to having a stable MySQL 4.0.x now. Based on the reports I've seen to the bugs list, there aren't a lot of outstanding issues. But until 4.0.3-beta gets in wide circulation, it'll probably be hard to say. It is impossible for me to believe that I am the only one frustrated by this. Based on the number of people asking, you're not. It is not my intention to put the MySQL team on the defensive about this -- I know they are working incredibly hard. I do not, however, feel it is unreasonable for the user community to ask for more information about upcoming releases. What more information would be helpful, exactly? Do guesses really help? (Maybe they do, but I don't see how. Unless you know that the person guessing is really good at it...) I know that you are probably thinking that I don't have the right to be demanding when the software is free. Well guess what, it isn't! Not if you're a paying MySQL AB customer. Every company that uses MySQL spends money on it (remember, licensing represents only a small fraction of total cost of ownership). But that money isn't paid *to* MySQL AB, so how is that relevant? Furthermore, MySQL AB is a for-profit venture whose fortunes are tied to the continued spread and use of their flagship open-source product. Certainly. NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. But you fail to tell someone how to know if they're the intended recipient, don't you? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 21 days, processed 433,722,272 queries (232/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: Process Sleeping
The confusing thing is, I run the query across the EXACT same code on a different database (old copy of same database), and it works just fine. Are there any ways to peak into what mysql is doing besides mysqladmin showprocesses? Thanks, Chad -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 12:05 PM To: Chad Arimura Cc: [EMAIL PROTECTED] Subject: Re: Process Sleeping In the last episode (Aug 27), Chad Arimura said: The search function for our ecommerce site has worked great up until the other day, but now when a search is ran the whole system just hangs. Here is what I have found: A process is started (search.cgi) that stays open until all of the memory is used up (sometimes two same-named processes). Then, it fills up ALL of the swap space until the system is basically unsable. After about 30 seconds of filling up space and hanging the system, the process dies and the memory and swap are released, returning the system to normal condition. I ran mysqladmin processlist while the system was still responding (before all swap filled), and all I found was a new mysql process sleeping, but not doing anything. I tried the exact same code with a backed-up version of the database from a week ago, and the search worked fine. Something in our DB changed and messed stuff up, but so much is going on it's tough to figure out. My question is, does anyone have a suggestion for pinpointing the problem? Sounds like a problem in the CGI, where a particular field value or error condition is not handled correctly, and it starts allocating memory out of control. Add some printf()s to your code, or attach to the process with gdb and get a stack trace, or kill -9 the CGI and gdb the corefile. -- 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 - 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
Searching text in a big table
Hi there, I have a big table which has 25 rows.And each row has a BLOB field which stores lots of text. When I search text in this table(using the query: where CONTENT like '%news%'),the searching speed is quite slow. is there anybody also have such problem? Any ideas about improving the speed of searching text in big table? Thanks in advance. Sanny - 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: Process Sleeping
In the last episode (Aug 27), Chad Arimura said: The confusing thing is, I run the query across the EXACT same code on a different database (old copy of same database), and it works just fine. Are there any ways to peak into what mysql is doing besides mysqladmin showprocesses? If the processlist says 'sleep', that's exactly what it's doing. There is no active query. Mysql isn't doing anything. Take a look at your CGI and see if there are any failure cases not accounted for, or break out the debugger and see exactly why it it allocating all that memory. -- 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: Innodb deadlock printouts in .52
Joe,
- Original Message -
From: Joe Shear [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Wednesday, August 28, 2002 12:15 AM
Subject: Innodb deadlock printouts in .52
Hi,
I'm running mysql 3.23.52 w/ innodb tables, and I started getting some
deadlocks since upgrading from .51. When I do a show innodb status in
prints out the following:
020826 19:22:15 LATEST DETECTED DEADLOCK:
*** (1) TRANSACTION:
TRANSACTION 0 16655549, ACTIVE 1 sec, OS thread id 87339022 inserting
LOCK WAIT 6 lock struct(s), heap size 1024, undo log entries 4
MySQL thread id 21314, query id 7772397 10.1.0.1 10.1.0.2 pas update
INSERT plx_contact_field (user_id, entry_id, field_id, value) values (8,
0, 0, name107788)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 540698 n bits 272 table
user/plx_contact_field index PRIMARY trx id 0 16655549 lock_mode X
waiting
Record lock, heap no 1
*** (2) TRANSACTION:
TRANSACTION 0 16655527, ACTIVE 2 sec, OS thread id 87326732 inserting
9 lock struct(s), heap size 1024, undo log entries 5
MySQL thread id 21311, query id 7772401 10.1.0.1 10.1.0.2 pas update
INSERT plx_contact_field (user_id, entry_id, field_id, value) values (7,
1983, 2, pubemail1-1958)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 540698 n bits 272 table
user/plx_contact_field index PRIMARY trx id 0 16655527 lock_mode X
Record lock, heap no 1
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 540698 n bits 272 table
user/plx_contact_field index PRIMARY trx id 0 16655527 lock_mode X
waiting
Record lock, heap no 1
*** WE ROLL BACK TRANSACTION (2)
It seems from the printout, that's what happening is that transaction 2
obtains a lock on a row, then tries to obtain it again, while
transaction 1 is waiting for that lock, and this causes a deadlock to be
detected. Is this the expected behavior? We are running in
serializable. I would think that transaction 2 should first check to
see if it already has the lock before it runs through and detects a
deadlock.
have transactions (1) and (2) been reading the place where they are going to
insert? If yes, and you run in the SERIALIZABLE mode, then you will very
easily get a deadlock because they hold next-key locks over the insertion
spot.
If they have NOT read, updated, or deleted there, then the deadlock is
probably removed in MySQL-4.0.3 which should be out this week.
In the printout above a locking read, update, or delete may have set
next-key locks on the page 'supremum' (highest) record. The inserts had to
wait. To wait they set 'gap' type X-locks on the supremum.
A gap type lock does not grant the right to insert, it is above used as a
technical trick to leave the insert waiting. That is why (2) ignores the
X-lock it already has on the supremum record.
In InnoDB-3.23.52 the insert lock check was coarse: any waiting insert lock
on the same gap caused another insert to wait. In 4.0.3 this has been
improved. In 4.0.3 we have 4 types of locks on records:
1) ordinary next-key locks which lock the record and the gap before it;
2) gap locks only lock the gap before a record, not the record itself;
different transactions can have conflicting locks on the gap at the same
time, because when purge removes records, we may have to merge different
gaps; gap locks are purely inhibitive, they are used to force other users to
wait;
3) insert intention locks;
4) 'implicit' locks: every inserted record is X-locked by the inserting
transaction.
An insert in 4.0.3 only waits for type 1 and 2 locks to be released.
The details of next-key locking are complex. That is why they have not been
documented in the manual. The manual will be improved in the future to help
users to interpret the printout of SHOW INNODB STATUS.
See also http://www.innodb.com/ibman.html#Cope_with_deadlocks.
A relevant function from the source of 4.0.3:
/*
Checks if a lock request for a new lock has to wait for request lock2. */
UNIV_INLINE
ibool
lock_rec_has_to_wait(
/*=*/
/* out: TRUE if new lock has to wait for lock2 to be
removed */
trx_t* trx, /* in: trx of new lock */
ulint mode, /* in: LOCK_S or LOCK_X */
ulint gap, /* in: LOCK_GAP or 0 */
ulint insert_intention,
/* in: LOCK_INSERT_INTENTION or 0 */
lock_t* lock2) /* in: another record lock; NOTE that it is assumed
that this has a lock bit set on the same record as
in lock1 */
{
ut_ad(trx lock2);
ut_ad(lock_get_type(lock2) == LOCK_REC);
ut_ad(mode == LOCK_S || mode == LOCK_X);
ut_ad(gap == LOCK_GAP || gap == 0);
ut_ad(insert_intention == LOCK_INSERT_INTENTION
|| insert_intention == 0);
if (trx != lock2-trx !lock_mode_compatible(mode,
lock_get_mode(lock2))) {
/* We have somewhat complex rules when gap type
record locks cause waits */
if (!gap lock_rec_get_insert_intention(lock2)) {
/* Request
re: Info on 4.0.x release date
Hi Will, As far as I have heard (from MySQL) is that MySQL 4.0.3 is being made ready for Beta as we speak and is likely to be released in the next month. V4.1 will most likely be released Early Sept in Src form and in Binaries two months later. 4.0.x branch will be allowed to stabalise from the release of 4.0.3, through the normal Beta, Gamma and Stable routine. In fact I believe there was an email to this effect earlier today Rich When will 4.0.x finally be released to beta? The facts as I understand them: - 4.0 went alpha in Nov or Dec of '01 - At the time, Monty and his gang projected stabilization by Jan or Feb - More than 6 months have passed since and the product is not yet stabilized - No meaningful information has been released about what a more realistic date might be - Anytime anyone dares to stick their head up and inquire about this issue, they get their hat handed to them with a glib when its ready response It is impossible for me to believe that I am the only one frustrated by this. I spent months evaluating the suitability of MySQL for our systems last summer/fall. At that time I made the determination that it would be quite well suited, but only with the addition of some functionality promised in 4.0 and 4.1. It is not my intention to put the MySQL team on the defensive about this -- I know they are working incredibly hard. I do not, however, feel it is unreasonable for the user community to ask for more information about upcoming releases. I know that you are probably thinking that I don't have the right to be demanding when the software is free. Well guess what, it isn't! Every company that uses MySQL spends money on it (remember, licensing represents only a small fraction of total cost of ownership). Furthermore, MySQL AB is a for-profit venture whose fortunes are tied to the continued spread and use of their flagship open-source product. So... How about cluing us in on what's left to do, what progress has been made and a best-guess as to what the release schedule might look like. I wouldn't worry too much if your dates slip over time (I think you will find a sympathetic group), but just keep us updated so we can adjust our own plans. Thanks Will French == NOTE: The information in this email is confidential and may be legally privileged. If you are not the intended recipient, you must not read, use or disseminate the information. Although this email and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by Cadwalader, Wickersham Taft for any loss or damage arising in any way from its use. == - 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
