#mysql
21 October 2007
Total 22 pages. You are browsing page 1/22.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last
--- Log opened Sun Oct 21 00:00:01 2007
--- Day changed Sun Oct 21 2007
00:00 <****> says run as root
00:00 <****> Moreover, not having any exceptions really bites you, handling each error path is going to be awkward, cleanup is a pain
00:00 <****> sivik: you have to be the root user to run that
00:00 <****> How the hell do I reset a primary key integer auto_increment in a table WITH DATA IN IT?
00:00 <****> You're likely to end up with more error-handling code than normal code
00:00 <****> ok
00:00 <****> Most of which is very difficult to test, hence will be full of bugs
00:00 <****> beebeee: why would you?
00:00 <****> same error chadmaynard
00:01 <****> sivik: pastebin
00:01 <****> chadmaynard: Because it is in development mode and I don't want the odd numbers but start over from 0, 1, 2, 3...
00:01 <****> well, it froze the window, let me get back in
00:01 <****> beebeee: you'll have to manually set that column then set the auto_increment point
00:02 <****> sivik: froze the window as in didn't return to the prompt?
00:02 <****> no, as it looped and froze
00:02 <****> chadmaynard: Huh?
00:02 <****> sivik: it may have worked then
00:02 <****> I have lots of data in the table. I want the primary key integer to reset itself and go from 0, 1, 2, 3...
00:02 <****> It is currently 20, 30, 51, 1245.
00:03 <****> do i need to create a password for mysql to get it to work
00:03 <****> still says access denied for user
00:03 <****> beebeee: Do you want to renumber all the rows in the table by primary key? What about foreign keys in other tables?
00:03 <****> sivik: mysql -uroot should work
00:04 <****> http://pastebin.com/m6f5c4d8c
00:04 <****> nope
00:04 <****> http://pastebin.com/m59e2ee73
00:04 <****> beebeee: use a variable and increment it in an update sequence
00:04 <****> beebeee: You don't want an auto_increment column to contain 0 values.
00:05 <****> access is denied no matter what i do
00:05 <****> sivik: has this install been used before?
00:05 <****> nope
00:05 <****> beebeee: Try to answer MarkR42's question. It's important before going forward.
00:05 <****> mysql -uroot <-- pastebin
00:05 <****> MarkR42: "Foreign keys" being "other table rows referring to those ids"?
00:05 <****> beebeee: Yes.
00:06 <****> This is development mode. None of that data is important.
00:06 <****> Only for this specific table.
00:06 <****> then delete it
00:06 <****> chadmaynard, http://pastebin.com/m119fbc6c
00:06 <****> sivik: you must have already set a root password. Do you know it?
00:06 <****> beebeee: What do you expect to do with the referencing tables?
00:06 <****> nvm, i'm no longer in the correct ssh
00:06 <****> If the data are small, just drop the PK column (with an ALTER TABLE) and recreate it, the auto_increment will generate new IDs.
00:06 <****> Xgc: Nothing. They are irrelevant.
00:06 <****> On the other hand, for a large table this will take a long time
00:06 <****> They will have their data reset soon.
00:07 <****> beebeee: Can those rows be deleted?
00:07 <****> ...
00:07 <****> There is no technical reason why I want to or need to do this. It is a matter of philosophy and having odd numbers feels wrong and I would not be able to sleep.
00:07 <****> While developing, I was removing rows and adding them etc.
00:07 <****> So I end up with "42, 67, 125" etc. instead of 1, 2, 3.
00:08 <****> And now I simply wanna reset this primary key integer auto_increment for each row.
00:08 <****> beebeee: In that case, it's best to leave the data as is. This will more likely uncover errors where you might depend on sequential key values.
00:08 <****> The table in question never exposes this id in the app, and it will never grow over 100 records.
00:09 <****> Xgc: How is it possible to ignore what I said and misunderstand it? No data relies on this table!!
00:09 <****> you can delete and re-add the table or do what i said above.
00:09 <****> beebeee: Sorry if I confused you. My comment has nothing to do with the referencing tables.
00:09 <****> Obviously I can delete and readd it...
00:09 <****> If I wanted to waste my time, I would do that...
00:10 <****> beebeee: It was simply a suggestion.
00:10 <****> it takes a lot of time to delete and re-add the column?
00:10 <****> i meant column not table
00:10 <****> ...
00:10 <****> What?
00:10 <****> beebeee: There are a couple of ways to do what you ask. But if you don't want to waste time, don't do it.
00:10 <****> chadmaynard, this is the error, sorry http://pastebin.com/m32bcdc8b
00:11 <****> You can ALTER the table twice, once to remove and again to re-add the auto_increment column. The downside is that if your data are large, this will take a LONG time
00:11 <****> sivik: then mysql isn't running or the socket is in a different place
00:11 <****> For example, if you have 10 million rows, it could take half an hour for each alter (this is a vague guess)
00:11 <****> MarkR42: he said no more than 100 rows
00:11 <****> i know its not running, it fails
00:12 <****> sivik: then don't try to connect.
00:12 <****> thats the whole reason of me asking the question
00:12 <****> If there are 100 rows, the ALTER will complete before you've had time to notice that you pressed enter
00:12 <****> chadmaynard, thats what you told me to do
00:12 <****> sivik: you said it rejected the password...
00:12 <****> well, i was wrong
00:12 <****> that was locally and not via the ssh machine
00:13 <****> that i'm trying to get this to work with
00:13 <****> then don't act like a smartass to me about asking the wrong question based on the wrong information you provided
00:13 <****> chadmaynard, i'm not being a smart ass, i just said it wasn't working and that i did it wrong
00:13 <****> so don't even act like that, i'm asking for the help, thank ful for it and now your calling me a smartass
00:14 <****> i don't understand why your being that way
00:14 <****> hello
00:14 <****> sivik: what is the current error when starting the server?
00:14 <****> starting MySQL database server mysqld [fail]
00:14 <****> thats all it says
00:14 <****> sivik: in the log or using the method I showed you
00:15 <****> i'm newbie. please tell me, why this query wrong:
00:15 <****> create table cat (id smallint auto_increment unsigned primary key, name tinyblob, desc blob, magic tinyblob);
00:15 <****> chadmaynard, http://pastebin.com/m2ca78117
00:15 <****> nnivanov: you have some reserved words and put unsigned in the wrong place.
00:15 <****> there isn't anything in any of the log files, their empty
00:15 <****> ok, that didn't take as near as long as I thought it would.
00:16 <****> nnivanov: column_name datatype unsigned
00:16 <****> !m nnivanov reserved words
00:16 <****> nnivanov: (Treatment of Reserved Words in MySQL) : http://dev.mysql.com/doc/mysql/en/reserved-words.html
00:16 <****> sivik: you HAVE to run this command as root, for the 8th time.
Total 22 pages. You are browsing page 1/22.
First :: Prev :: [1] [2] [3] [4] [5] [...] :: Next :: Last