RE: MySQL Database Design
Hi Brian,
By no means am I a MySQL guru (or any other database server environment
guru, for that matter), but could you simply have a reference table that
indicates the percentage of the grape used in the relevant wine?
So, you might have three tables, Wines, GrapeVariety,
Wines_GrapeVarietiesUsed (or whatever).
In Wines, you record the details of the wine in question. In
GrapeVariety you record the details of the different grapes. In
Wines_GrapeVarietiesUsed, you record the key from the Wine table, the
key from the GrapeVariety table, and the percentage of the grape
variety.
As an example:
Wines:
Wineid, winename, winedescription
1, wine1, wine description 1
2, wine2, wine description 2
3, wine3, wine description 3
GrapeVariety:
Varietyid, varietyname, varietydescription
1, variety1, variety description 1
2, variety2, variety description 2
3, variety3, variety description 3
And then in Wines_GrapeVarietiesUsed:
Wineid, varietyid, percentage
1, 1, 1
2, 2, 1
3, 1, 0.2
3, 2, 0.3
3, 3, 0.5
In this example we have 3 bottles of wine and three varieties. Wines 1
2 use 100 percent (i.e. 1) of varieties 1 and 2 respectively, whereas
wine 3 uses all three grape varieties with 20 percent of variety1, 30
percent of variety2 and 50 percent of variety3.
To perform a query that would depict all of this in a single resultset,
you might do something like:
SELECT w.winename, w.winedescription, v.varietyname,
v.varietydescription, gv.percentage FROM wines w, GrapeVariety v,
Wines_GrapeVarietiesUsed gv WHERE w.wineid = gv.wineid AND v.varietyid =
gv.varietyid;
What this delivers is a recordset that looks something like:
'wine1','wine description 1','variety1','variety description 1','1'
'wine2','wine description 2','variety2','variety description 2','1'
'wine3','wine description 3','variety1','variety description 1','0.2'
'wine3','wine description 3','variety2','variety description 2','0.3'
'wine3','wine description 3','variety3','variety description 3','0.5'
Below are the CREATE TABLE and INSERT INTO statements I used to build
this example.
Hope this helps a little,
All the best,
MW
CREATE TABLE Wines (wineid INT(10) unsigned NOT NULL auto_increment,
winename VARCHAR(100), winedescription TEXT, PRIMARY KEY (`wineid`))
TYPE = MYISAM;
CREATE TABLE GrapeVariety (varietyid INT(10) unsigned NOT NULL
auto_increment, varietyname VARCHAR(100), varietydescription TEXT,
PRIMARY KEY (`varietyid`)) TYPE = MYISAM;
CREATE TABLE Wines_GrapeVarietiesUsed (wineid INT(10) unsigned NOT NULL,
varietyid INT(10) unsigned NOT NULL, percentage float NOT NULL default
'0') TYPE=MYISAM;
INSERT INTO wines (winename, winedescription) VALUES ('wine1', 'wine
description 1');
INSERT INTO wines (winename, winedescription) VALUES ('wine2', 'wine
description 2');
INSERT INTO wines (winename, winedescription) VALUES ('wine3', 'wine
description 3');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety1', 'variety description 1');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety2', 'variety description 2');
INSERT INTO GrapeVariety (varietyname, varietydescription) VALUES
('variety3', 'variety description 3');
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (1,1,1);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (2,2,2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,1,.2);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,2,.3);
INSERT INTO Wines_GrapeVarietiesUsed (wineid, varietyid, percentage)
VALUES (3,3,.5);
-Original Message-
From: Colaluca, Brian [mailto:[EMAIL PROTECTED]]
Sent: Friday, 10 January 2003 6:56 AM
To: [EMAIL PROTECTED]
Subject: MySQL Database Design
I am in the midst of designing a personal database for keeping track of
wines. After perusing through several beginner books on MySQL and PHP,
I
have decided that my next step would be to embark upon database design.
My design is almost complete and normalized, although I do expect to be
making many tweakings as my knowledge progresses.
I have come to a brick wall on one facet of my design, however. I've
come
to understand that having a lot of NULLs in your database may be a sign
of a
poor design. And yet I'm having a problem reconciling this with the
wildly
un-uniform world of wines from around the world. For instance, I would
like
to have a table called GrapeVariety, and have the variety_id be a
primary
key. Another table would be Wine. And yet, one wine could have one
type
of grape or more.
For instance, let's say that wine XYZ has 80% GrapeA, and 20% GrapeB.
Since
my grape variety would presumably be a foreign key in the Wine table,
how
could I specify a certain *percentage* of a foreign key? I've tried
hashing
this out in numerous ways, including the addition of a Blend table
with
multiple primary keys, but anyway I slice it, there will still be an
abundance of NULLs. For
Re: May I raise a question?
- Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 10, 2003 2:00 AM Subject: RE: May I raise a question? Thanks, Gerald, I'm trying to install the MySQL. Apache Php in Windows 2000 Professional. A book telling me that I should install them in the above sequence. Actually the error message is before the installation of Apache, is it the root cause? Should I try again after I start the server? The MySQL installation stands on its own, and has, what MySQL is concerned, nothing to do with Apache or PHP. Your book is right when suggesting the install order; as, typically, you want to compile Apache with something like --with-mysql=/usr/local; and then, thereafter, PHP with something like --with-apxs=/usr/local/www/bin/apxs. None of this, I suddenly realize, is actually relevant, as you are on Windoze, and probably are not compiling much of anything. :) What you need to be concentrating on, is making sure MySQL runs as a service. If memory serves me correctly, you will, depending on your binary, have to run that binary with the --install parameter; like so: mysqld-max-nt --install. After that, check that it exists as a service, and start the service if necessary. - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Before posting, 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: need solution
is mysql daemon started? --- brchang [EMAIL PROTECTED] ha scritto: Dear Michael Widenius: After installation of mysql-3.23.52-win in Win2000 platform, I have the problem with the errors, as shown below, once I try to start mysql. Could you provide me the solution to this problem as soon as possible. Thanks! /*-Error Message 1 -*/ C:\mysql\binmysql ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) /*-- --*/ /*-Error Message 2 -*/ C:\mysql\binmysqladmin -u root -p password Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to MySQL server on 'localhost' (10061)' Check that mysqld is running on localhost and that the port is 3306. You can check this by doing 'telnet localhost 3306' /*-- --*/ Best regards, Bao Rong Chang EE Department at Cheng-Shiu Institute of Technology, Taiwan. - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, 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: May I raise a question?
Dear all, Your endeavour and speedy action impress me. Thanks. Seed Chan -Original Message- From: admin [SMTP:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 3:11 PM To: Chan, Seed; gerald.clark Cc: admin; mysql Subject: Re: May I raise a question? - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 10, 2003 2:00 AM Subject: RE: May I raise a question? Thanks, Gerald, I'm trying to install the MySQL. Apache Php in Windows 2000 Professional. A book telling me that I should install them in the above sequence. Actually the error message is before the installation of Apache, is it the root cause? Should I try again after I start the server? The MySQL installation stands on its own, and has, what MySQL is concerned, nothing to do with Apache or PHP. Your book is right when suggesting the install order; as, typically, you want to compile Apache with something like --with-mysql=/usr/local; and then, thereafter, PHP with something like --with-apxs=/usr/local/www/bin/apxs. None of this, I suddenly realize, is actually relevant, as you are on Windoze, and probably are not compiling much of anything. :) What you need to be concentrating on, is making sure MySQL runs as a service. If memory serves me correctly, you will, depending on your binary, have to run that binary with the --install parameter; like so: mysqld-max-nt --install. After that, check that it exists as a service, and start the service if necessary. - Mark System Administrator Asarian-host.org --- If you were supposed to understand it, we wouldn't call it code. - FedEx - Before posting, 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
cannot get foreign keys to work
i have been using the following sql to try and get foreign keys
working, the table creation works fine but when i try and delete data
from the parent table it deletes as would normally happen in mysql,
also the desired results do not happen if i use RESTRICT.
is this a problem to do with indexes???
DROP TABLE IF EXISTS parent;
CREATE TABLE parent(id INT NOT NULL, name char(5), PRIMARY KEY (id))
TYPE=INNODB;
DROP TABLE IF EXISTS child;
CREATE TABLE child(id INT NOT NULL, parent_id INT, INDEX par_ind
(parent_id), PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE RESTRICT
) TYPE=INNODB;
insert into parent(name) values('asdfg');
insert into child(parent_id) values(last_insert_id());
system
os x 10.2.3
mysql 3.23.53-entropy.ch
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting max value from sets
On Thu, 2003-01-09 at 22:06, Benjamin Dixon wrote: Hi all, I'm trying to figure out a way to select a group of maximums from a set such that each value pair's greatest value is in the result set. For example, let's say I have this table Value_Pairs: Name | Value Bob1 Joe7 Bob2 Don3 Don4 Bob6 The result I want is like this: Name | Value Bob6 //Bob's highest value in the table Joe7 //Joe's highest value in the table Don4 //Don't highest value in the table The following might work: mysql select name,max(val) from res group by name ; +--+--+ | name | max(val) | +--+--+ | Bob |6 | | Don |4 | | Joe |7 | +--+--+ 3 rows in set (0.00 sec) Regards, KentV --- | / / KENT VILHELMSEN - Eniro | / / |/\ / mob +47 40213917 OSLO +47 22583917 STH +46 87043619 / \/* Standardise your content to fit ALL browsers! * --- So I'm looking for distinct maximums. Is it possible to do this *with a single query* in MySQL? I've tried a number of things and nothing comes close. 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
Using more than one CPU on FreeBSD?
Hi, I've inherited a FreeBSD/MySQL database-server (Compaq DL360, dual P3 1GHz), running (at the moment): Server version: 4.0.3-beta However, as far as I can tell, MySQL/FreeBSD 4.6.2-RELEASE still can't agree on utilizing more than one CPU. My question is this: A year or two ago, someone mentioned simply running two mysqld's on the same database-files (using file-locking) and then, using some form for loadbalancing between the two, was able to use more than one CPU for the mysqlds. Is this still the recommended way of doing this? I know I could use Linuxthreads, but the times I've tried them in the past, they have done more harm than good (low performance, unstability etc). Any advice would be appreciated :) Kind regards, Tommy Eriksen - Before posting, 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[4]: mysql 4.0.8- crash on TCP connection
Hi, It seems is happens to all which use InnoDB.Is announced that will be solved in 4.0.9. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Johannes Ullrich [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 10, 2003 4:28 AM Subject: Re: Re[4]: mysql 4.0.8- crash on TCP connection On Thu, 9 Jan 2003 22:56:04 +0200 Gelu Gogancea [EMAIL PROTECTED] wrote: All this is very interesting, BUT i have two binary builds (4.0.7 4.0.8), Ha,Hayou are gentle. (load avg 10-60 Query/sec), and 4.0.8 crash (in some hardware/software) after 2 seconds work :( Did you see any relationship with 'replication'? I just downloaded 4.0.8. All it did was act as a slave. It crashed after a large 'load table' from the master and now refuses to start. Looks like it crashes as it read the relay-log-info file or just after it does so. at least thats my latest theory after doing more backtrace resolving, stracing and experimenting. did submit one or two bug reports about this. -- [EMAIL PROTECTED] Collaborative Intrusion Detection join http://www.dshield.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, 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: Bug in foreign keys
On Thu, 09 Jan 2003 23:04:37 +0100 Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED] wrote: Rafal, alter table rezerwacje add foreign key (nip_h,nrpok_p) references pokoje(nip_h,nrpok_p); ERROR 1005: Can't create table './test/#sql-932_4.frm' (errno: 150) Most probably, you are using MySQL = 4.0.1 but 4.0.5. I had these kind of problems with ALTER TABLE with 4.0.1 and 4.0.4, but not with 4.0.7 anymore (hey, this rhymes :). I guess it has nothing to do with the foreign keys, but with the temporary form table MySQL creates when you do an ALTER TABLE. Anyway, the problem seems to be fixed, so you should update to = 4.0.7. Well, I'm using 4.0.8 -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, 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 fulltext. Question about the stopword list
Hi! On Jan 07, Erlend Hopso Stromsvik wrote: What I can easily do without breaking 4.0.x gamma status, is to add command line switch --disable-fulltext-stopwords. It can help as a temporary solution, untill a proper fix - per-index options, that is - will be implemented. That would be helpful for me, but what about Thomas Spahni's suggestion? In todo already :) I remember working on a project when I was school where we wrote this program using autogenerated stopword lists and N-gram matching for the text and search string. By this the stopword list was not hard coded.. What is N-gram matching ? n-grams are used to describe objects as vectors. This makes it possible to apply geometric, statistical and other mathematical techniques, which are well defined for vectors, but not for objects in general. For example, one of the most common uses is to define a similarity measure between textual documents based on the application of a mathematical function to the vector representations of the documents Yep. This is how fulltext search in MySQL works (not IN BOOLEAN MODE, of course). It is called vector space model, though words are used as vector coordinates, not n-grams. An n-gram is a set of n consecutive characters extracted from a word. The main idea behind this approach is that, similar words will have a high proportion of n-grams in common. Typical values for n are 2 or 3, these corresponding to the use of digrams or trigrams, respectively. So if you have the word 'computer' you'll get the following digrams: *c, co, om, mp, pu, ut, te, er, r* and the trigrams: **c,*co,com,omp,mpu,put,ute,ter,er*,r** Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, 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: Full text limitation?
Hi! SELECT count(a.rsori) pippo FROM ecoras a, ecotxt b WHERE MATCH ( b.rstxt ) AGAINST ( '-linux' IN BOOLEAN MODE ) AND a.rsori = b.rsori AND a.rscod = b.rscod; If I execute a query like in the example, I don't obtain all rows where this word is not present. Instead if I use a leading plus sign I obtain the right resultset. I do not think it is a bug, maybe it is a limitation in full-text search and I would know how can I obtain a valid resultset. It's not a bug, it's how fulltext search was designed to work. `-word' works as a filter, removing rows from result set. `+word' and `word' add rows to result set. Naturally, if there is neither `+word' nor `word', the result set is empty, and `-word' is useless. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, 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: May I raise a question?
If it was me I would simplify the problem: A. tear out apache and php and B. Get mysql installed and working C. shut down Mysql and install apache and get it working D. shut down apache, keep mysql shut down and install Php and get it working. then start up each, and test as you bring up the full system to make sure each object is acting nice this will isolate the bugs (if any) in the boundaries between B and C, or C and D, etc. not being a computer science guru, i have to do things gentlyelse i crash and burn and kick the dog. Jim -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 5:01 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: May I raise a question? Thanks, Gerald, I'm trying to install the MySQL. Apache Php in Windows 2000 Professional. A book telling me that I should install them in the above sequence. Actually the error message is before the installation of Apache, is it the root cause? Should I try again after I start the server? Best rgds, Seed Chan System Services Management Branch Services * 2962 7431 * 29173503 / 29173504 * [EMAIL PROTECTED] -Original Message- From: gerald.clark [SMTP:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 10:20 PM To: Chan, Seed Cc: gerald.clark; mysql Subject: Re: May I raise a question? What OS are you running? Did you start the server? What is in the error logs? [EMAIL PROTECTED] wrote: Dear Editor, After installed the MySQL version 3.23.53, I tried to test the MySQL by typing the following command, relevant error message appears, please advise how to fix. Command: Display Error Message: mysqlshowmysqlshow: Can't connect to MySQL server on 'localhost' (10061) mysqladmin CREATE test mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to MySQL server on 'localhost' (10061)' Check that mysqld is running on localhost and that the port is 3306. You can check this by doing 'telnet localhost 3306' mysql test ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) telnet localhost 3306Connecting To localhost...Could not open a connection to host on port 3306 : Connect failed Best rgds, Seed Chan Seed Chan System Services Management Branch Services * 2962 7431 * 29173503 / 29173504 * [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 - Before posting, 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: Bug in foreign keys
On Thu, 09 Jan 2003 23:41:46 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: Rafal, I tested also with the `hotele`, and it worked ok. Please provide your my.cnf and MySQL version number. And test if you can repeat the error. Regards, Heikki MySQL version 4.0.8, my my.cnf is as follows: # Example mysql config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # a important part and systems up to 128M very MySQL is used together with # other programs (like a web server) # # 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=16M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M #set-variable = max_user_connections =2 log-bin server-id = 1 default-character-set = latin2 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M:autoextend innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates and the test case: mysql drop table pokoje,rezerwacje,hotele; Query OK, 0 rows affected (1.13 sec) mysql create table hotele ( - nip_h int(10) not null, - nazwa_h varchar(100) not null, - miasto_h varchar(50) not null, - kodpocz_h int(5) not null, - adres_h varchar(50), - email_h varchar(50), - kontobank_h int(32), - wyprzedaz_h varchar(10), - standard_h varchar(20), - opis_h varchar(200), - primary key (nip_h) - ) - type=innodb; Query OK, 0 rows affected (0.17 sec) mysql create table pokoje ( - nrpok_p char(10) not null, - nip_h int(10) not null, - lozka_p char(2), - tv_p char(1), - lazienka_p char(1), - cena_p int(10), - zaliczka_p int(10), - index nip_h_index (nip_h,nrpok_p), - foreign key (nip_h) references hotele (nip_h) on delete cascade, - primary key (nrpok_p, nip_h) - ) - type=innodb; Query OK, 0 rows affected (0.21 sec) mysql create table rezerwacje ( - id_r int(10) not null, - pesel_k int(11), - nip_h int(10) not null, - nrpok_p char(10) not null, - data_r date, - od_r date, - do_r date, - cena_r int(10), - zaliczka_r int(5), - zaplac_r char(1), - wplaczal_r char(1), - index nip_h_index (nip_h,nrpok_p), - foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on delete se t null, - index pesel_k_index (pesel_k), - primary key(id_r)) type=innodb; ERROR 1005: Can't create table './test/rezerwacje.frm' (errno: 150) Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.0.8-gamma-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql drop database test; Query OK, 0 rows affected (0.08 sec) mysql create database test; Query OK, 1 row affected (0.00 sec) mysql CREATE TABLE `hotele` ( - `nip_h` int(10) NOT NULL default '0', - `nazwa_h` varchar(100) NOT NULL default '', - `miasto_h` varchar(50) NOT NULL default '', - `kodpocz_h` int(5) NOT NULL default '0', - `adres_h` varchar(50) default NULL, - `email_h` varchar(50) default NULL, - `kontobank_h` int(32) default NULL, - `wyprzedaz_h` varchar(10) default NULL, -
Re: cannot get foreign keys to work
Dear Ben,
i have been using the following sql to try and get foreign keys
working, the table creation works fine but when i try and delete data
from the parent table it deletes as would normally happen in mysql,
also the desired results do not happen if i use RESTRICT.
is this a problem to do with indexes???
DROP TABLE IF EXISTS parent;
CREATE TABLE parent(id INT NOT NULL, name char(5), PRIMARY KEY (id))
TYPE=INNODB;
DROP TABLE IF EXISTS child;
CREATE TABLE child(id INT NOT NULL, parent_id INT, INDEX par_ind
(parent_id), PRIMARY KEY (id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE RESTRICT
) TYPE=INNODB;
insert into parent(name) values('asdfg');
insert into child(parent_id) values(last_insert_id());
When you installed MySQL, did you follow the instructions in the manual
to enable the innobb features? You can check by:
./mysqladmin variables | grep have_innodb
If you see this: sorry, it's fine and you need a better guru :)
| have_innodb | YES |
If you see this: Read the doc snippets below and follow the
instructions in the MySQL manual.
| have_innodb | DISABLED |
You can also verify the existance of the foreign keys by:
mysql show create table parent;
Snippet from the MySQL documentation:
2.3.1 Quick Installation Overview
If you want to have support for InnoDB tables, you should edit the
/etc/my.cnf file and remove the # character before the parameter that
starts with innodb_ See section 4.1.2 `my.cnf' Option Files, and
section 7.5.2 InnoDB Startup Options.
Unfortunately, MySQL silently accepts and ignores 'innodb' type tables
when innodb is disabled. All the foreign key constraints are accepted
and ignored too.
Good luck,
Alan.
-
Before posting, 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: Bug in foreign keys
Rafal, the test case below is different from the one you posted yesterday. Below you are trying to add a constraint foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on delete set null though you have declared nip_h int(10) not null in 'rezerwacje'. InnoDB does not accept the constraint because 'on delete set null' is not sensible when you declare the column not null. I have now added a note to InnoDB manuals about this. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query . Subject: Re: Bug in foreign keys From: Rafal Jank Date: Fri, 10 Jan 2003 11:10:35 +0100 On Thu, 09 Jan 2003 23:41:46 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: Rafal, I tested also with the `hotele`, and it worked ok. Please provide your my.cnf and MySQL version number. And test if you can repeat the error. Regards, Heikki MySQL version 4.0.8, my my.cnf is as follows: # Example mysql config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # a important part and systems up to 128M very MySQL is used together with # other programs (like a web server) # # 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=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M #set-variable = max_user_connections =2 log-bin server-id = 1 default-character-set = latin2 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M:autoextend innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates and the test case: mysql drop table pokoje,rezerwacje,hotele; Query OK, 0 rows affected (1.13 sec) mysql create table hotele ( - nip_h int(10) not null, - nazwa_h varchar(100) not null, - miasto_h varchar(50) not null, - kodpocz_h int(5) not null, - adres_h varchar(50), - email_h varchar(50), - kontobank_h int(32), - wyprzedaz_h varchar(10), - standard_h varchar(20), - opis_h varchar(200), - primary key (nip_h) - ) - type=innodb; Query OK, 0 rows affected (0.17 sec) mysql create table pokoje ( - nrpok_p char(10) not null, - nip_h int(10) not null, - lozka_p char(2), - tv_p char(1), - lazienka_p char(1), - cena_p int(10), - zaliczka_p int(10), - index nip_h_index (nip_h,nrpok_p), - foreign key (nip_h) references hotele (nip_h) on delete cascade, - primary key (nrpok_p, nip_h) - ) - type=innodb; Query OK, 0 rows affected (0.21 sec) mysql create table rezerwacje ( - id_r int(10) not null, - pesel_k int(11), - nip_h int(10) not null, - nrpok_p char(10) not null, - data_r date, - od_r date, - do_r date, - cena_r int(10), - zaliczka_r int(5), - zaplac_r char(1), - wplaczal_r char(1), - index nip_h_index (nip_h,nrpok_p), - foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on delete se t null, - index pesel_k_index (pesel_k), - primary key(id_r)) type=innodb; ERROR 1005: Can't create table './test/rezerwacje.frm' (errno: 150)
Re: Order by does not use an index when it should.
On Tue, Jan 07, 2003 at 12:31:36PM +0100, harm wrote: Anybody else who has any idea why the index are not used as they should? I've got same things with 3.23.xx and select query through a TCP/IP connection. I don't know why, but you can solve this issue using the USE INDEX syntax for select queries ... http://www.mysql.com/doc/en/SELECT.html Hmmm, that does help. It does not use the filesort anymore. I cannot use the 'use index' hardcoed in this query (is is not always the fastest). I think the following is happening: A select with only constants and an 'order by' does not use the index for the orderby if there are 2 indexes which differ only in that the last one has the 'order by' column append to it. For example: select * from foo where a=const order by b; indexes: first_index (a), second_index (a,b) The fix should be easy, drop the first_index (It is useless anyway) I'll test this hypothesis tonight when I can do the 20 minute taking alter table :) Confirmed, it does use the index correct since I dropped first_index. Seems like a little bug in the optimiser. Thanks for the help. The load on the server is notecable lower (about 1.00 down !) now the sort uses the index as well! Harmen -- The Moon is Waxing Crescent (49% of Full) - Before posting, 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 Database Design
i was playing around with ideas below, so they may be worthless chatter :-) what i was thinking I'll just assume the wine world is not wildly un-uniform... and see where that gets me... :-) and wine is just an object... but the wine has many grape varieties sounds a hell of a lot like one to many in sql thanks Jim ===start chatter n = null anything else = not null all attributes analysis a1 a2 a3 a4 a5 a6 a7 wine1 x n x n x n x wine2 y x x n n x n wine3 z x x x x n n 8 nulls common attributes table parent ID a1 a3 wine1 x x wine2 y x wine3 z x special attributes table 1 child0 ID a5 a7 wine1 x x wine3 x n special attributes table 2 child1 ID a2 a6 wine2 x x wine3 x n special attributes table 3 child2 ID a4 wine3 x 2 nulls ===end chatter -Original Message- From: Michael T. Babcock [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 09, 2003 6:09 PM To: Colaluca, Brian Cc: [EMAIL PROTECTED] Subject: Re: MySQL Database Design Colaluca, Brian wrote: I have come to a brick wall on one facet of my design, however. I've come to understand that having a lot of NULLs in your database may be a sign of a poor design. And yet I'm having a problem reconciling this with the wildly un-uniform world of wines from around the world. For instance, I would like to have a table called GrapeVariety, and have the variety_id be a primary key. Another table would be Wine. And yet, one wine could have one type of grape or more. Just an idea ... to get your head spinning (and some sample queries): Wine - ID int unsigned not null auto_increment primary key, Name ... Winery ... Grapes - ID int unsigned not null auto_increment primary key, Name ... Vineyard? ... GrapesInWine - WineID int unsigned not null, GrapesID int unsigned not null, Percentage int unsigned not null ... where Percentage is between 0 and 100. Then you can, to insert a wine named Foo with 50% of each Grape1 and Grape2: INSERT INTO Wine (Name) VALUES (Foo); SELECT @WinesID := last_insert_id();# I'm using server variables here for the sake of demo ... INSERT INTO Grapes (Name) VALUES (Grape1); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WinesID, @GrapesID, 50); INSERT INTO Grapes (Name) VALUES (Grape2); SELECT @GrapesID := last_insert_id(); INSERT INTO GrapesInWine (WineID, GrapesID, Percent) VALUES (@WineID, @GrapesID, 50); Then, to find out what's in the wine named Foo: SELECT * FROM Grapes LEFT JOIN GrapesInWine ON Grapes.ID = GrapesID LEFT JOIN Wine ON WinesID = Wine.ID WHERE Wine.Name = Foo; Or, to find the amounts of Grape1 in all wines: SELECT * FROM Wine LEFT JOIN GrapesInWine ON WineID = Wine.ID LEFT JOIN Grapes ON Grapes.ID = GrapesID WHERE Grapes.Name = Grape1; -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, 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: Transaction and Row Locking Feature
Robert, - Original Message - From: Robert Tam [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, January 10, 2003 7:43 AM Subject: Transaction and Row Locking Feature Hello, I am a new user to MySQL. I need innoDB's transaction and row locking feature in MySQL. Could someone clarify whether the latest 3.23.54a of MySQL fully supports innoDB transaction and row locking or do I have to use beta/gamma version of MySQL 4.0.x. Thanks in advance. transactions and row locking are fully supported in the stable 3.23. If you want to emulate Oracle with different transaction isolation levels, they are available starting from 4.0.5. R.T. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com 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: Re[4]: mysql 4.0.8- crash on TCP connection
Hi! - Original Message - From: Gelu Gogancea [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, January 10, 2003 1:25 PM Subject: Re: Re[4]: mysql 4.0.8- crash on TCP connection Hi, It seems is happens to all which use InnoDB.Is announced that will be solved in 4.0.9. The bug has nothing to do with InnoDB. I think the bug is the following: http://www.mysql.com/doc/en/News-4.0.9.html Bugs fixed: ... A security patch in 4.0.8 causes the mysqld server to die if the remote hostname can't be resolved. This is now fixed. A general note: the bug list of MySQL is the changelog in the manual, because it lists also upcoming releases. By looking there you often find the explanation why some version does not work. For InnoDB the bug list is http://www.innodb.com/ibman.html#InnoDB_history. Regards, Gelu Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Johannes Ullrich [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 10, 2003 4:28 AM Subject: Re: Re[4]: mysql 4.0.8- crash on TCP connection On Thu, 9 Jan 2003 22:56:04 +0200 Gelu Gogancea [EMAIL PROTECTED] wrote: All this is very interesting, BUT i have two binary builds (4.0.7 4.0.8), Ha,Hayou are gentle. (load avg 10-60 Query/sec), and 4.0.8 crash (in some hardware/software) after 2 seconds work :( Did you see any relationship with 'replication'? I just downloaded 4.0.8. All it did was act as a slave. It crashed after a large 'load table' from the master and now refuses to start. Looks like it crashes as it read the relay-log-info file or just after it does so. at least thats my latest theory after doing more backtrace resolving, stracing and experimenting. did submit one or two bug reports about this. -- [EMAIL PROTECTED] Collaborative Intrusion Detection join http://www.dshield.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, 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: sql delete question
Hello Adolfo, Yes I have tried it too but it did not work. Anyway i have done it programatically in my app. Thanks. Adolfo Bello [EMAIL PROTECTED] wrote: I have two tables whose structures are below. - Table1 - table1id int not null auto_increment data varchar(30) - Table2 - table2id int not null auto_increment table1id int not null data varchar(30) These two tables are connected to each other with the table1id column. I need to delete rows in table1 which have no corresponding table1id values in table2 and also i need to delete rows in table2 which have no corresponding table1id values in table1. Any comments? Can you try a multi table DELETE? I haven't done it but I would be a shot at something like (adapted from MySQL Manual) DELETE t1 FROM t1,t2 WHERE t1.id=t2.id AND t2.id IS NULL Adolfo - Before posting, 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 __ The NEW Netscape 7.0 browser is now available. Upgrade now! http://channels.netscape.com/ns/browsers/download.jsp Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.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: Starting MySQL
You should check the manual: http://www.mysql.com/doc/en/Can_not_connect_to_server.html. It shows the steps you can do to solve the problem. On Thu, 2003-01-09 at 23:45, cam vong wrote: I'm currently using Red Hat 7.1. I have loaded the apache, php, and mysql rpm. I seem to be having trouble starting MySQL. It gives me the following error: Can not connect to localhost MySQL server through socket /var/lib/mysql/mysql.sock (111). Can some help me, please? _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - Before posting, 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 -- Diana Soares - Before posting, 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
2013 Error
Hi, Having a problem with accessing the mysql database from a remote machine. I am typing in mysql -h ???.??.???.?? -u ?? -p. It asked for password .. do that then the reply message is ERROR 2013: Lost connection to MySQL server during query. Have even tryed replacing IP with a host name and get the same error. Would appreciate any advice Regards Troy - Before posting, 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
2013 Error
Hi, Having a problem with accessing the mysql database from a remote machine. I am typing in mysql -h ???.??.???.?? -u ?? -p. It asked for password .. do that then the reply message is ERROR 2013: Lost connection to MySQL server during query. Have even tryed replacing IP with a host name and get the same error. Would appreciate any advice Regards Troy - Before posting, 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
Running out of memory - memory leakage?
Hi, I have a RedHat 8 box /w MySQL 3.23.54, /w 640M RAM. It looks like there are some sort of a memory leakage somewhere in the system, because slowly (in a day or two) I run out of memory. Even SWAP space gets used up at the end (slowly but steadily, fluctuating). If I restart MySQL, nothing happens. If I restart the services using MySQL (e.g. Apache), nothing happens. But if restart BOTH of them (MySQL AND Apache) suddenly my memory is back again and the SWAP is reclaimed also. How can I find out where my memory goes? Any ideas? - Csongor - Before posting, 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 question (group by) - please help
Hi. I want to calculate how many rows my query which uses 'GROUP BY' returns. The query: SELECT something FROM test WHERE (kid=1) OR (kid=2) OR (kid=4) GROUP BY cid,aid; Is it posible to get number of rows with such a query from MySQL v3.23.49? If it isn't posible ... is it posible in MySQL 4.x? I think a lot about this problem and I realize only the following solution: CREATE TEMPORARY TABLE t (i INT); INSERT INTO t (i) SELECT aid FROM the_query_above SELECT COUNT(*) FROM t; DROP TABLE t; But this isn't so elegant. I want to do so on data generated by: CREATE TABLE test ( kid INT, aid INT, cid INT ); INSERT INTO test (kid, aid, cid) VALUES ( 1, 0, 1), ( 2, 2, 2), ( 1, 3, 2), ( 2, 3, 2), ( 4, 4, 2), ( 4, 0, 3), ( 3, 3, 4), ( 4, 3, 4); Regards, Dezo - Before posting, 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 monitoring
I didn't read the original thread...but go to CPAN and check out MyTop.pl. Just like unix Top process watch application, but for mysql... pretty cool! -Chris On Thursday 09 January 2003 06:02 pm, Michael Weiner wrote: There are a few solutions out there for you. I recommend you check out RRDTOOL, and associated packages at http://people.ee.ethgz.ch/~oetiker/webtools/rrdtool/ and also look for a took called cactus, it absolutely rules. THere are others, but rrdtool, has a myriad of contributed packages it can work with to monitor email servers, bandwidth, and just about anything you can think of. Enjoy Michael Weiner spamfilteraide sql,query,queries,smallint /spamfilteraide - Before posting, 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 on large server
I rade some where that in the lateste MySQL versopn you can use 1000 to 1400 concurrent connection.Pls cross check in mysql site. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: mysql list [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, January 09, 2003 5:36 PM Subject: Re: MySQL on large server On Thu, Jan 09, 2003 at 08:55:36AM +, mysql list wrote: Hi, I was wondering if anybody has built MySQL 3.23 from source that can handle a high number of connections threads. I've have tried MySQL binaries in the past (not RPMs), but these have had stability/load problems. When building from source I don't have those issues, but I am limited by the inbuilt limits (of glibc,etc...) How many connections do you need? If memory serves, the master db behind Slashdot was handling around 700 connections on a 4 processor P3 Xeon with 4GB RAM. But it's been a while since I've talked with Brian about it. I need to build MySQL 3.23 on a production server running RedHat 7.2, patched glibc (2.2.5-42) and a custom kernel (2.4.19-2 SMP). Hardware contains Dual Xeon 2.4GHz (hyperthreading disabled) and 6GB RAM. With that kind of RAM and horespower you should be able to go well above 500 connections. I'd say more on this topic, but I've not really needed to push MySQL in that direction. We tend to use more boxes that cost less and replicate data. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 25 days, processed 863,460,485 queries (388/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: Bug in foreign keys
Thank You!! Rafal, the test case below is different from the one you posted yesterday. Below you are trying to add a constraint foreign key(nip_h,nrpok_p) references pokoje(nip_h,nrpok_p) on delete set null though you have declared nip_h int(10) not null in 'rezerwacje'. InnoDB does not accept the constraint because 'on delete set null' is not sensible when you declare the column not null. I have now added a note to InnoDB manuals about this. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query . Subject: Re: Bug in foreign keys From: Rafal Jank Date: Fri, 10 Jan 2003 11:10:35 +0100 On Thu, 09 Jan 2003 23:41:46 +0200 Heikki Tuuri [EMAIL PROTECTED] wrote: Rafal, I tested also with the `hotele`, and it worked ok. Please provide your my.cnf and MySQL version number. And test if you can repeat the error. Regards, Heikki MySQL version 4.0.8, my my.cnf is as follows: # Example mysql config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # a important part and systems up to 128M very MySQL is used together with # other programs (like a web server) # # 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=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M #set-variable = max_user_connections =2 log-bin server-id = 1 default-character-set = latin2 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables innodb_data_file_path = ibdata1:25M;ibdata2:37M;ibdata3:100M:autoextend innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=5M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=16M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates and the test case: mysql drop table pokoje,rezerwacje,hotele; Query OK, 0 rows affected (1.13 sec) mysql create table hotele ( - nip_h int(10) not null, - nazwa_h varchar(100) not null, - miasto_h varchar(50) not null, - kodpocz_h int(5) not null, - adres_h varchar(50), - email_h varchar(50), - kontobank_h int(32), - wyprzedaz_h varchar(10), - standard_h varchar(20), - opis_h varchar(200), - primary key (nip_h) - ) - type=innodb; Query OK, 0 rows affected (0.17 sec) mysql create table pokoje ( - nrpok_p char(10) not null, - nip_h int(10) not null, - lozka_p char(2), - tv_p char(1), - lazienka_p char(1), - cena_p int(10), - zaliczka_p int(10), - index nip_h_index (nip_h,nrpok_p), - foreign key (nip_h) references hotele (nip_h) on delete cascade, - primary key (nrpok_p, nip_h) - ) - type=innodb; Query OK, 0 rows affected (0.21 sec) mysql create table rezerwacje ( - id_r int(10) not null, - pesel_k int(11), - nip_h int(10) not null, - nrpok_p char(10) not null, - data_r date, - od_r date, - do_r date, - cena_r int(10), - zaliczka_r int(5), - zaplac_r char(1), - wplaczal_r char(1), - index nip_h_index (nip_h,nrpok_p), - foreign key(nip_h,nrpok_p) references
mysql / innodb foreign key
hi to all, is it still true that mysql/innodb needs explicit index creation on foreign keys? why can't i use a standard syntax for foreign keys creations? i have a database schema (ddl) with over 50 tables and i was trying to create the database on mysql when i receive a lot of errors like this: ERROR 1005: Can't create table (errno 150) how can i create the database without creating explicitly an index on each foreign keys of my database? any suggestions are appreciated. thanks to all. __ Yahoo! Cellulari: loghi, suonerie, picture message per il tuo telefonino http://it.yahoo.com/mail_it/foot/?http://it.mobile.yahoo.com/index2002.html - Before posting, 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: Using more than one CPU on FreeBSD?
Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Fri, 10 Jan 2003, Tommy F. Eriksen wrote: Date: Fri, 10 Jan 2003 09:14:18 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Using more than one CPU on FreeBSD? Hi, I've inherited a FreeBSD/MySQL database-server (Compaq DL360, dual P3 1GHz), running (at the moment): Server version: 4.0.3-beta However, as far as I can tell, MySQL/FreeBSD 4.6.2-RELEASE still can't agree on utilizing more than one CPU. My question is this: A year or two ago, someone mentioned simply running two mysqld's on the same database-files (using file-locking) and then, using some form for loadbalancing between the two, was able to use more than one CPU for the mysqlds. Is this still the recommended way of doing this? I know I could use Linuxthreads, but the times I've tried them in the past, they have done more harm than good (low performance, unstability etc). Any advice would be appreciated :) Kind regards, Tommy Eriksen - Before posting, 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: post installation error
You don't say how you started the server. It looks to me like you ran mysqld from the /root directory ? Error 13 is permission denied. Why would it be looking in /root/tmp? Use the mysql.start script. Black Eagle wrote: Hey guys, I've just installed mysql from source 4.23.54a But after ./configure --prefix=/usr/local/mysql make make install I makes an error that there's no mysql user .. so I created one ! And I changed the owner of /usr/local/mysql/var/ and recursively to mysql:mysql . now it gives me that error but I dont know what to do : 030110 00:38:39 mysqld started ./usr/local/mysql/libexec/mysqld: Can't read dir of '/root/tmp' (Errcode: 13) 030110 0:38:39 /usr/local/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 030110 00:38:39 mysql ended now what ? he told me he needed to be run as mysql and now he complaints he can't access /root/tmp ... hey .. for the ./mysql/host.frm . I really don't know what 2 do Any ideas ? thx black eagle - Before posting, 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: Calculate time span in mysql
You need to read the manual on date and time functions. Convert each to seconds, subtract, and convert back to time. You may have another problem, though. You can't calculate an interval based solely on time. You need the date too, unless all transactions are less than 24 hours in length. Paul Choy wrote: Hi : I am trying to calculate the time span of listing a large table database in mysql and I run into trouble where I am trying to use curtime() - value stored in startime.But I do not know how to get the stored value out from startime.Does anybody knows how I can get value out from startime where I can do subtraction. Thanks mysql select * from time; +--+ | startime | +--+ | 17:06:35 | | 17:09:39 | | 00:00:00 | | 13:27:49 | | 15:04:52 | +--+ 5 rows 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: Starting MySQL
Start the server. cam vong wrote: I'm currently using Red Hat 7.1. I have loaded the apache, php, and mysql rpm. I seem to be having trouble starting MySQL. It gives me the following error: Can not connect to localhost MySQL server through socket /var/lib/mysql/mysql.sock (111). Can some help me, please? - Before posting, 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
logrotate problem with mysql
I'm using mysql that was distributed by mysql.com. I seem to be having
a problem with logrotate. When I run logrotate manually on mysql only,
the logs seem to rotate fine. But, when it runs in the cron job, it
creates the new log, but mysql continues to log to the old log.
My mysql logrotate entry looks like:
/var/log/mysqld.log {
create 644 mysql mysql
notifempty
daily
rotate 2
missingok
nocompress
postrotate
# just if mysqld is really running
if test -n `ps acx|grep mysqld`; then
/usr/bin/mysqladmin flush-logs
fi
endscript
}
I have also tried using mysqladmin refresh. Any ideas why the logs aren't
being rotated properly under cron?
--
Jiann-Ming Su [EMAIL PROTECTED] 404-712-2603
Development Team Systems Administrator
General Libraries Systems Division
--
redhat-list mailing list
unsubscribe mailto:[EMAIL PROTECTED]?subject=unsubscribe
https://listman.redhat.com/mailman/listinfo/redhat-list
-
Before posting, 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 startup (FYI)
Just for the sake of showing what we do, here's our setup for MySQL at
all our sites.
We use Dan Bernstein's daemontools package to monitor all our services,
MySQL included. You can download and install them easily from
http://cr.yp.to/daemontools.html. They won't touch your system files
except by adding a line (by default) to your /etc/inittab to start
svscan, the service starter.
Having this installed and working, we create a /var/mysql/service
directory and /var/mysql/service/log.
/var/mysql/service/log/run is an executable file containing one line to
log all output with nanosecond timestamps to a file: exec setuidgid
logging multilog t /var/log/mysql/
/var/mysql/service/run contains a shell script to start mysqld:
#!/bin/sh
sleep 2 # To make `mysqladmin shutdown` work properly
exec 21
exec envdir ./env /usr/bin/safe_mysqld \
--log-slow-queries=/var/log/mysql/slow_queries \
--log-bin=/var/log/mysql/transactions \
--log=/var/log/mysql/queries
{eof}
We're using `envdir` to empty and initialize the environment so
/var/mysql/service/env contains one file for each environment variable
to be set, including PATH, TMPDIR, TZ, UMASK and UMASK_DIR.
After the directories are all set up and the `run` files are executable,
simply:
$ cd /service
$ ln -s /var/mysql/service mysqld
... and the MySQL service will start up, monitored by supervise and
restarted automatically if killed (after the two second sleep in the run
file). The sleep is there to stop respawns from being too fast and to
keep from confusing mysqladmin; when you do mysqladmin shutdown, it gets
confused if mysqld starts back up too soon (thinking it didn't shut down
yet I suppose).
Just FYI ... :-)
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd. (sql)
http://www.fibrespeed.net/~mbabcock
-
Before posting, 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: Version 4 safe to use?
Cool, you work for Yahoo? I'd love to know the extent to which Yahoo uses PHP and MySQL these days. What is the average queries/sec that you are getting with MySQL - what server setup, etc? Maximo. At 17:12 9/1/2003 -0800, you wrote: On Thu, Jan 09, 2003 at 01:51:20PM -0300, Maximo Migliari wrote: What were the main benefits that you noticed straight away before adapting your code to use the new features that MySQL 4 offers? Replication is faster in 4.0. That's a big win for us, as we use replication pretty heavily. Does the query cache make a big difference? Do you have any benchmarks, even off your head? The query cache helps a lot. I ran some stats a while back. We have some queries that are as optimized as they can get. Accessing this page: http://biz.yahoo.com/n/y/yhoo.html uses the quey to fetch headlines. Without the query cache, that query can take anywhere from 50ms to 200ms to return (depends on the specific ticker and a few other factors). With the query cache, it returns in 4ms-6ms when the data is cached. For frequently accessed tickers, it makes a big difference. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 25 days, processed 866,323,874 queries (388/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
How disable query log?
Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex - Before posting, 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: 2013 Error
On Fri, 10 Jan 2003 23:53:51 +1100 mysql [EMAIL PROTECTED] wrote: Hi, Having a problem with accessing the mysql database from a remote machine. I am typing in mysql -h ???.??.???.?? -u ?? -p. It asked for password .. do that then the reply message is ERROR 2013: Lost connection to MySQL server during query. Have even tryed replacing IP with a host name and get the same error. Which version of MySQL? What OS? -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, 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
What's returned from $sth-execute(); ?
Hi, I've been using the return value of $sth-execute(); (in Perl DBI) to determine if I have a result set. I'm not using the value I get back but am assuming that if it's =1 then I have some results. Is this a safe thing to do with MySQL? And The results do actually seem to be correct for the number of records I get back. Should this be the case? Is it reliable? Thanks, Jeff - Before posting, 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
relations between tables
Hi all, Is MySQL able (like MS Access) to define permanent relations between tables? For example, I want to define a master - child relation between 2 tables so when deleting some entries from the master table to automaticly delete the entries from the details table without specifying this in the query. Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [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
Dumping remote DB
Hi all, I use MyCC 0.8.6 to work around remote data bases, and I need to dump the structures of all of them (also their contents). Can it be done? What SQL query should I use? Thanks in advance, Cesar L. Aracena [EMAIL PROTECTED] [EMAIL PROTECTED] (0299) 156-356688 Neuquén (8300) Capital Argentina - Before posting, 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: 2013 Error
mysql wrote: - Having a problem with accessing the mysql database from a remote machine. I am typing in mysql -h ???.??.???.?? -u ?? -p. It asked for password .. do that then the reply message is ERROR 2013: Lost connection to MySQL server during query. Have even tryed replacing IP with a host name and get the same error. - What version of MySQL and Linux are you running? Not that this is really important, but if you can upgrade to a newer version of MySQL, that might be your quickest road to getting it working. I had this problem to and ultimately upgrade the server to the latest RedHat rpm which I think was 3.23.49-3. If you want (or can) go down this road then it's really easy. Search archives for a message from me in December 2002 where I list what I did to copy the data over to the new server without dumping. When I sent my initial message about my 2013 failure, I never got a response. Google turned up some suggestions but none that really match my problem. I found the shortest road to recovery was upgrading. HTH - Before posting, 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 Your opportunity...
Hello , sql I know you're busy so let me get right to the point. If you have a business then I'm going to show you something that will double, triple or even Quadruple your sales... If you're new to making money on the net then boy, I'm going to show you something that will earn you a realistic $2000 a month..! ..worth spending the next 30 seconds to read on... -- This is very new... You are the leading group of people to discover this cutting edge concept, so take action BEFORE the rest of the internet finds out and adopt it! -- Did you know that even the best site in the world will only signup at most 5% of interested visitors on their first visit! No. I'm not kidding. If you don't follow up with the remaining 95% of those interested in what you are offering, you are throwing money AWAY... but what if I could offer you a 100% FREE solution to catching the 95% that don't buy in straight away? Would you be interested? ZERO cost to you! -- Here's Why. -- CASHCulture has developed a state-of-the-art selling machine that will automatically follow up your prospects, from first contact right through to closing the sale! 100% automaticlly without you lifting a finger... It will increase your PROFITS and make your sales SKYROCKET! Other companies would charge anything up to $35 a month for this! You get it for FREE but. why stop there when you can also have Professional Web Hosting and a whole lot more. -- as if that was not enough... -- I'm going to give you FREE access to the Make Money University! For newcomers to making money online AND seasoned Netrepreneurs alike, the Make Money University provides valuable, Top Quality resources to making money on the net -- including Step-by-Step guide to Online Success! No experience required! --EARN while you LEARN! -- Discover A New Breakthrough In Compensation! -- It's Impossible NOT to EARN with this program!! We've MADE SURE OF IT with our revolutionary 2x15 Personally Forced Matrix. Our STUNNING Compensation Plan can earn you a realistic $2000 a month, and even $77,360 a month for experienced marketers over time! Show it to your friends and they'll fall off their chair! GUARANTEED! -- Here's the best part: It's made so Simple Easy that even my 12 year old niece could run it and make More Money than my good old uncle! Become an early adoptor and take an unfair advantage. Act NOW! This is the one thing you can't afford to miss! Check it out For FREE Now! CLICK -- http://www.cashculture.com/?ref=harison A HREF=http://www.cashculture.com/?ref=harison AOL Users Click here/a and you can start using the system straight away Sincerely, Harrison Ming p.s. Remember it is FREE and It's Impossible NOT to EARN! When you sign up for FREE I will show you that it works -- and boy it works GREAT! p.p.s. This is a win-win situation -- you have nothing to lose EXCEPT if you Wait -- when many others have signed up. It's Important That you Act NOW! -- Discover A New Breakthrough In Compensation! - It's Impossible Not To Earn - mailto:[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
Enabling Cache feature in MySQL 4
Hello I've been running MySQL 3.23.49 as my DB for my webpage since up till now. The number of visitors have been increasing, and the poor box is currently running at 100% load, 24/7, which can't be healthy, and the page is getting slower to load. I've checked all my queries and tried to optimized them as much as possible, but I think the box is the real limiter, it hosts both Apache Webserver and MySQL with 400 Mhz of CPU speed and 192 MB of SDRAM -- not much, I know =) So, I visited the chat the otherday, and they suggested upgrading to MySQL 4 and enable the Caching feature, and after reading the stuff about it, I was really happy since my page is built in a way that queries are called often, but usually the same query, in short, the cache should be an optimal solution. Happy as I was, I pulled out a test box, installed Linux Debian on it, downloaded MySQL 4 on it, everything worked smooth, now I wanted to enable the cache feature, but it didn't let me! I want the change to be permanent, so I looked up the doc pages at mysql.com, and found info about the /etc/my.cnf file, which I created and put in: [mysqld] set-variable = query_cache_size = 67108864 The MySQL deamon was restarted, but when I checked the run-time variables with SHOW STATUS, query_cache_size was still set to 0? I even tried rebooting the entire machine after the changes to my.cnf, but it wouldn't work -- as if MySQL ignored my file? Now, I was thinking, either MySQL reads the setting from somewhere else in my system, but since I cannot find a config file variable in SHOW STATUS, I cannot find out from where? Another possibility is that it does read it, but for some reason ignores it, or even worse, reads a 2nd config file and the setting is overriden? Now, this was quite odd I thought, so I tried to set the cache manually with a query, SET GLOBAL query_cache_size=67108864 and that worked like a charm, it even updated query_cache_type to 1, which, according to the manuals mean ON. Now, I was wondering if any of you have a hint on what might be wrong? Also, as a little sidenote, I'm kinda wondering, what is a _decent_ size for the Cache buffer? is 64 Mb enough? I'm getting an upgraded server aswell, it will be 800 Mhz and have 256 MB of ram, this box will be 100% MySQL and I'll leave the Webserver on the old 400 Mhz. Any other hints on general optimizing is appreciated! Sorry if this post got too long, this is my first time posting on this list. Take it easy on my poor english skills =) - Before posting, 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
The little girl
Hello sql, The fund of the help collects donations for the little girl. If you know that mercy and compassion means, visit our website please: http://www.kathelp.boom.ru -- Best regards, Ming mailto:[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
Supermarket
Hello sql, The best products and services do not require advertising, you only should know how to receive it. We have a nice Affiliate Program. Let's go shopping! : http://www.sprmarket.com -- Best regards, Ming mailto:[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: Starting MySQL
On Friday 10 January 2003 01:45, cam vong wrote: I'm currently using Red Hat 7.1. I have loaded the apache, php, and mysql rpm. I seem to be having trouble starting MySQL. It gives me the following error: Can not connect to localhost MySQL server through socket /var/lib/mysql/mysql.sock (111). Can some help me, please? Is MySQL server running? -- 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: Selecting max value from sets
On Thursday 09 January 2003 23:06, Benjamin Dixon wrote: I'm trying to figure out a way to select a group of maximums from a set such that each value pair's greatest value is in the result set. For example, let's say I have this table Value_Pairs: Name | Value Bob1 Joe7 Bob2 Don3 Don4 Bob6 The result I want is like this: Name | Value Bob6 //Bob's highest value in the table Joe7 //Joe's highest value in the table Don4 //Don't highest value in the table So I'm looking for distinct maximums. Is it possible to do this *with a single query* in MySQL? I've tried a number of things and nothing comes close. SELECT Name, MAX(Value) FROM Value_Pairs GROUP BY Name. You can also find example in the manual: http://www.mysql.com/doc/en/example-Maximum-column-group.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
IGNORE LINES in LOAD LOCAL not working in FIXED LENGTH CSV FILE
Hello Ppl... I have a fixed length CSV file and when I try to import it using the LOAD LOCAL command load data local infile 'F:/Documents and Settings/Karam/Desktop/test.csv' into table `karam`.`test_table` fields escaped by '\\' enclosed by '' terminated by '' lines terminated by '\n' ignore 5 lines ( ID, VARCH_VAL, RAW_VAL, LONG_VAL ) MySQL always seem to import from the first line itself and never ignores those 5 lines which is expected ? But when I import it from a tab separated file, LOAD LOCAL works fine and it ignores the 'n' lines which is specified in the query ? Am I missing something or MySQL does not ignores lines for a FIXED LENGTH CSV ? Thanks in advance. Karam SQL, QUERY __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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: Using more than one CPU on FreeBSD?
I would be scared out of my gourd to do the dual mysqld processes. Just backup the machine and put linux on there. That was my solution to the FreeBSD problem. -Original Message- From: Tommy F. Eriksen [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 3:14 AM To: [EMAIL PROTECTED] Subject: Using more than one CPU on FreeBSD? Hi, I've inherited a FreeBSD/MySQL database-server (Compaq DL360, dual P3 1GHz), running (at the moment): Server version: 4.0.3-beta However, as far as I can tell, MySQL/FreeBSD 4.6.2-RELEASE still can't agree on utilizing more than one CPU. My question is this: A year or two ago, someone mentioned simply running two mysqld's on the same database-files (using file-locking) and then, using some form for loadbalancing between the two, was able to use more than one CPU for the mysqlds. Is this still the recommended way of doing this? I know I could use Linuxthreads, but the times I've tried them in the past, they have done more harm than good (low performance, unstability etc). Any advice would be appreciated :) Kind regards, Tommy Eriksen - Before posting, 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 question (group by) - please help
Sorry if this mail already arived to the list (I'm not sure if my posting was sucesfull, because I had problems with my mail server). The past posting: Hi. I want to calculate how many rows my query which uses 'GROUP BY' returns. The query: SELECT something FROM test WHERE (kid=1) OR (kid=2) OR (kid=4) GROUP BY cid,aid; Is it posible to get number of rows with such a query from MySQL v3.23.49? If it isn't posible ... is it posible in MySQL 4.x? I think a lot about this problem and I realize only the following solution: CREATE TEMPORARY TABLE t (i INT); INSERT INTO t (i) SELECT aid FROM the_query_above SELECT COUNT(*) FROM t; DROP TABLE t; But this isn't so elegant. I want to do so on data generated by: CREATE TABLE test ( kid INT, aid INT, cid INT ); INSERT INTO test (kid, aid, cid) VALUES ( 1, 0, 1), ( 2, 2, 2), ( 1, 3, 2), ( 2, 3, 2), ( 4, 4, 2), ( 4, 0, 3), ( 3, 3, 4), ( 4, 3, 4); Regards, Dezo - Before posting, 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
Bug: file permissions problem on ALTER TABLE
Hi there,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table. I kept encountering
this problem (trying to go from VARCHAR(6) to CHAR(6) for an index field),
but it would only work after I restarted the mysqld-max-nt. Immediately
after restarting the server, it worked, but then after a few minutes I
tried
to ALTER another field in the table, and it had the same problem. So I
restarted the server again, it went through, but the problem persisted. I
checked the file permissions, but everyone has full control to the files.
The table now looks like this:
CREATE TABLE postcodes (
id mediumint(8) unsigned NOT NULL auto_increment,
postcode char(6) NOT NULL default '',
plaats smallint(5) unsigned NOT NULL default '0',
straat mediumint(8) unsigned NOT NULL default '0',
low mediumint(5) unsigned NOT NULL default '0',
high mediumint(5) unsigned NOT NULL default '0',
parity enum('0','1','2') NOT NULL default '2',
PRIMARY KEY (id),
KEY postcode (postcode)
) TYPE=MyISAM;
It has 570,441 rows.
Is this a bug in MySQL, or am I doing something wrong? I was using
phpMyAdmin to do the changes, but I don't think that should make any
difference.
Regards,
Dirkjan Ochtman
P.S. I really dislike the spam/off-topic bot at [EMAIL PROTECTED] It
doesn't want this email, and that gives me a kind of
we-don't-want-help-even-if you-really-try-to-describe-the-problem kind of
feeling.
-
Before posting, 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
Fwd: Re: Copying MySql database to others
Query. I guess I don't understand how this mailing list works.. I send messages and some of them get posted, some don't . Some instances, I send a message and it gets posted, I try to respond and the message never appears Can someone explain? Date: Wed, 08 Jan 2003 18:37:33 -0800 To: Stefan Hinz, iConnect \(Berlin\) [EMAIL PROTECTED], [EMAIL PROTECTED] From: Frank Peavy [EMAIL PROTECTED] Subject: Re: Copying MySql database to others Thanks everyone for your input. Stefan, From, within phpMyAdmin? snip 1. In the left frame, choose the database you want to backup / copy. 2. Click the EXPORT tab in the right frame. 3. Choose the tables in the database you want to backup. 4. Choose Structure and data. 5. Check Enclose table and field names with backquotes if your table or column names might contain special characters (like ä, ö, ü). 6. Check Save as file. 7. Click Go. Since I have limited command line access (ISP hosted database), I will probably have to use phpMyAdmin. Part of my logic was to copy my MySql database to another MySql database, so that I can use the second database for QA purposes. When I perform the Export as above, does that make a copy in another database or is that to an external file? If it is an external file, would I have to Import it to another database that I create? - Before posting, 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: Copying MySql database to others
Stefan, Let's say, you exported database my_database, and you saved the export file as c:\mysql\my_database_export.sql on your QA machine. You can import it with this command (assuming that database my_database exists on your QA machine, but has no tables in it): c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Doesn't this assume that I have command line access? Since I am in a hosted (ISP) environment, I am trying to do this through phpMyAdmin. Can I not do this through phpMyAdmin? - Before posting, 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: logrotate problem with mysql
Jiann-Ming Su wrote: if test -n `ps acx|grep mysqld`; then /usr/bin/mysqladmin flush-logs fi You're probably running it as root with the password loading from the /root/.my.cnf or something. Try adding the -uroot -p command-line options, or a [mysqladmin] section to your ~root/.my.cnf file. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, 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 disable query log?
At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex The log isn't enabled by default, so it must be getting turned on *somewhere* at startup time. Check all your option files, not just one. Run this command to check what options are getting passed to it from option files: mysqld --print-defaults - Before posting, 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
GRANT Privilege Go Bye Bye
Ummm...what exactly do you do if you end up with a situation where no user has GRANT permission? Like, perhaps while experimenting with user administration in MySQL, the GRANT privilege was removed from every user? Thank you, Kevin Wixson IT Manager Norman Camera Video (616) 567-5552 1-800-900-6676 Fax: (616) 343-6410 e-mail: [EMAIL PROTECTED] Visit us on the web at: http://www.normancamera.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: Copying MySql database to others
Frank, c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Doesn't this assume that I have command line access? Since I am in a hosted (ISP) environment, I am trying to do this through phpMyAdmin. Can I not do this through phpMyAdmin? Right. It means you need command line access on the QA host, which I assumed was a local machine. Of course, it's also possible to use phpMyAdmin for this: 1. Choose the database you want the imported tables to go. 2. Click the SQL tab. 3. Click Browse to choose my_database_export.sql. 4. Click Okay. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Frank Peavy [EMAIL PROTECTED] To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, January 10, 2003 8:09 PM Subject: Re: Copying MySql database to others Stefan, Let's say, you exported database my_database, and you saved the export file as c:\mysql\my_database_export.sql on your QA machine. You can import it with this command (assuming that database my_database exists on your QA machine, but has no tables in it): c:\mysql\bin mysql -uusername -ppassword my_database c:\mysql\my_database_export.sql Doesn't this assume that I have command line access? Since I am in a hosted (ISP) environment, I am trying to do this through phpMyAdmin. Can I not do this through phpMyAdmin? - Before posting, 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
Bug Report: Signal 11]
Description: I keep recieving signal 11 and stack dumps. There are no connections going into mysql and no databases besides mysql and test. Only user in there is root and the other defualt ' ' user. How-To-Repeat: Simply started up the server and let it sit there for a min. or two. It will then proceed to recieve signal 11's restart, wait a few, signal 11, restart Fix: Submitter-Id: submitter ID Originator:Nick Stuart MySQL support: none Synopsis: Server keeps getting signal 11 Severity: critical Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.8-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: Pentium 2 400mhz 256m ram System: Linux vort112.inc.vortechnics.com 2.4.19-16mdk #1 Fri Sep 20 18:15:05 CEST 2002 i586 unknown unknown GNU/Linux Architecture: i586 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/3.2/specs Configured with: ../configure --prefix=/usr --libdir=/usr/lib --with-slibdir=/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --enable-long-long --enable-__cxa_atexit --enable-languages=c,c++,ada,f77,objc,java --host=i586-mandrake-linux-gnu --with-system-zlib Thread model: posix gcc version 3.2 (Mandrake Linux 9.0 3.2-1mdk) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxr-xr-x1 root root 13 Jan 9 15:31 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1147848 Aug 19 06:17 /lib/libc-2.2.5.so -rw-r--r--1 root root 178 Aug 19 06:08 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' - Before posting, 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: relations between tables
Octavian Rasnita wrote: Is MySQL able (like MS Access) to define permanent relations between tables? For example, I want to define a master - child relation between 2 tables so when deleting some entries from the master table to automaticly delete the entries from the details table without specifying this in the query. Search for REFERENCES in the MySQL manual; it only applies to InnoDB type tables though. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, 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: relations between tables
--- Octavian Rasnita [EMAIL PROTECTED] wrote: Hi all, Is MySQL able (like MS Access) to define permanent relations between tables? For example, I want to define a master - child relation between 2 tables so when deleting some entries from the master table to automaticly delete the entries from the details table without specifying this in the query. Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] As far as I know and how I understand it, the relationships are basically all in your head... You just have to coordinate it in your queries and other functions __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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: What's returned from $sth-execute(); ?
At 16:15 + 1/10/03, Jeff Snoxell wrote:
Hi,
I've been using the return value of $sth-execute(); (in Perl DBI) to
determine if I have a result set. I'm not using the value I get back but am
assuming that if it's =1 then I have some results.
That's incorrect because it's valid for a result set to be empty (have
zero rows). Consider the result of SELECT * FROM t WHERE 1 = 0.
You don't need to use the return value at all. Check the metadata to
see if the number of columns is zero. If it is, there's no result set.
If it's 0, there is a result set.
Some code to illustrate:
printf Query: %s\n, $query;
my $sth = $dbh-prepare ($query);
$sth-execute();
# metadata information becomes available at this point ...
printf NUM_OF_FIELDS: %d\n, $sth-{NUM_OF_FIELDS};
print Note: query has no result set\n if $sth-{NUM_OF_FIELDS} == 0;
Is this a safe thing to do with MySQL?
And
The results do actually seem to be correct for the number of records I get
back. Should this be the case? Is it reliable?
If you mean, can you interpret the $sth-execute() result as a row
count, the DBI docs specifically discourage it.
Thanks,
Jeff
-
Before posting, 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: Dumping remote DB
At 13:53 -0300 1/10/03, Cesar Aracena wrote: Hi all, I use MyCC 0.8.6 to work around remote data bases, and I need to dump the structures of all of them (also their contents). Can it be done? What SQL query should I use? Why not use mysqldump? That's what it's for? Thanks in advance, Cesar L. Aracena [EMAIL PROTECTED] [EMAIL PROTECTED] (0299) 156-356688 Neuquén (8300) Capital Argentina 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: Forgot to include stack....
har har, silly filter On Fri, 2003-01-10 at 14:43, [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query,queries,smallint If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I forgot to include the stack dump in my bug report. Here it is: 0x806f3bb handle_segfault + 447 0x8269928 pthread_sighandler + 184 0x807724c check_connections__FP3THD + 200 0x8077665 handle_one_connection + 337 0x82670dc pthread_start_thread + 220 0x829c67a thread_start + 4 - Before posting, 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: Running out of memory - memory leakage?
On Fri, Jan 10, 2003 at 01:59:07PM +0100, Csongor Fagyal wrote: Hi, I have a RedHat 8 box /w MySQL 3.23.54, /w 640M RAM. It looks like there are some sort of a memory leakage somewhere in the system, because slowly (in a day or two) I run out of memory. Even SWAP space gets used up at the end (slowly but steadily, fluctuating). If I restart MySQL, nothing happens. If I restart the services using MySQL (e.g. Apache), nothing happens. But if restart BOTH of them (MySQL AND Apache) suddenly my memory is back again and the SWAP is reclaimed also. How can I find out where my memory goes? Any ideas? Without seeing your my.cnf file, it is dificult to speculate on the cause. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 26 days, processed 892,214,953 queries (387/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: relations between tables
On Fri, Jan 10, 2003 at 04:54:14PM +0200, Octavian Rasnita wrote: Hi all, Is MySQL able (like MS Access) to define permanent relations between tables? For example, I want to define a master - child relation between 2 tables so when deleting some entries from the master table to automaticly delete the entries from the details table without specifying this in the query. Dear Teddy, The InnoDB table type supports this type of functionality - see http://www.mysql.com/doc/en/SEC449.html for more details Cheers! -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com MySQL Tip: Upgrading your servers to 4.0.8? Make sure to upgrade your clients to 4.0.8 as well! See http://mysql.com/doc/en/News-4.0.8.html While we are postponing, life speeds by.--Lucius Annaeus Seneca - Before posting, 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: Dumping remote DB
On Fri, Jan 10, 2003 at 01:53:55PM -0300, Cesar Aracena wrote: Hi all, I use MyCC 0.8.6 to work around remote data bases, and I need to dump the structures of all of them (also their contents). Can it be done? What SQL query should I use? Hello Cesar, Use the mysqldump utility. % mysqldump --host mysql.example.com For more information, see: http://www.mysql.com/doc/en/mysqldump.html Cheers! -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com MySQL User Conference and Expo April 10-12, 2003, San Jose, California Visit http://mysql.com/training for more information Public Service Announcement: Open Source Initiative (http://www.opensource.org) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: How disable query log?
It's *nothing* about query logging in configuration files! # mysqld --print-defaults mysqld would have been started with the following arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-locking --set-variable=max_connect_errors=1000 --set-variable=max_connections=600 --default-character-set=win1251 --set-variable=key_buffer=128M --set-variable=max_allowed_packet=1M --set-variable=table_cache=512 --set-variable=sort_buffer=2M --set-variable=record_buffer=2M --set-variable=thread_cache=12 --set-variable=thread_concurrency=6 --set-variable=myisam_sort_buffer_size=64M --set-variable=query_cache_size=64M --set-variable=query_cache_limit=1M --set-variable=query_cache_type=1 --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=ibdata1:1500M;ibdata2:1500M;ibdata3:1500M;ibdata4:1500M;ibdata5:1500M:autoextend --innodb_log_group_home_dir=/var/lib/mysql/ --innodb_log_arch_dir=/var/lib/mysql/ --set-variable=innodb_buffer_pool_size=256M --set-variable=innodb_additional_mem_pool_size=20M --set-variable=innodb_log_file_size=128M --set-variable=innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=0 --set-variable=innodb_lock_wait_timeout=20 --set-variable=innodb_thread_concurrency=6 Friday, January 10, 2003, 10:25:00 PM, you wrote: PD At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex PD The log isn't enabled by default, so it must be getting turned on PD *somewhere* at startup time. Check all your option files, not just PD one. Run this command to check what options are getting passed to PD it from option files: PD mysqld --print-defaults - Before posting, 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
let users create as much DB as they want ?
Greetings all. Hope someone could give me some pointers on this. I am working at a small ISP in Montreal. Some of our corpo users. have access to a MySQL database, that we created for them (usually, this DB is named after there username). The way we configured our mysql installation, which seams a pretty standard procedure, they can create as many tables as they want in there DB, but of course, they can't another DB. Well, we would like to do just that. Is there a way to configure mysql so that the user can create as much DB as he wants, by making sure every DB created by this user can only be accessed by him, some kind of 'root' access if you follow my logic. Furthermore, since we are billing our clients for the disk space they are using, will the structure of this will ressemble to this ; MAIN_USER - DB1 - table1, table2, ... - DB2 - table1, table2, ... Thanks for any help. --- Frederic Trudeau PHP Coder, Colocation, Development [EMAIL PROTECTED] 514.529.3000 ext. 246 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Selecting max value from sets
Slish, nu ti I nazvalsia, Egor Egorov, eshe bi Vasey Pupkinim nazvalsia-bi hahahahahaha ;-) Best Regards, Anton -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 9:38 AM To: [EMAIL PROTECTED] Subject: re: Selecting max value from sets On Thursday 09 January 2003 23:06, Benjamin Dixon wrote: I'm trying to figure out a way to select a group of maximums from a set such that each value pair's greatest value is in the result set. For example, let's say I have this table Value_Pairs: Name | Value Bob1 Joe7 Bob2 Don3 Don4 Bob6 The result I want is like this: Name | Value Bob6 //Bob's highest value in the table Joe7 //Joe's highest value in the table Don4 //Don't highest value in the table So I'm looking for distinct maximums. Is it possible to do this *with a single query* in MySQL? I've tried a number of things and nothing comes close. SELECT Name, MAX(Value) FROM Value_Pairs GROUP BY Name. You can also find example in the manual: http://www.mysql.com/doc/en/example-Maximum-column-group.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 - Before posting, 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 Secure Connection Setup
I'm trying to setup a secure SSL connection between our website and our MySQL server using MySQL 4.0.8 and OpenSSL on FreeBSD 4.6 and can't find any step by step instructions explaining how it's done. The MySQL documentation appears to only have a few scraps on secure connections and from what I can tell is woefully inadequate. Maybe that's because it's such a new area for MySQL. Can someone point me to something that might help? Thanks, David Rock - Before posting, 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: count(expr)
On Thu, Jan 09, 2003 at 12:24:28PM -0600, Paul DuBois wrote: ... You probably want SUM(col3 col4,1,0) instead. Hey Paul, Am I missing something here? I did not think that SUM(col3 col4,1,0) would be valid syntax? Cheers! -- Zak Greant [EMAIL PROTECTED] | MySQL Advocate | http://zak.fooassociates.com Zak's correct. I erred. The expression would use IF() inside a SUM(): SUM(IF(col3 col4,1,0)) Sheesh. - Before posting, 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
Blobs
Is there an easy way to see If and how much data I have written into a blob? sql...tomakefilterhappy - Before posting, 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: file permissions problem on ALTER TABLE
Dirkjan,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I
encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table.
Last 3.23 I used was 3.23.49, and I didn't have an ALTER TABLE problem,
neither on Win98, Win2K, nor Linux. I did have problems like the ones
you describe with 4.0.1, 4.0.3 and 4.0.4 under Win98 and Win2K. Using
4.0.7 now und Win98 and Win2K, the ALTER TABLE problems have gone.
Reporting this problem to the list, I heard strange advices like switch
lower_case_table_names to 0. I believe it's a MySQL on Win bug with the
4.0.x versions mentioned above, and it might also affect 3.23 version
after 3.23.49.
My advice is: Update, MySQL 4.0.7 is real funky :)
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Dirkjan Ochtman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, January 10, 2003 7:40 PM
Subject: Bug: file permissions problem on ALTER TABLE
Hi there,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I
encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table. I kept
encountering
this problem (trying to go from VARCHAR(6) to CHAR(6) for an index
field),
but it would only work after I restarted the mysqld-max-nt.
Immediately
after restarting the server, it worked, but then after a few minutes
I
tried
to ALTER another field in the table, and it had the same problem. So
I
restarted the server again, it went through, but the problem
persisted. I
checked the file permissions, but everyone has full control to the
files.
The table now looks like this:
CREATE TABLE postcodes (
id mediumint(8) unsigned NOT NULL auto_increment,
postcode char(6) NOT NULL default '',
plaats smallint(5) unsigned NOT NULL default '0',
straat mediumint(8) unsigned NOT NULL default '0',
low mediumint(5) unsigned NOT NULL default '0',
high mediumint(5) unsigned NOT NULL default '0',
parity enum('0','1','2') NOT NULL default '2',
PRIMARY KEY (id),
KEY postcode (postcode)
) TYPE=MyISAM;
It has 570,441 rows.
Is this a bug in MySQL, or am I doing something wrong? I was using
phpMyAdmin to do the changes, but I don't think that should make any
difference.
Regards,
Dirkjan Ochtman
P.S. I really dislike the spam/off-topic bot at
[EMAIL PROTECTED] It
doesn't want this email, and that gives me a kind of
we-don't-want-help-even-if you-really-try-to-describe-the-problem kind
of
feeling.
-
Before posting, 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
Question
How can I delete a database in MySQL so I recreate it? George Flatman - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: How disable query log?
At 23:45 +0300 1/10/03, Andrey V. Ignatov wrote: It's *nothing* about query logging in configuration files! # mysqld --print-defaults mysqld would have been started with the following arguments: --port=3306 --socket=/var/run/mysqld/mysqld.sock --skip-locking --set-variable=max_connect_errors=1000 --set-variable=max_connections=600 --default-character-set=win1251 --set-variable=key_buffer=128M --set-variable=max_allowed_packet=1M --set-variable=table_cache=512 --set-variable=sort_buffer=2M --set-variable=record_buffer=2M --set-variable=thread_cache=12 --set-variable=thread_concurrency=6 --set-variable=myisam_sort_buffer_size=64M --set-variable=query_cache_size=64M --set-variable=query_cache_limit=1M --set-variable=query_cache_type=1 --innodb_data_home_dir=/var/lib/mysql/ --innodb_data_file_path=ibdata1:1500M;ibdata2:1500M;ibdata3:1500M;ibdata4:1500M;ibdata5:1500M:autoextend --innodb_log_group_home_dir=/var/lib/mysql/ --innodb_log_arch_dir=/var/lib/mysql/ --set-variable=innodb_buffer_pool_size=256M --set-variable=innodb_additional_mem_pool_size=20M --set-variable=innodb_log_file_size=128M --set-variable=innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=0 --set-variable=innodb_lock_wait_timeout=20 --set-variable=innodb_thread_concurrency=6 Okay, that's strange. Next step: - What's the name of the log file that the server is logging to? - Does logging occur if you shut down the server (with mysql.server stop, for example), and then start mysqld manually? Friday, January 10, 2003, 10:25:00 PM, you wrote: PD At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? My configure options: ./configure --without-berkley-db --with-named-curses-libs=/opt/ncurses-5.3/lib/libncurses.a --build=powerpc64-linux --prefix=/usr/local/mysql --localstatedir=/var/lib/mysql --sysconfdir=/etc/mysql --sbindir=/usr/local/mysql/bin --libexecdir=/usr/local/mysql/bin --with-unix-socket-path=/var/run/mysqld/mysqld.sock --without-debug --without-isam --with-extra-charsets=complex PD The log isn't enabled by default, so it must be getting turned on PD *somewhere* at startup time. Check all your option files, not just PD one. Run this command to check what options are getting passed to PD it from option files: PD mysqld --print-defaults - Before posting, 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 monitoring
Thanks Chris, i had not seen that before, nice little tool to compliment the plots!! thanks, added as a bookmark and running it now Michael Weiner -- On Sat, 2003-01-11 at 09:06, Dr. Poo wrote: I didn't read the original thread...but go to CPAN and check out MyTop.pl. Just like unix Top process watch application, but for mysql... pretty cool! -Chris spamfilteraide sql,query,queries,smallint /spamfilteraide - Before posting, 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: file permissions problem on ALTER TABLE
Hmm, okay, but isn't the 3.23 supposed to be stable and thus more bugless
than a gamma like 4.0.7?
And also, I was wondering when 4 is going to be released as stable. Looking
over the changelogs, it shouldn't be too long, right?
Zak: thanks for the advice. I got real pissed off about it, since it seems
like a real bug. :) But I guess it helps pretty good.
Regards,
Dirkjan
- Original Message -
From: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]
To: Dirkjan Ochtman [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, January 10, 2003 11:45 PM
Subject: Re: file permissions problem on ALTER TABLE
Dirkjan,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I
encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table.
Last 3.23 I used was 3.23.49, and I didn't have an ALTER TABLE problem,
neither on Win98, Win2K, nor Linux. I did have problems like the ones
you describe with 4.0.1, 4.0.3 and 4.0.4 under Win98 and Win2K. Using
4.0.7 now und Win98 and Win2K, the ALTER TABLE problems have gone.
Reporting this problem to the list, I heard strange advices like switch
lower_case_table_names to 0. I believe it's a MySQL on Win bug with the
4.0.x versions mentioned above, and it might also affect 3.23 version
after 3.23.49.
My advice is: Update, MySQL 4.0.7 is real funky :)
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
Geschäftsführer / CEO iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Tel: +49 30 7970948-0 Fax: +49 30 7970948-3
- Original Message -
From: Dirkjan Ochtman [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, January 10, 2003 7:40 PM
Subject: Bug: file permissions problem on ALTER TABLE
Hi there,
I am using MySQL 3.23.54 on Windows XP (with SP1). Today, I
encountered a
weird problem: when trying to ALTER a TABLE, MySQL said that it no
permissions to rename the *.myi file for that table. I kept
encountering
this problem (trying to go from VARCHAR(6) to CHAR(6) for an index
field),
but it would only work after I restarted the mysqld-max-nt.
Immediately
after restarting the server, it worked, but then after a few minutes
I
tried
to ALTER another field in the table, and it had the same problem. So
I
restarted the server again, it went through, but the problem
persisted. I
checked the file permissions, but everyone has full control to the
files.
The table now looks like this:
CREATE TABLE postcodes (
id mediumint(8) unsigned NOT NULL auto_increment,
postcode char(6) NOT NULL default '',
plaats smallint(5) unsigned NOT NULL default '0',
straat mediumint(8) unsigned NOT NULL default '0',
low mediumint(5) unsigned NOT NULL default '0',
high mediumint(5) unsigned NOT NULL default '0',
parity enum('0','1','2') NOT NULL default '2',
PRIMARY KEY (id),
KEY postcode (postcode)
) TYPE=MyISAM;
It has 570,441 rows.
Is this a bug in MySQL, or am I doing something wrong? I was using
phpMyAdmin to do the changes, but I don't think that should make any
difference.
Regards,
Dirkjan Ochtman
P.S. I really dislike the spam/off-topic bot at
[EMAIL PROTECTED] It
doesn't want this email, and that gives me a kind of
we-don't-want-help-even-if you-really-try-to-describe-the-problem kind
of
feeling.
-
Before posting, 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[3]: How disable query log?
1) logs file names: hostname.log , hostname-bin.001-4 and all of them created in datadir. hostname = sql3 2) i am delete all log files from /var/lib/mysql and run mysqld --user=mysql And all files create again :( -rw-rw1 mysqlmysql 14686 Jan 11 01:40 sql3-bin.001 -rw-rw1 mysqlmysql 15 Jan 11 01:39 sql3-bin.index -rw-rw1 mysqlmysql 149 Jan 11 01:39 sql3-slow.log -rw-rw1 mysqlmysql 11640 Jan 11 01:40 sql3.001 -rw-rw1 mysqlmysql 143581 Jan 11 01:40 sql3.log -rw-rw1 mysqlmysql 5 Jan 11 01:39 sql3.pid Saturday, January 11, 2003, 2:16:29 AM, you wrote: PD At 23:45 +0300 1/10/03, Andrey V. Ignatov wrote: It's *nothing* about query logging in configuration files! PD Okay, that's strange. Next step: PD - What's the name of the log file that the server is logging to? PD - Does logging occur if you shut down the server (with mysql.server stop, PDfor example), and then start mysqld manually? Friday, January 10, 2003, 10:25:00 PM, you wrote: PD At 18:59 +0300 1/10/03, Andrey V. Ignatov wrote: Hi, all! I am compile mysql-4.0.9 for PPC64 with GLIBC64. It's nothing about logging in mysql.server startup script and in my.cnf, but mysqld create log file and write a lot of queries to it. How i can disable this? PD The log isn't enabled by default, so it must be getting turned on PD *somewhere* at startup time. Check all your option files, not just PD one. Run this command to check what options are getting passed to PD it from option files: - Before posting, 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: Using more than one CPU on FreeBSD?
On Fri, Jan 10, 2003 at 05:29:41PM +0300, Varshavchick Alexander wrote: Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Agreed. LinuxThreads/MySQL on FreeBSD is quite good now. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 26 days, processed 894,781,935 queries (386/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
Troubles with the prompt of mysql.exe
Hi, I have problems with the prompt of mysql : When i execute mysql.exe it doesn't ask me about any id or password. Why it happens? if i execute the query select user() it returns the user odbc@localhost. The other problem happened when i try to execute this query (this query is um the chapter 3 of the manual reference) : mysql load data local infile 'pet.txt' into table pet;. This query return me the follow erro : ERROR 1148: The used command is not allowed with this MySQL version. I was using the MySQL 3.23.54 and i have the MyODBC 2.5 driver instaled. No more and really greatefull. Saulo Eduardo ___ Busca Yahoo! O melhor lugar para encontrar tudo o que você procura na Internet http://br.busca.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: GRANT Privilege Go Bye Bye
At 14:35 -0500 1/10/03, Kevin Wixson wrote: Ummm...what exactly do you do if you end up with a situation where no user has GRANT permission? Like, perhaps while experimenting with user administration in MySQL, the GRANT privilege was removed from every user? Naturally, you're just speaking hypothetically, right? :-) Connect as root and modify the mysql tables directly with UPDATE. For example. Thank you, Kevin Wixson IT Manager Norman Camera Video (616) 567-5552 1-800-900-6676 Fax: (616) 343-6410 e-mail: [EMAIL PROTECTED] Visit us on the web at: http://www.normancamera.com 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
Source RPM Compile Error
i downloaded mysql-4.0.8-0.src.rpm off of the mysql web-site and have tried to compile it on several different redhat 8.0 boxes. the same error happens whether compiling latest mysql 3.23 or 4.0 source rpms. the build moves along for about fifteen minutes (long after i begin to believe it will succeed without error). i don't know if it is redhat specific or not. anyone have any suggestions or ideas about how to resolve the error below? thanks all. automake: strings/Makefile.am: Assembler source seen but `CCAS' not defined in `configure.in' automake: strings/Makefile.am: Assembler source seen but `CCASFLAGS' not defined in `configure.in' error: Bad exit status from /var/tmp/rpm-tmp.37688 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.37688 (%build) -chris - Before posting, 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: Enabling Cache feature in MySQL 4
Hi, I have a server doing 26,000 queries a second so thought it was about time to enable caching. Did some research with MySQL 4 and got it to work. After running it for a while it turned out that more than 70% of queries were coming out of cache (straight out of memory not hitting the disk (disk has always been the bottleneck)) which is nice! I used the following entries in my.cnf set-variable= query_cache_type=1 set-variable= query_cache_size=64M And it works! Hope this helps. Cheers, Andrew sql,query -Original Message- From: Blaster [mailto:[EMAIL PROTECTED]] Sent: 10 January 2003 17:23 To: [EMAIL PROTECTED] Subject: Enabling Cache feature in MySQL 4 Hello I've been running MySQL 3.23.49 as my DB for my webpage since up till now. The number of visitors have been increasing, and the poor box is currently running at 100% load, 24/7, which can't be healthy, and the page is getting slower to load. I've checked all my queries and tried to optimized them as much as possible, but I think the box is the real limiter, it hosts both Apache Webserver and MySQL with 400 Mhz of CPU speed and 192 MB of SDRAM -- not much, I know =) So, I visited the chat the otherday, and they suggested upgrading to MySQL 4 and enable the Caching feature, and after reading the stuff about it, I was really happy since my page is built in a way that queries are called often, but usually the same query, in short, the cache should be an optimal solution. Happy as I was, I pulled out a test box, installed Linux Debian on it, downloaded MySQL 4 on it, everything worked smooth, now I wanted to enable the cache feature, but it didn't let me! I want the change to be permanent, so I looked up the doc pages at mysql.com, and found info about the /etc/my.cnf file, which I created and put in: [mysqld] set-variable = query_cache_size = 67108864 The MySQL deamon was restarted, but when I checked the run-time variables with SHOW STATUS, query_cache_size was still set to 0? I even tried rebooting the entire machine after the changes to my.cnf, but it wouldn't work -- as if MySQL ignored my file? Now, I was thinking, either MySQL reads the setting from somewhere else in my system, but since I cannot find a config file variable in SHOW STATUS, I cannot find out from where? Another possibility is that it does read it, but for some reason ignores it, or even worse, reads a 2nd config file and the setting is overriden? Now, this was quite odd I thought, so I tried to set the cache manually with a query, SET GLOBAL query_cache_size=67108864 and that worked like a charm, it even updated query_cache_type to 1, which, according to the manuals mean ON. Now, I was wondering if any of you have a hint on what might be wrong? Also, as a little sidenote, I'm kinda wondering, what is a _decent_ size for the Cache buffer? is 64 Mb enough? I'm getting an upgraded server aswell, it will be 800 Mhz and have 256 MB of ram, this box will be 100% MySQL and I'll leave the Webserver on the old 400 Mhz. Any other hints on general optimizing is appreciated! Sorry if this post got too long, this is my first time posting on this list. Take it easy on my poor english skills =) - Before posting, 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: InnoDB vs. MySQL performance Issue
If this is going to hurt someone please when u see mails from [EMAIL PROTECTED] do not scroll down to the bottom I won't. I've had enough of this, so I just set my mail program to erase any mails from you. I do the same for anyone who repeatedly promotes religious or political views on the mailing list, whether passively or actively. I subscribe to this list to obtain info on MySQL, not middle-eastern or any other politics. I can read the newspaper for that. Steve. - Before posting, 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
Cannot connect after upgrading to mysql-4.0.8
Description: The problem described on http://bugzilla.redhat.com/bugzilla/long_list.cgi?buglist=75128 with solution described on http://rhn.redhat.com/errata/RHSA-2002-197.html comes back after upgrading to mysql-4.0.8 from 3.23.54a. How-To-Repeat: Install the mysql-4.0.8 rpm posted by www.mysql.com using either rpm -U or rpm -e followed by rpm -i on stock Redhat 7.3 with all updates from Redhat using Redhat's up2date service. After that, connect attempts to the mysql server on Linux from a mysql client such as mysql or mysqlcc on W2000 all fail no matter how user accounting (host,user,privileges) is setup on either box. Fix: The above solution appears to work i.e. add the involved hosts in /etc/hosts and \WINNT\system32\drivers\etc\hosts. Submitter-Id: ? Originator:Claus Olesen Organization: Transdyn Controls, Inc. MySQL support: none Synopsis: Cannot connect after upgrading to mysql-4.0.8 Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.8-gamma (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: Stock Redhat 7.3 fully up-to-date using Redhat's up2date service. System: Linux saturn 2.4.18-19.7.x #1 Thu Dec 12 07:56:46 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-113) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Nov 7 10:36 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Oct 10 08:16 /lib/libc-2.2.5.so -rw-r--r--1 root root 2312442 Oct 10 07:51 /usr/lib/libc.a -rw-r--r--1 root root 178 Oct 10 07:46 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, 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: What's returned from $sth-execute(); ?
this should answer your question, from the DBI documentation http://search.cpan.org/author/TIMB/DBI-1.30/DBI.pm#execute sql,query,queries,smallint - hcir On Friday, January 10, 2003, at 02:55 PM, [EMAIL PROTECTED] wrote: I've been using the return value of $sth-execute(); (in Perl DBI) to determine if I have a result set. I'm not using the value I get back but am assuming that if it's =1 then I have some results. - Before posting, 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: lsof +L1 - mysql.err.1 unlinked ...
Hello On Tue, Jan 07, 2003 at 11:25:18PM +0100, Christian Jaeger wrote: I have always these mysql.err.1 files not unlinked in all my mysqlservers, and only a mysqld restart resolve these (nor reload neither mysqladmin refresh doesn't unlink them). The err file is created by safe_mysqld to catch the output mysqld produces when it cannot start. As mysqld outputs to stderr on errors, too, everything is appended to that file in the nohup line, too. So there's currently the problem whether never to rotate it or to loose the errors messages. I change the behaviour of the Debian package to not rotating now. A better fix is coming hopefully, I already proposed some possible solutions to the [EMAIL PROTECTED] list (reopening the file in flush-logs or using syslog). bye, -christian- (maintainer of Debian mysql package) - Before posting, 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
