Re: Next working binary version ?
Victoria Monday, August 19, 2002 11:58 AM, you wrote : VR Hi, David! VR You can find new 3.23.52 binary packages at www.mysql.com VR Could you install new packages and check if loading problem occurs VR again? One of our production server is running 3.23.52 ( the tar.gz one ). Uptime is 26 Hours and all is ok for the moment ... 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
Solution: Compiling and mysql_lex.cc
Hi, after some research we found out something, that was reported sometimes to this list (and never has been answered). The errors while compiling mysql_lex.cc, that were reported, looked like this: make[3]: Entering directory `/services/source/database/mysql/mysql-3.23.52/sql' g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/services/database/mysql/mysql-3.23.52\ -DDATADIR=\/services/database/mysql/data\ -DSHAREDIR=\/services/database/mysql/mysql-3.23.52/share/mysql\ -DHAVE_CONFIG_H -I./../include -I./../regex-I. -I../include -I.. -I.-O3 -DDBUG_OFF -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/services/source/database/mysql/mysql-3.23.52/include -DHAVE_RWLOCK_T -c sql_lex.cc sql_lex.cc: In function `void lex_init()': sql_lex.cc:85: `symbols' undeclared (first use this function) sql_lex.cc:85: (Each undeclared identifier is reported only once for each function it appears in.) sql_lex.cc:87: `sql_functions' undeclared (first use this function) sql_lex.cc: In function `int find_keyword(LEX*, unsigned int, bool)': sql_lex.cc:168: `get_hash_symbol' undeclared (first use this function) make[3]: *** [sql_lex.o] Error 1 make[3]: Leaving directory `/services/source/database/mysql/mysql-3.23.52/sql' The error is the following: The file lex_hash.h is created during make from the binary gen_lex_hash, that is generated before. If the start of gen_lex_hash fails (usually, because the dynamic library libstdc++.so is not found), lex_hash.h is created with a file length of zero. All followings calls of make do not recognize this, so the errors occur because the definitions in lex_hash.h do not exist. One should include this in the makefiles as dependencies, but I'm not firm enough with make to do this. The actual fix was: remove lex_hash.h, set LD_LIBRARY_PATH, so that the missing library is found, and start make again. Regards... Olaf -- Dipl.Inform. Olaf Gellert PRESECURE (R) Consultant, Consulting GmbH Phone: (+49) 0700 / PRESECURE [EMAIL PROTECTED] Check on European Security Incident Response Teams http://www.ti.terena.nl - 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
Missing header file
Dear MySQL! I can't compile the source codes I've made top MySQL4.0.2 alpha release because the my MS Visual C++ 6.0 compiler tells me it can't find the my_alloc.h header file. I've set the mysql include path for the compiler, so it seems this file is missing from your install kit. Please write me how i can get it. Best regards Attila Miklosi - 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
Performance issue: slow inserts
I have a lot of tables, and not all of them are filled
equally.
Inserts to tables that have a lot of entries(see the
count below), take a long time (about .06 secs on the
average in mysql, over 0.09-0.1 in DBI), for example
mysql INSERT INTO T1 VALUES
('3CCF571C1A88118801040302','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.06 sec)
inserts into tables like T2 which have few elements
take a much shorter time:
mysql INSERT INTO T2 VALUES
('3CCF571C1A88118801040302','072','7269','','','',''
,'','',3103,1,24,'2002-09-
01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
3855206',1,0);
Query OK, 1 row affected (0.00 sec)
mysql SELECT COUNT(*) FROM T2;
+--+
| COUNT(*) |
+--+
| 509 |
+--+
1 row in set (0.00 sec)
mysql SELECT COUNT(*) FROM T1;
+--+
| COUNT(*) |
+--+
| 782910 |
+--+
1 row in set (7.43 sec)
Of course DELETES are similar(actually much worse with
a where), but deletes are not an issue here
mysql DELETE FROM T1 WHERE date ='2002-09-01';
Query OK, 1 row affected (40.19 sec)
mysql DELETE FROM T2 WHERE date ='2002-09-01';
Query OK, 1 row affected (0.24 sec)
I presume the problem is that in the table definitions
I have a couple of unique constraints and
the INSERT time difference is due to mysql
checking all entries in the table for a unique
violation, hence the insert would be slow:
CREATE TABLE T1 (
corrno varchar(43) default NULL,
a varchar(30) default NULL,
b varchar(30) default NULL,
c varchar(30) default NULL,
oc varchar(30) default NULL,
red varchar(30) default NULL,
sanum varchar(30) default NULL,
sbnum varchar(30) default NULL,
tnum varchar(30) default NULL,
o int(5) default NULL,
d int(5) default NULL,
cic int(6) default NULL,
date date default NULL,
start_time time default NULL,
value decimal(30,2) default NULL,
iamtoacm decimal(7,4) default NULL,
iamtoans decimal(7,4) default NULL,
reltorlc decimal(7,4) default NULL,
duration decimal(12,3) default NULL,
rcv int(3) default NULL,
rcvabn int(1) default '0',
loc int(3) default NULL,
reldir int(1) default NULL,
secode int(3) default NULL,
trans int(3) default NULL,
tc int(3) default NULL,
rsp 0 int(3) default NULL,
ccq int(5) default NULL,
ccr int(3) default NULL,
ccc int(3) default NULL,
ccp int(3) default NULL,
ccsan int(3) default NULL,
nata int(2) default NULL,
natb int(2) default NULL,
natc int(2) default NULL,
natocn int(2) default NULL,
natredir int(2) default NULL,
natsccpa int(2) default NULL,
natsccpbint(2) default NULL,
nattrue int(2) default NULL,
acat int(2) default NULL,
bcat int(1) default NULL,
upi char(1) default NULL,
upp int(3) default NULL,
acind char(1) default NULL,
inter int(1) default NULL,
ech int(1) default NULL,
length int(3) default '0',
file varchar(30) default NULL,
link varchar(8) default NULL,
fileind int(11) default NULL,
unicor varchar(100) NOT NULL default '',
stp int(1) default '0',
alarm int(1) default '0',
UNIQUE KEY unicor (unicor),
UNIQUE KEY corrno (corrno)
) TYPE=InnoDB;
The question is what can be done about this?
I guess getting rid of the unique constraints
would help, but then I would be getting some
unwanted duplicates. I should add that currently
I need to do about 100,000
inserts/10 minutes and the system is too slow for this
as it is, while it used to be very fast. So, I am
looking fro what can be done to avoid performance
degradation
as the tables fill.
The server is a dual PIII x1000, 2GB RAM Linux 2.4.18
machine but the inserts are done from a client(a 1000
PIII , 1GB RAM Linux machine). Below is /etc/my.cnf
# Example mysql config file for very large systems.
#
# This is for large system with memory of 1G-2G where
the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
(in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the
program supports.
# If you want to know which options a program support,
run the program
# with --help option.
# The following options will be passed to all MySQL
clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer_size=500M
set-variable = key_buffer=100M
set-variable = max_allowed_packet=1M
Re: How can I do this query ? (Join with a preselction)
Hi Andreas, I'm just getting started with mysql so please bear with me. =8-} I do a 3 table query like: SELECT a.x, b.y, c.z FROM a, b, c WHERE a.b_fk = b.id AND a.c_fk = c.id; That works so far. In c can be rows which aren't active anymore. There is a good_from and a good_until DATETIME field which should be respected in the query above. Can I filter the old rows out before I do the SELECT ? I'd like to reduce the workload. The WHERE clause is used to filter in/out data rows. What about adding AND NOW() BETWEEN c.good_from AND c.good_until or similar? (manual has good examples of BETWEEN, and another good keyword is JOIN (as well as WHERE)) With regards to workload/optimisation, yes it is appropriate to sequence the WHERE clause so that the most exclusive condition comes first. Free advice: Make it work before you make it better! Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
win98+mysql3.23.52+innodb (upgrade from 3.23.51)
Hi. I have problem. I would like to upgrad from 3.23.51 on my win98 to 3.23.52 but with innodb tables 3.23.52 hang on starting. and my win98 goes slowly and slowly. ctrl-alt-del helps :) In log i see: 010820 20:20:13 C:\MYSQL\BIN\MYSQLD.EXE: Got signal 2. Aborting! 010820 20:20:13 Aborting 010820 20:20:16 InnoDB: Warning: shutting down a not properly started InnoDB: or created database! I made downgrading to 3.23.51 all is allright. Server is going. Any idea ? P.s. I have frontpage 2002 instaled on my win98 box, during instalation some files were changed by frontpage 2002 instalator. regards, Irek - 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
The key words are over which I have no control. See if you can figure it. - Original Message - From: Andrew Conkling [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 22, 2002 12:36 PM Subject: Error Hey, I'm trying to create a db on a server (over which I have no control) and I keep getting this error: mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'musica'' Any suggestions? Andrew My homepage http://www.andrewski.fr.st - 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
Can we invoke user defined function made in java in MySQL
Greetings! MySql documents specifies user defined function made in .dll/.so to be invoked in MySQL. Is there a way in which a function made in java be invoked in MySQL. Regards - 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: Missing header file
Attila, Thursday, August 22, 2002, 11:54:24 AM, you wrote: AM I can't compile the source codes I've made top MySQL4.0.2 alpha release because the my MS Visual C++ 6.0 compiler tells me it can't find the my_alloc.h header file. I've set the mysql include AM path for the compiler, so it seems this file is missing from your install kit. Please write me how i can get it. As far as I remember there was the same problem because of my_alloc.h wasn't in the include dir. But I just downloaded mysql-4.0.2-alpha-win-src and my_alloc.h is present in the include dir. How long ago did you download src package ? -- 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: Error
Andrew, Thursday, August 22, 2002, 7:36:35 AM, you wrote: AC Hey, I-m trying to create a db on a server (over which I have no AC control) and I keep getting this error: AC = AC mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: AC '@localhost' to database 'musica'' AC = AC Any suggestions? If you don't have permissions to create database ask database administrator to create a user with appropriate privileges. You can also start MySQL server with --skip-grant-tables option: http://www.mysql.com/doc/en/Command-line_options.html http://www.mysql.com/doc/en/Resetting_permissions.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: question about replication - a very simple one - I know they all
Norris, Wednesday, August 21, 2002, 6:44:51 PM, you wrote: N I have just got into a situation in which I need to explore replication and N I know nothing about it. Can any one direct me to specific documentation on N this and maybe a simple tutorial for the greener of us? Check the appropriate chapter in the MySQL manual: http://www.mysql.com/doc/en/Replication.html -- 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: Next working binary version ?
David, Thursday, August 22, 2002, 9:48:14 AM, you wrote: DB Victoria DB Monday, August 19, 2002 11:58 AM, you wrote : VR Hi, David! VR You can find new 3.23.52 binary packages at www.mysql.com VR Could you install new packages and check if loading problem occurs VR again? DB One of our production server is running 3.23.52 ( the tar.gz one ). DB Uptime is 26 Hours and all is ok for the moment ... Hi! Glad to hear it! Thank you for your feedback. -- 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 Quota
Alex, Thursday, August 22, 2002, 4:54:01 AM, you wrote: AJ We want to set quota per user. Is that possible on MySQL ? If so AJ how can that be done? Depends on what you want to limit per user. Some info you can find here: http://www.mysql.com/doc/en/User_resources.html -- 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
WG: problem with null values by insert into .. select ..from .. join
-Ursprüngliche Nachricht- Von: Ferdinand, Dieter Gesendet am: Donnerstag, 22. August 2002 10:56 An: '[EMAIL PROTECTED]' Betreff: problem with null values by insert into .. select ..from .. join hello, i have one problem with insert into ... select ... from .. join ... my query: insert into tkanlage(standort,nummer,name,vorname, pname,pvorname,pnachname, pnr) select if(i.rv='J',3,if(pe.standort=0 or pe.standort is null or pe.standort=3,1,pe.standort)) as standort, ^^ here is the problem, without this checked with if, the record is set to NULL i.directory_number,i.directory_name,i.directory_first_name, i.displayed_name,i.phone_book_first_name,i.displayed_name, p.directory_name from import_tk as i left join import_tk as p on p.directory_number=i.directory_number and p.users0 left join personal as pe on pe.pnr=p.directory_name and austritt'1900-1-1' where i.users=0 when a field from the join is in the fieldlist and this field is null, then the whole record is set to null. server version: 3.23.49 a bug with null values by this function is also in version 3.22.xx. here is used the last value which was in this files at last time. when i check this with if and use a initial value, than it works, but this is only a workaround. goodby Mit freundlichen Grüßen Dieter Ferdinand __ Nothelfer __ Abteilung EDV Schlossstr. 22 D - 66687 Wadern - Lockweiler Phone + 49 (6871) 60 - 1211 Mobil + 49 (178) 5238926 oder 5518414 FAX+ 49 (6871) 60 - 0 - 1211 E-mail [EMAIL PROTECTED] Internet www.nothelfer.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
Strange behaviour of mysqld after dropping a column
Description: A time ago I reported next bug: The ALTER TABLE command behaves very strange when stopping and restarting the MySQL server afterwards. I noticed this after having rebooted my PC (which means that the MySql server is stopped and restarted). Before the reboot I dropped a column in one of my tables. After I had done this all the data was available in my table. However after have been rebooting my PC all the data was gone !!! It even becomes crazier. After the second reboot the data was visisble again. The tests I have executed showed that this problem shows up for both adding and deleting a column of a table. Also I noticed that when you add data after have beenstopping the MySQL server the first time this data is dissapeared after a second stop of the MySQL server. This problem should be solved in mysql-3.23.52. I have checked this and came to the conclusion the problem is indeed solved when adding a column, but after dropping a column the problem still exists !!! How-To-Repeat: mysql create database findBug; mysql use findBug mysql create table metatable(objid BIGINT not null, tablename varchar(64), field varchar(64), type varchar(50), PRIMARY KEY (objid)) type=BDB; mysql create table metaindex(objid BIGINT not null, indexname varchar(64), ref_metatable DOUBLE not null, PRIMARY KEY (objid)) type=BDB; mysql create table metaoid(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, PRIMARY KEY (objid), UNIQUE(tablename)) type=BDB; mysql insert into metaoid values(1, metaoid,4); mysql insert into metaoid values(2, metatable,1); mysql insert into metaoid values(3, metaindex,1); mysql select * from metaoid; +---+---+-+ | objid | tablename | oid | +---+---+-+ | 1 | metaoid| 4 | | 2 | metatable | 1 | | 3 | metaindex | 1 | +---+---+-+ mysql alter table metaoid add column test varchar(255); mysql select * from metaoid; +---+---+-+--+ | objid | tablename | oid | test | +---+---+-+--+ | 1 | metaoid | 4 | NULL | | 2 | metatable | 1 | NULL | | 3 | metaindex | 1 | NULL | +---+---+-+--+ mysql quit; C:\net stop mysql C:\net start mysql mysql select * from metaoid; +---+---+-+--+ | objid | tablename | oid | test | +---+---+-+--+ | 1 | metaoid | 4 | NULL | | 2 | metatable | 1 | NULL | | 3 | metaindex | 1 | NULL | +---+---+-+--+ So this seems to be ok now, the problem starts when executing the next steps. mysql alter table metaoid drop column test; mysql quit; C:\net stop mysql C:\net start mysql mysql select * from metaoid; Empty set (0.01 sec) mysql insert into metaoid values (5,test,1); mysql select * from metaoid; +---+---+-+ | objid | tablename | oid | +---+---+-+ | 5 | test | 1 | +---+---+-+ mysql quit; C:\net stop mysql C:\net start mysql mysql select * from metaoid; +---+---+-+ | objid | tablename | oid | +---+---+-+ | 5 | test| 1 | +---+---+-+ When I restarted mysql in mysql-3.23.51 my old data was visible again and the new data was lost. Now it seems to be the other way round ! Fix: No fix found for this problem. Synopsis: Strange behaviour of MySQL after dropping a column. Submitter-Id: Originator: Ismaël Cams Organization: Siemens Atea MySQL support: license Severity: critical Priority: high Category: mysqld Class: sw-bug Release: mysql-3.23.52. Executable: mysqld Environment: 512 MB RAM, PII System: Win2000 and NT Compiler: VC++ 6.0 Architecture: i Kind regards, Ismaël - 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
Re: MYSQL Quota
Alex, Mysql does not do quota for users (atleast as far as I know). But I read once in sysadmin mag that an programmer at an isp wrote a mysql quota daemon. The article is not on the web but here is the issue: http://www.sysadminmag.com/articles/2002/0202/ There is a link on the left hand side uder magazine called back issues that offers you the option to order back issues of sysadmin magazine. https://www.sdmediagroup.com/samag/back_issue.htm Hope this helps, chad On Thu, 22 Aug 2002 14:08:01 +0300 Victoria Reznichenko [EMAIL PROTECTED] wrote: Alex, Thursday, August 22, 2002, 4:54:01 AM, you wrote: AJ We want to set quota per user. Is that possible on MySQL ? If so AJ how can that be done? Depends on what you want to limit per user. Some info you can find here: http://www.mysql.com/doc/en/User_resources.html -- 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: Re: Next working binary version ?
David, Thursday, August 22, 2002, 9:48:14 AM, you wrote: DB Victoria DB Monday, August 19, 2002 11:58 AM, you wrote : VR Hi, David! VR You can find new 3.23.52 binary packages at www.mysql.com VR Could you install new packages and check if loading problem occurs VR again? DB One of our production server is running 3.23.52 ( the tar.gz one ). DB Uptime is 26 Hours and all is ok for the moment ... Hi! Glad to hear it! Thank you for your feedback. Oups forget that i said :( Serveur has just crashed, load average 200 ! Same problem that 3.23.51 but appear some hours after. Surely 'cause we have less people during these days ... Do we come back to 3.23.45 ... 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: Getting rid of filesort
Ok, so, I created that index. Now, it does not use filesort. Still 3 seconds, but better than a minute. Now, however, the previously fast query is using filesort. WTF? I will be needing both of these queries in Phorum 5 so I need to have them both running as fast as possible. I will add a forum_id, parent, thread key and see what happens. Brian. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Brian Moon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, August 22, 2002 12:28 AM Subject: Re: Getting rid of filesort | On Wed, Aug 21, 2002 at 11:57:53PM -0500, Brian Moon wrote: | I have a table: | | CREATE TABLE `single_table` ( |`forum_id` int(10) unsigned NOT NULL default '0', |`id` int(10) unsigned NOT NULL auto_increment, |`datestamp` datetime NOT NULL default '-00-00 00:00:00', |`thread` int(10) unsigned NOT NULL default '0', |`parent` int(10) unsigned NOT NULL default '0', |`author` varchar(37) NOT NULL default '', |`subject` varchar(255) NOT NULL default '', |`email` varchar(200) NOT NULL default '', |`attachment` varchar(64) NOT NULL default '', |`host` varchar(50) NOT NULL default '', |`email_reply` char(1) NOT NULL default 'N', |`approved` char(1) NOT NULL default 'N', |`msgid` varchar(100) NOT NULL default '', |`viewcount` int(11) NOT NULL default '0', |`modifystamp` int(10) unsigned NOT NULL default '0', |`userid` int(10) unsigned NOT NULL default '0', |`body` text NOT NULL, |PRIMARY KEY (`id`), |KEY `forum_id_3` (`forum_id`,`thread`,`modifystamp`,`approved`), |KEY `approved` (`approved`,`datestamp`), |KEY `parent` (`parent`), |FULLTEXT KEY `body` (`body`,`author`,`subject`) | ) TYPE=MyISAM | | | If I run a query like: | | select id from single_table where forum_id=5818 and parent=0 order by | modifystamp desc | | It runs really slow (a minute) and explain says it is using filesort. | | However this query: | | select id from single_table where forum_id=5818 and parent=0 order by thread | desc | | runs in .2 seconds and does not use a filesort. Is MySQL using the first | part of forum_id_3 key for this query? Do I need to create a forum_id, | parent, modifystamp key? | | That should do the trick, yes. | | Jeremy | -- | Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! | [EMAIL PROTECTED] | http://jeremy.zawodny.com/ | | MySQL 3.23.51: up 15 days, processed 323,657,916 queries (235/sec. avg) | | - | Before posting, please check: |http://www.mysql.com/manual.php (the manual) |http://lists.mysql.com/ (the list archive) | | To request this thread, e-mail [EMAIL PROTECTED] | To unsubscribe, e-mail [EMAIL PROTECTED] | Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php | | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Performance issue: slow inserts
InnoDB are supposed to be faster at Inserts and updates than MyISAM.
However, my dealings with InnoDB have made me think that unless you _need_
transactions, foreign keys, etc, MyISAM is still the way to go for speed on
large tables.
If you can go MyISAM (you may need that stuff), fixed width tables will
insert and update faster than variable width tables. You could turn all
those varchars into chars. There will be an increase in data size.
As for the delete, all you need is a key on the date column. That will
speed that up.
- Original Message -
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, August 22, 2002 4:22 AM
Subject: Performance issue: slow inserts
|
| I have a lot of tables, and not all of them are filled
| equally.
| Inserts to tables that have a lot of entries(see the
| count below), take a long time (about .06 secs on the
| average in mysql, over 0.09-0.1 in DBI), for example
|
| mysql INSERT INTO T1 VALUES
| ('3CCF571C1A88118801040302','072','7269','','','',''
| ,'','',3103,1,24,'2002-09-
| 01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
| 0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
| ,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
| 3855206',1,0);
| Query OK, 1 row affected (0.06 sec)
|
| inserts into tables like T2 which have few elements
| take a much shorter time:
| mysql INSERT INTO T2 VALUES
| ('3CCF571C1A88118801040302','072','7269','','','',''
| ,'','',3103,1,24,'2002-09-
| 01','05:46:52',0.99,13.7560,24.0480,0.0710,190.839,16,0,
| 0,1,0,0,0,0,0,0,0,0,0,3,4,0,0,0,0,0,0,10,0,'1',1,'0',0,1
| ,0,'200205010250_ampsc1','AMP',4,'-12015927269-1-95-
| 3855206',1,0);
| Query OK, 1 row affected (0.00 sec)
|
| mysql SELECT COUNT(*) FROM T2;
| +--+
| | COUNT(*) |
| +--+
| | 509 |
| +--+
| 1 row in set (0.00 sec)
|
| mysql SELECT COUNT(*) FROM T1;
| +--+
| | COUNT(*) |
| +--+
| | 782910 |
| +--+
| 1 row in set (7.43 sec)
|
| Of course DELETES are similar(actually much worse with
| a where), but deletes are not an issue here
| mysql DELETE FROM T1 WHERE date ='2002-09-01';
| Query OK, 1 row affected (40.19 sec)
|
| mysql DELETE FROM T2 WHERE date ='2002-09-01';
| Query OK, 1 row affected (0.24 sec)
|
|
| I presume the problem is that in the table definitions
| I have a couple of unique constraints and
| the INSERT time difference is due to mysql
| checking all entries in the table for a unique
| violation, hence the insert would be slow:
|
| CREATE TABLE T1 (
| corrno varchar(43) default NULL,
| a varchar(30) default NULL,
| b varchar(30) default NULL,
| c varchar(30) default NULL,
| oc varchar(30) default NULL,
| red varchar(30) default NULL,
| sanum varchar(30) default NULL,
| sbnum varchar(30) default NULL,
| tnum varchar(30) default NULL,
| o int(5) default NULL,
| d int(5) default NULL,
| cic int(6) default NULL,
| date date default NULL,
| start_time time default NULL,
| value decimal(30,2) default NULL,
| iamtoacm decimal(7,4) default NULL,
| iamtoans decimal(7,4) default NULL,
| reltorlc decimal(7,4) default NULL,
| duration decimal(12,3) default NULL,
| rcv int(3) default NULL,
| rcvabn int(1) default '0',
| loc int(3) default NULL,
| reldir int(1) default NULL,
| secode int(3) default NULL,
| trans int(3) default NULL,
| tc int(3) default NULL,
| rsp 0 int(3) default NULL,
| ccq int(5) default NULL,
| ccr int(3) default NULL,
| ccc int(3) default NULL,
| ccp int(3) default NULL,
| ccsan int(3) default NULL,
| nata int(2) default NULL,
| natb int(2) default NULL,
| natc int(2) default NULL,
| natocn int(2) default NULL,
| natredir int(2) default NULL,
| natsccpa int(2) default NULL,
| natsccpbint(2) default NULL,
| nattrue int(2) default NULL,
| acat int(2) default NULL,
| bcat int(1) default NULL,
| upi char(1) default NULL,
| upp int(3) default NULL,
| acind char(1) default NULL,
| inter int(1) default NULL,
| ech int(1) default NULL,
| length int(3) default '0',
| file varchar(30) default NULL,
| link varchar(8) default NULL,
| fileind int(11) default NULL,
| unicor varchar(100) NOT NULL default '',
| stp int(1) default '0',
| alarm int(1) default '0',
| UNIQUE KEY unicor (unicor),
| UNIQUE KEY corrno (corrno)
| ) TYPE=InnoDB;
|
|
| The question is what can be done about this?
| I guess getting rid of the unique constraints
|would help, but then I would be getting some
| unwanted duplicates. I should add that currently
| I need to do about 100,000
| inserts/10 minutes and the system is too slow for this
| as it is, while it used to be very fast. So, I am
| looking fro what can be done to avoid performance
| degradation
| as the tables fill.
| The server is a dual PIII x1000, 2GB RAM Linux 2.4.18
| machine but the inserts are done from a client(a 1000
| PIII , 1GB RAM Linux machine). Below is /etc/my.cnf
| # Example mysql config file
Re: MySQL fulltext search
On Tue, 20 Aug 2002, Steinar Kolnes wrote: I am going to make a 6.5 mill. phone directory database, and after some previous advice I am able to make a search in a fraction of a second. However there should only be a single search line, not different fields for First, Last - name, street address, Zip code and City name. The last name field in the database could also contain a company name. So here is some examples 1) First Last 2) First Last City 3) First Last Street_addr city 4) First Middle Last Street_addr city 5) First Middle 6) Company 7) Company City .. Well, there is a lot of alternatives, so the best might be a freetext. The database looks like this: create table subscriber ( idbigint unsigned not null auto_increment primary key, subscr_id bigint unsigned, telco_id int unsigned, typeidint unsigned, first varchar (40), middlevarchar (40), last varchar (40), address varchar (40), postcode int unsigned, postname varchar (40), phone varchar (12) ); My plan was to do the thing below, but the query was very slow: create table subscriber ( idbigint unsigned not null auto_increment primary key, subscr_id bigint unsigned, telco_id int unsigned, typeidint unsigned, first varchar (40), middlevarchar (40), last varchar (40), address varchar (40), postcode int unsigned, postname varchar (40), phone varchar (12), FULLTEXT (first, middle, last, address, postname, phone) ); All suggestions are appreciated, thanks in advance. Steinar, Here is a suggestion for you: Your setup should basically work but try the following (i'm not sure but it could be faster): add another column such as: srchwords varchar (217) and populate it with all words from first, middle, last, address, postname, phone. Then make your fulltext index over srchwords. 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
problem in insert a space charachter
Hi,
my name is Francesca and I've a problem with the space character using mysql.
I try to explain the problem in a way similar to mysqldebug.txt.
m: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Problem updating a CHAR(1) columns whith a space character.
Description:
I have a table with a CHAR(1) column. When i try to insert a space charcacter
(' ') in this column
I haven't an error but the column not contains ' '; I'm not able to insert
this character.
I read in the html manual that for VARCHAR columns, trailing spaces are
removed when the value is stored.
The same thing happens with CHAR?.
There is the possibility to insert this value or not?
How-To-Repeat:
I try to explain my problem describing the table and the statements that I try
to execute with mysql.
In order to create table I use this statement:
CREATE TABLE ANTI_MNY_LNDRY_RESN (
C_INSTint(2) NOT NULL,
C_ANTI_MNY_LNDRY_RESN CHAR(1) NOT NULL
);
To insert a row:
INSERT INTO ANTI_MNY_LNDRY_RESN (C_INST, C_ANTI_MNY_LNDRY_RESN) VALUES(1,' ');
The statement was executed with success (Query OK, 1 row affected (0.00 sec))
but the char inserted in column is '' and not ' '.
Originator: Francesca Sandrini
Organization: DS DataSystems
MySQL support: none
Severity: non critical
Priority: medium
Category: mysql
Class: support
Release:mysql-3.23.38
System:Windows 2000
Thanks a lot in advance,
Francesca
-
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
