CheapWebHosting.co.nz

Domain Search | www. .com .net .org .nz Check ALL

cyth varies dependent on datatypes

the php mysql hosting reference manual does

dkr orly?

can i drop tables en masse?
e.g. drop table where table_name like 'prefix_'
err drop table where table_name like '%prefix_%'

prefix drop

http://archivist.info/search/index.php/Prefix_drop_table

or better use information_schema and a script

k, i'll just do it by hand again

dkr found what i was looking for, thanks

hmm, actually phpMyAdmin has a feature to do that relatively painlessly
requires checking the checkbox next to each table, but that's better than click, click drop, click ok for each damn table

Hi, I'm trying to do select across two tables, using a LEFT OUTER JOIN, I have a WHERE clause and its effecting the whole SQL Statement, when I want it to effect just the right table - http://pastebin.ca/568928. Any ideas how I can make the WHERE effect the right table only?

Outer join criteria needs to be in the ON clause, not the WHERE clause.
LEFT OUTER JOIN downloads ON files.uid = downloads.fileid AND downloads.userid =1

thanks

sometimes secure email hosting subject lines actually make me laugh

0', which is

use a timestamp
also kill your colleague

it seems to be required in his DB (it fails saying there is no default value for the column)
we're trying to get good created dates. we're using a timestamp for updated.
is there a better data type to use?

g'day all

could someone help me understand some EXPLAIN output. http://pastebin.com/929995

sorry, to be clear, it's kinda strange to have to have your 'updated' timestamp before your 'created' -- at least to me.

best I can tell the worst part of the query is the search for 'client' in table_var

and I am curious why his DB requires a valid date, and ours does not.

since it says "Using where; Using temporary; Using filesort"

is it easy for software to pull data entries from mysql?
ie easier to do that than send the data from php hosting to a software
i'm presuming yes

the innodb references have nothing to do with mysql optimalizations ? only what to do on update and delete and stuff ?

Uhg. My MySQL process is taking up 45% of the CPU. How might I check what is making all the queries, etc?

mtop is nice

is it easier for software to pull data entries from php mysql web hosting than php to send it to software? i'm guessing yes

paging all query guru's
I have 2 tables with ids
one has all ids from 1 to 2869685, the other has holes in them all over the place
I need to break up all the missing ids from the one table into spans to finish processing
I know the number of missing ids total

guys

and want to know what the spans are to divide the missing ids equally to finish processing the missing queries in parallel

anyone wanna answer my q?

depends one the queries, if the queries are doing all the work, and not itterating through anything, should'nt make a difference

well it's just a login system
and i just want to do it the easiest way
ie the method which requires the least programming
i'm presuming it's easier to get data from mysql
than it is to send data with php TO the software

then that's a 'what am I fastest with c or php' than a mysql thing

thing is the software in question is made by another guy and hes asked me to do a website for him that also sends the login data to his software
and if hes asked me to do that maybe he doesn't know how to do it from his end?
but tbh
mysql is best hosting for login
so i'll tell him he should be fetching the login data from me

not really the way to do it I think

yeh i realised a better way too
website + most of scripts on my webhost
them my script sends data to some scripts on his server which simply have information about what data they have

so it's really not his concern you are using cms mysql hosting on your system, he just wants data, maybe xml or whatever format.
any way to speedup adding indexes ?
allready busy for over an hour now

better hardware

ok good point

When using SHOW TABLE STATUS, is the size of the database displayed in bytes or kilobytes? It appears to be byte.
0.o

Hi
I'm having a really annoying, but probably really easily fixable MySQL problem.
A row et to auto_increment is incrementing one over what it should
I have `id` 1, 2 and 3, and when i enter, it puts ID 5 on it.
then I change it to 4... and next time it gives an ID of 6, so I have to change that to 5...
I've got the autoincremental blues.

dont worry about gaps

mmm. I suppose/

but you can insert to a gap

