MySQL Workbench
Anyone have any info on MySQL Workbench? In it's latest state it isn't usable at all. It looks like it'll be really great. Thanks, Chris ParkerAardvark Tactical, Inc.IT Manager1002 W Tenth St. Azusa, CA 91702phone: 800.997.3773 x130 fax: 626.334.6860[EMAIL PROTECTED]
Re: How many colums should a index contain?
John.H wrote: but why when I do a query with 'explain' ,it shows that the 'possible_keys' is null or primary rather than the index I just create Please always CC the list - you will get much faster responses and others will be able to offer their input as well. Post the query, the explain and the indexes you have on the table(s) and someone might be able to help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Will a subquery use a index?
John.H wrote: I found that when I use a query as a subquery it will not use the right index at all?? Post the query, the explain and the relevant table details and someone might have a suggestion. Also - not every single query is going to be able to use an index, this may be one of those cases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 17:40:44 +0100, Martijn Tonies escreveu: > InterBase had two-phase commits ages ago, Firebird inherited it. > > If there's anything specific you want to know, ask I *am* asking — where is the specific piece of documentation? Because if you don’t read MySQL’s documentation attentively, it gives you the impression everything’s A-OK with XA. And it’s not. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 17:30:14 +0100, Martijn Tonies escreveu: > Falcon has a transactional storage engine, including Foreign > Keys (Jim wouldn't do a database without em) Obviouſly. > MGA Ma ze? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.1.12 Beta has been released
Dear MySQL users, We are proud to present to you the MySQL Server 5.1.12 Beta release, a new Beta version of the popular open source database. Bear in mind that this is a beta release, and as any other pre-production release, it should not be installed for production level systems or systems with critical data. For production level systems, pay attention to the product description of MySQL Enterprise at: http://mysql.com/products/enterprise/ The MySQL 5.1.12 Beta release is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing Be it that this is a Beta release, there are several incompatible changes that have happened since last release, and there's a tremendous amount of bug fixes--way too many to mention here (more than 500). We're providing a detailed list at http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html and for your convienience also an excerpt of the ones where functionality have been added or changed: * Incompatible change: Support for the BerkeleyDB (BDB) engine has been dropped from this release. Any existing tables that are in BDB format will not be readable from within MySQL from 5.1.12 or newer. You should convert your BDB tables to another storage engine before upgrading to 5.1.12. * Incompatible change: The namespace for scheduled events has changed, such that events are no longer unique to individual users. This also means that a user with the EVENT privilege on a given database can now view, alter, or drop any events defined on that database. If you used scheduled events in an earlier MySQL 5.1 release, you should rename any of them having the same name and defined on the same database but belonging to different users --- so that all events in a given database have unique names --- before upgrading to 5.1.12 (or newer). For additional information, see Section 20.5, "The Event Scheduler and MySQL Privileges." * Incompatible change: The permitted values for and behaviour of the event_scheduler system variable have changed. Permitted values are now ON, OFF, and DISABLED, with OFF being the default. It is not possible to change its value to or from DISABLED while the server is running. For details, see Section 20.1, "Event Scheduler Overview." * Incompatible change: The full-text parser plugin interface has changed in two ways: + The MYSQL_FTPARSER_PARAM structure has a new flags member. This is zero if there are no special flags, or MYSQL_FTFLAGS_NEED_COPY, which means that mysql_add_word() must save a copy of the word (that is, it cannot use a pointer to the word because the word is in a buffer that will be overwritten.) This flag might be set or reset by MySQL before calling the parser plugin, by the parser plugin itself, or by the mysql_parse() function. + The mysql_parse() and mysql_add_word() functions now take a MYSQL_FTPARSER_PARAM as their first argument, not a MYSQL_FTPARSER_PARAM::mysql_ftparam as before. These changes are not backward compatible, so the API version (MYSQL_FTPARSER_INTERFACE_VERSION) has changed. For additional information, see Section 26.2.5, "Writing Plugins." * Incompatible change: In the INFORMATION_SCHEMA.EVENTS table, the EVENT_DEFINITION column now contains the SQL executed by a scheduled event. The EVENT_BODY column now contains the language used for the statement or statements shown in EVENT_DEFINITION. In MySQL 5.1, the value shown in EVENT_BODY is always SQL. These changes were made to bring this table into line with the INFORMATION_SCHEMA.ROUTINES table, and that table's ROUTINE_BODY and ROUTINE_DEFINITION columns. (Bug#16992: http://bugs.mysql.com/16992) * Incompatible change: MySQL Cluster node and system restarts formerly required that all fragments use the same local checkpoint (LCP); beginning with this version, it is now possible for different fragments to use different LCPs during restarts. This means that data node filesystems must be rebuilt as part of any upgrade to this version by restarting all data nodes with the --initial option. (Bug#21271: http://bugs.mysql.com/21271, Bug#21478: http://bugs.mysql.com/21478) See Section 15.5.2, "Cluster Upgrade and Downgrade Compatibility," and related sections of the Manual before upgrading a MySQL Cluster to version 5.1.12 or later. * Incompatible change: A number of MySQL constructs are now prohibited in partitioning expressions, beginning with this release. These include: + A number of MySQL functions. You can
Re: MyISAM vs InnoDB
On 11/2/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: > Is there a better open source database out there for that amount of data? Several. MySQL's own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. Jochem
Re: MyISAM vs InnoDB
> >> Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into > >> Borland stuff, Ingres if you need XA distributed transactions. > > > > Firebird isn't Borland > > Granted. But it is (even more) attractive if you are already a Borland > shop. > > > >> I usually recommend PostgreSQL, or Ingres if two-phase commits are > >> needed. > > > > Firebird has two-phase commits. > > Great to know — do you have any pointers? InterBase had two-phase commits ages ago, Firebird inherited it. If there's anything specific you want to know, ask :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 15:32:06 +0100, Martijn Tonies escreveu: >> Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into >> Borland stuff, Ingres if you need XA distributed transactions. > > Firebird isn't Borland Granted. But it is (even more) attractive if you are already a Borland shop. >> I usually recommend PostgreSQL, or Ingres if two-phase commits are >> needed. > > Firebird has two-phase commits. Great to know — do you have any pointers? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
>> > > Is there a better open source database out there for that amount of >>data? >> > >> > Several. MySQLâ?Ts own MaxDB, PostgreSQL, Firebird if you are into >> > Borland stuff, Ingres if you need XA distributed transactions. >> >>Firebird isn't Borland :-) >> >> > I usually recommend PostgreSQL, or Ingres if two-phase commits are >> > needed. >> >>Firebird has two-phase commits. > >Martijin, > Can Firebird store 1TB in a single table? All of FB tables are >stored in a single .GDB file, so is it possible to even split the table >across several drives? You can split a database across multiple drives, but you cannot direct a specific table to be in this or that part of the database. As far as I know, this make it possible that internally, tables are split across drives, but you cannot tell Firebird to do it directly. As for 1TB - I must admit I don't know, there's probably a maximum number of rows, not data though. > There is also the Falcon table engine that is coming out for >MySQL, but MySQL AB claims it is *not* a replacement for InnoDb. So why >introduce it? Falcon will be part of MySQL, unline InnoDB, which is licenses [from Oracle]. I would not agree with the remark that Falcon is not a replacement, as far as I understood, Falcon has a transactional storage engine, including Foreign Keys (Jim wouldn't do a database without em), MGA and more... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 08:32 AM 11/2/2006, you wrote: > >> Always use a DBMS, and MySQL is no (proper) DBMS without a > >> transactional backend. There are InnoDB, which is not completely free (needs > >> a proprietary backup tool); BDB, which is deprecated until further notices; > >> and SolidDB, which is still β. > > > > Ok, so your solution is to use something else? > > Well, this is a MySQL list you can use MySQL with InnoDB, if you are > willing to either have a proprietary backup solution or to use a β backend. > > > > Is there a better open source database out there for that amount of data? > > Several. MySQLâs own MaxDB, PostgreSQL, Firebird if you are into > Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) > I usually recommend PostgreSQL, or Ingres if two-phase commits are > needed. Firebird has two-phase commits. Martijin, Can Firebird store 1TB in a single table? All of FB tables are stored in a single .GDB file, so is it possible to even split the table across several drives? There is also the Falcon table engine that is coming out for MySQL, but MySQL AB claims it is *not* a replacement for InnoDb. So why introduce it? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
> >> Always use a DBMS, and MySQL is no (proper) DBMS without a > >> transactional backend. There are InnoDB, which is not completely free (needs > >> a proprietary backup tool); BDB, which is deprecated until further notices; > >> and SolidDB, which is still β. > > > > Ok, so your solution is to use something else? > > Well, this is a MySQL list… you can use MySQL with InnoDB, if you are > willing to either have a proprietary backup solution or to use a β backend. > > > > Is there a better open source database out there for that amount of data? > > Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into > Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) > I usually recommend PostgreSQL, or Ingres if two-phase commits are > needed. Firebird has two-phase commits. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create database
Mário Gamito wrote: Hi, I've installed MySQL 5.0.27, but can't create databases. I get "ERROR 1006 (HY000): Can't create database 'contineo' (errno: 28)" In the log file i have this: "InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data!" Any ideas on how to solve this issue ? Any help would be appreciated. Best Regards, MG perror 28 Error code 28: No space left on device You need more disk space. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: > At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: >> >> Always use a DBMS, and MySQL is no (proper) DBMS without a >> transactional backend. There are InnoDB, which is not completely free (needs >> a proprietary backup tool); BDB, which is deprecated until further notices; >> and SolidDB, which is still β. > > Ok, so your solution is to use something else? Well, this is a MySQL list… you can use MySQL with InnoDB, if you are willing to either have a proprietary backup solution or to use a β backend. > Is there a better open source database out there for that amount of data? Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create database
Make sure that the UNIX or Linux Admins have added you to the User Group that will be writing data and making folders in the data directory of MySQL - Original Message - From: Mário Gamito <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Thursday, November 2, 2006 8:06:13 AM GMT-0500 US/Eastern Subject: Can't create database Hi, I've installed MySQL 5.0.27, but can't create databases. I get "ERROR 1006 (HY000): Can't create database 'contineo' (errno: 28)" In the log file i have this: "InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data!" Any ideas on how to solve this issue ? Any help would be appreciated. Best Regards, MG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Troubles starting MySQL
Hi, I'm having a hard time starting MySQL 4.1.21 I get: "Starting mysqld daemon with databases from /usr/local/mysql-4.1.21/var STOPPING server from pid file /tmp/mysql.pid 061102 14:06:13 mysqld ended" In the logs, i have: "061102 14:07:25 mysqld started InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 061102 14:07:25 [ERROR] Can't init databases 061102 14:07:25 [ERROR] Aborting" Now, if i add the line innodb_data_file_path = /usr/local/mysql-4.1.21/var/ibdata1:100M:autoextend i get: "061102 14:08:25 mysqld started 061102 14:08:25 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: File name .//usr/local/mysql-4.1.21/var/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 061102 14:08:25 mysqld ended" What's going on here ? This never happened to me in years. What is teh problem and how to solve it ? Thanks in advance. Mário Gamito
Re: help trying to add an autoincrement col to an exisiting table
You are better off doing the following DROP TABLE IF EXISTS users_new; CREATE TABLE users_new ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ); INSERT INTO users_new (uname,passwd,fname,lname,dir,pict,level,email,rank,dgroup,parent,seminar,getnewsletter) SELECT uname,passwd,fname,lname,dir,pict,level,email,rank,dgroup,parent,seminar,getnewsletter FROM users; DROP TABLE users; ALTER TABLE users_new RENAME TO users; That's it. You may want to create the table as mentioned before. However, if you prefer the 'uname' as the primary key, then create the table like this instead: CREATE TABLE users_new ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (uname), UNIQUE KEY id (id), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ); - Original Message - From: Randy Paries <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Wednesday, November 1, 2006 9:23:55 PM GMT-0500 US/Eastern Subject: help trying to add an autoincrement col to an exisiting table Hello, currently i have the following table structure CREATE TABLE users ( uname varchar(20) NOT NULL default '', passwd varchar(15) NOT NULL default '', fname varchar(25) NOT NULL default '', lname varchar(40) NOT NULL default '', dir varchar(28) NOT NULL default '', pict varchar(50) NOT NULL default '', level int(4) NOT NULL default '0', email varchar(40) NOT NULL default '', rank int(4) NOT NULL default '0', dgroup int(4) NOT NULL default '0', parent varchar(20) NOT NULL default '', seminar int(11) NOT NULL default '0', getnewsletter int(11) default '0', PRIMARY KEY (uname), KEY uname_users (uname), KEY dir_users (dir), KEY seminar_users (seminar), KEY user_lvl_idx (level) ) TYPE=MyISAM; I want to add an autoincrement field when i first tried i got the error Incorrect table definition; there can be only one auto column and it must be defined as a key so then i tried ALTER TABLE `users` DROP PRIMARY KEY; ALTER TABLE `users` ADD PRIMARY KEY (id); ALTER TABLE `users` CHANGE `id` `keyid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT; and i get the error Duplicate entry '0' for key 1 Can some one please tell me what i am doing wrong 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: Pushing mysql to the limits
Em Wed, 01 Nov 2006 00:00:23 -0800, Cabbar Duzayak escreveu: > We have huge amount of data, and we are planning to use logical > partitioning to divide it over multiple machines/mysql instances. This is a hard call. You will have to keep data consistent among servers, and MySQL does not support distributed transactions: it does have the XA interface, but it does not do its job properly. As far as I know, the only free SQL DBMS supporting distributed transactions is Ingres. > We are planning to use Intel based machines and will prefer ISAM since there > is not much updates but mostly selects. You are asking for trouble. Hear the voice of experience. > So, what I wanted to learn is how much can we push it to the limits on a > single machine with about 2 gig rams? Do you think MYSQL can handle ~ > 700-800 gigabyte on a single machine? Probably yes, but it will all depend on what you will do precisely with it. Anyway, you would be much better of with a more solid system, preferrably with proper XA distributed transactions (two-phase commit). > And, is it OK to put this much data in a single table, or should we divide it > over multiple tables? With a proper DBMS, you can partition the table in physical segments without complicating the logical model. Check Ingres or PostgreSQL, perhaps MySQL’s own MaxDB. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to view locks in MySQL
You may want to look into a MySQL paradigm called Advisory Locking. You could read MYSQL 5.0 Certification Study Guide, Chapter 28 pages 403,404 or got these URLs for more on GET_LCOK and RELEASE_LOCK functions http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html - Original Message - From: Ow Mun Heng <[EMAIL PROTECTED]> To: Submit MySQL Sent: Wednesday, November 1, 2006 11:21:25 PM GMT-0500 US/Eastern Subject: How to view locks in MySQL Under MSSQL there's a stored procedure called sp_lock which can be used. Is there an equivalent one in mySQL? -- 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: Last and Last-1
select idTable ... FROM FaxServer ORDER by DateFaxInsert DESC LIMIT 2 suomi Vittorio Zuccalà wrote: Hi, i've a table in a mysql database. It has a lot of field and three of them are: * A counter: IDTable * A date: DateFaxInsert * A Number: NumberFaxInsert I want to obtain the last and the last - 1 IDTable. If i write: SELECT MAX(IDTable) AS IDMax, DateFaxInsert, NumberFaxInsert FROM FaxServer GROUP BY DateFaxInsert is ok but i would like to see MAX and MAX-1 of IDTable... Any idea please? Thanks :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Manually Inserted Data
I did this on my computer create table site_calendar_v2 ( id int,start date,end date,global int, status varchar(20),time timestamp not null default now()); SELECT A.ID,A.Start,A.End FROM ((SELECT ID, Start, End FROM site_calendar_v2 as c WHERE Global='1' ) UNION ( SELECT '9', '2006-11-01', '-00-00')) A ORDER BY End, Start ASC, Status DESC, Time ASC; When I ran it, it gave me this ERROR 1054 (42S22): Unknown column 'Status' in 'order clause' Just extend the SELECT list to have the STATUS and a TIME like this: SELECT A.ID,A.Start,A.End FROM ((SELECT ID, Start, End , STATUS, TIME FROM site_calendar_v2 as c WHERE Global='1' ) UNION ( SELECT '9', '2006-11-01', '-00-00', 'no status', '-00-00 00:00:00')) A ORDER BY End, Start ASC, Status DESC, Time ASC; With no rows in the site_calendar_v2 table, I got back this: +---+++ | ID| Start | End| +---+++ | 9 | 2006-11-01 | -00-00 | +---+++ Give it a try. - Original Message - From: Gerald L. Clark <[EMAIL PROTECTED]> To: Keith Spiller <[EMAIL PROTECTED]> Cc: [MySQL] Sent: Wednesday, November 1, 2006 4:51:21 PM GMT-0500 US/Eastern Subject: Re: Manually Inserted Data Keith Spiller wrote: > Hi Rolando, > > Thanks for your help. > > I have reduced the query to the bare essensials to try to test the > concept, but > it still fails... > > ( SELECT ID, Start, End ( SELECT ID, Start, End as z > FROM site_calendar_v2 as c > WHERE Global='1' ) > UNION > ( SELECT '9', '2006-11-01', '-00-00' as z ) > ORDER BY z, Start ASC, Status DESC, Time ASC a > > Does anyone see my mistake? > > Keith > -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query ignoring index
Hi Johan, Thanks for that, it explains what's going on. I've added `date` to the `coords` index and that seems to have sped things up considerably. Thanks. - Jonathon Johan Höök wrote: Hi Jonathon, MySQL is using the index for both queries, as the column "key" in the explain result says. Using index means that MySQL can find all info it needs by looking at the index only, i.e doesn't need to look into the table. See: http://dev.mysql.com/doc/refman/5.0/en/explain.html for more. /Johan Jonathon Wardman skrev: Hello, I've been working on some queries with a large dataset (7.5 million rows) and have been finding problems with indexes seemingly being ignored for some queries - this obviously slows the query right down, I've seen some queries take up to 30 seconds. This only seems to happen when I use certain other columns in the where clause. The table I'm working with is this (I've cut out the columns not relevant to this query to save space): CREATE TABLE `sales` ( `transaction_id` varchar(255) NOT NULL default '', `date` date NOT NULL default '-00-00', `road` varchar(255) NOT NULL default '', `locality` varchar(255) NOT NULL default '', `district` varchar(255) NOT NULL default '', `post_town` varchar(255) NOT NULL default '', `county` varchar(255) NOT NULL default '', `postcode` varchar(10) NOT NULL default '', `easting` int(11) NOT NULL default '0', `northing` int(11) NOT NULL default '0', PRIMARY KEY (`transaction_id`(40)), KEY `postcode` (`postcode`), KEY `road` (`road`), KEY `locality` (`locality`), KEY `district` (`district`), KEY `post_town` (`post_town`), KEY `county` (`county`), KEY `northing` (`northing`), KEY `date` (`date`), KEY `coords` (`easting`,`northing`) ) ENGINE=MyISAM The following query does not use the index (as you can see from the explain output): SELECT count(*) AS number_of_rows FROM `sales` WHERE `easting` >= '314981' AND `easting` <= '315281' AND `northing` >= '176627' AND `northing` <= '176927' AND `date` > '2004-09-01'; ++-++---+--++-+--+--+-+ | id | select_type | table | type | possible_keys| key| key_len | ref | rows | Extra | ++-++---+--++-+--+--+-+ | 1 | SIMPLE | sales | range | northing,date,coords | coords | 8 | NULL | 2781 | Using where | ++-++---+--++-+--+--+-+ However, removing the date section of the where clause makes MySQL use the index it found: SELECT count(*) AS number_of_rows FROM `landregistry` WHERE `easting` >= '314981' AND `easting` <= '315281' AND `northing` >= '176627' AND `northing` <= '176927'; ++-++---+-++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+-++-+--+--+--+ | 1 | SIMPLE | sales | range | northing,coords | coords | 8 | NULL | 2781 | Using where; Using index | ++-++---+-++-+--+--+--+ Does anyone know any reason why this might be happening? It seems a little odd to me. Any ideas would be appreciated. Thanks, Jonathon Wardman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't create database
Hi, I've installed MySQL 5.0.27, but can't create databases. I get "ERROR 1006 (HY000): Can't create database 'contineo' (errno: 28)" In the log file i have this: "InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 640 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data!" Any ideas on how to solve this issue ? Any help would be appreciated. Best Regards, MG
Re: Query ignoring index
Hi Jonathon, MySQL is using the index for both queries, as the column "key" in the explain result says. Using index means that MySQL can find all info it needs by looking at the index only, i.e doesn't need to look into the table. See: http://dev.mysql.com/doc/refman/5.0/en/explain.html for more. /Johan Jonathon Wardman skrev: Hello, I've been working on some queries with a large dataset (7.5 million rows) and have been finding problems with indexes seemingly being ignored for some queries - this obviously slows the query right down, I've seen some queries take up to 30 seconds. This only seems to happen when I use certain other columns in the where clause. The table I'm working with is this (I've cut out the columns not relevant to this query to save space): CREATE TABLE `sales` ( `transaction_id` varchar(255) NOT NULL default '', `date` date NOT NULL default '-00-00', `road` varchar(255) NOT NULL default '', `locality` varchar(255) NOT NULL default '', `district` varchar(255) NOT NULL default '', `post_town` varchar(255) NOT NULL default '', `county` varchar(255) NOT NULL default '', `postcode` varchar(10) NOT NULL default '', `easting` int(11) NOT NULL default '0', `northing` int(11) NOT NULL default '0', PRIMARY KEY (`transaction_id`(40)), KEY `postcode` (`postcode`), KEY `road` (`road`), KEY `locality` (`locality`), KEY `district` (`district`), KEY `post_town` (`post_town`), KEY `county` (`county`), KEY `northing` (`northing`), KEY `date` (`date`), KEY `coords` (`easting`,`northing`) ) ENGINE=MyISAM The following query does not use the index (as you can see from the explain output): SELECT count(*) AS number_of_rows FROM `sales` WHERE `easting` >= '314981' AND `easting` <= '315281' AND `northing` >= '176627' AND `northing` <= '176927' AND `date` > '2004-09-01'; ++-++---+--++-+--+--+-+ | id | select_type | table | type | possible_keys| key| key_len | ref | rows | Extra | ++-++---+--++-+--+--+-+ | 1 | SIMPLE | sales | range | northing,date,coords | coords | 8 | NULL | 2781 | Using where | ++-++---+--++-+--+--+-+ However, removing the date section of the where clause makes MySQL use the index it found: SELECT count(*) AS number_of_rows FROM `landregistry` WHERE `easting` >= '314981' AND `easting` <= '315281' AND `northing` >= '176627' AND `northing` <= '176927'; ++-++---+-++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+-++-+--+--+--+ | 1 | SIMPLE | sales | range | northing,coords | coords | 8 | NULL | 2781 | Using where; Using index | ++-++---+-++-+--+--+--+ Does anyone know any reason why this might be happening? It seems a little odd to me. Any ideas would be appreciated. Thanks, Jonathon Wardman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than one MyQSL in a server
hi, have you traced .err file for the nature of the error? if not you can try that. Thanks ViSolve DB Team - Original Message - From: "Mário Gamito" <[EMAIL PROTECTED]> To: "Nico Sabbi" <[EMAIL PROTECTED]> Cc: Sent: Thursday, November 02, 2006 4:31 PM Subject: Re: More than one MyQSL in a server Hi, I'm now trying to run the second MySQL with: # ./mysqld_safe --port=3307 --socket=/usr/local/mysql-5.0.27/share/mysql.sock2 --pid-file=/tmp/mysql.sock2 --datadir=/usr/local/mysql-5.0.27/var/ but i get: "Starting mysqld daemon with databases from /usr/local/mysql-5.0.27/var/ STOPPING server from pid file /usr/local/mysql-5.0.27/share/mysql.sock2 061102 10:54:03 mysqld ended" Any ideas ? Best Regards, Mário Gamito On 11/2/06, Nico Sabbi <[EMAIL PROTECTED]> wrote: Mário Gamito wrote: > Hi, > > I have a 3.23 MySQL running in a server and i want to install 5.0.27 > > I made > > # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 > # make > # make install > > and then > > # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var > # ./mysqld_safe --datadir=/usr/local/mysql-5.0.27/var/ & > > But here, i get the error "A mysqld process already exists" > > How can i have the two MySQL running in the same machine ? > > Any help would be appreciated. > > Warm Regards, > MG > mysqld_multi works pretty well. It's documented in www.mysql.com/doc. mysqld_multi --example shows a sample config file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query ignoring index
Hello, I've been working on some queries with a large dataset (7.5 million rows) and have been finding problems with indexes seemingly being ignored for some queries - this obviously slows the query right down, I've seen some queries take up to 30 seconds. This only seems to happen when I use certain other columns in the where clause. The table I'm working with is this (I've cut out the columns not relevant to this query to save space): CREATE TABLE `sales` ( `transaction_id` varchar(255) NOT NULL default '', `date` date NOT NULL default '-00-00', `road` varchar(255) NOT NULL default '', `locality` varchar(255) NOT NULL default '', `district` varchar(255) NOT NULL default '', `post_town` varchar(255) NOT NULL default '', `county` varchar(255) NOT NULL default '', `postcode` varchar(10) NOT NULL default '', `easting` int(11) NOT NULL default '0', `northing` int(11) NOT NULL default '0', PRIMARY KEY (`transaction_id`(40)), KEY `postcode` (`postcode`), KEY `road` (`road`), KEY `locality` (`locality`), KEY `district` (`district`), KEY `post_town` (`post_town`), KEY `county` (`county`), KEY `northing` (`northing`), KEY `date` (`date`), KEY `coords` (`easting`,`northing`) ) ENGINE=MyISAM The following query does not use the index (as you can see from the explain output): SELECT count(*) AS number_of_rows FROM `sales` WHERE `easting` >= '314981' AND `easting` <= '315281' AND `northing` >= '176627' AND `northing` <= '176927' AND `date` > '2004-09-01'; ++-++---+--++-+--+--+-+ | id | select_type | table | type | possible_keys| key| key_len | ref | rows | Extra | ++-++---+--++-+--+--+-+ | 1 | SIMPLE | sales | range | northing,date,coords | coords | 8 | NULL | 2781 | Using where | ++-++---+--++-+--+--+-+ However, removing the date section of the where clause makes MySQL use the index it found: SELECT count(*) AS number_of_rows FROM `landregistry` WHERE `easting` >= '314981' AND `easting` <= '315281' AND `northing` >= '176627' AND `northing` <= '176927'; ++-++---+-++-+--+--+--+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows | Extra| ++-++---+-++-+--+--+--+ | 1 | SIMPLE | sales | range | northing,coords | coords | 8 | NULL | 2781 | Using where; Using index | ++-++---+-++-+--+--+--+ Does anyone know any reason why this might be happening? It seems a little odd to me. Any ideas would be appreciated. Thanks, Jonathon Wardman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Last and Last-1
Hi, i've a table in a mysql database. It has a lot of field and three of them are: * A counter: IDTable * A date: DateFaxInsert * A Number: NumberFaxInsert I want to obtain the last and the last - 1 IDTable. If i write: SELECT MAX(IDTable) AS IDMax, DateFaxInsert, NumberFaxInsert FROM FaxServer GROUP BY DateFaxInsert is ok but i would like to see MAX and MAX-1 of IDTable... Any idea please? Thanks :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than one MyQSL in a server
Hi, I'm now trying to run the second MySQL with: # ./mysqld_safe --port=3307 --socket=/usr/local/mysql-5.0.27/share/mysql.sock2 --pid-file=/tmp/mysql.sock2 --datadir=/usr/local/mysql-5.0.27/var/ but i get: "Starting mysqld daemon with databases from /usr/local/mysql-5.0.27/var/ STOPPING server from pid file /usr/local/mysql-5.0.27/share/mysql.sock2 061102 10:54:03 mysqld ended" Any ideas ? Best Regards, Mário Gamito On 11/2/06, Nico Sabbi <[EMAIL PROTECTED]> wrote: Mário Gamito wrote: > Hi, > > I have a 3.23 MySQL running in a server and i want to install 5.0.27 > > I made > > # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 > # make > # make install > > and then > > # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var > # ./mysqld_safe --datadir=/usr/local/mysql-5.0.27/var/ & > > But here, i get the error "A mysqld process already exists" > > How can i have the two MySQL running in the same machine ? > > Any help would be appreciated. > > Warm Regards, > MG > mysqld_multi works pretty well. It's documented in www.mysql.com/doc. mysqld_multi --example shows a sample config file
Re: More than one MyQSL in a server
Mário Gamito wrote: Hi, I have a 3.23 MySQL running in a server and i want to install 5.0.27 I made # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 # make # make install and then # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var # ./mysqld_safe --datadir=/usr/local/mysql-5.0.27/var/ & But here, i get the error "A mysqld process already exists" How can i have the two MySQL running in the same machine ? Any help would be appreciated. Warm Regards, MG mysqld_multi works pretty well. It's documented in www.mysql.com/doc. mysqld_multi --example shows a sample config file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Will a subquery use a index?
I found that when I use a query as a subquery it will not use the right index at all??
More than one MyQSL in a server
Hi, I have a 3.23 MySQL running in a server and i want to install 5.0.27 I made # ./configure --prefix=/usr/local/mysql-5.0.27 --with-tcp-port=3307 # make # make install and then # scripts/mysql_install_db --datadir=/usr/local/mysql-5.0.27/var # ./mysqld_safe --datadir=/usr/local/mysql-5.0.27/var/ & But here, i get the error "A mysqld process already exists" How can i have the two MySQL running in the same machine ? Any help would be appreciated. Warm Regards, MG
Re: How many colums should a index contain?
John.H wrote: I have two tables and I must do : select `id`,`bid`,`title`,`link`,`bname` from table1 where `bid` in ( ...this is a subquery in table2 ) should I create a index (`id`,`bid`,`title`,`link`,`bname`) so that my query will take less time or should a index contain so many colums? Indexes are only needed for the where clauses, not for the items you are selecting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How many colums should a index contain?
I have two tables and I must do : select `id`,`bid`,`title`,`link`,`bname` from table1 where `bid` in ( ...this is a subquery in table2 ) should I create a index (`id`,`bid`,`title`,`link`,`bname`) so that my query will take less time or should a index contain so many colums?
