need white papers on performace tuning of full text indexing
anyone know where i can get these, will highly appreciate, this is a feature of mysql 5.0 and my platform is linux.
Statement release
Hello, I would like to solve this problem. I get a Connection instance from a connection pool, create some statements and prepared statements, do some database operations, close all statements and return the connection to the pool. My question is, are the statements released (garbage collected) after calling Statement.close() or is it necessary to set them to null? I would like to avoid a possible memory leak because the connection is not physically closed, only returned to the pool to be reused later. The second question is - if a statement is not properly closed due to an exception thrown before the .close() method is called, could it cause a memory leak as well? I use the latest Connector of the 3.1.x branch and MySQL 4.1.x. Filip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: error 1016 : cant open ibd file even though it exists
Hello David. Thanks for the prompt response. The permissions were the first thing that I checked when I got the error. In fact, I even tried giving 777 permissions on the .ibd files. No results. Regards, Rithish. -Original Message- From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] Sent: Thursday, February 23, 2006 10:42 AM To: Rithish Saralaya; MySQL general mailing list Subject: RE: error 1016 : cant open ibd file even though it exists Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 1016 : cant open ibd file even though it exists
Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish.
Help with a join query
Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear > "2005-01-01" GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing "AND c.locationid IS NULL" returns no results at all. Any help would GREATLY be appreciated!!! Thank you!!
RE: error 1016 : cant open ibd file even though it exists
Hi Rithish, Please check your ownership/permissions hambone ~ $ perror 1 OS error code 1: Not owner hambone ~ $ Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Rithish Saralaya [mailto:[EMAIL PROTECTED] Sent: Thursday, 23 February 2006 3:43 PM To: MySQL general mailing list Subject: error 1016 : cant open ibd file even though it exists Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error 1016 : cant open ibd file even though it exists
Hello. I get the following error when I try to query a table in a particular database (test). The error is generated for all tables within that database. However, 'mysql' database works fine. ERROR 1016 (HY000): Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) However, I have noticed that both the .frm and the .ibd file exists for the table TBL_FORUM_MSG_BODY. Then why the error? I looked into the error log and it is as follows 060222 15:14:09 InnoDB error: Cannot find table test/TBL_FORUM_MSG_BODY from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? Look from section 15.1 of http://www.innodb.com/ibman.html how you can resolve the problem. 060222 15:14:09 [ERROR] /usr/sbin/mysqld: Can't open file: 'TBL_FORUM_MSG_BODY.ibd' (errno: 1) I tried restarting MySQL service in the hope that INNoDB will recognise the files properly, but to no avail. Is there a way to find and correct what has gone wrong? Someone please say 'yes'... I am on MySQL 4.1.11 with InnoDB as the storage engine and with per-table tablespace. Regards, Rithish.
bind-address by name under 5.0.18
I would like to be able to bind to a host name, rather than an IP number. IP numbers come and go, and are beyond the control of anyone who doesn't have their own direct allocation. But since I own my domain, a host name is more permanent. When the time comes to change IP numbers, I want to just edit /etc/hosts to show my new IP: 10.0.0.10 shortname myrealname.example.com and use "--bind-address=shortname" in my start-up script to automatically pick up the new IP number. This eliminates the headache of having to find and fix umpteen literal IP numbers hiding in my startup scripts. This is much the same reason one says '#define BLKSIZ 512' and then uses the constant 'BLKSIZ' instead of sprinkling literal '512's throughout one's code). Why doesn't bind-address allow this? Or if it does, how can I accomplish this? Thanks! Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
> At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
>> I think we are close, thanks
>> ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
>> conditions
>
>>> SELECT p.id, p.prod_name, sum(oi.quantity) as qty
>>> FROM Products p
>>> LEFT JOIN orders as o
>>> ON (p.id = oi.product_id)
>
> Maybe this is where your problem is - you're joining to orders but
> referencing order_items in your join condition. Shurely shome mishtake?*
I am not sure, but I think that is what I want. If it is of any help, I was
able to do this with what I would call a hack, and some temp tables, the
result is what I am after, however, I am not 100% happy with the method I
used.
// first make a selection of the data I want
CREATE TEMPORARY TABLE prod_report
SELECT p.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
INNER JOIN order_items as oi
ON (p.id = oi.product_id)
INNER JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2006-02-22 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
// select all products, set qty to '0', this fills in the gaps where there
// are zero item products
INSERT INTO prod_report (SELECT id, prod_name, 0 FROM products)
// re-seslect the real data, using group by to merge the duplicates
// out of the select
SELECT * FROM prod_report GROUP BY id ORDER BY prod_name
--
-
Scott HanedaTel: 415.898.2602
Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re:mysql client gets segmentation fault
Update. I downloaded the mysql-standard-4.1.18-pc-linux-gnu-i686.tar.gz and extracted mysql. The 4.1 mysql client does not get a segmentation fault when I do an up-arrow or anything else. It works just fine. The plot thickens. -- Barton L. Phillips Applied Technology Resources, Inc. Tel: (818)652-9850 Web: http://www.applitec.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
At 5:08 pm -0800 22/2/06, Scott Haneda wrote:
>I think we are close, thanks
>ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
>conditions
> > SELECT p.id, p.prod_name, sum(oi.quantity) as qty
>> FROM Products p
>> LEFT JOIN orders as o
>> ON (p.id = oi.product_id)
Maybe this is where your problem is - you're joining to orders but referencing
order_items in your join condition. Shurely shome mishtake?*
> > AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22
> > 23:59:59"
>> AND o.status not IN ('cancelled', 'pending', 'ghost')
>> LEFT JOIN order_items as oi
>> ON (o.id = oi.order_id)
>> GROUP BY p.id
> > ORDER by qty ASC
* ask a Brit, or consult
http://en.wikipedia.org/wiki/Private_Eye#Examples_of_humour
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
> You just need to invert a couple of things...
>
> SELECT p.id, p.prod_name, sum(oi.quantity) as qty
> FROM Products p
> LEFT JOIN orders as o
> ON (p.id = oi.product_id)
> AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59"
> AND o.status not IN ('cancelled', 'pending', 'ghost')
> LEFT JOIN order_items as oi
> ON (o.id = oi.order_id)
> GROUP BY p.id
> ORDER by qty ASC
>
>
> That should give you a list of all products and a count of how many have been
> ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status of the
> order is neither 'cancelled', 'pending', or 'ghost'.
>
> The think to remember is that an ON clause can be as complex as a WHERE
> clause. The ON clause also determines which rows of which table participate in
> a JOIN. In this case the only table to be affected will be the one on the
> right side of a LEFT join (in an INNER join both tables are filtered). So you
> keep all of your products visible (as declared in the FROM clause) and
> optionally associate with each product an order and optionally past that to an
> order_item.
> HTH!
I think we are close, thanks
ERROR 1120: Cross dependency found in OUTER JOIN. Examine your ON
conditions
Not sure if this is related to my version of mysql, or something else?
--
-
Scott HanedaTel: 415.898.2602
Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number Searches
On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: > So far i've been able to store ip addresses as strings like you would > type them in DOS, for ex, '192.168.0.1'. This serves me great since my > application uses IP addresses as strings in all cases. I've done > queries with the IP column , for example, select office_name from > table_1 where ip='10.100.1.1'; and have never had any problems. > However, if you plan on sorting based on this column, strings with > periods do not behave correctly, and the answers to my previous > question on this list do not apply; it makes a good aproximation, > though. > > Hope this helps, but I must admit I am not sure if this answers your > question. An example in the mysql console would be great for clearing > up your objetive. > > Regards, > > Ariel OK, I think I did not explain things right the first time. :( I have a table like so: CREATE TABLE `portal_forums_users` ( `user_id` bigint(255) NOT NULL auto_increment, `ip` varchar(200) default NULL, `signup_date` varchar(30) default NULL, `city` varchar(200) default NULL, `state` varchar(100) default NULL, `email_address` varchar(200) default NULL, `username` varchar(100) default NULL, `password` varchar(100) default NULL, `yim` text, `aol` text, `web_url` text, `post_count` varchar(255) default NULL, `info` text, `sig` text, `avatar` text, `css_id` int(11) default NULL, `mod_f` varchar(20) default NULL, `admin` varchar(20) default NULL, PRIMARY KEY (`user_id`), FULLTEXT KEY `full_index` (`city`,`state`,`username`,`email_address`,`ip`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Notice the FULLTEXT; I have the ip listed to be indexed. Using a PHP script I have it will not grab the IP that I am searching for, even though it *is* in the database. The ip's are entered into the database as 192.168.1.10. When I search for a username it works great. Is it because of the "." (periods) in the search string?? Hopefully I explained that right this time!! :) LOL -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
RE: Number Searches
So far i've been able to store ip addresses as strings like you would type them in DOS, for ex, '192.168.0.1'. This serves me great since my application uses IP addresses as strings in all cases. I've done queries with the IP column , for example, select office_name from table_1 where ip='10.100.1.1'; and have never had any problems. However, if you plan on sorting based on this column, strings with periods do not behave correctly, and the answers to my previous question on this list do not apply; it makes a good aproximation, though. Hope this helps, but I must admit I am not sure if this answers your question. An example in the mysql console would be great for clearing up your objetive. Regards, Ariel -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 15:53 Para: MySQL-List Asunto: Number Searches Hello all, I have searched but cannot find what I am looking for. I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? Thanks, Will -- 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: dropping a database to reclaim space
Luke, if you do not have ANY valuable InnoDB tables in the installation, you can simply delete the ibdata files and ib_logfiles. Be very careful if you have several MySQL instances in the same computer. As Sheeri wrote, MySQL's system tables 'user.MYD' etc. are MyISAM tables in the 'mysql' database directory. They are not InnoDB type. In the future, you may want to put the option: innodb_file_per_table to your my.cnf. Then InnoDB tables are stored in .ibd files in individual database directories, and dropping those tables does free the disk space to the operating system. The ibdata files never shrink. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Hi. I have a database that is fairly large and I'm doing some testing with 2 different mysql packages, one 32-bit, one 64bit, for comparison's sake. Now I've finished with one of the dbs and I want to reclaim the disk space. I've tried deleting a few (large) tables from the database, thinking this would free up space, however the mysql/var/ibdata1 file doesn't change in size. How can I get rid of a table or even complete database to reclaim disk space? I don't really want to delete the ibdata1 file, because that will also delete the system database right? I've already tried drop database and that doesn't show reduced disk space either. I'm doing this on a solaris 9 box. Any input appreciated. Thanks. Kind regards. -- Luke Thread dropping a database to reclaim space - Luke Vanderfluit, February 22 2006 3:48am Re: dropping a database to reclaim space - sheeri kritzer, February 22 2006 6:32pm RE: dropping a database to reclaim space - Robert DiFalco, February 22 2006 5:14pm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number Searches
On Wed, February 22, 2006 4:52 pm, CodeHeads wrote: > > I have a full index index on a table and on of the fields is a number > field (IP Address). Can MySQL search for numbers?? > I'm potentially introducing confusion here, because I've never actually _used_ MySQL, but can't you do one of the following things: a)Store the number as a string with guaranteed uniqueness, then search by a string (which I think MySQL will do). In the case of an IP address, to make the (IP) -> (STRING) mapping have no collisions, I think all you need to do is prepend with zeros to reach length three on each number, i.e. 192.168.0.10 would become "19216810". b)Store the IP as a 64-bit integer. I think MySQL will key on strings, right? Dave. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number Searches
- Original Message - From: "CodeHeads" <[EMAIL PROTECTED]> To: "MySQL-List" Sent: Wednesday, February 22, 2006 4:52 PM Subject: Number Searches Hello all, I have searched but cannot find what I am looking for. I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? I don't know the answer to your question but I'm pretty sure it has come up before in this list. You might want to do a search on this topic in the MySQL archives at http://lists.mysql.com/. I think the answer depends on exactly how you store your IP addresses, i.e. datatype and number of columns used. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.12/266 - Release Date: 21/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Number Searches
Hello all, I have searched but cannot find what I am looking for. I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? Thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fulltext IN BOOLEAN wildcard subexpression
Please help me understand the functionality of the wildcard character with Fulltext searching IN BOOLEAN MODE. For the following searches, I get tangible query results as expected, except for the last search which returns no records from the query. Why is this? 1. +jones +mag* 2. +smith +book* 3. +(+jones +mag*) +(+jones +mag*) 4. (+jones +mag) (+smith +book) 5. (+jones +mag*) (+smith +book*) Your timely reply would be greatly appreciated, as this is the final piece of code before I release my interface for testing by others. Thank you, Harry Hege
Re: Inner join with left join
Scott Haneda <[EMAIL PROTECTED]> wrote on 02/22/2006 03:58:10 PM:
> > Is this what you mean?
> >
> > SELECT
> > p.prod_name,
> > count(oi.product_id) AS mycount
> > FROM ORDERS AS o
> > INNER JOIN products ON o.id=p.id
> > LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
> > WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> > GROUP BY oi.product_id
> > ORDER BY mycount;
>
> Well, sort of, here is what I managed to coble together, which gets me
> pretty close, it is just what I want, other than it is missing products
with
> a zero count. This tells me those products have not been ordered ever,
but
> I would like to know what they are.
>
> SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
> FROM orders as o
> INNER JOIN order_items as oi
> ON (o.id = oi.order_id)
> LEFT JOIN products as p
> ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> AND
> (o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
> GROUP BY oi.product_id
> ORDER by qty ASC
>
>
You just need to invert a couple of things...
SELECT p.id, p.prod_name, sum(oi.quantity) as qty
FROM Products p
LEFT JOIN orders as o
ON (p.id = oi.product_id)
AND o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22
23:59:59"
AND o.status not IN ('cancelled', 'pending', 'ghost')
LEFT JOIN order_items as oi
ON (o.id = oi.order_id)
GROUP BY p.id
ORDER by qty ASC
That should give you a list of all products and a count of how many have
been ordered between 2005-01-01 and 2006-02-22 23:59:59 where the status
of the order is neither 'cancelled', 'pending', or 'ghost'.
The think to remember is that an ON clause can be as complex as a WHERE
clause. The ON clause also determines which rows of which table
participate in a JOIN. In this case the only table to be affected will be
the one on the right side of a LEFT join (in an INNER join both tables are
filtered). So you keep all of your products visible (as declared in the
FROM clause) and optionally associate with each product an order and
optionally past that to an order_item.
HTH!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: [SPAM] - Re: Inner join with left join - Bayesian Filter detected spam
If you want to see all of the products {even those that have never been
ordered} then you need to SELECT ... FROM products ... LEFT JOIN orders
I think you also have to do a LEFT JOIN on order_items
And pull prod_name from products {don't know what the column name in
products is}.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM products as p
LEFT JOIN order_items as oi
ON (p.id = oi.product_id)
LEFT JOIN orders as o
ON (o.id = oi.order_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY p.id
ORDER by qty ASC
-Original Message-
From: Scott Haneda [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 22, 2006 2:58 PM
To: MySql
Subject: [SPAM] - Re: Inner join with left join - Bayesian Filter
detected spam
> Is this what you mean?
>
> SELECT
> p.prod_name,
> count(oi.product_id) AS mycount
> FROM ORDERS AS o
> INNER JOIN products ON o.id=p.id
> LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> GROUP BY oi.product_id
> ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products
with
a zero count. This tells me those products have not been ordered ever,
but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC
--
-
Scott HanedaTel: 415.898.2602
Novato, CA U.S.A.
--
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: next, prev, records in MySql. Handler Function
Hi Alvaro,
I have no experience using these in PHP scripts. Couldn't hurt to try
putting it in mysql_query statements and see if it works. If you can
turn on the general query log for a bit then you can see exactly if
it's working; if you can't turn it on, then you'll have to guess from
output.
-Sheeri
On 2/22/06, Alvaro Cobo <[EMAIL PROTECTED]> wrote:
> Thanks Sheeri:
>
> Yes, you where right. It was because I was not opening the table first.
> Actually, I was using this statements from a MySQL GUI (MySQL Query
> Browser) so it doesn't work in this program (it doesn't keeps the table
> opened).
>
> But when I use your example in the shell mode it works perfect.
>
> Have you ever tried to use this kind of statements using PHP?. It is
> because I would like to use it in a PHP script (I was exploring this
> function to make a navigator which sends me to the next record each time
> I click on a link generated from a MySQL query).
>
> Thanks a lot and very grateful with you,
>
> Alvaro.
>
> sheeri kritzer escribió:
> > Hi there,
> >
> > What did your MySQL error say?
> >
> > It looks like you didn't open the table. my example, which worked:
> >
> > CREATE TABLE `foo` (
> > `id` int(11) NOT NULL auto_increment,
> > `bar` char(3) default NULL,
> > PRIMARY KEY (`id`),
> > KEY `idx_bar` (`bar`)
> > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
> >
> > insert into foo (bar) VALUES
> > ('abc'),('def'),('qwe'),('ert'),('wer'),('tyu'),('asd'),('sdf'),('dfg'),('zxc'),('xcc');
> >
> > and then:
> >
> > mysql> HANDLER foo open;
> > Query OK, 0 rows affected (0.00 sec)
> >
> > mysql> HANDLER foo READ idx_bar FIRST where bar='wer';
> > ++--+
> > | id | bar |
> > ++--+
> > | 5 | wer |
> > ++--+
> > 1 row in set (0.00 sec)
> >
> > mysql> HANDLER foo READ idx_bar NEXT;
> > ++--+
> > | id | bar |
> > ++--+
> > | 11 | xcc |
> > ++--+
> > 1 row in set (0.00 sec)
> >
> > mysql> HANDLER foo READ idx_bar PREV;
> > ++--+
> > | id | bar |
> > ++--+
> > | 5 | wer |
> > ++--+
> > 1 row in set (0.00 sec)
> >
> > mysql> HANDLER foo READ idx_bar PREV;
> > ++--+
> > | id | bar |
> > ++--+
> > | 6 | tyu |
> > ++--+
> > 1 row in set (0.00 sec)
> >
> > Granted, that's using "previous" and "next" in an alphabetical sense.
> > I found that using the "id" index didn't work, and I had to create
> > another non-primary index on key for it to work.
> >
> > -Sheeri
> >
> >
> > On 2/8/06, Alvaro Cobo <[EMAIL PROTECTED]> wrote:
> >> Dear all:
> >>
> >> I have been exploring about this issue quite a lot, and find no solution:
> >>
> >> Platform: Debian, MySql 4.1.11, PHP 4.3.10-2, Apache.
> >>
> >> Is there any way I can retrieve a set of values depending in a where
> >> clause:
> >>
> >> For example:
> >>
> >> from a set of values in one field: 1,2,5,8,9,11,13
> >>
> >> I'd like to retrieve a record (8) and also the previous one (5) and the
> >> next one (9) (so the record set would be: 5,8,9)
> >>
> >> I have found the "Handler" function in the Manual, but it and keeps
> >> giving me errors (I have also checked in the manual and it seems to work
> >> with MySql 4.1.x)
> >>
> >> /* --Start example
> >> -
> >> HANDLER tbl_sm04_indicador READ PK_indicador_id { FIRST | NEXT | PREV |
> >> LAST }
> >>
> >> WHERE PK_indicador_id=8
> >>
> >> LIMIT 0, 3
> >> --
> >> --End example (I know, I am completely lost)*/
> >>
> >> Does anybody has tried this function before?.
> >> Is it useful for the result I would like to accomplish?
> >> Could anybody could share an example of how to use this function?
> >>
> >> Thanks and best regards.
> >>
> >> Alvaro Cobo
> >>
> >> --
> >> 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: Inner join with left join
> Is this what you mean?
>
> SELECT
> p.prod_name,
> count(oi.product_id) AS mycount
> FROM ORDERS AS o
> INNER JOIN products ON o.id=p.id
> LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
> WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
> GROUP BY oi.product_id
> ORDER BY mycount;
Well, sort of, here is what I managed to coble together, which gets me
pretty close, it is just what I want, other than it is missing products with
a zero count. This tells me those products have not been ordered ever, but
I would like to know what they are.
SELECT o.id, oi.prod_name, sum(oi.quantity) as qty
FROM orders as o
INNER JOIN order_items as oi
ON (o.id = oi.order_id)
LEFT JOIN products as p
ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
AND
(o.created BETWEEN "2005-01-01 00:00:00" AND "2006-02-22 23:59:59")
GROUP BY oi.product_id
ORDER by qty ASC
--
-
Scott HanedaTel: 415.898.2602
Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql-bench and results
Hi All, I installed MySQL 5.0.18 on a Debian Sarge box. I ran the benchmark suite in the subfolder sql-bench. Now I'd like to compare my results with other results. According to http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html some results are included in sql-bench/Results. But I cannot find this folder at all. I looked inside the binary installation package and the source installation package. Any idea where I can find these results? Thanks in advance. Sam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport, \r\n and \n
On 2/22/06, sheeri kritzer <[EMAIL PROTECTED]> wrote: > A batch script or shell script can easily be written to do this. > > -Sheeri > > On 2/20/06, Daniel Kasak <[EMAIL PROTECTED]> wrote: > > I've got some import scripts that are giving me trouble. > > > > Some MOFOs keep changing the format of the data they give us, and > > sometimes I loose half the records. When this happens, I change the line > > terminator from \r\n to \n ... or from \n to \r\n. > > > > It's starting to get to me. Is there any way to ease my pain here, short > > of importing with one format, counting the number of records, truncating > > the table, importing wit the other format, counting the number of > > records, and then selecting the format with the most number of records? > > > > -- > > Daniel Kasak > > IT Developer > > NUS Consulting Group > > Level 5, 77 Pacific Highway > > North Sydney, NSW, Australia 2060 > > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > > email: [EMAIL PROTECTED] > > website: http://www.nusconsulting.com.au > > > > -- > > 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] > > I would stick with the VBA solution, heh, if you use it you can even "call" Word and use "save as", in fact "converting" your document to plain ol' windows text file... Then I guess you can go back to Access and just let it roll... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlimport, \r\n and \n
A batch script or shell script can easily be written to do this. -Sheeri On 2/20/06, Daniel Kasak <[EMAIL PROTECTED]> wrote: > I've got some import scripts that are giving me trouble. > > Some MOFOs keep changing the format of the data they give us, and > sometimes I loose half the records. When this happens, I change the line > terminator from \r\n to \n ... or from \n to \r\n. > > It's starting to get to me. Is there any way to ease my pain here, short > of importing with one format, counting the number of records, truncating > the table, importing wit the other format, counting the number of > records, and then selecting the format with the most number of records? > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au > > -- > 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: dropping a database to reclaim space
Luke, I believe the 'mysql' database (system) does NOT use InnoDB. The best thing to do would be to change your database to skip-innodb, check that the server is working, and then delete the innodb data and log files. -Sheeri On 2/21/06, Luke Vanderfluit <[EMAIL PROTECTED]> wrote: > Hi. > > I have a database that is fairly large and I'm doing some testing with 2 > different mysql packages, one 32-bit, one 64bit, for comparison's sake. > > Now I've finished with one of the dbs and I want to reclaim the disk space. > > I've tried deleting a few (large) tables from the database, thinking > this would free up space, however the mysql/var/ibdata1 file doesn't > change in size. > > How can I get rid of a table or even complete database to reclaim disk > space? > I don't really want to delete the ibdata1 file, because that will also > delete the system database right? > I've already tried drop database and that doesn't show reduced disk > space either. > > I'm doing this on a solaris 9 box. > Any input appreciated. > > Thanks. > Kind regards. > > -- > Luke > > > -- > 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: inquiry
On Wed, 2006-02-22 at 03:47 -0800, Anago Chima wrote: Q: What do the exams cost? > > A: Both exams are offered at the local equivalent of > US$200 / EUR 170*. > > This question and answer was copied from certification > FAQ. Please can somebody tell me 'both' means in the > answer here. Does it mean that the price for both > MySQL Developer Exam I & II are US $200? ie US $200 > cover the two exams $200 for the core certification, and $200 for the Professional Certification. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: Permissions for /var/run/mysqld
/ "sheeri kritzer" <[EMAIL PROTECTED]> was heard to say: | That's odd. My mysql.sock is chmod 777, which happened automatically. Yes, the mysql.sock file is 777. But the directory that contains it /var/run/mysqld is 770 on boot. | Check the startup script. Is it calling mysqld_safe? Are you using | the regular startup script that came with mysql, or have you mucked | with it? I didn't muck with it. I don't know if Ubuntu did. | Make sure when you chmod that the file is still a socket. Shouldn't | be a problem. As I said, it isn't the socket that I have to change, it's the directory. > | I don't really have much to add. > | a quick google search on "mysql.sock permissions" shows this bug: > | http://bugs.mysql.com/bug.php?id=11380 > | did you also upgrade mysql when you did the OS upgrade ? If so, backup | your stuff and run mysql_install_db. That's the only suggestion I | have, other than doing a google search as I did and look at more than | the first 2 results (which was what I did to find that bug). I looked through a whole bunch of them before subscribing to this list :-) However, your note got me to looking at /usr/bin/mysqld_safe and it would seem to me that either of the following changes would fix the problem: 1. Change umask 007 to umask 002 at the top of the script or 2. Add "chmod 775 $mysql_unix_port_dir" after creating and chowning it. I opted for the latter locally. But I'm not sure why others don't have this problem. Are either of those changes local to Ubuntu? Be seeing you, norm -- Norman Walsh <[EMAIL PROTECTED]> | Everything should be made as simple as http://nwalsh.com/| possible, but no simpler. pgpLe4NRbwA1u.pgp Description: PGP signature
Re: selecting based on a max() condition
Octavian, I want to select the list of unique symbols and the corresponding price and last_update fields for each line where the last_update is equal to the last_update field for each symbol. You're close! See the example at http://www.artfulsoftware.com/queries.php#18. PB - Octavian Rasnita wrote: Hi, I have a table with 3 relevant columns: symbol varchar(10) not null price decimal not null and last_update datetime not null I want to select the list of unique symbols and the corresponding price and last_update fields for each line where the last_update is equal to the last_update field for each symbol. I have tried without succes: select symbol, price, last_update from table where last_update = (select max(last_update) from table); This gave me a single row. I have tried more complex queries that took a lot of time and I needed to break them with ^C. Thank you. Teddy -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.0.0/266 - Release Date: 2/21/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
Scott,
I need a report that shows me all the products with a sum() for each, but
only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost')
Is this what you mean?
SELECT
p.prod_name,
count(oi.product_id) AS mycount
FROM ORDERS AS o
INNER JOIN products ON o.id=p.id
LEFT JOIN order_items AS oi ON (p.id = oi.product_id)
WHERE o.status NOT IN ('cancelled', 'pending', 'ghost')
GROUP BY oi.product_id
ORDER BY mycount;
PB
---
Scott Haneda wrote:
Got myself a little stumped here, 4.0.18-standard
Three tables in this mess, orders, order_items and products.
orders.prod_id = order_items.prod_id = products.prod_id is how I relate them
all to each other.
order_items has a quantity field.
I need a report that shows me all the products with a sum() for each, but
only if the status of the order is NOT IN ('cancelled', 'pending', 'ghost')
This gets me pretty close, but does not mask out the orders that have the
wrong status, as I do not know how to add in the join on orders
SELECT p.prod_name, count(oi.product_id) as mycount
FROM products as p
LEFT JOIN order_items as oi
on (p.id = oi.product_id)
group by oi.product_id order by mycount;
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.0.0/266 - Release Date: 2/21/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: dropping a database to reclaim space
If I am testing for performance, I make sure to have one schema for each database server. When I want to tear down the data and restart (possibly with new settings). I first DROP the database I am working on. Then I shutdown the server and in the /data directory, I delete all the files there. It seems like, for InnoDB at least, that you have to delete these log files if you want to be able to try different log file sizes; once they are created the size cannot be changed. I'm not sure what is left in ibdata after dropping a database, but deleting this file too just causes it to be recreated next time you start MySQL. -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 21, 2006 6:48 PM To: mysql@lists.mysql.com Subject: dropping a database to reclaim space Hi. I have a database that is fairly large and I'm doing some testing with 2 different mysql packages, one 32-bit, one 64bit, for comparison's sake. Now I've finished with one of the dbs and I want to reclaim the disk space. I've tried deleting a few (large) tables from the database, thinking this would free up space, however the mysql/var/ibdata1 file doesn't change in size. How can I get rid of a table or even complete database to reclaim disk space? I don't really want to delete the ibdata1 file, because that will also delete the system database right? I've already tried drop database and that doesn't show reduced disk space either. I'm doing this on a solaris 9 box. Any input appreciated. Thanks. Kind regards. -- Luke -- 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: "Nested Set Model" or "modified preorder tree traversal" mySQL/PHP code wanted
Daevid, I've been searching the web for the past few hours trying to find a simple drop-in class or functions to implement "Nested Set Model" or "modified preorder tree traversal". The reason there's not a drop-in module may be that adding the layers implementing such generality would slow performance. There is a nested set example for MySQL 5 at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html PB - Daevid Vincent wrote: I've been searching the web for the past few hours trying to find a simple drop-in class or functions to implement "Nested Set Model" or "modified preorder tree traversal". I've found several tutorials and related links: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=tutorial-ferrara2 &id=3453&open=1&anc=0&view=1 http://simon.incutio.com/archive/2003/06/19/storingTrees http://istherelifeafter.com/joecelko.html http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp I even found some outdated PEAR DB_NestedSet class from 2003 that seemed way to overly complicated. And some other PHP4_Mysql3MPTTA class on PHP Classes that was equally complicated and written for ancient PHP4 and mySQL 3!! Hasn't anyone else done this before (recently)? Did everyone just write their own code every time? Anyone have some easy to use code that allows for: Add, delete, update/rename, select, show tree, bread crumb display, etc. I use PHP 5.0.5 and mySQL 5.0.18, so ideally it would take advantage of all the optimizations and bells & whistles of those more modern versions. TIA. D.Vin -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.0.0/266 - Release Date: 2/21/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Queries within Oscommerce
"AM COMS" <[EMAIL PROTECTED]> wrote on 02/22/2006 09:16:24 AM: > Has anyone here had any experience with Oscommerce? > > I am having problems with the style of queries they have used or am I just > seeing things the wrong way! > > andrew > Sorry! I have never used it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
MySQL Queries within Oscommerce
Has anyone here had any experience with Oscommerce? I am having problems with the style of queries they have used or am I just seeing things the wrong way! andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inner join with left join
Scott Haneda <[EMAIL PROTECTED]> wrote on 02/22/2006 01:47:38 AM:
> Got myself a little stumped here, 4.0.18-standard
>
> Three tables in this mess, orders, order_items and products.
>
> orders.prod_id = order_items.prod_id = products.prod_id is how I relate
them
> all to each other.
>
> order_items has a quantity field.
>
> I need a report that shows me all the products with a sum() for each,
but
> only if the status of the order is NOT IN ('cancelled', 'pending',
'ghost')
>
> This gets me pretty close, but does not mask out the orders that have
the
> wrong status, as I do not know how to add in the join on orders
>
> SELECT p.prod_name, count(oi.product_id) as mycount
>FROM products as p
>LEFT JOIN order_items as oi
>on (p.id = oi.product_id)
>group by oi.product_id order by mycount;
> --
> -
> Scott HanedaTel: 415.898.2602
> Novato, CA U.S.A.
>
>
Shouldn't there be a relationship between orders and order_items like
`orders`.`id` = `order_items`.`order_id`
or something similar? That would make better sense to me Making each
`order` product-specific doesn't sound like a good design especially when
each `order` looks like it can contain multiple `order_item`s, each
potentially for a different `product`.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: binary log missing in 5.1.6?
In answer to my own question: Why don't the binary logs write? The '%-bin.index' file left over from previous version (4.1.9) had to be deleted after the conversion to 5.1.6. By deleting this file, the binary logs started to recreate. I hope this is useful to somebody :) Ben Ben Clewett wrote: Dear MySQL, I have just upgraded 4.1.9 to 5.1.6, by compiling source code. I have lost my binary logs. http://dev.mysql.com/doc/refman/5.0/en/binary-log.html eg: my-bin.01 my-bin.02 The usual option is present: [mysqld] log-bin = /usr/local/mysql/logs/my-bin **or** [mysqld] log-bin = ../logs/my-bin I do have the new tables 'mysql.general_log' and 'mysql.slow_log' which contain correct information. Does any member have any advise on what may be going wrong? Regards, Ben -- Ben Clewett +44(0)1923 46 Project Manager Road Tech Computer Systems Ltd http://www.roadrunner.uk.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: parameterized view ?
[EMAIL PROTECTED] wrote on 02/21/2006 11:20:36 AM: > Is there such a thing in mySQL ? > I could not find anything anywhere. > > thanks, > laszlo Short answer: No Long answer: MySQL has VIEWS but they are non-parameterized and non-persistent. MySQL also has FUNCTIONS and STORED PROCEDURES, both of them take parameters but are not exactly views. There have been suggestions to make parameterized views and persistent views but I am not sure where those projects are on the TODO lists (I have had to cut back on my list time and haven't been able to lurk the "commits" list like I had been so I am a few weeks out of date as far as current project statuses.) Sorry but you will probably need to make a SPROC work for what you need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
selecting based on a max() condition
Hi, I have a table with 3 relevant columns: symbol varchar(10) not null price decimal not null and last_update datetime not null I want to select the list of unique symbols and the corresponding price and last_update fields for each line where the last_update is equal to the last_update field for each symbol. I have tried without succes: select symbol, price, last_update from table where last_update = (select max(last_update) from table); This gave me a single row. I have tried more complex queries that took a lot of time and I needed to break them with ^C. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inquiry
Yes... count me in for this question also... I didn't figured that out... I just bought "MySQL 5.0 Cerrt. Study Guide" and I wanna know also... I also saw that the exams for the 5.0 branch are in BETA... when are we going to expect a final exam ? -- Gabriel PREDA Senior Web Developer On 2/22/06, Anago Chima <[EMAIL PROTECTED]> wrote: > > Q: What do the exams cost? > > A: Both exams are offered at the local equivalent of > US$200 / EUR 170*. > > This question and answer was copied from certification > FAQ. Please can somebody tell me 'both' means in the > answer here. Does it mean that the price for both > MySQL Developer Exam I & II are US $200? ie US $200 > cover the two exams >
inquiry
Q: What do the exams cost? A: Both exams are offered at the local equivalent of US$200 / EUR 170*. This question and answer was copied from certification FAQ. Please can somebody tell me 'both' means in the answer here. Does it mean that the price for both MySQL Developer Exam I & II are US $200? ie US $200 cover the two exams __ 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]
binary log missing in 5.1.6?
Dear MySQL, I have just upgraded 4.1.9 to 5.1.6, by compiling source code. I have lost my binary logs. http://dev.mysql.com/doc/refman/5.0/en/binary-log.html eg: my-bin.01 my-bin.02 The usual option is present: [mysqld] log-bin = /usr/local/mysql/logs/my-bin **or** [mysqld] log-bin = ../logs/my-bin I do have the new tables 'mysql.general_log' and 'mysql.slow_log' which contain correct information. Does any member have any advise on what may be going wrong? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table parameters not transferring while using Migration toolkit
Hi, When I use the MySQL Migration Toolkit to transfer a Database from one Mysql server to another, the table parameters configured do not seem to be transferred as well. Fields specified as Primary keys that autoincrement do not autoincrement in the transferred database and fields set as null fields with default values all of a sudden show as NOT NULL. I'm using MySQL Server 5.0.11 and Migration Toolkit 1.0.21rc. Any suggestions / ideas as to why this is happening (the transfer is done online, so all objects and data is moved online and not scripted). Thanks, Dewald Troskie GIS Developer / Database Architect GIS Global Image (Pty) Ltd. Helping the world make informed decisions P.O Box 15 The Innovation Hub 0087 Cell: +27 (0)72 685 4246 Tel: +27 (0)12 844 0660 Fax: +27 (0)12 844 0668 Email: [EMAIL PROTECTED] Web: www.globalimage.co.za Web: www.mapme.co.za Registered Linux User No: 371874 Office L15 Enterprise Building The Innovation Hub - Hotel Street Lynnwood, Pretoria, 0087 "The are 10 kinds of people, those who understand binary and those who don't"
Re: simple addition in a query?
I think the following should work, try taking a look on the net for help on MySQL queries that use aggragate functions, there is probably a lot of info on there. select exim.hour,sum(exim.count) from exim,servers where exim.machine=servers.id and servers.type='MX' and exim.date='2006-02-22' group by exim.hour Regards Ade Ronan wrote: select servers.type,exim.hour,exim.count from exim,servers where exim.machine=servers.id and servers.type='MX' and exim.date='2006-02-22'" this gives me +--+--+---+ | type | hour | count | +--+--+---+ | MX |0 | 1117 | | MX |0 | 366 | | MX |0 | 147 | | MX |1 | 1166 | | MX |1 | 288 | | MX |1 | 216 | | MX |2 | 1176 | | MX |2 | 256 | | MX |2 | 169 | | MX |3 | 1016 | | MX |3 | 232 | | MX |3 | 200 | | MX |4 | 1186 | | MX |4 | 311 | | MX |4 | 216 | | MX |5 | 905 | | MX |5 | 297 | | MX |5 | 129 | | MX |6 | 1021 | | MX |6 | 777 | | MX |6 | 131 | | MX |7 | 915 | | MX |7 | 418 | | MX |7 | 138 | | MX |8 | 1506 | | MX |8 | 1544 | | MX |8 | 175 | | MX |9 | 1949 | | MX |9 | 2598 | | MX |9 | 142 | +--+--+---+ I have 3 MXs... I am running a perl script which queries the DB on request and draws GNUplot graphs... Im looking at the websire but as im still a novice at this can anyone confirm , or gently shove me in the right direction on how I might take a sum of the 3 fields on the hourly field so I can hae a total count per hour? I could do it in perl easily but I want to know if its possible in MySQL... thanks Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple addition in a query?
select servers.type,exim.hour,exim.count from exim,servers where exim.machine=servers.id and servers.type='MX' and exim.date='2006-02-22'" this gives me +--+--+---+ | type | hour | count | +--+--+---+ | MX |0 | 1117 | | MX |0 | 366 | | MX |0 | 147 | | MX |1 | 1166 | | MX |1 | 288 | | MX |1 | 216 | | MX |2 | 1176 | | MX |2 | 256 | | MX |2 | 169 | | MX |3 | 1016 | | MX |3 | 232 | | MX |3 | 200 | | MX |4 | 1186 | | MX |4 | 311 | | MX |4 | 216 | | MX |5 | 905 | | MX |5 | 297 | | MX |5 | 129 | | MX |6 | 1021 | | MX |6 | 777 | | MX |6 | 131 | | MX |7 | 915 | | MX |7 | 418 | | MX |7 | 138 | | MX |8 | 1506 | | MX |8 | 1544 | | MX |8 | 175 | | MX |9 | 1949 | | MX |9 | 2598 | | MX |9 | 142 | +--+--+---+ I have 3 MXs... I am running a perl script which queries the DB on request and draws GNUplot graphs... Im looking at the websire but as im still a novice at this can anyone confirm , or gently shove me in the right direction on how I might take a sum of the 3 fields on the hourly field so I can hae a total count per hour? I could do it in perl easily but I want to know if its possible in MySQL... thanks Ronan -- Ronan McGlue Analyst / Programmer CMC Systems Group Queens University Belfast -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