I was thinking was there a way to tell autoincrement to start t 'X'

you can

Uhg. I've been MySQL for years now. i'm using MySQL under PHP. I want to run two queries on a single line (a TRUNCATE and an INSERT). How might I do this? I tried ; as a delimiter but it doesn't seem to have worked

Stormx2, one at a time for normal use

I just don't want an empty table if the insert fails
But, I guess I'll just make sure the insert works

is there an equivalent function/variable in sql hosting to the C mysql_affected_rows() function?

I'm trying to find out ids that arent in one table from a series of 1 through 2869685
I have another table with all the ids
what is wrong with my query
select id from table1 where id not in select id from table2;

a not in b

SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;

select id from table1 where id not in (select id from table2)

is this where the mysql web hosting brainiacs hang out?

unless they spontaneously combust, yes
mysqliacs?
interesting nick btw

well, i don't smell any smoke, so can i ask a question
wth am i supposed to look for with too many connections errors

feel free to provide context ;-)
if you're using PHP to connect, you probably don't want to use mysql_pconnect(), or disable persistent connections globally. they are not doing what you think they are, because of apache's process logic. use mysql's thread_cache instead.
basically, mysql byd efault only allows 100 connections.
if you use php from apache, you can eat that up.
but that's just a matter of not having set up php and mysql server properly.
running with defaults is not good for a production system ;-)
mysql can handle up to a few thousand before other subsystems in the OS get unhappy. but the key issue is how to manage them from your app end
so there ya g
go even

well, it is on a paid host, they say it is my code, i am not using pconnect and i read that a regular connect statement closes the table on exit of the code, is that correct
my opened tables says 91 k right now and is colored red. what do i do to lower that number?

closing it explicitly is better but yes it should.
opened tables has nothing to do with this
it's affected by table_cache which is a server param that your hosting provider should tune. it just shows that they don't know enough about mysql or don't manage it ;-)

you said, "use mysql's thread_cache instead". I don't know what that means
my table cache is 256
oh, thread cache size says 8
at least the variable says 8 but the status for thread cache says 0
slow launch threads is 44

SELECT datetime as dt from webpage_views
GROUP BY dt
what's wrong with that/

MySQL will tell you what is wrong with it, or at least where the error is.

how to i repair a table that has been marked as crashed?

!man repair

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

where can i find a mysql-devel rpm or tar.gz file ?

archivist, myisamchk worked

ok

is there a good way to look for missing numbers in a series? like having 2 tables where ids in one match the other after processing, but some spans didn't get processed
like one table contains 1 - 10 and the other has 1 - 3 and 6 - 8 and 10
so I want a query to tell me the spans, like in the example above it would be 4 - 5, and 9

hmm yes
Common queries

http://www.artfulsoftware.com/queries.php

http://www.artfulsoftware.com/infotree/queries.php#72

shit archivist, that may just be what I need!
running, we'll see what it outputs

does the vs 2005 plugin work for vs 20005 express edition?

