Legor, sure, let me get my crystal ball

lol, great thx for your help tibyke
http://forums.mysql.com/read.php?10,161567,162707#msg-162707
here is my post on the forum

Legor, what version/os/box?

version is 5.0.41-Dotdeb_1.dotdeb.2-log
os is Debian 3.4.3-13sarge1
box ?

hardware

double Dual Core AMD Opteron(tm) Processor 270 stepping 02

hp?

8 GB RAM
what is hp ?

a brand

Hewlett Packard

yes the server is brand new
thx giddion

lol

i don't know the brand exactly

they make good printers

is there a way to now that from the os ?

isnt it on the PC case?

the server is in a datacenter

who runs the dc?

Legor, sig11 is mostly a hardware-related issue

well, how to know which part of the hardware is guilty ?

why doesn't GIS support 3D

thanks tibyke! all i had to do was add it in one place and walla! woot!

its not 100%, but its worth tracking down
Legor, ^
phrame, cool

how to track down ?

memtest, hardware stress test, etcetc

the server admin has told me the hardware has been succesfully tested :/

hang the admin

lol

from the server
with barbed wire
sorry, rough morning

barbed ? (sorry i'm not a native english)

metal wire with sharp bits on it

oh yeah i see

nasty stuff, google barbed wire images
lol

if you google barbed wire youll just get all the pammela anderson movie stuff
dont ask why i know that

hello all

welcome to hell

hehe ty

Legor, then check mysql settings, logs, query logs, etc to try to figure out what makes it die

i have some results containing a created_at date and a user_id among other things. i would like to group those results by user_id BUT per month range per user_id… so i would get grouped rows per user_id for june and july for example. though i have no idea how to delimit the group by on the
created_at date range….

hello
I have a table called order_tasks

smeevil, check date and time functions in the fine mysql manual at mysql.com/doc
month(whatever);

i did , though could not find it … will check again with your hint

in which I do save tasks for my order… each order can have many tasks that are ordered with a field order…(1,2,3,4)

Hi. Does the "Temporary tables are replicated" statement from http://dev.mysql.com/doc/refman/5.0/en/replication-features.html includes temporary tables used when sorting rows in a select ?

smeevil,

If I delete a task with order 3, that the tasks for this order would be 1,2,4

what should i check in mysql settings ?

whats the simplest query to reorder all the tasks for an order?
so it would be 1,2,3 instead of 1,2,4

Lion29, order by

hi.. can i return the a id of the affected rows of this: sql("UPDATE `auctions` SET `finished`='1' WHERE `stop`'".time()."' && `finished`='0'");

guess not

OMG IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

tibyke… yeah,, yoiu mean I should select all the rows first ORDER BY table.order… than do a loop with a $i++ and uopdate id SET order=$i ?
is there a simpler solution?

Giddion55, what?

just managed to setup my first working relational table

Lion29, you want an order of 1,2,4,3 (eg) instead of 1,2,3,4,, right?
or i misunderstand sg

rocking thanks got it by : AND year(created_at)=2007 GROUP BY month(created_at),user_id

somone say grats, plzzz

no… after a deleted row the actuall order values are 1,2,4 instead of 1,2,3,4
so In this case I would like to be reordered again
1,2,3

smeevil, neat!

basically I think it's a bad idea to require such consistency at the database level

Lion29, why would you reorder that? thats totally stupid, forget it. get a coke instead.
Subdino's got the point

if applicable, you should number the lines when reading them before using the value

ah…maybe for later handling it would be good to have the correct values in there

problems I see are if you want to insert a new task 2 between what was previously 1 and 2

for example… for now when I add another task I put in the order value: num of rows +1;
if the order is not ok, than I should read the max(order)… and get that value

and another problem is if you do a partial select, you cannot get the priority number of the first row easily (unless "partial" meant using "limit" statement)

nah, always inserting on the end
and ther wil be not more than 10 tasks per order
its not a big deal.. I just wanted to know If I can do all this just in one query or do I need to make a selection first and do a loop after
to update with new values

is it possible to replay only partially a binlog ? is the system date an availabe criterion ?
(gah, shitty connection)
is it possible to replay only partially a binlog ? is the system date an availabe criterion ?

Subdino, I don't think so
if you're doing statement level replication you could probably hack something up to do it

not sure what I will use. I only know I must use transactionnal system (already using innodb tables) and I must backup mysql and files, keeping them sync'ed as much as possible

Hi, i'm having trouble with this tiny tiny easy sql query, and it is really nagging me, plz help me
http://pastebin.parentnode.org/19330

ie, when restoring backups of files and mysql I must make sure the restored mysql is not anny newer than the restored files, and should be as close as possible of file time
gah

complains about line 9

is ntree and rtree the same thing?

finpingvin, date is not numeric, quote it
just common sense

it is
it is a timestamp from php
it complains about line 9

whats the error?

error in syntax
it is really really nagging me, i must be blind not seeing whats wrong in that simple query

finpingvin insert into blah values ('a','b','c');

finpingvin, try quoteing the numeric values

oh and date and from are reserved words
and comment too, probably

but this insert syntax has worked perfect for me many times before. if i remove "from" it works

that would be not syntax error
its a valid insert syntax

finpingvin quote the column names

will try to rename from

hi.. can i return the a id of the affected rows of this: sql("UPDATE `auctions` SET `finished`='1' WHERE `stop`'".time()."' && `finished`='0'");

quote it?

backticks
quotes

Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. php mysql web hosting does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)

