Re: How do I use a dynamic filename for an into outfile statement
On Feb 29, 2012, at 10:43 AM, Ed Patterson wrote:
Be kind, I am by no means any type of DB expert.
I would like to eventually move this to a stored procedure but for now I am
using the \. to fire it off.
Here is what I have so far (generating a list of machines missing software)
select last_logon.host_name_short
from last_logon
left join mcafee on last_logon.host_name_short = last_logon.host_name_short
where mcafee.host_name_short is null
and last_logon.host_name_short like 'w%'
-- the above works
into outfile (select concat('Missing-',date_format(now(),'%Y%m%d%H%i'),'txt');
-- this line breaks it
The select concat() works from the command line
I can manually add the file name but would like to automate the process
Lastly, if it makes a difference, I don't use any graphical tools for DB
stuff. Inevitably someone says 'click here' :-)
Thanks for any help
Ed
-- create statement, assign to user variable
set @s = concat('
select last_logon.host_name_short
from last_logon
left join mcafee on last_logon.host_name_short = last_logon.host_name_short
where mcafee.host_name_short is null
and last_logon.host_name_short like \'w%\'
into outfile \'Missing-',date_format(now(),'%Y%m%d%H%i'),'.txt\'');
-- display so you can verify what it looks like
select @s;
-- prepare statement, execute it, discard it
prepare s from @s;
execute s;
deallocate prepare s;
Note: I added a '.' before 'txt' and a closing quote to the file name.
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html
--
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: does the number of column affect performance
On Feb 28, 2012, at 9:59 AM, Zheng Li wrote: for example there are 2 tables to save same data table A has 10 columns: a primary key column and 9 blob column table B has 2 columns : a primary key column and 1 blob column which includes all data in 2nd~10th columns of table A are there any differences in performance when selecting, inserting, updating, and deleting data. Sure. For example, with table A, you can select only those blob columns you're interested in. With B, you have to select all of them if you want *any* of them. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: delete all hosts using a wildcard
On Jan 14, 2012, at 5:23 PM, Tim Dunphy wrote: hello list, I have a number of hosts that I would like to delete using a wildcard (%) symbol. Here is the query I am using: mysql delete from mysql.user where user='%.summitnjhome.com'; Couple of things: * You want to compare your pattern to the host column, not user. * To match the pattern, use LIKE, not =. So: WHERE host LIKE '%.summitnjhome.com' But to see what rows your DELETE will affect, try this first: SELECT * FROM mysql.user WHERE host LIKE '%.summitnjhome.com'; Something else to consider: What if these accounts have privileges defined in the other grant tables, such as database-level privileges in the db table? http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like http://dev.mysql.com/doc/refman/5.5/en/grant-table-structure.html Query OK, 0 rows affected (0.00 sec) And I am attempting to delete all the hosts at the domain 'summitnjhome.com'... But as you can see I am unsuccessful: mysql select user,host from mysql.user; +--+-+ | user | host| +--+-+ | root | 127.0.0.1 | | repl | virtcent10.summitnjhome.com | | admin| virtcent11.summitnjhome.com | | repl | virtcent19.summitnjhome.com | | repl | virtcent23.summitnjhome.com | | repl | virtcent30.summitnjhome.com | +--+-+ I know I can delete them individually and this is what I am going to do. But I would like to use this as a learning opportunity to help me understand how the wildcard works. Thanks in advance.. Best regards, Tim -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: hide server-version at connect?
On Jan 9, 2012, at 7:27 PM, Reindl Harald wrote: Nessus/OpenVAS Test detects the exact server version _ NVT: MySQL Detection (OID: 1.3.6.1.4.1.25623.1.0.100152) Overview: MySQL, a open source database system is running at this host. MySQL Version '5.5.19-log' was detected on the remote host. _ is there any way to not disclosure the mysqld-version for a anonymous connected client? For the case you give below, no authentication has yet taken place, so you don't know whether the client is anonymous or not. But the version is needed for proper client-server negotiation to take place, I believe. Even if that were not true, any client, anonymous or not, can use SELECT @@version or SELECT VERSION() to get the version. [harry@srv-rhsoft:~]$ telnet localhost 3306 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. N 5.5.19-logs+%b?QYO]g��ke8'Xg~e\}!(mysql_native_password -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
On Jan 8, 2012, at 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? In DATE, DATETIME, and TIMESTAMP columns, you must specify the date part on year-month-day order. If you want to store a value in a different format, you must use some other data type such as VARCHAR. But then it won't be interpreted as a date. If you want to display a date from a DATE, etc. column in some other format, pass the value to DATE_FORMAT(). http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format If you want to reformat a date value in some other format to put it in year-month-day format so that you can store it in a DATE, etc. column, STR_TO_DATE() might be helpful. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE() can be useful, for example, when loading non year-month-day data into a table with LOAD DATA. You can use STR_TO_DATE() to reformat the values on the fly. LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t (name,@date,value) SET date = STR_TO_DATE(@date,'%m/%d/%y'); -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: 'myisam_use_mmap' unstable like hell
On Dec 15, 2011, at 12:02 PM, Reindl Harald wrote: this is NOT a memory issue 'myisam_use_mmap' in mysqld is buggy since a long time http://bugs.mysql.com/bug.php?id=48726 This is fixed in 5.1.61, 5.5.20, 5.6.5: http://dev.mysql.com/doc/refman/5.6/en/news-5-6-5.html we are speaking of a HP ProLiant DL 380G7 in a VMware-Cluster with 36 GB ECC-RAM while there are machines using InnoDB with 'large-pages' and some GB buffer_pool_size on the same host and not about some customer hardware Am 15.12.2011 18:22, schrieb Andrés Tello: When I had memory issues, with something relatively stable, mostly is due faulty ram... Can you use or less ram or change fisically the ram? On Thu, Dec 15, 2011 at 2:23 AM, Reindl Harald h.rei...@thelounge.netwrote: Am 15.12.2011 08:47, schrieb Rob Wultsch: To be brutally honest, if you want stability you should not be using MyISAM this is bullshit without 'myisam_use_mmap' i never saw mysqld crashing in the past 10 years, independent of the storage engine much less a not particularly commonly used feature. mmap is not rocket science, so i do not understnd why this is not properly debugged and DEFAULT on -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: error log rotation problem
On Aug 11, 2011, at 2:30 PM, Keith Murphy wrote: Hey everyone, I have run across something that has me stumped. I have some systems that have very large error logs because we haven't moved from statement-based to mixed-based replication yet so they get a lot of warnings logged. I need to rotate the error logs and have started looking at it doing so. The problem is that on one system a normal course of action works perfectly, but on anther it does not. And these systems were installed from the same RPM packages (5.1.50 -- downloaded from the MySQL website). Here is what I do: log in with mysql client and 'flush logs' OR mysqladmin --flush-log It should rename the old log file to mysqld.log-old and start a new mysqld.log file. On one system it works perfectly On the other...nothing. I tried moving the error log (mv /var/log/mysqld/mysqld.log /var/log/mysqld.log.old) and then issuing the flush logs command...it stays writing to the old file and never makes a new one. If I were to restart mysqld it would solve the problem but this is a production system and that isn't very practical. These systems are very similar. my.cnfs have been checked for differences. I searched the interwebs and specifically bugs.mysql.com for something similar. Not finding anything. I would appreciate any ideas! There was a change to log flushing that affects the error log in 5.1.51/5.5.7. It might be the cause of what you're seeing. http://dev.mysql.com/doc/refman/5.1/en/news-5-1-51.html http://dev.mysql.com/doc/refman/5.5/en/news-5-5-7.html Incompatible Change: Previously, if you flushed the logs using FLUSH LOGS or mysqladmin flush-logs andmysqld was writing the error log to a file (for example, if it was started with the --log-error option), it renamed the current log file with the suffix -old, then created a new empty log file. This had the problem that a second log-flushing operation thus caused the original error log file to be lost unless you saved it under a different name. For example, you could use the following commands to save the file: shell mysqladmin flush-logs shell mv host_name.err-old backup-directory To avoid the preceding file-loss problem, renaming no longer occurs. The server merely closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands: shell mv host_name.err host_name.err-old shell mysqladmin flush-logs shell mv host_name.err-old backup-directory (Bug #29751) See also Bug #56821. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Error Unknown column in 'where clause'
Some discussion of causes for this is at: http://dev.mysql.com/doc/refman/5.0/en/join.html Look for the part beginning: Join Processing Changes in MySQL 5.0.12 On Nov 16, 2010, at 10:09 AM, Tompkins Neil wrote: Hi I've the following query : SELECT players_bids.players_bids_id, players_bids.players_id, players_bids.bid_date, players_bids.bid_type, players_bids.bid_value, (SELECT SUM(IF(home_users_id = players_bids.users_id_from,home_manager_points,away_manager_points)) FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from OR away_users_id = players_bids.users_id_from) AND worlds_id = 1) AS manager_points, players_bids.users_id_from, (SELECT ROUND((SUM(won_home)+SUM(won_away))/COUNT(*)*100,0) FROM (SELECT IF(home_goals away_goals, 1, 0) AS won_home ,0 AS won_away FROM fixtures_results WHERE (home_users_id = players_bids.users_id_from) AND status = 'approved' UNION ALL SELECT 0 AS won_home ,IF(away_goals home_goals, 1, 0) as won_away FROM fixtures_results WHERE away_users_id = players_bids.users_id_from AND status = 'approved') s1) AS wins, players_bids.users_id_to, players_bids.worlds_id, players_bids.seasons_id, players_master.first_name, players_master.second_name, players_master.known_as, players_master.estimated_value, players_master.rating, players_master.positions_id, players.games_played, players_bids.teams_id_from, players_bids.teams_id_to, teams_master_from.team_name AS team_name_from, teams_master_to.team_name AS team_name_to FROM players_bids INNER JOIN players ON players_bids.players_id = players.players_id INNER JOIN players_master ON players_bids.players_id = players_master.players_id INNER JOIN teams_master teams_master_from ON players_bids.teams_id_from = teams_master_from.teams_id INNER JOIN teams_master teams_master_to ON players_bids.teams_id_to = teams_master_to.teams_id WHERE players_bids.bid_status = 'accepted' AND players_bids.players_id = 279 AND players_bids.worlds_id = 1 ORDER BY players_bids.bid_type ASC, players_bids.bid_value DESC, manager_points DESC, players_bids.bid_date ASC but the problem I have is that when referencing players_bids.users_id_from within the UNION I get back the following error : Error Code: 1054 Unknown column 'players_bids.users_id_from' in 'where clause' Any ideas how to overcome this problem ? Cheers Neil -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SQL book recommendation?
On Oct 26, 2010, at 6:31 AM, Philip Riebold wrote: On 26 Oct 2010, at 11:49, MikeB wrote: I'm finding the MySQL online manuals hard going in figuring out how to construct SQL queries. Can anyone perhaps recommend a good book that can shed light on the subject? Thanks. The book I've been using is 'MySQL, The definitive guide to using, programming, and administering MySQL 4.1 and 5.0' ISBN 0-672-32673-6 (there may be a more recent version). If that's my book, it sounds like the third edition. The fourth edition is more recent. http://www.kitebird.com/mysql-book/ Well written, with a general introduction to SQL and (from my POV) very good sections on writing MySQL with C and PHP -- TTFN. Philip Riebold, p.rieb...@ucl.ac.uk /\ Media Services\ / University College London X ASCII Ribbon Campaign Windeyer Building, 46 Cleveland Street/ \ Against HTML Mail London, W1T 4JF +44 (0)20 7679 9259 (direct), 09259 (internal) -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Is conversion required?
On Sep 29, 2010, at 5:15 PM, Paul Halliday wrote: I just converted (reinstalled) a FreeBSD system from i386 to amd64. Of course; I missed the memo. I have been struggling to get everything back online. I just finished exporting a few Gigs of RRD's to XML so that I could use them :| My question: I was s/rushing/stupid so I just moved /var/mysql to a partition (i386) and reinstalled. Can I just copy this back or does some magic need to happen first? If you're talking about the data, I wouldn't expect this change to cause issues, unless perhaps you're also updating to a different version of MySQL. That could be a problem, depending on how different the old and new versions are. Consult the upgrading section of the manual to see. http://dev.mysql.com/doc/refman/5.1/en/upgrading.html -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SHA1 returns binary value
On Sep 17, 2010, at 7:08 AM, Tompkins Neil wrote:
Hi
Why when I run the command (MySQL 5.1) SELECT SHA1('abc'); is it returned as
a binary value and not a string value ?
If you mean, why is it a binary rather than a nonbinary (character) string,
that's true of most of the encryption or compression functions in 5.1. This was
changed
in 5.5. http://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html says:
Many encryption and compression functions return strings for which the result
might contain arbitrary byte values. If you want to store these results, use a
column with a VARBINARY or BLOB binary string data type. This will avoid
potential problems with trailing space removal or character set conversion that
would change data values, such as may occur if you use a nonbinary string data
type (CHAR,VARCHAR, TEXT).
Some encryption functions return strings of ASCII characters: MD5(),
OLD_PASSWORD(), PASSWORD(), SHA(),SHA1(). As of MySQL 5.5.3, their return value
is a nonbinary string that has a character set and collation determined by the
character_set_connection and collation_connection system variables. Before
5.5.3, these functions return binary strings. The same change was made for
SHA2() in MySQL 5.5.6.
--
Paul DuBois
Oracle Corporation / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Sequence of Execution in Non-Transactional Script
On Aug 30, 2010, at 12:09 PM, real...@areality.co.uk wrote: Hello, I'm working on a very simple stock tracking system on unix with MySQL 5 and PHP. Working under the assumption that all my updates would be executed sequentially in the order they appear in the PHP script I perform these operations: $sql=UPDATE shop_product SET working_stocklevel=working_stocklevel+.$_SESSION['prod'][$product_id]. WHERE id=$product_id; $result = mysql_query ($sql) or die(Error=.mysql_error().BR.$sql.br); Then I record this activity into a seperate journal table using a function call Then $sql=UPDATE shop_product SET working_stocklevel=working_stocklevel-.$newamount. WHERE id=$product_id; $result = mysql_query ($sql) or die(Error=.mysql_error().BR.$sql.br); My journal records that occasionally the second command is executed before the first - throwing all the figures out. Is this possible? My tables are MyISAM, indexed on ID and not particularly large (about 3000 rows). I knopw this probably needs to be transactional, but I thought updates would auto-commit, and as such be sequential. Can anyone give any advice? If the two UPDATE statements are issued in sequence within the same session (within the same connection), they should execute in the same order you issue them. Particularly since you are waiting for the result of the first before you proceed to the second. I suggest that when you observe an out of order entry in your journal, you compare it with the MySQL server's general query log or binary log. The general query log will show the order in which the server receives the statements. The binary log will show the order in which they finish executing. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Importing User credentials from mysql.sql file???
On Aug 2, 2010, at 3:57 PM, Nunzio Daveri wrote: Hello Gurus, I just upgraded several MySQL 4.1 to 5.1 versions and also wanted to know how to extract the user name, password and credentials from the mysql.sql file (around 22 of them per server - have 8 servers total)? The contract admin emailed me a sql file which is a dump of the default mysql database from the 4.1 version and I am trying to see if I can just grep out of the mysql.sql file the INSERT INTO... from the .sql file and import that into the 5.1. When I tried it on our test box it keeps on saying: ERROR 1136 - Column count doesn't match value count at row 2? Can someone please tell me how I can extract the data out of the 4.1 mysql.sql file and inject it into the 5.1 version please? Ask the admin to re-dump the data, this time using mysqldump --complete-insert so that the INSERT statements include the column names. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: FLUSH LOCAL LOGS
On Jun 9, 2010, at 11:59 AM, Darvin Denmian wrote: Hello ! Is there some diference between : FLUSH LOCAL LOGS and FLUSH LOGS ? Yes. http://dev.mysql.com/doc/refman/5.1/en/flush.html says: By default, FLUSH statements are written to the binary log so that they will be replicated to replication slaves. Logging can be suppressed with the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL. Note FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not written to the binary log in any case because they would cause problems if replicated to a slave. -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why UTF8 need 24bit in MySQL?
On Jun 7, 2010, at 11:44 AM, Warren Young wrote: On 6/7/2010 9:57 AM, Ryan Chan wrote: http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html Since MySQL only support BMP, so in fact 16 bit is needed actually? I imagine they were thinking they'd extend the support to full Unicode in the future and didn't want you to have to dump and reload your databases when that happened. The Unicode consortium has stated that Unicode will never require more than 21 bits per character[*], and 24 bits is the next even multiple of 8 up from that. [*] Why 21? Because that's the maximum number of bits you can express in 4 bytes with UTF-8 encoding. If Unicode were allowed to use all 2^32 code points as originally envisioned, it would require up to 6 bytes per character in UTF-8 encoding. This promise makes UTF-8 code easier to write and easier to future-proof without bad performance penalties. Supplemental Unicode characters (4-byte) are supported as of MySQL 5.5.3: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode.html http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: declare multiple 'Definer'
The syntax allows a single DEFINER clause. On Feb 22, 2010, at 7:45 AM, Steven Staples wrote: Well... let me finish... LOL (hit send some how...) I want to be able to have 2 different users access to a stored procedure... I've tried multiple ways to write it, and none of them seem to work. Does anyone here know how to do this? CREATE (definer=`use...@`%`, definer=`sstapl...@`localhost`) PROCEDURE `sstest`() . CREATE definer=`use...@`%` OR definer=`sstapl...@`localhost` PROCEDURE `sstest`() . CREATE definer=`use...@`%` definer=`sstapl...@`localhost` PROCEDURE `sstest`() I can't think how it would be possible? Or is it? Steve -Original Message- From: Steven Staples [mailto:sstap...@mnsi.net] Sent: February 22, 2010 8:42 AM To: mysql@lists.mysql.com Subject: declare multiple 'Definer' Good morning! I have been struggleing with creating a store procedure, that will allow 2 users -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: set max_allowed_packet
On Feb 20, 2010, at 11:22 AM, Vikram A wrote: Hi experts, When I try to set the packet size to some other value, it is not getting updated. show variables; set max_allowed_packet = 2097152; set global max_allowed_packet = 2097152; it resulting, Query is ok, 0 rows afected (0.00 sec) That's okay. What does this query return: mysql select @@global.max_allowed_packet; +-+ | @@global.max_allowed_packet | +-+ | 2097152 | +-+ 1 row in set (0.06 sec) Can you suggest me how set this value to higher one. By default it is 1 MB. Thank you VIKRAM A -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with the World.sql sample database
On Feb 11, 2010, at 11:47 AM, kebede teferi wrote: Could any one lead me to a true link where I can download the world.sql sample database? Thanks. Go to: http://dev.mysql.com/doc/ Click on the Other Docs tab to get to: http://dev.mysql.com/doc/index-other.html You'll see an entry for the world database. Direct links are: http://downloads.mysql.com/docs/world.sql.gz http://downloads.mysql.com/docs/world.sql.zip -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Selecting Dates
On Jan 31, 2010, at 7:35 PM, ML wrote: Hi All, Switching from Oracle to MySQL, I seem to be having some difficulty selecting dates using between or even where = and = like: SELECT * FROM orders WHERE order_date BETWEEN='2010-01-01' AND '2010-01-30' ORDER BY order_date; No = after BETWEEN. or SELECT * FROM orders WHERE order_date ='2010-01-01' AND = '2010-01-30' ORDER BY order_date; Need order_date =, not just =. Neither of these work. What am I missing? -ML -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: auto_increment without primary key in innodb?
The requirement is that it be indexed. The index need not be a primary key. mysql create table t (i int not null auto_increment, index(i)) engine innodb; Query OK, 0 rows affected (0.45 sec) On Jan 25, 2010, at 9:39 AM, Yang Zhang wrote: Right, I saw the docs. I'm fine with creating an index on it, but the only way I've successfully created a table with auto_increment is by making it a primary key. And I still don't understand why this requirement is there in the first place. On Mon, Jan 25, 2010 at 10:32 AM, Tom Worster f...@thefsb.org wrote: it's not an innodb thing: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. -Original Message- From: Yang Zhang yanghates...@gmail.com Sent: Monday, January 25, 2010 10:21am To: mysql@lists.mysql.com Subject: auto_increment without primary key in innodb? In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=...@thefsb.org -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: REGEXP and unicode weirdness
On Jan 21, 2010, at 9:27 AM, John Campbell wrote: I want to find rows that contain a word that matches a term, accent insensitive: I am using utf8-general collation everywhere. attempt 1: SELECT * FROM t WHERE txt LIKE '%que%' Matches que qué, but also matches 'queue' attempt 1.5: SELECT * FROM t WHERE txt LIKE '% que %' OR LIKE 'que %' OR LIKE '% que'; Almost, but misses que! or 'que...' attempt2: SELECT * FROM t WHERE txt REGEXP '[[::]]que[[::]]' Matches que, not queue, but doesn't match qué. attempt3 SELECT * FROM t WHERE txt REGEXP '[[::]]q[uùúûüũūŭůűųǔǖǘǚǜ][eèéêëēĕėęě][[::]]' Matches que, queue, qué. (I have no idea why this matches queue, but the Regex behavior is bizarre with unicode.) Does anyone know why the final regex acts weird? It there a good solution? http://dev.mysql.com/doc/refman/5.1/en/regexp.html: Warning The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
On Jan 13, 2010, at 1:28 PM, Lawrence Sorrillo wrote: The issue is that in theory this should work given the facts announced by MySQL regarding binary logging and replication. I can certainly do it the way you propose, but to my mind I should also be able to do it using the fact that both machines are fully synced and hence at that point I should be able to to local respective dumps and restores and still be in sync. Anyone knows anything special about position 106? It seems to be the very initial position in MySQL 5.1 servers? It's not. 4 is still the initial position, as shown by the at 4 in your mysqlbinlog output below. The 106 that you observe is the position *after* the server writes the initial event to the binary log. It writes this event immediately after opening the file, even before executing any statements. If you want the gory details: This event is the format description event that identifies in the binary log file the server version and other information. See http://forge.mysql.com/wiki/MySQL_Internals_Binary_Log#Binary_Log_Versions if you have a high tolerance for pain. :-) mysql show master status; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | X-bin.01 | 106 | | | +---+--+--+--+ 1 row in set (0.00 sec) r...@:/usr/local/mysql/data ] /usr/local/mysql/bin/mysqlbinlog mssdb2-bin.01 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #100113 13:50:40 server id 5 end_log_pos 106 Start: binlog v 4, server v 5.1.42-log created 100113 13:50:40 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' ABZOSw8FZgAAAGoBAAQANS4xLjQyLWxvZwAA Fk5LEzgNAAgAEgAEBAQEEgAAUwAEGggICAgC '/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET completion_ty...@old_completion_type*/; r...@:/usr/local/mysql/data ] ~Lawrence Tom Worster wrote: Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, Lawrence Sorrillo sorri...@jlab.org wrote: This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, Lawrence Sorrillo sorri...@jlab.org wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=paul.dub...@sun.com -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
On Jan 12, 2010, at 12:36 PM, Lawrence Sorrillo wrote: Hi: I want to ensure that right after the reload that the same data is present in both the master and the slave. They are in perfect sync. Then I think its safe to consider starting binary logging and replication etc. And after these are started, changes can start? And in setting up replication in this manner I would not use the CHANGE MASTER... I will just master-host=xxx.xxx.xxx.xxx master-connect-retry=60 master-user=auser master-password=apassword in the my.cnf file and restart the slave server. From there it should start reading the binary logs and committing changes properly. Is this correct? You're upgrading to MySQL 5.1, for which several of those options no longer have any effect. Better to use CHANGE MASTER. See: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: START TRANSACTION COMMIT ROLLBACK
If all the tables are InnoDB, XA isn't needed. It doesn't matter whether all tables are in the same database. On Oct 28, 2009, at 5:48 AM, Martijn Tonies wrote: Ah, works for InnoDB I see. Nice. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com Looks to me we should use XA transaction syntax instead. Check this: http://dev.mysql.com/doc/refman/5.0/en/xa.html Thanks, YY 2009/10/28 Martijn Tonies m.ton...@upscene.com Michael, Does MySQL support multi-db transactions? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com That is correct. Many db interfaces off programmatic abstractions of these facilities, but you may certainly just issue the statments. START TRANSACTION INSERT that UPDATE that on success: COMMIT on error: ROLLBACK - michael dykman On Wed, Oct 28, 2009 at 12:07 AM, Mosaed AlZamil mosza...@gmail.com wrote: Hello Everyone, I am a newbie using innodb. How can I implement START TRANSACTION COMMIT ROLLBACK when I need to update two tables that are located in two different databases. Would a single START TRANSACTION be sufficient ? Any help would be appreciated. TIA Mos -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Getting mySQL to ignore case sensitivity of field names
On Aug 27, 2009, at 5:11 PM, Adrian Aitken wrote: Hi Scott, it's not the values I have a problem with, it's the fieldnames themselves. As an example the mysql.user table has 'Host' but when I do an update setting 'host' to a value it fails to update. I have to enter 'Host'. The mysql.com link seems to only talk about field values. That should not happen. Column names are not case sensitive in MySQL. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html: Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Can we see your query? Regards Adrian - Original Message - From: Scott Haneda To: Adrian Aitken Cc: mysql@lists.mysql.com Sent: Thursday, August 27, 2009 11:04 PM Subject: Re: Getting mySQL to ignore case sensitivity of field names http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html You need to set the field format to a non binary one, and case insensitive will be the default. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Renaming a Database
On Aug 17, 2009, at 4:22 PM, Matt Neimeyer wrote: I know the best way to rename a database is to use mysqldump, extract the database and then reload to the new database. (At least based on what I can find in the 12.1.32. RENAME DATABASE Syntax section of the documentation) That said... Is there anything wrong (dangerous, disasterous, etc) with stopping the MySQL service and renaming the folder in the MySQL data folder? By my logic (if I'm right) this should preserve any permissions on the folder and since the service is stopped it should simply find the new instance. If you have InnoDB tables, there will be a problem. InnoDB maintains the database name in the shared tablespace, and it will no longer be able to find those tables. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why is MySQL using /tmp?
This might help: http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html On Jun 11, 2009, at 12:51 AM, Mike Spreitzer wrote: I find my MySQL Community Edition 5.1.34 server running out of space on /tmp (which is indeed small). Why is it using /tmp? How much free space do I need on /tmp? Can/should I make the server use a different location instead of /tmp? Thanks, Mike Spreitzer -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to execute prepare statement when the placeholder is a specific table name?
On Feb 14, 2009, at 8:00 PM, Moon's Father wrote:
Thanks for your fast reply.
Do you know MySQL will support this feature in the future?
It doesn't need to. Baron indicated how you can solve this problem.
If you look at prepared statements in general (not just for MySQL),
placeholders are for data values, not identifiers.
On Sun, Feb 15, 2009 at 12:59 AM, Baron Schwartz ba...@xaprb.com
wrote:
Hello,
On Sat, Feb 14, 2009 at 11:15 AM, Moon's Father
yueliangdao0...@gmail.com wrote:
Here is my routine.
DELIMITER $$
CREATE
PROCEDURE `t_girl`.`sp_join2`()
BEGIN
set @a = 'a';
set @b = 'g';
set @stmt = concat('select * from ?,? where a.id = g.id');
prepare s1 from @stmt;
execute s1;
drop prepare s1;
END$$
DELIMITER ;
But it didn't work for me.
So what I want to know is how to table name when there're a
placeholder
within sproc.
You can't use placeholders for identifiers, only for literal values.
So you will need to use CONCAT() to build the string with the
identifiers already in it, before you PREPARE.
--
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
--
Paul DuBois
Sun Microsystems / MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql full tutorial download or any book
On Feb 14, 2009, at 5:49 PM, mos wrote: At 05:20 PM 2/14/2009, mos wrote: At 04:07 AM 2/14/2009, Andy Shellam wrote: To be honest, I got started straight from the MySQL manual. There is a tutorial section in the manual as well, and it's also available to download as a PDF. Links: Manual (English): http://dev.mysql.com/doc/refman/5.1/en/index.html Manual (English PDF US Letter): http://downloads.mysql.com/docs/refman-5.1-en.pdf Tutorial (English): http://dev.mysql.com/doc/refman/5.1/en/tutorial.html Substitute 5.1 with whatever version you're running. HTH, Andy Andy, I agree, the manual is well written. Ironically, the hyperlinks in the PDF file do NOT WORK so it is difficult to navigate. Mike Oops, my bad. I was using Foxit v2.x reader and that was the problem. I've upgraded to Foxit v3 and the hyperlinks work fine in the MySQL manual. Whew! I was about to panic. :-) -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Installing MySQL from source
On Feb 13, 2009, at 8:52 AM, Joerg Bruehe wrote: Michel, michel wrote: From the different documentation I was reading that after going to the root directory I should execute /.configure; but there is no configure file in the root source directory, just configure.in there is a difference between Installing MySQL from the current development source tree and Installing MySQL from the source tarball of some released version. In the development source tree, we have files which need to be processed by the GNU autotools (autoconf, automake, aclocal), whereas a source tarball is created from the result of these tools. The manual should cover that, but I don't have a specific URL handy. It's http://dev.mysql.com/doc/refman/5.1/en/installing-source-tree.html The development tree contains a file BUILD/autorun.sh which runs these tools. HTH, Jörg -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Install problem: log file issue
On Feb 12, 2009, at 6:28 PM, csego...@gmail.com wrote: Andy, Michael, and Walter - thank you! Adding a [mysqld_safe] group to my.cnf gets me further but the start still fails. The good thing is that the failure is no longer due to the inability to write the log file. The [mysqld_safe] section of my.cnf reads: [mysqld_safe] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M datadir = /app/mysql/data #data_file_path = /app/mysql/data #log = /app/mysql/log/mysqld.log log-bin = /app/mysql/log/mysql-bin.log log-output = FILE general_log = 1 general_log_file = /app/mysql/log/msyql_general.log socket = /app/mysql/var/mysql.sock log-error = /app/mysql/log/error.log Luckily, I now have an error log which reads: 090212 18:24:04 mysqld_safe Starting mysqld daemon with databases from /app/mysql/data InnoDB: Log scan progressed past the checkpoint lsn 0 39548 090212 18:24:04 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 0 46409 090212 18:24:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 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 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 090212 18:24:04 InnoDB: Started; log sequence number 0 46409 090212 18:24:04 [ERROR] /app/mysql/libexec/mysqld: Can't create/ write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 090212 18:24:04 [ERROR] Can't start server: can't create PID file: No such file or directory 090212 18:24:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended Now I need to figure out how to point PID file creation the appropriate directory. Sounds like you want: pid-file = /app/mysql/log/mysql.pid http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_pid-file -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to get Error Number and Error Message 2nd Try
On Feb 10, 2009, at 11:26 PM, Al wrote: I know that all of the prgramming interfaces have the ability to issue a Function Call to get error number for the most recently invoked MySQL function and the error message for the most recently invoked MySQL function such as in C using *mysql_errno() etc. BUT * Surely there is a way to get the SAME info via a SQL statment or function I know one can do a ** select @@error_count as errorcount; to get the COUNT of errors, Surely there is SOMETHING similar to get the latest ERROR NUMBER and latest ERROR MESSAGE, SHOW WARNINGS might help. http://dev.mysql.com/doc/refman/5.1/en/show-warnings.html -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are dates stored as String? Or Integer?
On Jan 27, 2009, at 11:31 PM, mos wrote: Are date columns stored as String or Integer in a MyISAM table? I am trying to squeeze more speed from my application and a date column is used in most of the indexes for my tables. I'm wondering if changing the Date data type to an Integer is going to speed the queries up. I'm using Delphi and internally it represents dates as float so using integers will speed up the Delphi code. But the main slow down I have is with executing the queries. The dates are used in the indexes, sorting, and in a few table joins. So is there a speed advantage of switching the dates to integer? Dates are stored numerically. See: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html Beginning with The storage requirements shown in the table arise from the way that MySQL represents temporal values ... -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Setting the value for the EDITOR variable
On Jan 27, 2009, at 5:44 PM, Xristos Karvouneas wrote: Dear All, I would like to be able to do that from the cnf file, as I want to have a different editor for MySQL commands than the one I use when writing shell scripts. When I put the following in the .my.cnf file, [shell] EDITOR=/usr/local/bin/pico export EDITOR I get no error messages when mysql starts up, but the value of the EDITOR variable does not change. Is there any way I can achieve that from the cnf file? No. MySQL option files are for setting MySQL program options, not environment variables. -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Book: MySQL, Fourth Edition, now available
The book MySQL, Fourth Edition (aka Doorstop IV) has been published. More information is available at the book's Web site: http://www.kitebird.com/mysql-book/ -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reorder records in database
On May 15, 2008, at 1:38 PM, afan pasalic wrote: hi, I have a table with tasks. column status could be 1 (means todo) and 0 (meas done). also,have column order_no to sort tasks by priorities. once in a while order_no is not in order, e.g 1, 2, 3, 5, 6, 8, 11, 12, 13, 19, 20,... (some numbers are missing). is there built in function to reset order_no or I have to create php script for it? Why do you care if there are gaps? Do they really matter? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
On May 15, 2008, at 4:30 AM, rustam ershtukaev wrote: I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. Where does postcode come from? Your SELECT statement appears to have no relation to the row to be inserted. It also appear that it will always set v_postcode to 1000 if the departement table contains *any* rows with a postcode of 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Floor Decimal Math
On May 16, 2008, at 2:42 AM, Adam de Zoete wrote: Thanks for your responses, i thought it was a float problem so i was trying to CAST as a DECIMAL to fix it. It turns out (and the manual does not document this) that casting as decimals doesn't actually work in mysql 4.1.20. ROUND() is needed instead. DECIMAL is not listed for CAST() in the 4.1 manual because it is not supported in 4.1. DECIMAL is listed for CAST() in the 5.0 as supported from 5.0.8 on. mysql select ROUND(11.11-FLOOR(11.11),2)=0.11; +---+ | ROUND(11.11-FLOOR(11.11),2)=0.11 | +---+ | 1 | +---+ Thanks for all your help, Adam Jerry Schwartz wrote: Don't feel bad, many an experience programmer has been bitten by this. The problem is that many decimal fractions do not have exact representations as binary fractions. .01 is an example of this. I'm not sure how MySQL does arithmetic internally, but (11.11 - 11) is just a hair under .11: mysql SELECT (11.11 - 11) = .10; +-+ | (11.11 - 11) = .10 | +-+ | 1 | +-+ You need to allow for a slight fudge factor. This is even a problem at the hardware design level. The best way to handle this is to make sure all of your operators are type DECIMAL. You might have to cast them. Apparently MySQL 5+ introduced some algorithms that helped with these problems, although not with yours. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT doesn't work with NULL?
On May 14, 2008, at 10:53 AM, Afan Pasalic wrote:
hi,
I have query
SELECT CONCAT(r.first_name, ' ', r.last_name, '\n', r.organization,
'\n', r.title, '\n', a.address1, '\n', a.city, ', ', a.state, ' ',
a.zip, '\n', r.email)
FROM registrants r, addresses a
WHERE r.reg_id=121
if any of columns has value (e.g. title) NULL, I'll get as result 0
records.
If query doesn't have concat() - it works fine.
Why is that?
That's how CONCAT() is documented to work:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat
CONCAT() returns NULL if any argument is NULL.
You might want to try CONCAT_WS('', ...) instead. CONCAT_WS() isn't
fazed
by NULL values the same way that CONCAT() is. :-)
mysql select CONCAT('a',NULL,'b');
+--+
| CONCAT('a',NULL,'b') |
+--+
| NULL |
+--+
1 row in set (0.07 sec)
mysql select CONCAT_WS('','a',NULL,'b');
++
| CONCAT_WS('','a',NULL,'b') |
++
| ab |
++
1 row in set (0.00 sec)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
On May 9, 2008, at 12:17 AM, Sebastian Mendel wrote: Paul DuBois schrieb: On May 7, 2008, at 4:36 AM, Sebastian Mendel wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables How would a persistent connection save any of that? Suppose the script that previously used the connection reset any or all of those things? i do not fully understand, why should the script reset these things? It doesn't have to reset any of those things, but you are *assuming* that no other script *will* reset any of those things. As far as I understand what you want to do, that's an invalid assumption. How do you know what some other script might want to do? Perhaps it wants a different character set than you do. i do not talk of a global persistent connection, every session should have it's own persistent connection (if requested). A session is a connection. When the connection ends, the session ends. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: pls help clarify dox: InnoDB Consistent Non-Locking Read behavior
On May 9, 2008, at 6:58 AM, Chris Pirazzi wrote: Hello, I _thought_ I knew how InnoDB worked, but due to a recent mysql doc change, I am no longer sure--the change made the dox significantly less clear, and potentially code-breaking. Please can someone tell me the real behavior of InnoDB in the following case, and ideally clarify the dox too... The question comes up in the first paragraph of 13.5.10.4. Consistent Non-Locking Read: http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. Note that the exception to the rule causes the following anomaly: if you update some rows in a table, a SELECT will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the same table, the anomaly means that you may see the table in a state that never existed in the database. The unclear wording is if you update some rows in a table, a SELECT will see the latest version of the updated rows, but it might also see older versions of any rows What does the author mean by any rows? Do you mean that when you do a SELECT, you may get back a result for your modified row, AND you may ALSO get back a result for an older version of the SAME row? This is very very important as it affects how we can use non-locking read at the lowest level of our code. You may wonder why I suspect this case...it's all because of what the text USED to say: (change made by user paul on 2007-08-23 16:38:39 +0200 (Thu, 23 Aug 2007) http://lists.mysql.com/commits/32967?f=plain ) -see the latest version of the updated rows, while it sees the -old version of other rows. If other users simultaneously update -the same table, the anomaly means that you may see the table in -a state that never existed in the database. +see the latest version of the updated rows, but it might also +see older versions of any rows. If other users simultaneously +update the same table, the anomaly means that you may see the +table in a state that never existed in the database. the old wording the old versions of OTHER rows was crystal clear: you will only see one copy of your new row, and it will be your new copy. the new wording is unclear. but the new wording makes me wonder if InnoDB could return multiple copies of the rows I have modified. Can someone clarify the actual InnoDB behavior? Could someone suggest a clearer wording for the dox that 1) expresses the actual InnoDB behavior 2) covers whatever case Paul was trying to cover when he made that change? Thanks for your time! Hopefully we can clarify this for all mysql users! The background for this change is Bug#30184: http://bugs.mysql.com/bug.php?id=30184 -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL purge logs
On May 11, 2008, at 11:39 AM, Kaushal Shriyan wrote: Hi I am referring to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html whats the exact syntax to purge this MySQL Binary Logs It's as indicated on the page that you reference. For example, to purge all logs before .000698, use PURGE MASTER LOGS TO 'host1-bin.000698'; To purge all logs older than May 7, use PURGE MASTER LOGS BEFORE '2008-05-07 00:00:00'; -rw-rw 1 mysql 701 1.1G May 5 07:39 host1-bin.000681 -rw-rw 1 mysql 701 1.1G May 5 09:09 host1-bin.000682 -rw-rw 1 mysql 701 1.1G May 5 10:49 host1-bin.000683 -rw-rw 1 mysql 701 1.1G May 5 20:24 host1-bin.000684 -rw-rw 1 mysql 701 1.1G May 5 21:47 host1-bin.000685 -rw-rw 1 mysql 701 1.1G May 5 23:31 host1-bin.000686 -rw-rw 1 mysql 701 1.1G May 6 01:40 host1-bin.000687 -rw-rw 1 mysql 701 1.1G May 6 04:26 host1-bin.000688 -rw-rw 1 mysql 701 1.1G May 6 07:00 host1-bin.000689 -rw-rw 1 mysql 701 1.1G May 6 08:58 host1-bin.000690 -rw-rw 1 mysql 701 1.1G May 6 17:54 host1-bin.000691 -rw-rw 1 mysql 701 1.1G May 6 21:01 host1-bin.000692 -rw-rw 1 mysql 701 1.1G May 6 22:46 host1-bin.000693 -rw-rw 1 mysql 701 1.1G May 7 00:56 host1-bin.000694 -rw-rw 1 mysql 701 1.1G May 7 02:52 host1-bin.000695 -rw-rw 1 mysql 701 1.1G May 7 05:44 host1-bin.000696 -rw-rw 1 mysql 701 1.1G May 7 07:28 host1-bin.000697 -rw-rw 1 mysql 701 1.1G May 7 09:09 host1-bin.000698 -rw-rw 1 mysql 701 1.1G May 7 18:40 host1-bin.000699 -rw-rw 1 mysql 701 1.1G May 7 21:00 host1-bin.000700 -rw-rw 1 mysql 701 1.1G May 7 22:35 host1-bin.000701 -rw-rw 1 mysql 701 1.1G May 8 00:40 host1-bin.000702 -rw-rw 1 mysql 701 1.1G May 8 03:20 host1-bin.000703 -rw-rw 1 mysql 701 1.1G May 8 05:53 host1-bin.000704 -rw-rw 1 mysql 701 1.1G May 8 07:59 host1-bin.000705 -rw-rw 1 mysql 701 1.1G May 8 09:24 host1-bin.000706 -rw-rw 1 mysql 701 1.1G May 8 18:36 host1-bin.000707 -rw-rw 1 mysql 701 1.1G May 8 21:21 host1-bin.000708 -rw-rw 1 mysql 701 1.1G May 8 22:57 host1-bin.000709 -rw-rw 1 mysql 701 1.1G May 9 01:25 host1-bin.000710 -rw-rw 1 mysql 701 1.1G May 9 03:41 host1-bin.000711 -rw-rw 1 mysql 701 1.1G May 9 06:05 host1-bin.000712 -rw-rw 1 mysql 701 1.1G May 9 07:50 host1-bin.000713 -rw-rw 1 mysql 701 1.1G May 9 09:29 host1-bin.000714 -rw-rw 1 mysql 701 1.1G May 9 19:20 host1-bin.000715 -rw-rw 1 mysql 701 1.1G May 9 21:46 host1-bin.000716 Thanks and Regards Kaushal -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
On May 7, 2008, at 4:36 AM, Sebastian Mendel wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables How would a persistent connection save any of that? Suppose the script that previously used the connection reset any or all of those things? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: viewing passwords as root
At 12:49 PM -0400 4/17/08, Pam Astor wrote: Hi, Is there a way to view mysql user passwords once logged in as the MySQL root user in v 5.0.22? Or are they encrypted even from the MySQL toot user? It's one-way encryption. You cannot see them. http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_password -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, 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 SET
When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, 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 SET
At 8:49 AM -0700 4/16/08, Rob Wultsch wrote: On Wed, Apr 16, 2008 at 7:24 AM, Paul DuBois [EMAIL PROTECTED] wrote: When you create a table, you can specify a character set for a column. How can you tell what character set was used when the column was created? SHOW CREATE TABLE. If no character set is shown for the column, it uses the table default character set. Example: mysql create table t (c1 char(5) character set utf8, c2 char(5)); Query OK, 0 rows affected (0.16 sec) mysql show create table t\G *** 1. row *** Table: t Create Table: CREATE TABLE `t` ( `c1` char(5) CHARACTER SET utf8 DEFAULT NULL, `c2` char(5) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) The definition for c1 shows that utf8 is used The definition for c2 shows nothing, so the table character set (latin1) is used. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com Is there any reason that the information_schema would not be the preferred method of finding this information? mysql select table_collation from tables WHERE `table_name` = 'mytable' AND table_schema ='mydatabase'\G You can do that, too, unless your version of MySQL is older than 5.0. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.51a and SHOW ENGINES
At 2:57 PM -0400 4/16/08, Jim Winstead wrote: On Fri, Apr 11, 2008 at 08:54:26AM +0200, Martijn Tonies wrote: Hello Jim, On Thu, Apr 10, 2008 at 09:32:43AM +0200, Martijn Tonies wrote: It seems that SHOW ENGINES fails on MySQL 5.0.51a (community edition checked). Instead of returning the full data, the first two columns are cut off at 3 characters, while the comment column is cut off at 26 characters. sounds like you are using the wrong value for the length of a utf8 field, where the number of characters is being divided by the max character length. (10 / 3 = 3, 80 / 3 = 26) or it could be the server returning the wrong length. use mysql --column-type-info to see what it is returning. That doesn't work with 5.0 as far as I can tell. sorry, i think --column-type-info is new in 5.1. you can get the same effect using -T with 5.0, i believe. RIght: --column-type-info, -m Display result set metadata. This option was added in MySQL 5.1.14. (Before that, use --debug-info.) The -m short option was added in MySQL 5.1.21. --debug-info, -T Before MySQL 5.1.14, this option prints debugging information and memory and CPU usage statistics when the program exits, and also causes display of result set metadata during execution. As of MySQL 5.1.14, use --column-type-info to display result set metadata. http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create table
At 10:32 AM +0530 4/15/08, Krishna Chandra Prajapati wrote: Hi, I have created a table name group. CREATE TABLE `group` ( `group_id` int(11) NOT NULL, `group_name` varchar(128) NOT NULL, `date_created` datetime NOT NULL, `created_by` int(11) NOT NULL, `modified_by` int(11) default NULL, `status` char(1) NOT NULL default 'Y', PRIMARY KEY (`group_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; The table was successfully created without any errors. After that when i try to describe it. Its giving error. mysql desc group; 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 'group' at line 1 mysql drop table group; 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 'group' at line 1 Does the problem is with table name. group is a reserved word. See this page: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html The page also describes how to quote identifiers to avoid this problem. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about mysql procedures
At 2:15 PM -0700 4/11/08, Joshua D. Drake wrote: Hello, Can MySQL functions/stored procedures access database data? Yes, with some limitations. You will want to read this section to see whether what you want to do is restricted: http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html For example, in a stored function, you cannot modify a table if the table is referenced by the statement that invokes the function. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records to send to another table in another database
At 12:21 PM -0400 4/10/08, Larry Brown wrote: I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select records to send to another table in another database
At 1:09 PM -0400 4/10/08, Christoph Boget wrote: I have a slew of records that went to the wrong database. The tables have the same names and now I want to copy those records over to the correct database. Is there such a mechanism using the cli mysql application in Linux? For each corresponding table: INSERT INTO db1.mytable SELECT * FROM db2.mytable; And you can do that using the cli mysql app? Yes. You can do this in any interface that enables you to issue SQL statements. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We have just moved to a new web server where we are running mysql version: 5.0.45
At 1:13 PM +0300 4/9/08, Malka Cymbalista wrote:
We have just moved to a new web server where we are running mysql
version: 5.0.45
On the old machine, when we gave the following command:
update table1 set passwd = password('xx') where user_name=xx;
and then gave the following command,
SELECT user_name FROM alon_protein_passwd WHERE (user_name = xx
AND passwd=password('xx'))
we got the expected results.
Now when we do this, on the new machine, we get no result i.e. Empty set.
Any help will be appreciated.
The password hashing algorithm changed in MySQL 4.1. You can read about
it here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
This is likely the cause of the changes that you're observing. I
imagine that you'll either need to upgrade the passwords in your
table, or look for them using OLD_PASSWORD() rather than PASSWORD().
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indices in InnoDB/MySQL
At 10:01 AM -0700 4/1/08, Robert DiFalco wrote: I've been told that an index always contains the primary key. By who? Ask for proof. So if I have a LONG ID that is the primary key of table and create on index on LONG VALUE, the index on LONG VALUE will actually work as a typical compound index on ID,VALUE. My question is this, if I don't know that about MySQL and create my indices on purpose (i.e. not taking into account implementation details about MySQL) I would create the index explicitly on ID,VALUE. If I did that would I effectively have the same index as one on just VALUE or would there some how be duplicate data in the ID,VALUE index (i.e. ID, ID, VALUE)? I'm not exactly sure what you're asking, but if I understand correctly, the answer is no. You might find these sections of the manual helpful: http://dev.mysql.com/doc/refman/5.0/en/indexes.html http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security overrides in mysql.cnf
At 2:51 PM -0500 3/19/08, Brown, Charles wrote: I inherited a mysql server database. Stuff are not documented. My question is: Are there any security work-arounds in mysql. I have access to the cnf file. I need to get in and dump the database. I was told that the cnf file allows security over rides. Please help If you were told that, perhaps you could ask the person(s) who told you that what they meant and how to do it. :-) Information in other followups about --skip-grant-tables is useful, too. You can start the server that way, connect as root w/no password, issue a FLUSH PRIVILEGES statement to re-enable the grant tables (so that you can use CREATE USER, GRANT, etc.), and then set up the accounts the way you want (new root password and so forth). -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the limitaiton of table size
At 10:47 PM -0400 3/18/08, Sookhyun Yang wrote: Dear all, I have a question about the limitatin of table size. If I use the InnoDB engine, I don't have to worry that the total size of table is greater than the single filesize? You can create an InnoDB tablespace that spans multiple files, yes. InnoDB will use the aggregate file size as the tablespace size. But remember that multiple tables share the tablespace. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 mysqld instances
At 3:25 PM -0300 3/20/08, Edson Noboru Yamada wrote: On Thu, Mar 20, 2008 at 2:53 PM, Dan Nelson [EMAIL PROTECTED] wrote: -P is only used when connecting to non-localhost addresses. Try adding -h 127.0.0.1 to your commandline. It worked! Thank you! You can also force a TCP/IP connection, even for localhost, with --protocol=tcp http://dev.mysql.com/doc/refman/5.0/en/invoking-programs.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on March 13 - canceled
Today's MySQL University session: Checking Threading and Locking With Helgrind http://forge.mysql.com/wiki/Checking_Threading_and_Locking_With_Helgrind has been canceled and will be rescheduled for a later date. I apologize for the short notice. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql.time_zone_name
At 4:20 PM +0100 3/6/08, Andre Hübner wrote: Hi List, user wants to do: SELECT COUNT(*) FROM mysql.time_zone_name; problem ist that this table in mysql is empty. I dont know how to fill with data. I tried on testmachine with mysql_install_db but it leaves empty. Where to get sql-data to fill these tables in mysql-db? Please see this section of the manual: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: function/procedure error!
At 8:05 AM -0800 3/6/08, puntapari wrote: Hi! And returning a list of numbers? It can be and how? You cannot return a list. You could generate a result set in a procedure, but it's returned to the client, not the caller. That's why I suggested writing the numbers into a table. Then you can select from the table. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: function/procedure error!
At 9:10 AM -0800 3/6/08, puntapari wrote: Somethin like this? DELIMITER $$ DROP PROCEDURE IF EXISTS `cantidad` $$ CREATE PROCEDURE `cantidad` (in n int ) for i in 0..n loop insert into temp values(i); end loop; select * from temp; END $$ DELIMITER ; Not quite. This will still return the result set to the client. So, don't select from the table in the procedure. Use the procedure to populate the table, and then use your original query, with the originally proposed stored function reference replaced with a reference to the table that contains 0 ... n. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4 Grant command
At 4:12 PM -0800 3/5/08, Garris, Nicole wrote: OK, what is wrong with the following statement? MySQL 4.1 doesn't like my syntax ... mysql grant all privileges on *.* to [EMAIL PROTECTED] identified by 'myownpassword'; % needs quotes around it. I recommend always quoting the username and hostname: 'login3'@'%' -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is a schema? what is a database?
At 8:58 AM + 3/4/08, Thufir wrote: On Mon, 03 Mar 2008 14:20:58 -0500, Martin Gainty wrote: http://dev.mysql.com/doc/refman/5.0/en/schemata-table.html According to MYSQL doc: A schema is a database That contradicts the following claim (to my reading): A true fully (database, schema, and table) qualified query is exemplified as such: select * from database.schema.table http://en.wikipedia.org/wiki/ Comparison_of_relational_database_management_systems#Databases_vs_Schemas_.28terminology.29 What' I'm familiar with is: SELECT * FROM database.table; That's ok, that makes sense, this is how MySQL does it and is how I've been doing it. Some databases do it differently, apparently. Apparently MySQL lacks this feature, but what feature is it lacking? There's no equivalent to: SELECT * FROM database.schema.table; In MySQL, the two are equivalent. The keyword DATABASE or DATABASES can be replaced with SCHEMA or SCHEMAS wherever it appears. Examples: CREATE DATABASE = CREATE SCHEMA SHOW DATABASES = SHOW SCHEMAS -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursion
At 7:27 AM + 2/27/08, Thufir wrote: I'm reading SQL for dummies and one of the more interesting sections was on recursion. The example query was something like: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT UNION SELECT in.Source, out.Destination FROM ReachableFrom in, FLIGHT out WHERE in.Destination = out.Source ) SELECT * FROM ReachableFrom WHERE Source = Portland; I'm a bit thrown by the union. Can this be simplified to: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT ) SELECT * FROM ReachableFrom WHERE Source = Portland; MySQL does not have WITH RECURSIVE. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select with table name
At 12:04 AM + 2/17/08, Miguel Vaz wrote: Hi, I have a small issue that i can get my head around to solve: Is it possible to do a select from two tables using a union all (select * from t1 union all select * from t2), and have it display the table name in front of each row? mysql select 't1', t1.* from t1 union select 't2', t2.* from t2; ++--+ | t1 | i| ++--+ | t1 |1 | | t2 |2 | | t2 |3 | ++--+ -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: triggers sintax error with IF
Hi, im struggling with triggers but i can make this works because of bad sintax, i hope someone can give me a hand CREATE TRIGGER trigger_actualize AFTER INSERT ON usuarios_imgvid FOR EACH ROW IF NEW.type=IMG THEN UPDATE users_status E SET images=images+1 WHERE E.id_usr = NEW.id_usr END IF; this is giving me an error of sintax #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 '' at line 5 thanks a lot You're using compound statement syntax (the IF-THEN-END), so you need to enclose the body within BEGIN/END. http://dev.mysql.com/doc/refman/5.0/en/begin-end.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is mysql_upgrade *required*?
At 9:01 PM -0600 2/16/08, Hayden Livingston wrote: We're trying to upgrade from 4.1.20 to 5.0.54. The problem is running mysql_upgrade. It's turning out to be that about 70% of our tables (over 800GB) are being needing repair. The question is, will it be possible to get by without upgrading? Eventually we'll get to it, but will data be served from these tables if we don't? A lot of our tables DO have TEXT indexes, which apparently have gone under some padding related incompatible changes, what does this really mean, these tables can't be read or they will be inefficient? The server should be able to read them (if it couldn't, mysql_upgrade, which uses mysqlcheck, a client, couldn't tell the server to repair them!). But until those TEXT indexes are rebuilt, you might get incorrect results for some queries. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is mysql_upgrade *required*?
At 9:12 PM -0600 2/16/08, Hayden Livingston wrote: Ahh yes, but I canceled it before all the tables/databases were checked. But I guess, you're saying, it'll work but, I might get bad results. How when you mean certain queries, is there like some type? scans? or the ones that key off that index? or a subset of them? By certain queries, I mean that I don't know how to predict which queries might not work correctly. :-) I'll wimp out and quote the manual: http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html Incompatible change: The indexing order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, TEXT indexes are compared as space-padded at the end (just as MySQL sorts CHAR, VARCHAR and TEXT fields). If you have a index on a TEXT column, you should run CHECK TABLE on it. If the check reports errors, rebuild the indexes: Dump and reload the table if it is an InnoDB table, or run OPTIMIZE TABLE or REPAIR TABLE if it is a MyISAM table. So if CHECK TABLE doesn't complain, you should be okay. Thanks! On Feb 16, 2008 9:09 PM, Paul DuBois [EMAIL PROTECTED] wrote: At 9:01 PM -0600 2/16/08, Hayden Livingston wrote: We're trying to upgrade from 4.1.20 to 5.0.54. The problem is running mysql_upgrade. It's turning out to be that about 70% of our tables (over 800GB) are being needing repair. The question is, will it be possible to get by without upgrading? Eventually we'll get to it, but will data be served from these tables if we don't? A lot of our tables DO have TEXT indexes, which apparently have gone under some padding related incompatible changes, what does this really mean, these tables can't be read or they will be inefficient? The server should be able to read them (if it couldn't, mysql_upgrade, which uses mysqlcheck, a client, couldn't tell the server to repair them!). But until those TEXT indexes are rebuilt, you might get incorrect results for some queries. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 cursors in a store procedure
At 10:57 AM +0800 2/11/08, Rilawich Ango wrote: HI all, How can a store procedure to contain 2 cursors? Any advise? ango create procedure a() begin declare ... declare c1 cursor for select ... declare contine handler for not found set x=1; declare c2 cursor for select ... declare contine handler for not found set y=1; http://dev.mysql.com/doc/refman/5.0/en/cursors.html: Cursors must be declared before declaring handlers. Variables and conditions must be declared before declaring either cursors or handlers. You have a handler declared before a cursor. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Decimal - Maximum is 30
At 9:50 PM -0600 1/31/08, Peter Brawley wrote: Postgres has PostGIS Oracle has OracleSpatial MySQL has ? OpenGis. As stated here: http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ON vs. WHERE
At 11:46 AM -0500 1/30/08, Jerry Schwartz wrote: The documentation says (I think) that you should not use an ON join condition when you can use a WHERE, but I'm pretty sure someone from MySQL.com posted a message suggesting the opposite. The manual says this: The ON conditional is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set. See: http://dev.mysql.com/doc/refman/5.0/en/join.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cache in mysql Windows
At 9:01 AM -0500 12/21/07, Duhaime Johanne wrote: Hello We are doing some performance tests and would like to disable the cache. We did set the query_cache_size=0 but it did not have any effect as well as a select no_cache. The process is a select on MyISAM table. We are using a Mysql 5.0.27. We are wondering if Mysql uses a file to cache or something like this. The first request take several seconds but the second is instantaneous. Even if we stop Mysql it does the second one very fast. It is quite odd. Any clue? You might be seeing the effect of filesystem caching even when the MySQL query cache is disabled. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is up with = and trailing spaces?
At 7:00 PM -0700 9/14/07, Daevid Vincent wrote: From: Matt Gordon Sent: Friday, September 14, 2007 6:37 PM To: Daevid Vincent Subject: Trailing space thing to post to mysql list I searched Google and didn't find anything obvious. Here is minimal SQL to reproduce the problem. # # Running mysql 5.0.41 # CREATE TABLE test ( id int(10) unsigned NOT NULL auto_increment, name varchar(255) default NULL, PRIMARY KEY (id) ) CHARSET=utf8 ENGINE=MyISAM; # Note the trailing spaces in the name. INSERT INTO test( id, name ) VALUES( 1, 'TEST ' ); # Note there are no trailing spaces in the name but this query still returns the record. SELECT * FROM test where name = 'TEST'; # Note the leading spaces in the name. INSERT INTO test( id, name ) VALUES( 2, 'FOO' ); # Note there are no leading spaces in the name and this query fails. SELECT * FROM test where name = 'FOO'; Also, related to this, we could use LIKE instead of = and that had almost the opposite results as we expected, not even using a % sign ?!!? Trailing spaces in VARCHAR values are not significant. See: http://dev.mysql.com/doc/refman/5.0/en/char.html Which says: Note that all MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG: DATE_ADD 99999 fails, but 9999 works.
At 4:56 PM -0700 8/27/07, Daevid Vincent wrote: using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! Why do you consider this extremely obnoxious? Also, I'm curious why you tried to use the user comment page to report a bug, given that the page says: If you've found a bug or wish to request a feature MySQL is missing, please use the bug system. When doing this, please perform a search first in order not to report a bug or request a feature that's already in that system. The option is there to let you know that the user comment system isn't for reporting bugs *and* to let you know the correct place to report them. It's there as extra reinforcement for people who ignore or overlook the statement that I mentioned in the preceding paragraph. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG: DATE_ADD 99999 fails, but 9999 works.
At 5:44 PM -0700 8/27/07, Chris wrote: I don't think this is a bug. I think what's happening is that your timestamp column can't hold that date, it's max value is somewhere in 2038. So I guess either change your timestamp column to a datetime column, or prevent users from putting invalid data in. Ahh ... yes, indeed. mysql select DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY); +-+ | DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) | +-+ | 2281-06-10 | +-+ Daevid, one strategy that might work for you is to enable strict or traditional SQL mode so that you get an error if the timestamp value is out of range: mysql UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL - 9 DAY) WHERE CoreID = 1 LIMIT 1; Query OK, 1 row affected, 1 warning (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql show warnings; +-+--+--+ | Level | Code | Message | +-+--+--+ | Warning | 1264 | Out of range value for column 'password_expire' at row 1 | +-+--+--+ 1 row in set (0.00 sec) mysql set sql_mode='traditional'; Query OK, 0 rows affected (0.01 sec) mysql UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; ERROR 1292 (22007): Incorrect datetime value: '2281-06-10' for column 'password_expire' at row 1 Daevid Vincent wrote: using 9 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work. root# mysql --version mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper CREATE TABLE `Users` ( `CoreID` int(10) unsigned NOT NULL auto_increment, `Username` varchar(155) default NULL, `Password` varchar(64) default NULL, `password_expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`CoreID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL DAY) WHERE CoreID = 1 LIMIT 1; UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9 DAY) WHERE CoreID = 1 LIMIT 1; Added as bug: http://bugs.mysql.com/bug.php?id=30656 Added as a tip: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html BTW, it's extremely obnoxious that when I enter in a comment on that page, then choose bug from the select box, it throws me to another page that says, Sorry, but this is not the correct place to report bugs. You need to report bugs using our online bug reporting system. You can start filling out a bug report with the text you have already entered by clicking the following button: ... Well why the heck did you let me choose that option then! UGH! ÐÆ5ÏÐ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is the average of an int column returned as a string
At 8:25 PM -0400 8/21/07, Baron Schwartz wrote: Eric Lommatsch wrote: Hello, I am working with a query that calculates the averages of survey answers. The survey answers are stored in the database as int(11) fields. When I run the query the results that I am getting are being returned as string data. The query that I am working with is a data source for a Crystal Reports reports. The average columns that are being returned by the query are used in the report in fields that have been formatted for double values. I am using MySQL 5.0.18 as the database. What would I have to do to get the averages of Int columns to return as doubles, rather then having to change all of my columns to be double columns? I'm a little unclear where they're being returned as strings, and how you know they're strings and not floats. I think the math is done with floats, so even if your columns are floating-point, you'll get the same results. But in general, you can use CAST(), though CAST-ing to a floating-point isn't supported. I don't know why not. SELECT CAST(AVG(col) AS DECIMAL(9,2)... Eric, are you using the C API binary (prepared statement) protocol? If so, even if the values are DECIMAL, they'll be returned in string form: DECIMAL values are returned as strings, which is why the corresponding C type is char[]. DECIMAL values returned by the server correspond to the string representation of the original server-side value. For example, 12.345 is returned to the client as '12.345'. If you specify MYSQL_TYPE_NEWDECIMAL and bind a string buffer to the MYSQL_BIND structure, mysql_stmt_fetch() stores the value in the buffer without conversion. If instead you specify a numeric variable and type code, mysql_stmt_fetch() converts the string-format DECIMAL value to numeric form. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: looking for a good book for learning mysql
At 3:31 PM -0700 8/2/07, sol beach wrote: MYSQL Third Edition by Paul DuBios (a frequent list contributor) I think that's slightly misspelled. Perhaps you meant dubious? :-) On 8/2/07, Richard [EMAIL PROTECTED] wrote: Hello, I've got a good book for learning php, very complete and goes through almost everything. However I'm now looking for a good mysql book. I don't want ebooks, only paper versions. I know how to connect and the real basics of mysql (I can connect, do a basic search, insert data etc ... but would like to go alot further. I'm still a beginner but I learn fast, so I don't want something to simple, I need something that can get me going and can also push me alot further. Thanks in advance ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Behaviour of auto_increment
At 5:34 PM -0500 7/15/07, Andrew Carlson wrote: I have created a new table, with an auto_increment value. I would like the first auto_increment value to be 1001. So I - 1) inserted a fake record with an id of 1000 2) alter table tblname auto_increment=1000 (with and without a fake record) 3) alter table tblname auto_increment=1001 (with and without a fake record) Mysql version is 5.0.38 using MyIsam tables. Can someone explain auto_increment, more than what is in the manual? I have read it, and apparently I am missing something or don't understand it. Thanks! We could probably explain it better if you said just what it is you're wondering about. For example, did the things you try not work and you're wondering why? You didn't say what were the results of your actions, nor whether they were what you expected. Please clarify. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New error to me...
At 6:53 PM -0700 7/14/07, Steffan A. Cline wrote: I was trying to reduce a set of queries and ran into this: insert into forums (forum_reply_id, forum_dev_id, forum_subject, forum_message) values (0, 1, (select forum_subject from forums where forum_id=3 ), I figured this one needed a reply too.) yields: error: You can't specify target table from 'forums' for update in from clause I read somewhere online when they referenced 4.x that said you cannot do subqueries in an update. Is this true of 5.x ? You can do subqueries *unless* you're selecting from the same table that you're inserting into. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html (You're not quite using INSERT INTO ... SELECT syntax, but what you're doing appears to be effectively equivalent.) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOW_PRIORITY_UPDATES
At 10:09 PM +0200 7/7/07, Ian Collins wrote: Hi, I am seeing a large lock contention on a production database (A large table_locks_waited against table_locks_immediate). I want to set low priority updates, but I don't want to shut the database down to do it. Is, set global LOW_PRIORITY_UPDATES=1; the same as starting mysql with --low_priority_updates? I think it is but want confirmation. It's the same. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database cache /
At 2:45 PM +1000 7/3/07, Daniel Kasak wrote: On Mon, 2007-07-02 at 21:19 -0700, Ed Lazor wrote: I have a 400mb database. The first query to tables takes about 90 seconds. Additional queries take about 5 seconds. I wait a while and run a query again; it takes about 90 seconds for the first one and the rest go quickly. I'm guessing data is being loaded into memory which is why things speed up. Does this sound right? Is there a way to keep the table in memory? Nothing is changing in the data. You probably have the query cache enabled already if it's behaving like this. See: http://dev.mysql.com/doc/refman/5.0/en/query-cache.html The same behavior might be observed without the query cache being enabled. The key buffer caches MyISAM index blocks, filesystem caching is used for data blocks, etc. The query cache only works for the current connection, so if you open a connection and execute a query, it's only cached for *that* connection. It's not cached only for that connection, actually. But you should get MUCH better performance than what you're currently getting anyway. You need to look at your queries, and put indexes on appropriate fields. Generally you want them on fields used in joins, and fields in your 'where' clause. Always a good idea. Also, you might want to increase your server buffer sizes as appropriate. See, for example: http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: flush logs vs. mysqladmin
At 12:12 PM -0400 6/12/07, Ofer Inbar wrote: We run a mysqladmin flush-logs from cron every night. This causes our server to start a new binary log. However, the slow query log does not get flushed - our server continues updating the same slow query log file. If I run mysql and then issue a flush logs command, it flushes the binary logs and the slow query log as well. - Redhat ES 4 - MySQL 5.0.24 from redhat-supplied RPM - Both mysqladmin from cron, and my mysql flush logs, use -u root Why does mysqladmin not flush the slow query log? It does, but the slow query log (just like the general query log) isn't created as a numbered sequence of files. You'll need to rotate it yourself. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, 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 to get Number of rows matched?
At 11:11 PM -0700 6/10/07, Ace wrote: Hi Experts, When issuing updates in mysql (in the console window), mysql will tell you if any rows matched and how many rows were updated (see below). I know how to get number of rows udpated using mysql_affected_rows(), but is there any way to get the number of rows matched? I want to find out, when rows updated = 0, if there were no updates because the row wasn't found (rows matched will = 0) or because the update would not have changed any data (rows matched = 1). Pass the CLIENT_FOUND_ROWS flag value to mysql_real_connect() when you connect to the server. http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html mysql select * from test; +--+--+ | roll | s| +--+--+ |1 | new | +--+--+ 1 row in set (0.00 sec) mysql update test set roll = 1, s = 'new' where roll = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'new' where roll = 17; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql update test set roll = 1, s = 'neww' where roll = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -- Cheers, Rajan -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maximum number of records in a table
At 6:24 PM -0400 6/11/07, kalin mintchev wrote: hi all... from http://dev.mysql.com/doc/refman/5.0/en/features.html: Handles large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 60,000 tables and about 5,000,000,000 rows. that's cool but i assume this is distributed over a few machines... No. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change in behaviour in version 5.0.41
At 12:10 PM -0400 6/18/07, Baron Schwartz wrote:
Paul DuBois wrote:
At 3:29 PM +0100 6/15/07, Ben Clewett wrote:
Dear MySql,
I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26
with date comparisons.
In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True.
In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False.
In 5.1.6-alpha: '2007-06-15' = '2007-06-15 00:00:00' is True.
This has caused us a few problems. Is this the way things should
be, because this change does not seem right?
I am also very worried that this behaviour revert when we role out 5.1?
Does any member know whether this is a bug, or just an anoying feature?
Regards,
Ben
To Replicate:
CREATE TABLE t (d DATE);
INSERT INTO t VALUES ('2007-06-15');
SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00';
+--+
| COUNT(*) |
+--+
|0 |
+--+
SELECT COUNT(*) FROM t WHERE d = '2007-06-15';
+--+
| COUNT(*) |
+--+
|1 |
+--+
This might be relevant:
Prior to MySQL 5.0.42, when DATE values are compared with DATETIME
values the time portion of the DATETIME value is ignored. Starting
from MySQL 5.0.42, a DATE value is coerced to the DATETIME type by
adding the time portion as '00:00:00'. To mimic the old behavior
use the CAST() function in the following way: SELECT date_field =
CAST(NOW() as DATE);.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html
There is a corresponding change in 5.1.18.
See: http://bugs.mysql.com/bug.php?id=28929
I tried to look at this bug, but it says access is denied. :-(
It should be accessible now.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change in behaviour in version 5.0.41
At 3:29 PM +0100 6/15/07, Ben Clewett wrote:
Dear MySql,
I have noticed a change in behaviour in MySql 5.0.41 from 5.0.26
with date comparisons.
In 5.0.26:'2007-06-15' = '2007-06-15 00:00:00' is True.
In 5.0.41:'2007-06-15' = '2007-06-15 00:00:00' is False.
In 5.1.6-alpha: '2007-06-15' = '2007-06-15 00:00:00' is True.
This has caused us a few problems. Is this the way things should
be, because this change does not seem right?
I am also very worried that this behaviour revert when we role out 5.1?
Does any member know whether this is a bug, or just an anoying feature?
Regards,
Ben
To Replicate:
CREATE TABLE t (d DATE);
INSERT INTO t VALUES ('2007-06-15');
SELECT COUNT(*) FROM t WHERE d = '2007-06-15 00:00:00';
+--+
| COUNT(*) |
+--+
|0 |
+--+
SELECT COUNT(*) FROM t WHERE d = '2007-06-15';
+--+
| COUNT(*) |
+--+
|1 |
+--+
This might be relevant:
Prior to MySQL 5.0.42, when DATE values are compared with DATETIME
values the time portion of the DATETIME value is ignored. Starting
from MySQL 5.0.42, a DATE value is coerced to the DATETIME type by
adding the time portion as '00:00:00'. To mimic the old behavior use
the CAST() function in the following way: SELECT date_field =
CAST(NOW() as DATE);.
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html
There is a corresponding change in 5.1.18.
See: http://bugs.mysql.com/bug.php?id=28929
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: perl DBI does not do execute mysql statement
At 6:54 PM -0700 6/9/07, Kemin Zhou wrote: Hi All, I have one of the toughest problem to solve now. I have not been able to sleep well in the last three days. There is a simple query insert into table foo (column1, column2, column5) select $col from bar If I run this query by hand whatever the value of $col, it works fine. The insertion was done. If I run this query with other values of $col this sql works fine if $col is not name. If $col eq name then the insertion does not happen and no error message is given. (Of course, if I run the same query by hand, then this works fine). Not sure how crazy this could be. Any clue? No, because you're describing the problem in English without showing any of the code that doesn't work. How can anyone help you? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documentation
At 12:16 PM -0700 6/4/07, [EMAIL PROTECTED] wrote: Hello I am going to be going on vacation and will be bringing my laptop with mysql installed on it. Is there a source of good documentation that can be downloaded (.doc or .pdf) for mysql? Downloadable document formats are available at the main documentation page on our web site: http://dev.mysql.com/doc/ -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger OLD question
At 4:59 PM -0400 5/9/07, Olaf Stein wrote: Hi All, I have a table with 205 columns. When an update statement updates a row in this table I want a trigger that creates a record of the old row in a separate table. The following works fine when not too many columns need to be written into the other table CREATE TRIGGER track_table BEFORE UPDATE ON table FOR EACH ROW BEGIN INSERT INTO table_track (value1,value2) VALUES (OLD.value1,old.value2); END; Is the any way of using OLD for all columns or generally another way of doing this. If you have a primary ID in the table, perhaps you could use a statement something like this: INSERT INTO table_track SELECT * FROM track_table WHERE key_col=OLD.key_col; I admit I have not tested this. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --xml or --html output to file
At 6:09 PM -0400 5/8/07, John Kebbel wrote: When I try using the --xml or --html option with a batch file using INTO OUTFILE 'dirpath', the --xml or --html option seems to be ignored in favor of the tab-delimited default. (If I get rid of the INTO OUTFILE, xml or html displays fine in the terminal.) --xml and --html affect how the mysql client writes its output. INTO OUTFILE takes place on the server side. It cannot be affected by --xml or --html. (Or --batch or --table, either. I tried using the pager to write to a file from inside MySQL. I succeeded, but it was table data. I couldn't figure out how to add the --xml or --html options from inside the pager. I tried the redirection operator from the command line, but I haven't stumbled on the correct syntax if such a syntax does exist. It exists; this is a function of your command interpreter. But you're not likely to stumble on it; better to consult the documentation for your interpreter. For Unix, you can do something like this: Put statements in a file x. Run mysql like this to capture output into y: mysql --xml db_name x y -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
At 9:55 PM -0400 5/3/07, Baron Schwartz wrote: Mark Leith wrote: 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. 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? Not in my case. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
At 5:33 AM -0400 5/2/07, John Kebbel wrote: I went to the MySQL documentation pages and read up on using COLLATE. I knew SELECT was case-insensitive, but I was sort of surprised that using a character class didn't override that. Anyway, I next tried the status command to see if it gave me any characterset information. Client characterset:latin1 Server characterset:latin1 Once I thought I understood what was going on with COLLATE and case sensitivity, I tried this command... SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' COLLATE latin1_bin; It seemed to work fine. I searched the column to see if I could find any instances of all caps value, but did not find any. (They do exist; I created the data for this table from a Perl script solely to practice using character class regular expressions.) Then I tried this command. It should not have found any instances of all lower case passwords, but it did. SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' COLLATE latin1_bin; +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | Is there something obvious that I'm missing here? Ah. Yes. I should have noticed this in your earlier message, sorry: The character class names *include* the square brackets, so to use them as part of a range, you need another set of square brackets, i.e., [[:upper:]]. What your statement looks for is any values containing :, u, p, e, or r, and that's why you see the values returned that you do. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
At 8:46 PM -0400 5/2/07, Baron Schwartz wrote: Ofer Inbar wrote: Mark Leith [EMAIL PROTECTED] wrote: 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. 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. :-) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: REGEXP Character Classes
At 6:20 PM -0400 5/1/07, John Kebbel wrote: Linux Version: Linux version 2.6.15-28-386 MySQL Version: 5.0.22-Debian_0ubuntu6.06.3-log I have two queries using REGEXP character classes and their respective outputs below. The first is supposed to match an upper case character in a column, but I wind up with 4 rows out of 25 that contain only lower case characters. The second is supposed to match lower case characters but returns 11 rows out of 25 that contain only upper case characters. Am I using these character classes correctly? The classes define what characters are members of the class. But the matching is determined by the collation of the REGEXP operands. If you have a case-insensitive collation (which you do, by default), the matching takes place in a case-insensitive fashion. You could apply a COLLATE clause to one of the operands to force the use of a case-sensitive collation. -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | +--+--+--++++ 25 rows in set (0.00 sec) -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |5 | VBOEUTTM | Human Resources | Purchasing | Clerical | c18528 | |9 | ENDPAXWW | Human Resources | Accounting | Clerical | 73d00f | | 14 | TEVXTOBK | Human Resources | Accounting | Sales Rep. | 6606a0 | | 15 | WREZUFAU | Customer Service | Receiving | Asst. | 14159 | | 17 | LGMMPJEY | Customer Service | Accounting | Asst. | 291512 | | 21 | DMCLWWDX | Customer Service | Receiving | Sales Rep. | 968745 | | 23 | BZZCQWWE | Customer Service | Payroll| Asst. | 11f2b7 | | 24 | EPGWQEXC | Customer Service | Payroll| Clerical | 706894 | | 31 | NYOOQVJI | Human Resources | Accounting | Sales Rep. | e7d0bc | | 33 | BUTSHOUS | Human Resources | Payroll| Asst. | 548082 | | 34 | VOSCTTGZ | Customer Service | Receiving | Sales Rep. | 858435 | +--+--+--++++ 25 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is /*!40101 SET @...
At 8:44 AM -0400 4/25/07, Kebbel, John wrote: I've been developing Perl/ PHP and MySQL applications on a small scale for years, reworking my small core of knowledge over and over again. I recently decided to hit the books and shoot for certification as a MySQL CMDEV. Most of my questions have answers in my books and on web sites, but I occassionally run into stuff so weird, I don't even know how to phrase a web query. For example, what is this? It appears at the top of a data dump. I recognize the Create and Insert sections, but this is Greek. /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; Those are code-containing comments, such that the code will be executed only if the server is as recent as the version number at the beginning of the comment. http://dev.mysql.com/doc/refman/5.0/en/comments.html Basically, it's version-specific code for features that are unavailable in older servers. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, 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 to overwrite existing file with SELECT .. INTO?
At 10:14 AM -0400 4/13/07, Amer Neely wrote: I'm using MySQL 5.0.21 and am trying to find out if it is possible to overwrite an existing file when using a 'SELECT ... INTO' command from the command line. Is there another parameter that can do this? I've looked through the online reference manual, but found no specific help there. You cannot. This is a security feature. Otherwise, you might be able to select into such files as /etc/password or /etc/shells. From the manual: The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. http://dev.mysql.com/doc/refman/5.0/en/select.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation Order in MySQL?
At 4:46 PM -0400 4/10/07, David T. Ashley wrote: I'd like to have a table in MySQL with the key field being a 12-character text string consisting of digits and upper-case letters (minus the vowels and Y, to prevent accidental bad words), i.e. something like: XM39C6B4... When I do queries and get back result sets sorted on this text string, is it always safe to assume that: a)Digits come before letters? b)Letters are in ascending order, i.e. A before B, B before C, etc.? Character orderings are entirely determined by the collation that you choose. So you should choose a collation the produces the ordering you want. :-) To be conservative, should I set collation order? Yep. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dumpfile question
At 10:15 AM +0800 4/11/07, wangxu wrote: follow is my sql: select * into dumpfile '/home/wangxu/test4.data' from mytable ; mysql server report: Result consisted of more than one row why?how to use the select into dumpfile? From the manual: If you use INTO DUMPFILE instead of INTO OUTFILE, MySQL writes only one row into the file, without any column or line termination and without performing any escape processing. This is useful if you want to store a BLOB value in a file. http://dev.mysql.com/doc/refman/5.0/en/select.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: information_schema not found
At 12:30 PM -0500 3/29/07, Brown, Charles wrote: Hi all. I just finished installing mysql on a new server. I noticed that database information_schema wasn't found. Can someone help me. What version of MySQL? I_S is present only in 5.0 and up. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch
At 4:17 PM -0600 2/20/07, Paul DuBois wrote: At 4:36 PM -0500 2/20/07, Sun, Jennifer wrote: Any answers for the question below ? Is there a DST patch for MySql 4.0.20? Thanks. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 09, 2007 9:30 AM To: mysql@lists.mysql.com Subject: MySQL Daylight Savings Time Patch Is there a DST patch for MySQL 4.0.x series? I've been getting scary emails from our sys and net admins about impending doom. Thanks, David Before MySQL 4.1.3, the server gets its time zone from the operating system at startup. The time zone can be specified explicitly by setting the TZ TZ environment variable setting, or by using the --timezone option to the mysqld_safe server startup script. Assuming that the server host itself has had its operating system updated to handle the new Daylight Saving Time rules, that should be all that's necessary for MySQL to know the correct time. I should mention also: For those of you running 4.1.3 or later, to get your MySQL server to know about the new DST rules, you should make sure your OS is updated with the new zoneinfo files, and then reload those files into MySQL with mysql_tzinfo_to_sql. See: http://dev.mysql.com/doc/mysql/en/time-zone-support.html Particularly the Note in the middle of the page and the last few paragraphs. You may have previously loaded your system's zoneinfo files into MySQL, but when those zoneinfo files are updated, the changes do not automatically propagate to MySQL's time zone tables. You must reload the tables to update them. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
