Re: Difference of MySQL Standard and Max Version
On Mon, Jul 07, 2003 at 10:57:52AM -0500, Dan Nelson wrote: In the last episode (Jul 07), Nils Valentin said: Thank you for the superfast reply. I actually was looking for other information. I understood the actual feature difference of the standard and f.e max version. Sorry if this didnt came out so clear. I will try to make it clearer. My question was more aiming at what advantages the dynamically linking or the statically linking have (except the memory usage of course). I was thinking when given 2 times the same versions once linked statically and once linked dynamically which one would have which advantages in regards to performance, reliability etc. ? You can't call dlopen() on a statically-linked binary, so you can't use UDFs. On the other hand, static binaries usually run ~20% faster. 20%?! That seems like a high number. Have you actually seed that much of a boost? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 217,761,232 queries (348/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld shows high cpu usage over extended time, restart = normal
On Wed, Jul 09, 2003 at 01:08:56PM -0400, Dave [Hawk-Systems] wrote: load). Is there a known issue (running on FreeBSD 4.8, MySQL 3.23.55 MyISAM)? its been a known issue for quite a long time use linuxthreaded version and it should work fine. although much of work has been done on threads implementation, there are still such problems with mysql. it happens even on freebsd 5.0 Thanks Terry... gave me enough information to google the following which went into further detail regarding this issue specifically on FreeBSD http://jeremy.zawodny.com/blog/archives/000203.html Yes. LinuxThreads + MySQL + FreeBSD 4.x == Good -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 217,907,314 queries (348/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
get a certain element in a GROUP BY
Hi, I am trying to control which element gets picked when I do a group by, but I can't figure out how to do it. First some example data: CREATE TABLE t ( id int not null primary key auto_increment, group_id int not null, level int not null); INSERT INTO t VALUES (1,1,2); INSERT INTO t VALUES (2,1,3); INSERT INTO t VALUES (3,1,1); INSERT INTO t VALUES (4,2,1); INSERT INTO t VALUES (5,2,1); INSERT INTO t VALUES (6,2,1); INSERT INTO t VALUES (7,4,4); INSERT INTO t VALUES (8,1,1); INSERT INTO t VALUES (9,2,2); mysql SELECT * from t; ++--+---+ | id | group_id | level | ++--+---+ | 1 |1 | 2 | | 2 |1 | 3 | | 3 |1 | 1 | | 4 |2 | 1 | | 5 |2 | 1 | | 6 |2 | 1 | | 7 |4 | 4 | | 8 |1 | 1 | | 9 |2 | 2 | ++--+---+ 9 rows in set (0.00 sec) The real schema is of course much more complex. I want to get one line for each group_id and it must be the one with the lowest level,id. Adding order by level just orders the results when they are already grouped. mysql select *,MIN(level) as min_level from t group by group_id order by level,id; ++--+---+---+ | id | group_id | level | min_level | ++--+---+---+ | 4 |2 | 1 | 1 | | 1 |1 | 2 | 1 | | 7 |4 | 4 | 4 | ++--+---+---+ In this case I wanted to get row 3 (with level 1) for group 1 for example. I think I understand why MySQL can't guess what I want in this case; how can I explain it better in SQL? :-) - ask -- http://www.askbjoernhansen.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to perform Inner Join
Hello Henry, Wednesday, July 16, 2003, 5:35:49 PM, you wrote: I've noticed 2 errors. First: You describe table with name: PUBLISHER but useing in SQL table PUBISHER - missing a letter L Second: As I'm understanding you want to make join table PUBLISHER with table TITLES. But see what you wrote in SQL: FROM pubishers JOIN pubishers ON pubishers.pub_id = titles.pub_id You trying to make join table with itself. H I have 2 tables with the following fields : H table : publisher H field1: pub_id - Primary, int, auto H table : titles H field1: title_id - Primary, int, auto H field1: pub_id - int H Using sqlyog I noticed that the indexes had already been created for the H Primary keys but according to my tutorial I also had to create an index for H the foreign key. H I did this and then tried to run the following query : H SELECT pubishers.pub_id, titles.pub_ID H FROM pubishers JOIN pubishers ON pubishers.pub_id = titles.pub_id H It keeps giving me the following error : H Error Code : 1066 H Not unique table/alias: 'pubishers' H Can someone tell me what I am doing wrong ? -- Best regards, Krasimir_Slaveykovmailto:[EMAIL PROTECTED] Tel: ++359 2 97 666 [EMAIL PROTECTED] Tel: ++359 2 97 66 701 Fax: ++359 2 97 66 731 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql connection error
hi No its not listening and giving same error ERROR 2013: Lost connection to MySQL server during query how can i allow a ip to connect mysql database from other system. plz explain me little (i have not yet idea to use mysql fully) Thanks ZafAr - Original Message - From: [EMAIL PROTECTED] To: zafar rizvi [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 10:11 AM Subject: Re: mysql connection error It says lost error during query.. but I'd be suspect if it even succesfully connected/authenticated.. I'd do a quick check first to ensure your mysql server is listening on the 192.xxx interface.. if it's unix, netstat -atn On Thu, 17 Jul 2003, zafar rizvi wrote: hi I am running one centralized mysql server, its runnig fine and conneded with localhost and his own ip very fine. But when i want to connect him from remote system or other lan system it give me errror like that. [EMAIL PROTECTED] root]# mysql -h 192.168.0.63 mysql ERROR 2013: Lost connection to MySQL server during query Anybody has idea what's wrong and where is ? I get no answer from searching on google with that error. Waiting for reply ThAnks ZaFAr -- 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: mysql connection error
Keep in mind you need 2 things to happen: A) the mysql server has to be bound/listening on the ip/interface you need it to be. This is typically configured in my.cnf configuration file. B) you need to ensure the mysql privlidges will allow access from other hostnames/ip addresses. If mysql isn't listening on the required interface there is no a way tcp/ip connection can be established... It is very possible that the application running on the same server is using mysql sockets to connect to the sql server instead of using tcp/ip... Start with ensuring the server is listing tcp/ip on the correct interface.. If you can send us your my.cnf,it would help.. Unless mysql was perhaps configured without networking (I think that is a valid configure option if I'm not mistaken) On Thu, 17 Jul 2003, zafar rizvi wrote: hi No its not listening and giving same error ERROR 2013: Lost connection to MySQL server during query how can i allow a ip to connect mysql database from other system. plz explain me little (i have not yet idea to use mysql fully) Thanks ZafAr - Original Message - From: [EMAIL PROTECTED] To: zafar rizvi [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 10:11 AM Subject: Re: mysql connection error It says lost error during query.. but I'd be suspect if it even succesfully connected/authenticated.. I'd do a quick check first to ensure your mysql server is listening on the 192.xxx interface.. if it's unix, netstat -atn On Thu, 17 Jul 2003, zafar rizvi wrote: hi I am running one centralized mysql server, its runnig fine and conneded with localhost and his own ip very fine. But when i want to connect him from remote system or other lan system it give me errror like that. [EMAIL PROTECTED] root]# mysql -h 192.168.0.63 mysql ERROR 2013: Lost connection to MySQL server during query Anybody has idea what's wrong and where is ? I get no answer from searching on google with that error. Waiting for reply ThAnks ZaFAr -- 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: join query result difference between 3.23.49 and 4.0.13
Doug Reese [EMAIL PROTECTED] wrote: i have what seems to me a very common operation i'm performing. i need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. there must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what i expected, nor what i received with v3.23. i'm including sample data and queries with my results. i've not been able to find any relevant messages in the list archives. any comments are greatly appreciated. here's the table structure and some sample data: CREATE TABLE `billing` ( `invoice` mediumint(9) NOT NULL auto_increment, `user_id` mediumint(9) NOT NULL default '0', `invoice_date` date NOT NULL default '-00-00', `amount` float NOT NULL default '0', `timestamp` timestamp(14) NOT NULL, PRIMARY KEY (`invoice`), KEY `user_id` (`user_id`), KEY `user_invoice` (`user_id`,`invoice`) ) TYPE=MyISAM COMMENT='invoices'; INSERT INTO `billing` VALUES (1, 1, '2003-07-01', '500', 20030716092700); INSERT INTO `billing` VALUES (10001, 1, '2003-07-03', '600', 20030716092807); # CREATE TABLE `billing_payment` ( `invoice` mediumint(9) NOT NULL default '0', `amount_paid` mediumint(9) NOT NULL default '0', `payment_status` varchar(15) NOT NULL default '', `pending_reason` varchar(15) default NULL, `payment_date` date default NULL, `txn_id` varchar(20) default NULL, `timestamp` timestamp(14) NOT NULL, KEY `txn_id` (`txn_id`), KEY `invoice` (`invoice`) ) TYPE=MyISAM COMMENT='payments on invoices'; INSERT INTO `billing_payment` VALUES (1, 500, 'Completed', NULL, '2003-07-02', '112233', 20030716092746); here are the queries and results. note that query #1 gives the expected (and desired) result on both versions, but query #2 only gives the expected (and useful) result in v3.23. to give a brief explanation of the difference between the queries: invoice 1 has a payment against it, while invoice 10001 has no payment records in the payment table. === v3.23.49 query #1 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=1 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 1 | 500 | 0 | +-+--+-+ 1 row in set (0.00 sec) query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 |0 | 600 | +-+--+-+ 1 row in set (0.00 sec) === v4.0.13 query #1 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=1 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 1 | 500 | 0 | +-+--+-+ 1 row in set (0.01 sec) query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 | NULL |NULL | +-+--+-+ 1 row in set (0.00 sec) NULL values in this result are not expected, nor are they helpful in determining the invoice balance. It's correct result. Since 4.0.13 SUM() returns NULL if there is no rows in the result or if all values are NULL (as in your case). And SELECT billing.amount - NULL also returns NULL. -- 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: Deleted index file (.MYI)
Hi! On Jul 16, Oswaldo Castro wrote: Hi Everybody I have acidentally deleted an index file (.MYI) from a table. Is there another way, besides its backup, to recover this table ? Thanks for any help Yes. Backup your MYD file (just in case) and try REPAIR ... USE_FRM or create dummy MYI file, TRUNCATE TABLE, restore MYD file (you backed it up, didn't you ?), REPAIR TABLE Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question again : mysql.sock
Oliver Etzel - GoodnGo.COM \(R\) [EMAIL PROTECTED] wrote: after Installing mysql I started the mysql daemon.. The I tried to log in to my mysql database and got the message could not find mysql.sock in /tmp. In the mysql-configuration file /etc/my.cnf (in my redhat system 8.0) I changed the place for the socket file from socket=/var/lib/mysql/mysql.sock to socket=/tmp/mysql.sock. That doesn?t work. If you change path to the socket file for the server, you must change it to the clients, too. -- 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]
RE: Best practice column type for storing decimal currency amounts?
Hmmm The trainer on our Managing MySQL told us that they are stored as FLOATS/DOUBLES. But I could have wrong recollection of it, maybe he meant they are stored as strings and only CALCULATION is done with DOUBLES. I this case, I would trust the manual :) But still, it is no true monetary calculation as you will get these floating point errors with doubles. On the other hand it is good to know that they store it as STRING, because some legal institutes require you to store numbers in human readable format (e.g. some bookkeeping data). Thanx to point out my error, John! Cheers /rudy -Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 22:03 To: Rudy Metzger; [EMAIL PROTECTED] Subject: Re: Best practice column type for storing decimal currency amounts? Thanks for the reply, Rudy. My source for the statement that decimal values are stored as strings is: http://www.mysql.com/doc/en/Numeric_types.html : DECIMAL and NUMERIC values are stored as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values. One character is used for each digit of the value, the decimal point (if scale 0), and the `-' sign (for negative numbers). If scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part. But you sound knowledgeable. Is the manual in error? --John On Wednesday 16 July 2003 04:36 am, Rudy Metzger wrote: Problem is that DECIMALs are currently stored as FLOAT or DOUBLE in the DB (at least MyISAM). MySQL AB is busy with adding a true monetary type (like MONEY) to the system. What I am doing when using monetary values is putting them into floats if I can live with rounding problems or put them into in ints and multiply them by 100 (or 1000) before putting them there (thus taking care of the decimal point myself). Cheers /rudy ps: I wish DECIMALS would be stored as char :) -Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 21:39 To: [EMAIL PROTECTED] Subject: Best practice column type for storing decimal currency amounts? Is there an accepted best practice on whether to store decimal currency amounts (e.g. dollars and cents) in MySQL decimal column types? Certainly, the most straightforward way is to use decimal columns. But it appears that such values are stored as ASCII strings, which would be inefficient for calculations (requiring conversion to a numeric type for each calculation). I guess the alternative would be to use integer columns (and multiply by 100 to store the value as total cents). My particular context is a PHP/MySQL sales system. What's the consensus? Thanks much, --John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question again : mysql.sock
Hello Egor, hello all, If you change path to the socket file for the server, you must change it to the clients, too. How can I change the path to the socket file for the mysql clients??? Are there any config-files or shall I have to put a ln -s - link??? Oliver Oliver Etzel - GoodnGo.COM \(R\) [EMAIL PROTECTED] wrote: after Installing mysql I started the mysql daemon.. The I tried to log in to my mysql database and got the message could not find mysql.sock in /tmp. In the mysql-configuration file /etc/my.cnf (in my redhat system 8.0) I changed the place for the socket file from socket=/var/lib/mysql/mysql.sock to socket=/tmp/mysql.sock. That doesn?t work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
i need help, innodb blocks insertion into table for a while
hi dear mysql list members
i have setup a mysql database 4.013 with innodb enabled. when i set the transaction
isolation level to serializable. after executing a specific query is the insertion
into a table blocked for about 20 seconds. the table is defined as follows
CREATE TABLE `slide`
(
`id` bigint(20) unsigned NOT NULL auto_increment,
`identifier` varchar(255) NOT NULL default '',
`comment` varchar(255) default NULL,
`fromid` bigint(20) unsigned NOT NULL default '0',
`system` tinyint(1) unsigned NOT NULL default '0',
`size` int(10) unsigned NOT NULL default '0',
`data` text NOT NULL,
`modifyid` bigint(20) unsigned NOT NULL default '0',
`modifytime` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=InnoDB
executing the following query seems to work well. it returns quickly and the result is
as expected.
SELECT `id`, `data` FROM `slide` WHERE LOCATE('filterid=7',`data`) != 0
but the first insertion into the slide table after that select is locked for about 20
seconds. selects work without any timeout. i would be happy if someone has an idea how
i can resolve the problem.
best regards
benny
RE: mysql setup compared to oracle
1. No, especially not MyISAM. In MyISAM, a database (you can compare that to instance) is just a directory on disk. Every table in this DB (instance) again is file (well actually 3 files, one for data, one for metadata, one for index information). InnoDB looks a bit similar, as it also uses the concept of tablespace. But you can mix InnoDB types and MyISAM in MySQL. So actually there are 2 DB engines implemented in MySQL (ignoring BDB for the moment) which can be mixed in one DB. The metadata of this information (what table belongs to which DB) is stored in MySQL. The manipulation is then passed to the appropriate DB engine (MyISAM, InnoDB). 2. Only for InnoDB. The principles for administration apply, but commands are different. Also it is not 100% the same. 3. You can give this in the init files my.cnf. Or can change it on compile time when you compile the DB (which is something I would NOT advice) (compiling the DB I mean) 4. my.cnf 5. yes, because one server can serve multiple DBs. But like said above, a DB in MySQL is more like an instance in ORACLE. But you can also start multiple servers processes (there is even a script for that, musqld_multi) 6. no. executable are placed in the /bin foldes (depending on installation). Cannot recall SID anymore, was this the instance config? If yes, there is none. Suitable docs: www.mysql.com :) Cheers /rudy -Original Message- From: Harald Falkenberg [mailto:[EMAIL PROTECTED] Sent: woensdag 16 juli 2003 22:53 To: [EMAIL PROTECTED] Subject: mysql setup compared to oracle Hi, I'm new to mysql. Coming from the oracle database a couple of questions came up. Perhaps somebody, who has also experience in oracle, can set me on the tracks. Questions: 1. is mysql simular organized as oracle: instance/users/tables, so that I have to connect to chossen instance and user? 2. does mysql also use the term tablespace and can it administrated in the same way? 3. when creating a mysql database, how to determine where the datafiles are located? 4. is there something like a init.ora? 5. is it possible to start several databases from the same installation? 6. is there something like ORACLE_HOME and ORACLE_SID? Hope you can give me details prepared for a newbie and point me to the suitable commands or docs. Thanks in advance Harald -- 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: subselect question... shouldn't this work?
Ben Margolin [EMAIL PROTECTED] wrote: I am new to subselects, and what I really want is to do them in an update, but the following shows a simpler select, that also doesn't seem to work as I think it should. Advice? Do I just misunderstand how subselects are actually executed? (This is on mysql version 4.1.0-alpha-max-nt.) First, here's the tables in question: mysql describe m; +---+-+---+--+-+-+---+ | Field | Type| Collation | Null | Key | Default | Extra | +---+-+---+--+-+-+---+ | toid | int(11) | binary| YES | | NULL| | | rd| int(11) | binary| YES | | NULL| | +---+-+---+--+-+-+---+ mysql describe p; +-+-+---+--+-+-+---+ | Field | Type| Collation | Null | Key | Default | Extra | +-+-+---+--+-+-+---+ | userid | int(11) | binary| | PRI | 0 | | | pmnew | int(11) | binary| YES | | NULL| | | pmtotal | int(11) | binary| YES | | NULL| | +-+-+---+--+-+-+---+ and the data in the tables... mysql select * from p; ++---+-+ | userid | pmnew | pmtotal | ++---+-+ | 1 | 0 | 0 | | 2 | 0 | 0 | ++---+-+ 2 rows in set (0.00 sec) mysql select * from m; +--+---+ | toid | rd| +--+---+ |1 | 0 | |1 | 0 | |1 | 0 | |1 |12 | |1 |15 | |1 | 123 | |1 | 12312 | |1 | 12312 | |1 | 123 | |2 | 0 | |2 | 0 | |2 | 1 | |2 | 2 | +--+---+ 13 rows in set (0.00 sec) mysql select userid,pmtotal,pmnew, (select count(rd) from m where toid=p.userid) calc_total, (select count(rd) from m where rd=0 and toid=p.userid) calc_new from p where userid in (select distinct toid from m); ++-+---++--+ | userid | pmtotal | pmnew | calc_total | calc_new | ++-+---++--+ | 1 | 0 | 0 | 9 |3 | | 2 | 0 | 0 | NULL | NULL | ++-+---++--+ Now, the first row has what I want and expect, in calc_total and calc_new... but the second row doesn't. Why? Shouldn't the subselects in the field selector part (not the where part) be re-executed for each value in the IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the second row. For example, if I manually fudge the WHERE ... IN, I get: mysql select userid,pmtotal,pmnew, (select count(rd) from m where toid=p.userid) calc_total, (select count(rd) from m where rd=0 and toid=p.userid) calc_new from p where userid in (2); ++-+---++--+ | userid | pmtotal | pmnew | calc_total | calc_new | ++-+---++--+ | 2 | 0 | 0 | 4 |2 | ++-+---++--+ which is exactly what I want, but all at once :-) Ideas? Misunderstanding on my part? Bug? (By the way, what I eventually want to do is an update to set pmtotal and pmnew to be the calc_total and calc_new; in the real schema this is a simplified version of, they are essentially 'caches' of the new/total counts...) Thanks for the report. I added your report to the MySQL bug database: http://bugs.mysql.com/bug.php?id=860 -- 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 setup compared to oracle
I'm fairly new to mysql myself, but I'll have a go. The mysql manual is at http://www.mysql.com/doc/. I've only dipped into it, but it seems to be pretty good. Questions: 1. is mysql simular organized as oracle: instance/users/tables, so that I have to connect to chossen instance and user? Yes and no. mysql is more like Sybase/MS SQL server where you have a single instance and multiple databases. An instance is called a server and is represented by the mysqld process. 2. does mysql also use the term tablespace and can it administrated in the same way? No and yes. The default table storage structure is 'MyIsam' where each table is represented by a separate file (actually several files for data and indexes). There is a a new alternative storage structure 'InnoDB' which is more like tablespaces. I know nothing about it. http://www.mysql.com/doc/en/InnoDB_overview.html 3. when creating a mysql database, how to determine where the datafiles are located? The data directory is specified in my.cnf and all data files are created there. As far as I know mysql doesn't support fine grained control of storage the way Oracle does. There may be more control with InnoDB 4. is there something like a init.ora? Yes. It is my.cnf There is a global file in /etc which is installation wide and server(instance) specific optios in DATADIR/my.cnf. See http://www.mysql.com/doc/en/Option_files.html 5. is it possible to start several databases from the same installation? Yes. Although this is the wrong question really. You can have several servers (instances) and each server can have several databases. 6. is there something like ORACLE_HOME and ORACLE_SID? Not sure. There are MYSQL_UNIX_PORT (for local connections) AND MYSQL_TCP_PORT ( remote connections) which are sort of equivalent to ORACLE_SID. Hope you can give me details prepared for a newbie and point me to the suitable commands or docs. Thanks in advance Harald -- 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: mysql connection error
Also make sure that the port is not firewalled. Cheers /rudy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 9:56 To: zafar rizvi Cc: [EMAIL PROTECTED] Subject: Re: mysql connection error Keep in mind you need 2 things to happen: A) the mysql server has to be bound/listening on the ip/interface you need it to be. This is typically configured in my.cnf configuration file. B) you need to ensure the mysql privlidges will allow access from other hostnames/ip addresses. If mysql isn't listening on the required interface there is no a way tcp/ip connection can be established... It is very possible that the application running on the same server is using mysql sockets to connect to the sql server instead of using tcp/ip... Start with ensuring the server is listing tcp/ip on the correct interface.. If you can send us your my.cnf,it would help.. Unless mysql was perhaps configured without networking (I think that is a valid configure option if I'm not mistaken) On Thu, 17 Jul 2003, zafar rizvi wrote: hi No its not listening and giving same error ERROR 2013: Lost connection to MySQL server during query how can i allow a ip to connect mysql database from other system. plz explain me little (i have not yet idea to use mysql fully) Thanks ZafAr - Original Message - From: [EMAIL PROTECTED] To: zafar rizvi [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 10:11 AM Subject: Re: mysql connection error It says lost error during query.. but I'd be suspect if it even succesfully connected/authenticated.. I'd do a quick check first to ensure your mysql server is listening on the 192.xxx interface.. if it's unix, netstat -atn On Thu, 17 Jul 2003, zafar rizvi wrote: hi I am running one centralized mysql server, its runnig fine and conneded with localhost and his own ip very fine. But when i want to connect him from remote system or other lan system it give me errror like that. [EMAIL PROTECTED] root]# mysql -h 192.168.0.63 mysql ERROR 2013: Lost connection to MySQL server during query Anybody has idea what's wrong and where is ? I get no answer from searching on google with that error. Waiting for reply ThAnks ZaFAr -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql setup compared to oracle
1. No, especially not MyISAM. In MyISAM, a database (you can compare that to instance) is just a directory on disk. Every table in this DB (instance) again is file (well actually 3 files, one for data, one for metadata, one for index information). Actually the server is equivalent to an instance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: join query result difference between 3.23.49 and 4.0.13
I also fell into this NULL trap. Make sure to read the change log concerning NULL values! They are still buggy (imho) and with every change I have the feeling that new bugs/features are introduced. Example: Select sum(1) from foo where 1 = 3; This statement actually returns a row with NULL. Which is a clear BUG imho because the where condition can never be true! But Victoria says in her/his mail that this is expected behavior... Cheers /rudy -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 10:33 To: [EMAIL PROTECTED] Subject: Re: join query result difference between 3.23.49 and 4.0.13 Doug Reese [EMAIL PROTECTED] wrote: i have what seems to me a very common operation i'm performing. i need to find the balance on an invoice. i was not having any problems until the production server was upgraded to mysql v4.0.13-standard for pc-linux. there must be a better way to query for this information than the method i'm using, since the result with v4.0 is not what i expected, nor what i received with v3.23. i'm including sample data and queries with my results. i've not been able to find any relevant messages in the list archives. any comments are greatly appreciated. here's the table structure and some sample data: CREATE TABLE `billing` ( `invoice` mediumint(9) NOT NULL auto_increment, `user_id` mediumint(9) NOT NULL default '0', `invoice_date` date NOT NULL default '-00-00', `amount` float NOT NULL default '0', `timestamp` timestamp(14) NOT NULL, PRIMARY KEY (`invoice`), KEY `user_id` (`user_id`), KEY `user_invoice` (`user_id`,`invoice`) ) TYPE=MyISAM COMMENT='invoices'; INSERT INTO `billing` VALUES (1, 1, '2003-07-01', '500', 20030716092700); INSERT INTO `billing` VALUES (10001, 1, '2003-07-03', '600', 20030716092807); # CREATE TABLE `billing_payment` ( `invoice` mediumint(9) NOT NULL default '0', `amount_paid` mediumint(9) NOT NULL default '0', `payment_status` varchar(15) NOT NULL default '', `pending_reason` varchar(15) default NULL, `payment_date` date default NULL, `txn_id` varchar(20) default NULL, `timestamp` timestamp(14) NOT NULL, KEY `txn_id` (`txn_id`), KEY `invoice` (`invoice`) ) TYPE=MyISAM COMMENT='payments on invoices'; INSERT INTO `billing_payment` VALUES (1, 500, 'Completed', NULL, '2003-07-02', '112233', 20030716092746); here are the queries and results. note that query #1 gives the expected (and desired) result on both versions, but query #2 only gives the expected (and useful) result in v3.23. to give a brief explanation of the difference between the queries: invoice 1 has a payment against it, while invoice 10001 has no payment records in the payment table. === v3.23.49 query #1 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=1 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 1 | 500 | 0 | +-+--+-+ 1 row in set (0.00 sec) query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 |0 | 600 | +-+--+-+ 1 row in set (0.00 sec) === v4.0.13 query #1 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=1 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 1 | 500 | 0 | +-+--+-+ 1 row in set (0.01 sec) query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 | NULL |NULL | +-+--+-+ 1 row in set (0.00 sec) NULL values in this result are not expected, nor are they helpful in determining the invoice balance. It's correct result. Since 4.0.13 SUM() returns NULL if there is no rows in the result or if all values are NULL (as in
RE: mysql setup compared to oracle
Hmmm... right. You can compare the server to the instance. And the user to the DB (in the scheme, not at authentication) Thanx for the pointer! /rudy -Original Message- From: Jim Smith [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 11:06 To: Rudy Metzger; 'Harald Falkenberg'; [EMAIL PROTECTED] Subject: RE: mysql setup compared to oracle 1. No, especially not MyISAM. In MyISAM, a database (you can compare that to instance) is just a directory on disk. Every table in this DB (instance) again is file (well actually 3 files, one for data, one for metadata, one for index information). Actually the server is equivalent to an instance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql connection error
zafar rizvi [EMAIL PROTECTED] wrote: hi I am running one centralized mysql server, its runnig fine and conneded with localhost and his own ip very fine. But when i want to connect him from remote system or other lan system it give me errror like that. [EMAIL PROTECTED] root]# mysql -h 192.168.0.63 mysql ERROR 2013: Lost connection to MySQL server during query Anybody has idea what's wrong and where is ? I get no answer from searching on google with that error. What is the version of MySQL server? What OS do you use? -- 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: Question again : mysql.sock
Oliver Etzel - GoodnGo.COM \(R\) [EMAIL PROTECTED] wrote: If you change path to the socket file for the server, you must change it to the clients, too. How can I change the path to the socket file for the mysql clients??? Are there any config-files or shall I have to put a ln -s - link??? Oliver Put entry to the [client] section of my.cnf (my.ini) [client] socket=/path/to/the/spcket/file Oliver Etzel - GoodnGo.COM \(R\) [EMAIL PROTECTED] wrote: after Installing mysql I started the mysql daemon.. The I tried to log in to my mysql database and got the message could not find mysql.sock in /tmp. In the mysql-configuration file /etc/my.cnf (in my redhat system 8.0) I changed the place for the socket file from socket=/var/lib/mysql/mysql.sock to socket=/tmp/mysql.sock. That doesn?t work. -- 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]
OT: Signing off for a while
I will be gone from the list until further notice. Unfortunately, the volume of email and volume of work do not coincide well. Should anyone need to contact me, please do so directly. Thanks a lot, and sorry for the OT. Edward Dudlik Becoming Digital www.becomingdigital.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
server doesn't take all options out of my.cnf
My problem is, that all variables except wait_timeout are initialized from the my.cnf file, only wait_timeout is always set to the standard value... Has anyone the same problem? Does somebody know how to resolve it? Moritz
Another mysql config help...
Hello all,
Let me start out by saying that I am still a newbie. I just freshly
installed mysql version 4.0.13.
The following are the steps that I took.
Unzipped the binary file in a windows directory.
Ran setup
Edited my-small.cnf and changed it to my.cnf saved it in the root of my
C-drive.. (Did not put any password in my.cnf)
Initialized the service startup - Automatic and service was started.
Command Prompt - started the client C:\mysql\bin mysql -u root mysql
The above gave me the mysql prompt
Ran the following
mysqldelete from user where Host='%';
mysqldelete from user where User=' ';
mysqldelete from db where Host='%';
mysqlUpdate user set User='myname', Password=password('mypassword')
where User='root';
mysqlflush privileges;
mysqlcreate database dbasethen
mysqlcreate database dbasenow
mysqlcreate database dbasewhen
I was able to run all the above without any problem.
My question is concerning security and access for myname. From looking
at what I have done so far and the sequence, do I need the GRANT command
to have all privileges on the 3 new databases or did I inherit the
rights with my Update statement? In other words, do I need to grant
individual privileges on each database in order to have total control
over them.
I have seen some statement like the folowing in some of the readings
that I have come across:
GRANT ALL on dbasethen.* to User myname@'localhost';
I am thinking that since myname is now the root user, I have all the
GRANTS...Please correct me if I am wrong.And if you could, let me
know the exact way to grant the privileges.. I am trying touse the
command line to first set myname up and after I finish that, I will be
using PHPMYADMIN to do more admin stuff.
Thank you all in advance...
finding user details inside UDF
hi! i heared that its possible to get user details inside the UDf by adding the mysql_priv.h header file. But where will this file be available. i am using MySql 3.23.52 on linux8.0 when i try to compile it says :mysql_priv file not found. If i can include this file, i can get the thread constructor called... how should i compile my UDF source file. kindly help me. Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is MySQL cluster capable?
Can two mysql processes (running in different machines) access and work with the same database files in a shared storage scenario? Would there be any problem? Thanks :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN with php
Hello: I am making a php script, and I have the next problem: SELECT * FROM alo LEFT JOIN alo_resp ON alo.id=alo_resp.id result is: iddescpriceiddesc 1gg81dd 9 3df53 ff but if I do the same in mysqlgui result is: iddescpriceiddesc 1gg81dd 2ss 92jj 3df53 ff What is the problem? PHP? Thank in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN with php
Can u apply the standard list of rows to your query. Like: $result = pg_exec($db, $query); while($row = pg_fetch_row($result)) print_r($row); - Original Message - From: Patricio Gigoux [EMAIL PROTECTED] To: mysql groups [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 4:19 PM Subject: JOIN with php Hello: I am making a php script, and I have the next problem: SELECT * FROM alo LEFT JOIN alo_resp ON alo.id=alo_resp.id result is: iddescpriceiddesc 1gg81dd 9 3df53 ff but if I do the same in mysqlgui result is: iddescpriceiddesc 1gg81dd 2ss 92jj 3df53 ff What is the problem? PHP? Thank in advance -- 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 with php
sorry [my unfortunate mistake] disregard the pg_ [stuff] and consider mysql_ instead [mysql_query($query)] - Original Message - From: Viorel Dragomir [EMAIL PROTECTED] To: Patricio Gigoux [EMAIL PROTECTED]; mysql groups [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 4:25 PM Subject: Re: JOIN with php Can u apply the standard list of rows to your query. Like: $result = pg_exec($db, $query); while($row = pg_fetch_row($result)) print_r($row); - Original Message - From: Patricio Gigoux [EMAIL PROTECTED] To: mysql groups [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 4:19 PM Subject: JOIN with php Hello: I am making a php script, and I have the next problem: SELECT * FROM alo LEFT JOIN alo_resp ON alo.id=alo_resp.id result is: iddescpriceiddesc 1gg81dd 9 3df53 ff but if I do the same in mysqlgui result is: iddescpriceiddesc 1gg81dd 2ss 92jj 3df53 ff What is the problem? PHP? Thank in advance -- 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: Is MySQL cluster capable?
Im thinking of using OpenMOSIX for that kind of clustering (load balancing and shared filesystem/storage). But it doesn't support application that uses shared memory. That's including Oracle, MySQL, etc... But the shared storage feature gonna be useful for our hundred gigs of images :) Me fail English? That's unpossible ###___Archon___### - Original Message - From: Jordi Sánchez López [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 7:58 PM Subject: Is MySQL cluster capable? Can two mysql processes (running in different machines) access and work with the same database files in a shared storage scenario? Would there be any problem? 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]
mysql.sock again
Hello all, after Installing mysql I started the mysql daemon.. Then I tried to log in to my mysql database and got the message could not find mysql.sock in /tmp. In the mysql-configuration file /etc/my.cnf (in my redhat system 8.0) I changed the place for the socket file from socket=/var/lib/mysql/mysql.sock to socket=/tmp/mysql.sock. That doesnt work. What can I do? Perhaps put a link ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock in /tmp Oliver -- t-host GmbH Mobil: 0170/2781117 D-80337 Mnchen Tel: +49 89 54071102 Tumblinger Strae 50 Fax: +49 89 54071103 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL cluster capable?
Broadl;y speakng, no. See http://www.mysql.com/doc/en/Multiple_servers.html . There are single writer/multiple reader configurations that can be achieved using replication - see the replication section of the manual. Alec -- Can two mysql processes (running in different machines) access and work with the same database files in a shared storage scenario? Would there be any problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help!!!
Hi I am trying to extract two lots of information from one table but in such a way as to maintain the relationship between the two entities. I.E.: Table A ID NAME WORK HOME ^ 1 Jon A null 2 TracyA null 3 Agnes A B 4 Tom C null 5 Pete X J 6 SteveY G 7 Jim Z W 8 Mary Z null 9 Sue Z R 10 Lynn K null 11 TracyH I 12 W 13 M 14 N 15 O What I need to do is: 1.Get the data held in the fields: WORK and HOME into one table (removing duplications) 2.Provide a reference to an address for each NAME. Hopefully producing something like: ADDRESS ^ A B C G H I J K M N O X Y Z and separately: NAME Jon A TracyA Agnes A Agnes B Tom C Pete X Pete J SteveY SteveG Jim Z Jim W Mary Z Sue Z Sue R Lynn K TracyH TracyI W M N O I don't even know where to start. Regards
Select via Perl
I am trying to read a file and see if the contents of it exists in the DB or not and am trying to do it via a Select. I try to do a select * into outfile /tmp/result.txt from table where field like %$var%; but it always goes through the first two lines of the input file and then says the file already exists. Is this because I read each line of a file in a for loop in perl and the mysql query tries to recreate the outfile each time? Is there any other way I can dump the results of the select to a file of some sort? Thanks.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
complex query qusetion
Hi, I'm looking at some search engine where we can have up to 14-15 categories and each categories have about 5-10 option (checkbox) my question is if we have a complex query like this would it be better to split some categories in tables i.e we have 21 different categories should we have 7 tables(indexed) with three columns, then do a big join statment?? or any other better way? awards
Re: Another mysql config help...
Ola Ogunneye [EMAIL PROTECTED] wrote:
Let me start out by saying that I am still a newbie. I just freshly
installed mysql version 4.0.13.
The following are the steps that I took.
Unzipped the binary file in a windows directory.
Ran setup
Edited my-small.cnf and changed it to my.cnf saved it in the root of my
C-drive.. (Did not put any password in my.cnf)
Initialized the service startup - Automatic and service was started.
Command Prompt - started the client C:\mysql\bin mysql -u root mysql
The above gave me the mysql prompt
Ran the following
mysqldelete from user where Host='%';
mysqldelete from user where User=' ';
mysqldelete from db where Host='%';
mysqlUpdate user set User='myname', Password=password('mypassword')
where User='root';
mysqlflush privileges;
mysqlcreate database dbasethen
mysqlcreate database dbasenow
mysqlcreate database dbasewhen
I was able to run all the above without any problem.
My question is concerning security and access for myname. From looking
at what I have done so far and the sequence, do I need the GRANT command
to have all privileges on the 3 new databases or did I inherit the
rights with my Update statement? In other words, do I need to grant
individual privileges on each database in order to have total control
over them.
No, you don't need additional privileges for this user, because it has all privileges.
I have seen some statement like the folowing in some of the readings
that I have come across:
GRANT ALL on dbasethen.* to User myname@'localhost';
I am thinking that since myname is now the root user, I have all the
GRANTS...Please correct me if I am wrong.And if you could, let me
know the exact way to grant the privileges.. I am trying touse the
command line to first set myname up and after I finish that, I will be
using PHPMYADMIN to do more admin stuff.
The easy way to add new user or to give some premissions to the user is usage of
GRANT statement.
--
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]
RE: i need help, innodb blocks insertion into table for a while
hi i solved the problem by executing the commit after the select. best regards benny
Warning: thr_alarm queue is full errors and panic with invalid backtrace
Description: I'm running mysql in a 3 server configuration, with 2 servers being slaves to the first. I'm running vpopmail, which means a connection every incoming mail and every check. I woke up this morning to a mysql that wasn't answering connections, and that had left this in the log: 030716 13:48:06 InnoDB: Started 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=8388600 read_buffer_size=131072 Fatal signal 11 while backtracing 030716 13:48:07 mysqld restarted Before this, I got thousands of these: Warning: thr_alarm queue is full I checked the logs of the other 2 replication clients, and they also had the alarm queue error, and were also sucking up large resources. I was not able to connect to them, either. Before those, both replication clients had this logged: 030716 13:42:45 Slave: received 0 length packet from server, apparent master shutdown: 030716 13:42:45 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'courtney-bin.016' position 38236125 030716 13:42:45 Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'ip-address-changed' (61)' errno: 2003 retry-time: 10 retries: 86400 How-To-Repeat: I'm not sure what is triggering this. I was hoping the backtrace might help on that, but crashing while getting a backtrace isn't exactly promising. I have dome some small amount of tuning, which involves setting the max_connections to 500 and max_connect_errors to 50, on all machines. The binary log on the master server is rotated nightly and kept around for about a week. Fix: Unknown. I restarted all 3 mysqld processes and it seems to be going ok now. Submitter-Id: submitter ID Originator:User Organization: MySQL support: none Synopsis: Warning: thr_alarm queue is full errors and panic with invalid backtrace Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.13 (FreeBSD port: mysql-server-4.0.13_1) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.13, for portbld-freebsd4.8 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 23 min 12 sec Threads: 11 Questions: 37671 Slow queries: 0 Opens: 42 Flush tables: 1 Open tables: 36 Queries per second avg: 27.062 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: FreeBSD courtney.linkline.com 4.8-RELEASE FreeBSD 4.8-RELEASE #2: Fri Jun 20 11:39:35 PDT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/COURTNEY i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1223544 Jun 20 14:26 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Jun 20 14:26 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 577872 Jun 20 14:26 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.0.13_1' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.8' 'CFLAGS=-O -pipe -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.8' 'CC=cc' 'CXXFLAGS=-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' Perl: This is perl, version 5.005_03 built for i386-freebsd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select via Perl
Ashwin Kutty wrote: I am trying to read a file and see if the contents of it exists in the DB or not and am trying to do it via a Select. I try to do a select * into outfile /tmp/result.txt from table where field like %$var%; but it always goes through the first two lines of the input file and then says the file already exists. Is this because I read each line of a file in a for loop in perl and the mysql query tries to recreate the outfile each time? Yes Is there any other way I can dump the results of the select to a file of some sort? Have the perl program append the /tmp/result.txt to /tmp/ultimateresult.txt , and unlink /tmp/result.txt inside your loop. Thanks.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join query result difference between 3.23.49 and 4.0.13
hello victoria, thanks for your response. however, the real goal of my question still remains unanswered: given my sample data, how would i find the balance on an invoice with one sql statement. sure, i could query for the amount paid, then in a separate query subtract the result from the invoice total, but that seems like a step backwards. is there a way to do what i was doing with v3.23, given the new NULL behavior? thanks in advance. doug At 02:53 PM 7/17/2003 +, [EMAIL PROTECTED] wrote: -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: donderdag 17 juli 2003 10:33 To: [EMAIL PROTECTED] Subject: Re: join query result difference between 3.23.49 and 4.0.13 Doug Reese [EMAIL PROTECTED] wrote: query #2 mysql SELECT billing.invoice, SUM(amount_paid) AS paid, (billing.amount - SUM(amount_paid)) AS balance FROM billing LEFT JOIN billing_payment ON ( billing.invoice=billing_payment.invoice ) WHERE billing.invoice=10001 GROUP BY billing_payment.invoice; +-+--+-+ | invoice | paid | balance | +-+--+-+ | 10001 | NULL |NULL | +-+--+-+ 1 row in set (0.00 sec) NULL values in this result are not expected, nor are they helpful in determining the invoice balance. It's correct result. Since 4.0.13 SUM() returns NULL if there is no rows in the result or if all values are NULL (as in your case). And SELECT billing.amount - NULL also returns NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warning: thr_alarm queue is full errors and panic with invalid backtrace
Description: I'm running mysql in a 3 server configuration, with 2 servers being slaves to the first. I'm running vpopmail, which means a connection every incoming mail and every check. I woke up this morning to a mysql that wasn't answering connections, and that had left this in the log: 030716 13:48:06 InnoDB: Started 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=8388600 read_buffer_size=131072 Fatal signal 11 while backtracing 030716 13:48:07 mysqld restarted Before this, I got thousands of these: Warning: thr_alarm queue is full I checked the logs of the other 2 replication clients, and they also had the alarm queue error, and were also sucking up large resources. I was not able to connect to them, either. Before those, both replication clients had this logged: 030716 13:42:45 Slave: received 0 length packet from server, apparent master shutdown: 030716 13:42:45 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'courtney-bin.016' position 38236125 030716 13:42:45 Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'ip-address-changed' (61)' errno: 2003 retry-time: 10 retries: 86400 How-To-Repeat: I'm not sure what is triggering this. I was hoping the backtrace might help on that, but crashing while getting a backtrace isn't exactly promising. I have dome some small amount of tuning, which involves setting the max_connections to 500 and max_connect_errors to 50, on all machines. The binary log on the master server is rotated nightly and kept around for about a week. Fix: Unknown. I restarted all 3 mysqld processes and it seems to be going ok now. Submitter-Id: submitter ID Originator: User Organization: MySQL support: none Synopsis: Warning: thr_alarm queue is full errors and panic with invalid backtrace Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-4.0.13 (FreeBSD port: mysql-server-4.0.13_1) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.13, for portbld-freebsd4.8 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 23 min 12 sec Threads: 11 Questions: 37671 Slow queries: 0 Opens: 42 Flush tables: 1 Open tables: 36 Queries per second avg: 27.062 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: FreeBSD courtney.linkline.com 4.8-RELEASE FreeBSD 4.8-RELEASE #2: Fri Jun 20 11:39:35 PDT 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/COURTNEY i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rt ti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1223544 Jun 20 14:26 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Jun 20 14:26 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 577872 Jun 20 14:26 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.0.13_1' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.8' CFLAGS=-O -pipe -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.8' 'CC=cc' 'CXXFLAGS=-O -pipe -O3 -fno-omit-frame-pointer -felide-constructors -fno-rt ti -fno-exceptions' Perl: This is perl, version 5.005_03 built for i386-freebsd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference of MySQL Standard and Max Version
In the last episode (Jul 16), Jeremy Zawodny said: On Mon, Jul 07, 2003 at 10:57:52AM -0500, Dan Nelson wrote: In the last episode (Jul 07), Nils Valentin said: Thank you for the superfast reply. I actually was looking for other information. I understood the actual feature difference of the standard and f.e max version. Sorry if this didnt came out so clear. I will try to make it clearer. My question was more aiming at what advantages the dynamically linking or the statically linking have (except the memory usage of course). I was thinking when given 2 times the same versions once linked statically and once linked dynamically which one would have which advantages in regards to performance, reliability etc. ? You can't call dlopen() on a statically-linked binary, so you can't use UDFs. On the other hand, static binaries usually run ~20% faster. 20%?! That seems like a high number. Have you actually seed that much of a boost? I have, but I haven't tested long-lived monolithic programs like mysql. The big penalties you pay for shared linking are much higher startup overhead, the requirement that your shared libraries be compiled with -fPIC (thus losing a register), and the possible cost of a lookup in a jump table whan calling functions from one library into another. Startup time isn't a problem for mysql, and the server code isn't built out of shared libraries, so a statically-linked mysql might not really buy you that much. http://groups.google.com/groups?selm=3E74E3FB.D08EB9AC%40doe.carleton.ca and http://groups.google.com/groups?threadm=k7clgvoqjur1f10mosue3os74vv7nd34hi%404ax.com talk a bit about the penalties of using shared libs, but they're really from the point of view of the library author and relative speeds of shared libs vs static libs. A dynamically-linked program that spends most of its time in its own code (as opposed to doing function calls to CPU-intensive routines in libraries) will probably not see much of a difference. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Working with the text datatype in MySQL
Hi All, I am managing a small database and some of the tables need to be strings of an average length of about 2,000 characters. I decided to use the text datatype to store this information knowing that the upper limit is about 65,000 characters. However, the system crashes when the user tries to enter about 2,000 characters. I checked the max_allowed_package and net_buffer_size variables and they are both set to 1MB. Could you please advice me on what to look for or what to change in order for the system to work as it is supposed to be working? I look forward to hearing from you soon. Thanks in advance. George Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to enter iso-8859-2 characters through command line client?
Hi, We're trying enter polish language into mysql. My understanding is that polish language uses iso-8859-2 character set. I am cutting and pasting polish text from a web browser (Konqueror) into the mysql command line client running in a terminal window (konsole). MySQL server is version 4.0.12 running on a remote Linux server that I have connected to with ssh. When I paste polish characters into the command line client using a very simple insert statement a lot of characters are being lost or converted to a question mark (?) ... I believe this is happening to all non-ascii characters. The database server must support both English and Polish so anything that we do must be specific to the individual database (at the most). I have tried passing various options to the command line client, here is an example (line split for readability): mysql --character-sets-dir=/usr/share/mysql/charsets --default-character-set=latin2 -u user -p database I have also tried playing with Linux locale settings by starting mysql like this: LC_ALL=polish mysql --character-sets-dir=/usr/share/mysql/charsets --default-character-set=latin2 -u user -p database Nothing quite seems to work. What does work fine is inserting the characters through a web interface. As well as in my purely Linux environment, we also need this to work when on a Microsoft Windows based desktop. Any ideas appreciated, I'm shooting in the dark still even after hours of documentation reading and Internet searching. Thanks, -- Fraser Campbell [EMAIL PROTECTED] http://www.wehave.net/ Halton Hills, Ontario, Canada Debian GNU/Linux -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.sock
Hello all, after Installing mysql I started the mysql daemon.. The I tried to log in to my mysql database and got the message could not find mysql.sock in /tmp. In the mysql-configuration file /etc/my.cnf (in my redhat system 8.0) I changed the place for the socket file from socket=/var/lib/mysql/mysql.sock to socket=/tmp/mysql.sock. That doesnt work. What can I do? Perhaps put a link ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock in /tmp Oliver -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Working with the text datatype in MySQL
Lekeas GK wrote: Hi All, I am managing a small database and some of the tables need to be strings of an average length of about 2,000 characters. I decided to use the text datatype to store this information knowing that the upper limit is about 65,000 characters. However, the system crashes when the user tries to enter about 2,000 characters. What is your definition of crashes? Are special characters properly quoted before insertion? I checked the max_allowed_package and net_buffer_size variables and they are both set to 1MB. Could you please advice me on what to look for or what to change in order for the system to work as it is supposed to be working? I look forward to hearing from you soon. Thanks in advance. George Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger http://uk.messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN with php
- Original Message - From: Viorel Dragomir [EMAIL PROTECTED] To: Patricio Gigoux [EMAIL PROTECTED]; mysql groups [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 9:25 AM Subject: Re: JOIN with php Can u apply the standard list of rows to your query. Like: $result = pg_exec($db, $query); while($row = pg_fetch_row($result)) print_r($row); yes it the same to me script, but i don't get good result - Original Message - From: Patricio Gigoux [EMAIL PROTECTED] To: mysql groups [EMAIL PROTECTED] Sent: Thursday, July 17, 2003 4:19 PM Subject: JOIN with php Hello: I am making a php script, and I have the next problem: SELECT * FROM alo LEFT JOIN alo_resp ON alo.id=alo_resp.id result is: iddescpriceiddesc 1gg81dd 9 3df53 ff but if I do the same in mysqlgui result is: iddescpriceiddesc 1gg81dd 2ss 92jj 3df53 ff What is the problem? PHP? Thank in advance -- 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]
Java API to mySQL
Hello I want to know if there is java API to mySQL like its available for C and C++. Bye Kalika SMS using the Yahoo! Messenger;Download latest version.
Sun Cobalt Web Server RTX
What type of MySql can I install on a cobalt web server??? Please help... not sure... Clint S. Jones Computer Teks, Inc. President 1520 East Sunrise Blvd. Suite: 202 Fort Lauderdale, FL 33304 (954) 465-5516 (954) 767-6150 fax [EMAIL PROTECTED] www.webingenuity.net http://www.webingenuity.net/ www.computerteks.net http://www.computerteks.net/
Re: Sun Cobalt Web Server RTX
If you have a compiler.. probably any version you want... I'm pretty sure I've compiled mysql on an older qube in the past (took forever).. Cobalt may have binary distribution packages available for download.. I'd check their site first.. may save you some time... On Thu, 17 Jul 2003, Clint S. Jones wrote: What type of MySql can I install on a cobalt web server??? Please help... not sure... Clint S. Jones Computer Teks, Inc. President 1520 East Sunrise Blvd. Suite: 202 Fort Lauderdale, FL 33304 (954) 465-5516 (954) 767-6150 fax [EMAIL PROTECTED] www.webingenuity.net http://www.webingenuity.net/ www.computerteks.net http://www.computerteks.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Java API to mySQL
Welcome to the word of JDBC ... mysql calls it connector/j @ http://www.mysql.com/products/connector-j/index.html On Thu, 17 Jul 2003, [iso-8859-1] kalika patil wrote: Hello I want to know if there is java API to mySQL like its available for C and C++. Bye Kalika SMS using the Yahoo! Messenger;Download latest version. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: encryption/authentication
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 John Jolet wrote: I need to find out if it's possible to do two things with mysql. The first is external authentication, preferably with kerberos5, but PAM will work as well. No. Second, encrypting the data stream. Specifically with jdbc connections. Yes, via SSL. See the README that comes with Connector/J, the JDBC driver for MySQL (http://www.mysql.com/products/connector-j/). -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 Thunderbird - http://enigmail.mozdev.org iD8DBQE/FvfTtvXNTca6JD8RAhRjAJ9BkOSiP/r9AC8WM8U/YRgmxtgxFQCgxGDn OESlL+z0+21IkUJ0dgomXSs= =j2Mi -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting table comments
I have a short and simple question that I can't find an answer to. Is there a way to return table comments with a query? Thanks, -Jackson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 support
At 17:25 +0300 7/13/03, Sagi Bashari wrote: Hello, I would like to know the status of the UTF8 support in MySQL 4.1. I tried to create a table using utf8 charset, and inserting hebrew text into it. it seems like it still treats this text as binary - for example the length() function returns 8 on 4 chars string, or when cretting a column using varchar(4) i can only insert 2 letters to it. LENGTH() returns the length in bytes. Try CHARACTER_LENGTH() instead. A declaration of VARCHAR(n) means n bytes, not n characters. I believe this may change sometime, but I do not know when. So few questions: 1. Do you plan to support utf8 at this level, with all the string functions working properly with utf8 (my first impression was that thats what utf8 support is..). You do know that you're using an alpha release? This means that there is still work to be done, and that comments such as yours about problems that you find are valuable. 2. What about fulltext search on UTF8 strings? Unavailable. 3. Is there any target date for releasing a stable version of 4.1? 4.1 is just at the very beginning of its development cycle. Sagi -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting table comments
At 14:29 -0500 7/17/03, Jackson Miller wrote: I have a short and simple question that I can't find an answer to. Is there a way to return table comments with a query? SHOW CREATE TABLE t; SHOW TABLE STATUS LIKE 't'; Thanks, -Jackson -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sun Cobalt Web Server RTX
For my Cobalt server, I just d/l th mysql build from the Cobalt website (which is now @ sun.com) . And it worx just fine. Saqib Ali - http://www.xml-dev.com On Thu, 17 Jul 2003, Clint S. Jones wrote: What type of MySql can I install on a cobalt web server??? Please help... not sure... Clint S. Jones Computer Teks, Inc. President 1520 East Sunrise Blvd. Suite: 202 Fort Lauderdale, FL 33304 (954) 465-5516 (954) 767-6150 fax [EMAIL PROTECTED] www.webingenuity.net http://www.webingenuity.net/ www.computerteks.net http://www.computerteks.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connection error driving me nutz, need advice
I run a site that gets a fair amount of traffic throughout the day. For days at a time the site runs smoothly, and then, at seemingly random intervals I get the mysql error Too many connections. The only solution for this is to stop the mysql service and restart it. I changed the max_connections variable to allow for 300 connections instead of the default 100. When I view processes when the error occurs the process list is totally flooded with mysql processes. I am using php to establish connections and using the mysql_connect() function, not mysql_pconnect. I used to use the pconnect function and it seemed to produce the Too many connections error more frequently. I'm at a bit of a loss with this, any tips on how to address this are greatly appreciated. thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Security question: Possible to hide table structure? I couldn't find...
Hello, Think that we have a database named DATABASE1, and table named TABLE1, and fields named FIELD1, FIELD1,FIELD2,FIELD3,FIELD4 You want to give a specific permission to a user named USER1 For ex, you give only SELECT permission to USER1 for FIELD1 and FIELD4 in TABLE1 and DATABASE1. and you did not assignany other permission to USER1. Now everything is OK! USER1 can only select FIELD1 and FIELD4, and can not see data or change or etc.. to FIELD2 or FIELD3.. So we think that everything is OK! But, USER1 is still able to see the table structure of TABLE1. He see fields which i don't want him to see! As i searched internet related to this topic i couldn't find any satistfactory solution to this one. Anyone has idea to prevent USER1 to be able to see table structure and only permission to SELECT FIELD1 and FIELD4 as i assigned? Also there should be some default error message for these users when they try to select from another field. why? Because if my first question gets answered and solved, then, USER1 can try to SELECT FIELD3 FROM TABLE1.. .and it will say something like "you have no permission for FIELD3" insted of this, it can be "This field does not exist".. Thanks. QWERTY IncrediMail - Email has finally evolved - Click Here
Re: too many connection error driving me nutz, need advice
Suggestion.. make a small script called closeall.php .. basically it has some code to force closed the mysql connection opened (be sure to run a close for all openened handles) I have seem some sites code that actually open multiple connections to the same database.. Add this file into php.ini under the script_prepend part (gets run after all other scripts are done) .. Perhaps something is getting left open/not closing properly? On Thu, 17 Jul 2003, Eben Goodman wrote: I run a site that gets a fair amount of traffic throughout the day. For days at a time the site runs smoothly, and then, at seemingly random intervals I get the mysql error Too many connections. The only solution for this is to stop the mysql service and restart it. I changed the max_connections variable to allow for 300 connections instead of the default 100. When I view processes when the error occurs the process list is totally flooded with mysql processes. I am using php to establish connections and using the mysql_connect() function, not mysql_pconnect. I used to use the pconnect function and it seemed to produce the Too many connections error more frequently. I'm at a bit of a loss with this, any tips on how to address this are greatly appreciated. thanks, Eben -- 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 using temporary, filesort
I asked this a couple days ago, but never got an answer, so I'll try to include some more detail: MySQL 4.0.12 on linux 2.4.18... I'm doing a join on 2 tables and trying to optimize it but I always end up with Using temporary; Using filesort and the query takes way too long... What can I do to speed this up? Here's my tables: CREATE TABLE `metardata` ( `stn` char(4) NOT NULL default '', `Hr` int(2) NOT NULL default '0', `min` int(2) NOT NULL default '0', `Day` int(2) NOT NULL default '0', `wmoid` int(10) default NULL, `temp` decimal(6,2) NOT NULL default '0.00', `rh` decimal(6,2) default NULL, `wdsp` decimal(6,2) default NULL, `wddir` char(2) default NULL, `dwpt` decimal(6,2) default NULL, `lpre` decimal(6,2) default NULL, `yearmoda` date NOT NULL default '-00-00', PRIMARY KEY (`stn`,`yearmoda`,`Hr`,`min`) ) TYPE=MyISAM CREATE TABLE `FcstHr_avn` ( `model` varchar(12) NOT NULL default '', `yearmoda` date NOT NULL default '-00-00', `modelruntime` int(2) NOT NULL default '0', `modelhr` int(3) NOT NULL default '0', `fyearmoda` date NOT NULL default '-00-00', `fhr` int(2) NOT NULL default '0', `stn` varchar(4) NOT NULL default '', `temp` decimal(6,2) default NULL, PRIMARY KEY (`yearmoda`,`modelruntime`,`modelhr`,`stn`), KEY `stn` (`stn`,`fyearmoda`,`fhr`), KEY `fcst` (`stn`,`yearmoda`,`modelruntime`) ) TYPE=MyISAM MAX_ROWS=4294967295 explain gives me this output: mysql explain select modelruntime, modelhr, avg(m.temp-f.temp) from metardata as m, FcstHr_avn as f where m.yearmoda=f.fyearmoda and m.stn=f.stn and m.hr=f.fhr and m.yearmoda='2003-07-01' and m.stn='kdro' group by modelruntime+modelhr; +---+--+---+-+-+---+ --+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| +---+--+---+-+-+---+ --+--+ | m | ref | PRIMARY | PRIMARY | 7 | const,const | 18 | Using where; Using temporary; Using filesort | | f | ref | stn,fcst | stn | 11 | const,m.yearmoda,m.Hr | 27 | Using where | +---+--+---+-+-+---+ --+--+ 2 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 support
2. What about fulltext search on UTF8 strings? Unavailable. Eh? Why should this be a special DB feature at all? Correct me if I'm wrong (I didn't have very much contect with fulltexts yet), but can't I just give mysql an UTF8 string to search in an UTF8 column (/data)? Shouldn't this work anyway? (Even if I put UTF8 data in mysql 3.23 by now and just search this?) -yves -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 support
On 17 Jul 2003 at 23:03, Yves Goergen wrote: 2. What about fulltext search on UTF8 strings? Unavailable. Eh? Why should this be a special DB feature at all? Correct me if I'm wrong (I didn't have very much contect with fulltexts yet), but can't I just give mysql an UTF8 string to search in an UTF8 column (/data)? Shouldn't this work anyway? (Even if I put UTF8 data in mysql 3.23 by now and just search this?) FULLTEXT indexes are constructed by dividing the text up into words, which requires that MySQL handle the particular character encoding and character set so as to know which characters (or byte sequences) are parts of words and which aren't. Perhaps you're think of the LIKE operator? -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connection error driving me nutz, need advice
This comment confuses me: I have seem some sites code that actually open multiple connections to the same database.. If I am using php's mysql_connect() function, then every time a query is run this function gets run. Wouldn't this mean that for every query a new connection is opened to the same database? thanks, Eben [EMAIL PROTECTED] wrote: Suggestion.. make a small script called closeall.php .. basically it has some code to force closed the mysql connection opened (be sure to run a close for all openened handles) I have seem some sites code that actually open multiple connections to the same database.. Add this file into php.ini under the script_prepend part (gets run after all other scripts are done) .. Perhaps something is getting left open/not closing properly? On Thu, 17 Jul 2003, Eben Goodman wrote: I run a site that gets a fair amount of traffic throughout the day. For days at a time the site runs smoothly, and then, at seemingly random intervals I get the mysql error Too many connections. The only solution for this is to stop the mysql service and restart it. I changed the max_connections variable to allow for 300 connections instead of the default 100. When I view processes when the error occurs the process list is totally flooded with mysql processes. I am using php to establish connections and using the mysql_connect() function, not mysql_pconnect. I used to use the pconnect function and it seemed to produce the Too many connections error more frequently. I'm at a bit of a loss with this, any tips on how to address this are greatly appreciated. thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show Status data
I'm developing a net-snmp module that executes a 'show status' command on a MySQL server and advertises the values. My choices seem to be super concrete: one server, one set of results, the variables don't change, ever... moderatly concrete: multiple possible servers, one set of results each, the variables don't change, ever.. abstract: multiple servers returning a table of some results. Things are what they are when they are... The problem with the obvious choice is that it's a table of tables of values, and if you've used SNMP much you know where that leads, and try programing for it... So the question is, are the values that it returns likely to be added to/removed from the show status command likely to change much, or can I just write a long but easy to code for MIB with each of the values. -- Michael 5yy5jp Conlen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connection error driving me nutz, need advice
On Thu, 17 Jul 2003, Eben Goodman wrote: This comment confuses me: I have seem some sites code that actually open multiple connections to the same database.. I have worked on some larger sites that 30+ past and current developers worked on.. Some good, some terrible.. The code typically starts up 1 database connection and all scripts should use it.. (Correct way) This code had programmers using things like DBLibraries, DBIMPTOOL, PHPLIB Database objects, etc.. to make more connections for their scripts/includes.. Scenario: 1) script starts - 1 connection opened for duration (this should be it!) 2) does some work 3) script gets included that different programmer wrote, they use DBLibrary object/etc to establish connection to database (current connections = 2) 4) the madness continues I've see as many as unique 5 connections to a DB to build 1 output page.. This is not good and required much cleanup.. But it is known to happen on larger codebases with many wacky developers If I am using php's mysql_connect() function, then every time a query is run this function gets run. Wouldn't this mean that for every query a new connection is opened to the same database? No mysql_connect opens a handle (connection) .. and all subsequent queries using mysql_query are routed thru it.. [EMAIL PROTECTED] wrote: Suggestion.. make a small script called closeall.php .. basically it has some code to force closed the mysql connection opened (be sure to run a close for all openened handles) I have seem some sites code that actually open multiple connections to the same database.. Add this file into php.ini under the script_prepend part (gets run after all other scripts are done) .. Perhaps something is getting left open/not closing properly? On Thu, 17 Jul 2003, Eben Goodman wrote: I run a site that gets a fair amount of traffic throughout the day. For days at a time the site runs smoothly, and then, at seemingly random intervals I get the mysql error Too many connections. The only solution for this is to stop the mysql service and restart it. I changed the max_connections variable to allow for 300 connections instead of the default 100. When I view processes when the error occurs the process list is totally flooded with mysql processes. I am using php to establish connections and using the mysql_connect() function, not mysql_pconnect. I used to use the pconnect function and it seemed to produce the Too many connections error more frequently. I'm at a bit of a loss with this, any tips on how to address this are greatly appreciated. thanks, Eben -- 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: Java API to mySQL
Hello I want to know if there is java API to mySQL like its available for C and C++. Bye Kalika SMS using the Yahoo! Messenger;Download latest version. Look for Connector/J on the MySql site. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
* HELP * MySQL Connection Issue - Access Denied!!
.I just sent this out to the win32 distribution list but no one has replied.can someone on this list please help? The issue should be trivial for experienced MySQL users, I'm just a novice, thanks! Anyone - I've been racking my brain for the majority of the day and scouring through the MySQL manual trying to figure out what is wrong.can anyone please help? Here's my issue: - Extracted files to C:\MySQL - Ran setup.exe (everything went fine, using MySQL Admin 1.4 I can see 'mysql' and 'test' dbs installed) - MySQL is running as a service and MySQL Admin 1.4 shows a green light From the box that is running MySQL I try to set the root password in DOS two ways and when I mention the host (believe it's 'SAMSERVER' according to MySQL Admin tool) it gives me an error. O c:\mysql\bin\mysqladmin -u root password X.. Works fine and the next line goes back to my root directory of c:\mysql\bin O c:\mysql\bin\mysqladmin -u root -h SAMSERVER password X.. Gives me the below error! Mysqladmin: connect to server at 'samserver' failed Error: 'Access denied for user: '[EMAIL PROTECTED]' using password: NO' Also, when I try to connect to the database from another computer via the web using http://samserver.sytes.net:8080/mysqltest.php I get the following error (I've attached my mysqltest.php file too): 'Warning: mysql_connect(): Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) in C:\Apache\Apache2\html\mysqltest.php on line 20 Could not connect' *** Note, I had to change my router settings to forward port 8080 to my web server ip since my ISP blocks port 80. Also, I've successfully installed phpMyAdmin 2.5.2-rc2. When I run 'http://samsever.sytes.net:8080/phpadmin/index.php' I get the following message: Welcome to phpMyAdmin 2.5.2-rc2 phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in config.inc.php and make sure that they correspond to the information given by the administrator of the MySQL server. Error MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Server Settings: - Under MySQL Admin 1.4 on the Environment tab I see the following settings: o Local Host Name: 'SAMSERVER' o Local User Name: ' Administrator' o OS Platform: 'Whistler' o Local IP Address: '192.168.1.102 o ..also, in MyODBC window it finds driver 3.51 and API level 2 and SQL level 1 - Chose 'username' and 'password' for MySQLAdmin.exe application - MyODBC 3.51.06 installed - MySQL Connector/ODBC Running These Software Versions: - Windows XP with service pack 1 - MySQL 4.0.13 for windows - Apache 2.0.47 - PHP 4.3.2 Any help is MUCH appreciated!
Changing ENUM Values
So I've got fairly big sized table (20,000 records) which has an ENUM row. I need to change the name of one of the ENUM options. For example, if the ENUM value was 'blue' before, it needs to be 'purple' now. What's the best way to do this? Thanks, -Aaron --- Aaron Blew Jackson County School District #6 Network/Systems Analyst [EMAIL PROTECTED] (541)494-6900 You can destroy your now by worrying about tomorrow. -- Janis Joplin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld starts and ends
when I enter: cd /usr/local/mysql sudo chown -R mysql data/ sudo echo sudo ./bin/mysqld_safe mysqld starts and ends. I am using a Mac with OSX 10.2.4 and have installed 4.0.4 Any assistance would be greatly appreciated! Helen
mysqlimport question
Hi all I have some data in Excel ( I didn't put it there - a customer did ). I'm setting up an import routine for it, with an Access front-end. I don't like the way Access imports Excel files, though, and I'm thinking about getting the user who will import it to first open the spreadsheet and save it as a csv file. Then I'll use mysqlimport on it. Then the fun will begin. The data really is in a horrible format. The order of each row is important. Can I trust mysqlimport to import the data in the right order, or is it just by chance that that's the way it's happened so far? Thanks! -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL binaries on a K6-2?
Hi all. Another question... I'm trying to diagnose a problem I'm having with MyISAM tables, and it was recommended that I use the binaries instead of compile MySQL myself. I noticed the binaries have 'i686' in their name. Does this mean they are optimised for an i686, or will only work on an i686? I have a K6-2 which only likes things compiled with -march=i586 or -march=k6 Thanks! -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
access denied
mysql CREATE DATABASE csttrainrep - ; ERROR 1044: Access denied for user: '@localhost' to database 'csttrainrep' mysql GRANT ALL ON csttrainrep. TO helen - ; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'TO helen' at line 1 mysql GRANT ALL ON csttrainrep.* TO helen; ERROR 1044: Access denied for user: '@localhost' to database 'csttrainrep' mysql how do i change this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
My hair... I am ready to tear it out. I've been working with Python and the MySQLdb module for a long time, thought I couldn't get snagged by anything, but today I just can't seem to persuade the blasted thing to let me stick an HTML document (a string) into a TEXT column. I'm getting SQL syntax errors, as though the document isn't properly escaped, even though I'm using substitution so that MySQLdb should be taking care of that. I'm wondering if I'm missing something terribly obvious, because this is dead-simple code. Here's the relevant bit and a couple of surrounding lines. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) It's retrieving the document just fine, resulting in a big ol' string (it really is a string, I checked), but that string just won't go into the database. rss_article is a TEXT column in a MyISAM table. I get an SQL syntax error and MySQL tells me to check the syntax with a snippet from right near the beginning of the HTML, where it has lots of (annoying, I suppose) backslashes, quotes and other stuff that is a pain to encode properly by hand. Any help will be most gratefully accepted. My hair will thank you, too. Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
On Wed, Jul 09, 2003 at 11:51:01PM -0500, Paul DuBois wrote: At 14:14 +0700 7/9/03, Dominicus Donny wrote: Sorry, my mistake. It's reindexing then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1. Generally, it is faster to build all your indexes with a single ALTER TABLE statement than to build them one by one with separate ALTER TABLE statements. Single. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 8 days, processed 247,399,591 queries (350/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 support
At 23:03 +0200 7/17/03, Yves Goergen wrote: 2. What about fulltext search on UTF8 strings? Unavailable. Eh? Why should this be a special DB feature at all? Correct me if I'm wrong (I didn't have very much contect with fulltexts yet), but can't I just give mysql an UTF8 string to search in an UTF8 column (/data)? Shouldn't this work anyway? (Even if I put UTF8 data in mysql 3.23 by now and just search this?) Full-text searching works only with single-byte character sets. -yves -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
At 20:49 -0700 7/17/03, Nick Arnett wrote: My hair... I am ready to tear it out. I've been working with Python and the MySQLdb module for a long time, thought I couldn't get snagged by anything, but today I just can't seem to persuade the blasted thing to let me stick an HTML document (a string) into a TEXT column. I'm getting SQL syntax errors, as though the document isn't properly escaped, even though I'm using substitution so that MySQLdb should be taking care of that. I'm wondering if I'm missing something terribly obvious, because this is dead-simple code. Here's the relevant bit and a couple of surrounding lines. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) It's retrieving the document just fine, resulting in a big ol' string (it really is a string, I checked), but that string just won't go into the database. rss_article is a TEXT column in a MyISAM table. I get an SQL syntax error and MySQL tells me to check the syntax with a snippet from right near the beginning of the HTML, where it has lots of (annoying, I suppose) backslashes, quotes and other stuff that is a pain to encode properly by hand. Any help will be most gratefully accepted. My hair will thank you, too. - Have you tried this with *short* HTML documents? What happens? This would help you determine whether it's a length-of-data issue. - Let's see an actual error message (at least the first part), and the corresponding text of the document. - What shows up in the server's query log? Nick -- Nick Arnett Phone/fax: (408) 904-7198 [EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
umm say i reindex after a reimport i usually drop all the indexes first , then add indexes again one by one , seems to be quicker for me especially for fulltext indexes , doing it at once tends to be slower, must crunch the cpu a bit dont know On Wed, Jul 09, 2003 at 11:51:01PM -0500, Paul DuBois wrote: At 14:14 +0700 7/9/03, Dominicus Donny wrote: Sorry, my mistake. It's reindexing then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1. Generally, it is faster to build all your indexes with a single ALTER TABLE statement than to build them one by one with separate ALTER TABLE statements. Single. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 8 days, processed 247,399,591 queries (350/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: Inserting HTML page to TEXT fails... syntax error?! (Python MySQLdb)
-Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, July 17, 2003 9:38 PM To: Nick Arnett; [EMAIL PROTECTED] Well, Paul, just knowing you were on the job inspired me and I finally realized the dumb thing I'd done. The list I'm iterating, urls, comes from a single-column MySQL results list, so it's a list of tuples (url,), not strings. That's why I open url[0], rather than just url in the first line inside the loop. I was smart enough to do that, but not smart enough to remember to use url[0], rather than url, in the INSERT statement. So I was trying to insert a tuple, not a string, and thus MySQL barfed. for url in urls: doc = urllib.urlopen(url[0]).read() dbh.execute(INSERT INTO rss_article (body,url) VALUES (%s, %s),(doc, url)) dbh.execute(UPDATE rss_item SET cached = '1' WHERE url = %s,(url,)) - Have you tried this with *short* HTML documents? What happens? This would help you determine whether it's a length-of-data issue. - Let's see an actual error message (at least the first part), and the corresponding text of the document. - What shows up in the server's query log? By the way, what helped me figure this out was switching the column names and values, which changed the string in the error message to )), rather than the start of the HTML doc. That's when I realized the problem was something about url, not the HTML. This one has bitten me before, but it's been a long time... Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Faster reindexing
Yep... im using fulltext indexes That is, multiple indexing would be the best. Nut now i add more memory and do some fine-tuning on my.cnf, perhaps the result won't be the same. Me fail English? That's unpossible ###___Archon___### - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, July 18, 2003 11:42 AM Subject: Re: Faster reindexing umm say i reindex after a reimport i usually drop all the indexes first , then add indexes again one by one , seems to be quicker for me especially for fulltext indexes , doing it at once tends to be slower, must crunch the cpu a bit dont know On Wed, Jul 09, 2003 at 11:51:01PM -0500, Paul DuBois wrote: At 14:14 +0700 7/9/03, Dominicus Donny wrote: Sorry, my mistake. It's reindexing then. Once i should add 2 key indexes on a huge table. But instead of alter the table in 1 single query, I build each index 1 by 1. Generally, it is faster to build all your indexes with a single ALTER TABLE statement than to build them one by one with separate ALTER TABLE statements. Single. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 8 days, processed 247,399,591 queries (350/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]