thank you for your help, renamed from and now it works will " `" go around that it is a reserved word ?
isn't sql error messages really awful? =/

finpingvin no idea, sorry, i just dont use any reserved words

hehe, had no idea it was
well
i did, but didnt think of it ;p

is it better to normalize a database host or store some useful/frequently used field in some tables even they are duplicated?

select foo from bar;
that from there is a reserved word

yeah, i finally see me stupidity
Its easy to go blind sometimes when coding

it is

please help!

i prefer adding a prefix to columns
helps make sure you never use any reserved word

okay
Thanks for your help anyway!

but RoR doesnt like it, and i was hoping to begin doing something with it
cheers

finpingvin, you can use `from` and `now`, but its not recommended
finpingvin, btw a reserved word error is not a syntax error

hello everyone

well… its an error in the syntax, but the main difference is 42000

i know, but that was what mysql query browser told me

DavidHKMrPowers, it all depends on your app, so you need to do the thinking

ah

is there a function that will tell me the number that appears most times in a dataset ?

DavidHKMrPowers, you come here every day, and always shout out "Please help!"

archivist tibyke i needa make a search engine
and i use ISAM

cool, use word index

MyISAM I hope and use fulltext or roll you own search engine
or use Lucene

yea, or fulltext

I roll my own
I didnt like the fulltext restrictions back in the early days

fulltext? i see this opsion in phpmyadmin

as an index type

so… i see…

4,5,2,2,6,4,5,4,4,4,2,5,4,4,4,4) and I need a function to use with group by that will return the number that appears the most (4 in this case)
is there such a function ?

hi, i wonder if someone can help me, i want to create a way so users can put a certain tag in the subject line of an email, and it makes an alteration to a database, then the body of the email is added to another part of the table within the database

DavidHKMrPowers, I see you have not been reading the manual from those last two comments

archivist i could see "index type" but never used it. i use unique, primary only..

you have a lot to learn then
your app will be slow till you learn about indexes

archivist thanks!
btw, one more thing, do you know how to make two colums unique as a combo in phpmyadmin?

is it better to use few store routines with lots of options or to use many similar routines with few options?

DavidHKMrPowers, add an index

I would base that on the overall overhead that is added by adding more options
'response time is king'

archivist thanks

still small tables, but growing. maybe I should make a few routines of it

++

Hi guys. mysqld_safe gives this error: http://nopaste.com/p/acZRMQddU - could anyone please help?

sveta, look in the .err file
oops sveweck ^^

Hmm, where would I find that? It's not in the current dir.

data dir

I don't have an *.err file in there (/var/lib/mysql/mysql/)

up a dir
/var/lib/mysql

there neither. I do have ib_logfile0, but it's binary

thats ok, maybe your system puts it somewhere else
eg in the syslog see my.cnf for details

ok found it, it's in syslog
8 InnoDB: Started; log sequence number 0
8 [Note] Recovering after a crash using
8 [Note] Starting crash
8 [Note] Crash recovery
9 [ERROR] /usr/sbin/mysqld: Incorrect information in file:
9 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect information in file:
I remember I read something about this error in some forum… I'll google again