is there an equivalent function/variable in SQL to the C mysql_affected_rows() function? (i've looking everywhere, and find nothing)

usually the number of affected rows can be retrieved from the API you are using to issue your queries
for example in java when you issue a executeUpdate("update table....") query the function returns an int that indicates how many rows were updated

yes, I use the mysql_affected_rows() function often, I'm just wondering why this information isn't available in the SQL language itself, but only in the API

ah
hmm well it might be... not sure

is there a limit in the number of OR'S & AND'S i put in a query?
i can put*

Not really.

Hi

is there simple way of checking if a record exists before adding it ?

SELECT is pretty simple.

okay... select it is .. I was hoping there was some flag to insert .. but okay .. thanks fer the help

What is your intention?

adding a user account .. but if the username exists .. throw an error

How can i use 2 and statements in a good way? i get errors when i do for ex select * from blah where foo = 'a' AND foo1 ='1' AND foo2='d'

errr if username or (first and lastname)

Well, you can do that by using a UNIQUE index/constraint. It will throw an error. I prefer to SELECT first, so my INSERT doesn't touch the DB, and it's easier to interpret the results.

okay .. I'll use my scripting language to do the select , check and then insert

That's what I prefer to do.
That query looks fine. What is the error messagef?

ah it was a missing " ' " at the end :P, anyway is it possible to do AND x='1', p='2'

What is that supposed to be?
Time for me to split!

a query, instead of 2 using AND do it this way select * from blah where foo = 'a' AND foo1 ='1', foo2='d'

AND foo1 ='1', foo2='d' -- huh?
You might want an OR...
where (foo = 'a' AND foo1 ='1') OR foo2='d'
NOW time to go and buy something expensive.

ah k, thx !

I want to delete all rows with id 10 to 100 how can i do that?

Can I impose an artifical limit of a TEXT column?
Like 64000 chars is the max. it can hold, but can I make it only hold 30k?
Also, am I SOL if I want to store more than 64 kB?
Hello?

err12, delete from t1 where id between 10 and 100

thx

My question was ignored.

I think TEXT will hold a megabyte or so, not 64K

why do you want to do that?

I don't know about your length limit questions, can you be more clear

unfortunately, mysql doesn't support those sorts of constraints... unless you use a trigger. can't you impose that in your app?

It's 64 kB... according to people in here.
Yeah... I am doing it in the app.

read the manual

But it would feel better to have it at MySQL leve ltoo.
I HAVE read the manual. It's worthless. Doesn't tell anything.

the manual says 65535, wtf are you talking about

MEDIUMTEXT will store 16 million characters and LONGTEXT can handle over 4 trillion,

aww... the manual has it all! it just might be hard to find

mediumtext, then

you're right! you might have noticed, though, that he was talking about TEXT!
read the manual

you're so right

Oh... I didn't even realize that there were MEDIUMTEXT and LONGTEXT.

http://dev.mysql.com/doc/refman/5.0/en/string-type-overview.html

see? I did show him something. nah nah

question, how do I create a new blank record in a table?
what I'm current doing is something like this:

god, what could you possibly mean by "blank record" and why would you possibly want that

INSERT INTO quote (id) values ('');
all fields except id set to NULL

WHY!

it's for an AJAX-based admin application I'm writing
want to see it so far?

you are surely writing this in a horribly silly fashion!

(Firefox recommended)
it will make sense soon
well, i guess since what I'm doing works fine enough for me, I'll just stick to it

you can insert an "empty" row by just doing INSERT INTO t1 () VALUES ()
but that is a completely asinine thing to do

want to see the actual app?

not really!

you'll understand why

it probably involves people filling out information and submitting it?

nope, way off
it's an admin app for mysql

ok, so no filling and submission of information?

and so far, everyone loves it
well, you have to fill it out and submit it, but there's no forms, it's all AJAX
you only ever load one page
think kind of like google maps

i wanna see ;P, link?

http://joold.com/admin?table=shape
when you click on a table cell, you can edit it

k

and the delete buttons on the right work too
and notice how the page never refreshes because it's all AJAX
now, to add something in, i need to create a blank record in mysql and display a blank table row, where the user can proceed to fill all those in
that's why i asked what i asked
oh, and don't worry about any of the data, it's a mock-up

don't create the row in the database until they've entered the data for it

yeah i code ajax too, good job

what a plan!

just make an extra row, with empty fields, and a "create new" button next to it or whatever

so you're suggesting i have them fill out the new record first?
it's an idea...

if you don't think adding a row to this thing constitues "filling out information and submitting it" you are completely nuts

had to convince you to look somehow, eh?
thanks!

most schemas worth a damn will have some columns that are NOT NULL, which would *never* work with the way you're trying to build this thing!

that's actually true--i was planning on dealing with that problem when it came up lol

just came up!

hahaha

no, you're ok
just don't insert the row till you have the data

and how about that, i did not even have to look at your app to tell you what you were trying was ridiculous

right, thanks
you must be using tarot cards or something...
what's the general consensus on the app itself though?
i mean, the idea is there...i just have to make it reality

i wish i could cancel after i clicked on a cell

yes, good point, thank you, added to my list

i worry that when i click on a cell someone else will change it before i have a chance to do somethhing to it

so you suggest locks?

it's the only way for something like this to work

transactions

eek no

really horrible in a web environment, though
:-D

yeah it poses problems with the ajaxyness

yeah, well, it's not something that 10 people are going to be using at the same time if that's what you mean

oh ou can hold a transaction in an ajax app
but it's still undesirable
1 ?

yes 1

yeah, just because it's possibly doens't mean i'm not going to go do something else for a half hour or that my web browser won't crash

then you need to deal with multi-user (concurrency) issues, fundamentally.

but the most that would happen is someone overwrites a change

or you can close your eyes, stick fingers in ears, go lalala and hope it never goes boom

that's pretty bad, if you're trying to build a system that has any hope of being useful!

which is what many sites actually do. until they go boom

i also worry about how you handle foreign keys, data type constraints, etc

lalalal ;-)

