Re: How to loop a query that is only able to handle 1 ROW at a time?
Hi Incarus,
You don't need a loop just a correlated subquery on the update:
UPDATE TB1 SET Ivet=PREG_REPLACE('/TeXT/', '', Ivet);
Example:
mysql CREATE TABLE blah(value INTEGER, square INTEGER);
Query OK, 0 rows affected (0.17 sec)
mysql INSERT INTO blah(value) VALUES (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql SELECT * FROM blah;
+---++
| value | square |
+---++
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+---++
5 rows in set (0.00 sec)
mysql UPDATE blah SET square=POW(value,2);
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql SELECT * FROM blah;
+---++
| value | square |
+---++
| 1 | 1 |
| 2 | 4 |
| 3 | 9 |
| 4 | 16 |
| 5 | 25 |
+---++
5 rows in set (0.00 sec)
On Mon, Aug 20, 2012 at 10:23 AM, Incarus Derp icarusd...@gmail.com wrote:
I have a two line query that is only able to handle 1 row per
execution. Could any of you give me some insight on how I could loop this
per every single row in the table? The query in question is:
SELECT PREG_REPLACE('/TeXT/', '' , Ivet) FROM `DB1`.`TB1` INTO @VAR12;
UPDATE `TB1` SET Ivet = @VAR12;
Re: 'myisam_use_mmap' unstable like hell
of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=268435456 read_buffer_size=262144 max_used_connections=33 max_threads=200 thread_count=6 connection_count=6 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 418015 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x2a4bd50 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f507cf70d40 thread_stack 0x4 /usr/libexec/mysqld(my_print_stacktrace+0x33)[0x7ab8f3] /usr/libexec/mysqld(handle_segfault+0x470)[0x50f190] /lib64/libpthread.so.0(+0xeeb0)[0x7f51054caeb0] /lib64/libc.so.6(+0x12ffa5)[0x7f5103843fa5] /usr/libexec/mysqld(mi_mmap_pread+0x15a)[0x90880a] /usr/libexec/mysqld(_mi_read_dynamic_record+0x1fe)[0x90ac5e] /usr/libexec/mysqld(mi_rkey+0x378)[0x930f48] /usr/libexec/mysqld(_ZN9ha_myisam14index_read_mapEPhPKhm16ha_rkey_function+0x59)[0x8f1fe9] /usr/libexec/mysqld[0x5b3f35] /usr/libexec/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x61)[0x5a4721] /usr/libexec/mysqld[0x5b2c65] /usr/libexec/mysqld(_ZN4JOIN4execEv+0xbe1)[0x5c39b1] /usr/libexec/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x152)[0x5bf182] /usr/libexec/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x184)[0x5c5074] /usr/libexec/mysqld[0x57df97] /usr/libexec/mysqld(_Z21mysql_execute_commandP3THD+0x2438)[0x585808] /usr/libexec/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x186)[0x589ef6] /usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x15e5)[0x58b505] /usr/libexec/mysqld(_Z24do_handle_one_connectionP3THD+0x117)[0x61fff7] /usr/libexec/mysqld(handle_one_connection+0x50)[0x6200a0] /lib64/libpthread.so.0(+0x6ccb)[0x7f51054c2ccb] /lib64/libc.so.6(clone+0x6d)[0x7f51037f4c2d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f4ffc0058a0): is an invalid pointer Connection ID (thread ID): 460043 Status: NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. The --memlock argument, which was enabled, uses system calls that are unreliable and unstable on some operating systems and operating-system versions (notably, some versions of Linux). This crash could be due to use of those buggy OS calls. You should consider whether you really need the --memlock parameter and/or consult the OS distributer about mlockall bugs. 24 08:20:18 mysqld_safe Number of processes running now: 0 24 08:20:18 mysqld_safe mysqld restarted 24 8:20:18 [Note] Plugin 'InnoDB' is disabled. 24 8:20:18 [Note] Plugin 'FEDERATED' is disabled. 24 8:20:18 [Note] Plugin 'BLACKHOLE' is disabled. 24 8:20:18 [Note] Plugin 'ARCHIVE' is disabled. 24 8:20:18 [Note] Plugin 'partition' is disabled. 24 8:20:19 [Note] Event Scheduler: Loaded 0 events 24 8:20:19 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.18-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 thelounge.net build 24 8:20:19 [ERROR] /usr/libexec/mysqld: Table './afi/cms1_global_cache' is marked as crashed and should be repaired 24 8:20:19 [Warning] Checking table: './afi/cms1_global_cache' 24 8:20:19 [ERROR] Got an error from unknown thread, /home/builduser/rpmbuild/BUILD/mysql-5.5.18/storage/myisam/ha_myisam.cc:870 -- Mit besten Grüßen, Reindl Harald the lounge interactive design GmbH A-1060 Vienna, Hofmühlgasse 17 CTO / software-development / cms-solutions p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40 icq: 154546673, http://www.thelounge.net/ http://www.thelounge.net/signature.asc.what.htm -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Cleaning up old files
Hi, In my MySQL directory, I have more than a few gig and a half sized files, mysql-bin.01, mysql-bin.01 and et cetera. They date from today all the way back to early 2010. I don't know exactly what those files are but I would like to delete as many as are no longer is use since I had a 40GB partition fill up over the weekend which resulted in bringing down our web server. So what are those files and can I delete all but the most recent? Thanks. Rob Tanner UNIX Services Manager Linfield College, McMinnville Oregon
Re: Seconds Behind the master 0 but in reality it's over 10 minutes or hours..
Hm. It seems we have sort of ethernet segment saturation problem. A possible bandaid/quick fix is turning on compression for mysql replication. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using Excel to query the database
Hi, I have a fairly standard set of queries that I do then I save the resultset as a CSV and send it off to a couple dozen folks. My understanding is that I can build an Excel .iqy file that can do the queries (one for each query) and that I need send that file but once to the users. Does anyone have any sample Excel .iqy files for MySQL access that they would be willing to share (with passwords removed, of course)? Thanks. Rob Tanner UNIX Services Manager Linfield College, McMinnville Oregon ITS will never ask you for your password. Please don’t share yours with anyone! [cid:74641041-0AC7-459B-AA5C-2BDE34206F14]
Re: MySQL HA on cloud
On Thu, Jul 21, 2011 at 6:20 AM, Yogesh Kore yogeshk...@gmail.com wrote: Hi, I have cloud setup where MySQL servers are installed. Replication is applied in between mysql servers. Now I want to implemet HA for MySQL. Can any one help me how can I achieve MySQL failover? I looked DRBD, Linux Heartbeat, but I am not able to confirm which should I go for or is there anything else by which I can achieve my goal? Thank You. Regards, Yogesh Getting automatic failover right is near impossible and it is very easy to destroy your data. I strongly suggest not attempting this project. -- Rob Wultsch wult...@gmail.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: Facebook Trapped In MySQL a 'Fate Worse Than Death'
On Mon, Jul 11, 2011 at 2:43 PM, Daevid Vincent dae...@daevid.com wrote: http://developers.slashdot.org/story/11/07/09/1256241/Facebook-Trapped-In-My SQL-a-Fate-Worse-Than-Death According to database pioneer Michael Stonebraker, Facebook is operating a huge, complex MySQL implementation equivalent to 'a fate worse than death,' and the only way out is 'bite the bullet and rewrite everything http://gigaom.com/cloud/facebook-trapped-in-mysql-fate-worse-than-death/ .' Not that it's necessarily Facebook's fault, though. Stonebraker says the social network's predicament is all too common among web startups that start small and grow to epic proportions. Preface: I in no way speak for my employer. I suggest reading http://dom.as/2011/07/08/stonebraker-trapped/ -- Rob Wultsch wult...@gmail.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: FW: [USN-1017-1] MySQL vulnerabilities
On Fri, Nov 12, 2010 at 3:23 PM, Gael gael.marti...@gmail.com wrote: On Fri, Nov 12, 2010 at 4:12 PM, Daevid Vincent dae...@daevid.com wrote: my point exactly. there is NONE. and if you don't patch your mysql as needed, then you will need a lot more help when you're hacked. ;-p http://lists.mysql.com/ On May 21 they sent out an email about MySQL Server 5.0.91 being released. I for one read release notes for each point release and had a *very* busy night. -- Rob Wultsch wult...@gmail.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 is crashing
On Fri, Aug 13, 2010 at 11:47 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Yectli, I am talking about mysql server. Is it 32bit or 64bit. Provide some additional information. mysql configuration file, show variables, Error log file details etc. Kirshna On Fri, Aug 13, 2010 at 8:36 PM, Yectli Huerta yhue...@msi.umn.edu wrote: On Fri, Aug 13, 2010 at 04:33:38PM +0530, Krishna Chandra Prajapati wrote: Hi Yectli, In mysql configuration file (my.cnf) you have given key_buffer_size = 5G For 32bit OS it should be less than or equal to 4GB and for 64bit it can be greater than 4GB. Reduce the key_buffer_size to 3GB and try. _Kirshna Hello, thanks for the tip. It is a 64 bit executable so 5gb should be OK. It is running on a x86_64 SLES 10.3 server. Then your mysql server should also be 64 bit. Please Check. -- Yectli Try using the file command: # file /usr/local/mysql/bin/mysqld /usr/local/mysql/bin/mysqld: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.4.0, dynamically linked (uses shared libs), for GNU/Linux 2.4.0, not stripped -- Rob Wultsch wult...@gmail.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 cluster with 3 db/data and 2 mgm nodes
On Sun, Aug 8, 2010 at 11:49 PM, Walter Heck - OlinData.com li...@olindata.com wrote: Unless you have a very good reason, you probably shouldn't go with cluster in the first place. If it is HA you want to have, check out other options like MMM for MySQL (http://mysql-mmm.org), DRBD +Heartbeat and others. Can you tell us a bit more about your goals/desires? Walter Heck Engineer @ Open Query (http://openquery.com) Walter is spot on and yes, 3 is not a good number for data nodes. The only recommended (and somewhat well tested) number of replicas is 2, so 3 would not be useful. You may want to buy another box so that do 2 replicas with 2 shards, OR just use the third node as a warm standby. -- Rob Wultsch wult...@gmail.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: There is something wrong with bugs.mysql.com email server I think (another one)
On Sat, Aug 7, 2010 at 11:47 AM, Baron Schwartz ba...@xaprb.com wrote: I consider this a bug in the bug tracking system :-) Severity: S4 (Feature request) -- Rob Wultsch wult...@gmail.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: Possible tricks to ALTER on huge tables?
On Thu, Aug 5, 2010 at 5:07 PM, Daevid Vincent dae...@daevid.com wrote: Driving to work today, I had an epiphany thought, but wanted to see if anyone could prove my theory or not. We currently have some tables that are approaching 1 BILLION rows (real Billion, with nine zeros, not that silly six zero version). Trying to do an ALTER on them to add a column can sometimes take hours. I'm wondering if we had the foresight to create the tables, and then tack on extra dormant columns of various common types, such as: future_uint int(10) unsigned null, future_int int(10) signed null, future_var varchar(255) null, Etc. So basically they'd be unused, then when we wanted a new column of that type, we would just rename the dormant one. I'm not sure if mySQL is smart enough to realize that if the schema definition for a column is identical, then it's just a simple rename, or if it treats any change the same and will still take hours to complete (if so, perhaps there's an optimization for you mysql developer team) Another option I considered, was is it possible to just go in with a hex editor and rename the field in the .frm file? Is there some kind of .frm editor available anywhere? r...@mypse:/var/lib/mysql/agis_core# hexdump -C country.frm 1000 01 01 00 00 0a 00 00 00 02 00 01 00 00 00 01 80 || 1010 02 00 00 12 00 02 00 ff 50 52 49 4d 41 52 59 ff |PRIMARY.| 1020 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 || * 13f0 00 00 00 00 00 00 00 00 00 00 00 00 06 00 49 6e |..In| 1400 6e 6f 44 42 00 00 00 00 00 00 00 00 00 00 00 00 |noDB| 1410 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 || * 2150 04 00 0b 69 64 5f 63 6f 75 6e 74 72 79 00 05 00 |...id_country...| 2160 05 69 73 6f 32 00 06 00 05 69 73 6f 33 00 07 00 |.iso2iso3...| 2170 0d 63 6f 75 6e 74 72 79 5f 6e 61 6d 65 00 04 0b |.country_name...| 2180 05 05 00 02 00 00 12 00 0f 00 00 02 c0 00 00 05 || 2190 05 06 06 00 04 00 00 00 80 00 00 00 fe c0 00 00 || 21a0 06 05 09 09 00 0a 00 00 00 80 00 00 00 fe c0 00 || 21b0 00 07 0d 42 fd 02 13 00 00 00 00 00 00 00 0f c0 |...B| 21c0 00 00 ff 69 64 5f 63 6f 75 6e 74 72 79 ff 69 73 |...future_var...| 21d0 6f 32 ff 69 73 6f 33 ff 63 6f 75 6e 74 72 79 5f |...future_int...| Having significant amount of overhead for unused columns will without doubt harm performance significantly for certain operations. Altering .frm files should is always be tried on a non-prod box before even considering using it on prod. There are some well known use cases (adding enums values, enlarging varchar columns) where altering a .frm is useful, but it should always be considered very dangerous. You could of course consider using PostgreSQL which would only need a very brief exclusive lock for adding a default null column... -- Rob Wultsch wult...@gmail.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 and oom-killer
2010/8/5 Евгений Килимчук ekilimc...@gmail.com: When OOM-killer kill mysqld, I had a critical corrupted tables. My database is a very big. I think Apache with cgi-scripts not critical process in this story. And sshd is a real true. OOM is configurable via the proc filesystem via /proc/pid/oom_adj . You can set the processes that you do not want to get killed to a negative number, if I remember correctly, and that will help to prevent them from being killed by OOM. So let's say mysqld pid is 123456, you could echo -17 /proc/123456/oom_adj and then it would be less likely to be selected by OOM to be killed. -- Rob Wultsch wult...@gmail.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 and oom-killer
2010/8/5 Rob Wultsch wult...@gmail.com: 2010/8/5 Евгений Килимчук ekilimc...@gmail.com: When OOM-killer kill mysqld, I had a critical corrupted tables. My database is a very big. I think Apache with cgi-scripts not critical process in this story. And sshd is a real true. OOM is configurable via the proc filesystem via /proc/pid/oom_adj . You can set the processes that you do not want to get killed to a negative number, if I remember correctly, and that will help to prevent them from being killed by OOM. So let's say mysqld pid is 123456, you could echo -17 /proc/123456/oom_adj and then it would be less likely to be selected by OOM to be killed. -- Rob Wultsch wult...@gmail.com And I should have stated that this in significant use by the PostgreSQL community. -- Rob Wultsch wult...@gmail.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: Automate Install/Configuration of MySQL on Linux
On Tue, Jul 27, 2010 at 1:56 PM, Todd E Thomas todd_...@ssiresults.com wrote:
I'm looking for automation direction...
I've found many packages that sit on top of MySQL. For the purposes of
consistency I'd like to automate these installs.
I've been able to automate the install and configuration of everything
except the mysql part.
I'm using CentOS 5.5. Installing/verifying is no big deal.
It's the MySQL configuration that's holding me up.
Basically I've created an expect script. It works 99% but it's a PITA to
finish. Here's what I'd like to accomplish:
*Set the default admin password
# mysqladmin -u root password 'root-password'
*login to mysql
mysql mysql -u root -p
*Drop the anonymous accounts
mysql DELETE FROM mysql.user WHERE user = '';
*Sync all of the root passwords
mysql UPDATE mysql.user SET Password = PASSWORD('root-password') WHERE
User = 'root';
*Remove the test database:
mysql drop database test;
In another script I would like to create databases for specific packages.
EG:
Concrete5, for example needs:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON concrete5.db TO
'admin'@'localhost' IDENTIFIED BY 'admin-password';
If there is a better way to do this than using expect I would greatly
appreciate any pointers in the right direction. Bash is comfortable for me
and perl is within reach. I'm not much versed in anything else right now.
If you are serious about spending time and doing automation well then
Puppet or cfengine would be the way to go.
As for the basic tasks that you describe, have you considered
modifying the rpm/deb/whatever to distribute a data dir with whatever
you want?
In addition, do you really need expect? Could you get the same effect
with good use of the sleep command inside bash?
--
Rob Wultsch
wult...@gmail.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: migration via replication for large DB?
On Sun, Jul 25, 2010 at 12:55 PM, Geoff Galitz ge...@galitz.org wrote: Hello. I need to migrate a master and slave to new hardware. The DB is approx 50G on disk and my time window for downtime is approximately 4 hours. My question is, is it advisable to do a mysqldump from the old master and then load on the new master and slave, or is it faster to just set the new master up as a slave, and when it catches up to the old master I flip the switch? The catch is that the new hardware is in a different datacenter. In other words, which is faster: dump and load or replication over the Internet? Thanks for your time. There may be significantly better options available to you. What version are you coming from and what version are you to? What engines do you use (Innodb, MyISAM,etc)? Is the data directory currently mounted on a lvm volume? -- Rob Wultsch wult...@gmail.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: MMM Mysql
On Thu, Jul 22, 2010 at 8:42 PM, Kyong Kim kykim...@gmail.com wrote: Has anyone used this in production? We're looking at this as part of our sharding/scale strategy and wanted some insight into real world experience. Are there alternatives out there? Kyong Lots of people are using MMM. Alternatives include Linux-HA (aka heartbeat) often combined with DRBD and MySQL cluster. For the general case MMM is probably the best option. -- Rob Wultsch wult...@gmail.com
Re: Myisam advantages
On Thu, Jul 15, 2010 at 10:46 PM, P.R.Karthik prk...@gmail.com wrote: Hi, I am newbie to mysql can i know the advantages of myisam storage engine and some of its special features. -- Regards Karthik.P.R kart...@mafiree.com Special features: 1. Not atomic. 2. No consistency. 3. Horrible isolation 4. Durable on good days. In short it can easily eat your data. If you love your data and treat it well it will love you back. There are very few valid uses for MyISAM (full text and spatial are not valid, use sphinx and PostGIS respectively) -- Rob Wultsch wult...@gmail.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 become a DBA on MySQL
I very much disagree with the suggestion that any sort of training is needed or useful. The fastest way (IMHO) to gain the knowledge to become a solid MySQL DBA is to answer questions on #mysql and to read planet mysql. There are very few people than understand MySQL well. After 6 months of trying to help people on #mysql most people will have enough knowledge that I would suggest hiring them as either junior or full DBA. In addition it is easy to make contacts that can become employment opportunities. -- Rob Wultsch wult...@gmail.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 5.5.3 and innodb from source.
On Sat, Jul 17, 2010 at 7:34 AM, Andrés Tello mr.crip...@gmail.com wrote: Who you build mysql 5.5.3 with innodb suport? I made sh configure.am --with-plugins=all I see the makefile at innodb subdir being created. I build the system correctly but when I log in to the mysql 5 instance and do a show engines; I only have this: ++-+---+--+--++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++-+---+--+--++ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | ++-+---+--+--++ my my.cnf... skip-name-resolve log_error =/mysql5/mysql5.err socket = /mysql5/mysql.sock port = 3308 pid-file = /mysql5/mysql5.pid datadir = /mysql5/data tmpdir=/tmpfs binlog_cache_size=64M #tamaño de la transaccion a cachear bulk_insert_buffer_size=256M #cache de insert por thread delay_key_write=OFF #detiene la creacion de llaves? No, no nos conviene por integridad. max_allowed_packet=256M table_cache=4096 join_buffer_size=256M tmp_table_size=1024M sort_buffer_size=1024M thread_cache_size=64 #default_storage_engine=INNODB query_cache_size=1024M query_cache_limit=256M innodb_file_per_table innodb_data_home_dir = /mysql5/innodb innodb_data_file_path = ibdata/innodb:2000M:autoextend innodb_buffer_pool_size=512M innodb_flush_method=fdatasync #ls opciones son fdatasync (default), O_DSYNC, (lento?) O_DIRECT innodb_locks_unsafe_for_binlog=1 #solo usa indices innodb_additional_mem_pool_size=512M innodb_log_file_size=2000M innodb_log_buffer_size=32M innodb_max_dirty_pages_pct=95 innodb_max_purge_lag=0 innodb_flush_log_at_trx_commit=1 innodb_lock_wait_timeout=50 innodb_thread_concurrency=200 max_heap_table_size=4G what I'm doing wrong? Please advice. Thanks. Assuming that you actually built the innodb plugin (I have no experience building 5.5) the you should probably look at what you need to add to the cnf in order to use the plugin. http://www.innodb.com/doc/innodb_plugin-1.0/innodb-plugin-installation.html This is only a guess. -- Rob Wultsch wult...@gmail.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 always linked to Php?
On Thu, Jul 15, 2010 at 7:00 AM, alba.albetti alba.albe...@libero.it wrote: Browsing the Web I've seen that usually companies look for developers working on MySQL and Php. Why are the two things linked? I mean I've not found any requests for just a MySQL developer or DBA (as for example it happens for Oracle), but it's always requested a MySQL/Php expert. I ask for it 'cause I've always been a developer/DBA on RDBMS (Oracle and DB2) and as I've been learning MySQL for few weeks, I'd like to know whether and why it's so important to learn Php as well. It would be so difficult to find a job as MySQL developer/DBA without knowing Php as well. Thanks! There are numerous good reasons why there is this correlation. I would like to propose one that has not been mentioned. PHP is a mess. For several years PHP put food on my table and I respect it for what it is, but it really is a mess. Whether it is inconsistencies in function parameters or looseness in how it deal with data that does not quite fit, it is a mess. I think MySQL is historically similar. Both are very weakly typed and can produce surprises because of it. Both seem like they are designed for quick productivity. It seems like PostgreSQL has significant mindshare amongst Perl and C++ users... -- Rob Wultsch wult...@gmail.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 become a DBA on MySQL
On Tue, Jul 13, 2010 at 6:24 AM, alba.albetti alba.albe...@libero.it wrote: Hi, I'd like to know which are the main tasks for a DBA, so in addition of the on-line tutorial of MySQL, is there anyone telling me whether there exists some tutorial (better if in PDF) where it's possible to undestand and learn all you need for managing the MySQL DB as DBA? I mean I'd like to read something explaining what are datafiles, tablespaces, ... and what usually a DBA is asked to do (creating databases, starting up and shutting down the database, managing the database's storage structures, making database backups and performing recovery, monitoring and tuning performance, ...) In other words I'd like to read how to manage a DB as DBA, because I've always worked only as developer and not as DBA. Thanks! It really depends on the company. Some DBA's are glorified sys admins, some are mostly db developers and there is everything in between. I do work that would be normal for a front end developer, I also sometimes have to go digging into the C+ guts MySQL and I also deal with lots non-technical issues. It really depends a lot on the company. -- Rob Wultsch wult...@gmail.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: query executes very slow in a table with 2m records
On Tue, Jul 13, 2010 at 8:22 AM, mos mo...@fastmail.fm wrote: At 09:29 AM 7/13/2010, æ Žå¾ wrote: Hello, There are more than 2m records in the table -- fxrate. I create patitions, indexes, but it still takes me about 7 minutes to execute the following query SELECT COUNT(*) FROM fxrate WHERE MONTH(quoteDate) = 6 AND quoteHourMinSec BETWEEN 06:00:00 AND 19:00:00 result: 647337 How can I improve the performace? Thanks in advance. It is because you are using MONTH(quoteDate) which means it has to go through all the rows. You should add a compound index to QuoteDate,QuoteTime. Then Try select count(*) from fxrate where quotedate between '2010-06-01' and '2010-06-30' and quotetime between '06:00:00' and '19:00:00' I'm not sure why you have two columns for storing the date and time. I would have used one column QuoteDateTime as DateTime. I also don't know why you have all those partitions for a small 2m row table. Mike A compound index index will not be used after the first range condition. I agree about using a single datatype (datetime or timestamp) and partitioning not being a good idea for only 2M rows. -- Rob Wultsch wult...@gmail.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: Slow disk access: 1.4m to do a select count over 23GB table.
On Fri, Jul 9, 2010 at 9:09 PM, Andrés Tello mr.crip...@gmail.com wrote: Hi, I'm using mysql 4.1.21, a legacy system. Next step to migrate it, but for now, I need the community help. I have a 23GB table, if I do a select count(over_an_index) from table it uses 1.4minutes to read. The main issue is that this table is the main table of a system and each query is taking too long to complete. A table definition would be heplful here. How can I optimize the access of the database? any mysql variable I could use? or how can I load the index into memory? I have 32GB of ram XD plenty to useit into something more useful A configuration file would be helpful here. Thanks. My guess is that you have a innodb table and that you have unallocated innodb buffer pool. This is nothing more than a guess What is ram XD? -- Rob Wultsch wult...@gmail.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 slim MySQL?
On Mon, Jul 5, 2010 at 7:59 AM, Nima nima@gmail.com wrote: Hi folks, I'd like to install MySQL on an embedded system. It's a powerful x86-based computer with the only limitation of having a small-size flash ROM as its secondary storage. I tried installing MySQL from source which resulted in occupying 140 MB of disk space, while the maximum amount of flash memory I'm permitted to use is about 20-30 MB. So I'm wondering how to go about slimming MySQL down to the bare minimum. Following is the list of directories at the root of the installation directory: * bin/ * include/ * lib/ * libexec/ * mysql-test/ * share/ * sql-bench/ I think removing the 'include', 'mysql-test' and 'sql-bench' directories may be a good start, but I'm still far away from having a tiny little mysql! BTW, I tried MySQL Embedded, but it seems that it doesn't meet our needs. Any help would be highly appreciated :) *-- Nima Mohammadi* Have you considered SQLite? You probably want to compile your own version of MySQL. You probably want to remove debugging symbols* (which have been present since the mid 5.0 series, iirc), and any engines/character sets/etc you don't need. *Alternatively you can run the strip command. -- Rob Wultsch wult...@gmail.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: Got error 139 from storage engine (InnoDB)
On Sun, Jul 4, 2010 at 8:35 PM, James Corteciano ja...@linux-source.org wrote: Hi All, I have received error message ERROR 1030 (HY000) at line 167: Got error 139 from storage engine when importing dump database to MySQL server. The MySQL server is using InnoDB. I have google it and it's something problem on exceeding a row-length limit in the InnoDB table. Any have idea how to fix this? Thanks. Regards, James I can not recall having seen that error before. I did a slight amount of googling and it sounds like it might be a innodb tuning issue. Please post: 1. Any relevant entries in your error log file. 2. Your my.cnf. 3. You servers specs and whether the server also runs other daemons. -- Rob Wultsch wult...@gmail.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 slim MySQL?
On Mon, Jul 5, 2010 at 10:07 AM, Nima Mohammadi nima@gmail.com wrote: On Mon, Jul 5, 2010 at 8:26 PM, Rob Wultsch wult...@gmail.com wrote: Have you considered SQLite? You probably want to compile your own version of MySQL. You probably want to remove debugging symbols* (which have been present since the mid 5.0 series, iirc), and any engines/character sets/etc you don't need. *Alternatively you can run the strip command. -- Rob Wultsch wult...@gmail.com Well, I'm not in charge of deciding which RDBMS to use and the MySQL is needed for a program which has already been written. So it seems that we have no choice but to stick with MySQL. I guess for removing debug symbols I need to add the --without-debug option to the ./configure command. I think using these options would also be helpful: - --without-man - --without-docs - --without-ipv6 - --disable-largefile I'm not sure which engine we're going to use, so I'll have to defer this to another time. Is there any other work I could do to strip MySQL? *-- Nima Mohammadi* This is not a topic I have worked with previously. I image this may be a good topic to bring to a consulting company (specifically Percona and Open Query might work well) if you are unable to meet your size goals. -- Rob Wultsch wult...@gmail.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: Got error 139 from storage engine (InnoDB)
On Mon, Jul 5, 2010 at 9:06 AM, Rob Wultsch wult...@gmail.com wrote: On Sun, Jul 4, 2010 at 8:35 PM, James Corteciano ja...@linux-source.org wrote: Hi All, I have received error message ERROR 1030 (HY000) at line 167: Got error 139 from storage engine when importing dump database to MySQL server. The MySQL server is using InnoDB. I have google it and it's something problem on exceeding a row-length limit in the InnoDB table. Any have idea how to fix this? Thanks. Regards, James I can not recall having seen that error before. I did a slight amount of googling and it sounds like it might be a innodb tuning issue. Please post: 1. Any relevant entries in your error log file. 2. Your my.cnf. 3. You servers specs and whether the server also runs other daemons. -- Rob Wultsch wult...@gmail.com It would also be helpful for you to run the import with the verbose flag. Then we would have a better understanding of exactly what statement was causing the error. -- Rob Wultsch wult...@gmail.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: Connection Diagnostic Tool
On Mon, Jul 5, 2010 at 3:55 PM, michel compu...@videotron.ca wrote: I have been trying to figure this one out, but I don't have the skill sets here so I can use some help. I tried ' -h 127.0.0.1' in my bash shell and I get a command not found, so I am still really off-the-mark. Is there a place on the net I can look up what it does and how to run it? Thanks! I am pretty sure Michael that meant running the command line mysql client: mysql -uuser -ppass -h127.0.0.1 -e 'select hello world!' -- Rob Wultsch wult...@gmail.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: Updating 4 to 5
On Sat, Jul 3, 2010 at 4:00 AM, Grant Peel gp...@thenetnow.com wrote: - Original Message - From: Rob Wultsch wult...@gmail.com To: Grant Peel gp...@thenetnow.com Cc: mysql@lists.mysql.com Sent: Friday, July 02, 2010 3:57 AM Subject: Re: Updating 4 to 5 On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. It would be good if mentioned what release of the various series you were using or wanting to upgrade to. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). By restoring from backup for mysql a sql dump, or a filesystem backup? Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant It depends. The way I generally do upgrades is the following: 1. Identify the backup point for the current server. Do a 'mysqldump --all-databases --complete-insert' from it using the mysqldump from the version of mysql I will be using after the upgrade and record the 'show slave status' while it is running. 2. Import the backup on to the new server after removing any commands that would perform ddl on the mysql schema. 3. Setup replicaton and fail over to the new server at an opportune time. So, you should keep in mind a few things: 1. Between version of MySQL the table format changes, and it is generally worthing while to take advantage of the changes. 2. mysqlupgrade runs REPAIR TABLE which acts differently in different versions of MySQL See http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gp...@thenetnow.com Rob, Thanks for taking the time to reply, The mysql databases would be restored from a filesystem backup. The whole server, mysql and all, are FreeBSD 6.x I usually: Run a complete backup of all filesystems, (/,/var,/home,/user) to an NFS server, Build the new server, Restore all filesystems to the new disk (that is built with mysql 5), Run the mysql upgrade script, start the mysql servers, then fix any PHP issues (as php is upgraded from 4 to 5 as well). Comments please, -Grant That may be ok, it may not. Your methodology should be fine if you are only upgrading one release: 4.1 - 5.0 But it may not be ok for other upgrades: 4.0 - 5.0 (skipping 4.1) 4.0 - 5.1 (skipping 4.1 and 5.0) 4.1 - 5.1 (skipping 5.0) At this point MySQL 5.0 is near EOL, so it would probably be wise to upgrade to 5.1. It is unwise to skip upgrades so if you are going from 4.1 to 5.1, so you would need to do an intermediate upgrade to 5.0 . -- Rob Wultsch wult...@gmail.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: Reserving threads for root user
On Wed, Jun 30, 2010 at 8:29 AM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: On 6/30/2010 6:56 AM, Machiel Richards wrote: Good day all Sorry one more question. I have seen many questions about this on the web but no resolution yet. When MySQL runs out of threads, you are unable to stop / restart the database. Is there a way to reserve threads for the root user / database restarts? Yes. Don't give normal users the SUPER privilege. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_super Your applications and non-administrative users should be using accounts with the least privileges necessary to to their jobs. That way the extra connection allocated to the SUPER user accounts will not be consumed with non-administrative activities. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN That is the solution. It is worth note that the facebook patch set has a new variable reserved_super_connections which defaults to 10. It would be very useful to have this feature in mainline MySQL. -- Rob Wultsch wult...@gmail.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 connections not released
On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.zawrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: · When running ‘ show processlist’, the amount of processes were increasing the whole time. · From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. · From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com
Re: Updating 4 to 5
On Thu, Jul 1, 2010 at 5:44 PM, Grant Peel gp...@thenetnow.com wrote: Hi all, I have serveral servers running mysql 4, and need to update to mysql 5. It would be good if mentioned what release of the various series you were using or wanting to upgrade to. I have version 5 setup on a new dev server and will be cloning that to the old servers, then restoring all the data from backups (mysql databases included). By restoring from backup for mysql a sql dump, or a filesystem backup? Once I have restored the data from backups, I will neeed to run mysqlupgrade. My question is, will the mysqlupgrade script update all the mysql tables, (grant tables etc), as well as update all the users databases, or will there be other things that need to be done? -Grant It depends. The way I generally do upgrades is the following: 1. Identify the backup point for the current server. Do a 'mysqldump --all-databases --complete-insert' from it using the mysqldump from the version of mysql I will be using after the upgrade and record the 'show slave status' while it is running. 2. Import the backup on to the new server after removing any commands that would perform ddl on the mysql schema. 3. Setup replicaton and fail over to the new server at an opportune time. So, you should keep in mind a few things: 1. Between version of MySQL the table format changes, and it is generally worthing while to take advantage of the changes. 2. mysqlupgrade runs REPAIR TABLE which acts differently in different versions of MySQL See http://www.mysqlperformanceblog.com/2010/05/14/mysql_upgrade-and-innodb-tables/. -- Rob Wultsch wult...@gmail.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 connections not released
A bad assumption I make these days is that everyone has skip resolve off. If that is the case DNS issues will not impact you. Do you have that setting? Do you see errors on the interface? On Fri, Jul 2, 2010 at 1:05 AM, Machiel Richards machi...@rdc.co.za wrote: Hi From a thread found on the net it also suggests that it might be network or DNS related. The connections building up is the following: *** 1346. row *** Id: 903175 User: unauthenticated user Host: ip:51798 db: NULL Command: Connect Time: NULL State: login Info: NULL The servers however only uses ip connections and no DNS related lookups. From what I could find on the web though it seems that there is one session that is unable to authenticate and the authentication process does not complete causing it to get “stuck” This then causes all other requests to wait for the transaction to complete. Everyone who replied to these threads with the same issue stated that when they kill that one problem connection it brings everything back to normal again, however the problem happens regularly and if not picked up immediately eventually causes an inability to access the database to even kill processes, etc... So what I know now is that the problem is caused by a connection / transaction not completing. However what I need to find out now is why and how to resolve this. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Rob Wultsch [mailto:wult...@gmail.com] *Sent:* 02 July 2010 9:42 AM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Mysql connections not released On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: · When running ‘ show processlist’, the amount of processes were increasing the whole time. · From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. · From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting *Error! Filename not specified.* My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com
Re: Mysql connections not released
I do not know that part of the mysql source code well, but I am very sure you will take the dns hit even if all of your grants are ip based. I suggest you put put that setting into your cnf and bounce the instance. You will also never run into issues with max connect error if you set skip name resolve. It might be worthwhile to start watching a continuous ping from the webserver to the db server. Are you losing any packets? On Fri, Jul 2, 2010 at 1:48 AM, Machiel Richards machi...@rdc.co.za wrote: We do not use that setting at present no specifically because all hosts use ip’s to connect and not dns / hostnames. We can’t seem to find any errors thus the reason for truggling to pinpoint the exact cause. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Rob Wultsch [mailto:wult...@gmail.com] *Sent:* 02 July 2010 10:46 AM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Mysql connections not released A bad assumption I make these days is that everyone has skip resolve off. If that is the case DNS issues will not impact you. Do you have that setting? Do you see errors on the interface? On Fri, Jul 2, 2010 at 1:05 AM, Machiel Richards machi...@rdc.co.za wrote: Hi From a thread found on the net it also suggests that it might be network or DNS related. The connections building up is the following: *** 1346. row *** Id: 903175 User: unauthenticated user Host: ip:51798 db: NULL Command: Connect Time: NULL State: login Info: NULL The servers however only uses ip connections and no DNS related lookups. From what I could find on the web though it seems that there is one session that is unable to authenticate and the authentication process does not complete causing it to get “stuck” This then causes all other requests to wait for the transaction to complete. Everyone who replied to these threads with the same issue stated that when they kill that one problem connection it brings everything back to normal again, however the problem happens regularly and if not picked up immediately eventually causes an inability to access the database to even kill processes, etc... So what I know now is that the problem is caused by a connection / transaction not completing. However what I need to find out now is why and how to resolve this. Machiel Richards MySQL DBA Relational Database Consulting [image: RDC_Logo] *From:* Rob Wultsch [mailto:wult...@gmail.com] *Sent:* 02 July 2010 9:42 AM *To:* Machiel Richards *Cc:* mysql@lists.mysql.com *Subject:* Re: Mysql connections not released On Thu, Jul 1, 2010 at 11:58 PM, Machiel Richards machi...@rdc.co.za wrote: Good morning all A few days ago i posted some questions relating to mysql threads running out and not being able to restart the database. We had the same again this morning however this time I had a bit of a heads up and managed to get into the database before it went down (so to speak). What I found was the following: · When running ‘ show processlist’, the amount of processes were increasing the whole time. · From the details, it seemed that all the connections were coming from the website and were trying to make connections to the database, however the statuses never changed from connect to anything else, and yet the amount of connections kept on rising. · From what I could gather, the incoming processes were trying to connect to the database but the connections could not be released causing havoc. I have tried to find clues in log files, etc... but nothing turned up. I suspected that it could perhaps be php however this could not be verified yet. Can someone please provide me with some thoughts as to why this could be happening? I am fairly new to MySQL and not sure where else to go from here to find the root cause. Generally everything works fine, however , every now and then things suddenly seem to be going wrong... L Any help would be appreciated. Machiel Richards MySQL DBA Relational Database Consulting *Error! Filename not specified.* My first guess would be a networking problem. If you run ifconfig repeatedly do you notice the number of errors increasing? It would be good to look at both the database server and the web server. Also, until you figure the issue out you should be able to mitigate it by setting the connect timeout to a more aggressive value. -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com -- Rob Wultsch wult...@gmail.com
Re: MySQL Upgrading
On Wed, Jun 23, 2010 at 6:33 AM, Steven Staples sstap...@mnsi.net wrote: Hi, I am looking at upgrading my servers Debian version from Etch to Lenny, and in doing that, I think it will upgrade MySQL from 5.0.32 to the lenny version, which is 5.0.53 (I think). I have also been thinking about using the 'dotdeb' packages, which will upgrade it even further to 5.1.47. I have done this on a test server, and it all my stored procedures and stuff work fine, so now to my question. Will this break any replication if I don't upgrade my replication server that is still running 5.0.32 (until I upgrade that server as well, which could be a few weeks due to timing)? It will. I suggest upgrading to at least 5.0.67 if you have no fear of internal users, and the most recent version of 5.0 and 5.1 (I don't recall what they are) if you have fears of malicious users. The other issue, is that the replication server is running multiple instances of the same MySQL on different ports, so that I can replicate multiple sources to a single server (that has attached tape drives for backup purposes), is it possible to run both versions of mysql (the 5.0.32 and the 5.1.47)? granted, this is not the debian mailing list, just thought I would ask that last part ;) Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.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 setup replication - MySQL 5.0.x - Migration and new databases
On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de wrote: Hi, we do have different LAMP systems and recently I started to put some mysql databases on one, new master server. (RedHat, Fredora, MySQL 4.x - 5.0.xx) MySQL 4.X is EOL. I strongly suggest not using it for new projects, if you have the option. If possible, MySQL 5.1 is recommended, I did this by exporting some databases with mysqldump and importing tham on the new server. Now I'd like to add a slave mysqlserver and so I started to read some docs from the web and manuals from addison-wesley but some questions do remain or occur. What is the best way to copy the databases from the master to the slave? I thought that I can shut down the master and copie the database directory to the slave and than go on with the config, restarting the servers, etc. Doing so, do I have to lock any InnoDB tables or anything else? (May be I missunderstand some docs...) Perhaps I am misunderstanding what you are doing, but shutting down the master instance will make it inaccessible until it is restarted. Please read http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html . That document has the basics right, other than snapshoting. In terms of getting a snapshot, if you have a innodb only instance* (which is good idea), and can stop ddl commands, you can use mysqldump with the master-data and single-transaction flags in order to take a non-blocking dump suitable for replication use. For MyISAM only instances FLUSH TABLES WITH READ LOCK can be used. The easiest way to make a snapshot is to shut down the master instance and make a copy of the data files. When you restart the master make note of which binary log file it starts to write to. *Other than the mysql schema, of course. So far I only copied a few databases from the different servers to the new master. The second big question is: How to add new databases to the master after sucessfully running a master-slave-setup? Will the new database be copied/created on the slave automatically? Or do I have to create tham twice? New databases will be automatically created. Once you have the Master setup with binary logging you can inspect what it will have have the slave execute by using the mysqlbinlog command on the log files or the SHOW EVENT (http://dev.mysql.com/doc/refman/5.1/en/show-events.html) syntax. -- Rob Wultsch wult...@gmail.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: Temporary tables created on disk: 99%
On Tue, Jun 1, 2010 at 8:40 AM, John G. Heim jh...@math.wisc.edu wrote: On my db server, mysql has 2 gigabytes for temporary tables and yet its creating 99% of temporary tables on disk. According to mysqltuner, 99% of temporary tables are created on disk. Probably blobs: Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types (see Section 7.5.10, “How MySQL Uses Internal Temporary Tables”). Use of disk incurs a performance penalty, so include BLOB or TEXT columns in the query result only if they are really needed. For example, avoid using SELECT *, which selects all columns. From http://dev.mysql.com/doc/refman/5.0/en/blob.html If you can get us a query, explain, and table/index info we may be able to help you more. Also, you might want to consider creating a ram disk so that filesorts that must be done on disk might be done inexpensively. -- Rob Wultsch wult...@gmail.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: Anyone can help resolve this problem?
This seems like a wordpress problem, not mysql. On 5/30/10, Lancer skyleac...@gmail.com wrote: Hi there. Sorry for my poor english. Iinstalled MySQL 5.5 m3 though original RPM package and I modified the MySQL root password. When Iinstall WordPress, I got the error message like 'Error establishing adatabase connection'. But when I clear MySQL root password, everything goes well. I don't know that why. For security, Idon't want keep the empty password for production service. Anyone can help me to resolve it? Btw, phpbb using mysql (not mysqli, it normal working) extension, phpbb will return error message like 'Access Denied for r...@localhost (using password:NO), why display NO and why phpbb use root to connect to database? THX. -- Rob Wultsch wult...@gmail.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: source backup.sql - troubleshoot
On Tue, May 25, 2010 at 2:39 PM, Sydney Puente sydneypue...@yahoo.com wrote: Hello, I have a mysqldump file created by AutoMySQLBackup. And it hangs when I do a mysql source backup.sql It is 32 MB - it creates 4 tables and after creation of each table it populates it. Actually it only creates the 1st table before hanging. My first thought is it would be nice to echo each of the commands it is executing so I can tell whoch command it is that is the problem. My second thought is that there must be a quite a few troubleshooting techniques i could/should use - except I dont know hwtat they are. All advice gratefully received! BTW Platform redhat 5.2, Server version 5.0.54a-enterprise -Syd The easiest way to see what MySQL is working on is to start up another connections and run SHOW PROCESSLIST. Note that the command output will be truncated if it is long at all. If you need to see the entire query run SHOW FULL PROCESSLIST. Alternatively start up the mysql client with the --verbose flag. I don't recall in what version that became available. You will probably flood your term if you use this option. -- Rob Wultsch wult...@gmail.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: Security issues
On Mon, May 24, 2010 at 12:07 PM, Jerry Schwartz je...@gii.co.jp wrote: -Original Message- From: Rob Wultsch [mailto:wult...@gmail.com] Sent: Saturday, May 22, 2010 11:52 AM To: Martin Gainty Cc: mysql@lists.mysql.com Subject: Re: Security issues On Sat, May 22, 2010 at 5:44 AM, Martin Gainty mgai...@hotmail.com wrote: Good Morning Rob- one vulnerability (with UDFs) http://dev.mysql.com/tech-resources/articles/security_alert.html a manager considering a enterprise-wide security solution may want to consider Oracle Identity Manager (with Glassfish 3.2) http://under-linux.org/en/content/oracle-introduces-schedule-for-glassfish- 556/ Does this help? Martin Gainty Martin, Thank you for the reply. The guys across the street have a single page with cliff notes about every vulnerability effecting every supported version*. The page I noted was comprehensive. Martin, what you listed was a page with an single vuln and a page which looks like a product. [JS] This is always a tough call for a software developer. On the one hand, announcing an unfixed problem alerts users; but at the same time, it also alerts abusers. Some companies go one way, some go the other. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 I explicitly do not want a list of unfixed problems. I want a list of fixed issues and what versions are effected. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Security issues
On Mon, May 24, 2010 at 1:42 PM, Martin Gainty mgai...@hotmail.com wrote: Good Afternoon Rob- if you're implementing either glassfish or weblogic webserver your best fit solution would be Oracle Identity Manager there are 'other' identity solutions such as RSA which are 1)far more complex .. 2)virtually hackproof.. at random intervals RSA implements an alternate encryption algorithm with an alternate keysize RSA issues smart cards which contain sufficient biometric information to authenticate you (and pass the authentication token to the OS) does this help? Martin Gainty I am explicitly not setting up identity solutions or anything else. All I want is a page from mysql which lists security issues.and what versions are effected. I don't think that this is such an insane thought... -- Rob Wultsch wult...@gmail.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: Security issues
On Sat, May 22, 2010 at 5:44 AM, Martin Gainty mgai...@hotmail.com wrote: Good Morning Rob- one vulnerability (with UDFs) http://dev.mysql.com/tech-resources/articles/security_alert.html a manager considering a enterprise-wide security solution may want to consider Oracle Identity Manager (with Glassfish 3.2) http://under-linux.org/en/content/oracle-introduces-schedule-for-glassfish-556/ Does this help? Martin Gainty Martin, Thank you for the reply. The guys across the street have a single page with cliff notes about every vulnerability effecting every supported version*. The page I noted was comprehensive. Martin, what you listed was a page with an single vuln and a page which looks like a product. The grass is looking pretty darn green on the other side of the street. *And they support all the way back to 7.4, which is equivalent to 4.1 era. 2005 is not that long ago. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Security issues
Given the rather serious recent bug fixes I have been thinking a good bit about security. Does MySQL AB/Sun/Oracle maintain a page similar to http://www.postgresql.org/support/security.html which lists security issues and what releases they effected? -- Rob Wultsch wult...@gmail.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: Where to index - over 15m records and growing
Added whitespace for readabilty:
SELECT
COUNT(FlightRoutes.FlightID) AS Count,
FlightRoutes.Dep AS Dep,
FlightRoutes.Des AS Des
FROM FlightRoutes
LEFT JOIN IVAOData ON FlightRoutes.FlightID=IVAOData.FlightID
WHERE IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-30 23:59:50')
GROUP BY FlightRoutes.Dep, FlightRoutes.Des
ORDER BY COUNT(FlightRoutes.FlightID)
LIMIT 20;
First thing that pops to mind: Do you *really* mean left join?
Second thing:
How selective is
WHERE IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-30 23:59:50')
Test by running
SELECT COUNT(*)
FROM IVAOData
WHERE IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-01 00:00:00')
AND IVAOData.TrackerTime =UNIX_TIMESTAMP('2010-04-30 23:59:50')
If this is a large proportion of the row count then you are probably
in store for pain. It sounds like you are matching half the table. Big
(intermediate) result sets often end in pain.
Third thing:
My (rather sleepy) gut thinks your best bet is a a composite index on
the table IVAOData on the columns TrackerTime and FlightID. This will
make all access to the table in this query hit a covering index.
Fourth thing:
What do you intend to ask the database with this query. This query
smells overly broad.
--
Rob Wultsch
wult...@gmail.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 spatial functions (Was: Best index for searching on lat / long data i.e. decimal vs. float)
On Sat, May 1, 2010 at 11:12 PM, dan d...@tappin.ca wrote: Can any one help me with understanding the mysql spatial functions? I can only seem to find bits and pieces of how-to's etc. I have an existing table of lat / long data representing unique boundaries i.e. rectangles and I want to search the table to find the rectangle that bounds a specific point. Dan troll Please at least considered PostGIS. In my limited experience all the good GIS people I know use PG. Also spatial indexes are limited to MyISAM in MySQL, which is a significant limitation for many users. http://postgis.refractions.net/ /troll -- Rob Wultsch wult...@gmail.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: 4- Question
On Sun, May 2, 2010 at 11:50 PM, Junior Ortis jror...@gmail.com wrote: Hi guys, i have a server with 20GB InnoDb on a single database and i use just one user for its. I use mysql 5.4 1- If i change my SCSI 15k RPM to a SSD i will increase my performance ? Umm, maybe. More information is required. Does the entire dataset float in RAM? Does an IO bottleneck impact performance at all? What sort of SSD? How many drives in either configuration? 2- What is the fast method to a backup to this InnoDB database ? And the more easy ? And how i restore its ? 20GB ain't much data. Consider mysqldump --single-tranaction --master-data dump.sql . (You are storing binary logs so that master data is useful, right?) The restore is super simple with mysql dump.sql. The answer you may want is xtrabackup. If you go that route a plain text backup is still a good idea. 3-I need make a clean on one table, its something like: table 1 = 400k rows ( Index field ) table 2 = 15kk rows ( Index field ) table 3 = 150k rows ( Index field ) table 4 = 15kk rows ( Index field ) Its like: delete from table 4 where id NOT IN ( select id from table1 ) and id NOT IN ( select id from table2 ) id NOT IN ( select id from table3 ) Have others better methos to make its ? Define better. NOT IN with a sub query is generally a poor performer. In my experience removing 10k rows at a time I have found to be often ideal. 4- InnoDB have a optimize table ? For re-index or something to make a better performance http://tinyurl.com/32ysu4s I have lots of boxes and darn near never run optimize table on a innodb table. Exceptions are made for massive bloat when file per table is in use or statistics insanity that survives a analyze table. -- Rob Wultsch wult...@gmail.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: capacity planning.
On Mon, May 3, 2010 at 11:20 AM, raju.sakineti anjaneyara...@gmail.com wrote: HI everyone, could somebody help me on how to do capacity planning for mysql databases. if anyone have documentation please provide me. thanksregards varma http://www.amazon.com/Art-Capacity-Planning-Scaling-Resources/dp/0596518579 will get you started. -- Rob Wultsch wult...@gmail.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: Join syntax problem
I am reading this on a tiny screen but it looks like you need whitespace before the where. On 4/26/10, Gary gp...@paulgdesigns.com wrote: I cant seem to get this working. $query=SELECT im.image_id, im.caption, im.where_taken, im.description, im.image_file, im.submitted, kw.fox, kw.wolves, kw.wildlife, kw.american, kw.scenic, kw.birds, kw.africa, kw.eagles, kw.hunter . FROM *images AS im.JOIN keywords AS kw USING (image_id) . WHERE ky.image_id = im.image_id; Gets me this error message. 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 'keywords AS kw USING (image_id)WHERE ky.image_id = im.image_id' at line 1 Anyone see where I am going wrong? Thank you. Gary __ Information from ESET Smart Security, version of virus signature database 5063 (20100426) __ The message was checked by ESET Smart Security. http://www.eset.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.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: Slow query, unknown why
On Sun, Apr 25, 2010 at 9:12 AM, Yves Goergen nospam.l...@unclassified.de wrote: Hi, I'm still stuck with my SQL query that is slow but really shouldn't be. The problem is that I cannot create a simple test case. I could only provide you a whole lot of pages of PHP code and SQL queries to explain the problem. I have now three versions of my query. One with a sub select, which takes 40 ms and works. One with a left join instead, which takes 40 ms and works. And one with an inner join instead, which takes 3 ms and doesn't work. The number of left-joined rows should be around 5, so what can make it take 35 ms to join those handful of rows? MySQL server version is 5.0.67 and 5.1.41 (just updated). Here's a small impression of my query: SELECT t.TagId, t.TagName, tk.UserId FROM message_revision_tag mrt JOIN tag t USING (TagId) LEFT JOIN keylist tk ON -- Here's the left join (tk.KeylistId = t.ReadAccessKeylistId AND tk.UserId IN (22943, 10899)) WHERE mrt.MessageId = 72 AND mrt.RevisionNumber = 1 AND t.ReadAccessKeylistId IS NOT NULL; This is only a sub-query of a larger search query in my PHP application. MySQL workbench can't show query timings so I can'T say how long this part of the query takes. It's probably fast, but it is applied to ~600 other rows to determine whether they should be included in the results or not. Please post create table statements, show indexes and explain for any queries with which you want help. Please then post each of your attempted queries coupled with a written description of what you think you are asking the database for. -- Rob Wultsch wult...@gmail.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 Community Server 5.1.46 has been released
?id=40625) * SHOW CREATE VIEW returned invalid SQL if the definition contained a SELECT 'string' statement where the string was longer than the maximum length of a column name, due to the fact that this text was also used as an alias (in the AS clause). Because not all names retrieved from arbitrary SELECT statements can be used as view column names due to length and format restrictions, the server now checks the conformity of automatically generated column names and rewrites according to a predefined format any names that are not acceptable as view column names before storing the final view definition on disk. In such cases, the name is now rewritten as Name_exp_pos, where pos is the position of the column. To avoid this conversion scheme, define explicit, valid names for view columns using the column_list clause of the CREATE VIEW statement. As part of this fix, aliases are now generated only for top-level statements. (Bug#40277: http://bugs.mysql.com/bug.php?id=40277) * While looking for the shortest index for a covering index scan, the optimizer ignored that a clustered primary key read the entire table. (Bug#39653: http://bugs.mysql.com/bug.php?id=39653) * mysqlbinlog had a memory leak in its option-processing code. (Bug#38468: http://bugs.mysql.com/bug.php?id=38468) * The test for readline during configuration failed when trying to build MySQL in a directory other than the source tree root. (Bug#35250: http://bugs.mysql.com/bug.php?id=35250) Thanks, MySQL RE Team Hery Ramilison, Karen Langford, MySQL Release Engineers Database Group, Oracle. For the record I think it is really bad form that ~10 of ~50 bugs noted are non-public. This is really annoying. Trying to make judgments about risks of upgrading vs not upgrading with non-public bugs is very difficult. -- Rob Wultsch wult...@gmail.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: Loading 4.1.12 dump to 5.0.18 server
On Thu, Apr 22, 2010 at 1:42 PM, Larry Martell larry.mart...@gmail.com wrote: I need to load a dump from a 4.1.12 server to a 5.0.18 server. When I do that it fails with: Upgrading from an early 4.1 series to an incredibly early 5.0 series is a bad idea. Your first priority should be upgrading your destination to something 5.0.67 (min) or later. -- Rob Wultsch wult...@gmail.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: better way to backup 50 Gig db?
On Tue, Apr 20, 2010 at 11:03 AM, Gavin Towey gto...@ffn.com wrote: More good ideas from Andrew! Just a note though, I noticed someone added replication to a slave as a backup option. I really discourage that. Replication makes no guarantees that the data on your slave is the same as the data on your master. Unless you're also checking consistency, a slave should be treated as a somewhat unreliable copy of your data. Regards, Gavin Towey I would like to second this sentiment. Once you start looking for data inconsistencies on slaves you will be surprised how often you find them. -- Rob Wultsch wult...@gmail.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-bin log file
On Mon, Apr 19, 2010 at 1:07 AM, Johan De Meersman vegiv...@tuxera.be wrote: On Mon, Apr 19, 2010 at 6:48 AM, Rob Wultsch wult...@gmail.com wrote: And if your slave's IO lags badly enough this will hose you. Further True, but if you remove logs that haven't been transferred, yet, you lose your slave. Transfer of logs shouldn't be lagging that much, really, unless you're replicating over some POTS line. Don't forget that log transfer doesn't have to wait for processing on the slave. -- Agreed. 7 days is an absurd length of time for the io thread to lag. However, if someone has setup replication but not monitoring it, 7 days isn't that long. The behavior of mysql after a crash (that is, breaking the io thread) makes me weary of suggesting to a inexperienced user that they should turn on this features. In an ideal world the user should determined how many days of backups they need and further how much pitr. It might be very sane to say 30 days of daily backups with 7 days of pitr. One way or another this should be a conscious decision, not a copy and paste from a mailing list. -- Rob Wultsch wult...@gmail.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 corrupt a database please???
On Sun, Apr 18, 2010 at 10:39 AM, Suresh Kuna sureshkumar...@gmail.com wrote: open the file and remove some data and close it for both data file and index files, So the tables will be corrupted when access. On Sun, Apr 18, 2010 at 9:55 PM, Nurudin Javeri nsjav...@idh.com wrote: Hi all, I am hiring a few new junior DBA's and I want to put them thru a simple db repair training. Does anyone know how I can deliberately corrupt a MyISAM and InnoDB database in different ways please? So what I want to do is corrupt 3 MyISAM 100gb databases, 3 InnoDB 100gb databases - ALL WITH DIFFERENT ERROR MESSAGES and have these newbies fix them in a 2 hour period :-) I have fixed oodles of db's but NEVER thought I would see the say where I would WANT to corrupt a db on purpose, but that day is here and am looking for advise please. Thanks... Nunu Umm, shouldn't you train your Junion DBA to: 1. fail off of the corrupted servers. 2. restore from backup. 3. or at least get a non-junior dba and then have them shadow? I have a problem with the idea of repairing Innodb. Depending on where the corruption (checksum mismatch) has occurred it can be very difficult to get all the original data out. Don't get me wrong, there are way to do it, but it is a nasty endeavor. For that mater I don't trust repairing MyISAM all that much either. I try my very best to keep MyISAM out of production. In my opinion MyISAM should be treated as something one step higher than the blackhole engine. Put data in and you might be able to pull it out later. (don't get me wrong, packed myisam has its place...) -- Rob Wultsch wult...@gmail.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 corrupt a database please???
On Sun, Apr 18, 2010 at 11:07 AM, Jim Lyons jlyons4...@gmail.com wrote: You can remove the innodb logs and/or the innodb data file. You can also remove some of the individual .idb files (if you're using file-per-table option). He originally asked about how to provide a training excise about repairing a db. How the hell do you repair from not having data files? For that matter the recovery from lacking log files (and assuming a crashed state) is imho ugly as hell. -- Rob Wultsch wult...@gmail.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 corrupt a database please???
On Sun, Apr 18, 2010 at 11:13 AM, Andrés Tello mr.crip...@gmail.com wrote: What if the DBA ask for the backup? And those recommendations can be fixed or they have a very high chance of making recovery impossible? Who is the dba going to ask for a backup? Himself? The guy that puts backups on tape? One way or another the DBA damn well better know how to get a backup. Failing off of a server gets you on to a slave which should be sync'd with the master. If you restore from backup then you can run a pitr . In my opinion both of these options are usually superior to running repair table on a production server. That is if you like uptime. For the record innodb corruption is quite rare, at least in comparison to MyISAM corruption. If I get a call at 2AM and find a server having died due to innodb corruption I would fail off of the server. No ifs, no ands, not buts. I would assume: 1. Possible, perhaps even probably hardware issues if there is Innodb corruptions. 2. A failover takes a set amount of time. Repairing corruptions will usually take longer, perhaps much much longer. -- Rob Wultsch wult...@gmail.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: Recommended swap partition size
On Sun, Apr 18, 2010 at 12:04 PM, Eric Bergen eric.ber...@gmail.com wrote: Linux will normally swap out a few pages of rarely used memory so it's a good idea to have some swap around. 2G seems excessive though. Usually I prefer to have linux kill processes rather than excessively swapping. I've worked on machines before that have swapped so badly that it took minutes just to ssh to them. This is effectively a failure scenario that can last for a lot longer than it takes to restart/failover mysqld. For apache it means the clients will see errors until the load balancer health check drops the server out of rotation. The best solution in all cases is to keep an eye on swap in/out and memory usage so neither the crash nor the excessive swapping becomes a problem. Umm, you were probably horribly over io utilized. Swapping by itself will not kill perforance I have some boxes where mysql has leaked a metric crap ton of memory and swapping is ok. The leaked memory is swapped out and sits out in swap. Every now and a again I create more swap to keep the server happy. Swapping is often preferable to crash with unplanned downtime. Note that innodb_flush_method can implact this... -- Rob Wultsch wult...@gmail.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-bin log file
On Sun, Apr 18, 2010 at 8:58 PM, Angelina Paul arshup...@gmail.com wrote: How can I remove old mysql-bin log file in log directory? A mysql full backup will clear the old mysql bin log file or not? Thanks, Arshu Paul You probably want http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/purge-master-logs.html -- Rob Wultsch wult...@gmail.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: InnoDB Default Storage Engine
On Sun, Apr 18, 2010 at 8:31 PM, Angelina Paul arshup...@gmail.com wrote: I want to change the mysql default storage engine from MyISAM to InnoDB. What are the steps involved .Is it edit my.cnf file and add a line default-storage-engine=innodb and restart the mysql server? If you do not want to change any existing tables all you need to do is add the line to your cnf. How I can bring my databases with mixed storage engine down without any data loss. mysqladmin shutdown What steps I have to take if I encounter a page corruption in innodb tables. The right answer is restore from backup or failover to a slave. The answer you probably want is http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html why I am getting a message *Error*: *No query specified* when I run a show engines\g commands -version (5.0.45) Exactly what are you running? -- Rob Wultsch wult...@gmail.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-bin log file
On Sun, Apr 18, 2010 at 9:40 PM, Prabhat Kumar aim.prab...@gmail.com wrote: You can add a expire_logs_days Variable in my.cnf during the configuration of replication server. # expire_logs_days = 7 It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log swittch. And if your slave's IO lags badly enough this will hose you. Further it might well come in handy to an arbitrary number of bin logs for pirt purposes. -- Rob Wultsch wult...@gmail.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: InnoDB - 16GB Data
On Sat, Apr 10, 2010 at 12:10 AM, Junior Ortis jror...@gmail.com wrote: Hi Guys i have a dedicated mysql-server and neeed tips and sugestion to optimize its to a better performance. 1-) Here i have results from mysqltunner MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: toscaoSo Please enter your MySQL administrative password: General Statistics -- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.4.3-beta-community [OK] Operating on 64-bit architecture Storage Engine Statistics --- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 458M (Tables: 349) [--] Data in InnoDB tables: 15G (Tables: 73) [!!] Total fragmented tables: 47 Performance Metrics - [--] Up for: 29d 12h 8m 1s (334M q [131.330 qps], 153K conn, TX: 194B, RX: 77B) [--] Reads / Writes: 31% / 69% [--] Total buffers: 15.5G global + 16.2M per thread (50 max threads) [OK] Maximum possible memory usage: 16.3G (69% of installed RAM) [OK] Slow queries: 0% (386/334M) [OK] Highest usage of available connections: 46% (23/50) [OK] Key buffer size / total MyISAM indexes: 300.0M/87.3M [OK] Key buffer hit rate: 100.0% (78M cached / 22K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4M sorts) [OK] Temporary tables created on disk: 0% (548 on disk / 1M total) [OK] Thread cache hit rate: 99% (23 created / 153K connections) [OK] Table cache hit rate: 44% (467 open / 1K opened) [OK] Open file limit used: 1% (684/65K) [OK] Table locks acquired immediately: 99% (320M immediate / 320M locks) [!!] InnoDB data size / buffer pool: 15.5G/15.0G Recommendations - General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Variables to adjust: query_cache_size (= 8M) innodb_buffer_pool_size (= 15G) 2-) And here is my dedicate server i have (24GB ): 1 [ 0.0%] Tasks: 71 total, 2 running 2 [||| 7.8%] Load average: 0.11 0.18 0.19 3 [| 0.7%] Uptime: 62 days, 19:24:09 4 [| 0.7%] Mem[|16878/24165MB] Swp[| 0/5122MB] 3-) And my.cnf vim .my.cnf [client] #password = [your_password] port = 3306 socket = /tmp/mysql.sock # *** Application-specific options follow here *** # # The MySQL server # [mysqld] #large-pages # generic configuration options port = 3306 socket = /tmp/mysql.sock skip-locking skip-external-locking datadir = /disk3/Datareal/oficial/mysql net_buffer_length = 1024K join_buffer_size = 4M sort_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M table_cache = 1000 max_allowed_packet = 160M max_connections=50 max_user_connections=200 key_buffer = 300M key_buffer_size = 300M #thread_cache = 400 thread_stack = 192K thread_cache_size = 96 thread_concurrency = 8 #thread_stack = 128K default-character-set = utf8 innodb_flush_method=O_DSYNC innodb_buffer_pool_size= 15G innodb_additional_mem_pool_size=128M innodb_log_file_size= 256M innodb_log_buffer_size=72M innodb_flush_log_at_trx_commit=0 innodb_thread_concurrency=8 innodb_file_per_table=1 innodb_log_files_in_group=2 innodb_table_locks=0 innodb_lock_wait_timeout = 50 ~/.my.cnf 72L, 1570C Thanks guys for any tips/suggestion ! First, most performance comes from optimized table structures, index, and queries. Server tuning will not get you you all that much additions performance, if you have a semi sane configuration. What is your current bottleneck or performance problem? Anyways... here are some reactions: innodb_flush_log_at_trx_commit=0 ... THIS MEANS YOU CAN LOSE COMMITTED TRANSACTIONS. Read up on this. innodb_flush_method=O_DSYNC Any particular reason you aren't using O_DIRECT ? Read up on this. Why do you not have skip-name-resolve on? Read up on this. innodb_thread_concurrency... As you are running 5.4 you can probably set this to 0. Assuming you have 4 cores or less I wouldn't worry too much about this. I do not see log-bin... which would indicate that you don't have binary logging on. What is your disaster recover plan? To create an optimal cnf would require more knowledge about your workload and your hardware. -- Rob Wultsch wult...@gmail.com -- MySQL General
Re: upgrade from version 5.0.45
On Sun, Apr 4, 2010 at 9:29 PM, Walter Heck - OlinData.com li...@olindata.com wrote: Depending on the seriousness of your environment you can read the changelogs and upgrade if you don't see any showstoppers. I have hardly ever seen any problems with minor version upgrades of mysql. Of course what Rob says is true, and it is a good idea to test things out in a test environment first. But I know many environment where it is okay to just run the upgrade, as long as it is a minor version upgrade. I guess it depends on the type of production environment you are running in. be careful though! Walter Not everything that gets changed is in the change log. In particular there was a memory leak that I had (...still have...) to deal with that got fixed without any note in the change log. http://www.maatkit.org/doc/mk-upgrade.html is your friend. -- Rob Wultsch wult...@gmail.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: upgrade from version 5.0.45
On Tue, Mar 30, 2010 at 6:36 AM, Marco Baiguera marco.baigu...@gmail.com wrote: Hello everyone, i am quite new to mysql and i recently begin to work with a company who is using mysql 5.0.45 in production. i think this version is too old and would like to upgrade to the most recent 5.0.xx my os is CentOS release 5.3. is it safe to simply use yum upgrade mysql ? are there any important differences i should be aware of between 5.0.45 and 5.0.77 ? any diffferences in password encoding etc. ? the db is properly backed up and replicated on two 5.0.77 slaves. thank you Marco I would not simply upgrade. I would upgrade the test environment first and have the development team sign off that there were no bad effects caused by the upgrade. The first version of 5.0 that I think is particularly useable and not buggy is 5.0.67. I suggest that this is worth the upgrade. In theory there are not significant differences between 5.0 versions after GA other that bug fixes. I *do not* trust this. -- Rob Wultsch wult...@gmail.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: MySQLTuner
On Wed, Mar 31, 2010 at 9:47 PM, sangprabv sangpr...@gmail.com wrote: Hi, I run mysqltuner this morning and I got these warning: [!!] Key buffer size / total MyISAM indexes: 12.0G/23.2G [!!] Key buffer hit rate: 76.9% [!!] Query cache efficiency: 0.0% [!!] Temporary tables created on disk: 27% [!!] Table cache hit rate: 5% And mysqltuner recommends to adjust these setting: key_buffer_size ( 23.2G) query_cache_limit ( 1M, or use smaller result sets) tmp_table_size ( 64M) max_heap_table_size ( 128M) table_cache ( ) My physical RAM is only 16Gb. I am afraid it will freezed the server if I set key_buffer_size ( 23.2G). Any suggestion to optimize it? Thanks for any response. sangprabv sangpr...@gmail.com I would not worry much at all about the three top lines of what you posted. Before I became a DBA I worked as a dev on a not small website where we used the ultra conservative default configuration. If you do no know what you are doing you can easily cause far more harm than good by mistuning your configuration. In fact, if you have 16GB of RAM and have devoted 12GB to key buffer you have probably significantly over allocated. Keep in mind that MyISAM using the OS file system cache to cache table data. 1. Are you currently having a performance problem? If so, what queries are slow? Posting the query + the explain would help us help you. 2. Are you using any other table types other than MyISAM? If not, why not? MyISAM has horrible characteristics for many workloads due to a lack of row level locks. MyISAM also can easily corrupt. Keep in mind that this will change how a server should be tuned 3. It is probably worthwhile to try to tune table and thread cache to sane levels. 4. Having well indexed and well written queries will probably help you far more than any server tunning. 5. How are you going to test any changes to configuration? -- Rob Wultsch wult...@gmail.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 Certification exams
On Sun, Jan 3, 2010 at 11:06 PM, machiel.richards machiel.richa...@gmail.com wrote: I am trying to get hold of sample exams, etc... that I can use for preparation to the exams in order to test myself and make sure that I have all the knowledge I need. I have passed all the 5.0 dev and dba exams as well as 5.1 cluster. Buy the official guides and read them well: http://www.amazon.com/MySQL-5-0-Certification-Study-Guide/dp/0672328127 That has all the information you need and practice exam questions. The certification exams are not that hard if you work with MySQL consistently and read through the guide. -- Rob Wultsch wult...@gmail.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: Rename Database - Why Would This Not Work?
On Mon, Nov 23, 2009 at 7:37 AM, Johan De Meersman vegiv...@tuxera.bewrote: You don't even need to stop the server afaik. As mentioned previously, though, works for MyISAM only. While this is strictly true there are some big caveats (flushing tables, etc). It is safer to shut down the database before moving files around. -- Rob Wultsch wult...@gmail.com
Re: Rename Database - Why Would This Not Work?
On Sat, Nov 21, 2009 at 2:43 PM, Robinson, Eric eric.robin...@psmnv.comwrote: DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. Like I said, there are only MyISAM tables in the database, so would there be any risks associated with my simple approach? (Also there are no stored procedures because this is MySQL 4.1.22.) --Eric Within your specific circumstances what you suggest should be safe. -- Rob Wultsch wult...@gmail.com
Re: Rename Database - Why Would This Not Work?
DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. On Sat, Nov 21, 2009 at 10:38 AM, Robinson, Eric eric.robin...@psmnv.comwrote: I used a simple procedure to rename my MySQL 4.1.22 database, which has only My-ISAM tables: 1. Stopped MySQL 2. Renamed the database directory from olddbname to newdbname 3. Started mysql At this point, I grepped for 'olddbname' and found that many of the old .MYI files still had references to 'olddbname'. So I... 4. Did mysqlcheck -o newdbname Then all the references to 'olddbname' were removed from the index files. I then started our application and everything seems to be working fine using the new database name. Yet I'm still worried because when I Google it, I see people talking about lots of different ways to do a database rename, and people are making it sound like a complicated, dangerous procedure. Why would my simple approach not work? Should I be watching for potential problems down the road because I did it this way? -- Eric Robinson Director of Information Technology Physician Select Management, LLC 775.885.2211 x 111 -- Rob Wultsch wult...@gmail.com
Re: Optimizing my.cnf
On Mon, Oct 5, 2009 at 6:12 PM, sangprabv sangpr...@gmail.com wrote: I have Dell PE2950iii with 16GB of RAM, and 1 Quadcore processor @2.00G. Installed with MySQL 5.075 on 64bit Ubuntu Jaunty. I have these parameters in my.cnf: blah blah blah... This heavily depends on workload. Are you using innodb? etc... -- Rob Wultsch wult...@gmail.com
Re: Is myisam_repair_threads considered safe
I decided not to use it because of bug 47444 which is a continuation of earlier bugs... http://bugs.mysql.com/bug.php?id=47444 On Thu, Sep 17, 2009 at 4:22 PM, Eric Bergen eric.ber...@gmail.com wrote: It's been in mysql for at least 7 years. http://ebergen.net/wordpress/2009/04/11/longest-beta-ever-myisamchk-parallel-recover/ On Thursday, September 10, 2009, Rob Wultsch wult...@gmail.com wrote: myisam_repair_threads If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1. Note :Multi-threaded repair is still beta-quality code. The note is present is all versions of MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_repair_threads http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_myisam_repair_threads http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html#sysvar_myisam_repair_threads Can anyone comment about whether this setting is safe, and if so on what major versions? -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eric.ber...@gmail.com -- Eric Bergen eric.ber...@gmail.com http://www.ebergen.net -- Rob Wultsch wult...@gmail.com
Is myisam_repair_threads considered safe
myisam_repair_threads If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1. Note :Multi-threaded repair is still beta-quality code. The note is present is all versions of MySQL manual: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_repair_threads http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_myisam_repair_threads http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html#sysvar_myisam_repair_threads Can anyone comment about whether this setting is safe, and if so on what major versions? -- Rob Wultsch wult...@gmail.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 equi-joins faster than left joins?
On Thu, Mar 12, 2009 at 8:41 AM, mos mo...@fastmail.fm wrote: I have 3 tables that are 1:1 and will always have a row for a given product,_code date. If I want to join them together, is it going to be faster to use an equi join or a left join, or does it matter? IIRC:With an inner join the optimizer has more latitude to reorganize the join order and can short circuit the process if there is not a corresponding row. This is completely secondary to getting the right result set for your query. If there is the possibility of a row in the parent table not joining and needing to be part of the result then you must use left. -- Rob Wultsch wult...@gmail.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: Replica questions
When you start up replication the data should be recorded in the master.info in your data dir. [r...@someserver ~]# cat /var/lib/mysqldata/master.info 14 MASTERHOST-bin.01 MASTERPOS 192.168.0.2 REPLICATION_USER REPLICATION_PASSWORD 3306 60 0 I am not familiar with any setups where the master.info would not be created, and I am too lazy too look for a setting that would lead to such an silly/annoying state. On Thu, Mar 12, 2009 at 10:03 AM, Bryan Irvine sparcta...@gmail.com wrote: Do I jsut need to monitor better and manually add it should the log and pos change? -Bryan On Wed, Mar 11, 2009 at 7:05 PM, Baron Schwartz ba...@xaprb.com wrote: That's deprecated too :-) On Wed, Mar 11, 2009 at 9:17 PM, Cui Shijun rancp...@gmail.com wrote: hi, #2. try adding the information of master into my.cnf then restart the server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=wult...@gmail.com -- Rob Wultsch wult...@gmail.com wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replica questions
On Wed, Mar 11, 2009 at 4:03 PM, Bryan Irvine sparcta...@gmail.com wrote: I've got 2 questions about my newly set up master-slave replica. 1 When I run load data from master; I get an error that I do not have RELOAD privileges and then it boots the slave offline. I've run GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO root@'%' IDENTIFIED BY 'password'; flush privileges; but it still does the same thing. 2 Is there a way to get a slave to automatically re-connect as the slave without having to know the masters binlog and position? Each time it reboots I have to manually add it back in with the change master to ... command. TIA -Bryan #1. This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL. #2. Replication info should be being recorded on the master.info in your data dir. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysqlimport remote host problem
On Tue, Mar 10, 2009 at 7:16 PM, René Fournier m...@renefournier.com wrote: OK, I've managed to do the same thing with just the mysql command line program: mysql -h 192.168.0.224 -u root -p alba2 /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql Works great. However, the sql file is normally gzipped, so Can I ungzip the file on the fly (and without removing the .gzip version) and pipe the contents as I did above? (Yes, I'm UNIX-impaired.) Something like: mysql -h 192.168.0.224 -u root -p alba2 gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz But so that it works... ...Rene Pipe is your friend. You probably want something like: gzip -dc /Backup/Latest/alba2_2009-03-10_00h45m.Tuesday.sql.gz | mysql -h 192.168.0.224 -u root -p alba2
Re: WHERE vs. ON
On Tue, Feb 3, 2009 at 1:54 PM, Jerry Schwartz jschwa...@the-infoshop.com wrote: From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: Tuesday, February 03, 2009 1:03 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: WHERE vs. ON ON condition uses the same columnname from both source and target tables whereas any column expressions can go in the WHERE clause... [JS] That isn't necessarily true. ON a.x = b.y Is valid. You don't even need to reference either table in the join. ON ROUND( RAND() ) (yes, I have found the need to use that) Join by rand! The on clause is just something evaluated for each row that if it returns an expression that evaluates to true will allow the row to be joined. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: moving from 3.23.58 to 5.0.45
On Thu, Oct 30, 2008 at 5:16 AM, Obantec Support [EMAIL PROTECTED]wrote: Hi are there any doc's on how to migrate DB in 3.23.58 format to 5.0.45 moving from a Fedora Core3 to Centos5.2 server. Mark http://www.justfuckinggoogleit.com/?q=mysql+upgrade First result. -- Rob Wultsch
Re: deleted user...lost access to appointment data
I sooo hear that now. That is the funniest thing I have heard this month. On Mon, Oct 27, 2008 at 8:36 PM, Fish Kungfu [EMAIL PROTECTED] wrote: For some reason when I started reading this, I heard Humphrey Bogart's voice speaking it like the beginning of one of his dark and rainy black white detective movies. ;-) On Mon, Oct 27, 2008 at 10:25 PM, Dr Michael Daly [EMAIL PROTECTED] wrote: It all started when I deleted one of the users, 'michael', from within a database program -- lo and behold 'michael' disappeared from all groups along with the data. 'michael' was an admin user as was another admin user, but the data belonged to 'michael' I recreated this user but could not see the data So I restored the respective mysql database, named pbcs7, from a tar backup (I now understand this is not the best way to backup mysql), but the data remained elusive. The database program - PBCS which is an appointment system - resides in /home/www/public_html and the respective mysql database (MySQL Vers 4.1.20) is in /var/lib/mysql What am I missing please? Steps for restoration of the backup (permissions were found to be preserved): 1. the backed up 'pbcs7' database files were temporarily restored using the tar -zxvf command to: /var/lib/restore/var/lib/mysql/pbcs7/ 2. made copy of files in use (ie the files from which data was missing) to...restore2 directory cp -a /var/lib/mysql/pbcs7/* /var/lib/restore2/ 3. /sbin/service mysqld stop 4. restored pbcs7 database files to the working mysql directory cp -a /var/lib/restore/var/lib/mysql/pbcs7/* /var/lib/mysql/pbcs7/ 5. /sbin/service mysqld start Any help would be great Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
Re: MySQL Sort by Array
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field it is ORDER BY ... and in this case the ... is the function described above. On Wed, Oct 22, 2008 at 12:24 PM, Jim Lyons [EMAIL PROTECTED] wrote: I'm not familiar with order by field (unless field is a UDF). I know of order by binary. Is this standard mysql syntax? On Wed, Oct 22, 2008 at 10:42 AM, Peter Brawley [EMAIL PROTECTED] wrote: ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? ORDER BY FIELD( id, 5, 34, 9, 25 ) PB - Keith Spiller wrote: Hi Guys, I'm trying to sort by a particular order: SELECT * FROM tablename WHERE id='5' OR id='9' OR id='25' OR id='34' ORDER BY id(5, 34, 9, 25) Can anyone tell me the proper syntax to accomplish this task? Thanks for your help. Keith No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.175 / Virus Database: 270.8.2/1739 - Release Date: 10/22/2008 7:23 AM -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
Re: Error Code 28
On Tue, Oct 21, 2008 at 8:26 AM, Heston James - Cold Beans [EMAIL PROTECTED] wrote: Afternoon all, I have a SELECT query which is returning the following error: (InternalError) (3, Error writing file '/tmp/MYqlGnfn' (Errcode: 28)) After doing a little searching on google all signs seem to point to a lack of disk space to be able to store the query results. However, I have several hundred MB left on the storage device and the database itself, in its entirety is only around 19Mb in size so it sees very strange to be causing that. Here is a quick output from 'fd -h' which displays the space on my storage device. FilesystemSize Used Avail Use% Mounted on rootfs973M 595M 330M 65% / udev 10M 20K 10M 1% /dev /dev/disk/by-label/ROOT_FS 973M 595M 330M 65% / /dev/disk/by-label/ROOT_FS 973M 595M 330M 65% /dev/.static/dev tmpfs 126M 0 126M 0% /lib/init/rw tmpfs 126M 0 126M 0% /dev/shm tmpfs 8.0M 0 8.0M 0% /rw/tmp Can anyone offer any suggestions as to what might be causing this issue and anything I can do to correct this? I'd really appreciate some help. I'm running MySQL 5 on a Debian based system. If you need any more information what so ever, please let me know. Cheers in advance, Heston 1. Just because your data set is small does not mean that mysql will not create a larger temporary file to store a temporary table. 2. If I had to guess I would say some sort of quota is in effect, also possibly for /tmp/ in particular. -- Rob Wultsch
Re: mysql is dead slow
1, Subqueries should be avoid when using mysql. Rewriting to be using joins will probably solve your problem. 2. Explain is your friend. On Mon, Oct 20, 2008 at 4:38 PM, Sangeetha [EMAIL PROTECTED] wrote: Hi, Is something wrong with a sql query like this? (SELECT commentname, count(comments.ID) as comid, 'comments' as section from comments where commentname IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by commentname) UNION ALL (SELECT Username, count(ID), 'questions' from questions where Username IN (SELECT DISTINCT commentname from comments where id=35037 ORDER BY commentname)Group by Username) It is dead slow... almost doesnt work.. is it the query or is my DB set up wrong? When i had records of around 500 rows it worked fine.. now i have around 6 rows its dead slowPlease help me... -- Rob Wultsch
Re: mysql is dead slow
On Mon, Oct 20, 2008 at 8:30 PM, Perrin Harkins [EMAIL PROTECTED] wrote: On Mon, Oct 20, 2008 at 10:15 PM, Sangeetha [EMAIL PROTECTED] wrote: Gosh I wonder why mysql does not support SEubqueries.. It just hangs in the copying to tmp table status. Atleast I know whats wrong... Thanks very much It supports them, but won't optimize them well until 5.2. There's some good info here: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - Perrin I'm pretty sure 5.2 became 6.0 6.0 is two major versions out The last major release happened 3 years ago -- Rob Wultsch
Re: mysql is dead slow
On Mon, Oct 20, 2008 at 9:31 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Mon, Oct 20, 2008 at 8:30 PM, Perrin Harkins [EMAIL PROTECTED] wrote: On Mon, Oct 20, 2008 at 10:15 PM, Sangeetha [EMAIL PROTECTED] wrote: Gosh I wonder why mysql does not support SEubqueries.. It just hangs in the copying to tmp table status. Atleast I know whats wrong... Thanks very much It supports them, but won't optimize them well until 5.2. There's some good info here: http://www.xaprb.com/blog/2006/04/30/how-to-optimize-subqueries-and-joins-in-mysql/ - Perrin I'm pretty sure 5.2 became 6.0 6.0 is two major versions out The last major release happened 3 years ago What I should have said is: five two is six oh five one comes before six oh five oh three years old -- Rob Wultsch
Re: Confusion over query stratergy
On Fri, Oct 17, 2008 at 2:12 AM, Ian Christian [EMAIL PROTECTED] wrote: Hi all, I'm trying to work out the difference in a field between the last 2 updates in an updates table. I'm doing this as shown below: mysqlSELECT - (@in - AcctInputOctets) AS AcctInputOctets, - (@out - AcctOutputOctets) AS AcctOutputOctets, - (@in := AcctInputOctets), - (@out := AcctOutputOctets) -FROM updates -WHERE acctuniqueid = '4b9fe4a361344536' -ORDER BY updates.AcctSessionTime DESC LIMIT 2 - ; +--+--+--++ | AcctInputOctets | AcctOutputOctets | (@in := AcctInputOctets) | (@out := AcctOutputOctets) | +--+--+--++ | 18446744073654284768 | 18446744073171813223 | 55266848 | 537738393 | | 9508 |18620 | 55257340 | 537719773 | +--+--+--++ 2 rows in set (0.02 sec) mysql explain(query above) *** 1. row *** id: 1 select_type: SIMPLE table: updates type: ref possible_keys: AcctUniqueID key: AcctUniqueID key_len: 34 ref: const rows: 327 Extra: Using where; Using filesort 1 row in set (0.00 sec) As can be seen, this query uses a key, and runs well. However, I only require the 2nd row of that dataset. I couldn't figure out a better way of doing it than this: mysql SELECT AcctInputOctets, AcctOutputOctets FROM - (SELECT - (@in - AcctInputOctets) AS AcctInputOctets, - (@out - AcctOutputOctets) AS AcctOutputOctets, - (@in := AcctInputOctets), - (@out := AcctOutputOctets) -FROM updates -WHERE acctuniqueid = '4b9fe4a361344536' -ORDER BY updates.AcctSessionTime DESC LIMIT 2 - ) AS t1 LIMIT 1,2 - ; +-+--+ | AcctInputOctets | AcctOutputOctets | +-+--+ |9508 |18620 | +-+--+ 1 row in set (0.02 sec) This does exactly what I want, but to me feels wrong, I think I'm missing a trick to doing this 'the right way'. Also, look at how the query runs: mysql explain SELECT AcctInputOctets, AcctOutputOctets FROM - (SELECT - (@in - AcctInputOctets) AS AcctInputOctets, - (@out - AcctOutputOctets) AS AcctOutputOctets, - (@in := AcctInputOctets), - (@out := AcctOutputOctets) -FROM updates -WHERE acctuniqueid = '4b9fe4a361344536' -ORDER BY updates.AcctSessionTime DESC LIMIT 2 - ) AS t1 LIMIT 1,2 - \G *** 1. row *** id: 1 select_type: PRIMARY table: derived2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: *** 2. row *** id: 2 select_type: DERIVED table: updates type: ALL possible_keys: AcctUniqueID key: AcctUniqueID key_len: 34 ref: rows: 28717165 Extra: Using filesort 2 rows in set (0.02 sec) Apparently, it's doing a full table scan over all 29 million records. Whilst this query appears to run fast still, surly it's not right that a full table scan is needed? Thanks, Ian Hi, In my experience derived table performance often sucks. This does not feel like a place where it should suck, though. My guess is that the limit is messing with EXPLAIN's head. I have experienced LIMIT causing EXPLAIN output to show full table scans while the query performance did not act that way, and the servers stats did not show it either. How long does the second query actually take to run? -- Rob Wultsch
Re: Confusion over query stratergy
On Fri, Oct 17, 2008 at 9:56 AM, Rob Wultsch [EMAIL PROTECTED] wrote: On Fri, Oct 17, 2008 at 2:12 AM, Ian Christian [EMAIL PROTECTED]wrote: Hi all, I'm trying to work out the difference in a field between the last 2 updates in an updates table. I'm doing this as shown below: mysqlSELECT - (@in - AcctInputOctets) AS AcctInputOctets, - (@out - AcctOutputOctets) AS AcctOutputOctets, - (@in := AcctInputOctets), - (@out := AcctOutputOctets) -FROM updates -WHERE acctuniqueid = '4b9fe4a361344536' -ORDER BY updates.AcctSessionTime DESC LIMIT 2 - ; +--+--+--++ | AcctInputOctets | AcctOutputOctets | (@in := AcctInputOctets) | (@out := AcctOutputOctets) | +--+--+--++ | 18446744073654284768 | 18446744073171813223 | 55266848 | 537738393 | | 9508 |18620 | 55257340 | 537719773 | +--+--+--++ 2 rows in set (0.02 sec) mysql explain(query above) *** 1. row *** id: 1 select_type: SIMPLE table: updates type: ref possible_keys: AcctUniqueID key: AcctUniqueID key_len: 34 ref: const rows: 327 Extra: Using where; Using filesort 1 row in set (0.00 sec) As can be seen, this query uses a key, and runs well. However, I only require the 2nd row of that dataset. I couldn't figure out a better way of doing it than this: mysql SELECT AcctInputOctets, AcctOutputOctets FROM - (SELECT - (@in - AcctInputOctets) AS AcctInputOctets, - (@out - AcctOutputOctets) AS AcctOutputOctets, - (@in := AcctInputOctets), - (@out := AcctOutputOctets) -FROM updates -WHERE acctuniqueid = '4b9fe4a361344536' -ORDER BY updates.AcctSessionTime DESC LIMIT 2 - ) AS t1 LIMIT 1,2 - ; +-+--+ | AcctInputOctets | AcctOutputOctets | +-+--+ |9508 |18620 | +-+--+ 1 row in set (0.02 sec) This does exactly what I want, but to me feels wrong, I think I'm missing a trick to doing this 'the right way'. Also, look at how the query runs: mysql explain SELECT AcctInputOctets, AcctOutputOctets FROM - (SELECT - (@in - AcctInputOctets) AS AcctInputOctets, - (@out - AcctOutputOctets) AS AcctOutputOctets, - (@in := AcctInputOctets), - (@out := AcctOutputOctets) -FROM updates -WHERE acctuniqueid = '4b9fe4a361344536' -ORDER BY updates.AcctSessionTime DESC LIMIT 2 - ) AS t1 LIMIT 1,2 - \G *** 1. row *** id: 1 select_type: PRIMARY table: derived2 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 Extra: *** 2. row *** id: 2 select_type: DERIVED table: updates type: ALL possible_keys: AcctUniqueID key: AcctUniqueID key_len: 34 ref: rows: 28717165 Extra: Using filesort 2 rows in set (0.02 sec) Apparently, it's doing a full table scan over all 29 million records. Whilst this query appears to run fast still, surly it's not right that a full table scan is needed? Thanks, Ian Hi, In my experience derived table performance often sucks. This does not feel like a place where it should suck, though. My guess is that the limit is messing with EXPLAIN's head. I have experienced LIMIT causing EXPLAIN output to show full table scans while the query performance did not act that way, and the servers stats did not show it either. How long does the second query actually take to run? *How long does the second query actually take to run compared to first? Also, you could do this as a join, and it would probably be somewhat cleaner -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim)
Re: Confusion over query stratergy
On Fri, Oct 17, 2008 at 10:31 AM, Ian Christian [EMAIL PROTECTED] wrote: 2008/10/17 Rob Wultsch [EMAIL PROTECTED]: *How long does the second query actually take to run compared to first? Actually, really quickly - so quickly that I also suspected that a full table scan was not taking place. I'd like to understand how the output of EXPLAIN can differ from the actual strategy used, why is there this difference (if indeed, there is)? This question is now redundant, as we're approaching the problem from a different angle, but this will bug me if I don't get to understand it :) Thanks! flush status; //run your query show status like 'Handl%'; That will tell you how much work it actually did to grab the info. Remember that EXPLAIN only shows estimates of the amount of work required to perform a query. My guess is that the limit confuses the optimizer and it shows a worst case scenario (full table scan) for the query. Note that the explain is still saying that it will us an index. -- Rob Wultsch
Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?
On Fri, Oct 17, 2008 at 1:33 PM, Rene Fournier [EMAIL PROTECTED] wrote: Okay, I realize that query won't work, but that's essentially want I want to do: Add four months to the current date, then return the first day of that month, e.g.: floor ( 2008-10-16 + 4 months ) = 2009-02-1 Is there a nice SQL way of achieving this? ...Rene SELECT date(now() + INTERVAL 4 MONTH - INTERVAL day(now())-1 DAY); -- Rob Wultsch
Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?
On Fri, Oct 17, 2008 at 2:15 PM, Ian Christian [EMAIL PROTECTED] wrote: 2008/10/17 Rob Wultsch [EMAIL PROTECTED]: On Fri, Oct 17, 2008 at 1:33 PM, Rene Fournier [EMAIL PROTECTED] wrote: Okay, I realize that query won't work, but that's essentially want I want to do: Add four months to the current date, then return the first day of that month, e.g.: floor ( 2008-10-16 + 4 months ) = 2009-02-1 Is there a nice SQL way of achieving this? ...Rene SELECT date(now() + INTERVAL 4 MONTH - INTERVAL day(now())-1 DAY); Be aware that only 1 suggested answer was correct :) mysql SELECT date( date(2008-10-31) + INTERVAL 4 MONTH - INTERVAL day(2008-10-31)-1 DAY) as d\G *** 1. row *** d: 2009-01-29 mysql select cast(date_format( date(2008-10-31) + interval 4 month,%Y-%m-01) as date) as d \G *** 1. row *** d: 2009-02-01 Woops! Good catch. -- Rob Wultsch
Re: FLOOR(DATE_ADD(CURDATE(), INTERVAL 4 MONTH)) -- ?
On Fri, Oct 17, 2008 at 2:19 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Fri, Oct 17, 2008 at 2:15 PM, Ian Christian [EMAIL PROTECTED]wrote: 2008/10/17 Rob Wultsch [EMAIL PROTECTED]: On Fri, Oct 17, 2008 at 1:33 PM, Rene Fournier [EMAIL PROTECTED] wrote: Okay, I realize that query won't work, but that's essentially want I want to do: Add four months to the current date, then return the first day of that month, e.g.: floor ( 2008-10-16 + 4 months ) = 2009-02-1 Is there a nice SQL way of achieving this? ...Rene SELECT date(now() + INTERVAL 4 MONTH - INTERVAL day(now())-1 DAY); Be aware that only 1 suggested answer was correct :) mysql SELECT date( date(2008-10-31) + INTERVAL 4 MONTH - INTERVAL day(2008-10-31)-1 DAY) as d\G *** 1. row *** d: 2009-01-29 mysql select cast(date_format( date(2008-10-31) + interval 4 month,%Y-%m-01) as date) as d \G *** 1. row *** d: 2009-02-01 I should have said: SELECT DATE( NOW() )-INTERVAL day( NOW() )-1 DAY + INTERVAL 4 MONTH; but yeah, the date_format is a heck of a lot cleaner. Learn something new everyday... -- Rob Wultsch
Re: Stored proc - dynamic sql in cursor
On Thu, Oct 16, 2008 at 12:31 PM, Olaf Stein [EMAIL PROTECTED] wrote: Hi all, I am running into some issues with what I am trying to do in a stored proc. Basically I am trying to find records related to certain individuals in other tables in the databases and if there are any, tell me how many. Instead of doing this for each of these tables individually I use a cursor: DECLARE tnames CURSOR FOR select table_name from information_schema.tables where table_schema='agpv2' and table_name like 'ad%' and table_name not like '%headers' order by table_name desc; to get all the tables I need. Now I loop over the result set of this cursor and want to dynamically insert the tablename into a second cursor. From what I read (and tried) that does not work: OPEN tnames; REPEAT FETCH tnames INTO tablename; IF NOT tnames_done THEN SELECT tablename; BEGIN DECLARE resadi INT; DECLARE adi_done INT DEFAULT 0; DECLARE adi CURSOR FOR select count(*) from tablename a join individual i on a.ident=i.ident where fid=agpfid; OPEN adi; FETCH adi INTO resadi; CLOSE adi; IF (resadi0) THEN select resadi as adi_wps; END IF; The cursor does not use tablename as a variable. What does work is using prepared statements: SET @stmt_text=CONCAT(select count(*) from ,tablename, a join individual i on a.ident=i.ident where fid=,agpfid); PREPARE stmt FROM @stmt_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; The problem with this is that I only want the result of the query if count(*) 0 as there are many tables I am looking in and most have no reference to individual so I do not want them in the output and this just executes the statement. Is there any way I can dynamically manipulate the string fro the cursor. Or, is there anyway I can catch the EXECUTE stmt output and look at it before outputting it? Thanks Olaf Here is the full proc as I would like it to work: DELIMITER // DROP PROCEDURE IF EXISTS show_pheno// CREATE PROCEDURE show_pheno(agpfid INT) READS SQL DATA SQL SECURITY INVOKER COMMENT 'shows phenotypes for given family id' BEGIN BEGIN DECLARE tablename TEXT; DECLARE tnames_done INT DEFAULT 0; DECLARE tnames CURSOR FOR select table_name from information_schema.tables where table_schema='agpv2' and table_name like 'ad%' and table_name not like '%headers' order by table_name desc; DECLARE CONTINUE HANDLER FOR NOT FOUND SET tnames_done=1; OPEN tnames; REPEAT FETCH tnames INTO tablename; IF NOT tnames_done THEN SELECT tablename; BEGIN DECLARE resadi INT; DECLARE adi_done INT DEFAULT 0; SET @tn = tablename; DECLARE adi CURSOR FOR select count(*) from a join individual i on a.ident=i.ident where fid=agpfid; OPEN adi; FETCH adi INTO resadi; CLOSE adi; IF (resadi0) THEN select resadi as adi_wps; END IF; END; END IF; UNTIL tnames_done END REPEAT; CLOSE tnames; END; END // A couple of thoughts, and please bear in mind that you probably know more about stored than I do. 1. Would this be better dealt with by BASH? You could look at the results and act accordingly much more easily with BASH/Perl/whatever. 2. Why not fetch the prepared statement into a udv? would become SET @stmt_text=CONCAT(select count(*) INTO resadi from ,tablename, a join individual i on a.ident=i.ident where fid=,agpfid); ... you could then test resadi as shown. I hope I am not adding to the confusion, Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to build sub-sequence ... AUTO_INCREMENT on a sub-key starting with a specific offset?
I would do a muli key PK with a after insert trigger to that would change widget_number 1 to 1000. Just my HO... I would use this combo as the primary key, but I hate doing joins with multiple primary keys, so I'll also keep the widget_id for the purpose of making joins easier. Why? Both of these fields are ints, so the key length would rather small. I don't think the 'MAX' is optimized, though and maybe there is a better, more robust way to do this which is already built into MySQL that I don't know about. MAX should be fast, assuming the field is indexed. -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have RETURNING in the language?
On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote: There's an awesome feature that was added to PostgreSQL a while back called RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement behave like a SELECT statement. You can do something like this: INSERT INTO mytable (id, value) VALUES (1, 'something') RETURNING any_column_you_want; This would be equivalent to running something like this in MySQL: INSERT INTO mytable (id, value) VALUES (1, 'something'); SELECT any_column_you_want FROM mytable WHERE id = 1; Here is another example with an UPDATE query: UPDATE mytable SET value = 'something' WHERE id = 1 RETURNING id, other_number; The nice thing about this is that every insert or update can return any column you want (even multiple columns) without having to do the INSERT/UPDATE then turn around and perform another SELECT query. I want to use this because when I insert a value into a table, I don't always want to get the primary key returned to me. Sometimes I want another column which may contain a candidate key and I'd like to avoid the round-trip and additional logic incurred with running multiple queries. Does RETURNING exist in any current release of MySQL or is it on the TODO list even? If it's not, how can I go about asking to have it put on there? -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] You can do your insert through a stored procedure and then at the end do a select of those values. http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14 22.4.14: Can MySQL 5.0 stored routines return result sets? Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have RETURNING in the language?
On Wed, Oct 15, 2008 at 2:00 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote: Rob Wultsch wrote: On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote: There's an awesome feature that was added to PostgreSQL a while back called RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement behave like a SELECT statement. You can do something like this: INSERT INTO mytable (id, value) VALUES (1, 'something') RETURNING any_column_you_want; This would be equivalent to running something like this in MySQL: INSERT INTO mytable (id, value) VALUES (1, 'something'); SELECT any_column_you_want FROM mytable WHERE id = 1; Here is another example with an UPDATE query: UPDATE mytable SET value = 'something' WHERE id = 1 RETURNING id, other_number; The nice thing about this is that every insert or update can return any column you want (even multiple columns) without having to do the INSERT/UPDATE then turn around and perform another SELECT query. I want to use this because when I insert a value into a table, I don't always want to get the primary key returned to me. Sometimes I want another column which may contain a candidate key and I'd like to avoid the round-trip and additional logic incurred with running multiple queries. Does RETURNING exist in any current release of MySQL or is it on the TODO list even? If it's not, how can I go about asking to have it put on there? -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] You can do your insert through a stored procedure and then at the end do a select of those values. http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14 22.4.14: Can MySQL 5.0 stored routines return result sets? Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension. This is an interesting strategy in that all your queries would turn into CALL statements. There are several reasons why I would NOT want to turn all my queries into stored procedures, though. The main problem I have is that it is difficult to deploy stored procedures from DEV to PROD environments and have those deployments synchronized with the deployment of the web code. SQL which is kept with the application is easily deployed when the application is deployed and the same goes for version control of the SQL if you are using something like Subversion to maintain change history. So, I suppose you CAN perform an UPDATE and run a SELECT from a stored procedure, but this strategy is not much better than doing both calls from the client and still does not act like the RETURNING feature I was hoping for. -- Dante For the record I am not a big fan of stored procedures, particularly because the svn/cvn issues. Also debugging is a bear. However, what I am suggesting is not to run an update and then a SELECT ... FROM ...(unless you are using triggers, or a virtual columns when that is merged, etc) because you will already have all the values passed in as variables you should be able to do something like: SELECT var1 AS 'colname', var2 AS 'col2'; (Note the lack of a FROM clause.) Alternatively, you could set a user defined variable in insert triggers and then reuse the variables later on. Other than the above strategies I think you are probably out of luck. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have RETURNING in the language?
On Wed, Oct 15, 2008 at 2:09 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Wed, Oct 15, 2008 at 2:00 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote: Rob Wultsch wrote: On Wed, Oct 15, 2008 at 12:25 PM, D. Dante Lorenso [EMAIL PROTECTED] wrote: There's an awesome feature that was added to PostgreSQL a while back called RETURNING that allows you to make an INSERT, UPDATE, and DELETE statement behave like a SELECT statement. You can do something like this: INSERT INTO mytable (id, value) VALUES (1, 'something') RETURNING any_column_you_want; This would be equivalent to running something like this in MySQL: INSERT INTO mytable (id, value) VALUES (1, 'something'); SELECT any_column_you_want FROM mytable WHERE id = 1; Here is another example with an UPDATE query: UPDATE mytable SET value = 'something' WHERE id = 1 RETURNING id, other_number; The nice thing about this is that every insert or update can return any column you want (even multiple columns) without having to do the INSERT/UPDATE then turn around and perform another SELECT query. I want to use this because when I insert a value into a table, I don't always want to get the primary key returned to me. Sometimes I want another column which may contain a candidate key and I'd like to avoid the round-trip and additional logic incurred with running multiple queries. Does RETURNING exist in any current release of MySQL or is it on the TODO list even? If it's not, how can I go about asking to have it put on there? -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] You can do your insert through a stored procedure and then at the end do a select of those values. http://dev.mysql.com/doc/refman/5.0/en/faqs-stored-procs.html#qandaitem-22-4-14 22.4.14: Can MySQL 5.0 stored routines return result sets? Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that — for instance — in PHP, you need to use the mysqli extension rather than the old mysql extension. This is an interesting strategy in that all your queries would turn into CALL statements. There are several reasons why I would NOT want to turn all my queries into stored procedures, though. The main problem I have is that it is difficult to deploy stored procedures from DEV to PROD environments and have those deployments synchronized with the deployment of the web code. SQL which is kept with the application is easily deployed when the application is deployed and the same goes for version control of the SQL if you are using something like Subversion to maintain change history. So, I suppose you CAN perform an UPDATE and run a SELECT from a stored procedure, but this strategy is not much better than doing both calls from the client and still does not act like the RETURNING feature I was hoping for. -- Dante For the record I am not a big fan of stored procedures, particularly because the svn/cvn issues. Also debugging is a bear. However, what I am suggesting is not to run an update and then a SELECT ... FROM ...(unless you are using triggers, or a virtual columns when that is merged, etc) because you will already have all the values passed in as variables you should be able to do something like: SELECT var1 AS 'colname', var2 AS 'col2'; (Note the lack of a FROM clause.) Alternatively, you could set a user defined variable in insert triggers and then reuse the variables later on. Other than the above strategies I think you are probably out of luck. Example: mysql use test; Database changed mysql create table t1(c int); Query OK, 0 rows affected (0.08 sec) mysql create table t2(c int); Query OK, 0 rows affected (0.08 sec) mysql CREATE TRIGGER triggy AFTER INSERT - ON t1 FOR EACH ROW - set @c=NEW.c; Query OK, 0 rows affected (0.05 sec) mysql INSERT INTO t1 values(3); Query OK, 1 row affected (0.06 sec) mysql insert into t2 values(@c); Query OK, 1 row affected (0.05 sec) mysql select * from t2; +--+ | c| +--+ |3 | +--+ 1 row in set (0.00 sec) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Permissions
On Tue, Oct 14, 2008 at 1:49 PM, Grant Peel [EMAIL PROTECTED] wrote: Hi all, I run a shared Apache, Perl, PHP, Mysql, on FreeBSD environment. Here is a question: IF I have a user, that has no permissions, but with a decent password, (in the mysql 'Users' table), AND that user only has access to his/her database through the local host (i.e. perl or PHP scripts), IS it safe to grant 'All' privs to that user in the database grants table? -Grant GRANT ALL PRIVILEGES is a bad habit. http://ronaldbradford.com/blog/why-you-do-not-use-grant-all-on-2008-09-23/ -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication question -different db name on slave server
On Mon, Oct 13, 2008 at 11:43 AM, AM Corona [EMAIL PROTECTED] wrote: In mysql 4, can one replicate a database to another server but have the DB name on the slave server be different? Master : dbname1 Slave: dbname1 AND Slave : dbname2 (but contains data from db2name1) A project manager is asking for this.. don't blame the guy asking :-) Regards, Martin Corona If you don't need things to synched you could *not* run a sql thread on the slave, and then use sed/awk/whatever to filter the relay log files and then run the sql thread. In fact I bet there is some way for awk to sit between the relay logs and sql threads in real time... -- Rob Wultsch
Re: Copying tables between databases
Assuming that both databases are on the same mysqld instance setting triggers to keep the table up to date should do what you want... On Thu, Oct 9, 2008 at 10:34 AM, Eric Anderson [EMAIL PROTECTED] wrote: I've got two databases Foo and Bar. Both databases have a table called 'zoop_t'. Foo's is the master copy (i.e. the one that gets updated) and Bar needs to be updated if/when changes are made. Currently, I'm mysqldump'ing that table from Foo at midnight via cron and feeding it back into Bar via cron. Is there a way to set up real-time replication of that table? Assuming that both databases are on the same mysqld instance setting triggers to keep the table up to date should do what you want... -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Global search and replace utility
On Thu, Oct 9, 2008 at 4:21 PM, mike cantor [EMAIL PROTECTED] wrote: Does anyone know of a utility that can go through a set of tables (or every table) in a MySql database and preform a global search and replace (i.e. replace every instance string1 in a text field with string2). Or is there a super clever query that accomplishes this? I have seen a few Windows-based utilities that proport to do this but I am looking for something I can run on Linux. Thanks for any help you can offer! -Mike Use the information schema to make a list of all tables and columns, and then run updates on all of them. OR Dump to a text file and run a find and replace in your editor of choice. -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Views and replication
On Thu, Oct 2, 2008 at 1:29 PM, Olaf Stein [EMAIL PROTECTED] wrote: Hi all, In my master/salve setup the only database I do not replicate is mysql. The slave has only the users absolutely necessary to select and administer, that is why a lot of the users I have on the master are not there. When I create a view on the master the definer is set to the user I am logged in as. As this user does not exist on the slave the view is created there but I cannot select from it. I tried setting the definer to current_user in the hope it would mean user logged in in current session, not user that was logged in when creating the view. How can I get around this, I do not want to re-create all user that potentially could create views on the slave. Thanks Olaf Take a look at SQL SECURITY INVOKER Example: Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\robc:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql grant select on test.* to bob identified by 'stuff'; Query OK, 0 rows affected (0.00 sec) mysql grant select,create view on test.* to joe identified by 'stuff'; Query OK, 0 rows affected (0.00 sec) mysql use test; Database changed mysql create table t(c int); Query OK, 0 rows affected (0.31 sec) mysql insert into t values(1),(3); Query OK, 2 rows affected (0.16 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql exit Bye C:\Documents and Settings\robc:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe -u joe -p Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use test; Database changed mysql show tables; ++ | Tables_in_test | ++ | t | ++ 1 row in set (0.00 sec) mysql create SQL SECURITY INVOKER VIEW viewy AS select c+1 from t; Query OK, 0 rows affected (0.00 sec) mysql exit Bye C:\Documents and Settings\robC:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql drop user joe; Query OK, 0 rows affected (0.00 sec) mysql exit Bye C:\Documents and Settings\robc:\Program Files\MySQL\MySQL Server 5.0\bin\mysql .exe -u bob -p Enter password: * Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.0.51a-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use test; Database changed mysql show tables; ++ | Tables_in_test | ++ | t | | viewy | ++ 2 rows in set (0.00 sec) mysql select * from viewy; +--+ | c+1 | +--+ |2 | |4 | +--+ 2 rows in set (0.00 sec) -- Rob Wultsch [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
