RE: Getting the last entered row from a relational table
Try: select * from comments where (place_holder id) = (id in main table) order by id desc LIMIT 1 Andy -Original Message- From: Petre Agenbag [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 11:05 To: [EMAIL PROTECTED] Subject: Getting the last entered row from a relational table Hi List. I think I'm having a very off day and need some confirmations on how MySQL works with it's result sets. I have a couple of relational tables , the first holding the person's name and address for instance, and the other tables holds comments and complaints respectively. Each table has it's own id field, as well as a master_id that equals the id of the main table. The app that I'm writing (in PHP), lists the users with a simple select * from main , and this returns all the users currently on the system ( I have names as varchar and unique). My problem is now with the following: When the user clicks on one of the names, I want to do a couple of things: a) The user details be displayed along with all the comments and complaints that correspond to that users id located in the other tables. pseudo SQL - select * from comments where (place_holder id in comments) = (id in main table); b) Being able to list the comments and complaints in reverse order ie, older ones first: pseudo SQL - select * from comments where (place_holder id) = (id in main table) order by id desc; c) List ONLY the last (newest) comments/complaints THIS IS WHERE I have problems: If I do a select MAX(id), comment from comments where (place_holder id) = (id in main table) will MySQL automagically grab the comment from the row that has the maximum ID? If so, is there a shorter way of doing this query? For my example here, it's not a big deal, but with larger tables with more collumns, having to specify the collumns in the query ( when I want ALL to be returned) becomes a bit of a hassle.. I basically want to say: return ONLY the last comment added where the id matches the supplied id from main_table. So, I need to Translate this to SQL... Can I do this with SQL, or must I first establish the id with the select MAX(id) from comments where id = provided_id, and then do a new query select * from comments where id = MAX(id) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REPOST: FULLTEXT searching help needed. Please somebody help.
When mysql is indexing You can check your data file direcory and You ca see that one file (the index) is growing in size. I see it grow up to 8M and it stays there. Watching this, it grows very slowly. After 5 minutes of indexing, the file size has barely hit 2MB. The command I use is: create fulltext index Name on Table(field) So far I've only used the alter table call... I will use this call next and see if I get any problems. I've used the create index call now, as described above, and it does not change anything. The index still only grows to 8MB. I've read something of MySQL 4.0.12-0 having a fulltext index bug that converts text into char. (MySQL documentation in MySQL 4.0.13-0 docs describes this in a bug fix entry. Perhaps this is a symptom ? I am busy downloading the newer version to find out. No change. Can your system handle files larger than 2GB? Maybe this is the problem. Can anyone give me any indication as to whether this may be the case ? Should I load the kernel that supports bigger filesystems ? Thanks for all help Ciao H M Kunzmann signature.asc Description: This is a digitally signed message part
Re: Problem with mysql replication
Dan Houtz [EMAIL PROTECTED] wrote: I'm currently trying to setup replication following the directions from the mysql manual, however, once I configure the slave server with the server-id parameter, it fails to load. If I remove server-id=2 from my.cnf then the server starts up fine. I'm hoping someone can help me out: Master and Slace are both running mySQL 3.23.52 Master Config: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb log-bin server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log Pid-file=/var/run/mysqld/mysqld.pid Slave Config: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock master-host=192.168.0.8 master-user=replicate master-password=password server-id=2 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid here is what the slave server logs when started (it appears to hang on each attempted start/-restart): Number of processes running now: 1 mysqld process hanging, pid 22725 - killed 030604 15:44:44 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html /usr/libexec/mysqld: ready for connections Number of processes running now: 1 mysqld process hanging, pid 22749 - killed 030604 15:44:44 mysqld restarted 030604 15:44:44 Can't start server: Bind on TCP/IP port: Address already in use 030604 15:44:44 Do you already have another mysqld server running on port: 3306 ? 030604 15:44:44 Aborting 030604 15:44:44 /usr/libexec/mysqld: Shutdown Complete 030604 15:44:44 mysqld ended But it's not a replication related error. Do you use InnoDB? Do you have any entries in the my.cnf related to the InnoDB startup options? -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling:Not a valid library (invalid magic number).Possibly a 64-bit PA archive library (Mismatched ABI).
Hi! I am porting a program from windows to UNIX and I cannot get it to compile on the UNIX version. Any suggestions?? Are the libraries shipped with mysql correct? Am I compiling it correctly? Any help is appreciated! Thanks Eric Error exit code 1 /opt/ansic/bin/c89 -o bin/ceagtest obj/ceagtest/ceagtest.o -lm lib/mysqldbi.a lib/gcsys.a lib/gcdbi.a lib/sclib.a lib/gparse2.a lib/cfg.a lib/cpk.a lib/gms.a lib/gf.a lib/gtrace.a /software/grs/actr/mysql/lib/libmysys.a /usr/ccs/bin/ld: /software/grs/actr/mysql/lib/libmysys.a: Not a valid library (invalid magic number). Possibly a 64-bit PA archive library (Mismatched ABI). *** Error exit code 1 Server/Version Information: HP-UX 11.00 Operating System mysql status -- /software/grs/actr/mysql/bin/mysql Ver 12.18 Distrib 4.0.12, for hp-hpux11.11 (hppa2.0w) Connection id: 3 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.12-standard-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 1 day 16 hours 4 min 20 sec Threads: 1 Questions: 12 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.000 -- mysql _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to select without using sub-select?
Hi, I am running MySQL 4.0.10 gamma. I have records with data: id title version 1a 1 2a 2 3b 1 4b 2 How do I select all the records with highest version for each title? (The records with id 2 and 4) I've been thinking about combinating GROUP BY and max(), but I cant get it to work... SELECT id, title, max(version) FROM table GROUP BY title; returns 1 a 2 3 b 2 Thanks for any help /Ola
ERROR 2003: Can't connect to MySQL server on ....
Hello list: Just finished installing mySQL on a Linux Debian system and I have a question about how the 2 root users that are set up by default. When I look at my user table there is a root user allowed to log in from localhost and also another root user but this one is only allowed to log in from host.mydomain.com (host being the computer that the mySQL server is installed in). I understand the difference since localhost is when you are working on the computer itself (or remotely through ssh) that has the mySQl server installed and that HOST.MYDOMAIN.COM is the name of the localhost machine on the LAN. However, if you are accessing the mySQL server from HOST.MYDOMAIN.COM isn't that technically the same thing as accessing from localhost? since in order to access the mySQL server from HOST.MYDOMAIN.COM I would have to be at the machine itself wouldn't the server just treat that connection as localhost? I have read on the mysql.com site about how the mySQL server sorts the entries in the user table at the time the server is started by priority of specific entries to less specific ones, however if I have a password set for both [EMAIL PROTECTED] and [EMAIL PROTECTED] then which connection would be used? Not that it matters since the set of privileges are the same. I am just really wondering if I need the extra entry of [EMAIL PROTECTED] when I already have [EMAIL PROTECTED] And how would I ever connect to the mySQL server using the [EMAIL PROTECTED] account? Also I was trying to access the mySQL server using the root@ HOST.MYDOMAIN.COM account by using the command below: Shell mysql -u root -h ZEUS.OLYMPUS.LOCAL -p Enter Password: *** And this is what I get: ERROR 2003: Can't connect to MySQL server on 'ZEUS.OLYMPUS.LOCAL' (111) I also tried using the IP of the server: Shell mysql -u root -h 192.168.1.8 -p Enter Password: *** And I still got: ERROR 2003: Can't connect to MySQL server on '192.168.1.8' (111) What does that mean? I tried searching on the list archives but found nothing on it. And I did check my /etc/hosts file and this is what is in it: 127.0.0.1 localhost 192.168.1.8 ZEUS.OLYMPUS.LOCAL ZEUS Any help would be appreciated it, thanx!
RE: load file
That should work just fine (although you should end the line with ; and not l) Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 3:54 AM To: [EMAIL PROTECTED] Subject: load file Hi, I have a a text file delimited with | and I don't how to load the data into a table and say delimited by '|' I think it is this: LOAD DATA INFILE 'C:\data.txt' INTO TABLE table2 FIELDS TERMINATED BY '|'l Any clarification would help. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select without using sub-select?
Hi Ola, How about SELECT id, title, max(version) FROM table GROUP BY version ORDER BY title; ? Thats a guess though... Best regards Nils Valentin Tokyo/Japan 2003 6 5 22:[EMAIL PROTECTED] : Hi, I am running MySQL 4.0.10 gamma. I have records with data: id title version 1a 1 2a 2 3b 1 4b 2 How do I select all the records with highest version for each title? (The records with id 2 and 4) I've been thinking about combinating GROUP BY and max(), but I cant get it to work... SELECT id, title, max(version) FROM table GROUP BY title; returns 1 a 2 3 b 2 Thanks for any help /Ola -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select without using sub-select?
[EMAIL PROTECTED] wrote: Hi, I am running MySQL 4.0.10 gamma. I have records with data: id title version 1a 1 2a 2 3b 1 4b 2 How do I select all the records with highest version for each title? (The records with id 2 and 4) I've been thinking about combinating GROUP BY and max(), but I cant get it to work... SELECT id, title, max(version) FROM table GROUP BY title; returns 1 a 2 3 b 2 Thanks for any help /Ola Because this is not proper SQL. 'id' is in neither the MAX aggragate function nor the GROUP BY. You told the server you want only 1 line per title with GROUP BY title, but the server has no way of knowing which line you want. MySQL allows this syntax where other servers don't, because sometimes it is useful. You have to be careful though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug report: LIMIT of 1000 rows returned on SELECT (2nd try)
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: LIMIT of 1000 rows returned on SELECT (2nd try)
Description:
When doing a query on a table with more than 1000 rows,
the SELECT * query returns only the first 1000 rows.
How-To-Repeat:
I tried to attach data for the shop table in your Tutorial which
demonstrates the problem, but got the following reply from you:
Hi. This is the qmail-send program at lists.mysql.com.
I'm afraid I wasn't able to deliver your message to the following addresses.
This is a permanent error; I've given up. Sorry it didn't work out.
[EMAIL PROTECTED]:
ezmlm-reject: fatal: Sorry, I don't accept messages larger than 3 bytes
(#5.2.3)
[EMAIL PROTECTED]:
So what I have done this time is attached the AWK program which
generated the data.
Fix:
Work-around:add a LIMIT -1 to the SELECT query
Long-term fix: add words in section 6.4.1 of the manual which:
1. describe the 1000 line limit
2. describe how to use the LIMIT -1 option
Synopsis: LIMIT of 1000 rows returned on SELECT
Submitter-Id: none
Originator: [EMAIL PROTECTED]
Organization: eds
MySQL support: none
Severity: non-critical
Priority: low
Category: mysql manual
Class: doc-bug
Release:mysql-3.23.38
Exectutable: mysqld
Environment: PC
System:Win2000
Compiler: VC++ 6.0
Architecture: i
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));
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);
LOAD DATA LOCAL INFILE nameoffilebelow.txt INTO SHOP;
SELECT * FROM SHOP;# demonstrates limit of 1000 rows
SELECT * FROM SHOP LIMIT -1; # demonstrates work-around
# genshop.awk
# generate random data for 1500 articles in the dummy MySQL
# tutorial db table called shop
BEGIN {
min_article = 200
max_article = 1700
min_data_value = 0.50
max_data_value = 999.50
len_dealer_name = 5
for (a=min_article; a = max_article; a++) {
printf(%04d\t, a)
dealer =
for (k=1; k = len_dealer_name; k++) {
dealer = dealer randltr()
}
printf(%s\t%8.2f\n, dealer, randf(min_data_value, max_data_value))
}
}
function randint (a, b) { return int((b-a+1)*rand()) + a }
function randf (a, b) { return (b-a)*rand() + a }
function randltr () {
return substr(ABCDEFGHIJKLMNOPQRSTUVWXYZ, randint(1,26), 1)
}
[EMAIL PROTECTED]
Electronic Data Systems
Plano Solution Centre - Internal Systems
(972) 605-3931(8-837)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count on Multiple Tables
Can you show some table structure so we have something work with? It's hard to recommend a query when we do not know what your sales table structure is. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 4:00 PM To: [EMAIL PROTECTED] Subject: Count on Multiple Tables I've been stuck on this one all morning. Can't seem to figure it out. I have 2 tables, one with affiliate sales and another with affiliate clickthroughs. I have to query both tables, so that I can get clickthrough dates, hits, and then query affiliate sales table to get number of orders for each date. I want to display the results like this: DATE | TOTAL HITS | TOTAL SALES 05/03/2003 6 1 05/04/2003 7 0 I've managed to get dates and total hits by using the following query: SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*) AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND YEAR(affiliate_clientdate) = '2003' GROUP BY date; But then I can't get the total number of sales on affiliate sales table. Any suggestions? Your help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you create ascii line breaks
How do you create ascii line breaks in a text paragraph in mysql?. When displaying text taken from a mysql row it displays it all as a flowing paragraph ie sdf sdfgfj fgjhgfh fgj fgj hfj hj hg gh hgk ghk gj gh kg k hg hg jh j fgj fg When i need to look like sdf sdfgfj fgjhgfh fgj fgj hfj hj hg gh hgk ghk gj gh kg k hg hg jh j fgj fg I am dispalying it in a webbrowser using the below code which will convert ascii line breaks into HTML br tags. but i am unsure how to put in the ascii line breaks into mysql. ?php echo nl2br($row_myRecordset['myField']); ?
Reg my.cnf entry
Hello mysql, Just let me know whether we can create seperate binary files for seperate DBs used. And how to.. also Thanx Regards, Charitha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: QUESTION ABOUT DATA AND INDEX FILE DIRECTORIES
Ares Liu [EMAIL PROTECTED] wrote: But is it available on AIX 4.3.x ? Yes, DATA DIRECTORY and INDEX DIRECTORY work on AIX. -Ares On 02 Jun 2003 19:50 CST you wrote: DATA DIRECTORY and INDEX DIRECTORY is ignored on Windows. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database file quota question
Dear all, We are an established IT company that recently started a webhosting service. We are offering MySQL databases and PHP on our servers. We are setting up disk quota for our virtual domains. Everything works fine, except we have no clue as to how to link one or more databases to a certain linux account or group and hence count these files in the quota. Any suggestions? _ Ontvang je Hotmail Messenger berichten op je mobiele telefoon met Hotmail SMS http://www.msn.be/gsm/smsservices -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug report: LIMIT of 1000 rows returned on SELECT (2nd try)
Johnson, David C [EMAIL PROTECTED] wrote: From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: LIMIT of 1000 rows returned on SELECT (2nd try) Description: When doing a query on a table with more than 1000 rows, the SELECT * query returns only the first 1000 rows. Do you use SQL_SELECT_LIMIT session variable? -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-4 binary problem for IRIX64
My machine is a IRIX 6.5 Online Manual: 2.6.6.8 SGI Irix Notes If you are using Irix Version 6.5.3 or newer mysqld will only be able to create threads if you run it as a user with CAP_SCHED_MGT privileges (like root) or give the mysqld server this privilege with the following shell command: shell chcap CAP_SCHED_MGT+epi /opt/mysql/libexec/mysqld maybe i have a problem with the CAP_SCHED_MGT privileges? BTW, in my binary distribution i don't have a libexec directory... nothing seem to happens if shell chcap CAP_SCHED_MGT+epi /opt/mysql/bin/mysqld (my binary is in /opt) Thankx. Hello everybody, i don´t know if i deserve a big RTFM... After install a MySQL Binary Distribution, the mysql-standard-4.0.13-sgi-irix6.5-mips in a SGI machine with IRIX64, the localhost.err was: 030604 17:50:59 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 030604 17:51:00 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 030604 17:51:02 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 030604 17:51:03 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 030604 17:51:06 InnoDB: Started /opt/mysql-standard-4.0.13-sgi-irix6.5-mips/bin/mysqld: ready for connections. Version: '4.0.13-standard' socket: '/tmp/mysql.sock' port: 3306 But when i try to acces the mysql console... bin/mysql ERROR 1135: Can't create a new thread (errno 1). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug lets see the process ps -e | grep my 1763485 ? 0:00 mysqld_sa 1777906 ? 1:51 mysqld and the mysql.sock is in the /tmp directory... please, could you tell me what i'm doing wrong? Thank you very much.
Securing a Website driven with MySQL/PHP/Apache
I am in the process of developing a MySql database driven website but I need to know that it is secure. Should the User Authentication Password stored in the database be encryted? And if so, how? Any other security tips are welcome..! Thanks. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
Can't get MySQL to log queries.
I need to be able to examine the queries being sent to MySQL. After reading the documentation I created /etc/my.cnf and added a line for the log option. Unfortunately, I have been unable to get it to log the queries. Is there another option that must be set? I have the following in /etc/my.cnf [mysqld] log=/var/log/mysql/mysql_query.log [mysql.server] log=/var/log/mysql/mysql_query.log This is what /var/log/mysql/mysql_query.log looks like. /usr/sbin/mysqld, Version: 4.0.13-log, started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id CommandArgument I am using MySQL 4.0.13 on Redhat 7.3. Aaron
RE: Securing a Website driven with MySQL/PHP/Apache
[snip] I am in the process of developing a MySql database driven website but I need to know that it is secure. Should the User Authentication Password stored in the database be encryted? And if so, how? Any other security tips are welcome..! [/snip] RTFM passwords in MySQL are encrypted using the MD5 hash. You can read about these security features at http://www.mysql.com keyword security. Security for PHP can be found at http://www.php.net TTFN! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count on Multiple Tables
CREATE TABLE `affiliate_clickthroughs` ( `affiliate_clickthrough_id` int(11) NOT NULL auto_increment, `affiliate_id` int(11) NOT NULL default '0', `affiliate_stores_id` bigint(11) NOT NULL default '0', `affiliate_clientdate` datetime NOT NULL default '-00-00 00:00:00', `affiliate_clientbrowser` varchar(200) default 'Could Not Find This Data', `affiliate_clientip` varchar(50) default 'Could Not Find This Data', `affiliate_clientreferer` varchar(200) default 'none detected (maybe a direct link)', `affiliate_products_id` int(11) default '0', `affiliate_banner_id` int(11) NOT NULL default '0', PRIMARY KEY (`affiliate_clickthrough_id`), KEY `refid` (`affiliate_id`) ) TYPE=MyISAM AUTO_INCREMENT=9 ; CREATE TABLE `affiliate_stores_sales` ( `affiliate_id` int(11) NOT NULL default '0', `affiliate_stores_id` bigint(11) NOT NULL default '0', `affiliate_orders_id` int(11) NOT NULL default '0', `affiliate_clickthroughs_id` int(11) NOT NULL default '0', `affiliate_payment_id` int(11) NOT NULL default '0', `date` datetime NOT NULL default '-00-00 00:00:00', `browser` varchar(100) NOT NULL default '', `ipaddress` varchar(20) NOT NULL default '', `sale` decimal(15,2) NOT NULL default '0.00', `commission` decimal(15,2) NOT NULL default '0.00', `billing_status` int(5) NOT NULL default '0', `payment_date` datetime NOT NULL default '-00-00 00:00:00', `commission_percent` decimal(4,2) NOT NULL default '0.00', PRIMARY KEY (`affiliate_orders_id`) ) TYPE=MyISAM; -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 7:09 AM To: Ralph; [EMAIL PROTECTED] Subject: RE: Count on Multiple Tables Can you show some table structure so we have something work with? It's hard to recommend a query when we do not know what your sales table structure is. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 4:00 PM To: [EMAIL PROTECTED] Subject: Count on Multiple Tables I've been stuck on this one all morning. Can't seem to figure it out. I have 2 tables, one with affiliate sales and another with affiliate clickthroughs. I have to query both tables, so that I can get clickthrough dates, hits, and then query affiliate sales table to get number of orders for each date. I want to display the results like this: DATE | TOTAL HITS | TOTAL SALES 05/03/2003 6 1 05/04/2003 7 0 I've managed to get dates and total hits by using the following query: SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*) AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND YEAR(affiliate_clientdate) = '2003' GROUP BY date; But then I can't get the total number of sales on affiliate sales table. Any suggestions? Your help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you create ascii line breaks
Daniel Crompton wrote: How do you create ascii line breaks in a text paragraph in mysql?. When displaying text taken from a mysql row it displays it all as a flowing paragraph ie sdf sdfgfj fgjhgfh fgj fgj hfj hj hg gh hgk ghk gj gh kg k hg hg jh j fgj fg When i need to look like sdf sdfgfj fgjhgfh fgj fgj hfj hj hg gh hgk ghk gj gh kg k hg hg jh j fgj fg I am dispalying it in a webbrowser using the below code which will convert ascii line breaks into HTML br tags. but i am unsure how to put in the ascii line breaks into mysql. ?php echo nl2br($row_myRecordset['myField']); ? The string you are storing in the text field must have the line feeds in it before it is quoted and saved. How they get there depends on the language you are using to generate the data. This is not a MySQL question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2003: Can't connect to MySQL server on ....
Hello list: I just finished installing mySQL on a Linux Debian system and I have a question about how the 2 root users that are set up by default. When I look at my user table there is a root user allowed to log in from localhost and also another root user but this one is only allowed to log in from host.mydomain.com (host being the computer that the mySQL server is installed in). I understand the difference since localhost is when you are working on the computer itself (or remotely through ssh) that has the mySQl server installed and that HOST.MYDOMAIN.COM is the name of the localhost machine on the LAN. However, if you are accessing the mySQL server from HOST.MYDOMAIN.COM isnt that technically the same thing as accessing from localhost? since in order to access the mySQL server from HOST.MYDOMAIN.COM I would have to be at the machine itself wouldnt the server just treat that connection as localhost? I have read on the mysql.com site about how the mySQL server sorts the entries in the user table at the time the server is started by priority of specific entries to less specific ones, however if I have a password set for both [EMAIL PROTECTED] and [EMAIL PROTECTED] then which connection would be used? Not that it matters since the set of privileges are the same. I am just really wondering if I need the extra entry of [EMAIL PROTECTED] when I already have [EMAIL PROTECTED] And how would I ever connect to the mySQL server using the [EMAIL PROTECTED] account? Also I was trying to access the mySQL server using the root@ HOST.MYDOMAIN.COM account by using the command below: Shell mysql u root h ZEUS.OLYMPUS.LOCAL p Enter Password: *** And this is what I get: ERROR 2003: Can't connect to MySQL server on 'ZEUS.OLYMPUS.LOCAL' (111) I also tried using the IP of the server: Shell mysql u root h 192.168.1.8 p Enter Password: *** And I still got: ERROR 2003: Can't connect to MySQL server on '192.168.1.8' (111) What does that mean? I tried searching on the list archives but found nothing on it. And I did check my /etc/hosts file and this is what is in it: 127.0.0.1 localhost 192.168.1.8 ZEUS.OLYMPUS.LOCAL ZEUS Any help would be appreciated it, thanx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Acrobat Talk Stats for May, 2003
As some of you know, we host the Acrobat Talk list searchable archives at ListSearch.com as a free service to the Acrobat community. To access said archives, visit http://www.listsearch.com/acrobattalk.lasso. The archives not only allow you to search on various fields of information (body, date, subject, author, etc.) and to view threads, but, we keep lots of stats, including the number of searches performed and a log of all search terms used when accessing the archives. You can see what other people are searching about! Here are the stats for the Acrobat Talk list for May, 2003. You can see these updated every 1/2 hour by clicking the Browse tab at ListSearch.com. Enjoy! Bill Acrobat Talk Archives Stats May, 2003 Note: Up/Down % as compared with April, 2003 Posts: 70 (Down 33%) Authors: 38 (Down 7%) Threads: 22 (Down 24%) Top 20 Contributors by Number of Posts Leonard Rosenthol 9 Nini Tjader5 D. Brien 5 Brian Radford 3 Dov Isaacs 3 Matthew Fitzgerald 3 Lindsey Thomas Martin 2 David L. Perry 2 Shlomo Perets 2 Manso-Rivera, Ruth 2 Kevin Hinchey 2 Beth Friedman 2 Justin M 2 Pat Randle 2 Cy Galley 2 Chris Tangora 2 Desirea Herrera1 Bill Doerrfeld 1 Jim Sheffer1 acrobat-admin 1 Top 20 Threads by Number of Posts Acrobat 6 16 Fixing a PDF? 14 Renaming Watched Folders 6 Security vrs. index generation 3 White boxes in landscape pdfs... 3 Changes in Mac OS X PDF... 3 Help bubbles 3 [Memo] Notice forthcoming outage 3 Printing search results2 unsubscribe2 Generating pdf's from Access 2 Out of Office AutoReply: acrobat Digest #122 Database 2 Forms help needed 1 Mark Osborne/BNA Inc is out of the office. 1 Out of Office AutoReply: acrobat Digest #7 1 [ADMIN] new posting procedures 1 Summary Renaming Watched Folders1 Security and Signatures v5 Acrobat 1 Out of Office AutoReply: acrobat Digest #171 Top 20 Search Terms by Number of Requests graphics 13 command 11 line 11 web 10 Acrobat7 page 5 security 5 plugin 5 url4 form 4 missing4 window 4 pdf4 fonts 3 save 3 character 3 open 3 file 3 extract3 landscape 3 --
threads not being killed
Greetings, we running mysql 4.0.13 compiled statically with linuxthreads on freebsd 5.0 (SMP). The problem is that a few connections are not dying after the wait_timeout and interactive_timeout expired. The only pattern we can see here is that mysql clients like jakarta-tomcat (jdbc), and windows (obdc) are the ones not that are not dying. Another thing is that these are the only clients based on 3.XX libmysqlclient... We are not having problem so far with php and perl clients compiled with 4.XX libs. I searched the manual for answers and nothing... The manual does say that we shouldn't have any problem with old 3.XX clients except for using the new privileges provided by 4.XX. I don't really know if this is a freebsd problem as described in http://jeremy.zawodny.com/blog/archives/000697.html or the old mysql client. Any ideas? -- -- Gustavo Baratto - Programming and Technical Support [EMAIL PROTECTED] * (604) 638-2525 ext. 408 Technical support web-site: http://support.superb.net Superb Internet Corp. Ahead of the Rest - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: Acrobat Talk Stats for May, 2003
Date: Thu, 5 Jun 2003 10:01:41 -0700 To: [EMAIL PROTECTED] From: Bill Doerrfeld [EMAIL PROTECTED] Subject: Acrobat Talk Stats for May, 2003 Cc: Bcc: X-Attachments: As some of you know, we host the Acrobat Talk list searchable archives at ListSearch.com as a free service to the Acrobat community. Oops. Apologies that I accidently sent this to the wrong list. I tried to stop it just as it was going out... Anyway, obviously, I used the template for my post yesterday to the MySQL List for this other posting. Sorry for the additional clutter. Bill -- - Bill Doerrfeld[EMAIL PROTECTED] Blue World Communications, Inc. http://www.blueworld.com/ - Build and serve powerful data-driven Web sites with Lasso Studio and Lasso Professional. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting up users
I've been using MySQL on a local Windows host and connecting using the Root user. Now I want to create a user that can only access the database used by an application. I've created the user with no Global privileges and granted Database privileges for that database. However, when I try to connect using that user, I can't see the database in MySQLYog, and the application fails to connect to the database. Is there a global privilege that is required in order for the user to get access to the database? FYI, I have stopped and restarted the service to reload the privileges... Thank you, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update statement, error free but no affected rows?
Hello, I am experiencing some strangeness that I've never ran into before. On an update statement, like: update students set counselor_id = 1 and school_attending='Univsity of Maryland' where id = 1; I have no errors generated with the query, but the affected rows reads 0? This is not duplicate data, and even if I change the data around, the query appears to go through, but no affected rows. I got around the problem by writing a separate query for each field, but that sucks. All of my queries are going through the PHP, so I guess, through the PHP native MySQL functions. Any ideas? Christian Calloway, Web Developer [EMAIL PROTECTED] Antharia, LLC | TDS Interactive www.antharia.com www.tdsinteractive.com 301-982-7358 775-218-4603 (fax) WHERE DESIGN MEETS TECHNOLOGY! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC is not surviving a mysql reboot
Dudes Dudetts filter: mysql, select, sql mysql version: 3.23.49 jdbc version: 2.0.14 development os: win2k production os: solaris Problem: During a reboot of mysqld, the perl connections survived and the jdbc connections did not. My definition of survived is that the connections waited until mysqld rebooted and automatically reconnected. Discussion -- None of the jdbc connections survived. Back to the books I went. I did not have the properties that control this behavior: autoReconnect, maxReconnects, initialTimeout I changed my url + db + properties to: jdbc:mysql://localhost/db1?user=davidpassward=secret\ autoReconnect=truemaxReconnects=5initialTimeout=10 (The trailing backslash is simply for ledgability - not in code) I believe this translates to: If connection is lost, keep trying 5 times and wait 10 seconds between retrys. Result: --- If I start a test application program which simply selects a bunch of records from a local mysql db and writes them to a file, then terminate the mysqld in the middle of the program, I get this exception: Error: findFeature java.sql.SQLException: null: Server shutdown in progress If I start the application program with mysqld not running, I get this exception: java.sql.SQLException: Cannot connect to MySQL server on localhost:3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.net.ConnectException) Question: -- How can I automatically keep my application program running without getting exceptions? Confused as usual David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update statement, error free but no affected rows?
-Original Message- From: Christian Calloway [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 1:13 PM To: '[EMAIL PROTECTED]' Subject: update statement, error free but no affected rows? Hello, I am experiencing some strangeness that I've never ran into before. On an update statement, like: update students set counselor_id = 1 and school_attending='Univsity of Maryland' where id = 1; I have no errors generated with the query, but the affected rows reads 0? This is not duplicate data, and even if I change the data around, the query appears to go through, but no affected rows. I got around the problem by writing a separate query for each field, but that sucks. All of my queries are going through the PHP, so I guess, through the PHP native MySQL functions. Any ideas? Christian Calloway, Web Developer [EMAIL PROTECTED] Antharia, LLC | TDS Interactive www.antharia.com www.tdsinteractive.com 301-982-7358 775-218-4603 (fax) WHERE DESIGN MEETS TECHNOLOGY! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update statement, error free but no affected rows?
-Original Message- From: Christian Calloway [mailto:[EMAIL PROTECTED] Sent: 05 June 2003 18:32 To: [EMAIL PROTECTED] Subject: RE: update statement, error free but no affected rows? update students set counselor_id = 1 , school_attending='Univsity of Maryland' where id = 1; I think Peter -Original Message- From: Christian Calloway [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 1:13 PM To: '[EMAIL PROTECTED]' Subject: update statement, error free but no affected rows? Hello, I am experiencing some strangeness that I've never ran into before. On an update statement, like: update students set counselor_id = 1 and school_attending='Univsity of Maryland' where id = 1; I have no errors generated with the query, but the affected rows reads 0? This is not duplicate data, and even if I change the data around, the query appears to go through, but no affected rows. I got around the problem by writing a separate query for each field, but that sucks. All of my queries are going through the PHP, so I guess, through the PHP native MySQL functions. Any ideas? Christian Calloway, Web Developer [EMAIL PROTECTED] Antharia, LLC | TDS Interactive www.antharia.com www.tdsinteractive.com 301-982-7358 775-218-4603 (fax) WHERE DESIGN MEETS TECHNOLOGY! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Count on Multiple Tables
SELECT DATE_FORMAT(clicks.affiliate_clientdate, '%M %D, %Y') AS date, COUNT(affiliate_clickthrough_id) AS hits, count(affiliate_orders_id) AS sells FROM affiliate_clickthroughs AS clicks LEFT JOIN affiliate_stores_sales as sales USING(affiliate_id) WHERE clicks.affiliate_id = '111' AND clicks.affiliate_stores_id = '123' AND MONTH(clicks.affiliate_clientdate) = '06' AND YEAR(clicks.affiliate_clientdate) = '2003' GROUP BY date; Does that do what you are looking for? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 10:33 AM To: Mike Hillyer; [EMAIL PROTECTED] Subject: RE: Count on Multiple Tables CREATE TABLE `affiliate_clickthroughs` ( `affiliate_clickthrough_id` int(11) NOT NULL auto_increment, `affiliate_id` int(11) NOT NULL default '0', `affiliate_stores_id` bigint(11) NOT NULL default '0', `affiliate_clientdate` datetime NOT NULL default '-00-00 00:00:00', `affiliate_clientbrowser` varchar(200) default 'Could Not Find This Data', `affiliate_clientip` varchar(50) default 'Could Not Find This Data', `affiliate_clientreferer` varchar(200) default 'none detected (maybe a direct link)', `affiliate_products_id` int(11) default '0', `affiliate_banner_id` int(11) NOT NULL default '0', PRIMARY KEY (`affiliate_clickthrough_id`), KEY `refid` (`affiliate_id`) ) TYPE=MyISAM AUTO_INCREMENT=9 ; CREATE TABLE `affiliate_stores_sales` ( `affiliate_id` int(11) NOT NULL default '0', `affiliate_stores_id` bigint(11) NOT NULL default '0', `affiliate_orders_id` int(11) NOT NULL default '0', `affiliate_clickthroughs_id` int(11) NOT NULL default '0', `affiliate_payment_id` int(11) NOT NULL default '0', `date` datetime NOT NULL default '-00-00 00:00:00', `browser` varchar(100) NOT NULL default '', `ipaddress` varchar(20) NOT NULL default '', `sale` decimal(15,2) NOT NULL default '0.00', `commission` decimal(15,2) NOT NULL default '0.00', `billing_status` int(5) NOT NULL default '0', `payment_date` datetime NOT NULL default '-00-00 00:00:00', `commission_percent` decimal(4,2) NOT NULL default '0.00', PRIMARY KEY (`affiliate_orders_id`) ) TYPE=MyISAM; -Original Message- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 7:09 AM To: Ralph; [EMAIL PROTECTED] Subject: RE: Count on Multiple Tables Can you show some table structure so we have something work with? It's hard to recommend a query when we do not know what your sales table structure is. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Ralph [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 04, 2003 4:00 PM To: [EMAIL PROTECTED] Subject: Count on Multiple Tables I've been stuck on this one all morning. Can't seem to figure it out. I have 2 tables, one with affiliate sales and another with affiliate clickthroughs. I have to query both tables, so that I can get clickthrough dates, hits, and then query affiliate sales table to get number of orders for each date. I want to display the results like this: DATE | TOTAL HITS | TOTAL SALES 05/03/2003 6 1 05/04/2003 7 0 I've managed to get dates and total hits by using the following query: SELECT DATE_FORMAT(affiliate_clientdate, '%M %D, %Y') AS date, COUNT(*) AS hits FROM affiliate_clickthroughs WHERE affiliate_id = '111' AND affiliate_stores_id = '123' AND MONTH(affiliate_clientdate) = '06' AND YEAR(affiliate_clientdate) = '2003' GROUP BY date; But then I can't get the total number of sales on affiliate sales table. Any suggestions? Your help is greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join question
Hi, I have posted a similar question, but can't find the answers. I'm sorry. What i'm trying to do is that i have 3 tables each has three columns and are indexed. so I want to do something like select everything in 3 tables and display only similar id. Select id from table1 where table1.cl1 = 1 and table1.cle2=5 and table1.cl3 IN(1,2,5,8) Join Select id from table2 where table2.cl1 = 4 and table2.cle2IN(10,12,81) and table2.cl3 IN(3,7) Join Select id from table3 where table3.distance BETWEEN 1 and 99 where table1.id = table2.id=table3.id Any help is appreciated Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join question
SELECT table1.id FROM table1, table2, table3 WHERE table1.cl1 = 1 AND table1.cle2=5 AND table1.cl3 IN(1,2,5,8) AND table2.cl1 = 4 AND table2.cle2 IN (10,12,81) AND table2.cl3 IN (3,7) AND table3.distance BETWEEN 1 AND 99 AND table1.id = table2.id AND table2.id = table3.id; That should do what you are looking for, otherwise let us know. Regards, Mike Hilyer www.vbmysql.com -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 12:17 PM To: [EMAIL PROTECTED] Subject: Join question Hi, I have posted a similar question, but can't find the answers. I'm sorry. What i'm trying to do is that i have 3 tables each has three columns and are indexed. so I want to do something like select everything in 3 tables and display only similar id. Select id from table1 where table1.cl1 = 1 and table1.cle2=5 and table1.cl3 IN(1,2,5,8) Join Select id from table2 where table2.cl1 = 4 and table2.cle2IN(10,12,81) and table2.cl3 IN(3,7) Join Select id from table3 where table3.distance BETWEEN 1 and 99 where table1.id = table2.id=table3.id Any help is appreciated Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_real_connect() hangs with SSL support
Hello I have a MySQL 4.0.3 server with SSL option. I know that the SSL option is runnish coz when I do show variables...i am getting the value for ssl to be true :) I am writing a C app to connect to the MySQL server to do some jobs. I am using the mysql_real_connect() function to connect. The code i use is - UINT client = 0; client = client | CLIENT_SSL; ret = mysql_real_connect ( mysql, localhost, user, pwd, NULL, 3306, NULL, client ); The app seems to hang? but when i comment out the line client = client | CLIENT_SSL; everything seems to be working find. I want to use the SSL protocol? Am I doint anything wrong. Yeah...I am using the libmysql.dll which was provided with 3.23.54? Do I need to upgrade the .dll and .lib which is provided with MySQL 4.x??? Thanks in advance. Karam __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Anthony, Do you mean this (not tested)? Select table1.id from table1 INNER JOIN table2 USING (id) INNER JOIN table3 USING (id) WHERE table1.cl1 = 1 and table1.cle2=5 AND table1.cl3 IN(1,2,5,8) AND table2.cl1 = 4 and table2.cle2 IN(10,12,81) AND table2.cl3 IN(3,7) AND table3.distance BETWEEN 1 and 99; HTH PB - Original Message - From: Anthony Ward To: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 1:17 PM Subject: Join question Hi, I have posted a similar question, but can't find the answers. I'm sorry. What i'm trying to do is that i have 3 tables each has three columns and are indexed. so I want to do something like select everything in 3 tables and display only similar id. Select id from table1 where table1.cl1 = 1 and table1.cle2=5 and table1.cl3 IN(1,2,5,8) Join Select id from table2 where table2.cl1 = 4 and table2.cle2IN(10,12,81) and table2.cl3 IN(3,7) Join Select id from table3 where table3.distance BETWEEN 1 and 99 where table1.id = table2.id=table3.id Any help is appreciated Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
vbulleting mysql errors
Hi We have recently upgraded our phpBB forum based software to a vbulletin based one. Since then we get lots of : vBulletin (forum) Database error! mail messages from vbulletin. In the body of the message it says something like: Invalid SQL: SELECT template FROM template WHERE title='error_nopermission_loggedout' AND (templatesetid=-1 OR templatesetid=1) ORDER BY templatesetid DESC LIMIT 1 mysql error: select command denied to user: 'user@host' for table 'template' mysql error number: 1142 The errors are not only on the same table or only on the same query. The problem is that if I connect using mysql from host to the db engine using user and the password of vbulletin user and I try the same query it succeeds. The error doesnt always show. So if I access a vbulletin link for 30 times, one time it does an error and I get an email like the one above. The MySQL server is a 4.0.13, linux binary distribution from a mysql mirror. The load average on the SQL machine is under 1. The SQL server has an average of ~200 queries/s (in 30 days). What should I do to debug this problem and find out the cause ? Thanks Mihai RUSU Disclaimer: Any views or opinions presented within this e-mail are solely those of the author and do not necessarily represent those of any company, unless otherwise specifically stated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Hi, what is the difference between your way and Mike Hillyer way?? (I can see the INNER join). But thanx to both of you. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Where is the COMPRESS() function?
Hi, I was looking through the online manual, and on this page (http://www.mysql.com/doc/en/Miscellaneous_functions.html), there is a function called COMPRESS(): COMPRESS(string_to_compress) compresses a string mysql select length(compress(repeat(a,1000))); ++ | length(compress(repeat(a,1000))) | ++ | 21 | ++ 1 row in set (0.00 sec) I just installed the 4.1 alpha, and that command throws an error. Is this an error in the docs? ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join question
Syntax only. INNER JOIN table2 USING (id) INNER JOIN table3 USING (id) Equates to table1.id = table2.id AND table2.id = table3.id; Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Anthony Ward [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 12:53 PM To: [EMAIL PROTECTED] Subject: Re: Join question Hi, what is the difference between your way and Mike Hillyer way?? (I can see the INNER join). But thanx to both of you. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT: Problems compiling mysql 3 and 4 on IRIX indy 6.5 IP22 mips
Michael Widenius wrote: Hi! Jason == Jason Buchanan [EMAIL PROTECTED] writes: Jason hi, Jason I'm having a hard time getting mysql 4 and mysql 3 to compile on IRIX 6.5... Jason Does anyone have a solution to this? Jason Below is an example of compiling mysql 4.0.13 (looks practically Jason identical for mysql 3.23.56): cut Jason g++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include -I./.. Jason -I.. -I.. -O -DDBUG_OFF -D_BOOL -c -o mysql.o `test -f mysql.cc || Jason echo './'`mysql.cc Jason In file included from Jason /usr/local/lib/gcc-lib/mips-sgi-irix6.5/2.95.3/include/curses.h:5, Jason from mysql.cc:54: Jason /usr/include/curses.h:112: syntax error before `,' Jason /usr/include/curses.h:143: syntax error before `;' cut This problem is covered in the Irix section of the MySQL online manual. Please check it for details how to solve it. I wish there was a link to the irix tips and suggestions page off of the mysql binaries page with the compiler switches... but people in hell want icewater, too ;-) Thanks!! Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Super Smack
Has anyone had any success with super-smack with MySQL 3.23 or 4.0? Thanks _ Sign-up for a FREE BT Broadband connection today! http://www.msn.co.uk/specials/btbroadband -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC is not surviving a mysql reboot
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Lopez David E-r9374c wrote: Dudes Dudetts filter: mysql, select, sql mysql version: 3.23.49 jdbc version: 2.0.14 development os: win2k production os: solaris Problem: During a reboot of mysqld, the perl connections survived and the jdbc connections did not. My definition of survived is that the connections waited until mysqld rebooted and automatically reconnected. Discussion -- None of the jdbc connections survived. Back to the books I went. I did not have the properties that control this behavior: autoReconnect, maxReconnects, initialTimeout I changed my url + db + properties to: jdbc:mysql://localhost/db1?user=davidpassward=secret\ autoReconnect=truemaxReconnects=5initialTimeout=10 (The trailing backslash is simply for ledgability - not in code) I believe this translates to: If connection is lost, keep trying 5 times and wait 10 seconds between retrys. Not exactly, it means it will first wait 10, then 100 , then 1, then 1000 then 100 seconds (it's an exponential backoff). In any case, 'autoReconnect' is a crutch (I don't recommend that you use it) that should not be relied upon. Your application should be able to recover from exceptions itself, that's just good coding practice. Relying on the JDBC driver to do it can be dangerous...Only your application _really_ knows when it is appropriate to retry a transaction. AutoReconnect also only keeps an established connection re-connected. It doesn't retry failed attempts to connect (it wasn't designed to). Any communication-level error in JDBC for MySQL has a SQLSTATE of '08S01', so you can check for that, and handle it appropriately in your application. -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+35YktvXNTca6JD8RApiUAKDErofASLK5G0cDo4pjFIy9o1LxcQCeN6dr 8QUr2I35s7Sgf1/p+vmMreQ= =YyJI -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
os X install mySQL help
This is a mac os X installation problem: When I download/install the pkg version of mysql 4.0.13, two directories seem to be missing in /usr/local/bin/mysql: var/ and run/. So, when I try to run mysqld_safe (or safe_mysqld), I get errors like: touch: /usr/local/mysql/var/myhostname.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql/var... followed by alot more errors which basically say, you don't have a var/ directory or a run/ directory. Is this addressed anywhere in the manual? If I run sudo ./bin/mysqld_safe, I get the mysqld ended error, and this is what my .err file says about it: Can't start server : Bind on unix socket: No such file or directory 030605 10:59:08 Do you already have another mysqld server running on socket: /usr/local/mysql/run/mysql_socket ? 030605 10:59:08 Aborting what's going on here, I've been trying to install this for 10 hours??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems installing mysql and modules on AIX 5.1
I'm trying to install mysql with the DBI and DBD Perl modules on a AIX 5.1 machine, with Perl 5.6. I've also tried using CPAN. On the DBI-1.37 portion, it's failing at the make test stage- below is the ouput. Any suggestions? Running make test PERL_DL_NONLAZY=1 /usr/bin/perl -Iblib/arch -Iblib/lib -I/usr/opt/perl5/ lib/5.6.0/aix -I/usr/opt/perl5/lib/5.6.0 -e 'use Test::Harness qw(runtests $ver bose); $verbose=0; runtests @ARGV;' t/*.t t/base..Can't locate Storable.pm in @INC (@INC contains: blib/arch b lib/lib /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5 /lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/opt/perl5/lib/5.6.0/aix /usr/opt/pe rl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/opt/perl5/lib/site_perl/5.6.0/ai x /usr/opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr /opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr/opt/p erl5/lib/site_perl . /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/ opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr/opt/pe rl5/lib/site_perl .) at blib/lib/RPC/PlServer/Comm.pm line 25. Compilation failed in require at t/base.t line 5. t/base..dubious Test returned status 2 (wstat 512, 0x200) DIED. FAILED tests 1-3 Failed 3/3 tests, 0.00% okay t/clientCan't locate Storable.pm in @INC (@INC contains: blib/arch b lib/lib /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5 /lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/opt/perl5/lib/5.6.0/aix /usr/opt/pe rl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/opt/perl5/lib/site_perl/5.6.0/ai x /usr/opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr /opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr/opt/p erl5/lib/site_perl . /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/ opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr/opt/pe rl5/lib/site_perl .) at blib/lib/RPC/PlServer/Comm.pm line 25. Compilation failed in require at blib/lib/RPC/PlClient.pm line 26. BEGIN failed--compilation aborted at blib/lib/RPC/PlClient.pm line 26. Compilation failed in require at t/lib.pl line 4. Compilation failed in require at t/client.t line 7. t/clientdubious Test returned status 2 (wstat 512, 0x200) t/compress..skipped test on this platform t/crypt.skipped test on this platform t/methods...Can't locate Storable.pm in @INC (@INC contains: blib/arch b lib/lib /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5 /lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/opt/perl5/lib/5.6.0/aix /usr/opt/pe rl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/opt/perl5/lib/site_perl/5.6.0/ai x /usr/opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr /opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr/opt/p erl5/lib/site_perl . /usr/opt/perl5/lib/5.6.0/aix /usr/opt/perl5/lib/5.6.0 /usr/ opt/perl5/lib/site_perl/5.6.0/aix /usr/opt/perl5/lib/site_perl/5.6.0 /usr/opt/pe rl5/lib/site_perl .) at blib/lib/RPC/PlServer/Comm.pm line 25. Compilation failed in require at blib/lib/RPC/PlClient.pm line 26. BEGIN failed--compilation aborted at blib/lib/RPC/PlClient.pm line 26. Compilation failed in require at t/lib.pl line 4. Compilation failed in require at t/methods.t line 7. t/methods...dubious Test returned status 2 (wstat 512, 0x200) Failed Test Status Wstat Total Fail Failed List of failed --- t/base.t 2 512 33 100.00% 1-3 t/client.t2 512?? ?? % ?? t/methods.t 2 512?? ?? % ?? 2 tests skipped. Failed 3/5 test scripts, 40.00% okay. 3/3 subtests failed, 0.00% okay. make: 1254-004 The error code from the last command is 2. Stop. /usr/bin/make test -- NOT OK Running make install make test had returned bad status, won't install without force -- Dana Holland[EMAIL PROTECTED] 903-875-7355 Navarro CollegeCorsicana, TX http://www.navarrocollege.edu/staff_pages/dana/dana.html All opinions stated are my own, and probably don't even vaguely resemble those of Navarro College. :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Super Smack
On Thu, Jun 05, 2003 at 08:02:50PM +0100, my5ql _ wrote: Has anyone had any success with super-smack with MySQL 3.23 or 4.0? Yes. And I'm setting up a site for it. Sasha has updated the code recnetly (after I nudged him) and asked me to host it. Plus I have a few more patches to roll into it. What problems are you having, exactly? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 2 days, processed 90,131,521 queries (413/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: os X install mySQL help
Go to the /usr/local/mysql directory and rename (mv) data to var. -- Jim Dickenson mailto:[EMAIL PROTECTED] Computers for Marketing Corporation http://www.cfmc.com/ From: gerald jones - Sun Microsystems - Broomfield United States [EMAIL PROTECTED] Reply-To: gerald jones - Sun Microsystems - Broomfield United States [EMAIL PROTECTED] Date: Thu, 5 Jun 2003 13:18:29 -0600 (MDT) To: [EMAIL PROTECTED] Subject: os X install mySQL help This is a mac os X installation problem: When I download/install the pkg version of mysql 4.0.13, two directories seem to be missing in /usr/local/bin/mysql: var/ and run/. So, when I try to run mysqld_safe (or safe_mysqld), I get errors like: touch: /usr/local/mysql/var/myhostname.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql/var... followed by alot more errors which basically say, you don't have a var/ directory or a run/ directory. Is this addressed anywhere in the manual? If I run sudo ./bin/mysqld_safe, I get the mysqld ended error, and this is what my .err file says about it: Can't start server : Bind on unix socket: No such file or directory 030605 10:59:08 Do you already have another mysqld server running on socket: /usr/local/mysql/run/mysql_socket ? 030605 10:59:08 Aborting what's going on here, I've been trying to install this for 10 hours??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: os X install mySQL help
A good reference, may help (or not): http://www.entropy.ch/software/macosx/mysql/ Ted Rogers On Thursday, June 5, 2003, at 03:18 PM, gerald jones - Sun Microsystems - Broomfield United States wrote: This is a mac os X installation problem: When I download/install the pkg version of mysql 4.0.13, two directories seem to be missing in /usr/local/bin/mysql: var/ and run/. So, when I try to run mysqld_safe (or safe_mysqld), I get errors like: touch: /usr/local/mysql/var/myhostname.err: No such file or directory Starting mysqld daemon with databases from /usr/local/mysql/var... followed by alot more errors which basically say, you don't have a var/ directory or a run/ directory. Is this addressed anywhere in the manual? If I run sudo ./bin/mysqld_safe, I get the mysqld ended error, and this is what my .err file says about it: Can't start server : Bind on unix socket: No such file or directory 030605 10:59:08 Do you already have another mysqld server running on socket: /usr/local/mysql/run/mysql_socket ? 030605 10:59:08 Aborting what's going on here, I've been trying to install this for 10 hours??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where is the COMPRESS() function?
Ever consider that this could have something to do with the fact that you're running an alpha release? Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Ed Leafe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 05 June, 2003 14:53 Subject: Where is the COMPRESS() function? Hi, I was looking through the online manual, and on this page (http://www.mysql.com/doc/en/Miscellaneous_functions.html), there is a function called COMPRESS(): COMPRESS(string_to_compress) compresses a string mysql select length(compress(repeat(a,1000))); ++ | length(compress(repeat(a,1000))) | ++ | 21 | ++ 1 row in set (0.00 sec) I just installed the 4.1 alpha, and that command throws an error. Is this an error in the docs? ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding the 1st 2nd 3r or 4th DOW of a month
I have a database in mySQL that I use to keep track of phone calls to my company. I've been able to query the database and get the information I need until now and being pretty much a self taught novice to sql I'm somewaht stumped. What I want to do is to compare specific days of the week in a month to the same month from previous year. An example would be that I would want to compare the 3rd Thursday in May to the 3rd Thursday in May one or even two years ago. What I have been doing is something like: select * from mytable where dayname(dateint) = 'Thursday' and week(dateint) = 10 However this does not seem to always give me the results I'm expecting. Does anyone have any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: vbulleting mysql errors
Hi, vBulletin has a customer support forum, if you post there the support team will deal with it or pass it to one of the developers. Jerry - Original Message - From: Mihai RUSU [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:13 PM Subject: vbulleting mysql errors Hi We have recently upgraded our phpBB forum based software to a vbulletin based one. Since then we get lots of : vBulletin (forum) Database error! mail messages from vbulletin. In the body of the message it says something like: Invalid SQL: SELECT template FROM template WHERE title='error_nopermission_loggedout' AND (templatesetid=-1 OR templatesetid=1) ORDER BY templatesetid DESC LIMIT 1 mysql error: select command denied to user: 'user@host' for table 'template' mysql error number: 1142 The errors are not only on the same table or only on the same query. The problem is that if I connect using mysql from host to the db engine using user and the password of vbulletin user and I try the same query it succeeds. The error doesnt always show. So if I access a vbulletin link for 30 times, one time it does an error and I get an email like the one above. The MySQL server is a 4.0.13, linux binary distribution from a mysql mirror. The load average on the SQL machine is under 1. The SQL server has an average of ~200 queries/s (in 30 days). What should I do to debug this problem and find out the cause ? Thanks Mihai RUSU Disclaimer: Any views or opinions presented within this e-mail are solely those of the author and do not necessarily represent those of any company, unless otherwise specifically stated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
The diff is readability. I prefer the clarity of putting the condition under 'Join' and other cnditions under 'Where'. The MySQL optimiser actually moves the Join to the Where clause, so there ought to be no performance diff between the two. PB - - Original Message - From: Anthony Ward To: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 1:53 PM Subject: Re: Join question Hi, what is the difference between your way and Mike Hillyer way?? (I can see the INNER join). But thanx to both of you. Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
os X install followup
This is a follow-up mac os X installation problem: I mentioned in an earlier e-mail that var/ and run/ are nowhere to be found, so I did as Jim Dickenson suggested and mv'd data to var. now when I run ./bin/mysqld_safe , I get an error that says /usr/local/mysql/libexec/mysql is missing (because there is no libexec directory. So I edited the mysqld_safe script to look in bin instead of libexec. But it gives methe same error as before when I read the .err log file: Can't start server : Bind on unix socket: No such file or directory 030605 10:59:08 Do you already have another mysqld server running on socket: /usr/local/mysql/run/mysql_socket ? 030605 10:59:08 Aborting ??? -Gerald (Jay) Jones -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select without using sub-select?
[EMAIL PROTECTED] schrieb: I am running MySQL 4.0.10 gamma. I have records with data: id title version 1a 1 2a 2 3b 1 4b 2 How do I select all the records with highest version for each title? [...] Try: SELECT t1.id, t1.title, t1.version FROM table_name t1 LEFT JOIN table_name t2 ON t1.title=t2.title AND t1.versiont2.version WHERE t2.version IS NULL; [optional: ORDER BY title] Replace both occurrences of table_name with the name of your table. HTH, Hans-Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is this not possible???
Hello, I'm not really sure (I'm by no means an expert) but shouldn't this be possible? UPDATE test set i_test=item.i_itemID WHERE test.i_ID=item.i_itemID; I'm getting this reply: MySQL said: Unknown table 'item' in where clause while of course table 'items' exists... I use mySQL service at my webhosting, so I don't have the control over the database. Thanx Marc __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: funky segfaults
I'm not blocking anything, though. Even so, mysql shouldn't be getting a sig11. I added that line anyways. BTW, I checked my log server, and I noticed an error message that might be helpful: Jun 5 16:56:09 perlpimp mysqld[64009]: warning: /etc/hosts.allow, line 23: can't get client address: Bad file descriptor Jun 5 16:56:13 perlpimp mysqld[64019]: warning: can't get client address: Bad file descriptor Right after it gets that first msg it gets the sig11 and safe_mysqld restarts it (see the pid?). Line 23 in /etc/hosts allow is mysqld : ALL : allow. It used to be ALL : ALL : allow. Randi -- Original Message --- From: Ivan Debnár [EMAIL PROTECTED] To: sektie [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thu, 5 Jun 2003 12:12:16 UT Subject: RE: funky segfaults This one may be very esy to solve: Just add mysqld: ALL : allow to your /etc/hosts.allow You can change ALL to IP range that suits you. Let me know if this helps. It helped me. Ivan Debnar Zoznam - Original Message - From:sektie [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent:Thu, 5 Jun 2003 06:48:22 -0500 Subject: funky segfaults I've tried posting this to the FreeBSD-questions mailing list, but to no avail. I'm running FreeBSD 5.0, and I've had this problem on 2 builds so far. 3.23.54 works just fine for me, but 3.23.56 and all versions I've tried of 4.x get a signal 11 anytime a connection is made by anything other than the local socket. I've tried everything I can think of, but I'm at my wits end. Does anyone have any suggestions? Surely I can't be the only person in the world with this problem. :) Randi Harper Interland UNIX PA http://www.interland.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- End of Original Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding the 1st 2nd 3r or 4th DOW of a month
Ahh, how tricky this could be. Don't you wish we had a decimal system of time? Frankly, I think that doing this strictly with SQL would be near-impossible, but I am by no means an SQL guru. If this data is for final presentation via a website, I'd suggest looking into PHP or PERL, either of which will facilitate much more complex processing of your data. However, if someone can explain how to do this strictly with SQL, I would be impressed. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: kallwn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 05 June, 2003 15:47 Subject: finding the 1st 2nd 3r or 4th DOW of a month I have a database in mySQL that I use to keep track of phone calls to my company. I've been able to query the database and get the information I need until now and being pretty much a self taught novice to sql I'm somewaht stumped. What I want to do is to compare specific days of the week in a month to the same month from previous year. An example would be that I would want to compare the 3rd Thursday in May to the 3rd Thursday in May one or even two years ago. What I have been doing is something like: select * from mytable where dayname(dateint) = 'Thursday' and week(dateint) = 10 However this does not seem to always give me the results I'm expecting. Does anyone have any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this not possible???
Unknown table 'item' in where clause while of course table 'items' exists... Well... which table exists, 'item' or 'items?' Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Moj Bordel [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 05 June, 2003 09:30 Subject: Why is this not possible??? Hello, I'm not really sure (I'm by no means an expert) but shouldn't this be possible? UPDATE test set i_test=item.i_itemID WHERE test.i_ID=item.i_itemID; I'm getting this reply: MySQL said: Unknown table 'item' in where clause while of course table 'items' exists... I use mySQL service at my webhosting, so I don't have the control over the database. Thanx Marc __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2003: Can't connect to MySQL server on ....
Hello list: Just finished installing mySQL on a Linux Debian system and I have a question about how the 2 root users that are set up by default. When I look at my user table there is a root user allowed to log in from localhost and also another root user but this one is only allowed to log in from host.mydomain.com (host being the computer that the mySQL server is installed in). I understand the difference since localhost is when you are working on the computer itself (or remotely through ssh) that has the mySQl server installed and that HOST.MYDOMAIN.COM is the name of the localhost machine on the LAN. However, if you are accessing the mySQL server from HOST.MYDOMAIN.COM isn't that technically the same thing as accessing from localhost? since in order to access the mySQL server from HOST.MYDOMAIN.COM I would have to be at the machine itself wouldn't the server just treat that connection as localhost? I have read on the mysql.com site about how the mySQL server sorts the entries in the user table at the time the server is started by priority of specific entries to less specific ones, however if I have a password set for both [EMAIL PROTECTED] and [EMAIL PROTECTED] then which connection would be used? Not that it matters since the set of privileges are the same. I am just really wondering if I need the extra entry of [EMAIL PROTECTED] when I already have [EMAIL PROTECTED] And how would I ever connect to the mySQL server using the [EMAIL PROTECTED] account? Also I was trying to access the mySQL server using the root@ HOST.MYDOMAIN.COM account by using the command below: Shell mysql -u root -h ZEUS.OLYMPUS.LOCAL -p Enter Password: *** And this is what I get: ERROR 2003: Can't connect to MySQL server on 'ZEUS.OLYMPUS.LOCAL' (111) I also tried using the IP of the server: Shell mysql -u root -h 192.168.1.8 -p Enter Password: *** And I still got: ERROR 2003: Can't connect to MySQL server on '192.168.1.8' (111) What does that mean? I tried searching on the list archives but found nothing on it. And I did check my /etc/hosts file and this is what is in it: 127.0.0.1 localhost 192.168.1.8 ZEUS.OLYMPUS.LOCAL ZEUS Any help would be appreciated it, thanx!
Re: ERROR 2003: Can't connect to MySQL server on ....
Francisco Castellon schrieb: Hello list: Just finished installing mySQL on a Linux Debian system and I have a question about how the 2 root users that are set up by default. When I look at my user table there is a root user allowed to log in from localhost and also another root user but this one is only allowed to log in from host.mydomain.com (host being the computer that the mySQL server is installed in). I understand the difference since localhost is when you are working on the computer itself (or remotely through ssh) that has the mySQl server installed and that HOST.MYDOMAIN.COM is the name of the localhost machine on the LAN. However, if you are accessing the mySQL server from HOST.MYDOMAIN.COM isn't that technically the same thing as accessing from localhost? since in order to access the mySQL server from HOST.MYDOMAIN.COM I would have to be at the machine itself wouldn't the server just treat that connection as localhost? I have read on the mysql.com site about how the mySQL server sorts the entries in the user table at the time the server is started by priority of specific entries to less specific ones, however if I have a password set for both [EMAIL PROTECTED] and [EMAIL PROTECTED] then which connection would be used? Not that it matters since the set of privileges are the same. I am just really wondering if I need the extra entry of [EMAIL PROTECTED] when I already have [EMAIL PROTECTED] And how would I ever connect to the mySQL server using the [EMAIL PROTECTED] account? Also I was trying to access the mySQL server using the root@ HOST.MYDOMAIN.COM account by using the command below: Shell mysql -u root -h ZEUS.OLYMPUS.LOCAL -p Enter Password: *** And this is what I get: ERROR 2003: Can't connect to MySQL server on 'ZEUS.OLYMPUS.LOCAL' (111) I also tried using the IP of the server: Shell mysql -u root -h 192.168.1.8 -p Enter Password: *** And I still got: ERROR 2003: Can't connect to MySQL server on '192.168.1.8' (111) What does that mean? I tried searching on the list archives but found nothing on it. And I did check my /etc/hosts file and this is what is in it: 127.0.0.1 localhost 192.168.1.8 ZEUS.OLYMPUS.LOCAL ZEUS Any help would be appreciated it, thanx! check it with tcpdump -ilo port 3306 tcpdump -ieth0 port 3306 ... ping 192.168.1.8 telnet 192.168.1.8 3306 nmap -p3306 zeus.olympus.local ... hint $ sed s/ //g endlos.txt endloslang.txt Hellolist: JustfinishedinstallingmySQLonaLinuxDebiansystemandIhavea questionabouthowthe2rootusersthataresetupbydefault.WhenI lookatmyusertablethereisarootuserallowedtologinfrom localhostandalsoanotherrootuserbutthisoneisonlyallowedto loginfromhost.mydomain.com(hostbeingthecomputerthatthemySQL serverisinstalledin).Iunderstandthedifferencesincelocalhostis whenyouareworkingonthecomputeritself(orremotelythroughssh) thathasthemySQlserverinstalledandthatHOST.MYDOMAIN.COMisthe nameofthelocalhostmachineontheLAN.However,ifyouareaccessing themySQLserverfromHOST.MYDOMAIN.COMisn'tthattechnicallythesame thingasaccessingfromlocalhost?sinceinordertoaccessthemySQL serverfromHOST.MYDOMAIN.COMIwouldhavetobeatthemachineitself wouldn'ttheserverjusttreatthatconnectionaslocalhost?Ihaveread onthemysql.comsiteabouthowthemySQLserversortstheentriesin theusertableatthetimetheserverisstartedbypriorityofspecific entriestolessspecificones,howeverifIhaveapasswordsetforboth [EMAIL PROTECTED]@HOST.MYDOMAIN.COMthenwhichconnectionwouldbe used?Notthatitmatterssincethesetofprivilegesarethesame.Iam justreallywonderingifIneedtheextraentryof [EMAIL PROTECTED]@localhost.Andhowwould [EMAIL PROTECTED] account? AlsoIwastryingtoaccessthemySQLserverusingtheroot@ HOST.MYDOMAIN.COMaccountbyusingthecommandbelow: Shellmysql-uroot-hZEUS.OLYMPUS.LOCAL-p EnterPassword:*** AndthisiswhatIget:ERROR2003:Can'tconnecttoMySQLserveron 'ZEUS.OLYMPUS.LOCAL'(111) IalsotriedusingtheIPoftheserver: Shellmysql-uroot-h192.168.1.8-p EnterPassword:*** AndIstillgot:ERROR2003:Can'tconnecttoMySQLserveron '192.168.1.8'(111) Whatdoesthatmean?Itriedsearchingonthelistarchivesbutfound nothingonit.AndIdidcheckmy/etc/hostsfileandthisiswhatisin it: 127.0.0.1localhost 192.168.1.8ZEUS.OLYMPUS.LOCALZEUS Anyhelpwouldbeappreciatedit,thanx! /hint -- shrek-m -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I thought single UPDATE statements were atomic
According to the docs, single update statements are atomic.
So why doesn't this work?
mysql create table t (num INT, UNIQUE (num));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('1');
Query OK, 1 row affected (0.00 sec)
mysql insert into t values ('2');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+--+
| num |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)
mysql update t set num=num+1;
ERROR 1062: Duplicate entry '2' for key 1
mysql
Help!
Regards,
Mark
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ERROR 2003: Can't connect to MySQL server on ....
Hi: Thanx for the response. But yes the ZEUS.OLYMPUS.LOCAL host is in the mysql.user database for the user root. There are 2 entries for the root user, one for [EMAIL PROTECTED] and one fore [EMAIL PROTECTED] and they both have the same global privileges set to ALL. I think that I have to add the IP address as an allowed host though, but I thought that the mySQL server could resolve the IP to the host name? Thanx! -Original Message- From: O'K Web Design [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:06 AM To: Francisco Castellon Subject: Re: ERROR 2003: Can't connect to MySQL server on Hi I am not an expert but I would make all root privileges global and make sure you are allowing log in's by root into any host. It just sounds like ZEUS.OLYMPUS.LOCAL is not on your allowed list. As far as my understanding goes, if you are resolving an address, then it would try to connect using the 'not' local method. Mike - Original Message - From: Francisco Castellon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: June 5, 2003 2:40 AM Subject: ERROR 2003: Can't connect to MySQL server on Hello list: Just finished installing mySQL on a Linux Debian system and I have a question about how the 2 root users that are set up by default. When I look at my user table there is a root user allowed to log in from localhost and also another root user but this one is only allowed to log in from host.mydomain.com (host being the computer that the mySQL server is installed in). I understand the difference since localhost is when you are working on the computer itself (or remotely through ssh) that has the mySQl server installed and that HOST.MYDOMAIN.COM is the name of the localhost machine on the LAN. However, if you are accessing the mySQL server from HOST.MYDOMAIN.COM isn't that technically the same thing as accessing from localhost? since in order to access the mySQL server from HOST.MYDOMAIN.COM I would have to be at the machine itself wouldn't the server just treat that connection as localhost? I have read on the mysql.com site about how the mySQL server sorts the entries in the user table at the time the server is started by priority of specific entries to less specific ones, however if I have a password set for both [EMAIL PROTECTED] and [EMAIL PROTECTED] then which connection would be used? Not that it matters since the set of privileges are the same. I am just really wondering if I need the extra entry of [EMAIL PROTECTED] when I already have [EMAIL PROTECTED] And how would I ever connect to the mySQL server using the [EMAIL PROTECTED] account? Also I was trying to access the mySQL server using the root@ HOST.MYDOMAIN.COM account by using the command below: Shell mysql -u root -h ZEUS.OLYMPUS.LOCAL -p Enter Password: *** And this is what I get: ERROR 2003: Can't connect to MySQL server on 'ZEUS.OLYMPUS.LOCAL' (111) I also tried using the IP of the server: Shell mysql -u root -h 192.168.1.8 -p Enter Password: *** And I still got: ERROR 2003: Can't connect to MySQL server on '192.168.1.8' (111) What does that mean? I tried searching on the list archives but found nothing on it. And I did check my /etc/hosts file and this is what is in it: 127.0.0.1 localhost 192.168.1.8 ZEUS.OLYMPUS.LOCAL ZEUS Any help would be appreciated it, thanx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error
Using InnoDB on a large table ( 100,000,000 rows)... Trying to get a count that should equal about 25,000,000. Wasn't using that much memory. Database crashes Any idea? 030605 19:38:27 InnoDB: Assertion failure in thread 45068 in file row0sel.c line 1977 InnoDB: Failing assertion: len == DATA_ROW_ID_LEN InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=419430400 read_buffer_size=104853504 sort_buffer_size=2097144 max_used_connections=3 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2465391 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87626c0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe3e7b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80741ea 0x829c1e8 0x814db1b 0x80d1a58 0x80d4cbc 0x80a6abd 0x80a0e29 0x80a0ad3 0x80998e0 0x80a630d 0x807ecfa 0x808273b 0x807de2d 0x8083c5e 0x807cfff 0x82c 0x82cd0aa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8773000 = select count(*) from user_new where list_code = 18 thd-thread_id=3 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 3 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I thought single UPDATE statements were atomic
Hi
You are not defining num so you are adding 1 to 0 to get 1 and you
already have that record. Sounds like you need an autoincrement field.
Mike
- Original Message -
From: Mark Rages [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: June 5, 2003 7:52 PM
Subject: I thought single UPDATE statements were atomic
According to the docs, single update statements are atomic.
So why doesn't this work?
mysql create table t (num INT, UNIQUE (num));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('1');
Query OK, 1 row affected (0.00 sec)
mysql insert into t values ('2');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+--+
| num |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)
mysql update t set num=num+1;
ERROR 1062: Duplicate entry '2' for key 1
mysql
Help!
Regards,
Mark
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
start innodb without transactions
Hi all Is there any way to start mysql/innodb skipping transactions ?, i had a crash and i cant get the database to work. i tried the force_recovery option level 3: # 3 (SRV_FORCE_NO_TRX_UNDO) do not run transaction rollbacks after recovery; this way i can get it to work, but as the manual said i cant do any update query while force_recovery is on and also cant do a mysqldump, so im stucked :(. any ideas ? Im using mysql 4.0.4, solaris 8 sparc . thanx Carlos _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2003: Can't connect to MySQL server on ....
Hi Francisco, please login to your mysql server and do this: SHOW GRANTS FOR username; Thats probably the fastes way to troubleshoot this issue. If you really have 2 entries for the same user than t depends on 2 things which one takes precedence 1) which entry is mor specific or 2) which order they come in the mysqls privilege system. There are two reports on devshed which I believe are an excellent source. There is really nothing to add. Print them off read them carefully on your way home and tomorrrow you will be 2 steps further. http://www.devshed.com/Server_Side/MySQL/Access/page1.html http://www.devshed.com/Server_Side/MySQL/Grant_Tables/page1.html http://www.devshed.com/Server_Side/MySQL/Administration/page1.html They also offer printer friendly formats. I hope you dont think ... hmm just another document ;-). They are really worth the time. Please let me know the output of SHOW GRANTS FOR username; I am am confident to be able to help you quickly knowing the ouptut. Best regards Nils Valentin Tokyo/Japan 2003 6 6 09:41Francisco Castellon : Hi: Thanx for the response. But yes the ZEUS.OLYMPUS.LOCAL host is in the mysql.user database for the user root. There are 2 entries for the root user, one for [EMAIL PROTECTED] and one fore [EMAIL PROTECTED] and they both have the same global privileges set to ALL. I think that I have to add the IP address as an allowed host though, but I thought that the mySQL server could resolve the IP to the host name? Thanx! -Original Message- From: O'K Web Design [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:06 AM To: Francisco Castellon Subject: Re: ERROR 2003: Can't connect to MySQL server on Hi I am not an expert but I would make all root privileges global and make sure you are allowing log in's by root into any host. It just sounds like ZEUS.OLYMPUS.LOCAL is not on your allowed list. As far as my understanding goes, if you are resolving an address, then it would try to connect using the 'not' local method. Mike - Original Message - From: Francisco Castellon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: June 5, 2003 2:40 AM Subject: ERROR 2003: Can't connect to MySQL server on Hello list: Just finished installing mySQL on a Linux Debian system and I have a question about how the 2 root users that are set up by default. When I look at my user table there is a root user allowed to log in from localhost and also another root user but this one is only allowed to log in from host.mydomain.com (host being the computer that the mySQL server is installed in). I understand the difference since localhost is when you are working on the computer itself (or remotely through ssh) that has the mySQl server installed and that HOST.MYDOMAIN.COM is the name of the localhost machine on the LAN. However, if you are accessing the mySQL server from HOST.MYDOMAIN.COM isn't that technically the same thing as accessing from localhost? since in order to access the mySQL server from HOST.MYDOMAIN.COM I would have to be at the machine itself wouldn't the server just treat that connection as localhost? I have read on the mysql.com site about how the mySQL server sorts the entries in the user table at the time the server is started by priority of specific entries to less specific ones, however if I have a password set for both [EMAIL PROTECTED] and [EMAIL PROTECTED] then which connection would be used? Not that it matters since the set of privileges are the same. I am just really wondering if I need the extra entry of [EMAIL PROTECTED] when I already have [EMAIL PROTECTED] And how would I ever connect to the mySQL server using the [EMAIL PROTECTED] account? Also I was trying to access the mySQL server using the root@ HOST.MYDOMAIN.COM account by using the command below: Shell mysql -u root -h ZEUS.OLYMPUS.LOCAL -p Enter Password: *** And this is what I get: ERROR 2003: Can't connect to MySQL server on 'ZEUS.OLYMPUS.LOCAL' (111) I also tried using the IP of the server: Shell mysql -u root -h 192.168.1.8 -p Enter Password: *** And I still got: ERROR 2003: Can't connect to MySQL server on '192.168.1.8' (111) What does that mean? I tried searching on the list archives but found nothing on it. And I did check my /etc/hosts file and this is what is in it: 127.0.0.1 localhost 192.168.1.8 ZEUS.OLYMPUS.LOCAL ZEUS Any help would be appreciated it, thanx! -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I thought single UPDATE statements were atomic
On Thu, Jun 05, 2003 at 09:06:43PM -0400, O'K Web Design wrote:
Hi
You are not defining num so you are adding 1 to 0 to get 1 and you
already have that record. Sounds like you need an autoincrement field.
Mike
I don't need an autoincrement field. In my actual application, the num
is a sequence index that determines the order of some rows. When I want
to reorder the rows, I need to change a series of numbers in this way.
In my example I'm trying to take the table from one valid state to
another valid state... if this syntax is wrong, how can I do it?
Regards,
Mark
[EMAIL PROTECTED]
- Original Message -
From: Mark Rages [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: June 5, 2003 7:52 PM
Subject: I thought single UPDATE statements were atomic
According to the docs, single update statements are atomic.
So why doesn't this work?
mysql create table t (num INT, UNIQUE (num));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('1');
Query OK, 1 row affected (0.00 sec)
mysql insert into t values ('2');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+--+
| num |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)
mysql update t set num=num+1;
ERROR 1062: Duplicate entry '2' for key 1
mysql
Help!
Regards,
Mark
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error
John, what do SHOW CREATE TABLE user_new; and explain select count(*) from user_new where list_code = 18; and CHECK TABLE user_new; (this may last hours and will also print to the .err log) say? Which MySQL version you are running and on what Linux distro? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: John Brooks [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, June 06, 2003 3:59 AM Subject: error Using InnoDB on a large table ( 100,000,000 rows)... Trying to get a count that should equal about 25,000,000. Wasn't using that much memory. Database crashes Any idea? 030605 19:38:27 InnoDB: Assertion failure in thread 45068 in file row0sel.c line 1977 InnoDB: Failing assertion: len == DATA_ROW_ID_LEN InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=419430400 read_buffer_size=104853504 sort_buffer_size=2097144 max_used_connections=3 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2465391 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87626c0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe3e7b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80741ea 0x829c1e8 0x814db1b 0x80d1a58 0x80d4cbc 0x80a6abd 0x80a0e29 0x80a0ad3 0x80998e0 0x80a630d 0x807ecfa 0x808273b 0x807de2d 0x8083c5e 0x807cfff 0x82c 0x82cd0aa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8773000 = select count(*) from user_new where list_code = 18 thd-thread_id=3 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 3 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: start innodb without transactions
Carlos, - Original Message - From: Carlos Proal [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, June 06, 2003 4:04 AM Subject: start innodb without transactions Hi all Is there any way to start mysql/innodb skipping transactions ?, i had a crash and i cant get the database to work. i tried the force_recovery option level 3: # 3 (SRV_FORCE_NO_TRX_UNDO) do not run transaction rollbacks after recovery; this way i can get it to work, but as the manual said i cant do any update query while force_recovery is on and also cant do a mysqldump, so im stucked :(. why you cannot do a mysqldump? innodb_force_recovery does not prevent SELECT queries. any ideas ? Im using mysql 4.0.4, solaris 8 sparc . Upgrade to 4.0.13. Also send me the .err log. Database corruption is uncommon in Solaris, interesting to see what is in the .err log. thanx Carlos Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't open privilege tables: Table 'mysql.host' doesn't exist
Hi Heikki, Thank you for confirming what I was also thinking about. I realised that when specifying datadir=/usr/local/mysql that the server doesnt start up. Knowing this I also realised that I still have some need to learn ;-). I already have some more questions, but I will go back to studies first before posting anything silly ;-). Anyway thank you for the reply. Best regards Nils Valentin Tokyo/Japan 2003 6 4 21:09Heikki Tuuri : Nils, - Original Message - From: Nils Valentin [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, June 04, 2003 7:05 AM Subject: Re: Can't open privilege tables: Table 'mysql.host' doesn't exist Sorry, I should have given more details.=20 Its Mysql 4.1 alpha on a Suse Linux system Version 8.1. =2E..and yes, the mysql.host table and the other 5 exist and are well alive= ;-). If I replace my.cnf with a version skipping innodb then it perfectly starts= =20 up, so it must be related to innodb, perhaps my.cnf and/or mysql itself. the problem is in your my.cnf or the access rights of the person running mysqld. The error means mysqld cannot access the file host.frm in the mysql subdir under the datadir. I suggest specifying the datadir explicitly in your my.cnf. Best regards Nils Valentin Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ Hi Mysql list members, I added a Innodb database to the existing MyISAM databases and now MySQL doesnt start up. From the InnoDB logfile I saw this error message Can't open privilege tables: Table 'mysql.host' doesn't exist Does any body know what this error comes from ? innodb logfile extract. 030603 16:46:20 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 80 MB InnoDB: Database physically writes the file full: wait... 030603 16:46:31 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 80 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 030603 16:46:48 InnoDB: Started 030603 16:46:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 030603 16:46:49 mysqld ended . 030604 09:30:28 mysqld started 030604 9:30:47 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43892 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 030604 9:30:48 InnoDB: Flushing modified pages from the buffer pool... 030604 9:30:49 InnoDB: Started 030604 9:30:49 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 030604 09:30:49 mysqld ended Any reply much appreciated. -- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =2D-=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error
Using InnoDB on a large table ( 100,000,000 rows)... Trying to get a count that should equal about 25,000,000. Wasn't using that much memory. Database crashes Any idea? 030605 19:38:27 InnoDB: Assertion failure in thread 45068 in file row0sel.c line 1977 InnoDB: Failing assertion: len == DATA_ROW_ID_LEN InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=419430400 read_buffer_size=104853504 sort_buffer_size=2097144 max_used_connections=3 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2465391 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87626c0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe3e7b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80741ea 0x829c1e8 0x814db1b 0x80d1a58 0x80d4cbc 0x80a6abd 0x80a0e29 0x80a0ad3 0x80998e0 0x80a630d 0x807ecfa 0x808273b 0x807de2d 0x8083c5e 0x807cfff 0x82c 0x82cd0aa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8773000 = select count(*) from user_new where list_code = 18 thd-thread_id=3 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 3 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I thought single UPDATE statements were atomic
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark Rages wrote: On Thu, Jun 05, 2003 at 09:06:43PM -0400, O'K Web Design wrote: Hi You are not defining num so you are adding 1 to 0 to get 1 and you already have that record. Sounds like you need an autoincrement field. Mike I don't need an autoincrement field. In my actual application, the num is a sequence index that determines the order of some rows. When I want to reorder the rows, I need to change a series of numbers in this way. In my example I'm trying to take the table from one valid state to another valid state... if this syntax is wrong, how can I do it? How about UPDATE ... ORDER BY num DESC ??? Do the opposite, if you're moving your rows 'down'. See http://www.mysql.com/doc/en/UPDATE.html -Mark - -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, SW Dev. Manager - J2EE/Windows /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE+3/IwtvXNTca6JD8RAj5cAJ9fDPfy9WT07D/xltk9ooxUke4CdACeLuU6 EJG1xLn6UYl6ZxGjVqKqUk8= =DbtX -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisamchk - error 28
I am using mysql 3.23.54 and am trying to repair the index, using myisamchk -o tablename.MY and am getting the following error: - recovering (with keycache) MyISAM-table 'msg.MYI' Data records: 498802 myisamchk: Error writing file 'msg.TMD' (Errcode: 28) myisamchk: error: 28 when writing to datafile MyISAM-table 'msg.MYI' is not fixed because of errors Try fixing it by using the --safe-recover (-o) or the --force (-f) option However, I have tried -o -f, -r, -o, etc. and I always get the same error. Has anyone run into this, and if so, any idea how I get around it? Fraser On Thu, 5 Jun 2003, John Brooks wrote: Using InnoDB on a large table ( 100,000,000 rows)... Trying to get a count that should equal about 25,000,000. Wasn't using that much memory. Database crashes Any idea? 030605 19:38:27 InnoDB: Assertion failure in thread 45068 in file row0sel.c line 1977 InnoDB: Failing assertion: len == DATA_ROW_ID_LEN InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=419430400 read_buffer_size=104853504 sort_buffer_size=2097144 max_used_connections=3 max_connections=100 threads_connected=3 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2465391 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x87626c0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfe3e7b8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80741ea 0x829c1e8 0x814db1b 0x80d1a58 0x80d4cbc 0x80a6abd 0x80a0e29 0x80a0ad3 0x80998e0 0x80a630d 0x807ecfa 0x808273b 0x807de2d 0x8083c5e 0x807cfff 0x82c 0x82cd0aa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x8773000 = select count(*) from user_new where list_code = 18 thd-thread_id=3 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 3 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid. The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the crash. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I am hanging
I just loaded mySql on windows98. I try to run it and I get the following messages: C:\mysql\binmysqld --standalone --console 030605 19:10:46 InnoDB: Started C:\MYSQL\BIN\MYSQLD.EXE: ready for connections. Version: '4.0.13-max-debug' socket: '' port: 3306 I do not get a prompt back. I assume it is hanging? Any idea how to correct this problem? Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Installation - libmysqlclient.so (Redhat 8)
Hi All, I know I'm re-posting but there must be someone out there who can explain. Please Help. Thanks Cheers, Mun Heng, Ow H/M Engineering Western Digital M'sia DID : 03-7870 5168 -Original Message- From: Ow Mun Heng Sent: Thursday, June 05, 2003 9:47 AM To: [EMAIL PROTECTED] Subject: Installation - libmysqlclient.so (Redhat 8) Hi All, Newbie question. Stock Standard Redhat 8.0 install with MySQL 3.23. Have not started using, so thought I can upgrade to Version 4.0 it before I face more problems later on. RPM -e mysql* libmysqlclient.so.10 needed by perl-DBD-MySQL from the Mysql manual, it says that we can install MySQL-shared-compat-xx.rpm which will install the libmysqlclient.so.10 and libmysqlclient.so.12 and maintain dependency. As such, before I go any further can I # rpm -e --nodeps mysql-server mysql # rpm -ivh MySQL-server MySQL-shared-compat MySQL-client would this do? I've downloaded the rpm from MySQL.com PS : Does this rpm installs to the *SAME* location as Redhat RPMs? DO I Upgrade or install? (rpm -U or Rpm -i since this is not Redhat RPM) Thanks Cheers, Mun Heng, Ow H/M Engineering Western Digital M'sia DID : 03-7870 5168 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I am hanging
At 21:46 -0400 6/5/03, Richard Adler wrote: I just loaded mySql on windows98. I try to run it and I get the following messages: C:\mysql\binmysqld --standalone --console 030605 19:10:46 InnoDB: Started C:\MYSQL\BIN\MYSQLD.EXE: ready for connections. Version: '4.0.13-max-debug' socket: '' port: 3306 I do not get a prompt back. I assume it is hanging? No. It's running. Open a new console window and run a client program to see if you can connect to it. When you shut it down, e.g., with: mysqladmin -u root shutdown the server will exit and another prompt will be displayed. Any idea how to correct this problem? Regards, Rich -- Paul DuBois http://www.kitebird.com/ sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this not possible???
At 14:30 +0100 6/5/03, Moj Bordel wrote: Hello, I'm not really sure (I'm by no means an expert) but shouldn't this be possible? UPDATE test set i_test=item.i_itemID WHERE test.i_ID=item.i_itemID; I'm getting this reply: MySQL said: Unknown table 'item' in where clause while of course table 'items' exists... I use mySQL service at my webhosting, so I don't have the control over the database. No statement is possible if you use incorrect syntax. :-) Assuming you have a version of MySQL recent enough to support multiple-table updates, it's necessary to name all the tables before the SET keyword. Try: UPDATE test, item set i_test=item.i_itemID WHERE test.i_ID=item.i_itemID; Thanx Marc -- Paul DuBois http://www.kitebird.com/ sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I thought single UPDATE statements were atomic
At 18:52 -0500 6/5/03, Mark Rages wrote:
According to the docs, single update statements are atomic.
That's correct.
So why doesn't this work?
mysql create table t (num INT, UNIQUE (num));
Query OK, 0 rows affected (0.00 sec)
mysql insert into t values ('1');
Query OK, 1 row affected (0.00 sec)
mysql insert into t values ('2');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+--+
| num |
+--+
|1 |
|2 |
+--+
2 rows in set (0.00 sec)
mysql update t set num=num+1;
ERROR 1062: Duplicate entry '2' for key 1
mysql
Consider what happens if MySQL tries to update the first record and
then the second, version what happens if it tries to update the second
record and then the first.
Then add an ORDER BY clause that will cause MySQL to update the records
in the order that doesn't result in duplicate keys.
Help!
Regards,
Mark
[EMAIL PROTECTED]
--
Paul DuBois
http://www.kitebird.com/
sql, query
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
establishing a relationship
I have a friend teaching me but I want more :-) (second opinions), please. A simple Database: 2 tables a one-to-many relationship each table has a Primary Key: table1 (one) Primary Key = peopleID; table2 (many) Primary Key = machinesID Normally, I would put the peopleID also in table2 as a Foreign key to establish the relationship and be done with it. I am being taught now to create a third table, table3, and in it have 2 columns; those being the peopleID and machinesID (the Primary keys from the other 2 tables). This is apparently a good idea when it comes to deleting or updating records. (?) My question is, how is the relationship between table1 and table2 established using this method? I hope you understand my question. If I try to explain further it will only become convoluted, possibly more than it is! Thanks, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: establishing a relationship
Ted, If in fact you only have a one-to-many relationship, you don't need the third table (what's also known as a join table in some circles). This type of table is only really needed if you are doing a many-to-many (people have 0 to infinity machines, and machines have 0 to infinity people). I fail to see how adding a layer of complexity would help when deleting or updating records. Unless, of course, you are doing transaction logging manually. Which might be necessary with some DBMS products, but I don't think you need to in MySQL because you have the binary and other logs available. But then, you would need more information in the third table than just the two primary keys. I wouldn't mind hearing from your friend about why this type of set up is beneficial. jeff On Fri, 06 Jun 2003 02:14:37 -0400, [EMAIL PROTECTED] wrote: I have a friend teaching me but I want more :-) (second opinions), please. A simple Database: 2 tables a one-to-many relationship each table has a Primary Key: table1 (one) Primary Key = peopleID; table2 (many) Primary Key = machinesID Normally, I would put the peopleID also in table2 as a Foreign key to establish the relationship and be done with it. I am being taught now to create a third table, table3, and in it have 2 columns; those being the peopleID and machinesID (the Primary keys from the other 2 tables). This is apparently a good idea when it comes to deleting or updating records. (?) My question is, how is the relationship between table1 and table2 established using this method? I hope you understand my question. If I try to explain further it will only become convoluted, possibly more than it is! Thanks, Ted Rogers --- Listserv only address. Jeff Shapiro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: establishing a relationship
Yes, a linking table or a map table or a join table. Linking is what I see it called in books I have; yes I understand that. I hope to understand the advice for a third more thoroughly, too. I am doing this for practice so the theory of what I'm being taught is what I'm after and afaik it must be more than the linking of many-to-many (busting up a many-to-many as I like to call it. ;) Thanks, more is always welcome. Ted On Friday, June 6, 2003, at 02:31 AM, Jeff Shapiro wrote: Ted, If in fact you only have a one-to-many relationship, you don't need the third table (what's also known as a join table in some circles). This type of table is only really needed if you are doing a many-to-many (people have 0 to infinity machines, and machines have 0 to infinity people). I fail to see how adding a layer of complexity would help when deleting or updating records. Unless, of course, you are doing transaction logging manually. Which might be necessary with some DBMS products, but I don't think you need to in MySQL because you have the binary and other logs available. But then, you would need more information in the third table than just the two primary keys. I wouldn't mind hearing from your friend about why this type of set up is beneficial. jeff On Fri, 06 Jun 2003 02:14:37 -0400, [EMAIL PROTECTED] wrote: I have a friend teaching me but I want more :-) (second opinions), please. A simple Database: 2 tables a one-to-many relationship each table has a Primary Key: table1 (one) Primary Key = peopleID; table2 (many) Primary Key = machinesID Normally, I would put the peopleID also in table2 as a Foreign key to establish the relationship and be done with it. I am being taught now to create a third table, table3, and in it have 2 columns; those being the peopleID and machinesID (the Primary keys from the other 2 tables). This is apparently a good idea when it comes to deleting or updating records. (?) My question is, how is the relationship between table1 and table2 established using this method? I hope you understand my question. If I try to explain further it will only become convoluted, possibly more than it is! Thanks, Ted Rogers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
index failure, cannot generate.
Hi all. I am running RH9.0 with MySQL 4.0.13 I am trying to create a fulltext index on a 1.5GB table with 400 records. Whenever I do a create index (retried this a couple of times), the index size grows to only 8MB before the index completes. When using this index in fulltext searches, the search takes 1.5 Minutes. It seems to me like MySQL fails to generate the index correctly. Is this a bug ? Does anyone know what's going on ? Thanks in advance. Herbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
server has gone away Error
I' m developing a client application using C-Api functions . Server and Client reside on the same machine, so I connect the Server via Unix socket . If I call the mysql_real_query after the wait_timeout expired on the server side , my client crashes. The Manual speaks about the ability of catch the server has gone away Error, Why I haven't ?
Re: I am hanging
Richard, I just loaded mySql on windows98. I try to run it and I get the following messages: C:\mysql\binmysqld --standalone --console 030605 19:10:46 InnoDB: Started C:\MYSQL\BIN\MYSQLD.EXE: ready for connections. Version: '4.0.13-max-debug' socket: '' port: 3306 I do not get a prompt back. I assume it is hanging? No, it's not hanging, it's just a server that provides no interfaces for humans, so you can watch it blinking in your DOS window forever ... Use a client program (in another DOS window) to connect to the server, after you started that server successfully (which you did). You can do something like this: C:\mysql\bin mysql That client program called mysql (note: not mysqld!) connects to the server and gives you a prompt. Good luck! Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld CPU usage is almost 100% !!!
Hi, I'm using GNU/Linux Slackware 8.0 and not using any my.cnf (so the configs are all at default).. Is it possible that mysql is doing some background tasks while the system is in idle state ? thanks -- Original Message -- From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Wed, 4 Jun 2003 20:10:40 -0700 On Thu, Jun 05, 2003 at 10:05:00AM +0700, nino wrote: Hi folks, I'm experiencing some phenomenon that mysqld is consuming CPU at almost 100%. At this time, I only assemble a few database with record size is about 37000 rows. Is mysqld likely gonna crash or something ?? I also noticed that, if some other mysqld thread is active, the mysqld which consume almost 100% CPU is decreasing its CPU usage a little bit, and sometimes the CPU usage is normal. What operating system do you use? And which verison of MySQL? Can we see your my.cnf file? Have you enabled the slow query log to see what's slowing you down? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 1 days, processed 66,202,387 queries (415/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld CPU usage is almost 100% !!!
Neither am I, I'm not ODBC literate :) Is there any answer relating my problem (mysqld CPU consume) with the way mysqld doing its background tasks ?? Or, is it normal that mysqld sometimes consuming my CPU at some 99.9% ?? -- Original Message -- From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Wed, 4 Jun 2003 23:14:23 -0700 On Thu, Jun 05, 2003 at 11:33:23AM +0800, Ahmad Rezal Ibrahim @ PNSB wrote: check either your using ODBC - trace is on ? How would that impact the server? I'm not terrible ODBC-literate, so I don't know. Jeremy -Original Message- From: nino [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 11:05 AM To: milis mysql Subject: mysqld CPU usage is almost 100% !!! Hi folks, I'm experiencing some phenomenon that mysqld is consuming CPU at almost 100%. At this time, I only assemble a few database with record size is about 37000 rows. Is mysqld likely gonna crash or something ?? I also noticed that, if some other mysqld thread is active, the mysqld which consume almost 100% CPU is decreasing its CPU usage a little bit, and sometimes the CPU usage is normal. Is it because my record size grows ? Anyway, it's only 37000 rows please help me, thanks in advance --nino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 1 days, processed 67,889,776 queries (398/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is this not possible???
As my webspace provider uses version 3.23.50 and multiple-table updates are available starting with MySQL Version 4.0.4, is there any way how to do that with an older version of MySQL? Thanx Marc --- Paul DuBois [EMAIL PROTECTED] wrote: At 14:30 +0100 6/5/03, Moj Bordel wrote: Hello, I'm not really sure (I'm by no means an expert) but shouldn't this be possible? UPDATE test set i_test=item.i_itemID WHERE test.i_ID=item.i_itemID; I'm getting this reply: MySQL said: Unknown table 'item' in where clause while of course table 'items' exists... I use mySQL service at my webhosting, so I don't have the control over the database. No statement is possible if you use incorrect syntax. :-) Assuming you have a version of MySQL recent enough to support multiple-table updates, it's necessary to name all the tables before the SET keyword. Try: UPDATE test, item set i_test=item.i_itemID WHERE test.i_ID=item.i_itemID; Thanx Marc -- Paul DuBois http://www.kitebird.com/ sql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Yahoo! Plus For a better Internet experience http://www.yahoo.co.uk/btoffer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqld CPU usage is almost 100% !!!
I'm no Linux expert (barely even a novice) but, from my Windows experience I think that most applications will from time to time do this sort of thing. What is important is how long it does it for! If its a brief surge, its just trying to use the resources to complete the task quickly. If it takes a while, then there's a problem somewhere. I'm currently having a similar problem with 40 minute surges on Filemaker which is giving me a real headache as its going to be a few months before my MySQL replacement is ready. Just my 2c (2p actually) George in Oxford -Original Message- From: nino [mailto:[EMAIL PROTECTED] Sent: 06 June 2003 8:48 am To: [EMAIL PROTECTED] Subject: Re: mysqld CPU usage is almost 100% !!! Neither am I, I'm not ODBC literate :) Is there any answer relating my problem (mysqld CPU consume) with the way mysqld doing its background tasks ?? Or, is it normal that mysqld sometimes consuming my CPU at some 99.9% ?? -- Original Message -- From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Wed, 4 Jun 2003 23:14:23 -0700 On Thu, Jun 05, 2003 at 11:33:23AM +0800, Ahmad Rezal Ibrahim @ PNSB wrote: check either your using ODBC - trace is on ? How would that impact the server? I'm not terrible ODBC-literate, so I don't know. Jeremy -Original Message- From: nino [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 11:05 AM To: milis mysql Subject: mysqld CPU usage is almost 100% !!! Hi folks, I'm experiencing some phenomenon that mysqld is consuming CPU at almost 100%. At this time, I only assemble a few database with record size is about 37000 rows. Is mysqld likely gonna crash or something ?? I also noticed that, if some other mysqld thread is active, the mysqld which consume almost 100% CPU is decreasing its CPU usage a little bit, and sometimes the CPU usage is normal. Is it because my record size grows ? Anyway, it's only 37000 rows please help me, thanks in advance --nino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 1 days, processed 67,889,776 queries (398/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld CPU usage is almost 100% !!!
Nino, are you using InnoDB tables? InnoDB does background purge and insert buffer merge. What does SHOW INNODB STATUS say during that spike? Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: nino [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, June 06, 2003 10:41 AM Subject: Re: mysqld CPU usage is almost 100% !!! Neither am I, I'm not ODBC literate :) Is there any answer relating my problem (mysqld CPU consume) with the way mysqld doing its background tasks ?? Or, is it normal that mysqld sometimes consuming my CPU at some 99.9% ?? -- Original Message -- From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Wed, 4 Jun 2003 23:14:23 -0700 On Thu, Jun 05, 2003 at 11:33:23AM +0800, Ahmad Rezal Ibrahim @ PNSB wrote: check either your using ODBC - trace is on ? How would that impact the server? I'm not terrible ODBC-literate, so I don't know. Jeremy -Original Message- From: nino [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 11:05 AM To: milis mysql Subject: mysqld CPU usage is almost 100% !!! Hi folks, I'm experiencing some phenomenon that mysqld is consuming CPU at almost 100%. At this time, I only assemble a few database with record size is about 37000 rows. Is mysqld likely gonna crash or something ?? I also noticed that, if some other mysqld thread is active, the mysqld which consume almost 100% CPU is decreasing its CPU usage a little bit, and sometimes the CPU usage is normal. Is it because my record size grows ? Anyway, it's only 37000 rows please help me, thanks in advance --nino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 1 days, processed 67,889,776 queries (398/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL server 4.1 getColumns ()
Hello, I am working on a web application using tomcat 4.1 j2sdk 1.4.1 on Red Hat 7.2. This app get the following error in a request to a database on MySql server 4.1. java.lang.StringIndexOutOfBoundsException: String index out of range: -1 at java.lang.String.substring(String.java:1474) at com.mysql.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:877) I use Jconnector 3.0.8 as the JDBC driver. I have seen that others had this problem but I could not see if it was solved or not. Thanks for any help. Thierry Boucheny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld CPU usage is almost 100% !!!
Hi, I'm using GNU/Linux Slackware 8.0 and not using any my.cnf (so the configs are all at default).. Is it possible that mysql is doing some background tasks while the system is in idle state ? Which MySQL version did you use ? Some old mysql binary were compiled with wrong glibc and have this kind of problem ... Around 3.23.50, 3.23.51 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 different Mysql DB (in 2 diff dir) on the same server
Hi, may be I can add some details to the mail attached below: As I said I Installed mysql by phpdev423. On my PC (Win2000) I found out only the configurations file my.cnf.OLD on C:/ and on C:\Programs\phpdev423\php\sapi\install; no my.ini were found; therefore I suppose that in this moment no option file is used from mysql. When I created the new DB I simply did it by PhpMyAdmin Tool, and it was created in ...\phpdev423\mysql\data; Now, if I want to have 2 different tables (or DB) in 2 different HD what can I do? Do I have to use a conf file? In which dir do I have to put in? DO I have to rename it? And moreover, what I have to write inside? I will appreciate if you can help me... Thank You very much in Advance Fausto - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:55 AM Subject: 2 different Mysql DB on the same server Hi to all, I need to have on the same PC (that is the Server) 2 different mysql DBs; I need it because I need some data on the standard mysql dir (for example ...\mysql\data\) and some data on a different dir. I installed mysql simply installing phpdev423; therefore I found out automatically the DB data on a precise dir (...\phpdev423\mysql\data); when I created a new database automatically the data were put inside the dir ...\phpdev423\mysql\data\name_new_database\. Now I have to create a new DB on a totally different dir (and different HD on the same PC): how can I do it? is there a .ini or .cnf file were I can specify the path? And moreover, can I specified 2 different paths because I need 2 different DBs on 2 different dir? Thak You in Advance for any suggestion Fausto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: threads not being killed
Hail Jeremy!! my kernel is new as releng5_0 (release + security patches). I saw your Yahoo! patch in the source with 4.0.13. My tomcat clients and windows ODBC are all ancient (still 3.XX) Thanks for your help! PS: thnaks for being our guru in mysql/freebsd... Your blogs saved our asses many times :) - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Gustavo A. Baratto [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 05, 2003 8:06 PM Subject: Re: threads not being killed On Thu, Jun 05, 2003 at 10:01:23AM +, Gustavo A. Baratto wrote: Greetings, we running mysql 4.0.13 compiled statically with linuxthreads on freebsd 5.0 (SMP). The problem is that a few connections are not dying after the wait_timeout and interactive_timeout expired. The only pattern we can see here is that mysql clients like jakarta-tomcat (jdbc), and windows (obdc) are the ones not that are not dying. Another thing is that these are the only clients based on 3.XX libmysqlclient... We are not having problem so far with php and perl clients compiled with 4.XX libs. I searched the manual for answers and nothing... The manual does say that we shouldn't have any problem with old 3.XX clients except for using the new privileges provided by 4.XX. I don't really know if this is a freebsd problem as described in http://jeremy.zawodny.com/blog/archives/000697.html or the old mysql client. Any ideas? Sounds like the same bug to me. How recent is your 5.0 kernel? I don't recall exactly when the patch went in. 4.0.13 should have an adjustment in vio/vio.c that looks like this: fcntl(sd, F_SETFL, vio-fcntl_mode); /* Yahoo! FreeBSD patch */ That works around the problem to. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 2 days, processed 96,077,464 queries (391/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: start innodb without transactions
Carlos,
please send me your my.cnf. How big is the combined size of your log files?
MySQL/InnoDB-4.0.9, January 14, 2003
Fixed a bug: if the combined size of InnoDB log files was = 2 GB in a
32-bit computer, InnoDB would write log in a wrong position. That could make
crash recovery and InnoDB Hot Backup to fail.
In the first printout:
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 13 1000286684
InnoDB: Doing recovery: scanned up to log sequence number 13 1000286208
looks like InnoDB is not able to scan the log file at all, not even up to
the checkpoint!
InnoDB: log sequence number 13 1002344016
InnoDB: Doing recovery: scanned up to log sequence number 13 1002343936
The same in the next printout. It has written some 2 MB more log but cannot
after a crash scan the log at all!
The failing assertion checks when freeing a BLOB or TEXT field that its
length is right:
if (extern_len - part_len == 0) {
ut_a(next_page_no == FIL_NULL);
}
This could be:
1) a bug in InnoDB's log writing;
2) hardware fault, broken disk;
3) serious corruption of the OS file system.
If InnoDB fails to scan lots of log in crash recovery, it means that the
resulting database may have very extensive corruption. That can explain why
some of your tables are not visible at all.
There is little hope of recovering your tables. You should resort to a
backup and upgrade to 4.0.13. Then do some heavy processing, for example, a
table import, and crash mysqld artificially by
killall -9 mysqld
Look then if InnoDB is able to scan the log in crash recovery.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: Carlos Proal [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Friday, June 06, 2003 7:41 AM
Subject: Re: start innodb without transactions
Probably i need to describe the whole story:
I did a huge insert, a file about 100Mb, and mysql crashed/restarted, the
err file showed:
--
030605 12:11:59 mysqld restarted
030605 12:12:00 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 13 1000286684
InnoDB: Doing recovery: scanned up to log sequence number 13 1000286208
InnoDB: Last MySQL binlog file position 0 756499, file name
./catarina-bin.057
030605 12:12:01 InnoDB: Flushing modified pages from the buffer pool...
030605 12:12:01 InnoDB: Started
/centia01/final/database/mysql/libexec/mysqld: ready for connections
030605 12:14:01 InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
mysqld got signal 10;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.
key_buffer_size=67104768
read_buffer_size=131072
sort_buffer_size=524280
max_used_connections=4
max_connections=500
threads_connected=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =
385528
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
--
(i have 256Mb in buffer pool) But the next restart stopped mysql showing:
--
030605 12:14:30 mysqld restarted
030605 12:14:32 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 13 1002344016
InnoDB: Doing recovery: scanned up to log sequence number 13 1002343936
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: Trx id counter is 0 119167744
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 119167355
030605 12:14:32 InnoDB: Assertion failure in thread 1 in file btr0cur.c
line 3350
InnoDB: We intentionally generate a memory trap.
InnoDB: Send a detailed bug report to [EMAIL PROTECTED]
mysqld got signal 11;
--
i started with force_recovery and indeed i can recover with mysqldump some
databases but i was confused because my first try was with a db that
Re: How to select without using sub-select?
Thanks everyone! Here is a working solution: (Normalizing the tables as O'K Web Design suggested is also a good design solution) Hans-Peter Grimm [EMAIL PROTECTED] [EMAIL PROTECTED] schrieb: I am running MySQL 4.0.10 gamma. I have records with data: id title version 1a 1 2a 2 3b 1 4b 2 How do I select all the records with highest version for each title? [...] Try: SELECT t1.id, t1.title, t1.version FROM table_name t1 LEFT JOIN table_name t2 ON t1.title=t2.title AND t1.versiont2.version WHERE t2.version IS NULL; [optional: ORDER BY title] Replace both occurrences of table_name with the name of your table. HTH, Hans-Peter
msSQL-MySQL
Is there is utility which is free to convert ms SQL 7.0 running on windows
NT into a mySQL format, i would be very grateful if someone in here could
help me out.
Thanks.
Karma
- Original Message -
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, June 06, 2003 2:53 PM
Subject: Re: start innodb without transactions
Carlos,
please send me your my.cnf. How big is the combined size of your log
files?
MySQL/InnoDB-4.0.9, January 14, 2003
Fixed a bug: if the combined size of InnoDB log files was = 2 GB in a
32-bit computer, InnoDB would write log in a wrong position. That could
make
crash recovery and InnoDB Hot Backup to fail.
In the first printout:
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 13 1000286684
InnoDB: Doing recovery: scanned up to log sequence number 13 1000286208
looks like InnoDB is not able to scan the log file at all, not even up to
the checkpoint!
InnoDB: log sequence number 13 1002344016
InnoDB: Doing recovery: scanned up to log sequence number 13 1002343936
The same in the next printout. It has written some 2 MB more log but
cannot
after a crash scan the log at all!
The failing assertion checks when freeing a BLOB or TEXT field that its
length is right:
if (extern_len - part_len == 0) {
ut_a(next_page_no == FIL_NULL);
}
This could be:
1) a bug in InnoDB's log writing;
2) hardware fault, broken disk;
3) serious corruption of the OS file system.
If InnoDB fails to scan lots of log in crash recovery, it means that the
resulting database may have very extensive corruption. That can explain
why
some of your tables are not visible at all.
There is little hope of recovering your tables. You should resort to a
backup and upgrade to 4.0.13. Then do some heavy processing, for example,
a
table import, and crash mysqld artificially by
killall -9 mysqld
Look then if InnoDB is able to scan the log in crash recovery.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
Transactions, foreign keys, and a hot backup tool for MySQL
Order MySQL technical support from https://order.mysql.com/
- Original Message -
From: Carlos Proal [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Friday, June 06, 2003 7:41 AM
Subject: Re: start innodb without transactions
Probably i need to describe the whole story:
I did a huge insert, a file about 100Mb, and mysql crashed/restarted,
the
err file showed:
--
030605 12:11:59 mysqld restarted
030605 12:12:00 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 13 1000286684
InnoDB: Doing recovery: scanned up to log sequence number 13 1000286208
InnoDB: Last MySQL binlog file position 0 756499, file name
./catarina-bin.057
030605 12:12:01 InnoDB: Flushing modified pages from the buffer pool...
030605 12:12:01 InnoDB: Started
/centia01/final/database/mysql/libexec/mysqld: ready for connections
030605 12:14:01 InnoDB: Out of memory in additional memory pool.
InnoDB: InnoDB will start allocating memory from the OS.
InnoDB: You may get better performance if you configure a bigger
InnoDB: value in the MySQL my.cnf file for
InnoDB: innodb_additional_mem_pool_size.
mysqld got signal 10;
This could be because you hit a bug. It is also possible that this
binary
or one of the libraries it was linked against is corrupt, improperly
built,
or misconfigured. This error can also be caused by malfunctioning
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.
key_buffer_size=67104768
read_buffer_size=131072
sort_buffer_size=524280
max_used_connections=4
max_connections=500
threads_connected=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
=
385528
K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
--
(i have 256Mb in buffer pool) But the next restart stopped mysql
showing:
--
030605 12:14:30 mysqld restarted
030605 12:14:32 InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 13 1002344016
InnoDB: Doing recovery: scanned up to log sequence number 13 1002343936
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: Trx id counter is 0 119167744
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling
Re: MySQL Secure connections
FYI
I got is working in the end on Solaris, I had to rebuild it
patched configure so it picked OpenSSL up from /usr/sfw
configured with --with-openssl
secure connections then worked okay
thanks again
paul
Paul Cunningham (At Home) wrote:
Gareth,
Thanks
It looks as though my build is not picking up the OpenSSL stuff so
HAVE_OPENSSL is not getting defined :-(
can't see why at the moment though as OpenSSL is installed in
--with-openssl=/usr/sfw
Paul
Gareth Davis wrote:
I've just done a similar thing. Are you sure that you are running the
mysql client that you have just compiled.
I used the following build options
CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions
\
-fno-rtti ./configure \
--enable-assembler \
--enable-local-infile \
--with-mysqld-user=mysql \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--prefix=/ \
--with-extra-charsets=complex \
--exec-prefix=/usr \
--libexecdir=/usr/sbin \
--sysconfdir=/etc \
--datadir=/usr/share \
--localstatedir=/var/lib/mysql \
--infodir=/usr/share \
--includedir=/usr/include \
--mandir=/usr \
--with-embedded-server \
--enable-thread-safe-client \
--with-vio \
--with-openssl ;
I nicked this out of the RPM build spec and added the last two options.
Gareth.
On Tue, 2003-06-03 at 16:04, Paul Cunningham wrote:
Hi,
Please can someone help with advise on setting up a Secure MySQL Connection. The
details are as follows.
I have built and configured OpenSSL 0.9.7 and MySQL 4.0.12.
MySQL was configured with the options
./configure \
--enable-local-infile \
--with-vio --with-openssl=/usr/sfw \
--enable-thread-safe-client --with-pthread \
--sysconfdir=/etc/sfw/mysql \
--prefix=/usr/sfw/mysql
I have set up the certificates as described in
http://www.mysql.com/doc/en/Secure_Create_Certs.html
and ran the mysql daemon as follows .
CONFF=/export/openssl/my.cnf
/usr/sfw/mysql/bin/mysqld_safe --defaults-file=${CONFF} --user=mysql
the contents of /export/openssl/my.cnf being ...
[client]
ssl-ca=/export/openssl/cacert.pem
ssl-cert=/export/openssl/client-cert.pem
ssl-key=/export/openssl/client-key.pem
[mysqld]
master-ssl-ca=/export/openssl/cacert.pem
master-ssl-cert=/export/openssl/server-cert.pem
master-ssl-key=/export/openssl/server-key.pem
mysqld started okay after changing the [mysqld] entries and added the 'master-'
bits.
I then tried to run the 'mysql' client as follows ...
/usr/sfw/mysql/bin/mysql --defaults-file=/export/openssl/my.cnf
but this fails with the following
/usr/sfw/mysql/bin/mysql: ERROR: unknown variable
'ssl-ca=/export/openssl/cacert.pem'
The clients does not seem to like these options either in the my.cnf file or if
put on the mysql command-line.
Has anyone got any ideas where I am going wrong?
Thanks
Paul
--
Gareth Davis [EMAIL PROTECTED]
Logical Practice Systems Limited
--
__
Paul Cunningham Email: [EMAIL PROTECTED]
Software EngineerWork: (+44) (0)1923 696888
Sun Microsystems Fax:(+44) (0)1923 696801
54 Clarendon Road, Watford, Herts, WD17 1DU, UK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I thought single UPDATE statements were atomic
* Mark Rages According to the docs, single update statements are atomic. I would not say that your problem is with atomicity, but with consistency. It is expected that a transaction oriented database should follow the ACID properties, and with them, any consistency properties should not be evaluated until the transaction is committed. Appearantly MySQL is not able to do so. (Which is perhaps a trade off between speed and functionality.) Why don't you just drop the unique criterium and do the consistency check by youself? -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding inoperable symbolic links in Win2K Pro
At 18:06 3/6/2003 -0700, Charles L. Nelson wrote: Hi, I recently posted an email about 'Inoperable Symbolic Links in Win2K Pro ' and I subsequently subscribed to the belief that MySQL would fail when installed in a location that was different that the default location. I tried a myriad of things to get the database system to function correctly. I could not get it to work when it was installed as a service or when the data was located somewhere different that drive c. The problem here is that the default service installation is for the Local system account and when the drive is mapped the system account can't to be authenticated against a mapped driver. If the above your case, then you need to change the local system account for an user account, this account should have the privileges granted on the mapped driver. If your disk driver is installed on the same computer, then verify if for the local system account is enabled the privileges over it. -- Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Miguel Angel Solórzano [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ São Paulo - Brazil /_/ /_/\_, /___/\___\_\___/ ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql language
Hi, I was looking in what language what myslq written in? Just curious Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