yeah, i'm trying to get the functionality down before i do things like multiuser support

wrong. this is an architecture question. don't deal with that after functionality or you will find yourself rewriting it ALL

but this could happen in a regular admin app too

the problem is ... that *IS* the "functionality". what you are calling functionality is just a dusting of powdered sugar on top a donut made of poop

I architect while coding myself, but you cannot ignore this until later.

if someone clicks edit and sits there for an hour while changes are made and then commits the change

dude it's ajax

yep! and these are problems that plague "regular admin apps"

you can poll periodically and alert the user that someone else has touched the row!

you have a point, but the javascript is separate from the python in this case
oh, I like that idea!

a donut made of poop

and that is the end-user's problem HOW?

ewwww

I was merely saying...

and i'm not (yet) an ajax coder. just an old programmer. ha. gotcha.

lol

if the user doesn't care, don't even ponder the angle.

i've done C too

ah, a rare event.

well, my hope is that I can use this outside of my company

it's a great idea, but you really must stop, now, and consider these fundamental concerns before you get too excited about the ajax and whatnot

let's see, I've done C, C++, Java, Perl, Python, PHP, Ruby...list goes on
yes, I agree with you
here's what idea I have in mind
well, actually, how will my code know when something has been changed in the database?
there needs to be an event fired from somewhere

i'd rather see you manage transactions and then roll them back if the user goes away
so have a watchdog thing that expects the client to check in from time to time
the client could even time out after "a while", tell the server it has done so, and cancel the operations the user has undertaken

yes, but...that still doesn't solve the concurrency problem entirely
if two people are editing the same cell, the one that's lagging by 4 seconds needs to know that it was just changed

if you manage transactions properly and get locks on the rows people are fiddling with, it could help
but yeah people still won't see updates to rows until it's too late

i say i get version 1 out without multiuser hacks because it needs to get done first before it gets perfect
i don't mind a re-write later
more money in my pocket
lol, getting paid to make open source apps
the best of both worlds

it's very very easy

what is?

a proper row has an ID, and you can give it a revision#. you edit that revision# and on update increment it. you can even automate that with a trigger so the app doesn't have to worr abot it.

just re-fetch every row each time a user moves his mouse over it

and thus you ca do an UPDATE ... WHERE id=# and rev=# and the update just won't complete if it's not the same any more. then you report to the user

I like that idea most

you just need to be state-safe. simple coding practise however applied to SQL.

can you image how much traffic that would cause?

i'm kidding!

lol litheum

tags /sarcasm

no, you forgot to put on your sarcasm glasses!

im off

one last thing, where do we store revision numbers?

bb

in a column?

ah, but then I have to fiddle with the db

yea design is a great hting

each table will need that column

and ALTER TABLE is another.
it may

