Re: MySQL 5.0.22 and show columns bug?
While you are not wrong, James, is the length member suppose to denote the maximum length of data contained in result's specified column. NOTE: The result's. I.e. why give such an arbitrary number of bytes/length when no ENUM's or SET's are even in the result. The point being, even if you create a table containing 10 INT columns, the result of show columns from should show the Type column length of 3 with a maximum data allocation for the 10 rows of 30 bytes, and not ~2MB, as is currently the case. And even, in a worse case, MySQL Dev decided to give the length back as the maximum potential length, who determined 196605 should be the magic number? An ENUM can have 64K values, each of which can be a text value/label of at least 64 characters, thus a magic number should be at least megs in size to play it safe. Thus, such an approach is simply put, stupid. Ideally, as was the case in previous versions of MySQL, the Type column's Length should be given in context of the result, i.e. if there is an ENUM in the column list and it has the longest type description, the Type column's length should reflect its contained data size. Kind Regards SciBit MySQL Team http://www.scibit.com -Original Message- From: James Harvard [EMAIL PROTECTED] To: SciBit MySQL Team [EMAIL PROTECTED] CC: mysql@lists.mysql.com mysql@lists.mysql.com Subject: [Spam-Junk]Re: MySQL 5.0.22 and show columns bug? Sent: Thu, 06 Jul 2006 13:50:33 GMT Received: Thu, 06 Jul 2006 13:50:29 GMT Read: Sat, 30 Dec 1899 00:00:00 GMT Although I know nothing about C I imagine this is because the 'type' column can contain all the possible values from an ENUM or SET field. James Harvard At 10:30 am + 6/7/06, SciBit MySQL Team wrote: Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes -- 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]
[Spam-Probable]MySQL 5.0.22 and show columns bug?
Since a couple of recent stable versions back (and more recently, MySQL 5.0.22), MySQL has been returning the column length (C API) of the 'Type' column of a show columns from.. statement as being 196605 (almost 192KB), when this column only really contains data in the region of 10 bytes, if even. This is not only a bug, but extremely wasteful, as most client apps will therefore provide to allocate megs of ram for the potential data in this column to display even the simpliest of tables' column listing. Not only that, but many MySQL client apps will predetermine the correct manner of data display depending on the size of the data, i.e. use a normal text display vs a blob editor to display the subsequent information. I'm refering here to the st_mysql_field C API structure and specifically the 'length' member as defined in mysql.h. However, other columns seems fine with correct lengths, it just seems the person responsible for checking the result of the show columns into this structure has not done a very good job, as the column type text can not possibly take even 1KB of space, doesnt matter which charset you use, nevermind 192KB per column row displayed! Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow sorting
-Original Message- From: Marcus Bointon [EMAIL PROTECTED] [SNIPPED] That takes 11 seconds to run. Without the order by it takes 0.13 sec. I have simple indexes on both first name and last name (they are sometimes searched separately). It strikes me that this is really very slow - it really doesn't have much to sort. I tied doing an explain, and though I could see that it was using the indexes, it was also saying use where, use temporary, use filesort. Why is it falling back to these methods? How can I make this faster? Think you will find, should you study the query carefully, that in fact poor MySQL needs to query and actually sort all 400,000 records (was it not for the account id), before it can give you your batch of 30. If MySQL does not have enough ram allocated, may this even entail disk swapping for a NxMb table. This is of course the case because MySQL can not possibly give you the top 30, without first having to use the pertinent index to sort all of them. Indexes on the name and surname may also be a deathshot rather than a blessing, as these indexes would be almost as big as the original table. So instead of simply loading and sorting through one file, MySQL now has to do it with two equally big files. Dare I suggest the following: 1. Remove your name and surname indexes. 2. Ensure you have an index on the account column. 3. Insert a new composite column into the table which is of fixed width (CHAR) and at most 4/5 characters wide. Now populate this column with the first 2/3 characters of the surname and first 2 of the name, index this column and rather sort by it. (You can obviously change the containing data's permutation as you like, ex. first 4 of the surname, etc. 4. Also ensure MySQL has enough ram allocated (see show variables) such that it can load the complete table index in RAM (if possible, even the table's data), so that no disk/virtual mem swapping takes place. I think you will find MySQL much snappier with this source data, as it will first filter the data quickly by account and ordering of the subset should be very quick using only a 4 char column index. Chances are also good that if the first 4 chars of a surname match, the surnames are most likely identical (granted, the odd one will be sorted below rather than above it's actual position), but in the end is it a balance between speed and the odd mis-ordered record. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: are autoincrement values not always increasing in innodb?
Frank, my experience MySQL returns the the rows in the order that you inserted them This is true, if, and only if you have never deleted a record from the table. Like most SQL servers, MySQL leaves deleted records' space in the physical table unoccupied, but still available. When you insert a record, it first checks if the new record can not be inserted into an already allocated space (previously occupied by a valid record). If so, it will insert it there, else it will be appended to the table. This will explain the order in which your records are listed. Record 5 either got inserted into an open space, OR it was inserted while the other client thread/transaction inserted the other 4 records. If you optimize your table, then only is it truly purged from space previously occupied by deleted records. To answer your question though, the previous situation has nothing to do with your autoinc values, which will always be incremented - guaranteed. This behaviour can be changed though if you actually specify a value for an AUTOINC column during the insert, and thereby not allow MySQL to do or follow it's normal course in incrementing the autoinc. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Frank Sonntag [EMAIL PROTECTED] To: mysql@lists.mysql.com mysql@lists.mysql.com CC: Subject: are autoincrement values not always increasing in innodb? Sent: Mon, 27 Dec 2004 00:45:37 GMT Received: Mon, 27 Dec 2004 00:49:46 GMT Read: Tue, 28 Dec 2004 10:38:11 GMT Hi, does InnoDB guarantee that the values of an autoincrement column do always increase? What happened to me is that a select * from my_table returns something like id | ... 10 11 5 12 13 where id is defined as int(10) unsigned NOT NULL auto_increment and is the primary key of the table. The inserts corresponding to ids (10, 11, 12, 13) are done inside one transaction, the insert that generates id = 5, in another (concurrent) one. Cheers Frank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: error
Hi Jim, Your advice is indeed correct for the access denied problem. For your own problem, you might consider taking a look at max_allowed_packet variable of MySQL, as this error is common when you are sending a large blob update and the variable is too small for the update SQL, i.e. max_allowed is 1Mb and you are sending a 2Mb SQL statement. Also the client write and read timeout also causes this error, i.e. when you are doing a query which takes longer than say 60 seconds and your read timeout is set for a default 30 seconds. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Jim Zipper [EMAIL PROTECTED] To: Emmanuel d [EMAIL PROTECTED] CC: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: error Sent: Thu, 23 Dec 2004 03:58:55 GMT Received: Thu, 23 Dec 2004 04:01:35 GMT Read: Thu, 23 Dec 2004 09:05:20 GMT I am no expert by any means but over the last week I have been trying to solve why I can't connect from W XP as well. I keep getting the error message 2013 lost connection during SQL query. But what I have learned I think is that the error message you have received indicates that you have not set up the proper MySQL user access privileges. As I understand it you need to define access privileges for the client host, user and password. There are wildcard settings and defaults when these fields are left blank. I learned allot from these sections of the MySQL manual http://dev.mysql.com/doc/mysql/en/Privilege_system.html http://dev.mysql.com/doc/mysql/en/User_Account_Management.html but unfortuantely I have still not solved my problem. I don't know if this helps or not but I thought I would try to help. If there is anything you can suggest to solve my problem please respond as well TTFN - Original Message - From: Emmanuel d'Ange [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 3:26 PM Subject: error Hi, I've install odbc 3.51driver on win XP. I've already configure the connector with the correct parameter but when I try to test the connection, I've got this message : [MySQL][ODBC 3.51 Driver]Access denied for user: '[EMAIL PROTECTED]' (using password: yes) I don't know what to do. thanks Best regard e.d'Ange -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc 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: Definition of password hashing algorithm in 4.1.7
Mike, The newest MySQL uses SHA1 in combo with random generated 20 byte session hash values. The procedure is irreversible and therefore why it is not possible to obtain the original password. MySQL is thus very secure and only vulnerable to a bruteforce attack. You can partially secure yourself against this by limiting users to specific hosts. The day will surely come when MySQL will built in a timeout after a failed login attempt (i.e. when the username and host is ok, but the password failed). This will render even the bruteforce attack useless, as the attacker will have to wait years to test even a billion passwords (depending on the timeout value of course). As a typical bruteforce attack (depending on the number of valid characters and password length) can easily run into 10+ billion password permutations, this attack will be in vain as it will take decades to test all the passwords. Currently though, has MySQL no such feature. This allows you to test passwords against it upward of 10,000+ per second (if it is localhost), i.e. you can therefore test a billion passwords in approx. 30 mins. All this is obviously just estimates, as it depends on factors such as the MySQL hardware, your hardware, where the MySQL is running relative to you and how fast a connection can be established, etc etc. Typically (using a remote MySQL server) even just the connection setup time takes 1 second, i.e. 1 password/sec, thus 1 billion passwords will take 31 years :) Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Mike Moran [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED];[EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: Definition of password hashing algorithm in 4.1.7 Sent: Wed, 15 Dec 2004 12:44:10 GMT Received: Wed, 15 Dec 2004 12:48:19 GMT Read: Wed, 15 Dec 2004 13:46:54 GMT X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on mail3.infinology.net X-Spam-Status: No, hits=0.0 required=7.0 tests=none autolearn=no version=2.63 I've been looking into what algorithm MySQL 4.1.7 uses for password hashing/encryption, with a view to ascertaining how secure it is. Does it conform to any combinations of published Specs e.g. MD5/SHA-1/etc? I had a look at com.mysql.jdbc.Util#newHash() and #newCrypt() in Connector/J 3.0, but the code is somewhat opaque. Is this algorithm native to MySQL or is it just an implementation of a published algorithm? Is it worth my time trying to track down the intriguing 'Monty' code mentioned in Util.java? Ta, -- Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: find all records with more than one occurrence
Hi, How can I check all duplicated rows out from a large table? The values are not keys so they may have more than one occurrence. Thanks for your help. Regards, CHAN Chan, what about using DISTINCT in the select? .. or am I missing something? Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: MyCon 2005.2.6 Released
SciBit is proud to announce the release of the newest version of MyCon, v2.6 This version includes many new and improved features as well as all reported bugfixes. Amongst others: 1. Built-in support for the new 4.1 authentication, i.e. without need for an external libmysql.dll 2. Improved CopyPaste and DragDrop functionality for copying/backing up and restoring databases, tables, queries, scripts and report MySQL objects. Now includes Outlook-style Move to/Copy to dialogs. 3. Simplied folder view for all the Mascon fans. 4. Full range of data editors for every MySQL column type, from Blob, Memo, Picture editors to date/time, string editors. Now includes a full date AND time editor for datetime/timestamp columns 5. Skin/Style support For more information see: http://forum.scibit.com/viewtopic.php?t=224 http://forum.scibit.com/viewtopic.php?t=215 http://forum.scibit.com/viewtopic.php?t=164 For free downloads and/or free versions, see: http://www.scibit.com/products/mycon Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Different password() function ?
Hi Ady, See your OLD_PASSWORD MySQL option in your MySQL Docs. Or the following articles: http://dev.mysql.com/doc/mysql/en/MySQL_4.1_Nutshell.html http://forum.scibit.com/viewtopic.php?t=195 Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Ady Wicaksono [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: Different password() function ? Sent: Fri, 03 Dec 2004 10:01:06 GMT Received: Fri, 03 Dec 2004 09:51:54 GMT Read: Fri, 03 Dec 2004 09:57:47 GMT I just upgrade my MySQL from 4.0.20 to 4.1.7, however i found new things here, password() function in 4.0.20 - password(xxx) result in 5336eb751494bdb1 in 4.1.7 - password(xxx) result in *3E5287812B7D1F947439AC45E739353 how to get backward compatibility for this function ? since i use password() to encrypt users password Thanks -- 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: Serious error in update Mysql 4.1.7
Hi Luciano, Not that this reply will solve your problem, but let it serve as a notice. It is NEVER a good idea to use a FLOAT/BLOB column in your where clause as MySQL can not uniquely identify the record. Especially not with floats because of the inherent floating point error made between machines after a specific number of decimals (which depends on the hardware on which the MySQL is running). To clarify: select MyFloat from MyTable; Machine A might result in: 0.123456789012345[987345765] Machine B, using exactly the same MySQL version with exactly the same table and data: 0.123456789012345[765365423] Because of precision floating point errors (in the sample, after the 15th decimal) the values in the square brackets will differ and effectively be random numbers. You can thus see the problem in asking MySQL to match floating point data using a WHERE clause. In fact you can do the same query twice on the same machine and MySQL won't be able to locate the record as the ultimate float value will differ twice in a row. Always remember computers are binary machines which loves integers. After filling the internal 8 bytes with a floating value, the rest of any floating value precision becomes a toss up. Another sample (MySQL 4.1.7): mysql select pi(); +--+ | PI() | +--+ | 3.141593 | +--+ 1 row in set (0.00 sec) mysql select pi()=3.141593; +---+ | pi()=3.141593 | +---+ | 0 | +---+ 1 row in set (0.00 sec) If the sample you gave was auto-generated by the MyODBC driver it most likely compiled the WHERE clause because you don't have an unique primary key in your table. Best advise is to always add a primary key AUTOINC column to all tables. This will not only result in all your queries always being able to find the exact record, but will also reduce the traffic your current queries cause. The addition of an AUTOINC column is mainly due to MySQL's lack of server side cursors. This will be corrected it seems in MySQL 5, after which everyone will always be able to find their records independent of the data contained in the table. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Luciano Pulvirenti [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: Serious error in update Mysql 4.1.7 Sent: Fri, 03 Dec 2004 08:18:05 GMT Received: Fri, 03 Dec 2004 08:22:55 GMT Read: Fri, 03 Dec 2004 09:24:15 GMT I am trying Mysql 4.1.7 before putting it in production in 4.0.16 substitution on Windows NT. I have found an anomaly for me serious. I use Visual Basic 6 with ADO last version and the driver MYODBC 3.51.10. The program produces the following query: UPDATE `paghe`.`anagpaghe` SET `giorni_congedo_mp`=1.25000e+001, `giorni_congedo_anno_prec_mp`=0.0e+000, `giorni_permessi_retrib_mp`=2.0e+000, `giorni_congedo`=1.15000e+001, `giorni_congedo_anno_prec`=0.0e+000, `giorni_permessi_retribuiti`=2.0e+000, `swnuovo`=0 WHERE `matricola`=43258 AND `giorni_congedo_mp`=1.25000e+001 AND `giorni_congedo_anno_prec_mp`=0.0e+000 AND `giorni_permessi_retrib_mp`=2.0e+000 AND `giorni_congedo`=1.15000e+001 AND `giorni_congedo_anno_prec`=0.0e+000 AND `giorni_permessi_retribuiti`=2.0e+000 AND `swnuovo`=1 Mysql doesn't succeed to update the record because no succeeds in finding the record corresponding to the syntax WHERE. I have made some tests have discover that the cause is AND `giorni_congedo`=1.15000e+001 In the version 4.0.16 work correctly. The fields giorni... have declared in the structure double(5,1). Thank you -- Internal Virus Database is out-of-date. Checked by AVG Anti-Virus. Version: 7.0.290 / Virus Database: 265.4.3 - Release Date: 26/11/2004 -- MySQL Windows Mailing List For list archives: http://lists.mysql.com/win32 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]
ANN: MyCon 2004.2 released
Hi All, SciBit is happy and proud to announce the 2004.2.0 release of MyCon. Upgrading your current version is free and you can download the new MyCon Pro version at: http://download.scibit.com/release/mycon/mcp.exe What's new summary: * One of the newest features, and by all accounts a first for any MySQL GUI is the ability to dragdrop or copypaste tables/queries/scripts/reports between databases and even between different MySQL servers. Yes, while everyone in the world will be using all kinds of dumping techniques taking hours to setup and minutes to run, moving complete tables' structures and/or data from one database or server to another (ex. development to production and back) will take you no more than a sweep of your mouse. * Then again, maybe you want a fully schedulable task manager to dump/backup your data and/or generate reports periodically to PDF, RTF, HTML, etc. So, another much improved feature is the full availability of all the MyRun options right in your GUI, where you can now set up it's schedule, if it is to dump to file, another server or even clipboard. * A lot of small enhancements were built in, like the setting of a data directory on a per connection basis for better management of individual and/or shared MySQL data objects. In general, after fulfilling all requests and features received from users, MyCon just stepped up a gear in the functionality, responsiveness and robustness departments. For a demo of the dragdrop copy or move of tables see: http://www.scibit.com/products/mycon/mycondd.htm For a full description and discussion of what is new, please see: http://forum.scibit.com/viewtopic.php?t=164 MyCon website: http://www.scibit.com/products/mycon Detailed Online Help is available at: http://help.scibit.com/mycon For any questions, suggestions or bug reports, please don't hesitate to contact us at: [EMAIL PROTECTED] For general discussions relating to MyCon, please use our forum at: http://forum.scibit.com If you use a RSS reader, you can also add our RSS news feed to get announcements: http://www.scibit.com/scibit.rss Best wishes, SciBit's MySQL Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: moving data
A) 0. optionally: start transaction; 1. create temporary table mytemptable select * from mytable order by mytableprimarykey limit 10; 2. insert into destinationtable select * from mytemptable; 3. delete * from mytable order by mytableprimarykey limit 10; 4. drop mytemptable; 5. optionally: commit; 6. Goto step 0. if you don't want to play it save, then you can skip step 1 and insert your data straight into the destination table. or B) Study the MySQL Manual for the mysqldump's commandline parameters, to dump all your data to a file first. Then recreate your table on the database/table you wish. or C) You can use many of the GUI tools around most of which can backup your data to scripts or other databases/tables. Ex. http://www.scibit.com/products/mycon 0. Double click your table, sort and limit your data as you wish 1. Click Backup 2. Use the resulting script against your destination table. 3. Hit Ctrl+A in your table's grid and then Del to delete the select records and then hit Refresh to start again (if you have selected only a 100,000 batch for example). 4. Goto step 1. By default your resulting script will contain records batched 100 per insert statement and thus for a 100,000 records you will only have a 1000 insert statements. This will of course execute much faster than 100,000 separate insert statements. It would be highly recommended to use an intermediate local dump script file (if your space problem is not a concern for your local computer), that way you have all your data backed up for just in case. Kind Regards SciBit MySQL Team http://www.scibit.com -Original Message- From: J S [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] CC: Subject: moving data Sent: Thu, 02 Sep 2004 12:38:30 GMT Received: Thu, 02 Sep 2004 12:40:48 GMT Read: Thu, 02 Sep 2004 13:23:07 GMT Hi, I want to copy data from one table to another. However I need to do this carefully because I haven't got much of space left. I was thinking of maybe selecting data from every 10 rows of the old table, inserting it into the new table, then deleting those rows from the old table. Could someone help me out with the SQL for this please? Or tell me if there's a better way of doing this? Thanks, JS. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- 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]
ANN: MyCon 2004.1.1 Released
SciBit is happy and proud to announce the 2004.1.1 release of MyCon. If you already have a registered copy, upgrading your current version is free and you can download the new MyCon Pro version (or for trial purposes): http://download.scibit.com/software/mycon/mcp.exe FreeMyCon is completely free for all to use: http://download.scibit.com/software/mycon/mcf.exe What's new summary: * More than a hundred user suggestions and improvements have been implemented * Pertaining to navigation, we had to reconcile such diverse comments as I LOVE MyCon with I want my simpler Mascon folders back. Also to accommodate users with a limited screen size (laptops etc) we have added favorite creation functionality which allows you to create favorite shortcuts to ANY folder or subfolder in your folder view. You can also organize, reorder and arrange these shortcuts to your heart's content. For example, you can now group tables, queries, reports, etc together even though they may all reside on different servers or in different databases. You can also use the favorite view without loss of any functionality when compared to the folders view. In short, we have enabled you to create virtual folder structures containing subsets of any nodes in your default folder view. * Lots of new functionality were built into the Connection Properties pane, including server and client versions, ping times, setup/customization of databases to show, compression and timeout settings have also been added. It now also contains a configuration tab where you can view all your MySQL variables (global session, including support for the SET command), MySQL status, - processes (including support for the KILL command), - table types, - privileges and - logs (BDB) * Table/Query Grid Views now include a text button which you can use to fetch MySQL data as it is returned by MySQL and edit it as if it is all text. Thus, when enabled, MyCon does not convert any values to integers, date/time, enums, sets, etc with the numerous editors available for editing these datatypes. * Grid View now also does server-side sorting by default when you sort one or multiple columns by clicking the column names. Client-side sorting can still be done on one or multiple columns utilizing the group by this column pane. For a full description and discussion of what is new, please see: http://forum.scibit.com/viewtopic.php?t=147 To display the new favorite's functionality, we have added a new online flash tutorial to the MyCon home: http://www.scibit.com/products/mycon Tutorial: http://www.scibit.com/products/mycon/MyCon.htm Detailed Online Help is available at: http://help.scibit.com/mycon For any questions, suggestions or bug reports, please don't hesitate to contact us at: [EMAIL PROTECTED] For general discussions relating to MyCon, please use our forum at: http://forum.scibit.com If you use a RSS reader, you can also add our RSS news feed to get announcements: http://www.scibit.com/scibit.rss Best wishes, SciBit's MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: [OT] PostgreSQL / MySQL Data Dictionary
mysqldump --no-data --all-databases SNIP Eamon Daly Yeap Eamon, as mentioned MyRun is not the only utility on earth with the functionality. The difference between mysqldump and MyRun is that while MyRun includes all the mysqldump functionality, MyRun can take ANY source script. Let's make an example: mysqldump is great for backuping up complete database(s) with or without data. This is ofcourse nice, except when you have 50M records in a table, because then you get a resulting script which is huge. So essentially they both do something like this to generate the insert record sql for data backup purposes: select * from accounts; -- as an example but because you can customize the source sql script for MyRun, you can go like: select * from accounts where AccountDateYEAR(CURDATE()); -- i.e. limit the inserts you going to get to that which is really important. Also because it takes a source script, you can essentially limit the tables in a specific database to those with the important stuff in which you want to backup: --- use this-db; select * from accounts; -- Yes, important select * from orders; -- Yes, important -- select * from sessions; -- No skip this table completely select * from logs limit 0; -- Data not important, only capture schema .. -- Maybe do a little maintenance while we are busy? update accountpasswords set AccPassword=encrypt(AccPassword) where AccOpenDateCURDATE(); select * from accountpasswords; .. use that-db; select * from ...etc etc --- The logs table is a good example of such tables, it contains temporary kind of data and potentially a huge amount,ex. millions of recs. This will unnecessarily bloat the destination script file, so we limit it. Honestly, your backups is only limited by your imagination. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [OT] PostgreSQL / MySQL Data Dictionary
Great, MyCon produces SQL statements ready to recreate just your schema and/or all data as well, now did I miss something, or does MyCon actually write the SQL one needs to create and populate a set of system tables for the schema? PB . Nope Peter, you didn't miss a beat ;) Just to be very clear, MyCon is the front-end GUI which auto setup scripts for MyRun to use for backups or it can also optionally schedule MyRun to auto execute these source scripts. The end-user hardly ever sees MyRun (except when the OS task scheduler fires it up). All the end-user ever does, is to click on a database (or a specific table) and click Backup, the rest just happens. MyRun is the commandline utility which actually does the hard yards. And to just state it again so there is no confusion; MyRun's target script is fully capable of recreating the full schema as well as populate it with data (insert statements) from whatever was selected in the source script. NOTES: * MyRun can also do this from a remote MySQL server. * It can also execute the target script against another mysql server/database instead of to a script file * MySQL V3.23 and higher * The target script can optionally contain USE db;, DROP table if exists tb1;, CREATE table if not exists tb1 ... and your data using INSERT into tb1 (..columns..) values ((...record1...),(...record2...),..); in 100xrecord batches (so a 1,000 records will be contained in only 10 insert statements). These batch inserts are used because from the MySQL Manual: This is much faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to non-empty table, you may tune up the bulk_insert_buffer_size variable to make it even faster. EXAMPLE: Every morning we have a scheduled MyRun which backups up our company's MySQL webdata from our remote webhost/ISP's MySQL server to our local network using a ADSL connection. It contains tens of tables with thousands of records each and from start to finish takes approx. 10 seconds (our webhost and we are on different continents). And to set all this up initially took a massive.. one click! If our ISP drops our database by mistake, it will take us all of two seconds to recreate a complete snapshot of the database using the latest target script. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [OT] PostgreSQL / MySQL Data Dictionary
-w, --where=nameDump only selected records; QUOTES mandatory! :) The more options the merrier for MySQL and the end-users :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: [OT] PostgreSQL / MySQL Data Dictionary
In MySQL, by parsing the output of SHOW CREATE TABLE. It would be a boon if someone were to write a utility, in an OS-independent language, which does that parsing for all tables in a MySQL database and returns SQL output that's suitable for creating a set of system tables. Obviously bits of this task have already been accomplished by the folks who wrote the MySQL module in PHP, for example, and for all we know, much of the code for doing it may already exist in phpMyAdmin. PB Well it is not OS-independent, nor free, but the part that does the job you wish for is actually free and can easily become OS independent. MyCon (http://www.scibit.com/products/mycon) comes with a commandline utility called MyRun which it uses in the background to backup databases or single tables or groups of tables or general scripts' data or to schedule these tasks with. It has full helpfile on the command-line parameters and options, some of the output script options can optionally include drop if exists statements, create table and the actual data, all neatly parsed into SQL statements ready to recreate just your schema and/or all data as well. It can also dump to another server live instead of to an output script file. It has been optimized to dump an average size table's data at roughly 1000recs/sec on a 1xCPU3GHz with MySQL running localhost using only enough client-side memory for a single record at any given time. In short is takes any source script (which you can setup yourself or use MyCon to auto create it for you),ex: --- select * from accounts; SNIP...etc select * from visits left join countries using (CountryID); select * from servicerequests where ServDateYEAR(CURDATE()); SNIP...etc --- And then produces something like this: -- MyRun -- Source Server: SNIPPED -- Source Script: Tables.Scheduled.Run.All.sql -- Target Script: Tables.Scheduled.Backup.All.sql -- Target:Tables.Scheduled.Backup.All.sql -- Date: 2004-08-11 09:00:02 500 -- SCRIPT SQL: select * from accounts; -- DROP: accounts drop table if exists accounts; -- CREATE: accounts create table if not exists `accounts` ( `AccountID` int(10) NOT NULL auto_increment, `AccountEmail` varchar(50) default NULL, SNIP..etc PRIMARY KEY (`AccountID`), KEY `AccountStatusID` (`AccountStatusID`), KEY `AccountTypeID` (`AccountTypeID`) ) TYPE=MyISAM; -- DATA: accounts BATCH #1 insert into accounts (AccountID,AccountEmail,SNIP..etc) values SNIP it then proceeds to add batches of 100 recs per INSERT statement -- DATA: accounts BATCH #2 SNIP..etc As said, the DROP, CREATE and DATA are all optional. MyRun is ofcourse not the only utility to do this, mysqldump has been around forever and just about every other MySQL GUI includes this type of functionality. That said, if there is really a demand for something like this, I am sure we can put MyRun into both a linux and win32 CGI which can then be prompted from a website. MyRun's source is OS independent, so this won't be an issue. The current version is win32 and although the scripting side of it is really small, it got a bit bloated because it also includes code to generate live PDF,XLS,RTF,etc reports from MySQL data, which means the report modules made it heavy. So a cgi would more than likely fall into the 200Kb footprint range, instead of 1Mb. Kind Regards SciBit MySQL Team http://www.scibit.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Mascon for Linux 0.03 released
Hi All, SciBit would like to announce the latest release of Mascon for Linux 0.03. For those interested please check: http://www.scibit.com/Products/Software/Mascon4Linux Kind Regards SciBit MySQL Team http://www.scibit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANN: Mascon for Linux 0.02 released
Hi All, SciBit is happy to announce the release of Mascon for Linux 0.02, a port of the popular Mascon for Windows to the Linux (I386) platform. For those interested, further information, download and installation instructions available from: http://www.scibit.com/products/software/mascon4linux What is Mascon? Mascon is a powerful and easy to use graphical front end for MySQL. The first version of Mascon (Win32) was released in March 2000 and have grown from strength to strength. Mascon's main features include visual table design, connections to multiple servers, data and blob editing, user and privilege management, dump functionality and much more. Kind Regards SciBit MySQL Team http://www.scibit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
