Re: Issue with em dash character
information_schema.columns reports a character_set_name of 'utf8' and a collation_name of 'utf8_general_ci' On Wed, Jun 3, 2015 at 3:14 PM, Emil Oppeln-Bronikowski e...@fuse.pl wrote: Is this a bug? If it's a feature, what could we do instead to get around this issue? Is your column set to unicode? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Issue with em dash character
We're finding that, when working with the em dash character, the LEFT and
LENGTH functions don't work well together. This query shows trying to
strip off the last character from a string containing an em dash:
mysql select LEFT('031492349−0002,', LENGTH('031492349−0002,') - 1),
LENGTH('031492349−0002,'), LENGTH('031492349-0002,');
++-+---+
| LEFT('031492349−0002,', LENGTH('031492349−0002,') - 1) |
LENGTH('031492349−0002,') | LENGTH('031492349-0002,') |
++-+---+
| 031492349−0002,|
17 |15 |
++-+---+
1 row in set (0.06 sec)
Is this a bug? If it's a feature, what could we do instead to get around
this issue?
Thanks,
Robert Voliva
Re: slave replication with lots of 'duplicate entry' errors
On Thu, Feb 14, 2013 at 5:46 PM, Rick James rja...@yahoo-inc.com wrote: Is it in read only mode? Furthermore, are all users logging in as non-SUPER users? Note: root bypasses the readonly flag! No. The user that is commonly used does have Super privileges. I am not sure why, but it does. Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
On Thu, Feb 14, 2013 at 4:54 PM, Manuel Arostegui man...@tuenti.com wrote: 2013/2/13 Robert Citek robert.ci...@gmail.com On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com wrote: Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, which are the most likely? [from off-list responder]: Other possibility: The replication is reading from master not from the point when the dump was done, but some time before and is fetching insert statements which are already in the dump. To prevent that I used the coordinates in the dump file included with --master-data=2. Could the coordinates still be off? Hello, Are you sure nothing is getting inserted directly into the slave? Is it in read only mode? If you're starting replication using the values provided by --master-data=2 (which should be something like): -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974', MASTER_LOG_POS=240814775; And if you're using the right IP, there's no reason to have duplicate entries unless someone is writing directly into the slave. Manuel. According to the client, nothing is writing to the slave and everything is being logged at the master. I have not had the opportunity to independently verified any of this, yet. I do know that the slave is not in read-only mode, but rather we promise not to write to it mode. At the moment, I am trying to come up with plausible explanations for the observations. Thanks for your feedback. Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
Yes. Except for a handful of static MyISAM tables. But the tables that are experiencing the issues are all InnoDB and large (a dozen or so fields, but lots of records.) Regards, - Robert On Thu, Feb 14, 2013 at 5:59 PM, Singer Wang w...@singerwang.com wrote: Are you using all InnoDB? S -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
Agreed. Will do that along with several other possible changes. But for the moment, I'm still gathering information and coming up with plausible models. Will also be turning on general mysql logging on both Master and Slave, at least briefly, to see what statements are being run on both. Regards, - Robert On Thu, Feb 14, 2013 at 6:13 PM, Rick James rja...@yahoo-inc.com wrote: Recommend, for security reasons, you rectify that. If you need some SUPER action, create a stored procedure with privileges 'creator', so the security hole is still mostly contained. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave replication with lots of 'duplicate entry' errors
On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek robert.ci...@gmail.com wrote: Any other possibilities? Do other scenarios become likely if there are two or more tables? Of those, which are the most likely? [from off-list responder]: Other possibility: The replication is reading from master not from the point when the dump was done, but some time before and is fetching insert statements which are already in the dump. To prevent that I used the coordinates in the dump file included with --master-data=2. Could the coordinates still be off? Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
need help creating relation
Hi there, I would like to create a table that optionally links to an other table. The field company_id in the following table should be either Null or link to the table tblCompany (`id` ). How do I do that. The way I it now, I can not enter records where the field company_id is null. Thanks robert drop table IF EXISTS tblAgentCompanyMapper; CREATE TABLE IF NOT EXISTS `energie_2`.`tblAgentCompanyMapper` ( `siaid` INT COMMENT 'id assigned in the excelsheet by SIA' , `company_id` INT NULL DEFAULT NULL UNIQUE COMMENT 'id automatically used by energiecluster adresses' , `name` VARCHAR(128) NOT NULL , `url` VARCHAR(256) NULL , INDEX `fk_tblAgentCompanyMapper_tblCompany1` (`company_id` ASC) , PRIMARY KEY (`siaid`) , CONSTRAINT `fk_tblAgentCompanyMapper_tblCompany1` FOREIGN KEY (`company_id` ) REFERENCES `energie_2`.`tblCompany` (`id` ) ON DELETE SET NULL ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: odd problem with select as statement
Yes! The illegal double error only happens if you do the select like you did. The only error I was getting was the generic there's an error in your sql. Thank you! Bob From: Hank [mailto:hes...@gmail.com] Sent: Monday, December 20, 2010 11:52 AM To: Ramsey, Robert L Cc: mysql@lists.mysql.com Subject: Re: odd problem with select as statement i.e. just try this: mysql select 4E5664736F400E8B482EA7AA67853D13; ERROR 1367 (22007): Illegal double '4E5664736' value found during parsing -Hank On Mon, Dec 20, 2010 at 12:50 PM, Hank hes...@gmail.commailto:hes...@gmail.com wrote: Here's my 5 second guess.. 4E5664736... is being interpreted as a number in scientific notation .. i.e. 4*10^5664736 and the parser doesn't like that as a field name. -Hank On Mon, Dec 20, 2010 at 12:43 PM, Ramsey, Robert L robert-ram...@uiowa.edumailto:robert-ram...@uiowa.edu wrote: I am having the hardest time getting a select as statement right. Here is the full query: select SUM(IF(image='EE0840D00E2ED8A317E5FA9899C48C19',1,0)) as EE0840D00E2ED8A317E5FA9899C48C19, SUM(IF(image='235C7987796D5B7CEBF56FBDA2BF7815',1,0)) as 235C7987796D5B7CEBF56FBDA2BF7815, SUM(IF(image='96DC0562ED6E6F7FE789A18E09BC5889',1,0)) as 96DC0562ED6E6F7FE789A18E09BC5889, SUM(IF(image='D8B0EA710D2EF408391132F451AE724A',1,0)) as D8B0EA710D2EF408391132F451AE724A, SUM(IF(image='018C4DB7229D7D2BEB040D241739B784',1,0)) as 018C4DB7229D7D2BEB040D241739B784, SUM(IF(image='98DE1FCA50AC9CE6E0FEA25BAB0177FE',1,0)) as 98DE1FCA50AC9CE6E0FEA25BAB0177FE, SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13, --offending line SUM(IF(image='FEB810A43A1B275605BD6B69F444700C',1,0)) as FEB810A43A1B275605BD6B69F444700C from dsrssfeed If I remove that one line, the query works fine. If I do: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E from dsrssfeed ; it works. But these fail: select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5664736F400E8B482EA7AA67853D13 from dsrssfeed ; select SUM(IF(image='4E5664736F400E8B482EA7AA67853D13',1,0)) as 4E5 from dsrssfeed ; It can't be field name length, since even 4E5 fails, the field name can start with a number since 4E succeeds. Any ideas? The goal is to see what arbitrary images have information associated with them. The table has two fields: image is a UID that is the primary key, and caption which is a varchar(255) that has information about the image. Images are added and deleted from the table as they are changed on a web page. The UID is generated by a third party program that I have to interface with and have no control over. An array of image UIDs is sent to the php script and the script needs to determine which UIDs are present in the table. Rather than make N number of individual queries as I iterate through the array, I iterate through the array and build the query on the fly to make one query. Then I iterate through the array again and check the value in the field. 1 means the UID has an entry, 0 means it doesn't. I thought doing 1 mysql call would be more efficient than lots of calls as I iterate through the array. But since there will probably never be more than 100 images in the table at any one time, it may not make any difference. But now I'm just curious as to why this is happening. Thanks, Bob
Re: migrating a split replication
On Thu, Dec 9, 2010 at 9:10 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 12/8/2010 22:50, Robert Citek wrote: Greetings to all, Can I migrate slave databases between slave servers? Imagine the following scenario: I have one master database server with 10 databases. I also have two slave database servers, one replicating 5 of the 10 databases, the other replicating the other 5 databases. Can I migrate one of the replicated databases from one slave to the other, resulting in one slave having 6 databases and the other having 4? I'm using the term migrate, but is there a more appropriate term? The docs mention various replication strategies[1], including splitting out different databases to different slaves. In the extreme case, I would like to do the opposite, consolidate databases among slaves, with the final state being all 10 databases on one slave and none on the second. Thanks in advance for your help, especially pointers to any references. [1] http://dev.mysql.com/doc/refman/5.0/en/replication-solutions.html Regards, - Robert The trick to moving replicated tables between boxes is to get both slaves to the same replication coordinates. Stop replication on one wait 5 minutes then stop it on the other. Check the binary log coordinates between the two. For the one that's looking at the older data, use a START SLAVE UNTIL ... command to get them both to the same binlog position. http://dev.mysql.com/doc/refman/5.1/en/start-slave.html Now, the copy of the data on one slave should be in an identical state to the copy of the data on the other slave (if it were replicating the table). Move the table(s) or database(s) to the other slave then undo any --replicate-* filters you may have that prevented replicating that information on the new box before the move. At this point, you probably need to copy the same --replicate-* rule you are removing from the new box to the old box so that it will stop processing commands for the data are trying to move. http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html After all looks good, START SLAVE on both machines and observe SHOW SLAVE STATUS to ensure that they are both catching up to the master. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN Thanks, Shawn. I'll give that a try. Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
migrating a split replication
Greetings to all, Can I migrate slave databases between slave servers? Imagine the following scenario: I have one master database server with 10 databases. I also have two slave database servers, one replicating 5 of the 10 databases, the other replicating the other 5 databases. Can I migrate one of the replicated databases from one slave to the other, resulting in one slave having 6 databases and the other having 4? I'm using the term migrate, but is there a more appropriate term? The docs mention various replication strategies[1], including splitting out different databases to different slaves. In the extreme case, I would like to do the opposite, consolidate databases among slaves, with the final state being all 10 databases on one slave and none on the second. Thanks in advance for your help, especially pointers to any references. [1] http://dev.mysql.com/doc/refman/5.0/en/replication-solutions.html Regards, - Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
multiple aliases
I have a query with three subselects, all referencing the same table. I'd like to be able to combine them into one with aliases. Here's what I have now: select letter_codename, (select greek from letter_otherlanguages where letter ='A') as greek, (select french from letter_otherlanguages where letter ='A') as french, (select german from letter_otherlanguages where letter ='A') as german from intl_codes where letter='A'; I'd like to replace it with: select letter_codename, (select greek, french, german from letter_otherlanguages where letter ='A') as (greek, french, german) from intl_codes where letter='A'; Don't get hung up on the tables and structures, this is just a simple example. :) I want to use the three subselects because if I use a left join, the processing time goes from .4 to 5 seconds. Is this possible? Thanks!
mysql vs postgresql -- is this list accurate?
no, i don't want to start a flame war, i just want some feedback on a current list of mysql drawbacks WRT postgresql. in the context of a fully open-source, java based ECM product, there is a FAQ entry that summarizes why the developers would prefer their users to use postgresql as opposed to mysql: http://www.nuxeo.org/xwiki/bin/view/FAQ/WhyAvoidMySQL i'm not advocating one way or the other, i just want to make sure that list is accurate and i'm not enough of an expert to be able to judge the entire list. if anyone wants to tell me whether any of those entries are no longer relevant, or are overblown, or whatever, i'd appreciate it. again, i'm not taking sides, i just want to make sure the information is as accurate as possible. thanks. rday -- Robert P. J. Day Waterloo, Ontario, CANADA Top-notch, inexpensive online Linux/OSS/kernel courses http://crashcourse.ca Twitter: http://twitter.com/rpjday LinkedIn: http://ca.linkedin.com/in/rpjday -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
might need some help recovering tables from trashed DB
i asked about this once upon a time, and might need a little more help here. a friend's mysql hosting provider lost an entire DB, but has managed to recover and hand over the ibdata1 file (or at least some portion of it). when my friend popped into mysql, what he's seeing is that some of the tables appear to be back, but others generate a does not exist diagnostic. by way of trying to help last time, i literally copied the underlying mysql files onto my linux system, then fired up mysql to see what magically appeared, but that was before i even had the ibdata1 file so i wasn't surprised to get very little in the way of recovered data. now, though, with this ibdata1 file, i can try that again -- fire up a new linux box, and manually install the files under /var/lib/mysql. i'm guessing i'll see much of what he's seeing. i just want to verify that, if that's all i have access to and some of the tables still appear to be missing, there's not much i can do. or is there? rday -- Robert P. J. Day Waterloo, Ontario, CANADA Top-notch, inexpensive online Linux/OSS/kernel courses http://crashcourse.ca Twitter: http://twitter.com/rpjday LinkedIn: http://ca.linkedin.com/in/rpjday -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
sorting numbers as spelled
Say I have the following data: +---+ | title | +---+ | ...And justice for all | | 12 Angry Men | | The Abe Lincoln of 9th Avenue | | A Cry for Freedom | | Alice in Wonderland | +---+ And I want to sort it alphabetically, but with the numbers spelled out and the leading articles removed. I know I can use trim to get rid of the leading A/An/The/... but I want to sort 12 in the Ts for twelve, like this: +---+ | title | +---+ | The Abe Lincoln of 9th Avenue | | Alice in Wonderland | | ...And justice for all | | A Cry for Freedom | | 12 Angry Men | +---+ So it is alphabetized by Abe, Alice, And, Cry, and 12 as twelve. Any ideas, or should I just tell the user to spell out numbers? Thanks!
Re: Help Save MySQL
I have to disagree, your degrading the open community that built MySQL (well supported it anyway). But now that it is doing well, your saying they aren't good enough, after the fact they already built it. Yet it is good enough to for SUN and Oracle to buy. As a small business owner, the last thing I am going to do is service a free product, that will compete with my cash flow product. Get real!! Oracle is not going to rescue MySQL. Oh they are willing take control of it, just so they can dumb it down, and use its code in their own product. If you actually believe Oracle is going to service, update, and develop MySQL, then you are the naive one. Except naive isn't the word that comes to mind. Having said that, a save-mysql-website is not the answer, if SUN wants to sell it they have the right to do so, and sell it to the highest bidder, or the lowest, or whoever they feel like. MySQL belongs to SUN. Apparently they are serious about selling it off. With that in mind, if you want to stop the giant Oracle from oppressing MySQL you better step up and make SUN an offer, because it is business, SUN will sell it. Selling to Oracle will not give Oracle a monopoly, therefore there is no grounds to prevent the sell. Again, SUN will sell it to Oracle unless someone gives SUN a better deal. It will not matter how many I-hate-Oracle or Anti-sell-to-Oracle websites are created. This business people, big business. Robert Canary OCDirect Electrical-Datacomm (270) 298-9331 (270) 955-0362 - Douglas Nelson douglas.nel...@sun.com wrote: You guys are incredibly naive, Oracle has promised to support MySQL for the next 5 years. Who better to have stewardship of a database product then the leading database supplier. I think you guys have visions of grandeur to think you could spend this kind of effort or resources to build a better MySQL then Oracle. As for competing for market share against the Oracle product.. you guys have obviously never used the Oracle database it puts MySQL to shame in so many different ways and so many different levels. Keeping MySQL around serves many purposes for Oracle very nicely.. one of my favorites is the screws to IBM's DB2 and Microsoft's Access... couple of real winners there I tell ya. Putting MySQL in the hands of half cocked want-a-be's would surely be the death blow to MySQL faster then Oracle would ever hope too.. should they decide to. So at least for the next 5 years sit back and enjoy the ride! Thanks Doug Peter Brawley wrote: Actually just $8.95/year, I just registered save-mysql.org. Anybody want to help set up the page? PB - Peter Brawley wrote: Twenty bucks a year to hold the domain name. I'll do it if a couple of folks volunteer to help set up the page. PB - Bruno B. B. Magalhães wrote: Lets register the domain www.keep-mysql-open.com, build a champain, and ask all our partners, clients and friends to support to it? And let's make some noise... Let's engage the society and big companies like Google, Facebook, twitter, 37Signals, Amazon, Percona, etc. And starting talking to the media like TechCrunch.com, BusinessWeek, ZD.Net, etc. Without society's engagement we won't acomplish anything! Regards, Bruno B. B. Magalhães Sócio-Diretor de Negócios e Tecnologia BLACKBEAN CONSULTORIA Rua Real Grandeza 193/210, Botafogo Rio de Janeiro, RJ, 22281-035, Brasil +55 (21) 9996-1093 +55 (21) 2266-0597 www.blackbean.com.br Esta mensagem pode conter informação confidencial e/ou privilegiada. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, não pode usar, copiar ou divulgar as informações nela contidas ou tomar qualquer ação baseada nessas informações. Se você recebeu esta mensagem por engano, por favor avise imediatamente o remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua cooperação. This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. Quoting mos mo...@fastmail.fm: Instead of forming a group hug and singing kumbuya to save MySQL, why don't we take some action to prevent Oracle from keeping (destroying/crippling) MySQL? Who are the regulatory bodies that will decide whether Oracle can keep MySQL? Both in Europe and North America? Asia? What are their email addresses? They may not realize how destructive Oracle can be to the future of MySQL and the number of MySQL database installations this will affect. I think we should start a writing campaign to the regulatory
getting repeating events between two dates
Hi,
I have a table of events like this:
Evtuid
Evtname
Startdate
Stopdate
Repeats enum('true', 'false')
Monday enum('true', 'false')
Tuesday enum('true', 'false')
Wednesday enum('true', 'false')
Thursday enum('true', 'false')
Friday enum('true', 'false')
Satday enum('true', 'false')
Sunday enum('true', 'false')
An event might start on Monday Nov. 2 and occur on every Monday and Thursday
until November 26th. In which case startdate would equal 2009-11-02, stopdate
would equal 2009-11-26, repeats = 'true', Monday='true', and Thursday='true'.
The other days are false. Assume that the Evtuid=1 and Evtname='Test Event'.
Is there a query I can run that will give me a listing of every date with an
event like this:
2009-11-02 Test Event 1
2009-11-05 Test Event 1
2009-11-09 Test Event 1
2009-11-12 Test Event 1
2009-11-16 Test Event 1
2009-11-19 Test Event 1
2009-11-23 Test Event 1
2009-11-26 Test Event 1
And of course any other dates with other events that either repeat or not.
Is there a way to do that or am I better off just using php and looping through
every day in the range?
Thanks!
Bob
Re: Adding Fields To Table
If you are very new, you may want to go through the tutorial at w3shools: http://www.w3schools.com/SQl/default.asp Regards, - Robert On Wed, Sep 30, 2009 at 11:45 AM, Carlos Williams carlosw...@gmail.com wrote: I am no doubt very new to MySQL and have been studying the on line manual however I find it a bit complex and don't understand what it's suggesting I do in this case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: sub query or something else
It's not clear what exactly you are looking for. Two possible solutions: 1) use a union 2) use a join with another table containing partner and factor fields. Can you give a short example of what the input looks like and what you would like the output to look like? Regards, - Robert On Thu, Sep 3, 2009 at 10:46 PM, sangprabvsangpr...@gmail.com wrote: I have these query: SELECT SUM(price)*0.5 AS price1 FROM table WHERE partner = 'A'; SELECT SUM(price)*0.65 AS price2 FROM table WHERE partner = 'B'; Is it possible to make the queries into 1 single query? How to make it happen? Many thanks for helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
getGeneratedKeys
I have a stored procedure that inserts a record that I call from a PreparedStatement. When I call #getGeneratedKeys it always returns a null result set. Is that expected behavior? Robert DiFalco | Chief Technology Officer (Products) Direct: 503.276.7564 Mobile: 503.890.4994 Charlotte Caswell | Executive Assistant Direct: 503.276.7665 TRIPWIRE | The Leader in Configuration Audit Control Check out the latest Tripwire news! http://www.tripwire.com/press/index.cfm
Re: questions about merging
Walter Heck li...@olindata.com writes: Might be a bit late, Better late than never. but on the naming issue: why not rename the tables on the old database before doing the dump? that would save you from potentially messing with your data due to parsing problems. A good idea, but it wouldn't have worked for me. The original site was not mine and I didn't have permission to do so. Thanks, rdc -- Robert D. Crawford rd...@comcast.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: questions about merging
Olaf Stein olaf.st...@nationwidechildrens.org writes: I think he only has one DB, I guess that is a provider restriction... True. I would create a separate db for each application if I could. rdc -- Robert D. Crawford rd...@comcast.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
questions about merging
Hello all, I hope I get the terminology right. I am not sure merge is what I want, so let me describe what I need. I have a sql dump from a db that is a mediawiki. I cannot upgrade the software on the old server and need to move it to a new server. I have only one db available to me on the new server that currently holds a wordpress blog. As I understand it this should be no problem as long as the table names do not conflict, right? I know the command for restoring the db from the mysql command prompt. First, I want to make sure that doing mysql -p dbname dump.sql is not going to hose the current db. Next I want to find out if there is an easy way to change the table names. I need the tables prefixed with the wiki name. Can anyone help me to figure out the easiest and safest way to accomplish this? If any more information is needed I can get it for you, I'm sure. Thanks in advance for any help or pointers to relevant documentation, rdc -- Robert D. Crawford rd...@comcast.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: questions about merging
Hello, Olaf, Stein, Olaf olaf.st...@nationwidechildrens.org writes: the import of the dump will not remove your other tables unless there is a naming conflict, then the table will be overwritten. As far as the prefix goes you can edit the dump file, find the create table statement for each table and add the prefix. If you have lots of tables or a huge dump file it might make sense to use a more automated approach, perl or python scripts will work on most platforms. The prefix will also make sure you have no naming conflicts I guess I figured that would be the case but I really needed to make sure. The file is huge but judicious use of head, emacs, and cat should do the job just fine without having to write something. One other question concerns these lines: CREATE DATABASE `db169254900`; USE db169254900; I can just remove the CREATE DATABASE line and change the USE line to reflect the name of the current db, right? Thanks for your help. My partner will be rather happy to have her wiki up. rdc -- Robert D. Crawford rd...@comcast.net Laugh at your problems; everybody else does. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: looking for ready made address management
Jerry Schwartz schrieb: I strongly advise you to use an off-the-shelf solution. In fact, if you can afford it you should go with a CRM vendor. They will have all kinds of features, such as address duplication detection, that you will need. you are of course perfectly right, however I need it as base of a bigger scenario (as part of a event/document managementsystem based on zope/plone). robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
looking for ready made address management
Hi there, I am about to create tools to maintain addresses (companies, persons, groups) As this is probably done allredy a million times over I would like to ask if somebody could point me from where I migth download the database structure for such a feat or whether someone of you could provide me one. this would not only spare me some time and errors designing it but I migth lern some tricks of the trade. thanks a lot robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: looking for ready made address management
Jason Pruim schrieb: On Aug 12, 2008, at 2:30 AM, robert rottermann wrote: Hi there, I am about to create tools to maintain addresses (companies, persons, groups) As this is probably done allredy a million times over I would like to ask if somebody could point me from where I migth download the database structure for such a feat or whether someone of you could provide me one. this would not only spare me some time and errors designing it but I migth lern some tricks of the trade. Hi Robert, It sounds like you are looking for a CRM... There are lots of options out there (I'm assuming open source since I don't have time to check) google is your friend! :) thanks jason, the problem is, that I get so many options (i tired source forge with ~8000 propositions) that I would be happy to get some guidance .. robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
design question
hi there, I am a casual database tinkerer that has to build a new database. so please do not fall down laughing if I ask stupid questions .. the problem at hand is that I want to create tables with the following structure: - suppliers they produce/deal-with 0 to n products - products a product is produced/sold by 1-n suppliers it can have 0-1 declaration - declarations these are product specsheets my questions are now: what indices and actions do I have to create to link these tables ? in the following script created by by MySQL-workbench. there is the table tblProducts_has_tblSupplier which is linked to both tblSupplier and tblProducts by foreign keys. How do I guaranty integrity when deleting a product or supplier?? do i have to do that using triggers? thanks for your insigth robert SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `energie` ; USE `energie`; -- - -- Table `energie`.`tblProducts` -- - DROP TABLE IF EXISTS `energie`.`tblProducts` ; CREATE TABLE IF NOT EXISTS `energie`.`tblProducts` ( `idtblProducts` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `simplesystem` TINYINT(4) NULL DEFAULT 0 , `kompaktenergiezentrale` TINYINT(4) NULL DEFAULT 0 , `device_with_humidity_recovery` TINYINT(4) NULL DEFAULT 0 , `room_ventilator` TINYINT(4) NULL DEFAULT 0 , `comfort_regulation` TINYINT(4) NULL DEFAULT 0 , `heat_pump` TINYINT(4) NULL DEFAULT 0 , `outlet` TINYINT(4) NULL DEFAULT 0 , `air_dispersing_system` TINYINT(4) NULL DEFAULT 0 , `heat_exchanger` TINYINT(4) NULL DEFAULT 0 , `heat_recovery` TINYINT(4) NULL DEFAULT 0 , `humidity_recovery` TINYINT(4) NULL DEFAULT 0 , `sound_suppressor` TINYINT(4) NULL DEFAULT 0 , `pollen_filter` TINYINT(4) NULL DEFAULT 0 , `groundsregister` TINYINT(4) NULL DEFAULT 0 , `single_room` TINYINT(4) NULL DEFAULT 0 , `one_family_house` TINYINT(4) NULL DEFAULT 0 , `multi_family_house` TINYINT(4) NULL DEFAULT 0 , PRIMARY KEY (`idtblProducts`) ) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1; -- - -- Table `energie`.`tblDeclaration_vent` -- - DROP TABLE IF EXISTS `energie`.`tblDeclaration_vent` ; CREATE TABLE IF NOT EXISTS `energie`.`tblDeclaration_vent` ( `idtblDeclaration_vent` INT NOT NULL AUTO_INCREMENT , `manufacturer` VARCHAR(45) NOT NULL , `name` VARCHAR(45) NOT NULL , `flowrate` INT NULL DEFAULT 0 , `humidity_recovery` INT NULL DEFAULT 1 , `energy_class` VARCHAR(1) NOT NULL , `energy_class_value` VARCHAR(10) NOT NULL , `hygiene_class` VARCHAR(1) NOT NULL , `noise_class` VARCHAR(1) NOT NULL , `audited_by` VARCHAR(45) NULL DEFAULT '' , `audited_date` DATE NULL DEFAULT NULL , `autition_report` VARCHAR(45) NULL DEFAULT '0-0-0' , `picture_name` VARCHAR(45) NULL DEFAULT '' , `remarks` TEXT NULL DEFAULT NULL , `reindex` BOOLEAN NULL DEFAULT 1 , `tblProducts_idtblProducts` INT(11) NULL , PRIMARY KEY (`idtblDeclaration_vent`) , CONSTRAINT `fk_tblDeclaration_vent_tblProducts` FOREIGN KEY (`tblProducts_idtblProducts` ) REFERENCES `energie`.`tblProducts` (`idtblProducts` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB COMMENT = 'table with declaration data for ventilation' PACK_KEYS = 1; CREATE INDEX imanufacturer ON `energie`.`tblDeclaration_vent` (`manufacturer` ASC) ; CREATE INDEX ienergy_class ON `energie`.`tblDeclaration_vent` (`energy_class` ASC) ; CREATE INDEX ihygiene_class ON `energie`.`tblDeclaration_vent` (`hygiene_class` ASC) ; CREATE INDEX inoise_class ON `energie`.`tblDeclaration_vent` (`noise_class` ASC) ; CREATE UNIQUE INDEX imanufaturer_name ON `energie`.`tblDeclaration_vent` (`manufacturer` ASC, `name` ASC) ; CREATE INDEX iname ON `energie`.`tblDeclaration_vent` (`name` ASC) ; CREATE INDEX fk_tblDeclaration_vent_tblProducts ON `energie`.`tblDeclaration_vent` (`tblProducts_idtblProducts` ASC) ; -- - -- Table `energie`.`tblSupplier` -- - DROP TABLE IF EXISTS `energie`.`tblSupplier` ; CREATE TABLE IF NOT EXISTS `energie`.`tblSupplier` ( `idtblSupplier` INT(11) NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NOT NULL , `extraname` VARCHAR(45) NOT NULL , `description` VARCHAR(45) NOT NULL , `address` VARCHAR(45) NOT NULL , `extraaddress` VARCHAR(45) NOT NULL , `zip` INT(20) NOT NULL , `city` VARCHAR(45) NOT NULL , `pob` INT(20) NOT NULL , `country` VARCHAR(45) NOT NULL , `language` VARCHAR(45) NOT NULL , `email` VARCHAR(45) NOT NULL , `url` VARCHAR(60) NOT NULL , `phone` VARCHAR(45) NOT NULL , `fax` VARCHAR(45) NOT NULL , `responsibleperson` VARCHAR(45) NOT NULL
trigger that calls a webservice??
Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
what codepage /colation to use?
hi there, I am usin mysql workbench to edit tables now I wonder what codepage I shoud use. what I want is to use utfo input and output. now ther is utf8 - utf8_bin, utf8 - utf8_unicode_ci and about 7 million of others. which one do I use. thanks very much robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How To run Multiple Storage Engines: CSV, InnoDB...
I'm running MySQL 5.0.22 on CentOS 5.1 (with all current patches applied) and tried to define a table 'testcsv' using ENGINE = CSV. I couldn't find a *.CSV file in the data directory. After looking it up in MySQL Third Edition (by Paul DuBois) it turns out that I'm not running the CSV engine. My testcsv table is using the default MyISAM engine. See what 'show tables' says: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 to server version: 5.0.22 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show engines; ++-++ | Engine | Support | Comment| ++-++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | YES | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| NO | Archive storage engine | | CSV| NO | CSV storage engine | | ndbcluster | NO | Clustered, fault-tolerant, memory-based tables | | FEDERATED | NO | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine| ++-++ 12 rows in set (0.01 sec) I'm not sure if a 'YES' in the 'Support' column above means the engine is started at runtime, or if it means the MySQL server was compiled with support for that engine. In any case, I want to be able to use the CSV engine in addition to MyISAM, MEMORY, InnoDB, BerkeleyDB and MRG_MYISAM. How do I activate that CSV engine? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indices in InnoDB/MySQL
I've been told that an index always contains the primary key. So if I have a LONG ID that is the primary key of table and create on index on LONG VALUE, the index on LONG VALUE will actually work as a typical compound index on ID,VALUE. My question is this, if I don't know that about MySQL and create my indices on purpose (i.e. not taking into account implementation details about MySQL) I would create the index explicitly on ID,VALUE. If I did that would I effectively have the same index as one on just VALUE or would there some how be duplicate data in the ID,VALUE index (i.e. ID, ID, VALUE)? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indices in InnoDB/MySQL
There is already a primary key index on ID. -Original Message- From: Wm Mussatto [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2008 10:50 AM To: mysql@lists.mysql.com Subject: Re: Indices in InnoDB/MySQL On Tue, April 1, 2008 10:01, Robert DiFalco wrote: I've been told that an index always contains the primary key. So if I have a LONG ID that is the primary key of table and create on index on LONG VALUE, the index on LONG VALUE will actually work as a typical compound index on ID,VALUE. My question is this, if I don't know that about MySQL and create my indices on purpose (i.e. not taking into account implementation details about MySQL) I would create the index explicitly on ID,VALUE. If I did that would I effectively have the same index as one on just VALUE or would there some how be duplicate data in the ID,VALUE index (i.e. ID, ID, VALUE)? R. Close but not quite there... You should always have AN index which is the primary key. You can create other indexes which don't. If you are searching for 'Value' then that should be the index. If you do ID,VALUE it can't use the index AFAIK because it won't know the ID. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- 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: Indices in InnoDB/MySQL
Right, the proof is that if I have an PKEY on ID and an index just on VALUE in MySQL then a query that would use both ID and VALUE works fine with just the index on VALUE. For Oracle, I need an explicit compound index (in addition to the PKEY) on (ID,VALUE). The results on MySQL get a little blurry when the PKEY is compound. -Original Message- From: Olexandr Melnyk [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 01, 2008 11:08 AM To: mysql@lists.mysql.com Subject: Indices in InnoDB/MySQL On 4/1/08, Paul DuBois [EMAIL PROTECTED] wrote: At 10:01 AM -0700 4/1/08, Robert DiFalco wrote: I've been told that an index always contains the primary key. By who? Ask for proof. I guess he was referring to the fact that InnoDB stores the primary key values alongside the indexed columns value, as a way of referencing the associated row. -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
limit and count to get summaries
Hi, I'm having trouble wrapping my head around this problem. I have a list of events for multiple computers. What I want to get is a summary of the top 3 most common errors for each computer. So I get a result like this: Computername event numb_times Comp1 logon 100 Comp1restart 90 Comp1Word Error 75 Comp2 logoff 88 Comp2Excel Error 82 Comp2Word Error 17 Comp3 logon 110 Comp3restart 50 Comp3Notepad Error 25 Comp4 logon 90 Comp4explorer error 89 Comp4print job 65 Each computer might have a hundred different types of errors and a thousand entries in the table. I thought the sql would be something like: Select computername, event, count(event) and numb_times, from eventtbl group by computername, event order by computername, numb_times limit 3; But that wasn't the answer. Can I do this is one sql statement? Or am I going to have to make temp tables or multiple select statements? Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
useCursorFetch
A while back there was a general consensus that useCursorFetch (with useServerPrepStmts) was somehow flakey? Is this still the case? I had heard from someone that MySQL will not even provide support for customers using these options in the JDBC driver. Is that true? TIA, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql claims table does not exist. but it does
Hi there, I have a strange problem. I am using Server version: 5.0.45 SUSE MySQL RPM I made a backup of a database by copying its directory like so: mv urulu urulu_X then I recreateded the database, played a little with it and then dropped it again. the I moved the old database in place again. now when I want to access the tables of the database I get an error that the tables do not exist, alltough the showtables lists them correctly. what can I do to fix that? to explayin thin a bit better, here a dump of an mysql dialog mysql use urulu Database changed mysql show tables; +--+ | Tables_in_urulu | +--+ | dataccess| | datancestors | | datchildren | | datserialisation | | datstat | | datvariables | | datwidgets | | defchildren | | defdependencies | | defgeneral | | defhandler | | defvariables | | lang | | session | | synaccess| | synconnect | | syncreate| | syndata | | synproperties| | syntoc | | xslcache | +--+ 21 rows in set (0.00 sec) mysql select * from lang; ERROR 1146 (42S02): Table 'urulu.lang' doesn't exist mysql thanks robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting number of associated many-to-many items
Hello! I have three tables, mapping out a n:n relationship of authors and the books they worked on: table 1: authors (id, name) table 2: authorships (author_id, book_id) table 3: books (id, name, bestseller tinyint) Here's two different queries I want to run: 1. Select each author, and how many books he has worked on. 2. Select each author, and how many bestseller books (bestseller = 1) he has worked on. Not exactly sure how to do this, can someone help me out with this? Thank you, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need advice on how to design tables for recurring events
This and Frederic Wenzel's suggestions were exactly what I was looking for. Thanks! Bob -Original Message- From: Erich C. Beyrent [mailto:[EMAIL PROTECTED] Sent: Thursday, October 11, 2007 6:10 AM To: mysql@lists.mysql.com Subject: Re: need advice on how to design tables for recurring events I've been working with Drupal for some time, and there is a module that allows you to create event-based content with a repeating schedule. The schema that it uses may be of some help to you. CREATE TABLE IF NOT EXISTS event_repeat ( rid int(10) unsigned NOT NULL default '0', repeat_data longtext NOT NULL, repeat_RRULE longtext NOT NULL, repeat_COUNT_remaining int(4) NOT NULL default '-1', repeat_start int(10) unsigned NOT NULL default '0', repeat_end int(10) unsigned NOT NULL default '0', repeat_last_rendered int(10) unsigned NOT NULL default '0', PRIMARY KEY (rid) ); CREATE TABLE IF NOT EXISTS event ( rid int(10) unsigned NOT NULL default '0', nid int(10) unsigned NOT NULL default '0', repeat_edited int(2) unsigned NOT NULL default '0', UNIQUE KEY nid (nid), KEY rid (rid) ); CREATE TABLE IF NOT EXISTS event_repeat_calendar_map ( day_stamp char(15) NOT NULL default '', date_stamp int(10) unsigned NOT NULL default '0', day_of_week char(2) NOT NULL default '', day_in_month char(3) NOT NULL default '', day_in_month_R char(4) NOT NULL default '', month_day char(2) NOT NULL default '', month_day_R char(3) NOT NULL default '', month char(2) NOT NULL default '', year_day char(3) NOT NULL default '', year_day_R char(4) NOT NULL default '', week_number char(2) NOT NULL default '', week_number_R char(3) NOT NULL default '', PRIMARY KEY (date_stamp), KEY day_of_week (day_of_week), KEY day_in_month (day_in_month), KEY day_in_month_R (day_in_month_R), KEY month_day (month_day), KEY month_day_R (month_day_R), KEY month (month), KEY year_day (year_day), KEY year_day_R (year_day_R), KEY week_number (week_number), KEY week_number_R (week_number_R) ); HTH -Erich- Frederic Wenzel wrote: On 10/10/07, Ramsey, Robert L [EMAIL PROTECTED] wrote: I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. (...) The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Well that doesn't seem to resource intensive to me, however it depends on what you are displaying later. Knowing the amount of times the class meet would be counting the Mondays (+Wednesdays+Fridays) between start_datetime and stop_datetime, but that shouldn't be too bad. What you should never do though is putting different values into the same field -- it defeats the purpose of a relational database. '1,3,5' is therefore a no-go... You ought to make a column for each day of the week and set it 0 for no class and 1 for class, or something along the lines of that. That will also make it insanely easy to retrieve all classes that meet on any given day: SELECT * FROM classes WHERE monday = 1 AND start_datetime = NOW() AND stop_datetime = NOW(); Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=robert- [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need advice on how to design tables for recurring events
Hi, I'm looking for a best practices way of creating tables to store both one time and regularly repeating events. These are classes, so for the most part the have a regularly recurring time, but we do have some one off events. For example, let's say the following: Class-A Mon,Wed,Fri 8-9 from August 20th-December 5th Class-B Mon,Wed,Fri 9:10-10:10 from August 20th-December 5th Class-C Tues,Thurs 10:30-12 from August 20th-December 5th Class-D Wed,Thurs,Friday 1-2 from August 20th-December 5th Class-E Tues,Thurs 8-9 from September 15th-January 15th Event-F Thursday, November 8th, 12:30-3 I could have 1 table that had Name, Start_time, Stop_time and have one entry for every single class and event. That is a simple way to design the table, but it seems like there's lots of needless duplication. There are going to be 50+ entries for Class-A, one for each day it meets. It seems like there ought to be a way to use something like the ical recurring event format to set things up so that there's 1 entry for each event and that includes the recurrence. The only other way I could think of to do it would be to duplicate the cron format and have a table like this: Name, start_day, start_datetime, stop_day, stop_datetime, 'Class-A', '1,3,5','2007-08-20 08:00:00', '2007-12-05 09:00:00' 'Class-D', '3,4,5','2007-08-20 13:00:00', '2007-12-05 14:00:00' And then parse everything, but that seems resource intensive too. Any suggestions for the best way to handle this? Thanks, Bob ~~~ Many things went on at Unseen University and, regrettably, teaching had to be one of them. The faculty had long ago confronted this fact and had perfected various devices for avoiding it. But this was perfectly all right because, to be fair, so had the students. Terry Pratchett Interesting Times ~~~ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Well, assume a higher level abstraction that does not give clients to
that abstraction access to the raw connection. It only has methods like
update, search, commit, or rollback. What the connection is doing is a
kind of implementation detail.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 18, 2007 10:00 AM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
I realize that wasn't the question, but it does seem like a lot of
trouble to get the equivalent of setAutoCommit(true);
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
Sure, but that wasn't really the question.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:56 PM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
While it is functionally equivalent I wonder if it the code paths
taken are the same. I suppose for both commit and rollback mysql
would
have to look for any pending work, if there were none both would do
nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like
to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act
the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only
selects.
Any difference in performance between calling rollback or commit?
I know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave
open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards
the read view. But I just tested that, and both commands start a
new transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far
as
I know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
While it is functionally equivalent I wonder if it the code paths taken
are the same. I suppose for both commit and rollback mysql would have to
look for any pending work, if there were none both would do nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards the
read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as I
know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rollback on a Transaction with No Updates
Is there any difference between calling rollback or commit on a transaction that did not alter data? For example, not a read-only transaction but a transaction that only performed read-only selects. Any difference in performance between calling rollback or commit? I know they are functionally the same at the high level. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Rollback on a Transaction with No Updates
Sure, but that wasn't really the question.
-Original Message-
From: Michael Dykman [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:56 PM
To: Robert DiFalco
Cc: Baron Schwartz; mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
If your transaction are only 1 query deep, why use them at all? An
individual query is already atomic, regardless of table type/server
mode.
- michael dkyman
On 9/17/07, Robert DiFalco [EMAIL PROTECTED] wrote:
While it is functionally equivalent I wonder if it the code paths
taken are the same. I suppose for both commit and rollback mysql would
have to look for any pending work, if there were none both would do
nothing.
That's what makes me think that there is probably no performance
difference between the two. I ask this because my programmers like to
do
this:
con = ...
try
{
queryOnlyWith( con );
}
finally
{
con.rollback();
}
And I wanted to make sure that this would perform the same and act the
same as issuing a commit (unless there was an exception but I'm not
analyzing that case).
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Monday, September 17, 2007 2:36 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Rollback on a Transaction with No Updates
Robert DiFalco wrote:
Is there any difference between calling rollback or commit on a
transaction that did not alter data? For example, not a read-only
transaction but a transaction that only performed read-only selects.
Any difference in performance between calling rollback or commit? I
know they are functionally the same at the high level.
The only thing I could think of was possibly rollback would leave open
transaction and its read view if you are running in REPEATABLE READ
isolation mode, whereas commit begins a new transaction and discards
the read view. But I just tested that, and both commands start a new
transaction and discard the read view.
That's a long way of saying they are functionally equivalent as far as
I know, as long as there are no changes to discard.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
- michael dykman
- [EMAIL PROTECTED]
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating buddy list. request for help
hi there,
I have a rather basic problem I would be glad to get some help:
What I intend to do is:
- create a person-list
- create a buddy-list
each entry in the person-list can have 0 to many buddies
- when an entry in the person-list is deleted, I would like to
have correspnding entries in the buddies list removed.
This are my tables:
# -- #
# Add table tblUser#
# -- #
CREATE TABLE tblUser
(
userDBID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(200)
)
ENGINE = InnoDB;
# -- #
# Add table tblBuddies #
# -- #
CREATE TABLE tblBuddies (
userDBID INTEGER,
buddyDBID INTEGER,
CONSTRAINT PK_tblBuddies PRIMARY KEY (userDBID, buddyDBID)
)
ENGINE = InnoDB;
CREATE INDEX userDBID_buddyDBID ON tblBuddies (userDBID, buddyDBID);
ALTER TABLE tblBuddies ADD CONSTRAINT tblBuddies_parent
FOREIGN KEY (userDBID) REFERENCES tblUser (userDBID);
ALTER TABLE tblBuddies ADD CONSTRAINT tblBuddies_buddy
FOREIGN KEY (buddyDBID) REFERENCES tblUser (userDBID) ON DELETE CASCADE;
When I execute the following statement:
INSERT INTO tblBuddies (userDBID,buddyDBID) VALUES ('test user', 'test user
2');
I get the the error:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails
(`adhoco_vpn_mapper_test/tblBuddies`, CONSTRAINT `tblBuddies_parent` FOREIGN
KEY (`userDBID`) REFERENCES `tblUser`
(`userDBID`))
I would be very gratefull, if somebody could help me getting this straigth (
and point me to my missconseptions)
thanks
robert
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating buddy list. request for help
Martijn Tonies schrieb:
Hi,
I have a rather basic problem I would be glad to get some help:
What I intend to do is:
- create a person-list
- create a buddy-list
each entry in the person-list can have 0 to many buddies
- when an entry in the person-list is deleted, I would like to
have correspnding entries in the buddies list removed.
This are my tables:
CREATE TABLE tblUser
(
userDBID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
description VARCHAR(200)
)
ENGINE = InnoDB;
CREATE TABLE tblBuddies (
userDBID INTEGER,
buddyDBID INTEGER,
CONSTRAINT PK_tblBuddies PRIMARY KEY (userDBID, buddyDBID)
)
ENGINE = InnoDB;
CREATE INDEX userDBID_buddyDBID ON tblBuddies (userDBID, buddyDBID);
ALTER TABLE tblBuddies ADD CONSTRAINT tblBuddies_parent
FOREIGN KEY (userDBID) REFERENCES tblUser (userDBID);
ALTER TABLE tblBuddies ADD CONSTRAINT tblBuddies_buddy
FOREIGN KEY (buddyDBID) REFERENCES tblUser (userDBID) ON DELETE
CASCADE;
When I execute the following statement:
INSERT INTO tblBuddies (userDBID,buddyDBID) VALUES ('test user', 'test
user 2');
You are inserting string values, not ID (integer) values. Any reason for
that?
yes! blunt stupidity!
(the insert was actually created programmatically, but I should have
seen it myself)
thanks
robert
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query examining 10 Million Rows, please help !!!
Could also be the DISTINCT processing depending on the number of dups and the fields in the result set that must be sorted to perform the distinct operation. Normally if there were a lot of dupes I would suggest a sub-query but that is not a great option for MySQL. -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 20, 2007 10:23 AM To: Brent Baisley Cc: mysql@lists.mysql.com Subject: Re: Slow query examining 10 Million Rows, please help !!! Yes I already did try adding an index on tag, but as you said it didn't work as its using the primary key from the freetags table for the join , anyway I will try adding an index on object_type, and see if that helps ... Thanks Kishore Jalleda http://kjalleda.googlepages.com On 6/20/07, Brent Baisley [EMAIL PROTECTED] wrote: As Dan mentioned, you're searching on the 'tag' field which has no index. But since that field is in the table you're joining on, adding an index on it might not help. You actually searching on the tag_id in the join field, not the 'tag'. Add an index on 'object_type' in the freetagged_objects table since you're searching on object_type=1. You're doing a full table scan on that table as indicated my the explain. On Jun 19, 2007, at 6:20 PM, Kishore Jalleda wrote: Hi everybody, we have this super slow query which is going through more than 10 million rows to retrieve results, here is the query and other information, I tried a few things to make this faster , but failed , so any help from you guys in making this faster is greatly appreciated # Query_time: 10 Lock_time: 0 Rows_sent: 1 Rows_examined: 11863498 SELECT DISTINCT object_id FROM freetagged_objects INNER JOIN freetags ON (tag_id = id) WHERE tag = 'shot' AND object_type = 1 ORDER BY object_id ASC LIMIT 0, 10 explain gives the following output ++-+++--- +-+-+-- +-+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra| ++-+++--- +-+-+-- +-+--+ | 1 | SIMPLE | freetagged_objects | ALL| PRIMARY | NULL |NULL | NULL | 9079381 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | freetags | eq_ref | PRIMARY | PRIMARY | 4 | osCommerce.freetagged_objects.tag_id | 1 | Using where; Distinct| ++-+++--- +-+-+-- +-+--+ mysql show create table freetagged_objects; | freetagged_objects | CREATE TABLE `freetagged_objects` ( `tag_id` int(11) unsigned NOT NULL default '0', `tagger_id` int(11) unsigned NOT NULL default '0', `object_id` int(11) unsigned NOT NULL default '0', `tagged_on` datetime NOT NULL default '-00-00 00:00:00', `object_type` int(11) NOT NULL default '0', PRIMARY KEY (`tag_id`,`tagger_id`,`object_id`), KEY `tagger_id_index` (`tagger_id`), KEY `object_id_tagger_id_index` (`object_id`,`tagger_id`), KEY `object_id_tag_id_index` (`object_id`,`tag_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | mysql show create table freetags; | freetags | CREATE TABLE `freetags` ( `id` int(11) unsigned NOT NULL auto_increment, `tag` varchar(30) NOT NULL default '', `raw_tag` varchar(50) NOT NULL default '', `suppress` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), KEY `raw_tag` (`raw_tag`(10)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | Freetags table has like a million rows in it MySQL version 4.1.11 , server has 16GB RAM .. Kishore Jalleda http://kjalleda.googlepages.com/mysqlprojects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INNER versus OUTER
Any thoughts? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Monday, June 18, 2007 10:10 AM To: mysql@lists.mysql.com Subject: INNER versus OUTER I'm using the latest MySQL with InnoDB and something is happening I don't understand. I am going to try this first by paraphrasing my queries since they are complex and have some proprietary info in them. It seems that when a LEFT OUTER or an INNER join will produce the same result and other joins in the query are LEFT OUTER that I get a better query plan if I make the JOIN that could go either way a LEFT OUTER instead of an INNER. For example consider the following query (everything is indexed)... SELECT Field_List FROM Header JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; This seems to run much slower than the following: SELECT Field_List FROM Header LEFT OUTER JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; Looking at the query plan, the first query puts the HeaderNames table first and Uses a temporary and a filesort. The second query examines a few more rows but it puts the Header file first and uses a Where and index for everything. Could someone explain to me why this is? Does it have something to do with the ORDER BY? R. -- 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]
INNER versus OUTER
I'm using the latest MySQL with InnoDB and something is happening I don't understand. I am going to try this first by paraphrasing my queries since they are complex and have some proprietary info in them. It seems that when a LEFT OUTER or an INNER join will produce the same result and other joins in the query are LEFT OUTER that I get a better query plan if I make the JOIN that could go either way a LEFT OUTER instead of an INNER. For example consider the following query (everything is indexed)... SELECT Field_List FROM Header JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; This seems to run much slower than the following: SELECT Field_List FROM Header LEFT OUTER JOIN ON HeaderNames ON Header.nameID = HeaderNames.ID LEFT OUTER JOIN Items LastItem ON Header.lastItemID = LastItem.ID WHERE Header.nodeID = 20 ORDER BY Header.ruleID; Looking at the query plan, the first query puts the HeaderNames table first and Uses a temporary and a filesort. The second query examines a few more rows but it puts the Header file first and uses a Where and index for everything. Could someone explain to me why this is? Does it have something to do with the ORDER BY? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need confirmation: Subselects are broken with regards to index usage?
Lets hope something is done about it soon, not all sub queries SHOULD be re-written as joins. Generally joins that result in the need to use DISTINCT are better off re-written as IN OR EXISTS sub queries. In any event, I have a lot of very complex queries that work great for our other database backends but will need to be rewritten for MySQL. Does anyone know of a tool that will just take a standard SQL select query string and rewrite it (with the option of rewriting subqueries to joins). I know such a tool can't be perfect but it could help out with the heavy lifting leaving only some tuning and debugging. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 2:35 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Need confirmation: Subselects are broken with regards to index usage? Hi Robert, The way non-correlated subqueries are sometimes optimized into correlated ones and then executed for each row in the outer table is a well-known MySQL deficiency, yes. I would not really look for it to be fixed soon, though it's been in progress for a while. The version in which it gets fixed is still likely a long way from GA. But maybe a MySQL employee can give better info on that. In the meantime, we are all forced to find alternative ways to write such queries :-) Baron Robert DiFalco wrote: Ok, so I guess it is more complicated than that. This query which has 5M records that match its criteria returns instantly: SELECT ELEMS.id FROM ELEMS WHERE (( ELEMS.nodeID IN ( SELECT link.childID FROM link JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1 LIMIT 0,100; Now if I change the ancestorId criteria to a node group that does not exist the query takes a very long time. Btw, it also looks like this is being optimized into a less efficient EXISTS query. Anyway, the join version doesn't have the same problem, it is fast if it is searching for a conditions that has results or one that has none. Note that the JOIN version requires a SELECT in case a node has multiple ancestors. SELECT DISTINCT ELEMS.id FROM ELEMS JOIN link ON ELEMS.nodeID = link.childID JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1) LIMIT 0,100; Anyone have any ideas why this is the case? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 1:11 PM To: mysql@lists.mysql.com Subject: Need confirmation: Subselects are broken with regards to index usage? I think I'm discovering that sub-selects in MySQL are broken. Is that true? It seems like you cannot have a sub-select without doing a table scan -- even for a constant IN expression -- this because it gets re-written as an EXISTS that executes for each row. Is that true? Forcing an index doesn't even seem to help. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need confirmation: Subselects are broken with regards to index usage?
I think I'm discovering that sub-selects in MySQL are broken. Is that true? It seems like you cannot have a sub-select without doing a table scan -- even for a constant IN expression -- this because it gets re-written as an EXISTS that executes for each row. Is that true? Forcing an index doesn't even seem to help. R.
RE: Need confirmation: Subselects are broken with regards to index usage?
Ok, so I guess it is more complicated than that. This query which has 5M records that match its criteria returns instantly: SELECT ELEMS.id FROM ELEMS WHERE (( ELEMS.nodeID IN ( SELECT link.childID FROM link JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1 LIMIT 0,100; Now if I change the ancestorId criteria to a node group that does not exist the query takes a very long time. Btw, it also looks like this is being optimized into a less efficient EXISTS query. Anyway, the join version doesn't have the same problem, it is fast if it is searching for a conditions that has results or one that has none. Note that the JOIN version requires a SELECT in case a node has multiple ancestors. SELECT DISTINCT ELEMS.id FROM ELEMS JOIN link ON ELEMS.nodeID = link.childID JOIN path ON link.parentID=path.decendantId WHERE (path.ancestorId = 1) LIMIT 0,100; Anyone have any ideas why this is the case? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 1:11 PM To: mysql@lists.mysql.com Subject: Need confirmation: Subselects are broken with regards to index usage? I think I'm discovering that sub-selects in MySQL are broken. Is that true? It seems like you cannot have a sub-select without doing a table scan -- even for a constant IN expression -- this because it gets re-written as an EXISTS that executes for each row. Is that true? Forcing an index doesn't even seem to help. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date format question
Assign all dates to have a day of 01 Store in a date field, use DATE_FORMAT to just extract the MM and . As you don't have the real day information it doesn't matter what day is used, so long as it present in all months. Hope this helps Robert Gehrig Webmaster at www.gdbarri.com e-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: leading the technological bandwagon
I ran into this before. They had a system of spreadsheets that worked for them and they didn't want that to go away. What I did was installed them a MySql server on a RHEL box, and rework all the spreadsheets that had special links to point to the database. My argument to them was this allowed for one central point information instead the potential for the same information to repeated (and possibly changed) from another source. This also gave them an eaisier backup solution. They liked it. Nowadays, Most business' have learned if it works for you let it alone. I have seen a $4-million business printing out mailing labels on a Win98SE machine. Why? Because it works perfectly for what they want, why change it. They print out over 7 million mailing labels a year. They did upgrade the printer. -- robert Stephen Cook wrote: You should send that one to The Daily WTF... http://thedailywtf.com/ Jay Blanchard wrote: [snip] I recently last week Had and experience with an Very small Company, where as they had around 15 Machines all hooked Star topology and a central iis ASP Web server that only showed the date, and a few small utilities when addressed To it, now there entire Operation was based upon Excel, everything not joking I mean everything, was a file share to an Folder (around 90 Folders) in which they had probably 200 + excel sheets in each one u name something u need to do in business I guarantee there was a excel sheet for it, repetitive sheets for Different Operations, all Sheets linked to one another thru references in excel, excel97 Mind u, I was there on a Consulting call, to inform them of a better way to update / Operate there business They ended up saying they liked there Current way better it makes more sence to them and Only wanted me to link 10 more sheets to what they already had and add a few more file shares, of Course I refused this Project and walked out Laughing my ass off in the car [/snip] Seriously? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
When are subselects faster than Joins?
Are there any hard and fast rules for this? If someone has already compiled a list I'd love to see it. For example: * When a subselect will eliminate duplicates a join might introduce. Change: SELECT DISTINCT Acl.* FROM Acl JOIN Link ON Link.childID = Acl.ID JOIN Paths ON Link.parentID = Path.descendantID WHERE Path.ancestorID = '12345'; To: SELECT Acl.* FROM Acl WHERE Acl.ID IN ( SELECT Link.childID FROM Link JOIN Paths ON Link.parentID = Path.descendantID WHERE Path.ancestorID = '12345' ); When table Acl might have many duplicates and VARCHAR fields. Also, lets take something simple. For example I have a one to many table of some kind of Objects to Strings. Lets call the tables Food and Category. Would the subselect version below be faster than the JOIN version or just the same? SELECT Food.* FROM Food JOIN FoodCategory ON Food.categoryID = FoodCategory.ID WHERE FoodCategory.name LIKE '%rui%'; Or: SELECT Food.* FROM Food WHERE Food.categoryID IN ( SELECT FoodCategory.ID FROM FoodCategory WHERE FoodCategory.name LIKE '%rui%' ); Or in this case will the JOIN as a rule be faster? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Analysis Tool
I'm looking for a tool that could parse a boat load of various queries using complex joins and subqueries, analyze each, and print out the optimal covering indices that could be used on each table for each query. It would have to take into consideration stuff like a WHERE expression that could not use an index even if the column was indexable. I suppose it would not be difficult to build one but I was hoping that someone knew of a tool out there that already did such a thing? The problem with EXPLAIN is that it will print out how the query will be executed based on existing indices, not the optimal indices that may or may not exist yet. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Analysis Tool
Ah well, wishful thinking I guess. I guess I thought that at least the recommendations would be deterministic. Fwiw, there would still be human judgment in the end to decide which covering/index recommendations to actually create. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Friday, November 03, 2006 1:40 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Analysis Tool H. I actually think this would be somewhat difficult to write, Robert. Parsing the queries would be complex enough given the different ways one can construct SQL. Also it would have to examine the cardinality of the data in each column to determine if indexing would be worthwhile vs. a table scan... and then there's the human judgment that needs to be made as far as which queries actually need optimizing vs those that don't, or that need an index less at any rate. The ones that get run several times a second vs once an hour or once a day ... My two cents' worth anyway. Dan On 11/3/06, Robert DiFalco [EMAIL PROTECTED] wrote: I'm looking for a tool that could parse a boat load of various queries using complex joins and subqueries, analyze each, and print out the optimal covering indices that could be used on each table for each query. It would have to take into consideration stuff like a WHERE expression that could not use an index even if the column was indexable. I suppose it would not be difficult to build one but I was hoping that someone knew of a tool out there that already did such a thing? The problem with EXPLAIN is that it will print out how the query will be executed based on existing indices, not the optimal indices that may or may not exist yet. TIA, R. -- 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]
Prefix Indices
I have some long VARCHAR fields that a user will sometimes sort on. Does a prefix index in any way help with sorting or just for lookups? Will it speed up a filesort? I couldn't find this information in How MySQL uses indices. R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: References on Optimizing File Sort
Btw, this is using the InnoDB engine. -Original Message- From: Robert DiFalco Sent: Tuesday, October 17, 2006 9:26 AM To: mysql@lists.mysql.com Subject: References on Optimizing File Sort I have an unavoidable filesort in a very large query. Can someone point me to references for optimizing filesort? I'm assuming this is going to be changes to my.ini or the hardware. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
References on Optimizing File Sort
I have an unavoidable filesort in a very large query. Can someone point me to references for optimizing filesort? I'm assuming this is going to be changes to my.ini or the hardware. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Yup, innodb_locks_unsafe_for_binlog=1 fixes the problem and so does your suggestion of using a JOIN instead of a subselect. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 8:54 AM To: Baron Schwartz; Rick James Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks We'll do some testing with innodb_locks_unsafe_for_binlog but if this fixes the problem then it is a pretty safe assumption that the problem also exists with subqueries in DELETE and UPDATE and not just for that one case of INSERT as the article points out. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Baron Schwartz http://www.xaprb.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: Innodb Locks
Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals
RE: Innodb Locks
Right, as I understand it the query optimizer in 5.2 will simply rewrite these sub selects as joins when possible. -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:45 AM To: Robert DiFalco; 'Baron Schwartz' Cc: 'Rick James'; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks It probably uses a single lock to handle a JOIN, and two locks to handle a sub-SELECT. I doubt that it helps, but if I'm right it will change what you see when you poking around. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 2:42 PM To: Baron Schwartz Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Innodb Locks Then I guess I am not understanding why re-writing the statement as a JOIN alleviates that need. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 10, 2006 11:35 AM To: Robert DiFalco Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks It's not a bug in InnoDB. There are far more knowledgeable people than I on this list, but it should get a share-mode lock on anything it selects from, otherwise there might be inconsistencies as it tries to serialize different transactions into the binary log for replication. If the statement reads different values from Elems on the master and slave, there'll be different results. So locking is necessary for replication to work right. If I'm glossing over the finer points too much, it's because I don't know them. Someone else can surely correct me :-) Baron Robert DiFalco wrote: I think what is strange to me is that InnoDB is locking on the subquery table at all. Here's another example: DELETE FROM Vers WHERE ( Vers.elementID IN ( SELECT Elems.ID FROM Elems WHERE (Elems.nodeID = ?))) Disregarding whether performance would be better or worse with a JOIN, what I find odd is that this DELETE statement on Vers seems to be putting locks on Elems. Might this be a bug in InnoDB? Innotop has this to say: Locks Held and Waited For Txn What Mode DB Tbl Index Heap Special Ins Intent 1 waits_for Xte elems PRIMARY2 rec but not gap 0 Not that Txn 1 is an UPDATE on a single row of the ELEMS table and it is waiting for the LOCK from the above DELETE FROM Vers to be released. I'm not sure why the DELETE statement is locking the subquery table ELEMS which is simply being queried. Do I *really* need to change all of these to write the subquery to a temporary table in order to gain better concurrency? R. -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:39 PM To: Rick James Cc: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Innodb Locks There is a detailed write-up on how locking works in the manual: http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html If you are not doing replication, you might check out innodb_locks_unsafe_for_binlog as mentioned in http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html. Peter Z also wrote an article on this: http://www.mysqlperformanceblog.com/category/replication/ You may get better performance from using a JOIN instead of an IN() subquery. You will have to test. Sometimes it is much better, sometimes worse. Usually better in my experience. Making the long-running query as short as possible is probably a good idea. Maybe you can break it up into several queries so it doesn't try to lock so many rows at once. There could be many other approaches too, it just depends on your needs and data. Without altering how locks are handled with startup options, the temporary table approach will avoid the locks only if you COMMIT after the CREATE... SELECT. The other subquery approach will not avoid them. I'm not sure if I should be replying to both the 'internals' and 'lists' mailing lists, since this was cross-posted. Feel free to give me guidance :-) Baron Rick James wrote: Can't answer your question directly. But I wonder if this would trick it into avoiding the lock: UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM SomeTable); And the real workaround would be CREATE TEMPORARY TABLE t SELECT id ...; UPDATE AnotherTable SET... WHERE id IN (SELECT id FROM t); -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 9:26 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED
RE: Glitch in Query Optimizer
Anyone here know enough about how the optimizer works to explain why it is use the less optimal index in this case? -Original Message- From: Christian Hammers [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:54 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Glitch in Query Optimizer On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? Search the docs for FORCE INDEX () and IGNORE INDEX () if you don't want to rely on MySQL's cleverness. bye, -christian- -- Christian Hammers WESTEND GmbH | Internet-Business-Provider Technik CISCO Systems Partner - Authorized Reseller Lütticher Straße 10 Tel 0241/701333-11 [EMAIL PROTECTED]D-52064 Aachen Fax 0241/911879 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb Locks
Any thoughts on this? Should SomeTable be locked when performing the UPDATE on AnotherTable? --- Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- 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: Innodb Locks
No foreign key relationships. If I pull it into a temp table or a separate query that I then iterate through for all the updates on AnotherTable, then all works well. Odd. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 11:09 AM To: mysql@lists.mysql.com Subject: Re: Innodb Locks On 10/2/06, Robert DiFalco wrote: Is there a detailed source for when innodb creates row or table locks? The sourcecode. I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? It shouldn't from what you have described here. But might there be foreign key relation sbetween both tables? Jochem -- 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]
Glitch in Query Optimizer
Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table_cache not read
All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Glitch in Query Optimizer
Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- 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: RE: Glitch in Query Optimizer
Thanks, I had seen that but I don't have a lot of flexibility for adding database specific extensions on a query by query basis. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:30 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: RE: Glitch in Query Optimizer Have you tried using the USE/IGNORE/FORCE INDEX optimizer hints? http://dev.mysql.com/doc/refman/5.0/en/join.html Sometimes, MySQL's optimize just doesn't make the best choice. Somewhat rare in my experience but it happens. HTH, Dan On 10/3/06, Robert DiFalco [EMAIL PROTECTED] wrote: Scratch that, the only way to have the optimizer choose the correct index is to remove all compound indices that start with NodeID or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer Here's an odd one. I have a table called Elements and another table called ElementNames. The ElementNames table has a unique ID and a VARCHAR display name. The Elements table has a ElementName.ID, a node ID, a rule ID and some other stuff. I have an index on the NameID, NodeID, and RuleID. I have another index on just the NodeID and RuleID. For the following query, MySQL normally uses the NameID, NodeID, RuleID index. SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? ORDER BY Enames.displayName LIMIT ?, ?; However, once I have more than about 50K elements, the query switches over to using the index on NodeID and RuleID (which results in a very slow query). I can *force* it to use the correct index by adding this to the query: SELECT * FROM Elements JOIN Enames ON Elements.nameID = Enames.ID WHERE Elements.nodeID = ? AND Elements.nameID 0 ORDER BY Enames.displayName LIMIT ?, ?; It is impossible for nameID to be zero so it doesn't effect the outcome of the query, it just forces it to use the correct index. Any idea why this is happening? Is this a bug in the query optimizer? R. -- 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: table_cache not read
Dan, Thanks for the reply... I wondered if this might be the case myself. However, I made a change the the variable query_cache_size in the same ini and it registered correctly after the service restart. Or, are you saying MySQL might be reading from multiple ini files? Well, either way in the mean time I am going to search for additional ini files that may contain the offending variable value. Thanks! Robert Coggins Dan Buettner wrote: Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- 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: table_cache not read
Well, I scoured the HDDs and I was unable to find additional my.ini files. Any other thoughts! Thanks again for your help! Robert Coggins wrote: Dan, Thanks for the reply... I wondered if this might be the case myself. However, I made a change the the variable query_cache_size in the same ini and it registered correctly after the service restart. Or, are you saying MySQL might be reading from multiple ini files? Well, either way in the mean time I am going to search for additional ini files that may contain the offending variable value. Thanks! Robert Coggins Dan Buettner wrote: Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- 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: table_cache not read
Well, I scoured the HDDs and I was unable to find additional my.ini files. Any other thoughts! Thanks again for your help! Robert Coggins wrote: Dan, Thanks for the reply... I wondered if this might be the case myself. However, I made a change the the variable query_cache_size in the same ini and it registered correctly after the service restart. Or, are you saying MySQL might be reading from multiple ini files? Well, either way in the mean time I am going to search for additional ini files that may contain the offending variable value. Thanks! Robert Coggins Dan Buettner wrote: Robert, I recall a similar issue on Windows a couple of months ago. The problem there was the presence of multiple ini files scattered about. Check your system for multiple ini files and consolidate remove the extras, perhaps. HTH, Dan On 10/3/06, Robert Coggins [EMAIL PROTECTED] wrote: All, I asked this in the win32 list but not getting much of a response. Thought I would ask here since there seems to be more traffic. I am currently using a win/mysql solution. I am running into a problem where the table cache is not registering from the ini when the server starts. Below are my versions... Win: Windows 2003 Enterprise MySQL: 5.0.18 In my.ini the table_cache is set to 3020 (I am not sure why as I did not originally configure.) But when I query the variable value (show variables like 'table_cache';) it shows 264. If I correct the value (set global table_cache=120;) the value will stays until the service is restarted. This is happening on 2 Win2K3 Ent servers. Although on the other server the table_cache in the ini is 6020 but displays 64. I also understand 64 is the default. My question is two-fold. 1. Why would this happen? and 2. How would I correct this so that I do not have to reset the table_cache every time the service is restarted? Thank you all for your feedback! Robert Coggins -- 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]
Innodb Locks
Is there a detailed source for when innodb creates row or table locks? I have a situation where one thread is performing this in one transaction: UPDATE SomeTable SET WHERE SomeTable.id = N; This is invoked after another thread has kicked off this long running query in another transaction: UPDATE AnotherTable SET ... WHERE EXISTS( SELECT null FROM SomeTable WHERE SomeTable.id = AnotherTable.id ); Would this create a conflicting lock? I am getting Lock wait timeout exceeded on SomeTable fro the UPDATE to SomeTable. TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AW: Count of children
For us the querying of trees is more important than the speed of writing them. So each time we add a child or change a parent or whatever, we trigger a stored procedure that updates a paths table. Then our query for children is pretty simple: SELECT Node.* FROM Node JOIN Paths P ON Node.id = Paths.descendantID WHERE P.ancestorID = pID; So to perform a count I can just do this part without the join: SELECT COUNT(*) FROM Paths WHERE Paths.ancestorID = pID; Our system is structured using a sort of GoF composite parent so some nodes can be Groups and others can only be leaves. If I want to return children nodes that are not leaves I can do this: SELECT Node.* FROM Node JOIN Paths P ON Node.id = Paths.childID WHERE P.ancestorID = pID AND Node.isLeaf = false; The Paths table is pretty simple; just descendantID and ancestorID columns that make up a composite primary key. Some people also add another column called depth as this can make recalculating the tree easier. R. From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 27, 2006 8:20 AM To: André Hänsel Cc: mysql@lists.mysql.com Subject: Re: AW: Count of children André With an edge list, the solution entails recursion, so you need either an sproc or application proc. With a nested sets model, the count is dead simple. If the id of the target row is N, and the left right node columns are named leftedge and rightedge, the query is SELECT COUNT(t2.id) FROM tbl t1 JOIN tbl t2 ON t2.leftedge t1.leftedge AND t2.leftedge t1.rightedge WHERE t1.id=N; PB - André Hänsel wrote: I will use any model that is suitable. ;) I am somewhat familiar with both tree models but I can't come up with a method to get the count of all sub- and sub-sub-nodes in either of them. -Ursprüngliche Nachricht- Von: Peter Brawley [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 27. September 2006 16:49 An: André Hänsel Cc: mysql@lists.mysql.com Betreff: Re: Count of children André, I want the count of all sub-entries for a specific entry. Depends on the model you are using--edge list or nested sets? PB - André Hänsel wrote: I have a table with id and parent_id. I want the count of all sub-entries for a specific entry. I found several documents about working with graphs/trees in MySQL but I could not find a solution for my problem. I can imagine two possibilities, but one is memory intensive and the other one creates load on updates. The first is, that I select all entries and then use a procedural language to determine recursively whether an node is a sub-node of the specific node. The second is, that I store the sub-node count with each node and when I do an insert, I walk the tree upwards and increment the node-counts. Is there a smart solution/best practice for my problem? Now I can't think of another sentence starting with an i. ;-) Best regards, André -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.407 / Virus Database: 268.12.9/457 - Release Date: 9/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating two fields from an aggregate query
I have two tables that are related: Parent LONG id LONG childCount LONG maxChildAge ... Child LONG parentId LONG age ... There can be thousands of parents and millions of children, that is why I have denormalized childCount and maxChildAge. The values are too expensive to calculate each time the data is viewed so I update these values each time a Child is added, removed, or modified. I currently have to update the Parent table with two queries like so: SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = x; UPDATE Parent SET maxChildAge = MAX, childCount = COUNT WHERE id = x; Worse yet I might be updating the stats for several hundred Parents at a time, so I have to loop through the above where x is the current Parent.id in the batch. What I would like to do is something like the following (made up syntax): UPDATE Parent SET maxChildAge AND childCount = COUNT WITH ( SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = Parent.id ) WHERE id IN ( set_of_parents_to_update ); Any suggestions? TIA R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Updating two fields from an aggregate query
Hdo you think this would perform better than simply using the two queries? I wonder if the overhead associated with the ON DUPLICATE KEY exception and handler might not outweigh the benefits of a single query. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 7:15 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Updating two fields from an aggregate query Robert, you might give insert ... select ... on duplicate key update a try: http://dev.mysql.com/doc/refman/5.0/en/insert-select.html something like this (untested): INSERT INTO parent (id, maxChildAge, childCount) SELECT parentid, MAX(age) as maxAge, COUNT(*) as ct FROM child WHERE parentid IN (1, 2, 3, 4) ON DUPLICATE KEY UPDATE maxChildAge=maxAge, childCount=ct That seems like it ought to work as long as the id column in the parent table is a unique key. One consideration is that if you are writing this query programmatically and using a long list of parentid values in the IN clause, the SQL could get pretty long, perhaps too long for the default value of MAX_ALLOWED_PACKET. You can adjust that up pretty easily, but bear in mind you need to adjust it for both client and server. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html HTH, Dan On 9/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: I have two tables that are related: Parent LONG id LONG childCount LONG maxChildAge ... Child LONG parentId LONG age ... There can be thousands of parents and millions of children, that is why I have denormalized childCount and maxChildAge. The values are too expensive to calculate each time the data is viewed so I update these values each time a Child is added, removed, or modified. I currently have to update the Parent table with two queries like so: SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = x; UPDATE Parent SET maxChildAge = MAX, childCount = COUNT WHERE id = x; Worse yet I might be updating the stats for several hundred Parents at a time, so I have to loop through the above where x is the current Parent.id in the batch. What I would like to do is something like the following (made up syntax): UPDATE Parent SET maxChildAge AND childCount = COUNT WITH ( SELECT MAX( Child.age ), COUNT(*) FROM Child WHERE parentID = Parent.id ) WHERE id IN ( set_of_parents_to_update ); Any suggestions? TIA R. -- 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: Complex SQL for multiple joins
Something else you may or may not want to consider. You may want to have
both users and user-groups be principles. Something like the following:
Principle (ID, NAME, PERMS)
User (P_ID, PASSWORD, ... )
UserGroup (P_ID, ... )
PrincipleLink (PID, CID) - Many to Many (parent, child)
Also consider a row in the Links table for each user refering to itself.
Then you can do stuff like the following pretty easily:
// search for a user permission (or the groups they belong to)
EXISTS(
SELECT null
FROM Principle P
JOIN PrincipleLink L ON P.ID = L.PID
WHERE
( P.PERMS (1 + 2) = (1 + 2) )
AND L.CID = userID )
You can then simply have User and UserGroup store information that is
not directly related to permissions.
R.
-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 31, 2006 7:59 AM
To: Brent Baisley
Cc: Stephen Orr; mysql@lists.mysql.com
Subject: Re: Complex SQL for multiple joins
Also, depending on the number of permissions you are tracking, you could
use a single INT field and do bitwise ORing in your application to
determine permission checks...
Though I usually don't recommend denormalizing the schema, this is one
scenario that would potentially make life a bit easier. Instead of
having six tables, you would have 3:
Users
UserGroups
User2Group (stores many-to-many relationship)
Both the fact tables (Users and UserGroups) would have an INT UNSIGNED
field called, say, permission_flags which could contain up to 32 flag
values for various permissions.
This is a very compact and efficienct way of *storing* permissions.
Retrieving sets of users/groups based on a single flag would be easy,
though an index would not be used.
For instance, imagine you have set bit 1 to mean has read access.
To find all users with read access, you would do:
SELECT * FROM Users WHERE permissions 1;
Let's say you have another permission for write access at the second bit
and you want to see all users with both read and write permission, you'd
do:
SELECT * FROM Users WHERE permissions (1 + 2) = (1 + 2);
the third bit would be 2^3 or 4, etc...:
SELECT * FROM Users WHERE permissions (1 + 2 + 4) = (1 + 2 + 4);
Additionally, what is nice about this type of organization is that you
can store the user's permissions in session memory and reference the
permissions without having to go to the database by using the same
bitwise operations in your application code.
For instance, in PHP you would write something like:
?php
define('CAN_READ', 1 0);
define('CAN_WRITE', 1 1);
define('CAN_DO_OTHER', 1 2);
$permissions = $my_session_vars['permissions']; $can_read = $permissions
CAN_READ; $can_write = $permissions CAN_WRITE;
...
?
Some more notes:
* Use a 64-bit BIGINT datatype if you need 33-64 flags for permissions
* You can use a SET datatype as well in this manner
* Make sure you understand bitwise operations
Cheers,
Jay
On Thu, 2006-08-31 at 09:39 -0400, Brent Baisley wrote:
You should be able to do it 1 query, I can think of two ways. I'm not
sure which one will run faster. I use table aliasing to keep the lines
shorter.
The first possibility would have user permissions in one column and
group permissions in another:
SELECT users.userid,perm_u.permissions,perm_g.permissions
FROM users
LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid LEFT
JOIN permissions AS perm_u ON u_p.permid=perm_u.permid LEFT JOIN
users_usergroups AS u_ug ON users.userid=u_ug.userid LEFT JOIN
usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid LEFT JOIN
permissions AS perm_g ON ug_p.permid=perm_g.permid WHERE
users.userid=#
But if you want to just have one column of permissions, you'll need to
use UNIONs. I think this is more readable.
SELECT users.userid AS userid,permissions AS permissions FROM users
LEFT JOIN users_permissions AS u_p ON users.userid=u_p.userid LEFT
JOIN permissions ON u_p.permid=permissions.permid WHERE
users.userid=# UNION SELECT users.userid AS userid,permissions FROM
users LEFT JOIN users_usergroups AS u_ug ON users.userid=u_ug.userid
LEFT JOIN usergroups_permissions AS ug_p ON u_ug.groupid=ug_p.groupid
LEFT JOIN permissions AS perm_g ON ug_p.permid=perm_g.permid WHERE
users.userid=#
Either of those should work. I don't know your table structure, so I
can't get too specific with it.
- Original Message -
From: Stephen Orr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Wednesday, August 30, 2006 7:27 PM
Subject: Complex SQL for multiple joins
Hi,
For my current website project I'm developing a fine-grained access
control system.
I have the schema mostly worked out, although it will be having
additional data added to parts of it later.
For the record, I have 6 tables:
users (contains all my individual users) usergroups (contains all
the groups users can belong to) permissions (contains all the things
that the system allows)
Performance: LIMIT 1 with UPDATE
Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance: LIMIT 1 with UPDATE
FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- 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: Performance: LIMIT 1 with UPDATE
Yeah, seems like for a uniquely index column expression that the LIMIT 1 provides no benefit (which seems like a logical conclusion). The performance on a large data set with both approaches is close enough to call equivalent. -Original Message- From: Rick James [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 11:51 AM To: Robert DiFalco; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE Maybe it is the tiny extra time to parse the unnecessary LIMIT 1? -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 10:09 AM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: RE: Performance: LIMIT 1 with UPDATE FWIW, my preliminary testing shows no performance increase for adding LIMIT 1 to the following construct: [ SELECT | UPDATE ] WHERE Table.UniqueID = N In fact, there seems to be a slight performance edge to NOT appending LIMIT 1 to the statement. R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 29, 2006 9:06 AM To: mysql@lists.mysql.com Subject: Performance: LIMIT 1 with UPDATE Does using LIMIT 1 with UPDATE provide a performance improvement when the WHERE condition is on a unique index or primary key? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with INNODB transactions
What connection pool code are you using? My guess is that the problem is in your code somewhere. Either transactions are not being closed (i.e. because of a connection pool flaw maybe?) or you have two threads trying to update the same row at the same time (in which case this would be expected behavior). R. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 9:31 AM To: mysql@lists.mysql.com Subject: Problem with INNODB transactions Hi, I am facing a strange problem with INNODB. My application communicates with mysql server using JDBC. I am using mysql 5.1 version. Even after issuing connection.commit() / connection.rollback() commands, still on the sql side the transactions are not getting closed properly. In our application we never try to acquire locks on the same row from different threads. A request is sent only when the previous transaction is closed by issuing commit or rollback. But still the following error is seen : Lock wait timeout exceeded; try restarting transaction. The transaction isolation level used is READ-COMMITTED. We maintain a database connection pool and try to reuse the connections from the pool instead of trying to close and create the connections everytime. In our application there is a continuous database updates happening at a very high rate. Could you please suggest what could be going wrong. Thanks Prasad The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More query help for user-defined values
No takers? -Original Message- From: Robert DiFalco Sent: Thursday, August 17, 2006 3:03 PM To: Robert DiFalco; mysql@lists.mysql.com Subject: RE: More query help for user-defined values One simple solution is to denormalize a little and take the refTypeID column out of StringValue and add it to the Links table. Then I could have: SELECT P.* FROM Person P LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:43 AM To: mysql@lists.mysql.com Subject: More query help for user-defined values To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) As you can probably tell this wont work because the two values will now show up in two different rows so the AND of the search terms will always fail. Can anyone think of a clean way to do this or do I have to get rid of the joins altogether and do a subquery for each StringValue search term? TIA, R. -- 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: More query help for user-defined values
Given the three tables (Type, Value, and Links), here is another (crazy looking) way to structure the query for the Person table. SELECT P.* FROM Person P WHERE ( EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'foo' ) OR ( NOT EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV2 ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'foo' ) AND EXISTS ( SELECT null FROM StringType ST WHERE ST.ID = 1 AND ST.defaultVal LIKE 'foo' ) ) ) AND ( EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'bar' ) OR ( NOT EXISTS ( SELECT null FROM StringLinks L JOIN StringValue SV2 ON L.ID = SV.ID AND SV.refTypeID = 1 WHERE P.ID = L.parentID AND SV.f_val LIKE 'bar' ) AND EXISTS ( SELECT null FROM StringType ST WHERE ST.ID = 1 AND ST.defaultVal LIKE 'bar' ) ) ) -Original Message- From: Robert DiFalco Sent: Friday, August 18, 2006 12:37 PM To: Robert DiFalco; 'mysql@lists.mysql.com' Subject: RE: More query help for user-defined values No takers? -Original Message- From: Robert DiFalco Sent: Thursday, August 17, 2006 3:03 PM To: Robert DiFalco; mysql@lists.mysql.com Subject: RE: More query help for user-defined values One simple solution is to denormalize a little and take the refTypeID column out of StringValue and add it to the Links table. Then I could have: SELECT P.* FROM Person P LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:43 AM To: mysql@lists.mysql.com Subject: More query help for user-defined values To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2
More query help for user-defined values
To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) As you can probably tell this wont work because the two values will now show up in two different rows so the AND of the search terms will always fail. Can anyone think of a clean way to do this or do I have to get rid of the joins altogether and do a subquery for each StringValue search term? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: More query help for user-defined values
One simple solution is to denormalize a little and take the refTypeID column out of StringValue and add it to the Links table. Then I could have: SELECT P.* FROM Person P LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1 LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2 LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Thursday, August 17, 2006 8:43 AM To: mysql@lists.mysql.com Subject: More query help for user-defined values To support user defined properties I have the following tables: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG parentID LONG typeID VARCHAR val Assume the correct indices are in place. Different string value types can be defined by giving them a new row in StringType. The StringValue.parentID can be a row from any table that has a LONG identity column. If a row in the parent table does not have a corresponding row in StringValue, we want to assume an implicit default value. So to return all the Person rows that have either an explicit StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I can just execute the following query. SELECT P.* FROM Person P LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) The above seems to work fine. I can also get rid of the constant joins on the type table and just do a subquery for the default value, either approach works. Now I want to change things to introduce a ValueLink table so that if 100 rows have the same string value that I don't have to write the string 100 times. So my table structure changes like this: TABLE StringType LONG ID PKEY VARCHAR name ... VARCHAR defaultValue TABLE StringValue LONG ID PKEY // the value unique ID LONG typeID VARCHAR val TABLE StringLinks LONG parentID PKEY LONG valueID PKEY My naive approach to the query was this: SELECT P.* FROM Person P LEFT JOIN StringLinks L ON P.ID = L.parentID LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1 LEFT JOIN StringType ST1 ON ST1.ID = 1 LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2 LEFT JOIN StringType ST2 ON ST2.ID = 2 WHERE ( SV1.f_val LIKE 'foo' OR ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) ) AND ( SV2.f_val LIKE 'bar' OR ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) ) As you can probably tell this wont work because the two values will now show up in two different rows so the AND of the search terms will always fail. Can anyone think of a clean way to do this or do I have to get rid of the joins altogether and do a subquery for each StringValue search term? TIA, R. -- 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: Can a row be refered using row number?
Why would you want to do this? As data moves around within the table the updates will be in error. Wouldn't it be easier to assign a unique key to each row, search for the key or unique set of information and update the resulting row? Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, August 10, 2006 4:43 AM To: mysql@lists.mysql.com Subject: Can a row be refered using row number? Hi All, Is there any way to refer a row based on the row number or row count? I just the effect as below... E.g. UPDATE TBL_NAME SET COL_NAME = xyz WHERE ROW_NO=4 Or SELECT COL_NAME FROM TBL_NAME WHERE ROW_NO=5 Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help for Loosely Couple Properties
So the question is what if I have multiple conditions say COLOR LIKE 'Blue' [AND|OR] NAME LIKE 'Fred'. Will I have to create a JOIN for each? SELECT * FROM T LEFT JOIN StringVal CSV ON T.ID = CSV.REF_ID INNER JOIN StringType CST ON CSV.TYPE_ID = CST.ID AND CST.ID = COLOR LEFT JOIN StringVal NSV ON T.ID = NSV.REF_ID INNER JOIN StringType NST ON NSV.TYPE_ID = NST.ID AND NST.ID = NAME WHERE ( CSV.VAL = 'Blue' OR (CST.VAL = 'Blue' AND CSV.REF_ID IS NULL) ) AND ( NSV.VAL = 'Fred' OR (NST.VAL = 'Fred' AND NSV.REF_ID IS NULL) ) How do I generally simplify this? R. -Original Message- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Friday, August 04, 2006 4:12 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: Query Help for Loosely Couple Properties I think this only works if I know that Orange is the default value. For example if Orange is the default and I am searching for Blue I will have to use a different query. Since I don't know the default (its in the type table) I would have to do two queries. How about something like this? SELECT * FROM T LEFT JOIN StringVal SV ON T.ID = SV.REF_ID INNER JOIN StringType ST ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE SV.VAL = Blue OR (ST.VAL = Blue AND SV.REF_ID IS NULL); -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 9:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) SELECT * FROM T LEFT JOIN StringVal V ON T.ID = V.REF_ID INNER JOIN StringType ST ON V.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE V.REF_ID IS NULL OR V.VAL = Orange; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help for Loosely Couple Properties
I think this only works if I know that Orange is the default value. For example if Orange is the default and I am searching for Blue I will have to use a different query. Since I don't know the default (its in the type table) I would have to do two queries. How about something like this? SELECT * FROM T LEFT JOIN StringVal SV ON T.ID = SV.REF_ID INNER JOIN StringType ST ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE SV.VAL = Blue OR (ST.VAL = Blue AND SV.REF_ID IS NULL); -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 02, 2006 9:37 AM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) SELECT * FROM T LEFT JOIN StringVal V ON T.ID = V.REF_ID INNER JOIN StringType ST ON V.TYPE_ID = ST.ID AND ST.ID = COLOR WHERE V.REF_ID IS NULL OR V.VAL = Orange; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query Help for Loosely Couple Properties
They are user defined properties. -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 01, 2006 8:11 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Query Help for Loosely Couple Properties On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote: I have a table that contains properties that can be associated with any table whose primary key is a LONG. Lets say that there is just one kind of property. The table looks something like this: TABLE StringVal REF_ID BIGINT// row to associate property with TYPE_ID BIGINT// type of string property VAL VARCHAR // property value P_KEY( REF_ID, TYPE_ID ) There is another table to represent a specific StringVal type along with its default value: TABLE StringType ID BIGINT // The TYPE ID NAMEVARCHAR // The unique name of this property DEF_VAL VARCHAR // The default value of this property Actually, the rub is that you are not using specific columns for specific entity attributes, and are pretty much storing everything in one gigantic table. Any particular reason for this? For instance, why not have a column called color, instead of overcomplicating things? -jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help for Loosely Couple Properties
I have a table that contains properties that can be associated with any table whose primary key is a LONG. Lets say that there is just one kind of property. The table looks something like this: TABLE StringVal REF_ID BIGINT// row to associate property with TYPE_ID BIGINT// type of string property VAL VARCHAR // property value P_KEY( REF_ID, TYPE_ID ) There is another table to represent a specific StringVal type along with its default value: TABLE StringType ID BIGINT // The TYPE ID NAMEVARCHAR // The unique name of this property DEF_VAL VARCHAR // The default value of this property The rub is that the target table could have millions of records and I only want a record in StringVal if the associated property is going to be a value other than the default. So consider that StringType has a record that defines a property named COLOR with a default value of ORANGE. For some table T, a T record will only have a corresponding row in StringVal if it has a COLOR property whose value has been explicitly set. It *could* be ORANGE but in most cases it will be something else. Each row implicitly gets a COLOR value of ORANGE. The question is, how do I query this? Say I want all records from table T whose COLOR property value is ORANGE. The only thing I can come up with (and I'm no SQL expert and this looks wrong to me) is the following: SELECT * FROM T WHERE ( T.ID NOT IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringValue.TYPE_ID = COLOR ) AND EXISTS ( SELECT * FROM StringType WHERE StringType.DEF_VAL LIKE Orange AND StringType.ID = COLOR ) ) OR ( T.ID IN ( SELECT StringVal.REF_ID FROM StringVal WHERE StringVal.VAL LIKE Orange AND StringVal.TYPE_ID = COLOR ) ) Any suggestions on how to simplify this (besides writing a row for each T that has the default value)? Should I lay out the tables differently or keep the DDL the same and just clean up the query? R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL service startup time
Considering there are a number of pieces that are involved in the connection being ready why not create a small program that tries to connect and checks the return value. If it fails sleep for 500ms to 1 sec and try again for up to X-times before aborting altogether? Bob -Original Message- From: Rob Desbois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 11:51 AM To: mysql@lists.mysql.com Subject: MySQL service startup time Hi, I have an application self-installer program which also installs MySQL and sets it up. This is all on Windows. I have a problem in that when the installer runs 'net start MySQL', it returns immediately but the MySQL daemon is not ready for connections immediately. As the next step in the installation is to create the application database, I need to wait until I can connect. What's the best way to achieve this? At the moment I have a rather crude 5 second 'sleep', but that isn't always long enough. Any ideas? --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- 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: New to the group
If you will excuse my ignorance. I have no immediate need for this, but have often asked what the pros/cons there are writing a WEB based interface in PHP vs. say Perl. Do you have any insight into that? Thanks Bob -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, June 22, 2006 3:39 PM To: mysql@lists.mysql.com Subject: Re: New to the group At 08:46 AM 6/22/2006, Nicholas Vettese wrote: Hello, My name is Nick, and I am a new MySQL user. My hope is not to become a PITA, so I will make sure that any question is straight and to the point with the information needed to answer the question. My skill in MySQL is pretty low, and I am looking to build a website for myself that will take information and save it to a database. At this time, I have a login, registration, change/lost password functionality working from a book that I read, but I am looking to expand my knowledge into more robust site. I am not looking to become the master programmer, just someone with enough knowledge and skill to accomplish his goals. Thanks, Nick Welcome Nick, You've come to the right place. There are a couple of books on MySQL that are quite good and I'd like to recommend. MySQL 3rd Edition by Paul Dubois and MySQL Cookbook by Paul Dubois (I think these guys are relatedvbg) If you are using PHP to build your website I found PHP and MySQL for Dynamic Web Sites : Visual QuickPro Guide (2nd Edition) (Visual Quickpro Guide) to be quite good and gets you going quite fast. There's not a lot of reading to do and they have you writing PHP code the first day. If you want a more thorough book on PHP MySQL there is: PHP and MySQL Web Development (3rd Edition) (Developer's Library) (Paperback) by Luke Welling, Laura Thomson There are also PHP/Mysql tutorials on the web but I don't know how good they are. You'll get up to speed faster by getting some of these books. Of course if you're not using PHP, then someone else can jump in with some reading suggestions. Mike -- 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: max size of TEXT columns
FWIW, IMO LOB handling is really where MySQL lags behind all other enterprise database solutions. Firebird, DB2, Oracle, et al all are able to stream LOB data to and from disk so that it does not all need to be loaded in memory (multiple times for a single LOB). I would call this a bug but others would probably prefer to call it a limitation. :) -Original Message- From: schlubediwup [mailto:[EMAIL PROTECTED] Sent: Monday, May 29, 2006 8:07 AM To: mysql@lists.mysql.com Subject: max size of TEXT columns hi listers, 1. environment: [EMAIL PROTECTED] ~ uname -a Linux myhost.mydomain.com 2.6.16-1.2080_2.rhfc5.cubbi_suspend2 #1 Wed Mar 29 12:54:32 CEST 2006 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~ localhost.(none) show variables like version%; +-+--+ | Variable_name | Value | +-+--+ | version | 5.0.18-standard | | version_comment | MySQL Community Edition - Standard (GPL) | | version_compile_machine | i686 | | version_compile_os | pc-linux-gnu | +-+--+ 4 rows in set (0.00 sec) localhost.(none) localhost.mydb show variables like max%; ++--+ | Variable_name | Value | ++--+ | max_allowed_packet | 1048576 | localhost.mydb select max(char_length(history)) from my_contacts2; +---+ | max(char_length(history)) | +---+ | 65535 | +---+ 1 row in set (0.01 sec) localhost.mydb 2. problem according do the mysql docu TEXT/BLOB fields depend solely on the db environment and can grow to any length: 11.4.3. The |BLOB| and |TEXT| Types The maximum size of a |BLOB| or |TEXT| object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the |max_allowed_packet| variable, but you must do so for both the server and your client program. For example, both *mysql* and *mysqldump* allow you to change the client-side |max_allowed_packet| value. See Section 7.5.2, Tuning Server Parameters http://www.ayni.com/mysql/optimization.html#server-parameters, Section 8.3, mysql - The MySQL Command-Line Tool http://www.ayni.com/mysql/client-side-scripts.html#mysql, and Section 8.8, mysqldump - A Database Backup Program http://www.ayni.com/mysql/client-side-scripts.html#mysqldump. BUT: The table in consideration contains a column named history containing all message text which has been sent to the mail-address in the row in question. i ment to have observed that this column in reality does not contain all message text from the very beginning. today, i found out that the maximum lenght of the history fields in all tables of this kind is 65535. when an update is ocurring, the column history is recreated using concat(new_text, history) so that the newest text is always at the beginning of the column. but the oldest text at the end of the column apparently is lost. 3. question which parameter do i have to change in order to get this TEXT column really to any length? any hint is very much appreciated, thanks in advance. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is This A Redundant Info Example?
Bad design that violates basic DB design checks. Redundant information will become a major problem for you going forward. Unless you have hard and fast performance issues they require it, just don't do it:-) Create a single table that contains customer info and reference the information using Foreign keys in your other tables that require customer information. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, May 19, 2006 8:57 AM To: Mark Sargent; mysql@lists.mysql.com Subject: RE: Is This A Redundant Info Example? [snip] Why have customer info in both? Delivery and Billing info makes sense, but why the redundant info in both? Anyone got views on this? Do/would you do it differently, and could you tell us why? Cheers. [/snip] It is bad database design IMHO. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to extract common text string from field?
Why not store them in separate columns? You could then have the domain field be a foreign key into another table. -Original Message- From: Ferindo Middleton [mailto:[EMAIL PROTECTED] Sent: Friday, May 12, 2006 2:14 PM To: mysql@lists.mysql.com Subject: how to extract common text string from field? Suppose you have a field in a db table that holds email addresses and all of the address end in domain.com. Is there a MySQL function that can be used to extract the first part of the email address, the username (the part of the email address before the 'domain.com' part of the email address). I was hoping somehow to do this within a SELECT statement so instead of seeing the email address I would see the username without the 'domain.com' part. Ferindo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Intermittent deadlock/InnoDB
No problem, just didn't want to jump the gun if it was a known issue or if it were clear from the posted traces that I was doing something wrong. -Original Message- From: Stewart Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 10, 2006 10:54 PM To: Robert DiFalco Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Intermittent deadlock/InnoDB On Wed, 2006-05-10 at 16:26 -0700, Robert DiFalco wrote: Version 5.0.19. We have no autogenerate keys. We perform a single insert and get a lock timeout. The insert is done with a stored procedure with a single line. The lockup happens VERY rarely and we have no idea how to reproduce it. Probably best to file a bug report in the bugs system. This is the best way so we can track problem reports. -- Stewart Smith, Software Engineer MySQL AB, www.mysql.com Office: +14082136540 Ext: 6616 VoIP: [EMAIL PROTECTED] Mobile: +61 4 3 8844 332 Jumpstart your cluster: http://www.mysql.com/consulting/packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Intermittent deadlock/InnoDB
Version 5.0.19. We have no autogenerate keys. We perform a single insert and get a lock timeout. The insert is done with a stored procedure with a single line. The lockup happens VERY rarely and we have no idea how to reproduce it. Here's the hostname.err data: Alarm status: Active alarms: 0 Max used alarms: 0 Next alarm time: 0 Thread database.table_name Locked/WaitingLock_type 11 te.node Locked - writeConcurrent insert lock And then here is the SHOW INNODB STATUS output. Notice that thread 11 is the one that is WAITING for the lock. However, above it says that it is locked. TRANSACTIONS Trx id counter 0 2912 Purge done for trx's n:o 0 2834 undo n:o 0 0 History list length 13 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 1928 MySQL thread id 30, query id 14092 172.18.0.102 root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, OS thread id 1176 MySQL thread id 25, query id 13730 172.18.0.102 root ---TRANSACTION 0 0, not started, OS thread id 3176 MySQL thread id 15, query id 12618 rogerrabbit.tripwire.com 10.150.1.60 root ---TRANSACTION 0 2905, not started, OS thread id 1080 MySQL thread id 13, query id 14038 localhost 127.0.0.1 root ---TRANSACTION 0 2911, ACTIVE 1 sec, OS thread id 2312 inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 320, undo log entries 2 MySQL thread id 11, query id 14091 localhost 127.0.0.1 root update INSERT INTO Node(F_MAKE,F_MODEL,F_VER,oid) VALUES (p_F_MAKE,p_F_MODEL,p_F_VER,p_oid) --- TRX HAS BEEN WAITING 1 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 241 n bits 72 index `PRIMARY` of table `te/node` trx id 0 2911 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; -- ---TRANSACTION 0 2791, ACTIVE 4064 sec, OS thread id 3096 2 lock struct(s), heap size 320 MySQL thread id 12, query id 13254 localhost 127.0.0.1 root == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
