Re: recovery help needed
I actually get the feeling you are not connecting as root. Try mysql -uroot -p test instead of just mysql test Have a nice day, - Martijn On Wed, Aug 26, 2009 at 03:02, Joemysql@bluepolka.net wrote: OK, thanks, that got me in. But upon inspection, the user.host values do not look fouled up as I thought they were (it appears the bogus update may have aborted). But my access problem remains If I start with --skip-grant-tables, 'show databases' shows all DBs. But without that flag, I only see the 'information_schema' DB. Any suggestions as to where I look from here? On Tuesday 25 August 2009 @ 18:17, Walter Heck - OlinData.com wrote: Hey Joe, stop the server, start it with --skip-grant-tables, change the root entry in mysql.user to your liking, and then restart the server without --skip-grant-tables. viola! Walter On Wed, Aug 26, 2009 at 02:12, Joemysql@bluepolka.net wrote: We have an inaccessible MySQL v5.0.45 DB (w/Innodb) we really need some help regaining access to. While attempting to adjust/add remote user access, we accidentally did the following: use mysql; update user set host = 'SomeBogusIP' where user = 'root'; Now, we can't get into the DB to fix it: # mysql test ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'test' # mysql mysql ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql' We are not MySQL experts by any stretch, so any help is appreciated. Here are the files we evidently touched: # ls -ltr /var/lib/mysql/mysql/ -rw-r- 1 mysql mysql 5256 Aug 25 17:33 db.MYD -rw-r- 1 mysql mysql 844 Aug 25 17:35 user.MYD -rw-r- 1 mysql mysql 2048 Aug 25 17:50 user.MYI -rw-r- 1 mysql mysql 4096 Aug 25 17:50 db.MYI We do have a months-old copy of the 'mysql' db directory. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=li...@olindata.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: query question...
It sounds to me like you want to join the two tables? http://dev.mysql.com/doc/refman/5.1/en/join.html On Mon, Jun 15, 2009 at 03:56, brucebedoug...@earthlink.net wrote: hi. i've got a situation, where i'm trying to figure out how to select an item from tblA that may/maynot be in tblB. if the item is only in tblA, i can easilty get a list of the items select * from tblA if the item is in tblA but not linked to tblB, i can get the items as well select * from tblA where id not in (select id from tblB); but i have no idea how to combine the two selects.. i need to combine them, as the app can create tblA for a given item, and then later on create the data in tblB, with thblA.id = tblB.aid. thoughts/pointers on this would be appreciated. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: BULK DATA HANDLING 0.5TB
My first thought was: Archive Storage Engine; http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html But then I read fetch the result, so you're not only inserting data, but also doing some queries on it? Can you show the queries and your model? On Sat, Jun 13, 2009 at 07:40, Krishna Chandra Prajapatiprajapat...@gmail.com wrote: Hi guys, I'm working in a telecom company. I have table called deliverylog in which 30 million records gets inserted per/day. The table has grown to 0.5TB I have to keep 60days record in the table. So, 60days * 30 million = 1800 million records. The query is taking a lot of time to fetch the result. Please sugget me what storage engine must be used and how i can get the things done. Is there any other alternative. Any response is highly appreciated. Thanks, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT of records that have a matching record in a many to many table
If I understand your question correctly (and I'm really not sure about that), you're using a LEFT JOIN where you actually want to use a RIGHT JOIN. Or you need to rewrite your query and set the tables in another order. On Thu, Apr 30, 2009 at 23:01, Nigel Peck nigel.p...@miswebdesign.com wrote: Can someone please help me with this one? I'm trying to SELECT from a table only those records that have a record, matching a search term, in a table related by a many to many relationship. The many to many relationship is in a mapping/junction table. Here's an example of what I have so far: -=-=-=-=-=-=-=-=-=-=-=-=- SELECT `Notes`.`note_id` FROM `Notes` INNER JOIN `Notes__Districts` ON `Notes__Districts`.`note_id` = `Notes`.`note_id` LEFT JOIN `Districts` ON `Districts`.`district_id` = `Notes__Districts`.`district_id` WHERE `Districts`.`name` REGEXP 'bradford'; -=-=-=-=-=-=-=-=-=-=-=-=- Hopefully someone can see what I'm trying to do here and point me in the right direction :) Maybe I need to use a subquery? I've got a feeling I can do this without that but can't get my head round how to set up the JOINs in this case with having to use three tables in the one query, I'm only used to two tables at once. I couldn't find any tutorials that cover this. Thanks in advance, Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: configure parameters
You mean the list you get when you run `./configure --help`? Also check out http://dev.mysql.com/doc/refman/5.0/en/configure-options.html Or do you need more detail? Please be specific in what you need. On Mon, May 4, 2009 at 22:40, michel compu...@videotron.ca wrote: Is there a place on the net that has detailed documentation on the possible parameters? I am doing a quick google and can't find it. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Now() : SQL syntax error. But why?
Can you please give the full table structure and query? On Tue, Apr 28, 2009 at 23:18, Antonio PHP php.anto...@gmail.com wrote: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Created = NOW(), Updated = NOW()' at line 8 'Created' and 'Updated' are set to datetime (InnoDB). The same syntax works for some newly created tables... and gives no error. It's very strange. 'Now()' works for some tables, and it doesn't for some. (All set in phpmyadmin...) What could have caused this? Any similar experience? Please help~. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: UNIX_TIMESTAMP - Can anyone explain this behavior?
Hi Keith,
I'm not sure, but this might be DST that's in your way. Have you
looked into that?
Have a nice day,
- Martijn
On Mon, Apr 20, 2009 at 18:34, Keith Hughitt keith.hugh...@gmail.com wrote:
Hi all,
Does anyone know what is going on here:
//Query:
select UNIX_TIMESTAMP(TIMESTAMP('2003-01-01 00:00:00')) as first,
UNIX_TIMESTAMP(TIMESTAMP('2003-10-05 00:00:00')) as second,
UNIX_TIMESTAMP(TIMESTAMP('2004-01-01 00:00:00')) as third;
++++
| first | second | third |
++++
| 1041400800 | 106533 | 1072936800 |
++++
// Converting timestamps to UTC using linux date command (could also use
http://www.4webhelp.net/us/timestamp.php)
$ date -u -d @1072936800
Thu Jan 1 06:00:00 UTC 2004
$ date -u -d @1041400800
Wed Jan 1 06:00:00 UTC 2003
$ date -u -d @1064984400
Wed Oct 1 05:00:00 UTC 2003
MySQL seems to treat the local time as being UTC -6 hours in the first two
cases but as UTC -5 in other cases. The system local time appears to be
UTC-5 (EST):
// Attempting to determine MySQL's timezone offset:
select UNIX_TIMESTAMP(UTC_TIMESTAMP()) - UNIX_TIMESTAMP(now()) as offset:
++
| offset |
++
| 18000 |
++
which is consistent with the last result, but not the first two.
I have not yet tested more dates throughout the year to see when the change
occurs, and if there is a pattern, but I though I'd ask first to see if
anyone else has either
encountered this before, or knows what is going on?
I would like to be able to store some UTC datetimes in a system that uses
localtime, and then extract them as UTC timestamps again, which is why I'm
trying to figure
out the proper offset. On this particular system I also do not have the
ability to change the default timezone (e.g. to UTC/GMT), so I'm stuck with
using local dates.
Any suggestions? Any help would be greatly appreciated :)
Thanks!
-Keith
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to change where NULL values sort?
They'll normally sort at the top, unless you use ORDER BY DESC. Anyway, fixing that is easy: SELECT col1, col1 IS NULL AS isnull FROM tbl1 ORDER BY isnull DESC, col1 ASC That should give you the results ordered by col1, with the null-values at the top. - Martijn On Sat, Apr 18, 2009 at 19:54, David M. Karr davidmichaelk...@gmail.com wrote: I think normally NULL values will sort at the end, correct? I believe there's a way to make NULL values sort at the beginning, but I can't remember how to do it. I just searched a couple of MySQL resources, but I couldn't find it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to set db property so that table name queries are case-insensitive?
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names I think you'll want to set it to 1 On Wed, Mar 18, 2009 at 22:15, David M. Karr davidmichaelk...@gmail.com wrote: Ubuntu 8.10. I was experimenting with the Spring Petclinic sample application, configured with MySQL. I found that some of the tests were failing, apparently because of table name case-sensitivity issues. I was able to fix some of the code references, but after that I hit other, probably for the same issue. The tables were created with lower case names, but generated queries are using uppercase names. I can't easily control how it generates the SQL (using Hibernate). I think I'd prefer to have table names be case-sensitive, but I guess for now I have to figure out how to disable that, at least for this database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speeding up character set conversion
Hi Morten, You might want to read this post on MySQL Performance Blog that was posted a few days ago: http://www.mysqlperformanceblog.com/2009/03/17/converting-character-sets/ Have a nice day, - Martijn On Wed, Mar 18, 2009 at 11:50, Morten my.li...@mac.com wrote: Hi, I just tried this on a local copy of the table with ~500.000 rows: execute 'ALTER TABLE users MODIFY email VARCHAR(255) CHARACTER SET latin1 NOT NULL' The old character set was UTF8. We're doing this to make the index smaller. This took around 45 minutes to complete. In production, we have about 1.000.000 rows. While the production servers are dedicated DB servers in comparison to my MacBook, I'm still concerned that this is going to literally take hours. How can this be speeded up if at all? Dropping the index first and then recreate? Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mart...@crystal-labs.nl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