!m sveweck repair

sveweck see http://dev.mysql.com/doc/refman/5.0/en/repair.html

heh heh nice nick for the bot
thanks, reading

serving wench for the community

right

-p password" and i still get to be asked for the password. why is this /

?

no space after the P

can't i set default value for DATE field as NOW()

but I'm using InnoDB, not MyISAM
(I think)

sveweck, that table is myisam
saamy, use a timestamp type

oh ok
does timestamp include date & time

http://bugs.mysql.com/bug.php?id=29727

saamy, yes

whats the difference. Timetstam p/ DATETIME

myisamchk requires the name of the corrupted table - but I don't know what tables I have (Joomla created them). How could I check which is the corrupted one? Or get a list of them all? (considering MySQL is not running)
er, nevermind
looks like the tutorial says it all

sveweck, its your mysql.host table

I am trying to execute ALTER TABLE `crew` ADD PRIMARY KEY ( `name` ) but it keeps saying "BLOB/TEXT column 'name' used in key specification without a key length". Name is of type mediumtext. Is this the problem???

no the problem is you have not said how much to index
and all a text column is stupid

yo arch. do you know which is more appropriate? datetime? or timestamp

up to you

oh ok

So in phpmyadmin, I should alter the Length/Values for the name field such that it is of size say 25 (for a first name)???

how are they different then

saamy, time you looked at the docs

:p

WWill, no the index length

OK. I've found that. So Index type: PRIMARY. What should be the size of the index??? Should I make it quite a bit bigger than i might expect a first name to be??? Say 30???

Hmm, `myisamchk –safe-recover db` gives me "error: 'db' is not a MyISAM-table"
does that mean that the table is unrecoverably corrupted?

nasty
maybe
WWill, probably

http://www.linuxquestions.org/questions/showthread.php?t=572388

Thanks for your help!

ok, I'll purge the database and start over then… thanks anyway.

sveweck, its onle the user table

you mean user.MYI is the only corrupted one? but `myisamchk -es *.MYI` lists 6 files.

myi is an index file

I'm really confused… do I have to make an index on a field before I can make it a primary key??? I didn't think this was nexessary…

no just define the primary key properly

"BLOB/TEXT column 'name' used in key specification without a key length". What needs changing???

the lenght
of the key

Is that the same as Length/Values (which is currently empty for my table)

no

MySQL save the log of delete and update off the tables?

so how do i change the length of the key then?

filed length for a bob is undefined but the key length must be
field

ORDER BY FIELD( country, 'US', 'Canada', country ), country; // This will order the list by placing the US first, Canada as second, and the other countries in alphabetical order

ahhh. So it is the type that is incorrect…. like i originally said!
I should prob be using varchar
i think!

you may have a good reason to be using blob so could be correct
but varchar for names is far more normal

that makes sense

I've been looking at this database problem for the past 3/4 hours. Can anyone help shed some light or point things out… http://www.sitepoint.com/forums/showthread.php?t=493595

ajohnstone, index is not unique

CREATE UNIQUE INDEX… is unique

Sorry it is, missed the Unique
It is unique however.

prove it

heh, ok.
bare with me..

OK, I really don't care that much about the data I have in the database. How do I purge it all? Just `rm /var/lib/mysql/mysql/*`? So I can start anew.

TRUNCATE TABLE.

put your clothes back on plz

I can't run the MySQL server currently (because some tables are corrupted), that's why I wanna get rid of all.

sveweck, move all data to a safe place and use mysql_install_db to recreate the mysql db then move all but the mysql db back
read up on db structure to know whats what

Thanks.

Hmmm, I dropped the index the second time missing out the unique as highlighted and its filled up with duplicate indexes Is there a CREATE UNIQUE INDEX tradeSeq ON trade_messages(trade_seq); with ignore dups or drop dups

hehe
dupes

find them with select count(dupefield) as qty,otherfields from table group by dupefield having qty 1

dupes delete
delete dupes

If you have a unique ID and the name may contain duplicates then DELETE t1 FROM table1 t1 JOIN table1 t2 ON t1.idt2.id AND t1.name=t2.name if you have other fields that need to be taken into consideration extend the join as needed

But I want to leave one in there.

which one? you choose
use the first sql to find and deal with as you see fit

Ok Cool cheers
Will have to come here more often, thanks for the help tho