Re: Replication question
On 24/07/2013 19:52, Rick James wrote: 4) 3 tables from the slaves are to be replicated back to the master NO. However, consider Percona XtraDb Cluster or MariaDB+Galera. They allow multiple writable masters. But they won't let you be so selective about tables not being replicated. Here are the gotchas for Galera usage: http://mysql.rjweb.org/doc.php/galera If you can live with them (plus replicating everything), it may be best for you. Ok thanks Rick for confirming my initial gut feelings about this...! Will have to implement a manual process to push the required data back to the master. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication question
I have been asked to set up multiple database replication which I have done before for simple cases however there are some nuances with this instance that add some complexity and I'd like to hear your collective expertise on this proposed scenario:- 1) Single master database 2) n (probably 3 to start with) number of slave databases 3) All but 5 tables (123 tables in total) are to be replicated from the master to all the slaves 4) 3 tables from the slaves are to be replicated back to the master It is mainly item 4) that concerns me - the primary ID's are almost certain to collide unless I seed the auto increment ID to partition the IDs into separate ranges or does MySQL handle this issue? There are some foreign keys on one of the 3 slave to master tables but they are pointing at some extremely static tables that are very unlikely to change. Is the above a feasible implementation...? Thanks in advance for any advice/pointers! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: (real) silly question about variables...
On 04/10/2012 15:52, MAS! wrote: Hi I know there'd be a reason, but I can't understand that.. mysql select @valore:=rand(), @valore, @valore:=ciao, @valore; +---+---+-+-+ | @valore:=rand() | @valore | @valore:=ciao | @valore | +---+---+-+-+ | 0.483624490428366 | 0.483624490428366 | ciao| 0 | +---+---+-+-+ 1 row in set (0.00 sec) mysql select @valore:=rand(), @valore, @valore:=ciao, @valore; +---+---+-+-+ | @valore:=rand() | @valore | @valore:=ciao | @valore | +---+---+-+-+ | 0.747058809499311 | 0.747058809499311 | ciao| ciao| +---+---+-+-+ 1 row in set (0.00 sec) why in the first execution the latest value is 0 and not 'ciao'? and why in the first 2 columns the variables seems works as expected!? what version of MySQL are you running? I get this:- Server version: 5.5.17-log MySQL Community Server (GPL) mysql select @valore:=rand(), @valore, @valore:=ciao, @valore; +++-+-+ | @valore:=rand()| @valore| @valore:=ciao | @valore | +++-+-+ | 0.8187706152151997 | 0.8187706152151997 | ciao| ciao| +++-+-+ Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Having trouble with SQL query
Hi Nitin Thanks - I tried that and got 0 rows... I have spent more time on describing my problem -- see below hopefully this will make the issue more clear... Rich I have a MySQL database with a menu table and a product table. - The products are linked to the menus in a one-to-many relationship i.e. each product can be linked to more than one menu - The menus are nested in a parent child relationship - Some menus may contain no products The desire is that when a user clicks on a menu entry then all products linked to that menu - there may be none - will get displayed as well as all products linked to any child menus of the menu clicked on ... So say we have a menu like this:- Motor cycles - Sports bikes - Italian - Ducati Motor cycles - Sports bikes - Italian - Moto Guzzi Motor cycles - Sports bikes - British - Triumph Motor cycles - Tourers - British - Triumph Motor cycles - Tourers - American - Harley-Davidson . etc etc Clicking on 'Sports bikes' will show all products linked to 'Sports bikes' itself as well as all products linked to ALL menus below 'Sports bikes', clicking on 'Harley-Davidson' will just show products for that entry only. Below are 'describe table' for the 2 main tables in question NB there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant here:- CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 For the sake of this question I will simplify it and say there is only 2 levels of nesting i.e. root level and 1 level below that... this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway when I run the above query it returns far too many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Having trouble with SQL query
I have a MySQL database with a menu table and a product table linked to the menus *(each product can be linked to more than menu row)* and the menus are nested. The query is that when a user clicks on a menu entry then all products linked to that entry *(there may be none)* will get displayed as well as all products linked to child menus... below are describe tables for the 2 main tables in question (there is a 3rd table that holds product descriptions which I won't show as I don't think it is relevant) CREATE TABLE `menu` ( `menuid` int(11) unsigned NOT NULL AUTO_INCREMENT, `parent_menuid` int(11) unsigned NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`menuid`) ) ENGINE=MyISAM AUTO_INCREMENT=225 DEFAULT CHARSET=utf8 CREATE TABLE `menu_product` ( `menuid` int(11) unsigned NOT NULL, `productid` int(11) unsigned NOT NULL, PRIMARY KEY (`menuid`,`productid`), KEY `prodidx` (`productid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Anyway for the sake of this question lets say there is only 2 levels of nesting so a parent menu can only have children so no grandkids+ this is the query I came up with:- SELECT DISTINCT p.productid, pd.name FROM menu_product as p INNER JOIN menu as m ON (m.menuid = p.menuid) INNER JOIN product_description as pd ON (pd.productid = p.productid) LEFT JOIN menu as m2 ON (m2.parent_menuid = m.menuid) # User selected menu may itself be a child menu... WHERE (m.name = 'name obtained from user's click' OR p.productid IN (SELECT p2.productid from menu_product AS p2 WHERE p2.menuid = m2.menuid) Anyway the above query returns many many entries from menus that are totally unrelated... I have been staring too hard at this for too long - I am sure it will be a forehead slapper! I hope I have explained this sufficiently and I TYIA for any guidance Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
[GIG] $500 For Site Speed Improvement
Hey folks! This gig just popped up on our system, thought it could be some easy money for anybody out there who knows Ruby/Rails and how to optimize queries! http://gun.io/contracts/67/improve-site-speed-for-startup Thanks! -- Rich Jones Director, Gun.io
MySQL v5.5.12 on Mac OSX - Server won't start...
I have installed MySQL v5.5.12 Community Server on Mac OS X v10.5.8 When I try and start the server I get these messages before it aborts... 110514 17:16:14 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 110514 17:16:16 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead. 110514 17:16:17 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 110514 17:16:17 [Note] Plugin 'FEDERATED' is disabled. /usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist 110514 17:16:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 110514 17:16:17 InnoDB: The InnoDB memory heap is disabled 110514 17:16:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins 110514 17:16:17 InnoDB: Compressed tables use zlib 1.2.3 110514 17:16:17 InnoDB: Initializing buffer pool, size = 128.0M 110514 17:16:17 InnoDB: Completed initialization of buffer pool 110514 17:16:17 InnoDB: highest supported file format is Barracuda. 110514 17:16:19 InnoDB: Waiting for the background threads to start 110514 17:16:20 InnoDB: 1.1.6 started; log sequence number 1595675 110514 17:16:20 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-character-set=utf8' 110514 17:16:20 [ERROR] Aborting 110514 17:16:20 InnoDB: Starting shutdown... 110514 17:16:21 InnoDB: Shutdown completed; log sequence number 1595675 110514 17:16:21 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 110514 17:16:21 mysqld_safe mysqld from pid file /usr/local/mysql/data/x.local.pid ended Anybody point me towards where I can get this issue sorted... Googling didnt turn up much ... TIA Rich
RE: Any easier way to compare mysql schema of 50 databases?!
I just used mysqldiff for a project, and was successful. -Original Message- From: Uma Bhat [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 7:51 PM To: Andy Shellam; mysql@lists.mysql.com Subject: Re: Any easier way to compare mysql schema of 50 databases?! Thank you all, guys!! i shall try these and respond on which worked best for me. Regards, Uma On 10/6/08, Andy Shellam [EMAIL PROTECTED] wrote: Hi, I was also going to go down this route some time back, but then when I looked at it, it's pretty simple how it works and you can do the same thing yourself for free. Take a dump of both servers (mysqldump or via MySQL Administrator) of the databases in question, then use WinMerge (for free) to compare the dump files and (optionally) create a 3rd file which is a result of merging the 2 by choosing the changes you want to keep. I've used this method plenty of times to synchronise changes between servers, some that concern 000s of rows of data. Granted you cannot do this with PostgreSQL as those dumps tend to be in binary format, but it works well for MySQL backups. WinMerge: http://www.winmerge.org/ Andy D. Dante Lorenso wrote: Uma Bhat wrote: We are in progress of *optimizing* and designing the existing mysql database enviromnent on *linux*. And need help in comaparing schema of 50 databases from the same mysql instance. If you can afford to spend a few dollars to get the right tool, you want to get DB Comparer for MySQL from the folks at EMS: http://www.sqlmanager.net/en/products/mysql/dbcomparer This tool will compare the schemas of 2 MySQL Databases and allow you to selectively choose which changes to make in order to synch to the master or the target DB. I've been using the PostgreSQL version of this tool for many years and just recently started using their MySQL one. -- Dante -- D. Dante Lorenso [EMAIL PROTECTED] -- 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]
encode/decode question
Hi, One of our previous developers decided to obfuscate some columns via the mysql encode and decode functions. This was done on a 4.x server. Recently, we converted to a 5.x server, and some of the fields in the rows will not decode correctly, and I cannot figure out why. My first thought was that perhaps the implementation of encode/decode changed, but googling so far has been to no avail. My second thought was that perhaps there was a problem when we dumped the data from the 4.x server and imported it to the 5.x server. The old 4.x server is gone, so I cannot verify whether that is the case. I did dump/restore the data in question from the 5.x back to a 4.x server, but it decodes in error as well. This obfuscation was a retrofit to an existing table, mostly of varchar fields. Another thought I had was that perhaps it's not a good idea to store binary data in a varchar field. So, a few questions: Anyone know if the implementation of endcode/decode changed along the line from v4 to v5? What algorithm is used for encode/decode? Are there limits on what can be stored in a varchar field? Is it ok to store the results of encode/decode in a varchar? We don't need to encrypt the data, simply obfuscate it to prevent accidental, unintentional use. Any ideas appreciated. Thank you. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Last X rows
Hi folks. Just wanting to know the best way to grab the last 10 rows from a table. Looking twice to the db to see how many records there are will be outdated by the time the SELECT is done, so it's moot. This is a fast moving db with records coming and going. Instead of having an offset in LIMIT, is there a way to ask for the last 10 rows directly? Cheers Rich in Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Last X rows
On Jun 30, 2007, at 9:06 AM, Borokov Smith wrote: Hey, Why is ORDER BY in combination with LIMIT not a valid solution to your problem ? Greetz, Hi there. Because if I choose ASC it chooses the first X records, and if I choose DESC it chooses the last X records, but in reverse order. If I use the latter, park it into a compound data structure, there's overhead, so I thought I'd chase up the db to see if there was anything I could solve it with. Rich in Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Last X rows
Ah that makes sense. It's a double shot, first grabbing the necessary records, then selecting all in that temp value (hitlist) in reverse order. Well done. Cheers On Jun 30, 2007, at 11:26 AM, Octavian Rasnita wrote: Hi, Try something like this: select * from (select * from table_name where ... order by last_update desc limit 10) as tbl order by tbl.last_update; Rich in Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
peformance help: preventing 'using temporary; using filesort'
Hello all. I'm looking for help with the query below. Is there anyway to prevent the temporary and filesort? I've tried about as many combinations as I could think of, but can't seem to prevent it. I'm sure that's the reason, when run on a table of around 750k records, it takes in excess of 20 seconds. There are indexes on sourceID in both tables as well as the date field in the first table. Thanks for any ideas. SELECT t1.sourceID as sourceID, count(t1.sourceID) as clicks, sum(t1.converted) as conversions, (sum(t1.converted)/count(t1.sourceID)) * 100 as conv_rate, count(t1.sourceID) * t2.cost as cost, sum(t1.revenue) as revenue, (sum(t1.revenue)) - (count(t1.sourceID) * ifnull(t2.cost,0)) as margin, ( ((sum(t1.revenue)) - (count(t1.sourceID) * t2.cost)) / sum(t1.revenue) ) * 100 as gm, (count(t1.sourceID) * t2.cost) / sum(t1.converted) as cpl, (sum(t1.revenue)) / sum(t1.converted) as rpl, t2.cost as cpc FROM source_site_quality as t1 LEFT JOIN rpt_cpc as t2 ON (t1.sourceID = t2.sourceID) WHERE t1.date = '2007-06-26' AND t1.date = '2007-06-28' GROUP BY sourceID ORDER BY clicks desc, conversions desc; When using EXPLAIN: ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | t1| range | idx_date | idx_date | 3 | NULL | 4612 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t2| ALL | NULL | NULL | NULL | NULL | 1351 | | ++-+---+---+---+--+-+--+--+--+ -- rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select Primary Key
Hi folks. How can I select primary key from a table? I can't find any syntax out of show fields from tablename, and the 'key' field = PRI. Just seems to be a bit much. Any takers? Cheers Rich in Toronto (cold) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update field concactenation
Hi folks. Boy do I have an interesting one for you today. This list is great. Can I update a record using a typical update query, and have a field be updated to: previous value of the field + (something I want added to the end) so field 'flavours' starts out as: vanilla chocolate ...then I want to add strawberry to it in a single update...to get... vanilla choclolate strawberry Or do I have to grab the value first in a subquery or alternate query? I just have multiple records to work on so I'd like to do it in one db call. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Two Tables Comparator
Hi folks. Any suggestions on the following? If a student signs up for classes, I don't want them viewing those classes so they can choose them again. So I want to take out their 'signups' from the master class list. select classnumber, classnumbersignup from classes, signups where classnumber !IN (select classnumbersignup from signups where userid = [$myID]) and mytype = 'yellow'; I don't want to have to rely on middleware for this. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql crashes on drop database?
- Standard (GPL) It seems that the innodb table space may be corrupted in some way. Since this is a backup server, I can certainly destroy the data directories and re-import all of the data, but it seems counter-intuitive to wipe over everything and reinstall. Isn't there some sort of repair facility to fix up the innodb table space? Or at least validate that it's ok? Thank you! Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MS Access gives error no. -7776.
-Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 5:45 PM To: C K; mysql@lists.mysql.com Subject: Re: MS Access gives error no. -7776. C K wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: Contrary to the advice given on working with MS Access, I've found that I can *only* get things to work with Access 2003 if I remove the timestamp field. Also, make sure your primary key column isn't larger than an int ( ie don't use int unsigned, and don't use bigint ). I recently converted an application with 1M+ records to use mysql as the backend. I had similar errors until I: Make sure every table haa a unique key field Convert all timestamp fields to datetime (via alter table) Add one (and only one) last_changed timestamp, and fill it with a valid stamp Null out datetime fields that have '-00-00 00:00:00' as their value. Refresh the ODBC links. Access likes to use the primary key field + the last_changed timestamp in it's update queries so that it can easily manage optimistic locking. It issues update queries like Update some_table ... where primary_key = 27 and last_changed = '2006-12-01 23:52:55'. If the query doesn't affect any rows, then Access knows that someone else changed the record since it was initially retrieved, and can then prompt the user accordingly. Access also throws errors when it finds that timestamp or datetime fields have '-00-00 00:00:00'. Immediately after I loaded the mysql tables with the access data, I ran an 'update $full_table set $full_column = NULL where $full_column = '-00-00 00:00:00' on every datetime column in the database. In the case of the special last_changed column, I set all records to '1980-01-01 00:00:00' immediately following the conversion. Since this column was added by the conversion, it is never referenced in any forms, which seems to help things. The bad news is that all of the automatic date fields that were in the original .mdb file now have to be filled in with 'now()' in all sorts of places like forms and insert queries. I also had some trouble where a data entry subform would not allow additions. The 'add record' part of the record navigator would be grayed out. Usually, this was because I had neglected to have a primary key + last_changed timestamp on the table in question. In some cases, it was because a data source for a subform was a query. What I did to get those to work was to change the datasource to the table, and supply a proper filter/link. That aside, I feel much better having the data backed by mysql. The application has now been running successfully for about three weeks and users do not notice any difference in performance. Our regular backup capability now encompasses backup and restore for the data, which is great for us. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unknown tables
-Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Monday, June 12, 2006 1:48 PM To: MySQL List Subject: Unknown tables When I do a SHOW TABLES in MySQL, it shows me a list of tables. But when I try to do a select * from countries, I get the error ERROR 1146 (42S02): Table 'karate.countries' doesn't exist. I cannot drop the table. I cannot drop the entire schema, and I cannot view the tables themselves. Does anyone know how to fix this problem? I'm using MySQL 5.0 on a Windows XP Pro (my development machine) environment. Thanks, Jesse I think there are lots of possible reasons, but first thing to do is to check the mysql data directory for a karate directory. Look inside it. That's where your tables should be. I had a problem not too long ago where a table I wanted to work with was an innodb type table, but because of a config file issue, the innodb storage engine wouldn't start. So, the table was there, but because innodb wasn't active, weird things like that were happening. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finally Working...wow
Never mind. This actually posted to the wrong list. I don't know how that happened. Must have been cause I was tired. Sorry about that. On May 24, 2006, at 9:19 PM, sheeri kritzer wrote: Rich for what? is SELECT host,user,password FROM mysql.user; showing you cleartext passwords? It shouldn't. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finally Working...wow
Hi folks. Me again. I finally got this all up and running under crypt of 'cleartext'. So, even though I am going to be on the same box as the server, how do I set up an MD5 or password entry? MYSQLCrypt password() MYSQLCrypt password MYSQLCrypt MD5() MYSQLCrypt MD5 I'm not sure if the brackets are needed or not. So if I use password or MD5 encryption in the tables, the value in the field is encrypted. How does this change how I structure things? instead of 'passwordalpha' in the password field, I have to enter 'ff08d88bab6edcf9d730a96418c05358'? I am entering users via my own interface, and I can't seem to get MD5 working with either MD5 or MD5(). Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[5] Starting Up General Query Log
Mac OS X 10.4.6 (Tiger), MySQL 5.0.21. Hi folks. I'm needing to start up my general query log to see what's ticking me off. I've looked into safe_mysqld but it's confusing as ... something that's confusing. Anybody know how I can easily turn this thing on for a day, then turn it off? I'm assuming put the following command has to be entered: --log = myqueries.log Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
Why this list goes private I'll never know. I guess that's why I always get two copies. In the errors log: Found option without preceding group in config file: /etc/my.cnf at line: 1 Fatal error in defaults handling. Program aborted /var/log/ does indeed exist root runs mysqld On May 19, 2006, at 12:01 PM, sheeri kritzer wrote: Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backups with MySQL/InnoDB
-Original Message- From: Daniel da Veiga [mailto:[EMAIL PROTECTED] Sent: Monday, May 08, 2006 1:55 PM To: mysql@lists.mysql.com Subject: Re: Backups with MySQL/InnoDB On 5/8/06, David Hillman [EMAIL PROTECTED] wrote: On May 7, 2006, at 11:29 PM, Robert DiFalco wrote: Fast, incremental, compressed, and no max-size limitations. Must be transaction safe; able to run while transactions are going on without including any started after the backup began; the usual stuff. Incremental, transaction safe, compressed, fast, no-max-size. ( In order ) Those are certainly the most important features (and I'll be glad to beta-test it ;) I'll add: manage multiple servers, deal with replication (using the replicated server as a backup would be cool), manage binlogs (date and purge) and be compatible with version 4.1 and above (I don't plan on using the 5 version any time soon). -- David Hillman LiveText, Inc 1.866.LiveText x235 In addition, I'd like to see a configurable option for how often to take a full and or incremental backups, a mechanism to age the backups and drop them after a certain amount of time. For example, I want a simple way to keep four weekly near line backups each month, then age off and keep one backup for each of the previous 11 months, and then just one backup per year. This would be about 1T of data for us. It would then be really sweet to be able to say 'restore a full backup of x database as of April 2, 2005 at 8:42 am' and have it create a new instance on a user defined port, then restore the closest previous full, then apply the binlogs up to the correct point in time. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlmanager logging?
-Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Thursday, May 04, 2006 3:12 PM To: Duzenbury, Rich Cc: mysql@lists.mysql.com Subject: Re: mysqlmanager logging? Can the program write to /var/lib/mysql/mysqlmanager.log? check permissions. # su - mysql [EMAIL PROTECTED]:~ whoami mysql [EMAIL PROTECTED]:~ cd /var/lib/mysql [EMAIL PROTECTED]:~ touch foo.txt [EMAIL PROTECTED]:~ ls -al total 3 drwxr-xr-x 4 mysql mysql 120 2006-05-05 13:25 . drwxr-xr-x 56 root root 1472 2006-05-05 04:25 .. -rw-r--r-- 1 mysql mysql0 2006-05-05 13:25 foo.txt drwx--x--x 2 mysql mysql 1752 2006-05-01 09:33 mysql drwxr-xr-x 2 mysql mysql 48 2006-05-01 09:33 test I don't think there is a permission problem. Any further ideas? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlmanager logging?
-Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 3:00 PM To: Duzenbury, Rich Cc: mysql@lists.mysql.com Subject: Re: mysqlmanager logging? su - mysql touch /var/lib/mysql/mysqlmanager.log see if that helps; maybe having the file there will kick it into gear. Is mysqlmanager actually running? Is there a pid file? -Sheeri LX03:~ # ps -ef | grep mysql mysql18706 1 0 May04 ?00:00:00 /usr/sbin/mysqlmanager --user=my sql --pid-file=/tmp/manager.pid And, both my instances are up. Excerpt from /etc/my.cnf: [mysql.server] use-manager [manager] socket=/tmp/manager.sock pid-file=/tmp/manager.pid monitoring-interval=30 LX03:/tmp # ls -al /tmp | grep manager -rw-rw 1 mysql mysql 6 May 5 15:30 manager.pid srwxrwxrwx 1 mysql mysql 0 May 5 15:30 manager.sock Creating the mysqlmanager.log file does not seem to have helped. It's still empty. Any further advice? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: blank user names in user table (SOLVED)
-Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Friday, May 05, 2006 2:37 PM To: Duzenbury, Rich Cc: mysql@lists.mysql.com Subject: Re: blank user names in user table Rich, anonymous access means that ''@host has access. That is, blank at host, as opposed to [EMAIL PROTECTED] To see if anonymous access is allowed, at the command prompt type: mysql -u asdf This doesn't work. If I grant all on *.* to ''@workstation.domain.local Then, I find a record in mysql.user with host=workstation.domain.local, user is blank, password is blank. If I then connect to the server from my workstation using the MySQL query browser and leave the user id and password blank, it connects and works. After much horsing around, I can make it work as: mysql --user=anything --host=host -p (Press enter at the password prompt) Silly me, I thought I had to somehow supply a blank value for the user name. Instead, I have to simply supply an empty password. Thanks for your help! Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
blank user names in user table
Hi all, I've got a database I recently inherited where there are a number of records in the mysql.user table that have no user id. According to the mysql docs, this is supposed to allow guest access, and there is mention of how to turn it off. How does one actually connect in guest mode? My attempts with the mysql client have so far been in vain. How can I ask mysql to log connection attempts by users so I can see if any of these ID's are actually in use? Thank you. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb file per table
but you don't know __when__ innodb_file_per_table was set! So it's possible that many innodb tables actually reside in ibdata [1-4]. Check your data directory to see the individual innodb files/tables (*.ibd). This is true, and even on a fresh install that has always had innodb_file_per_table, InnoDB still needs the shared tablespace (though it probably doesn't need to be that large). Once you have an InnoDB tablespace the only way to reduce the size of the shared tablespace is to completely dump the data and recreate the tablespace. Roughly the sequence is: mysqldump to text...be very careful to keep a consistent snapshot, handle blobs, quoting names, etc Test this. Shut down mysql Rename/move old mysql data and log directories, create new, empty ones (copy over mysql database...it's not innodb and will keep the same users) Alter my.cnf, point to include new InnoDB shared table definition Start mysql, make sure InnoDB initializes correctly (check .err file) Read in dump file you took in step 1 Again, be careful with this. It essentially involves exporting and importing all your data, so make sure you have a valid export file. Good luck, Ware Thanks for the response. I routinely mysqldump --host=source | mysql --host=target for backup purposes, and then compare the row counts to see that the restore acutally completed, so I think I've got a good handle on making a clean copy of the data. In fact, I re-configured the my.cnf on the backup server, and I see that the directory size went from 58G to 45G, so there was a nice space savings. Thanks for the advice. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb file per table
Hi all, I've inherited an innodb database that is configured like: innodb_file_per_table innodb_data_file_path = ibdata1:3000M;ibdata2:3000M;ibdata3:3000M;ibdata4:3000M:autoextend Um, doesn't this allocate 12G that winds up being unused, since innodb_file_per_table is set? If so, what is the correct way to reclaim the 12G? Thanks! Regards, Rich Duzenbury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Combining ansi and theta joins bug?
Hi all, This query works fine on a 4.1 server, but not a 5.0 server: Select * from agentrelationships, agents as a2 left outer join agents on agentrelationships.agentidparent = agents.agentid where agentrelationships.agentidchild = a2.agentid On a 5.0 server, I receive 'unknown column agentrelationships.agentidparent in on clause'. If I rework the query to change the theta style joins to ansi style joins, it works fine. Select * from agentrelationships left outer join agents on agentrelationships.agentidparent = agents.agentid join agents as a2 on agentrelationships.agentidchild = a2.agentid Is there some known bug about combining theta and ansi style joins in the same query? As I say, this works on a 4.1 server, and it will be troublesome to convert all of the old queries in order to upgrade. Thank you. Regards, Rich Duzenbury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlmanager logging?
MySQL 5.0.20 I've got two instances running with mysqlmanager. I'm not getting any logging of any sort. mysqlmanager --help shows: - - log /var/lib/mysql/mysqlmanager.log pid-file /tmp/manager.pid socket/tmp/manager.sock bind-address (No default value) port 2273 password-file /etc/mysqlmanager.passwd default-mysqld-path /usr/sbin/mysqld monitoring-interval 10 run-as-serviceFALSE user (No default value) wait-timeout 28800 I've also tried to set the --log option in the [manager] section of /etc/my.cnf and restarted the server, to no avail. Do I have to run-as-service to get logging? Thanks for any help you can offer. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
I just did this last week on a 5.0.18 machine. It's supported by the
mysqlmanager out of the box. Here are a copy of my notes, and worked
well on a Suse machine. The locations of your files may not be the
same.
# stop the server, if running
/etc/init.d/mysql stop
# edit /etc/my.cnf to set up the instances and
# and also tell the startup script to use mysqlmanager
[mysql.server]
use-manager
[mysqld07]
port= 3307
socket = /srv/mysql/lx07sock
pid-file= /srv/mysql/lx07/lx09.pid07
datadir = /srv/mysql/lx07/data
log-error = /srv/mysql/lx07/mysql.error.log
[mysqld20]
port= 3320
socket = /srv/mysql/lx20sock
pid-file= /srv/mysql/lx20/lx09.pid20
datadir = /srv/mysql/lx20/data
log-error = /srv/mysql/lx20/mysql.error.log
# run commands as user mysql
su mysql
# go to the main mysql directory
cd /srv/mysql
# make a directory for each instance
mkdir lx07
mkdir lx20
mysql_install_db --datadir=/srv/mysql/lx07/data --user=mysql --verbose
mysql_install_db --datadir=/srv/mysql/lx20/data --user=mysql --verbose
# start the server
/etc/init.d/mysql start
# connect to first instance and configure so root can log in
# from anywhere. You may or may not want to do this.
mysql --socket=/srv/mysql/lx07sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password;
use mysql;
update user set password=password('password') where user='root';
flush privileges
# same for second instance
mysql --socket=/srv/mysql/lx20sock
create user 'root'@'%' identified by 'password'
grant all on *.* to 'root'@'%' identified by 'password';
use mysql;
update user set password=password('password') where user='root';
flush privileges
Regards,
Rich
-Original Message-
From: Mohammed Abdul Azeem [mailto:[EMAIL PROTECTED]
Sent: Saturday, April 15, 2006 12:53 AM
To: mysql@lists.mysql.com
Subject: ~ How to install 3 instances of mysql~
Hi,
I need to install 3 instances of mysqld server on a single
machine. Can anyone let me know how this can be acheived ?
It would be helpful if someone can send me some links and
suggestions regarding the same. Also pls lemme know what kind
of a configuration file i need to have in order to acheive the same.
Thanks in advance,
Abdul.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
Wow, I found the problem! I think this may be a bug. In my case, I've got three instances running on ports 3306, 3307, and 3320. On the local machine, I connect to them via mysql -p --port-3306 --host=localhost mysql -p --port-3307 --host=localhost mysql -p --port-3320 --host=localhost Except that connecting to port 3307 doesn't really happen. It seems that the command line client connects to the main instance via the default socket when the host is not specified, or is the value localhost. So, even though I've specified the host and port, I wind up connecting to the main instance. I found this out by stopping the main instance on 3306, and then I issue: LX09:/etc # mysql --port=3307 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=localhost ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib /mysql/mysql.sock' (2) LX09:/etc # mysql --port=3307 --host=127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. LX09:/etc # mysql --port=3307 --host=nnn.nnn.nnn.nnn -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Note that /var/lib/mysql/mysql.sock is the socket associated with the main instance, not the alternates. It seems that if I *specify* a port, then mysql ought to use that port. It only seems to be an issue when not specifying a --host, or when using the value 'localhost'. It's a bit terrifying because during all my testing, I am thinking I am connected to the correct instance, when in fact, I was not. It will be very easy to blow away the main instance data by mistake. Can I somehow convince the mysql command line client to use the specified parms, rather than the (incorrect) socket? Thank you. Regards, Rich -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 12, 2006 6:00 PM To: mysql@lists.mysql.com Subject: Re: MySql Error Number 1130 When you are trying to connect to port 3307, for example, are you specifying that port from your remote machine? Or are you connecting to the mysqld listening on port 3306? You could try shutting down the server listening on port 3306, and then connecting to port 3307. See if the error message changes or goes away. shell# mysql -h host_name -u root -px --port=3307 I think you may need to specify the absolute IP address in the user table, instead of a wildcard '%'. See if this helps. Use your root username and password in place of 'tommy'. I did not want to mess up my root user account! Use the IP address of your remote machine you want to connect to mysql with, in place of 10.0.0.5. mysql create user 'tommy'@'10.0.0.5' - identified by '12345'; Query OK, 0 rows affected (0.00 sec) mysql grant all on *.* - to 'tommy'@'10.0.0.5' - identified by '12345'; Query OK, 0 rows affected (0.00 sec) mysql select * from user where user = 'tommy' \G ** 1. row * Host: 10.0.0.5 User: tommy Password: snipped Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 1 row in set (0.00 sec) HTH Keith In theory, theory and practice are the same; in practice they are not. On Wed, 12 Apr 2006, Duzenbury, Rich wrote: To: mysql@lists.mysql.com From: Duzenbury, Rich [EMAIL PROTECTED] Subject: MySql Error Number 1130 Hi all, I am setting up two additional instances of mysql on my mysql server, which is running version 5.0.18-standard-log. I've got the additional instances set up, and they are running. I can see that they are bound to the proper ports. I can connect to them locally like mysql -p --port=3306 mysql -p --port=3307 mysql -p --port=3320 I have always been able to connect remotely from my workstation to the base server on 3306. My problem is that I cannot connect remotely to either of the new additional instances running on 3307 or 3320. Anytime I attempt to do so, I receive MySQL Error Number 1130, Host
RE: MySql Error Number 1130
localhost means socket. Hmm, I don't believe it does. localhost is a DNS shortcut to the IP address of the local machine. If that is how mysql wants to treat things, then it should issue an error message on connect because --host=localhost and --port=anything would then be mutually exclusive. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
-Original Message- From: Barry [mailto:[EMAIL PROTECTED] Sent: Thursday, April 13, 2006 9:41 AM To: mysql@lists.mysql.com Subject: Re: MySql Error Number 1130 Duzenbury, Rich wrote: localhost means socket. Hmm, I don't believe it does. localhost is a DNS shortcut to the IP address of the local machine. If that is how mysql wants to treat things, then it should issue an error message on connect because --host=localhost and --port=anything would then be mutually exclusive. Thanks. Regards, Rich Wasn't it something like on local machines MySQL doesn't open a socket port because it works directly in program and not going the loop out of mysql and back in through the socket? I'm not sure I understand your statement. It's wise to use a unix domain socket where possible because they perform better than network sockets. However, it's misleading for the mysql client to ignore a command line directive as important as --port or --host without warning. Especially since this can cause connection to the wrong instance. Another way this would have been made more obvious is if the welcome message in the client were a bit more descriptive. Currently, I see 'Your mysql connection id is 2 to server version: 5.0.18-standard-log'. Perhaps the client should indicate the socket or ip/port that was actually used in the welcome message. Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication, sort of (5.0.18)
Hi all, I have two servers that have production data on them, and then one server where I would like to keep an active copy of the data. Currently, there is a job that runs in the middle of the night that basically does a mysqldump --host=production_server --all-databases | mysql --host=backup_server It takes a long time to copy over all the data, when in reality, it's not changing that much. The great thing about this approach is that yesterdays tables are very nearby and easily accessible in the case where someone fat-fingers some data. The trick to this is that I want the backup server to be one day behind the production server, not real time mirrored, so maybe replication isn't the right thing, I am not sure. I am using the innodb storage engine for most things, though there are a few myIsam tables. Advice appreciated. Thank you. Regards, Rich Duzenbury -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySql Error Number 1130
Interesting. I have never heard of that option. LX09:/home/rduz/backup # mysql -p -h. --port=3307 Enter password: ERROR 2005 (HY000): Unknown MySQL server host '.' (1) Perhaps it no longer functions? Thanks. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql Error Number 1130
Hi all, I am setting up two additional instances of mysql on my mysql server, which is running version 5.0.18-standard-log. I've got the additional instances set up, and they are running. I can see that they are bound to the proper ports. I can connect to them locally like mysql -p --port=3306 mysql -p --port=3307 mysql -p --port=3320 I have always been able to connect remotely from my workstation to the base server on 3306. My problem is that I cannot connect remotely to either of the new additional instances running on 3307 or 3320. Anytime I attempt to do so, I receive MySQL Error Number 1130, Host 'nnn.nnn.nnn.nnn' is not allowed to connect to this MySQL server. I've been googling and reading manuals all day, and haven't made a dent. User Table *** 1. row *** Host: % User: root Password: (redacted) Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 *** 2. row *** Host: localhost User: root Password: (redacted) Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 db Table: *** 1. row *** Host: % Db: mysql User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Host table is empty. Here is one of the processes: mysql 5972 5960 0 16:17 pts/000:00:00 /usr/sbin/mysqld --no-defaults - -port=3307 --socket=/srv/mysql/lx07/mysql.sock --pid-file=/srv/mysql/lx07/lx09.p id07 --datadir=/srv/mysql/lx07/data --log=/srv/mysql/lx07/mysql.log --skip-locki ng --key_buffer=256M --max_allowed_packet=16M --table_cache=256 --sort_buffer_si ze=16M --read_buffer_size=8M --read_rnd_buffer_size=4M --myisam_sort_buffer_size =64M --thread_cache_size=8 --query_cache_size=16M --thread_concurrency=8 --tmpdi r=/tmp/ --innodb_file_per_table --innodb_data_home_dir=/srv/mysql/lx07 --innodb_ data_file_path=ibdata1:100M:autoextend --innodb_log_group_home_dir=/srv/mysql/lx 07/ --innodb_log_arch_dir=/srv/mysql/lx07/ --innodb_buffer_pool_size=128M --inno db_additional_mem_pool_size=20M --innodb_log_file_size=32M --innodb_log_buffer_s ize=8M --innodb_flush_log_at_trx_commit=1 --innodb_lock_wait_timeout=50 I have made sure to flush privileges, and I've restarted the server several times, to no avail. I am running out of things to try, and hoping someone can see my (what is most likely) obvious error. Thank you. Regards, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete Duplicates
Hi there. Any quick way of killing duplicate records? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compound Insert Statement
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield) VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't be
done.
Any recommendations?
Appreciate it.
Cheers
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update Multiple Records
Hi folks. I want to set the status of 5 records to 'completed'. how do I go about that without having to prepare 5 different instructions? update myTable set status = 'completed' where id=10 OR id=20 OR id=30 OR id=40 OR id=50 I'm trying to reduce the number of instructions. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
offset or skip_count
Hi folks. I'm wanting to provide some safety for a search. I'll be grabbing about 45 fields, and perhaps thousands of records, so that I can build an xml file using my middleware. How can I structure an SQL statement so that I can jump through records 100 at a time? Do I use offset or skip_count? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tiger 4.1.15 Issues
Hi folks. Just installed 4.1.15 on OS X (Tiger 10.4) and am having issues. With permissions, have they changed since 4.1.14? It seems 4.0.x had the following permissions on the data folder: 775 for root/wheel Now, what works for me on this new version, is: 770 for mysql/wheel Also, hopefully not related, if anybody can tell me why my browser on my dev machine (kayak) can't see kayak.local, I'd buy them a beer or two. It's driving me nuts. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tiger 4.1.15 Issues
Another developer told me his data folder had root/wheel as user/group. Also 775 as the permissions. Just changed it to 700 and it works. As for the kayak issue, it's Safari. It doesn't want to see the web sharing on the very same machine it's on. Very temperamental. Cheers Michael Stassen: No changes I'm aware of. Is it possible you're simply misremembering? Normally, mysqld runs as user mysql, so the data directory needs to be owned by mysql. Otherwise the server wouldn't be able to access its own data. Now, what works for me on this new version, is: 770 for mysql/wheel I'd suggest 750 for mysql:mysql, or even 700. Mysql itself doesn't care about the group or the group permissions. It certainly has no need of a group-write permissions on the data directory. In fact, it won't be happy if some other process modifies its data directory. You should have a good reason to allow group access to the data directory before you grant it. Also, hopefully not related, if anybody can tell me why my browser on my dev machine (kayak) can't see kayak.local, I'd buy them a beer or two. It's driving me nuts. This doesn't sound like a mysql question, but I might have an answer. Could you elaborate on what you mean by my browser on my dev machine (kayak) can't see kayak.local? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Terrible MySQL Administrator
Hi Scott. Thanks for the reply. Saying that I deleted the crontab was incorrect. I should have said that I deleted the line where that crontab schedule was entered. Anyway, it hasn't shown up as I did a crontab -e, which would have reloaded it, so perhaps it worked. Cheers Scott Haneda: Just cause you deleted the crontab, does not mean it stops: crontab -l That lists the current cron jobs crontab the_cron_file_name Will reload the one you have changed. Of course, you could just send the alerts to nowwhere with something like /dev/null -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Terrible MySQL Administrator
Hi folks. Since I installed MySQL Administrator on OS X and tried to put a schedule in for a backup, the thing simply won't go away. It sends an email into my mail app in the terminal on a daily basis and it's driving me mad...slowly...bit by bit. Can someone suggest how I get rid of these emails, as the cron was deleted weeks ago, and I took out the entry in a crontab I found, but it still ...persists...over...and...over. I appreciate any advice. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function to show when a field's value was last updated
Unfortunately that would have to be designed into the system into a field. When updated, the instructions would be to instruct that field to update. Cheers Mark: I need it some info to help a client defend against a legal challenge. Is there a MySQL function that will allow me to ascertain the date and time that a particular field's value was last updated. I can't find anything in the MySQL documentation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: automatic backups not working MySql Admin
If you open your terminal, you'll see some error messages waiting for you. Apparently it's buggy. I turned off my admin backups. Cheers John Doneker: Is this because this free software doesn't supply this functionality? Or could it be a bug? Is there another answer? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Administrator Cron Errors
Hi folks. Downloaded the new Admin for OS X. I set a weekly cron and for some reason, it's emailing my terminal Mail app saying it couldn't load a profile. It's not the address I told it to send it to, and I don't quite get why it can't load a profile. Having just tried to back up my tables twice directly from the application, it crashed twice. Is this thing buggy? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Joins Tutorial Anywhere?
Hi folks. Any chance on a tutorial from joins? I find them totally confusing and I know there's some power in them, so I need to learn them. Having asked that, another question arises...do my field names in different tables within the same database have to overlap (same field name) in order for joins to work? I'm currently naming fields in such a manner: TABLE1 table1_alpha table1_bravo TABLE2 table2_firstname table2_lastname Appreciate any guidance on these two questions. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins Tutorial Anywhere?
Thanks Shawn. My naming convention (table_field) is more for my middleware development. Much easier to see what to grab that way. It started when was doing embedded inlines (pre-joins) and found the reference to 'id' was confusing. Anyway, thanks for the join tutorial links, folks. Appreciate it. Cheers [EMAIL PROTECTED]: If you have tried the tutorial and are still lost, let us know and someone will happily work with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic value like excel
this may help you test create table d ( - date date ); Query OK, 0 rows affected (0.45 sec) test insert into d values(now()); Query OK, 1 row affected (0.12 sec) test select * from d; ++ | date | ++ | 2005-09-10 | ++ 1 row in set (0.00 sec) On Sep 10, 2005, at 8:52 AM, liofr wrote: i want to put a value in a column that is the date of today . IS there a solution to do that in sql way ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] Rich Allen Dare Do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking Methods
Hi there. I started a different thread on this, but then I realized I might not have to use InnoDB for this. Let me explain what I wish to achieve. I want to create records in an established table. I then want them locked (either by locking the whole table or by individual record) so that they cannot be updated or deleted. That's it. I am concerned that locking a table won't allow me to add new records. I also need the records fully viewable. Which table format should I choose, and how do I implement this? I've reviewed some of the alternatives, and they got all confusing to me. 15.11.3 InnoDB and Transaction Isolation Level indicates that READ COMMITTED is what I should be looking for, but it refers to an index that I'm unaware of, as nothing is indexed. Any leadership appreciated. Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SCO issue
On Sep 5, 2005, at 3:10 PM, Daniel Kasak wrote: This is the part that gets me: As part of the agreement, the companies will work together on a range of joint marketing, sales, training, business development and support programs that will benefit customers throughout the Americas, Europe and Asia. I suppose it depends on how much 'joint work' is actually involved. As another poster pointed out, this could just be SCO up to their usual spin. But I agree with you - if this is some kind of special business relationship, then Postgres is looking all the more inviting. MySQL AB doing work for SCO is one thing, partnership would be much a different matter which would then lead me to agree that looking at Pg would be a good idea. Hopefully MySQL AB will make all this clear one way or the other ... Rich Allen Dare Do
InnoDB Record Lock Command
Hi there. I am looking for the syntax to lock individual records in an InnoDB table. I can't seem to find anything outside of locking full tables. Any urls out there? Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Record Lock Command
something along the lines of: mysql select * from table where column = value lock in share mode On Sep 5, 2005, at 4:01 PM, Rich wrote: Hi there. I am looking for the syntax to lock individual records in an InnoDB table. I can't seem to find anything outside of locking full tables. Rich Allen A kidney transplant saved my life, plese consider being an organ donor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
views in 5.0.11
iH i have a view created in 5.0.11 on several innodb tables. when doing a select * on the view after first getting into the mysql command line, the last column has incorrect values. without running any other command and performing the same select statement, all columns are correct. is this a known issue? running mysql on mac os x 10.4 thanks Rich Allen Dare Do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: all user command
mysqladmin processlist On Jul 26, 2005, at 5:21 PM, Joeffrey Betita wrote: hello what command should i type to see all the user connected to the database. thank you very much. Rich Allen Dare Do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLDump - Command line password
have you looked at using a my.cnf file? eMac:~ hcir$ mysqldump test /temp/test.sql eMac:~ hcir$ ls -l /temp/test.sql -rw-r--r-- 1 hcir staff 78893008 Jul 19 16:47 /temp/test.sql contents of ~/.my.cnf [client] user= username password= password # actual username and password of course are not 'username' and 'password' On Jul 19, 2005, at 3:40 PM, Cabbar Duzayak wrote: Hi, I have setup cronjobs to take daily backups of my db using mysqldump. But the problem is, mysqldump requires the password to be passed via command line, which means anyone on the same machine can take a peek at my password using top, ps -ef, etc. Is there a way of avoiding this, i.e. making it read the password from some file, etc? Or, is there any other alternative I can use?
Re: How do you become a MySQL DBA
iH Mark, i work for a small independent telco where i have developed a number of apps that interface with class 5 switches and other telco gear. if you would like to email me off list i would be happy to give more details On May 31, 2005, at 2:50 PM, Mark Ahlstrom wrote: Here's an odd question for you, how do you become a MySQL DBA? I've got enough Solaris/Linux Experience under my belt and I was a Jr. Oracle DBA for a year, which got me really interested in RDBMS. I try to work with MySQL as much as possible, but I work with one of those large telco's that does not like anything where they can't pay large amounts of money. This means I have to work it into my spare time. I've been trying to tie MySQL into basic services: ftp, DSPAM, pop3, AND offer help for what we do have: running backups and repairing the odd table when needed. But the question is, what else could I do to help develope DBA skills? Right now I have very little data that goes beyond 2 tables, so my query skills are withering. Rich Allen A kidney transplant saved my life, plese consider being an organ donor. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tiger - MySQL --- any news ???
i am running 4.1.9 binary on tiger without any trouble Your MySQL connection id is 1 to server version: 4.1.9-standard - hcir On May 23, 2005, at 1:03 PM, Kevin Victor wrote: I would like to know if there is any existing solution for running MySQL on Tiger. I dont see a binary version for 10.4 yet, will it be released any time soon?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone explain???? strange query!!
On Fri, Apr 29, 2005 at 10:46:16AM -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I did two query, can someone explain what happened? Not with what you included: | customer_num | title | first_name | middle_name | last_name | email | dayphone | evenphone | address1 | Address2 | address3 | address4 | city | state_province | country | CompanyName | pkey | zipcode | bldgID | 19 columns. | NULL | NULL | NULL | Tempe | Arizona| USA | NULL| D5BC55546AC74547EE497D4F559607DF | 85281 | NULL | 10 columns, presumably the line with address and zipcode, not the line with first_name and so forth. How did it find that row when the fields are NULL? I believe it isn't. Do the query with \G instead of ; on the end so that you see what values are in which columns. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql syntax
I'm used to doing something simple such as the following in sql server: SELECT u.Username, p.UserID FROM Users u LEFT JOIN Person p ON u.UserID = p.UserID However, I'm not seeing the same results in MySQL. I don't get all the recs in the users table and NULLs in the userID column from the person table when the users.userID is not in the person table. I get no recs at all. What am I missing here? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql syntax
I forgot the important part: what I want is to filter on a userID in the person table such as - SELECT u.Username, p.UserID FROM Users u LEFT OUTER JOIN Person p ON u.UserID = p.UserID WHERE (p.UserID = 5) OR (p.UserID IS NULL) THis will return both the matching recs from the user table and NULLs from the person table in sql server, but is what I cant get to work in mysql... _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 19, 2005 3:14 PM To: Rich Brant Cc: mysql@lists.mysql.com Subject: Re: mysql syntax Rich Brant [EMAIL PROTECTED] wrote on 04/19/2005 03:05:51 PM: I'm used to doing something simple such as the following in sql server: SELECT u.Username, p.UserID FROM Users u LEFT JOIN Person p ON u.UserID = p.UserID However, I'm not seeing the same results in MySQL. I don't get all the recs in the users table and NULLs in the userID column from the person table when the users.userID is not in the person table. I get no recs at all. What am I missing here? Thanks! I don't see any obvious problems with your query. I use LEFT JOINs all the time. Which version MySQL are you using? Can you give us an example of what your base data looks like (Users table and Person table), and what you actually got as a response? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?
Thank you so much Vivian! Your first solution was exactly what I was looking for! It works perfectly! Thanks so much! Richard Vivian Wang [EMAIL PROTECTED] wrote: create table temp select * from viewvisitor order by lastviewtime desc; select app, itemid, ownerid, visitorid, vusername,lastviewtime, sum(viewcount) AS totalcount, itemname from temp where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; or if you only care about max(lastviewtime), you can do this, select app, itemid, ownerid, visitorid, vusername, max(lastviewtime), sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount; Rich Carr wrote: Is there a way to set which rows values are used by the GROUP BY clause for the fields that are not in the GROUP BY clause? In this following select statement the group by and order work but the value of the lastviewtime field is not the value of the most recent datetime row. Is there any way to modify the select statement so that the returned lastviewtime field will have the most recent datetime? select app, itemid, ownerid, visitorid, vusername, lastviewtime, sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount create table viewvisitor ( app char(15), itemid INT UNSIGNED NOT NULL, ownerid INT UNSIGNED NOT NULL, ousername varchar(25), visitorid INT UNSIGNED NOT NULL, vusername varchar(25), vfullname varchar(70), lastviewtime DATETIME NOT NULL, viewcount INT, itemname VARCHAR(40), PRIMARY KEY master(app, visitorid, itemid), INDEX (ownerid), INDEX (lastviewtime), INDEX (viewcount) ); Thanks!!! Rich - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?
Thanks again! Dan Bolser [EMAIL PROTECTED] wrote:On Tue, 12 Apr 2005, Rich Carr wrote: Hi Dan, Thanks very much! First, I can't figure out how to reply to this so that it shows up in the MySQL list. How does one do it? erm... if you hit 'reply all' or answer yes to 'reply to all' it should send mail to [EMAIL PROTECTED] Ahhh...I see what you mean now... you want to reply to *that* message in the archive... Don't know. Second, when I tried to suggest I get the following error message, I'm using MySQL 4.0.15 ahhh... you need = 4.1 to support subqueries... I cc:'ed this back to the mailing list if anyone else can help. Let me reformat your query to check SELECT * FROM viewvisitor INNER JOIN ( select app, itemid, sum(viewcount) AS totalcount, FROM viewvisitor GROUP BY concat( app,itemid ) ) USING( app, itemid, visitorid, itemid, itemname, vusername,totalcount, lastviewtime); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ...' Yeah.. it don't like the sub-query. Not sure what your query is actually doing, but it is academic unless you upgrade your server. Try this recipy... create temporary table bleah select PK, MIN(value_that_you_want_to_min_of_in_the_results_row) as the_original_column name from meTable; select a.* from meTable a inner join bleah using(PK,the_original_column_name); Their should be a unique minimum for the_original_column. When I said before that 'you really need to know what you are doing' - what I mean is, I am very bad at explaining. Sorry if the above looks like garble (but I can't do better). Thanks! Dan Bolser wrote: I think the suggestion posted here... http://lists.mysql.com/mysql/182424 should get you going in the right direction. You really need to know what you are doing to know if it is giving you the correct answer or not. It would be cool if their was something like a GROUP_ROW(cols, expr) to do what you want explicity. SELECT * FROM chi; +--++--+ | child_id | id | type | +--++--+ | 1 | 1 | 0 | | 2 | 1 | 0 | | 3 | 1 | 1 | | 4 | 1 | 2 | | 5 | 2 | 2 | +--++--+ -- Totally fake and probably silly SQL... SELECT id, group_row(child_id,type,child_id=min(child_id)) FROM chi2 GROUP BY id; +--++--+ | child_id | id | type | +--++--+ | 1 | 1 | 0 | | 5 | 2 | 2 | +--++--+ or GROUP_ROW(cols,sortby=) -- More sillyness SELECT id, group_row(child_id,type,sortby=rand()) FROM chi2 GROUP BY id; Just an idea. Dan. On Mon, 11 Apr 2005, Rich Carr wrote: Is there a way to set which rows values are used by the GROUP BY clause for the fields that are not in the GROUP BY clause? In this following select statement the group by and order work but the value of the lastviewtime field is not the value of the most recent datetime row. Is there any way to modify the select statement so that the returned lastviewtime field will have the most recent datetime? select app, itemid, ownerid, visitorid, vusername, lastviewtime, sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount create table viewvisitor ( app char(15), itemid INT UNSIGNED NOT NULL, ownerid INT UNSIGNED NOT NULL, ousername varchar(25), visitorid INT UNSIGNED NOT NULL, vusername varchar(25), vfullname varchar(70), lastviewtime DATETIME NOT NULL, viewcount INT, itemname VARCHAR(40), PRIMARY KEY master(app, visitorid, itemid), INDEX (ownerid), INDEX (lastviewtime), INDEX (viewcount) ); Thanks!!! Rich - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! - Do you Yahoo!? Yahoo! Sports - Sign up for Fantasy Baseball.
Group By - Is there a way to set which rows values are used for the fields not in the Group By clause?
Is there a way to set which rows values are used by the GROUP BY clause for the fields that are not in the GROUP BY clause? In this following select statement the group by and order work but the value of the lastviewtime field is not the value of the most recent datetime row. Is there any way to modify the select statement so that the returned lastviewtime field will have the most recent datetime? select app, itemid, ownerid, visitorid, vusername, lastviewtime, sum(viewcount) AS totalcount, itemname from viewvisitor where ownerid = 2 GROUP BY concat( app, itemid ) ORDER BY totalcount create table viewvisitor ( app char(15), itemidINT UNSIGNED NOT NULL, ownerid INT UNSIGNED NOT NULL, ousername varchar(25), visitorid INT UNSIGNED NOT NULL, vusername varchar(25), vfullname varchar(70), lastviewtime DATETIME NOT NULL, viewcount INT, itemname VARCHAR(40), PRIMARY KEY master(app, visitorid, itemid), INDEX (ownerid), INDEX (lastviewtime), INDEX (viewcount) ); Thanks!!! Rich - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
change a column type and innodb foreign key constraints
Hi, I have a column 'id' within a table : CREATE TABLE `reference` ( *`*id*`* smallint(5) unsigned NOT NULL auto_increment, `study_name` text, `author` text NOT NULL, `date` date NOT NULL default '-00-00', `reference` varchar(250) NOT NULL default '', `title` varchar(250) NOT NULL default '', `pmid` int(15) default NULL, `project` varchar(35) default NULL, `abstract` text, `datasource` smallint(5) unsigned default NULL, PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB that i need to change from smallint to int There are a number of foreign key constraints from other columns in other tables on this column: eg CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `num_peds` int(7) unsigned default NULL, `affected` int(7) unsigned default NULL, `unaffected` int(7) unsigned default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_178` FOREIGN KEY (`gene_ID`) REFERENCES `gene` (`id`), CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE ) TYPE=InnoDB Upon trying to modify the id columns in the reference table, i'm getting the following mysql alter table reference modify id int; ERROR 1025: Error on rename of './nugenob/#sql-2ed1_e001' to './nugenob/reference' (errno: 150) mysql It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? cheers Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
access two like tables with one query
iH i have a database that i only have READ access to (i am not the creator). there are tables with stats data that are created each day; ie data3_1_2005 and 'data3_2_2005. each table has the same layout. create table data3_1_2005 ( port char(8), tmval int, val int ) how can i create a single query to get all the rows related to port from both files without creating a merge table? thanks - hcir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql vs postgresql
On Fri, Feb 25, 2005 at 06:43:50PM +0100, Jochem van Dieten [EMAIL PROTECTED] wrote: Don't you think it is childish to link to documentation from 2003? I've never seen a child do anything like you describe. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
On Thu, Feb 24, 2005 at 02:38:46PM +0200, DebugasRu [EMAIL PROTECTED] wrote: Probably fsync() had failed to flush some part of a 16 kB page to disk. so what ? one of trade-offs would be to re-read the data from the disk and compare it with what it should be (another copy on the disk) and only after that fix the transaction, That didn't get rid of the race condition, it just moved it. So now you write the transaction, and you read it off, and then you write that it was OK -- but what if that write gets interrupted? So you write the transaction, and you read it off, and then you write that it was OK, and then you read that off, and then you write that the OK was OK. No, that could get interrupted to... Meanwhile, it turns out that you've been reading back from cache instead of platters all along. Or perhaps it turns out that the battery-backed cache in the disk controller has a dead battery, or simply doesn't work. If the disk promises data is written but it is not then there is nothing MySQL can do to *tell* that something is amiss. I think you've confused an RDBMS with a system administrator. Next thing you know you'll be complaining that MySQL isn't ACID-compliant because it can't survive a fire. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wikipedia down, slashdot covering, mysql mentioned
On Thu, Feb 24, 2005 at 09:10:32AM -0500, Greg Whalin [EMAIL PROTECTED] wrote: Exactly. No ACID database can ensure integerity in such a situation. Postgres, Oracle, or any other transactional DB would have suffered the same fate in these two cases (LiveJournal, Wiki). FWIW, my understanding of LiveJournal's integrity problem after the power outage involved tables they weren't yet able to migrate off of MyISAM, and getting replication content in all directions. The stuff migrated to InnoDB already came up fine. Their replication setup is a bit complex -- see http://www.danga.com/words/2004_mysqlcon/mysql-slides.pdf for an overview. (My kit at the same facility was hit too, and recovered correctly, for what it's worth. :-) At least this mailing list has progressed beyond Why didn't they have a UPS?, I suppose. :-) -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backup User
On Thu, Feb 24, 2005 at 08:39:00AM +1100, Daniel Kasak [EMAIL PROTECTED] wrote: You don't need to create a special MySQL user for backups. If you like, you can create a Linux account that only you know the password to, and then make sure the backup scripts is only readable by you ( root will also be able to read it ). Or just run the backup script as root, and then root can only read it ( you have to set the correct permissions here too of course ). What you really want to avoid is having the password on the commandline. File permissions won't matter at all if you end up running a command that puts your password in the output of 'ps'! Command lines are always public information. Put the password for mysqldump in the running user's ~/.my.cnf instead, and tighten the permissions on *that* file. [client] password=Your password goes here -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: set auto_increment does not work?
On Tue, Feb 22, 2005 at 03:46:34PM -0600, Scott Purcell [EMAIL PROTECTED] wrote: Hello, ALTER TABLE tbl_name AUTO_INCREMENT = 1000 will start your records at 1000 But it does not work for myself. How can I get the auto_increment to begin at a set starting point? I know I could insert a bogus record, but it just seems sloppy. Does it work if you use MyISAM tables? Just a hunch, based on something I ran into in 2002: http://www.lafferty.ca/stuff/misc/innodb-autoincrement-bug but never had time/reason to investigate further (MyISAM proved sufficient in that application). If you can duplicate what I saw then it might be worth following up with MySQL AB after testing in the latest MySQL release. -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one hour is/is not 60 minutes, that's the question...
On Tue, Feb 15, 2005 at 06:48:08PM +0100, schlubediwup [EMAIL PROTECTED] wrote: Hi again mysql-listers mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) ^^ Did you check the warning (with SHOW WARNINGS)? -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with SELECT
- Original Message - From: Rhino [EMAIL PROTECTED] To: mysql mysql@lists.mysql.com Sent: Thursday, December 30, 2004 9:03 AM Subject: Need help with SELECT I am trying to think of SQL that will let me show a one to many relationship as a single row in a result set. They say a picture is worth a thousand words so let me draw that picture. Event_IDEvent_Nameetc. 1 Concert 2 Art Exhibit 3 Spelling Bee [Primary key: Event_ID] Event_IDFile_NumberFile 1 1promoter_logo.jpg 1 2venue_logo.jpg 1 3performer_graphic.jpg 2 1artist_graphic.jpg [Primary Key: Event_ID, File_Number] Desired Result: Event_IDEvent_NameFileFile File 1 Concert promoter_logo.jpg venue_logo.jpg performer_graphic.jpg 2 Art Exhibitartist_graphic.jpg 3 Spelling Bee There is one record in the Events table for each event that an organization is promoting. For each event, there could be 0 to 'n' files that give more information about the event, such as pictures, audio clips, or whatever. I want to display all the information about the event, including all of the file names for that event, on a single result set row for each event, regardless of how many files exist for that event. [At the moment, there is a limit of 3 files per event but that could increase.] Here are two ways, but both are kind of hoaky. They both assume you know the number of files you want to display. Method 1 - with subqueries SELECT DISTINCT EventName, (SELECT f1.FileName from EventFiles as f1 where f1.EventID = e.EventID and f1.FileID = 1), (SELECT f2.FileName as File2 from EventFiles as f2 where f2.EventID = e.EventID and f2.FileID =2), (SELECT f3.FileName as File3 from EventFiles as f3 where f3.EventID = e.EventID and f3.fileID =3) FROM Events e Method 2 - without subqueries CREATE TEMPORARY TABLE FileOrder( EventID tinyint, EventName char(20), File1 varchar(35), File2 varchar(35), File3 varchar(35)); INSERT INTO FileOrder (select Events.EventID,EventName, FileName,'None','None' FROM EVENTS INNER JOIN EventFiles on EventFiles.EventID = Events.EventID Where FileID = 1) UPDATE FileOrder SET File2 = (SELECT DISTINCT FileName FROM EventFiles,Events WHERE EventFiles.FileID = 2 AND EventFiles.EventID = FileOrder.EventID); UPDATE FileOrder SET File3 = (SELECT DISTINCT FileName FROM EventFiles,Events WHERE EventFiles.FileID = 3 AND EventFiles.EventID = FileOrder.EventID); This approach won't give you a row for Event 3 since it doesn't exist in EventFiles. I'm sure this can be solved with a little work. Sorry I didn't you the same column names as you did, but I was in a hurry. A report writer or even ACCESS makes these cross-tab reports trivial. Regards Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd MySQL error
I have searched the archives (well, the search never came back...), and I have searched the web, but found very little that could help with the scenario that we are currently experiencing. We have the 4.0.21 RPM's installed direct from MySQL.com on a Fedora Core 2 server. The previous incarnation of this server was a Fedora Core 1 server which ran flawlessly. Since the new server has been put in to production, on a daily basis (random times), we are forced to restart mysql because all processes which attempt to get data out of the databases fail. We access the database through Apache+PHP+MySQL as well as via the Perl DBI interface.. Everything comes back after a restart... In the logs, all we see is: 041018 20:32:50 mysqld started 041018 20:32:51 Warning: Asked for 196608 thread stack, but got 126976 041018 20:32:51 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.21-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM 041020 16:20:35 Error in accept: Too many open files 041020 16:33:23 /usr/sbin/mysqld: Normal shutdown 041020 16:33:24 InnoDB: Starting shutdown... 041020 16:33:27 InnoDB: Shutdown completed 041020 16:33:27 /usr/sbin/mysqld: Shutdown Complete 041020 16:33:27 mysqld ended I did find that the Warning can be safely ignored. However, the problem definitely is at the Error in accept: Too many open files line... the rest of the system is behaving happily, so I am not sure where to go from here.. /proc/sys/fs/file-max comes back with an astronomical 76949, and sysctl shows nominal activity: fs.file-max = 76949 fs.file-nr = 3760 0 76949 fs.inode-state = 17709 26290 0 0 0 0 fs.inode-nr = 17709 2629 A couple of related postings seemed to indicat that it had to do with the mysql process and/or the limits set upon the mysql user account, but that was all speculation. Ideas, Comments, Suggestions are all more than welcome! -Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best way to restore remote database
Hi all, I have a bunch of tables I need to create on a remote database. The backup files are all in one folder. They are .frm,.myd and.myi files. 1. Do these files need to be on the remote server in order to be used as to create the table? 2. Looking at the user docs on myslq, it appears I need to retrore the tables individually. What's the command to restore all the tables at once? 3. Can I use control center to accomplish this, or deos it have to be done via the command line? Thanks! Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: On the licensing once again
On Wed, Aug 11, 2004 at 05:43:16PM +0200, Leonardo Francalanci [EMAIL PROTECTED] wrote: If you develop a product, say, some kind of online shopping system that you distribute on a CD which installs Linux, Apache, MysQL, PHP and your App and distribute that, then you probably should be paying for a license. This is because instead of you handing over full code (and it's rights) to the client as their property, you are placing licensing limitations on it. Ok, but if I say to a client (that has his own web server) you will need to install Mysql on your server to run the site I'm writing for you, will he need a license? Look at it this way: No matter what, *everyone* using MySQL requires a license. Here's the question you need to ask: Which of the two possible licenses can I use without contravening the terms? Obviously, you'd rather not pay, so you want to look at the GPL first. To do that, you and/or your company's lawyer need to read the terms of the GPL and apply them to the way you wish to use the software. If you reach a term which you are unable to comply with, then you can't use the GPL, and need a commercial license. But you're entering into a legal agreement with MySQL AB, so you need to read every term in the license and determine, with legal assistance if you need it, whether or not you can do what you want to do under the license's terms. The mailing list is probably a bad place to turn for legal advice (which, incidentally, I am not giving you right now :-). -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large 30 GB Database
i have one MySQL dB that is currently about 26Gb, properly indexed searches are very quick - hcir On Jul 28, 2004, at 4:23 AM, matt ryan wrote: Should I even attempt this using mysql? Has anyone played with this much data in mysql? I've got two 100 gig databases in mysql, and slave replication on both of them, the only time I have a problem is table scans, that much data will be slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Macs
iH any machine running OS X can run MySQL, this link http://dev.mysql.com/downloads/mysql/4.0.html has a Mac OS X version with a package installer - hcir On Jun 29, 2004, at 5:14 PM, Jim Carwardine wrote: Im new to the list and new to mySQL. Im a Mac user and would like to set up a DB on my Mac. When I look at the MySQL web site, I cant seem to find any info on what hardware can be used. Can mySQL be run on a Mac? If so, what do I need to know about how to set it up? Can anyone point me to a setup procedure? Jim -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to monitor that slave is not working because of user rights?
On Thu, Jun 17, 2004 at 06:01:56PM +0300, Cemal Dalar [EMAIL PROTECTED] wrote: Actually. I am talking about a machine which is at office. And inside office we're connecting using ADSL which gets dynamic Ip address. The slave server's IP address actully doesn't change. It doesn't have a real ip address only the ADSL has. Simply saying we are using NAT. What I have in mind is to monitor the Read_Master_Log_Pos, Exec_master_log_pos with the master server and only allow small differences like ~1. And also checking the other Slave_* values. You're close here, I think. You've discovered that monitoring the slave's running status isn't sufficient (but note that that doesn't mean that you *shouldn't*, just that you need more), and that those counters will keep incrementing. But you can't really tell how much they will increment, and on the slave they will stop incrementing together if network connectivity is unavailable. There's two ways to be sure that updates are going from the master to the slave. The simplest is to watch that exec_master_log_pos keeps incrementing. This really only works reliably if it *is* always incrementing, though; if you have large lulls where there are no database writes, you won't see those changes. This is why I said that you were close, above :-) The other way to do it is to make sure there *are* writes; on the master, create a table that contains a timestamp, and have a process there keep updating the timestamp with the current time every, say, 30 seconds or a minute. On the slave, read that row and compare the current time to the value in it; if they're more than, say, twice the update period off, you know that updates aren't getting replicated. Cheers, -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [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 infile
sounds like --safe-mode has been turned on, check your my.cnf files - hcir On Jun 10, 2004, at 7:18 PM, Nik Belajcic wrote: I have a strange problem importing data from a text file. There are 1353 rows in the text file (generated by a Perl script) but only 1000 get imported into MySQL. I am clueless why would this be happening - it seems as if there was a cutoff point at 1000 rows which, of course, doesn't make any sense. Any thoughts much appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
Garth, good catch! - hcir mysql - hcir On May 24, 2004, at 1:05 PM, Garth Webb wrote: On Mon, 2004-05-24 at 11:32, John Nichel wrote: Rich Allen wrote: iH this should work test select * from xt; ++---+ | id | field | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 7 | | 4 | 8 | | 5 | 7 | | 6 | 0 | | 7 | 6 | | 8 | 7 | | 9 | 8 | ++---+ 9 rows in set (0.00 sec) test select count(distinct(field)) + (select count(*) from xt where field=0) - 1 from xt; + --+ | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | + --+ | 6 | + --+ 1 row in set (0.01 sec) note that i subtract one since i counted a 0 value in the distinct part ... - hcir That's what I needed. Thanks! Note that this solution will be off by one if there aren't any zeros in your data. Try this: select count(IF(field0,NULL,1)) + count(distinct IF(field0,field,NULL)) from test; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
iH this should work test select * from xt; ++---+ | id | field | ++---+ | 1 | 0 | | 2 | 0 | | 3 | 7 | | 4 | 8 | | 5 | 7 | | 6 | 0 | | 7 | 6 | | 8 | 7 | | 9 | 8 | ++---+ 9 rows in set (0.00 sec) test select count(distinct(field)) + (select count(*) from xt where field=0) - 1 from xt; +--+ | count(distinct(field)) + (select count(*) from xt where field=0) - 1 | +--+ |6 | +--+ 1 row in set (0.01 sec) note that i subtract one since i counted a 0 value in the distinct part ... - hcir mysql - hcir On May 24, 2004, at 9:36 AM, John Nichel wrote: Hi, I have a table which I want to select data from (obiviously). In this table, I have a field which is an integer, and defaults to 0. What I would like to do is count all rows in that table which not only equals 0 for the field, but has a distinct value which is greater than 0. id field 1 0 2 0 3 7 4 8 5 7 6 0 7 6 8 7 9 8 For the above example, my count should return 6. Three zero's count as 3, three seven's count as 1, two eight's count as 1, and one six counts as 1. I've tried... SELECT COUNT(*) FROM db.table WHERE ( field = 0 || ( field 0 DISTINCT field ) ) But it still returns the count of all the rows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table primary key and foreign keys
I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr primary key(bom_id) ** ** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich
RE: Alter table primary key and foreign keys
The error log shows nothing when the binary dies. I can't rebuild the child table - anything that touches the child table after the alter stops the binary. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 2:07 PM To: 'Rich Schramm '; '[EMAIL PROTECTED] ' Subject: RE: Alter table primary key and foreign keys I would first see if an upgrade to a later version of InnoDB tables is possible. What is being written to the error log? The ALTER TABLE statement subtly creates a new table, with new contraint names that the child table is unaware of, and drops the original table. Have you tried rebuilding the child table? -Original Message- From: Rich Schramm To: [EMAIL PROTECTED] Sent: 5/18/04 12:43 PM Subject: Alter table primary key and foreign keys I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr primary key(bom_id) ** ** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich -- 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: Problem running MySQL on MacOS X.3
ps should show you something like the following: Jupiter:~/desktop hcir$ ps uax | grep mysql root 291 0.0 0.118644 1072 ?? S 3May04 0:00.06 sh ./bin/safe_mysqld --user=mysql mysql 338 0.0 2.551720 19872 ?? S 3May04 10:17.86 /usr/local/mysql-standard-4.1.0-alpha-apple-darwin6.4-powerpc/bin/ mysqld hcir20399 0.0 0.018172344 std S+ 10:52AM 0:00.01 grep mysql if you dont see this i would check the error log after you try to start the server with something like: #/usr/local/mysql/bin/mysqld_safe -uuser - hcir On May 11, 2004, at 10:29 AM, Gabriel Ricard wrote: Did you configure MySQL and run the /usr/local/mysql/scripts/mysql_install_db to initialize your data directory? - Gabriel On May 10, 2004, at 5:21 PM, Tim Jarman wrote: I have an iBook G4 running MacOS X.3 on Darwin 7.0. I downloaded and ran the binary installer (mysql-standard-4.0.18.pkg and it appeared to work fine; I have /usr/local/mysql and so on as per the docs. I also installed MySQLStartupItem. However, I don't actually appear to have a functional installation. If I do: /usr/local/mysql/bin/mysql or even sudo /usr/local/mysql/bin/mysql I get: ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (61) although /tmp/mysql.sock does exist, which seems to indicate the server is running. But then again ps -a -x doesn't seem to show it, so maybe it isn't; if I do ps -A |fgrep mysql on my Linux box, where MySQL is running happily, I get a hit for mysqld_safe plus ten others for mysqld. I tried starting the server manually as suggested in the docs: sudo /Library/StartupItems/MySQL/MySQL start Starting MySQL database server but when I try running mysql I get error 2002 as before. I found mention of this problem on FAQTS and in the MySQL mailing lists archive, the latter of which suggested this email address. Any clues would be most welcome! Please cc any replies to me as I am not currently subscribed to any of the MySQL lists. Thanks in advance, Tim Jarman - hcir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
iH is mysql installed and running? - hcir On Apr 8, 2004, at 9:53 AM, Kirti S. Bajwa wrote: cpan install DBD::mysql Errors returned: Can't exec mysql_config: No such file or directory at Makefile.PL line 174 readline() on closed filehandle PIPE at Makefile.PL line 176 ... (there are 10 such errors) Then few other lines of output without error. Then I get the following (error) response: Checking if your kit is complete... looks good Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (number of such errors) Note (probabally harmless): No library found for -lmysqlclient Unsuccessful stat on filename containing newline at /usr/lib/perl5/5.8.0/ExtUtils/Liblist/Kid.pm line 97. ... (again quite a number of such errors) Note (probabally harmless): No library found for -lgz Writing Makefile for DBD::mysql Makefile:89: *** missing separator. Stop. /usr/bin/make --NOT OK Running make test Can't test without successful make Running make install make had returned bad status, install seems impossible cpan What are these errors? How should I fix them? Can I continue installing other software (Apache, qmail, etc.) Thanks. Kirti -Original Message- From: Rich Allen [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 07, 2004 6:02 PM To: Kirti S. Bajwa Cc: '[EMAIL PROTECTED]' Subject: Re: Perl Modelues you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Perl Modelues
you can install Perl modules with $ perl -MCPAN -e shell cpan install [module::name] - hcir mysql - hcir On Apr 7, 2004, at 10:57 AM, Kirti S. Bajwa wrote: Hello List: I have run into stone wall in figuring out installation of Perl DBI modules with MySQL. When I review the MySQL documentation, 2.7.1 Installing Perl on Unix, it mentions that the easiest way to install Perl DBI is to use CPAN. However when I go to the link provided in the documentation http://search.cpan.org, I can't figure out how to find the Perl module. Can someone show me the light. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.ini file for two instances of MySql - need help
Hello, I need to get two instances of Mysql running on the same machine. It's a windows 2000 server. The existing MySQL is 3.23 and is running in another directory. It appears to be used for LiveStats. I need to install version 4 and have them both running. What do I need to add to the my.ini file? Can anyone post a typical setup of this as an example? Thank you! BID _ Check out the new MSN 9 Dial-up fast reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-uspage=dialup/homeST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.ini file for two instances of MySql - need help
Thanks, Chris, but how do I refer to the sencond instance of MySQL, the version 4? That's what's not clear to me. I did read the section in the manual, but it doesn't explain how to refer to seperate instances. THanks! From: Chris L. White [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: 'Rich Brant' [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: my.ini file for two instances of MySql - need help Date: Mon, 12 Jan 2004 10:55:21 -0600 Here is an example of what I used: [mysqld] # set basedir to your installation path basedir=C:/mysql # set datadir to the location of your data directory datadir=C:/mysql/data # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 #socket=MySQL skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= table_cache=64 set-variable= sort_buffer=512K set-variable= net_buffer_length=8K set-variable= myisam_sort_buffer_size=8M server-id = 1 # Uncomment the following if you want to log updates log-bin # Uncomment the following rows if you move the MySQL distribution to another # location # basedir = c:/mysql/ # datadir = c:/SQLData # Uncomment the following if you are NOT using BDB tables skip-bdb # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables #innodb_data_file_path = ibdata1:400M #innodb_data_home_dir = c:\ibdata #innodb_log_group_home_dir = c:\iblogs #innodb_log_arch_dir = c:\iblogs #set-variable = innodb_mirrored_log_groups=1 #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=5M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_archive=0 #set-variable = innodb_buffer_pool_size=16M #set-variable = innodb_additional_mem_pool_size=2M #set-variable = innodb_file_io_threads=4 #set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL safe-updates [isamchk] set-variable= key_buffer=20M set-variable= sort_buffer=20M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=20M set-variable= sort_buffer=20M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-nt.exe user=xxx password= QueryInterval=5 Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED] -Original Message- From: Rich Brant [mailto:[EMAIL PROTECTED] Sent: Monday, January 12, 2004 10:48 AM To: [EMAIL PROTECTED] Subject: my.ini file for two instances of MySql - need help Hello, I need to get two instances of Mysql running on the same machine. It's a windows 2000 server. The existing MySQL is 3.23 and is running in another directory. It appears to be used for LiveStats. I need to install version 4 and have them both running. What do I need to add to the my.ini file? Can anyone post a typical setup of this as an example? Thank you! BID _ Check out the new MSN 9 Dial-up - fast reliable Internet access with prime features! http://join.msn.com/?pgmarket=en-uspage=dialup/homeST=1 -- 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] _ Let the new MSN Premium Internet Software make the most of your high-speed experience. http://join.msn.com/?pgmarket=en-uspage=byoa/premST=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.ini file for two instances of MySql - need help
Ok, Thanks Tobias! I'm all set. And I actually found the relevant info in the manual. Sorry for the bother, all. From: Tobias Asplund [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: 'Rich Brant' [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: RE: my.ini file for two instances of MySql - need help Date: Mon, 12 Jan 2004 18:28:50 +0100 (CET) Hello, What do I need to add to the my.ini file? Can anyone post a typical setup of this as an example? I install with mysqld-nt-max --install servicename servicename in those cases are MysQL40, MySQL41 and MySQL50 Those are the relevant rows: [mysqld] [mysql40] basedir = C:/mysql/4.0-tree/ datadir = C:/mysql/4.0-tree/data port= 3307 [mysql41] basedir = C:/mysql/4.1-tree datadir = C:/mysql/4.1-tree/data port= 3308 [mysql50] basedir = C:/mysql/5.0-tree datadir = C:/mysql/5.0-tree/data port= 3309 cheers, Tobias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Rethink your business approach for the new year with the helpful tips here. http://special.msn.com/bcentral/prep04.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)
Thanks for responding randy. so I checked my my.ini file and the address is set to 127.0.0.1. I checked the database error file and it was empty. I posted the my.ini file down below. Any other suggestions ? When I try to startmysql through the MSDOS prompt it says C:\Program Files\EasyPHP1-7\mysql\binmysql ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) My my.ini file # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] skip-innodb bind-address=127.0.0.1 port=3306 #socket=MySQL skip-locking set-variable = key_buffer=16K set-variable = max_allowed_packet=1M set-variable = thread_stack=64K set-variable = table_cache=4 set-variable = sort_buffer=64K set-variable = net_buffer_length=2K server-id = 1 # Uncomment the following if you want to log updates #log-bin # Uncomment the following rows if you move the MySQL distribution to another # location basedir = C:/Program Files/EasyPHP1-7/mysql/ datadir = C:/Program Files/EasyPHP1-7/mysql/data/ # Uncomment the following if you are NOT using BDB tables #skip-bdb # Uncomment the following if you are using Innobase tables #innodb_data_file_path = ibdata1:100M #innodb_data_home_dir = c:\ibdata #innodb_log_group_home_dir = c:\iblogs #innodb_log_arch_dir = c:\iblogs #set-variable = innodb_mirrored_log_groups=1 #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=5M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_archive=0 #set-variable = innodb_buffer_pool_size=16M #set-variable = innodb_additional_mem_pool_size=2M #set-variable = innodb_file_io_threads=4 #set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [myisamchk] set-variable = key_buffer=8M set-variable = sort_buffer=8M [mysqlhotcopy] interactive-timeout Randy Clamons [EMAIL PROTECTED] wrote: Check your my.ini (in c:\WINNT or c:\windows) for the bind-address. To force TCP connection the address should be set to 127.0.0.1. When connecting from PHP, connect to 127.0.0.1 instead of localhost. Specifying localhost as the address attempts to connect with named pipe. Also, check your [database].err file (located in the mysql/data folder) for any errors while starting the server. Randy At 11:32 AM 01/12/2004 -0800, you wrote: So I recently removed firepages PHP 4.0, and then installed the EasyPHP package 1.7(MYSQL, APACHE, PHP) . When it starts up everything runs except MYSQL the program is installed in the C:Program files- EASY PHP1.7- MYSQL. I recieve the following error, Can't connect to MySQL server on 'localhost' (10061) Do I need to change the port setting? or do I need to install in the C:/ folder? any advice is muchly appreciated. rich - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes Thanks for using Astro-Auction.com. Randy Clamons Systems Programming - Do you Yahoo!? Yahoo! Hotjobs: Enter the Signing Bonus Sweepstakes
finding equations intersections
I have a simulation of moving objects that interact with each other. If I store the object positions as coordinates, I think I will need at least one database call for every object to determine intersections. [for N objects there are N database calls] I'm wondering if I could do the same thing storing object positions as an equation. An equation where if I plug in any point of time t, I could determine where the object will be at that time. i.e., for y = t + 5 and t = 3 the y position is 8. Is there a way store equations with one variable in MySQL such that I could compare them for intersections by plugging in values for the variable? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL and evaluating expressions
Can SQL statements evaluate expressions? I want to store thousands of equations, and return a subset when I plug in a value. For instance, if I stored the following two equations: x = y + 5; x = 100 / y; I might want to query for all records whose x is less than 10 when y is equal to 3. I'd expect back the record associated with x = y + 5; My motivation is to reduce storage and calculations for a spatial program with moving objects. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL and evaluating expressions
Okay, after reading the MySQL manual I figured out how to get what I want. Can anyone advise me where to send such newbie questions in the future so that I would get a response? I realize my question was a SQL database question and not a MySQL-specific database question, but I figured the audience would be the same plus I saw some other posts regarding how to construct tables and queries. Can SQL statements evaluate expressions? I want to store thousands of equations, and return a subset when I plug in a value. For instance, if I stored the following two equations: x = y + 5; x = 100 / y; I might want to query for all records whose x is less than 10 when y is equal to 3. I'd expect back the record associated with x = y + 5; My motivation is to reduce storage and calculations for a spatial program with moving objects. -- 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: hostname
127.0.0.1 is always localhost, perhaps you could make this change in your scripts - hcir use MySQL is there a way to find out which host I am connected from? My CGI script checks privileges of users based on host names they came from. But their hosts some times are detected as IP. E.g. when the script asks: SHOW GRANTS FOR 'user'@'127.0.0.1' Mysql replies that there is no such grants, because it knows 'user'@'localhost' instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL access issue
this link from the mysql docs should help http://www.mysql.com/doc/en/Resetting_permissions.html - hcir Kind of an oddball question but I'll try to make it as clear as possible. We have a Solaris server, that we have root access to. It houses mysql db's and information. I was not the admin for the db's and frankly I'm not an MySQL buff to be honest. Our DB Admin is gone now, I need access to mysql db's, but I don't know what the username/password was for them. What are my options here to be able to not lose this info and get root access into the db's in mysql? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
