Re: Same question, better example
leo huang wrote: Ariel, You can try this: mysqlselect stri from prueba order by stri+0 desc; Leo Huang Or just make the column an INT instead of a CHAR, if you want a natural number order. Sorting is performed differently between characters and integers - as is obviously shown in the example. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql on Windows
Hi Neil, Neil Tompkins wrote: Is anyone running mySQL on Windows 2003 server platform. Are there any known problems ? There is a known bug for Windows 2003 - bug #12071: http://bugs.mysql.com/bug.php?id=12071 You should use 4.0.19 or 5.0.19. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Accountability with MySQL
Martijn Tonies wrote: We're arguing about whether or not your database design (as stored on disk) should contain NULLs. IMO: no, or at least as few as possible. I believe the answer really is - *it depends*. You are both right, really. Martijn, yes, according to academia and proper database design, you should not really be using NULLs, (Rhino, see the writings of Chris Date and Fabian Pascal somewhere like www.dbdebunk.com, www.thethirdmanifesto.com etc.). Here's a good one: http://www.dbdebunk.citymax.com/page/page/1396241.htm Of course, you're still going to pay $10-$15 for this.. However, what is good for academia is *not* always good for the real world. Why store Unknown in a column for a table that could store millions, or billions, of rows, when you could simply allow a NULL (and save a *bunch* of disk space across your schemas) in the real world. If you can allow for the use of the NULL in the application, and *not* try to interpret it 5 ways from Sunday, then, in my honest opinion, a NULL would be acceptable. Short answer, as long as you do it without trying to get your application to handle them in many different ways, I don't see a great problem (when weighing them against extra costs, in both space and speed). If you can get away with not using NULL as well, then great. This argurment will, however, carry on between academia and the real world for at least the next 4000 years. :) Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help: recovering db from crash
This is actually for Linux/Unix, not Windows. What error do you get from MySQL when trying to log in? Does the mysqld(-nt) process show within Task Manager? What does the new error log say? You may need to reset permissions: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html Or you may need to set the appropriate datadir / basdir etc. depending on how you set up MySQL: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Best regards Mark Adrian Bruce wrote: not sure, but it may be worth trying the following run the script: mysql_install_db --user=root In the installation dir this should change ownership and make mysql recognise the data dir. good luck Ade Foo Ji-Haw wrote: Hi all, My Windows-based database server crashed (no fault of MySQL. probably OS or hardware), and I managed to copy out the data files. I am using version 5.0 of the Essentials package. I tried to install a similar setup on another server, then copy the data\ folder over. The MySQL service starts, but I am not able to login, even as root. Is there anyone who can advise me on the recovery steps? Appreciate your feedback! -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb in 4.1.18
Bill Adams wrote: InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate. well, thats what I found before. But it doesn't explain why InnoDB does need a logfile even when all transactions are committed or rolled back and mysql is shut down. More is stored in the innodb log files besides a strict log of events. It does explain, that I can use innodb_file_per_table which gives the impression that with that option a shared ibdata isn't required. Or maybe it is required during runtime, but not for a backup. You might be able to set e.g. innodb_data_file_path = ibdata:0M but in any event the innodb log files track the existence of the different ibdata files (size, if they have been formatted, etc). You always have to have the central tablespace, whether you are using file per table or not. First and foremost, for your backup purposes, *it holds data dictionary information* (as well as within the frm files within each database), plus undo/rollback segment space and the adaptive hash index - even when using file per table. So with InnoDB it is not possible to shutdown the database server, backup the files and maybe use them on another server if the other server already has an ibdata and ib_logfile? Do I always have to do a mysqldump then? That takes much longer. You can. But direct file system operations on MySQL (really any database) are dangerous. Same thing with why IMHO you should use PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); rather than just deleting your binary logs directly (if you are using replication and all that). If you are going to copy the InnoDB files you probably need to have an exact setup. And as I am sure you have figured out you have to back up the .idb files and the innodb log files at the same time. AFAIK, You cannot take the .idb file from one server and copy it to another server and have it just work (like you can with the MyISAM files). Correct, to move any InnoDB tables between machines you need: o All ib data files o All ib logfiles o All related database directories and frm files o Your my.cnf/ini http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html Regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Hi Jorrit, Jorrit Kronjee wrote: [EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Jorrit Kronjee wrote: On 3/29/2006 2:10 PM, Jorrit Kronjee wrote Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Mark, We've been testing it over the weekend and it seems that the bugs we ran into before completely disappeared. Thanks for your support! Jorrit Hi Jorrit, Great news - that bug was one that we tried for a long time to replicate (as can be seen from the bug report), eventually I managed to repeat it a few months ago, and we got the fix turned around fairly quickly - so it's a fairly obvious one every time I see it now ;) Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: describe table : improvement
Hi, Gabriel PREDA wrote: It is: SHOW FULL COLUMNS FROM a_table You will get 2 extra columns: - Privileges (showing the privileges of the user for that column) - Comment (showing a per column comment) As well as Collation (the columns collation). When creating a table you can add a comment using COMMENT keyword: CREATE TABLE a_table ( a_column CHAR(30) CHARSET utf8 COMMENT 'Some comment' ); Is this... what you needed ? -- Gabriel PREDA Senior Web Developer Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to find size of my database
, size pages 97 FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update INDEX: name rental_date, id 0 45, fields 3/4, type 2 root page 208, appr.key vals 17655, leaf pages 28, size pages 29 FIELDS: rental_date inventory_id customer_id rental_id INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0 root page 210, appr.key vals 4467, leaf pages 16, size pages 17 FIELDS: inventory_id rental_id INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0 root page 211, appr.key vals 589, leaf pages 16, size pages 17 FIELDS: customer_id rental_id INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0 root page 212, appr.key vals 1, leaf pages 13, size pages 14 FIELDS: staff_id rental_id FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id ) REFERENCES sakila/staff ( staff_id ) FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental ( inventory_id ) REFERENCES sakila/inventory ( inventory_id ) FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental ( customer_id ) REFERENCES sakila/customer ( customer_id ) FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment ( rental_id ) REFERENCES sakila/rental ( rental_id ) As you can see - bunches of information (including showing the internal columns used for transactions). However the things to note here are the leaf pages and size pages for each index. leaf pages is the number of pages that actually contain data (as indexes store their data in the leaf nodes), whilst size pages is the total number of pages that are allocated to the table. InnoDB uses clustered indexes - so the data for each row is actually stored within the PRIMARY KEY index for each table. Each page within InnoDB is 16kb in size. So as you can see above for Index: name PRIMARY we have size_pages 97 (16,384 * 97 = 1,589,248 (1.5Mb)) allocated to the table within the InnoDB tablespace, with only leaf pages 53 (16,384 * (97-53) = 720,896 (~700Kb)) actually being used. You can then perform the same kind of sum across the rest of the indexes within each table to get an approximation such as that given for Index_length within SHOW TABLE STATUS / INFORMATION_SCHEMA.TABLES as well. Of course, this is not an easy method to use, there is no simple command to quickly analyze this data (yet), it's mostly a manual process reading through the dump file - however shouldn't be that hard to script, either. Hope this helps! Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE question
Cummings, Shawn (GNAPs) wrote: If I have 4 Fields (FIELD1, FIELD2, FIELD3 FIELD4) I can do this easily; UPDATE TABLE_NAME SET FIELD4 = FIELD1; But -- how do I do it so that FIELD4 = FIELD1 FIELD2 ??? I can't seem to find any examples online. Maybe it's just too early in the morning - I'm drawing a blank! ;) This depends on the data types, if you want to set FIELD4 to be FIELD1 + FIELD2 with integers: UPDATE table_name SET FIELD4 = (FIELD1 + FIELD2); If they are strings: UPDATE table_name SET FIELD4 = CONCAT(FIELD1,' ',FIELD2); If you are swapping values: UPDATE table_name SET FIELD4 = (@tmp:=FIELD4), FIELD4 = FIELD1, FIELD1 = @tmp; Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PRINT statement?
Stephen Cook wrote: There are such things as extensions to the standard, and many languages besides BASIC that have the ability to output a character string. No need to be snippy. I will look into the --silent option, thanks! I also tend to use -BN with these kind of scripts: mysql -u user -BN dbname file.sql mysql -u root -BN -e SHOW DATABASES etc. Regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: STORED PROCEDURE
CREATE PROCEDURE title() BEGIN DECLARE title VARCHAR(255); SET title = '%Unconditional%'; SELECT title; END; // mysql CALL title()// +-+ | title | +-+ | %Unconditional% | +-+ 1 row in set (0.01 sec) HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: 02 August 2005 15:12 To: 'Mysql ' Subject: STORED PROCEDURE I'm used with MS SQL and could not understand MySQL's document. I'm trying to create something like the simple example DECLARE @title varchar(255) SET @title='%Unconditional%' SELECT @title; How do I get around to it in MySQL? -- Power to people, Linux is here. -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: STORED PROCEDURE
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:38
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: STORED PROCEDURE
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote:
CREATE PROCEDURE title()
BEGIN
DECLARE title VARCHAR(255);
SET title = '%Unconditional%';
SELECT title;
END;
//
mysql CALL title()//
+-+
| title |
+-+
| %Unconditional% |
+-+
1 row in set (0.01 sec)
HTH
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:12
To: 'Mysql '
Subject: STORED PROCEDURE
I'm used with MS SQL and could not understand MySQL's
document. I'm
trying to create something like the simple example
DECLARE @title varchar(255)
SET @title='%Unconditional%'
SELECT @title;
How do I get around to it in MySQL?
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Something similiar to
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255)
SET u_title='%Unconditional%'
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Yea, that will work - just modify the example I gave above to yours above..
What the hell, I'll even write it:
DELIMITER //
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255);
SET u_title='%Unconditional%';
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Seems a bit of a strange use for a stored procedure though - I would have
thought something like this would be of more use:
CREATE PROCEDURE Select_title( u_title VARCHAR(255) )
BEGIN
CASE
WHEN u_title = '' THEN SET u_title='%Unconditional%';
ELSE SET u_title = CONCAT('%',u_title,'%');
END CASE;
SELECT T.Title,B.BAND_Name,C.Type,T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: STORED PROCEDURE
-Original Message-
From: Mark Leith [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:56
To: mysql@lists.mysql.com
Subject: RE: STORED PROCEDURE
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:38
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: STORED PROCEDURE
On 8/2/05, Mark Leith [EMAIL PROTECTED] wrote:
CREATE PROCEDURE title()
BEGIN
DECLARE title VARCHAR(255);
SET title = '%Unconditional%';
SELECT title;
END;
//
mysql CALL title()//
+-+
| title |
+-+
| %Unconditional% |
+-+
1 row in set (0.01 sec)
HTH
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: Scott Hamm [mailto:[EMAIL PROTECTED]
Sent: 02 August 2005 15:12
To: 'Mysql '
Subject: STORED PROCEDURE
I'm used with MS SQL and could not understand MySQL's
document. I'm
trying to create something like the simple example
DECLARE @title varchar(255)
SET @title='%Unconditional%'
SELECT @title;
How do I get around to it in MySQL?
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Something similiar to
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255)
SET u_title='%Unconditional%'
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
--
Power to people, Linux is here.
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date:
01/08/2005
Yea, that will work - just modify the example I gave above to
yours above..
What the hell, I'll even write it:
DELIMITER //
CREATE PROCEDURE Select_title()
BEGIN
DECLARE u_title varchar(255);
SET u_title='%Unconditional%';
SELECT
T.Title,
B.BAND_Name,
C.Type,
T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Seems a bit of a strange use for a stored procedure though -
I would have thought something like this would be of more use:
CREATE PROCEDURE Select_title( u_title VARCHAR(255) ) BEGIN CASE
WHEN u_title = '' THEN SET u_title='%Unconditional%';
ELSE SET u_title = CONCAT('%',u_title,'%'); END CASE;
SELECT T.Title,B.BAND_Name,C.Type,T.Track
FROM Title T
LEFT JOIN Bands B ON B.BandID=T.B_ID
LEFT JOIN CD_Type C ON C.CD_ID=T.C_ID
WHERE Title LIKE u_title
ORDER BY Title;
END;
//
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
Hmm nice screwed formatting, but anyway.. ;)
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.9.8/61 - Release Date: 01/08/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table and Data access only through procedures
Gleb, I had already submitted that bug, around a week ago, you can see my version here: http://bugs.mysql.com/bug.php?id=12307 Apparently it's been fixed in 5.0.11.. Now documenting. You may want to update your bug to point to mine and close it off ;) The procedure in the bug causes some interesting issues when thinking about security levels also. Basically, it was a test case procedure that I had wrote as an example for my blog (I'm doing kind of a 5.0 new features run on it at the moment) - that does the kind of thing being asked about here. From the security perspective, you can see that SQL SECURITY is set to INVOKER. In other words the procedure will run with all the privileges that the person that calls the procedure has.. This is done because we would pick up on who's running the procedure, to compare against a list of privileged users to make sure that they can actually run the procedure. However, as this is the case - they can't then go and UPDATE the mysql.user table, without direct privileges to it. If you ran it with SQL SECURITY DEFINER, and created the procedure as say, root, then CURRENT_USER() will always return [EMAIL PROTECTED], which would be kind of useless in this context. To run this under SQL SECURITY INVOKER you would have to, at the very minimum, GRANT SELECT (user), UPDATE (password) ON mysql.user TO 'invoker'@'host' To run the FLUSH (which doesn't look like it's going to be allowed anyway), you would also need to GRANT the RELOAD privilege to the user as well. Then - what's to stop the person going an updating the table directly..? ;) Well, to get this to work all you have to do is set the procedure to run as SQL SECURITY DEFINER, and create the procedures with a user that has all of the privileges to run them, drop all of the user checking stuff - then simply GRANT EXECUTE on each procedure to each user that should be allowed to run them.. This way the users will have no direct table level privileges, just very specific PROCEDURE EXECUTE permissions. To add the privileges with the example above would be GRANT EXECUTE ON PROCEDURE admin.update_user_password TO .. to each user that you want to allow to update a password. Hope this helps out a little with the original question as well.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 10 August 2005 09:55 To: mysql@lists.mysql.com Subject: Re: Table and Data access only through procedures Hello. MySQL 5.0.10 offers modifying tables from stored procedures, however I've met some problems. I've tried to create a procedure like your DELETE_USER, but DROP USER seems to accept only literal strings, not variables, while direct modifying of GRANT tables and calling FLUSH privileges leads server to hang. See my bug report: http://bugs.mysql.com/bug.php?id=12485 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 09/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GRANT EXECUTE in MySQL 4.1
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 09:40 To: mysql@lists.mysql.com Subject: GRANT EXECUTE in MySQL 4.1 Hi there, For some reason, GRANT EXECUTE is possible on the global level, but not on the database level: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES Has anyone got any idea what EXECUTE should do on a global level in MySQL 4.1? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Martin, EXECUTE is specifically for stored procedures.. Whilst the privilege is available in = 4.0.2, it has no effect at all until 5.0.something. This is all documented in the Privileges provided by MySQL manual page I believe.. EXECUTE on a GLOBAL level would, of course, allow the grantee to call any stored procedure. HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql database characterset
-Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 10:43 To: mysql@lists.mysql.com Subject: mysql database characterset Hi there, Is the mysql database always in UTF8 characterset for MySQL 4.1 and up? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com Yep.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.7/70 - Release Date: 11/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to change ft_max_word_len value beyond 254
-Original Message- From: Bendick Mahleko [mailto:[EMAIL PROTECTED] Sent: 12 August 2005 12:22 To: mysql@lists.mysql.com Subject: how to change ft_max_word_len value beyond 254 Hello, I want to index a table using a TEXT value, with length 255. I tried changing ft_max_word_len but each time I check the status of variables, I notice the changes are not taken. It defaults to 254. I am able to change this value to anything below 254. Is there any other way to enforce this ft_max_word_len value to some arbitrary value above 254? The point is, because my index length is being limited to only 254, I am having false misses in my SELECT queries, based on the TEXT index. Bendick Hi Bendick, Am I missing something here? The ft_max_word_len variable sets the maximum length of any word that fulltext will index, *not* the maximum length of the field that you are indexing. Now, unless you are indexing some scientific data, with for instance some strange, long virus name - I don't know of any word, in the English language at least, that is longer than 254 characters. I recently built a dictionary table for fun, with ~500,000 words from the English language in the table, so I can verify this for you if you want ;) Perhaps your false misses are due to something else, such as ft_min_word_len, or the values being in more than 50% of the rows etc. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.7/70 - Release Date: 11/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why can't I revoke usage from user?
Martin, Comments in-line: -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 16 August 2005 10:35 To: mysql@lists.mysql.com Subject: Re: Why can't I revoke usage from user? Gleb, Hello. 'USAGE' means that user doesn't have any privileges. Use 'DROP USER'. See: http://dev.mysql.com/doc/mysql/en/drop-user.html I disagree here. drop user will drop the user, revoking usage can be something different. You can't actually revoke USAGE, without dropping the user. If, for example, you do grant usage on dbname.* this is different from then just a user without usage granted to that database. USAGE is a global level privilege, whilst you can GRANT USAGE ON test.* .., it's actually ignored. Besides, having a user without privileges might be useful as well :-) Exactly what USAGE is for.. ;) Here's a quick example: mysql create user frank; Query OK, 0 rows affected (0.00 sec) mysql show grants for frank; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'frank'@'%' | +---+ 1 row in set (0.00 sec) mysql show databases; ++ | Database | ++ | information_schema | | Tracking | | mysql | | test | ++ 4 rows in set (0.00 sec) mysql grant usage on test.* to frank; Query OK, 0 rows affected (0.00 sec) mysql show grants for frank; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'frank'@'%' | +---+ 1 row in set (0.00 sec) mysql revoke usage on *.* from frank; Query OK, 0 rows affected (0.00 sec) mysql show grants for frank; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'frank'@'%' | +---+ 1 row in set (0.00 sec) Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can I use the information from SHOW STATUS in a SELECT statment ?
-Original Message- From: Sid Lane [mailto:[EMAIL PROTECTED] Sent: 16 August 2005 14:54 To: mysql@lists.mysql.com Subject: Re: Can I use the information from SHOW STATUS in a SELECT statment ? I don't know if you can do it directly in a mysql shell like that (like you would with v$, dba_ in Oracle) but if you call a show command from PERL (via DBI) it hashes the result just like it were from a select. may not be the most elegant solution but its the best I've come up with though I'll happily blush in embarassment if there's a direct way I've not found... No you are absolutely correct, there is no way to for instance, use a SHOW command in a sub query, or even in a cursor on version 5.. Of course, if you are using version 5 then a lot of the SHOW commands are also being migrated to the INFORMATION_SCHEMA, although not all of them as yet (such as SHOW FULL PROCESSLIST) - and you can use those just like the DBA_ views in Oracle. So, the only fall back is to use an external script/application. I'm not sure whether this is planned to be supported or not, but it would be nice to grab the output of a SHOW command in a cursor, I was discussing this in #mysql on freenode last night as it happens.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change Datatype (time)
-Original Message- From: Dennis Olvany [mailto:[EMAIL PROTECTED] Sent: 16 August 2005 16:43 To: mysql@lists.mysql.com Subject: Change Datatype (time) Is there a function that I can use in a query to return values in a different datatype? I've got a column which is time datatype and I'd like to return the values in a vb-compatible datatype. You can use DATE_FORMAT() for this: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Date Ranges
---snip--
Given any two dates, MySQL can tell if a third date is within
that range.
That's easy.
To actually return a list of all dates between any arbitrary
pair of dates requires some form of loop (v5.0+) or a lookup
into a table populated with all possible dates (any version
that supports joins). It's possible to get MySQL to give you
a list of dates but not as a native function. There is just
no facility built into the system to return that list. Sorry!
Have you seen such a function before? If so, where and what
was it called?
Most of the times when people ask this question, they have a
report they want to write and need to generate blank rows
for dates that aren't in the data. Is that what you need or
is there some other purpose to your question?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Indeed, we were asked this question on the freenode IRC channel a few weeks
ago - somebody wanted to calculate how many days between two dates fell
within a weekend, where there were possibly gaps within the data that they
had. I came up with the following procedure which gives an example of how to
do it with a loop and a temporary table, then some other little date
statistics for the given date range. The main point of interest, as Shawn
noted, is the loop that creates the dates within the range, and inserts them
in to a temporary table:
DROP PROCEDURE date_stats//
CREATE PROCEDURE date_stats ( IN sdate DATE, IN edate DATE)
BEGIN
DECLARE dates_done INT DEFAULT 0;
CREATE TEMPORARY TABLE date_range ( tdate DATE );
dates: LOOP
IF dates_done = 1 THEN
LEAVE dates;
END IF;
CASE WHEN sdate edate
THEN INSERT INTO date_range VALUES (sdate);
ELSE SET dates_done = 1;
END CASE;
SET sdate = sdate + INTERVAL 1 DAY;
END LOOP dates;
SELECT SUM(IF(WEEKDAY(tdate) IN (0,1,2,3,4),1,0)) as week_day_cnt,
SUM(IF(WEEKDAY(tdate) IN (5,6),1,0)) as weekend_day_cnt
FROM date_range;
SELECT ROUND(COUNT(*)/7) as number_of_weeks,
COUNT(*) as number_of_days
FROM date_range;
SELECT TIMESTAMPDIFF(SECOND,MIN(tdate),MAX(tdate)) as seconds_diff,
TIMESTAMPDIFF(MINUTE,MIN(tdate),MAX(tdate)) as minutes_diff,
TIMESTAMPDIFF(HOUR,MIN(tdate),MAX(tdate)) as hours_diff
FROM date_range;
DROP TEMPORARY TABLE date_range;
END;
//
CALL date_stats('2005-01-01','2005-02-01')//
+--+-+
| week_day_cnt | weekend_day_cnt |
+--+-+
| 21 | 10 |
+--+-+
1 row in set (2.78 sec)
+-++
| number_of_weeks | number_of_days |
+-++
| 4 | 31 |
+-++
1 row in set (2.78 sec)
+--+--++
| seconds_diff | minutes_diff | hours_diff |
+--+--++
| 2592000 |43200 |720 |
+--+--++
1 row in set (2.78 sec)
Query OK, 0 rows affected (2.97 sec)
Hope this helps,
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
http://leithal.cool-tools.co.uk
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.18/91 - Release Date: 06/09/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: colum totals
You can achieve this with the WITH ROLLUP option of GROUP BY. Although you would probably have to perform a trick on the date column to have the NULL value that WITH ROLLUP would return to return as total - i.e IFNULL(date,'Total') as a quick (and dirty) example, as this doesn't work too well on statements that have multiple GROUP BY columns ;) You can read more about WITH ROLLUP here: http://dev.mysql.com/doc/mysql/en/group-by-modifiers.html Mark Leith -Original Message- From: Tony Leake [mailto:[EMAIL PROTECTED] Sent: 16 September 2005 13:19 To: mysql@lists.mysql.com Subject: colum totals Hi, I'm sure I read somewhere about a new function that would give the totals of all columns, ie if i have 2 cols, numberOfOrders valueOfOrders for a range of dates i can get something like the following Date numberOfOrdersvalueOfOrders 01-09 4 20.00 02-09 6 100.00 total 10120.00 could someone point me to this in the manual please as I can't seem to find it. Or did i just imagine it anyway? thanks tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database link
You may want to think about doing this the opposite way around also, and look at pushing the data from Oracle in to MySQL. Oracle has something called heterogeneous services, which allows you to define ODBC datasources as valid entries in the tnsnames.ora file. Then you could simply create a job in Oracle that executes a procedure to do the entire process (truncate / load), no external scripting necessary.. Here's a quick example of what to do: First set up an ODBC data source for your MySQL database, using MyODBC. Create a file in ORACLE_HOME/hs/admin called initMySQL.ora. In this file put the following options: HS_FDS_CONNECT_INFO = MySQL5 #ODBC DSN /* Replace MySQL5 with your DSN */ HS_FDS_TRACE_LEVEL = OFF Alter your listener.ora file (ORACLE_HOME/network/admin) to add the following: (SID_DESC = (PROGRAM = hsodbc) (ORACLE_HOME = oracle/product/92) /* Your ORACLE_HOME */ (SID_NAME = MySQL5) /* Your DSN */ ) Add the following to your tnsnames.ora file: MYSQL5 = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=MYSQL5)) (HS=OK) ) Reload your Oracle listener (lsnrctl reload), and then connect to the Oracle database. To set the database link up: CREATE DATABASE LINK mysql5 CONNECT TO user identified by password using 'mysql5'; User and password should be a valid user within MySQL, that can connect from the Oracle host. You should be set to go from there. Here's a quick example of this working, to a MySQL 5 database using the new sakila sample database that Mike Hillyer recently released (http://www.openwin.org/mike/download/sakila.zip): SQL select count(*) from [EMAIL PROTECTED]; COUNT(*) -- 1000 SQL desc [EMAIL PROTECTED]; Name Null?Type - film_idNUMBER(10) category_id NOT NULL NUMBER(10) title NOT NULL VARCHAR2(27) descriptionLONG rental_duration NOT NULL NUMBER(3) length NUMBER(10) rating CHAR(5) SQL insert into [EMAIL PROTECTED] values (100,1,'test','test',1,1,'PG'); 1 row created. ---change prompts--- mysql use sakila Database changed mysql select max(film_id) from film; +--+ | max(film_id) | +--+ | 100 | +--+ 1 row in set (0.01 sec) HTH Mark Leith -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.11.0/103 - Release Date: 15/09/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to push an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for mysql external command trigger. Well, you can create a UDF, and should be able to call the UDF within a trigger.. Check out some of the memcached UDFs that were created: http://capttofu.livejournal.com/8078.html These should give a good idea of A) how to create a UDF and B) how to talk to another process within them to send / update data etc. You might even choose to cache your stuff in memcached and just use these anyway! ;) Cheers, Mark -- Mark Leith MySQL Regional Support Manager, Americas Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wrong PID
Marcin Polewski wrote: On my machine, I start the following command: /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/zigzu.bixbots.com.pid For some reason my PID file does not contain the parent PID, instead it contains one the children's PID. So when I attempt to stop MySQL, it stops only one of the child mysqld processes and not all of them. For example, when mysqld_safe starts up, the following PIDs are running: 23349 mysqld_safe + 23378mysqld --snip-- + 23567mysqld + 24078mysqld But the PID file will contain 23383 which is not the parent process. How can I resolve this? By using the correct way to shut down the server? :) mysqladmin -u root -p shutdown If you don't want to put your password in the clear within a script, you can use something like expect (http://expect.nist.gov/), or put the password for MySQL's root within ~/.my.cnf for the OS user you are running the script as. I certainly don't recommend killing the server (well, depends on the situation and kill level I guess, but I have to say this up front). Also, if you're running under mysqld_safe, mysqld will simply be restarted by that process should it go down (by killing it or not), so you would also have to kill mysqld_safe first. Further, the above looks like you are running under LinuxThreads, which actually shows threads as processes within ps etc. - so the above are not processes, but threads. If you switch to NPTL (Native Posix Threading Library) within Linux, you will actually see the single mysqld process (along with the single mysqld_safe process). You can tell what you are running with 'getconf GNU_LIBPTHREAD_VERSION', to switch to NPTL 'export LD_ASSUME_KERNEL=2.6.9', to switch back to LinuxThreads 'export LD_ASSUME_KERNEL 2.4.19'. I'd be interested in seeing if that stores the right PID for you whilst running under NPTL. Best regards Mark -- Mark Leith MySQL Regional Support Manager, Americas Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quick question on innodb_log_file_size
Jenny Chen wrote:
Hi,
According to the reference manual, it was said that the combined lnnodb log
file size is less than 4G on 32-bit system. But I'm running on my 64-bit
solaris, I still got the error complaining the innodb log file 4G for my
64-bit MySQL. So I'm wondering is this 4G limit apply on 64-bit system as
well? Why?
Yes.
[EMAIL PROTECTED]:~/mysql/mysql-5.1/storage] $ grep -irn
innobase_log_file_size ./*
./innobase/handler/ha_innodb.cc:105:static long long
innobase_buffer_pool_size, innobase_log_file_size;
./innobase/handler/ha_innodb.cc:1466: if
(innobase_log_file_size UINT_MAX32) {
./innobase/handler/ha_innodb.cc:1468:
innobase_log_file_size can't be over 4GB
./innobase/handler/ha_innodb.cc:1580: srv_log_file_size = (ulint)
innobase_log_file_size;
./innobase/handler/ha_innodb.cc:8106:static
MYSQL_SYSVAR_LONGLONG(log_file_size, innobase_log_file_size,
The docs are wrong - I'll have that updated.
Best regards
Mark
--
Mark Leith
MySQL Regional Support Manager, Americas
Sun Microsystems, Inc., http://www.sun.com/mysql/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Jesse wrote: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. They simply don't 'exist' at that point in time. All of the rows are read at one point in time (according to a WHERE clause if applicable), then the expressions within the column lists are done, then grouping, order by etc., and finally HAVING clauses are done (where one can use a column alias, when not available within a WHERE clause). However, you should be able to use them in an ORDER BY - as they do exist at that point - so I'm not sure that issue would have been related to this. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Juan Eduardo Moreno wrote: Hi, I'm experience using expire_log_days and don't work. I set this parameters in the CNF and when the time of ( for example 5 days) is in, don't delete anything. On my expirience, this parameters don't work ( 5.0.27). I am testing this now (on 5.0.40) and will see how it works out. Cheers! Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Mark Leith wrote: Juan Eduardo Moreno wrote: Hi, I'm experience using expire_log_days and don't work. I set this parameters in the CNF and when the time of ( for example 5 days) is in, don't delete anything. On my expirience, this parameters don't work ( 5.0.27). I am testing this now (on 5.0.40) and will see how it works out. OK initial testing of this has shown no problems with the rolling over that happens when the server starts/stops: medusa:/usr/local/mysql/data root# ls -l total 41024 -rw-rw1 mysql wheel 5242880 May 5 22:34 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 10485760 May 5 22:34 ibdata1 -rw-rw1 mysql wheel 117 May 5 22:33 medusa-bin.02 -rw-rw1 mysql wheel 117 May 5 22:34 medusa-bin.03 -rw-rw1 mysql wheel 117 May 5 22:34 medusa-bin.04 -rw-rw1 mysql wheel98 May 5 22:34 medusa-bin.05 -rw-rw1 mysql wheel 160 May 5 22:34 medusa-bin.index -rw-rw1 mysql wheel 6051 May 5 22:34 medusa.err -rw-rw1 mysql wheel 5 May 5 22:34 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---2 mysql wheel68 Apr 21 06:09 test medusa:/usr/local/mysql/data root# mysqladmin -u root shutdown medusa:/usr/local/mysql/data root# date Sat May 5 22:35:07 BST 2007 medusa:/usr/local/mysql/data root# date Wed May 9 22:35:24 BST 2007 medusa:/usr/local/mysql/data root# ../bin/mysqld --user=mysql [1] 1867 medusa:/usr/local/mysql/data root# 070509 22:35:53 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql-enterprise-gpl-5.0.40-osx10.4-i686/data/ is case insensitive 070509 22:35:53 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=/usr/local/mysql-enterprise-gpl-5.0.40-osx10.4-i686/data/medusa-bin' to avoid this problem. 070509 22:35:53 InnoDB: Started; log sequence number 0 43655 070509 22:35:54 [Note] ../bin/mysqld: ready for connections. Version: '5.0.40-enterprise-gpl-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Enterprise Server (GPL) medusa:/usr/local/mysql/data root# ls -l total 41000 -rw-rw1 mysql wheel 5242880 May 9 22:35 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 10485760 May 5 22:35 ibdata1 -rw-rw1 mysql wheel98 May 9 22:35 medusa-bin.06 -rw-rw1 mysql wheel75 May 9 22:35 medusa-bin.index -rw-rw1 mysql wheel 6341 May 5 22:35 medusa.err -rw-rw1 mysql wheel 5 May 9 22:35 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---2 mysql wheel68 Apr 21 06:09 test The only other one to test now is test that this also happens when a binary log rolls over. Do keep in mind that expire_logs_days only gets triggered at a) server start up b) the time a binary log has to roll over. If your binary logs do not roll over for quite a period of time (i.e are lower load systems) that still stay up for long periods - you might not see a log expired for some period. Anyway, I'll still check out binary log rollover as well.. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Paul DuBois wrote: At 8:46 PM -0400 5/2/07, Baron Schwartz wrote: Ofer Inbar wrote: That's a good point, though probably a minor one: At most you would end up with one binary logfile that's old and not deleted. As soon as you create a new one, that one would be deleted (if this feature works). In our case, we flush logs nightly. (but hardly ever restart mysqld) -- Cos We roll many logs every day, but never restart unless we have to. So for us, it looked like it genuinely wasn't working on roll; I have no idea about restart. I have a 4.1.13 server that's been up for 100 days. It has expire_logs_days, and I have 7 binlog files. I do flush my logs once a day to force the logs to rotate. So that's one confirmation that it works, at least in 4.1.13. :-) This seems to work just fine on 5.0.40 as well: medusa:/usr/local/mysql/data root# ls -l total 58352 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102514 May 3 10:55 medusa-bin.01 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.02 -rw-rw1 mysql wheel102517 May 3 10:55 medusa-bin.03 -rw-rw1 mysql wheel102517 May 3 10:56 medusa-bin.04 -rw-rw1 mysql wheel 81473 May 3 10:56 medusa-bin.05 -rw-rw1 mysql wheel 375 May 3 10:56 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test medusa:/usr/local/mysql/data root# cat /etc/my.cnf [mysqld] log-bin max_binlog_size = 100K expire_logs_days = 2 medusa:/usr/local/mysql/data root# date Thu May 3 10:58:22 BST 2007 medusa:/usr/local/mysql/data root# date Sun May 6 10:58:42 BST 2007 medusa:/usr/local/mysql/data root# while [ 1 ] do mysql -u root test -e 'insert into binlog_test (i,j) values (1,1)' done ^C medusa:/usr/local/mysql/data root# ls -l total 57888 -rw-rw1 mysql wheel 5242880 May 3 10:49 ib_logfile0 -rw-rw1 mysql wheel 5242880 May 2 22:27 ib_logfile1 -rw-rw1 mysql wheel 18874368 May 3 10:47 ibdata1 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.05 -rw-rw1 mysql wheel102517 May 6 10:59 medusa-bin.06 -rw-rw1 mysql wheel 55853 May 6 10:59 medusa-bin.07 -rw-rw1 mysql wheel 225 May 6 10:59 medusa-bin.index -rw-rw1 mysql wheel 5 May 3 10:49 medusa.pid drwxr-x--- 53 mysql wheel 1802 May 2 22:26 mysql drwxr-x---9 mysql wheel 306 May 3 10:52 test I declare 'No Bug Here' :) At least on the current versions of 5.0 (tested on 5.0.40), anyway. Cheers! Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Baron Schwartz wrote: I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 OK even with a slave connected to a master with expire_logs_days, I still see the desired affect. I've made a note on the bug - let's continue discussion on there? Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Baron Schwartz wrote: Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. It looks like there was a 5.1 installation here at some point - 5.0.37 should not include the general_log or slow_log tables - these are 5.1 new features. I suspect that you likely have general_log and slow_log frm files within the mysql database directory, without any corresponding data files. If this is the case then just 'rm' the general_log.frm and slow_log.frm files from the mysql database directory, and all these errors should go away. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Greater function ?
[EMAIL PROTECTED] wrote: Hi all, I'm looking for the mysql equivalent to oracle Greater function. In fact, I want to order by the greater of two timestamp fields order by greater(last_conneciton_date, insert_date) Anybody knows the way to do it in Mysql ? GREATEST() - and it's opposite LEAST().. Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql monitering and performance tool
Maxim Veksler wrote: On 7/26/07, Baron Schwartz [EMAIL PROTECTED] wrote: krishna chandra prajapati wrote: Hi Everybody, I am looking for a tool which can moniter mysql and if any thing goes wrong with mysql then it can send a mail or alert. It can show the innodb status and all the complete details about mysql. Regards, Krishna I don't think a single tool exists that can do all this. However, you can use innotop to monitor servers and then use another system, such as Nagios, to send alerts. I believe innotop is the only tool in existence that can parse InnoDB status output. Actually the tool that we provide to Enterprise customers can do both of these (parse InnoDB status output and send emails with alerts) :) You can see more about it here: http://www.mysql.com/products/enterprise/advisors.html However as yet it will not print the full output (transactions and all) to the monitor screen (it just monitors for key statistics and alerts when necessary). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql monitering and performance tool
Baron Schwartz wrote: I stand corrected. I don't know why I didn't think of this! So you guys had to go the route of parsing InnoDB status too, huh? Fun, isn't it! Indeed, it is a rather interesting thing to do ;) Made even better when it is limited to 64K and truncated with large transactions! Google did some smart stuff altering this (moving the 'dynamic' content such as deadlocks, transactions etc.) to the end of the output, and increasing the limit to 128K, however I'm not sure whether we will see that in the server soon. You can see examples here: http://dammit.lt/2007/06/23/mysql-40-google-edition/ I have seen that the InnoDB developers are looking at moving some of the output over to INFORMATION_SCHEMA tables as well (they mentioned this on the internals@ list), so hopefully this should all become a little easier in the not too distant future! Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql monitering and performance tool
Baron Schwartz wrote: Mark Leith wrote: Baron Schwartz wrote: I stand corrected. I don't know why I didn't think of this! So you guys had to go the route of parsing InnoDB status too, huh? Fun, isn't it! Indeed, it is a rather interesting thing to do ;) Made even better when it is limited to 64K and truncated with large transactions! Or a big deadlock with tons of tuples. I have submitted a feature request to remove the list of locks held, but I don't know how high a priority it's given. It's useless for non-developers, though Heikki told me he uses it every day. I wouldn't think it'd be hard to define a compile-time option whether to include all the tuples locked, so Heikki and Marko could see it but spare the rest of us :) Of course seeing just the first part of that output, which shows what kind of lock was held on which index, is very useful for non-InnoDB-developers too. Have you filed a feature request on this? I would agree that the list of locks is certainly not of use to the average DBA, so removing this by default would be a good idea. Google did some smart stuff altering this (moving the 'dynamic' content such as deadlocks, transactions etc.) to the end of the output, and increasing the limit to 128K, however I'm not sure whether we will see that in the server soon. You can see examples here: http://dammit.lt/2007/06/23/mysql-40-google-edition/ Yes, Mark told me about that, and I thought it was a great idea. I don't know why we shouldn't see that in the server soon. It's not a hard thing to do, just a few lines changed, right? It seems kind of obvious that it's better to put the potentially huge deadlock output after the 'important' stuff. Sure it's an easy change to make in the code, but it could have repercussions for all those monitoring tools/scripts out there already that parse this stuff ;) I have seen that the InnoDB developers are looking at moving some of the output over to INFORMATION_SCHEMA tables as well (they mentioned this on the internals@ list), so hopefully this should all become a little easier in the not too distant future! Yep. But there's always that legacy support you have to keep around! Exactly! ;) If 'you' have parsed the output in a sane manner, many tools should be unaffected, however, we can not rely on that, and changing innodb status could break a lot of scripts already made out there ;) This is why we have to be very careful and considered in making these kinds of changes. Of course, the burden is also on the InnoDB developers to do this. We are also pretty committed within *MySQL* to maintain both SHOW and INFORMATION_SCHEMA tables concurrently, I'm pretty sure the InnoDB developers will be of the same mind as well (though I/'we' certainly do not speak for them). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Ends Enterprise Server Source Tarballs
Eric Bergen wrote: It's nothing to be concerned about because the source tar balls and binaries are being mirrored at http://mirror.provenscaling.com/mysql/ -Eric On 8/10/07, Daevid Vincent [EMAIL PROTECTED] wrote: Is this anything to be concerned about? We are Enterprise customers. We distribute mySQL on our appliance that we sell. It doesn't seem like we should worry, now. But I'm a little nervous about the future? http://linux.slashdot.org/article.pl?sid=07/08/09/2047231 http://linux.slashdot.org/article.pl?sid=07/08/09/2047231from=rss from=rss http://www.linux.com/feature/118489 http://www.linux.com/feature/118489 There is nothing to worry about for Daevid anyway, as he is an Enterprise customer - and hence will still have direct access to the tar balls from the source anyway, via enterprise.mysql.com.. We will not be removing these source tar balls completely, only from public access via our FTP. Enterprise customers still have (and will always have) access to the source tar balls, we have no plans of changing this at all. In any case, distributing MySQL on an appliance that is being sold would require commercial binaries, not GPL binaries, as I understand. Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
Michael Dykman wrote: As most 64 bit libraries declare their 64-bitness clearly int heir names, I think that there is a very high chance that you have built yourself a 32-bit database in which case 4G is the limit of the known universe. On 8/14/07, Gu Lei(Tech) [EMAIL PROTECTED] wrote: try: uname -a to see if your OS is 64bit or not. Regardless of whether it is a 64bit or 32bit OS, if the binary is built as 32bit, it will still be limited. 'file mysqld' will tell you whether it is 32bit or 64bit. And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
Baron Schwartz wrote: Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: run out of memory
Baron Schwartz wrote: Mark Leith wrote: And in practice, a 32bit binary is actually limited to around ~2.5-2.7G, rather than a full 4G. What are the practical memory limits for 64-bit binaries? I have heard that MySQL's indexing code is only 32-bit safe anyway, and I assume for example the MyISAM key buffers can still only be 4 GiB in a 64-bit version. Is this true of all storage engines? Are there any other gotchas trying to use lots of memory in 64-bit systems? There are a couple of things to beware of 64bit binaries - the main being buffer management.. The larger the buffer pools you have, the greater the risk of having buffer pool management operations taking longer and longer, and locking out operations. Some good examples of this are having a large query cache (see http://bugs.mysql.com/bug.php?id=21074, patch pending and in progress), and large InnoDB buffer pools, with some high load against the adaptive hash index (which has only recently become an issue since InnoDB have improved concurrency within the engine really) see http://bugs.mysql.com/bug.php?id=20358 - which is only showing itself on multi core 64bit machines, and is proving itself to be very hard to track down and reproduce. Of course, InnoDB also has to manage it's buffer pool over and above the adaptive hash index as well, and can show hanging in various other operations as well, such as large checkpointing or insert buffer merging operations. Playing around with innodb_max_dirty_pages_pct etc. can help with this also. With regards to the MyISAM key buffer - yes this is only safe up to 4G right now - even on 64bit - as well as a number of the other thread based variables (sort buffer, read buffer, join buffer etc.). Of course, most sane people would not set these thread variables that high, but we did not limit them, and some people *did* in fact try to set them very high! :) See: http://bugs.mysql.com/bug.php?id=5731 http://bugs.mysql.com/bug.php?id=29419 http://bugs.mysql.com/bug.php?id=29446 etc. However, this is per key buffer as well - one can create multiple key buffers, and assign indexes to be loaded in to each, to work around this issue with MyISAM. I'm not sure where the comment on indexing code only being 32bit safe comes from, maybe it is due to the limitation of the key buffer? I know of people that have pushed the InnoDB buffer up to 32G, and it hums along just fine, you just have to make sure that you do not get caught in huge flushing operations (keep the dirty pages low, try not to do too many huge insert operations all in a big batch at the same time etc.). Cheers, Mark -- Mark Leith, Senior Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring and analysis tool
Daniel Caune wrote: Hi, Our MySQL server used for our development environment is slowing down, certainly because of a massive query execution by different processes. We are trying to determine which kind of query is the most executed and on which database instance(s). We are quite novice in administrating MySQL. We imagine that we can configure MySQL so that it logs every queries executed in its queries.log file. However is there any analysis tool that would import this log file and that would generate a complete report providing the number of similar queries (same DML order on the same table) per databases per minute? P.S.: we are using mytop and innotop, but it seems that they don't support such a feature. We see a lot of different queries executed against MySQL but we can't figure out, which kind of queries is the most executed, on which database. Turn on the slow query log (log_slow_queries), set long_query_time to 1 (second), and then use the mysqldumpslow tool to aggregate all of the slow queries: [EMAIL PROTECTED]:~/mysql/mysql-5.0-bk] $ mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verboseverbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html Regards Mark -- Mark Leith, Manager of Support, Americas MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Monitoring and analysis tool
Baron Schwartz wrote: Correct. But if you are willing to patch your server, you can: http://www.mysqlperformanceblog.com/2007/10/31/new-patch-for-mysql-performance/ This is in 5.1 as well now :) Regards Mark -- Mark Leith, Manager of Support, Americas MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sun and mysql
Olaf Stein wrote: I am not judging this move by mysql, as long as it stays open source, this is probably good for the product itself. I just think it is weird that a pure entertainment website (admittedly with lots of users) is estimated at so much more than a software company. Two words: Marketing Potential. :) -- Mark Leith, Manager of Support, Americas MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forcing leading 0 for numeric fields
Jonathan Mangin wrote: - Original Message - From: George Law [EMAIL PROTECTED] To: MYSQL General List mysql@lists.mysql.com Sent: Wednesday, August 09, 2006 3:40 PM Subject: forcing leading 0 for numeric fields Hello All, I have what is probably a simple question. I have a table of phone numbers, broken into npa,nxx,station So, 8001231234npa =800 nxx=123 station=1234 Some queries pull data from this table in the format: select * from table where concat(npa,nxx,station)=8001231234 That is all good. The problem I ran into today is where the station column is 1000, ie 8001230123 station =0123 which gets stored as 123 by mysql Is there a quick and easy way to force station to 4 digits when I do the query select * from table where concat(npa,nxx,station)=8001230123 This query does not work, butselect * from table where concat(npa,nxx,station)=800123123 Store them as INT with ZEROFILL: mysql CREATE TABLE zeros (i INT(4) ZEROFILL, j INT(4) ZEROFILL, k INT(4) ZEROFILL); Query OK, 0 rows affected (0.09 sec) mysql INSERT INTO zeros VALUES (23,3244,0123); Query OK, 1 row affected (0.01 sec) mysql select * from zeros; +--+--+--+ | i| j| k| +--+--+--+ | 0023 | 3244 | 0123 | +--+--+--+ 1 row in set (0.08 sec) Make sure you specify the length of the digits that you would like padded to within the INT specification. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on before insert trigger
Hi Patrick,
Patrick Aljord wrote:
I would like to prohibit the value 'xxx' on my column title, and if it
does contain the value I would like to create an exception by
assigning 'xxx' to the primary key id which is int(5).
This is what I do but I get an error on its creation so I guess it's
not the right way:
CREATE TRIGGER testref BEFORE INSERT ON bookmarks
FOR EACH ROW
BEGIN
if NEW.title like '%xxx%'
set NEW.id='xxx';
END;
the error:
server version for the right syntax to use near ':
set NEW.id='xxx' at line 4
You have your IF syntax a little wrong, try this:
CREATE TRIGGER testref BEFORE INSERT ON bookmarks
FOR EACH ROW
BEGIN
IF NEW.title LIKE '%xxx%' THEN
SET NEW.id ='xxx';
END IF;
END;
//
mysql INSERT INTO bookmarks values ('two', 'hawt xxx sex')//
Query OK, 1 row affected (0.00 sec)
mysql SELECT * FROM bookmarks//
+--+--+
| id | title|
+--+--+
| one | one bookmark |
| xxx | hawt xxx sex |
+--+--+
2 rows in set (0.27 sec)
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i drop tables with the same predix by using only one single statement?
Hey
方外 醉月 wrote:
How can i do if i want to drop tables with the same predix?
For example, there is a database including a lot of tables,such as
tableA,tableB(table*)
Now,how can i drop those tables by using only one single statement?
_
与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn
If you are using 5.0:
DELIMITER //
DROP PROCEDURE drop_table_prefix//
CREATE PROCEDURE drop_table_prefix(IN dbs VARCHAR(64), IN pref VARCHAR(63))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE t_name VARCHAR(64);
DECLARE cur1 CURSOR FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = dbs
AND TABLE_NAME LIKE CONCAT(pref,'%');
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO t_name;
IF NOT done THEN
SET @qry = CONCAT('DROP TABLE ', t_name);
PREPARE stmt FROM @qry;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END;
//
An example:
mysql SHOW TABLES;
++
| Tables_in_test |
++
| a1 |
| a2 |
| t1 |
| t2 |
++
4 rows in set (0.00 sec)
mysql CALL drop_table_prefix('test', 't')//
Query OK, 0 rows affected (0.01 sec)
mysql SHOW TABLES//
++
| Tables_in_test |
++
| a1 |
| a2 |
++
2 rows in set (0.00 sec)
Hope this helps.
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
Ed Curtis wrote: I'm having some trouble setting a future date within a table. I have one column 'this_date' which is a DATE field and I'm trying to add 90 days to it and set a column named 'future_date', also a DATE field. I don't know if the problem is that I'm trying to write the value into the 'this_date' and 'future_date' fields in the same query. UPDATE this_table SET this_date = $this_date, future_date = (DATE_ADD(this_date) INTERVAL 90 DAY) Would this work? Thanks, Ed UPDATE this_table SET this_date = NOW(), future_date = NOW() + INTERVAL 90 DAY; This is probably along the lines of what you want.. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date_add function
Ed Curtis wrote: UPDATE this_table SET this_date = NOW(), future_date = NOW() + INTERVAL 90 DAY; This is probably along the lines of what you want.. Actually I'm setting the DATE via drop down menus using PHP and creating the date by hand via variables. NOW() won't work in this instance. UPDATE this_table SET this_date = '$this_date', future_date = '$this_date' + INTERVAL 90 DAY; Same thing still applies really.. And quote your dates... Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC,office='Dir' DESC; Not many people know that you can order by literals as well ;) Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Dan Nelson wrote: --snip-- More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); Oops. I only read the replies and not the original post. Assuming there are many other values for the office field, you might want SELECT * FROM team WHERE office = 'Exec' UNION SELECT * FROM team WHERE office = 'VP' UNION SELECT * FROM team WHERE office = 'Dir'; Indeed! :) Especially if the table is of any decent size (I assumed that it was not *huge*). The UNION will give index accesses, the tricks on the ORDER BY will cause filesorts (although, they will still likely be in memory unless you the table is large, again). You should of course EXPLAIN and tune accordingly (with response times as well). Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext problem
devy wrote:
---cut---
-
insert into ft_test (field1,field2,field3)
VALUES('mysql full text', 'this is a test', 'mysql fulltext');
-
the problem is that when I execute this query I always get 0 as
relevance:
---cut---
A FULLTEXT search will not match return values that are within 50% of
the rows (or, index them) - this would just lead to returning too many
hits and make relevance not-so-relevant ;)
As you are only inserting one row, the this is certainly 50% of the
rows ;)
Add more rows, then re-run your test, keeping in mind the above.
Cheers,
Mark
--
Mark Leith, Support Engineer
MySQL AB, Worcester, England, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Crashing Error - Assertion failed: fixed == 1, file item.h, line 1601
Hi Jason, Jason J. W. Williams wrote: Hello, We've been getting random crashes on our MySQL servers running MyISAM tables for the last month, its gotten very bad in the last two weeks. This has occurred on both 5.0.27, 5.1.11 and 5.1.15-nightly20070103. It crashes the tables with high queries per second. We've fixed the issue on one of the servers by changing its tables to InnoDB. We can't do that however on another server, which we turned debugging on instead. It appears to be an assertion failure, the error message from the MySQL debugging code is: Assertion failed: fixed == 1, file item.h, line 1601 Any help is greatly appreciated. Should we report this as a bug? Any crashing is most certainly a bug, so if you could gather as much information on this as possible and report a bug that would be great. Please include: o The full section of the error log for the time of the crash o If there is a stacktrace reported, the resolved trace following: o http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html o If you could turn on core files and upload the core file, and mysqld binary used to create it, as tar.gz to: o ftp://ftp.mysql.com/pub/mysql/upload o Link this in the bug report as well Do you have any way to reproduce this as yet? Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need SUPER Privilidge for UPDATE?
Hi Jesse wrote: I have an ASP.NET app where I'm doing an update, and the user name that I'm using has the access to do an update. When I execute this command, I get the error, #42000Access denied; you need the SUPER privilege for this operation. Following is my query: UPDATE Families SET LastName='a',FathersFirstName='a',MothersFirstName='a',Address1='a', Address2='',City='a',State='FL',Zip='a', Donation=0,HomePhone='a',FathersWorkPhone='',MothersWorkPhone='', FathersCell='',MothersCell='',EMail='[EMAIL PROTECTED]', UserName='a',Password='a' WHERE ID=157 I thought that the Password field might be an issue, but I've changed it to `password`, and that didn't make any difference. Neither did Families.Password, or Families.`Password`. Any ideas why I would be getting this error? This same exact query works fine on another database with the same structure and every thing. Sounds like you have triggers on the table, see the DEFINER clause within this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Triggers within 5.0 require the user (within the DEFINER clause, or executing the statement against the table when using CURRENT_USER) to have the SUPER privilege. Within 5.1 this moves to the TRIGGER privilege. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alias a function result?
Jerry Schwartz wrote: I know that you can alias a function result as easily as anything else, but I've run into a problem with an application I inherited. For reasons too murky to go into, I would like to refer to the result of a group function as the name of the argument of the function. Here's an example of what I mean: SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY book_author WHERE book_title LIKE something; This actually seems to work, but it makes me ill to look at it. Is this legal, or have I found a loophole that might be closed in the future? Perfectly legal, will not get changed. :) Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alias a function result?
Felix Geerinckx wrote: [EMAIL PROTECTED] (Jerry Schwartz) wrote in news:[EMAIL PROTECTED]: SELECT book_author, GROUP_CONCAT(book_title) AS book_title GROUP BY book_author WHERE book_title LIKE something; This actually seems to work, but it makes me ill to look at it. I doubt this works: there is no FROM clause, and the WHERE and GROUP BY clauses are in the wrong order. The following will work: SELECT book_author, GROUP_CONCAT(book_title) AS book_title FROM foo WHERE book_title LIKE 'SOMETHING' GROUP BY book_author but the book_title in the WHERE clause is *not* the alias but the individual column. Results of an aggregate function are *never* available in a WHERE clause. You need a HAVING clause. Ahh indeed, I was only looking at the aliasing of the group concat function. :) Teach me for skimming emails! Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure?
I think a much better example of store procedures is one showing actual
procedural processing. Whilst they also allow a lot less communication
over the network, and a lot less SQL knowledge for the user(!) - a
stored procedure is also a way of doing all the work that you currently
get the client to do (thinking things like PHP with IF, WHILE or LOOP
statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql -
then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error
( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message along
with the substitued username to be used as the message body. It will
then generate an application error to the user with another procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
Best regards
Mark
Mark Leith
Cool-Tools UK Limited
http://www.cool-tools.co.uk
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Wolfram Kraus
Sent: 30 November 2004 12:58
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
Michael J. Pawlowsky wrote:
Because you will be downloading 500,000 rows... And I don't really
think that was the point. Who cares what the example is. Personally I
was quite impressed with great explanation.
I second that it was a great explanation! Please don't get me wrong! And
furthermore I will only download one row two times: select count - one
row select ... limit count/2, 1 - one row
I would have simply said a chunk of code that runs on the server that
the client can call. And this guy took the time to put together a
really good reply with an example.
I don't say that the example is bad, I only said that in MySQL you can
do this without a SP.
Mike
Wolfram
Wolfram Kraus wrote:
[...]
Suppose you have a table with a million records, test scores from a
widely taken exam for example. You need to find the median mark
- NOT the average! - so your algorithm needs to read all million
records, sort them into ascending or descending sequence by the test
score, then read exactly half way through the sequenced list to find
the middle record; that's the one that contains the median score.
If that work were to happen on the client, the client would have to
fetch a million records, sort them all, then read through half of
those records until it found the middle record; then it would report
on the median mark. There would clearly be a lot of network traffic
involved in getting all those records to the client, sorting them
and then reading through the sorted records.
[...] Pardon my ignorance, but why can't you do this (in MySQL) with
a select count ... and afterwards a select ... order by... LIMIT?
All the work is done on the server, too. No need for a SP here.
Wolfram
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure?
I just wish that MySQL was a year or two further along than it is so
that 5.x would be fairly mature and we could actually start coding
stored procedures, views, etc
Oh indeed, neither can I! Not just procedures and views either, but also
triggers and sequences! And a job scheduling system would be fantastic
as well!!
Mark dreaming away the day
-Original Message-
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: 30 November 2004 16:04
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Stored Procedure?
- Original Message -
From: Mark Leith [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 30, 2004 10:47 AM
Subject: RE: Stored Procedure?
I think a much better example of store procedures is one showing
actual procedural processing. Whilst they also allow a lot less
communication over the network, and a lot less SQL knowledge for the
user(!) - a stored procedure is also a way of doing all the work
that you currently get the client to do (thinking things like PHP
with IF, WHILE or LOOP statements).
So, if you have a long transaction that does multiple round trips from
the client - which could be your web server), whilst looping through
the results in the client code and doing something else with mysql
- then a stored procedure is where it's at.
Here's a fairly easy example of one I use in Oracle, that allows an
admin user to reset the passwords for only a specific set of users:
CREATE PROCEDURE reset_password
(rp_username in varchar2,
rp_password in varchar2)
as
begin
if ( USER = 'ADMIN' and rp_username in
('GERRY', 'LYNNE', 'KRIS', 'STEPH') )
then
execute immediate
'alter user ' || rp_username ||
' identified by ' || rp_password;
else
send_admin_mail
('Admin trying to change password for ' || rp_username );
raise_application_error ( -20001, 'Change not Authorised' );
end if;
end;
The above will ensure that only the ADMIN user can execute the change
(enforcing extra security). It will then check that the user being
changed is within the given list, and if it is - it will run the
password change script (alter user LYNNE identified by 'password';).
However, if any of the aforementioned check fail, it will first of all
execute another procedure (send_admin_email), passing the message
along with the substitued username to be used as the message body. It
will then generate an application error to the user with another
procedure.
Don't get me wrong Rhino, your example was great, and is still very
applicable.. I just thought this might be a slightly more insightful
example of what SP's can really handle.
No offense taken, Mark. You've simply given a more advanced example that
illustrates even more capabilities of a stored procedure. It's a great
supplemental example.
I was simply citing the 'classic' example that I've taught in DB2
courses. Those courses were for people who were new to stored procedures
(and many other aspects of DB2) and needed to know the basic concepts
before trying to write one. That seemed to be the level of the person
who asked the original question. However, your example illustrates how
much more a stored procedure can do so it should certainly be strongly
considered by all developers, particularly advanced ones.
I just wish that MySQL was a year or two further along than it is so
that 5.x would be fairly mature and we could actually start coding
stored procedures, views, etc
Rhino
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.3 - Release Date: 26/11/2004
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cannot create Windows service for MySql. Error: 0
I usually find that this means there is already a MySQL service defined - you can verify this in Start/Control Panel/Administrative Tools/Services. If there is a service that exists, go to your current \mysql\bin directory within a command prompt and issue mysqld-nt --remove which will delete the current installed service. If you can't get to this (i.e the install dir has been removed) - with the new installer you can use a different name for the service, such as MYSQL41. HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 06 January 2005 08:31 To: mysql@lists.mysql.com Subject: Re: Cannot create Windows service for MySql. Error: 0 Hello. While I am trying to install MySql Server 4.1, What exact version do you install? Use 4.1.8. Did you stop the previous instance of MySQL (if you have such one)? Please close all the executable files and the applications that uses MySQL's dlls. Can you start MySQL from Windows Command Line? Eugenia Mariani [EMAIL PROTECTED] wrote: My O.S in Win Xp Pro SP2. My web server is Apache 5.0 While I am trying to install MySql Server 4.1, I have the following error and I cannot install the Server: Cannot create Windows service for MySql. Error: 0 Can someone help me to install without error? Thanks Eugenia Mariani _ Ricerche online pi? semplici e veloci con MSN Toolbar! http://toolbar.msn.it/ -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.8 - Release Date: 03/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.6.8 - Release Date: 03/01/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: InnoDB transaction and table changes
Simple, ROLLBACK reverts DML (data) changes, not DDL (structure) changes.. mysql select version(); +---+ | version() | +---+ | 4.1.7-nt | +---+ 1 row in set (0.01 sec) mysql SHOW VARIABLES LIKE have_innodb; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | YES | +---+---+ 1 row in set (0.00 sec) mysql use test; Database changed mysql SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql DROP TABLE IF EXISTS foo; Query OK, 0 rows affected (0.03 sec) mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE foo (bar int) TYPE=InnoDB; Query OK, 0 rows affected, 1 warning (0.17 sec) mysql INSERT INTO foo VALUES (100); Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM foo; +--+ | bar | +--+ | 100 | +--+ 1 row in set (0.00 sec) mysql ROLLBACK; Query OK, 0 rows affected (0.04 sec) mysql SELECT * FROM foo; Empty set (0.00 sec) mysql SHOW CREATE TABLE foo; +---+--- ---+ | Table | Create Table | +---+--- ---+ | foo | CREATE TABLE `foo` ( `bar` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+--- ---+ 1 row in set (0.00 sec) See: http://dev.mysql.com/doc/mysql/en/cannot-roll-back.html HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Ville Karjalainen [mailto:[EMAIL PROTECTED] Sent: 04 February 2005 13:59 To: mysql@lists.mysql.com Subject: Question: InnoDB transaction and table changes Greetings, I created a table during transaction and was surprised to find out it still existed after I did a ROLLBACK. The same seems to apply to changes made using ALTER TABLE statements. Is there a simple logical explanation to this behaviour? Any help would be appreciated. Demonstration follows: mysql SELECT VERSION(); ++ | VERSION() | ++ | 4.1.7-Debian_4-log | ++ 1 row in set (0.01 sec) mysql SHOW VARIABLES LIKE have_innodb; +---+---+ | Variable_name | Value | +---+---+ | have_innodb | YES | +---+---+ 1 row in set (0.00 sec) mysql SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql DROP TABLE IF EXISTS foo; Query OK, 0 rows affected (0.19 sec) mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE foo (bar int) TYPE=InnoDB; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql SHOW CREATE TABLE foo; ++-- ---+ | Table | Create Table | ++-- ---+ | foo | CREATE TABLE `foo` ( `bar` int(11) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++-- ---+ 1 row in set (0.00 sec) -- Ville Karjalainen - [EMAIL PROTECTED] Toiminto Media ky - [EMAIL PROTECTED] - http://toiminto.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 03/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: auto-increment stops at 127
Change the column from a TINYINT (which has a maximum value of 127), see here: http://dev.mysql.com/doc/mysql/en/numeric-types.html HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: 14 February 2005 13:55 To: mysql@lists.mysql.com Subject: auto-increment stops at 127 additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated.. _ 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] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.7 - Release Date: 10/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: referencial integrity problem
Foreign keys are only supported within InnoDB tables (on both sides).. Mark Mark Leith Cool-Tools http://www.cool-tools.co.uk -Original Message- From: Philipp Snizek [mailto:[EMAIL PROTECTED] Sent: 22 February 2005 10:30 To: Mysql List (E-mail) Subject: referencial integrity problem Hi I run a Postfix MTA attached to a mysql DB with various domains on it. A domain consists of email addresses. When I want to delete the domain the referenced email addresses should be deleted, too. But that doesn't work and I don't know why. here are the two tables domains and users: CREATE TABLE domains ( ID_DOMAINS int(11) auto_increment, active int(1) not null, domain varchar(50) NOT NULL, PRIMARY KEY (ID_DOMAINS) ) TYPE=MyISAM; create table users ( email varchar (80) primary key unique not null, belongs_to integer not null, foreign key (belongs_to) references domains on delete cascade ); if I use the delete command like delete from domains where id_domains='1' the dataset that belongs to id 1 in domains is deleted while the email addresses belonging to this domain are left untouched. What am I missing? thanks Philipp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.3.0 - Release Date: 21/02/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 266.3.0 - Release Date: 21/02/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 5.03 Still crashes on win32
There's no real official word on the 5.0.2 crashes on win32, although I believe there were a number of bug reports. A lot of the crashing that I have come across when using 5.0.2 on win32 has to do with the informational functions - DESCRIBE, SHOW etc. There were also a number of crashes that I came across when trying views, but these seemed to be related the view algorithms, as with certain functions (inserting to a view etc.) the algorithm was supposed to be dynamic as I understand, but that functionality hadn't been fully written. Creating views on a join of a table and another view, then selecting from it also caused crashing. On speaking with one of the support guys, he mentioned that pretty much all of this had been fixed on win32 within 5.0.3 however. Frederick - do you have reproducible test cases for the crashes? Filing them over at bugs.mysql.com would be helpful if you do.. Cheers, Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: 29 March 2005 10:39 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: 5.03 Still crashes on win32 Warning, do not install 5.03 it still crashes on win32. This occured after very few minutes of testing. I will send more info as I locate it. Great... Is there any official word on the 5.0.2 crashes? Have there been fixes regarding this issue? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27/03/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.4 - Release Date: 27/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to run a file in MySQL
Hi Joppe, Use SOURCE or \. to execute the filename: mysql SOURCE E:\SQL\MySQL\test_tables.sql Database changed ++ | Tables_in_test | ++ | a | | academies | . HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: Joppe A [mailto:[EMAIL PROTECTED] Sent: 04 April 2005 11:59 To: mysql@lists.mysql.com Subject: how to run a file in MySQL Hello all, This is probably really basic for all of you but I have been trying to find it in the manual without success... My question is if it is possible when you are logged in to MySQL to run a file with sql-statements in, instead of sit and execute each statement seperatly. The file I have is a to clean up my DB and to erase data that I don#t want to have, som all rows in the file is normal DELETE-statetments. Thanks in advance! /Joppe -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Maximize mysql ini for 4gig big query win 2003 box
You mention that MySQL is only using 1.5 gig.. Out of interest, are you aware that Windows by default when using large amounts of memory, will split the memory allocation down the middle - between applications and system? So given your 4Gb of memory, applications with be getting 2Gb.. You can read more about this here: http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx Basically, add a \3GB switch to your boot.ini file. Then you can start look at tuning the memory on MySQL as well.. HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: matt_lists [mailto:[EMAIL PROTECTED] Sent: 28 April 2005 13:57 To: Gleb Paharenko Cc: mysql@lists.mysql.com Subject: Re: Maximize mysql ini for 4gig big query win 2003 box I'm more concerned with mysql only using 1.5 gig, wouldnt it be faster to maximize memory usage? The only thing these box's do is mysql I do have slow queries, but they are big joins across all the big tables, and users doing querys on tables without using a key, the contract requires dynamic query on any fields, and the tables are too big to index every possible key combination they would use (30-40 columns on some) So far, I've got no major issues, aside from big queries and there's no way around that, unless mysql changes on how it combines indexes, ie it does not combine them currently. Matt Gleb Paharenko wrote: Hello. SHOW STATUS and a piece of SHOW PROCESSLIST with slow queries could give additional information, so the probability of the helpful answer grows. matt_lists [EMAIL PROTECTED] wrote: Anybody have any suggested settings for our my.ini ? 4 gig box, running windows 2003 peak memory usage for mysql is 1.4 gig currently using myisam files, lots of little index querys, and some very large queries at night 615 tables, 88.3 gb of myd, 45.2 gb of myi, 33 of the tables/indexes break 1 gig, largest is 6 gig if you discount the piddly little tables, there are only 125 tables with size that matter, ie larger than 50mb Here's what we have now, sugestions welcome max_connections=100 query_cache_size=512M query_cache_type=1 table_cache=1024 tmp_table_size=400M thread_cache_size=8 myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=369M key_buffer_size=318M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K open-files-limit=500 myisam-recover=BACKUP,FORCE memlock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 27/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Null alphabetic order
-Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: 13 July 2005 13:38 To: Scott Hamm Cc: 'Mysql ' Subject: Re: Null alphabetic order Scott Hamm wrote: How do I use ORDER BY in a way that it list null last after Z instead of before A? I.e. instead of: Null, A, B, C result would be: X, Y, Z, null How can I get around to that? Something like ORDER BY IF(col IS NULL, 1, 0), col Michael Or simply: ORDER BY col IS NULL, col Which will probably be *slightly* faster.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.13/47 - Release Date: 12/07/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
