Need help in running MySql in PHP script
Hi, Iam using php 5.0, IIS 5.0, Mysql 5.04 ,win2000 NT and IE as web browser and executed the php script,but got a fatal error describing that undefined fuction mysql_connect() in the file and not getting the desired output. I had uncommented the php.ini file the entry named 'extension=php_mysql.dll' and also set the path for PHPRC = c:\php , also the PATH variable to c:\php;. But still Iam not getting the desired output in browser. Kindly tell me where iam going wrong. Any sort of help is welcome. thanks, Sreedhar. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in running MySql in PHP script
Are you getting a MySQL error? On 5/17/05, madderla sreedhar [EMAIL PROTECTED] wrote: Hi, Iam using php 5.0, IIS 5.0, Mysql 5.04 ,win2000 NT and IE as web browser and executed the php script,but got a fatal error describing that undefined fuction mysql_connect() in the file and not getting the desired output. I had uncommented the php.ini file the entry named 'extension=php_mysql.dll' and also set the path for PHPRC = c:\php , also the PATH variable to c:\php;. But still Iam not getting the desired output in browser. Kindly tell me where iam going wrong. Any sort of help is welcome. thanks, Sreedhar. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving the performance of joins
From: Rod Heyd Tables Version_(1-5) -- ID |PRODUCT_ID |DATA1 |DATA2 |DATA3 |.|DATA_N | -- Table Preferred_Map - MAP_ID |ID |PRODUCT_ID | - SELECT Preferred_Map.PRODUCT_ID, DATA1, DATA2, DATA3 FROM Preferred_Map INNER JOIN Version_1 USING(ID,PRODUCT_ID) WHERE DATA1='x' AND DATA2='y' AND DATA3='z'; *** 1. row *** id: 1 select_type: SIMPLE table: Preferred_Map type: ALL possible_keys: ID,PRODUCT_ID key: NULL key_len: NULL ref: NULL rows: 45191 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: Version_1 type: eq_ref possible_keys: PRIMARY,PRODUCT_ID key: PRIMARY key_len: 4 ref: HiRISE_Test.Preferred_Map.ID rows: 1 Extra: Using where From the explain and the data structure I must conclude that there is no index available that MySQL can use for the where clause. It will then use the estimated number of rows in the two tables to see which table it will start with. There seems to be no alternative then to first use the entire Preferred_Map table, join it with the Version_1 table and then do a table scan to select the rows. You should try to build your db and queries in such a way that there is a step in the the join where MySQL can easily select (using indexes) a very small set of records. Preferrably MySQL should have index to solve (part of) the WHERE conditions before joining the two tables. In that case Version_1 would have been the first table with a very low row count (depending on how much of the WHERE it could solve) and the row count of the second table would have been equally low, resulting in a blazingly fast query. Depending on how many DATA_N columns you have, how many distinct values are present in each table, which columns you will use, etc. you could add indexes for each DATE column or a few multiple-column indexes. If this could not solve the problem, maybe you should redesign the table and use a single field for the DATA and an extra field for the type of data. This requires extra joins, but you could ensure an appropriate index for each of the steps and thus the query could probably be quite fast. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in running MySql in PHP script
Iam using php 5.0, IIS 5.0, Mysql 5.04 ,win2000 NT and IE as web browser and executed the php script,but got a fatal error describing that undefined fuction mysql_connect() in the file and not getting the desired output. This is not a MySQL problem/issue. The error undefined function indicates that the mysql extension is not activated in PHP (in PHP 5.0 there is no mysql included as default for various reasons). You can find more on how to get this working at: http://nl3.php.net/manual/en/faq.migration5.php#faq.migration5.mysql Especially item 2. seems to be appropriate (about the MySQL client libraries being no longer bundled). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help in running MySqli (extension) in PHP script
hi, I use php 5.0,Mysql 5.04 ,IIS 5.0 ,IE in windows2000 NT os and still got errors in running php script with Mysqli functions. when I execute the Php script with Mysql functions in it, it gives fatal error undefined function Mysqli_connect() on line no etc.,, in browser. IS that correct, can I use Mysqli functions in this because I am using Mysql 5.04 (beta) version in PHP documentation it was told that those who are using Mysql 4.0 or higher can use Mysqli in php scripts. if so how i can install Mysqli ( Extension MYSQL) in PHP. I doesn't understand the command use --with-mysqli= mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1. where should I type this command in order to install the mysqli extension so that I can use the Mysql 5.0 version in php script. Any help is welcome. Thanks SReedhar __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help in running MySqli (extension) in PHP script
[snip] in it, it gives fatal error undefined function Mysqli_connect() on line no etc.,, in browser. IS that correct, can I use Mysqli functions in this because I am using Mysql 5.04 (beta) version in PHP documentation it was told that those who are using Mysql 4.0 or higher can use Mysqli in php scripts. if so how i can install Mysqli ( Extension MYSQL) in PHP. [/snip] Please read the following installation instructions http://www.php.net/mysql and pay particular attention to the PHP5 sections. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlxml
Thank you very much, it worked fine!!! I just had a problem with aclocal which was version 1.4-p6, but by updating the automake to 1.7 everything was ok (in case of somebody trying to install and having the same problem). Could someone explain me what's the difference between adding an UDF, a native function or a patch? Is it the same but the format is different depending on the developper's choice? Thanks a lot for your help, Melanie From: Alexander Barkov [EMAIL PROTECTED] To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: mysqlxml Date: Mon, 16 May 2005 22:35:54 +0500 Hello! The patch which can be found at http://mysql.r18.ru/~bar/myxml/ adds XPATH support into MySQL. It is not an UDF. It is a patch for main MySQL sources. In order to build MySQL with XPATH please do the following: 1. Unpack MySQL source tar ball, say mysql-5.0.4.tar.gz: tar -zxf mysql-5.0.4.tag.gz mysql-5.0.4 directory will appear, containing MySQL sources. 2. cd mysql-5.0.4 3. Unpack mysql-xml.tar.gz: tar -zxf mysql-xml.tar.gz After this step item_xmlfunc.cc and item_xmlfunc.h should appear in sql directory. Make sure they have appeared: ls sql/item_xmlfunc.* 4. Apply xml.diff: patch -p1 xml.diff 5. Run: aclocal autoheader automake autoconf 6. Build MySQL in usuall way, for example: ./confugure --prefix=/usr/local/mysql make make install If everything went wrong, you should have two new functions ExtractValue() and UpdateXML(). Enjoy. mel list_php wrote: Ok, I've been trying to install that function this morning and no luck I downloaded 5.0.4beta , compiled it . everything working fine. Trying the udf_example function (make udf_example.so) no problem. Trying to gcc -shared -o item_xmlfunc.so item_xmlfunc.cc it has been complaining about missing files (my_time.h .) and I managed to solve that by downloading the binary and copying the missing file. All is now in /usr/local/include. I'm trying to complie from /usr/local/mysql-5.0.4-beta/sql, but I don't think there's any path problem as it finds for example my_time.h. But know it finds an error in item_func.h about string2mydecimal which is not defined. Questions: - did anybody succeed to install that function (item_xmlfunc.cc) - any special tip about that? - is there an official repository of headers file wheer I could download the whole *.h files stable? - any solution to the error? thanks, Melanie Here is the whole error output: [EMAIL PROTECTED] sql]# gcc -shared -o item_xmlfunc.so item_xmlfunc.cc In file included from item.h:1381, from mysql_priv.h:457, from item_xmlfunc.cc:22: item_func.h: In member function `virtual my_decimal* Item_func_udf_str::val_decimal(my_decimal*)': item_func.h:968: error: `string2my_decimal' undeclared (first use this function) item_func.h:968: error: (Each undeclared identifier is reported only once for each function it appears in.) item_xmlfunc.cc: At global scope: item_xmlfunc.cc:56: error: use of enum `my_xml_node_type' without previous declaration item_xmlfunc.cc:56: error: ISO C++ forbids declaration of `type' with no type item_xmlfunc.cc: In member function `void Item_nodeset_func::prepare(String*)': item_xmlfunc.cc:167: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual Item::Type Item_nodeset_func::type() const': item_xmlfunc.cc:172: error: `XPATH_NODESET' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual String* Item_nodeset_func::val_str(String*)': item_xmlfunc.cc:176: error: `val_nodeset' undeclared (first use this function) item_xmlfunc.cc:188: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual longlong Item_xpath_cast_bool::val_int()': item_xmlfunc.cc:362: error: `XPATH_NODESET' undeclared (first use this function) item_xmlfunc.cc:364: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual longlong Item_func_xpath_position::val_int()': item_xmlfunc.cc:408: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual longlong Item_func_xpath_count::val_int()': item_xmlfunc.cc:426: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc: In member function `virtual double Item_func_xpath_sum::val_real()': item_xmlfunc.cc:443: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:458: error: `MY_XML_NODE_TEXT' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual Item::Type Item_nodeset_to_const_comparator::type() const': item_xmlfunc.cc:481: error: `XPATH_NODESET_CMP' undeclared (first use this function) item_xmlfunc.cc: In member function `virtual longlong Item_nodeset_to_const_comparator::val_int()': item_xmlfunc.cc:488: error: 'class Item' has no member named 'val_nodeset' item_xmlfunc.cc:503: error: `MY_XML_NODE_TEXT' undeclared (first use this
Re: QUERY (TOP)
Shawan, Here you go. mysql select * from spam_stats - where rank =10 ; +-+-+--+---+---+---+ | report_date | report_sender_domain_id | rank | processed | spam | suspected | +-+-+--+---+---+---+ | 2005-04-07 | 46 |1 | 22054 | 19263 |13 | | 2005-04-06 | 46 |2 | 20229 | 16998 |37 | | 2005-04-08 | 46 |3 | 19493 | 16443 |24 | | 2005-04-05 | 46 |4 | 19322 | 15921 | 158 | | 2005-04-29 | 46 |5 | 19241 | 15804 | 8 | | 2005-05-02 | 46 |6 | 47732 | 15287 |82 | | 2005-05-04 | 46 |7 | 91907 | 14275 |25 | | 2005-04-29 | 52 |8 | 15875 | 13422 | 4 | | 2005-05-02 | 52 |9 | 56530 | 13098 |31 | | 2005-05-04 | 52 | 10 |129549 | 12566 |18 | +-+-+--+---+---+---+ 10 rows in set (0.00 sec) Thanks [EMAIL PROTECTED] wrote: Please try my solution before you tell me it's broken, OK? I know you want to see the top 10 spammers for EACH day. That's what I wrote for you. Please try my solution with your data and get back to me with the results and explain to me what's wrong so I can fix it. Thank you for your patience, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace wrote on 05/16/2005 04:41:19 PM: Shawn, query SELECT * FROM spam_stats WHERE rank = 10; will return all rows which I don't want. I need datewise top 10 spam domain. means condition would be serach those rows which are having top 10 spam (means highest) on each day and show the output like which I send earlier. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 11:13:48 AM: shawn I think 2nd query will return only 10 rows. I want out like followings date domain spam 05/05/05 hotmail.com 120 05/05/05 yahoo.com 110 05/05/05 abc.com 99 05/05/05 def.com 80 05/05/05 mnpo.net 79 . like that upto 10 -- 05/06/05 yahoo.com 300 05/06/05 def.com 250 05/06/05 zer.com 200 ..like that upto 10 Each day there are multiple entry from diffrent domains or same domain. I want each day whatever top 10 spam sender domain. thanks [EMAIL PROTECTED] wrote: Seena Blace wrote on 05/16/2005 10:08:15 AM: Any suggestion pl? Seena Blace wrote:hi, here is table description report1 +-+--+--+- +-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+- +-++ | id | int(10) unsigned | | PRI | NULL | auto_increment | | host_id | int(10) unsigned | | MUL | 0 | | | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | | time | datetime | | MUL | -00-00 00:00:00 | | | detected_spam | int(10) unsigned | | | 0 | | | detected_virus | int(10) unsigned | | | 0 | | | processed | int(10) unsigned | | | 0 | | | allowed | int(10) unsigned | | | 0 | | | suspected | int(10) unsigned | | | 0 | | | blocked | int(10) unsigned | | | 0 | | | spam | int(10) unsigned | | | 0 | | | virus | int(10) unsigned | | | 0 | | I WANT REPORT LIKE FOLLOWINGS date sender processed spam suspected I want top 10 spam sender each day. QUery i'm using select date_format(time,'%Y-%d-%m'),report_sender_domain_id, processed ,spam from report1 order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% d-%m') limit 10; Please suggest. thanks It was the weekend. Not everyone lurks on their days off. Try this select date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,processed ,spam ,suspected from report1 order by spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') limit 10; or if there is more than one entry per spammer per day SELECT date_format(time,'%Y-%d-%m') ,report_sender_domain_id ,sum(processed) as processed ,sum(spam) as spam ,sum(suspected) as suspected FROM report1 GROUP BY date_format(time,'%Y-%d-%m') ,report_sender_domain_id ORDER BY spam desc ,report_sender_domain_id ,date_format(time,'%Y-%d-%m') LIMIT 10; That will give you their total stats for each day. Shawn Green Database
alter table - better with MyISAM or InnoDB
Hi I have a table with about 20m rows. It is a MyISAM table. I do a alter table disable keys before loading with LOAD DATA. Re-creating the primary key after import takes a long time. I have increased the myISAM sort buffer to 75MB and the Key buffer is 100MB. Is there anything else I can do to improve the performance of index creation ? Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Numbering rows
In article [EMAIL PROTECTED], Mauricio Pellegrini [EMAIL PROTECTED] writes: Wow, that's simply magic!!! You couldn't imagine how many diferent things I've tried to solve this problem.. And when I thought it was impossible ...your solution worked just fine at once! God bless experienced people!! The reason for trying to do such a weird thing on col_type is that a needed something to use as a pivot condition I'll explain a little further; thanks to you now, I have this in table tbl --- Id xorder item value col_type --- 13 15 0 1 23 15 5 2 33 15 0 3 48 22 7 1 58 22 0 2 610 64 20 1 --- Then I can run this query against tbl SELECT xorder, item, if( col_type=1,value , 0 ) as 'Hon', if( col_type=2,value , 0 ) as 'Gas', if( col_type=3,value , 0 ) as 'Other' FROM tbl GROUP BY xorder, item Which would deliver this final result xorder item Hon Gas Other 3 15 0 5 0 8 22 7 0 0 10 64 20 0 0 There would never be more than three rows for each xorder,item group but, of course there could be less. How do you distinguish between Hon and Gas? When you delete the line with ID 1 and recreate col_type, Gas all of a sudden becomes Hon. Smells like a horribly broken table design. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Improving the performance of joins
You should have a compound index in all your tables on ID+PRODUCT_ID, since that is what you are joining on. Ideally, you should also have an index on the DATA column that you will be using in the WHERE clause. But your structure does really lend itself to this. Going forward, you may think about redesigning your tables if you can. Your structure is very flat, databases are designed for quick searching vertically, meaning finding specific rows. You really want to keep your columns to a minimum if you can so you can take full advantage of indexes. If your data really doesn't change all that often (or not at all), then I think you should create a Preferred Products table. This is sort of what you do in datawarehousing, design for accessibility instead of maintainability. On May 16, 2005, at 6:04 PM, Rod Heyd wrote: I have a question about joins. My situation is as follows: I have 5 tables identical in structure. Each table represents essentially the same data, however, the data in each table represents a different version. The processing involved in generating the values stored in each table may have changed slightly, and therefore the values calculated may vary as well. On top of this structure is a need to identify a particular version of a record as the preferred version. The version tables look something like this: Tables Version_(1-5) --- --- ID |PRODUCT_ID |DATA1 |DATA2 |DATA3 |.|DATA_N | --- --- IDis a unique value across all 5 version tables PRODUCT_ID is unique to a given version table and identifies the source product, but is not unique in all 5 tables, ie, given a PRODUCT_ID=27 all 5 version tables will have a single record with PRODUCT_ID=27. DATA1...DATA_N These are columns with calculated values. Now in order to identify which version of a given PRODUCT_ID is preferred, we have created a map table that looks similar to this: Table Preferred_Map - MAP_ID |ID |PRODUCT_ID | - MAP_ID is an auto_increment primary key ID is the unique ID from the Version tables (unique value) PRODUCT_ID is the PRODUCT_ID from the tables (unique value) So to find information about records that are preferred, we have queries that look like this: SELECT Preferred_Map.PRODUCT_ID, DATA1, DATA2, DATA3 FROM Preferred_Map INNER JOIN Version_1 USING(ID,PRODUCT_ID) WHERE DATA1='x' AND DATA2='y' AND DATA3='z'; We run this same query for each of the 5 version tables and union them together. However the performance is terrible compared with what we get by running the query on a single version table without the join. These queries run anywhere from 6 to 10 times slower with the join than without it, which is pretty much unacceptable for our application. Running explain on the above query yields this: *** 1. row *** id: 1 select_type: SIMPLE table: Preferred_Map type: ALL possible_keys: ID,PRODUCT_ID key: NULL key_len: NULL ref: NULL rows: 45191 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: Version_1 type: eq_ref possible_keys: PRIMARY,PRODUCT_ID key: PRIMARY key_len: 4 ref: HiRISE_Test.Preferred_Map.ID rows: 1 Extra: Using where The bottle neck appears to be the full table scan on the Preferred_Map table. Is there a way to optimize this somehow, to prevent the full table scan? Or are we better off creating a Preferred Products table with copies of all the records that are preferred? At the moment, it appears that the latter option is better than trying to do this with a bunch of unions and joins. Thanks for your help! -Rod -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help in running MySqli (extension) in PHP script
[snip] I read the instalation document particulary but I didn't understand it. They told --with-mysql [=dir] configuration option where it is found or where I should type that please give details so that mysql will works And on thing I want to know is I want MYSQLI (extenstion of MYSQL ) in php not MYSQL which works below 4.0 of Mysql ,functions . [/snip] 1. Always hit reply all when replying to the list in the event that the person you are responding to doesn't see your e-mail for one reason or another. (In this case I was clearing my spam folder when I found it). 2. --with-mysqli=mysql_config_path/mysql_config goes in the configuration line when you configure PHP. (It was a link you needed to click from the previous information provided.) http://us3.php.net/manual/en/ref.mysqli.php 3. Since this is a PHP question you'd be better off asking on a PHP list. http://www.php.net/mailinglists -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there a way
Hello everyone, I am looking for a way to dump a number of tables from mysql Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc) into tabulated (csv) files with tables's column names being on the top of each data column, something like: IDColumn Name 1Column Name 2 1Data 1.1Data 2.1 2Data 2.1Data 2.2 I have done some research in manual and archives, looking for ways to use mysqldump, mysqlimport , or Select INTO OUTFILE.., but did not find a solution, yet. Your help is appreciated, Mikhail Berman Ives Inc (508)476-7007 ext.27 [EMAIL PROTECTED]
Error
The message contains Unicode characters and has been sent as a binary attachment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory Management
So I don't think this is a mysql issue, but I wanted to bounce it off the group anyways and see if anyone had seen similar behavior. I'm running MySQL 4.1.10 on a Dell Poweredge 1850 with 2 EM64T Xeons and 2GB of memory running Redhat Enterprise 3 ES. I've been trying to address some slowness in full-text searching a 120M table. So the first thing that it occurs to me to do is expand the key_buffer_size to 1GB. At this size it should be able to fully store the full-text index in memory. The only problem is that MySQL doesn't seem to be growing, in memory, to the size I've specified. Doing a 'ps aux' reveals that MySQL is using 295MB of real memory and 1GB of virtual memory - which makes me think I'm out of real memory. Performing a 'Top' confirms that I have only 10MB of Real Memory available. However, when I add up all the RSS values in the 'ps aux' it seems like I'm only using about 350MB of memory. To further confirm I cat /proc/meminfo and get this: total: used: free: shared: buffers: cached: Mem: 2073825280 2063302656 10522624 0 176189440 1421033472 Swap: 1875353600 401408 1874952192 MemTotal: 2025220 kB MemFree: 10276 kB MemShared: 0 kB Buffers: 172060 kB Cached: 1387508 kB SwapCached: 220 kB Active: 967092 kB ActiveAnon: 362908 kB ActiveCache: 604184 kB Inact_dirty: 734648 kB Inact_laundry: 173492 kB Inact_clean: 47280 kB Inact_target: 384500 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 2025220 kB LowFree: 10276 kB SwapTotal: 1831400 kB SwapFree: 1831008 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB It seems like I have a very large value for Cached memory. Has anyone seen this before? Can anyone explain it? TIA for indulging me in a slightly off-topic question! -Dan
Re: Improving the performance of joins
On 5/17/05, Jigal van Hemert [EMAIL PROTECTED] wrote: From: Rod Heyd Tables Version_(1-5) -- ID |PRODUCT_ID |DATA1 |DATA2 |DATA3 |.|DATA_N | -- Table Preferred_Map - MAP_ID |ID |PRODUCT_ID | - SELECT Preferred_Map.PRODUCT_ID, DATA1, DATA2, DATA3 FROM Preferred_Map INNER JOIN Version_1 USING(ID,PRODUCT_ID) WHERE DATA1='x' AND DATA2='y' AND DATA3='z'; *** 1. row *** id: 1 select_type: SIMPLE table: Preferred_Map type: ALL possible_keys: ID,PRODUCT_ID key: NULL key_len: NULL ref: NULL rows: 45191 Extra: *** 2. row *** id: 1 select_type: SIMPLE table: Version_1 type: eq_ref possible_keys: PRIMARY,PRODUCT_ID key: PRIMARY key_len: 4 ref: HiRISE_Test.Preferred_Map.ID rows: 1 Extra: Using where From the explain and the data structure I must conclude that there is no index available that MySQL can use for the where clause. It will then use the estimated number of rows in the two tables to see which table it will start with. There seems to be no alternative then to first use the entire Preferred_Map table, join it with the Version_1 table and then do a table scan to select the rows. You should try to build your db and queries in such a way that there is a step in the the join where MySQL can easily select (using indexes) a very small set of records. Preferrably MySQL should have index to solve (part of) the WHERE conditions before joining the two tables. In that case Version_1 would have been the first table with a very low row count (depending on how much of the WHERE it could solve) and the row count of the second table would have been equally low, resulting in a blazingly fast query. Depending on how many DATA_N columns you have, how many distinct values are present in each table, which columns you will use, etc. you could add indexes for each DATE column or a few multiple-column indexes. If this could not solve the problem, maybe you should redesign the table and use a single field for the DATA and an extra field for the type of data. This requires extra joins, but you could ensure an appropriate index for each of the steps and thus the query could probably be quite fast. Thanks for your response, your thoughts more or less confirm my own thinking about this. I can add indexes easily enough, the trouble with the indexes is that we're a little too early in the project to be able to tell which columns or groups of columns will make good indexeswell, there are *some* data columns that we know will make good indexes but there are number of others that are still up in the air. Cheers, Rod -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Management
Dan, Have you tried LOAD INDEX INTO CACHE? See http://dev.mysql.com/doc/mysql/en/load-index.html Mike At 09:06 AM 5/17/2005, Dan Salzer wrote: So I don't think this is a mysql issue, but I wanted to bounce it off the group anyways and see if anyone had seen similar behavior. I'm running MySQL 4.1.10 on a Dell Poweredge 1850 with 2 EM64T Xeons and 2GB of memory running Redhat Enterprise 3 ES. I've been trying to address some slowness in full-text searching a 120M table. So the first thing that it occurs to me to do is expand the key_buffer_size to 1GB. At this size it should be able to fully store the full-text index in memory. The only problem is that MySQL doesn't seem to be growing, in memory, to the size I've specified. Doing a 'ps aux' reveals that MySQL is using 295MB of real memory and 1GB of virtual memory - which makes me think I'm out of real memory. Performing a 'Top' confirms that I have only 10MB of Real Memory available. However, when I add up all the RSS values in the 'ps aux' it seems like I'm only using about 350MB of memory. To further confirm I cat /proc/meminfo and get this: total: used: free: shared: buffers: cached: Mem: 2073825280 2063302656 10522624 0 176189440 1421033472 Swap: 1875353600 401408 1874952192 MemTotal: 2025220 kB MemFree: 10276 kB MemShared: 0 kB Buffers: 172060 kB Cached: 1387508 kB SwapCached: 220 kB Active: 967092 kB ActiveAnon: 362908 kB ActiveCache: 604184 kB Inact_dirty: 734648 kB Inact_laundry: 173492 kB Inact_clean: 47280 kB Inact_target: 384500 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 2025220 kB LowFree: 10276 kB SwapTotal: 1831400 kB SwapFree: 1831008 kB HugePages_Total: 0 HugePages_Free: 0 Hugepagesize: 2048 kB It seems like I have a very large value for Cached memory. Has anyone seen this before? Can anyone explain it? TIA for indulging me in a slightly off-topic question! -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: using OR seems faster than using IN
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a query where I am fetching, in my test 68, rows. Using OR was faster by about 1.5 times, than the same query using IN. Should this be expected, using mysql 5.0.4, on Solaris 8, java 1.5. SELECT name, id FROM table WHERE idx=? OR idx=? OR idx=? ... SELECT name, id FROM table WHERE idx IN(?,?,?) I am also using prepared statements, obviously, and looping through my function 100 times, to get a better idea as to speed, and using junit for the testing. - -- Love is mutual self-giving that ends in self-recovery. Fulton Sheen James Black[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCig6sikQgpVn8xrARAuBzAJ44tfBLXpYelSd0bTu6GDqjnvJEbgCcDAh4 pOr6+PhSZ7YEXKqCZPtKPzU= =zo9u -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why does this query work??!!!
Help! I did this query and it updated everything! update customer_order_history set customerID=123 456 789 0123 where invoiceid-12217; I typed a '-' rather than an '='. I haven't did a commit or a rollback saving point. Is there any other way To undo this update?? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A question of joining...
Hi, I am having problems with the JOIN function. MESSAGES memberID_1, memberID_2, Message MEMBERS id, name I can only manage to replace the memberID_1 in MESSAGES with the name in MEMBERS, I can not replace both memberID_1 and memberID_2 with name. Please can someone tell me what I should be looking for in the help documents. -- David Scott
Re: A question of joining...
Critters wrote: Hi, I am having problems with the JOIN function. MESSAGES memberID_1, memberID_2, Message MEMBERS id, name I can only manage to replace the memberID_1 in MESSAGES with the name in MEMBERS, I can not replace both memberID_1 and memberID_2 with name. Please can someone tell me what I should be looking for in the help documents. -- David Scott You need a join for each lookup. Something like: SELECT mem1.name, mem2.name, mess.Message FROM messages mess JOIN members mem1 ON mess.memberID_1 = mem1.id JOIN members mem2 ON mess.memberID_2 = mem2.id WHERE ...; If it is possible that either memberID_1 or memberID_2 is NULL, or doesn't point to a valid member row, then you should replace JOIN with LEFT JOIN. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why does this query work??!!!
Hi, your query updates all the records where invoiceid != 12217 That's the same than : update customer_order_history set customerID=123 456 789 0123 where invoiceid != 12217; because invoiceid-12217 !=0. It's so true. have you a backup if the command is old (no flashback query in mysql) ? Mathias Selon [EMAIL PROTECTED]: Help! I did this query and it updated everything! update customer_order_history set customerID=123 456 789 0123 where invoiceid-12217; I typed a '-' rather than an '='. I haven't did a commit or a rollback saving point. Is there any other way To undo this update?? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A question of joining...
Thankyou, That worked a treat! Thankyou so very much -- David Scott - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Critters [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 17, 2005 5:01 PM Subject: Re: A question of joining... Critters wrote: Hi, I am having problems with the JOIN function. MESSAGES memberID_1, memberID_2, Message MEMBERS id, name I can only manage to replace the memberID_1 in MESSAGES with the name in MEMBERS, I can not replace both memberID_1 and memberID_2 with name. Please can someone tell me what I should be looking for in the help documents. -- David Scott You need a join for each lookup. Something like: SELECT mem1.name, mem2.name, mess.Message FROM messages mess JOIN members mem1 ON mess.memberID_1 = mem1.id JOIN members mem2 ON mess.memberID_2 = mem2.id WHERE ...; If it is possible that either memberID_1 or memberID_2 is NULL, or doesn't point to a valid member row, then you should replace JOIN with LEFT JOIN. Michael -- 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 database problems
Are your php scripts by any chance called phpWebSite? If so, you might find more help here: http://www.phpwsforums.com/ What you're getting is almost definitely a PHP script catching some kind of error trying to connect to the database server. You should have a file called configure.php or something similar. Make sure the database settings are correct. kgt Dwayne Hottinger wrote: Greetings all, Im new to mysql and have inherited several mysql databases and everything has been going well until lately. Most of my webpages come from mysql databases with php scripts. Now I am getting the message Unable to load database indicated by configuration file or something similiar when trying to connect to any database running on the server when the mysql user is running @localhost. I can however login at terminal as the mysql user and look at the database with no problems. My mysql version is 3.23.58 (upgraded from yum), php version 4.3.10, server is Fedora Core 2 kernel 2.6.5-1.358smp. Im pretty new to mysql so be gentle and easy in any help. Everything was working fine prior to Friday of last week. Mysqld.log show nothing other than start and restarts that I initiated trying to get things working. thanks, ddh -- Dwayne Hottinger Network Administrator Harrisonburg City Public Schools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why does this query work??!!!
[EMAIL PROTECTED] wrote: Help! I did this query and it updated everything! update customer_order_history set customerID=123 456 789 0123 where invoiceid-12217; WHERE expects an integer (and will convert other types to an integer). 0 is false, null is null, everything else is true. Hence, WHERE invoiceid-12217 is true for every row where invoiceid is neither 12217 nor null. I expect you changed every row except the one you wanted. I typed a '-' rather than an '='. I haven't did a commit or a rollback saving point. Is there any other way To undo this update?? Restore from backup. Thanks. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to set trabnsaction isoloation in my.ini file
I have MySql 4.1.12 installed on an XP box in C:\mysql. I have the settings below in my C;\MySql\my.ini file: I have the transaction-isolation set to READ-COMMITTED but the server seems to ignore this. When the server starts up the tx_isolation as reported by mysqladmin extended-status is REPEATABLE-READ. I can set it manually from the mysql command line and that works. The tx_isolation is then reported as READ-COMMITTED. Its almost as if the server is not reading the my.ini file. How can I set the tx isolation level on startup? Does anyone have any ideas? Thanks -Adam [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir=C:/MySQL/ #Path to the database root datadir=C:/MySQL/Data/ # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=INNODB # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=100 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # Qcache_lowmem_prunes status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable open-files-limit in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=13M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) thread_cache_size=8 #*** MyISAM Specific options # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than this, the index will be created # through the key cache (which is slower). myisam_max_sort_file_size=100G # If the temporary file used for fast index creation would be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. This is mainly used to force long character keys in # large tables to use the slower key cache method to create the index. myisam_max_extra_sort_file_size=100G # If the temporary file used for fast index creation would be bigger # than using the key cache by the amount specified here, then prefer the # key cache method. This is mainly used to force long character keys in # large tables to use the slower key cache method to create the index. myisam_sort_buffer_size=26M # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=8M # Size of the buffer used for doing full table scans of MyISAM tables. # Allocated per thread, if a full scan is needed. read_buffer_size=64K read_rnd_buffer_size=256K # This buffer is allocated when MySQL needs to rebuild the index in # REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE # into an empty table. It is allocated per thread so be careful with # large settings. sort_buffer_size=256K #*** INNODB Specific options *** # Use this option if you have a MySQL server with InnoDB support enabled # but you do not plan to use it. This will save memory and disk space # and speed up some things. #skip-innodb # Additional memory pool that is used by InnoDB to store metadata # information. If InnoDB requires more memory for this purpose it will # start to allocate it from the
Re: why does this query work??!!!
NNGau, It works because 'where invoiceid-12217' is true for every invoice except invoice 12217. To undo it, assuming your server is running a binary log, back up the table in its current state to a safe place, restore the most recent backup of the table, then run mysqlbinlog for that database (-d) and that table (-t) from the timepoint of the backup (-start-datetime=...) to just before your typo (-stop-datetime=...). PB - [EMAIL PROTECTED] wrote: Help! I did this query and it updated everything! update customer_order_history set customerID=123 456 789 0123 where invoiceid-12217; I typed a '-' rather than an '='. I haven't did a commit or a rollback saving point. Is there any other way To undo this update?? Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.12 - Release Date: 5/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Export from Access
Hi, can anyone explain how I would export a database created in Access to MySQL using PHPMyAdmin - I can't seem to import the data as csv or txt. Thanks Steven
Re: How to set trabnsaction isoloation in my.ini file
Adam Lipscombe wrote: I have MySql 4.1.12 installed on an XP box in C:\mysql. I have the settings below in my C;\MySql\my.ini file: I have the transaction-isolation set to READ-COMMITTED but the server seems to ignore this. When the server starts up the tx_isolation as reported by mysqladmin extended-status is REPEATABLE-READ. I can set it manually from the mysql command line and that works. The tx_isolation is then reported as READ-COMMITTED. Its almost as if the server is not reading the my.ini file. It may not be. Do you specify this file when you start the server on the command line? If you don't then you need to move my.ini to the windows directory: When the MySQL server starts on Windows, it looks for options in two files: the `my.ini' file in the Windows directory, and the `C:\my.cnf' file. The Windows directory typically is named something like `C:\WINDOWS' or `C:\WinNT'. You can determine its exact location from the value of the |WINDIR| environment variable using the following command: C:\ echo %WINDIR% MySQL looks for options first in the `my.ini' file, then in the `my.cnf' file. However, to avoid confusion, it's best if you use only one file. If your PC uses a boot loader where the |C:| drive isn't the boot drive, your only option is to use the `my.ini' file. Whichever option file you use, it must be a plain text file.
Re: How to set trabnsaction isoloation in my.ini file
Adam Lipscombe wrote: I have MySql 4.1.12 installed on an XP box in C:\mysql. I have the settings below in my C;\MySql\my.ini file: I have the transaction-isolation set to READ-COMMITTED but the server seems to ignore this. When the server starts up the tx_isolation as reported by mysqladmin extended-status is REPEATABLE-READ. I can set it manually from the mysql command line and that works. The tx_isolation is then reported as READ-COMMITTED. Its almost as if the server is not reading the my.ini file. transaction-isolation = READ-OMMITTED Have you tried leaving out the spaces? transaction-isolation=READ-OMMITTED -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why does this query work??!!! - THANKS!!!
Thanks everyone!! It was just a typo and everything was updated :( That's okay, I got my Admin guy to restore backup files. I'll just Have to get in the habit of using commit and rollback. -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 17, 2005 10:17 AM Cc: mysql@lists.mysql.com Subject: Re: why does this query work??!!! NNGau, It works because 'where invoiceid-12217' is true for every invoice except invoice 12217. To undo it, assuming your server is running a binary log, back up the table in its current state to a safe place, restore the most recent backup of the table, then run mysqlbinlog for that database (-d) and that table (-t) from the timepoint of the backup (-start-datetime=...) to just before your typo (-stop-datetime=...). PB - [EMAIL PROTECTED] wrote: Help! I did this query and it updated everything! update customer_order_history set customerID=123 456 789 0123 where invoiceid-12217; I typed a '-' rather than an '='. I haven't did a commit or a rollback saving point. Is there any other way To undo this update?? Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.12 - Release Date: 5/17/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to set trabnsaction isoloation in my.ini file
It may not be. Do you specify this file when you start the server on the command line? If you don't then you need to move my.ini to the windows directory: You are quite right. When I moved the my.ini file to c:\windows it worked. I assumed that it would work 'cos the mySQL installer created the file in the installation dir. Many thanks - Adam -Original Message- From: Kristen G. Thorson [mailto:[EMAIL PROTECTED] Sent: 17 May 2005 17:23 To: Adam Lipscombe Cc: mysql@lists.mysql.com Subject: Re: How to set trabnsaction isoloation in my.ini file Adam Lipscombe wrote: I have MySql 4.1.12 installed on an XP box in C:\mysql. I have the settings below in my C;\MySql\my.ini file: I have the transaction-isolation set to READ-COMMITTED but the server seems to ignore this. When the server starts up the tx_isolation as reported by mysqladmin extended-status is REPEATABLE-READ. I can set it manually from the mysql command line and that works. The tx_isolation is then reported as READ-COMMITTED. Its almost as if the server is not reading the my.ini file. It may not be. Do you specify this file when you start the server on the command line? If you don't then you need to move my.ini to the windows directory: When the MySQL server starts on Windows, it looks for options in two files: the `my.ini' file in the Windows directory, and the `C:\my.cnf' file. The Windows directory typically is named something like `C:\WINDOWS' or `C:\WinNT'. You can determine its exact location from the value of the |WINDIR| environment variable using the following command: C:\ echo %WINDIR% MySQL looks for options first in the `my.ini' file, then in the `my.cnf' file. However, to avoid confusion, it's best if you use only one file. If your PC uses a boot loader where the |C:| drive isn't the boot drive, your only option is to use the `my.ini' file. Whichever option file you use, it must be a plain text file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there a way
SELECT ... INTO OUTFILE 'filename' export_options': http://dev.mysql.com/doc/mysql/en/select.html The 'export_options' are the same as this syntax: http://dev.mysql.com/doc/mysql/en/load-data.html That may work for you. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 17 May 2005, Berman, Mikhail wrote: Hello everyone, I am looking for a way to dump a number of tables from mysql Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc) into tabulated (csv) files with tables's column names being on the top of each data column, something like: IDColumn Name 1Column Name 2 1Data 1.1Data 2.1 2Data 2.1Data 2.2 I have done some research in manual and archives, looking for ways to use mysqldump, mysqlimport , or Select INTO OUTFILE.., but did not find a solution, yet. Your help is appreciated, Mikhail Berman Ives Inc (508)476-7007 ext.27 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export from Access
On Tue, 17 May 2005 17:17:31 +0100 S.D.Price [EMAIL PROTECTED] wrote: Hi, can anyone explain how I would export a database created in Access to MySQL using PHPMyAdmin - I can't seem to import the data as csv or txt. Acess should allow exporting to CSV. Otherwise you can skip phpMyAdmin and just use ODBC - check out MyODBC on mysql.com. Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql client update
Hi, Where should I look to find information about having clients notified when a table/record is updated? I have a situation where multiple clients will each have an open database connection, and I would like for them to be notified when the database is modified by a member of the group. Something like a trigger that informs all clients that a new record has been added for example. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Convert subquery
I am moving some code that was developed against MySQL 4.1 to a server with 3.23, and I have one query that uses a subquery. I'm having a bear of a time wrapping my mind around how to convert this. The original query is this: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) The table `page_links` contains parent (page_links.page_id) and child (page_links.child_id) mappings for items in `page`. Given a page.page_id=6, I want all items in `page` that satisfy the following: page.page_id6 and page.page_idpage_links.child_id only where page_links.page_id=6 So, for a given page, I want all possible child page candidates (condition is that current child pages and the given page are not possible candidates). Thanks in advance! kgt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is there a way
Atle, Thank you for your help Mikhail Berman Ives Inc (508)476-7007 ext.27 [EMAIL PROTECTED] -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 17, 2005 1:16 PM To: Berman, Mikhail Cc: mysql@lists.mysql.com Subject: Re: Is there a way SELECT ... INTO OUTFILE 'filename' export_options': http://dev.mysql.com/doc/mysql/en/select.html The 'export_options' are the same as this syntax: http://dev.mysql.com/doc/mysql/en/load-data.html That may work for you. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 17 May 2005, Berman, Mikhail wrote: Hello everyone, I am looking for a way to dump a number of tables from mysql Ver 14.7 Distrib 4.1.9, for sun-solaris2.8 (sparc) into tabulated (csv) files with tables's column names being on the top of each data column, something like: IDColumn Name 1Column Name 2 1Data 1.1Data 2.1 2Data 2.1Data 2.2 I have done some research in manual and archives, looking for ways to use mysqldump, mysqlimport , or Select INTO OUTFILE.., but did not find a solution, yet. Your help is appreciated, Mikhail Berman Ives Inc (508)476-7007 ext.27 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert subquery
Kristen G. Thorson [EMAIL PROTECTED] wrote on 05/17/2005 02:37:12 PM: I am moving some code that was developed against MySQL 4.1 to a server with 3.23, and I have one query that uses a subquery. I'm having a bear of a time wrapping my mind around how to convert this. The original query is this: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) The table `page_links` contains parent (page_links.page_id) and child (page_links.child_id) mappings for items in `page`. Given a page.page_id=6, I want all items in `page` that satisfy the following: page.page_id6 and page.page_idpage_links.child_id only where page_links.page_id=6 So, for a given page, I want all possible child page candidates (condition is that current child pages and the given page are not possible candidates). Thanks in advance! kgt Original: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) JOINED: SELECT page.page_id, page.page_keyword FROM page LEFT JOIN page_links on page.page_ID = page_links.child_id and page_links.page_id=6 WHERE page.page_id6 AND page_links.page_id is null The trick to doing a NOT IN() query as a JOIN query is that you try to make an _optional_ match on your columns/conditions (LEFT JOIN...ON...) then look for those rows where the match-up conditions are *not* met (WHERE table_on_the_right.nonnullablecolumnname is NULL). I have assumed that in the table page_links, page_id is not a nullable column. So by checking for a null where there shouldn't be one, you detect where the matching conditions in your LEFT JOIN phrase weren't met. Does that make sense? I know that talking in the negative can be confusing so here it is from the other direction. A LEFT JOIN will make sure that all of the rows of the LEFT table (the first table listed) are available to be processed against the conditions of the WHERE clause but only those rows from the table on the right-hand side of the phrase that meet the ON conditions will be in that same list. All column values (even those that are not normally nullable) for the table on the right side of a LEFT JOIN will be NULL if there is no match that meets the ON condition(s). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Convert subquery
2005/5/17, Kristen G. Thorson [EMAIL PROTECTED]: I am moving some code that was developed against MySQL 4.1 to a server with 3.23, and I have one query that uses a subquery. I'm having a bear of a time wrapping my mind around how to convert this. The original query is this: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) With some minimal test data, this seems to work, at first sight: SELECT page.page_id FROM page LEFT JOIN page_links ON page_links.child_id=page.page_id WHERE (page_links.page_id 6 OR page_links.page_id IS NULL) AND page.page_id 6 JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert subquery
Shawn, Thanks so much, this is exactly what I was looking for. I was having trouble getting the condition (page_links.page_id=6) in the right place. I had gotten as far as SELECT page.page_id, page_keyword FROM page LEFT JOIN page_links ON page.page_id=page_links.child_id WHERE page.page_id6 AND page_links.child_id is null; Which of course didn't give me what I was looking for. And of course it never occured to me I could put the missing condition in the ON clause. This one was a challenge for me, as I normally don't have to do anything beyond very simple queries. Thanks again! kgt [EMAIL PROTECTED] wrote: Kristen G. Thorson [EMAIL PROTECTED] wrote on 05/17/2005 02:37:12 PM: I am moving some code that was developed against MySQL 4.1 to a server with 3.23, and I have one query that uses a subquery. I'm having a bear of a time wrapping my mind around how to convert this. The original query is this: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) The table `page_links` contains parent (page_links.page_id) and child (page_links.child_id) mappings for items in `page`. Given a page.page_id=6, I want all items in `page` that satisfy the following: page.page_id6 and page.page_idpage_links.child_id only where page_links.page_id=6 So, for a given page, I want all possible child page candidates (condition is that current child pages and the given page are not possible candidates). Thanks in advance! kgt Original: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) JOINED: SELECT page.page_id, page.page_keyword FROM page LEFT JOIN page_links on page.page_ID = page_links.child_id and page_links.page_id=6 WHERE page.page_id6 AND page_links.page_id is null The trick to doing a NOT IN() query as a JOIN query is that you try to make an _optional_ match on your columns/conditions (LEFT JOIN...ON...) then look for those rows where the match-up conditions are *not* met (WHERE table_on_the_right.nonnullablecolumnname is NULL). I have assumed that in the table page_links, page_id is not a nullable column. So by checking for a null where there shouldn't be one, you detect where the matching conditions in your LEFT JOIN phrase weren't met. Does that make sense? I know that talking in the negative can be confusing so here it is from the other direction. A LEFT JOIN will make sure that all of the rows of the LEFT table (the first table listed) are available to be processed against the conditions of the WHERE clause but only those rows from the table on the right-hand side of the phrase that meet the ON conditions will be in that same list. All column values (even those that are not normally nullable) for the table on the right side of a LEFT JOIN will be NULL if there is no match that meets the ON condition(s). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Convert subquery
I actually had something similar to this at one point, but I never got it working. Your query returned 148 rows, which I can tell you right away can't be right since there are only 130 items in `page`. I believe the problem is that it doesn't take into account that a page may be a child page of several different parents. Therefore this part: SELECT page.page_id, page_keyword FROM page LEFT JOIN page_links ON page.page_id=page_links.child_id Will return more than 130 rows: mysql SELECT page.page_id, page_keyword, page_links.page_id FROM page LEFT JOIN page_links ON page.page_id=page_links.child_id; +--+--++ | page.page_id | page_keyword | page_links.page_id | +--+--++ |5 | training | 4 | |5 | training | 6 | |5 | training | 17 | |4 | services | 6 | |4 | services | 12 | |4 | services | 13 | |4 | services | 14 | |4 | services | 15 | Then adding the condition ( page_links.page_id 6 OR page_links.child_id IS NULL ) would still leave duplicate rows: +--+--++ | page.page_id | page_keyword | page_links.page_id | +--+--++ |5 | training | 4 | |5 | training | 17 | |4 | services | 12 | |4 | services | 13 | |4 | services | 14 | |4 | services | 15 | Shawn has already posted a working answer, so this is just me sharing how proud I am that I figured it out well enough to know why it doesn't work, which I guess is as good a learning tool as any! Thanks, kgt Jan Pieter Kunst wrote: 2005/5/17, Kristen G. Thorson [EMAIL PROTECTED]: I am moving some code that was developed against MySQL 4.1 to a server with 3.23, and I have one query that uses a subquery. I'm having a bear of a time wrapping my mind around how to convert this. The original query is this: SELECT page.page_id, page.page_keyword FROM page WHERE page.page_id6 AND page.page_id NOT IN ( SELECT page_links.child_id FROM page_links WHERE page_links.page_id=6 ) With some minimal test data, this seems to work, at first sight: SELECT page.page_id FROM page LEFT JOIN page_links ON page_links.child_id=page.page_id WHERE (page_links.page_id 6 OR page_links.page_id IS NULL) AND page.page_id 6 JP .
Database Table Date Assistance
Hello, I would like to do the following: I am creating a site where people can add items to a cart. In order to keep items for [X] amount of days, I would like to create a database table in mysql to hold a 'itemRef' and a 'Date'. Then in a relationship table I can hold the 'itemRef' and 'items' they have choosen. I think this would be simple. But there are a lot of choices for the date field. I would like a date field that I can insert a now() or something, when I insert. And then later, through Java code, query and find all dates that are greater than [X] amount of days, and delete them to keep the database clean? So my question would be, A) which date type field should I create. B) how to insert now() C) can I run one query to find out if the date field is greater than [X] days? Any help would be appreciated. Sincerely Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql client update
I'm no database expert, so I may be wrong but I do not think this functionality exists. Usually this type of notification is handled at the application level. That is build an application layer on top of the database which handles all reads and writes to the database. This layer would also keep track of all connected clients and would notify when a record is modified/inserted/created. Cheers, Philip. -Original Message- From: Eric White [mailto:[EMAIL PROTECTED] Sent: May 17, 2005 10:44 AM To: mysql@lists.mysql.com Subject: mysql client update Hi, Where should I look to find information about having clients notified when a table/record is updated? I have a situation where multiple clients will each have an open database connection, and I would like for them to be notified when the database is modified by a member of the group. Something like a trigger that informs all clients that a new record has been added for example. Thanks 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: Database Table Date Assistance
Hi Scott,
I show you timestamp usage, but there is also datetime and date. you can read
detailled infos at :
http://dev.mysql.com/doc/mysql/en/datetime.html
mysql create table items(itemRef varchar(10), dat timestamp default
current_timestamp);
Query OK, 0 rows affected (0.20 sec)
mysql
mysql
mysql insert into items (itemRef) values('value 1'),('value 2');
mysql insert into items (itemRef) values('value 3');
mysql select * from items;
+-+-+
| itemRef | dat |
+-+-+
| value 1 | 2005-05-17 23:55:10 |
| value 2 | 2005-05-17 23:55:10 |
| value 3 | 2005-05-17 23:57:59 |
+-+-+
3 rows in set (0.00 sec)
mysql insert into items (itemRef,dat) values('value 4','2005-04-10'),('value
5','2004-02-10'),('value 6','2005-05-18');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql select * from items;
+-+-+
| itemRef | dat |
+-+-+
| value 1 | 2005-05-17 23:55:10 |
| value 2 | 2005-05-17 23:55:10 |
| value 3 | 2005-05-17 23:57:59 |
| value 4 | 2005-04-10 00:00:00 |
| value 5 | 2004-02-10 00:00:00 |
| value 6 | 2005-05-18 00:00:00 |
+-+-+
6 rows in set (0.00 sec)
mysql select * from items where dat = date_add(now(),interval -2 day);
+-+-+
| itemRef | dat |
+-+-+
| value 4 | 2005-04-10 00:00:00 |
| value 5 | 2004-02-10 00:00:00 |
+-+-+
2 rows in set (0.00 sec)
mysql select * from items where dat = date_add(current_timestamp,interval -10
day);
+-+-+
| itemRef | dat |
+-+-+
| value 4 | 2005-04-10 00:00:00 |
| value 5 | 2004-02-10 00:00:00 |
+-+-+
2 rows in set (0.00 sec)
So you can delete rather than the select above.
Mathias
Selon Scott Purcell [EMAIL PROTECTED]:
Hello,
I would like to do the following: I am creating a site where people can add
items to a cart. In order to keep items for [X] amount of days, I would like
to create a database table in mysql to hold a 'itemRef' and a 'Date'. Then in
a relationship table I can hold the 'itemRef' and 'items' they have choosen.
I think this would be simple.
But there are a lot of choices for the date field. I would like a date field
that I can insert a now() or something, when I insert. And then later,
through Java code, query and find all dates that are greater than [X] amount
of days, and delete them to keep the database clean?
So my question would be,
A) which date type field should I create.
B) how to insert now()
C) can I run one query to find out if the date field is greater than [X]
days?
Any help would be appreciated.
Sincerely
Scott
--
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 client update
Hi, logbin can help you to track transactions and then write your notification code. trigger in mysql 5.x will help you a lot (v5 in still in beta state now). Mathias Selon Philip Denno [EMAIL PROTECTED]: I'm no database expert, so I may be wrong but I do not think this functionality exists. Usually this type of notification is handled at the application level. That is build an application layer on top of the database which handles all reads and writes to the database. This layer would also keep track of all connected clients and would notify when a record is modified/inserted/created. Cheers, Philip. -Original Message- From: Eric White [mailto:[EMAIL PROTECTED] Sent: May 17, 2005 10:44 AM To: mysql@lists.mysql.com Subject: mysql client update Hi, Where should I look to find information about having clients notified when a table/record is updated? I have a situation where multiple clients will each have an open database connection, and I would like for them to be notified when the database is modified by a member of the group. Something like a trigger that informs all clients that a new record has been added for example. Thanks 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: mysql client update
Philip Denno wrote: I'm no database expert, so I may be wrong but I do not think this functionality exists. Usually this type of notification is handled at the application level. That is build an application layer on top of the database which handles all reads and writes to the database. This layer would also keep track of all connected clients and would notify when a record is modified/inserted/created. Cheers, Philip. -Original Message- From: Eric White [mailto:[EMAIL PROTECTED] Sent: May 17, 2005 10:44 AM To: mysql@lists.mysql.com Subject: mysql client update Hi, Where should I look to find information about having clients notified when a table/record is updated? I have a situation where multiple clients will each have an open database connection, and I would like for them to be notified when the database is modified by a member of the group. Something like a trigger that informs all clients that a new record has been added for example. Thanks in advance. Regards Hi, Triggers were added at 5.0.2 They have some limitations but are documented in the manual at http://dev.mysql.com/doc/mysql/en/create-trigger.html Regards -- David Logan South Australia when in trouble, or in doubt run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
varchar(10) to decimal
I need to change format from varchar(10) to decimal. When I alter the table the data is trimmed. What I'm doing wrrong? TH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Opteron HOWTO - #mysql Freenode
Over the last week I added in lots of comments pasted in from various places. I'd appreciate those running with Opteron and MySQL to have a close look at the WIKI and make any amendments/suggestions. http://hashmysql.org/index.php?title=Opteron_HOWTO My Opteron server will be here shortly and I'll do some performance testing on RAID5 (8 disks) versus RAID 10 (8 disks, 2 channels). I'll also do some performance tests with 128MB cache and 512MB cache on the SCSI card to let you know if that makes a difference. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convert subquery
2005/5/17, Kristen G. Thorson [EMAIL PROTECTED]: I actually had something similar to this at one point, but I never got it working. Your query returned 148 rows, which I can tell you right away can't be right since there are only 130 items in `page`. I believe the problem is that it doesn't take into account that a page may be a child page of several different parents. Therefore this part: SELECT page.page_id, page_keyword FROM page LEFT JOIN page_links ON page.page_id=page_links.child_id Will return more than 130 rows: I suppose the duplicate rows problem with my answer could be solved by using 'SELECT DISTINCT' instead of 'SELECT'. Still, Shawns solution is definately more elegant. JP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Export from Access
You can try a freeware utility you can find at: http://www.bullzip.com//products.php (I have tried it and works quite well) Additionally you could use DBDesigner (www.fabforce.com) and make a reverse engineer of you database and export it to a Sql file. Hope this help you. Best regards, Alvaro - Original Message - From: S.D.Price [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Tuesday, May 17, 2005 11:17 AM Subject: Export from Access Hi, can anyone explain how I would export a database created in Access to MySQL using PHPMyAdmin - I can't seem to import the data as csv or txt. Thanks Steven -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO - #mysql Freenode
Richard Dale wrote: Over the last week I added in lots of comments pasted in from various places. I'd appreciate those running with Opteron and MySQL to have a close look at the WIKI and make any amendments/suggestions. http://hashmysql.org/index.php?title=Opteron_HOWTO My Opteron server will be here shortly and I'll do some performance testing on RAID5 (8 disks) versus RAID 10 (8 disks, 2 channels) Awesome! Thats great news... I reconfigured one of our slaves from RAID5 to RAID1 and setup the controller as writeback and caching the stripes. I'm getting about 75% additional performance. I wanted to put the controller on additional channels but it turns out that I need a stupid proprietary cable from Dell to accomplish this. I'm not sure what benchmark I could see from using two channels then. My gut though in our config says that we won't see any performance increase since I'm not maxing out the IO on each channel (but I might be wrong). I'd love to see your numbers here. -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Passing parameter to mysql script
Hi all , I need to pass paramter to mysql script , for example , my script , INSERT INTO SYSTEM_PARAMETER_TABLE (branch_code,param_name,station_id,param_value) VALUES(?,?,?,?) Anybody have any ideas how to do that ? Thanks - weetat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator Broke MySQL
First I would like to start off by saying I am a complete noobie to MySQL. I have MySQL 4.0 running on a freebsd machine. All was fine and dandy till I tried to connect to it using the W32 MySQL Administrator tool. Now if I try and execute a script against it I get DBI connect failed : Access denied for user: '[EMAIL PROTECTED]'. I can get my scripts to work if I start mysql with ./safe_mysqld --skip-grant-tables . That will let the scripts run until I try and change any users privs or passwords. I have noticed that when I run mysql with the --skip-grant-tables flag I get all of my dbs showing in mysqlshow, and as soon as I try and change a user it changes to just showing just a test db when I execute mysql show. ./mysqlshow +---+ | Databases | +---+ | bayesspam | | bulletin | | count | | mysql | | policyd | | radius| | test | | users | +---+ ./mysqlshow +---+ | Databases | +---+ | test | +---+ Please give me some pointers on where to go to fix this. I have googled and searched the archives and I haven't come up with anything that works. --Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help in locating configuration file
Hi, Iam using Mysql5.04 What does it mean, To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1. in this,is mysql_config file is same as my.ini and the path to it in windows 2000 NT (on my system is )c:\Program Files\mysql\mysql server 5.0 ; is it correct, then after editing the php.ini file why doesnot it is working. Any help is welcome. Thanks Sreedhar __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Passing parameter to mysql script
Hi , look at prepared statement : http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html?rated=0 Mathias Selon weetat [EMAIL PROTECTED]: Hi all , I need to pass paramter to mysql script , for example , my script , INSERT INTO SYSTEM_PARAMETER_TABLE (branch_code,param_name,station_id,param_value) VALUES(?,?,?,?) Anybody have any ideas how to do that ? Thanks - weetat -- 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]
