Re: temp table and view/function/procedure dilemma
Hi, On 04/07/2011 08:06 AM, petya wrote: Hi, You can always create any table from procedures. However, it seems to me that flexviews can solve all of your problems, take a look at it. It will provide you incrementally refreshable materialized views. How do you create a table from a procedure output? I found it stated everywhere that it's not supported. About flexviews: Looks like a solution indeed. I will look into it! Thanks Bgs Peter Boros On 04/05/2011 08:15 PM, Bgs wrote: Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: temp table and view/function/procedure dilemma
On 04/06/2011 09:13 PM, Sándor Halász wrote: I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Why not fake the temporariness, by DROPping the table as needful? I 'DROP'ed that approach for a couple of reasons: - While trying to minimize the load on the big table there is a real chance of concurrent jobs. Overall I find two temp table generation (loads) better than dropping each other's tables or locking other jobs. You also have to keep track of the fake-temp table's age. - A regular update of the fake-temp table would solve the above, but would produce too much load at the expected freshness. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
temp table and view/function/procedure dilemma
Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
problems with replication when db is changed
Greetings, We have regular problems with mysql replication when there is a db change. This is mostly ALTER TABLE. The sync breaks and we either have to do the changes manually or either shut down the whole system for a new sync from zero. Is there a way to sync alter table commands on the fly? Shouldn't they be executed just as the insert/update commands? Regards Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mirror db to new name
Hi all, Is it possible to mirror a db to a different name? I have multiple dbs with a proprietary software which all use the same db name. I want to mirror all of them to a single db for backup purposes, but I don't want to run multiple instances of slave mysqls. Example: db1.domain.com -> db=mydb db2.domain.com -> db=mydb mirror.domain.com -> mydb1 and mydb2 Thanks Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA FROM MASTER stops unfinished with Query OK
It seems nobody has a clue here :( I've given up with MySQL replication... Hope it will work in 5.1 ... BTW: Any official info or estimate about the production release? Bgs wrote: Nope... pure myisam... sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor <[EMAIL PROTECTED]> wrote: Bgs wrote: > > No ideas? > > I tried playing around with read/write timeouts (even thought the > replication is fast), all size limits are greater than the whole > replicated db. The last table with accesses MYD and zero size is a small > one (a couple of dozens kBs). > > Bgs wrote: > >> >> Greetings, >> >> I played around with load data from master (ldfm) and it worked fine >> in test environment. Now I want to replicate our actual db to a slave. >> When I issue the ldfm command, it starts the replication. I get Query >> OK, but only about 5% of the db is replicated. Apparently all tables >> that are on the slave in the end are exact copies of the master >> tables, but most MYD files are zero sized >> >> >> Any ideas? >> >> Thanks in advance >> Bgs >> >> > Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA FROM MASTER stops unfinished with Query OK
Nope... pure myisam... sheeri kritzer wrote: yeah, I'd be willing to guess that you're mostly innodb. LOAD DATA FROM MASTER only works for MYISAM. http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html -Sheeri On 5/24/06, Dan Trainor <[EMAIL PROTECTED]> wrote: Bgs wrote: > > No ideas? > > I tried playing around with read/write timeouts (even thought the > replication is fast), all size limits are greater than the whole > replicated db. The last table with accesses MYD and zero size is a small > one (a couple of dozens kBs). > > Bgs wrote: > >> >> Greetings, >> >> I played around with load data from master (ldfm) and it worked fine >> in test environment. Now I want to replicate our actual db to a slave. >> When I issue the ldfm command, it starts the replication. I get Query >> OK, but only about 5% of the db is replicated. Apparently all tables >> that are on the slave in the end are exact copies of the master >> tables, but most MYD files are zero sized >> >> >> Any ideas? >> >> Thanks in advance >> Bgs >> >> > Hi - Which storage engine are you using for the tables or database which you're trying to replicate? Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA FROM MASTER stops unfinished with Query OK
No ideas? I tried playing around with read/write timeouts (even thought the replication is fast), all size limits are greater than the whole replicated db. The last table with accesses MYD and zero size is a small one (a couple of dozens kBs). Bgs wrote: Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA FROM MASTER stops unfinished with Query OK
Greetings, I played around with load data from master (ldfm) and it worked fine in test environment. Now I want to replicate our actual db to a slave. When I issue the ldfm command, it starts the replication. I get Query OK, but only about 5% of the db is replicated. Apparently all tables that are on the slave in the end are exact copies of the master tables, but most MYD files are zero sized Any ideas? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort aborted
Hi, I've tried that there is no problem with that. Mysql creates bigger tmp tables from time to time. We had this problem with a specific query onyl I sad had, as last night I tried again and it worked. Beats me... Server was not rebooted, mysql was not restarted and the available space got a bit even smaller... There must have been something in the query, but I cannot reproduce the error right now. I will look out for new occurences in the near future... Bye Bgs Gleb Paharenko wrote: Hello. What operating system do you use? `su` to mysql user (I think the less ugly method should exists) and check if you able to create big files. Bgs <[EMAIL PROTECTED]> wrote: Hi, There are no quotas at all. The server has been up since march. This is the first time we had this problem, but now it is consistent... I run the query and after a few seconds, with about 8-10kB written on the disk, I get the abort. Bye Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort aborted
Hi, There are no quotas at all. The server has been up since march. This is the first time we had this problem, but now it is consistent... I run the query and after a few seconds, with about 8-10kB written on the disk, I get the abort. Bye Bgs Gleb Paharenko wrote: Hello. Do you have filesystem quotas enabled on your system? Bgs <[EMAIL PROTECTED]> wrote: Hi all, We have a problem with our mysql 4.1.11 system. It has been working well since the installation, we only have one problem. In the case of certain selects, it always stop with "Sort aborted" error. To the client it returns errors like "ERROR 3 (HY000): Error writing file '/tmp/MYFh2Ob0' (Errcode: 28)". The funny thing is that /tmp has 1777 perms as it should and there is plenty of place left (the select breaks when there are only a few kilobytes of temporary files there). Browsing for the solution I only found a bug dated back to 2001 and another mail with similar problems with 4.1.7 that had no answer... Does anybody have a clue, what might be the problem ? Thanks Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO issue
Here is the official link: http://www.sco.com/products/openserver6/mysql.html SCO states that they will distribute MySQL with a commercial license. According to this they do not plan to stick with GPL... Bye Bgs [EMAIL PROTECTED] wrote: __ Mirza <[EMAIL PROTECTED]> wrote on 05/09/2005 14:31:12: I would like someone from MySQL AB to clarify issue with SCO asap. I wouldn't like to use technologies for my business that later could be used against me (in legal sense). Does MySQL AB understand that it helps funding their legal cases against us (GPL users) ? If someone feels OK with SCO partnership, good luck, but (being long time MySQL user and alpha bug reporter) I would switch to Embedded PostgreSQL myself and encourage other people to do the same. I use _tons_ of GPL software so should I help funding of my own annoyance (albeit poorly supported with facts) ? All the press releases I have seen appear to originate from SCO. There is not, in any of them, any suggestion that money has passed or will pass from MySQL to SCO. SCO has for a long time been one of the many varieties of Unix that MySQL supports. MySQL cannot stop SCO from distributing their product (hypocritically) under the GPL. On the other hand, if they allow SCO to include "offical" releases of MySQL, they may get some support customers - which is where they earn their real income. The press blurbs are essentially saying that SCO resellers will market MySQL Network - to the benefit of MySQL. If there is any money flow, I would have thought it would be more likely to be the other way: SCO paying MySQL to ensure that one of the premier Unix applications remains supported on their platform. Of course, MySQL may say otherwise, but I think this is a piece of SCO hype intended to imply MySQL support of SCO when all they are really doing is supporting their own product on whatever platform their customers may choose - even when that platform is marketed by a company who many of us find totally repulsive. If you let yourself be hyped into dropping MySQL, you will be harming a company that is, in my opinion, a model of how to provide full commercial quality software (or better) with an Open Source licence, while not (I think) harming SCO in any way. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sort aborted
Hi all, We have a problem with our mysql 4.1.11 system. It has been working well since the installation, we only have one problem. In the case of certain selects, it always stop with "Sort aborted" error. To the client it returns errors like "ERROR 3 (HY000): Error writing file '/tmp/MYFh2Ob0' (Errcode: 28)". The funny thing is that /tmp has 1777 perms as it should and there is plenty of place left (the select breaks when there are only a few kilobytes of temporary files there). Browsing for the solution I only found a bug dated back to 2001 and another mail with similar problems with 4.1.7 that had no answer... Does anybody have a clue, what might be the problem ? Thanks Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair table priv
Hi, I think you need to have root privileges (ALL) on the table to run REPAIR TABLE since this is a disaster recovery sql command. It works only for MyISAM tables in MySQL. I don't think it is supported for all tables. I need it because I got a server with big load that sometimes has db problems. The server is a big mess but I cannot replace it for now. So I set up a script that removes unneded stuff every few minutes but I also want to do a repair table from that script. The script uses a restricted user (resztricted to only delete from a ceratin db) and I wanted to avoid giving to many rights to that user. I started to add rights but it didn't work. The user almost got all rights by now but it still doesn't work. The server is going to be shut down soon at last, but I will experiment with this and send the results to the list later for others to know. Thanks Zoltan Logically a repair table command would update, insert, delete and alter the tables: so a superset of these privileges should be available. -Goutham S Mohan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair table priv
up Bgs wrote: Does this silence mean that nobody knows?!? :) I've been trying to find what privilege is needed to 'REPAIR TABLE'. I couldn't find any usefull hint on the net or in the archives. Could anyone help me out? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair table priv
Does this silence mean that nobody knows?!? :) I've been trying to find what privilege is needed to 'REPAIR TABLE'. I couldn't find any usefull hint on the net or in the archives. Could anyone help me out? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multi period sum() selects
Thanks for the tips! I'll be playing with them tomorrow. I think with
this method, the processing time will drop from Nx to between 1x-2x pass
time. ('N' in my case is usually between 5 and 100).
Regards
Bgs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multi period sum() selects
Greetings, While waiting for the repair table reactions I thought to ask a question that hogged in my head for a while. I have a db which has among others (including text fields) a timestamp field and a counter field. I want to make statistics from them, doing sum()s with conditions 'timestamp>P1 and timestampP2 and timestamp A trivial way would be to make a cycle for Pn but that takes a lot of time and as one search takes considerable time (10-20sec for 100-200MB db), this multiplies to a huge overall time. Any ideas how to solve this with possibly one db pass ? Thanks Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
repair table priv
Greetings, I've been trying to find what privilege is needed to 'REPAIR TABLE'. I couldn't find any usefull hint on the net or in the archives. Could anyone help me out? Thanks Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
