Re: how do you update all data on table?
Louie Miranda [EMAIL PROTECTED] wrote: I have one table named Category under that all values contains Books How can i make all Books into Stocks? Use UPDATE statement like this: UPDATE Category SET column='Stocks' WHERE column='Books'; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.1.1 negative default column values doesn't work
Tedman Leung [EMAIL PROTECTED] wrote: I've been unable to find any information on this issue so I'm not quite sure if it's a bug or a new restriction. In either case I think it's a bug due to it's inconsistency with itself. create table foo (id int not null default -1) does not work create table foo (id int not null default '-1') does work. anyone have any information on this? It's a known bug: http://bugs.mysql.com/bug.php?id=2075 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.1.1 negative default column values doesn't work
Table created with out any error in MySQL 4.0.17-max-debug win mysql create table foo (id int not null default -1); Query OK, 0 rows affected (0.22 sec) mysql explain foo; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | id| int(11) | | | -1 | | +---+-+--+-+-+---+ 1 row in set (0.11 sec) Then Why its become bug in 4.1.1., I don't know regards, Arun. --- Victoria Reznichenko [EMAIL PROTECTED] wrote: Tedman Leung [EMAIL PROTECTED] wrote: I've been unable to find any information on this issue so I'm not quite sure if it's a bug or a new restriction. In either case I think it's a bug due to it's inconsistency with itself. create table foo (id int not null default -1) does not work create table foo (id int not null default '-1') does work. anyone have any information on this? It's a known bug: http://bugs.mysql.com/bug.php?id=2075 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] Will an index increase performance here?
Table employee: employeeNumber : int (primary key) age: int salary : float Would an index improve performance for these queries? SELECT * FROM USERS ORDER BY age DESC LIMIT 100,200 (index on age) SELECT * FROM USERS ORDER BY salary DESC LIMIT 200,300 (index on salary) Is it OK if the index has duplicate keys (people with same age or salaries?) Thanks! __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to determine when a MySQL database was last modified?
Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC crashes
hi, here are the two sql logs for the same code when one is run independently and when embedded in the other when it is embedded in the other it hangs and the application crashes so could any one tell what the problem can be..it crashes for the reason 0x4054afde in my_SQLPrepare () from /usr/local/lib/libmyodbc3-3.51.06.so so i think my_SQLPrepare() method fails somehow so can anyone help me, thanks in advance. === When run with in an application === [ODBC][4507][__handles.c][368] Exit:[SQL_SUCCESS] Environment = 0x8664fb8 [ODBC][4507][SQLSetEnvAttr.c][154] Entry: Environment = 0x8664fb8 Attribute = SQL_ATTR_ODBC_VERSION Value = 0x3 StrLen = (nil) [ODBC][4507][SQLSetEnvAttr.c][315] Exit:[SQL_SUCCESS] [ODBC][4507][SQLAllocHandle.c][315] Entry: Handle Type = 2 Input Handle = 0x8664fb8 [ODBC][4507][SQLAllocHandle.c][429] Exit:[SQL_SUCCESS] Output Handle = 0x8665548 [ODBC][4507][SQLSetConnectAttr.c][267] Entry: Connection = 0x8665548 Attribute = SQL_ATTR_LOGIN_TIMEOUT Value = 0x5 StrLen = (nil) [ODBC][4507][SQLSetConnectAttr.c][453] Exit:[SQL_SUCCESS] [ODBC][4507][SQLConnect.c][2390] Entry: Connection = 0x8665548 Server Name = [TelePro][length = 7 (SQL_NTS)] User Name = [ippbx][length = 5 (SQL_NTS)] Authentication = [*][length = 5 (SQL_NTS)] [ODBC][4507][SQLConnect.c][2947] Exit:[SQL_SUCCESS] [ODBC][4507][SQLAllocHandle.c][476] Entry: Handle Type = 3 Input Handle = 0x8665548 [ODBC][4507][SQLAllocHandle.c][840] Exit:[SQL_SUCCESS] Output Handle = 0x866cbd8 [ODBC][4507][SQLExecDirect.c][200] Entry: Statement = 0x866cbd8 SQL = [select extension from SUBSCRIBER where subscriber_type = 0][length = 58 (SQL_NTS)] === when run independently === [ODBC][4509][__handles.c][368] Exit:[SQL_SUCCESS] Environment = 0x805e2b8 [ODBC][4509][SQLSetEnvAttr.c][154] Entry: Environment = 0x805e2b8 Attribute = SQL_ATTR_ODBC_VERSION Value = 0x3 StrLen = (nil) [ODBC][4509][SQLSetEnvAttr.c][315] Exit:[SQL_SUCCESS] [ODBC][4509][SQLAllocHandle.c][315] Entry: Handle Type = 2 Input Handle = 0x805e2b8 [ODBC][4509][SQLAllocHandle.c][429] Exit:[SQL_SUCCESS] Output Handle = 0x805e9b0 [ODBC][4509][SQLSetConnectAttr.c][267] Entry: Connection = 0x805e9b0 Attribute = SQL_ATTR_LOGIN_TIMEOUT Value = 0x5 StrLen = (nil) [ODBC][4509][SQLSetConnectAttr.c][453] Exit:[SQL_SUCCESS] [ODBC][4509][SQLConnect.c][2390] Entry: Connection = 0x805e9b0 Server Name = [TelePro][length = 7 (SQL_NTS)] User Name = [ippbx][length = 5 (SQL_NTS)] Authentication = [*][length = 5 (SQL_NTS)] [ODBC][4509][SQLConnect.c][2947] Exit:[SQL_SUCCESS] [ODBC][4509][SQLAllocHandle.c][476] Entry: Handle Type = 3 Input Handle = 0x805e9b0 [ODBC][4509][SQLAllocHandle.c][840] Exit:[SQL_SUCCESS] Output Handle = 0x8066d10 [ODBC][4509][SQLExecDirect.c][200] Entry: Statement = 0x8066d10 SQL = [select extension from SUBSCRIBER where subscriber_type = 0][length = 58 (SQL_NTS)] [ODBC][4509][SQLExecDirect.c][425] Exit:[SQL_SUCCESS] [ODBC][4509][SQLBindCol.c][154] Entry: Statement = 0x8066d10 Column Number = 1 Target Type = -16 SQL_C_SLONG Target Value = 0xbfffedec Buffer Length =
Another new list member with questions
Hi I have to rebuild a database for someone that currently has in excess of twelve tables with around 20K records in the biggest table, the whole structure needs looking at as the original designer used char and int for everything, from dates to yes/no fields!! The db is accessed from a VB program (which I also have to re-write) due to a severe lack of data entry validation and any error checks at all, what I would like to know is what is the best way to retrieve the records for display, if I form a collection of all the entries in the main table it takes a long time, can this be speeded up? Currently the program starts at record 1 and then just retrieves the next by its id number, if the next rec does not exist it then simply moves on, this is fine as is, but I can see some problems as this method means that unless I know exactly what I am looking for I have to cycle through all 20k records to find anything. How long should a query on 20k records take? What is the fastest way to retrieve records from the DB? Does that make sense to anyone but me ;) Thanks Paul Owen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What field lengths to use
Hi all, I just tried to change a field with a type of LongText to Text and with a width of 10 characters in phpmyadmin. It accepted the change of the field type top text but didn't seem to accept the change in field length to 10. To test , I entered 20 characters into a text field in my php/mysql database and it accepted it when I updated. What's wrong ? or is it not possible to assign actual field lengths in phpmyadmin ? cheers, Adam
Problem deleteing records
I have a problem with a table that is too big it contains around 35,000,000 lines and each end of month i have to take out about 20,000,000 lines from it so my delete command is : delete from table where column_value** on a column that is indexed. But each time i do that i have mysql that have too many connection problem and i have to kill and restart mysql in the middle of the process. I finish up with a huge table that i must repaire and that takes time and sometime it does not even work. i have max connections=1000 and even this does not seem to be enough. Does anyone has a better way so that i can delete my records without damaging my table and having to restart mysql.. V!nay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied for user: 'ODBC@localhost' (Using password: YES)
I think MyODBC in this situation is irelevant. ODBC reinterpret the SQL language (and is good for some universal data components like ADO,DAO,...), but MySQL C API is native. Also you can't connect by MyODBC using MYSQL C API. So you really didn't forget some parameter??, it seems like. Otherwise try to create user named ODBC, and you will see, also it writes Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)*, so you are using some password, and question is from where this goes, and which password you have to give to user ODBC. I still thing that you must omit some parameter when establishing connection. (But I never tried to make connection from COBOL, I'm just using MYSQL C API from C++) - Original Message - From: Arunachalam [EMAIL PROTECTED] To: KKoTY [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 06, 2004 6:21 AM Subject: Re: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have installed MyODBC 3.51 and created the DSN for my Database. I want to clarify that; If the User name I have given is could not be resolved by mysal_real_connect, then it automatically establish the connection to the server using [EMAIL PROTECTED] - right If so, Is there need to have the user named ODBC in mysql database. OR It'll connect automatically using the DSN setting i have specified in myODBC. Becaz I don't have the source to check Is COBOL value passed to C as such what I have specified? Please clarify my doubts... regards, Arun. --- KKoTY [EMAIL PROTECTED] wrote: this occures when you ommit the user name, MYSQL C API uses user ODBC as default when you ommit or enter empty string as user name when calling mysql_real_connect() - Original Message - From: Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 05, 2004 3:38 PM Subject: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Hi all, Is any one met with the error while connecting to MySQL Server *Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)* if so pleass let me know the remedy to resolve this error. I don't have the user named ODBC in my mysql Database. my configuration is: Windows 2000 SP 4. MySQL Server 4.0.17-max-debug for windows The situation I have met this error is when I try to Connect MySQL from COBOL using the C API provided by MySQL. I have properly link the *libmysql.lib* file into my COBOL compiler. Any suggestion are highly appreciated... Thanks. regards, Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem deleteing records
On Fri, Feb 06, 2004 at 02:58:50PM +0400, Vinay wrote: I have a problem with a table that is too big it contains around 35,000,000 lines and each end of month i have to take out about 20,000,000 lines from it so my delete command is : delete from table where column_value** on a column that is indexed. But each time i do that i have mysql that have too many connection problem and i have to kill and restart mysql in the middle of the process. The DELETE probably takes a very long time. In the mean time, clients that use the table, will block until the DELETE is finished. If enough clients keep connecting, you'll run into the connection limit sometime. Do the DELETE in small chunks, so it will block your other clients for only a short time. Pause a little in between each chunk, to allow other clients to do some work. In an interactive application, your users will love you for this :) Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Another new list member with questions
It sounds a bit wierd, if you're searching thru a table getting one rec at a time. Suely you can go Select * from myTable to retrieve all recs. it sounds nuts to do it any other to be honest! As for the fields, mySQL treats booleans (yes,no) as Char (1). You should of course change the type to DATE where needed. Hope this helps From: Paul Owen Reply-To: To: Subject: Another new list member with questions Date: Fri, 6 Feb 2004 10:54:01 - Hi I have to rebuild a database for someone that currently has in excess of twelve tables with around 20K records in the biggest table, the whole structure needs looking at as the original designer used char and int for everything, from dates to yes/no fields!! The db is accessed from a VB program (which I also have to re-write) due to a severe lack of data entry validation and any error checks at all, what I would like to know is what is the best way to retrieve the records for display, if I form a collection of all the entries in the main table it takes a long time, can this be speeded up? Currently the program starts at record 1 and then just retrieves the next by its id number, if the next rec does not exist it then simply moves on, this is fine as is, but I can see some problems as this method means that unless I know exactly what I am looking for I have to cycle through all 20k records to find anything. How long should a query on 20k records take? What is the fastest way to retrieve records from the DB? Does that make sense to anyone but me ;) Thanks Paul Owen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem deleteing records
Vinay, u cancreatea temporary table CREATE TEMPORARY TABLE TMP Select * from ORIGINAL WHERE 'records to preserve'; TRUNCATE TABLE ORIGINAL; INSERT INTO ORIGINAL SELECT * FROM TMP; DROP TABLE TMP; Alejandro. ---Mensaje original--- De: Vinay Fecha: 06/02/04 08:32:04 Para: [EMAIL PROTECTED] Asunto: Problem deleteing records I have a problem with a table that is too big it contains around 35,000,000 lines and each end of month i have to take out about 20,000,000 lines from it so my delete command is : delete from tablewhere column_value** on a column that is indexed. But each time i do that i have mysql that have "too many connection problem" and i have to kill and restart mysql in the middle of the process. I finish up with a huge table that i must repaire and that takes time and sometime it does not even work. i have" max connections=1000" and even this does not seem to be enough. Does anyone has a better way so that i can delete my records without damaging my table and having to restart mysql.. V!nay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
MS-Access queries port to MySql 5 Stored Procedures
Hi all, now that MySQL 5 has support for Stored Procedures, i was wondering if anyone managed to port (or migrate) the Stored Procedures from MS Access to MySQL ?? (With Stored Procedures i'm referring to queries as they're called in MS Access) Regards to all / Mihalidis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with query
Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting first occurance in a given period of record
Hi, I have a table to log sessions and user (connect_id) id's, start time etc - see below +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | connect_id| varchar(12) | | | | | | session_id| varchar(32) | | | | | | start_time| int(11) | | | 0 | | | client_ip | varchar(16) | | | | | +---+-+--+-+-+---+ I need to create a report that will tell me, for instance, how many sessions were started today, that's easy I know but I also need another report that will tell me how many of those sessions were first-time visitors. FYI the start_time is a UNIX timestamp.. Any help would be greatly appreciated! Cheers Stew ___ BT Yahoo! Broadband - Free modem offer, sign up online today and save £80 http://btyahoo.yahoo.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query
You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what is called a 'cross product' query. If table A has 10 rows and table B has 20 rows then querying A and B will return 200 rows (every row of A will be joined with every row of B!). -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: index question part 2
rmck [EMAIL PROTECTED] wrote: I understand that I need to update the db's cardinality for this table I need speed Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one because with 56179085 records this could take a while... myisamchk -a does the same as ANALYZE TABLE. You can use either of them. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best-practices backups
For databases I usually just make a backup for each day of the month. After all, disk space is cheap. So if a month has 31 days, I have 31 backups. That gives you about 30 days to discover any corruption that may have occurred in a database. A crashed database is obvious, but corruption usually takes a little while to be noticed, so you want to make sure you can go back far enough to get good data. It's probably a bit overkill, but it's automated so it's no extra work for me. This is on top of the tape backups done for all systems with rotating off site tapes. To avoid extended down time, I also restore the latest backup on another machine. Then if the main computer crashes, I just change a DNS setting (or an IP address if you don't manage your own DNS) to redirect everything to the backup server. This is all done with a fairly simple shell script. On Feb 5, 2004, at 5:55 PM, Michael Collins wrote: Is there any best-practices wisdom on what is the most preferable method of backing up moderately (~10-20,000 record) MySQL 4 databases? A mysql dump to store records as text, the format provided by the BACKUP sql command, or some other method? I am not asking about replication, rotating backups, or remote storage, and I am not concerned about the size of the backup files. Replication might be the best scenario for some sites but this case is not high finance. -- 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: Help with query
Hi Evelyn, How would I do that - would something like this be what you had in mind? left join categories o on o.CategoryID = l.CategoryID This goes in the WHERE clause, right? Thanks! -Erich- -Original Message- From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 8:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help with query You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what is called a 'cross product' query. If table A has 10 rows and table B has 20 rows then querying A and B will return 200 rows (every row of A will be joined with every row of B!). -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- 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 determine when a MySQL database was last modified?
Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to determine when a MySQL database was last modified?
I'm not 100% sure on this, but what about the .myd file timestamp? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 9:09 AM To: Phil Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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 determine when a MySQL database was last modified?
Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with query
If every record in the listing table will have a corresponding record in the category table you may just include the category clause in with the rest. WHERE (a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%') AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID and l.CategoryID=o.CategoryID -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:00 AM To: [EMAIL PROTECTED] Cc: Subject: RE: Help with query Hi Evelyn, How would I do that - would something like this be what you had in mind? left join categories o on o.CategoryID = l.CategoryID This goes in the WHERE clause, right? Thanks! -Erich- -Original Message- From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 8:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help with query You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what is called a 'cross product' query. If table A has 10 rows and table B has 20 rows then querying A and B will return 200 rows (every row of A will be joined with every row of B!). -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to determine when a MySQL database was last modified?
Thanks. I'm using InnoDB tables (for transactions) and there's no sign of any .MYD files for them. I'm starting to think that maybe this information isn't available :( Anyone any other ideas? On Fri, 2004-02-06 at 14:17, Dan Greene wrote: I'm not 100% sure on this, but what about the .myd file timestamp? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 9:09 AM To: Phil Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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: Access denied for user: 'ODBC@localhost' (Using password: YES)
Arun, You are back to the same issue. This has nothing to do with MyODBC. The parameters you are passing are not the correct data types. If no username is passed, the user 'ODBC' is supplied. If no host is passed 'localhost' is supplied. The parameter you are passing for the password is being read (not necessarily correctly), but some value is being passed as the password. You need to have someone familiar with your flavor of COBOL show you how to pass the specific data types that the mysql library requires (based upon the MySQL C API). When your are able to pass and confirm the correct data types to the C functions, the process may work (assuming the callout from COBOL works as advertised). Until you can confirm the data types are being correctly interpreted when passed from COBOL to the function, your only going to experience serendipitous success. You need to solve the problem sequentially and the next step here is confirming the data types and parameters being passed from your COBOL code. Pat... [EMAIL PROTECTED] CocoNet Corporation SW Florida's First ISP - Original Message - From: Arunachalam [EMAIL PROTECTED] To: KKoTY [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 06, 2004 6:21 AM Subject: Re: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) I have installed MyODBC 3.51 and created the DSN for my Database. I want to clarify that; If the User name I have given is could not be resolved by mysal_real_connect, then it automatically establish the connection to the server using [EMAIL PROTECTED] - right If so, Is there need to have the user named ODBC in mysql database. OR It'll connect automatically using the DSN setting i have specified in myODBC. Becaz I don't have the source to check Is COBOL value passed to C as such what I have specified? Please clarify my doubts... regards, Arun. --- KKoTY [EMAIL PROTECTED] wrote: this occures when you ommit the user name, MYSQL C API uses user ODBC as default when you ommit or enter empty string as user name when calling mysql_real_connect() - Original Message - From: Arunachalam [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, February 05, 2004 3:38 PM Subject: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Hi all, Is any one met with the error while connecting to MySQL Server *Access denied for user: '[EMAIL PROTECTED]' (Using password: YES)* if so pleass let me know the remedy to resolve this error. I don't have the user named ODBC in my mysql Database. my configuration is: Windows 2000 SP 4. MySQL Server 4.0.17-max-debug for windows The situation I have met this error is when I try to Connect MySQL from COBOL using the C API provided by MySQL. I have properly link the *libmysql.lib* file into my COBOL compiler. Any suggestion are highly appreciated... Thanks. regards, Arun. Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! India Mobile: Download the latest polyphonic ringtones. Go to http://in.mobile.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to determine when a MySQL database was last modified?
You can try the 'show table status' from mysql. There is an update_time that lists the last modified date for the table. I also found out that these types of commands work with perl DBD::mysql. You can treat the command like a normal sql statement and the results are returned like any other sql. Pretty cool. IMHO I wouldn't bother with this. Just take the backup. As long as you only keep the most recent backup online I don't see the harm. Why do the extra work and risk not having backups? Evelyn -Original Message- From: Phil [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:27 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine when a MySQL database was last modified?
Don't know if it can be done in the database without lots of legwork. You can just use the filesystem to do it though. ls -la within the database directories. It'd probably be a lot easier to use perl or php file functions, then you'd be able to do all your calculations in epoch. P -Phil [EMAIL PROTECTED] wrote: - To: gerald_clark [EMAIL PROTECTED] From: Phil [EMAIL PROTECTED] Date: 02/06/2004 09:27AM cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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: What field lengths to use
From: Adam Staunton [mailto:[EMAIL PROTECTED] Hi all, I just tried to change a field with a type of LongText to Text and with a width of 10 characters in phpmyadmin. It accepted the change of the field type top text but didn't seem to accept the change in field length to 10. To test , I entered 20 characters into a text field in my php/mysql database and it accepted it when I updated. What's wrong ? or is it not possible to assign actual field lengths in phpmyadmin ? The TEXT type doesn't have a user-set limit on it: http://www.mysql.com/doc/en/BLOB.html If all the records going in the column are 10 characters, use a CHAR(10): http://www.mysql.com/doc/en/CHAR.html HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539
RE: How to determine when a MySQL database was last modified?
Sorry. Obviously didn't see this... -Phil [EMAIL PROTECTED] wrote: - To: Dan Greene [EMAIL PROTECTED] From: Phil [EMAIL PROTECTED] Date: 02/06/2004 09:36AM cc: gerald_clark [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: How to determine when a MySQL database was last modified? Thanks. I'm using InnoDB tables (for transactions) and there's no sign of any .MYD files for them. I'm starting to think that maybe this information isn't available :( Anyone any other ideas? On Fri, 2004-02-06 at 14:17, Dan Greene wrote: I'm not 100% sure on this, but what about the .myd file timestamp? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 9:09 AM To: Phil Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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: Help with query
OH MY GOD IT WORKS!!! I got 32 rows in set (0.08 sec). That is fantastic! Thank you so much Now, when I do an explain on this query, I get the following: +---++---+-+ | table | type | possible_keys | key | +---++---+-+ | l | ALL| NULL | NULL| | p | eq_ref | PRIMARY | PRIMARY | | c | eq_ref | PRIMARY | PRIMARY | | a | eq_ref | PRIMARY | PRIMARY | | o | eq_ref | PRIMARY | PRIMARY | +---++---+-+ +-+---+--+-+ | key_len | ref | rows | Extra | +-+---+--+-+ |NULL | NULL | 2647 | Using temporary; Using filesort | | 8 | l.PublisherID |1 | Using where | | 8 | l.ComposerID |1 | Using where | | 8 | l.ArrangerID |1 | Using where | | 4 | l.CategoryID |1 | | +-+---+--+-+ This seems really efficient, since the only large number of rows to search against is the main listings table, if I read this right. Is there any further optimization that I can do, or this as good as it gets? Believe me, I am NOT complaining!!! Thanks again! -Erich- If every record in the listing table will have a corresponding record in the category table you may just include the category clause in with the rest. WHERE (a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%') AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID and l.CategoryID=o.CategoryID Hi Evelyn, How would I do that - would something like this be what you had in mind? left join categories o on o.CategoryID = l.CategoryID This goes in the WHERE clause, right? Thanks! -Erich- -Original Message- From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 8:53 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Help with query You will need parentheses around the 'or' clauses of your where clause. You also don't seem to join the categories table with any other tables. If you don't join tables you will create what is called a 'cross product' query. If table A has 10 rows and table B has 20 rows then querying A and B will return 200 rows (every row of A will be joined with every row of B!). -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- 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]
Just simple question...
Hello everybody! I need to create some simple database. I've started to build it up in Microsoft Access. MS Access was really great for me, it was very easy and user friendly, intuitive. Without any manual I've managed to create a table, to create some views and queries and to prepare reports from them. But, I don't have MS Access at home. I don't want to use illegal software and I am not keen on buying it. That's why I've looked up MySQL. I thought MySQL could be something similar to Access, and for free, so I downloaded it. But now, I've installed it and I realized, that it's something different. There is need to run some server and i don't understand it. Here is my question: Is it possible to use MySQL in the similar way to Access? Without any servers and administration and clients and controlcenter...? I don't need any network at all, I just would like to create and run some small database for home use. Is it possible in MySQL? And, it seems, there is no user interface in MySQL at all. Am I right? Dusan - Tato sprava neobsahuje virusy. This message is virus-free. Automatic GroupWise signature added by GWAVA. - gwavasig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to determine when a MySQL database was last modified?
Nice try... but 'show table status' just displays NULL for Update_time - maybe because they're InnoDB tables. Besides, I didn't really want to have to squirrel around all the tables to see if the DB itself has been changed. Since what I want to do doesn't seem possible I'll carry on as usual... backing everything up :( Also, I'll suggest it as an enhancement. Thanks. On Fri, 2004-02-06 at 14:28, Schwartz, Evelyn wrote: You can try the 'show table status' from mysql. There is an update_time that lists the last modified date for the table. I also found out that these types of commands work with perl DBD::mysql. You can treat the command like a normal sql statement and the results are returned like any other sql. Pretty cool. IMHO I wouldn't bother with this. Just take the backup. As long as you only keep the most recent backup online I don't see the harm. Why do the extra work and risk not having backups? Evelyn -Original Message- From: Phil [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:27 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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: Just simple question...
Yes , you are correct. There is no GUI with mySQL. You can down load one off the web. look on www.mysql.com. I think they have a free one there. I use SQLyog, but you must pay for that. I would advise the move to mySQL from access. I did it a month ago and have never looked back. There is a bit of a learning curve to start with, but if you are in I.T. professionally it's a good thing to have in your toolbox. From: Dusan Spisak [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Just simple question... Date: Fri, 06 Feb 2004 16:03:23 +0100 Hello everybody! I need to create some simple database. I've started to build it up in Microsoft Access. MS Access was really great for me, it was very easy and user friendly, intuitive. Without any manual I've managed to create a table, to create some views and queries and to prepare reports from them. But, I don't have MS Access at home. I don't want to use illegal software and I am not keen on buying it. That's why I've looked up MySQL. I thought MySQL could be something similar to Access, and for free, so I downloaded it. But now, I've installed it and I realized, that it's something different. There is need to run some server and i don't understand it. Here is my question: Is it possible to use MySQL in the similar way to Access? Without any servers and administration and clients and controlcenter...? I don't need any network at all, I just would like to create and run some small database for home use. Is it possible in MySQL? And, it seems, there is no user interface in MySQL at all. Am I right? Dusan - Tato sprava neobsahuje virusy. This message is virus-free. Automatic GroupWise signature added by GWAVA. - gwavasig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to determine when a MySQL database was last modified?
If offline tools works for you try './mysqlshow.exe -vi db_name'. This provides useful information such as 'Create Time' 'Update Time' and 'Check Time'. Gowtham. --- Phil [EMAIL PROTECTED] wrote: Nice try... but 'show table status' just displays NULL for Update_time - maybe because they're InnoDB tables. Besides, I didn't really want to have to squirrel around all the tables to see if the DB itself has been changed. Since what I want to do doesn't seem possible I'll carry on as usual... backing everything up :( Also, I'll suggest it as an enhancement. Thanks. On Fri, 2004-02-06 at 14:28, Schwartz, Evelyn wrote: You can try the 'show table status' from mysql. There is an update_time that lists the last modified date for the table. I also found out that these types of commands work with perl DBD::mysql. You can treat the command like a normal sql statement and the results are returned like any other sql. Pretty cool. IMHO I wouldn't bother with this. Just take the backup. As long as you only keep the most recent backup online I don't see the harm. Why do the extra work and risk not having backups? Evelyn -Original Message- From: Phil [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:27 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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] __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query
On 6 Feb 2004, at 14:38, Erich Beyrent wrote: This seems really efficient, since the only large number of rows to search against is the main listings table, if I read this right. Is there any further optimization that I can do, or this as good as it gets? Believe me, I am NOT complaining!!! Yes, it has to to a table scan on the criteria because of the leading %: it can't use an index for that. And 2500-odd rows is nothing. -- Dave Hodgkinson CTO, Rockit Factory Ltd. http://www.rockitfactory.com/ Web sites for rock bands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Just simple question...
From: Dusan Spisak [mailto:[EMAIL PROTECTED] Here is my question: Is it possible to use MySQL in the similar way to Access? Without any servers and administration and clients and controlcenter...? I don't need any network at all, I just would like to create and run some small database for home use. Is it possible in MySQL? And, it seems, there is no user interface in MySQL at all. Am I right? MySQL is a database server. However, Access is, too. It's just masked by Microsoft and bundled into a nice GUI (graphical user interface, if you're not familiar with the term) package. It's perfectly acceptable to install the MySQL server locally and only use it locally. It doesn't need to be accessible from the outside. There's a companion client for the server, but it's pretty bare-bones command-line stuff. If you're looking for any sort of GUI, you'll need a webserver installed locally as well. Any GUI I've seen for MySQL runs as a web application, usually written in PHP. As someone said before, I can highly recommend taking on the learning curve for MySQL. It's actually much easier than it may seem -- one of those easy to learn, a lifetime to master things. However, if you're turned off by having to either work on the command-line console or run a local webserver to use a GUI, I can't say that MySQL is necessarily what you're looking for. Good luck in your decision, though. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine when a MySQL database was last modified?
mysqlshow gives the same results as SHOW TABLE STATUS, which, unfortunately, doesn't seem to give created/updated dates for InnoDB tables. Michael Gowtham Jayaram wrote: If offline tools works for you try './mysqlshow.exe -vi db_name'. This provides useful information such as 'Create Time' 'Update Time' and 'Check Time'. Gowtham. --- Phil [EMAIL PROTECTED] wrote: Nice try... but 'show table status' just displays NULL for Update_time - maybe because they're InnoDB tables. Besides, I didn't really want to have to squirrel around all the tables to see if the DB itself has been changed. Since what I want to do doesn't seem possible I'll carry on as usual... backing everything up :( Also, I'll suggest it as an enhancement. Thanks. On Fri, 2004-02-06 at 14:28, Schwartz, Evelyn wrote: You can try the 'show table status' from mysql. There is an update_time that lists the last modified date for the table. I also found out that these types of commands work with perl DBD::mysql. You can treat the command like a normal sql statement and the results are returned like any other sql. Pretty cool. IMHO I wouldn't bother with this. Just take the backup. As long as you only keep the most recent backup online I don't see the harm. Why do the extra work and risk not having backups? Evelyn -Original Message- From: Phil [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:27 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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] __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to determine when a MySQL database was last modified?
You could try the following: 1) Perform normal backup. 2) Run sql command flush status. --Resets most status variables to zero. 3) Next week prior to backup, run sql commands: show status like 'Handler_delete' show status like 'Handler_update' show status like 'Handler_write' If any of these values are greater than zero then a table has been modified. You should also note the server start date just in case a server crahed or restarted which will also reset the status variables to zero. Handler_delete - Number of times a row was deleted from a table. Handler_update - Number of requests to update a row in a table. Handler_write - Number of requests to insert a row in a table. Ed -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 9:18 AM To: Gowtham Jayaram Cc: Phil; Schwartz, Evelyn; [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? mysqlshow gives the same results as SHOW TABLE STATUS, which, unfortunately, doesn't seem to give created/updated dates for InnoDB tables. Michael Gowtham Jayaram wrote: If offline tools works for you try './mysqlshow.exe -vi db_name'. This provides useful information such as 'Create Time' 'Update Time' and 'Check Time'. Gowtham. --- Phil [EMAIL PROTECTED] wrote: Nice try... but 'show table status' just displays NULL for Update_time - maybe because they're InnoDB tables. Besides, I didn't really want to have to squirrel around all the tables to see if the DB itself has been changed. Since what I want to do doesn't seem possible I'll carry on as usual... backing everything up :( Also, I'll suggest it as an enhancement. Thanks. On Fri, 2004-02-06 at 14:28, Schwartz, Evelyn wrote: You can try the 'show table status' from mysql. There is an update_time that lists the last modified date for the table. I also found out that these types of commands work with perl DBD::mysql. You can treat the command like a normal sql statement and the results are returned like any other sql. Pretty cool. IMHO I wouldn't bother with this. Just take the backup. As long as you only keep the most recent backup online I don't see the harm. Why do the extra work and risk not having backups? Evelyn -Original Message- From: Phil [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:27 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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] __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- 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]
Updateing fields from VB
Hi, What is the easiest way to update data in a database field from withing VB. Do I have to build an sql statement that can then be executed on a connection or is their some way to get the update method to work as in access? This is probably a dimbo question but until my books arrive im afraid I have to ask ;) Thanks Paul Owen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Privileges
Hi List, Is it possible to GRANT an USER to just UPDATE one specific FIELD instead of the entire TABLE? Is it also possible to create a GROUP with all GRANTs and then create the USERs linked with a specific GROUP? Thanks. -- Andre Matos [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 determine when a MySQL database was last modified?
Thanks Gowtham and Ed. However, even this solution seems a bit dodgy when it comes to backing up... I'll stick with backing up all databases for now, and put in an enhancement request. Thanks, Phil On Fri, 2004-02-06 at 16:35, [EMAIL PROTECTED] wrote: You could try the following: 1) Perform normal backup. 2) Run sql command flush status.--Resets most status variables to zero. 3) Next week prior to backup, run sql commands: show status like 'Handler_delete' show status like 'Handler_update' show status like 'Handler_write' If any of these values are greater than zero then a table has been modified. You should also note the server start date just in case a server crahed or restarted which will also reset the status variables to zero. Handler_delete - Number of times a row was deleted from a table. Handler_update - Number of requests to update a row in a table. Handler_write - Number of requests to insert a row in a table. Ed -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 9:18 AM To: Gowtham Jayaram Cc: Phil; Schwartz, Evelyn; [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? mysqlshow gives the same results as SHOW TABLE STATUS, which, unfortunately, doesn't seem to give created/updated dates for InnoDB tables. Michael Gowtham Jayaram wrote: If offline tools works for you try './mysqlshow.exe -vi db_name'. This provides useful information such as 'Create Time' 'Update Time' and 'Check Time'. Gowtham. --- Phil [EMAIL PROTECTED] wrote: Nice try... but 'show table status' just displays NULL for Update_time - maybe because they're InnoDB tables. Besides, I didn't really want to have to squirrel around all the tables to see if the DB itself has been changed. Since what I want to do doesn't seem possible I'll carry on as usual... backing everything up :( Also, I'll suggest it as an enhancement. Thanks. On Fri, 2004-02-06 at 14:28, Schwartz, Evelyn wrote: You can try the 'show table status' from mysql. There is an update_time that lists the last modified date for the table. I also found out that these types of commands work with perl DBD::mysql. You can treat the command like a normal sql statement and the results are returned like any other sql. Pretty cool. IMHO I wouldn't bother with this. Just take the backup. As long as you only keep the most recent backup online I don't see the harm. Why do the extra work and risk not having backups? Evelyn -Original Message- From: Phil [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 9:27 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: How to determine when a MySQL database was last modified? Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? On Fri, 2004-02-06 at 14:09, gerald_clark wrote: Add a timestamp field to each table. Phil wrote: Hi, I have many smallish, discrete MySQL databases, each of which I would like to backup individually (mysqldump seems fine for this). However, there's no point re-backing up a database that has not changed since the last time it was backed up. So how can I tell if when a MySQL database was last modified, so that I can decide whether to run mysqldump on it again or not? Any help with this would be much appreciated. Thanks, Phil -- 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] __ Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.html -- 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 4 goes to sleep with table locks?
I am using Mandrake 9.1 and MySQL 4.0.11 from the LM CD's. In my application, I have a table that stores the sequential numbers of invoices prepared by several stores. Every time a salesman prepares an invoice for a customer, the system goes to this table, locks it with lock tables table write, reads the number of the last invoice made for that store, adds one to that number, updates the field, and unlocks the table. In theory, I should never get a duplicate invoice, but in practice, I do. So, it seems that MySQL maintains, under some special circumstances, the same number and does not update it. Maybe there is a parameter in my.cnf I could change to make sure all updates are processed inmediately? Auto increment field would not apply in this case, since there is only one record per store that gets updated for every invoice. I would appreciate any advise. Thank you. -- Alfredo J. Cole [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Logon bad handshake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I still have the problem with being unable to logon to a database with a password. When the password is entered - either on the command line (-p???) or on the following line (-p without a password), mysql returns a Bad handshake error message. I'm trying to connect on the machine running mysql The server version is 4.1.1-1 The client software is 4.1.0-0 (which is, I believe, the latest for download) The user is not trying to log on remotely - and it happens with any user (e.g. I can't even put a password on the root account) Things work fine as long as no password is involved. Can anyone offer any suggestions of how to fix this? I *CAN* convert everything to PostgreSQL if I have to, but that is a last resort. For what we're doing, mysql is a better solution. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAI8zIjeziQOokQnARAmZqAKC3upODywkEUkPYSLpj5g0zuRO6ZQCgkMqt jqbPKlfD1fcbgAZIKWYFOT4= =u7sQ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logon bad handshake
On Fri, 6 Feb 2004, Michael Satterwhite wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I still have the problem with being unable to logon to a database with a password. When the password is entered - either on the command line (-p???) or on the following line (-p without a password), mysql returns a Bad handshake error message. I'm trying to connect on the machine running mysql The server version is 4.1.1-1 The client software is 4.1.0-0 (which is, I believe, the latest for download) The user is not trying to log on remotely - and it happens with any user (e.g. I can't even put a password on the root account) Things work fine as long as no password is involved. Can anyone offer any suggestions of how to fix this? I *CAN* convert everything to PostgreSQL if I have to, but that is a last resort. For what we're doing, mysql is a better solution. The issue is that 4.1.0 used a different authentication handshake with a different form of password encoding. Due to the fact that 4.1.0 was alpha, it was decided not to support the authentication that 4.1.0 used for later releases as it was the only release that used it. If you upgrade the client to 4.1.1 (which is available for download? where didn't you see it?) then you will no longer get that error message. Regards, Harrison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logon bad handshake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 06 February 2004 11:45, Harrison Fisk wrote: The issue is that 4.1.0 used a different authentication handshake with a different form of password encoding. Due to the fact that 4.1.0 was alpha, it was decided not to support the authentication that 4.1.0 used for later releases as it was the only release that used it. If you upgrade the client to 4.1.1 (which is available for download? where didn't you see it?) then you will no longer get that error message. All solved now, thanks. I *KNOW* I checked twice on the download, I think I'm getting senile. Thanks again -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAI9VmjeziQOokQnARAi/wAJ9pWvG110t4nSx+Bz2THW0zEyJzgQCaA+fW eYyKMKhq73KMRKzgawQ5QFE= =IuYA -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Just simple question...
Hello Dusan: MS Access is actually a user front end and not a database engine at all. Many people don't understand this. A front end talks to a database engine, known as a data source in the lingo. MS Access defaults to using one of Microsoft's databases, I believe it is called jet or something like that. MySQL is a better database engine. You can set up Microsoft Access to talk to mySQL. I have done it a couple of times. Here is the recipe how I did it http://www.washington.edu/computing/web/publishing/mysql-access.html It has also been discussed on this list because I was part of the conversation. But if you want to use mySQL you have to either run a database server or some hosting services will give you a database on theirs free and you can connect to it remotely. Once you get your data source set up you can usually forget about it anyway. The closest free thing to a generic front end to mysql is mysqladmin, written in php. It doesnt have the polish of MS access though. Dusan Spisak wrote: Hello everybody! I need to create some simple database. I've started to build it up in Microsoft Access. MS Access was really great for me, it was very easy and user friendly, intuitive. Without any manual I've managed to create a table, to create some views and queries and to prepare reports from them. But, I don't have MS Access at home. I don't want to use illegal software and I am not keen on buying it. That's why I've looked up MySQL. I thought MySQL could be something similar to Access, and for free, so I downloaded it. But now, I've installed it and I realized, that it's something different. There is need to run some server and i don't understand it. Here is my question: Is it possible to use MySQL in the similar way to Access? Without any servers and administration and clients and controlcenter...? I don't need any network at all, I just would like to create and run some small database for home use. Is it possible in MySQL? And, it seems, there is no user interface in MySQL at all. Am I right? Dusan - Tato sprava neobsahuje virusy. This message is virus-free. Automatic GroupWise signature added by GWAVA. - gwavasig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine when a MySQL database was last modified?
On Fri, Feb 06, 2004 at 09:17:32AM -0500, Dan Greene wrote: I'm not 100% sure on this, but what about the .myd file timestamp? Well, it depends on which table type, obvously. There are several files for each database, see what the mtime is on each of them, to determine what's a live file. If you're using rsync for backups, I wouldn't worry; it's good at moving partial files around. -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query matching
I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
From: Ed Curtis [mailto:[EMAIL PROTECTED] I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? This is untested, but I imagine you could do the following: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND changelog.orig_id IN ( pages.mls_1, pages.mls_2, pages.mls_3, pages.mls_4, pages.mls_5, pages.mls_6, pages.mls_7, pages.mls_8, pages.mls_9, pages.mls_10, pages.mls_11, pages.mls_12 ); -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Client mySQL Server
First of all I admit that I am not an expert of MySQL. However, during the last three months I have TEST installed MySQL Server software and gone through the tutorial. My next step is to setup three computers as follows: |--| |--| |--| | 12.21.237.10 | | 12.21.237.11 | | 12.21.237.12 | | freeRADIUS | | qmail/HTTP | | DataServer | |--| |--| |--| | | | |---| My design is have run MySQL on data server and keep all data (sql) on this server. freeRADIUS server is for authentication and qmail/HTTP server is for mail and web pages. I am told that I need to install MySQL client program on freeRADIUS qmail/HTTP servers and master MySQL on DataServer. I have not been able to find either Client or Master MySQL but just MySQL! Is there a subset of MySQL which is known as Client and/or Master MySQL or is it just terminology? Any help is highly appreciated. Kirti -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query matching
Yes, I think the most straight forward way is to simply put in a series of grouped OR statements. See below. SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND ( changelog.orig_id = pages.mls_1 OR changelog.orig_id = pages.mls_2 OR changelog.orig_id = pages.mls_3 OR changelog.orig_id = pages.mls_4 OR changelog.orig_id = pages.mls_5 OR changelog.orig_id = pages.mls_6 OR changelog.orig_id = pages.mls_7 OR changelog.orig_id = pages.mls_8 OR changelog.orig_id = pages.mls_9 OR changelog.orig_id = pages.mls_10 OR changelog.orig_id = pages.mls_11 OR changelog.orig_id = pages.mls_12 ) John A. McCaskey -Original Message- From: Ed Curtis [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Query matching I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- 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: Query matching
Thanks, that seemed the sensible way to me as well. I just didn't know for sure if you could do that in a MySQL query for sure. Thanks, Ed On Fri, 6 Feb 2004, John McCaskey wrote: Yes, I think the most straight forward way is to simply put in a series of grouped OR statements. See below. SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine AND ( changelog.orig_id = pages.mls_1 OR changelog.orig_id = pages.mls_2 OR changelog.orig_id = pages.mls_3 OR changelog.orig_id = pages.mls_4 OR changelog.orig_id = pages.mls_5 OR changelog.orig_id = pages.mls_6 OR changelog.orig_id = pages.mls_7 OR changelog.orig_id = pages.mls_8 OR changelog.orig_id = pages.mls_9 OR changelog.orig_id = pages.mls_10 OR changelog.orig_id = pages.mls_11 OR changelog.orig_id = pages.mls_12 ) John A. McCaskey -Original Message- From: Ed Curtis [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 10:20 AM To: [EMAIL PROTECTED] Subject: Query matching I've been challenged to write a matching query in a project and do not know how to handle a part of it. The criteria are as follows: SELECT * from pages WHERE changelog.agent = pages.agent AND changelog.company = pages.company AND changelog.magazine = pages.magazine Now for the challenging part for me at least. one of the following must at least be true for the query to return a result. changelog.orig_id = pages.mls_1 changelog.orig_id = pages.mls_2 changelog.orig_id = pages.mls_3 changelog.orig_id = pages.mls_4 changelog.orig_id = pages.mls_5 changelog.orig_id = pages.mls_6 changelog.orig_id = pages.mls_7 changelog.orig_id = pages.mls_8 changelog.orig_id = pages.mls_9 changelog.orig_id = pages.mls_10 changelog.orig_id = pages.mls_11 changelog.orig_id = pages.mls_12 Would I nest these as an OR statement and how would I go about it? Thanks, Ed Curtis -- 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: Client mySQL Server
Just went through this. You need to install MySQL from source. The basic commands you must execute to install a MySQL source distribution are: shell groupadd mysql shell useradd -g mysql mysql shell gunzip mysql-VERSION.tar.gz | tar -xvf - shell cd mysql-VERSION shell ./configure --without-server shell make shell make install The ./configure --without-server only installs the client. You need to read the doco about the source install to look for other options you may want. Evelyn -Original Message- From: Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 1:31 PM To: '[EMAIL PROTECTED]' Subject: RE: Client mySQL Server First of all I admit that I am not an expert of MySQL. However, during the last three months I have TEST installed MySQL Server software and gone through the tutorial. My next step is to setup three computers as follows: |--| |--| |--| | 12.21.237.10 | | 12.21.237.11 | | 12.21.237.12 | | freeRADIUS | | qmail/HTTP | | DataServer | |--| |--| |--| | | | |---| My design is have run MySQL on data server and keep all data (sql) on this server. freeRADIUS server is for authentication and qmail/HTTP server is for mail and web pages. I am told that I need to install MySQL client program on freeRADIUS qmail/HTTP servers and master MySQL on DataServer. I have not been able to find either Client or Master MySQL but just MySQL! Is there a subset of MySQL which is known as Client and/or Master MySQL or is it just terminology? Any help is highly appreciated. Kirti -- 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]
What replaces IN?
I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Erwin from sql script
I have a sql file that I would like to convert to an ER diagram - any suggestions on a tool (free if possible) that would help me do this? Thanks for your suggestions.
Re: What replaces IN?
IN is supported in 4.1, I'm not sure about 4.0. But any, you can do you query like this: select table1.* from table1 left join table2 on table1.item_id=table2.item_id where table2.item_id is not null I think that's right. It may actually be quicker than using IN. On Feb 6, 2004, at 2:20 PM, Scott Purcell wrote: I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) -- 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: What replaces IN?
IN is supported in mysql, but subqueries do not work before 4.1.x. You could rewrite your query with a join, though. Something like: SELECT table1.* FROM table1, table2 WHERE table1.item_id = table2.item_id See http://www.mysql.com/doc/en/Rewriting_subqueries.html for more. Michael Scott Purcell wrote: I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What replaces IN?
From: Scott Purcell [mailto:[EMAIL PROTECTED] I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) IN has been supported for a while, but subselects have not. SELECT * FROM table1 WHERE item_id IN (1, 2, 3, 4, 5) should work, but not a subselect. I'm not sure of subselect syntax, actually, or what (recent) version in which it was introduced. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine when a MySQL database was last modified?
On 06-Feb-2004 Phil wrote: Thanks. But I would have thought that such information would have been kept automatically somewhere by the server, and it's just a case of how to get at it. I have quite a few tables in each database so I don't really want to have to maintain a timestamp on each update, and then go around all of them at backup time :( Anyone got any other ideas? SHOW TABLE STATUS Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Erwin from sql script
Datanamic is not free but very affordable and a nice utility. Windows only though... Original Message On 2/6/04, 1:22:55 PM, David Perron [EMAIL PROTECTED] wrote regarding Erwin from sql script: I have a sql file that I would like to convert to an ER diagram - any suggestions on a tool (free if possible) that would help me do this? Thanks for your suggestions. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Myisampack missing from 4.1.1 Win32 binaries
Hello all- Is the myisampack utility supposed to be included in the 4.1.1 binaries for Windows? It's not there - I couldn't find any explicit reference in the doc about whether this is deliberate or maybe it was just missed? Thanks, Rick
select on set yields exponential notation
I'm trying to select all the fields from a set but when the number is large, it is returned in exponential notation. Is there a way to keep the returned number an integer? Example: create simple table: create table test1( var1 int, var2 set(1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,30, 41,42,43,44,45,46,47,48,49,40, 51,52,53,54,55,56,57,58,59,60) ); Add a row: insert into test1 values (1, 40); Then select oops: select var1,var2+0 from test1; +--+-+ | var1 | var2+0 | +--+-+ |1 | 5.6294995342131e+14 | +--+-+ How can I get this output as an integer? I've tried both MySQL 3.23 and 4.0.17 with the same result. I'm running on linux (RHL 8.0) Thanks - Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to determine when a MySQL database was last modified?
Doesn't seem to change the mtime on table files. It appears that for InnoDB tables these files are only updated when the definition of a table is changed. The content of the all InnoDB tables is kept in one or two massive files directly under the 'data' directory! On Fri, 2004-02-06 at 18:13, Brian Reichert wrote: On Fri, Feb 06, 2004 at 09:17:32AM -0500, Dan Greene wrote: I'm not 100% sure on this, but what about the .myd file timestamp? Well, it depends on which table type, obvously. There are several files for each database, see what the mtime is on each of them, to determine what's a live file. If you're using rsync for backups, I wouldn't worry; it's good at moving partial files around. -- Brian Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303 Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on set yields exponential notation
Why are you adding 0? Try this: SELECT var1, var2 FROM test1; Michael Greg Vines wrote: I'm trying to select all the fields from a set but when the number is large, it is returned in exponential notation. Is there a way to keep the returned number an integer? Example: create simple table: create table test1( var1 int, var2 set(1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,30, 41,42,43,44,45,46,47,48,49,40, 51,52,53,54,55,56,57,58,59,60) ); Add a row: insert into test1 values (1, 40); Then select oops: select var1,var2+0 from test1; +--+-+ | var1 | var2+0 | +--+-+ |1 | 5.6294995342131e+14 | +--+-+ How can I get this output as an integer? I've tried both MySQL 3.23 and 4.0.17 with the same result. I'm running on linux (RHL 8.0) Thanks - Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What replaces IN?
This will work, but there's no reason to use a LEFT JOIN here. With a LEFT JOIN, you get a row for each item_id in table1 that does not have a corresponding item_id in table2, with the table2 fields set to NULL. You then have to filter these out with your WHERE clause. Just use a simple join: SELECT table1.* FROM table1, table2 WHERE table1.item_id=table2.item_id That way, you only get rows for each item_id that exists in both tables, which was the point, with no need to filter the extra stuff the LEFT JOIN would have created. Michael Brent Baisley wrote: IN is supported in 4.1, I'm not sure about 4.0. But any, you can do you query like this: select table1.* from table1 left join table2 on table1.item_id=table2.item_id where table2.item_id is not null I think that's right. It may actually be quicker than using IN. On Feb 6, 2004, at 2:20 PM, Scott Purcell wrote: I am trying to run the below query, and believe that the 'IN' is not supported in mysql. What is the 'IN' replacement? I tried exists and that doesn't work. select * from table1 where item_id IN (select item_id from table2) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What replaces IN?
My thinking on using a left join was that there was not a one to one relationship between the tables, otherwise the data would be in one table. So a regular join would produce duplicate records if there was a one to many relationship between table1 and table2. A left join would assure a distinct result set from table1. On Feb 6, 2004, at 4:04 PM, Michael Stassen wrote: This will work, but there's no reason to use a LEFT JOIN here. With a LEFT JOIN, you get a row for each item_id in table1 that does not have a corresponding item_id in table2, with the table2 fields set to NULL. You then have to filter these out with your WHERE clause. Just use a simple join: SELECT table1.* FROM table1, table2 WHERE table1.item_id=table2.item_id That way, you only get rows for each item_id that exists in both tables, which was the point, with no need to filter the extra stuff the LEFT JOIN would have created. -- 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: select on set yields exponential notation
With sets if you select the set, you get a comma delimited list, but if you add zero, you get the number value of the entire set (which is what I want). The response is not always in exponential notation - just when a high order bit is set. This seems to be a problem with the output formatting. Do you know if there is a way to set the number of characters in a numeric response? On Fri, 2004-02-06 at 12:54, Michael Stassen wrote: Why are you adding 0? Try this: SELECT var1, var2 FROM test1; Michael Greg Vines wrote: I'm trying to select all the fields from a set but when the number is large, it is returned in exponential notation. Is there a way to keep the returned number an integer? Example: create simple table: create table test1( var1 int, var2 set(1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,30, 41,42,43,44,45,46,47,48,49,40, 51,52,53,54,55,56,57,58,59,60) ); Add a row: insert into test1 values (1, 40); Then select oops: select var1,var2+0 from test1; +--+-+ | var1 | var2+0 | +--+-+ |1 | 5.6294995342131e+14 | +--+-+ How can I get this output as an integer? I've tried both MySQL 3.23 and 4.0.17 with the same result. I'm running on linux (RHL 8.0) Thanks - Greg -- Greg Vines mailto:[EMAIL PROTECTED] Manzanita Systems http://www.manzanitasystems.com 14400 Midland Road Voice: (858) 679-8990 x104 Poway, CA 92064 Fax: (858) 679-8991 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on set yields exponential notation
If you are using 4.0.2 and above you can use cast. mysql select var1,cast(var2 as unsigned) from test1; +--++ | var1 | cast(var2 as unsigned) | +--++ |1 |562949953421312 | |1 | 1 | |1 | 32768 | +--++ 3 rows in set (0.00 sec) Note I add a row with 1 and another with 16. Bernard On Friday 06 February 2004 16:22, Greg Vines wrote: With sets if you select the set, you get a comma delimited list, but if you add zero, you get the number value of the entire set (which is what I want). The response is not always in exponential notation - just when a high order bit is set. This seems to be a problem with the output formatting. Do you know if there is a way to set the number of characters in a numeric response? On Fri, 2004-02-06 at 12:54, Michael Stassen wrote: Why are you adding 0? Try this: SELECT var1, var2 FROM test1; Michael Greg Vines wrote: I'm trying to select all the fields from a set but when the number is large, it is returned in exponential notation. Is there a way to keep the returned number an integer? Example: create simple table: create table test1( var1 int, var2 set(1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,30, 41,42,43,44,45,46,47,48,49,40, 51,52,53,54,55,56,57,58,59,60) ); Add a row: insert into test1 values (1, 40); Then select oops: select var1,var2+0 from test1; +--+-+ | var1 | var2+0 | +--+-+ |1 | 5.6294995342131e+14 | +--+-+ How can I get this output as an integer? I've tried both MySQL 3.23 and 4.0.17 with the same result. I'm running on linux (RHL 8.0) Thanks - Greg -- Greg Vines mailto:[EMAIL PROTECTED] Manzanita Systems http://www.manzanitasystems.com 14400 Midland Road Voice: (858) 679-8990 x104 Poway, CA 92064 Fax: (858) 679-8991 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with wildcards in host field .
Hi everybody : I'm experiencing a trouble with user permissions and wildcards in the host field ( % ). I have several MySQL databases and we connect to them through myodbc to Centura team developer apps . Since the beginning we create all the users with a wildcard in the host field because we needed to connect from different LAN ' s . in the other hand ,we build a web server with apache - php - mysql and started to create app's which we used to connect to the 'stand alone' MySQL Servers. Everything was fine, until the web server crashed . So, we needed to move all the php app's to one of our MySQL 'stand alone' servers . since then we can not connect through php to the databases located in the same server . we have errors like ' access denied to [EMAIL PROTECTED] ' and thats o.k. the questions is , the wildcard in host field doesn't involve localhost o a machine host's ? Which kind of permissions we have to put in host fields to have a mobility and not to be afraid to move our servers for an emergency ? the mysql version i'm using is 4.0.14 . Thanks in Advance and happy weekend ¡ :-) Saludos / Regards , Alvaro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select on set yields exponential notation
Thanks Bernard - that fixed it! On Fri, 2004-02-06 at 13:41, Bernard Clement wrote: If you are using 4.0.2 and above you can use cast. mysql select var1,cast(var2 as unsigned) from test1; +--++ | var1 | cast(var2 as unsigned) | +--++ |1 |562949953421312 | |1 | 1 | |1 | 32768 | +--++ 3 rows in set (0.00 sec) Note I add a row with 1 and another with 16. Bernard On Friday 06 February 2004 16:22, Greg Vines wrote: With sets if you select the set, you get a comma delimited list, but if you add zero, you get the number value of the entire set (which is what I want). The response is not always in exponential notation - just when a high order bit is set. This seems to be a problem with the output formatting. Do you know if there is a way to set the number of characters in a numeric response? On Fri, 2004-02-06 at 12:54, Michael Stassen wrote: Why are you adding 0? Try this: SELECT var1, var2 FROM test1; Michael Greg Vines wrote: I'm trying to select all the fields from a set but when the number is large, it is returned in exponential notation. Is there a way to keep the returned number an integer? Example: create simple table: create table test1( var1 int, var2 set(1,2,3,4,5,6,7,8,9,10, 11,12,13,14,15,16,17,18,19,20, 21,22,23,24,25,26,27,28,29,30, 31,32,33,34,35,36,37,38,39,30, 41,42,43,44,45,46,47,48,49,40, 51,52,53,54,55,56,57,58,59,60) ); Add a row: insert into test1 values (1, 40); Then select oops: select var1,var2+0 from test1; +--+-+ | var1 | var2+0 | +--+-+ |1 | 5.6294995342131e+14 | +--+-+ How can I get this output as an integer? I've tried both MySQL 3.23 and 4.0.17 with the same result. I'm running on linux (RHL 8.0) Thanks - Greg -- Greg Vines mailto:[EMAIL PROTECTED] Manzanita Systems http://www.manzanitasystems.com 14400 Midland Road Voice: (858) 679-8990 x104 Poway, CA 92064 Fax: (858) 679-8991 -- Greg Vines mailto:[EMAIL PROTECTED] Manzanita Systems http://www.manzanitasystems.com 14400 Midland Road Voice: (858) 679-8990 x104 Poway, CA 92064 Fax: (858) 679-8991 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A challenge I think
Hi Using MySQL 4x I have two tables Lists and members Now for every list that a member is a member of there is list ID in the members table so to query a members lists I would do the following SELECT members.EmailAddr, members.ListID FROM members INNER JOIN lists ON members.ListID = lists.ListID WHERE (((members.EmailAddr)=[EMAIL PROTECTED])); So I get say [EMAIL PROTECTED] 3 [EMAIL PROTECTED] 2 [EMAIL PROTECTED] 44 And it works fine, but now I want to find which lists I'm not a member of and would appreciate some help. Ideally I would change the way the system works but that is not an option Regards John Berman
Re: A challenge I think
Hi, Using MySQL 4x I have two tables Lists and members Now for every list that a member is a member of there is list ID in the members table so to query a members lists I would do the following SELECT members.EmailAddr, members.ListID FROM members INNER JOIN lists ON members.ListID = lists.ListID WHERE (((members.EmailAddr)=[EMAIL PROTECTED])); Loose the parenthesis - no need. So I get say [EMAIL PROTECTED] 3 [EMAIL PROTECTED] 2 [EMAIL PROTECTED] 44 And it works fine, but now I want to find which lists I'm not a member of and would appreciate some help. select l.listid from lists l where l.listid not in (select m.listid from members m where m.emailaddr = '[EMAIL PROTECTED]') With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A challenge I think
Or use an outer join with where members.list_id is null, as was mentioned on the list earlier today. --Walt -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, February 06, 2004 3:18 PM To: [EMAIL PROTECTED] Subject: Re: A challenge I think Hi, Using MySQL 4x I have two tables Lists and members Now for every list that a member is a member of there is list ID in the members table so to query a members lists I would do the following SELECT members.EmailAddr, members.ListID FROM members INNER JOIN lists ON members.ListID = lists.ListID WHERE (((members.EmailAddr)=[EMAIL PROTECTED])); Loose the parenthesis - no need. So I get say [EMAIL PROTECTED] 3 [EMAIL PROTECTED] 2 [EMAIL PROTECTED] 44 And it works fine, but now I want to find which lists I'm not a member of and would appreciate some help. select l.listid from lists l where l.listid not in (select m.listid from members m where m.emailaddr = '[EMAIL PROTECTED]') With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
iterating through a month
In MySql is it possible to repeat through a month, for example I would like to Select count(user) from table where date = x However, I want x to be a range from the first of the month to the last day in the month, I of course would need to pass in a month and year value to limit it to that month and year. Basically, I am trying to provide a month at a glance report of total posts by various users to a table. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]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]
INNODB - Reclaiming ibdata space and various ibdata questions
Good Afternoon and thank you in advance for any help that you may be able to provide. QUESTION 1 : How do you reclaiming ibdata space? We have 3 databases that together use several ibdata files (ie each database does not have its own ibdata file) . We would like to drop 2 databases and reclaim the ibdata space that the 2 databases took up. What is the best way to do this? QUESTION 2 : Can you have multiple MySQL Instances that contain one database and a seperate ibdata spaces? Is this the best way to create seperate ibdata files for each database? What would you recommend? QUESTION 3: How can you defrag a database instead of a table? We have found that you can defrag a table: From MySQL Manual: The way to do the defragmenting is to perform a 'null' alter table operation ALTER TABLE tablename TYPE=InnoDB. We would like to do this for the complete database. Thanks again for any help. Respectfully, Mike - Do you Yahoo!? Yahoo! Finance: Get your refund fast by filing online
Backup Recover Database
Dear MySQL'ers, I have backed up my db using : % mysqldump -p -u tfiedler --opt BigData ./db_backup.sql When db_backup.sql is scp'd (secure copy) to another machine running mysqld, then I ssh to that machine and execute : $ mysql -u tfiedler BigData db_backup.sql ERROR 1044: Access denied for user: '@localhost' to database 'BigData' I have no problem connecting to the mysql server though. The top of db_backup.sql looks like : -- MySQL dump 9.10 -- -- Host: localhostDatabase: BigData -- -- -- Server version 4.0.17-standard I assume I am having a permissions problem? Any help would be appreciated. Thank you, Tristan -- [EMAIL PROTECTED] (alias) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
command line login question
Hi, I am trying to create a script that automatically logs in to mysql and chooses a db then runs a query. I can login fine when doing it this way: mysql -u user -p it then asks for a pass and it works. but if I try this: mysql -u user -ppassword dbname I get an access error. any suggestions? Thanks, Chuck -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup Recover Database
On Fri, 6 Feb 2004, Tristan Fiedler wrote: Dear MySQL'ers, I have backed up my db using : % mysqldump -p -u tfiedler --opt BigData ./db_backup.sql When db_backup.sql is scp'd (secure copy) to another machine running mysqld, then I ssh to that machine and execute : $ mysql -u tfiedler BigData db_backup.sql ERROR 1044: Access denied for user: '@localhost' to database 'BigData' Hello, You specified a -p to the mysqldump command. Would you like to try specifying the -p parameter to the mysql command too? :) Cheers, Tan Shao Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: command line login question
On Fri, Feb 06, 2004 at 05:06:08PM -0600, Chuck Barnett wrote: Hi, I am trying to create a script that automatically logs in to mysql and chooses a db then runs a query. I can login fine when doing it this way: mysql -u user -p it then asks for a pass and it works. but if I try this: mysql -u user -ppassword dbname I get an access error. any suggestions? I'd guess the user doesn't have permissions for that dbname. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 145 days, processed 1,515,422,676 queries (120/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iterating through a month
In MySql is it possible to repeat through a month, for example I would like to Select count(user) from table where date = x However, I want x to be a range from the first of the month to the last day in the month, I of course would need to pass in a month and year value to limit it to that month and year. Basically, I am trying to provide a month at a glance report of total posts by various users to a table. Looking for ... WHERE MONTH( date ) = x (x=1, 2, ... 12)? PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lower_case_tables_names variable
Iom trying to figure out to use mysqld and set this varible so that when I execute sql scripts, the case is considered. Running this version of mysql in Windows XP Pro. mysql select version(); ++ | version() | ++ | 4.1.1a-alpha-max-debug | ++ What is the syntax and the command I need to use in mysqld? I try to run this and nothing happens- C:\mysql\binmysqld --set lower_case_table_names=2 Thank you in advance for your help!
Re: Lower_case_tables_names variable
At 20:04 -0500 2/6/04, David Perron wrote: Iom trying to figure out to use mysqld and set this varible so that when I execute sql scripts, the case is considered. Running this version of mysql in Windows XP Pro. mysql select version(); ++ | version() | ++ | 4.1.1a-alpha-max-debug | ++ What is the syntax and the command I need to use in mysqld? I try to run this and nothing happens- C:\mysql\binmysqld --set lower_case_table_names=2 Not sure where you got that syntax, but it should be like this instead: C:\mysql\binmysqld --lower_case_table_names=2 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com MySQL Users Conference: April 14-16, 2004 http://www.mysql.com/uc2004/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory Leak using InnoDB ?
Hi, I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM. One application has to access the database (1 connection to the DB is open on startup and left open). However this application performs a lot of queries on the DB. Main InnoDB table : 50.000 Rows Other InnoDB tables (about 8) : From 0 to 1000 rows There is 1 BLOB column in the main table (longest string currently stored : about 500 chars) Thanks to top, I can see that the used memory is constantly increasing and never freed (up to 1 GB) when performing a lot of queries. In fact, I can see the total memory usage increasing but the mysqld process memory usage remains the same. When the DB is not accessed, the memory usage is stable. Stopping MySQL server doesn't free the abnormaly allocated memory. Here is my my.cnf file : server-id=1 socket=/tmp/mysql.sock innodb_data_file_path = ibdata1:750M:autoextend set-variable = innodb_buffer_pool_size=500M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 skip-locking set-variable = max_connections=5 set-variable = read_buffer_size=1M set-variable = sort_buffer=1M set-variable = key_buffer=10M Maybe someone can share his experience to help me to understand/solve the problem. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Leak using InnoDB ?
In the last episode (Feb 07), Geoffrey said: I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM. One application has to access the database (1 connection to the DB is open on startup and left open). However this application performs a lot of queries on the DB. Thanks to top, I can see that the used memory is constantly increasing and never freed (up to 1 GB) when performing a lot of queries. In fact, I can see the total memory usage increasing but the mysqld process memory usage remains the same. When the DB is not accessed, the memory usage is stable. Stopping MySQL server doesn't free the abnormaly allocated memory. Ideally, you should have very little free memory according to top (most systems will see under 20MB free). Free memory is wasted memory. Unix uses memory not allocated to processes for a disk cache. To determine whether you are truly low on memory, run iostat and watch the swap columns. Constant swap activity means you're low on memory. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: iterating through a month
At 05:38 PM 2/6/2004, you wrote: In MySql is it possible to repeat through a month, for example I would like to Select count(user) from table where date = x However, I want x to be a range from the first of the month to the last day in the month, I of course would need to pass in a month and year value to limit it to that month and year. Basically, I am trying to provide a month at a glance report of total posts by various users to a table. Looking for ... WHERE MONTH( date ) = x (x=1, 2, ... 12)? PB You can try: Select count(user) from table where date = '2004-02-01' and date '2004-03-01' This is the fastest if date is an indexed column and because the last comparison operator is and not = you don't need to worry about the # of days in the month. Clever, eh?g If you want to summarize everyone by month you can also do a group by as in: select Cust, count(Cust) as Count, Year(date) as Year, Month(date) as Month from table group by Cust, Year, Month Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory Leak using InnoDB ?
Geoffrey wrote: Hi, I'm running MySQL 4.0.17 with RH Linux 8 on Xeon 3.0/1GB RAM. One application has to access the database (1 connection to the DB is open on startup and left open). However this application performs a lot of queries on the DB. Main InnoDB table : 50.000 Rows Other InnoDB tables (about 8) : From 0 to 1000 rows There is 1 BLOB column in the main table (longest string currently stored : about 500 chars) Thanks to top, I can see that the used memory is constantly increasing and never freed (up to 1 GB) when performing a lot of queries. In fact, I can see the total memory usage increasing but the mysqld process memory usage remains the same. When the DB is not accessed, the memory usage is stable. Stopping MySQL server doesn't free the abnormaly allocated memory. Here is my my.cnf file : server-id=1 socket=/tmp/mysql.sock innodb_data_file_path = ibdata1:750M:autoextend set-variable = innodb_buffer_pool_size=500M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 skip-locking set-variable = max_connections=5 set-variable = read_buffer_size=1M set-variable = sort_buffer=1M set-variable = key_buffer=10M Maybe someone can share his experience to help me to understand/solve the problem. Regards. If you happen to be using the C API in the application then you need to make sure that mysql_free_result() is used after you are finished with the result set. The manual states that mysql_free_result() frees the memory allocated for a result set by |mysql_store_result()|, |mysql_use_result()|, |mysql_list_dbs()|, etc. When you are done with a result set, you must free the memory it uses by calling |mysql_free_result()|. Not sure if other APIs or languages have a similar requirement but it might be worth having a look. Hope it helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What replaces IN?
Brent Baisley wrote: My thinking on using a left join was that there was not a one to one relationship between the tables, otherwise the data would be in one table. So a regular join would produce duplicate records if there was a one to many relationship between table1 and table2. A left join would assure a distinct result set from table1. How would it do that? So far as I know, the left join does not differ from the regular join in this way. Both will produce duplicate rows (based on table1.item_id) if there are multiple corresponding rows in table2. The difference between the two is the left join adds rows for the missing values in table2. If you just want a list of item_ids with no duplicates, you can use DISTINCT or GROUP BY, like this: SELECT DISTINCT table1.item_id FROM table1, table2 WHERE table1.item_id=table2.item_id or SELECT table1.item_id FROM table1, table2 WHERE table1.item_id=table2.item_id GROUP BY table1.item_id Michael On Feb 6, 2004, at 4:04 PM, Michael Stassen wrote: This will work, but there's no reason to use a LEFT JOIN here. With a LEFT JOIN, you get a row for each item_id in table1 that does not have a corresponding item_id in table2, with the table2 fields set to NULL. You then have to filter these out with your WHERE clause. Just use a simple join: SELECT table1.* FROM table1, table2 WHERE table1.item_id=table2.item_id That way, you only get rows for each item_id that exists in both tables, which was the point, with no need to filter the extra stuff the LEFT JOIN would have created. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with wildcards in host field .
Alvaro Avello wrote: Hi everybody : I'm experiencing a trouble with user permissions and wildcards in the host field ( % ). I have several MySQL databases and we connect to them through myodbc to Centura team developer apps . Since the beginning we create all the users with a wildcard in the host field because we needed to connect from different LAN ' s . in the other hand ,we build a web server with apache - php - mysql and started to create app's which we used to connect to the 'stand alone' MySQL Servers. Everything was fine, until the web server crashed . So, we needed to move all the php app's to one of our MySQL 'stand alone' servers . since then we can not connect through php to the databases located in the same server . we have errors like ' access denied to [EMAIL PROTECTED] ' and thats o.k. the questions is , the wildcard in host field doesn't involve localhost o a machine host's ? Which kind of permissions we have to put in host fields to have a mobility and not to be afraid to move our servers for an emergency ? the mysql version i'm using is 4.0.14 . Thanks in Advance and happy weekend ¡ :-) Saludos / Regards , Alvaro. One possibility is that you haven't removed the anonymous user, ''@localhost, which comes with the default install. When mysql matches your [EMAIL PROTECTED] login, host takes precedence over user. So, ''@localhost trumps [EMAIL PROTECTED] when logging in from localhost. You can fix this by either explicitly adding [EMAIL PROTECTED] or by deleting the anonymous ''@localhost user. See http://www.mysql.com/doc/en/Connection_access.html for more. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC, my_SQLPrepare() fails
hi, I have a problem, executing queries through MySQL-4.0.15, but the same thing goes well when run with MySQL-3.23.49, for ODBC connectivity i'm using libmyodbc3-3.51, so is there some compaitibility problem as the application crashes for the reason 0x4054afde in my_SQLPrepare () from /usr/local/lib/libmyodbc3-3.51.06.so= so i think my_SQLPrepare() method fails somehow so can anyone help me, thanks in advance. === logs sql.log === [ODBC][4507][__handles.c][368] Exit:[SQL_SUCCESS] Environment = 0x8664fb8 [ODBC][4507][SQLSetEnvAttr.c][154] Entry: Environment = 0x8664fb8 Attribute = SQL_ATTR_ODBC_VERSION Value = 0x3 StrLen = (nil) [ODBC][4507][SQLSetEnvAttr.c][315] Exit:[SQL_SUCCESS] [ODBC][4507][SQLAllocHandle.c][315] Entry: Handle Type = 2 Input Handle = 0x8664fb8 [ODBC][4507][SQLAllocHandle.c][429] Exit:[SQL_SUCCESS] Output Handle = 0x8665548 [ODBC][4507][SQLSetConnectAttr.c][267] Entry: Connection = 0x8665548 Attribute = SQL_ATTR_LOGIN_TIMEOUT Value = 0x5 StrLen = (nil) [ODBC][4507][SQLSetConnectAttr.c][453] Exit:[SQL_SUCCESS] [ODBC][4507][SQLConnect.c][2390] Entry: Connection = 0x8665548 Server Name = [TelePro][length = 7 (SQL_NTS)] User Name = [ippbx][length = 5 (SQL_NTS)] Authentication = [*][length = 5 (SQL_NTS)] [ODBC][4507][SQLConnect.c][2947] Exit:[SQL_SUCCESS] [ODBC][4507][SQLAllocHandle.c][476] Entry: Handle Type = 3 Input Handle = 0x8665548 [ODBC][4507][SQLAllocHandle.c][840] Exit:[SQL_SUCCESS] Output Handle = 0x866cbd8 [ODBC][4507][SQLExecDirect.c][200] Entry: Statement = 0x866cbd8 SQL = [select extension from SUBSCRIBER where subscriber_type = 0][length = 58 (SQL_NTS)] here it just stops without any error. thanks in advance. regards Vinay Kumar Software Engineer Prologix Software Solutions Pvt. Ltd. Lucknow-226016.India Ph: +91(522)2721387. Res: 2702453. Fax: +91(522)2722286 E-Mail: [EMAIL PROTECTED] Web: www.prologixsoft.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
