Re: List archive?
On Tue, Dec 14, 2010 at 12:52, Patrice Olivier-Wilson b...@biz-comm.com wrote: On 12/14/10 12:51 PM, Patrice Olivier-Wilson wrote: I have a question that this list solved a year ago, and I can't remember what the solution was. Does this list have an archive? (Sorry if it is on one of the links to the list, but I don't have any on hand at the moment.) see it on the bottom of my post... thanks and sorry for noise It's also archived in many other places, such as GMANE[1] and MARC[2]. ^1: http://gmane.org/ ^2: http://marc.info/ -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How can i subscribe
On Tue, Dec 14, 2010 at 13:58, omar zorgui omarzor...@gmail.com wrote: Hello, How can i subscribe for this list http://lists.mysql.com/mysql?sub=1 -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [PHP] mySQL query assistance...
On Mon, Nov 29, 2010 at 14:35, Don Wieland d...@dwdataconcepts.com wrote: Hi all, Is there a list/form to get some help on compiling mySQL queries? I am executing them via PHP, but do not want to ask for help here if it is no the appropriate forum. Thanks ;-) Yes. For MySQL queries, write to the MySQL General list at my...@lists.mysql.com. For PHP-specific database questions (for any database backend, not strictly MySQL), such as problems in connecting to the database, questions on support for database platform/version, or even query processing, you should use php...@lists.php.net. For your convenience, both have been CC'd on this email. -- /Daniel P. Brown Dedicated Servers, Cloud and Cloud Hybrid Solutions, VPS, Hosting (866-) 725-4321 http://www.parasane.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql vs postgresql -- is this list accurate?
no, i don't want to start a flame war, i just want some feedback on a current list of mysql drawbacks WRT postgresql. in the context of a fully open-source, java based ECM product, there is a FAQ entry that summarizes why the developers would prefer their users to use postgresql as opposed to mysql: http://www.nuxeo.org/xwiki/bin/view/FAQ/WhyAvoidMySQL i'm not advocating one way or the other, i just want to make sure that list is accurate and i'm not enough of an expert to be able to judge the entire list. if anyone wants to tell me whether any of those entries are no longer relevant, or are overblown, or whatever, i'd appreciate it. again, i'm not taking sides, i just want to make sure the information is as accurate as possible. thanks. rday -- Robert P. J. Day Waterloo, Ontario, CANADA Top-notch, inexpensive online Linux/OSS/kernel courses http://crashcourse.ca Twitter: http://twitter.com/rpjday LinkedIn: http://ca.linkedin.com/in/rpjday -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
might need some help recovering tables from trashed DB
i asked about this once upon a time, and might need a little more help here. a friend's mysql hosting provider lost an entire DB, but has managed to recover and hand over the ibdata1 file (or at least some portion of it). when my friend popped into mysql, what he's seeing is that some of the tables appear to be back, but others generate a does not exist diagnostic. by way of trying to help last time, i literally copied the underlying mysql files onto my linux system, then fired up mysql to see what magically appeared, but that was before i even had the ibdata1 file so i wasn't surprised to get very little in the way of recovered data. now, though, with this ibdata1 file, i can try that again -- fire up a new linux box, and manually install the files under /var/lib/mysql. i'm guessing i'll see much of what he's seeing. i just want to verify that, if that's all i have access to and some of the tables still appear to be missing, there's not much i can do. or is there? rday -- Robert P. J. Day Waterloo, Ontario, CANADA Top-notch, inexpensive online Linux/OSS/kernel courses http://crashcourse.ca Twitter: http://twitter.com/rpjday LinkedIn: http://ca.linkedin.com/in/rpjday -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
multi crosstab
Hi Devs I have a tricky question and I want to know if its possible to accomplish it on mysql and how. The following query: SELECT fields.id AS field_id, data.user_id, fields.type, fields.name, data.value, entries.ip, entries.date, entries.user_agent FROM data, fields, entries, forms WHERE data.field_id = fields.id AND data.user_id = entries.id AND forms.id = fields.form_id AND forms.id = entries.form_id AND forms.id = 1 ORDER BY user_id, field_order Returns this: +--+-+--++---+---+-+--+ | field_id | user_id | type | name | value | ip| date| user_agent | +--+-+--++---+---+-+--+ |2 | 1 | text | RUT| 17881 | 127.0.0.1 | 2009-03-08 14:27:51 | Unit Check | |1 | 2 | text | Name | felipe| 127.0.0.2 | 2009-03-08 14:28:35 | Mozilla | |2 | 2 | text | RUT| 456465789 | 127.0.0.2 | 2009-03-08 14:28:35 | Mozilla | +--+-+--++---+---+-+--+ And I want to display this by field_id and user_id like this: user_idRUT Name 1 17881 2 456465789 felipe I know that if would be just one reference would be easy to do (by doing IF(field_id=X,value,'')), I've also found the solution by application side (by parsing columns first, rows later), but it comes to troubles when I add a new fieldID or when a fieldID for that userID its not found. Any idea? Thanks, Rod
Re: multi crosstab
I think I acompplish it! Merge that query into a view called report_1 and then: (1) SELECT user_id, MAX(IF(name='RUT',value,NULL)) AS 'Rut', MAX(IF(name='Name',value,NULL)) AS 'Name' FROM report_1 GROUP BY user_id; +-+---++ | user_id | Rut | Name | +-+---++ | 1 | 170332881 | NULL | | 2 | 456465789 | felipe | +-+---++ Then: INSERT INTO data(user_id,field_id,value) values (1, 1, 'Rodrigo'); And then (1): +-+---+-+ | user_id | Rut | Name| +-+---+-+ | 1 | 170332881 | Rodrigo | | 2 | 456465789 | felipe | +-+---+-+ :) 2009/4/5 Rodrigo Aliste P. rali...@gmail.com Hi Devs I have a tricky question and I want to know if its possible to accomplish it on mysql and how. The following query: SELECT fields.id AS field_id, data.user_id, fields.type, fields.name, data.value, entries.ip, entries.date, entries.user_agent FROM data, fields, entries, forms WHERE data.field_id = fields.id AND data.user_id = entries.id AND forms.id = fields.form_id AND forms.id = entries.form_id AND forms.id = 1 ORDER BY user_id, field_order Returns this: +--+-+--++---+---+-+--+ | field_id | user_id | type | name | value | ip| date| user_agent | +--+-+--++---+---+-+--+ |2 | 1 | text | RUT| 17881 | 127.0.0.1 | 2009-03-08 14:27:51 | Unit Check | |1 | 2 | text | Name | felipe| 127.0.0.2 | 2009-03-08 14:28:35 | Mozilla | |2 | 2 | text | RUT| 456465789 | 127.0.0.2 | 2009-03-08 14:28:35 | Mozilla | +--+-+--++---+---+-+--+ And I want to display this by field_id and user_id like this: user_idRUT Name 1 17881 2 456465789 felipe I know that if would be just one reference would be easy to do (by doing IF(field_id=X,value,'')), I've also found the solution by application side (by parsing columns first, rows later), but it comes to troubles when I add a new fieldID or when a fieldID for that userID its not found. Any idea? Thanks, Rod -- Rodrigo
Re: IN vs. OR on performance
2009/3/29 Oscar ro4...@gmail.com: Hi all- I want to know what the difference between IN and OR is under the hood. select * from dummy_table where id in (2, 3, 4, 5, 6, 7); select * from dummy_table where id=2 or id=3 or id=4 or id=5 or id=6 or id=7; I've have thought once the query is compiled, they are the same. What might cause a difference in performance is doing id 2 and id = 7. Test it on a large dataset and let us know :) -- Blog: http://pookey.co.uk/blog Follow me on twitter: http://twitter.com/ipchristian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Server Setup Question
On Wed, Nov 12, 2008 at 12:35 PM, Shain Miley [EMAIL PROTECTED] wrote: Hello all, I was wondering if anyone had any good insight into running the 32 bit and 64 bit versions of MySQL? We are going to be using a replication setup within my organization very shortly. We intend to a have at least one master (writable) DB and several (let's say 3 for this excersise ) read-only DB's. [snip!] Would I need to run the 64 bit version on all the servers or just the master, etc? Any help would be great. I would highly recommend running the 64-bit version on all systems if it's feasible from an infrastructure standpoint. One of my customers has a cluster that I manage where the RAM ranges from 16-24GB per machine, and I have 64-bit setups on each. They use replication as well, for the record. There may be some issues with read/write/seek times on a 32-bit machine as opposed to a 64-bit. The 32-bit may seem to lag, which can cause issues with replication under heavy loads. And, of course, filesizes and memory barriers do exist (and are being rather easily-reached now). However, if you're strictly asking about how it interfaces from one MySQL server to the next, it's no problem. MySQL couldn't care less if it's compiled for i586 or x86_64; that's only in how it relates to the OS on which it's installed, not how it interacts with sibling systems. -- /Daniel P. Brown http://www.parasane.net/ [EMAIL PROTECTED] || [EMAIL PROTECTED] Ask me about our current hosting/dedicated server deals! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help executing mysql stress test suite
Hi, For mysql stress test suite (mysql-stress-test.pl) to execute, it requires --stress-tests-file to be provided which contains the list of tests to be executed. As per the documentation the default name of the file is stress-test.txt and it will be located under ./mysql-test directory. But I could not find this file under the default directory. How to create this file? What is the format? Does this file come by default with test suite installation? or do I have to create this with the list of tests ? Can I have a sample copy of this file (stress-test.txt) ? I did try creating the file in the following format and tests stress-test.txt 1st alias alter_table analyse analyze ansi -- Note: Above tests are part of test suite stress test suite executes well with single thread. But it fails with multiple threads as these threads cannot be executed simultaneously. Regards, Durga.
Compiling mysql-5.0.51a - `./t/*.disabled'?
Hello. I get a rather strange error which I so far not been able to find the cause of: --- /usr/bin/install: cannot stat `./t/*.disabled': No such file or directory make[4]: [install-data-local] Error 1 (ignored) --- I thought maybe it could be beacuse I run Debian Testing? So far I have not been able to find the cause of this error and don't know where to look. : I configure the build using this command line: ./configure --prefix=/tmp/mysqltest50051a \ --with-charset=utf8 --with-extra-charsets=all \ --enable-thread-safe-client --with-big-tables \ --with-openssl --with-mysqld-user=mysqld Thanks in advance for any hints/pointers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED] locations
Greetings, I've got a retail operation with mysql 5.0.22 on linux pc's across the country, and i need some input on setting up a backup strategy, preferrably without purchasing a package. We're currently using MyISAM, with the databases being dumped to a filesystem on a separate drive, in case the main drive goes down. However we will need to implement some kind of transactional engine in the near future, and we'd prefer not to take down the database to take a backup. Any thoughts ? Thanks Pierre - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Select rows containing identical values in two columns
I have a table that has a column with the id of the person that created the row. In another column in the same row there is a column with the id of the person that modified that row. Is there a way to write a SELECT statement that will return all the rows where the value in the creation column equals the value in the modification column? I don't want to specify a specific id in either of the columns. TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
seeding a slave from a slave
Hi All, How do I create a mysql data dump from a slave to seed another slave? Using --master-data with mysqldump from my existing slave sets the master to the slave I was dumping, not the real master. Many Thanks, Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL upgrade from 5.0.32 to 5.0.42 broke replication
I upgraded my slave server a few weeks ago, and the slave failed, with an error similar to the one shown below. I rolled back my upgrade, and it started working again, so I forgot about it. Today, I upgraded the master (again, from 5.0.32 to 5.0.42) - and hte slave failed again. I thought upgrading the slave to match the master might help, but still it failed. Below is the error. The hostname did *not* change. 070612 13:35:09 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=/var/run/mysqld/mysqld-bin' to avoid this problem. 070612 13:35:09 InnoDB: Started; log sequence number 40 824537593 070612 13:35:09 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem. 070612 13:35:09 [ERROR] Failed to open the relay log './xian-relay-bin.000962' (relay_log_pos 284157529) 070612 13:35:09 [ERROR] Could not find target log during relay log initialization 070612 13:35:09 [ERROR] Failed to initialize the master info structure 070612 13:35:09 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.42-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.42 Any ideas/suggestions welcome, reseeding the slave will literally take days. -- Ian P. Christian ~ http://pookey.co.uk signature.asc Description: OpenPGP digital signature
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
Ian P. Christian wrote: I upgraded my slave server a few weeks ago, and the slave failed, with an error similar to the one shown below. I have figured out what happened here now - and I'm part of the way though fixing it. It turned out the defaults had changed somewhere, and rather then using /var/lib/mysql/hostname-bin, it was using /var/run/mysql/mysqld-bin (and the same change fro relay logs too). Now... I've changed the slave to use it's correct logs now - however, if I do the same on the master, I'll have the last 4 hours of logs in /var/run/mysql/mysqld-bin ignored. Somehow, I need to get the slave to catch up with the master's old logs in /var/lib/mysql/hostname-bin, and then continue from the brand new logs in /var/run/mysql/mysqld-bin This is an awful mess, and I'm not sure it's recoverable - perhaps it is. In theory, I should be able to find out where the slave was up to in the old logs, extract them manually and replay them on the slave, and then reset the slave to use the new logs - however i'm not sure how reliable that's going to be - or even how to go about doing it yet. Ideas anyone? -- Ian P. Christian ~ http://pookey.co.uk signature.asc Description: OpenPGP digital signature
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
Ofer Inbar wrote: Assuming your slave is not usable by client programs now anyway and you don't mind it being unusable for a while longer, you can restart the slaving from scratch: This is exactly what I'm trying to avoid doing, it means 2 days downtime whilst the data is re-inserted. I have actually managed to fix it now though. I checked the old binary log from the master, and it had no new data for the slave, so I simply issued a 'CHANGE MASTER ...' on the slave to tell it to use the new binary log file, with a position of 4 (the start) and off it when - back to being in sync. Why these defaults changed on a minor mysql release update is beyond me, however I suspect this is gentoo's fault, not MySQLs. -- Ian P. Christian ~ http://pookey.co.uk signature.asc Description: OpenPGP digital signature
Re: replication
richard wrote: as far as I can see, these commands select which db's to replicate on the slave that currently exist on the master server. What i am asking is, if I set a server up as a slave, can I have other databases on the slave that are not part of the replication system? Yes - I've been doing this for some time, works fine. -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a 'safe' way to move data?
I'm trying to move data between 2 tables. INSERT INTO new_table SELECT * FROM old_table LIMIT 5; DELETE FROM old_table LIMIT 5; This is the only process that deletes data from old_table, can I be *sure* that the limit in these 2 queries will address the same data set? (if I don't limit to small numbers in the LIMIT, I/O gets too high, so I'm moving data slowly in batches) Thanks, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
Brent Baisley wrote: No, you can't assure the same data will be addressed without at least including an order by. Even then you would need to make sure that the first X records in the order would not change. For instance, if you order by entered_date DESC, then the data set would change because any new records would get included in the LIMIT. Will it not always use the natural order of the table in selects/deletes, and therefore return results in the order in which they were inserted? -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a 'safe' way to move data?
Dan Nelson wrote: To be completely safe, you would want to use innodb tables, then select only the primary key of the 50,000 records you're interested in, using the FOR UPDATE keyword (to keep others from modifying those records while you're doing the move). Then INSERT INTO newtable SELECT * FROM oldtable WHERE primarykey in ( your 50,000 keys ), then DELETE FROM oldtable WHERE primarykey in ( your 50,000 keys ), then COMMIT, which will cause your insertions and deletions to be truly atomic. Ah of course - a far better idea. Thanks :) -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MD5()
Neil Tompkins wrote: I'm looking to use MD5() to encrypt credit card numbers. How do I unencrypt this when reading the value ? you can't. Google for MD5, or better still look at wikipedia, I'm sure they will have something -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MD5()
Neil Tompkins wrote: What do you recommend I use ? http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html That should help you. However... keep in mind that if your application is likely to be accessing this data all the time anyway, if someone compromises your database, chances are they will compromise your code, leaving to them being able to get your key, and then decrypt all your data anyway. I'm not saying encrypting it in the database is pointless, it's just far from enough to say your data is secure. A quick google on the subject returned this: http://forums.mysql.com/read.php?30,14020,14020 which honestly, I've not read - but you might want to :) -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MD5()
Neil Tompkins wrote: The problem I have is our mysql database version is 3.23 and we are not in a position to upgrade. Because you are unlikely to be selecting on this data directly, you could use functions of whatever language you're using to connect to the database... for example if you're using PHP... http://uk2.php.net/manual/en/ref.mcrypt.php I hope to god though your reason for not upgrading is because this is a shared host you're planning on storing credit card details on. Make sure you give the users a chance to opt out of you keeping that kind of data, and remember to never store the CVV number. -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
-- BUFFER POOL AND MEMORY -- Total memory allocated 611637398; in additional pool allocated 3526400 Buffer pool size 32768 Free buffers 0 Database pages 32750 Modified db pages 188 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 315389, created 2063, written 474318 489.21 reads/s, 0.59 creates/s, 2.05 writes/s Buffer pool hit rate 951 / 1000 -- ROW OPERATIONS -- 7 queries inside InnoDB, 1 queries in queue 2 read views open inside InnoDB Main thread process no. 23228, id 2367634320, state: sleeping Number of rows inserted 356882, updated 1963990, deleted 293832, read 875872021 2.05 inserts/s, 10.32 updates/s, 0.21 deletes/s, 48500.03 reads/s Thanks again, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
Filip Krejci wrote: Hi, I suppose this is really I/O problem. You're right, it looks like it was just an I/O problem - your suggestion was spot on. I've now managed to dump my master data, and can get my slave back online! Thanks a lot for your suggestion, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump --single-transaction causes database to become unusable
Recently my one and only slave went down, and stupidly I don't have a dump suitable for reseeding (is that's the right term...) the slave, so need to make a snapshot of the master database again. This time I'll make sure I keep this datafile for future restores should I need to - you live and learn. So... I'm doing a database dump: mysqldump --master-data --single-transaction database dump.sql This database I'm dumping has something like 17 million rows, all but 1 table (which uses FULLTEXT, and only has 3-4k rows) run innodb. There is only one table of any real size, and this table has all but about 100k of the total rows in. My understanding of this command is that the database should not be locked whilst this command is running. However, here's my problem... When the dump starts to read from large table, the database just grinds to a halt - my website running from the database just stops, and the dump (which I was watching progress with a privative `watch ls -la`) slows down a bit. Last time I had to do this (for the first 'seeding' of my slave), I eventually gave up trying to dump from the database whilst the site remained live, and took the site down for 15 minutes whilst the dump ran. As I'm sure you'll understand I'm not too keen on taking the website down again. Any suggestions as to why my database is stopping (could be I/O related maybe? it's on a good RAID setup though), and what I could do about it? Many Thanks, -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
Marcus Bointon wrote: Hi Marcus :) On 7 Mar 2007, at 08:44, Ian P. Christian wrote: --single-transaction doesn't _do_ the dump as a transaction, it simply wraps the dump in begin/commit statements so it's atomic when restoring. If the dump is to preserve relational integrity then it has to lock tables or disable access (or writes/deletes can happen during the dump). There are two alternatives: One is to use innoDB's commercial hotbackup utility (which I've not used, but it's apparently 'the way'). I was under the impression that with multi-versioning of InnoDB, that it wouldn't need to do a write lock? Sorry to quote this much from 'mysqldump --help' --master-data[=#] This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump - don't forget to read about --single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off. --single-transaction Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. Option automatically turns off --lock-tables. I'll accept my interpritation of the above could be very wrong however... The other is one of the reasons for using a slave - stop the slave, do the dump, restart the slave and it will catch up anything it missed. It helps if you can dedicate a slave for this. Yes...I'm aware of this one, but alas.. this was my only slave, and it managed to become out of sync somehow (something to do with a bug when using 'mysqladmin kill'). Now I know that things like this happen, I'll take weekly snapshots of the slave data, but like I said - you live and learn :) Whilst I'm here and talking about slaves... is it possible to have a slave to 2 different databases on 2 different hosts? -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump --single-transaction causes database to become unusable
Filip Krejci wrote: Hi, you are right, option --single-transaction does not accquire any lock on your innodb tables. Backup is fully on-line due to mvcc. You should look for another reason of this behavior. 1/ What says 'show full processlist' when backup is running It shows mostly inserts/updates into tables, these tables being tables other then the huge 16-17 million row one. These tables are used for session storage, and as such, if this block (which goes on as almost the first thing my site does), then no SELECT statements for site content are executed 2/ What says 'show engine innodb\G' when backup is running I've actually not checked - I shall do that and report shortly - it's nearly midday for me, and that means lots of traffic on the site, not a good time to be playing :) 2/ Is your db server stopped or very slow only? (you will see in 1/) I *think* it's slow to the point where it's unusable. Until the backup gets round to the huge table, inserts/updates/selects are all going on just fine,. 3/ If it's very slow a/ what is your mysqldump config in my.cnf [mysqldump] quick max_allowed_packet = 16M that's all there is... I'm not sure what hte 'quick' does, I'll go and check the manual on that, perhaps that's the issue. I'm guessing I might have more luck increasing the packet size? b/ what about remote backup (mysqldump --host x.x.x.x --master-data --single-transaction dump.sql) which cause separating writes on another box? I did do this under the assumption it could simply be an I/O problem - however the problem persists. It might be because the network connection between the two hosts is pretty fast Thanks Filip! -- Ian P. Christian ~ http://pookey.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange table problem
I dont think product_beta.test is a table...It seems to be a view.. what is the create statement for it.. show create table product_beta.test; ??? -Lakshmi Ruan wrote: Hi everybody, I have a very strange table problem - a table was created in one of our databases, but I can't seem to drop it. I tried with phpmyadmin and with mysql from command-line. If I try to view it I receive this error: View 'product_beta.test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them And when I try to repair it: | product_beta.test | repair | error| 'product_beta.test' is not BASE TABLE | I am logged in as root so I don't think it is a rights problem. Any ideas on how to get rid of it? Regards Ruan Fourie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collation (œ)
Is there a collation where 'œ' = 'oe' so that cœur is sorted between codirection and coffre? I am currently using latin1_swedish_ci and the 'œ' gets sorted to the end (cœur appears after czar). I am using MySQL 5.0.22-Debian_0ubuntu6.06.2. Thanks. -- Brian P. Giroux -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrays in stored procedures - pl. help
Chris, thank you for the response, but that was not my question. My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. On 7/17/06, Chris [EMAIL PROTECTED] wrote: L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance.
[5.0] Left Join Problem
I'm trying to upgrade from MySQL 4.1 to MySQL 5.0. A query that works in MySQL 4.1 does not work in MySQL 5.0 and I'm at a loss as to how to proceed. SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; The above query now generates this error: Unknown column 'tab_a.id in 'on clause'. Placing parentheses around the ON clause, LEFT JOIN db.tab_b ON (tab_b.id = tab_a.id) does not work. However, splitting the original query apart and grouping the Left Joins in one query and the regular joins in another query does NOT generates any errors: SELECT tab_a.item1, tab_b.item2, tab_c.item3, tab_d.item4 FROM db.tab_a LEFT JOIN db.tab_b ON tab_b.id = tab_a.id LEFT JOIN db.tab_c ON tab_c.id = tab_a.id LEFT JOIN db.tab_d ON tab_d.id = tab_a.id WHERE tab_a.id = 'value' LIMIT 1; - this is ok AND SELECT tab_a.item1, tab_e.item5, tab_f.item6 FROM db.tab_a, db.tab_e, db.tab_f WHERE tab_a.id = 'value' AND tab_a.id2 = tab_e.id2 AND tab_e.id3 = tab_f.id3 LIMIT 1; - this is ok Why doesn't the original query work in MySQL 5.0? What do I need to do to make it work? TIA Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5.0] Left Join Problem
On 2006-07-21 1:01 PM, Gerald L. Clark [EMAIL PROTECTED] wrote: It is a join precedence issue. Use INNER Join instead of a comma. Thanks Gerald. Paul DuBois' polite suggestion to read the manual helped. Upon re-reading the section about the change in precedence with the comma operator and the join, I realized there was a simple fix and that I had misinterpreted the section on the first read. The revised query works. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
arrays in stored procedures - pl. help
Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? I do not want to call the stored proc. multiple times from an external program. (avoid if possible) I do not want to write to a dump file, manage that, and use mysqlloader. I do not want incorporate split, join logic into the stored procedure. (avoid if possible) Thanks, listaction
Optimizing DISTINCT searches
Several of my DISTINCT searches are frequently showing up in the slow query log. These queries use multiple table joins. Using EXPLAIN shows that the queries are using the appropriate keys, as far as I know. Are DISTINCT searches using multiple joins slow? TIA. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Optimizing DISTINCT searches
On 2006-05-01 11:55 AM, Robert DiFalco [EMAIL PROTECTED] wrote: Well, normally a DISTINCT has to do a type of sort and is slower than non-DISTINCT queries. Each field of the result set is considered in the DISTINCT logic. Can you modify the query so that it does not require the DISTINCT? Can you post the query? Robert - Query: SELECT DISTINCT Project.Site_ID, Site, Status, Type FROM Project, Site WHERE Site.Site_ID = Project.Site_ID ORDER BY Site; Site is the site name, Status and Type contain additional information about the site, and Site_ID is the unique site id. The Project table contains among other things a list of sites where the projects are being done. The results of this query are supposed to be a non-duplicated list of sites that are associated with at least one project. As the number of projects and sites have increased, this query is now frequently in the slow query log. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing DISTINCT searches
On 2006-05-01 1:14 PM, Robert DiFalco [EMAIL PROTECTED] wrote: Would you need the DISTINCT if you change the query like so? SELECT Site.Site_ID, Site, Status, Type FROM Site JOIN Project ON Site.Site_ID = Project.Site_ID ORDER BY Site; You may also want to just try your initial query without the distinct to see if that is the issue. Also, do you have an index on the Site column? The issue with this query is that you are pretty much selecting everything from the Project table. Robert - Your query doesn't work - it finds ALL the rows in Project table and hence repeats the sites.. I do have an index on the Site table, it is the Site_ID. The Project.Site_ID is also indexed. Stephen P. Fracek, Jr. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I select from remote tables in mysql 5.0.18-standard-log?
Thanks all, this looks exactly like what i'm looking for. However, when I create the federated table, it says it was successful but creates the table as Myisam. CREATE TABLE `petestdb.backup_pp_line_code` ( `catalog_id` int(10) unsigned NOT NULL, `line_code` char(3) NOT NULL, `product_typ_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`catalog_id`,`product_typ_id`), KEY `line_code_Index_2` (`line_code`) ) ENGINE=federated DEFAULT CHARSET=latin1 connection='mysql://[EMAIL PROTECTED]:3306/database2/backup_pp_line_code'; Show create table gives me : backup_pp_line_code | CREATE TABLE `backup_pp_line_code` ( `catalog_id` int(10) unsigned NOT NULL, `line_code` char(3) NOT NULL, `product_typ_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`catalog_id`,`product_typ_id`), KEY `line_code_Index_2` (`line_code`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CONNECTION='mysql://[EMAIL PROTECTED]:3306/database2/backup_pp_line_code' | When I query the federated table, it says 0 rows although the target has over 12000 rows. Could the mysql-max distribution be a factor ? How do I verify if this is what i'm running ? Paul DuBois [EMAIL PROTECTED] wrote: At 14:38 -0700 4/27/06, P. Evans wrote: Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? You can use FEDERATED to access tables on other MySQL servers. http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com - How low will we go? Check out Yahoo! Messengers low PC-to-Phone call rates.
Can I select from remote tables in mysql 5.0.18-standard-log?
Hello Listers, Is it possible to run a query on one mysql server to another database on a different server ? eg creating an alias in database A on server A to table B on database B on server B ? Like a federated nickname on db2 udb or synonym on informix ? Thanks Pierre - Yahoo! Mail goes everywhere you do. Get it on your phone.
RE: Expiration date on users utilizing freeradius and mysql
I submitted this yesterday and was not sure if maybe it did not get out to folks. How would I put an expiration date on a mysql field so that it would match a radius entry? Also, is there a way that I can call up a web based screen and have all the information at my fingertips for inputting user data? Thanks Dwane -Original Message- From: Atkins, Dwane P [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 10:59 AM To: mysql@lists.mysql.com Subject: Expiration date on users utilizing freeradius and mysql I am using freeradius with MySql and what I would like to do is create in my radius table an user with attributes stating a start and stop date. I would like to be able to do a bulk entry (more than 1 at a time) or would love for this to be web based. Is this process out there? Thanks Dwane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expiration date on users utilizing freeradius and mysql
I am using freeradius with MySql and what I would like to do is create in my radius table an user with attributes stating a start and stop date. I would like to be able to do a bulk entry (more than 1 at a time) or would love for this to be web based. Is this process out there? Thanks Dwane
/var/log/mysql.000001 Not Found?
I just tried upgrading from MySQL 4.0 to 5.0 on my Slackware Linux system, but whenever I try to start the new service it immediately exits and the only error it logs is: /usr/libexec/mysqld: File '/var/log/mysql.01' not found (Errcode: 13) [ERROR] Could not use /var/log/mysql for loggins (error 13). Turning logging off for the whole duration of the MySQL server process. [ERROR] Aborting If I touch the file, the error is for mysql.02, then mysql.03. What would be causing this, and how should I go about fixing it? Any help would be much appreciated, thanks. Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Freeradius and MySql
Good morning. I am trying to install Free Radius with MySql, but I either have a login issue or a permissions issue. I have added Radius and [EMAIL PROTECTED] to database, Fedora local users and just about everywhere I can think possible. I have added permissions for radius radius local to databases mysql and database radius. However, when I try to log in to mysql using the command mysql -u radius -p radius, I get and error stating, Error 1045 (28000): Access Denied for user 'radius'@'localhost' (using:Password: YES). I am really frustrated since I have been working on this login and permission issue for some time. Thanks mailto:[EMAIL PROTECTED]
RE: Freeradius and MySql
* a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; mysql SELECT user, host from mysql.user where user ='radius'; ++---+ | user | host | ++---+ | radius | % | | radius | localhost | ++---+ 2 rows in set (0.00 sec) SHOW GRANTS FOR 'user'@'host'; mysql SHOW GRANTS FOR 'radius'@'localhost'; ++ | Grants for [EMAIL PROTECTED]| ++ | GRANT USAGE ON *.* TO 'radius'@'localhost' | | GRANT ALL PRIVILEGES ON `radius`.* TO 'radius'@'localhost' | ++ 2 rows in set (0.00 sec) As you can see, it looks like I have granted permissions to the user, [EMAIL PROTECTED] for db radius. I am not sure what to do next. Thanks Dwane From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 23, 2006 9:51 AM To: Atkins, Dwane P Cc: mysql@lists.mysql.com Subject: Re: Freeradius and MySql Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57 AM: Good morning. I am trying to install Free Radius with MySql, but I either have a login issue or a permissions issue. I have added Radius and [EMAIL PROTECTED] to database, Fedora local users and just about everywhere I can think possible. I have added permissions for radius radius local to databases mysql and database radius. However, when I try to log in to mysql using the command mysql -u radius -p radius, I get and error stating, Error 1045 (28000): Access Denied for user 'radius'@'localhost' (using:Password: YES). I am really frustrated since I have been working on this login and permission issue for some time. Thanks mailto:[EMAIL PROTECTED] Try this: a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; Then for each user-host combination listed above do one of these and compare the privileges listed to those you expected the accounts to have. SHOW GRANTS FOR 'user'@'host'; b) if the records exist but aren't being respected, issue a FLUSH PRIVILEGES command. I have noticed that (in contradiction to the documentation) that you sometimes need to manually refresh the privilege cache even after using a GRANT or REVOKE command. If those don't get you started, come back with whatever new information you learn and we can try something else. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Freeradius and MySql
I did do a FLUSH PRIVILEGES and this still resulted in the same error. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 23, 2006 10:31 AM To: Atkins, Dwane P Cc: mysql@lists.mysql.com Subject: RE: Freeradius and MySql You could try suggestion B) ;-) Shawn Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:25:24 AM: * a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; mysql SELECT user, host from mysql.user where user ='radius'; ++---+ | user | host | ++---+ | radius | % | | radius | localhost | ++---+ 2 rows in set (0.00 sec) SHOW GRANTS FOR 'user'@'host'; mysql SHOW GRANTS FOR 'radius'@'localhost'; ++ | Grants for [EMAIL PROTECTED]| ++ | GRANT USAGE ON *.* TO 'radius'@'localhost' | | GRANT ALL PRIVILEGES ON `radius`.* TO 'radius'@'localhost' | ++ 2 rows in set (0.00 sec) As you can see, it looks like I have granted permissions to the user, [EMAIL PROTECTED] for db radius. I am not sure what to do next. Thanks Dwane From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 23, 2006 9:51 AM To: Atkins, Dwane P Cc: mysql@lists.mysql.com Subject: Re: Freeradius and MySql Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57 AM: Good morning. I am trying to install Free Radius with MySql, but I either have a login issue or a permissions issue. I have added Radius and [EMAIL PROTECTED] to database, Fedora local users and just about everywhere I can think possible. I have added permissions for radius radius local to databases mysql and database radius. However, when I try to log in to mysql using the command mysql -u radius -p radius, I get and error stating, Error 1045 (28000): Access Denied for user 'radius'@'localhost' (using:Password: YES). I am really frustrated since I have been working on this login and permission issue for some time. Thanks mailto:[EMAIL PROTECTED] Try this: a) Verify that you have actually GRANTED permission for the account you are trying to authenticate with SELECT user, host from mysql.user where user ='radius'; Then for each user-host combination listed above do one of these and compare the privileges listed to those you expected the accounts to have. SHOW GRANTS FOR 'user'@'host'; b) if the records exist but aren't being respected, issue a FLUSH PRIVILEGES command. I have noticed that (in contradiction to the documentation) that you sometimes need to manually refresh the privilege cache even after using a GRANT or REVOKE command. If those don't get you started, come back with whatever new information you learn and we can try something else. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: Freeradius and MySql
I didn't encrypt and that was going to be my next questions. How do I
do that?
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 10:45 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: RE: Freeradius and MySql
OK, make sure you are using the correct password, too. Did you remember
to encrypt the password with PASSWORD() or OLD_PASSWORD() when you
create the account?
SELECT user, host, password
FROM mysql.user
WHERE user='radius';
make sure your password is hashed, if not we can help you fix that
pretty easily
UPDATE mysql.user
SET `password`=PASSWORD('plain-text-of-password')
WHERE user='radius';
FLUSH PRIVILEGES;
Then try again.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:30:41 AM:
I did do a FLUSH PRIVILEGES and this still resulted in the same error.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 10:31 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: RE: Freeradius and MySql
You could try suggestion B) ;-)
Shawn
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:25:24
AM:
* a) Verify that you have actually GRANTED permission for the
account you are trying to authenticate with
SELECT user, host from mysql.user where user ='radius';
mysql SELECT user, host from mysql.user where user ='radius';
++---+
| user | host |
++---+
| radius | % |
| radius | localhost |
++---+
2 rows in set (0.00 sec)
SHOW GRANTS FOR 'user'@'host';
mysql SHOW GRANTS FOR 'radius'@'localhost';
++
| Grants for [EMAIL PROTECTED]|
++
| GRANT USAGE ON *.* TO 'radius'@'localhost' |
| GRANT ALL PRIVILEGES ON `radius`.* TO 'radius'@'localhost' |
++
2 rows in set (0.00 sec)
As you can see, it looks like I have granted permissions to the
user,
[EMAIL PROTECTED] for db radius. I am not sure what to do next.
Thanks
Dwane
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 9:51 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: Re: Freeradius and MySql
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57
AM:
Good morning. I am trying to install Free Radius with MySql, but
I
either have a login issue or a permissions issue.
I have added Radius and [EMAIL PROTECTED] to database, Fedora local
users
and just about everywhere I can think possible. I have added
permissions for radius radius local to databases mysql and
database
radius. However, when I try to log in to mysql using the command
mysql
-u radius -p radius, I get and error stating, Error 1045 (28000):
Access Denied for user 'radius'@'localhost' (using:Password: YES).
I
am
really frustrated since I have been working on this login and
permission
issue for some time.
Thanks
mailto:[EMAIL PROTECTED]
Try this:
a) Verify that you have actually GRANTED permission for the account
you
are trying to authenticate with
SELECT user, host from mysql.user where user ='radius';
Then for each user-host combination listed above do one of these and
compare the privileges listed to those you expected the accounts to
have.
SHOW GRANTS FOR 'user'@'host';
b) if the records exist but aren't being respected, issue a FLUSH
PRIVILEGES command. I have noticed that (in contradiction to the
documentation) that you sometimes need to manually refresh the
privilege
cache even after using a GRANT or REVOKE command.
If those don't get you started, come back with whatever new
information
you learn and we can try something else.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Freeradius and MySql
Shawn,
Thanks. That has done the trick. It turns out that I had passwords
hashed for [EMAIL PROTECTED], but not [EMAIL PROTECTED] Resetting the password
and flushing the privilege has really helped. Guess what, you have done
in 20 minutes what myself and other could not do in a week.
Dwane
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 10:45 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: RE: Freeradius and MySql
OK, make sure you are using the correct password, too. Did you remember
to encrypt the password with PASSWORD() or OLD_PASSWORD() when you
create the account?
SELECT user, host, password
FROM mysql.user
WHERE user='radius';
make sure your password is hashed, if not we can help you fix that
pretty easily
UPDATE mysql.user
SET `password`=PASSWORD('plain-text-of-password')
WHERE user='radius';
FLUSH PRIVILEGES;
Then try again.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:30:41 AM:
I did do a FLUSH PRIVILEGES and this still resulted in the same error.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 10:31 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: RE: Freeradius and MySql
You could try suggestion B) ;-)
Shawn
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 11:25:24
AM:
* a) Verify that you have actually GRANTED permission for the
account you are trying to authenticate with
SELECT user, host from mysql.user where user ='radius';
mysql SELECT user, host from mysql.user where user ='radius';
++---+
| user | host |
++---+
| radius | % |
| radius | localhost |
++---+
2 rows in set (0.00 sec)
SHOW GRANTS FOR 'user'@'host';
mysql SHOW GRANTS FOR 'radius'@'localhost';
++
| Grants for [EMAIL PROTECTED]|
++
| GRANT USAGE ON *.* TO 'radius'@'localhost' |
| GRANT ALL PRIVILEGES ON `radius`.* TO 'radius'@'localhost' |
++
2 rows in set (0.00 sec)
As you can see, it looks like I have granted permissions to the
user,
[EMAIL PROTECTED] for db radius. I am not sure what to do next.
Thanks
Dwane
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 23, 2006 9:51 AM
To: Atkins, Dwane P
Cc: mysql@lists.mysql.com
Subject: Re: Freeradius and MySql
Atkins, Dwane P [EMAIL PROTECTED] wrote on 03/23/2006 10:38:57
AM:
Good morning. I am trying to install Free Radius with MySql, but
I
either have a login issue or a permissions issue.
I have added Radius and [EMAIL PROTECTED] to database, Fedora local
users
and just about everywhere I can think possible. I have added
permissions for radius radius local to databases mysql and
database
radius. However, when I try to log in to mysql using the command
mysql
-u radius -p radius, I get and error stating, Error 1045 (28000):
Access Denied for user 'radius'@'localhost' (using:Password: YES).
I
am
really frustrated since I have been working on this login and
permission
issue for some time.
Thanks
mailto:[EMAIL PROTECTED]
Try this:
a) Verify that you have actually GRANTED permission for the account
you
are trying to authenticate with
SELECT user, host from mysql.user where user ='radius';
Then for each user-host combination listed above do one of these and
compare the privileges listed to those you expected the accounts to
have.
SHOW GRANTS FOR 'user'@'host';
b) if the records exist but aren't being respected, issue a FLUSH
PRIVILEGES command. I have noticed that (in contradiction to the
documentation) that you sometimes need to manually refresh the
privilege
cache even after using a GRANT or REVOKE command.
If those don't get you started, come back with whatever new
information
you learn and we can try something else.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
FW: Any help with resetting the administrative password using 'my sqld_safe' w/ the --init-file option
-Original Message- From: Skarlatos, Matthew P. To: 'mysql@lists.mysql.com' Sent: 2/13/2006 8:37 AM Subject: Any help with resetting the administrative password using 'mysqld_safe' w/ the --init-file option Has anyone run into a problem with setting the root user password in mysql using mysqld_safe with the '--init-file' option that contains the new password for startup? What I'm seeing after I issue the kill command for the 'host.pid' file and then restarting 'mysqld_safe --init-file', is a short pause, and then an error message that mysqld is unable to start - i.e. 060213 13:28:35 mysqld ended My mysql version is 4.0.20 running on a Solaris 9 system. If there are any additional patches or workarounds that are required, I'd appreciate any advice or tips that you could pass along to me for those that have tried to create the root password in this way. http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html Thanks in advance, Matt Skarlatos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any help with resetting the administrative password using 'mysqld _safe' w/ the --init-file option
Has anyone run into a problem with setting the root user password in mysql using mysqld_safe with the '--init-file' option that contains the new password for startup? What I'm seeing after I issue the kill command for the 'host.pid' file and then restarting 'mysqld_safe --init-file', is a short pause, and then an error message that mysqld is unable to start - i.e. 060213 13:28:35 mysqld ended My mysql version is 4.0.20 running on a Solaris 9 system. If there are any additional patches or workarounds that are required, I'd appreciate any advice or tips that you could pass along to me for those that have tried to create the root password in this way. http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html Thanks in advance, Matt Skarlatos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error inserting text containing a ? character
This morning I began noticing some errors coming from my MySQL database that appear to be the result of a user inserting text which contains a question mark anywhere in it. The error is: Parameter '?' must be defined ... Is there a way to tell MySQL to just treat the ? as another character in the string, rather than as a parameter? Maybe have my application replace all occurrances of ? with something else that will represent a ? to the database before issuing the query? For reference, I'm using the MySQLConnector .NET for connecting my application to a MySQL 4.x database. Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting SQL_BIG_SELECTS
I've determined that I need to set SQL_BIG_SELECTS=1 for an application I have which connects to a MySQL 4.x database. However, I don't see how to apply it universally to that application's connection. Is it something I need to put in the connection string? Something I need to put in each query? Basically, is there a way to set it globally on the MySQL server so it will apply to all sessions/connections? Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Setting SQL_BIG_SELECTS
How to specify that option depends entirely on how you are forming your connection with the MySQL server. Which connection library are you using and how are you opening your connection? The application is an ASP .NET web app hosted on Mono on an Apache web server running Linux. My data access class uses the MySQLConnector .NET library (1.0.7) to issue SELECT statements to populate DataSets. The connection string is as follows: Uid=myUsername;Pwd=myPassword;Server=192.168.0.12;Database=myDatabase; The function which returns the results of the query boils down to the following: mySqlConnection = new MySqlConnection(stringDatabaseConnection); mySqlDataAdapter = new MySqlDataAdapter(stringSelect, mySqlConnection); dataSetSQL = new DataSet(); mySqlDataAdapter.Fill(dataSetSQL); return dataSetSQL; Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlimport debug options
Hello Listers, Can anyone explain what are valid values for the 'debug options' on a mysqlimport ? The manuals just say : --debug[=debug_options], -# [debug_options] Write a debugging log. The debug_options string often is 'd:t:o,file_name'. What is d: ? t: ? o (ok,thats relatively easy but just to be sure...) ? This is mysql Ver 14.12 Distrib 5.0.15, for sun-solaris2.9 (sparc) using readline 5.0 Thanks Pierre - Yahoo! Shopping Find Great Deals on Holiday Gifts at Yahoo! Shopping
Re: 1266 creating innodb tables
Much thanks, that did the trick. Pierre Jasper Bryant-Greene [EMAIL PROTECTED] wrote: P. Evans wrote: skip-innodb is commented out,thats why its not in the options I sent previously. The logs are showing something peculiar - InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 268435456 bytes! ls shows : drwxr-x--- 2 myznet myznet 2048 Oct 27 10:52 mysql -rw-rw 1 myznet myznet 5242880 Oct 31 00:32 ib_logfile1 -rw-rw 1 myznet myznet 20688404480 Oct 31 16:27 ibdata1 -rw-rw 1 myznet myznet 5242880 Oct 31 16:27 ib_logfile0 but in /etc/my.cnf, innodb_log_file_size = 256M What gives ? How can I get the logfiles in synch ? That's your problem. Back up and remove the old ib_logfile* files and restart MySQL to recreate them. Either you have changed the log file size in my.cnf or you've upgraded MySQL and the default has changed. Jasper - Yahoo! FareChase - Search multiple travel sites in one click.
Re: 1266 creating innodb tables
skip-innodb is commented out,thats why its not in the options I sent previously. The logs are showing something peculiar - InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes InnoDB: than specified in the .cnf file 0 268435456 bytes! ls shows : drwxr-x--- 2 myznet myznet 2048 Oct 27 10:52 mysql -rw-rw 1 myznet myznet 5242880 Oct 31 00:32 ib_logfile1 -rw-rw 1 myznet myznet 20688404480 Oct 31 16:27 ibdata1 -rw-rw 1 myznet myznet 5242880 Oct 31 16:27 ib_logfile0 but in /etc/my.cnf, innodb_log_file_size = 256M What gives ? How can I get the logfiles in synch ? Pierre Jasper Bryant-Greene [EMAIL PROTECTED] wrote: P. Evans wrote: Shawn, as far as I can tell, no. Here's all my innodb options set in my.cnf : Well, it's easy to check: grep skip-innodb my.cnf Is there anything interesting in the logs, maybe on MySQL startup? Jasper - Yahoo! FareChase - Search multiple travel sites in one click.
Re: 1266 creating innodb tables
Here's an example : mysql create table petest (col1 integer,col2 char(5)) engine=innodb; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql show warnings - ; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1266 | Using storage engine MyISAM for table 'petest' | +-+--++ 1 row in set (0.00 sec) Jasper Bryant-Greene [EMAIL PROTECTED] wrote: P. Evans wrote: Whenever I try to create a table using innodb, i get a 1266 warning that the table has been created with myisam instead. I receive the error both at the mysql command line and using the mysql query browser. Can you please show us the CREATE TABLE command that you are using? - Yahoo! FareChase - Search multiple travel sites in one click.
Re: 1266 creating innodb tables
Shawn, as far as I can tell, no. Here's all my innodb options set in my.cnf : innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 4G innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 Anywhere else I can check for that ? Pierre [EMAIL PROTECTED] wrote: Is your server configured with skip-innodb ? It's an option that turns off support to that engine forcing it to elect a different method of storage. Shawn Green Database Administrator Unimin Corporation - Spruce Pine P. Evans [EMAIL PROTECTED] wrote on 11/10/2005 10:10:44 AM: Here's an example : mysql create table petest (col1 integer,col2 char(5)) engine=innodb; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql show warnings - ; +-+--++ | Level | Code | Message| +-+--++ | Warning | 1266 | Using storage engine MyISAM for table 'petest' | +-+--++ 1 row in set (0.00 sec) Jasper Bryant-Greene [EMAIL PROTECTED] wrote: P. Evans wrote: Whenever I try to create a table using innodb, i get a 1266 warning that the table has been created with myisam instead. I receive the error both at the mysql command line and using the mysql query browser. Can you please show us the CREATE TABLE command that you are using? - Yahoo! FareChase - Search multiple travel sites in one click. - Yahoo! FareChase - Search multiple travel sites in one click.
1266 creating innodb tables
Whenever I try to create a table using innodb, i get a 1266 warning that the table has been created with myisam instead. I receive the error both at the mysql command line and using the mysql query browser. I've also tried alterring the table to change the engine , which says it completes successfully but still has the 1266 warning. eg alter table pp_related_product_type engine=innodb; Query OK, 0 rows affected, 1 warning (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1266 | Using storage engine MyISAM for table 'pp_related_product_type' | +-+--+-+ 1 row in set (0.00 sec) I'm using mysql Ver 14.12 Distrib 5.0.15, for sun-solaris2.9 (sparc) using readline 5.0. Has anyone come across this before ? Any suggestions ? Thanks Pierre - Yahoo! FareChase - Search multiple travel sites in one click.
InnoDB maintenance question?
I have an ibdata file that is growing and growing and growing Are there maintenance routines that I can run to keep this file in check? My database is performing close to half a million transactions a day and this file is now well over 75GB. Can I expect this file to continue to grow, until I run out of disk space. I am currently using MySQL 4.1 but will be moving to 5.x soon. Thanks, Lane
MySQLHotCopy Error
We're having a problem with MySQLHotCopy. It has worked flawlessly in the
past but now we're getting an error message similar to this:
Dumping database... DBD::mysql::db do failed: Can't find file:
'./file.frm' (errno: 24) at /usr/local/mysql/bin/mysqlhotcopy line 468.
Deleting previous 'old' hotcopy directory ('mydirectory')
Existing hotcopy directory renamed to '/mydirectory/db_name_old'
done.
The frm file it can't find varies with each attempt to use MySQLHotCopy.
The files are there. We've flushed, optimized, and repaired all the tables
in the database without any luck. The db appears to be working fine.
MySQLHotCopy works ok with other databases on this server.
MySQL 4.0.23
Server: Mac OSX Server 10.3.8 dual 1.33 Ghz PPC G4 with 2 GB SDRAM
DB has 274 tables with 1.6 million records. DB size is 400 MB.
Any insights would be greatly appreciated.
TIA
Stephen P. Fracek, Jr.
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Show table status
Hello, Does anyone know where (c-api functions perhaps) SHOW TABLE STATUS gets its info from? Specifically, the new columns added in 4.1.2 and 4.1.3, are they the result of underlying c-function changes, new functions, or something else. Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
datetime/timestamps/4.1.12
Hello, I just upgraded to 4.1.12 from 4.0.22 and my timestamps changed formats to %Y-%m-%d %H:%i:%s So I added the following lime to my my.cnf file datetime_format=%Y%m%d%H%i%s Which is the format I prefer, I restart the server and my time stamps still appear as %Y-%m-%d %H:%i:%s The 'show variables' command now confirms that the format is what I defined in the my.cnf but the output of the query does not change... What am I missing? Thanks, Michael
RE: datetime/timestamps/4.1.12
Hello, Nevermind - duh -datetime is not timestamp (oneday I fullfill my promise to myself and not work on Sundays when my 'duh' level is a bit higher). Sofrom what I gather, the backward compatibility comes in the form of adding a +0 (string to int). This is most inconvenient and annoying. Any plans on rectifying this, or has anyone found a workaround, or are we left to go SIOH (hint OH stands for our hat) :-} Later... Michael -Original Message- From: DePhillips, Michael P Sent: Sun 7/17/2005 11:05 AM To: mysql@lists.mysql.com Cc: Subject: datetime/timestamps/4.1.12 Hello, I just upgraded to 4.1.12 from 4.0.22 and my timestamps changed formats to %Y-%m-%d %H:%i:%s So I added the following lime to my my.cnf file datetime_format=%Y%m%d%H%i%s Which is the format I prefer, I restart the server and my time stamps still appear as %Y-%m-%d %H:%i:%s The 'show variables' command now confirms that the format is what I defined in the my.cnf but the output of the query does not change... What am I missing? Thanks, Michael
radius account management using sql
Hello, We have radius server and using mysql. I would like to determine who are the users who have not login to our dialup server for over six months and then disable the account In the example below, I'm just using 10 days mysql SELECT distinct username,framedipaddress,acctstarttime,acctstoptime from radacct where DATEDIFF(CURDATE(),acctstoptime) 10 order by username; ++-+-+-+ | username | framedipaddress | acctstarttime | acctstoptime| ++-+-+-+ | testuser | 191.168.2.37 | 2005-04-11 14:56:40 | 2005-04-11 15:02:45 | | testuser | 191.168.2.47 | 2005-04-24 15:23:18 | 2005-04-24 15:35:58 | | testuser | 191.168.2.42 | 2005-04-19 18:38:51 | 2005-04-19 19:06:11 | | testuser | 191.168.2.51 | 2005-04-14 16:57:14 | 2005-04-14 18:16:20 | | testuser | 191.168.2.41 | 2005-04-25 16:22:30 | 2005-04-25 16:32:44 | The problem here is that testuser may have logged in the day before the command was executed. So my question is how to get the users last login to the server and if it's more than 6 months disable the account. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dateTime vrs. Timestamp
Hello, Am I gaining anything by using a timestamp instead of using dateTime and calling now(), for example, increased performance, better indexing, etc. I guess another way to ask this is does the MySQL internals handle a timestamp more efficiently than a dateTime. Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Error 1005:150
Michael, Thank you for your reply. Here is a bit more info. I changed the default table type to innodn in the my.ini file before creating the database, so all tables are innodb. I tried the create statements with and without explicit index clauses with all permutations - same result each time. I agree that something is wrong. Did you try running the ddl you suggested below? If so, did it work for you? I downloaded the latest release from thr ANL mirror which says v 4.1.2 in the file name. When I run MySQL, the system says it is 4.0.22. Is the engine version different than the release version? This is a secondaary issue however. Steve -- Original message -- Something is wrong, but it's hard to say what. It seems unlikely you entered exactly those commands and got an error only on the last ALTER TABLE. First, you need InnoDB tables to support foreign keys, but you don't specify the table engine in your CREATE statements. The default is MyISAM, unless you've changed it. But that's not it. If they were MyISAM tables, neither ALTER would work, but if they're all InnoDB, then all should work. Is it possible that just table address is MyISAM? In order to create a foreign key, you must have an index on the columns on each side of the relationship. That is, you need person_id and address_id to be indexed in both tables. Prior to 4.1.2, you had to do that by hand, but in 4.1.2 and later it's automatic. Again, all or nothing, so not likely relevant here. Some other things to note (which are unrelated to the error): There is no need to put an index on a column which has already been indexed as the primary key. It's a waste of space that adds overhead to inserts. You are relying on MySQL to create indexes for you in table person_address, but I don't think it will make the best choices in this case. You need an index on each column, but you most likely also need the combination of person_id and address_id to be unique. In other words, if you let mysql create indexes for you to satisfy the foreign key needs, you get separate single-column indexes, but you need a combined column unique constraint which renders one of the single column indexes redundant. How about: CREATE TABLE person ( person_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint person_pk PRIMARY KEY (person_id) ) ENGINE=InnoDB; CREATE TABLE address ( address_id INT UNSIGNED NOT NULL AUTO_INCREMENT, constraint address_pk PRIMARY KEY (address_id) ) ENGINE=InnoDB; CREATE TABLE person_address ( person_id INT UNSIGNED NOT NULL, address_id INT UNSIGNED NOT NULL, CONSTRAINT person_address_pk PRIMARY KEY (person_id, address_id), INDEX (address_id), CONSTRAINT person_person_address_FK1 FOREIGN KEY (person_id) REFERENCES person (person_id); CONSTRAINT address_person_address_FK1 FOREIGN KEY (address_id) REFERENCES address (address_id); ) ENGINE=InnoDB; Michael [EMAIL PROTECTED] wrote: I am unable to define a foreign key with the following three tables. I am unable to find the error having searched the documentation and tried several variations. Note that I created the first two tables with and without the index clause in the table ddl with no difference in outcome. The three tables and the first foreign key, person_person_address_FK1, create properly. The second foreign key, address_person_address_FK1, causes the error. Please help. create table person ( person_id int unsigned not null auto_increment, constraint person_pk primary key (person_id), index(person_id)); create table address ( address_id int unsigned not null auto_increment, constraint address_pk primary key (address_id), index(address_id)); create table person_address ( person_id int unsigned not null, address_id int unsigned not null); -- This statement works. alter table person_address add constraint person_person_address_FK1 foreign key (person_id) references person (person_id); -- This statement fails. alter table person_address add constraint address_person_address_FK1 foreign key (address_id) references address (address_id); Replies may be sent to [EMAIL PROTECTED] Thank you! Steve
Foreign Key Error 1005:150
I am unable to define a foreign key with the following three tables. I am unable to find the error having searched the documentation and tried several variations. Note that I created the first two tables with and without the index clause in the table ddl with no difference in outcome. The three tables and the first foreign key, person_person_address_FK1, create properly. The second foreign key, address_person_address_FK1, causes the error. Please help. create table person ( person_id int unsigned not null auto_increment, constraint person_pk primary key (person_id), index(person_id)); create table address ( address_id int unsigned not null auto_increment, constraint address_pk primary key (address_id), index(address_id)); create table person_address ( person_id int unsigned not null, address_id int unsigned not null); -- This statement works. alter table person_address add constraint person_person_address_FK1 foreign key (person_id) references person (person_id); -- This statement fails. alter table person_address add constraint address_person_address_FK1 foreign key (address_id) references address (address_id); Replies may be sent to [EMAIL PROTECTED] Thank you! Steve
Re: mysqld no longer starts after update 4.0 - 4.1
Hello Gleb, Please read the notes about MySQL on Linux: http://dev.mysql.com/doc/mysql/en/Linux.html I don't find anything specific to my problem there, sorry! My Linux installation seems to be missing something that the bdb part of MySQL 4.1 needs and which was not needed by v4.0 and below. As I don't use the bdb engine in MySQL I found some hint yesterday in some posting which explained how to disable the bdb functions (put the command skip-bdb into the [mysqld] section of my.conf). This shuts down the error message and MySQL 4.1.5 now runs on my machine. Do you use the official binary distribution? No, I use a Debian package. Actually I don't know if it is based on the official binaries or if the package maintainer compiled a special version. Usually Debian packages install perfectly but MySQL 4.1 is in the experimental stage. I also ran into more problems but I could solve all those manually. [ERROR] bdb: unable to initialize mutex: Function not implemented [ERROR] bdb: process-private: unable to initialize environment lock: Function not implemented I still wonder which part in my rather new Linux (kernel 2.4.18, libc6) is missing to support these bdb functions. At least I can work with GROUP_CONCAT now... Best regards, Stefan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems making a mysql.so file
Greetings all, I tried the PHP list with this one and can't seem to get an answer so I thought I'd try here. I upgraded from MySQL 3.23 to 4.0. I placed all the new mysql files in /usr/local/mysql/bin, /usr/local/mysql/include and /usr/local/mysql/lib. I compiled PHP5 and it is still using the 3.23 client API. So I removed all the old mysql files from /usr/bin, /usr/include and /usr/lib and tried again. No luck. Am I missing something here? Thanks!! Jough -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Removing Entries From a MySQL Table
Hello; DELETE FROM table_name WHERE 1 ; This command will remove all rows from the table. For more information, see: http://dev.mysql.com/doc/mysql/en/DELETE.html Make sure you have a backup if you think you may need the data in the future. -Tom -Original Message- From: Michael Mason [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 20, 2004 2:47 PM To: 'MySQL Mailing List' Subject: Removing Entries From a MySQL Table I've been looking through the manual and searching the web for the command and syntax used to do the above. I basically just want to clear a table I use for logon entries. Can anyone help with this please.? Michael Mason Business Support Services ArrasR People Tel: 01706 342310 Mobile: 07793 782287 Fax: 01706 642754 Member of the Recruitment Employment Confederation (00052055) The views expressed in this mail are entirely those of the sender, and do not necessarily represent the views or position of Arras Services Ltd. The information contained in this communication is confidential and may be legally privileged. It is intended solely for the use of the individual or entity to whom it is addressed and others authorised to receive it. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in relation to the contents of this information is strictly prohibited and may be unlawful. Neither the sender nor the represented institution is liable for the correct and complete transmission of the contents of this e-mail, or for its timely receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: NULL problem
Try SELECT * FROM table ORDER BY col LIMIT 23 ; If the col type is numeric, trying to match the string NULL might be the problem. And, if it is numeric, the NULL values will be listed first in 'ORDER BY col'. This doesn't really solve the problem of not being able to select 'where col is null', but should at least be of some use. Hope that helps, -Tom -Original Message- From: Deepak Vishwanathan [mailto:[EMAIL PROTECTED] Sent: Monday, July 19, 2004 11:14 AM To: [EMAIL PROTECTED] Subject: NULL problem Hi, I have a table with a column that has the Unique key constraint on it. In the table definition that column has a default NULL specification too. So, when I ran the query select * from table where col is NULL; I get only 1 row returned, when I have 23 such rows with NULL values. This might be because of the Unique Key constraint. Is there a query that will return all those 23 rows. Thanks, Deepak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade from 3.23 to 4.0 - mysql.sock error
Greetings all, I recently upgraded from mysql 3.23 to 4.0 on a Fedora box. I moved the old installation to a directory called old_mysql in my home directory. I can start the new installation using mysqld_safe . Now, when I try to do something like: /new/install/mysqladmin -u root -p version it says it can't connect because there is no /tmp/mysql.sock. And that is the case. The mysql.sock file is in /var/lib/mysql/mysql.sock When I do something like: old_mysql/mysqladmin -u root -p version it can connect and, in fact, I can connect to the new mysql server by using old_mysql/mysql -u root -p Should I just go on using the old clients? Why is there no /tmp/mysql.sock? How can I make mysql create one? Help! and Thanks!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 3.23 to 4.0 - mysql.sock error
Okay, so what I did to solve this was: ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock That seems like a bit of a hack though. The my.cnf file has this line: socket=/var/lib/mysql/mysql.sock Anyone know why it's looking in /etc ? On Jul 14, 2004, at 4:53 PM, Jough P wrote: Greetings all, I recently upgraded from mysql 3.23 to 4.0 on a Fedora box. I moved the old installation to a directory called old_mysql in my home directory. I can start the new installation using mysqld_safe . Now, when I try to do something like: /new/install/mysqladmin -u root -p version it says it can't connect because there is no /tmp/mysql.sock. And that is the case. The mysql.sock file is in /var/lib/mysql/mysql.sock When I do something like: old_mysql/mysqladmin -u root -p version it can connect and, in fact, I can connect to the new mysql server by using old_mysql/mysql -u root -p Should I just go on using the old clients? Why is there no /tmp/mysql.sock? How can I make mysql create one? Help! and Thanks!!! -- 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]
Access denied for user: 'root@localhost' (Using password: NO)
Hi - I am new to MySQL - I just recently re-installed MySQL by rpm and when ever I try to issue a command from the shell prompt, such as mysqladmin or mysqlshow, I would get the error of 'Access denied for user: '[EMAIL PROTECTED]' mailto:'[EMAIL PROTECTED]' (Using password: NO)'. Could someone please tell me what I need to set or did not set. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access denied for user: 'root@localhost' (Using password: NO)
shouldn't I be able to issue commands at the shell it goes and does it thing and give back output? or does this mean I have a passwd already set and it's secured? -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 8:57 AM To: Nguyen, Long P (Mission Systems) Cc: [EMAIL PROTECTED] Subject: Re: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) Re-installing does not get rid of your old passwords. You need to use the -p option. Nguyen, Long P (Mission Systems) wrote: Hi - I am new to MySQL - I just recently re-installed MySQL by rpm and when ever I try to issue a command from the shell prompt, such as mysqladmin or mysqlshow, I would get the error of 'Access denied for user: '[EMAIL PROTECTED]' mailto:'[EMAIL PROTECTED]' (Using password: NO)'. Could someone please tell me what I need to set or did not set. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bugzilla not working now after MySQL re-installed
Your thoughts on this would be appreciated.
MySQL was re-installed and now Bugzilla is not working now I am getting this error
when I bring up - http://localhost/bugzilla/ http://localhost/bugzilla/
*
Software error:
Bugzilla is currently broken. Please try again later. If the problem persists, please
contact [EMAIL PROTECTED] The error you should quote is: Can't connect to local MySQL
server through socket '/tmp/mysql.sock' (2) at globals.pl line 140.
For help, please send mail to the webmaster ([EMAIL PROTECTED] mailto:[EMAIL
PROTECTED] ), giving this error message and the time and date of the error.
*
The re-installation of MySQL did something... below is the output of the
./checksetup.pl
[EMAIL PROTECTED] bugzilla-2.16.5]# ./checksetup.pl
Checking perl modules ...
Checking for AppConfig (v1.52) ok: found v1.56
Checking for CGI::Carp (any) ok: found v1.27
Checking forData::Dumper (any) ok: found v2.121
Checking for Date::Parse (any) ok: found v2.27
Checking for DBI (v1.13) ok: found v1.42
Checking for DBD::mysql (v1.2209) ok: found v2.9003
Checking for File::Spec (v0.82) ok: found v0.87
Checking for File::Temp (any) ok: found v0.14
Checking forTemplate (v2.07) ok: found v2.13
Checking for Text::Wrap (v2001.0131) ok: found v2001.09291
Checking for CGI::Carp (any) ok: found v1.27
The following Perl modules are optional:
Checking for GD (v1.19) ok: found v1.20
Checking for Chart::Base (v0.99) ok: found v2.3
Checking for XML::Parser (any) ok: found v2.34
Checking user setup ...
Precompiling templates ...
DBI connect(';localhost;3306','bugs',...) failed: Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2) at ./checksetup.pl line 1189
[Thu Jul 1 10:00:17 2004] checksetup.pl: DBI connect(';localhost;3306','bugs',...)
failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at
./checksetup.pl line 1189
h1Software error:/h1
preCan't connect to the mysql database. Is the database installed and
up and running? Do you have the correct username and password selected in
localconfig? AHA
/pre
p
For help, please send mail to this site's webmaster, giving this error message
and the time and date of the error.
/p
[Thu Jul 1 10:00:17 2004] checksetup.pl: Can't connect to the mysql database. Is the
database installed and
[Thu Jul 1 10:00:17 2004] checksetup.pl: up and running? Do you have the correct
username and password selected in
[Thu Jul 1 10:00:17 2004] checksetup.pl: localconfig? AHA
[Thu Jul 1 10:00:17 2004] checksetup.pl:
[EMAIL PROTECTED] bugzilla-2.16.5]#
RE: bugzilla not working now after MySQL re-installed
MySQL is running.. [EMAIL PROTECTED] /]# ps -ef | grep mysql root 1338 1 0 10:51 pts/200:00:00 /bin/sh /usr/bin/mysqld_safe --d mysql 1362 1338 0 10:51 pts/200:00:00 /usr/sbin/mysqld --basedir=/ --d root 1374 1124 0 10:51 pts/200:00:00 grep mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 10:38 AM To: Nguyen, Long P (Mission Systems); [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: Your thoughts on this would be appreciated. MySQL was re-installed ,,, preCan't connect to the mysql database. Is the database installed and up and running? Do you have the correct username and password selected in localconfig? What else do you need besides the error message above? It appears that MySQL isn't running. Have you *tried* accessing it from the command-line client, using the username/password set in your localconfig? That would be the first logical step... -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: bugzilla not working now after MySQL re-installed
I am able to log into mysql as this: [EMAIL PROTECTED] root]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) Thanks for your help... -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:34 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: MySQL is running.. OK, that's /half/ the question :-) Have you *tried* accessing it from the command-line client, using the username/password set in your localconfig? Does the username and password in localconfig get you into the DB using the MySQL client? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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: bugzilla not working now after MySQL re-installed
yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:52 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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: bugzilla not working now after MySQL re-installed
I am able to logon with user 'bugs' and passwd 'marvin' as below:
[EMAIL PROTECTED] root]# mysql -u bugs -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.20-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql
I noticed before the MySQL installed - when I do a 'which mysql' I get output of
'/usr/local/mysql/bin/mysql' - but now I get /usr/bin/mysql.
But why does bugzilla checksetup.pl failed to connect to the database in error below..
Checking user setup ...
Precompiling templates ...
DBI connect(';localhost;3306','bugs',...) failed: Can't connect to local MySQL server
through socket '/tmp/mysql.sock' (2) at ./checksetup.pl line 1189
[Thu Jul 1 12:32:14 2004] checksetup.pl: DBI connect(';localhost;3306','bugs',...)
failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) at
./checksetup.pl line 1189
h1Software error:/h1
preCan't connect to the mysql database. Is the database installed and
up and running? Do you have the correct username and password selected in
localconfig? AHA
/pre
p
For help, please send mail to this site's webmaster, giving this error message
and the time and date of the error.
/p
[Thu Jul 1 12:32:14 2004] checksetup.pl: Can't connect to the mysql database. Is the
database installed and
[Thu Jul 1 12:32:14 2004] checksetup.pl: up and running? Do you have the correct
username and password selected in
[Thu Jul 1 12:32:14 2004] checksetup.pl: localconfig? AHA
[Thu Jul 1 12:32:14 2004] checksetup.pl:
[EMAIL PROTECTED] bugzilla-2.16.5]#
-Original Message-
From: Hassan Schroeder [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 11:52 AM
To: [EMAIL PROTECTED]
Subject: Re: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) wrote:
I tried this and get a 0 affect below..
mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON
bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin';
Query OK, 0 rows affected (0.00 sec)
OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config
file; can you log in from the MySQL client using them instead of the
root user/pwd?
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
--
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: bugzilla not working now after MySQL re-installed
I re-installed it this time by rpm because when I tried to recompile it with '--with-mqsql' this DRES and some other errors for Keystone tools keeps telling me that MySQL needs to be recompiled with '--with-mqsql'. This time with the re-installed - I used rpm. Same version but by rpm. I noticed that my databases are not there anymore when I do a 'show databases'. I just need to get Bugzilla to run the checksetup.pl script and it will go and create what it needs. but the unable to logon error is preventing it. There should not be any differ between rpm and the compile install is there? -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 12:20 PM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Uh, OK. You said MySQL was re-installed -- why? Was it the same version, or an upgrade? If the latter, there's a difference in the authentication between 3.x and 4.x that requires a client upgrade (or that you explicitly use the old auth). If it was the same version re-installed, is it possible some file system permissions were changed? And does anything show up in the MySQL logs when bugzilla runs (or tries to)? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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: bugzilla not working now after MySQL re-installed
The 'mysql.sock' file is at /var/lib/mysql/mysql.sock with a size of 0 Does this look right? [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# ls -l mysql.sock srwxrwxrwx1 mysqlmysql 0 Jul 1 11:41 mysql.sock -Original Message- From: Yiannis Mavroukakis [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 12:23 PM To: [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed The original error might be slightly more cryptic than it intends. It is saying that it cannot connect to your db through /tmp/mysql.sock. Check if your mySQL installation has installed the socket file in another place. If it has, a quick and dirty fix is to soft link the socket file wherever it is to /tmp/mysql.sock i.e. ln -s /usr/local/mysql/mysql.sock /tmp/mysql.sock Change /usr/local/mysql/mysql.sock to your file. In some distributions it can be found in /var/lib/mysql/mysql.sock. Yiannis. -Original Message- From: Nguyen, Long P (Mission Systems) [mailto:[EMAIL PROTECTED] Sent: 01 July 2004 17:07 To: Hassan Schroeder; [EMAIL PROTECTED] Subject: RE: bugzilla not working now after MySQL re-installed yes.. below: [EMAIL PROTECTED] root]# mysql -u bugs -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.20-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Thursday, July 01, 2004 11:52 AM To: [EMAIL PROTECTED] Subject: Re: bugzilla not working now after MySQL re-installed Nguyen, Long P (Mission Systems) wrote: I tried this and get a 0 affect below.. mysql GRANT SELECT,INSERT,UPDATE,DELETE,INDEX, ALTER,CREATE,DROP,REFERENCES ON bugs.* TO [EMAIL PROTECTED] IDENTIFIED BY 'marvin'; Query OK, 0 rows affected (0.00 sec) OK, I assume 'bugs' and 'marvin' are the values in bugzilla's config file; can you log in from the MySQL client using them instead of the root user/pwd? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- 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] This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. Note:__ This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Jaguar Freight Services and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. -- 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]
Replication - Too many master threads -BUG?
Hi - perhaps this is a bug - but I figure I'd try here first in case I'm doing something wrong. Master is 4.0.20 - slave connect and replicate fine, HOWEVER, for each binlog dump a new thread gets created and the old one is NOT released. Within a couple of days my master sever is flooded and allowing no more connections. Status of all threads (most recent and old are Has sent all binlog to slave; waiting for binlog to be updated) Is it me or should I report a bug? Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication died
Hello - I just upgraded my master to 4.0.20 from 3.23.54 (ran the mysql_fix_privileges script) My slaves are all 4.0.[16,17,18] All seems well EXCEPT the Slave_IO_Running will NOT start. All my slave a stuck at the point prior to the upgrade. Error logs reveal nothing out of the ordinary. I'm baffeled, anybody have some insight??? Thanks Michael
RE: Replication died
Never mind...I've fixed it. Thanks Michael -Original Message- From: DePhillips, Michael P Sent: Wednesday, June 09, 2004 4:17 PM To: '[EMAIL PROTECTED]' Subject: Replication died Hello - I just upgraded my master to 4.0.20 from 3.23.54 (ran the mysql_fix_privileges script) My slaves are all 4.0.[16,17,18] All seems well EXCEPT the Slave_IO_Running will NOT start. All my slave a stuck at the point prior to the upgrade. Error logs reveal nothing out of the ordinary. I'm baffeled, anybody have some insight??? Thanks Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE - Order By Problem
If surname is a field, then use it without the single quotes ('),
otherwise it is treated as a literal string and 0 is the correct result:
select locate(' ',surname,1) from advisers
andy thomas wrote:
Yes, this is the approach I was thinking of using but:
select locate(' ','surname',1) from advisers
just returns 0 for all records, whether or not they contain the ' ' space
substring.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Codes for U.S. Counties
David Blomstrom wrote: I just wondered if anyone on this list has worked with counties in databases and is aware of a pre-existing code system that's in fairly wide use. It would be nice to make a database that's compatible with other databases, if they share a common code for counties. I don't know, but you might ask the folks at: http://www.naco.org/ The National Association of Counties (NACo) =Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT table1 SET col1 = table2.col1
Why when there is a figurae, figurae2 table does the follow give an error? INSERT INTO figurae2 SET id = figurae.id; The error is: ERROR 1109 at line 35: Unknown table 'figurae' in field list Line 35 is the Insert line shown. There are no following lines in the file. I cut and pasted figurae from mysqlcc just to make sure I have the right (Latin) spelling. The following placed on the line above in the same file above this INSERT returns a set of ids. SELECT id FROM figurae; I am using mysql (the command line tool). Can anyone tell me what I am doing wrong? -Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT table1 SET col1 = table2.col1
Victoria Reznichenko wrote: P. Hill [EMAIL PROTECTED] wrote: Why when there is a figurae, figurae2 table does the follow give an error? INSERT INTO figurae2 SET id = figurae.id; The error is: ERROR 1109 at line 35: Unknown table 'figurae' in field list Look at INSERT .. SELECT statement: http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html I'm not asking for an alternative way to do it; I'm asking what is wrong with what I did? I did rework it to an alternative syntax, but I wanted to use the SET syntax so I could list all my old fields right next to my new fields, so that I didn't have to skip up and down 40 lines to see how I have them matched up. So does INSERT ... SET ... work? How? -Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT table1 SET col1 = table2.col1
Garth Webb wrote: I'm not asking for an alternative way to do it; I'm asking what is wrong with what I did? It looks like you want to copy over several or all values from a second table. The INSERT .. SELECT syntax isn't an alternate way, its the only way. Okay, thanks for the clarification. I didn't register what they were talking about with the INSERT ... SET form, I guess I was assuming othertable.othercolumn was an explicit value as per that sentence, but combined with the next sentence I see they mean constant values and certainly no references to another table. Darn, I ws hoping there was some form that allowed the juxtaposition of old and new closer to each other. thanks, -Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication in 5.0.0-alpha
Hi, collegues On Sat, Apr 17, 2004 at 05:19:16PM +0300, Oleg P. Philon wrote: Have I use precompiled binaries from ftp.mysql.com? Ya, I have to Is there working setups with 5.0.0 and replication? Again yes, in mein setup at last Auf Wiederlesenophil aka - -- Oleg P. Philon http://gomelug.agava.ru/articles Linux Lab, Gomel, Belarus mailto:ophil(at)gomelug.agava.ru http://anticommunist.narod.ru mailto:anticommunist(at)narod.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql syntax error
I'm using amavisd-new -20030616p9, RH 3.0 ES and mysql 3.23.58-1 trying to do sql lookups for user prefs. I've done this before and have compared my sql statements and can't figure out the problem. When i start amavisd-new with the debug switch, here's what i get: # /usr/local/sbin/amavisd debug Error in config file /etc/amavisd.conf: syntax error at /etc/amavisd.conf line 829, near ' ORDER BY users.priority DESC '; Here are the lines from my /etc/amavisd.conf file: $sql_select_policy = 'SELECT *,users.vuid FROM users,policy_names'. ' WHERE (users.policy=policy_names.id) AND (users.username IN (%k))'. ' ORDER BY users.priority DESC '; Please help! Thanks in advance, Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication in 5.0.0-alpha
Hi, collugues Have a problem with replication in MySQL-5.0.0-alpha Configured server with server-id=2, slave with server-id=4 Copied DBs, configured the slave, restarted the servers. Under MySQL v4.0.18 from debian distro replication works perfectly. Kept all configs in place, overwrite all binary with self build binaries from MySQL v5.0.0.-alpha Slave logs: Version: '5.0.0-alpha-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 040417 16:32:53 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'netlab-bin .01' at position 4 040417 16:32:53 Error reading packet from server: Misconfigured master - server id was not set (server_errno=1236 ) 040417 16:32:53 Got fatal error 1236: 'Misconfigured master - server id was not set' from master when reading dat a from binary log 040417 16:32:53 Slave I/O thread exiting, read up to log 'netlab-bin.01', position 4 Master status shows: mysql show binlog events; +---+-+-+---+-++ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---+-+-+---+-++ | netlab-bin.01 | 4 | Format_desc | 1 | 95 | Server ver: 5.0.0-alpha-log, Binlog ver: 4 | | netlab-bin.01 | 95 | Query | 1 | 181 | use `km`; delete from zakaz where id=2146 | | netlab-bin.01 | 181 | Query | 1 | 267 | use `km`; delete from zakaz where id=3378 | | netlab-bin.01 | 267 | Query | 1 | 353 | use `km`; delete from zakaz where id=2155 | +---+-+-+---+-++ 4 rows in set (0.00 sec) See, all the time server-id=1, while in v4.0.18 and in config it happen to =2 ! Have I use precompiled binaries from ftp.mysql.com? Is there working setups with 5.0.0 and replication? Auf Wiederlesenophil aka - -- Oleg P. Philon http://gomelug.agava.ru/articles Linux Lab, Gomel, Belarus mailto:ophil(at)gomelug.agava.ru http://anticommunist.narod.ru mailto:anticommunist(at)narod.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables + SUM + GROUP BY = strange behavior
Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++---+-+-++- -+ | CallDate | @a := SUM(Charge) | @b := SUM(Cost) | @a - @b | @a | @b | ++---+-+-++- -+ . | 2004-03-01 | 621.059 | 249.310 | 30.882 | 39.512 | 8.63 | | 2004-02-29 |54.620 | 17.660 | 30.882 | 39.512 | 8.63 | | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | | 2004-02-27 | 622.282 | 248.920 | 30.882 | 39.512 | 8.63 | | 2004-02-26 | 607.274 | 277.100 | 30.882 | 39.512 | 8.63 | | 2004-02-25 | 709.698 | 308.580 | 30.882 | 39.512 | 8.63 | | 2004-02-24 | 783.210 | 298.560 | 30.882 | 39.512 | 8.63 | | 2004-02-23 | 799.764 | 252.890 | 30.882 | 39.512 | 8.63 | .
User variables + SUM + GROUP BY = strange behavior
Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Killing overactive mysql selects
Start another Mysql shell session; then do show processlist; identify the thread ID of the offending process, then do kill ID. Not sure how graceful this method is, though. I use it to kill overlooked hanging connections from time to time. Cheers, Vadim. Scott Haneda wrote: Every now and then I am not thking and I am working on the mysql shell and I send it a select name from table; Just so happens there are a few hundred K of records. What I meant to do is LIMIT 10; Anyway, how do I get mysql to stop, the best I can do is control-C, but that leaves mysql altogether and I have to log back in again. Also, sometimes I am able to use the tab key to auto-complete field and table names, this is really handy, sometimes it works and sometimes not, how do I get this all the time? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT DISTINCT.. ORDER BY.. DESC - bug??
After a bit of digging, found out that this is indeed a bug: http://bugs.mysql.com/bug.php?id=1274 Strangely enough, it is listed as CLOSED. Now - this is hard to explain, but it looks like MySQL developers have no answer or interest in dealing with this problem. The fact is - adding an index improves SELECT .. ORDER BY .. ASC, but slows down SELECT.. ORDER BY .. DESC at least by a factor of 10. If this is not a bug, what is it? Vadim P. wrote: Hi all, Sorry to be so persistent, but I am bringing this up again since noone from the MySQL development team commented on my previous post, and the issue seems very serious, to the point I may start looking to switching away from MySQL, so - please, please, shed some light on this issue!!! The problem is that the performance of SELECT DISTINCT... query seems to depend on the order the results are sorted, DESC being more than 10x slower than ASC (14.77 sec vs. 1.06 sec). == Here is a more detailed description: The table has over 700,000 records. MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) === Thanks in advance! Vadim. mysql query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT DISTINCT.. ORDER BY.. DESC - bug??
Hi all, Sorry to be so persistent, but I am bringing this up again since noone from the MySQL development team commented on my previous post, and the issue seems very serious, to the point I may start looking to switching away from MySQL, so - please, please, shed some light on this issue!!! The problem is that the performance of SELECT DISTINCT... query seems to depend on the order the results are sorted, DESC being more than 10x slower than ASC (14.77 sec vs. 1.06 sec). == Here is a more detailed description: The table has over 700,000 records. MySQL 4.0.18 running under OpenBSD 3.4 Intel/PIII 900MHz/2GB RAM mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle DESC; +--+ | billingCycle | +--+ | 2004-04-01 | | 2004-03-01 | | 2004-02-01 | | 2004-01-01 | | 2003-12-01 | | 2003-11-01 | | 2003-10-01 | | 2003-09-01 | | 2003-08-01 | | 2003-07-01 | | 2003-06-01 | | 2003-05-01 | | 2003-04-01 | | 2003-01-01 | +--+ 14 rows in set (14.77 sec) mysql SELECT distinct billingCycle FROM PhoneCalls ORDER BY billingCycle; +--+ | billingCycle | +--+ | 2003-01-01 | | 2003-04-01 | | 2003-05-01 | | 2003-06-01 | | 2003-07-01 | | 2003-08-01 | | 2003-09-01 | | 2003-10-01 | | 2003-11-01 | | 2003-12-01 | | 2004-01-01 | | 2004-02-01 | | 2004-03-01 | | 2004-04-01 | +--+ 14 rows in set (1.06 sec) === Thanks in advance! Vadim. mysql query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
