Re: Stored Procedure help
The order makes quite a big difference, actually. In this case it ensures that the ordering of the values in the sort_id column is maintained, even though the numbers are different. Say this is your data (I have ignored the category thingy for now): SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 12 | |2 | 13 | |3 | 11 | +--+-+ Now if I run this the update without the order by: UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category; The result will be: SELECT id, sort_id FROM documents; +--+-+ | id | sort_id | +--+-+ |1 | 1 | |2 | 2 | |3 | 3 | +--+-+ Whereas with the order by UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; the result would be: +--+-+ | id | sort_id | +--+-+ |1 | 2 | |2 | 3 | |3 | 1 | +--+-+ /Karlsson Keith Murphy skrev 2014-07-14 15:31: I would second what m. dykman says. There is no reason I can think of that you would even be doing the order by clause. keith On Sun, Jul 13, 2014 at 11:16 PM, yoku ts. yoku0...@gmail.com wrote: Would you try this? CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN SET @a = 0; UPDATE documents SET sort_id = (@a := @a + 1) WHERE document_category = category ORDER BY sort_id; END // 2014-07-14 11:42 GMT+09:00 Don Wieland d...@pointmade.net: I am trying to create this stored procedure, but can't understand why my editor is chocking on it. Little help please: DELIMITER // CREATE PROCEDURE `reset_sortid` (IN category INT(11)) BEGIN DECLARE a INT; SET a = 0; UPDATE documents SET sort_id = (a := a + 1) WHERE document_category = category ORDER BY sort_id; END // Don Wieland d...@pointmade.net http://www.pointmade.net https://www.facebook.com/pointmade.band -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Why is mySQL not respecting foreign characters as different
That is because of the collation. It's the collations that determines character equality. I can't tell what the collation is in your case for the columns us, es, de, es and fr. Also, that you match character sets in different columns is usually not a good idea, unless you have a good reason for it (and there are exception to this rule). Try specifying the utf8_bin collation instead and that will work. For a more complete explanation of all this, read my blog on this subject: http://karlssonondatabases.blogspot.nl/2012/11/character-sets-collations-utf-8-and-all.html /Karlsson Daevid Vincent skrev 2013-09-26 23:44: How come MySQL is not differentiating between these characters? SELECT text_id, us, de, es, fr FROM texts WHERE us = fr; Results in matching here. Notice the difference in the scene vs scène text_id us es de fr -- -- -- - all_page_scene scene escena Filmszene scène I wold expect this NOT to match. Do I have to add something to my query to tell MySQL to respect other character sets as different? CREATE TABLE `texts` ( `text_id` varchar(50) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '', `us` text, `es` text, `de` text, `fr` text, PRIMARY KEY (`text_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 FieldType Collation NullKey Default Extra Privileges Comment --- --- - -- -- --- -- --- - text_id varchar(50) latin1_general_ci NO PRI select,insert,update,references us text utf8_general_ciYES (NULL) select,insert,update,references es text utf8_general_ciYES (NULL) select,insert,update,references de text utf8_general_ciYES (NULL) select,insert,update,references fr text utf8_general_ciYES (NULL) select,insert,update,references -- Anders Karlsson, Senior Sales Engineer SkySQL | t: +46 708-608-121 | Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MyQuery 3.4.1 Released
I have released MyQuery 3.4.1 today. MyQuery is a Windows based Ad-Hoc query tool with some interesting features: - Colour coded syntax - Based on Scintilla - Code folding - Based on Scintilla - Ability to strat a script run inside the script. - Support for error/stop/continue script editing - Highly configurable with user defined tools and many other features - Powerful plugin API And a bunch more things. MyQuery is completely free and Open Source. Version 3.4.1 is a minor bugfix version, where the main thing being fixed is a bug that caused issues to run MyQuery without Admin rights on Windows 7. Read more on the blog here: http://karlssonondatabases.blogspot.com/2010/11/announcement-myquery-341-released.html Or just go ahead and download it from here: http://sourceforge.net/projects/myquery/ Best regards Anders Karlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQLStats 1.1 available
If you downloaded this earlier today, note that there is now a 1.2 version available, that also shows ROWS_EXAMINED in INFORMATION_SCHEMA tables. /Karlsson Claudio Nanni wrote On 2010-11-24 09:02: Cool! On Nov 24, 2010 8:46 AM, Anders Karlsson and...@recordedfuture.com mailto:and...@recordedfuture.com wrote: SQLStats is a MySQL 5.5 plugin that allows MySQL SQL Statement monitoring in real time, without any Proxies, source code modifications, different connectors or anything. Read more on my blog here: http://karlssonondatabases.blogspot.com/2010/11/monitoring-mysql-sql-statement-way-it.html The plugin is GPL and is downloadable from sourceforge here: https://sourceforge.net/projects/sqlstats/ Best regards Anders Karlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
SQLStats 1.1 available
SQLStats is a MySQL 5.5 plugin that allows MySQL SQL Statement monitoring in real time, without any Proxies, source code modifications, different connectors or anything. Read more on my blog here: http://karlssonondatabases.blogspot.com/2010/11/monitoring-mysql-sql-statement-way-it.html The plugin is GPL and is downloadable from sourceforge here: https://sourceforge.net/projects/sqlstats/ Best regards Anders Karlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Death of MySQL popularity?
I was a MySQL Sales Engineer up til a few weeks ago. I spent 6+ year at MySQL. MySQL Classic never ever had InnoDB in it. Actually, the reason for the existence of MySQL Classic was just that: MySQL without InnoDB for OEMs. If you wanted a non-GPL MySQL, you had to pay for it. And if MySQL wanted a non-GPL InnoDB (in the old days, before Oracle), MySQL had to pay for it. So for the customers that only embedded MyISAM, they could get by by not having InnoDB included, which would lower the cost for MySQL, as there was no InnoDB licence to pay. Note in the above that this is OEM / Embedded only. For MySQL Enterprise customers InnoDB was always included. Why? Because this was a GPL distribution, using a GPL InnoDB, so no need for a InnoDB licence. Simple as that. In the old scheme then, when I was around, MySQL came in a few different shapes: - MySQL Embedded / OEM -- With or without InnoDB. Two different prices (MySQL Classic being the low end then). Commercial icence. - MySQL Enterprise -- The supported MySQL version. Different flavours mainly using different SLAs and different MySQL Enterprise Monitor functionalities. GPL Licence. - MySQL Community Edition - The good old GPL downloadable version. GPL Licence. /Karlsson Michael Dykman skrev 2010-11-08 22:47: I think Jorge Bruehe already has weighed in. That is about as direct as you are likely to hear unless you have Larry Ellison on facebook. - michael dykman On Mon, Nov 8, 2010 at 4:41 PM, Daevid Vincentdae...@daevid.com wrote: -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, November 04, 2010 2:26 AM To: jcbo...@yahoo.com Cc: MySQL Subject: Re: Death of MySQL popularity? You may want to read that again, but with your glasses on :-) Subscription means roughly commercial support. The (1) subscript means Features only available in Commercial Editions, and is noted *only* for Workbench SE, Enterprise Monitor, Enterprise Backup and Cluster Manager. I will join you in wondering whether that means Workbench is gonna go payware, though. On Thu, Nov 4, 2010 at 10:13 AM, Christoph Boget christoph.bo...@gmail.comwrote: http://www.mysql.com/products/ So the free version is going to include only MyISAM? And you won't be able to connect using MySQL Workbench (and presumably apps like MySQL Query Browser)? Otherwise you have to shell out $2k? Wow. I think it might be time to start seriously looking at Postgres... So there definitely is some confusion out there. Can someone from the @mysql / @oracle camp please confirm or deny the allegations? http://blog.herlein.com/2010/11/oracle-is-the-borg-enterprise-software-deve lopment-will-be-assimilated/ http://digitizor.com/2010/11/05/innodb-dropped-from-oracle-mysql-classic-ed ition/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyQuery 3.2.1
MyQuery 3.2.1 is now available for download from Sourceforge: https://sourceforge.net/project/myquery The highlights of this release is much enhanced keyboard navigation, including many more accelerators, main window Tab navigation and much more, so the Mouse isn't much needed if you don't want to or are unable to and printing support. Enhanced navigation also includes a dynamic help window with currently assigned shortcuts, and the ability to print a cheat-sheet from this. Printing support includes printing of both the SQL Query, with syntax highlightning, if you are using a colour printer that is, and query results. Printing also, as does the editor, screen results and the database, support UTF-8 data. Best regards and happy SQLing Anders Karlsson BTW. Sorry for cross-posting, but I felt this is interesting to both groups, and I don't really overflow these mailing lists anyway. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson (and...@mysql.com) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyQuery 3.2.1 released - Bad link
The correct link is: http://sourceforge.net/projects/myquery/ And if you are asking why I did not try the link before posting (which is a valid question), well, Sourceforge is slow these days. vry slow. And I was lazy. Sorry 'bout that. For next release, I'll find somewhere else to host MyQuery, and I'll also check links before posting. Best regards Anders Karlsson After a few beers, including an Anderson Valley Double IPA, not bad! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson (and...@mysql.com) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyQuery 3.1 Beta Released
MyQuery 3.1 is now ready for download from Sourceforge: http://sourceforge.net/projects/myquery/ The features of this release includes, but is not limited to: - UTF-8 support - Events management dialog - SQL Statement profiling - Much enhanced drag and drop support - Drag a dictionary object, and the CREATE statement is there for you! - SQL variables dialog with documentation links. If you have not used MyQuery before, then MyQuery is a Windows-only GUI tool for MySQL. It allows SQL statement and script editing, supports scripts compatible with the mysql-prompt, but also has several enhancement. Color syntax highlightning by using the Scintilla editor control. The MyQuery_3_1_setup.zip contains a single full-featured installer, including the documentation. Best regards Anders Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson (and...@mysql.com) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MyQuery 3.1 Beta Released
For those of you who downloaded 3.1, there was a slight bug in that, that should not cause any big operational problems, but it could cause a crash. So I have released a bugfixed version 3.1.1, available for download on Sourceforge, just like before. http://sourceforge.net/projects/myquery/ Best regards Anders Karlsson Anders Karlsson wrote: MyQuery 3.1 is now ready for download from Sourceforge: http://sourceforge.net/projects/myquery/ The features of this release includes, but is not limited to: - UTF-8 support - Events management dialog - SQL Statement profiling - Much enhanced drag and drop support - Drag a dictionary object, and the CREATE statement is there for you! - SQL variables dialog with documentation links. If you have not used MyQuery before, then MyQuery is a Windows-only GUI tool for MySQL. It allows SQL statement and script editing, supports scripts compatible with the mysql-prompt, but also has several enhancement. Color syntax highlightning by using the Scintilla editor control. The MyQuery_3_1_setup.zip contains a single full-featured installer, including the documentation. Best regards Anders Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson (and...@mysql.com) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyQuery 3.0 Alpha available now
Sorry for crossposting, but I felt this might be of intrerest to both lists. MyQuery 3.0 is an interactive Windows GUI based query tool for MySQL. Among the features are - Support running scripts from a position, up to a position of just a single query in the script, allowing you to run up to some error, correct the error, and then continue running the script. - Color syntax highlightning by the Scintilla editor control. - Support for script history. - Common files mat be organized in a sepcial Bookmarks menu. - Draging of binary data files into the editor. - Saving of dinary content from results. - Multiple resultsets support, and results may also be saved and not be overwritten. MyQuery 3.0 introduces a bunch of new features, among them: - Multiple editor tabs, allowing editing of several scripts at the time. - Dual connections to the database, to have a separate controlling thread. - Several status monitors, showing GLOBAL and SESSION status, PROCESSLIST and statements. - STATUS list values can displayed / not displayed on a value by value basis, or filtered. - PROCESSLIST data may be sorted. - Status monitors are non-modal. - Dictionary view for tables and routines, again non-modal. - Pasting of CREATE statement from the dictionary views to the editor. MySQL 3.0 is currently Alpha, as some testing still remains, and as it is not yet feature complete. A few more dictionary views and possibly drag-and-drop support for these, as well as some Wizards is among what is planned. MyQuery is GPL and may be downloaded from Sourceforge here: http://sourceforge.net/projects/myquery/ Best regards /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson (and...@mysql.com) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MyQuery 2.3.2 released
MyQuery 2.3.2 is released, and this contains a rather small bugfix that could cause a crash in many situations if there were no registry settings defined. This was for some reasons particularily apparent when not running with administrator privileges. If you tested MyQuery 2.3.1 and had not had MyQuery installed before, this might have happened to you. The effect was that the application would crash at startup. This release fixees that issue, which was a small fix to a small problem that had rather ill effects I'm afraid. Sorry for that to anyone using MyQuery, and I hope you give it another chance now, there are some cool features in this Query tool. Available for download at: http://sourceforge.net/projects/myquery/ Best regards /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyQuery 2.3 Beta available for download
OK. Let me have some input on what you think should be implemented, and I'll look at it. I am working on version 2.4 right now, and I'll add a few things there, but I am now at a stage where I want some input. What is planned is this (not bnecessarily in 2.4 though): * More advanced data view, i.e. the dialog that pops up when yo click on a selected field. - Hex view of data. - Opening data with the predefined program after selecting atype, i.e. select JPG and open BLOB in a image editing program. * Reconnect * Multiple saved connections. * Auto reconnect (this is already done for 2.4), slightly different than what the MySQL API itself provides. * Some admin functions, such as users, grants etc. This I have to think about. * More information functions. * Session variables handling, somehow. * Printing SQL text support, with syntax highlights. * Printing of data. * Export of data (To a spreadsheet for example). * More output command options. etc. /Karlsson Moon's Father wrote: It's very nice! But it's too simple. On Mon, Oct 6, 2008 at 12:26 AM, Anders Karlsson [EMAIL PROTECTED] wrote: Sorry for crossposting, but I think this is relevant both th general MySQL and specifically to Win32 users. MyQuery 2.3 has a lot of new features, a few bugfixes and some other niceties: * Output and sparse_output commands - The commands will output selected data to a file. The latter is a way to output ONLY what you select, no column headers, no summaries, ni fillers etc. only the data. This is useful when using the next new feature. * Source commands - This is a means of running another script from inside a script. * A better, more structured settings dialog using tabs. * Better handling of locked results, and a means of auto-locking results, so that where there are more than on result, these will show up in multiple tabs. * Lock / unlock of result tabs using right-click on the tabs. * Fixed a bug in the login dialog that caused the database list to work if there was an initial, unsuccessful attempt to connect. Dowload from https://sourceforge.net/projects/myquery/ Using the SPARSE_OUTPUT_FILE and SOURCE commands to write SQL that in turn generates SQL and then ruun this is a powerful feature. This particular feature is though rather complex to implement, more so than one might think, so input in this area is highly valued. /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyQuery 2.3 Beta available for download
Sorry for crossposting, but I think this is relevant both th general MySQL and specifically to Win32 users. MyQuery 2.3 has a lot of new features, a few bugfixes and some other niceties: * Output and sparse_output commands - The commands will output selected data to a file. The latter is a way to output ONLY what you select, no column headers, no summaries, ni fillers etc. only the data. This is useful when using the next new feature. * Source commands - This is a means of running another script from inside a script. * A better, more structured settings dialog using tabs. * Better handling of locked results, and a means of auto-locking results, so that where there are more than on result, these will show up in multiple tabs. * Lock / unlock of result tabs using right-click on the tabs. * Fixed a bug in the login dialog that caused the database list to work if there was an initial, unsuccessful attempt to connect. Dowload from https://sourceforge.net/projects/myquery/ Using the SPARSE_OUTPUT_FILE and SOURCE commands to write SQL that in turn generates SQL and then ruun this is a powerful feature. This particular feature is though rather complex to implement, more so than one might think, so input in this area is highly valued. /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyQuery 2.1 beta available
All! Sorry for crossposting again, but I hope that is OK. I have now released MyQuery 2.1 beta, the MySQL Query and Scripting tool. 2.1 introduces one major feature and a few minor ones, as well as a few fixes. - Major feature: Support for Multiple resultsets (from CALL commands usually). - Minor feature: Optional profress icon in the Windows tray. - Minor feature: .sql File associations. - Minor feature: Wait cursor during processing. - Minor feature: A few more installer features. - Fixed a few memory leaks. - Prepared a bit more for Unicode, although I'm still far from ready done this. Download MySQL 2.1, inslcuding a complete windows installer (including PDF documentation), PDF documentation and sourcecode from: https://sourceforge.net/projects/myquery/ Happy SQL'ing /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyQuery 2.0
Again, sorry for cross-posting, but might be of interest to both the general and win32 mailing lists. The MyQuery MySQL scripting tool has advanced into version 2.0 and has turned into a reasonably full-features query tool. Version 2.0 introduces syntax highlightning, search and replace and many other features. The cool BLOB handling is retained. The editor window is now based on Scintilla, with a custom MySQL Lexer. If you need a windows-based Query tool for MySQL, this might be what you are looking for. Version 2.0 is still in Alpha though, and one major feature is still missing: proper UTF-8 support. Supporting UTF-8 should be easier, now that Scintilla is in place, but more work is needed for this. Download MyQuery 2.0 from Sourceforge here: https://sourceforge.net/projects/myquery/ /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyQuery 1.1 alpha available
All! To being with, sorry for crossposting, but I felt that there might be an interest with both the General as well as the Win32 MySQL mailing lists. This email is just a heads-up that I have now made my MySQL Script tool available as Open Source. It has been going on and off for quite a while, but I am now ready to release it in Alpha form. What this tool is, in short, is a tool for running, checking and fixing MySQL scripts in a more interactive fashion than what is possible with the mysql commandprompt. The latter is still faster, no doubt, but MyQuery has some features for running the scripts that are useful I think. To being with, is uses the same format as the MySQL commandline tool, including using the USE and DELIMITER commands, and any of the usual SQL commands of course. The added features consists of, but aren't limited to: - Interactive execution: You see the statements being executed and the progress is also shown. - Interactive stop when there is an error, fix the problem, and then continue to run. - During development, run up to a specific point in the script. - Support for INSERT/UPDATE and SELECT of BLOBs using drag and drop. This is currently a Windows only tool, developed in C for the Win32 API, so anything else is some time off. As the editor, I currently use the Rich Edit control, which is hardly optimal for this. Scintilla support is being considered here. It is available on Sourceforge at https://sourceforge.net/projects/myquery/ and there is sourcecode or a complete Windows Installer or the documentation in PDF format. The latter is included with the Windows installer. Well, that's it for now, happy programming /Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
into how other DBMS handle it and whether the SQL syntax would be the same, should there be any method on the language layer to do it right. I only know that SQLite stores in UTF-8 but otherwise doesn't care about Unicode, i.e. sorting should be broken, comparison is correct. PostgreSQL didn't find its own columns again, so I cancelled the test. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode sorting and binary comparison, please!
Yves! OK. I agree I don't like this much myself, but we have to live with the multi-lingual aspect of UNICODE. Or rather, we have to agree to be either multi-lingual, and have the cons and pros of that (using UNICODE), or ignore UNICODE and have binary collations etc. And collation also determine equalness. real life example: I have a friend called called Widén, with an accented e. In Sweden, someone called Widen (with a non-accented e, and which is also a perfectly valid name) would sort and compare the same. I.e. in Sweden Widén = Widen. That's just how it works. But the same names, which are binary different but the same using swedish language and swedish collations, would be different when using a french collation. I happen ti live on a street with a ringed and and an umlauted character in the name. When in the US, these two guys have their unlauts removed are are sorted as the umlauts weren't there. Which is OK in US. Which is not OK in sweden. In essence, string comparisons needs to and must use collations when using UNICODE data. You state that Handel is different than Händel. I tend to agree with you, I am swedish by all means. But using a language collation where these characters don't exist just doesn't cut it. UNICODE collation determines not only sorting but also equality (i.e. é = e etc). Right or wrong, well I think that however you turn something will break. Frankly, I think a lot of blame here is on UNICODE to try to do too much, I'm not a big fan of this myself. But whichever way we do it, it will not be perfect. I think MySQL right now follows the UNICODE spec quite well, although there are still things missing. UNICODE is a reasonable compromise, and I see no better means of dealing with this. So even though I admit I'm no big fan of how UNICODE operates, I've still not figurted out a better way of delaing with it. And you are right of course, you may use the COLLATE keyword also, to enforce a certain collation, although if you want BINARY, I think using BINARY might be slightly more effective. What about a feature request to allow WHERE clauses to use a different collations than the one used for ORDER BY. So collation_connection controls the ORDER BY collation, and then I could say SET collation_connection_comparison = 'utf8_bin'. That would do what you want basically, and I think there might possibly be a need for this. /Karlsson Yves Goergen wrote: On 03.03.2008 10:27 CE(S)T, Anders Karlsson wrote: [a lot about why sorting unicode is complicated] If you want to accknowledge exact matching, and say any character, accented / unlauted etc, is different from any other character, specifiy a binary comparison: SELECT * FROM phonebook WHERE BINARY name = 'Handel'; Hm, not quite compatible. The solution I found is using this: SELECT * FROM table WHERE column = 'value' COLLATE ...; But still there binary collation has a different name on MySQL and SQLite. PostgreSQL doesn't support the COLLATE clause, although part of the SQL-92 standard. But you din't quite get my actual problem. You said that sorting Unicode things is complicated. I agree. I can live with a trade-off for sorting. But I cannot accept incorrect selection of records. When I want something that I can specify exactly, I only want to get that back, nothing else. The same counts for uniqueness constrains. I've asked a freind who could test the matter with PostgreSQL. He said, it works exactly as expected. Sorting is unicode-like, selection is precise. Why can't MySQL do that, too? Is it so hard to distinguish sorting and selecting? -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to locate SetSRID(),Makebox2D(),Distance_Sphere()
Rakesh!
The distance_sphere and makebox2d functions are specific to postgis.
MySQL GIS Implementation is based on the Open GIS Simple SQL
Specification (read more on www.opengeospatial.com). The SetSRID is also
not in the specification as far as I know (it should be, but I can't
find it), but many GIS implementations does seem to implement it. Not so
MySQL though, as MySQL currently only supports on SRID, the flat or
euclidean geometry.
Still, there are a few things missing in the current MySQL GIS
implementation compared to the Simple SQL spec. Many of these (not
including SRID support though) is available in special version that you
can read about here: http://forge.mysql.com/wiki/GIS_Functions where you
can also find download links.
Best regards
Anders Karlsson
[EMAIL PROTECTED] wrote:
Hi All
I looking for the stated functions. Earlier I was using postGIS in that
they are present i am wondering if I can get similar kind of method in
MySql.I am Using Mysql 5.0.51a version.
1. I am trying to retrieve the distance between two geometries in the
table using the following syntax:
SELECT X(s.geom),Y(s.geom),todofuken||shigun||kuchoson AS address,
(distance_sphere(s.geom,GeomFromText('POINT(135 35)',4326))) AS
distance
FROM todofuken_tbl t,shikuchoson_tbl s.
ERROR 1305 (42000): FUNCTION .DISTANCE _SPHERE does not exist
2. I am trying to excute this query in mysql :
select MakeBox2D(GeomFromText('POINT(135.0 34.0)',4326),
GeomFromText('POINT(135.5 34.5)',4326));
ERROR 1305 (42000): FUNCTION blog_gisdb.MakeBox2D does not exist
3. I am trying to excute this query in mysql :
SELECT uri,X(geom),Y(geom),ROUND(dist,2) AS distance FROM (SELECT
uri,geom,distance(geom,GeomFromText('POINT(135.25 34.25)',4326)) AS dist
FROM geom_tbl g,uri_tbl u WHERE g.id=u.id AND geom
SetSRID(MakeBox2D(GeomFromText('POINT(135.0 34.0)',4326),
GeomFromText('POINT(135.5 34.5)',4326)),4326)) AS d ORDER BY dist;
ERROR 1305 (42000): FUNCTION blog_gisdb.SetSRID does not exist
How I find above functions in MYSQL and how i use mysql GIS Extension.
Thanking You in inticipation
Rakesh
Please do not print this email unless it is absolutely necessary. Spread
environmental awareness.
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
___/ www.mysql.com Cellphone: +46 708 608121
Skype: drdatabase
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is MYSQL's equivalent to Oracle's DBMS_OUTPUT
Have a look at my, with an update way overdue but allthesame, myProcDbg project at sourceforge. I think this might do what you are looking for. /Karlsson sol beach wrote: Oracle provides a stored procedure called DBMS_OUTPUT which primarily is used to write/print/display text string to StandardOut (a.k.a. the terminal). In V5 MYSQL is there a functional equivalent? If so, what is it called. I am willing to RTFM if somebody provides me a clue as to which manual contains the answer to my question. I have Guy Harrison's MYSQL Stored Procedures but could not find what I'm looking for in it. Since I am not sure if what I want exists or what it may be called, I just may be looking in the wrong places for the answer. TIA! -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG in UNION implementation?! Confimation or Explaination please
UNION will only return distinct rows. This is according to spec and to the SQL Standard. To avoid this, use UNION ALL instead of UNION. Try that with your queries and you'll see that this will do the trick. This is, as I said, in accordance with the standard and the way all SQL based databases work. Quoting SQL 2003 section 4.10.6.2: MULTISET UNION is an operator that computes the union of two multisets. There are two variants, specified using ALL or DISTINCT, to either retain duplicates or remove duplicates. Where UNION DISTINCT is the default if neither DISTINCT nor ALL is specified then. Cheers /Karlsson list account wrote: Hi all, I believe to have found a bug in MySQL's union implementation. Can someone confirm this, please or convince me that this is not a buggy behaviour of mysql : UNION seems to behave like DISTINCT by default: mysql select 2 c1 - union - select 1 c1 - union - select 2 c1 - union - select 1 c1; ++ | c1 | ++ | 2 | | 1 | ++ 2 rows in set (0.00 sec) mysql select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1 c1,4; ++---+ | c1 | 1 | ++---+ | 2 | 1 | | 1 | 2 | | 2 | 3 | | 1 | 4 | ++---+ 4 rows in set (0.00 sec) mysql select 2 c1,1 union select 1 c1,2 union select 2 c1,3 union select 1,2; ++---+ | c1 | 1 | ++---+ | 2 | 1 | | 1 | 2 | | 2 | 3 | ++---+ 3 rows in set (0.00 sec) mysql select avg(c1),avg(distinct c1),sum(c1),count(c1),count(distinct c1),count(*) from - ( - select 2 c1 - union - select 1 c1 - union - select 1 c1 - union - select 1 - ) a - ; +---++---+-+---+--+ |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) | +---++---+-+---+--+ |1.5000 | 1.5000 | 3 | 2 | 2 |2 | +---++---+-+---+--+ 1 row in set (0.00 sec) but I would have expected: +---++---+-+---+--+ |avg(c1)|avg(distinct c1)|sum(c1)|count(c1)|count(distinct c1) | count(*) | +---++---+-+---+--+ |1.2500 | 1.5000 | 5 | 4 | 2 |4 | +---++---+-+---+--+ TIA, CVH -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Skype: drdatabase -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Convertion ORACLE query to MYSQL
These commands are Oracle specific or contains Oracle specific
extensions. Nothing wrong with that, but in some cases there just is no
corresponding command in MySQL, as the concepts are different.
ViSolve DB Team wrote:
Hi Experts,
We are in the process of converting Oracle administration commands into MySQL
for some purposes. We dont know how to convert the following Oracle commands to
MySQL. How to do that..?
1. ALTER USER spec TEMPORARY TABLESPACE temp_ts;
In MySQL there are no tablespaces in general, although certain storage
engines use them. But they have completely different properties from
what is the case with Oracle. Also, there is no such thing as a specific
temporary tablespace.
2. DROP USER jbossjms1 CASCADE;
DROP USER works fine in MySQL. In Oracle, there is a specific connection
between a User and the Schema, or rather, they are the same. In MySQL,
these are different, there is a schema (or in MySQL, a database) and
then there is granted access to that schema, that is it, there is no
specific ownership of a schema. Assuming you set up MySQL the same way
as Oracle, that each user (jbossjms1) gets his own schema (jbossjms1)
and you want to drop both of those, in MySQL you would:
DROP DATABASE jbossjms1;
DROP USER jbossjms1;
3. DROP TABLESPACE jbossjms1 INCLUDING CONTENTS;
As there is a DROP TABLESPACE command in MySQL 5.1, but that does not
with all certainty do what you want it to. The closest command is
probably DROP DATABASE, but that assumes that you have all the jbossjms1
objects in that database. I'd be careful here though, and read up on
these commands in both Oracle and MySQL before you do this, as this
might, and again might not, do what you want. These are admin commands,
which typically work differently in different RDBMS systems.
4. CREATE TABLESPACE jbossjms1DATAFILE '${JBOSSJMS1}' SIZE 100M REUSE
AUTOEXTEND ON MAXSIZE UNLIMITED;
This is again an administration commend. Assuming you are using the
InnoDB storage engine, this command corresponds to
innodb_data_file_path setting in the MySQL configuration file (my.cnf
/ my.ini etc).
5. CREATE USER jbossjms1 IDENTIFIED BY jbossjms1DEFAULT TABLESPACE jbossjms1;
This command works similarly same in MySQL, with the exception that
there is no concept of a DEFAULT TABLESPACE in MySQL.
Thanks in Advance,
ViSolve PlanCAT Team
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
___/ www.mysql.com Cellphone: +46 708 608121
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some trouble with a Join after upgrade from 3.23 to 5.0
This is due to a change in MySQL 5.0.12 that was done to align with SQL:2003. Here, we started to be more conservative regarding what could go into the ON clause. The whole thing is documented here: http://dev.mysql.com/doc/refman/5.0/en/join.html In your case, the JOIN would look something like this (Not tested, just straight from under the hairy stuf on the top of my head): FROM article_country ac, article a LEFT JOIN article_menu am ON a.id = am.article_id Or, to be more SQL'ish: FROM article_country ac JOIN article a LEFT JOIN article_menu am ON a.id = am.article_id Or, to be even more more SQL'ish (this one I tested): FROM article_country ac CROSS JOIN article a LEFT JOIN article_menu am ON a.id = am.article_id /Karlsson nocturnal wrote: Hi I moved a lot of databases from a 3.23 system to a new 5.0 system that was taking over because of hardware upgrades. I had no major problems until the last database. This query: SELECT a.id, a.nr, a.parent_id, a.designation, a.designation2, a.short_description, a.road_desc, ac.activestatus_id, ac.active_date, am.menu_id, am.active_status, a.grouparticle_type, ac.bestseller FROM article a, article_country ac LEFT JOIN article_menu am ON a.id = am.article_id AND am.active_status =1 WHERE a.parent_id = '' AND ac.country_code = 'SE' AND a.id = ac.article_id AND a.grouparticle_type 2 ORDER BY a.designation LIMIT 0 , 30; Gives me this error: Unknown column 'a.id' in 'on clause' I'm no MySQL expert but i assumed that a.id was an alias for article.id so i checked if the column existed manually and sure enough it does exist and it is full of data identical to the database on the old 3.23 server. So now i'd like to know what needs to be updated in the application sending this query because there is obviously something incompatible between 3.23 and 5.0. I read the documentation on this link: http://dev.mysql.com/doc/refman/5.0/en/join.html and found the section describing changes made to MySQL 5.0.12. The problems is that i couldn't find any errors in the query when i read about the new JOIN syntax described. I would like some help with this if anyone has the time to just point out what is wrong with the above query so that i can make the changes in the rest of the application. I'm sure i'll see the light if someone just pushes me in the right direction on this problem. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get into mysql command line?
The error shows that the passwords doesn't match. You just must have made some mistake when resetting the password. But this can be fixed: 1) You can start the server with the --skip-grant-tables option that disables password checking, then you log in as root, set the password, and the restart the server without the --skip-grant-tables. or 2) Use the --init-file option to run a file that resets the password. Bith methods are described here: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html It is a rare occurence, but the problem might also be this: http://dev.mysql.com/doc/refman/5.0/en/password-too-long.html Best regards Anders Karlsson Cornelia Menzel wrote: Am 16.10.2006 um 13:08 schrieb Dominik Klein: Unfortunately, that is not the reason, why I get this message. I have tried 'mysql', 'mysql -uroot', mysql -u root', 'mysql -uroot -p', 'mysql -uroot -pMY_PASSWORD', but anything fails. When I am using the password option, the error message is like this: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Any ideas? Did you restart the server after you set the password? Did you execute flush privileges? Yes, I did, but it did not work. I have no idea, what the problem is. And I cannot understand, why I cannot login after having reset the root password. - Cornelia Menzel Spanien / España / Spain e-Mail: [EMAIL PROTECTED] http://www.corneliamenzel.com -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get into mysql command line?
Come to think of it, might it be that you are using an old mysql client, possibly one that exists as part of an old installation of MySQL or in the case of Linux, one that was installed as part of the Linux distro installation? Some of these older versions of MySQL Client does not support the more secure password encryption methods used by newer MySQL versions, and you will get just the errors that you are getting. So if you are using Linux, do a which mysql and see what mysql client you are using. Also do a mysql --version which will show the command line client version. You have to watch for this if the client has version 4.0 or earlier, and you have server with version 4.1 or higher, then this is surely the problem you are experiencing. Read more here: http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html Best regards Anders Karlsson Cornelia Menzel wrote: Am 16.10.2006 um 13:23 schrieb Anders Karlsson: The error shows that the passwords doesn't match. You just must have made some mistake when resetting the password. But this can be fixed: 1) You can start the server with the --skip-grant-tables option that disables password checking, then you log in as root, set the password, and the restart the server without the --skip-grant-tables. or 2) Use the --init-file option to run a file that resets the password. Bith methods are described here: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html It is a rare occurence, but the problem might also be this: http://dev.mysql.com/doc/refman/5.0/en/password-too-long.html Best regards Anders Karlsson Thank you Anders, I have tried this already. But I will do it again, perhaps I have more luck now. Thank you. Best regards, Cornelia - Cornelia Menzel Spanien / España / Spain e-Mail: [EMAIL PROTECTED] http://www.corneliamenzel.com -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure Security Question
What you are asking for is exactly what DEFINER security does. The applicxation owner grants appuser the right to execute the procedure, but not to SELECT from any tables. The procedure is then run with the security attributes of the definer of the procedure, the application owner, even though it is the application user that runs it. This is no different than other DBMS systems, the difference being that you have the option of defining a procedure with INVOKER rights, in which case the procedure will run with the security attributes of the application user, and you need to grant that user access to any tables that are accessed within the procedure. So in essence, MySQL doesn't limit you compared to most other DBMS's, it gives you more options. Cheers /Karlsson [EMAIL PROTECTED] wrote: When creating a stored procedure, you can set the sql security characteristic to either definer or invoker. As an example, I have a stored procedure that does a select from a table, and an application user (appuser) that calls the stored procedure. If the sql security is set to invoker, then I have to give appuser both select and execute privileges. If the sql security is set to definer, then the definer needs select privileges and appuser only needs execute. What I'd like to be able to do is to give appuser the execute privilege and not have to give any privileges on the underlying tables to the definer. Is this possible? We do almost 100% of our work through stored procedures. It would be a lot easier to manage just the execute privilege. Are there reasons why this is not a good idea? This is how we manage security with our other DBMS and it's worked quite well, but it doesn't have the definer/invoker characteristic for stored procs either. Any suggestions about how to manage users/privileges would be appreciated. Donna -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql HA
Sure. MySQL runs just fine in an Active /Passive configuration for HA. There is a white paper that you can request from the MySQL homepage among the other white papers here: http://www.mysql.com/why-mysql/white-papers/ For such a configuration, a popular choice is to use Linux HA. You can read more about that on the Linux HA homepage at: http://www.linux-ha.com/ There are a few different way to adopt MySQL in this type of environment. You can use DRBD (which is part of the Linux HA project) or a SAN or you can use MySQL Replication. Best regards Anders Karlsson JM wrote: Hi, Is it possible to implement an HA configuration in mysql without using Mysql Clustering? A howto is very much appreciated.. thanks, Mailing-List -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing URL 2083 characters
That is up to 64k bytes, not chars, which might not be the same thing if UNICODE is used using utf-8/utf-16 or ucs-2 for example. Although this is usually not an issue in the specific case of an URL. /Karlsson Johan Höök wrote: Hi Peter, I'd thought I'd just mention that the varchar length depends on your MySQL version and character set. 5.0.3 and later handles upto 64k chars. See: http://dev.mysql.com/doc/refman/5.0/en/char.html /Johan Peter Van Dijck skrev: Hi, URL's have a practical limit of 2083 characters it seems. To store these in a space efficient way (I have 1,000,000s of url rows), what's the best approach? varchar has a 255 maximum, right? Should I just use TEXT? I'm not searching *in* the urls, I am selecting like this: where url = 'xxx'. Thanks, Peter -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding the slow query
Or try my own tool mymonitor. Available on sourceforge at https://sourceforge.net/projects/mymonitor It's still in beta, but works OK. Still work in progress, and features will be added, but in your situation, I think it could be useful. Best regards Anders Karlsson Duncan Hill wrote: On Tuesday 20 June 2006 08:49, Peter Van Dijck wrote: My server has regular high loads when a lot of queries that hit the same tables slow down. The question is, which query is slowing it down? The others are probably just slow because the whole thing is slow. http://www.google.co.uk/search?q=mysql+slow+queryie=UTF-8oe=UTF-8 Combined with show processlist / mysqltop, you can see if you have locking contention. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyMonitor - A novel SQL monitor tool for MySQL release 1
The first version of MyMonitor is now available in source form on sourceforge and can be downloaded from: http://sourceforge.net/projects/mymonitor The reason for this tool is simple, and it's also a bit different from other similar tools (at least the ones I've looked at). The tool gets the output from SHOW PROCESSLIST repeatedly and does some magic to this to count the number of executions and execution time. The difference is in how it handles the SQL statement text. Before somparing the SQL text of a statment to the statements in the previous run of SHOW PROCESSLIST or to the SQL in an internal list of frequently accessed SQL statements, it removes any references to literal values. So the these statements: SELECT * FROM customer WHERE cust_id = 123; and SELECT * FROM customer WHERE cust_id = 123; Which in SHOW PROCESSLIST is shown as 2 distinct statements are handled as two executions of the same statement: SELECT * FROM customer WHERE cust_id = ?, which is usually how it works anyway. The output through ncurses, a bit like top. The collected statements may also be written to file, and there are a bunch of other settings, and there is also documentation in shape of a User Guide in PDF format. The current version is considered a beta, so comments on porting are more than welcome. I have so far only run it on a couple of Linux'es, but there is more to come. ncurses library is required, as well as the MySQL Client library of course. Enjoy, and comments are welcome, I hope this tool will turn out useful -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyMonitor - A novel SQL monitor tool for MySQL release 1
John! No, that is not within the scope of this tool. One could imaging that is was possible though, but without some effort. This information is not available in the show processlist, so some other means needs to be figured out. For a seelct one could always reissue the statement (not all statements, but the ones one is interested in) and count the # of rows returned. This is a rather terrible kludge though. The best you get right now is the original SQL text of the last instance of a particular query. In the example below, what is displayed as a query is SELECT * FROM customer WHERE cust_id = ?, but there is an option to show the last instance of the real query (SELECT * FROM customer WHERE cust_id = 456). This is rather far away from what you are looking for I guess. If I get some idea of where to find this information, I'd be happy to integrate this featuer into an upcoming version of the tool though. Best regards Anders Karlsson John May wrote: Is there any way with this tool, or some other tool, to monitor the amount of data (in KBytes or the likes) returned by a particular query? - John The first version of MyMonitor is now available in source form on sourceforge and can be downloaded from: http://sourceforge.net/projects/mymonitor The reason for this tool is simple, and it's also a bit different from other similar tools (at least the ones I've looked at). The tool gets the output from SHOW PROCESSLIST repeatedly and does some magic to this to count the number of executions and execution time. The difference is in how it handles the SQL statement text. Before somparing the SQL text of a statment to the statements in the previous run of SHOW PROCESSLIST or to the SQL in an internal list of frequently accessed SQL statements, it removes any references to literal values. So the these statements: SELECT * FROM customer WHERE cust_id = 123; and SELECT * FROM customer WHERE cust_id = 123; Which in SHOW PROCESSLIST is shown as 2 distinct statements are handled as two executions of the same statement: SELECT * FROM customer WHERE cust_id = ?, which is usually how it works anyway. The output through ncurses, a bit like top. The collected statements may also be written to file, and there are a bunch of other settings, and there is also documentation in shape of a User Guide in PDF format. The current version is considered a beta, so comments on porting are more than welcome. I have so far only run it on a couple of Linux'es, but there is more to come. ncurses library is required, as well as the MySQL Client library of course. Enjoy, and comments are welcome, I hope this tool will turn out useful -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyMonitor - A novel SQL monitor tool for MySQL release 1
Also, I need to learn how to spell. The docs arn't as bad as the language in the reply below. /Karlssons spellchecker Anders Karlsson wrote: John! No, that is not within the scope of this tool. One could imaging that is was possible though, but without some No, that is not within the scope of this tool. One could imagine that is was possible though, but not without some effort. This information is not available in the show processlist, so some other means needs to be figured out. For a seelct one could always reissue the statement (not all statements, but the ones one is interested in) and count the # of rows returned. This is a rather terrible kludge though. The best you get right now is the original SQL text of the last instance of a particular query. In the example below, what is displayed as a query is SELECT * FROM customer WHERE cust_id = ?, but there is an option to show the last instance of the real query (SELECT * FROM customer WHERE cust_id = 456). This is rather far away from what you are looking for I guess. If I get some idea of where to find this information, I'd be happy to integrate this featuer into an upcoming version of the tool though. Best regards Anders Karlsson John May wrote: Is there any way with this tool, or some other tool, to monitor the amount of data (in KBytes or the likes) returned by a particular query? - John The first version of MyMonitor is now available in source form on sourceforge and can be downloaded from: http://sourceforge.net/projects/mymonitor The reason for this tool is simple, and it's also a bit different from other similar tools (at least the ones I've looked at). The tool gets the output from SHOW PROCESSLIST repeatedly and does some magic to this to count the number of executions and execution time. The difference is in how it handles the SQL statement text. Before somparing the SQL text of a statment to the statements in the previous run of SHOW PROCESSLIST or to the SQL in an internal list of frequently accessed SQL statements, it removes any references to literal values. So the these statements: SELECT * FROM customer WHERE cust_id = 123; and SELECT * FROM customer WHERE cust_id = 123; Which in SHOW PROCESSLIST is shown as 2 distinct statements are handled as two executions of the same statement: SELECT * FROM customer WHERE cust_id = ?, which is usually how it works anyway. The output through ncurses, a bit like top. The collected statements may also be written to file, and there are a bunch of other settings, and there is also documentation in shape of a User Guide in PDF format. The current version is considered a beta, so comments on porting are more than welcome. I have so far only run it on a couple of Linux'es, but there is more to come. ncurses library is required, as well as the MySQL Client library of course. Enjoy, and comments are welcome, I hope this tool will turn out useful -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyMonitor - A novel SQL monitor tool for MySQL release 1
There will probably be a full-blown Windows GUI version eventually. But for now, I guess you have to compile it under cygwin, which should work but hasn't been tried so far. There is probably a demand for windows, keep me posted now and then, and I'll let you know when it's available. Or just look at the project at sourceforge now and then. Best regards Anders Karlsson Gabriel Mahiques wrote: Anders I want this for Windows? how do I do it? Anders Karlsson escribió: The first version of MyMonitor is now available in source form on sourceforge and can be downloaded from: http://sourceforge.net/projects/mymonitor The reason for this tool is simple, and it's also a bit different from other similar tools (at least the ones I've looked at). The tool gets the output from SHOW PROCESSLIST repeatedly and does some magic to this to count the number of executions and execution time. The difference is in how it handles the SQL statement text. Before somparing the SQL text of a statment to the statements in the previous run of SHOW PROCESSLIST or to the SQL in an internal list of frequently accessed SQL statements, it removes any references to literal values. So the these statements: SELECT * FROM customer WHERE cust_id = 123; and SELECT * FROM customer WHERE cust_id = 123; Which in SHOW PROCESSLIST is shown as 2 distinct statements are handled as two executions of the same statement: SELECT * FROM customer WHERE cust_id = ?, which is usually how it works anyway. The output through ncurses, a bit like top. The collected statements may also be written to file, and there are a bunch of other settings, and there is also documentation in shape of a User Guide in PDF format. The current version is considered a beta, so comments on porting are more than welcome. I have so far only run it on a couple of Linux'es, but there is more to come. ncurses library is required, as well as the MySQL Client library of course. Enjoy, and comments are welcome, I hope this tool will turn out useful -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: go back machine
An example, which use a subquery (available from MySQL 4.1): select eqid, paramid, lastmodified, value from eq_deltalist param1 where lastmodified = (SELECT max(lastmodified) FROM eq_deltalist WHERE paramid = param1.paramid AND eqid = param1.eqid AND lastmodified = now()) You would probably want to join the outer of the two queries with eq and eq_params also Regards /Karlsson Niklas Karlsson wrote: I'd like to setup an application to store equipment configuration data in a MySql database. The basic setup is that each equipment has a certain number of parameters which may change over time. I'd like to track these changes over time and want to be able to create queries which determine the status a certain date. I guess the easiest approach is to have one column per parameter and simply store the value of all parameters whenever I read up the configuration data using the Equipment Id and Date as keys. However, I'd like to store only the delta information, I.e. data changed between different dates. To give an idea, I need to be able to track around 100 parameters for roughly 1 different equipments so performance is an issue. Assuming now I create the following tables; CREATE TABLE `eq` ( `eqid` int(11) NOT NULL auto_increment, `eqname` char(10) default NULL, PRIMARY KEY (`eqid`) ) CREATE TABLE `eq_params` ( `paramid` int(4) NOT NULL default '0', `paramname` char(10) default NULL, PRIMARY KEY (`paramid`) ) CREATE TABLE `eq_deltalist` ( `eqid` int(4) NOT NULL default '0', `paramid` int(11) NOT NULL default '0', `lastmodified` datetime NOT NULL default '-00-00 00:00:00', `value` double(15,3) default NULL, PRIMARY KEY (`eqid`,`paramid`,`lastmodified`) ) If I define my equipments in eq, the different parameters in eq_params and each change of given parameter in eq_deltalist, how do I query for the valid parameters a certain date (i.e. when the lastmodified date is closest to the date in question) ?? Thankful for any good ideas. BR // Niklas -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional statement: IF
I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Conditional statement: IF
That's what I though. In Transact SQL this is valid code to run on the server, and you may optionally choose to put it all into a single stored procedure. In most other databases, a stored procedure has a language that is not executable outside the procedure code itself. With MySQL there will be stored procedure support in version 5.0 of the Server, but in your case the 5.0 alpha will not help, as stored procedure calls probably has an issue or two with the .NET provider. Frankly, the way Sybase does this (which was later inherited by SQL Server) was the way you did things at that time. It's just procedural code, the only difference being that is runs on the server. Today, you would probably put this type of logic in a stored procedure or an appserver. None of these is an option for you, so I guess that you have to put it in your code. Really, it's not that much of an issue, and the performance gains from those days or doing things this way are way less now. In some cases, cleaver SQL constructs can be used for simple conditional processing, but it's not generally applicable. Good luck to you /Karlsson Luke Venediger wrote: Hi Anders, Thanks for that. Yes, I have come from an MSSQL environment, and I'm using .Net 1.1 with the MySQL Connector/Net. The idea behind using conditional statements was to assign a query to a business task. For example, I could write a query to handle adding items to a shopping cart. In the query, before I add the item, I need to check if the item is in stock. If it's not in stock, I need to return a result set that indicates there has been an error, i.e. There is no stock of the requested item. Putting this logic in my code means having to execute a number of statements with code checks in-between, where I would rather only execute one query. Cheers, Luke Venediger. On Fri, 22 Oct 2004 18:49:54 +0200, Anders Karlsson [EMAIL PROTECTED] wrote: I don't know why you want to do this, but looking at your sybtax, it seems like you come from a Sybase or SQL Server environment. What you are trying to achieve is the way it is done in Transact SQL, where the conditional statements and stuff like that which are typically used in stored procedures, may also be used outside stored procedures. As someone else suggested, a real programming environment might be more appropriate, with a MySQL connection. Like Perl or so. Depite this, the simple example you may well be done with reasonably normal SQL in MySQL like this: select IF(@val = 1,'It is 1', 'It is not one'); But I suspect you want something more advanced than this, as this is not the most useful program the world has seen. (But not the least useful either). /Karlsson Luke Venediger wrote: Hi, I'm trying to write a conditional statement in my query. Is the following possible in MySQL? IF(@SomeValue = 1) THEN SELECT The value is 1; ELSE SELECT The value is not 1; END IF I've tried different variations and nothing seems to be working (I'm getting syntax errors). I'm using MySQL 4.0.18. Any help would be greatly appreciated. Thanks, Luke Venediger. -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforce value on select
This is a pretty weird thing. If you have no rows returned, and want to taka an action on that, then the application should check for the case of no rows returned, not for a specific value. But if you insist and use MySQL 4.1 (as subqueries are assumed): SELECT IFNULL((SELECT nameColumn FROM theDatabase WHERE rowId = 5), 0); Which is not to say that I think this is a good idea :-) /Karlsson John Mistler wrote: Thanks for the reply. There is a slight difference in what I need from the IFNULL function. It will only return the specified value if the column is null on a row that actually exists. I am needing a function that will return the specified value if the row does NOT exist. Any other ideas? SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set (I want a value like '0' or something) Thanks again! -John on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote: --- John Mistler [EMAIL PROTECTED] Is there a way to force SOME value to be returned from a SELECT query when the result is empty set? For instance: SELECT nameColumn from theDatabase WHERE rowID = 5; (when no row has ID 5) result -- empty set I would like for it to return some value, such as '' or 0 . . . Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] select ifnull(column,'0') from table _ Do You Yahoo!? 150??MP3 http://music.yisou.com/ ??? http://image.yisou.com 1G??1000??? http://cn.rd.yahoo.com/mail_cn/tag/1g/*http://cn.mail.yahoo.com/event/mail_1g/ -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: This thing called MOD
MOD returns the remainder of the division, assuming we use integer arithmetic. I.e.: 234 / 10 = 23 Then 4 remains (234 - (10 * 23)) Or: 23 / 6: (23 - (6 * 3)) = 5 Best ragards Anders Karlsson Thomas Nyman wrote: Hi All I'm a bit perplexed..perhaps its a language thing,,but the MYSQL reference manual says that MOD ..Returns the remainder of N divided by M... and gives an example SELECT MOD(234,10) -- 4 This I do not understand. remainder of N divided by M - isn't that simply division? I mean 234 divided by 10 does not equal 4 On my own machine..if I do SELECT MOD(23,6) I would expect 3,8333 as the result and not 5. SInce MOD is returning something other than I expect there must be something I am missingin other words...what is MOD returning?? Thomas -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems compiling NDB-Cluster mysql-4.1.2
You have to have zlib installed. This seems to be a bug, which I have
just reported. The fix is
simple: install zlib.
You can check with a simple C-program like this:
#include zlib.h
#include stdio.h
int main(int argc, char *argv[])
{
printf(Hello, World\n);
return 0;
}
If this refuses to compile (missing includefile) you are
missing zlib includes at least. You might want to link with -lz to check
that the library is there too, or even call one one the functions in
zlib (compress
for example).
Install zlib and make sure this little program compiles, if it does, you
should be OK.
/Karlsson
[EMAIL PROTECTED] wrote:
Hello all,
i want to compile the development tree source from
bk://mysql.bkbits.net/mysql-4.1 on SuSE 8.2 Linux. The compiling of the
mysql-4.1 without the ndb-cluster works fine.
When set the configuration-option --with-ndbcluster, the compilation
fails with the error:
ha_ndbcluster.o(.text+0x3b5c): In function `packfrm(void const*,
unsigned, void const**, unsigned*)':
: undefined reference to `my_compress'
ha_ndbcluster.o(.text+0x3c34): In function `unpackfrm(void const**,
unsigned*, void const*)':
: undefined reference to `my_uncompress'
collect2: ld returned 1 exit status
make[4]: *** [mysqld] Error 1
make[4]: Leaving directory `/home/Steffen/mysql-4.1/sql'
I've tried to set the CXX and/or CC environment-variable to gcc, but the
compilation fails with the same error.
Installed Software Versions:
Kernel: 2.4.22-33
gcc/cpp : 3.3-23
bison: 1.875
autoconf: 2.53
automake: 1.5
libtool: 1.5
Thanks
Steffen
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
___/ www.mysql.com Cellphone: +46 708 608121
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: urban myth?
Also, I'd say that it depends on what you mean by row. If you mean the same physical data as was previously displayed, then they probably WILL come back in the same order, but there are no guarantees (a dataset is always unordered, unless something else is specified). But on the other hand, if by row, we mean the same unique data, well then it might change as someone else might delete a row, and then someone else again might insert the same unique data. Same data, but a different physical row. In this case, data will certainly come back in a different order, and if you only look at, say, the unique identifier to determine row position, then the row might well have changed it's position within the row, fact is, it is much more likely that it has! Finally, a quote from Chris Date, as read in Relatuional databases - Selected writings, rom the section entitled Relational Database: An overview: If the entire ORDER BY clause is omitted, the result appears in unpredictable order and A table is an unordered set of rows. And as the result of a SELECT is also considered a relation (or a table), this latter quote applies too. /Karlsson Boyd E. Hemphill wrote: To all who answered thank you. This answer below is the one that I can use to convince him what he proposes is not necessarily safe. Now I just need to decide how to convince him it was his idea :-) Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Monday, May 03, 2004 12:57 PM To: Bob Ramsey Cc: [EMAIL PROTECTED] Subject: Re: urban myth? Bob Ramsey wrote: Ah, but the ordering is not random. As your example has it, the results are in the order that the entries were inserted into the table. There is an explanation for the order of the returned data. snip Apparently not random, but not in the order inserted either. Consider: create temporary table foo (num int(10)); insert into foo values (1), (2), (3), (4), (5); select * from foo; delete from foo where num = 3; insert into foo values (6); insert into foo values (3); mysql select * from foo; +--+ | num | +--+ |1 | |2 | |6 | |4 | |5 | |3 | +--+ 6 rows in set (0.01 sec) (Same example as before with the delete...where num=6 removed.) Note the 6 is where the 3 was originally, because the slot where the first 3 was inserted/deleted was reused for the 6. This trivial example yields results which are ordered neither by num nor by the order inserted. The lesson is clear: The *only* way to be sure your rows are sorted in a particular way is to explicitly request it with an ORDER BY clause, as several others have pointed out. This is really a fundamental principle: It is the data in the row that matters, not how or where it is stored. Michael -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first LIMIT then ORDER
As I stated before, my guess that duplicates are removed is because the SELECT is handled like one part of a UNION (I'll have a look at the code later to check if this is the case). Really, a UNION should consist of two or more SELECTs, so this is not the expected behaviour. The way this REALLY should be interpreted would be as a subquery followed by an ORDER BY. But as 4.0 doesn't have subqueries, this is not an option. But in 4.1 it is. I just tested it in 4.1, and rightly so, duplicates are NOT removed from this: (SELECT ...) ORDER BY ...; There is another way to write this query, which is like this: SELECT av.c1 FROM (SELECT c1 FROM t1) av ORDER BY av.c1; In this case av is an alias for the subquery (this is sometimes called an anonymous view, which is why I give it the alias av). An then, if we add a LIMIT clause to this, we get: SELECT av.c1 FROM (SELECT c1 FROM t1 LIMIT 3) av ORDER BY av.c1; The first construct, without the leading SELECT, is also, as far as I can interpret SQL-92/99, a standard SQL construct. But I think there might be a debate on this. The latter two construct ARE clearly SQL-92 compatible though (with the obvious exception of the LIMIT clause of course). And by the way, in a UNION, there is no need to put parenteses around the unioned queries in the general case. So (SELECT .) UNION (SELECT) [ORDER BY ] Is the same as SELECT . UNION SELECT [ORDER BY ] I say in the general case, as there are cases when the parenteses are required, in particular when the individual SELECT is followed by a MySQL specific construct or keyword. If I remember things correctly for example, this (SELECT ORDER BY...) UNION (SELECT... ORDER BY...) [ORDER BY ] will require the parenteseses, but this construct is a MySQL extension to the standard (an ORDER BY is not part of a query specification which is this form of a subquery). And yes, I know that the above query is a bit meaningless :-) Anyway, to summarize my view on this. An alternative way to achieve the requested operation is (which is fully SQL-92/99 except for the LIMIT clause): SELECT av.c1 FROM (SELECT c1 FROM t1 LIMIT 3) av ORDER BY av.c1; But this is available in 4.1 only. In 4.0 you can write: (SELECT c1 FROM t1 LIMIT 3) ORDER BY c1; Although this later syntax does not seem to work properly in 4.1.1 right now. (the LIMIT clause in this case has no effect, I get all rows back. Also note that the syntax doesn't allow for an alias for the anonymous view in this case). And neither of these constructs has anything to to with a UNION or a UNION ALL, really, except the latter is interpreted as being part of something like that in 4.0 (or so it seems). And now I close the SQL-92 standard docs. It is saturday after all and a beautiful day outside! Anders Karlsson -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED]) / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer /_/ /_/\_, /___/\___\_\___/ Stockholm ___/ www.mysql.com Cellphone: +46 708 608121 Jigal van Hemert wrote: I find by experiment that (select * from FOO order by a desc limit 10) order by a; removes duplicates, but, if I drop the second order clause, (select * from FOO order by a desc limit 10); duplicates are retained. Why is the first a union, but not the second? Just curious. On http://dev.mysql.com/doc/mysql/en/UNION.html you can see a comment by Keith Ivey about this. Apparantly it's caused by the fact that (SELECT .) UNION (SELECT) [ORDER BY ] is the syntax for a UNION. If you leave the first table out, you're left with: (SELECT ) ORDER BY... The fact that there are parentheses and an ORDER BY outside these parentheses seems to make it a UNION. If you leave out the ORDER BY..., it's just a query with parentheses around it. The manual states that if you do not use the keyword ALL with the UNION, it's considered to be DISTINCT. So, leaving out the UNION keyword entirely automatically makes it using DISTINCT. Regards, Jigal. From: Keith C. Ivey [EMAIL PROTECTED] DuBois: This one-query union syntax doesn't allow you to use the ALL keyword after UNION (since the UNION keyword isn't even there). That means it will always eliminate duplicate rows (like DISTINCT). That hasn't come up when I've used it, since I've never been selecting result sets that could contain duplicate rows, but it's something to keep in mind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