this is meant to be a drop me in and i'll work right away

he's trying to make this a generic tool that can operate on any table

you also need your app to work and not eat either bandwidth or db/appserver cycles until you're dead

of course the app could just go in and alter tables lol

you could use a separate table that works on the IDs

id's can be the same between different tables

won't fly. doesn't scale. he needs to do something
exactly so that's more efficient even.
mysql can do multi-table updates, so it's quite pretty possibly.

and how will this not scale?
wow, if software were perfect from the get go...

not doing something with IDs/revision logic would involve way too much blah

what about leaving out revision logic altogether for version 1 ?

maybe version .1

yes! i'll be like the wine project!

bad idea

rewrite?

i love that you are already *planning* to do a rewrite becuase you know that your app has all of these problems

welcome to software development

no, this is called "hacking"

yes, that's more like it

"software development" has a design component that would have exposed these problems before you asked the ridiculous question about inserting empty rows and had us tell you about all this stuff
b"software development" has a design component that would have exposed these problems before you asked the ridiculous question about inserting empty rows and had us tell you about all this stuff/b

it was a unique and refreshing approach
i've been told that's what i think
i follow this development cycle:
http://pic14.picturetrail.com/VOL539/2669804/8578549/116304609.jpg

which part are you? the programmer writing the useless fuckin swing that sits on the ground?

i'm the supported part
oh and the roller coaster

so you're working on building a stump and fooling yourself into thinking it's a roller coaster... sounds good.

if bill gates made billions doing just that, i'm pretty sure that i'm not doing so bad after all

hasn't really worked quite so well for anyone since him... but best of luck!

lol, i'll be back in a few months and i'll let you know how it's going with the app
or sooner, i might have mysql questions
yeah, probably sooner

/win 21
oops sorry
Is it possible to mark something as both a primary and a unique key?

primary key is unique by definition

ok, thanks

When I INSERT into a `table`. How do I get the PRIMARY incremented ID from the INSERT as easily as possible? Do I have to do a SELECT statement after an INSERT, or...?
Nevermind, I got it. In php you call: mysql_insert_id(); weeeeeee

Hello, is it possible to INSERT/UPDATE without updating indexes?

Hello?

can i have a column that is an array in a table?
that doesn't make much sense but i don't know how else to phrase it
nevermind, that's just a dumb question
0am is NOT a good time to decide to restructure your table

I get what you're saying. You want to store the entire contents of an array into a table.
You table wouldn't be in 3NF then.

i got it. i just created another table to store the "array" and referenced the entry from my previous container table
now i just need to craft a clever query to retreive the results from both tables at once instead of in two seperate queries

ah, but you aren't storing your entire array in a single column right?

no

good, because that's what I thought you were doing :P

i have a container table that has a unique id column and i'm referencing that column in the table that actually stores the entries
no, if i stored it in one column in CSV then it wouldn't be very extensible

It's not uncommon to see people store array data with a delimiter like , or ; to separate their fields. Example storing in a text field hi;im;an;array;look;at;me;yay

not to mention the performance hit on a field of that size being hit over and over again

At least when they start out.
Rightly so!

if you're only storing a max of 10 elements or so that's under 256 chars then you're better off using one column
if you're talking about 1 - infinity entries then you'd be insane

It all depends if you want to keep 3NF or not. Sometimes breaking the rules for understandable reasons like you say for speed and such.

basically what i'm implementing is grouping...a cluster can contain any number of nodes and each node can be present in any number of clusters

I prefer to stick to it pretty strictly though.

the two table approach in my case is probably the best method

so an M:M relationship, then all you'd need is that extra table in between.
making two 1:M relationships

and provided that both tables are indexed properly then the performance hit will be negligable

so what is this database you're working on anyway?
out of curiosity ?

it's for a proprietary system i'm developing

ah, not a game?

sadly enough i don't think mysql is going to be scaleable enough to handle the transactions
no

