redirection & limit display accross multi pages .... ????
hi guyz
2 problems
1.
i ve a page that picks up random records from mysql database and displays
them
now i want to limit the display to 5 or 6 records per page
that is if it could pickup ALL the records in the db and disolay only 5 or 6
on each page
my project supervisor wants something like goole search results n i tried 2
reed thru one of the source codes there but its too terrabble couldnt
make anythin outta it
2.
i want to redirect a link from the summary of an article to the full article
the article is in mysql db
how do i do dat ... or shall i PICK up the article from the db when the user
cliks on the link
and wont i need some kindda script like in java script
onclick{(pick up da article )}
id really appricaite any help with these
gratefull as ever
toby .
_
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.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: My.cnf problem when upgrade from 4.01 to 4.02
Hello, I've experienced what seems to be the same problem on Redhat 7.2. I eventually realized that in addition to the regular 4.0.2 server RPM, I had to also install the MySql-Max 4.0.2 server RPM. After that, everything worked fine. I was under the impression that there would only be one server that supported all table types beginning with version 4.0, and that the Max server would no longer be needed. That doesn't seem to be the case. --Michael --- zlab1 <[EMAIL PROTECTED]> wrote: > Hi, > I'm a new user of mysql, I was upgrade from > mysql 4.01 to 4.02 in > mandrake 8.2. > > The following options in current running my.cnf > suddently not work: > > innodb_data_file_path = ibdata1:100M > innodb_data_home_dir = /data/mysql/ > innodb_log_group_home_dir = /data/mysql/ > innodb_log_arch_dir = /data/mysql/ > innodb_flush_log_at_trx_commit= 1 > innodb_log_archive= 0 > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=4M > set-variable = innodb_log_buffer_size=4M > set-variable = innodb_buffer_pool_size=64M > set-variable = innodb_additional_mem_pool_size=4M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=50 > set-variable = innodb_thread_concurrency=8 > > I change the 1st: > innodb_data_file_path = ibdata1:100M > To > innodb_data_file_path = > /data/mysql/ibdata1:100M:autoextend > > I have to remark rest options in order to make it > working and also > "set-variable = " seen not needed?? The err log > show: > > ERROR: unknown variable '..' > whenever I keep any options > other than the 1st line, > All previous section section is working when remove > 'set-variable = '. > > So the problem isolated to innodb options > sections and seen no doc > refer this change, would u kind enough to give me a > hand. > > Most of the options is copy from example config file > with minor > changesIt is working in 4.0 4.01 and suddently > not work now. Thanks. > > If possible, > Please reply to [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 > __ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.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: More than one foreign key in a table
Hi, The below mentioned sql makes an error when created with two foreign key constraints under MySQL/Innodb table type. Any help to solve this problem? create table UserMenuRestrictions ( CompanyCodechar(10) not null, UserId char(10) not null, MenuName char(40) not null, MenuParentName char(40) not null, AddRight char(1) , EditRight char(1) , DeleteRightchar(1) , ViewRight char(1) , Visiblechar(1) , Enabledchar(1) , primary key (CompanyCode, UserId, MenuName, MenuParentName), foreign key (CompanyCode, UserId) references UserDetails (CompanyCode, UserId) on update restrict on delete restrict, foreign key (MenuName, MenuParentName) references MenuDetails (MenuName, MenuParentName) on update restrict on delete restrict ) type = innodb; Thankyou in advance. Happiness Always BKR Shivaprakkash __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.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
Can't open server
In using MySQLGui v 1.75, I get the following message when I try to connect to the database: Can't connect to local MySQL server through socket "(111) What can I do in addition to following the configuration for .my.sql in MySQL.help? I can access the database through the text based mysql client just fine. I downloaded the statically linked Linux binary, so making and installing should not play a role. Here is /etc/my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Here is ~/.my.cnf: [client] port= 3306 host= localhost user= # This will be passed to MySQLGUI GUI client only [client_fltk] help_file=/usr/local/doc/MySQL.help client_file=~/.ee/MySQL.options queries_file=~/.mysql/.mysql_history history_length=100 database=test Any help you can offer would be appreciated. A newsgroup of people who are beta testng this software would be helpful. thanks, dion -- Dion Hollenbeck Email: [EMAIL PROTECTED] Home Page: http://www.woodsprite.com Brewing Page:http://hbd.org/hollen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 MySQL on Mac OS X Server
Does anyone know if these instructions actually work? (they didn't for me) http://cerebus.sandiego.edu/~jerry/UnixTips/mysql.html I'd like to fix them up so I and others never have to seek this out again, tall order? Who can help me? after I followed these the server didn't start. I had to use the --force option to install the default dbases. I will say that my particular problem may be due to my lack setting my host name correctly. I have very recently installed OS X Server (10.1.4) just so I could try this on a clean system. I don't understand too much about host names, here's what I do know: I have host names that I get from dynydns.org, e.g., mbase.dyndns.org. the Domain Name Servers in my Network settings are pointed to my provider's DNS numbers. I have many of these host and I know which one the main one is. This setup works great and I have had MySQL running on this before and similarly on OS X client. I simply want to to use/enable the MySQL that comes with OS X Server. I have written Apple about exactly the same. Thanks Lloyd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 query help
I know this is an off topic question, but wondered if anyone might have a
moment to help. I wouldn't ask if I could do a sub-select...
I am using mysql v3.23.51 and have a table I need to display the information
of users who are working in both category 3 and category 5.
Table def looks kind of like:
lookuptable {
id int,
user int,
category int,
status tinyint,
...
..
}
A query I am using is kind of like the following:
select B.name,C.name
from lookuptable A, user B, cat C
where A.user = B.id
and A.category = C.id
and A.category in (3,5);
The problem I see is that records come back where a user is in category 3 or
category 5. How do I limit records so that the selected user must have
category entries for both 3 and 5? (not in the same entry of course)
Thanks for your time,
-Craig
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.0.2 & my.cnf
Hi! I recently took a test drive for MySQL 4.0.2. Installation was a breeze as always. However, when I tried to start MySQL server using the same old my.cnf which 4.0.1 had no problems with, 4.0.2 complained about the following and refused to start. 020713 07:24:55 mysqld started Unknown suffix '.' used for variable 'bind-address' (value '192.168.0.2') /usr/local/mysql-4.0.2-alpha-source/libexec/mysqld: Error while setting value '192.168.0.2' to 'bind-address' 020713 07:24:56 mysqld ended As soon as I commented out bind-address option in my.cnf, it began to run without a problem. Am I doing something wrong or is this a known/hidden bug in 4.0.2? Yours, Jindo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 rpm from RH 7.3
[EMAIL PROTECTED] writes: > En réponse à Jan Kudrman <[EMAIL PROTECTED]>: > I've found it on the online documentation of MySQL.org with comments of user. > Since I've replaced the RP of RH 7.3 by RPM of MySQL.org, I don't have any > problem No known RH-specific bugs with the MySQL rpms shipped with RHL 7.3 - we've received one report on database corruption, but the had it happen with the ones from mysql.com too when he switched. -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql@lists.mysql.com
>Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-3.23.42 (Source distribution) >Environment: System: Linux localhost.localdomain 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 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-110) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 jul 12 08:43 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x2 root root 1260480 abr 15 10:44 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 abr 15 11:02 /usr/lib/libc.a -rw-r--r--1 root root 178 abr 15 10:55 /usr/lib/libc.so Configure command: /mysql/mysql-3.23.42/configure - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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@lists.mysql.com
>Description: >How-To-Repeat: >Fix: >Submitter-Id: >Originator:root >Organization: >MySQL support: [none | licence | email support | extended email support ] >Synopsis: >Severity: >Priority: >Category: mysql >Class: >Release: mysql-3.23.42 (Source distribution) >Environment: System: Linux localhost.localdomain 2.4.18-3 #1 Thu Apr 18 07:37:53 EDT 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-110) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 jul 12 08:43 /lib/libc.so.6 -> libc-2.2.5.so -rwxr-xr-x2 root root 1260480 abr 15 10:44 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 abr 15 11:02 /usr/lib/libc.a -rw-r--r--1 root root 178 abr 15 10:55 /usr/lib/libc.so Configure command: /mysql/mysql-3.23.42/configure - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NATURAL JOIN
So this works as expected,
mysql> create table test_1 (id int,value char);
Query OK, 0 rows affected (0.00 sec)
mysql> create table test_2 (id int,val char);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test_1 values (1,a),(2,b),(3,c);
ERROR 1054: Unknown column 'a' in 'field list'
mysql> insert into test_1 values (1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into test_2 values (2,'d'),(3,'e'),(4,'f');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_1 NATURAL JOIN test_2;
+--+---+--+--+
| id | value | id | val |
+--+---+--+--+
|2 | b |2 | d|
|3 | c |3 | e|
+--+---+--+--+
2 rows in set (0.00 sec)
--
But what is wrong with this:
CREATE TABLE `items` (
`item_id` int(11) NOT NULL auto_increment,
`centro_id` int(11) NOT NULL default '0',
`name` varchar(50) default NULL,
`description` varchar(255) default NULL,
`url` varchar(255) default NULL,
`directory_id` int(11) default NULL,
`cost` int(11) default NULL,
PRIMARY KEY (`item_id`),
KEY `directory_id` (`directory_id`)
) TYPE=MyISAM;
CREATE TABLE `shop_directory` (
`directory_id` int(11) NOT NULL auto_increment,
`short_name` varchar(32) default NULL,
`long_name` varchar(128) default NULL,
`description` text,
`parent_id` int(11) NOT NULL default '0',
PRIMARY KEY (`directory_id`)
) TYPE=MyISAM;
INSERT INTO items VALUES (5,58,'Swim','wet','http://www.bri.com/wet',3,50);
INSERT INTO items VALUES
(4,58,'Beach','beach','http://www.bri.com/beach',3,50);
INSERT INTO items VALUES (7,58,'Bed','bed','http://www.bri.com/bed',2,60);
INSERT INTO items VALUES (9,58,'sun','sun sun sun','www.bri.com/sun',1,34);
INSERT INTO shop_directory VALUES ('','a','aaa','a',0);
INSERT INTO shop_directory VALUES ('','b','bbb','b',1);
INSERT INTO shop_directory VALUES ('','c','ccc','c',2);
mysql> select * from items NATURAL JOIN shop_directory;
Empty set (0.00 sec)
What gives... why isn't it joining on directory_id. Using an inner join
with where condition it works fine.. but INNER JOIN should work also.. no?
"The NATURAL [LEFT] JOIN of two tables is defined to be semantically
equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that
names all columns that exist in both tables."
Richard
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: CHECK syntax
In the last episode (Jul 12), Larry Irwin said: > I'd like to set up my CREATE's to have CHECK and REGEXP but can't > seem to get 4.0.2 to accept my attempts... > > CREATE TABLE IF NOT EXISTS aptype ( > aptype_id INT UNSIGNED NOT NULL AUTO_INCREMENT, > appt_type VARCHAR(3) NOT NULL, > branch VARCHAR(2), > print_ticket VARCHAR(1), CHECK (SELECT (print_ticket) REGEXP "[0-9]"), > print_on_stmtVARCHAR(1), CHECK (print_on_stmt IN "Y|N|E|R"), > produce_ticket VARCHAR(1), CHECK (produce_ticket REGEXP [ASP]) > ) TYPE=InnoDB; http://www.mysql.com/doc/C/R/CREATE_TABLE.html * The `FOREIGN KEY', `CHECK', and `REFERENCES' clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. *Note Differences from ANSI::. Just remove the CHECK() clause completely. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Autoincrement in MultiColumn primary key?
filter: sql,query I did some tests recently on a two column primary key, one of which was an autoincrement column. It worked nicely, maintaining a separate increment for every unique value in the second column. I was ecstatic, as this was exactly what I need for some tables I'm designing. However, today I tried to use the same idea in a three column primary key, and it seems to be acting like a lone autoincrement column, instead of how it acted with two columns. What I want to know, is this: Is the two-column behavior an official feature, or is it an unintended effect? I personally /love/ that behavior, and think it should be a feature, but before I go further, I need to know if I can count on it to hang around. And if it is a feature, why it doesn't work in three column indexes. Please CC the reply-to address in your responses. Thanks for your time, Matthew Walker - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 up with MyODBC-3.51.03...????
Ok.. Bug #2.. (yes, I'm getting a bit cranky!).. The "copy_if_not_empty()" routine is whacking my username (and it would my password if it was also set). In my case, the routine gets the following args : copy_if_not_empty (to=0x7f7f41b0 "rflower", max_length=64, from=0x7f7f0980 "", length=-3) at connect.c:111 (gdb) print to $7 = 0x7f7f41b0 "rflower" (gdb) next 113 if (length == SQL_NTS) (gdb) next 114 length=max_length-1; (gdb) next 115 strmake(to,from,length); (gdb) next 117 } (gdb) print to $8 = 0x7f7f41b0 "" For now, I'm just going to comment out the calls to this function.. Obviously something is just plain wrong here.. Just for reference, here's the call stack : (gdb) where #0 SQLConnect (hdbc=0x4000b8c8 "@", szDSN=0x7f7f3000 "DEMO", cbDSN=-3, szUID=0x7f7f0980 "", cbUID=-3, szAuthStr=0x7f7f0ab0 "", cbAuthStr=-3) at connect.c:200 #1 0x7adff730 in SQLConnect (connection_handle=0x40005420 "", server_name=0x7f7f0850 "DEMO", name_length1=-3, user_name=0x7f7f0980 "", name_length2=-3, authentication=0x7f7f0ab0 "", name_length3=-3) at SQLConnect.c:2699 #2 0x385c in OpenDatabase (phEnv=0x4000176c, phDbc=0x40001768, szDSN=0x7f7f0850 "DEMO", szUID=0x7f7f0980 "", szPWD=0x7f7f0ab0 "") at isql.c:228 #3 0x322c in main (argc=3, argv=0x7f7f069c) at isql.c:93 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 150 on 3.23.51
Hello, We have compiled in InnoDB support for MySQL 3.23.51. We would LOVE to take advantage of the transactions and foreign key assignments that it provides. We are having an issue, with the following SQL, however: All of the alter table statements are ending with this error: ERROR 1005: Can't create table './everbase/#sql-955_b.frm' (errno: 150) I found references to a bug that was fixed in .51, but I can't find anything as to what is going on. We think we have the syntax right, but some help would be appreciated. Thanks! -James P.S. I also included my my.cnf file as well and the bottom. create table FailedPaymentContact ( FailedPaymentContactGenKey int AUTO_INCREMENT PRIMARY KEY, ClearingHouseCreditCardFKey int NOT NULL, INDEX iFailedPaymentContact_ClearingHouseCreditCardFKey(ClearingHouseCreditCardFKe y), ContactDate timestamp NOT NULL, ValidContactTypeFKey int NOT NULL, INDEX iFailedPaymentContact_ValidContactTypeFKey(ValidContactTypeFKey), EmployeeFKey varchar(50) NOT NULL, INDEX iFailedPaymentContact_EmployeeFKey(EmployeeFKey) ) TYPE = InnoDB; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N400038 FOREIGN KEY ( ClearingHouseCreditCardFKey ) REFERENCES ClearingHouseCreditCard (ClearingHouseCreditCardGenKey) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N400041 FOREIGN KEY ( EmployeeFKey ) REFERENCES Employee (EmployeeID) ON DELETE SET NULL ON UPDATE CASCADE ; ALTER TABLE FailedPaymentContact ADD CONSTRAINT FK_FailedPaymentContact_N40004A FOREIGN KEY ( ValidContactTypeFKey ) REFERENCES ValidContactType (ContactTypeID) ON DELETE SET NULL ON UPDATE CASCADE ; [mysql@warbaby innodblogs]$ cat /etc/my.cnf [mysqld] log-bin=/storage/mysql/ server-id=1 default-table-type=innodb innodb_data_home_dir=/storage/innodb set-variable = innodb_mirrored_log_groups=1 # # innodb_data_file_path=indata1:2000M;indata2:2000M;indata3:2000M;indata4:2000 M;indata5:2000M;indata6:2000M # # set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_size=80M innodb_log_group_home_dir = /storage/innodblogs # # innodb_log_arch_dir = /storage/innodblogs innodb_log_archive = 0 set-variable = innodb_log_files_in_group=3 # # set-variable = innodb_log_file_size=100M set-variable = innodb_log_buffer_size=16M # # # innodb_flush_log_at_trx_commit=1 set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
CHECK syntax
I'd like to set up my CREATE's to have CHECK and REGEXP but can't seem to get 4.0.2 to accept my attempts... CREATE TABLE IF NOT EXISTS aptype ( aptype_id INT UNSIGNED NOT NULL AUTO_INCREMENT, appt_type VARCHAR(3) NOT NULL, branch VARCHAR(2), print_ticket VARCHAR(1), CHECK (SELECT (print_ticket) REGEXP "[0-9]"), print_on_stmtVARCHAR(1), CHECK (print_on_stmt IN "Y|N|E|R"), produce_ticket VARCHAR(1), CHECK (produce_ticket REGEXP [ASP]) ) TYPE=InnoDB; None of the CHECK stmts will pass the syntax checker... Help greatly appreciated. Larry Irwin CCA Medical 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
CHECK syntax
I'd like to set up my CREATE's to have CHECK and REGEXP but can't seem to get 4.0.2 to accept my attempts... CREATE TABLE IF NOT EXISTS aptype ( aptype_id INT UNSIGNED NOT NULL AUTO_INCREMENT, appt_type VARCHAR(3) NOT NULL, branch VARCHAR(2), print_ticket VARCHAR(1), CHECK (SELECT (print_ticket) REGEXP "[0-9]"), print_on_stmtVARCHAR(1), CHECK (print_on_stmt IN "Y|N|E|R"), produce_ticket VARCHAR(1), CHECK (produce_ticket REGEXP [ASP]) ) TYPE=InnoDB; None of the CHECK stmts will pass the syntax checker... Help greatly appreciated. Larry Irwin CCA Medical 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
MyODBC-3.51.03 crashes in SQLError with Bus Error
I've found a nasty in SQLError.. I'm guessing it is a causualty of
cut-n-paste.. Notice that the CLEAR_STMT_ERROR macro is being used to clear
the error of "hstmt" when it's null.. Not only is the macro name wrong, but
the argument as well.. In my case, hstmt was null, and it was dereferencing
a null pointer.. My change is below (further down):
BROKEN VERSION ---
SQLRETURN SQL_API SQLError(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt,
SQLCHAR FAR*szSqlState,
SQLINTEGER FAR *pfNativeError,
SQLCHAR FAR*szErrorMsg,
SQLSMALLINTcbErrorMsgMax,
SQLSMALLINT FAR *pcbErrorMsg)
{
SQLRETURN error=SQL_INVALID_HANDLE;
DBUG_ENTER("SQLError");
if (hstmt){
error = my_SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,szSqlState,
pfNativeError, szErrorMsg,
cbErrorMsgMax,pcbErrorMsg);
if(error == SQL_SUCCESS)
CLEAR_STMT_ERROR(((STMT FAR*)hstmt));
}
else if (hdbc){
error = my_SQLGetDiagRec(SQL_HANDLE_DBC,hdbc,1,szSqlState,
pfNativeError, szErrorMsg,
cbErrorMsgMax,pcbErrorMsg);
if(error == SQL_SUCCESS)
CLEAR_STMT_ERROR(((STMT FAR*)hstmt));
}
else if (henv){
error = my_SQLGetDiagRec(SQL_HANDLE_ENV,henv,1,szSqlState,
pfNativeError, szErrorMsg,
cbErrorMsgMax,pcbErrorMsg);
if(error == SQL_SUCCESS)
CLEAR_STMT_ERROR(((STMT FAR*)hstmt));
}
DBUG_RETURN(error);
}
== MENDED VERSION =
SQLRETURN SQL_API SQLError(SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt,
SQLCHAR FAR*szSqlState,
SQLINTEGER FAR *pfNativeError,
SQLCHAR FAR*szErrorMsg,
SQLSMALLINTcbErrorMsgMax,
SQLSMALLINT FAR *pcbErrorMsg)
{
SQLRETURN error=SQL_INVALID_HANDLE;
DBUG_ENTER("SQLError");
if (hstmt){
error = my_SQLGetDiagRec(SQL_HANDLE_STMT,hstmt,1,szSqlState,
pfNativeError, szErrorMsg,
cbErrorMsgMax,pcbErrorMsg);
if(error == SQL_SUCCESS)
CLEAR_STMT_ERROR(((STMT FAR*)hstmt));
}
else if (hdbc){
error = my_SQLGetDiagRec(SQL_HANDLE_DBC,hdbc,1,szSqlState,
pfNativeError, szErrorMsg,
cbErrorMsgMax,pcbErrorMsg);
if(error == SQL_SUCCESS)
CLEAR_DBC_ERROR(((STMT FAR*)hdbc));
}
else if (henv){
error = my_SQLGetDiagRec(SQL_HANDLE_ENV,henv,1,szSqlState,
pfNativeError, szErrorMsg,
cbErrorMsgMax,pcbErrorMsg);
if(error == SQL_SUCCESS)
CLEAR_ENV_ERROR(((STMT FAR*)henv));
}
DBUG_RETURN(error);
}
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication error 1159
At 01:48 PM 7/12/2002, Jeremy Zawodny wrote: >On Fri, Jul 12, 2002 at 01:43:02PM -0700, Jon Drukman wrote: > > now instead of getting the error every 30 seconds, i get it every > > hour. is there a way to make it go away forever? > >Make the number really big? > >I wonder if you can set it to 0 to mean "don't timeout". But that >could be bad in some cases too. > > > why does it happen and what does it mean? > >It means that the slave closed the connection because it hadn't heard >from the master in a while. You must not be sending a lot of data to >the slave(s) regularly, huh? right, these machines are in test mode. the databases aren't being modified/used at all at the moment. i'm just setting them up for production use later. i sure would have been happier if the message wasn't phrased like some sort of network communication error! 020712 15:39:59 Error reading packet from server: (server_errno=1159) 020712 15:40:59 Slave: Failed reading log event, reconnecting to retry, log 'rs1-bin.004' position 215 020712 15:40:59 Slave: reconnected to master 'replica@rs1private:3306',replication resumed in log 'rs1-bin.004' i'll probably set it to 86400 (1 day) eventually. i just wanted to make sure that i wasn't masking the symptom of some deep problem. thanks for the speedy reply. -jsd- sql query purple monkey dishwasher - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Heiki, Is this problem fixed in 4.02.
Satish, you can look from http://www.mysql.com/doc/N/e/News.html if you can find such a bug fix from 3.23 or 4.0 during the past 6 months. 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 - Original Message - From: "Satish Prabhu" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, July 12, 2002 11:24 PM Subject: Heiki, Is this problem fixed in 4.02. > Hi Heikki, > > I am addressing this mail specifically to you, because I am still trying to > create a small reproducable test case. The brief description is that on > Windows MySQL 4.01 a left join query of two tables gives different results > at different times even when executed on a mysql command line. Was wondering > whether this has been reported earlier, and has been fixed in 4.02. > > Thanks > Satish > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication error 1159
On Fri, Jul 12, 2002 at 01:43:02PM -0700, Jon Drukman wrote: > i am having the "Error reading packet from server: (server_errno=1159)" > replication problem. > > in searching the archives for others experience replication problems, i > came across the suggestion of adding these lines to my.cnf > > set-variable = slave_net_timeout=3600 > set-variable = net_read_timeout=3600 > > now instead of getting the error every 30 seconds, i get it every > hour. is there a way to make it go away forever? Make the number really big? I wonder if you can set it to 0 to mean "don't timeout". But that could be bad in some cases too. > why does it happen and what does it mean? It means that the slave closed the connection because it hadn't heard from the master in a while. You must not be sending a lot of data to the slave(s) regularly, huh? Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 43 days, processed 958,678,930 queries (252/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
Fw: Minor install problem w/max-4.0.2
Concerning mysql-max-4.0.2-alpha-pc-linux-gnu-i686.tar.gz on SuSE 7.3 If you cd /usr/local and unpack the tarball then create a symlink to mysql, cd into mysql and run ./bin/mysqld.safe the daemon can't locate mysql-max-4.0.2-alpha-pc-linux-gnu-i686/share/mysql/english/errmsg.sys. If you remove the symlink and mv the distro name dir to mysql, then it works. Larry Irwin CCA Medical - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replication error 1159
i am having the "Error reading packet from server: (server_errno=1159)" replication problem. in searching the archives for others experience replication problems, i came across the suggestion of adding these lines to my.cnf set-variable = slave_net_timeout=3600 set-variable = net_read_timeout=3600 now instead of getting the error every 30 seconds, i get it every hour. is there a way to make it go away forever? why does it happen and what does it mean? replication still seems to work, but i'm anticipating putting these machines into a production environment soon and it makes me nervous. -jsd- sql query (for the filter) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error: undefined symbol "strtoll"
In the last episode (Jul 12), Robert Boyd Skipper said: > I've written a Perl application that works fine with MySQL as installed > in my Windose system, but causes MySQL to restart when I run it on my > FreeBSD version. I've narrowed it down to one script where the > problem seems to recur, but that script is pretty long. I will send it, > if that will help, but I'm hoping that someone can just point me at > someplace to start looking. > > The precompiled version of MySQL I installed is > mysql-3.23.51-unknown-freebsdelf4.6-i386. > The version of FreeBSD I'm running is FreeBSD 4.1.1-RELEASE (GENEREIC) > The version of perl is 5.005_03 > The version of DBI.pm is DBI-1.28 > The version of the Msql-Mysql-modules is 1.2219, but only MySQL is > installed. > > There were no installation errors, and other scripts seem to work fine. > > The error log entry reads: > Start log excerpt--- > /usr/libexec/ld-elf.so.1: >/usr/home/mysqladm/mysql-3.23.51-unknown-freebsdelf4.6-i386/bin/mysqld: Undefined >symbol "strtoll" > 020712 14:12:05 mysqld restarted > /usr/home/mysqladm/mysql-3.23.51-unknown-freebsdelf4.6-i386/bin/mysqld: ready for >connections > End log excerpt-- > > The script that causes the problem is in my cgi-bin directory, and is > called by a dynamically generated html page from another script. When > I run the script, this message is returned to my browser, "Lost > connection to MySQL server during query." > > Thanks in advance for any clues, You might want to consider upgrading your FreeBSD to 4.6. Pthreads were a bit unstable in the early 4.* releases. You may be hitting a bug that has been fixed since then. I'm also a bit confused about the strtoll error; that's usually a fatal error, but mysqld apparently started up anyway. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation question
Would I get better performance if mysql were being run from a SCSI hard drive? Lawrence - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error: undefined symbol "strtoll"
Hello: I've written a Perl application that works fine with MySQL as installed in my Windose system, but causes MySQL to restart when I run it on my FreeBSD version. I've narrowed it down to one script where the problem seems to recur, but that script is pretty long. I will send it, if that will help, but I'm hoping that someone can just point me at someplace to start looking. The precompiled version of MySQL I installed is mysql-3.23.51-unknown- freebsdelf4.6-i386. The version of FreeBSD I'm running is FreeBSD 4.1.1-RELEASE (GENEREIC) The version of perl is 5.005_03 The version of DBI.pm is DBI-1.28 The version of the Msql-Mysql-modules is 1.2219, but only MySQL is installed. There were no installation errors, and other scripts seem to work fine. The error log entry reads: Start log excerpt--- /usr/libexec/ld-elf.so.1: /usr/home/mysqladm/mysql-3.23.51-unknown- freebsdelf4.6-i386/bin/mysqld: Undefined symbol "strtoll" 020712 14:12:05 mysqld restarted /usr/home/mysqladm/mysql-3.23.51-unknown-freebsdelf4.6-i386/bin/mysqld: ready for connections End log excerpt-- The script that causes the problem is in my cgi-bin directory, and is called by a dynamically generated html page from another script. When I run the script, this message is returned to my browser, "Lost connection to MySQL server during query." Thanks in advance for any clues, Robert Boyd Skipper, Ph. D. Assistant Professor Department of Philosophy St. Mary's University San Antonio, Texas 78228 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Inner Join in MySQL
In the last episode (Jul 12), Carlos Rovetto said: > Hi. I want know why the time of response is slowly when i make a > Join between two tables using sql query in MySQL. > > I don't have more experience in MySQL Make sure you have indexes on the columns you use to join the tables. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Using Inner Join in MySQL
It depends on the size of the tables that you are joining together. Can you index any of the columns that you are joining? If you post the query that you are trying to execute I might be able to help you more. DK -Original Message- From: Carlos Rovetto [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 12:32 PM To: [EMAIL PROTECTED] Subject: Using Inner Join in MySQL Hi. I want know why the time of response is slowly when i make a Join between two tables using sql query in MySQL. I don't have more experience in MySQL Carlos _ MSN. Más Útil cada Día. http://www.msn.es/intmap/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Inner Join in MySQL
Hi. I want know why the time of response is slowly when i make a Join between two tables using sql query in MySQL. I don't have more experience in MySQL Carlos _ MSN. Más Útil cada Día. http://www.msn.es/intmap/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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/InnoDB-4.0.2 is released
Does your my.cnf file contain any comments that begin after a configuration option? I had similar problems setting up 4.0.2, but stripping such comments fixed it. E.G.: set-variable = innodb_mirrored_log_groups=1 # Some comment here... MySQL 4.0.2 complains about the set-variable statement, but remove the comment and it's fine.. -JF > -Original Message- > From: zlab1 [mailto:[EMAIL PROTECTED]] > Sent: Friday, July 12, 2002 10:54 AM > To: 'Heikki Tuuri'; [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: MySQL/InnoDB-4.0.2 is released > > > Hi, > I'm sorry to trouble you, I was upgrade from mysql 4.01 to 4.02 in > mandrake 8.2. > > The following options in current running my.cnf suddently not work: > > innodb_data_file_path = ibdata1:100M > innodb_data_home_dir = /data/mysql/ > innodb_log_group_home_dir = /data/mysql/ > innodb_log_arch_dir = /data/mysql/ > innodb_flush_log_at_trx_commit= 1 > innodb_log_archive= 0 > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=4M > set-variable = innodb_log_buffer_size=4M > set-variable = innodb_buffer_pool_size=64M > set-variable = innodb_additional_mem_pool_size=4M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=50 > set-variable = innodb_thread_concurrency=8 > > I change the 1st: > innodb_data_file_path = ibdata1:100M > To > innodb_data_file_path = /data/mysql/ibdata1:100M:autoextend > > I have to remark rest options in order to make it working and also > "set-variable = " seen not needed?? > The err log show: > > ERROR: unknown variable '..' whenever I keep any options > other than the 1st line, > All previous section section is working when remove 'set-variable = '. > > So the problem isolated to innodb options sections and seen no doc > refer this change, would u kind enough to give me a hand. > > Most of the options is copy from example config file with minor > changesIt is working in 4.0 4.01 and suddently not work now. > Thanks. > > Please reply to [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
Re: MySQL/InnoDB-4.0.2 is released
Hi! Below is my my.cnf. I tested the mysql-4.0.2-alpha-pc-linux-gnu-i686.tar.gz distro and it worked. What distro did you use? Regards, Heikki # The MySQL server [mysqld] #bdb-no-sync server-id=1 language=/home/heikki/mysql-4.0/sql/share/english default-table-type=innodb #log-bin port=3307 socket=/home/heikki/MySQLheikki skip-locking default-character-set=latin1 basedir=/home/heikki datadir=/home/heikki/data set-variable= table_cache=200 set-variable= max_connections=300 set-variable= sort_buffer=4M set-variable= record_buffer=1M set-variable= max_allowed_packet=16M set-variable= thread_stack=256K set-variable= key_buffer=64M #set-variable = flush_time=1800 innodb_data_home_dir =/home/heikki innodb_data_file_path = ibdata/ibdata1:12M;ibdata/ibdata2:4M;ibdata/ibdata3:306\ M;ibdata/ibdata4:450M:autoextend set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /home/heikki/iblogs innodb_log_arch_dir = /home/heikki/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=100M set-variable = innodb_additional_mem_pool_size=128M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=60 - Original Message - From: "zlab1" <[EMAIL PROTECTED]> To: "'Heikki Tuuri'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, July 12, 2002 8:53 PM Subject: RE: MySQL/InnoDB-4.0.2 is released > Hi, > I'm sorry to trouble you, I was upgrade from mysql 4.01 to 4.02 in > mandrake 8.2. > > The following options in current running my.cnf suddently not work: > > innodb_data_file_path = ibdata1:100M > innodb_data_home_dir = /data/mysql/ > innodb_log_group_home_dir = /data/mysql/ > innodb_log_arch_dir = /data/mysql/ > innodb_flush_log_at_trx_commit = 1 > innodb_log_archive = 0 > set-variable = innodb_mirrored_log_groups=1 > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_file_size=4M > set-variable = innodb_log_buffer_size=4M > set-variable = innodb_buffer_pool_size=64M > set-variable = innodb_additional_mem_pool_size=4M > set-variable = innodb_file_io_threads=4 > set-variable = innodb_lock_wait_timeout=50 > set-variable = innodb_thread_concurrency=8 > > I change the 1st: > innodb_data_file_path = ibdata1:100M > To > innodb_data_file_path = /data/mysql/ibdata1:100M:autoextend > > I have to remark rest options in order to make it working and also > "set-variable = " seen not needed?? > The err log show: > > ERROR: unknown variable '..' whenever I keep any options > other than the 1st line, > All previous section section is working when remove 'set-variable = '. > > So the problem isolated to innodb options sections and seen no doc > refer this change, would u kind enough to give me a hand. > > Most of the options is copy from example config file with minor > changesIt is working in 4.0 4.01 and suddently not work now. > Thanks. > > Please reply to [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
mysql_install_db problems on PS2!
I'm trying to install MySQL on my Playstation 2 (mipsel-pc-linux-gnu, apparently). For entertainment purposes, you understand. :) It configures, compiles, and installs fine, but when I get to running mysql_install_db, it gets a bit upset. Lots of error 1033. Am I going to be able to get there from here? It's not urgent, I'm just playing. Any ideas where to look? Thanks, Simon Full output of script: [simon@xyzzy mysql-3.23.51]$ sudo scripts/mysql_install_db Password: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' 020712 18:13:50 /usr/local/mysql/libexec/mysqld: Shutdown Complete -- This message has been scanned for viruses and dangerous content, and is believed to be clean. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Can access DBs when started w/ safe_mysql but not with mysqld ????
Yes, exactly. chown -R mysql /usr/local/mysql chown -R mysql /var/lib/mysql Of course you need to substitute the actual paths for the distribution and data directories. David Kramer wrote: >Ok thats what I was guessing, so I need to create a new user for the DB >seperate from the root. Do I " chown " the binaries and database files to >my new user? Or Do the binaries always stay to root? > >Thanks much! > >DK > >-Original Message- >From: Gerald Clark [mailto:[EMAIL PROTECTED]] >Sent: Friday, July 12, 2002 11:08 AM >To: David Kramer >Cc: [EMAIL PROTECTED] >Subject: Re: Can access DBs when started w/ safe_mysql but not with >mysqld > > >The mysql user should own the databases and files, not root. > >David Kramer wrote: > >>I am able to access all of the database instances that I have created when >>starting Mysql with safe_mysql(as root user). As soon as I start mysql any >>other way, I keep getting the Access Denied Error. Can root only access >> >DBs > >>under safe_mysql? Im completely lost here >> >>Thanks, >> >>DK >> >>David Kramer >>Reflect.com >> >> >> >>- >>Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >>To request this thread, e-mail <[EMAIL PROTECTED]> >>To unsubscribe, e-mail >> ><[EMAIL PROTECTED]> > >>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can access DBs when started w/ safe_mysql but not with mysqld????
Ok thats what I was guessing, so I need to create a new user for the DB seperate from the root. Do I " chown " the binaries and database files to my new user? Or Do the binaries always stay to root? Thanks much! DK -Original Message- From: Gerald Clark [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 11:08 AM To: David Kramer Cc: [EMAIL PROTECTED] Subject: Re: Can access DBs when started w/ safe_mysql but not with mysqld The mysql user should own the databases and files, not root. David Kramer wrote: >I am able to access all of the database instances that I have created when >starting Mysql with safe_mysql(as root user). As soon as I start mysql any >other way, I keep getting the Access Denied Error. Can root only access DBs >under safe_mysql? Im completely lost here > >Thanks, > >DK > >David Kramer >Reflect.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
My.cnf problem when upgrade from 4.01 to 4.02
Hi, I'm a new user of mysql, I was upgrade from mysql 4.01 to 4.02 in mandrake 8.2. The following options in current running my.cnf suddently not work: innodb_data_file_path = ibdata1:100M innodb_data_home_dir= /data/mysql/ innodb_log_group_home_dir = /data/mysql/ innodb_log_arch_dir = /data/mysql/ innodb_flush_log_at_trx_commit = 1 innodb_log_archive = 0 set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=4M set-variable = innodb_log_buffer_size=4M set-variable = innodb_buffer_pool_size=64M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=8 I change the 1st: innodb_data_file_path = ibdata1:100M To innodb_data_file_path = /data/mysql/ibdata1:100M:autoextend I have to remark rest options in order to make it working and also "set-variable = " seen not needed?? The err log show: ERROR: unknown variable '..' whenever I keep any options other than the 1st line, All previous section section is working when remove 'set-variable = '. So the problem isolated to innodb options sections and seen no doc refer this change, would u kind enough to give me a hand. Most of the options is copy from example config file with minor changesIt is working in 4.0 4.01 and suddently not work now. Thanks. If possible, Please reply to [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: Can access DBs when started w/ safe_mysql but not with mysqld????
The mysql user should own the databases and files, not root. David Kramer wrote: >I am able to access all of the database instances that I have created when >starting Mysql with safe_mysql(as root user). As soon as I start mysql any >other way, I keep getting the Access Denied Error. Can root only access DBs >under safe_mysql? Im completely lost here > >Thanks, > >DK > >David Kramer >Reflect.com > > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL/InnoDB-4.0.2 is released
Hi, I'm sorry to trouble you, I was upgrade from mysql 4.01 to 4.02 in mandrake 8.2. The following options in current running my.cnf suddently not work: innodb_data_file_path = ibdata1:100M innodb_data_home_dir= /data/mysql/ innodb_log_group_home_dir = /data/mysql/ innodb_log_arch_dir = /data/mysql/ innodb_flush_log_at_trx_commit = 1 innodb_log_archive = 0 set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=4M set-variable = innodb_log_buffer_size=4M set-variable = innodb_buffer_pool_size=64M set-variable = innodb_additional_mem_pool_size=4M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 set-variable = innodb_thread_concurrency=8 I change the 1st: innodb_data_file_path = ibdata1:100M To innodb_data_file_path = /data/mysql/ibdata1:100M:autoextend I have to remark rest options in order to make it working and also "set-variable = " seen not needed?? The err log show: ERROR: unknown variable '..' whenever I keep any options other than the 1st line, All previous section section is working when remove 'set-variable = '. So the problem isolated to innodb options sections and seen no doc refer this change, would u kind enough to give me a hand. Most of the options is copy from example config file with minor changesIt is working in 4.0 4.01 and suddently not work now. Thanks. Please reply to [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
Stupid libtool & MyODBC on HP-UX 11i
Sorry about the rant.. I've been spending the last two days trying to install gcc, MySQL, MyODBC and UnixODBC on several AIX and HP boxes with varying degrees of success and lots of aggravation (much of it due to braindead configure scripts that ALWAYS want to leave gcc arguments around when NOT using gcc!).. Anyway, I was able to get MyODBC built *late* last night and this morning found that I was getting the following error when trying to run a program that was using MyODBC (3.51.03) via UnixODBC (2.2.2): % ./test-program /usr/lib/dld.sl: Unresolved symbol: _db_enter_ (code) from /usr/local/unixodbc/lib/libmyodbc3-3.51.03.sl Abort If I do a "chatr" on libmyodbc3-3.51.03.sl, I get the following : % chatr /usr/local/unixodbc/lib/libmyodbc3-3.51.03.sl /usr/local/unixodbc/lib/libmyodbc3-3.51.03.sl: shared library shared library dynamic path search: SHLIB_PATH disabled first embedded path disabled second Not Defined internal name: libmyodbc3-3.51.03.sl shared vtable support disabled static branch prediction disabled executable from stack: D (default) kernel assisted branch prediction enabled lazy swap allocation disabled text segment locking disabled data segment locking disabled third quadrant private data space disabled fourth quadrant private data space disabled third quadrant global data space disabled data page size: D (default) instruction page size: D (default) nulptr references disabled Notice that it has NO DEPENDENCIES on libmysqlclient.sl.. Also notice that if I do the following, that I'm missing ALL references to stuff in the aforementioned client lib : % nm -u /usr/local/unixodbc/lib/libmyodbc3-3.51.03.sl ... [some output chopped for brevity sake ] my_SQLAllocConnect my_SQLAllocEnv my_SQLAllocStmt my_SQLBindParameter my_SQLDisconnect my_SQLDriverConnect my_SQLExecute my_SQLExtendedFetch my_SQLFreeConnect my_SQLFreeEnv my_SQLFreeStmt my_SQLGetDiagRec my_SQLPrepare ... Now, if I look at what crap libtool did, I find the following when building MyODBC : /bin/sh ./libtool --mode=link gcc -O2 -o libmyodbc3.la -rpath /usr/local/unixodbc/lib -release 3.51.03 catalog.lo connect.lo dll.lo execute.lo handle.lo info.lo myodbc3.lo options.lo prepare.lo results.lo transact.lo utility.lo cursor.lo error.lo misc.lo -L/opt/mysql/lib/mysql -lmysqlclient -L/usr/local/unixodbc/lib -L/opt/mysql/lib/mysql -lz -lodbcinst mkdir .libs *** Warning: inter-library dependencies are not known to be supported. *** All declared inter-library dependencies are being dropped. *** The inter-library dependencies that have been dropped here will be *** automatically added whenever a program is linked with this library *** or is declared to -dlopen it. /usr/ccs/bin/ld -b +h libmyodbc3-3.51.03.sl +s +b /usr/local/unixodbc/lib -o .libs/libmyodbc3-3.51.03.sl catalog.lo connect.lo dll.lo execute.lo handle.lo info.lo myodbc3.lo options.lo prepare.lo results.lo transact.lo utility.lo cursor.lo error.lo misc.lo Notice that it has completely dropped the "-L/opt/mysql/lib/mysql -lmysqlclient -L/usr/local/unixodbc/lib -L/opt/mysql/lib/mysql -lz -lodbcinst" This just means that the friggin library is totally whacked and ought to be put out to pasture.. In the meantime, I'm done a re-link by HAND to make sure it gets built properly.. See below : % /usr/ccs/bin/ld -b +h libmyodbc3-3.51.03.sl +s +b /usr/local/unixodbc/lib -o .libs/libmyodbc3-3.51.03.sl catalog.lo connect.lo dll.lo execute.lo handle.lo info.lo myodbc3.lo options.lo prepare.lo results.lo transact.lo utility.lo cursor.lo error.lo misc.lo -L/opt/mysql/lib/mysql -lmysqlclient -L/usr/local/unixodbc/lib -L/opt/mysql/lib/mysql -L/usr/local/lib -lz -lodbcinst Now that that is complete, notice what "chatr" finds now (notice the shared library list that has two items in it) : % chatr libmyodbc3-3.51.03.sl libmyodbc3-3.51.03.sl: shared library shared library dynamic path search: SHLIB_PATH enabled first embedded path enabled second /usr/local/unixodbc/lib internal name: libmyodbc3-3.51.03.sl shared library list: dynamic /opt/mysql/lib/mysql/libmysqlclient.sl.10 dynamic /usr/local/unixodbc/lib/libodbcinst.sl.1 shared vtable support disabled static branch prediction disabled executable from stack: D (default) kernel assisted branch prediction enabled lazy swap allocation disabled text segment locking disabled data segment locking disabled third quadrant private data space disabled fourth quadrant private data space disabled third quadrant global data space disabled data page size: D (default) instruction page size: D (default) nulptr r
mySQL user priviliges
Hi I Have a question relating to setting up users for our customers on a shared server environment. I can not find this answer in the documentation so I am trying here. Is it standard practice to give the user of the database (site owner) one set of permissions which I figure should be: select, insert, update,delete, create, drop On database to user@localhost and create one for them to put into their php pages that call the database from the website (which would be the one their visitors are using)? like an IUSER_ but for mysql? If so, what should the bare minimum permissions be for this username. I think insert, update, delete and select, but I am not sure. Thanks for your help. Alex - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: ORDER BY RAND() not working
I don't have your exact setup installed so cannot verify this, but how about
rewriting your statement a little? I just tried this in mysql and it works
fine, so it may from php. Let me know :-)
Change the query to:
SELECT *, Rand() as MyOrder
FROM products
WHERE sale_price IS NOT NULL
ORDER BY MyOrder;
hth,
Arthur
- Original Message -
From: "Jamie Tibbetts" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 11, 2002 1:14 PM
Subject: Re: ORDER BY RAND() not working
> > $id = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL
ORDER
> > BY RAND() LIMIT 3",$link) or die(mysql_error());
>
> PHP code:
> $result = mysql_query("SELECT * FROM products WHERE sale_price IS NOT NULL
> ORDER BY RAND() LIMIT 3") or die(mysql_error());
>
> The error message is as follows:
>
> You have an error in your SQL syntax near 'RAND() LIMIT 3' at line 1
>
> Jamie
>
> Jamie Tibbetts.[EMAIL PROTECTED]
> Epigroove .http://www.epigroove.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
Heiki, Is this problem fixed in 4.02.
Hi Heikki, I am addressing this mail specifically to you, because I am still trying to create a small reproducable test case. The brief description is that on Windows MySQL 4.01 a left join query of two tables gives different results at different times even when executed on a mysql command line. Was wondering whether this has been reported earlier, and has been fixed in 4.02. Thanks Satish - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can access DBs when started w/ safe_mysql but not with mysqld????
I am able to access all of the database instances that I have created when starting Mysql with safe_mysql(as root user). As soon as I start mysql any other way, I keep getting the Access Denied Error. Can root only access DBs under safe_mysql? Im completely lost here Thanks, DK David Kramer Reflect.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
newlines and REPLACE for mysqldump
Hello list, I would like to propose two changes for mysqldump: 1) With --extended-insert, separate the data for the individual rows with a newline (and possibly two spaces for alignment); this adds only a byte (or three) per row, is more readable, and most importantly (for me), it allows for diff-ing of two version of the dump. 2) Add an option for using REPLACE instead of INSERT. For newly created tables it makes no difference, but with --no-create-info, it could be used for updating existing table data. Thanks! Vaclav Dvorak [EMAIL PROTECTED] IDAS, s.r.o.http://www.idas.cz - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 with AUTO_INCREMENT
Mark Matthews wrote: > Steve Hay wrote: > | I've tried the same myself on MyISAM tables as well now, and I also > | found that there is no problem - it only seems to happen using BDB > tables. > | > | However, I need to use BDB tables rather than MyISAM to get transaction > | support, which is required by the software that I'm working on. > | > | In fact, it seems that BDB tables work too if the transaction > support is > | not used, i.e. set AUTOCOMMIT=1 and don't bother with explicit COMMIT > | statements. > | > | Maybe MySQL has a problem with AUTO_INCREMENT when used with > transactions? > > Is there a reason you can't use InnoDB tables? They are > transaction-capable, and they have a lot more active development on them > than BDB. They also support AUTO_INCREMENT. > > -Mark I had been overlooking InnoDB tables because of the need to specify a size for the database when it is created, which seems a rather awkward design to me. The software that I'm working on will be running on many databases with widely varying sizes, and not needing to worry about that for each one was more appealing. Anyway, just out of interest, I have now tried your suggestion, and I found that InnoDB tables work brilliantly! The test completes in record time with no deadlock errors or duplicate entry errors. Returning to my real software, I find that it also works like a dream (except for the occasional deadlock, which I think SELECT ... FOR UPDATE will avoid). I also note that the latest versions of InnoDB have an "autoextend" option which largely removes my problem of worrying about file sizes. I did have one fright, though: getting a 120MB+ "mysqld.exe" process showing up in Task Manager (rather than the 15MB or so that I get with --skip-innodb), but it seems that this was due to me having set the innodb_buffer_pool_size option to 50% of memory as the manual suggests. If I comment out the setting, leaving it at whatever default vaule it uses then the process size comes down to a much more reasonable 19MB! What is the default innodb_buffer_pool_size setting? The manual doesn't seem to say. Looks like I could end up using InnoDB tables. Thanks for the suggestion. - 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
RE: Load problems with 3.23.51
Just a note: I tried MySQL 4.0.2 and it works fine. Seems to be only 3.23.51 built by MySQL itself that has the issue. Releases before, and now a release after (albeit a 4.0.x version) work fine. Sincerely, Steven Roussey http://Network54.com/?pp=e > > I have MySQL 3.23.47 running on our sever. I skipped 48 through 50 and > tried 51. No dice. It does not handle load, CPU and the load average go > through the roof. I'm using Red Hat Linux 7.2 and the official mysql > binaries. It appears to be slow to connect, causing 0.5 to 1.0 second > delay on connection. Using persistent connections from PHP does not make > much of a difference. I thought it might be the hostname lookup changes so > I chose skip-grant-tables. This doesn't actually skip the hostname lookup > though and had no effect. > > Most queries are shorter than 1 second so this problem causes catastrophic > problems by making queries last a multiple times longer, which make the > number of concurrent queries jump exponentially. This is a bad thing. And > sadly makes 3.23.51 unusable. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
64 indexes in MyISAM tables
I've read that it is possible to use 64 indexes in MyISAM tables in MySQL. I haven't been able to find any documentation on how to actually do this though. any references to appropriate documentation would be greatly appreciated. regards Sean Peters [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: Order by - problem with numerics in varchar field
On 11 Jul 2002, at 16:06, Dan Lamb wrote: > I'd like it to order like this: > > aristo 156 > aristo 222 > aristo 1001 > > How can I do this in MySQL? Is there a way to take the numbers into > account when using order by? There are various ways to break up your strings and convert part to a number using MySQL SQL, depending on what assumptions we're allowed to make about the format of your strings and the size of your numbers. Jay Blanchard has posted one possibility. However, your sorting will be much faster (and can use indexes better) if you don't have to do such calculations for each row every time you want to sort. If you need to sort by a two-part key, then you really should split the key into two columns, make one VARCHAR and one SMALLINT (or whatever), and make an index on both. -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.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: MySQL Relationships
Hi, For estabilish the relationship you have to type the same same sql command what you fire from other RDBMS like oracle.Only the command prompt is mysql> in plance of sql>. For more detail go to www.mysql.com and read documentations. Thanks, Anjani -Original Message- From: Otoniel Cantu` [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 11:06 AM To: [EMAIL PROTECTED] Subject: MySQL Relationships Hi, I am new to MySQL and was needing to know about MySQL and relationship between tables. Can it be done and if so how? Any docs or show me how would be greatly appreciated. Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: reload my.cnf
Hussein, Thursday, July 11, 2002, 12:45:38 PM, you wrote: HM> when i change my configfile my.cnf, i have to restart my server. is HM> there another way without restarting mysqld ? If you change [mysqld] section, nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: --skip-locking options!
louie, Friday, July 12, 2002, 12:06:07 PM, you wrote: lm> http://www.mysql.com/doc/C/r/Crash_recovery.html lm> Is this very reliable? Coz im planning to remove the --skip-locking options. On some OS, like Linux, Windows, Solaris skip-locking is ON by default. Why do you want remove --skip-locking? to use myisamchk when mysqld is running? If so, you can just use CHECK and REPAIR statements... Moreover if you don't use skip-locking it would be slow down MySQL server because of extra system calls. And you also can run into lockup problems on any OS ... -- 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
select query
When I try this: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) LIMIT 0, 100 I got this error message: You have an error in your SQL syntax near 'SELECT MAX(s2.price) FROM shop s2 WHERE s1.article' at line 3 My shop table contains this data: +-++---+ | article | dealer | price | +-++---+ |0001 | A | 3.45 | |0001 | B | 3.99 | |0002 | A | 10.99 | |0003 | B | 1.45 | |0003 | C | 1.69 | |0003 | D | 1.25 | |0004 | D | 19.95 | +-++---+ and the table was created with this commnad: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); Thank You ! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Goodday
This is funny, but my email regarding replication slave disconnect error in 3.23.51 was bounced back as Spam and asked me to add sql,query in message. Whereas, Spam like this goes unnoticed. Nilesh -Original Message- From: Njamba [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 5:52 AM To: MySql List Subject: Fw: Goodday I wonder who to send this mail to. So I decided to send it to the list may be someone responsible for such will see it. There is someone spreading spam through the MySql List. I received this attached e-mail today If you check correctly it was originally addressed to the list. And that is how I ended up with a copy. Please someone do the necessary to prevent spam being sent through the list. I will highly appreciate if it is announced through the list that something is being done. Kind regards Harrison - Original Message - From: "Oluwafunmi Gabriel" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, November 14, 2001 13:08 Subject: Goodday > Attn: mysql , > > My name is Mr. Oluwafunmi G, the manager, credit > and foreign bills of Ecobank Plc. I am writing in > respect of a foreign customer of my bank with account > number 14-255-2004/utb/t who perished in a plane crash > [Korean Air Flight 801] with the whole passengers > aboard on August 6, 1997. > Since the demise of this our customer, I personally > has watched with keen interest to see the next of kin > but all has proved abortive as no one has come to > claim his funds of usd.20.5 m, [twenty million five > hundred thousand united states dollars] which has been > with my branch for a very long time. > On this note, I decided to seek for whom his name > shall be used as the next of kin as no one has come up > to be the next of kin. And the banking ethics here > does not allow such money to stay more than four > years, because money will be recalled to the bank > treasury as unclaimed after this period. In view of > this I got your contact through a trade journal after > realizing that your name and country is similar to the > deceased. I will give you 25% of the total. > Upon the receipt of your response, I will send you by > fax or e-mail the application, bank's fax number and > the next step to take. I will not fail to bring to > your notice that this business is hitch free and that > you should not entertain any fear as all modalities > for fund transfer can be finalized within five banking > days, after you apply to the bank as a relation to the > deceased. > When you receive this letter. Kindly send me an e-mail > signifying > Your decision including your private Tel/Fax numbers > for quick communication. > Respectfully submitted, > Oluwafunmi G. > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - This email and any files transmitted with it are confidential and intended solely for the use of the addressee. If you have received this email in error you may not disclose, copy, distribute or act upon its contents without the authors prior written consent. Please can you notify the system manager at [EMAIL PROTECTED] immediately and delete this email. - Any views or opinions expressed are those of the author and do not necessarily represent those of Somak Travel Limited, Kenya. - This footnote also confirms that this email message has been swept for the presence of computer viruses. No responsibility can be accepted in the event that a virus is contained within it or any attachments. - SOMAK TRAVEL LTD - KENYA NAIROBI, PO BOX 48495. Tel: +(2542) 557832 Fax:+(2542) 535175 MOMBASA, PO BOX 90738. Tel: +(25411)486197 Fax:+(25411)487324 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Order by - problem with numerics in varchar field
[snip] I'm having trouble with ordering. I've got data in a varchar field that currently gets ordered like this when I use 'order by myfield asc': aristo 1001 aristo 156 aristo 222 I'd like it to order like this: aristo 156 aristo 222 aristo 1001 How can I do this in MySQL? Is there a way to take the numbers into account when using order by? [/snip] SELECT columnName, RIGHT(columnName, 4) AS theNumber FROM tableName ORDER BY theNumber +-+---+ | columnName | theNumber | +-+---+ | aristo 156 | 156 | | aristo 222 | 222 | | aristo 1001 | 1001 | | aristo 2317 | 2317 | +-+---+ Now, this will only work if you specify the correct number in the RIGHT() function. Since you had 4 digit numbers, I used 4. But if this number is longer, you need to increase your integer in the RIGHT() function as it will not be able to determine the length of the number. mysql> select info AS columnName, SUBSTRING_INDEX(info, " ", -1) AS theNumber from tblStuff ORDER BY theNumber; +-+---+ | columnName | theNumber | +-+---+ | aristo 1001 | 1001 | | aristo 156 | 156 | | aristo 222 | 222 | | aristo 2317 | 2317 | +-+---+ As you can see, the SUBSTRING_INDEX() function retrieves the number after the space, but the query does not sort this the way that you want. RIGHT() takes into account the space before the 3 digit integer and sorts numbers with spaces first, which works for you in this case. The problem with these solutions is that they may not work in every case needed. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 Relationships
Hi, I am new to MySQL and was needing to know about MySQL and relationship between tables. Can it be done and if so how? Any docs or show me how would be greatly appreciated. Thanks in advance! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[4]: Numeric Type Inconsistency ?Bug [not!]
Keith, Thanks for your comments. KCI> It looks like the min and max are rounded to integers. Try KCI> "SELECT 2 BETWEEN 2.1 AND 5" or "SELECT 3 BETWEEN 2 AND 2.8". KCI> Also, the exponential numbers are converted to integers, not KCI> truncated at the "e". It's just that your 1e30 is too big for an KCI> integer. Try it with 1e18 instead. I don't know what 1e30 is KCI> being converted to, though -- apparently not the max BIGINT. Yes, good point. Sure enough, those integer examples get rounded (and not truncated, either syntactically or numerically!) so all return true, and as you expected, the 1e18 works. My _recollection_ from the manual, and what I've seen happen, is that oversized int's do indeed get converted to a MAXINT of some sort, and what's odd is that is clearly not happening when 1e30 is squashed into the integer type for the BETWEEN evaluation. It's not yet obvious to me from the manual how to perform explicit type-casts. I mean, I can do it on a case-by-case basis, so for example in the problem I had earlier I worked around it by doing this in my PHP code... "... WHERE ($myval + 0.0) BETWEEN min_field AND max_field;" But, I don't see any obvious type-specification or casting that I might use in a tidier, more general approach. Still reading though. I tend to read the manual in patches around areas related to a given question at any given time, so my knowledge is a bit uneven ;) -- Best regards, James. [Filter fodder: SQL] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 with AUTO_INCREMENT
Victoria Reznichenko wrote: >Steve, >Wednesday, July 10, 2002, 11:12:48 AM, you wrote: > >SH> The bug can be reproduced as follows: > >SH> Create the following simple database containing just one BDB table: > >SH> CREATE DATABASE test; > >SH> CREATE TABLE foo ( >SH>id INT NOT NULL AUTO_INCREMENT, >SH>num INT, >SH>str VARCHAR(10), >SH>PRIMARY KEY (id) >SH> ) TYPE=BDB; > >SH> Now start two "mysql.exe" sessions running with the "-f" flag to ignore >SH> errors. > >SH> Set each one into "no auto-commit" mode with: > >SH> SET AUTOCOMMIT=0; > >SH> and then feed each of them the following two lines 50,000 times each: > >SH> INSERT INTO foo (num, str) VALUES (1, 'a'); >SH> COMMIT; > >[skip] > >SH> When I inspect the output collected in these log files I find, not >SH> unexpectedly, that there are many "deadlock" errors (which I'm quite >SH> comfortable with, and can handle in my real software by simply retrying >SH> the transaction), but that there are also a few of the following error: > >SH> Duplicate entry '1' for key 1 > >SH> Surely this is a bug isn't it? > >Steve, I tested your example with PHP script, but I didn't get any >Duplicate entry error ... >(I tested with BDB tables and SET AUTOCOMMIT=0). > What was the PHP script that you tested it with? Perhaps I could try the same PHP script myself and see what I get. I've also found that my test fails on some machines and works on others; sometimes it runs on a lot further than 50,000 iterations on some machines before it fails. My desktop machine is a WinNT4 PIII 667MHz. What sort of machine have you tried on? - 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
' giving problem while inserting in table.
Hi,
I am making a faq maintenance system using mysql and php.
To insert a entry in to a faq table i am using the following query:
-
INSERT INTO faq_table_netvd (id,question,
answer,netvcr,netdetector,add_date,mod_date,keyword,category,display,attach_
id)
VALUES ('0','$frm[question]',
'$frm[answer]','$frm[netvcr]','$frm[netdetector]','$frm[add_date]','$frm[mod
_date]','$frm[keyword]','$frm[category]','$frm[display]','$frm[attach_id]')
");
---
now the problem is when $frm[question] has some string like: "why i can't
eat".
i get the following error:
MySQL Error: You have an error in your SQL syntax near 't eat?' ,answer =
'Please recheck the power of your specs:)' ' at line 3.Putting a '\' before
' (e.g. \')solves my problem...but when i open the same quesion to edit it,
again i have to put backslashes where ever i find " ' " in the quesion or
answer.
Can anyone please suggest a solution to this.
thanx and regards
anil
[please ask if i havent explained the problem fully]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication problems
Subject: Re: Replication problems From: "Jared Richardson" <[EMAIL PROTECTED]> === Did you change the slave number in the second slave's config file? I forget the exact syntax, but if memory serves, there is a number in the my.ini (or my.cnf) that must be unique. If you just copied, you may have missed that. "Santiago Alba" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... > Hi All, > > I had a system replication on Linux consisting of MASTER and only one SLAVE. > I stopped replication, I did a backup on my slave and it recreated in > another machine so now I have two SLAVES connected to database MASTER. > > But I found problems. I show you the output on my database MASTER and SLAVE > who is failed: > > *** > > MASTER > > mysql> show master status; > +-+--+--+--+ > | File| Position | Binlog_do_db | Binlog_ignore_db | > +-+--+--+--+ > | log.101 | 54557| | | > +-+--+--+--+ > 1 row in set (0.00 sec) > > > SLAVE: > > mysql> show slave status; > +--+-+-+---+ > --+-+---+-+-+- -- > -++--+ > | Master_Host | Master_User | Master_Port | Connect_retry | > Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | > Last_errno | Last_error | Skip_counter | > +--+-+-+---+ > --+-+---+-+-+- -- > -++--+ > | spiderman.terravista.srv | replica | 3306| 60| > | 4 | Yes | | | 0 | > | 0| > +--+-+-+---+ > --+-+---+-+-+- -- > -++--+ > 1 row in set (0.00 sec) > > > mysql> show processlist; > ++-+---+--+-+--+ > --+--+ > | Id | User| Host | db | Command | Time | State > | Info | > ++-+---+--+-+--+ > --+--+ > | 1 | system user | none | NULL | Connect | 7729 | Waiting to > reconnect after a failed read | NULL | > | 2 | root| localhost | NULL | Sleep | 337 | > | NULL | > | 3 | root| localhost | NULL | Query | 0| NULL > | show processlist | > ++-+---+--+-+--+ > --+--+ > > In errors log: > > 020712 12:43:40 Error reading packet from server: Could not open log file > (server_errno=2) > 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log > 'FIRST' position 4 > 020712 12:44:40 Slave: reconnected to master > 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' > at position 4 > 020712 12:44:40 Error reading packet from server: Could not open log file > (server_errno=2) > 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log > 'FIRST' position 4 > 020712 12:45:40 Slave: reconnected to master > 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' > at position 4 > 020712 12:45:40 Error reading packet from server: Could not open log file > (server_errno=2) > > my.cnf: > > # The MySQL server > [mysqld] > port= 3306 > socket = /tmp/mysql.sock > skip-locking > set-variable= key_buffer=384M > 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=8 > # Try number of CPU's*2 for thread_concurrency > set-variable= thread_concurrency=8 > set-variable= myisam_sort_buffer_size=64M > #log-bin= /mysql/log/log.nnn > server-id = 3 > > master-host = spiderman.terravista.srv > master-user = replica > master-password = x > > *** > > Any suggestion? > > Thanks in advance. > > Santiago. > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > 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
Re: Replication problems
Hello! You will get problems when you set the 2nd slaves server-id the same as other one. server-ids MUST be unique...once made that mistake too... Greetings Ralf Gerald Clark wrote: > Did you copy the master.info file over too? > > > Santiago Alba wrote: > >> Hi All, >> >> I had a system replication on Linux consisting of MASTER and only one >> SLAVE. >> I stopped replication, I did a backup on my slave and it recreated in >> another machine so now I have two SLAVES connected to database MASTER. >> >> But I found problems. I show you the output on my database MASTER and >> SLAVE >> who is failed: >> >> *** >> >> MASTER >> >> mysql> show master status; >> +-+--+--+--+ >> | File| Position | Binlog_do_db | Binlog_ignore_db | >> +-+--+--+--+ >> | log.101 | 54557| | | >> +-+--+--+--+ >> 1 row in set (0.00 sec) >> >> >> SLAVE: >> >> mysql> show slave status; >> +--+-+-+---+ >> >> --+-+---+-+-+--- >> >> -++--+ >> | Master_Host | Master_User | Master_Port | Connect_retry | >> Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | >> Last_errno | Last_error | Skip_counter | >> +--+-+-+---+ >> >> --+-+---+-+-+--- >> >> -++--+ >> | spiderman.terravista.srv | replica | 3306| 60| >> | 4 | Yes | | | >> 0 | >> | 0| >> +--+-+-+---+ >> >> --+-+---+-+-+--- >> >> -++--+ >> 1 row in set (0.00 sec) >> >> >> mysql> show processlist; >> ++-+---+--+-+--+ >> >> --+--+ >> | Id | User| Host | db | Command | Time | State >> | Info | >> ++-+---+--+-+--+ >> >> --+--+ >> | 1 | system user | none | NULL | Connect | 7729 | Waiting to >> reconnect after a failed read | NULL | >> | 2 | root| localhost | NULL | Sleep | 337 | >> | NULL | >> | 3 | root| localhost | NULL | Query | 0| NULL >> | show processlist | >> ++-+---+--+-+--+ >> >> --+--+ >> >> In errors log: >> >> 020712 12:43:40 Error reading packet from server: Could not open log >> file >> (server_errno=2) >> 020712 12:44:40 Slave: Failed reading log event, reconnecting to >> retry, log >> 'FIRST' position 4 >> 020712 12:44:40 Slave: reconnected to master >> 'replica#spiderman.terravista.srv:3306',replication resumed in log >> 'FIRST' >> at position 4 >> 020712 12:44:40 Error reading packet from server: Could not open log >> file >> (server_errno=2) >> 020712 12:45:40 Slave: Failed reading log event, reconnecting to >> retry, log >> 'FIRST' position 4 >> 020712 12:45:40 Slave: reconnected to master >> 'replica#spiderman.terravista.srv:3306',replication resumed in log >> 'FIRST' >> at position 4 >> 020712 12:45:40 Error reading packet from server: Could not open log >> file >> (server_errno=2) >> >> my.cnf: >> >> # The MySQL server >> [mysqld] >> port= 3306 >> socket = /tmp/mysql.sock >> skip-locking >> set-variable= key_buffer=384M >> 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=8 >> # Try number of CPU's*2 for thread_concurrency >> set-variable= thread_concurrency=8 >> set-variable= myisam_sort_buffer_size=64M >> #log-bin= /mysql/log/log.nnn >> server-id = 3 >> >> master-host = spiderman.terravista.srv >> master-user = replica >> master-password = x >> >> *** >> >> Any suggestion? >> >> Thanks in advance. >> >> Santiago. >> >> >> >> - >> Before posting, please check: >> http://www.mysql.com/manual.php (the manual) >> http://lists.mysql.com/ (the list archive) >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> To unsubscribe, e-mail >> <[EMAIL PROTECTED]> >> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php >> >> > > > >
RE: How to know the current database
I believe it is SELECT DATABASE(); Chris sql,query -Original Message- From: Jenny Christy [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 6:41 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: How to know the current database Hello all, Is there any function(APIs) provided by myodbc driver by which i can get the name of current database. Rgds, Jenny __ Do You Yahoo!? Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com - 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. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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[2]: Numeric Type Inconsistency ?Bug [not!]
On 12 Jul 2002, at 14:25, [EMAIL PROTECTED] wrote: > When the integer comparison is performed, the min & max are parsed as > integers and anything after the first non-digit is ignored, and that > works okay-ish for the decimal point for obvious reasons, but of > course the exponential syntax doesn't truncate so well! It looks like the min and max are rounded to integers. Try "SELECT 2 BETWEEN 2.1 AND 5" or "SELECT 3 BETWEEN 2 AND 2.8". Also, the exponential numbers are converted to integers, not truncated at the "e". It's just that your 1e30 is too big for an integer. Try it with 1e18 instead. I don't know what 1e30 is being converted to, though -- apparently not the max BIGINT. [Filter fodder: SQL] -- Keith C. Ivey <[EMAIL PROTECTED]> Tobacco Documents Online http://tobaccodocuments.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: Replication problems
Did you copy the master.info file over too? Santiago Alba wrote: >Hi All, > >I had a system replication on Linux consisting of MASTER and only one SLAVE. >I stopped replication, I did a backup on my slave and it recreated in >another machine so now I have two SLAVES connected to database MASTER. > >But I found problems. I show you the output on my database MASTER and SLAVE >who is failed: > >*** > >MASTER > >mysql> show master status; >+-+--+--+--+ >| File| Position | Binlog_do_db | Binlog_ignore_db | >+-+--+--+--+ >| log.101 | 54557| | | >+-+--+--+--+ >1 row in set (0.00 sec) > > >SLAVE: > >mysql> show slave status; >+--+-+-+---+ >--+-+---+-+-+--- >-++--+ >| Master_Host | Master_User | Master_Port | Connect_retry | >Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | >Last_errno | Last_error | Skip_counter | >+--+-+-+---+ >--+-+---+-+-+--- >-++--+ >| spiderman.terravista.srv | replica | 3306| 60| >| 4 | Yes | | | 0 | >| 0| >+--+-+-+---+ >--+-+---+-+-+--- >-++--+ >1 row in set (0.00 sec) > > >mysql> show processlist; >++-+---+--+-+--+ >--+--+ >| Id | User| Host | db | Command | Time | State >| Info | >++-+---+--+-+--+ >--+--+ >| 1 | system user | none | NULL | Connect | 7729 | Waiting to >reconnect after a failed read | NULL | >| 2 | root| localhost | NULL | Sleep | 337 | >| NULL | >| 3 | root| localhost | NULL | Query | 0| NULL >| show processlist | >++-+---+--+-+--+ >--+--+ > >In errors log: > >020712 12:43:40 Error reading packet from server: Could not open log file >(server_errno=2) >020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log >'FIRST' position 4 >020712 12:44:40 Slave: reconnected to master >'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' >at position 4 >020712 12:44:40 Error reading packet from server: Could not open log file >(server_errno=2) >020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log >'FIRST' position 4 >020712 12:45:40 Slave: reconnected to master >'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' >at position 4 >020712 12:45:40 Error reading packet from server: Could not open log file >(server_errno=2) > >my.cnf: > ># The MySQL server >[mysqld] >port= 3306 >socket = /tmp/mysql.sock >skip-locking >set-variable= key_buffer=384M >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=8 ># Try number of CPU's*2 for thread_concurrency >set-variable= thread_concurrency=8 >set-variable= myisam_sort_buffer_size=64M >#log-bin= /mysql/log/log.nnn >server-id = 3 > >master-host = spiderman.terravista.srv >master-user = replica >master-password = x > >*** > >Any suggestion? > >Thanks in advance. > >Santiago. > > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >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: Problem dealing with load data
The server must be able to read the file, and since the server normally runs as the user 'mysql', it will not be able to read files sitting in root's home directory. Put the file in /tmp, and make it readable by others. Sofiane FAESSEL wrote: > hello everybody > I have a problem using mysql and the load data infile command. i have > all my data in a text file tabulated and using load data as it is > documented i receive this message > "ERROR 13: Can't get stat of '/root/essai.txt' (Errcode: 13)" > here is the command i use > mysql> load data infile '/root/essai.txt' into table essai; > i'm connected as root user essai is a table containing an integer > field and one varchar field (just for trying before uploading my > datas) using mysql import return the same problem and this on two > different machines using mandrake 8.2 and redhat 7.3 with mysql 3.23.49 > thank you for your help > > > _ > Discutez en ligne avec vos amis ! http://messenger.msn.fr > > > - > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > 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: auto-increment usage question
I verified his problem with 3.23.38 here using myisam files. The key here is that the unique index is across TWO integer fields, one of which is auto_increment. Victoria Reznichenko wrote: >Richard, >Thursday, July 11, 2002, 7:26:00 PM, you wrote: > >RF> But the default table type is already MyISAM > >If you didn't set up another default table type. > >Please, show us the output of >SHOW TABLE STATUS LIKE "your_table_name"; > >Can you create a repeatable testcase? > >And some more info.. >What version of MySQL do you use? and what OS do you use? > >>>That is the normal behavior for ISAM type files. >>>Convert them to MYISAM. >>> >>>Richard Fox wrote: >>> >Hi, > >I have a mysql table which has a run_id which together with user_id > >RF> forms > the >primary key for the table. run_id is not null and auto-increment. I > >RF> delete > >records from this table, and add new records. I want the run_id to keep >incrementing and not reuse values, but here is the actual behavior: > > When records are deleted, upon the next insert run_id will be given > >RF> a > >value which is one higher than the greatest value remaining in the > >RF> table. > If >the table is empty, run_id will be assigned '1'. > >Is there any way in which I can get run_id to keep incrementing and not > >RF> be > >based on the current highest value in the table, but on the last > >RF> assigned > >run_id even if that record has been deleted? > >If not, are there any common workarounds? > > > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT performance
What would you expect?
You ask for the whole file, and then ask
that it be sorted by two fields.
How do you expect this to happen without a temp file?
Tiago Antao wrote:
> Hi!
>
> I have a very simple query
> SELECT a, b FROM t ORDER BY a, b
> I also have an index on t(a)
> I have run ANALYZE
> Also the number of repetitions of values of a in t is very low.
>
> My view is that the execution should require little temporary space
> and little time, something like:
> for each different a(already ordered and of fast access from index) {
> get all rows (usually 3/4, max arround 300)
> sort this (little) subset
> }
>
> What is happening is that a tmp file of the size of the t is being
> generated.
>
> Is there any thing that I am not seeing? I there any way to change
> this behaviour?
>
> Thanks,
> Tiago
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
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]: Numeric Type Inconsistency ?Bug [not!]
>>mysql> select 798 between 75.2 and 999.1; >>++ >>| 1 | >>++ >> >>mysql> select 798 between 75.2 and 1e30; >>+---+ >>| 0 |<<< Really? >>+---+ >> >>mysql> select 798.1 between 75.2 and 1e30; >>+-+ >>| 1 | >>+-+ OS> tested this against 3.23.45; it's reproducible. The problem OS> appears if the upper range exceeds 0.92233e19 (signed bigint). But OS> the manual states that this is NOT a bug: OS> expr BETWEEN min AND max OS> ... OS> - If expr is an integer expression, an integer comparision is done. OS> - Otherwise, a floating-point (real) comparision is done. OS> ... Hi Oliver, Thanks for your response. I am sorry I did not check the doc's as it struck me as _so_ bizarre that the inconsistency _appeared_ not just to be an int-vs-real problem. If I understand correctly: - When the integer comparison is performed, the min & max are parsed as integers and anything after the first non-digit is ignored, and that works okay-ish for the decimal point for obvious reasons, but of course the exponential syntax doesn't truncate so well! === I'm musing over the implications of this though. I can see that forcing a parser error when the min/max don't match the expected comparison type would have disadvantages, even if it was appropriate just because I had a problem one day ;) I'll go and read the manual in more depth around the S6.2 et seq to see if there's discussion there about it .. if not I'll follow-up with more thoughts :) -- Many thanks, James. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Permissions problem
Since -p="" is not valid syntax, it should not work. There is no equal sign after the -p, and no space between the p and the password which needs no quotes. If you have no password leave out the -p option. Derek Fountain wrote: >Hi, > >I have a server running 3.23.41 on Redhat 7.1. The problem is that I can't >access a database as a local user, but I can as a remote user. That is, on >the local machine I get this: > >>/usr/bin/mysqladmin -u tpdb -p="" create fred >> >/usr/bin/mysqladmin: connect to server at 'localhost' failed >error: 'Access denied for user: 'tpdb@localhost' (Using password: YES)' > >whereas if I access the machine from another Linux box: > >>/usr/bin/mysqladmin -h lowenbrau -u tpdb -p="" create fred >> > >it works. If I do it as the MySQL root user on the local machine that works >OK. I assume something's wrong with my local tpdb user. > >The permissions are set as follows (after a lot of faffing about - I'm not >suggesting these are optimal settings, or the ones I should have): > >localhost root "" >lowenbrau.my.domain root "" >lowenbrau.my.domain tpdb "" >% tpdb "" >localhost tpdb "" > >I've reloaded (and actually restarted) the server, but it won't play. There's >nothing in the db table except a couple of "test" database entries. > >I'm reading up on security issues, but I have someone waiting on this, so if >anyone could give me a pointer as to what I've done wrong, I'd much >appreciate it. > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Problem dealing with load data
Sofiane, Friday, July 12, 2002, 2:07:00 PM, you wrote: SF> hello everybody SF> I have a problem using mysql and the load data infile command. i have all my SF> data in a text file tabulated and using load data as it is documented i SF> receive this message SF> "ERROR 13: Can't get stat of '/root/essai.txt' (Errcode: 13)" SF> here is the command i use mysql>> load data infile '/root/essai.txt' into table essai; SF> i'm connected as root user essai is a table containing an integer field and SF> one varchar field (just for trying before uploading my datas) using mysql SF> import return the same problem and this on two different machines using SF> mandrake 8.2 and redhat 7.3 with mysql 3.23.49 SF> thank you for your help Error code 13: Permission denied Check permissions on your file. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Processing Time
Asma, Friday, July 12, 2002, 2:15:42 PM, you wrote: AA> does it means when a column has an index, the records inside will no longer AA> duplicate? Nope. It will be true if you create _unique_ index or primary key. If you create 'simple' index column can contain duplicated records. http://www.mysql.com/doc/C/R/CREATE_INDEX.html http://www.mysql.com/doc/I/n/Indexes.html http://www.mysql.com/doc/M/y/MySQL_indexes.html AA> if so, i can't put an index to that column because that record need to be AA> duplicate -- 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
Replication problems
Hi All, I had a system replication on Linux consisting of MASTER and only one SLAVE. I stopped replication, I did a backup on my slave and it recreated in another machine so now I have two SLAVES connected to database MASTER. But I found problems. I show you the output on my database MASTER and SLAVE who is failed: *** MASTER mysql> show master status; +-+--+--+--+ | File| Position | Binlog_do_db | Binlog_ignore_db | +-+--+--+--+ | log.101 | 54557| | | +-+--+--+--+ 1 row in set (0.00 sec) SLAVE: mysql> show slave status; +--+-+-+---+ --+-+---+-+-+--- -++--+ | Master_Host | Master_User | Master_Port | Connect_retry | Log_File | Pos | Slave_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | +--+-+-+---+ --+-+---+-+-+--- -++--+ | spiderman.terravista.srv | replica | 3306| 60| | 4 | Yes | | | 0 | | 0| +--+-+-+---+ --+-+---+-+-+--- -++--+ 1 row in set (0.00 sec) mysql> show processlist; ++-+---+--+-+--+ --+--+ | Id | User| Host | db | Command | Time | State | Info | ++-+---+--+-+--+ --+--+ | 1 | system user | none | NULL | Connect | 7729 | Waiting to reconnect after a failed read | NULL | | 2 | root| localhost | NULL | Sleep | 337 | | NULL | | 3 | root| localhost | NULL | Query | 0| NULL | show processlist | ++-+---+--+-+--+ --+--+ In errors log: 020712 12:43:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:44:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:44:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:44:40 Error reading packet from server: Could not open log file (server_errno=2) 020712 12:45:40 Slave: Failed reading log event, reconnecting to retry, log 'FIRST' position 4 020712 12:45:40 Slave: reconnected to master 'replica#spiderman.terravista.srv:3306',replication resumed in log 'FIRST' at position 4 020712 12:45:40 Error reading packet from server: Could not open log file (server_errno=2) my.cnf: # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=384M 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=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M #log-bin= /mysql/log/log.nnn server-id = 3 master-host = spiderman.terravista.srv master-user = replica master-password = x *** Any suggestion? Thanks in advance. Santiago. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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:Numeric Type Inconsistency ?Bug
[EMAIL PROTECTED] wrote on 12.07.2002 12:58:00: > >MySQL, > >I'd have expected all three of the following to give the same result, >but even if there's a type-conversion problem from INT, then I >certainly wouldn't expect it to be different for 798.1 and 1e30 for >example... > >mysql> select 798 between 75.2 and 999.1; >++ >| 798 between 75.2 and 999.1 | >++ >| 1 | >++ >1 row in set (0.01 sec) > >mysql> select 798 between 75.2 and 1e30; >+---+ >| 798 between 75.2 and 1e30 | >+---+ >| 0 |<<< Really? >+---+ >1 row in set (0.00 sec) > >mysql> select 798.1 between 75.2 and 1e30; >+-+ >| 798.1 between 75.2 and 1e30 | >+-+ >| 1 | >+-+ >1 row in set (0.00 sec) > > >Any comments? > Hi James, tested this against 3.23.45; it's reproducible. The problem appears if the upper range exceeds 0.92233e19 (signed bigint). But the manual states that this is NOT a bug: expr BETWEEN min AND max ... - If expr is an integer expression, an integer comparision is done. - Otherwise, a floating-point (real) comparision is done. ... Bye Oliver -- Good programming is 40% experience, 30% skill, 20% RTFM, 10% caffeine, and 5% attention to detail. Oliver Six, CEO CAHOS GmbH, Cimbernstr. 51, Germany 81377 Muenchen Phone +49 89 71 01 93 41, Fax +49 89 71 01 93 42 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Please advice...
Hi, For first i wish to apologize for using the list with some questions which don't have to much in common with the subject of this list.But i hope you understand me. Me and the organization for which i work, are interested for buying some servers.On this servers machine we wish to install like OS , LINUX and SCO UNIX and like RDBMS, MySQL and INFORMIX. Our problem is that we don't know yet, what should buying from this 3 next servers machine: - COMPAQ PROLIANT DL 580 (Generation 2) 2P EURO - INTEL SRSH4 - IBM x255 All have the next base configuration: CPU XEON 1.4 GB (512 cache on L3) x 4 pcs. HDD SCSI 15k rpm, 36 GB x 4 pcs. 2 GB ECC SDRAM DRIMM I have some doubt about IBM x255...it's too chip (15.000 USD w/o VAT) in comparison with COMPAQ(21.000 USD w/o VAT) or INTEL(18.000 w/o VAT).From description it's seems to be similarly with COMPAQ and INTEL(except the ChipKill). Another doubt for me (apropos by IBM) it's next: -the CPU's work at 400 Mhz and RAM work ... only at 200 Mhz. I wish to know if is some WEB site(on google i searched already...i found some MMB2 test...not much relevance) where is described, comparison and benchmark...or if is someone which have experience with IBM x255 or INTEL SRSH4. Any advice it's really appreciated. Best regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [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: Next 4.0 version
Matt, - Original Message - From: "Matt Parlane" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, July 12, 2002 8:08 AM Subject: Next 4.0 version > Hi all... > > Firstly, would I be correct in saying that there have been a whole heap of > bugs fixed in the next version of the 3.23 branch (3.23.52) which is due to > be released in the next week or two? several bugs in InnoDB are fixed in 3.23.52, but they are rare, mostly discovered with my own synthetic stress tests on InnoDB. > Secondly, would I be correct in saying that those patches haven't been > applied to the 4.0 branch, and won't be applied until the next 4.0 version > comes out? Yes, until recently the stable 3.23 versions have had a faster release turnover and consequently have got the bug fixes sooner. > The next 4.0 will probably be 4.0.3 - I doubt we're going to see 4.1 any > time soon. My guess is 4.0.3 will be released August 31, 2002. Since in the future the biggest changes are put to the 4.1 branch we can release new versions of 4.0 at shorter intervals. They will be mostly bug fix releases, and will make 4.0 eventually a stable release. > Are there any MySQL developers listening? I noticed this on the InnoDB.com > site - on http://www.innodb.com/ibman.html , down near the bottom (section > 16 - version history), it says that 4.0.2 is "essentially the same as > 3.23.51", but under 3.23.52, it lists a whole heap of InnoDB changes - this > would indicate to me that the version I'm running (4.0.2) does not have > those fixes applied. You are right. InnoDB in the 3.23 and 4.0 branches is the same. Only the date of release determines how fresh the InnoDB version in it is. > Any thoughts? > > Thanks, > > Matt Parlane > Zevi Interactive > [EMAIL PROTECTED] 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Remote entry
Hello all, Yeah i have worked with this replication to the core but i faced some minor problem with that as i have to do it in two way hence i dropped that idea and hunting for some other way to do that so i put those words like remote entry any way thanks a lot for your response - Actually i am doing some work related to redundancy so i am having two machines and at a time one is active and another is passive so when the active state the mysql will be keep on updating and the other passive server will be continuously updating its mysql with the active server at some time when the active server goes down that related entry will be not sent to the remote i do it as two-way replication for that i am facing some problem hope u r getting me thanx Charitha, Friday, July 12, 2002, 7:49:03 AM, you wrote: C> Can i make mysql to put enteries in two different machines C> If there is an entry made in mysql in local machine, is there any options C> so that the same entry is made in the remote machine mysql db C> If there is any option pls. let me know C> Thanx in advance If I fully appreciate, you want all changes with data on your local box will be applied with data on your remote box? If so, take a look at replication: http://www.mysql.com/doc/R/e/Replication.html -- Regards, Charitha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Serious doubt about InnoDB isolation level
Micheal,
- Original Message -
From: "micheal" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Friday, July 12, 2002 1:35 AM
Subject: Serious doubt about InnoDB isolation level
> Hi MySQL experts there,
> Here is the copy of the mail I sent to the author.
> ...
> 1. According to the document, InnoDB only support
> repeatable read and serializable.Why it does not
> support read committed? Major databases like
> Oracle,DB2,MS-SQL all support read-committed and use
> it as THE default isolation level.
> While in MySQL, I have no way to read committed data
> from another transaction without a new transaction or
> explict locking...which is either infeasible or has
> performance implications.
in all databases, both multiversioning and non-multiversioning, you need use
a locking read (in Oracle SELECT ... FOR UPDATE, InnoDB also has SELECT ...
LOCK IN SHARE MODE) to read the latest committed data. For example, Oracle
without the FOR UPDATE clause reads a snapshot which is taken at the start
of that SELECT, and nothing guarantees that the snapshot contains the latest
committed data when the query returns.
The default behavior of InnoDB with non-locking SELECTs is that they read
the snapshot taken at the start of the FIRST non-locking SELECT issued by
the current transaction. That behavior was chosen because if you have
several non-locking SELECTs inside one transaction, then they all read the
same snapshot and thus are consistent with respect to each other. Thus a
read-only non-locking transaction is serialized with respect to all other
transactions.
I could add yet another clause to InnoDB: SELECT ... READ LATEST SNAPSHOT.
That would read the snapshot taken at the start of this SELECT, not the
snapshot taken at the start of the first SELECT of the transaction. Thus in
a transaction containing several non-locking SELECTs, this would return a
fresher snapshot than the current InnoDB behavior.
> 2. InnoDB's default isolation level is "repeatable
> read", I seriously doubt that's true. IMHO, it is
> "fake" repeatable read, because although you get
> the same data every time, the data could be
> changed and committed by another transaction without
> your notice. I have thought lost update should never
> happen in "repeatable read" mode, right!?
You are right. The isolation levels of Oracle, PostgreSQL, or InnoDB should
rather be called MULTIVERSIONING. But that term is not available in the
SQL-92 standard, which is a pity, because the scale tailored for
non-multiversioning databases poorly describes this. I have pasted below the
relevant section from a SQL-92 standard draft.
..
The isolation level specifies the kind of phenomena that can occur
during the execution of concurrent SQL-transactions. The following
phenomena are possible:
1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
transaction T2 then reads that row before T1 performs a COMMIT.
If T1 then performs a ROLLBACK, T2 will have read a row that was
never committed and that may thus be considered to have never
existed.
2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-
transaction T2 then modifies or deletes that row and performs
a COMMIT. If T1 then attempts to reread the row, it may receive
the modified value or discover that the row has been deleted.
3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N
that satisfy some . SQL-transaction T2 then
executes SQL-statements that generate one or more rows that
satisfy the used by SQL-transaction T1. If
SQL-transaction T1 then repeats the initial read with the same
, it obtains a different collection of rows.
The four isolation levels guarantee that each SQL-transaction will
be executed completely or not at all, and that no updates will be
lost. The isolation levels are different with respect to phenomena
P1, P2, and P3. Table 9, "SQL-transaction isolation levels and the
three phenomena" specifies the phenomena that are possible and not
possible for a given isolation level.
68 Database Language SQL
X3H2-92-154/DBL CBR-002
4.28 SQL-transactions
__Table_9-SQL-transaction_isolation_levels_and_the_three_phenomena_
_Level__P1__P2___P3
| READ UNCOMMITTED | Possib|e Possib|e Possible|
| | || |
| READ COMMITTED | Not | Possibl| Possible |
Possible
| REPEATABLE READ | Not | Not| Possible |
Re: install mysql (update)
At 21:30 11/7/2002 -0300, [EMAIL PROTECTED] wrote: Hi, >I am trying to update my version and I am not getting. My current version is >3.23.49-nt. I am using MySql in Windows 2000. > >When I try to install it happens the mistake -103: > >An error occurred during the moves date process: -103 >Component: servers >File group: >File: Shutdown the current server before to begin the new install. Regards, -- For technical support contracts, visit https//order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ São Paulo - Brazil <___/ 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: Processing Time
does it means when a column has an index, the records inside will no longer duplicate? if so, i can't put an index to that column because that record need to be duplicate At 05:50 AM 7/12/02 -0500, Mark Matthews wrote: >-BEGIN PGP SIGNED MESSAGE- >Hash: SHA1 > >Asma Aripin wrote: >| >| >| >| -- >| I want to select 1 record from 200,000 rows of records. I use this SQL >| statement: >| >| SELECT password FROM TableUser WHERE username='jack00' >| >| It takes me about 20 seconds just to view the password. >| >| Is there any other way to decrease the time? (In about less then 1 second) >| -- >| Thanks & Regards, >| Asma Binti Aripin >| R&D Department - [TelCo:CELCOM] >| Messaging Technologies Sdn Bhd >| Tel. (O): 0376651101 ext. 121 >| Tel. (H): 0132316056 >| Email : [EMAIL PROTECTED] >| >| -- > >Does the "username" column have an index? See >http://www.mysql.com/doc/I/n/Indexes.html > > -Mark >-BEGIN PGP SIGNATURE- >Version: GnuPG v1.0.6-2 (MingW32) >Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > >iD8DBQE9LrSFlyjUJM+7nP4RAqG3AJ9AIsBM21xf1uY0T6SghH5Uh7Ep1ACdEuxm >rVSn0yxp6hd9P0YR3cPqmcA= >=LDRV >-END PGP SIGNATURE- -- Thanks & Regards, Asma Binti Aripin R&D Department - [TelCo:CELCOM] Messaging Technologies Sdn Bhd Tel. (O): 0376651101 ext. 121 Tel. (H): 0132316056 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
Problem dealing with load data
hello everybody I have a problem using mysql and the load data infile command. i have all my data in a text file tabulated and using load data as it is documented i receive this message "ERROR 13: Can't get stat of '/root/essai.txt' (Errcode: 13)" here is the command i use mysql> load data infile '/root/essai.txt' into table essai; i'm connected as root user essai is a table containing an integer field and one varchar field (just for trying before uploading my datas) using mysql import return the same problem and this on two different machines using mandrake 8.2 and redhat 7.3 with mysql 3.23.49 thank you for your help _ Discutez en ligne avec vos amis ! http://messenger.msn.fr - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to Find Admin info and related links
amolsonaikar, Friday, July 12, 2002, 9:46:45 AM, you wrote: a> I am an oracle dba and will be shifting to mysql. I have the documentation(html) downloaded with me and mysql on my PC (on linux) however I need to find out where exactly I ll get the info related a> to mysql database administration? Is it as difficult as oracle? The html downloaded is quite huge. I want to concentrate on specific info.like backups, distributed db, and other that are there a> with oracle. Can you help me out I don't know how difficult database administration in Oracle :) As to MySQL check section 4 Database Administration in the MySQL manual. You can find such subsections there: 4.1 Configuring MySQL 4.2 General Security Issues and the MySQL Access Privilege System 4.3 MySQL User Account Management 4.4 Disaster Prevention and Recovery 4.5 Database Administration Language Reference 4.6 MySQL Localisation and International Usage 4.7 MySQL Server-Side Scripts and Utilities 4.8 MySQL Client-Side Scripts and Utilities 4.9 The MySQL Log Files 4.10 Replication in MySQL -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: image data
William, Friday, July 12, 2002, 12:45:02 AM, you wrote: WM> I am new to MySQL and I was trying to find out how to setup a record WM> with a datatype as a image jpg etc... Take a look at BLOB column type: http://www.mysql.com/doc/B/L/BLOB.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: GUI Problem
Robert, Friday, July 12, 2002, 3:56:50 AM, you wrote: RA> Im running windows 98, Apache 2.03, MySQL 3.23.51 and Php 4.0 RA> I can user all mysql's functions using php to interact with the MySQL sever RA> but am having problems using the MySQL GUI. I can connect to the database RA> however when i try to use functions such as create database it comes back RA> with the error RA> Error in database function: Access denied for user: 'Robert@localhost' RA> (Using password: YES) 1045 RA> As you can see iam testing locally Seems, you have not enough privileges to create database. You must have Create_priv on the global level to create database. -- 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: help with MySQL SELECT statement
Kent,
Thursday, July 11, 2002, 9:22:52 PM, you wrote:
KH> I would use this query:
KH> SELECT DISTINCT SUBSTRING(email,LOCATE('@', email) ) FROM AddressList ;
So?
Syntax of your SQL statement is correct.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: install mysql (update)
I am trying to update my version and I am not getting. My current version is t> 3.23.49-nt. I am using MySql in Windows 2000. t> When I try to install it happens the mistake -103: t> An error occurred during the moves date process: -103 t> Component: servers t> File group: t> File: Did you removed your old version before or you installed new MySQL server in another location? If you removed your old version before installation and then got this error, reboot Windows, and try to install again. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: auto-increment usage question
Richard, Thursday, July 11, 2002, 7:26:00 PM, you wrote: RF> But the default table type is already MyISAM If you didn't set up another default table type. Please, show us the output of SHOW TABLE STATUS LIKE "your_table_name"; Can you create a repeatable testcase? And some more info.. What version of MySQL do you use? and what OS do you use? >> That is the normal behavior for ISAM type files. >> Convert them to MYISAM. >> >> Richard Fox wrote: >> >> >>Hi, >> >> >> >>I have a mysql table which has a run_id which together with user_id RF> forms >> >> >> >the >> > >> >>primary key for the table. run_id is not null and auto-increment. I RF> delete >> >>records from this table, and add new records. I want the run_id to keep >> >>incrementing and not reuse values, but here is the actual behavior: >> >> >> >>When records are deleted, upon the next insert run_id will be given RF> a >> >>value which is one higher than the greatest value remaining in the RF> table. >> >> >> >If >> > >> >>the table is empty, run_id will be assigned '1'. >> >> >> >>Is there any way in which I can get run_id to keep incrementing and not RF> be >> >>based on the current highest value in the table, but on the last RF> assigned >> >>run_id even if that record has been deleted? >> >> >> >>If not, are there any common workarounds? -- 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: Remote entry
Charitha, Friday, July 12, 2002, 7:49:03 AM, you wrote: C> Can i make mysql to put enteries in two different machines C> If there is an entry made in mysql in local machine, is there any options C> so that the same entry is made in the remote machine mysql db C> If there is any option pls. let me know C> Thanx in advance If I fully appreciate, you want all changes with data on your local box will be applied with data on your remote box? If so, take a look at replication: http://www.mysql.com/doc/R/e/Replication.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bug with AUTO_INCREMENT
Steve, Wednesday, July 10, 2002, 11:12:48 AM, you wrote: SH> Continuing my troubles with AUTO_INCREMENT (which nobody has yet SH> responded to), I've now ruled the Perl DBI out of the equation and SH> reproduced the same problem using the "mysql.exe" command-line tool. SH> Hopefully somebody will be tempted to help this time now that there is SH> no Perl involved to put people off? SH> The bug can be reproduced as follows: SH> Create the following simple database containing just one BDB table: SH> CREATE DATABASE test; SH> CREATE TABLE foo ( SH>id INT NOT NULL AUTO_INCREMENT, SH>num INT, SH>str VARCHAR(10), SH>PRIMARY KEY (id) SH> ) TYPE=BDB; SH> Now start two "mysql.exe" sessions running with the "-f" flag to ignore SH> errors. SH> Set each one into "no auto-commit" mode with: SH> SET AUTOCOMMIT=0; SH> and then feed each of them the following two lines 50,000 times each: SH> INSERT INTO foo (num, str) VALUES (1, 'a'); SH> COMMIT; [skip] SH> When I inspect the output collected in these log files I find, not SH> unexpectedly, that there are many "deadlock" errors (which I'm quite SH> comfortable with, and can handle in my real software by simply retrying SH> the transaction), but that there are also a few of the following error: SH> Duplicate entry '1' for key 1 SH> Surely this is a bug isn't it? SH> The transaction that is being performed 50,000 times each by the two SH> "mysql.exe" sessions doesn't specify any value for the AUTO_INCREMENT SH> column "id" - it just leaves it up to the database to choose the "next" SH> value. SH> This error suggests that the database has wrongly chosen the value '1', SH> and then (rightly) complains that it is already used! SH> I would really appreciate a response on this one as I'm now at the end SH> of my tether trying to work out what is wrong. Steve, I tested your example with PHP script, but I didn't get any Duplicate entry error ... (I tested with BDB tables and SET AUTOCOMMIT=0). -- 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
Numeric Type Inconsistency ?Bug
MySQL, I'd have expected all three of the following to give the same result, but even if there's a type-conversion problem from INT, then I certainly wouldn't expect it to be different for 798.1 and 1e30 for example... mysql> select 798 between 75.2 and 999.1; ++ | 798 between 75.2 and 999.1 | ++ | 1 | ++ 1 row in set (0.01 sec) mysql> select 798 between 75.2 and 1e30; +---+ | 798 between 75.2 and 1e30 | +---+ | 0 |<<< Really? +---+ 1 row in set (0.00 sec) mysql> select 798.1 between 75.2 and 1e30; +-+ | 798.1 between 75.2 and 1e30 | +-+ | 1 | +-+ 1 row in set (0.00 sec) Any comments? -- Best regards, James. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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: Remote entry
The feature is called replication, there are options to restrict replication down to table level. So I guess looking tinto the doc might help. Charitha wrote: >Hello all, > >Can i make mysql to put enteries in two different machines >If there is an entry made in mysql in local machine, is there any options >so that the same entry is made in the remote machine mysql db >If there is any option pls. let me know >Thanx in advance > > > -- Ralf Narozny SPLENDID Internet GmbH & Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Processing Time
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Asma Aripin wrote: | | | | -- | I want to select 1 record from 200,000 rows of records. I use this SQL | statement: | | SELECT password FROM TableUser WHERE username='jack00' | | It takes me about 20 seconds just to view the password. | | Is there any other way to decrease the time? (In about less then 1 second) | -- | Thanks & Regards, | Asma Binti Aripin | R&D Department - [TelCo:CELCOM] | Messaging Technologies Sdn Bhd | Tel. (O): 0376651101 ext. 121 | Tel. (H): 0132316056 | Email : [EMAIL PROTECTED] | | -- Does the "username" column have an index? See http://www.mysql.com/doc/I/n/Indexes.html -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6-2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE9LrSFlyjUJM+7nP4RAqG3AJ9AIsBM21xf1uY0T6SghH5Uh7Ep1ACdEuxm rVSn0yxp6hd9P0YR3cPqmcA= =LDRV -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help - query suggestion needed - interesting case
How about: SELECT e1.event_id, e1.time, count(distinct e2.time) FROM events e1 LEFT JOIN events e2 USING (event_id) GROUP BY e1.event_id, e1.time, e2.event_id HAVING count(e2.time_id) > 1 ; I don't know if this one does it too (might work in strange MySQL SQL ;-) ) SELECT event_id, time FROM events GROUP BY event_id, time HAVING count(time) > 1 ; Mihail Manolov wrote: >:) Is this some sort of a joke? > >I am grouping using event_id, which makes your query useless because it will >return just the first time row per each event_id. > >Thanks anyway. I may have to use second query... :-( > > >Mihail > > >- Original Message - >From: "Bhavin Vyas" <[EMAIL PROTECTED]> >To: "Mihail Manolov" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> >Sent: Thursday, July 11, 2002 10:51 PM >Subject: Re: Help - query suggestion needed - interesting case > > > > >>How about: >> >> SELECT >> event_id, time, >> count(DISTINCT time) AS Ranges >> FROM >> events >> GROUP BY >> event_id HAVING Ranges > 1 >> >> >>- Original Message - >>From: "Mihail Manolov" <[EMAIL PROTECTED]> >>To: <[EMAIL PROTECTED]> >>Sent: Thursday, July 11, 2002 2:58 PM >>Subject: Help - query suggestion needed - interesting case >> >> >> >> >>>Greetings, >>> >>>I am stuck with this problem: >>> >>>I have the following table: >>> >>>event_id time >>>1002000-10-23 >>>1002000-10-23 >>>1012000-10-24 >>>1012000-10-25 >>> >>>I need to know all event_id's that have multiple times + time columns. >>> >>> >Is > > >>it >> >> >>>possible to get that result in just one query? >>>The result should be something like this: >>> >>>event_id time >>>1012000-10-24 >>>1012000-10-25 >>> >>> >>>I managed to get all event_id's that have multiple times, but I don't >>> >>> >know > > >>>how to get the time column in the same query. >>>Here is my current query: >>> >>>SELECT >>>event_id, >>>count(DISTINCT time) AS Ranges >>>FROM >>>events >>>GROUP BY >>>event_id HAVING Ranges > 1 >>> >>>Please help me to find a single query that will return the time column >>> >>> >as > > >>>well. >>> >>> >>>Mihail >>> >>> > > > >- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > -- Ralf Narozny SPLENDID Internet GmbH & Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: SELECT performance
Tim Ward Brett Ward Limited - www.brettward.co.uk > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: 12 July 2002 11:17 > To: Tim Ward > Subject: Re: RE: SELECT performance > > > 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 > > 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: > > > Is there any thing that I am not seeing? I there any way to > > change this > > behaviour? > > Adding an index on (a,b), rather than just having one on a, > should change > the behaviour. > > Tim Ward > Brett Ward Limited - www.brettward.co.uk > > > > --- > On August 5th ip.access will be moving to: > > CPC1 Telephone: +44 (0) 1223 219000 > Capital Park Fax: +44 (0) 1223 219099 > Fulbourn > Cambridge CB1 5XE > United Kingdom > --- > This e-mail and the information it contains are > confidential and may be privileged. If you have > received this e-mail in error please notify us > immediately. You should not copy it for any purpose, or > disclose its contents to any other person. All emails > to and from ip.access may be monitored and stored for > audit and other legal reasons. > --- > > > --- On August 5th ip.access will be moving to: CPC1 Telephone: +44 (0) 1223 219000 Capital Park Fax: +44 (0) 1223 219099 Fulbourn Cambridge CB1 5XE United Kingdom --- This e-mail and the information it contains are confidential and may be privileged. If you have received this e-mail in error please notify us immediately. You should not copy it for any purpose, or disclose its contents to any other person. All emails to and from ip.access may be monitored and stored for audit and other legal reasons. --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT performance
Hi!
I have a very simple query
SELECT a, b FROM t ORDER BY a, b
I also have an index on t(a)
I have run ANALYZE
Also the number of repetitions of values of a in t is very low.
My view is that the execution should require little temporary space and
little time, something like:
for each different a(already ordered and of fast access from index) {
get all rows (usually 3/4, max arround 300)
sort this (little) subset
}
What is happening is that a tmp file of the size of the t is being
generated.
Is there any thing that I am not seeing? I there any way to change this
behaviour?
Thanks,
Tiago
--
Tiago Antão - RIPE NCC
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Goodday
I wonder who to send this mail to. So I decided to send it to the list may be someone responsible for such will see it. There is someone spreading spam through the MySql List. I received this attached e-mail today If you check correctly it was originally addressed to the list. And that is how I ended up with a copy. Please someone do the necessary to prevent spam being sent through the list. I will highly appreciate if it is announced through the list that something is being done. Kind regards Harrison - Original Message - From: "Oluwafunmi Gabriel" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, November 14, 2001 13:08 Subject: Goodday > Attn: mysql , > > My name is Mr. Oluwafunmi G, the manager, credit > and foreign bills of Ecobank Plc. I am writing in > respect of a foreign customer of my bank with account > number 14-255-2004/utb/t who perished in a plane crash > [Korean Air Flight 801] with the whole passengers > aboard on August 6, 1997. > Since the demise of this our customer, I personally > has watched with keen interest to see the next of kin > but all has proved abortive as no one has come to > claim his funds of usd.20.5 m, [twenty million five > hundred thousand united states dollars] which has been > with my branch for a very long time. > On this note, I decided to seek for whom his name > shall be used as the next of kin as no one has come up > to be the next of kin. And the banking ethics here > does not allow such money to stay more than four > years, because money will be recalled to the bank > treasury as unclaimed after this period. In view of > this I got your contact through a trade journal after > realizing that your name and country is similar to the > deceased. I will give you 25% of the total. > Upon the receipt of your response, I will send you by > fax or e-mail the application, bank's fax number and > the next step to take. I will not fail to bring to > your notice that this business is hitch free and that > you should not entertain any fear as all modalities > for fund transfer can be finalized within five banking > days, after you apply to the bank as a relation to the > deceased. > When you receive this letter. Kindly send me an e-mail > signifying > Your decision including your private Tel/Fax numbers > for quick communication. > Respectfully submitted, > Oluwafunmi G. > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > - This email and any files transmitted with it are confidential and intended solely for the use of the addressee. If you have received this email in error you may not disclose, copy, distribute or act upon its contents without the authors prior written consent. Please can you notify the system manager at [EMAIL PROTECTED] immediately and delete this email. - Any views or opinions expressed are those of the author and do not necessarily represent those of Somak Travel Limited, Kenya. - This footnote also confirms that this email message has been swept for the presence of computer viruses. No responsibility can be accepted in the event that a virus is contained within it or any attachments. - SOMAK TRAVEL LTD - KENYA NAIROBI, PO BOX 48495. Tel: +(2542) 557832 Fax:+(2542) 535175 MOMBASA, PO BOX 90738. Tel: +(25411)486197 Fax:+(25411)487324 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--skip-locking options!
http://www.mysql.com/doc/C/r/Crash_recovery.html Is this very reliable? Coz im planning to remove the --skip-locking options. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--skip-locking options!
http://www.mysql.com/doc/C/r/Crash_recovery.html Is this very reliable? Coz im planning to remove the --skip-locking options. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Re: Re: UNIQUE on blob
> Hi everybody, > my humble excuse to all I bothered with that ... mysql type mediumblob is working > perfectly, but there's another column in the tabel of type varchar > containing mostly the same values. So that's it > Stefan > > > P. S: I do the select first because I need the ID anyway. > > > > > Hi! > > > > > > First of all, MEDIUMBLOB type is case-sensitive. > > > I checked this sequence and everything worked as > > > expected. I think you have a mistake in your table. > > > What does "SHOW CREATE TABLE table" give? > > > Second, there is no needs to check that the value is already > > > presents using "SELECT" just to avoid an attempt of double > > > key value inserting. It is safe to insert without selecting. > > > After inserting you have just to check that really happened, > > > either record was inserted or error happened. > > > Why do you want to do iy - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) 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 rpm from RH 7.3
En réponse à Jan Kudrman <[EMAIL PROTECTED]>: I've found it on the online documentation of MySQL.org with comments of user. Since I've replaced the RP of RH 7.3 by RPM of MySQL.org, I don't have any problem Regards Julien Sobrier > I have receive an answer that there is a bug in MySQL rpm packages in > Red > Hat 7.3 distribution. > > I have tried to find any information about it but I didn't find > anything > about it. There is no errata on Red Hat web site, and also other > sources > hasn't any information about the bug in Red Hat 7.3 MySQL rpm > packages. > > Do you have some info about a MySQL rpm bug in Red Had 7.3? > > Thank you, > Jan > > - Original Message - > > > Hi All, > > > > I am running MySQL 3.23.49 on Red Hat Linux 7.3 (installed from rpm > included > > in RH distribution). I am using ext3 file system. In my my.cfg is > > "skip-locking". > > > > The system is Intel i810, Intel Celeron 433 MHz, 64 MB RAM. It is > used > only > > as a database server which is accessable only from our web (apache, > php). > > > > I would like to do some operation automatickly and periodicly (with > a > > crond): > > - check all databases / tables > > - optimize all databases / tables > > > > According to documentation > > (http://www.mysql.com/doc/C/o/Command-line_options.html) I can't use > > myisamchk, as long as mysqld is running. I have try somethong like > this: > > mysqld stop > > myisamchk -swr /path/to/datadir/*/*.MYI > > myisamchk -swm /path/to/datadir/*/*.MYI > > mysql start > > > > But I don't want to stop sql server every time. > > Could you help me how to solve this issue? > > Has somebody experience with MySQL on RedHat 7.3 with ext3? > > > > Thanks, > > Jan > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > 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
Bug in rpm from RH 7.3
I have receive an answer that there is a bug in MySQL rpm packages in Red Hat 7.3 distribution. I have tried to find any information about it but I didn't find anything about it. There is no errata on Red Hat web site, and also other sources hasn't any information about the bug in Red Hat 7.3 MySQL rpm packages. Do you have some info about a MySQL rpm bug in Red Had 7.3? Thank you, Jan - Original Message - > Hi All, > > I am running MySQL 3.23.49 on Red Hat Linux 7.3 (installed from rpm included > in RH distribution). I am using ext3 file system. In my my.cfg is > "skip-locking". > > The system is Intel i810, Intel Celeron 433 MHz, 64 MB RAM. It is used only > as a database server which is accessable only from our web (apache, php). > > I would like to do some operation automatickly and periodicly (with a > crond): > - check all databases / tables > - optimize all databases / tables > > According to documentation > (http://www.mysql.com/doc/C/o/Command-line_options.html) I can't use > myisamchk, as long as mysqld is running. I have try somethong like this: > mysqld stop > myisamchk -swr /path/to/datadir/*/*.MYI > myisamchk -swm /path/to/datadir/*/*.MYI > mysql start > > But I don't want to stop sql server every time. > Could you help me how to solve this issue? > Has somebody experience with MySQL on RedHat 7.3 with ext3? > > Thanks, > Jan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Permissions problem
Hi, I have a server running 3.23.41 on Redhat 7.1. The problem is that I can't access a database as a local user, but I can as a remote user. That is, on the local machine I get this: >/usr/bin/mysqladmin -u tpdb -p="" create fred /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'tpdb@localhost' (Using password: YES)' whereas if I access the machine from another Linux box: >/usr/bin/mysqladmin -h lowenbrau -u tpdb -p="" create fred it works. If I do it as the MySQL root user on the local machine that works OK. I assume something's wrong with my local tpdb user. The permissions are set as follows (after a lot of faffing about - I'm not suggesting these are optimal settings, or the ones I should have): localhost root "" lowenbrau.my.domain root "" lowenbrau.my.domain tpdb "" % tpdb "" localhost tpdb "" I've reloaded (and actually restarted) the server, but it won't play. There's nothing in the db table except a couple of "test" database entries. I'm reading up on security issues, but I have someone waiting on this, so if anyone could give me a pointer as to what I've done wrong, I'd much appreciate it. -- 8:32am up 1 day, 1:09, 1 user, load average: 0.33, 0.16, 0.05 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
