if the data is the concern, not the data structure, why not encrypt the data itself?
-Original Message-
From: David Crane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 11, 2004 2:15 PM
To: [EMAIL PROTECTED]
Subject: Re: how do i encrypt the .frm file with a password
Since this
One of the ways around this that I've seen is to maintain an additional field for the
'full path' info.
so that you have:
table_name
---
node_id integer auto_increment
node_name varchar(50) not null
parent_id integer
full_path varchar(255)
the obvious downside is that your application
you can do something like:
select phone_number, count(1)
from your_table_name_here
group by phone_number
-Original Message-
From: Jan Blomqvist [mailto:[EMAIL PROTECTED]
Sent: Monday, March 15, 2004 8:27 AM
To: [EMAIL PROTECTED]
Subject: any select statement like uniq in unix
I'm not 100% sure on this, but what about the .myd file timestamp?
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: Friday, February 06, 2004 9:09 AM
To: Phil
Cc: [EMAIL PROTECTED]
Subject: Re: How to determine when a MySQL database was last modified?
Add a
From what I've read ( I think in the MySQL docs, might have been here in the list),
technically it will take less time to add the indexes after the table creation, than
the overhead of index updating per-insert.
Either way, it's gonna take a loong time.
-Original Message-
From:
another option is to set your pager option:
mysql pager more
then re-run your query
-Original Message-
From: Mike Johnson [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 29, 2004 11:01 AM
To: Adel Ardalan; [EMAIL PROTECTED]
Subject: RE: Paging!
From: Adel Ardalan
Stored procedure versioning/backup/restoring has always been a pain in the butt for
all dbs that support them. What is done 9/10 times is the sql script that creates
them is stored, versioned, and used for backup...
now a 'show create stored procedure blah_blah' function would be nice... ;)
Could you CAST them first, then apply MIN?
-Original Message-
From: Eldon Ziegler [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 9:56 AM
To: [EMAIL PROTECTED]
Subject: MIN with negative numbers in VARCHAR
We have a table with floating point measurement values stored
ok... you might have two options:
1- (don't know if this will work)
do a min(cast(Value * 100 as signed integer) / 100
2- or min(Value + 0.0)
and see what happens.
-Original Message-
From: Eldon Ziegler [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 11:24 AM
To:
I've found, in my many years of app dev work with Oracle, MSSQL, and now a little
MySQL, that stored procedures are a very mixed blessing.
Depending on your application architecture, they be just what you want, a means of
encapsulating a complicated, data-centric function. Database stored
strong suit is Java, I don't think I'd
be too much help... ;)
Thanks,
Dan Greene
where the normal connection closure may be
skipped...
My $0.02...
Dan Greene
-Original Message-
From: Mike Morton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 06, 2004 2:15 PM
To: [EMAIL PROTECTED]
Subject: Sleeping Processes
I have a mysql database that is running
left joins are your key
select * from a
left join b on id = tbl_id and b.tbl = a
where b.tbl_id is null
(not 100% sure on my syntax, but note the join, and the limitation on 'left' result
set is specified within the left join clause)
-Original Message-
From: Dean A. Hoover
Without a unique identifier, the only way you're gonna get to update right is to use
other data in the row to identifiy the record...
so with a table structure of
name
address
state
your users can select anything they want, filtering w/ a where clause.
If you grant them update on the table,
So if I follow you, you allow your clients to execute direct sql on the database, both
select and update, but _they_ simply don't want to see the row id data?
If that's the case, then too bad for them if they are capable of writing sql, then
they have to handle the result, or omit the field
What if you were to make a 3rd database, containing shared elements, such as your user
table (I presume we're not talking the MySQL system user table) and then have
necessary permissions granted between your other database users to read that table
jointly, as you can query cross
you can use the concat function:
http://www.mysql.com/doc/en/String_functions.html
SELECT concat(vend_city,', ',vend_state,' ',vend_zip)
FROM Vendors
ORDER BY vend_name;
-Original Message-
From: Gilbert Wilson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 10, 2003 2:42 PM
window's internal multi-display support.
My $0.02,
Dan Greene
-Original Message-
From: Ray Ragan [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 09, 2003 3:10 PM
To: [EMAIL PROTECTED]
Subject: Bug in WinMySQLadmin 1.4
My SQL Team,
When launching WinMySQLadmin 1.4 on multi
If it's an option, I would run your datafile through a processor (sed on unix,
ultraedit on windows) to search and replace the string
with
\
and try it with
fields terminated by ',' optionally enclosed by ''
as mentioned by Mike Johnson's posting
(escaped by '\' is default)
-Original
how about recreating the table w/o the autoincrement, then reload the data, then alter
table to reimplement the autoincrement?
-Original Message-
From: Matthew [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2003 7:12 PM
To: Alex E.Wintermann; [EMAIL PROTECTED]
Subject: Re:
The time zone matters... your results are exactly 8 hours off... PST is gmt -8. So it
looks like the from_unixtime function is converting to what the time was locally at
that moment in GMT. Not what I would have expected either
What do you get when you run-
select unix_timestamp(urtime)
from what I've read in the docs, if you use 'grant' you don't have to flush, but if
you insert into user tables directly, you do
-Original Message-
From: Skippy [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 02, 2003 11:56 AM
To: [EMAIL PROTECTED]
Subject: Re: mysql remote
I think that to answer Todd's question, no there is no inherent way in MySQL to export
results as XML the way that sqlserver and oracle do.
Another solution that comes to mind (if you're using java) is to write a simple class
that converts a result set to an array of hashmaps (row in array is
The most guarenteed way to have a feature in a product pointed out is to make a public
statement that it's not available :)
keyboard in mouth,
Dan Greene
-Original Message-
From: John Griffin [mailto:[EMAIL PROTECTED]
Sent: Monday, December 01, 2003 10:49 AM
To: Dan Greene; Todd
yo yo yo,
another phat option is to create another page on your system that returns the %
number, and using flash, call that url and parse the results, and update your flash
chart accordingly. We've done similar things to have flash talk to our systems (set
up web pages that return xml
don't forget to change the port number that the server is listening on if you plan on
running them simultaneously
-Original Message-
From: Peter Sap [mailto:[EMAIL PROTECTED]
Sent: Monday, November 24, 2003 4:59 PM
To: [EMAIL PROTECTED]
Subject: Re: My.cnf
You could install
well... I'm not 100% sure of your table structure, but if the children are in same
tables, then join to each:
select *
from action_items a, child_table b, child_table c
where a.owner_id = b.person_id
and a.creator_id = c.person_id
now if the values in the action_items table could be null
This may be simplistic, but is mysql putting any text before / after blob content,
such as column name, '1 row processed OK', that may be 'corrupting' the tar data?
using a very small tar file, and run your command, piping to more instead of tar to
see if there is any extra text that mysql is
ok... try this:
mysql --skip-column-names test1 mytestoutput.tar
tar xvf mytestoutput.tar
and if it works, try
cat mytestoutput.tar | tar xf -
to see if it works
-Original Message-
From: Denis Mercier [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 19, 2003 2:41 PM
To:
one more idea:
try:
mysql --skip-column-names --raw test1 | tar xf -
-Original Message-
From: Denis Mercier [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 19, 2003 2:41 PM
To: [EMAIL PROTECTED]
Subject: Re: piping blob into shell command (tar)
On Wed, 2003-11-19 at
There recently was a thread discussing this with a very nice summary by the person who
had the issue... do a search on the archives for 'maintaining the size of a db' to
find the thread
-Original Message-
From: M.D. DeWar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 18, 2003
I can't believe I'm saying this, but MySQL may not be the db of choice for your
particular app...
I figure you have few options:
1- keep db structure same, keep mysql, suffer performance issues
2- keep db structure, switch db, suffer migration costs
3- change db structure, keep mysql, suffer
on.
The MySQL documentation is, IMHO (in my humble opinion - another oldie acronym),
incredibly good for a free product. Searching it for answers, particularly about
syntax, should be everyone's, including my, first step in solving an issue that we're
having.
[getting down off of soap box]
Dan
you need to tell mysql what field to equate to the value in your where clause:
update items set ItemDescription = 'new text'
where ItemDescription = 'old text'
replacing the items with the text you have below
-Original Message-
From: Andrew [mailto:[EMAIL PROTECTED]
Sent:
two last gotchas I thought of...
1- the routine will erase all the old records, but the day's logs will increment
between executions, so you may want to give it a day's worth of 'padding' if the 20GB
is a hard limit (disk size). No worries if it's flexible
2- until you have filled to your
I think that I must be missing something, as I agree with all the suggestions that to
change the seperate date element columns to a single date field, but Meli's original
post had a date falling into multiple quarters. Now to my knowledge, a date can only
be in one quarter, from a calendar
you could put a shell script as the actual cron job, and make the file only read-able
by root, using an environment variable as the password passed (defined in shell script
file), so that way even if someone 'sniffs' the process via 'ps -ef' they don't see
the actual password (if they happen to
cronjob a sql script that runs a delete statement for old jobs daily
-Original Message-
From: Scott H [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 12, 2003 10:40 AM
To: MySQL Mailing List
Subject: Re: maintaining size of a db
--- Egor Egorov wrote:
Scott H wrote:
To: Dan Greene; MySQL Mailing List
Subject: RE: maintaining size of a db
Yes sir, exactly. It's just that's what I'm
looking for, and can't figure out. I can set up
a cron job, but what exactly would the SQL delete
statement be that would allow me to delete old
records in such a way
McTernan; Dan Greene
Cc: MySQL Mailing List
Subject: RE: maintaining size of a db
Well, it sort of helps. But that section is
about future enhancements intended for mysql. I
need to set something up now, with the current
stable version. One thing I read (can't find it
now) indicated
your best bet would be to do a port-forwarding methodology, at the os level, not in
mysql itself
I've used some for SOAP tracing, to intercept and view http traffic, but I'm sure
there are some out there that just forward traffic w/o a gui
-Original Message-
From: Misaochankun
you could do
select article_num from $table
order by article_num desc LIMIT 75
-Original Message-
From: Mark [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 8:40 AM
To: [EMAIL PROTECTED]
Subject: Last 75 entries from a table
Hello,
Using MySQL 3.23.58, what I
I seem to remember someone saying that if a query would end up returning more than x%
of a table (I think it was either 30% or 50%), then mysql just ends up doing a full
scan regardless...
-Original Message-
From: Eric Anderson [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06,
you are going to want a 'buster' table... also known as a many-to-many table
so you have:
contacts
Contact_Key
Industries
-
Industry_Key
Contact_Industry_assoc
--
Contact_Key
Industry_Key
-Original Message-
From: DeBerry, Casey [mailto:[EMAIL
I just recently helped someone else with a similar issue
is your PHP code (and therefore your insert) getting executed when the form is being
displayed to the user initially, and then again when the form is submitted?
-Original Message-
From: Erich C. Beyrent [mailto:[EMAIL
Is there a way to do this on a live running (i.e. production) server?
-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 28, 2003 1:49 PM
To: 'Iago Sineiro'; 'MySql Mail List'
Subject: RE: Howto reduce size of MYISAM files after deleting
well... when I do db design, I tend to start with the objects of my system. The one
that comes to mind in your case is people.
so you'll need a people table.
well what are the details of a person?
first_name
Last_name
Middle_name1
Middle_name2
Maiden_name
[any other basic bio data]
so
are expensive'
I'll send you that memo
:D
Dan Greene
-Original Message-
From: Randy Chrismon [mailto:[EMAIL PROTECTED]
Sent: Monday, October 27, 2003 2:51 PM
To: [EMAIL PROTECTED]
Subject: My Company DB Wars
My old Micro-Economics professor must be chortling in his grave
Indexes are your friend. Perhaps your best friend (as far as databases go)
---
To give light on your question... you can index any field... if the entries are
distinct, it's called a 'unique index' which are the best kind to use. Otherwise you
have a 'non-unique
You probably want to restructure your tables (if that's an option)
if it's a heirachal menu system (one parent only), then you'll need to put a
parent_id column, and then map each child to the parent.
then once you have your parent menu (158), you select * from menus where parent_id =
158
As you are selecting all records (no where clause), it will scan the whole table every
time, I believe...
does anyone know if he added the other columns to his index, or had 4 seperate indexes
(one per column) would they be used in this operation?
Ok, I've found the optimization chapter in
think that you're stuck doing it in your application. Just encapsulate how
that data can be updated, and have all code that updates it call your
function/method/etc...
My $0.02...
Dan Greene
-Original Message-
From: Mumba Chucks [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 23
I may be missing something, but wouldn't a substring(bigdata, 0,instring(--header
end--)) suffice? Of course, my syntax is all kinds of wrong... but you get the
idea...
-Original Message-
From: Jan Magnusson [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 23, 2003 10:56 AM
To:
from one dan to another.
select man.description as man_description, cat.description as cat_description
from main_table as main, manufacturer_table as man, categories_table as cat
where main.manufacturer_id = man.manufacturer_id
and main.category_id = cat.category_id
and title_id = 3
Dan
To make this back to being a MySQL question
What if you used the encode or md5 functions when you retrieve the list of results,
and then do your lookup on the item details by matching it to decoding the same id?
ex. get list-
select fid as open_fid, MD5(fid) as crypt_id
from your_table
Mahesh,
The best advice, from what I've heard around the list is to base your decision on this
based on your filesystem. Some filesystems handle large # of files well (1 db, many
tables) some don't. Some handle many directories well (many db's 1 tbl each), some
don't (ala your example).
Emilio,
Can you supply the full insert statement? Also, is this happening for all users, or
one in particular? I ask, as I've seen issues with web app users double-clicking the
submit button causing similar issues
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL
sending on to list while I think... ;)
-Original Message-
From: Emilio Ruben Estevez [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:34 AM
To: Dan Greene
Subject: RE: When inserting data it insert 2 records one correct and one
blank!
Ok, here it is the full statement
, 2003 3:39 PM
To: Dan Greene
Subject: Re: FW: FW: When inserting data it insert 2 records
one correct
and one blank!
Im lost, im new on php and mysql so i may be omitting some
statements or
logics i send you the entire script, its an lbi that i use on
DreamWeaber MX
if you can just
an option for A-
no idea if this will work, but what if you moved your actual data file to new drive,
and soft linked it from the other drive?
-Original Message-
From: Brendan J Sherar [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 7:27 AM
To: [EMAIL PROTECTED]
Subject:
I know it's not the answer you're looking for... :( but dealing with overnights has
caused me so much aggravation in past apps I've written, I've tended to make the
client create two (or more) 'bookings' for the covered time... don't know if it's an
option for you, but it's my $0.02.
try
select commentid, commentref
from comments
ORDER by
commentref ,commentid DESC;
-Original Message-
From: Cummings, Shawn (GNAPs) [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 02, 2003 2:37 PM
To: [EMAIL PROTECTED]
Subject: sorting/grouping
I have a very
the right one...
Sorry that I'm being incredibly vague I set it up months ago
Dan Greene
-Original Message-
From: Steven Nakhla [mailto:[EMAIL PROTECTED]
Sent: Friday, October 03, 2003 9:42 AM
To: MySQL
Subject: Tomcat, Connection Pooling, and MySQL
Has anyone managed to setup
you want to do
insert into mytable (column1, column2, column3)
(select thiscolumn, '1', now() from anotherTable);
Mysql 3..
I can't figure this one out
I need to move data from one mysql table to another
The hurdle for me is adding additional column values.
Here is what I
In the last timetracking system I built, I used a 'timeentry' table which was basically
timeentry_id (pk)
employee_id
date
num_hours_worked
task_id
I put a few other indexes in there based on the searching that I had to do, but to
each their own...
-Original Message-
From: Syed
you,
Syed Ali
(609) 951-2989
-Original Message-
From: Dan Greene [mailto:[EMAIL PROTECTED]
Sent: Monday, September 29, 2003 11:12 AM
To: Syed Ali; [EMAIL PROTECTED]
Subject: RE: Representing time sheet data in Mysql
In the last timetracking system I built, I used
total_count = total_count - 1, je_total = je_total -
:old.je_total;
hope this helps,
Dan Greene
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, September 26, 2003 7:34 AM
To: [EMAIL PROTECTED]
Subject: GROUP BY performance on large tables
Hi: Issuing
'app server' as such, you can't really take advantage of connection
pooling. The closest you can do is write into your app that if the connection is idle
for x amount of time, then it closes the connection, and reestablishes it upon next
data request.
Hope this helps...
Dan Greene
it depends on how you want it
if you want them consecutively, you probably want to UNION two queries together, but
you need to make sure that the column types are identical...
i.e.
select meeting_name as event_name, meet_date as event_date
from meetings
where meet_date = curdate()
union
It's actually mentioned in the user comments in the online manual (was there after the
other reply looking for MINUS support),
that in a union, in any column that is a literal, the top most query defines the
datatype (non-literals obviously use the column type) for the column.
I agree it's
I believe it, as there's an open source project c-jdbc (clustered jdbc) that can do it
for x number of machines running any jdbc compatible db's (and they don't even need to
be the same type...)
-Original Message-
From: Neil Aggarwal [mailto:[EMAIL PROTECTED]
Sent: Friday, September
SELECT state_ID FROM financial_master WHERE category_ID = '1'
becomes
SELECT unique state_ID FROM financial_master WHERE category_ID = '1'
-or-
SELECT distinct state_ID FROM financial_master WHERE category_ID = '1'
2 points !
-Original Message-
From: Comcast [mailto:[EMAIL PROTECTED]
ok...
I think this'll work...
select unique t1.*
from table1 as t1
left outer join table2 as t2 on t2.name = t1.name
where t2.name is null
-Original Message-
From: xander xxx [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 11, 2003 2:56 AM
To: Dan Greene
Subject: Re: RE
What most data structures do is use foreign keys to the unique numerical id column, in
your case, company_id. That way, if the company code changes, the id does not, and
therefore, no issues on update...
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]
Sent:
not to respond with, 'read the manual', but
There is an entire section of the manual devoted to user management, I would start at
www.mysql.org
click on the documentation link on top, and start there...
(user account management is :
Having developed such an app myself (albeit in Oracle where the wonder of 'connect by'
exists which according to 'to-do' lists is coming for MySQL yippie!), this is what
I recommend...
1- if your forum is 'threaded', i.e. a message is in response to another, you can use
that linking to get
I have this strange feeling of deja-vu
This was just posted to the list recently (last week?), and currently, you cannot do
cross-table full-text indexes, excepting boolean text searches, which would be slow.
I have this strange feeling of deja-vu
Dan Greene
-Original Message
And although it blows out your single-query theory further out of the water,
you could query your listing for a count of each starting letter of the last name,
getting something like this:
[syntax will be wrong (closer to Oracle syntax), but I don't have a mysql installation
at work to get it
If you're accessing your db through JDBC, an idea that I've been following is the
c-jdbc project...
http://c-jdbc.objectweb.org/
it's software raid clustering for databases... it's still in beta, but it looks very
promising for easy clustering. Combined w/ MySQL's master/slave setup, it could
I would use MySQL CC (command center, I think...)... I seem to remember someone
mentioning that mysql gui is discontinued...
CC is available from the mysql.org site, and is very easy to install on windows xp
(it's on my laptop...)
-Original Message-
From: Liwen Han [mailto:[EMAIL
what I've done is
select ceil(value)
(not sure if ceil is the function on MySQL, but there is a ceiling function, I'm
sure...)
-Original Message-
From: Fabio Bernardo [mailto:[EMAIL PROTECTED]
Sent: Friday, September 05, 2003 2:59 PM
To: Mysql (E-mail)
Subject: Round Question
well, that'll teach me not to update my java api bookmarks to 1.4
the getGeneratedKeys() calls works like a charm!
Thanks for the help
-Original Message-
From: Mark Matthews [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 4:09 PM
To: Dan Greene
Cc: [EMAIL PROTECTED
in PHP/MySQL,
Dan Greene
-Original Message-
From: Parker Morse [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 03, 2003 11:43 AM
To: [EMAIL PROTECTED]
Subject: MySQL 4.0.14 stops responding to PHP 4.3.2
I've been using MySQL and PHP for a while in a shared hosting
environment
Ahh... the proverbial 'how many licks does it take to core the database'
There is no straightforward answer. That's the bad news.
However, the basics are identical for all databases
basically, though,
the more memory you have, the more concurrent connections you can handle.
The more
(newbie to MySQL)
I've been banging my head against the wall on this one for a bit now, and I understand
that last_insert_id() is per-connection based, but most webapps are connection pooled
(simple) or clustered (harder). What are my options to get the id of the inserted row
in a webapp? As
I don't know the answer to your question, but as a side note, I've always found
cat'ing /dev/null into a file to be safer if the file may be in use
cat /dev/null foo.log
-Original Message-
From: Jesse Sheidlower [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 10:27 AM
I may be missing something, but why not just do:
SELECT CustomerName, ApplicationName, Status, COUNT(1) AS Count
FROM LogMessage
GROUP BY Status, CustomerName, ApplicationName with rollup;
which should return all the data you need in 1 query, which has got to run faster than
4 seperate
87 matches
Mail list logo
