Re: REGEXP with UTF-8
Hi! On Apr 07, Hirofumi Fujiwara wrote: From: Sergei Golubchik [EMAIL PROTECTED] Hi! On Apr 06, Hirofumi Fujiwara wrote: I am testing regular expression feature of MYSQL 4.1.1-alpha (REGEXP) with UTF-8 characters (Japanese). So far it doesn't seem to work. LIKE handles each Japanese character properly as one character, but REGEXP doesn't. I'd like to know if REGEXP handles UTF-8 characters. No it does not :( (we need to change regexp library that we use, to have it fixed) I wonder if REGEXP doesn't handle multi bytes character(big5,ujis, sjis,euckr,gb2312,gbk,utf8,ucs2) either, which means it handles only single byte character set (Maxlen=1). Unfortunately, it does not either :( Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using BYTEFX to connect to MySql , closing connection do not release the connection.
Hi, Encounter an interesting here using VB.NET with ByteFx.mysql.mysqlclient to connect to Mysql Database. I have a sub-form (called from another main form) with 4 mysqlconnections declared and new. X1= new mysqlconnection X1.connectionstring = a valid connection string X1.open() X2= new mysqlconnection X2.connectionstring = a valid connection string X2.open() X3= new mysqlconnection X3.connectionstring = a valid connection string X3.open() X4= new mysqlconnection X4.connectionstring = a valid connection string X4.open() Msgbox(Phase 1) X1.close() X2.close() X3.close() X4.close() Msgbox (Phase 2) I am using MySQL Administrator to monitor the connection , at the Phase 1 break point I can see that 4 connection is allocated. And at Phase 2 , I expect the connection will be closed, but to my surprise, it did not. Not until I quit entire application. Then I see the connections are release. Anyone has encounter this before and any pointers for me to investigate further? Regards TweeWan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database recovery
Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? The technical director is away and I am in charge of this recovery... I dont want to get it wrong, so *any* help is greatly appreciated. Andy Hall.
Re: Database recovery
Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? Each table in MySQL is represented by a MYD, MYI, and frm file. These are collected in directories, each of which represents a database. So copy the files, along with the directories they were in (which represent the databases), except for the mysql database, into the MySQL data directory. For example, if you used the RPM install, this directory will be /var/lib/mysql, or wherever you extracted the binary install to if you've used the binary installation. Then restart MySQL, and it will pick up the databases. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
Andy Hall [EMAIL PROTECTED] wrote on 07/04/2004 09:50:19: Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? It would help for this sort of question if you said what OS you are using: the answer will vary with OS. Normally, you copy files from. to the mysql home directory/data/database name directory: on most Windows installations, this will be c:\mysql\data\database name. If you cannot find this, searching for files with the extension .MYI on the old disk might well find them for you. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? Each table in MySQL is represented by a MYD, MYI, and frm file. These are collected in directories, each of which represents a database. So copy the files, along with the directories they were in (which represent the databases), except for the mysql database, into the MySQL data directory. For example, if you used the RPM install, this directory will be /var/lib/mysql, or wherever you extracted the binary install to if you've used the binary installation. Then restart MySQL, and it will pick up the databases. Hi, Thanks for the info. I now have all the frm MYD and MYI files under a new directory within MySQL. How do I restart the service manually? Would it be: /etc/rc.d/init.d/mysql restart I am using Apache web server on a Cobalt RAQ. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database recovery
Hi, I have recently had a system crash that required the installation of a new hard drive. I have access to the files on the old hard drive, on which is a database I need to recover. I am running MySql 3.23.37 and all the tables in the database to be recovered are MyISAM. How can I recover the old database onto a new server with only file access? The documentation suggests to me that I need to copy all the *.frm, *.MYD, and *.MYI files. If this is the case, where should I copy them to on the new server? Each table in MySQL is represented by a MYD, MYI, and frm file. These are collected in directories, each of which represents a database. So copy the files, along with the directories they were in (which represent the databases), except for the mysql database, into the MySQL data directory. For example, if you used the RPM install, this directory will be /var/lib/mysql, or wherever you extracted the binary install to if you've used the binary installation. Then restart MySQL, and it will pick up the databases. Hi, Thanks for the info. I now have all the frm MYD and MYI files under a new directory within MySQL. How do I restart the service manually? Would it be: /etc/rc.d/init.d/mysql restart I am using Apache web server on a Cobalt RAQ. Thanks No matter, its all done. Thank you very much for your help. Regards Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Berkeley DB and MySQL
Victor Medina [EMAIL PROTECTED] wrote: When compiling mysql(4.0, 4.1 or 5) can I use a external Berkeley db source, and not the one provided with mysql source? I know that the configure script provides a --with-berkeley-db-includes and -libs, but.. up to what version of bdb can i use? Can I use the latest? BDB that comes with MySQL distribution has some patches to work with MySQL. So you should use patched version of BDB. -- 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: Need help creating table...
That did it. I really do appreciate all of the help that I received from you and the list. Thanks again. :) -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 7:52 PM To: Marvin Cummings Cc: [EMAIL PROTECTED] Subject: Re: Need help creating table... Sorry. I can imagine how frustrated you must be. In mysql 5, condition is a reserved word, so the person who told you to remove the quotes was wrong, at least for that column. (In his defense, CONDITION is relevant to stored procedures, so it isn't a reserved word for versions prior to 5, as far as I know.) You will need to put bacticks around the column name, like this `condition` varchar(10) NOT NULL default '', Michael Marvin Cummings wrote: Thanks for the responses but after copying and pasting this into the mysql command prompt I now get the following error: Error 1064 (42000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL version for the right syntax to use near condition varchar(10) NOT NULL default '', City varchar(20) NOT NULL default '', at line 8 I copy this exactly like it is: CREATE TABLE nuke_zc_ads ( ad_id smallint(5) unsigned NOT NULL auto_increment, cat_id smallint(5) unsigned NOT NULL default 0, user_name varchar(20) NOT NULL default '', email varchar(50) NOT NULL default '', price text NOT NULL, condition varchar(10) NOT NULL default '', city varchar(20) NOT NULL default '', state varchar(20) NOT NULL default '', country varchar(20) NOT NULL default '', lastup_date int(11) NOT NULL default 0, subject text NOT NULL, descript text NOT NULL, url text NOT NULL, views int(11) NOT NULL default 0, paypal char(3) NOT NULL default 'No', add_date int(11) NOT NULL default 0, exp_date int(11) NOT NULL default 0, PRIMARY KEY (ad_id) ) TYPE=MyISAM AUTO_INCREMENT=1 ; And it still isn't working!!! WHAT am I doing wrong here? Thanks again for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Linux can't auto flush-tables
TTBMK, there is no such thing as 'auto flush' in MySQL. When you say Everytime I add some record on my local computer then upload ... are you saying you upload the raw database files (from your data directory), or are you inserting/updating the data in your Linux databases with a query? If you are uploading the raw files, that is not a good practice. If you are updating/inserting it with a query, you shouldn't need to flush tables anyway. G. Jensen - Original Message - From: starofframe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 06, 2004 8:23 PM Subject: Mysql Linux can't auto flush-tables Hi, I have problem with Mysql 3.23.58 on Linux Mysql can't auto Flush-Tables I work with Mysql 3.23.58 windows version on my local computer... Then I upload to web-hosting that working with Mysql 3.23.58 on Linux.. Everytime I add some record on my local computer then upload to web-hosting... The table can't auto flush-tables... I have to ssh to server then type in mysql/bin - mysqladmin -u root -p flush-tables Then the table refresh... So anyone know how to configure Mysql to auto run flush-tables?? Thanx Nicholas Kho -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join problem: indexed columns being ignored
Afternoon all. I wonder if any experts on the join optimiser could help me out. I have read the docs, including the material on how mysql optimises joins, and have redesigned my schema somewhat to try and simplify the joins, and that has worked to some extent. But I am still way off the performance I should be getting on my problem queries. My Question: Why don't my queries use the entire index I have created on the spam(t1) table? Is there any way to force mysql to use my index as I intend? What factors influence the choice of index/column in this case? Basics first: I am using MySQL 4.0.18 (mysql.com official build) on a Dell 1750 dual Xeon running slackware 9.1, kernel 2.6.3, loads of memory, loads of disk space. My my.cnf is pasted in at the end. All tables in this query are innodb. My problem: I have 2 problem queries which involve a join on 3 tables. I have created an index on each table, and all columns required for the join are indexed. All boolean operations are AND. All conditions are x=y or x=const. On 2 of the tables, only 1 column is used - no problem there; on the 'problem' table (spam(t1)) 3 columns are used, and I have created a multi-column index (SP_RID_STA) which includes all 3 relevant columns. The problem: according to 'EXPLAIN' mysql is not using the entire index. I have two sample queries below; the first counts the items in the table, and uses 2/3 columns), and and the second selects the first 10 items (uses 2/3 colums). I am judging the index usage by the 'key_len' value in the explain output. The first column should be '1', the second '8' and the third '1'. I am therefore expecting '10' in the key_len result for both queries. Currently the script which runs these queries times out. I need it really to take of the order of a couple of seconds. I don't see why this should not be possible with the right indexes, judging by what I have been abloe to achieve with similar scripts on the same data. Thanks in advance Jim [EMAIL PROTECTED] Details: spam (t1) contains 5317996 rows recip (t2) conatins 9340685 rows mailin (t3) contains 6464183 rows Query 1: explain SELECT COUNT(*) as NumRecords FROM spam t1, recip t2, mailin t3 WHERE SpamFilter='y' AND t1.RecipID = t2.RecipID AND t2.MailInID = t3.MailInID AND t1.RecipID 34035098 AND t1.Status=present ; +---++-++-+- ++--+ | table | type | possible_keys | key| key_len | ref | rows | Extra| +---++-++-+- ++--+ | t1| range | SP_RID_STA,RecipID | SP_RID_STA | 9 | NULL | 467038 | Using where; Using index | | t2| eq_ref | PRIMARY,MailInID| PRIMARY| 8 | t1.RecipID | 1 | | | t3| eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY| 8 | t2.MailInID | 1 | Using index | +---++-++-+- ++--+ == this query takes about 12 seconds to execute == Query 2: explain SELECT t1.*,t3.* FROM spam t1, recip t2, mailin t3 WHERE SpamFilter='y' AND t1.RecipID = t2.RecipID AND t2.MailInID = t3.MailInID AND t1.Status=present AND t1.RecipID 34035098 ; +---++-++-+- ++-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++-++-+- ++-+ | t1| ref| SP_RID_STA,RecipID | SP_RID_STA | 1 | const | 294033 | Using where | | t2| eq_ref | PRIMARY,MailInID| PRIMARY| 8 | t1.RecipID | 1 | | | t3| eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY| 8 | t2.MailInID | 1 | | +---++-++-+- ++-+ == this query takes 90+ seconds to execute == Schema: CREATE TABLE `spam` ( `SpamID` bigint(20) NOT NULL auto_increment, `RecipID` bigint(20) NOT NULL default '0', `Reason` text, `TermDate` datetime default NULL, `SpamFilter` enum('n','y') NOT NULL default 'n', `ImageFilter` enum('n','y') NOT NULL default 'n', `Status` enum('present','released','deleted') NOT NULL default 'present', PRIMARY KEY (`SpamID`), KEY `ImageFilter` (`ImageFilter`), KEY `SP_RID_STA` (`SpamFilter`,`RecipID`,`Status`), KEY `RecipID` (`RecipID`) ) TYPE=InnoDB CREATE TABLE `recip` ( `RecipID` bigint(20) NOT NULL auto_increment, `Date` datetime NOT NULL default '-00-00 00:00:00', `AccountID` int(11) NOT NULL default '0', `DomainID` int(11) NOT NULL default '0', `EndUserID` int(11) NOT NULL default '-1', `Recipient` text NOT NULL, `MailInID` bigint(20) NOT NULL
Re: Recover registers afected by UPDATE
Francisco Ivan Anton Prieto [EMAIL PROTECTED] wrote: Egor Egorov wrote: Francisco Ivan Anton Prieto [EMAIL PROTECTED] wrote: I am crazy with a problem. I would like to know the best method to SELECT affected rows after an UPDATE. If you set column values to the new (different from current value), you can just use the same WHERE clause in the SELECT statement. UPDATE table SET a=1 WHERE a=0 LIMIT 2; SELECT * FROM table WHERE a=1 -doesn't work because column a have rows with value '1' before UPDATE I have the next idea : query(LOCK TABLES table); // HERE RETRIEVE ROWS I WILL UPDATE result = query(SELECT * FROM table WHERE a=0 LIMIT 2); // HERE I UPDATE ROWS query(UPDATE table SET a=1 WHERE WHERE a=0 LIMIT 2); query(UNLOCK TABLES); This would work fine??? Yup, you can retrieve rows before UPDATE. Is there a better way? I use SELECT...FOR UPDATE and it seems does not work. SELECT .. FOR UPDATE isn't that you are looking for. -- 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]
Joins
Hi, I am only a very occasional mySQL user and I need some help with a join. Our internal helpdesk is done in mySQL and PHP. I have been asked to dump some of the contents of the database into a file for loading into a data warehouse. The following query is getting the data from the workorders (helpdesk call) table and getting some normalised data from other lookup tables. However the ClosedBy field is sometimes NULL (if the call has not been closed). I want to get everything from this table, even if there is no corresponding record in the personnel table. At the moment if workorders.closedby is NULL then i do not get the record from the workorders table. my query is: select workorders.jcn AS WO Number, workorders.seq AS WO Seq, workorders.contact AS Contact, workorders.summary AS Summary, class.name AS Class, severities.nameAS Severity, DATE_FORMAT(workorders.createdon, '%Y-%m-%d') AS WOCreatedOn, CONCAT(personnel.lastname, , , personnel.firstname) as ClosedBy, workorders.closedonAS WOClosedOn, workorders.totalhours AS TotalHours, accounts.name AS Customer, products.name AS CallCatagory, statuses.name AS Status FROM workorders, class, severities, personnel, accounts, products, statuses WHERE class.id = workorders.clid and severities.id = workorders.severity and (workorders.closedby = personnel.id OR workorders.closedby IS NULL) and accounts.id = workorders.account and products.id = workorders.product and statuses.id = workorders.status ORDER BY workorders.jcn; Can anyone help please? TIA, Ciaran. Ciarán O'Neill IT Department, United Drug Plc, Magna Business Park, Citywest Road, Dublin 24. * +353 1 463 2535 Fax +353 1 463 2397 * www.united-drug.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using BYTEFX to connect to MySql , closing connection do not release the connection.
More information to add : Am using Bytefx 0.76 : I simplify the testing by using frmA call frmB. In frmB, I do the following : Private Sub frmB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Try xx = New MySqlConnection xx.ConnectionString = Data Source=127.0.01; _ Database=good; _ User ID=root; _ Password=; xx.Open() xx.Close() xx.ConnectionString = xx.Dispose() MsgBox(Closed) Catch ex As Exception MsgBox(Err.Number ex.Message) End Try End Sub In frmA, I have this tied to a button Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim frm As frm_single frm = New frm_single frm.Show() End Sub I press button1 in frmA, when frmB shows up, I close frmB, I press button1 again in frmA and I get the following error: Pooling exception: Unable to find original pool for connection Regards TweeWan -Original Message- From: tweewan.wong [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:02 To: [EMAIL PROTECTED] Subject: Using BYTEFX to connect to MySql , closing connection do not release the connection. Hi, Encounter an interesting here using VB.NET with ByteFx.mysql.mysqlclient to connect to Mysql Database. I have a sub-form (called from another main form) with 4 mysqlconnections declared and new. X1= new mysqlconnection X1.connectionstring = a valid connection string X1.open() X2= new mysqlconnection X2.connectionstring = a valid connection string X2.open() X3= new mysqlconnection X3.connectionstring = a valid connection string X3.open() X4= new mysqlconnection X4.connectionstring = a valid connection string X4.open() Msgbox(Phase 1) X1.close() X2.close() X3.close() X4.close() Msgbox (Phase 2) I am using MySQL Administrator to monitor the connection , at the Phase 1 break point I can see that 4 connection is allocated. And at Phase 2 , I expect the connection will be closed, but to my surprise, it did not. Not until I quit entire application. Then I see the connections are release. Anyone has encounter this before and any pointers for me to investigate further? Regards TweeWan -- 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]
Joins - extra info
By the way, we're using Version 3.23.49 on linux Ciaran. Ciarán O'Neill IT Department, United Drug Plc, Magna Business Park, Citywest Road, Dublin 24. * +353 1 463 2535 Fax +353 1 463 2397 * www.united-drug.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins
* O'Neill, Ciaran [...] I want to get everything from this table, even if there is no corresponding record in the personnel table. This is a typical task for a LEFT JOIN, try something like this: select workorders.jcn AS WO Number, workorders.seq AS WO Seq, workorders.contact AS Contact, workorders.summary AS Summary, class.name AS Class, severities.nameAS Severity, DATE_FORMAT(workorders.createdon, '%Y-%m-%d') AS WOCreatedOn, CONCAT(personnel.lastname, , , personnel.firstname) as ClosedBy, workorders.closedonAS WOClosedOn, workorders.totalhours AS TotalHours, accounts.name AS Customer, products.name AS CallCatagory, statuses.name AS Status FROM workorders, class, severities, accounts, products, statuses LEFT JOIN personnel ON personnel.id = workorders.closedby WHERE class.id = workorders.clid and severities.id = workorders.severity and accounts.id = workorders.account and products.id = workorders.product and statuses.id = workorders.status ORDER BY workorders.jcn; URL: http://www.mysql.com/doc/en/JOIN.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joins
I think the problem is here: and (workorders.closedby = personnel.id OR workorders.closedby IS NULL) There is no way for a personnel record to be selected if the closedby value is NULL, so no record gets selected. The simplest way to fix this is to add a 'null' personnel record with personnel.id set to NULL. Then remove the 'OR' phrase, and it should work, the null record being selected from personnel when closedby is NULL. Jim Imagine a service that checks your business email for viruses, junk-mail and pornographic content BEFORE it reaches you. Imagine a service that allows you to build custom rules for content management. Imagine a service that does all this without having to make a single change to your computer system. EMF Enterprise for businesses is a service that offers all of this To make your imagination a reality go to www.emf-systems.com (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Joins
That worked perfectly. I've never had much luck with joins! Thanks. Ciaran. -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 12:52 To: [EMAIL PROTECTED] Cc: O'Neill, Ciaran Subject: Re: Joins * O'Neill, Ciaran [...] I want to get everything from this table, even if there is no corresponding record in the personnel table. This is a typical task for a LEFT JOIN, try something like this: select workorders.jcn AS WO Number, workorders.seq AS WO Seq, workorders.contact AS Contact, workorders.summary AS Summary, class.name AS Class, severities.nameAS Severity, DATE_FORMAT(workorders.createdon, '%Y-%m-%d') AS WOCreatedOn, CONCAT(personnel.lastname, , , personnel.firstname) as ClosedBy, workorders.closedonAS WOClosedOn, workorders.totalhours AS TotalHours, accounts.name AS Customer, products.name AS CallCatagory, statuses.name AS Status FROM workorders, class, severities, accounts, products, statuses LEFT JOIN personnel ON personnel.id = workorders.closedby WHERE class.id = workorders.clid and severities.id = workorders.severity and accounts.id = workorders.account and products.id = workorders.product and statuses.id = workorders.status ORDER BY workorders.jcn; URL: http://www.mysql.com/doc/en/JOIN.html -- Roger ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Linux
I ran into the same issues on RH8, with a default implmentation. It can be overcome, but the mysql failed to write to the table after 2gb or so. It turned out to be a filesystem limitation issue, which was fixable. I am not sure, but given the size of files nowadays, RH9 defaults probably take care of it. I am currently running several very large tables on RH8 (5-30G) and it is stable. One should always beware that large tables can easily be corrupted, and are not a joy to recover though :-/ P Alan Williamson [EMAIL PROTECTED] 04/06/2004 05:57 PM To: Dan Nelson [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: MySQL on Linux Thank you, a much reasoned and sensible reply. This is information people can use, as oppose to the posts that 'say well its okay for me, you must be stupid' types. ;) Dan Nelson wrote: In the last episode (Apr 06), Alan Williamson said: the most popular would have been Red Hat, which doesn't have this limit you speak of, even plain vanilla install (no twiddling needed). Not to spoil a perfectly good pontification ... but i have to say that we have a Redhat8 distribution running on a Dell PowerEdge Server and when Apache gets to the 2GB size on its access file, it does indeed stop. This is not old hardware (12months old). That is because although Linux binaries can access files over 2gb, they do not do so by default. Apache was probably not compiled with the required defines (-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64), so that's why it stops at 2gb even though both the kernel and filesystem most likely do support larger files. So the question still remains. What would happen in MySQL when that file isn't allowed to grow any further? Mysql's configure script checks for systems that require special flags to access large files, so no mysql binaries should have this problem on modern Linux systems (i.e. any 2.4 kernel) -- 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]
stuck with simple query..... Plz have a look
We have two tables Table1: - ID |Name 1 |name1 2 |name2 3 |name3 4 |name4 Table2: --- PL | PC |PA| Description --- 1| 2 | 4| Some description for Project 1 2| 3 | 1| Some description for Project 2 1| 2 | 4| Some description for Project 3 4| 1 | 3| Some description for Project 4 3| 1 | 4| Some description for Project 5 2| 3 | 1| Some description for Project 6 I need the output like.. - PL-Name| PC-Name| PA-Name - name1 | name2 |name4 name2 | name3 |name1 name1 | name2 |name4 name4 | name1 |name3 name3 | name1 |name4 name2 | name3 |name1 Please guide me how can i achieve this kind of result set. Regards, Tariq
stuck with simple query..... Plz have a look
SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; Jim (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stuck with simple query..... Plz have a look
Looks like I trod on original post - forgot to add RE: to subject. Sorry about that! Jim (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stuck with simple query..... Plz have a look
SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; Should work Jim (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stuck with simple query..... Plz have a look
Jim Page - EMF Systems Ltd wrote: SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; Should work Jim (This email has been scanned for viruses by www.emf-systems.com) I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 2013 at line 7: Lost connection to MySQL server during query
Hello, MySQL gurus, I have been trying to output the result of some large query to a temporary table and then query from there. But all my trials ended up 'ERROR 2013 at line 7: Lost connection to MySQL server during query' before creating the temporary table. The version of MySQL is 3.23.54, for redhat-linux-gnu (i386). Does anyone have any idea of how to prevent this from happening? Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database design question
Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something.
Re: ERROR 2013 at line 7: Lost connection to MySQL server during query
What is your max packet size? Are you able to determine where the `error` occurs in the process? During the load of the temporary table? During the query of the temp table? On Wed, 07 Apr 2004, Ginger Cheng wrote: Hello, MySQL gurus, I have been trying to output the result of some large query to a temporary table and then query from there. But all my trials ended up 'ERROR 2013 at line 7: Lost connection to MySQL server during query' before creating the temporary table. The version of MySQL is 3.23.54, for redhat-linux-gnu (i386). Does anyone have any idea of how to prevent this from happening? Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on Linux
Well I guess it depends on what is meant by Default. I was using RH8 and had no such issues, even with RH7... I think it's important to note the filesystem chosen can make a huge difference. as of RH8 and onward I'd suggest EXT3, especially for Peter's issue of possible corruption - because it is a fully journalling filesystem. of course, a filesystem cannot (and will not) overcome issues with the software or OS itself. It's always best to use the latest stable Mysql (4.0.18 as of last I checked), and don't always upgrade just because. too many admins will upgrade just because the latest thing is out, if it's working, stable, etc, leave it alone. don't fix it if it aint broke. the point is, choosing the right options during install. myself, I never had an issue with the defaults, even back as far as RH7, using files larger than 2gigs with mysql. perhaps because I always choose the best file system available at the time. I chose journalling as soon as it was available, things like ReiserFS come to mind. for me, large files was always the default, never had to choose it manually or change any settings. oh well... Dan. On Wed, 7 Apr 2004, Peter J Milanese wrote: I ran into the same issues on RH8, with a default implmentation. It can be overcome, but the mysql failed to write to the table after 2gb or so. It turned out to be a filesystem limitation issue, which was fixable. I am not sure, but given the size of files nowadays, RH9 defaults probably take care of it. I am currently running several very large tables on RH8 (5-30G) and it is stable. One should always beware that large tables can easily be corrupted, and are not a joy to recover though :-/ P Alan Williamson [EMAIL PROTECTED] 04/06/2004 05:57 PM To: Dan Nelson [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: MySQL on Linux Thank you, a much reasoned and sensible reply. This is information people can use, as oppose to the posts that 'say well its okay for me, you must be stupid' types. ;) Dan Nelson wrote: In the last episode (Apr 06), Alan Williamson said: the most popular would have been Red Hat, which doesn't have this limit you speak of, even plain vanilla install (no twiddling needed). Not to spoil a perfectly good pontification ... but i have to say that we have a Redhat8 distribution running on a Dell PowerEdge Server and when Apache gets to the 2GB size on its access file, it does indeed stop. This is not old hardware (12months old). That is because although Linux binaries can access files over 2gb, they do not do so by default. Apache was probably not compiled with the required defines (-D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64), so that's why it stops at 2gb even though both the kernel and filesystem most likely do support larger files. So the question still remains. What would happen in MySQL when that file isn't allowed to grow any further? Mysql's configure script checks for systems that require special flags to access large files, so no mysql binaries should have this problem on modern Linux systems (i.e. any 2.4 kernel) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join problem: indexed columns being ignored
Thanks for the suggestion, but according to explain we are in worse shape than before. In both cases the multi-column index is ignored. I am going to try fiddling with the index col order to see if this helps. Here is what it comes up with as you suggested: Query1: explain SELECT COUNT(*) as NumRecords FROM spam t1 INNER JOIN recip t2 ON t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t3.MailInID WHERE t1.RecipID 34035098 AND t1.Status=present; +---++---+-+-++- +--+ | table | type | possible_keys | key | key_len | ref| rows | Extra| +---++---+-+-++- +--+ | t1| range | RecipID | RecipID | 8 | NULL | 488474 | Using where | | t2| eq_ref | PRIMARY | PRIMARY | 8 | t1.RecipID | 1 | Using index | | t3| index | NULL | PRIMARY | 8 | NULL | 5572118 | Using where; Using index | +---++---+-+-++- +--+ Not sure if the t1 result is better or worse but the t3 line is bad news. Query2: explain SELECT t1.*, t3.* FROM spam t1 INNER JOIN recip t2 ON t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t2.MailInID = t3.MailInID WHERE t1.Status=present AND t1.RecipID 34035098; +---++-+-+-+ -++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++-+-+-+ -++-+ | t1| range | RecipID | RecipID | 8 | NULL | 402604 | Using where | | t2| eq_ref | PRIMARY,MailInID| PRIMARY | 8 | t1.RecipID | 1 | | | t3| eq_ref | PRIMARY,MID_AID,MID_DID | PRIMARY | 8 | t2.MailInID | 1 | | +---++-+-+-+ -++-+ Sort of about the same. Did you try: SELECT COUNT(*) as NumRecords FROM spam t1 INNER JOIN recip t2 0N t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t3.MailInID WHERE t1.RecipID 34035098 AND t1.Status=present; re-written as: SELECT t1.*, t3.* FROM spam t1 INNER JOIN recip t2 ON t1.RecipID = t2.RecipID INNER JOIN mailin t3 ON t2.MailInID = t3.MailInID WHERE t1.Status=present AND t1.RecipID 34035098; (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
JOHN MEYER [EMAIL PROTECTED] wrote on 07/04/2004 15:39:10: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. You need to provide more information. For example, do prices differ with different wax types? Are you expecting some sort of validation that candle type A can be manufactured in wax type B? The easiest answer is just to have an extra column waxtype and have the ordering application only allow valid settings to be put in it, allowing NULL in the case where there is no choice of waxes. You seem to have discarded this option - but you haven't told us why, so we cannot tell what alternative to suggest. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013 at line 7: Lost connection to MySQL server during query
Hi, The max packet site is 1048576. But I don't think it is a problem cuz I have successfully outputted the result of this query before. I checked mysqld.log. This is the tail I think might related to the problem: Number of processes running now: 0 040406 22:22:01 mysqld restarted Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add to the [mysqld] section, for example, innodb_data_file_path = ibdata1:10M:autoextend But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 2 at http://www.innodb.com/ibman.html Does that mean it tried to create the tmp table as an innoDB table instead of MyISAM and I need to get innodb_data_file_path set to correct it? Thank you for help ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: stuck with simple query..... Plz have a look
Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 on the Mac?
James McConnell wrote: Hey all. I had asked a few days ago if anyone had experience installing 4.1 on the Mac and got no reply, but that's cool :-). Has anyone installed 5.0 on the Mac? Any problems? I'd like to try it, but I thought I'd ask and see if there was anything I needed to know before I tried installing it. Thanks, all! James McConnell HI! if you mean os/max X, it comes bundled with it, you can even download a binary from the mysql site =) Best Regards! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql error file
I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2013 at line 7: Lost connection to MySQL server during query
I think this is the problem (cited from mysqld.log) Some pointers may be invalid and cause the dump to abort... followed by my query to create the tmp table But I am not sure how to fix it. ginger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using BYTEFX to connect to MySql , closing connection do not release the connection.
I'm not sure why this is happening, but the behavior you described in the first email is correct pooling behavior. By default, connection pooling is enabled which means connections are not killed when they are closed but remain open and able to serve new connections if necessary. Only when the app quits is the pool manager collected and the connections killed. If this is not desired, you can add pooling=false to your connection string. -reggie -Original Message- From: tweewan.wong [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:44 AM To: [EMAIL PROTECTED] Subject: RE: Using BYTEFX to connect to MySql , closing connection do not release the connection. More information to add : Am using Bytefx 0.76 : I simplify the testing by using frmA call frmB. In frmB, I do the following : Private Sub frmB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Try xx = New MySqlConnection xx.ConnectionString = Data Source=127.0.01; _ Database=good; _ User ID=root; _ Password=; xx.Open() xx.Close() xx.ConnectionString = xx.Dispose() MsgBox(Closed) Catch ex As Exception MsgBox(Err.Number ex.Message) End Try End Sub In frmA, I have this tied to a button Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim frm As frm_single frm = New frm_single frm.Show() End Sub I press button1 in frmA, when frmB shows up, I close frmB, I press button1 again in frmA and I get the following error: Pooling exception: Unable to find original pool for connection Regards TweeWan -Original Message- From: tweewan.wong [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:02 To: [EMAIL PROTECTED] Subject: Using BYTEFX to connect to MySql , closing connection do not release the connection. Hi, Encounter an interesting here using VB.NET with ByteFx.mysql.mysqlclient to connect to Mysql Database. I have a sub-form (called from another main form) with 4 mysqlconnections declared and new. X1= new mysqlconnection X1.connectionstring = a valid connection string X1.open() X2= new mysqlconnection X2.connectionstring = a valid connection string X2.open() X3= new mysqlconnection X3.connectionstring = a valid connection string X3.open() X4= new mysqlconnection X4.connectionstring = a valid connection string X4.open() Msgbox(Phase 1) X1.close() X2.close() X3.close() X4.close() Msgbox (Phase 2) I am using MySQL Administrator to monitor the connection , at the Phase 1 break point I can see that 4 connection is allocated. And at Phase 2 , I expect the connection will be closed, but to my surprise, it did not. Not until I quit entire application. Then I see the connections are release. Anyone has encounter this before and any pointers for me to investigate further? Regards TweeWan -- 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: stuck with simple query..... Plz have a look
You are correct Jim.. This is certainly not Cartesian. Jim Page - EMF Systems Ltd [EMAIL PROTECTED] 04/07/2004 10:09 AM Please respond to Jim Page - EMF Systems Ltd To: gerald_clark [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. (This email has been scanned for viruses by www.emf-systems.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: stuck with simple query..... Plz have a look
I would like to ask a question here, just for my own knowledge. What is actually the difference between the statement below and this one? SELECT Table1-1.Name AS 'PL', Table1-2.Name AS 'PC', Table1-3.Name AS 'PA' FROM Table2 LEFT JOIN Table1 Table1-1 ON Table1.ID = Table1-1.PL LEFT JOIN Table1 Table1-2 ON Table1.ID = Table1-2.PC LEFT JOIN Table1 Table1-3 ON Table1.ID = Table1-3.PA -Original Message- From: Jim Page - EMF Systems Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:09 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stuck with simple query..... Plz have a look
Wait, I see it now :) -Original Message- From: Luc Foisy Sent: Wednesday, April 07, 2004 10:45 AM To: [EMAIL PROTECTED] Subject: RE: stuck with simple query. Plz have a look I would like to ask a question here, just for my own knowledge. What is actually the difference between the statement below and this one? SELECT Table1-1.Name AS 'PL', Table1-2.Name AS 'PC', Table1-3.Name AS 'PA' FROM Table2 LEFT JOIN Table1 Table1-1 ON Table1.ID = Table1-1.PL LEFT JOIN Table1 Table1-2 ON Table1.ID = Table1-2.PC LEFT JOIN Table1 Table1-3 ON Table1.ID = Table1-3.PA -Original Message- From: Jim Page - EMF Systems Ltd [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:09 AM To: gerald_clark Cc: [EMAIL PROTECTED] Subject: Re: stuck with simple query. Plz have a look Forgive me, but it would be a cartesian product if there were no where condition, I agree? It would return (size Table1)x(size Table2)^3 rows, definitely not what Tariq wants. The query I supplied will return (size Table2)x(1)^3 rows won't it? Or am I missing the point? Jim SELECT ta.Name,tb.Name,tc.Name,Description FROM Table2,Table1 ta,Table1 tb,Table1 tc WHERE ta.ID=PL AND tb.ID=PC AND tc.ID=PA; I t may work, but it may consume all ram and disk space in an attempt. It may take days. This is a 4 table cartesian product. There are no join criteria in the WHERE clause. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 on the Mac?
Not the 5.0!!! Santino At 10:02 -0400 7-04-2004, Victor Medina wrote: James McConnell wrote: Hey all. I had asked a few days ago if anyone had experience installing 4.1 on the Mac and got no reply, but that's cool :-). Has anyone installed 5.0 on the Mac? Any problems? I'd like to try it, but I thought I'd ask and see if there was anything I needed to know before I tried installing it. Thanks, all! James McConnell HI! if you mean os/max X, it comes bundled with it, you can even download a binary from the mysql site =) Best Regards! -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql error file
At 10:10 -0400 4/7/04, Luc Foisy wrote: I have the following in my /etc/my.cnf [mysqld] datadir=/usr/data/mysql pid-file=/usr/data/mysql/mysql.pid socket=/usr/data/mysql/mysql.sock user=mysql [mysql_safe] log-error=/usr/data/mysql/mysql.err [client] socket=/usr/data/mysql/mysql.sock I tried originally to put the log-error= in the [mysqld] section and it errored out, believe I read somewhere then that it should be in the [mysql_safe] section My log file is still writing to $HOSTNAME.err Is me /etc/my.cnf file wrong in some way? You might want to use [mysqld_safe] rather than [mysql_safe]. -- 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]
Re: Stored Procedure in mysql 5.0 failure!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 NanFei Wang wrote: Hi, somebody can help me! my database is: mysql-5.0.0a-alpha the connection driver is:mysql-connector-java-3.1.1-alpha-bin.jar I want to test stored procedure of the new feature in MySQL version 5.0. A I can call the stored procedure procPara in Window Console as next show, but when I run in procTest.java,I get error Messages. The error is at the line: CallableStatement cs=conn.prepareCall({call procPara(?)}); between checkpoint 1 and checkpoint 2 your answer is highly appreciated! from: NanFei - mysql delimiter // mysql mysql CREATE PROCEDURE procPara(IN name varchar(16)) - BEGIN -SELECT note FROM kmdoc where username=name; - END - // Query OK, 0 rows affected (0.22 sec) mysql call procPara(John)// +--+ | note | +--+ | mysql Manul | | Office2000 | | PDF| | PowerPoint Animation Runtime | | Office2003 | | Test Title| | Say Hello | +-+ 7 rows in set (1.16 sec) Query OK, 0 rows affected (1.67 sec) mysql - -- procTest.java as following: package km; import java.sql.*; public class procTest{ public static void main(String[] args)throws Exception { String driverConnection=jdbc:mysql://localhost/; String catalog=mycatloge; String user=myname; String psw=mypsw; String connDbUserPsw=driverConnection+catalog+ ? user=+user+password=+psw; try { Class.forName(com.mysql.jdbc.Driver); } catch (ClassNotFoundException e) {} Connection conn = DriverManager.getConnection(connDbUserPsw); System.out.println(checkpoint 1); CallableStatement cs=conn.prepareCall({call procPara(?)}); System.out.println(checkpoint 2); cs.setString(1,john); java.sql.ResultSet rst=cs.executeQuery(); while(rst.next()){ String s=rst.getString(1); System.out.println(s); } } } Messages: checkpoint 1 java.lang.StringIndexOutOfBoundsException: String index out of range: -9 at java.lang.String.substring(String.java:1480) at com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.init(DatabaseMetaData.java:7031) at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:6615) at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:2637) at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:904) at com.mysql.jdbc.CallableStatement.init(CallableStatement.java:72) at com.mysql.jdbc.Connection.prepareCall(Connection.java:999) at com.mysql.jdbc.Connection.prepareCall(Connection.java:978) at km.procTest.main(procTest.java:17) Exception in thread main - Use a nightly snapshot of Connector/J 3.1 from http://downloads.mysql.com/snapshots.php -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAdBy8tvXNTca6JD8RAlioAJ0V49MIcbWpMuG1sjQnbGHp1Y7yoQCghoFn HZn4vmYgdTFxMnhNliW9bkM= =wBMx -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 on the Mac?
To clarify: Mac OS X does not come with mysql 5.0 bundled. You can download a mysql 5.0 binary for Mac OS X from the mysql site http://www.mysql.com/downloads/mysql-5.0.html. Michael Santino wrote: Not the 5.0!!! Santino At 10:02 -0400 7-04-2004, Victor Medina wrote: James McConnell wrote: Hey all. I had asked a few days ago if anyone had experience installing 4.1 on the Mac and got no reply, but that's cool :-). Has anyone installed 5.0 on the Mac? Any problems? I'd like to try it, but I thought I'd ask and see if there was anything I needed to know before I tried installing it. Thanks, all! James McConnell HI! if you mean os/max X, it comes bundled with it, you can even download a binary from the mysql site =) Best Regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TIMESTAMP decimal year format available?
Hi, I am using mysql and I need to store timestamp. In input I got something like yyy-dddThh:mm:ss, ddd beeing the day of the year! I would like to make the minimun transformation before loading data in database, so I would like to enter the timestamp with a format handling decimal year yyy.ddd but AFA I understand the mysql doc, it seems not available? Any advice or information would be welcome, thanks in advance, -- Pierre -- DIDELON :@: pdidelon_at_cea.frPhone : 33 (0)1 69 08 58 89 CEA SACLAY - Service d'Astrophysique 91191 Gif-Sur-Yvette Cedex -- PS : I'm using v 4.0.14 on WinXP -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TIMESTAMP decimal year format available?
At 17:34 +0200 4/7/04, Pierre Didelon wrote: Hi, I am using mysql and I need to store timestamp. In input I got something like yyy-dddThh:mm:ss, ddd beeing the day of the year! I would like to make the minimun transformation before loading data in database, so I would like to enter the timestamp with a format handling decimal year yyy.ddd but AFA I understand the mysql doc, it seems not available? You understand correctly. Either transform the values before loading them, or load them into some other kind of column (CHAR?) and perform some kind of transformation using SQL to get them into the correct format. -- 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]
Hey what, no pity for a new user?
A couple days ago, I posted a careful message of my troubles, but no reply to date. Remember, after I tried to assign a password for the root user, it broke. Since then, I discovered that Mac OS X refuses to let me create a user account called 'mysql'. Apparently that account comes preconfigured since the latest version (Panther). But since I had an account by that name before I installed Panther, but then deleted in a misguided attempt to fix my problems, I was in a boat. I created a new dummy account, then used NetInfoManager to rename everything. Then I used Terminal to rename the home directory, and SharePoints to add the new user to the mysql account and delete the dummy group. Then I used Terminal to change the group of all the stuff in the new home directory. So now it has quit complaining about the chown command, but it still quits immediately when I try to run mysqld_safe, as follows: [iMac-dv:/usr/local/mysql] kj% cd /usr/local/mysql [iMac-dv:/usr/local/mysql] kj% sudo bin/mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 040407 09:55:25 mysqld ended [iMac-dv:/usr/local/mysql] kj% I would be happy to just kill it and start over. -- Sincerely, Kevin Jaques (at home) Use [EMAIL PROTECTED] for work related messages Send lawyers, guns and money! Dad get me out of this! - Warren Zevon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hey what, no pity for a new user?
i wouldn't say there is no pity, but there is probably a very low concentration of mysql people running on macos. Just my two cents... dan Kevin Jaques wrote: A couple days ago, I posted a careful message of my troubles, but no reply to date. Remember, after I tried to assign a password for the root user, it broke. Since then, I discovered that Mac OS X refuses to let me create a user account called 'mysql'. Apparently that account comes preconfigured since the latest version (Panther). But since I had an account by that name before I installed Panther, but then deleted in a misguided attempt to fix my problems, I was in a boat. I created a new dummy account, then used NetInfoManager to rename everything. Then I used Terminal to rename the home directory, and SharePoints to add the new user to the mysql account and delete the dummy group. Then I used Terminal to change the group of all the stuff in the new home directory. So now it has quit complaining about the chown command, but it still quits immediately when I try to run mysqld_safe, as follows: [iMac-dv:/usr/local/mysql] kj% cd /usr/local/mysql [iMac-dv:/usr/local/mysql] kj% sudo bin/mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 040407 09:55:25 mysqld ended [iMac-dv:/usr/local/mysql] kj% I would be happy to just kill it and start over. -- Sincerely, Kevin Jaques (at home) Use [EMAIL PROTECTED] for work related messages Send lawyers, guns and money! Dad get me out of this! - Warren Zevon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
compatible tools
Dear users IS there a list of tools that work with mysql 4.1 which includes the spatial extension ? Many Thanks Saurabh Data ___ Saurabh Data School of Computing University of Leeds Leeds LS2 9JT U.K. one who seeketh , will findeth and all door shall open - james Allen ___ _ Express yourself with cool emoticons - download MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database design question
Everything I've read about creating online stores is that you are selling inventory items, not the items that makeup the inventory item. So if you sell a red candle made from wax X, candle is the product and red wax X are two attributes of the product. Ideally your structure would work for any product, which means no columns like waxtype or color. Off the top of my head I'm thinking a product table with things like name, description, price, etc. An attribute table. And a productattribute table to link the two. Your attribute table could be a multipurpose table from which you could group your attributes into categories (i.e. wax type, colors, etc.). Something like this: IDPrime IDCategory AttrName AttrDesc The IDCategory field would be a relation to the IDPrime field (a self join). If the IDCategory field is '0', you know it's a top level category. This gives you the ability to create a hierarchy with unlimited depth. The data might look like this: 1 0 Candles Burns bright for hours 2 1 Shape Shapes of candles 3 2 Round 4 2 Square 5 2 Pyramid 6 1 Color Colors available 7 6 Red 8 6 Blue 9 6 Gold 10 1 Wax Type 11 10 X 12 10 Y 13 10 Z You can add as many attributes as you like without having to ever change your database structure. So if they are buying a Candle, you search the attributes for Candles and get Shape, Color and Wax type (scent, logo, etc). The user then picks each attribute. If you code it right, you wouldn't even have to change you code when you add attributes and categories. You then store the user selection in the productattribute table. The Product table would be pre-populated with the products you offer, including their links to the attributes. But the design is flexible enough to allow a user to create a custom product or customize and existing one. The shopping cart then only contains the link to the product table. Obviously there is a lot more too creating the whole system, but hopefully this gives you some ideas. On Apr 7, 2004, at 10:39 AM, JOHN MEYER wrote: Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- 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: Hey what, no pity for a new user?
I know nothing about Macs specifically. However under linux ... My first question would be 'what's in the error log'? Often in /var/lib/mysql/host.err Next, have you run the mysql_install_db script to create default databases and permissions? Next, have you checked that use mysql has permission to access the data directory and socket directory? Jim PS I am answering loads of questions in the hope that the power of my Karma will force someone to answer mine ... (join problem:indexed columns being ignored) (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hey what, no pity for a new user?
At 9:58 -0600 4/7/04, Kevin Jaques wrote: A couple days ago, I posted a careful message of my troubles, but no reply to date. Remember, after I tried to assign a password for the root user, it broke. Since then, I discovered that Mac OS X refuses to let me create a user account called 'mysql'. Apparently that account comes preconfigured since the latest version (Panther). But since I had an account by that name before I installed Panther, but then deleted in a misguided attempt to fix my problems, I was in a boat. I created a new dummy account, then used NetInfoManager to rename everything. Then I used Terminal to rename the home directory, and SharePoints to add the new user to the mysql account and delete the dummy group. Then I used Terminal to change the group of all the stuff in the new home directory. So now it has quit complaining about the chown command, but it still quits immediately when I try to run mysqld_safe, as follows: [iMac-dv:/usr/local/mysql] kj% cd /usr/local/mysql [iMac-dv:/usr/local/mysql] kj% sudo bin/mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 040407 09:55:25 mysqld ended [iMac-dv:/usr/local/mysql] kj% What does the error log say? (It'll probably be in /usr/local/mysql/data, and should be the file with a suffix of .err) -- 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]
Re: Hey what, no pity for a new user?
Jim Page - EMF Systems Ltd [EMAIL PROTECTED] wrote on 07/04/2004 17:14:57: PS I am answering loads of questions in the hope that the power of my Karma will force someone to answer mine ... (join problem:indexed columns being ignored) My first reaction would be to turn your index round. Something with settings of y and n is going to make a rotten index, especially as the first part. It is possible that MySQL will look at that part of it, decide that this is a lousy index, and ditch it. If you put the diffuse part of the index first, then the boolean, it might sort better. I don't think you have to change your query - I think MySQL is savvy enough to commute over the AND. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hey what, no pity for a new user?
Many thanks Alec. I need to wait a couple of hours before I can block access to the DB by messing with the indexes, but I will try out what you suggest. What you say makes sense as probably over 99% of the values in that first col will be 'y'. I will post my progress. I am also looking into recoding the where clause as inner joins ... I didn;t expect this to make any difference as I would have thought mysql figures this out but it _seems_ to cut down the query time to 2-3 secs without using any extra index columns ... bizarre. But I'll take what I can get. Answering questions definitely gets answers ... there is a pithy aphorism in there somewhere ... :) Jim PS I am answering loads of questions in the hope that the power of my Karma will force someone to answer mine ... (join problem:indexed columns being ignored) My first reaction would be to turn your index round. Something with settings of y and n is going to make a rotten index, especially as the first part. It is possible that MySQL will look at that part of it, decide that this is a lousy index, and ditch it. If you put the diffuse part of the index first, then the boolean, it might sort better. I don't think you have to change your query - I think MySQL is savvy enough to commute over the AND. (This email has been scanned for viruses by www.emf-systems.com) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table lock problem on INSERT with FULLTEXT index?
Hi there, I've got a bizarre problem I can't seem to solve. I have a small MyISAM table (6533 rows) with a small FULLTEXT index (3 columns per row, an average of 1 word per column). When I do an INSERT on the table, many times the thread gets stuck perpetually in Query | update. Future reads from other threads, of course, stay Locked. When I try to kill the thread using mysqladmin, the thread sticks around for thousands of seconds in Killed | update until I finally just have to kill mysqld manually and let it restart. I've tried REPAIR, ANALYZE, and OPTIMIZE on the table, both using mysql and myisamchk. Tried all the options, such as extended and force and the like. I've even tried wrapping the INSERT with LOCK TABLE table WRITE and UNLOCK TABLES. Still no dice, the INSERT still hangs sometimes. I haven't bothered trying INSERT DELAYED since LOCK TABLE seems more drastic anyway and it didn't work. This happens both on 4.0.17 and 4.0.18. This is a RHEL3 WS dual AMD64 box w/8GB of RAM. Strangely enough, I have another MyISAM table with 1,285,742 rows and a larger FULLTEXT index, and it never locks this way. I have a third MyISAM table with 61,834 rows and a larger FULLTEXT index that locked on me once like this last night, but then I ran through and updated nearly every row overnight, and inserted a few hundred new ones, and it didn't lock at all. FWIW, it only seems to hang on INSERT, not on UPDATE. I haven't tried DELETE yet. I'm stumped. Any help would be greatly appreciated! Thanks, Don -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hey what, no pity for a new user?
I'm not sure if this is part of your problem or not Kevin, seems you have a few issues, so I'll cover them one at at time... getting the permissions and ownership correct, this is cut/paste from the INSTALL docs, pretty much cut/pasteable to your sytem (errr): groupadd mysql useradd -g mysql mysql cd {wherever mysql is installed} scripts/mysql_install_db chown -R root . chown -R mysql data chgrp -R mysql . run mysql with: bin/mysqld_safe --user=mysql setting up the root user access: I found a few times MySQL had issues with setting up the root user, host access. This was in the past, and I've since found it seems ok, but every now and then it bombs out. Never did figure out why. But there is a solution to it. if you read the docs as per installation, it talks about setting up the new install, setting the root user password, and then allowing the host access. here's the root user password setup line: bin/mysqladmin -u root password your_password where your_password is the password you want. and password is the actual word password - so many people get confused about that. and this line: bin/mysqladmin -u root -h `hostname` password password for me, it was always the host access line that failed. but give this a shot. hopefully it helps a bit. Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 joins + aggregate-functions
+--+ | isl_player | +--+ | pla_id | | pla_username | +--+ +--+ | isl_login| +--+ | pla_id | | log_created | +--+ +--+ | isl_transfer | +--+ | pla_id | | tra_amount | +--+ I have 1 database: - islandfever I have 3 tables: - isl_player for every person; - isl_loginfor every login by a person as timestamp; - isl_transfer for every score by person; What I need is: for every person the last login and sum of all scores So I tried a double left join: SELECT islandfever.isl_player.pla_username, max(islandfever.isl_login.log_created) as lastvisit, sum(islandfever.isl_transfer.tra_amount) as score FROM islandfever.isl_player LEFT JOIN islandfever.isl_transfer USING(pla_id) LEFT JOIN islandfever.isl_login USING( pla_id ) GROUP BY isl_player.pla_id; There is a problem with 2 joins in combination with aggreagate-functions (or at least I don't understand them...) QUESTION: What is the approbriate sql-query for my problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database design question
I'm not 100% sure as to what you are trying to do, however, the relationship you describe could, I believe, be modeled as: Candles (candleid, description, price) Waxes (waxid, name/description) Candle_Waxes (candleid, waxid) Thus one table holds the description and price of each candle, another table holds the name of each wax, and a third table connects the two - as a candle can have multiple waxes, the logical way to do this (to me, anyway) is via this third table - glueing the other two together. You'll need to be a bit clever when querying, as simplying joining all three together will bring back multiple rows for candles which contain more than one wax - this could be eliminated by not bringing back the wax details (and using distinct), or in a number of other ways. One other way might be to come up with a way to combine all of the wax names into one field (tricky - can't think how to do this in mysql, off the top of my head). What precisely are you trying to achieve, though - this might be completely wrong for you! Thanks, Matt -Original Message- From: JOHN MEYER [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:39 To: [EMAIL PROTECTED] Subject: Database design question Hi, I'm writing a database for an online candle store. Here's the situation. This store sells all sorts of items including candles. Now the gist is that some of the candles can be made in different types of waxes and some only have one wax. My question is how do I resolve this when I write up the order and write up the line items. This almost seems like it is going to be some sort of a three way join or something. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting Aggregate Object Data
I'm in the process of trying to move some of our systems to MySQL from SQL Server and Oracle. At this point I'm learning and testing MySQL. One immediate question I have is regarding getting aggregate information. For example, the SHOW DATABASES command would be an example of something I would get from the SQL Server data dictionary table, sysdatabases. It appears that MySQL has no such mechanisms, and that you must rely on many of the SHOW commands. In the case of databases, it works great, but what about other objects? For example, if I want to see all indexes in my sales database? Or do I have to loop through each table with the SHOW INDEXES command? That's one example of what I'm talking about. Thanks so much for the help. Lou Olsten [EMAIL PROTECTED]
Re: Hey what, no pity for a new user?
If apple continues to bundle MySQL with panther and does not give any kind of support for it's users, they should at least donate a few machines to us, so we can play with it and give some kind of support! YEAP I'll keep dreaming! =) jeje -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could some one help !
Hi, I am trying to install MYSQL on a Sun Solaris box and here are the details and the error. Here is the version of MYSQL I am trying to install: mysql-max-4.0.17-unknown-freebsd5.1-sparc64 #uname -a SunOS kittyhawk 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-1 # ./mysql_install_db --user=mysql ERROR = Didn't find ./bin/mysqld You should do a 'make install' before executing this script I have looked and spent many hours trying to figure this out but having no luck what so ever. Could someone tell me what I am doing wrong or how to start troubleshooting this effectively. Thanks. Ravi T. - Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway - Enter today
Re: Could some one help !
yeah, there's a whole lot wrong with that. 1. you're using the wrong versions of mysql, the freebsd sparc64 version wont work on solaris, perhaps you should get the solaris package? hrm, just a thought... and 2, it actually has to be installed before you try to run it, best option is to read the INSTALL-BINARY docs that come with the solaris version. trying to get a freebsd binary working on solaris is in for a world of pain tho. Dan. On Wed, 7 Apr 2004, Ravi T wrote: Hi, I am trying to install MYSQL on a Sun Solaris box and here are the details and the error. Here is the version of MYSQL I am trying to install: mysql-max-4.0.17-unknown-freebsd5.1-sparc64 #uname -a SunOS kittyhawk 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-1 # ./mysql_install_db --user=mysql ERROR = Didn't find ./bin/mysqld You should do a 'make install' before executing this script I have looked and spent many hours trying to figure this out but having no luck what so ever. Could someone tell me what I am doing wrong or how to start troubleshooting this effectively. Thanks. Ravi T. - Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway - Enter today -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Perl Modelues
Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. Kirt TIB Mountain City, TN 37683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Could some one help !
Did you run it from the directory from which you install mysql? EG: Installed mysql at /usr/local/mysql-max-4.0.18-sun-solaris-etc cd /usr/local/mysql-max-4.0.18-sun-whatever-the-rest-is ./bin/mysql_install_db --user=mysql a.. If you installed MySQL using a binary distribution, use this command: ./scripts/mysql_install_db --user=mysql RTFM Step 2 http://www.mysql.com/doc/en/Unix_post-installation.html Hope this helps, Ken - Original Message - From: Ravi T [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 4:50 PM Subject: Could some one help ! Hi, I am trying to install MYSQL on a Sun Solaris box and here are the details and the error. Here is the version of MYSQL I am trying to install: mysql-max-4.0.17-unknown-freebsd5.1-sparc64 #uname -a SunOS kittyhawk 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-1 # ./mysql_install_db --user=mysql ERROR = Didn't find ./bin/mysqld You should do a 'make install' before executing this script I have looked and spent many hours trying to figure this out but having no luck what so ever. Could someone tell me what I am doing wrong or how to start troubleshooting this effectively. Thanks. Ravi T. - Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway - Enter today -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
Just type in the empty search box at the top of the screen: DBI returns: http://search.cpan.org/~timb/DBI-1.42/DBI.pm mysql returns: http://search.cpan.org/~rudy/DBD-mysql-2.9003/ Hope that helps Ken - Original Message - From: Kirti S. Bajwa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 2:57 PM Subject: Perl Modelues Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. Kirt TIB Mountain City, TN 37683 -- 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: Perl Modelues
you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
Short answer: Type 'cpan' Type 'install DBD::mysql' More information is at: http://search.cpan.org/~rudy/DBD-mysql-2.9003/lib/DBD/mysql/INSTALL.pod General information about installing Perl modules: http://www.cpan.org/misc/cpan-faq.html#How_install_Perl_modules Eamon Daly NextWave Media Group LLC Tel: 1 773 975-1115 Fax: 1 773 913-0970 - Original Message - From: Kirti S. Bajwa [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 1:57 PM Subject: Perl Modelues Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Perl Modelues
I'm not exactly sure whether this will work on UNIX but on Linux it's just type cpan in your console than you should get the cpan console there you can search with i /dbi::mysql/ for your module and install #module name# gets you the module. hope that helps btw. h shows help text and there is a Perl-MySQL list too. Matthias -Ursprüngliche Nachricht- Von: Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 7. April 2004 11:58 An: '[EMAIL PROTECTED]' Betreff: Perl Modelues Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. Kirt TIB Mountain City, TN 37683 -- 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: Perl Modelues
it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. I'm not sure about the light, but when I typed in DBI in the search box at http://search.cpan.org and hit the search button it displayed a list of links and descriptions, the first of which was DBI. You can then either go to the descriptions DBI link or to the DBI-1.42 link just below it to get to the source directly. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
Eamon Daly wrote: Short answer: Type 'cpan' Type 'install DBD::mysql' Contrary to intuition, you should install DBI first! DBD::mysql will look for DBI too. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
version 4.0 , 4.1 and 5.0
Dear users Can someone give me a brief description of version 4.0 , 4.1 and 5.0 The website says that 4.0 is a production release where as version 4.1 and 5.0 are alpha releases. I mainly want to know the difference between 4.1 and 5.0 . Why dhould one one 4.1 and not 5.0 and viceversa Thanks for any one who contributes Saurabh ___ Saurabh Data School of Computing University of Leeds Leeds LS2 9JT U.K. one who seeketh , will findeth and all door shall open - james Allen ___ _ Tired of 56k? Get a FREE BT Broadband connection http://www.msn.co.uk/specials/btbroadband -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: version 4.0 , 4.1 and 5.0
At 22:46 + 4/7/04, Saurabh Data wrote: Dear users Can someone give me a brief description of version 4.0 , 4.1 and 5.0 The website says that 4.0 is a production release where as version 4.1 and 5.0 are alpha releases. I mainly want to know the difference between 4.1 and 5.0 . Why dhould one one 4.1 and not 5.0 and viceversa Thanks for any one who contributes This section of the manual might be helpful: http://www.mysql.com/doc/en/Roadmap.html -- 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]
Perl Modelues
Hello List: -- I left out the following information (sorry too much on mind): RH 9 MySQL 0.9.3 (Installing both Server Clients -- I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. Kirt TIB Mountain City, TN 37683 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.0 on the Mac?
Thank you all for the responses. Looks like no one's had problems getting 5.0 running on the Mac. I'll be trying it after this weekend. If I have any problems, you'll be hearing from me again! Thanks for all the info. James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unique varchar in field
Hello again, How do you make a field unique, but not have it as part of the primary key? For example, I have a 'computers' table that has a 'comp_id' field that's the primary key. It's an int that's auto-incremented with each new entry. The same table has a 'comp_serial' field. I want this field to be unique, but I don't want it to be a part of the primary key, or any key for that matter. I just want it to be unique. How do I achieve this? Thanks, Brad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unique varchar in field
Brad Tilley wrote: Hello again, How do you make a field unique, but not have it as part of the primary key? For example, I have a 'computers' table that has a 'comp_id' field that's the primary key. It's an int that's auto-incremented with each new entry. The same table has a 'comp_serial' field. I want this field to be unique, but I don't want it to be a part of the primary key, or any key for that matter. I just want it to be unique. How do I achieve this? Thanks, Brad ALTER TABLE computers ADD UNIQUE INDEX (comp_serial); The index on comp_serial will enforce uniqueness, and it's separate from the primary key. I don't know of any way to enforce uniqueness without making a unique key on the column. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using BYTEFX to connect to MySql , closing connection do not release the connection.
Thanks reggie, implement that and tested the scenario as per my first email, it work the desired way as per you have expected and suggested. My feeling about the second scenario is that it is a bug. Try this link and search for the exception text: http://cvs.sourceforge.net/viewcvs.py/mysqlnet/mysqlclient/MySqlPoolManager. cs?rev=1.4 it has been great help! :-)TweeWan -Original Message- From: Reggie Burnett [mailto:[EMAIL PROTECTED] Sent: 08 April 2004 22:23 To: 'tweewan.wong'; [EMAIL PROTECTED] Subject: RE: Using BYTEFX to connect to MySql , closing connection do not release the connection. I'm not sure why this is happening, but the behavior you described in the first email is correct pooling behavior. By default, connection pooling is enabled which means connections are not killed when they are closed but remain open and able to serve new connections if necessary. Only when the app quits is the pool manager collected and the connections killed. If this is not desired, you can add pooling=false to your connection string. -reggie -Original Message- From: tweewan.wong [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:44 AM To: [EMAIL PROTECTED] Subject: RE: Using BYTEFX to connect to MySql , closing connection do not release the connection. More information to add : Am using Bytefx 0.76 : I simplify the testing by using frmA call frmB. In frmB, I do the following : Private Sub frmB_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Try xx = New MySqlConnection xx.ConnectionString = Data Source=127.0.01; _ Database=good; _ User ID=root; _ Password=; xx.Open() xx.Close() xx.ConnectionString = xx.Dispose() MsgBox(Closed) Catch ex As Exception MsgBox(Err.Number ex.Message) End Try End Sub In frmA, I have this tied to a button Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim frm As frm_single frm = New frm_single frm.Show() End Sub I press button1 in frmA, when frmB shows up, I close frmB, I press button1 again in frmA and I get the following error: Pooling exception: Unable to find original pool for connection Regards TweeWan -Original Message- From: tweewan.wong [mailto:[EMAIL PROTECTED] Sent: 07 April 2004 15:02 To: [EMAIL PROTECTED] Subject: Using BYTEFX to connect to MySql , closing connection do not release the connection. Hi, Encounter an interesting here using VB.NET with ByteFx.mysql.mysqlclient to connect to Mysql Database. I have a sub-form (called from another main form) with 4 mysqlconnections declared and new. X1= new mysqlconnection X1.connectionstring = a valid connection string X1.open() X2= new mysqlconnection X2.connectionstring = a valid connection string X2.open() X3= new mysqlconnection X3.connectionstring = a valid connection string X3.open() X4= new mysqlconnection X4.connectionstring = a valid connection string X4.open() Msgbox(Phase 1) X1.close() X2.close() X3.close() X4.close() Msgbox (Phase 2) I am using MySQL Administrator to monitor the connection , at the Phase 1 break point I can see that 4 connection is allocated. And at Phase 2 , I expect the connection will be closed, but to my surprise, it did not. Not until I quit entire application. Then I see the connections are release. Anyone has encounter this before and any pointers for me to investigate further? Regards TweeWan -- 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: Excel file
Please read the attached file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I use an AS value in the WHERE clause.
Mike, I see what you're saying `active` was the alias name not an actual column. Ironically I was using a HAVING clause because I agree with that last post. Mike, why keep the `IF` statement? You're really saying give me all the records where this expression is true. Why not just move the expression in the `IF` to the HAVING clause? So take my old statement and ditch the where clause. You'll get: SELECT * FROM wifi_table HAVING unix_timestamp()-unix_timestamp(last_seen) 600; A little easier on the eyes no? Cheers, Adam On Apr 6, 2004, at 9:42 PM, Michael Stassen wrote: Adam, That won't work. Daevid doesn't have a column named active. Nor does he have to do the math twice. As was pointed out earlier, he can do what he wants using HAVING instead of WHERE, like this: SELECT *, IF(((unix_timestamp()-unix_timestamp(last_seen)) 600),1,0) active FROM wifi_table HAVING active = 1; Michael Adam wrote: Daevid, SELECT * FROM wifi_table WHERE active = 1 HAVING unix_timestamp()-unix_timestamp(last_seen) 600; Regards, Adam On Apr 5, 2004, at 8:29 PM, Daevid Vincent wrote: I'm curious when will I be able to do something like this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) 600),1,0) as active FROM wifi_table WHERE active = 1; It's so obnoxious, especially since I can do this: SELECT *, IF(( (unix_timestamp()-unix_timestamp(last_seen)) 600),1,0) as active FROM wifi_table WHERE unix_timestamp()-unix_timestamp(last_seen) 600; Why do I have to do the math TWICE?! *sigh* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
BAO RuiXian wrote: Mark wrote: Eamon Daly wrote: Short answer: Type 'cpan' Type 'install DBD::mysql' Contrary to intuition, you should install DBI first! DBD::mysql will look for DBI too. DBI only needs to be installed once. After the installtion, you may need to add as many as you need DBD::XXX. Yes. but from the OP's comment figuring out installation of Perl DBI modules, it seemed to me that this was his first-time encounter with DBI/DBD. And I just wanted to point out, that while he needs the underlying DBD::mysql drivers for DBI to work, that, contrary to intuition, he would need to install DBI first. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too Many Connections
What is the best way to diagnose the root cause of this error? What scripts are doing the connecting and totalling them up? Warning: mysql_connect(): User ultimated has already more than 'max_user_connections' active connections I have a very active phpBB but I'm on a new server and its not pulling a server loading over 0.5. I ran some data before (crontab php script gathered the info for me every 5 minutes for several weeks) and the problem happened before related to server loading..not necessarily how many users I had on that site posting. That was an older Cobalt RaQ4. I seemed to be having a lot of search bots accessing the site then. [mysqld] set-variable = max_connections=512 set-variable = max_user_connections=200 set-variable = key_buffer=64M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = wait_timeout=300 I've only had this problem this week, its run 3 weeks fine. I do have a corrupted MYI file according to myisamck. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Too Many Connections
Run mysqladmin extended-status Look for something like this: | Max_used_connections | 138| If it says, 512 is your max connections that you have used, then you need to raise it. If your number is much lower and you are getting that problem, it's a different problem, but that's just what mysql is reporting. Donny -Original Message- From: Mark Susol | Ultimate Creative Media [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 10:35 PM To: [EMAIL PROTECTED] Subject: Too Many Connections What is the best way to diagnose the root cause of this error? What scripts are doing the connecting and totalling them up? Warning: mysql_connect(): User ultimated has already more than 'max_user_connections' active connections I have a very active phpBB but I'm on a new server and its not pulling a server loading over 0.5. I ran some data before (crontab php script gathered the info for me every 5 minutes for several weeks) and the problem happened before related to server loading..not necessarily how many users I had on that site posting. That was an older Cobalt RaQ4. I seemed to be having a lot of search bots accessing the site then. [mysqld] set-variable = max_connections=512 set-variable = max_user_connections=200 set-variable = key_buffer=64M set-variable = table_cache=256 set-variable = sort_buffer=4M set-variable = wait_timeout=300 I've only had this problem this week, its run 3 weeks fine. I do have a corrupted MYI file according to myisamck. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- 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]