unfortunate.
I've never worked with clusters or anything like that with MySQL, so I wouldn't know how well it scales.

i have a bad feeling that mysql is going to die a painful death with more than 1m concurrent users

I do know however it beats the pants off of MSSQL in terms of general query speeds.

this isn't database clusters
"cluster" is nothing more than a fancy word for "grouping" in my project
think of it as a folder; you can store items in it and have a hierarchial representation of your items

Usually when you talk about scaling, you talk about easily increasing the ability to meet the demand which usually means adding more clusters to an inter-network, yes?

typically, yes
when i mentioned scalability i was actually referring to performance

But you're talking about having a single MySQL hosted system to respond to 1M concurrent users?

i know that oracle can handle what i need to do but i don't want to use it if i can work around it so i'm thinking about utilizing pgsql with mysql

You do know there's like limitations to how many socket connections can be made, even for a LAN. You wouldn't get above 10,000 I suspect.

that problem has been solved

Depending on the horsepower of the server anyway.
It has? I had no idea.

i mean i've solved that

Oh I see.

there won't physically be 1M active connections to the database at one time
there will, however, be 1M or more active queries on the system at any given time
we've esitmated it to around 1.8M peak
we'll see how it goes. i've never really given mysql this much of a test before so i'm a little excited to see how it holds up
anyway i'm off to bed. too much table complexity for one day

quoting wikipedia about itself "Wikipedia, more than 200 million queries and 1.2 million updates per day with peak loads of 11,000 queries per second"

Wikipedia runs off of MySQL?

unless we've been being fooled

Crazy. I wonder how large their data cluster of servers is.
Dedicated to MySQL I mean.

around 12 servers
oh, 18

Consty, don't be curious http://meta.wikimedia.org/wiki/Wikimedia_servers

that page sucks
(it's usually months out of date)

amazing

not a big deal atm

How can i do a query so it looks for the first letter?
the first letter in a row

hello all, i am wondering if there is some way i can watch the queries on my db live?
is their some type of logging i can enable and then tail -f a file perhaps?

How can i do a query so it looks for the first letter in a row?

what do you mean?
!man string functions

see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

i mean, like select * from users where (user starts with A)
and the result will be for example, Albert

select * from users where user like 'A%';

ah thx!

any pastebin?
!pastebin

pastebin

try http://pastebin.ca

The|Navigator the column has auto_increment

hi there
any ideas on to backup a mysql database in which utf8 characters are not destroyed?

you can set the character set from the command line or have it as default in the [mysqldump] section of my.cnf

anyone awake?

So I'm trying to make a method in a web framework do something it's not supposed to. Assuming I'm limited to a query of the form "SELECT COUNT(x) FROM theTable y", where I can set x and y to whatever I want (but not exploit SQL injection), is it possible to make the query return a given number, say 42?
So I can set what goes inside COUNT() and I can set WHERE conditions, do JOINs, use GROUP BY, can also use DISTINCT. Oh, and the number I want to return is always less-than-or-equal-to the number of rows in theTable.
Tried &"WHERE id IN (SELECT & LIMIT 42)" but apparently "IN" subqueries aren't supported in my version of MySQL.
Huh. Specifically IN+LIMIT isn't allowed.
Seems I _can_ do injection. Hm.

i'm having a devil of a time with joins. i know how to do them, but they are taking forever.
if i have two tables, shamref and item_master_mirror, and try to do a join between them like this http://pastebin.com/930159
the query just keeps running...
the fields that I'm joining on are strings, so I'm sure that's not helping.
when i take the group by and set a limit of 1000, the query comes back in 43 seconds (which is way too long)
there are 175,000 and 125,000 records in the respective tables.
actually, one is a string, the other is an int that I'm joining....that probably has a little something to do with it.

Hi how can replace stuff in a sql query? like replace the text "foo" in all name rows

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_replace ?

where can i get database design examples for common things, like books ?

thx, guess im to lazy .

Related Topic: