Re: utf8 options under Mysql
Hi, On 22/04/2016 04:50, Martin Mueller wrote: MySQL has a bewildering variety of unicode collation choices. Most of them are language specific, but what is the difference between "utf8-general-ci", "utf8-unicode-ci", and "utf8-unicode-520-ci." Do they differ in the range of characters they can handle or is it just a matter of the cort order. I understand that utf8-bin is different because it is case sensitive, but the other differences elude me. Under what circumstances does it make a difference to use on or the other? I work with a lot of Early Modern print data and the weird symbols of various kinds they use. I've had trouble at times with the "utf8-general-ci" setting, but it may have been more a matter of settings on my front end tool than of the choice of this rather than unicode collation. Under character sets, there is just one utf8 setting. The simplest way to make sense of the choices would be to say that given a character set (utf8) the collation only makes a difference to the sort but makes no difference to what can be displayed. Is that correct. A collation contains definitions for sorting order and comparison. For most purposes one wants "crème brûlée" to be the same as "creme brulee". For unicode characters these rules can be complex. A character set (in your case UTF-8) defines which character can be stored. utf8-general-ci contains a simplified version of those conversion rules. It works for a lot of Western European languages very well, but in some cases there are problems. For Asian languages there are a lot more problems. For example, 'ß' isn't considered the same as 'ss'. utf8-unicode-ci has more complex rules and works fine for more languages. Due to the more complex rule set it is a bit slower than utf8-general-ci. utf8-unicode-520-ci uses a newer version of the rule set that is used in utf8-unicode-ci. Other utf8-* collations may contain specific rules for specific languages utf8-general-ci is the default collation for utf-8 in MySQL. If you use literal strings MySQL may assume that these have the default collation and comparing them to columns with other collations or performing things like cast operations may produce errors about invalid combinations of collations. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: To:, CC: mailing lists
Hi, On 17/05/2015 11:37, Emil Oppeln-Bronikowski wrote: Guys, can I implore you to post to a mailing list using its address in To: field and not CC:ing it? You are constantly breaking out of my filters. I've set filters on To: or Cc: contains to catch all the mails. The others in this thread use Gmail which obviously lacks a button Reply to list. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 10/12/2014 09:02, Johan De Meersman wrote: - Original Message - From: Jigal van Hemert ji...@xs4all.nl Subject: Re: forum vs email [was: Re: table-for-column] On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Hmm. That sounds interesting, I'll have a look. I don't suppose the software is available under a foss license? :-p Mailing lists [1] themselves use Mailman [2] (GPL), forum [3] is done with FUD forum [4] (FOSS GPL2), for integration between mailing lists and newsgroups, please contact the TYPO3 server admin team [5]. I'm sure they will tell you all you want to know about this setup. [1] http://lists.typo3.org/cgi-bin/mailman/listinfo [2] http://www.gnu.org/software/mailman/ [3] http://forum.typo3.org/ [4] http://cvs.prohost.org/index.php [5] http://typo3.org/teams/server-team/ -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 10/12/2014 10:09, Johan De Meersman wrote: Hm. Typo3 is a CMS; I take it the integration you're speaking of is specific to their support environment, and not part of the CMS? Correct, TYPO3 is a CMS (also FOSS GPL2+) and the integration is indeed not part of the CMS. See my other reply for details on the software that was used. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: forum vs email [was: Re: table-for-column]
Hi, On 05/12/2014 20:54, Jan Steinman wrote: From: Johan De Meersman vegiv...@tuxera.be I've long wanted to - but never quite got around to - write a forum that integrated a mailing list. Bar mail clients that don't handle list threads well, it really doesn't seem such a difficult task. There actually seem to be a lot of these around. I'm on several that send me email when there are new forum postings. On typo3.org there used to be mailing lists only in a distant past. Later on newsgroups were set up which communicate with the mailing lists (newsgroups are the central source of messages). Rather recently a forum was built on top of the newsgroup data (FUD forum was used). Users on all three message sources can easily communicate with eachother. Only some mail clients have difficulty keeping the threading headers in tact, but other than that there are no real issues. -- Met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql Access denied error
Hi, On 5-5-2014 10:57, Reindl Harald wrote: Am 05.05.2014 10:19, schrieb Manuel Arostegui: 2014-05-05 10:00 GMT+02:00 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net: Am 05.05.2014 08:34, schrieb Manuel Arostegui: % doesn't match localhost so if you don't specify it you will be attempting to connect via Unix Socket. If you don't want to specify -hlocalhost all the time, just do the grant with @localhost instead of @% nonsense % matches *any host* Do the test yourself i don't need to test such basics since i am working as mysql administrator the last 11 years and curently responsible for some hundret databases heavily using host specific permissions http://dev.mysql.com/doc/refman/5.5/en/connection-access.html '%' 'fred' fred, connecting from any host In that case you would know that connecting via a Unix socket is not the same as connection via a network. See: http://bugs.mysql.com/bug.php?id=69570 http://dev.mysql.com/doc/refman/5.5/en/connecting.html -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data masking for mysql
Hi, On 15-4-2014 18:42, Peter Brawley wrote: On 2014-04-15 5:37 AM, reena.kam...@jktech.com wrote: It can be done by data masking tool itself. Its one time activity, I do not need it again again. Rilly? If that's so, the data will never be accessed. I'm starting to think that a concept has been made that includes a database with the original data, a copy with the masked data and then there just needs to be a tool that copies the data and modifies (masks) some fields. Whatever solution we come up with (views, db copy with an update query that modifies the data, ...) it will not be accepted unless it fits the original concept. Most likely the client came up with the concept and then this outsourced development team doesn't dare to suggest that a different concept is probably a better way to reach the goal. But, I may be wrong here :-) -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data masking for mysql
Hi, On 15-4-2014 11:03, reena.kam...@jktech.com wrote: The main reason for applying masking to a data field is to protect data from external exposure. for example mobile no. is 9878415877, digits can by shuffle(8987148577) or can replace with other letter/number(first 6 digits replace with X-- xx5877) by using data masking. We can use any one data masking technique to protect our sensitive data from external exposure. I need a tool which will mask data in existing mysql db. You could create a VIEW on that table which contains processed columns. Use a special user for the application that has SELECT rights on the view but not on the table. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Data masking for mysql
Hi, On 15-4-2014 12:36, reena.kam...@jktech.com wrote: Actually data masking is a one time activity, so I need data masking tool. I do not need it again again. So you basically want to replace the data with modified data. You can do that with an update query [1]. There are all kinds of functions available to manipulate the data itself. [1] http://dev.mysql.com/doc/refman/5.5/en/update.html [2] http://dev.mysql.com/doc/refman/5.5/en/functions.html -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: New to MySQL
Asma, I think maybe it is a good idea if you start reading Mysql for dummies or something similar. If you do not understand the basic concepts of unix/linux and mysql, I see a hard road for you. Google is you friend and I believe they would even have documentation in your native language. Regards Brian vd Westhuizen -Original Message- From: Asma rabe [mailto:asma.r...@gmail.com] Sent: Thursday, 6 March 2014 12:08 a.m. To: Andrew Moore Cc: Reindl Harald; mysql@lists.mysql.com Subject: Re: New to MySQL How to do that? Thank you On Wed, Mar 5, 2014 at 8:03 PM, Andrew Moore eroomy...@gmail.com wrote: Next action is to review the MySQL error log for the reason that it failed. A On Wed, Mar 5, 2014 at 10:55 AM, Asma rabe asma.r...@gmail.com wrote: Hi All, Thank you very much. I checked if mysql installed rpm -qa | grep mysql and found it is installed, so no need for yum installation. when i tried to start the service chkconfig mysql on error reading information on service mysql: No such file or directory sorry to disturb you. Regards, Rabe On Tue, Mar 4, 2014 at 9:40 PM, Reindl Harald h.rei...@thelounge.net wrote: Am 04.03.2014 13:20, schrieb Asma rabe: I have checked before installation if mysql is installed using which mysql i found no mysql is installed. which is pointless rpm -qa | grep mysql next i did rpm installation and got the errors rpm -i MySQL-server-5.6.16-1.el6.x86_64.rpm I got the following errors file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/danish/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 clearly shows there are at least the libraries installed which is why i said which is pointless *do not* use the raw rpm command it can't solve any dependencies use yum which works also for local downloaded RPM files when i check now which mysql i found it has installed in /bin/mysql when i try to run mysql i doubt that MySQL-server contains /bin/mysql nor that after the error above anything was installed expect you did not show all what happened on your machine Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' Any idea? installing something does not mean it gets started or even enabled mysql is the client CLI connecting to a server, without specify one it connects to the local unix socket but given that your install above failed nor that you have tried to start mysqld that must fail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: New to MySQL
Why don't you just use yum install . Should sort out most if not all your problems regarding installation of mysql. Regards Brian vd Westhuizen -Original Message- From: Asma rabe [mailto:asma.r...@gmail.com] Sent: Wednesday, 5 March 2014 1:21 a.m. To: geetanjali mehra Cc: Johan De Meersman; mysql@lists.mysql.com Subject: Re: New to MySQL Thank you very much for responding. I have checked before installation if mysql is installed using which mysql ,i found no mysql is installed. next i did rpm installation and got the errors rpm -i MySQL-server-5.6.16-1.el6.x86_64.rpm I got the following errors file /usr/share/mysql/czech/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 file /usr/share/mysql/danish/errmsg.sys from install of MySQL-server-5.6.16-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.61-1.el6_2.1.x86_64 when i check now which mysql i found it has installed in /bin/mysql when i try to run mysql Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) Any idea? Thank you all for your kind help Rabe On Tue, Mar 4, 2014 at 12:30 AM, geetanjali mehra mailtogeetanj...@gmail.com wrote: Thanks for responding. MySQL installation ,here, do not require mysql-libs package. As far as I know, there is no harm on using this command. I too got the same problem, and I didn't face any problem after removing this package. This package is installed by default and not needed here. So, this command is very safe. On Mon, Mar 3, 2014 at 7:49 PM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: geetanjali mehra mailtogeetanj...@gmail.com Subject: Re: New to MySQL use rpm -i --replacefiles MySQL-server*.rpm While that will work, it really shouldn't happen. I'm a Debian man myself, so I don't know wether it's a problem with the packages or if you simply don't need the -libs package when installing the server. I would, however, suggest using Rug, Zypper or a similar advanced package manager instead of barebones RPM, as those will actually find and install any necessary dependencies, too. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Geetanjali Mehra Oracle DBA Corporate Trainer Koenig-solutions Moti Nagar,New Delhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Multi-master to one slave
Dont understand your question ? But If you have 2 masters replicating to each other, yes you can a have a single slave hanging from either those servers. Things to keep In mind Each server have unique server id Make the slave read only If you have very little experience in DBA'ng and particular with mysql the I suggest you look at other products. But it is fairly easy to set up and administer, we have quite a few instalations of this nature, multi master with lots of slaves hanging of the masters ! Regards Brian vd Westhuizen Informix/MySQL/Postgres/MongoDB Database Administrator Datacom NZ | Level 2, North Tower, 68 Jervois Quay www.datacom.co.nz | PO Box 6376, Wellington 6141 -Original Message- From: xiangdongzou [mailto:xiangdong...@gmail.com] Sent: Tuesday, 25 February 2014 10:48 p.m. To: Zhigang Zhang; mysql Subject: 回复: Multi-master to one slave DEAR zhang: You can use GoldenGate do that. 2014-02-25 I AM AN ORACLE FANS! Skype:Frank.oracle Email:xiangdong...@gmail.com 发件人：Zhigang Zhang zzgang2...@gmail.com 发送时间：2014-02-25 12:06 主题：Multi-master to one slave 收件人：mysqlmysql@lists.mysql.com 抄送： Does someone have the best schema from multi-master to one slave excluding the circular replication. Please tell me. Thanks. Zhigang
Re: Unique on non null entries but allowing multiple nulls
Hi, On 17-10-2011 15:39, Peng Yu wrote: If I use NULL UNIQUE when I create a table, it seems that only one NULL entry is allowed. Since NULL could mean unknown, in this case, two unknowns are not the same and I want to allow multiple nulls but I still want non null entries be unique. Is there a construct in mysql that can create a table column like this? From the documentation: A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. Only for 5.0 there is the exception that the BDB storage engine does not allow multiple NULL values in a column with a UNIQUE index. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: NULL-safe (in)equality =
Hi, On 1-10-2011 21:51, Halász Sándor wrote: It is, of course, generally considered more natural to make equality primary, not inequality, but that symbol that MySQL uses for NULL-safe equality,=, looks much more like inequality than equality. The whole concept and the name of this operator is wrong IMO. There is nothing NULL-*safe* about it. Equal and unequal operators are in fact more NULL-*safe* than =. But if I write IF A B THEN often I want it NULL-safe, for if one is NULL and the other not, I want that true You are not using NULL as the original concept of it was. NULL means that the value is undefined or unknown. If a value is undefined it may have *any* value. So, if you evaluate (A = NULL) the NULL part can have *any* value, even A. The result of this compare can only be NULL, because it is not known whether it's equal or unequal. Because of this (NULL = NULL) must be NULL too. (NULL NULL) must also result in NULL. The result is just as undefined/unknown as both values which were compared. The usual solution in the case you describe is that you use a normal value in the range of the field type which is not used normally. E.g. for an INT field where you only use values of zero or larger you can use e.g. -1 as a special value. If you insist on using NULL and the crazy = operator you can use NOT to invert it: SELECT NOT(A = B); -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql listed as attach page by google?
Hi, On 26-9-2011 20:30, Michael Albert wrote: I don't suppose I am the first to notice this, but most of the pages on dev.mysql.com have been listed by google as attack pages, e.g http://dev.mysql.com/downloads/. Has there been a problem, or is google being overzealous? I fear Google is right. http://www.net-security.org/malware_news.php?id=1853 -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: locked non-existent row
On Wed, Aug 31, 2011 at 4:10 AM, Peter Brawley wrote: While a transaction in one thread tries to update a non-existent InnoDB row with a given key value, an attempt to insert that value in another thread is locked out. Does anyone know where this behaviour is documented? In the manual it is called gap locking: http://dev.mysql.com/doc/refman/5.5/en/innodb-record-level-locks.html http://dev.mysql.com/doc/refman/5.5/en/innodb-next-key-locking.html Jochem -- Jochem van Dieten http://jochem.vandieten.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: Query Optimization
On Aug 30, 2011 6:46 PM, Brandon Phelps wrote: SELECT sc.open_dt, sc.close_dt, sc.protocol, INET_NTOA( sc.src_address ) AS src_address, sc.src_port, INET_NTOA( sc.dst_address ) AS dst_address, sc.dst_port, sc.sent, sc.rcvd, spm.desc AS src_port_desc, dpm.desc AS dst_port_desc FROM firewall_connections AS sc If this is a firewall connection log I presume open_dt is the time a connection was opened and is always going to be less than close_dt. Right? WHERE (open_dt = '2011-08-30 00:00:00' OR close_dt = '2011-08-30 00:00:00') AND (open_dt = '2011-08-30 12:36:53' OR close_dt = '2011-08-30 12:36:53') In that case your logic here simplifies to: WHERE open_dt = '2011-08-30 00:00:00' AND close_dt = '2011-08-30 12:36:53' Now add an index over open_dt and close_dt and see what happens. Jochem
Re: a lesson in query writing and (maybe) a bug report
Hi, On 28-8-2011 4:08, shawn wilson wrote: On Sat, Aug 27, 2011 at 17:33, Arthur Fullerfuller.art...@gmail.com wrote: I agree 110%. It is completely pointless to index a column with that amount of NULLs. In practical fact I would go further: what is the point of a NULLable column? A NULL 'value' is special in most operations. It indicates that the value is undefined, unknown, uncertain. In this regard it's actually not a value. SELECT 'Uncertain' = TRUE; Result: 0 SELECT 'Uncertain' = FALSE; Result: 1 SELECT 'Uncertain' = NULL; Result: NULL SELECT NULL = TRUE; Result: NULL SELECT NULL = FALSE; Result: NULL SELECT NULL = NULL; Result: NULL (Unfortunately someone decided to add the = operator: SELECT NULL = NULL; Result: 1 Even stranger is that it is documented as NULL safe !?!?) The advantage to me for having NULL 'values' is that it is usually handled as a truly undefined value. (When you compare an undefined value with for example 2, the result cannot be TRUE or FALSE. The undefined value might be equal to 2, or might not be equal to 2. The result can only be undefined.) To deal with NULL results inside expressions COALESCE() is a very useful function. how does null effect an index? i had always assumed that, since there is nothing there, that record wouldn't go into the index hence wouldn't be processed when utilizing the index. MySQL can use NULL in indexes when executing a query. If there are not enough different values in a column (low cardinality) it might be faster to do a full table search instead of first reading the index and then having to go through the table anyway. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a lesson in query writing and (maybe) a bug report
Hi, On 27-8-2011 1:28, Dave Dyer wrote: Can you post the EXPLAIN EXTENDED output for your before and after queries? also, have you recently run an ANALYZE TABLE on the tables? What was the result of ANALYZE TABLE? What is the engine of the tables involved? // before Used keys: p2.NULL, g.player2, p1.uid In your original post you wrote: The according to explain, the query used gmtdate as an index, an excellent choice. The explain output you posted later indicated that this is not the case (anymore). gmtdate isn't listed as possible index, so what has changed? It seems odd that the query optimizer would choose to scan a 3.5 million entry table instead of a 20,000 entry table. Let's see. Before: 28653 * 41 * 1 rows to consider = 1.1 M rows After: 15292 * 67 * 1 rows to consider = 1.0 M rows Conclusion: the query optimizer didn't choose to scan an entire table. In fact it found a way to have to look at 10% less rows. For the final order by and limit it would be great to have a (partial) index to work with. It's true that planning indexes isn't always an exact science. Generally speaking the goal is to construct both the query and the indexes in a way that you rule out as many rows as possible early on in the process. From your query it becomes evident that you want the latest fifty matches between two players who both have the status is_robot null. Try to create indexes which cover as many of the columns which are involved in the join, where and order parts, and look at the cardinality of those indexes. This will determine how many records can be discarded in each join and keeps the number of records MySQL has to scan as low as possible. Another way is a bit tricky, but can speed up queries a lot: you want the 50 most recent records, so analyse the data and see if you can predict how big your result set will be in a period of time. Let's assume that there are always between 10 and 50 of such records per day. If you want the top 50 it would be safe to limit the search for the last 10 to 20 days. Of course this requires an index which includes gmtdate, but it can make the result set before the limit a lot smaller. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a lesson in query writing and (maybe) a bug report
Hi, On 27-8-2011 22:52, Dave Dyer wrote: The innocuous change was to add an index for is_robot which is true for 6 out of 20,000 records and null for the rest. Not useful to add an index for that. I also wonder why the value is null (meaning: unknown, not certain) for almost all records. If you want to use such a column in an index it's best to use and index base on multiple columns. This makes it more useful for use in queries. My complaint/question/observation is not how to optimize the query that went awry, but to be alarmed that a venerable and perfectly serviceable query, written years ago and ignored ever since, suddenly brought the system crashing down after making a seemingly innocuous change intended to make a marginal improvement on an unrelated query. Adding an index will most likely trigger some maintenance actions to make sure the table is healthy before adding the index. The query optimizer has an extra index to take into account. I had previously believed that tinkering the schema by adding indexeswas a safe activity. A database should be left alone for a long period. It needs monitoring and maintenance. Changes in the schema and even changes in the data can lead to changes in the behaviour. You can make suggestions for the indexes to be used and you can even force the use of an index if the query optimizer makes the wrong decisions in a case. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Recognizing utf8 encoded data in latin1 fields/tables
Hi, The TYPO3 CMS I'm working on uses UTF-8 database fields for some time now by default. There are sometimes old installation, which have been updated without properly converting the database. The result: UTF-8 encoded data in (most often) latin1 tables/fields. I have a conversion script which analyses the table definitions and uses the trick of two alter table operations (first to the binary equivalent of the column type and then to the normal type with the utf8 charset) to convert the data to the correct character set. It would be nice to be able to detect this situation using queries only (faster than transferring the data into the PHP script and analysing it there). I have been fiddling a bit with a few columns: test: latin1 (latin1-swedish-ci) contains UTF-8 encoded data test1: latin1 (latin1-swedish-ci) contains latin1 encoded data test: Landrëéüöïß CONVERT(BINARY `test` USING utf8): Landrëéüöïß CONVERT(`test` USING utf8) : Landrëéüöïß test1: Landrëéüöïß CONVERT(BINARY `test1` USING utf8) : Landr CONVERT(`test1` USING utf8) : Landrëéüöïß I'm now looking for an expression which can differentiate between the two situations if possible without having to look for all possible combinations of the encoded data. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: DBA Mentor?
Hi, On 5-5-2011 0:52, Akachi Pictures wrote: perhaps u misinterpreted. didn't get angry. just moved on :) The reason why you get some surprised messages on the list is because we see the following: May 4th 19:06 - your first message about a mentor May 4th 19:57 - a reply by Claudio explaining where to find information yourself May 4th 21:52 - a reply from you Sorry everyone. Didn't know it'd cause this kind of response. May 4th 21:54 - a reply from you GUYS. I NEVER ASKED TO PAY MONEY OR ASKED FOR TUTORING! GEEZ! We now assume that you received some messages off list from people offering you tutoring for money. Nobody else saw those messages and your replies looked a bit odd because of this. Please keep track of whether a message came from the list or from someone personally... -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql Bug 04/01/11
Hi, On 2-4-2011 2:18, Thomas Dineen wrote: Can't find file: './mysql/host.frm' (errno: 13) http://tinyurl.com/3sc3ydx -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
contact gives empty result
Hello all,
I'm trying to get a TEXT field updated with its own content and an extra
string by using concat. The query looks something like this:
update field_comment set field_comment = concat(field_comment, '\n
my_new_string');
I've noticed that in this case the update doesn't work when the content of
the field is empty. I tried setting up a testcase, and as long as the
textfield is NULL concat doesn't return anything. Is this 'as designed' or a
bug? Should it work as designed, would anyone know another easy way without
setting a byte first?
Below the testcase:
CREATE TABLE memo_test (id INT (1) UNSIGNED DEFAULT '0' NOT NULL, comments
TEXT, PRIMARY KEY(id)) TYPE = MyISAM;
INSERT INTO memo_test (id, comments) VALUES (1, NULL);
INSERT INTO memo_test (id, comments) VALUES (2, 'Hello');
select concat(comments, 'Does not work') from memo_test
+---+
| concat(comments, 'Does not work') |
+---+
| NULL |
| HelloDoes not work|
+---+
select concat('Something', comments, 'Does not work') from memo_test;
++
| concat('Something', comments, 'Does not work') |
++
| NULL |
| SomethingHelloDoes not work|
++
Best regards,
Almar van Pel
www.makeweb.nl
RE: contact gives empty result
Hi Jaime, Joerg and All, Thanks for the explaining. In all those years I've never actually seen this, quite surprising actually :). In this case the comments field was empty and indeed not set. As it could be filled during the proces I'll be adding an empty string to the field. I still need to update it with additional text. Best regards, Almar van Pel www.makeweb.nl -Original Message- From: Jaime Crespo Rincón [mailto:jcre...@warp.es] Sent: dinsdag 22 februari 2011 13:20 To: Joerg Bruehe Cc: mysql@lists.mysql.com; Almar van Pel Subject: Re: contact gives empty result 2011/2/22 Joerg Bruehe joerg.bru...@oracle.com: You have not understood the concept of NULL in SQL: NULL does not mean empty, it means unknown. [...] Apart form fully agreeing with Joerg, just a tip: you can use the the IFNULL() operand as a workaround: http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull Anyway, you should get rid of misplaced NULL fields, as they also affect performance. -- Jaime Crespo MySQL Java Instructor Software Developer Warp Networks http://warp.es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=al...@makeweb.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Error 1045
Hi, On 21-9-2010 5:25, Tim Thorburn wrote: Ignore that ... it's amazing how you can solve problems with enough caffeine and enough time away from a computer screen . It's also amazing how frustrating it is for those who are searching for the problem you mentioned to only find threads with 'solutions' such as ignore this, found it myself, never mind, solved, etc. -- Kind regards / met vriendelijke groet, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Storage of UTF-8 char in MySQL
Ryan Chan wrote: According to this document: http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html It said MySQL support UTF-8 using one to three bytes per character. But I have created a test table: -- create table test ( c char(5) ) default charset =utf8; From the table status, the data length is alway a multiple of 16. So how does it support 3 byte UTF-8 in practice? I'm afraid you might need to read up on UTF8 and unicode in general. It's not a 'choice' to have 1, 2 or 3 bytes per character. Rather, when the characters is sufficiently weird then UTF8 will use 2 or 3 bytes for that specific character only. Only if your entire message is weird, will each character consume 3 bytes. Wkr, Werner,- -- http://werner.yellowcouch.org/ signature.asc Description: OpenPGP digital signature
Re: Possible tricks to ALTER on huge tables?
Daevid Vincent wrote: We currently have some tables that are approaching 1 BILLION rows (real Billion, with nine zeros, not that silly six zero version). Trying to do an ALTER on them to add a column can sometimes take hours. A few years ago I have tested possible table structures for an application which had to store at least a million profiles of persons. Because we expected that properties would be added (and/or removed) from the database quite often I also tested a structure where the properties of a single profile were stored in tables based on the data type. So we had tables with integers, strings, dates, etc. and used a record for each property; columns were like: id, property name, value, and a few other relevant things to handle and display the data. Most select queries were about as fast as they would be with a single table. Database size was approximately the same because not all profiles used all properties, so we only needed to store the properties a certain profile would use. The only limitation at that time was 31 joins, but I don't think we've ever hit that limit. Adding properties was easy, just adding them to the configuration of the application was enough. It really depends on the situation of your application which table structure is the most suitable. Test the performance of all kinds of operations you need to do with realistic data and various amounts of data to see how it scales. -- Jigal van Hemert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Downloading MySQL
Rhino wrote: I've been away from MySQL for a few years and have not really been keeping close tabs on things, although I have kept my mysql mailing list subscription and am aware of a major player planning to purchase MySQL. Did that actually go ahead? Well... first Sun bought MySQL AB and recently Oracle bought Sun. The reason I ask is that I want to download a free copy of MySQL to use for development purposes and found that I couldn't simply download it any more the way I did several years ago. It gives you a form to complete where you have to supply all kinds of contact information and then assures you that someone will be in touch within 48 hours. What the heck is all that about? Is this some sort of marketing offensive where some sales guy is going to try to push me into purchasing MySQL and a service contract?? Go to www.mysql.com , select the Downloads (GA) tab, click on MySQL Community Server and select the operating system. After you've clicked on the Download button you will be presented with a form to login as a returning user, or register as a new user. Below that form is a link No thanks, just take me to the downloads! Pretty simple to avoid the questions. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Multiple table engine
Tompkins Neil wrote: Just looking for some confirmation that under a single database - I assume it is perfectly normal to have both MyISAM and InnoDB engines for different tables ? Is there anything I need to be aware of ? In most case no problems. MySQL can mix engines without problems. Every engine uses it's own specific buffers, so if your database becomes big and memory becomes an issue (large buffers needed) it might be handy to use only one engine (so you can set the buffers for the other engine(s) to a very low number). On a daily basis I use databases with mixed MyISAM and InnoDB tables. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to deal with 96 Dimensional Points ?
Hello, I have been pondering this for a while, but never really looked deeply into the problem. I have 96 dimensional points and I would like to pose queries such as: 'give me all points that are within such a radius of this one'. The gis extensions to mysql might support such type of query. The problem is of course that points are 2 dimensional and I'm not sure whether I can extend it to more than 3 dimensions ? Does anybody have an idea about this ? Wkr, -- http://werner.yellowcouch.org/ signature.asc Description: OpenPGP digital signature
Re: How to deal with 96 Dimensional Points ?
Geert-Jan Brits wrote: You're most likely talking about something like consine-similarity on N-dimensional vectors. http://en.wikipedia.org/wiki/Cosine_similarity http://stackoverflow.com/search?q=cosine+similarity Cool links ! Although it is not why I need it for. I'm really talking about an eucledian distance measure between vectors. So in a sense it is simpler. Normally the gis extensions already provide the basic tools necessary, if only the points could be extended to more than 2 dimensions. You could google to see strategies that exist for mysql. However, depending on your use-case (e.g: scalable recommender systems) mysql (or any other rdbms) may not be the best tool for the job. -- http://werner.yellowcouch.org/ signature.asc Description: OpenPGP digital signature
Re: How to deal with 96 Dimensional Points ?
Johan De Meersman wrote: Well... a point in an n-dimensional space, is a location that has a defined value for each of it's n dimensions. If you have a value for each of your 96 dimensions, you have a point. Well, it's fairly simple. If you have two points with 96 values in each. Point1=(x1,...x96) and Point2=(y1,...,y96). The distance between these two is d=sqrt( (x_1-y_1)^2 + ... + (x_96-y_96)^2 ) There is no magic in this. The mathematics of comparing distances in 96 dimensions is beyond me, though :-) I guess a good start would be looking at comparing distances in 2 and 3 dimensions (vector math, that is) and trying to extrapolate a method from that. Alternatively, hire a mathematician :-p Extrapolating from lower dimensions doesn't work too well. In this case this would mean storing 48 different points and then trying to define a distance measure based on each individual point. I'm not sure this is feasable. In general: KD-trees are quite good tools to deal with such large dimensional spaces, but I see no possibility to use them in mysql, Wkr, On Tue, Mar 30, 2010 at 11:39 AM, Werner Van Belle wer...@yellowcouch.org mailto:wer...@yellowcouch.org wrote: Hello, I have been pondering this for a while, but never really looked deeply into the problem. I have 96 dimensional points and I would like to pose queries such as: 'give me all points that are within such a radius of this one'. The gis extensions to mysql might support such type of query. The problem is of course that points are 2 dimensional and I'm not sure whether I can extend it to more than 3 dimensions ? Does anybody have an idea about this ? Wkr, -- http://werner.yellowcouch.org/ -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- http://werner.yellowcouch.org/ signature.asc Description: OpenPGP digital signature
Re: How to deal with 96 Dimensional Points ?
Hello Chris,
The use case I'
m talking about is actually a typical usecase for GIS applications: give
me the x closest points to this one. E.g: give me the 10 points closest
to (1,2,79) or in my case: give me the 100 points closest to
(x1,x96). A query like yours might be possible and might be a good
solution if we would know the radius in which we are looking for the
points, but this is not really the case: we merely want a list returned
ordered by distance. Solving this with your solution is possible but is
quite slow. There exists nice datastructures to deal with this type of
problem as said and these are used in the GIS implementation in MySql.
Chris W wrote:
I'm not sure why, but it seems that some people, I don't mean to imply
that you are one of them, think there is some magic MySQL can preform
to find points with in a given radius using the GIS extension. There
is no magic. They simply use the well known math required to
determine what points are inside the circle.
GIS extenstions are also not only about distances: the above query is
better solved with specialized datastructures.
I could be wrong but I doubt there is any way to create an index that
can directly indicate points with a a certain distance of other points
unless that index included the distance from every point to every
other point. That is obviously not practical since with a set of only
14 points the index would have over 6 billion entries.
Partitioning of the space such as done in 3D render engines do solve
this problem more efficiently than having a list of all pairtwise
distances. So the question is not whether such algorithms exist, it is
rather whether they are available in/through MySql.
lets call each of your dimensions d1, d2, d3 d96. If you create
an index on d1, d2, d69, you can then create a simple query that
will quickly find all points that will find all points that are with
in a bounding box. Since this query is going to get a bit large with
96 dimensions, I would use code to create the query. I will use php.
Let's start with the desired radius being r and the test point
dimensions being in an array TestPointD[1] = x, TestPointD[2] = . . .
$select = 'SELECT `PointID`, ';
$where = 'WHERE ';
foreach($TestPointD as $i = $d){
$di = 'd' . $i;
$select .= `$di`,
$MinD = $d - $r;
$MaxD = $d + $r;
$where .= `$di` = '$MinD' AND `$di` = '$MaxD' AND ;
}
$select = substr($select, 0, -2); //trim of the trailing comma and space
$where = substr($where, 0, -4); //trim off the trailing 'AND '
$query = $select FROM `points` $where;
Thanks for the nice illustration. In this case with the proper indices
this will indeed split the space in sections; nevertheless this approach
has great difficulties returning an ordered list of distances and
prefereably only the 100 closest ones at that.
Wkr,
--
http://werner.yellowcouch.org/
signature.asc
Description: OpenPGP digital signature
Re: How to deal with 96 Dimensional Points ?
Geert-Jan Brits wrote:
Perhaps you could give us a (generalized) description of your use-case, so
we can better grasp what you want to achieve, and how you want to use it.
i.e: since I can't imagine/ envison a real 'eucledian distance' over 96
dimensions I bet you're talking a generalized distance function over N
dimenions.
This is usually only used in two general ways afaik:
2 calculating an ordered top-M list of closests points to the
target point (Chris' implementation slightly altered)
This is indeed the situation. A small alteration to chris his
implementation won't do, since we do not know with radius to start with,
so it is not just a matter of adapting the post-filtering.
3 (hmm maybe three:
clustering points based on their distance to eachother)
Yes, this is part of the usecase, but at the moment not my main focus. A
statistical approach will need to employed for that, without going for
full aggregation.
It helps if we know what you'r after. For instance: if you're points don't
change often and you want to achieve case 1 or 2 I would calculate these
once and all-at-once and save them in a seperate table, bc. the on-demand
variant may quickly become too slow. again depending on your case: option A.
{pointid, {neighborids}} -- list of neigborids per point id, with
pointid as key.
option B {pointid, neighborid} -- one neighborid per point id, with
pointid + neighborid as key.
That is not an option. Every 2 minutes or so the next point is randomly
choosen and we need a collection of points in the neighboorhood.
perhaps also helpful foor google etc.: - a distance function if more often
called a similarity function
- top-n 'points' for a given point are usually called its neighbors. - in
most cases you don't have to take the sqrt in Chris' implementation which
can save a lot (but instead do: if($SumSq =($r*$r)){//code here}
Indeed, but this is only a fraction of the time. The larger problem lies
in searching all points that have potential. An idea that might work is
to modify the radius of what we are looking at while we are searching
based on the maximum radius we have so far and cut down distance
comparisons if they will surely fall outside the current N closest
neighbours.
--
http://werner.yellowcouch.org/
signature.asc
Description: OpenPGP digital signature
Re: 7-day average
Brian Dunning wrote: My brain just squirted out my ear trying to figure this out, so maybe one of you can help me scoop it back up. Yummy, fresh brain! ;-) I have a table of accounts, and a table of hits-per-day per account. I'm trying to sort my accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago through yesterday). According to your table definitions: SELECT `account_id`, `company`, AVG(`hits`) AS avg_hits FROM `accounts` JOIN `hits_per_day` USING (`account_id`) WHERE `date` BETWEEN CURRENT_DATE() - INTERVAL 8 DAY AND CURRENT_DATE() - INTERVAL 1 DAY GROUP BY `account_id` ORDER BY avg_hits DESC -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how things get messed up
On 2/10/10, Ilya Kazakevich wrote: There was a joke in russian PHP club: why strore images in database? Are you going to have a full text search on them? Yes. That is what EXIF data is for, isn't it? And considering this is about PDFs any inability of a database engine to do a full text search on them surely is a limitation of that database, not a conceptual disqualification of storing binary data in a database. Jochem -- Jochem van Dieten http://jochem.vandieten.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: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. No, it's not a goal in itself, that's not what I said. I didn't say that you said that. You stated that Normalizing gets you -more- tables. It wasn't mentioned why you wanted to normalize the database in the first place. To me your statement looked like it said that normalizing a database would be a requirement for any database. This automatically would produce queries with 61+ joins in them. A lot of the enterprise level features can be useful in certain cases, Normalizing data has nothing to do with enterprise level, it's a matter if keeping your data consistent, being able to create proper constraints at the database, for example. Normalizing has nothing to do with enterprise level, but joining complex views has. Don't ask yourself why you've created the views, just use them in a join. So normalize each database because you may want to create constraints in some situations? This is the behaviour which causes unnecessarily complex databases, queries and applications. If you ask yourself if normalizing a column in a table is useful and if you really need the constraint and if the view, stored procedure, function or whatever you use is really useful, chances are that the application is a lot simpler, faster and easier to maintain. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Martijn Tonies wrote: database. I would also bet that 80% of the people who are actually writing queries with that many joins don't have a solid grasp of the fundamental principles of relational database design. Why not? Normalizing gets you -more- tables, not less. And normalizing is a goal in itself? I've seen plenty of normalized databases which have become a big mess because of the unnecessarily complex queries you needed to do a relatively simple job. A lot of the enterprise level features can be useful in certain cases, but it seems that a lot of times they are just used simply to use them. I cannot find justification for making databases unnecessarily complex, using subqueries when a simple join is all you need, using views, functions, stored procedures in cases that don't require such features, etc. I agree that a lot of people requiring more powerful hard- and software for their application are simply forgetting that they were supposed to produce a working application and not the most normalized database with all the fancy views and other stuff. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 50 things to know before migrating from Oracle to MySQL
Rudy Lippan wrote: How about complex data requirements? Depending on the resolution of your data set, I could see a simple person-type object that contained name, address, SSN, mother, and birth_info starting to approach the limit. Cities change, address changes, names change, and even mothers can change. The simple-looking street part of an address can have (at least) number, direction, name, suffix, any of which can change. Okay, so you want to link a person to an address table. I can justify that in the case of multiple addresses with a single person. But then you build a 'city' table to normalize that. Or no, better make a zip code table, link that to the 'city' table. Wait, streets can change names; a 'street' table too to link. Oh no! sometimes streets are split. So an address is a 'property' (a piece of ground), linked to a street, street linked to zip code, zip code linked to city. Damn (sorry), a 'property' can be divided... Oh my... Ever thought about updating a table by renaming a street? Or by selecting a group of street-number combinations and rename them? The real art is trying to balance the need of simplicity and ease of understanding with the need for flexibility, and that has nothing to do with relational theory. In real life the balance tends to go to unnecessary flexibility resulting in systems which are simply too heavy for the actual needs. Complex datasets are, by their nature, complex, and can only be simplified so much. You try to hide the complexity, you shift it, you move-it, you send it to its room, you pretend it is not there. And yet it still pops up at the most inopportune times and has to be dealt with. And still, in a lot of cases the complex datasets are even made more complex by normalization, trying to be ultimately flexible and creating a solution for problems which simply don't exist. In almost all cases a simple solution will be the best. Regards, Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Claudio Nanni wrote: If he really cared about MySQL he would have not sold it or prevent from selling it to Sun. Initially her was convinced that MySQL as a division of Sun would really benefit the future of MySQL [1]. Obviously his relationship with Sun changed a bit later on. It shows that he really cared about MySQL and in his own way, he still cares for MySQL. This has nothing to do with earning money or selling things. People sell things to companies or other people and think that the new owner will be good for the product they cared about. Sometimes it doesn't work out like you think it would and to me it shows that someone still cares about that product if they try to do something about it. I am not talking about agreeing with mr. Widenius or not; that is a different discussion. [1] http://www.internetnews.com/dev-news/article.php/3760831/MySQL+Back+to+Its+Roots+via+Sun.htm Regard, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help saving MySQL
Claudio Nanni wrote: Due to selling MySQL to Sun, Widenius earned about 16.6 million € in (...) I fail to see the relevance of this quote for this thread... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Multiple joins from same table?
Shawn, Thanks for the info, it does help indeed. I had also replied back to Gary to thank him as well, but I don't think that it made it to the list... so to Gary, thanks as well. Regards, Terry Terry Van de Velde Email: bya...@rogers.com Phone: (519) 685-0295 Cell: (519) 619-0987 -Original Message- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: December 12, 2009 4:39 PM To: Terry Van de Velde Cc: mysql@lists.mysql.com Subject: Re: Multiple joins from same table? Terry Van de Velde wrote: Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no Any help is appreciated. The trick to using the same table two or more times in the same query is through something called aliases when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the home team information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as home_team and other as away_team. I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bya...@rogers.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.105/2561 - Release Date: 12/12/09 14:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Multiple joins from same table?
Good Day, I am attempting to do something new (to me) with MySQL. I am looking to have my query return with the value in the visitor and home columns replaced with the corresponding team name from the teams table. schedule.visitor and schedule.home are essentially foreign keys to teams.team_no (though I have not defined them as such yet). What I have been trying is the select statement below which is fine when joining using one team (say the home team), but as soon as I attempt to add in the visitor team, things fall apart. I am basically looking to have the following outcome: Oct. 30 - Titans vs. Hawks (7:30 PM) Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) I would like this handled by MySQL instead of PHP if possible. - Schedule table 'id' int, 'date_time' datetime, 'visitor' tinyint 'home' tinyint - teams table 'id' int 'team_no' smallint, 'team_name' varchar (20) - SELECT schedule.date_time, teams.team_name FROM schedule, sojhlteams WHERE schedule.visitor = teams.team_no - Any help is appreciated. Best Regards, Terry
Re: Temp file issues on Ubuntu 9.10
Hi, I have got the correct answer now, from Linus Larsson, just forwarding it for the archives: ---8--- Hello, my name is Linus Larsson. I saw you got a problem with mysql on Ubuntu 9.10, I was researching the exact same problem and found your post. In the end I turned off app-armor. It seems to have a buggy profile for mysql. sudo /etc/init.d/apparmor stop Of course the correct fix is to update the profile, but I have no time to look into that now. I'm only using mysql temporarily on my computer anyway. Regards, Linus ---8--- Thanks to everybody who answered. :) Regards, Sebastiaan Ananda Kumar wrote: does the table ur trying to delete has any primary-foreign key relation. do show create table table_name\G Also instead of delete, use truncate, i t will be faster. regards anandkl On Tue, Nov 10, 2009 at 3:19 AM, Sebastiaan van Erk sebs...@sebster.com mailto:sebs...@sebster.com wrote: Hi, I followed the instructions but still get: mysql delete from mytable; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed) mysql Regards, Sebastiaan Krishna Chandra Prajapati wrote: Hi Sebastiaan, Steps to fix the issue. 1. Do proper shutdown of mysql server. 2. Check the error log file that mysql server is shutdown properly. 3. Remove log files (ib_logfile0 and ib_logfile1). 4. Start mysql server (The log files will be created automatically) Thanks, Krishna On Sun, Nov 8, 2009 at 2:51 PM, Sebastiaan van Erk sebs...@sebster.com mailto:sebs...@sebster.com mailto:sebs...@sebster.com mailto:sebs...@sebster.com wrote: Hi all, I just recently upgraded to Ubuntu 9.10, but now I'm having all sorts of temp file problems. For example, when I try to delete a row and violate a contraint I get: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed) Instead of telling me which constraint is violated, it tells me the temp file creation failed. I have no reason why it failed, I don't see any error messages in the log. To solve this problem I tried to make a tmpfs partition (I thought, maybe somehow my using ext4 might be a problem): mkdir /tmpfs mount -t tmpfs -o size=1g tmpfs /tmpfs mkdir /tmpfs/mysql chown mysql:mysql and changed the tmpdir in the mysql config to /tmpfs/mysql tmpdir=/tmpfs/mysql But then mysql fails on startup: /usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7' (Errcode: 13) 091108 10:12:46 InnoDB: Error: unable to create temporary file; errno: 13 091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error. 091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. I checked error code 13, which is permission denied, but I don't understand this, because if I change tmpdir to /tmp/mysql it does work, and I have: $ ls -ld /tmp/mysql drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql $ ls -ld /tmpfs/mysql drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql So I don't see the difference Has anyone encountered similar problems, or know what's going on here? Best regards, Sebastiaan smime.p7s Description: S/MIME Cryptographic Signature
Re: Temp file issues on Ubuntu 9.10
Hi, Not that I am aware of. I'm just running a standard out of the box 9.10 Ubuntu, upgraded from 9.04. Note that when I'm using /tmp/mysql or /tmp as tmpdir, at least InnoDB starts up, but then I still get the strange temp file operation failed message instead of the constraint when I try to do a delete of a record which would violate a constraint. So it seems there are at least 2 problems: 1) tmpfs refuses to work for me 2) even on a working tmpdir, temp file creations fails in some cases I'm thinking of doing a reinstall of my system (back to 9.04, why o why did I have to upgrade in the first place). Regards, Sebastiaan Johnny Withers wrote: Are you running selinux? On Sunday, November 8, 2009, Sebastiaan van Erk sebs...@sebster.com wrote: Hi, $ ls -ld /tmp/mysql drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql $ ls -ld /tmpfs/mysql drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql So I don't see the difference Has anyone encountered similar problems, or know what's going on here? Best regards, Sebastiaan This might just be a typo, but the chown statement you gave us didn't have a target and so would not have affected the relevant directories: mkdir /tmpfs mount -t tmpfs -o size=1g tmpfs /tmpfs mkdir /tmpfs/mysql chown mysql:mysql Probably should be: chown -R mysql:mysql /tmpfs/mysql john Hi, Thanks, yes that's a typo indeed, sorry I didn't catch it before sending the mail. The ls output was copy-pasted from a terminal and there the ownerships are correct. Regards, Sebastiaan smime.p7s Description: S/MIME Cryptographic Signature
Re: Temp file issues on Ubuntu 9.10
Hi, I followed the instructions but still get: mysql delete from mytable; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed) mysql Regards, Sebastiaan Krishna Chandra Prajapati wrote: Hi Sebastiaan, Steps to fix the issue. 1. Do proper shutdown of mysql server. 2. Check the error log file that mysql server is shutdown properly. 3. Remove log files (ib_logfile0 and ib_logfile1). 4. Start mysql server (The log files will be created automatically) Thanks, Krishna On Sun, Nov 8, 2009 at 2:51 PM, Sebastiaan van Erk sebs...@sebster.com mailto:sebs...@sebster.com wrote: Hi all, I just recently upgraded to Ubuntu 9.10, but now I'm having all sorts of temp file problems. For example, when I try to delete a row and violate a contraint I get: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed) Instead of telling me which constraint is violated, it tells me the temp file creation failed. I have no reason why it failed, I don't see any error messages in the log. To solve this problem I tried to make a tmpfs partition (I thought, maybe somehow my using ext4 might be a problem): mkdir /tmpfs mount -t tmpfs -o size=1g tmpfs /tmpfs mkdir /tmpfs/mysql chown mysql:mysql and changed the tmpdir in the mysql config to /tmpfs/mysql tmpdir=/tmpfs/mysql But then mysql fails on startup: /usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7' (Errcode: 13) 091108 10:12:46 InnoDB: Error: unable to create temporary file; errno: 13 091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error. 091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. I checked error code 13, which is permission denied, but I don't understand this, because if I change tmpdir to /tmp/mysql it does work, and I have: $ ls -ld /tmp/mysql drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql $ ls -ld /tmpfs/mysql drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql So I don't see the difference Has anyone encountered similar problems, or know what's going on here? Best regards, Sebastiaan smime.p7s Description: S/MIME Cryptographic Signature
Cross-table constraint
Hi, I have the following model: Domain (*)-(1) Account (*)-(1) User That is, each user belongs to exactly 1 account, an account can have multiple users; each account belongs to a single domain, and a domain can have multiple accounts. A user has an email address, which must be unique across the domain (since it's used to log into that domain: on login you select the domain and then enter your email and password). However, the email address need *not* be unique across different domains. Thus, I want the following combination to be unique: (Domain.id, User.email) Is there any way I can do this without duplicating the domain id in the User table? Regards, Sebastiaan smime.p7s Description: S/MIME Cryptographic Signature
Temp file issues on Ubuntu 9.10
Hi all, I just recently upgraded to Ubuntu 9.10, but now I'm having all sorts of temp file problems. For example, when I try to delete a row and violate a contraint I get: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed) Instead of telling me which constraint is violated, it tells me the temp file creation failed. I have no reason why it failed, I don't see any error messages in the log. To solve this problem I tried to make a tmpfs partition (I thought, maybe somehow my using ext4 might be a problem): mkdir /tmpfs mount -t tmpfs -o size=1g tmpfs /tmpfs mkdir /tmpfs/mysql chown mysql:mysql and changed the tmpdir in the mysql config to /tmpfs/mysql tmpdir=/tmpfs/mysql But then mysql fails on startup: /usr/sbin/mysqld: Can't create/write to file '/tmpfs/mysql/ibGgjPv7' (Errcode: 13) 091108 10:12:46 InnoDB: Error: unable to create temporary file; errno: 13 091108 10:12:46 [ERROR] Plugin 'InnoDB' init function returned error. 091108 10:12:46 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. I checked error code 13, which is permission denied, but I don't understand this, because if I change tmpdir to /tmp/mysql it does work, and I have: $ ls -ld /tmp/mysql drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql $ ls -ld /tmpfs/mysql drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql So I don't see the difference Has anyone encountered similar problems, or know what's going on here? Best regards, Sebastiaan smime.p7s Description: S/MIME Cryptographic Signature
Re: Temp file issues on Ubuntu 9.10
Hi, $ ls -ld /tmp/mysql drwxr-xr-x 2 mysql mysql 4096 2009-11-08 10:14 /tmp/mysql $ ls -ld /tmpfs/mysql drwxr-xr-x 2 mysql mysql 40 2009-11-08 10:12 /tmpfs/mysql So I don't see the difference Has anyone encountered similar problems, or know what's going on here? Best regards, Sebastiaan This might just be a typo, but the chown statement you gave us didn't have a target and so would not have affected the relevant directories: mkdir /tmpfs mount -t tmpfs -o size=1g tmpfs /tmpfs mkdir /tmpfs/mysql chown mysql:mysql Probably should be: chown -R mysql:mysql /tmpfs/mysql john Hi, Thanks, yes that's a typo indeed, sorry I didn't catch it before sending the mail. The ls output was copy-pasted from a terminal and there the ownerships are correct. Regards, Sebastiaan smime.p7s Description: S/MIME Cryptographic Signature
Re: Problem w/ mysqldump
On 9/2/2009 3:43 AM Victor Subervi said... Hi: I have the following python code: import os os.system(mysqldump -u root -pPASSWORD --opt spreadsheets dump.sql) First, test this at the system command line -- you'll likely get an empty file there as well, so calling from within python simply does the same. Then read the mysqldump docs for the command arguments and supply the database name... Emile This nicely creates the file...but the file is empty! The database exists and has lots of data, I double-checked it. If there is nothing wrong with my code, is there some way to do the same thing from within MySQL? Can I at least print everything to screen and copy it? Where would I find my database in Windoze? TIA, Victor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL View
On Mon, Feb 9, 2009 at 3:41 PM, Steven Buehler wrote: Ok, I just saw a post about using view's in mysql. I tried to look it up and found how to use it, but my question is: what is a view and why would you use it? The problem with any definition of an object in a database is that there are multiple definitions. Usually on the one hand you have the definition from abstract relational theory, and on the other hand you have the definition from actual working databases. So I am not going to bother with a definition, I will try to explain how a view works internally inside database code. The easiest way to understand a view is to consider a view as a macro that gets expanded during the execution of every query that references that view in its FROM. Lets take for example the view that your DBA has defined for you using: CREATE VIEW x AS SELECT * FROM y INNER JOIN z ON y.id = z.id; Then you query that view with the query: SELECT a FROM x; What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Notice that I have done nothing but replace x with its definition between parenthesis. And this results in a valid query that can be executed. And that is exactly what the database will do. It will do this substitution and then it will run the result of that substitution as if it were the query that you submitted. Obviously a bit more will go on behind the scenes to handle things like permissions and optimizations (especially if you get to databases that have more functionality then MySQL), but this is really all there is to it. A view is a simple macro that assigns an alias to a select statement, and when you reference that alias the select statement will get substituted back in. Jochem -- Jochem van Dieten http://jochem.vandieten.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: MySQL View
On Tue, Feb 10, 2009 at 1:47 PM, Steven Buehler wrote: From: Jochem van Dieten: What the database will do for you behind the scenes is expand your usage of the view. In effect, the database will replace x with its definition. So your query SELECT a FROM x; gets expanded to: SELECT a FROM (SELECT * FROM y INNER JOIN z ON y.id = z.id); Not sure about the other poster, but this helps explain it to me. If I understand you correctly, if I have multiple tables with many columns in them, but have several queries that need to pull only a few columns from each and put them together, it is probably best to create a view to do this so that I don't have to keep running joins in my queries? No. I am explicitly not saying how you should use views. I am just telling you how they work. But to give you some examples of how you could use views (I am still not saying how you should use views): 1. Use views to replace repetitive elements in queries. If you have lots of queries that perform the same join or filter, put it in a view. That has no semantic value, but you save yourself some typing. 2. Use views to manage permissions. If people have only access to a subset of the data, revoke their permissions on the table and define a view that has exactly the data that they have access to. Then give them permissions on the view. 3. Use views to define new schema elements that have meaning. If you have a normalized schema an invoice may be spread over a dozen tables (customer, invoice, invoiceline, item, price, shipping, payment, account etc.). You can define a view with all the proper joins and filters that groups that together so you get all the data at once. (Some people may argue that this is the same as no. 1, but I think it is an important distinction that the view represents an actual object: an invoice as you print and send them.) 4. your great view usage here Even if I reboot the computer, the view will still be there when it comes back up too? Yes, views are persitent. Jochem -- Jochem van Dieten http://jochem.vandieten.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: Updatable view using subquery??
On Mon, Feb 9, 2009 at 1:17 PM, blue.trapez...@gmail.com wrote: mysql CREATE VIEW v_aa AS - SELECT * - FROM flight AS f - WHERE f.RouteID IN - (SELECT r.RouteID - FROM route AS r - WHERE r.To= - (SELECT a.AirportID - FROM airport AS a - WHERE a.AirportCode='SIN') - ) - ORDER BY FlightID DESC; Query OK, 0 rows affected (0.02 sec) mysql insert into v_aa - values (1,1141,3145); Query OK, 1 row affected (0.00 sec) But according to the MySQL manual, a view is not updatable if it contains any of the following:...subquery in the select list. Your subquery is not in the select list, it is in the where. A subquery in the select list would be: CREATE VIEW v_aa AS SELECT * , (SELECT MAX(x) FROM y) AS z FROM flight This is not updatable because there is no sensible way to propagate changes to the y base table. Jochem -- Jochem van Dieten http://jochem.vandieten.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: Distinct Query Problem
On Fri, Dec 19, 2008 at 7:03 PM, Tompkins Neil wrote: Basically each product is listed in the master table, and can have a number of suppliers linked to it (ProductTB). The query above will show me a list of products for all suppliers for a particular product. However I want to be able to show the lowest price product from just the lowest priced supplier. Search the manual for group-wise maximum. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MBRWithin bug?
Chris, I might be being an idiot. Yes, you are :-) -122.1529 is between -121.148 and -121.1575 This is not true! -122.something cannot be between -121.somethingother and -121.somethingelse -121.1529 is between -121.148 and -121.1575 Regards, -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join question
Dear all, I have two tables,let's call then a and b: Table a: CUI1|CUI2 C001|C002 C002|C003 C003|C055 C004|C002 ... Table b: CUI|STY C001|T001 C002|T002 C003|T003 C004|T004 C005|T006 C055|T061 .. And the join table should be: T001|T002 T002|T003 T003|T061 T004|T002 ... I assume that the third table should be the result. If you need to store the result in a table you can use a INSERT ... SELECT query instead of only a SELECT (look INSERT...SELECT up in the online manual). So,I should convert table a according to table b. Thank you in advance for all your help My approach would be the opposite: SELECT t1.`STY`, t3.`STY` FROM `table_b` AS t1 JOIN `table_a` AS t2 ON t1.`CUI` = t2.`CUI1` JOIN `table_b` AS t3 ON t2.`CUI2`=t3.`CUI`; table_a determines which records from table_b must be connected. -- Jigal van Hemert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching subtables
Sebastian Mendel wrote: Werner Van Belle schrieb: Hello, You might find the following challenging -or- you might now the answer :-) Table Q Subtable, field, val, ID A 1 a42 A 2 b42 B 1 a78 B 2 t78 B 3 o78 C 1 u23 Table R Subtableid, field, val A 1 a A 2 b Table S Subtableid, field, val B 1 a B 2 t Table T Subtableid, field, val C 1 u A 1 a A 2 b We now want to check whether table R is fully contained in table Q and what the ID is. In this case the answer should be 42. However if we would use table S and mathc it against table Q, then we should not get 78 back since field 3 is missing in table S. Also, we might want to perform this operation in batch mode, where we provide a table such as T for which we then should get the return value Subtable, field, val, ID A 1 a42 A 2 b42 C 1 u23 Is there anybody that bumped into a similar query and was able to solve it satisfactory ? you can do a OUTER JOIN on subtableid, and than check for NULL values (with HAVING), which means that at least one field is missing in one of the tables with sub selects: untested: SELECT Subtable, ID FROM `T` WHERE ID NOT IN ( SELECT ID FROM T OUTER JOIN Q ON T.Subtable = Q.Subtable AND T.field = Q.field AND T.val = Q.val HAVING ISNULL(Q.ID) OR ISNULL(T.ID) ) Thanks for the hint. You query helped me along. Currently I have something like SELECT Q.subtable, Q.field, Q.id FROM Q JOIN T ON T.subtable=Q.subtable and T.field=Q.field and T.val=Q.val WHERE ID not in (SELECT distinct ID FROM Q LEFT OUTER JOIN T ON T.subtable=Q.subtable and T.field=Q.field and T.val=Q.val WHERE T.field is NULL) which does a large part of the job. Wkr, -- Dr. Werner Van Belle http://werner.sigtrans.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching subtables
Hello, You might find the following challenging -or- you might now the answer :-) Table Q Subtable, field, val, ID A 1 a42 A 2 b42 B 1 a78 B 2 t78 B 3 o78 C 1 u23 Table R Subtableid, field, val A 1 a A 2 b Table S Subtableid, field, val B 1 a B 2 t Table T Subtableid, field, val C 1 u A 1 a A 2 b We now want to check whether table R is fully contained in table Q and what the ID is. In this case the answer should be 42. However if we would use table S and mathc it against table Q, then we should not get 78 back since field 3 is missing in table S. Also, we might want to perform this operation in batch mode, where we provide a table such as T for which we then should get the return value Subtable, field, val, ID A 1 a42 A 2 b42 C 1 u23 Is there anybody that bumped into a similar query and was able to solve it satisfactory ? -- Dr. Werner Van Belle http://werner.sigtrans.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Some way/tool to do this with database scripts backups
Hi, if i have for example a simple table call person with 'id' and 'name' how columns i can do a backup and get some file (A.sql) with all the inserts statements here, all fine but how i can do this? Have a look at the 'mysqldump' command line utility. It goes something like 'mysqldump -u user -p password -h host database [table] output.sql'. This will generate a text file with SQL statements that you can use to recreate the table. See http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html. for each insert statement generated in A.sql create a new B.sql, with update statements its possible do this? You could do this by editting the 'output.sql' file, changing the table name from A to B. Or you could execute the following SQL: 'CREATE TABLE B SELECT * FROM A'. See http://dev.mysql.com/doc/refman/5.0/en/create-table.html. even worst, if a have a table with 20 columns i need the way to generate the same B.sql but with my desired columns to update I am not sure I understand your question, but you could make a database export using 'mysqldump', then alter the statements in the resulting output file to create the tables you need... HTH, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: feature request: statement SELECT...(INSERT|UPDATE) :)
On Jan 30, 2008 12:50 PM, Dmitry E. Oboukhov wrote: Is it possible to add to the syntax of the INSERT operator appoximately in such way: SELECT list INSERT [IGNORE] INTO ... - an added one. SELECT list UPDATE - an added one. PS: I understand that adding the changes into a language is a very serious question that needs a great discussion but one never can tell, may be mysql developers will be interested in my proposal ;) I think it would be a good idea to look at the way other databases can do this and see if there is some common syntax that could also be implemented by MySQL. For instance PostgreSQL implements a INSERT / UPDATE / DELETE ... RETURNING ... statement. ISTM that is exactly the functionality you want, but with a different syntax. If there are no other (better) competing implementation syntaxes (I don't know any, but maybe other list members do), I would like MySQL to adopt the PostgreSQL example. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up via slave
Ananda, My apologies for the late reply. To answer your question: I use 'show slave hosts'. It returns: +---+---+--+---+---+ | Server_id | Host | Port | Rpl_recovery_rank | Master_id | +---+---+--+---+---+ |33 | nlvdhq21 | 3309 | 0 |32 | |34 | nlvdhq203 | 3309 | 0 |32 | +---+---+--+---+---+ Kind regards, Martijn -Original Message- From: Ananda Kumar [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 02, 2008 14:06 PM To: Martijn van den Burg Cc: Martin Goldman; mysql@lists.mysql.com Subject: Re: Backing up via slave Hi Martijn, Master will not have any information of SLAVE how r u doing this in you second step. - check the master servers to find out the names/ports of their slaves regards anandkl On 1/2/08, Martijn van den Burg [EMAIL PROTECTED] wrote: Hi Martin, Hi folks, I have two MySQL servers running in a master-slave configuration, and I want to set up a process for backing up our application's data in which backups are sent to a server at another location. Ideally, I'd like to do a full backup once a week, and then incremental backups every 6 hours. It seems to make the most sense for this to happen on the slave. I was thinking it could work something like this: Weekly job: - Stop slave - Flush and delete binlogs on slave - Use mysqldump to generate full backup on slave - Create a directory on the remote server for this week's backups, and copy the full backup file over to it - Start slave Every-6-hours job: - Stop slave - Flush binlogs on slave - Copy over any newly created binlog files from the slave to the current weekly directory on the remote server - Start slave Then, if I needed to restore the backup, I'd: - Copy the weekly directory from the remote server to the MySQL server - Play back the full backup on the MySQL server - Play back the binlogs on the MySQL server I'm just curious as to whether the more experienced folks here think this is a logical approach, and if so, whether there are any caveats in particular to watch out for. (I've already stumbled upon the fact that I need to set log-slave-updates in order to have binlogs on my slave to be incrementally backed up.) Any thoughts? If this is a totally boneheaded approach, how would you recommend going about it? Thanks, Martin We use replication solely for the purpose of creating backups. Because we use NetApp filers, there's no need to use mysqldump (which would take very long since we have of lot of data in MySQL). What I do is the following. Every hour: - stop slave threads - stop slave server - make snapshot of NetApp qtree and store it in an 'hourly' directory - start slave server - start slave threads Once a day: - do the same thing, store the snapshot in a 'daily' directory. The hourly snapshots are rotated, and we keep five of these. Every day a 'daily' snapshot is made which is kept online for 30 days. I haven't used Baron's tools yet, but I will definitely give them a try, since rebuilding the slave from scratch after replication cannot be reliably restarted (it has issues sometimes) takes much too long with the amount of data we have. Regular (daily) snapshots are created on the master as well, with a running server. This causes data inconsistencies (we use InnoDB as well) but that is of no concern: what it does for us is that it keeps backups of the master binary logs. That way I don't have to run the slaves with 'log-slave-updates', which saves diskspace (couple of gigs a day). I wrote a script that purges the master's binary logfiles, to prevent the disks from filling up with them. This script is run once a day, and goes something like this: - start - check the master servers to find out the names/ports of their slaves - check on the slaves that replication is running and that there's no replication lag - if the slave is not running: alert the admins by email, and quit - purge the binary logs on the master till 'now() - interval 1 days' - end Regards, Martijn -- The information contained in this communication
Killing resource hogs - automatically
Hi, My DEV server is used by many people creating apps throughout the company. Consequently, I have no insight in the efficiency or quality of their SQL. This is an accepted state of affairs. However, 'developers' have repeatedly managed to crash mysqld because their queries are huge, don't use indexes, etc. At those times, the load on the box (dual-CPU, 4GB RAM) is 23, and I can't even ssh into it anymore. Is there a way to kill queries automatically if they take longer than x seconds to complete? I'm not sure (form the documentation) if this is where 'interactive_timeout' can be used. Lowering max_join_size could be an option too (it's currently 100,000,000), but I don't want to break people's existing apps that may rely on this hight join size. Thanks, -- Martijn van den Burg ASML ITMS MySQL/TIBCO Support -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating temp file, modifying data and putting into other table
On Jan 17, 2008 9:02 PM, Kerry Frater wrote: Thanks for the input Jochem. If you wish to ignore my code and continue with your own code that of course is fine with me. But why do you expect me to continue to help you if you ignore me anyway? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating temp file, modifying data and putting into other table
On Jan 17, 2008 2:22 PM, Kerry Frater wrote: Can someone please advise. I am looking to create a multiuser friendly way of getting a subset number of rows from a table into another whilst making a modification. create temporary table Ttable1 (select * from masterlist where ref='ABCDE'); update Ttable1 set ref='SMI0C001'; insert into sublist select * from Ttable1; drop Ttable1; How about: INSERT INTO sublist (ref, field1, field2, field3) SELECT 'SMI0C001' , field1 , field2 , field3 FROM masterlist WHERE ref='ABCDE' ; Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up via slave
Hi Martin, Hi folks, I have two MySQL servers running in a master-slave configuration, and I want to set up a process for backing up our application's data in which backups are sent to a server at another location. Ideally, I'd like to do a full backup once a week, and then incremental backups every 6 hours. It seems to make the most sense for this to happen on the slave. I was thinking it could work something like this: Weekly job: - Stop slave - Flush and delete binlogs on slave - Use mysqldump to generate full backup on slave - Create a directory on the remote server for this week's backups, and copy the full backup file over to it - Start slave Every-6-hours job: - Stop slave - Flush binlogs on slave - Copy over any newly created binlog files from the slave to the current weekly directory on the remote server - Start slave Then, if I needed to restore the backup, I'd: - Copy the weekly directory from the remote server to the MySQL server - Play back the full backup on the MySQL server - Play back the binlogs on the MySQL server I'm just curious as to whether the more experienced folks here think this is a logical approach, and if so, whether there are any caveats in particular to watch out for. (I've already stumbled upon the fact that I need to set log-slave-updates in order to have binlogs on my slave to be incrementally backed up.) Any thoughts? If this is a totally boneheaded approach, how would you recommend going about it? Thanks, Martin We use replication solely for the purpose of creating backups. Because we use NetApp filers, there's no need to use mysqldump (which would take very long since we have of lot of data in MySQL). What I do is the following. Every hour: - stop slave threads - stop slave server - make snapshot of NetApp qtree and store it in an 'hourly' directory - start slave server - start slave threads Once a day: - do the same thing, store the snapshot in a 'daily' directory. The hourly snapshots are rotated, and we keep five of these. Every day a 'daily' snapshot is made which is kept online for 30 days. I haven't used Baron's tools yet, but I will definitely give them a try, since rebuilding the slave from scratch after replication cannot be reliably restarted (it has issues sometimes) takes much too long with the amount of data we have. Regular (daily) snapshots are created on the master as well, with a running server. This causes data inconsistencies (we use InnoDB as well) but that is of no concern: what it does for us is that it keeps backups of the master binary logs. That way I don't have to run the slaves with 'log-slave-updates', which saves diskspace (couple of gigs a day). I wrote a script that purges the master's binary logfiles, to prevent the disks from filling up with them. This script is run once a day, and goes something like this: - start - check the master servers to find out the names/ports of their slaves - check on the slaves that replication is running and that there's no replication lag - if the slave is not running: alert the admins by email, and quit - purge the binary logs on the master till 'now() - interval 1 days' - end Regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to set 'sql_warnings' in the config file
Hi, I'm having issues with replication breaking on one machine (Linux, x64) but continuing normal on the other (Solaris). Both run MySQL 5.0.18-standard. The only difference betweenn the configuration of teh two is the setting of sql_notes and sql_warnings. I want to find out if that causes the problem. According http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html I should be able to set them in the config file (I'm using mysqld_multi), but no matter what I use (sql_warnings=0, sql_warnings=off, change sql_warnings to sql-warnings): every time mysqld refuses to start, complaining that it does not know the variable 'sql_warnings'. How to set sql_notes and sql_warnings at server start up? Did I hit a bug? I am aware that there's a bug in 5.0.18 with the /display/ of the value of sql_warnings and -notes, http://bugs.mysql.com/bug.php?id=16195, could that be related? Regards, Martijn van den Burg -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to set 'sql_warnings' in the config file
Hi Baron, I know. But that would mean http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html is in error, as it says sql_warnings can be set in the option file. Regards, Martijn -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Baron Schwartz Sent: Thursday, December 20, 2007 15:50 PM To: Martijn van den Burg Cc: mysql@lists.mysql.com Subject: Re: How to set 'sql_warnings' in the config file That is not a mysqld option, it's a session variable: mysql select @@global.sql_warnings; ERROR 1238 (HY000): Variable 'sql_warnings' is a SESSION variable On Dec 20, 2007 9:14 AM, Martijn van den Burg [EMAIL PROTECTED] wrote: Hi, I'm having issues with replication breaking on one machine (Linux, x64) but continuing normal on the other (Solaris). Both run MySQL 5.0.18-standard. The only difference betweenn the configuration of teh two is the setting of sql_notes and sql_warnings. I want to find out if that causes the problem. According http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html I should be able to set them in the config file (I'm using mysqld_multi), but no matter what I use (sql_warnings=0, sql_warnings=off, change sql_warnings to sql-warnings): every time mysqld refuses to start, complaining that it does not know the variable 'sql_warnings'. How to set sql_notes and sql_warnings at server start up? Did I hit a bug? I am aware that there's a bug in 5.0.18 with the /display/ of the value of sql_warnings and -notes, http://bugs.mysql.com/bug.php?id=16195, could that be related? Regards, Martijn van den Burg -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. Unless explicitly stated otherwise in the body of this communication or the attachment thereto (if any), the information is provided on an AS-IS basis without any express or implied warranties or liabilities. To the extent you are relying on this information, you are doing so at your own risk. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reply-to is to originator rather than to list
On 10/21/07, Rob Wultsch wrote: I was previously on a list where the reply-to was setup as it is on the mysql list, with the originator receiving a response rather than list. It ended up that that setting was the default, and had not been changed when the list was setup. Is there a good reason why the reply-to is setup as it is on this list? If you could explain why the answer in the FAQ doesn't satisfy you we might be of more assistance. I forget to change the destination address for most every email I write, I would guess I am not alone, and I do not think that this is good for the list. I believe it is excellent for the list as it raises the bar. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: importing a single table from mysqldump
Hello, If it is a dump you can pipe it into mysql. If you have a csv like file you can import it with LOAD DATA LOCAL INFILE like things. An example below: DROP TABLE IF EXISTS EnsgDescriptions; CREATE TABLE IF NOT EXISTS EnsgDescriptions (stable_id VARCHAR(128) PRIMARY KEY, description VARCHAR(128)); LOAD DATA LOCAL INFILE 'imports/ensgdescriptions.csv' INTO TABLE EnsgDescriptions; Wkr (don't write this in your script :-), -- Dr. Werner Van Belle http://werner.sigtrans.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connect To Foreign Tables
Hello, On Friday 05 October 2007 01:11:35 James Card wrote: The application I'm working on uses MySQL 5.0.41 and we also need to retrieve some data from a SQL-Server database in another department. SQL-Server has a feature (that I haven't tested) that allows it to make an ODBC connection to a foreign database and treat its tables as if they were local. Is there any way to accomplish something similar in MySQL? I'd love to be able to create a view that joins data from my local tables with that from SQL_Server. Did you get any response ? I'm quite interested in this question too. Recently I had to join around 1 G of local data with the genome databases in Germany and found that I either had to upload 1G of data to a temporary table or download 54G of data. Neither was particularly inspiring, so it would have been nice if I could just use various databases through the same local server. Are there any solutions for this kind of problem ? Werner,- -- Dr. Werner Van Belle http://werner.sigtrans.org/ signature.asc Description: This is a digitally signed message part.
Re: Query error
The comma at the end of the SELECT statement needs to be removed Naz Gassiep wrote: Hi, I'm trying to execute this query: SELECT group_post.group_thread_id, FROM group_post LEFT OUTER JOIN group_post_moderation ON (group_post.group_post_id = group_post_moderation.group_post_id) LEFT OUTER JOIN group_post_mod_option ON (group_post_moderation.group_post_moderation_option = group_post_mod_option.option_id) WHERE group_thread_id = '6' GROUP BY group_post.group_thread_id ORDER BY lft; But when I do, I get this error: ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right sy ntax to use near 'FROM group_post LEFT OUTER JOIN group_post_moderation ON (grou Can anyone please tell me what is causing that? I'm using MySQL4. Thanks, - Naz.
Re: Data Warehousing and MySQL vs PostgreSQL
On 7/26/07, Andrew Armstrong wrote: * Table 1: 80,000,000 rows - 9.5 GB * Table 2: 1,000,000,000 rows - 8.9 GB This is a generic star schema design for data warehousing. I have read that it is better if perhaps partitioning is implemented, where new data is added to a partitioned table (eg, that represents a single day) and then when those samples expire - simply drop the partition. I believe partitioning would solve issues with SELECT and INSERT performance because the actual index tree size (and data in the table itself) would be reduced. While partitioning will most likely alleviate your DML woes, partially by breaking it up and partially by changing DML to DDL, we can not make any reasonable statement about your SELECT performance since we don't know what type of queries you will be running. If your queries are going to cross all partitions partitioning is not going to help you much, if your queries typically only touch one partition it will help a lot. I am a bit hesitant however to go with PostgreSQL because the partitioning system seems a bit less easier to work with than MySQL (5.1's) implementation; as I would need to maintain my own master table for clients to query, and I do not think partition pruning is in use at this time (eg, the analyser can ignore partitions that wont contain any matching data based on the query being issued). The PostgreSQL planner will prune every partition it can determine not to have any matching data. Whether it can determine so depends on the query and it can be a bit picky (e.g. if you pick the wrong datatype for a timestamp it may not be a candidate for pruning because it has a timezone, which is a bit unexpected if you are not used to timestamps having a timezone at all). Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RSS Style Feeds from Itunes.xml to External Publicly Accessible DB
Greetings All: It's been a while since I've posted, but I wanted to see if any of you have approached the following challenge. I have a Mac that runs iTunes a few hours a day, sometimes with Ampache feeding playlists, to it and I'd like to expose my recently listened to songs to a public web-site for a see what Van's been listening to lately module on the site. I can cron this from a local linux replicating server that can access the xml playlist via samba from the imac. Disconnected network states are acceptable. My application requires the expertise of itunes.xml to mysql db developers more than anyone else. I need the locally connected mysql server to be able to get the itunes.xml via samba to parse through this xml file and insert into a table on the linux mysql server, which I'll cron out updates to the publicly available mysql server, which will accept the db update and render via a php script this list (song, number plays, skip count, etc.) as an include for publication from other pages on this site. Anyone started on such a thing? If not, and anyone else interested, I'll post my final module, and implementation. Thanks, Van -- === Ded Serius Music http://www.dedserius.com/ === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: off-topic unsubscribe concern
On 7/8/07, Mogens Melander wrote: On Fri, July 6, 2007 17:55, Michael Dykman wrote: I have been on this list for a pretty long time but in the last few months I have started to receive random 'confirm unsubscribe' messages..They always seem to originate from a Roadrunner IP (I have not thoroughly tested that hypothesis). I have no accounts on or near roadrunner, so I doubt I am inadvertantly kicking these off, which was my first theory. Is anyone else suffering from this or is it just me? I've seen quite few lately. Everybody who doesn't remove unnecessary junk from his messages before he posts them is 'suffering'. Messages send to you from the list include your customized unsubscribe link. If you include full messages when responding to them you are sending out your own custom unsubscribe link to all subscribers of this list, including all harvesters, public archives and broken preload-cachers. Since you are sending out your custom unsubscribe link quite literally by the thousands, are you really surprised the link is followed occasionally? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] If you don't want people to follow this link, why are you including it? To stop the 'suffering', stop including junk that doesn't belong in outgoing email. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data security - help required
Well, you can save all data encoded in the database: http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_encode - Mike Chris schreef: Ratheesh K J wrote: Hello all, I have a requirement of maintaining some secret information in the database. And this information should not be visible/accessible to any other person but the owner of the data. Whilst I know that encryption/decryption is the solution for this, are there any other level of security that I can provide to this? Which is the best security technique used in MySQL to store seceret information. PS: Even the database admin should not be able to access anybody else's information Then you're stuffed - *someone* has to be able to see everything so you can do a mysqldump. *Someone* has to be able to see everything so you can grant permissions to the other users too :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Partial char key not used in conjuction with inequality comparison (MySQL5)
Hello list! I've noticed in MySQL 5.0 partial keys on character fields aren't always used. In 4.1 they were. They seem not to be used when using inequality comparison. I'm not sure whether this is a bug or intended (in the latter case I have to work around it to get the speed I got with 4.1 back into 5.0) You can try for yourselves: Setup: CREATE TABLE t (T varchar(16) NOT NULL,KEY (T(1))); INSERT INTO t SET T=test1; INSERT INTO t SET T=test2; INSERT INTO t SET T=test2; Test: EXPLAIN SELECT * FROM t WHERE T=x; Result: key T used EXPLAIN SELECT * FROM t WHERE T!=x; Result: key T _unused_ Adding a key on entire field T works, but that's wasting a lot of space because I only want to test whether a certain field is empty or not. Important note: MySQL4.1 *did* use key T in the second query! Can anybody concur whether this happens for them too? And if so, is this a bug? grtz, Thomas -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: monitoring SQL query response times
Is there a way to monitor SQL query response times? Mysqlperformanceblog has patches for higher granularity query log: http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/ You could always just wrap the query calls in between some time registration of your own. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any issues migrating Solaris - Linux?
Dear list, My company wants to migrate MySQL 5.0.18 from SPARC/Solaris 8 to Intel/RedHat Enterprise Linux 4 update 4. Are there any incompatibilities or snags to be expected (expect from the endian issue, which will be solved by exporting/importing the data)? Thank you for your time, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with mysqldump and local-infile
Hi, I want to backup my databases with mysqldump, but mysqldump won't run because I use the 'local-infile=1' option in the my.cnf file: [client] port= 3306 socket = /tmp/mysql.sock local-infile= 1 This is because I want php and other clients to use local-infile. This works, by when I start mysqldump I got this error: ~ # mysqldump mysqldump: unknown variable 'local-infile=1' So, what is wrong? As far as I know local-infile is a valid option to put in the my.cnf. Is there a way to unset the local-infile option and start then start mysqldump or something? Or any other solution? Grtz, Mark van Herpen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql services to monitor.
Hi! Ananda Kumar said the following, On 28-Mar-07 06:15: Hi All, What are all the mysql services i need to monitor to make sure mysql db is running and send a pager when any one of these services go down. To check if a database is still running is diffrent then the actuall process. Do you want to check if MySQLd is running, or if a database is still accessible? A monitoring system is called Nagios, its worth taking a look at it (it can do both of the above described, but could be overkill if you only want to check a single service) http://nagios.org/ Bye, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why doesn't the InnoDB count() match table status?
On 3/27/07, Tim Lucia wrote: -Original Message- From: Maciej Dobrzanski Sent: Tuesday, March 27, 2007 6:46 AM To: mysql@lists.mysql.com Subject: Re: Why doesn't the InnoDB count() match table status? MyISAM and InnoDB (and there are plenty more). RDBMS is not an Office spreadsheet, you cannot simply change software as from OpenOffice Calc to MS Excel and expect everything will work the same. The migration can be done You can and ought to be able to expect it. After all, SQL is a standard. But the problem is not with the result of the query, but with the speed. And the speed of an implementation is not standardized by the ISO/IEC. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY question
Hello everybody, I got a small problem with ordering on en ENUM field. The values in this field are: - to be started - started - finished - canceled And i want to order on this field, but in the direction the are above here (and not alpabetically). Is that possible? - Mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY question
Thanks, that is also a solution. Friend of mine pointed me to the following: SELECT *, DATE_FORMAT(deadline, '%d-%m-%Y') as deadline_f, CASE `status` WHEN 'not yet started' then 1 WHEN 'in progress' then 4 WHEN 'finished' then 5 WHEN 'now hiring' then 3 WHEN 'waiting' then 2 WHEN 'closed' then 6 END AS sorted_grade FROM v_issue_project_task ORDER BY sorted_grade - Mike Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat Christophe Gregoir schreef: Hey Mike, Sounds like you would be better of with an ENUM of integers, e.g. ENUM(-1,1,2,3) where -1 stands for to be started, 1 for started and so on. To answer your question: ORDER BY `status` = 'to be started', `status` = 'started', `status` = 'finished', `status` = 'canceled' Mike van Hoof wrote: Hello everybody, I got a small problem with ordering on en ENUM field. The values in this field are: - to be started - started - finished - canceled And i want to order on this field, but in the direction the are above here (and not alpabetically). Is that possible? - Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Avaliablity mysql db.
Hi! I would start at: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-replication.html Bye, Ian Ananda Kumar said the following, On 16-Mar-07 11:04: Hi All, We are planing to develop and high available mysql db. Can anybody please point me to any good documentation. Also how stable is MySQL cluster and replication. regards anandkl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from SPARC/Solaris to AMD x86
Hi list, As part of a server virtualization project, we are going to migrate MySQL from SPARC/Solaris 8 to AMD/x86 Solaris 10. I seem to remember that it is possible to: - stop the SPARC server - move the mount point of the MySQL data directory to the Solaris/x86 server - start the AMD x86 server ...without running into architecture (little/big endian) issues. Am I right? Is this written out somewhere on dev.mysql.com? Thanks, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Key buffer partially unused - why
Hi, I have allocated 500MB to key_buffer_size, but only 324MB is in use (64%). Am I right to assume that this can mean one of the following: (1) all indexes have already been cached and together they are just 324 MB, Do find /var/lib/mysql -name *.MYI -ls (if you're using only MyISAM) to get a first hint on how much indices you actually have. Heh, thanks. I could have thought about that out myself. /sheepish (2) there is a limiting variable (open_files, inodb_open_files, for example) that prevents the key buffer to be more fully utilized. You can check at least open_files in SHOW STATUS IIRC. Key buffer usage is often hitting 100%, and just 0.01 tables are opened per second. Number of queries/second is 269. BTW, try mysqlreport to find other bottlenecks. Really good tool :) Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Key buffer partially unused - why
Hi, I have allocated 500MB to key_buffer_size, but only 324MB is in use (64%). Am I right to assume that this can mean one of the following: (1) all indexes have already been cached and together they are just 324 MB, (2) there is a limiting variable (open_files, inodb_open_files, for example) that prevents the key buffer to be more fully utilized. Key buffer usage is often hitting 100%, and just 0.01 tables are opened per second. Number of queries/second is 269. Setup: 5.0.18-standard-log Solaris 8 2 CPU, 4GB RAM Regards, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
On 2/5/07, Jim C. wrote:
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role VARCHAR NOT NULL,
CONSTRAINT role_check CHECK role IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'),
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
Well, here is what I got:
mysqlDump.postgres.working.sql 1398L, 13611680C written
[EMAIL PROTECTED] jims]$ psql inscom mysqlDump.postgres.working.sql 21 |
grep ERROR
ERROR: syntax error at or near , at character 164
ERROR: syntax error at or near role at character 218
Should be
CONSTRAINT role_check CHECK (role IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'))
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
On 2/5/07, Jim C. wrote: When I uncomment some of these statements I get an error in regards to a comma. What I'm afraid of is that perhaps there is a compatibility issue such that an INSERT command on Postgres can't take as many records as MySQL. What version are you running? Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL to Postgres
On 2/2/07, Jim C. [EMAIL PROTECTED] wrote:
I'm having to move some data from MySQL to Postgres. I used mysqldump
--compatible=postgresql, but the compatibility is extremely lacking.
It looks more like the person that designed the schema has payed very
little attention to the SQL standard. You can not blame anyone but the
designer for naming a field 'role' (which is a keyword in the SQL
standard) or using a non-standard set field type instead of a proper
lookup table.
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role
set('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest')
NOT NULL default ''
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
CREATE TABLE credits (
person integer NOT NULL default '0',
chanid int NOT NULL default '0',
starttime timestamp NOT NULL default '1970-01-01 00:00:00+00',
role VARCHAR NOT NULL,
CONSTRAINT role_check CHECK role IN
('actor','director','producer','executive_producer','writer','guest_star','host','adapter','presenter','commentator','guest'),
-- CONSTRAINT chanid_constraint0 UNIQUE
(chanid,starttime,person,role)
-- UNIQUE KEY chanid (chanid,starttime,person,role),
-- KEY person (person,role)
);
If that doesn't work, rename the role field. And it might be a good
idea to give this table a primary key.
Jochem
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to randomly select one value per group-by column?
Dear list, Suppose a table with articles that persons have ordered: create table t (pers_id int(6), odate date, art_id int(8)) I want to select one randomly choosen odate for every pers_id. I can easily select the minimum or the maximum odate: select pers_id, min(odate) from t group by pers_id What would like is something like: select pers_id, random_select(odate) from t group by pers_id Does anyone know how to do this? Cheers, Paul -- Paul B van den Berg, Manager InterAction database, http://www.iadb.nl Dept of Social Pharmacy, Pharmacoepidemiology and Pharmacotherapeutics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow query
Hello, i have the following query: SELECT DISTINCT ( Waarde ) AS bestemming FROM xml_kenmerk WHERE Omschrijving = 'Bestemming' AND IF ( DatumBegin IS NOT NULL AND DatumBegin != '-00-00', DatumBegin = CURDATE( ) , 1 =1 ) AND IF ( DatumEind IS NOT NULL AND DatumEind != '-00-00', DatumEind = CURDATE( ) , 1 =1 ) ORDER BY Waarde Table structure: CREATE TABLE `xml_kenmerk` ( `KenmerkRolid` int(11) NOT NULL auto_increment, `Omschrijving` varchar(255) default NULL, `Waarde` varchar(255) default NULL, `DatumBegin` date default NULL, `DatumEind` date default NULL, `OrganisatieRolid` int(11) unsigned default NULL, `RelatieRolid` int(11) unsigned default NULL, PRIMARY KEY (`KenmerkRolid`), KEY `OrganisatieRolid` (`OrganisatieRolid`), KEY `RelatieRolid` (`RelatieRolid`), KEY `Omschrijving` (`Omschrijving`), KEY `Waarde` (`Waarde`), KEY `DatumBegin` (`DatumBegin`), KEY `DatumEind` (`DatumEind`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2163460 ; and it contains approx. 7500 rows. But the query takes 1.5 seconds. Does anybody know how i can get bether preformance from this query? - Mike
Re: Slow query
Hello, I pinned down the problem to the order by line. If i leave this away the query is done in 0.05 seconds. - Mike Mike van Hoof schreef: Hello, i have the following query: SELECT DISTINCT ( Waarde ) AS bestemming FROM xml_kenmerk WHERE Omschrijving = 'Bestemming' AND IF ( DatumBegin IS NOT NULL AND DatumBegin != '-00-00', DatumBegin = CURDATE( ) , 1 =1 ) AND IF ( DatumEind IS NOT NULL AND DatumEind != '-00-00', DatumEind = CURDATE( ) , 1 =1 ) ORDER BY Waarde Table structure: CREATE TABLE `xml_kenmerk` ( `KenmerkRolid` int(11) NOT NULL auto_increment, `Omschrijving` varchar(255) default NULL, `Waarde` varchar(255) default NULL, `DatumBegin` date default NULL, `DatumEind` date default NULL, `OrganisatieRolid` int(11) unsigned default NULL, `RelatieRolid` int(11) unsigned default NULL, PRIMARY KEY (`KenmerkRolid`), KEY `OrganisatieRolid` (`OrganisatieRolid`), KEY `RelatieRolid` (`RelatieRolid`), KEY `Omschrijving` (`Omschrijving`), KEY `Waarde` (`Waarde`), KEY `DatumBegin` (`DatumBegin`), KEY `DatumEind` (`DatumEind`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2163460 ; and it contains approx. 7500 rows. But the query takes 1.5 seconds. Does anybody know how i can get bether preformance from this query? - Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL REGEXP help
Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL REGEXP help
Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike
Re: MYSQL REGEXP help
Hello, this doesn't work: mysql SELECT 'oer bv' REGEXP '[b|^b](!?[v$|v])'; ++ | 'oer bv' REGEXP '[b|^b](!?[v$|v])' | ++ | 1 | ++ 1 row in set (0.00 sec) He shouldn't select this one, because it says 'bv' and no other b Mike ViSolve DB Team schreef: Hi, [ERROR 1139 (42000): Got error 'repetition-operator operand invalid' from regexp] because, In your query, '!' is an Operator and ? is a wild character. Only wildcharacters should be follow the Operators. Try with. SELECT 'boer bv' REGEXP '[b|^b](!?[v$|v])'; Thanks ViSolve DB Team - Original Message - From: Mike van Hoof [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Monday, January 08, 2007 1:36 PM Subject: MYSQL REGEXP help Hello, i am try to make a regular expression work, but keep getting this error message: does anyone know how i can make it work? The query is: SELECT 'boer bv' REGEXP '[ b|^b](?![v$|v ])'; So it has to match each starting 'b' and all the b's pf following words. But now followed by a v(line end) or a v followed by a space. so it should match: 'b test' 'test b' 'test b bv' 'bv b test' and NOT 'test bv' 'bv test' Any idea's?! Thanks, mike -- Medusa, Media Usage Advice B.V. Science Park Eindhoven 5216 5692 EG SON tel: 040-24 57 024 fax: 040-29 63 567 url: www.medusa.nl mail: [EMAIL PROTECTED] Uw bedrijf voor Multimedia op Maat -- 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 scaleable is MySQL 5's Innodb with multi-core CPU's?
On 1/1/07, mos wrote: At 12:49 PM 1/1/2007, Jochem van Dieten wrote: On 1/1/07, mos wrote: http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/ http://tweakers.net/reviews/649/6 Has this been fixed? As the article on the MySQL Performance Blog mentioned, a fix from InnoDB has been integrated into 5.30. 5.0.30 I meant. Tweakers.net has already tested this fix and it does show some improvement, but it still has a long way to go: http://tweakers.net/reviews/661/6 Yes Innodb has a long ways to go and I'm wondering if it is fixable so the performance is more linear. As it is, performance in the Tweakers' charts drop dramatically (tanks?) after 7 concurrent users even for version 5.03. I know Innodb works best if the table fits into memory, but for me that isn't practical (at least on one machine) because the tables will grow over time and I don't want to crash into a wall when the table exceeds memory capacity of the machine. The tweakers.net tests are with all data in memory. It is very well possible that the scaling behaviour of an I/O bound InnoDB application is very different. I would expect it to show a lower peak performance, but also a smaller drop-off after the peak. So I'm wondering how high traffic websites that use Innodb can overcome this problem? Google GMail, Craigs List, TIcket Master, Yahoo etc all have high number of updates per second, so there must be an InnoDb solution, right? What exactly do they use MySQL for? For instance, doesn't Google just write e-mail on a filesystems (GFS is essentially append-only) and only keep a small amount of meta-data somewhere that is actually updated? Design-wise I would expect their infrastructure to have much more in common with Dovecot then an email-in-a-database solution. I know these questions are pretty much rhetorical, but I thought I'd bounce this off of you guys to see what the best approach is for a high traffic transactional web site. If you were going to write one of these web sites I mentioned, would you still use InnoDb? I probably wouldn't use a relational database at all. Convenient as they may be due to their standardized interface (SQL), they perform much worse then a dedicated solution. And if I were to use a relational database, I would probably scale out instead of up. You might find ftp://ftp.research.microsoft.com/pub/tr/TR-2004-107.pdf an interesting read. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How scaleable is MySQL 5's Innodb with multi-core CPU's?
On 1/1/07, mos wrote: Is there a problem with InnoDb scaling with multi-processor CPU's? Apparently after reading the Tweakers.net article, with only 40 simultaneous users the performance of MySQL 5 will collapse. http://www.mysqlperformanceblog.com/2006/11/30/interesting-mysql-and-postgresql-benchmarks/ http://tweakers.net/reviews/649/6 Has this been fixed? As the article on the MySQL Performance Blog mentioned, a fix from InnoDB has been integrated into 5.30. Tweakers.net has already tested this fix and it does show some improvement, but it still has a long way to go: http://tweakers.net/reviews/661/6 Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: interesting benchmark at tweakers.net
On 12/19/06, David Sparks wrote: I noticed an interesting benchmark at tweakers.net that shows mysql not scaling very well on hyperthreading and multicore cpus (see links at end of email). Does anyone know what engine they are using for their tests? (Innodb, myisam, berkdb heheh) InnoDB, the first installment of the series of tests had the following configuration: http://tweakers.net/reviews/620/2 And I would venture that these results are not because they did horrible things to their MySQL configuration. On the second installment of the series http://tweakers.net/reviews/633/7 engineers from Sun were brought in and they consulted with engineers from MySQL and on the last installment Peter Zaitsev of the MySQL Performance Blog did a review of their configuration: http://tweakers.net/reviews/660/6 In fact they seem to show that postgres is a faster db overall. This goes against my personal experience where I benchmarked a real world app we have and found mysql 10 to 100 times faster In my experience such a large performance difference between PostgreSQL and MySQL can always be attributed to some obvious difference in the internals. For instance because PostgreSQL can use indexes on datatypes that MySQL can not (or doesn't even have) or because queries are very repetitive and MySQL can use the query cache which PostgreSQL does not have. Without such obvious factors I am very inclined to attribute differences of the magnitude you are claiming to a difference in knowledge of the databases at hand. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
