Re: ms sql server to mysql migration
Hello, The platform we are working is Mysql 4.1.8 version and Windows XP os. In ms sql server the space used and free space available for each database is obtained through 'sp_spaceused'procedure( built in ). Primary memory used by ms sql server and related services like query browser is obtained by sysprocesses table.It gives primary memory used in terms of pages in cache. The cpu used by ms sql server is obtained through spt_monitor table. similarly my friends who are working on oracle obtained all the above values from system tables for oracle. so both of them that is those working on ms sql server and oracle could use them directly in java programs. Are there any system tables in mysql that give info. about the above issues.If so please suggest them. This gives you the current processes: show full processlist 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]
Re: Syntax diagram, where is it located in the doc?
Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 11:39:12: Hi! I'm looking for the syntax diagram for MySQL and can't find it. I have downloaded the entire MySQL manual as one html page and searched it for the definition of where_definition and I cant find it. Could somebody please point me to a location where the complete syntax diagram can be found? Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexes
MySql 4.0.14 I understand that the most effective way of speeding up SELECT statements is to have column level indexes. Are there any other level indexes exist? I have been told that there are but I have no idea, looked through the manual, could not find anything that speeds up column level indexing. regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: den 4 februari 2005 12:45 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Syntax diagram, where is it located in the doc? Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 11:39:12: Hi! I'm looking for the syntax diagram for MySQL and can't find it. I have downloaded the entire MySQL manual as one html page and searched it for the definition of where_definition and I cant find it. Could somebody please point me to a location where the complete syntax diagram can be found? Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Strange, where is the definition for the syntax element where_definition done then? That is the part of the syntax diagram I currently looking for. It is defined as an element in the select syntax diagram. But when trying to find the definition for what is legal to put in a where clause, I just can't find it. Could somebody point in me the correct direction? /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Syntax diagram, where is it located in the doc?
You're right there is none.. .but as you see there is a short line that says: In the WHERE clause, you can use any of the functions that MySQL supports, except for aggregate (summary) functions. See section Functions and Operators. Gabriel PREDA - Original Message - From: Thomas Sundberg [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, February 04, 2005 2:46 PM Subject: RE: Syntax diagram, where is it located in the doc? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: den 4 februari 2005 12:45 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Syntax diagram, where is it located in the doc? Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 11:39:12: Hi! I'm looking for the syntax diagram for MySQL and can't find it. I have downloaded the entire MySQL manual as one html page and searched it for the definition of where_definition and I cant find it. Could somebody please point me to a location where the complete syntax diagram can be found? Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Strange, where is the definition for the syntax element where_definition done then? That is the part of the syntax diagram I currently looking for. It is defined as an element in the select syntax diagram. But when trying to find the definition for what is legal to put in a where clause, I just can't find it. Could somebody point in me the correct direction? /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax diagram, where is it located in the doc?
From the manual, where_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected. http://dev.mysql.com/doc/mysql/en/select.html That seems simple and straightforward to me. Perhaps if you told us why you need this, someone could provide you with the answer you need. Michael Thomas Sundberg wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: den 4 februari 2005 12:45 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Syntax diagram, where is it located in the doc? Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 11:39:12: Hi! I'm looking for the syntax diagram for MySQL and can't find it. I have downloaded the entire MySQL manual as one html page and searched it for the definition of where_definition and I cant find it. Could somebody please point me to a location where the complete syntax diagram can be found? Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Strange, where is the definition for the syntax element where_definition done then? That is the part of the syntax diagram I currently looking for. It is defined as an element in the select syntax diagram. But when trying to find the definition for what is legal to put in a where clause, I just can't find it. Could somebody point in me the correct direction? /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 12:46:02: Does anybody at the list know the answer to my question? I sent it a few days ago and haven't received any response. It does exist a syntax diagram for MySQL, doesn't it? Since no-one replied to your first post, apparently not. I have never seen such a thing. Strange, where is the definition for the syntax element where_definition done then? That is the part of the syntax diagram I currently looking for. It is defined as an element in the select syntax diagram. But when trying to find the definition for what is legal to put in a where clause, I just can't find it. Could somebody point in me the correct direction? The WHERE keyword is followed by an expression. There appears to be no no formal definition of expression, but it could be informally defined as the a combination of Operatiors applied to column names and constants. See manual chapter 12: Operators. The WHERE clause restricts to rows where the expression returns true. Alec Cawley. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
-Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: den 4 februari 2005 14:19 To: Thomas Sundberg Cc: mysql@lists.mysql.com Subject: Re: Syntax diagram, where is it located in the doc? From the manual, where_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected. http://dev.mysql.com/doc/mysql/en/select.html That seems simple and straightforward to me. Perhaps if you told us why you need this, someone could provide you with the answer you need. It is very simple but absolutely not straight forward. It really doesn't say anything. Just that you should do things right and then you will not have any problems. The concrete problem I tried to solve were if MySQL supports xor in a where clause. And if so, how should the syntax be written? That would have been extremely simple if the syntax diagram started just above the quote you supplied us with had been completed and not ended when things got a bit interesting. /Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb problem
Hello, my name's Matteo, probably my question is basic but I'm new with mysql. I've an application that write some milion of row in mysql innodb table. Every day my application creates a new table, write data and drop table oldest than 15 days. After the drop table execution command the disk space on my linux server doen't shrink and the disk space grow winthout end. is there a way or configuration setting to resolve this problem? Many thanks if someone can help me!!! Best regards, Matteo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
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: Syntax diagram, where is it located in the doc?
-Original Message- From: Thomas Sundberg Sent: Friday, February 04, 2005 07:48 To: mysql@lists.mysql.com -Original Message- From: Michael Stassen Sent: den 4 februari 2005 14:19 To: Thomas Sundberg Cc: mysql@lists.mysql.com From the manual, where_definition consists of the keyword WHERE followed by an expression that indicates the condition or conditions that rows must satisfy to be selected. http://dev.mysql.com/doc/mysql/en/select.html That seems simple and straightforward to me. Perhaps if you told us why you need this, someone could provide you with the answer you need. It is very simple but absolutely not straight forward. It really doesn't say anything. Just that you should do things right and then you will not have any problems. The concrete problem I tried to solve were if MySQL supports xor in a where clause. And if so, how should the syntax be written? That Yes, you can use XOR in the where clause. SELECT * FROM mytable WHERE col1 XOR col2; This is not a bitwise XOR, it evaluates each column to true or false first then evals the XOR. example for an int column: a | b | eval 0 | 0 | false 1 | 0 | true 1 | 1 | false -1| 12| false 12| 0 | true would have been extremely simple if the syntax diagram started just above the quote you supplied us with had been completed and not ended when things got a bit interesting. /Thomas --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Syntax diagram, where is it located in the doc?
Thomas Sundberg [EMAIL PROTECTED] wrote on 04/02/2005 13:48:03: It is very simple but absolutely not straight forward. It really doesn't say anything. Just that you should do things right and then you will not have any problems. The concrete problem I tried to solve were if MySQL supports xor in a where clause. And if so, how should the syntax be written? That would have been extremely simple if the syntax diagram started just above the quote you supplied us with had been completed and not ended when things got a bit interesting. It would probably not have been very hepful because it would simply have mentioned operators and referred you back to section 12 of the manual for a complete (and growing) list of operators. Good database practice suggests that the same data - the list of valid operators - should not be in two places unless there is an aoutomated method of deriveing the lesser from the greater.. The master copy is the list of operators in the Syntax section of the manual. Since operators includes words like IN, AND, NOT, the syntax of operators is roughly [non-space-character]* . If you looked in the manuel, under operators, then bitwise operators, you would find xor near the top of the table - togehter with the information (not available in a syntax diagram) that it is only available since 4.0.2. Alternatively , a second's experimentation (SELECT 5^1;) would have shown that it has the obvious syntax - or the alternative syntax (SELECT 5 XOR 1;) ; Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
!! Help with query: Where Date = MAX(Date)
Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest Year,Month for a given date, so for example today (20050204) I should retrieve 2005,01. As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help much appreciated Graham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: passing environment variable to an SQL script
I found an answer: mysql -e SET @VAR:=1234; SOURCE xxx.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: !! Help with query: Where Date = MAX(Date)
Graham Cossey wrote: Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest Year,Month for a given date, so for example today (20050204) I should retrieve 2005,01. As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help much appreciated Graham have a look here : http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html it should be useful for you. -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- 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
DDL cannot be rollback http://dev.mysql.com/doc/mysql/en/cannot-roll-back.html It also apply to many DB like Sybase for example... Ville Karjalainen wrote: 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) -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character Sets, 4.0 and 4.1
So today for the second time in six weeks we are faced with rolling back to mysql 4.0 because of dramas with character sets. I don't know about anyone else but this supposedly wonderful feature has been nothing but a nightmare for us. So our Application servers use Unicode for our non US English products, and they talk to MySQL through Connector J with a flag set to use Unicode in the JDBC config. First time around we just dumped the data and then imported it into the 4.1 instance. Everything looked good, but it wasn't. The German folks were complaining their various umlauts and so on were missing, and there was more. Of course we're told to just bring the data over to mysql 4.1 and we'll have no problems, so we do that, and because we didn't specify a character set for the import, we got latin1, and our German and Chinese and... All broke. So six weeks of trial and experimentation later and we try for another update. This time in our create database statement when we begin to import the database, we set the default character set to utf8 for everything. Now after the import our Germans and Chinese folks still get the results they expect. A day later and we are getting complaints from Hong Kong that there are a whole bunch of messages appearing on their discussions with no message body. We look at the backend and right there in the database the messages are sitting and the body consists of exactly one space. Whatever content was sent to us, was turned into one space. We look at it and we see that there a more than a few messages that got migrated from 4.0 to 4.1 and their message bodies are also one space. Not all messages, just some. Not all messages from any individual user, just some... The 4.0 version of the data has content that consists of more than a single space... Can't quite tell what it is, but there's content there in 4.0 that disappears in 4.1. So I understand that having multiple character sets is a good thing, but to be honest, I pretty much thought we had it in 4.0.. We told the JDBC to us Unicode and away we went... Clearly someone was using something that wasn't unicode (some of the comments suggest that there is some Japanese in the missing messages, but I can't tell), and for whatever reason mysql 4.1 decided it should be repalced with a space character. I'm probably missing the point of the character set support along the way somewhere... But I need to know how to fix this (I understand that's difficult when all I have left is one blank space and don't know how to reproduce the problematic data). What did I miss in the simple open your data files with 4.1 and it's good to go instructions... What character set performs the same as MySQL 4.0, where it didn't care what character set you gave it, it would accept it? Can we have a character set that will give us this functionality? And why are we taking input data on an import and by the looks of it an insert, and turning it into a single space, can't we do something better with the data? 4.0 worked for us with products in 20+ languages. It worked with no great effort and no problems... Now we have the new enhanced version which provides better support for international character sets, and we find ourselves with lost data from the moment we import, and user posts disappearing as they come in. What do we do to not have this problem? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help please : ERROR 2006: MySQL server has gone away
Hello. 4.0.17 is old enough. You may switch to the debug version and attempt to find the clues in debug or trace files. Can you reproduce an error on the latest release (4.1.9 now)? Do you lost connection to the server with other statements, than 'SHOW DATABASES'? Please answer on this questions: -What operating system do you use? -Do you use official binaries? [snip] We still have the problem... In the error log, I have nothing about problem. I only have that: 050203 00:34:14 mysqld started /u01/mysql/libexec/mysqld: ready for connections. Version: '4.0.17-log' socket: '/tmp/mysql.sock' port: 3306 If I do show variables, I have max_allowed_packet = 16776192 IF I log into mysql with :mysql -uroot --max_allowed_packet=16M -p After I do:show databases; and I receive the answer. After I wait 30 sec and launch the command show databases; again and now I have the error: ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Marois, David [EMAIL PROTECTED] wrote: [snip] Marois, David [EMAIL PROTECTED] wrote: -- 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]
Re: ssh connecting with a mysql client I get: ERROR 2013
Hello. Can you connect using the mysql command line client program? leegold [EMAIL PROTECTED] wrote: ssh connecting with a mysql client gui I get: ERROR 2013 Lost connection to MySQL server during query I'm using mysqlyog and trying connect via the ssh tunnel. I can connect with out the ssh w/mysqljog OK. It's a very nice client and would like to get this working. I have googled it but nothing seems to ring a bell. I can ssh connect with Putty OK. Is this a problem on the mysql on server or my desktop? What can it be? Thanks. -- 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]
Re: Symchronization problem
Hello. Hello. A lot of thins may cause this behaviour, even not related to the replication. At: http://dev.mysql.com/doc/mysql/en/todo-mysql-5-1.html said that, the one of the new feautures would be: Online backup with very low performance penalty. So it can be an answer on your question. Andre Matos [EMAIL PROTECTED] wrote: Hi List, A have two MySQL 4.1.9 installed into two Linux servers and synchronized them, so I have now a master and slave. My problem is that since I synchronized them, I am receiving comments from my users that the speed is not good as before the synchronization. Is this possible? If yes, how and where can I check this? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- 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]
Re: Syntax diagram, where is it located in the doc?
Hello. May be it is not exactly what you want, but usually, I look at the sql/sql_yacc.yy in a source distribution. Thomas Sundberg [EMAIL PROTECTED] wrote: Hi! I'm looking for the syntax diagram for MySQL and can't find it. I have downloaded the entire MySQL manual as one html page and searched it for the definition of where_definition and I cant find it. Could somebody please point me to a location where the complete syntax diagram can be found? /Thomas -- 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]
Re: How can we use --log-warnings
Hello. I didn't find any bug related to this behaviour. What operation system do you use? Connect to the server with mysql command line client program and kill it with the SIGKILL signal. Check the error file to find messages about aborted connections. Can you reproduce a problem on the latest release (4.1.9 now)? Try only --log-warnings, without specifying the exact value. [snip] We are with mysql 4.0.17-log and we want to use --log-warnings to see aborted connections in our errorlog. We put log_warnings = 2 in our my.cnf and restarted mysql but we did not see aborted connections in our errorlog. We tried --log-warnings = 2 on the command line when we started mysql and again, we did not see aborted connections in our errorlog. We tried --log-warnings on the command line when we started mysql and again, we did not see aborted connections in our errorlog. When I do a mysqladmin variables, I saw log_warnings = ON. So, how can we set this parameter to be able to see something in our errorlog ?Marois, David [EMAIL PROTECTED] wrote: [snip] -- 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]
Re: innodb problem
Hello. Use the max attribute. See: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html If you want to decrease the size of your tablespace, see: http://dev.mysql.com/doc/mysql/en/adding-and-removing.html [EMAIL PROTECTED] wrote: Hello, my name's Matteo, probably my question is basic but I'm new with mysql. I've an application that write some milion of row in mysql innodb table. Every day my application creates a new table, write data and drop table oldest than 15 days. After the drop table execution command the disk space on my linux server doen't shrink and the disk space grow winthout end. is there a way or configuration setting to resolve this problem? Many thanks if someone can help me!!! Best regards, Matteo -- 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]
Re: Error on View
Hello. See: http://dev.mysql.com/doc/mysql/en/gone-away.html [snip] Hi, I installed the mysql 5.0.2 alpha for linux and i got an error after created a view and try to select * from the view. ERROR 2013: Lost connection to MySQL server during query Does anyone knows what is happening? Best RegardsOropeza Querejeta, Alejandro [EMAIL PROTECTED] wrote: [snip] -- 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]
RE: Character Sets, 4.0 and 4.1
Bruce We're sort of in the same boat but if you persevere you can eradicate all the problems. Now we don't run the server as utf8 which does cause a number of problems - we don't want all the databases to be utf8 just some of them, and just some tables on some of them. For a database that needs to be utf8 you need to ensure that its default character set is utf8 - do a show database create and then an alter database as necessary. Any connections to that database must specify their default character set to be utf8 when the connection is opened otherwise you get problems. In addition you need to ensure that all the character sets on the tables default to utf8 (show create table) and that all 'text' type columns (char,varchar, text, etc) have a default type of utf8. On a non-utf8 database where the table is utf8, the default character set on the table and all the 'text' type columns must be utf8. You can't use this table in a join to a non-utf8 table, and all transactions which use this table must explicitly wrap the transaction in as pair of set character set statements - unless you create the connection on a per table/per query basis We've had lots of fun over the last few months integrating web from ends with backend processing. We're currently running 4.1.4 so 4.1.9 may be better. When you're importing/dumping remember to set the default character set on the command line of mysqldump/mysqlimport to the appropriate value otherwise you'll garbage your data on import/export. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk -Original Message- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: 04 February 2005 14:55 To: mysql@lists.mysql.com Subject: Character Sets, 4.0 and 4.1 So today for the second time in six weeks we are faced with rolling back to mysql 4.0 because of dramas with character sets. I don't know about anyone else but this supposedly wonderful feature has been nothing but a nightmare for us. So our Application servers use Unicode for our non US English products, and they talk to MySQL through Connector J with a flag set to use Unicode in the JDBC config. First time around we just dumped the data and then imported it into the 4.1 instance. Everything looked good, but it wasn't. The German folks were complaining their various umlauts and so on were missing, and there was more. Of course we're told to just bring the data over to mysql 4.1 and we'll have no problems, so we do that, and because we didn't specify a character set for the import, we got latin1, and our German and Chinese and... All broke. So six weeks of trial and experimentation later and we try for another update. This time in our create database statement when we begin to import the database, we set the default character set to utf8 for everything. Now after the import our Germans and Chinese folks still get the results they expect. A day later and we are getting complaints from Hong Kong that there are a whole bunch of messages appearing on their discussions with no message body. We look at the backend and right there in the database the messages are sitting and the body consists of exactly one space. Whatever content was sent to us, was turned into one space. We look at it and we see that there a more than a few messages that got migrated from 4.0 to 4.1 and their message bodies are also one space. Not all messages, just some. Not all messages from any individual user, just some... The 4.0 version of the data has content that consists of more than a single space... Can't quite tell what it is, but there's content there in 4.0 that disappears in 4.1. So I understand that having multiple character sets is a good thing, but to be honest, I pretty much thought we had it in 4.0.. We told the JDBC to us Unicode and away we went... Clearly someone was using something that wasn't unicode (some of the comments suggest that there is some Japanese in the missing messages, but I can't tell), and for whatever reason mysql 4.1 decided it should be repalced with a space character. I'm probably missing the point of the character set support along the way somewhere... But I need to know how to fix this (I understand that's difficult when all I have left is one blank space and don't know how to reproduce the problematic data). What did I miss in the simple open your data files with 4.1 and it's good to go instructions... What character set performs the same as MySQL 4.0, where it didn't care what character set you gave it, it would accept it? Can we have a character set that will give us this functionality? And why are we taking input data on an import and by the looks of it an insert, and turning it into a single space, can't we do something better with the data? 4.0 worked for us with products in 20+ languages. It worked with no great effort and no problems... Now we have the new
developing an interface
I have taken on a job replacing an old AS400 RPM database with four thin clients. They really like the feel of the thin client interface, no point and click, no graphics, just green text on a black background. I could use any suggestions on what to read, or where to look to find a way to create a similar feel on the new interface. I am leaning heavily to php, since it would make the Internet port easier, but am not adversed to changing my mind. Thank You very much for your replies. Winn Johnston __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexes
A Z [EMAIL PROTECTED] wrote on 02/04/2005 07:21:35 AM: MySql 4.0.14 I understand that the most effective way of speeding up SELECT statements is to have column level indexes. Are there any other level indexes exist? I have been told that there are but I have no idea, looked through the manual, could not find anything that speeds up column level indexing. regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http: //uk.messenger.yahoo.com Creating a good index schema is usually the most effective way to speed up most queries. However all indexes are not used for every query. The query engine chooses up to one index (per table) if it feels that using the index will help query performance. It determines this by checking the cardinality of the index as it relates to your query. The cardinality is related to how many rows the index holds for any particular value (or value range or value set) compared to the size of the index. The higher the cardinality, the more selective an index will be and that generally means that you will get fewer records back in the results of the query. When the engine determines, by looking at the tables statistics, that using an index in a particular query will result in returning approximately 30% or less of all the rows in a table, it will probably use the index. Many new users create very non-selective indexes which will be considered but not used as they end up returning too many rows to be useful (like indexing a true/false column). The reason that the usability threshold of an index is somewhere near 30% is that, for values larger than that number, it would take longer to use an index to get the position of each target row then go get that row from the data than it would to just scan the data directly in the first place. There are at least 2 fewer disk seeks to perform a direct read of the table than for an indexed locate for EACH value retrieved. The key to creating a good index schema is to look at your query patterns. Look specifically at your WHERE clauses, first. If you notice that 80% of your queries use your_column_a and your_column_b as a pair then it would more than likely help you to create an index that covered both of those columns ALTER TABLE your_table_1 ADD INDEX (your_column_a, your_column_b) Because MySQL is smarter than some database engines, any query that only references your_column_a may also use that index (depending on the cardinality). My advice is: A) Avoid single column indexes whenever practical. Most useful indexes contain from 2 to 5 fields. B) Don't forget that PRIMARY keys and UNIQUE constraints are also indexes. C) Design your indexes after your most common or frequently used query patterns. Analyze your WHERE clauses first, then look at speeding up certain queries by considering values in your ORDER BY clauses. D) Learn how to use EXPLAIN. It will give you excellent advice on how to help your queries. E) Sometimes functions in your WHERE clauses eliminate the possibility of using an index. Learn how to say your_column_name comparison function or constant expression rather than function or expression using your_column_name comparison function or constant expression. For example, if you want to find date values in the column logdate that are at least 60 days old, DO NOT use this: WHERE logdate + 60 days CURDATE() use this instead: WHERE logdate curdate() - 60 days F) Read the fine manual. http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/estimating-performance.html http://dev.mysql.com/doc/mysql/en/select-speed.html http://dev.mysql.com/doc/mysql/en/where-optimizations.html http://dev.mysql.com/doc/mysql/en/optimizing-database-structure.html (whole chapter) (especially this part) http://dev.mysql.com/doc/mysql/en/mysql-indexes.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
interface developing
i have taken on a job replacing an old AS400 RPM database with four thin clients. They really like the feel of the thin client interface, no point and click, no graphics, just green text on a black background. I could use any suggestions on what to read, or where to look to find a way to create a similar feel on the new interface. I am leaning heavily to php, since it would make the Internet port easier, but am not adversed to changing my mind. Thank You very much for your replies. Winn Johnston __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: developing an interface
Winn Johnston [EMAIL PROTECTED] wrote on 02/04/2005 10:30:54 AM: I have taken on a job replacing an old AS400 RPM database with four thin clients. They really like the feel of the thin client interface, no point and click, no graphics, just green text on a black background. I could use any suggestions on what to read, or where to look to find a way to create a similar feel on the new interface. I am leaning heavily to php, since it would make the Internet port easier, but am not adversed to changing my mind. Thank You very much for your replies. Winn Johnston __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail Sounds like you want to develop a console application using some kind of TELNET daemon. I don't do that kind of development but it may be something for you to research. My users tell me they prefer the point-and-click of browser-based, thin client apps over their old terminal-type apps, sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
ENCODE DECODE
Hi, I have a table where users upload various data items via a web site. Some fields in the table are named DATA_ENC... to denote that the data should be encrypted uusing the encode function. This all works fine, however when I come to selecting the data from the table I would like to be able to select all the data and decrypt any fields as required. Is this possible with one query, or do I have to select every data item then decrypt it then present the data? Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax diagram, where is it located in the doc?
Thomas, As you've discovered, MySQL documentation doesn't (yet) include a syntax diagram, but there's a manual page for WHERE clauses, there's a manual page for logical operators including XOR, and they show that the answer to your question about WHERE clauses using XOR is 'yes', as does writing the simplest possible toy query on a test table. MySQL is open source. Perhaps you'll be the one to write the syntax diagram? PB - Thomas Sundberg wrote: -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED]] Sent: den 4 februari 2005 14:19 To: Thomas Sundberg Cc: mysql@lists.mysql.com Subject: Re: Syntax diagram, where is it located in the doc? From the manual, "where_definition consists of the keyword WHERE followed by an _expression_ that indicates the condition or conditions that rows must satisfy to be selected." http://dev.mysql.com/doc/mysql/en/select.html That seems simple and straightforward to me. Perhaps if you told us why you need this, someone could provide you with the answer you need. It is very simple but absolutely not straight forward. It really doesn't say anything. Just that you should do things right and then you will not have any problems. The concrete problem I tried to solve were if MySQL supports xor in a where clause. And if so, how should the syntax be written? That would have been extremely simple if the syntax diagram started just above the quote you supplied us with had been completed and not ended when things got a bit interesting. /Thomas No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.5 - Release Date: 2/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: developing an interface
--- [EMAIL PROTECTED] wrote: Winn Johnston [EMAIL PROTECTED] wrote on 02/04/2005 10:30:54 AM: I have taken on a job replacing an old AS400 RPM database with four thin clients. They really like the feel of the thin client interface, no point and click, no graphics, just green text on a black background. I could use any suggestions on what to read, or where to look to find a way to create a similar feel on the new interface. I am leaning heavily to php, since it would make the Internet port easier, but am not adversed to changing my mind. Thank You very much for your replies. Winn Johnston __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail Sounds like you want to develop a console application using some kind of TELNET daemon. I don't do that kind of development but it may be something for you to research. My users tell me they prefer the point-and-click of browser-based, thin client apps over their old terminal-type apps, sorry! Shawn Green Database Administrator Unimin Corporation - Spruce Pine My users have expressed a distinct dislike for point and click methods. They would rather use the keyboard in the same way a ticket agent does when she is looking for a flight :) Winn Johnston __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: developing an interface
On Friday 04 February 2005 16:03, Winn Johnston might have typed: My users have expressed a distinct dislike for point and click methods. They would rather use the keyboard in the same way a ticket agent does when she is looking for a flight :) You can do this one of two ways. Give them shell logins on a *nix box (or port to win32) and write your application in perl or php as a console app. Give them lynx and write your app in php as a web app, using style sheets etc. They get a green screen (literally with the right lynx config) where tab etc all behave (complete with 1 - 9 working as hotkeys for menus), but you can migrate new users to the Firefox/IE interface :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE DECODE
On Friday 04 February 2005 09:51 am, shaun thornburgh wrote: I have a table where users upload various data items via a web site. Some fields in the table are named DATA_ENC... to denote that the data should be encrypted uusing the encode function. This all works fine, however when I come to selecting the data from the table I would like to be able to select all the data and decrypt any fields as required. Is this possible with one query, or do I have to select every data item then decrypt it then present the data? Wouldn't select decode(col1) from table work? Jeff pgp1p0HRcMiEu.pgp Description: PGP signature
Re: developing an interface
lynx i like it :) thanks duncan, u the man winn johnston -- Duncan Hill [EMAIL PROTECTED] wrote: On Friday 04 February 2005 16:03, Winn Johnston might have typed: My users have expressed a distinct dislike for point and click methods. They would rather use the keyboard in the same way a ticket agent does when she is looking for a flight :) You can do this one of two ways. Give them shell logins on a *nix box (or port to win32) and write your application in perl or php as a console app. Give them lynx and write your app in php as a web app, using style sheets etc. They get a green screen (literally with the right lynx config) where tab etc all behave (complete with 1 - 9 working as hotkeys for menus), but you can migrate new users to the Firefox/IE interface :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: !! Help with query: Where Date = MAX(Date)
snip As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help much appreciated Graham have a look here : http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html it should be useful for you. Thanks Philippe that could do it. Graham. -- Philippe Poelvoorde COS Trading Ltd. +44.(0)20.7376.2401 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: developing an interface
On Fri, 2005-02-04 at 11:08, Duncan Hill wrote: On Friday 04 February 2005 16:03, Winn Johnston might have typed: My users have expressed a distinct dislike for point and click methods. They would rather use the keyboard in the same way a ticket agent does when she is looking for a flight :) You can do this one of two ways. Give them shell logins on a *nix box (or port to win32) and write your application in perl or php as a console app. Give them lynx and write your app in php as a web app, using style sheets etc. They get a green screen (literally with the right lynx config) where tab etc all behave (complete with 1 - 9 working as hotkeys for menus), but you can migrate new users to the Firefox/IE interface :) This is an excellent idea. It makes for easy development while giveing them the old DOS-terminal throwback feel, but may I suggest 'elinks' instead of 'lynx'? I have been a lynx user for years (I do a lot of work where I only have ssh/console access to my clients systems) but the newer elinks which I recently discovered is very nice with a cleaner, less cluttered screen. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade packaged MySQL
Any advice for upgrading MySQL when it came packaged with a commercially available code (iTracker by Newark Electronics)? -Elton WindowsNT
Re: !! Help with query: Where Date = MAX(Date)
On Fri, 2005-02-04 at 09:19, Graham Cossey wrote: Could someone please offer a little help. I have a table like: Year, Month, Start_date 20041020041102 20041120041203 20041220050104 20050120050204 20050220050303 I need to get the latest Year,Month for a given date, so for example today (20050204) I should retrieve 2005,01. As I'm using 4.0.20 I can't use subqueries so how can I create a query that does this? SELECT year, month FROM `dc_months` WHERE start_date = (SELECT MAX(start_date) from dc_months where start_date = '20050204') Any help much appreciated Graham I think this conveys the idea: SELECT year, month FROM `dc_months` WHERE start_date = '20050204' ORDER BY start_date DESC LIMIT 1 -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE DECODE
Apparently not! mysql SELECT DECODE ( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_1_DESC_137; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_ mysql Any ideas? From: Jeff Smelser [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: ENCODE DECODE Date: Fri, 4 Feb 2005 10:20:14 -0600 On Friday 04 February 2005 09:51 am, shaun thornburgh wrote: I have a table where users upload various data items via a web site. Some fields in the table are named DATA_ENC... to denote that the data should be encrypted uusing the encode function. This all works fine, however when I come to selecting the data from the table I would like to be able to select all the data and decrypt any fields as required. Is this possible with one query, or do I have to select every data item then decrypt it then present the data? Wouldn't select decode(col1) from table work? Jeff attach3 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with date query
I want to run a nightly cron job where you iterate throught each row of
a single table and reset a field depending on the conditions specified.
In this case I want to take a date field (RenewalDate) and compare it
to the current date less a 30 day grace period and if true reset the
(SubscriptionEpired) field to a 1 (true).
RenewalDate =(${now} - 30) SubscriptionExpired = 1
Both fields are in a single table called Restaurants
RenewalDate date (-mm-dd)
SubscriptionExpired int(1)
Will something like this work or do I need something different?
UPDATE Restaurant SET SubscriptionExpired = 1 WHERE TO_DAYS(NOW) -
TO_DAYS(RenewalDate) 30;
Thanks,
Jack
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade packaged MySQL
Hello. See: http://dev.mysql.com/doc/mysql/en/upgrade.html http://dev.mysql.com/doc/mysql/en/windows-upgrading.html If your MySQL distribution came as embedded version, you should look at: http://dev.mysql.com/doc/mysql/en/libmysqld-overview.html The complete answers related to licensing policy you can receive at [EMAIL PROTECTED] [snip] Any advice for upgrading MySQL when it came packaged with a commercially available code (iTracker by Newark Electronics)? -Elton WindowsNTElton Clark [EMAIL PROTECTED] wrote: [snip] -- 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]
Re: ENCODE DECODE
Hello. I think you should remove the space after DECODE. shaun thornburgh [EMAIL PROTECTED] wrote: Apparently not! mysql SELECT DECODE ( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_1_DESC_137; ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_ mysql Any ideas? From: Jeff Smelser [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Re: ENCODE DECODE Date: Fri, 4 Feb 2005 10:20:14 -0600 On Friday 04 February 2005 09:51 am, shaun thornburgh wrote: I have a table where users upload various data items via a web site. Some fields in the table are named DATA_ENC... to denote that the data should be encrypted uusing the encode function. This all works fine, however when I come to selecting the data from the table I would like to be able to select all the data and decrypt any fields as required. Is this possible with one query, or do I have to select every data item then decrypt it then present the data? Wouldn't select decode(col1) from table work? Jeff attach3 -- 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]
Multiple Tablespace
Hi ALL, Im using mySQL Distrib 4.0.18 and using single Tablespace (/data/ibdata/ibdata1). Now Im planning to upgrade to 4.1.8 and move single tablespace to Using Per-Table Tablespaces. Please share your expertise in moving single TB to using per-table TB with my existing data also. Thanks Sabeer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cant connect to mysql error :20031 /10061
Hi I've a question I have a delphi program that connects to a mysql database via obdc, and works perfect in the lan and in the server, I want to know if I can enter the database if I have the program installed in another city or place, I know that I have to enter the IP of the server but I've tried this and it doesn´t work when I try to connect to mysql from outside de lan I receive the message error 2003: can´t connect to xxx..xxx.xxx (10061) I create a host in the user database of mysql with % Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y % root Y Y Y Y Y Y Y Y Y Y Y Y Y Y localhost Y Y Y Y Y Y Y Y Y Y Y Y Y Y % N N N N N N N N N N N N N N 200.87.51.XX prueba 1f3d25cd5ea79fae Y Y Y Y Y Y Y Y Y Y Y Y Y Y Revisar todos/as / Desmarcar todos Con marca: And i can´t connect to mysql . Is there something more that I have to do in order to work? Regards Marcelo
Re: Log data transfer amount?
No ideas internally to mysql, you can always fire up a sniffer (sniffit, tcpdump, etc).. something like that would tell you. On Mon, 31 Jan 2005 20:32:49 -0500, John May [EMAIL PROTECTED] wrote: Anyone have any ideas on this one? : I've scoured the MySQL manuals... does anyone know if there's any way to log the amount of data that individual queries produce? Eg: like bytes transferred in a web server log? - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] Professional Lasso / PHP / MySQL / FileMaker Pro Hosting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with date query
Jack Lauman [EMAIL PROTECTED] wrote on 02/04/2005 11:57:37 AM:
I want to run a nightly cron job where you iterate throught each row of
a single table and reset a field depending on the conditions specified.
In this case I want to take a date field (RenewalDate) and compare it
to the current date less a 30 day grace period and if true reset the
(SubscriptionEpired) field to a 1 (true).
RenewalDate =(${now} - 30) SubscriptionExpired = 1
Both fields are in a single table called Restaurants
RenewalDate date (-mm-dd)
SubscriptionExpired int(1)
Will something like this work or do I need something different?
UPDATE Restaurant SET SubscriptionExpired = 1 WHERE TO_DAYS(NOW) -
TO_DAYS(RenewalDate) 30;
Thanks,
Jack
Yes, that would do what you want but because you have a function on the
left side of an = in your WHERE clause, you eliminate the possibility to
use an index on that field. You might try rewriting your statement this
way:
UPDATE Restaurant
SET SubscriptionExpired = 1
WHERE RenewalDate (CURDATE() - 30 Days);
It would probably use an index and finish much faster. However, if you
have a timestamp field, that statement will cause your timestamp to reset
for each already expired record, too (because you are resetting all of the
old records to 1, even if they are already expired). What you can do to
limit which timestamps are updated is to limit your changes to only those
rows that need changing like this:
UPDATE Restaurant
SET SubscriptionExpired = 1
WHERE RenewalDate (CURDATE() - 30 Days)
AND SubscriptionExpired = 0;
That may work even faster because it's more selective. Your mileage may
vary (YMMV).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Cant connect to mysql error :20031 /10061
First, check your my.cnf file to see if you have a 'bind-address' entry. Such an entry will constrain you system only responding to machines on the same network. Failing that, have you checked you firewall settings? Are you sure that can can even reach port 3306 on that machine from the outside world? You might try setting up a packet sniffer like Ethereal on the server to listen for connections to port 3306 and then try to connect from the outside. If you see no connection attempt being made, then very like your network configuration is not allowing these connections through to the server. - michael dykman On Fri, 2005-02-04 at 12:48, [EMAIL PROTECTED] wrote: Hi I've a question I have a delphi program that connects to a mysql database via obdc, and works perfect in the lan and in the server, I want to know if I can enter the database if I have the program installed in another city or place, I know that I have to enter the IP of the server but I've tried this and it doesnt work when I try to connect to mysql from outside de lan I receive the message error 2003: cant connect to xxx..xxx.xxx (10061) I create a host in the user database of mysql with % Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv localhost root Y Y Y Y Y Y Y Y Y Y Y Y Y Y % root Y Y Y Y Y Y Y Y Y Y Y Y Y Y localhost Y Y Y Y Y Y Y Y Y Y Y Y Y Y % N N N N N N N N N N N N N N 200.87.51.XX prueba 1f3d25cd5ea79fae Y Y Y Y Y Y Y Y Y Y Y Y Y Y Revisar todos/as / Desmarcar todos Con marca: And i cant connect to mysql . Is there something more that I have to do in order to work? Regards Marcelo -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing Questions (Problem?)
Thanks Tom and Michael, I got rid of all of the indexes except the one marked PRIMARY and a new one that I made like this: ALTER TABLE logs ADD unique (host,date,time,priority,facility,seq); I see quite an improvement from what I was seeing before. The one thing that I didn't realize is that the optimizer will only use one key. I also didn't realize that if I make an index on multiple keys that I could use that index to select when I was searching without all keys present (probably should have realized that). I still can't get rid of Using filesort but it doesn't seem to bee too big of a performance hit. I will try to re-read the docs again when I return next week. I also rewrote the queries that used HOUR(date) to something that doesn't include a function. Sometimes reading the docs isn't enough ;-) Thanks again for your help, BMG -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL upgrading
Hi all, I know this is a silly question but, frankly, I didn't find any useful and straightforward document in dev.mysql.com. I want to upgrade mysql server on a fedora core 3 linux system from 3.23 to the latest version 4.1. First I want to know if is possible, because I have read somewhere that upgrading from 3.23 should be done first to 4.0 and then from 4.0 to 4.1, but I don't know it is right or not ! If it is possible, is there any link to guide ? Thanks, Helena -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE DECODE
On Friday 04 February 2005 11:00 am, shaun thornburgh wrote: Apparently not! mysql SELECT DECODE ( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_1_DESC_137; Is it so hard to read directions? select decode(csv_data_enc_forename) from DATA_TABLE_PID_1_DESC_137; This assumes whatever is in that column, was encoded. Jeff pgpDfYZf8nbn6.pgp Description: PGP signature
Re: Multiple Tablespace
Sabeer, - Original Message - From: Sabeer MZ [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 04, 2005 7:33 PM Subject: Multiple Tablespace Hi ALL, Im using mySQL Distrib 4.0.18 and using single Tablespace (/data/ibdata/ibdata1). Now Im planning to upgrade to 4.1.8 and move single tablespace to Using Per-Table Tablespaces. there is a critical bug in innodb_file_per_table in = 4.1.8. You should upgrade to 4.1.9. Please share your expertise in moving single TB to using per-table TB with my existing data also. 1) Dump your tables. 2) Add to my.cnf: innodb_file_per_table 3) Rebuild the whole InnoDB installation according to the advice at http://dev.mysql.com/doc/mysql/en/error-creating-innodb.html Note that with multible tablespaces, a single file ibdata1:10M:autoextend is enough. Normally, it does not grow bigger than about 100 MB. 4) Import the table dumps to MySQL. Note that importing big tables to InnoDB can take days. As a sidenote: Jan and Jani are working on a fast index build algorithm. In 2006, the import may be quite fast. Thanks Sabeer Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb problem
Matteo, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 04, 2005 3:51 PM Subject: innodb problem Hello, my name's Matteo, probably my question is basic but I'm new with mysql. I've an application that write some milion of row in mysql innodb table. Every day my application creates a new table, write data and drop table oldest than 15 days. After the drop table execution command the disk space on my linux server doen't shrink and the disk space grow winthout end. is there a way or configuration setting to resolve this problem? an upgrade to 4.1.9, and reading http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html will help. When you DROP a table, the .ibd file will be deleted, and the disk space is released to the operating system. Many thanks if someone can help me!!! Best regards, Matteo Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Premature InnoDB conversion.
A, - Original Message - From: A Z [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, February 01, 2005 2:14 PM Subject: Premature InnoDB conversion. MySql 4.0.14 We tried to convert MyISAM table format to INNODB format, it took forever to finish the process, someone intervened and killed the process through Task Manager. Now can't run Mysqld-nt, running it with the --console reports the followings. Your help is appreciated. Microsoft Windows [Version 5.2.3790] (C) Copyright 1985-2003 Microsoft Corp. c:\MySql\mysqld-nt --console 050201 11:26:22 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 799702164 050201 11:26:22 InnoDB: Starting an apply batch of log records to the database. .. InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 7 3 74 75 76 77 78 050201 11:26:24 InnoDB: Assertion failure in thread 1344 in fi le ../innobase/include\page0page.ic line 482 InnoDB: Failing assertion: offs UNIV_PAGE_SIZE InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to mysql@lists.mysql.com the tablespace is corrupt. If you do not have valuable data in ibdata files, follow the advice at http://dev.mysql.com/doc/mysql/en/error-creating-innodb.html, and recreate the whole InnoDB installation. Tablespace corruption on Windows is rare. It could be an unknown InnoDB bug, an OS bug, or a hardware fault. New MySQL versions have better diagnostics. An upgrade to 4.0.23 would be good. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Syntax diagram, where is it located in the doc?
Hi Thomas,
I'm looking for the syntax diagram for MySQL and can't find it. I have
Just to give you a complete answer to your question, this is what is
valid (I've stripped out the C code parts to leave just the definition):
where_clause:
/* empty */
| WHERE expr
expr: expr_expr
| simple_expr
expr_expr:
expr IN_SYM '(' expr_list ')'
| expr NOT IN_SYM '(' expr_list ')'
| expr BETWEEN_SYM no_and_expr AND expr
| expr NOT BETWEEN_SYM no_and_expr AND expr
| expr OR_OR_CONCAT expr
| expr OR expr
| expr XOR expr
| expr AND expr
| expr LIKE simple_expr opt_escape
| expr NOT LIKE simple_expr opt_escape
| expr REGEXP expr
| expr NOT REGEXP expr
| expr IS NULL_SYM
| expr IS NOT NULL_SYM
| expr EQ expr
| expr EQUAL_SYM expr
| expr GE expr
| expr GT_SYM expr
| expr LE expr
| expr LT expr
| expr NE expr
| expr SHIFT_LEFT expr
| expr SHIFT_RIGHT expr
| expr '+' expr
| expr '-' expr
| expr '*' expr
| expr '/' expr
| expr '|' expr
| expr '^' expr
| expr '' expr
| expr '%' expr
| expr '+' INTERVAL_SYM expr interval
| expr '-' INTERVAL_SYM expr interval
simple_expr:
simple_ident
| literal
| '@' ident_or_text SET_VAR expr
| '@' ident_or_text
| '@' '@' opt_var_ident_type ident_or_text
| sum_expr
| '-' expr %prec NEG
| '~' expr %prec NEG
| NOT expr %prec NEG
| '!' expr %prec NEG
| '(' expr ')'
| '{' ident expr '}'
| MATCH ident_list_arg AGAINST '(' expr ')'
| MATCH ident_list_arg AGAINST '(' expr IN_SYM BOOLEAN_SYM
MODE_SYM ')'
| BINARY expr %prec NEG
Maybe this is more along the lines of what you're looking for...
snip all of the random functions
Regards,
Jeremy
--
Jeremy Cole
Technical Yahoo - MySQL (Database) Geek
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ENCODE DECODE
In my version of MySQL (4.1.9), your sample code generates an error: mysql select decode(encode(foo)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1 Apparently encode()/decode() take two args, at least on my version of MySQL: mysql select decode(encode(foo,bar),bar); +---+ | decode(encode(foo,bar),bar) | +---+ | foo | +---+ 1 row in set (0.00 sec) -Original Message- From: Jeff Smelser [mailto:[EMAIL PROTECTED] Sent: Friday, February 04, 2005 2:14 PM To: mysql@lists.mysql.com Subject: Re: ENCODE DECODE On Friday 04 February 2005 11:00 am, shaun thornburgh wrote: Apparently not! mysql SELECT DECODE ( CSV_DATA_ENC_FORENAME, test_password ) FROM DATA_TABLE_PID_1_DESC_137; Is it so hard to read directions? select decode(csv_data_enc_forename) from DATA_TABLE_PID_1_DESC_137; This assumes whatever is in that column, was encoded. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE DECODE
On Friday 04 February 2005 03:31 pm, John Trammell wrote: In my version of MySQL (4.1.9), your sample code generates an error: mysql select decode(encode(foo)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))' at line 1 your right, i was mistaken.. I use: select des_decrypt(des_encrypt(foo)); which works fine.. I am not sure what version he is using, but that should have worked. Jeff pgp7spCyw8ane.pgp Description: PGP signature
