I have date of birth stored in a DATETIME column and need to find for which
persons a certain date is their birthday.
I've tried so far:
DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec
col LIKE '%-11-07%'= 0.1643 sec
col RLIKE '-11-07'= 0.1702 sec
Are there
On Wed, 17 Nov 2004 21:02:27 -0800 (PST), Mitul Bhammar
[EMAIL PROTECTED] wrote:
The query is running fine for now. I wanted to know
how MySQL interprets and executes this query and can
it have problems in future??
Read about MySQL's query optimization here:
Clif,
I did not know that the mailing list censors email addresses :).
It is heikki dot tuuri at innodb dot com.
All spammers and viruses already know my email address. That is why there is
no harm showing it in public.
Regards,
Heikki
- Original Message -
From: Clif Smith [EMAIL
On Wed, 17 Nov 2004 18:37:37 -0700, Jim McAtee [EMAIL PROTECTED] wrote:
How would this be done if table_2 already exists? It has an
auto_increment field as PK and I want to take all the rows from table_1
and dump them into table_2. The records being copied from table_1 can get
new primary
Hello, I am in the position where I need row level user access, this is
crucial in my current project. I know this has been discussed before and the
answer has been use views when they become availble. But views would still
allow the root user access to the complete table, wouldnt it? I would like
Hi!
In the thread Re: mysqld-nt error 23. Urgent pls you stated that the file
system is corrupt. Thus, the error 995 might result from that.
There are two or three 995 reports from this year. I have suspected a bug in
some disk driver in Windows, but maybe they are caused by file system
Hi
What about encrypting the data using a password that is specific to the
user. That way only those that know the password for that row can access it.
Locking is really to stop two users editing the same record at the same
time, rather than controlling access.
HTH
Peter
-Original
Dear Party Expert
Just a quick email to let you know that PartyPages are now launching its new
online store today. Its also Children in need this week so we thought we would
support a great cause and donate £1 from every transaction that you make to
them.
Whether you are hosting a party,
Yeah you are correct locking is something else I actually meant was
restricted access.
If I understand you correctly I would then encrypt all information in the
table I was interested in restricting access to. But if two or more users
was to share a row in the table they would need a shared key?
Hi
I use a system based on linux securuty model
create groups - this will define access to the data, so you need to group
the data - and encrypt data the group can access using the password
belonging to that group.
make users members of any number of groups, as required.
Users can then access
Ok, jupp if I could use groups each group could have a shared key. How do
you create groups and then add users to them in MySQL? Are you refering to
the Linux systems user and groups? This idea should work but I am not
familiar with how groups work in mysql. I need to be able to audit logs on a
Hi ALL,
We are using MySQL 4.0.21 with InnoDB. For creating the
tablespace mentioned as innodb_data_file_path = ibdata1:10M:autoextend
in my.cnf file. Here, is there a facility to know the table space name?.
Shall we create multiple table spaces like the above in MySQL 4.0.21
and assign
Hi,
I think Peter's suggesting you implement that security model in your
application, rather than in the database server. Apart from probably
not supporting such a security model, it's easier to audit user
activity if you are controlling the security model.
Cheers,
Luke Venediger.
On Thu, 18
On Thu, 18 Nov 2004 10:45:37 +0100, Jonas Ladenfors wrote:
Hello, I am in the position where I need row level user access, this is
crucial in my current project. I know this has been discussed before and the
answer has been use views when they become availble. But views would still
allow the
Well, This is a road I would like to avoid. We have several different
applications fetching data from our MySQL system and constructing a
application security layer is both a lot of work and have to be cusomized
for some of the different applications. Some applications is not constructed
by me and
Yup, you are absolutly correct but my application runs in a closed
enviroment and our average users does not have sniffing/debugging knowledge.
So this might be something I could live without. BUT this is something which
touches on what I said before. That creating a good security system is a
very
On Thu, 18 Nov 2004 13:07:11 +0100, Jonas Ladenfors wrote:
Anyway I was given a link by Mark Leith (thanks!) on Oracle row level access
that seems interesting.
Here it is (not MySQL but the mind-work might be interesting).
http://www.securityfocus.com/infocus/1743
It does not meet your
Greetings All,
The new version 1.4 of microOLAP Database Designer for MySQL has been
released. Since now it supports the new features of MySQL 5.x: stored
procedures and functions.
What's new:
[+] Added support of MySQL 5.x stored procedures and functions.
[+] Added reverse engineering of
Hello.
See:
http://dev.mysql.com/doc/mysql/en/Windows_client_compiling.html
Karam Chand [EMAIL PROTECTED] wrote:
Hello,
Is it possible to get static library for MySQL C API()
instead of libmysql.dll?
Regards,
Karam
Hi
yes that is a loophole
you could encrypt the passwords using a password held in the software
so
SELECT DECODE(g.datapasword ,system pass supplied by software)
FROM `User` u, `Groups` g, `Groups_user_link` l
WHERE
u.userID = users ID here
AND u.userID = l.userID
AND l.groupID =
[snip]
How do I copy all data only from one table into another table? Both
tables
are in the same database. I have phpMyAdmin and it suppossedly does
this,
but it is not working, and there are no error messages.
[/snip]
INSERT INTO `table2` (`columnnames`)
SELECT `columnnames`
FROM `table1`
://www.upscene.com
Features and fixes: http://www.upscene.com/news/20041118.htm
Database Workbench supports:
- Borland InterBase ( v4.x - v7.x )
- Firebird ( v1.x )
- MS SQL Server/MSDE ( v6.5, 7, 2000, MSDE 1 2 )
- MySQL 4, 4.1
If you experience any problems with this new version, don't
hestitate and either
Thanks.
We haev to compile it with mysqlclient.lib
I just the binary distribution of MySQL 4.1.7 for
Windows. The size of mysqlclient.lib is 1.5MB whereas
libmysql.dll is 1.04MB and libmysql.lib is around 33K.
Why is the difference in size?
Regards,
Karam
--- Gleb Paharenko [EMAIL PROTECTED]
Whilst we're using 4.1.4 for most of our development, on e of our developers
who works remotely has been using 3.23.52 for his development. In trying to
migrate his database onto 4.1.4 (or 4.1.7) we appear to have hit an
introduce restriction in 4.1.x.
It appears that the length of the column
Hi!
On Nov 18, Jonas Ladenfors wrote:
Hello, I am in the position where I need row level user access, this is
crucial in my current project. I know this has been discussed before and the
answer has been use views when they become availble. But views would still
allow the root user access to
- Original Message -
From: Jay Blanchard [EMAIL PROTECTED]
To: Chip Wiegand [EMAIL PROTECTED]; MySQL List
[EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 8:10 AM
Subject: RE: copy data only from one table to another table
[snip]
How do I copy all data only from one table into
Yeah, What i am referring to is rather the Global access options in MySQL.
I have one root user with full global access for administration
purposes. An option would be to remove the root user and let every user
including the root user have access on table level instead of global
access.
But I
Hi,
I need to expand the database to 3 different locations. We have 3 servers in
3 cities. So far the database has been in one city and 2 others have been
linked to it and worked. But sometimes for a day or more a city lost the
connection to the master database and the users could not work. Now
[snip]
INSERT INTO `table2` (`columnnames`)
SELECT `columnnames`
FROM `table1`
If this is valid SQL surely grave accents are not?
[/snip]
Actually? MySQL supports the use of grave accents around table and
column names. I use them here for emphasis. In certain cases, with older
versions of MySQL,
Mojtaba Faridzad [EMAIL PROTECTED] wrote on 18/11/2004
13:59:21:
Hi,
I need to expand the database to 3 different locations. We have 3
servers in
3 cities. So far the database has been in one city and 2 others have
been
linked to it and worked. But sometimes for a day or more a city lost
I worked on a military system that went further than this, but again
required a proprietary application to perform the updates.
Databases were either slave, master, of standalone. Any update transaction
was logged to a file. If the master was available then
If we are the master we apply the
Thanks Alec,
how is a bank system implemented? do they have just one master sever and all
the other servers are slaves?
you solution is not bad and I should think more about it. it's close to one
of my solutions: I should convert the database to InnoDB. when a user in
location A needs to
- Original Message -
From: Jay Blanchard [EMAIL PROTECTED]
To: Jonathan Mangin [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 9:19 AM
Subject: RE: copy data only from one table to another table
[snip]
INSERT INTO `table2` (`columnnames`)
SELECT `columnnames`
[snip]
[snip]
INSERT INTO `table2` (`columnnames`)
SELECT `columnnames`
FROM `table1`
If this is valid SQL surely grave accents are not?
[/snip]
Actually? MySQL supports the use of grave accents around table and
column names. I use them here for emphasis. In certain cases, with older
versions of
Really what you are trying to do is search on month + day, not a date.
For special dates (birthday, anniversary, etc), I always store the
year separately. Especially since some people don't really want you to
know how old they are.
Without breaking the date up into it's separate parts, you
At 03:45 AM 11/18/2004, you wrote:
Hello, I am in the position where I need row level user access, this is
crucial in my current project. I know this has been discussed before and the
answer has been use views when they become availble. But views would still
allow the root user access to the
[snip]
Really what you are trying to do is search on month + day, not a date.
For special dates (birthday, anniversary, etc), I always store the
year separately. Especially since some people don't really want you to
know how old they are.
Without breaking the date up into it's separate parts,
A semi-generic solution:
You have tableA with columns (col1, col2, col3, ..., colN) where col1 is
an auto_increment column.
You have tableB that looks just like tableA except for some additional
columns (extra1, extra2, col1, col2, col3, ..., colN, extra3, extra4).
TableB is intended to act
Thanks Kevin! that's a good method. but in this method we should not have
any AUTO_INCREMENT field in tables and a master table should take care of
giving a unique key. because at first we should add a record to a table then
mysql gives the key. if it's a slave the master is off, then we will
- Original Message -
From: Jigal van Hemert [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 4:28 AM
Subject: efficient query for: it's your birthday today
I have date of birth stored in a DATETIME column and need to find for
which
persons a certain date is
I'm not even sure what this would be called, but maybe
someone (or more then one) can give me some pointers
and where to learn how this is down:
Someone does a search on my system:
1- I need a way to mark (somewhere) that the record
came up in a search. i.e. 700 records were returned
in a
From: Brent Baisley [EMAIL PROTECTED]
Without breaking the date up into it's separate parts, you can't use
an index, so you will always do a full table scan. Your searches will
get slower as you add more records.
That was what I feared; I was just hoping that MySQL wouldn't treat the
DATETIME
[snip]
1- I need a way to mark (somewhere) that the record
came up in a search. i.e. 700 records were returned
in a search, each one needs to a) be marked that it
came up in a search b) that number needs to be
incremented
2- Step further, out of those 700 records, user
chooses to view details
From: Jay Blanchard [EMAIL PROTECTED]
So if I index a date field (given -mm-dd) and then
SELECT `userNames`
FROM `userTable`
WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5)
it is still very fast. I have a table with several thousand records in
it and I get back ...
30
[snip]
From: Jay Blanchard [EMAIL PROTECTED]
So if I index a date field (given -mm-dd) and then
SELECT `userNames`
FROM `userTable`
WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5)
it is still very fast. I have a table with several thousand records in
it and I get back
Clif,
I have analyzed some of the hex dumps now:
First there was corruption in the buffer pool:
041001 21:44:51 InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.21-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306
Official MySQL RPM
InnoDB: Next record offset is
From: Rhino [EMAIL PROTECTED]
I don't know if it is more efficient but I would write this query as
follows, simply because it is easier to understand what it is doing when
you
look at it:
select [whatever columns you want]
from mytable
where month(birthdate) = 11
and dayofmonth(birthdate)
--- Jay Blanchard
[EMAIL PROTECTED] wrote:
[snip]
1- I need a way to mark (somewhere) that the record
came up in a search. i.e. 700 records were returned
in a search, each one needs to a) be marked that it
came up in a search b) that number needs to be
incremented
2- Step further, out
[snip]
I'm thinking this through. In other words, when a
record is returned in a search , and insert statement
makes an entry into another table? This will involve
scripting as well as sql statements ?
[/snip]
Well, you could use triggers and stored procedures, but yeyou are
going to have
You don't have to replicate the data, and you shouldn't. Since you will
be searching on parts of the data, you may want to store it in parts.
Then you can create indexes to merge the data for searching.
For instance, you use three fields for storage: year, month, day. The
you can create two
It depends on whether you are tracking info for individual users or
just on a global basis. On a global basis, you just need to run an
update query:
UPDATE dbTable SET searchCount=searchCount+1 WHERE queryparams
The default value for searchCount would be 0, if the record never came
up in a
Hello
I have just installed mysql 4.1.7 on Mandrake 10.0 (final).
All went smoothly.
However, when I issue the following command:
/usr/local/mysql/bin/mysqld_safe --user=mysql
I get the error:
Can't create/write to file /root/tmp/ibLmfg7w (Errcode: 13)
--- Brent Baisley [EMAIL PROTECTED] wrote:
It depends on whether you are tracking info for
individual users
Individual users (lucky me!)
Now, if you want to keep separate logs for each
user, you need to
create a log table. The log table would have the
following fields:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be for
11/7 would be
doycol=DAYOFYEAR(2004-11-07)
or
doycol=312
E
Jigal van Hemert [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I have date of
Hi all
I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE =
INNODB. There was no errors in the err log and database is working ok.
But after stopping mysql server It refuse to start again complaining:
041118 16:55:45 mysqld started
041118 16:55:45 [Warning] Asked for
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQLselect dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
Hello
I have just installed mysql 4.1.7 on Mandrake 10.0 (final).
All went smoothly.
However, when I issue the following command:
/usr/local/mysql/bin/mysqld_safe --user=mysql
I get the error:
Can't create/write to file /root/tmp/ibLmfg7w (Errcode: 13)
When
Yup, very good point. I am the one who is sorry. My bad.
E
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 1:06 PM
To: McGrane, Eric
Cc: [EMAIL PROTECTED]
Subject: Re: efficient query for: it's your birthday today
Good
Although parentUserId is indexed, the fact that you're using an IN list
turns the query into a range. IN lists are fast but at certain levels such
as yours it is not. The reasons are listed below:
The query parser must allocate memory for every string in the list and
convert it into an int. So
When ever you use INNODB it must create a table space, something like a
virtual file space or system for the data, that sits on top of the OS
filesystem. By default it has allocated a certain size, change the value of
innodb_data_file_path = ibdata1:500M:autoextend:max:2000M
500M back to the
perror 13
System error: 13 = Permission denied
The mysql user cannot read the /root filesystem.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Gail Lange [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 10:23 AM
To: [EMAIL
Hello,
I was wondering if a more knowledgeable person could help me out with my
configuration and let me know how I could further optimize MySQL. Here's the
hardware on my dedicated server:
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz
Processor #1
Sorry, logTable is just an arbitrary name I came up with. You can name
the table anything you want, like SearchViewTracking.
On Nov 18, 2004, at 12:26 PM, Stuart Felenstein wrote:
--- Brent Baisley [EMAIL PROTECTED] wrote:
It depends on whether you are tracking info for
individual users
It can be even simpler than that, if you stay withing the same branch.
Just replace mysqld and share/english/errmsg.sys ( or share/your
langauge/errmsg.sys if you want them in your native language) with
the files from the new version.
This is for the source, binary or both? Sorry to ask
Przemyslaw Popielarski wrote:
Sasha Pachev [EMAIL PROTECTED] wrote:
Check if you have any replication restricting rules on the slave.
There might be a bug that incorrectly flags a query to be excluded.
If that is the case, then try to re-write the rules to see if you can
get around the bug.
Yes.
Shankar,
Thanks for the input. I understand the route you suggest, but it
doesn't get at the heart of my issue. The info I'm interested in isn't
really about my particular app performance. I'm looking for issues in
regards to how the MySQL code executes against Solaris vs. Linux.
I have
- Original Message -
From: [EMAIL PROTECTED]
To: Jim McAtee [EMAIL PROTECTED]
Cc: MySQL List [EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 8:28 AM
Subject: Re: copy data only from one table to another table
A semi-generic solution:
You have tableA with columns (col1, col2, col3,
Hristo,
thank you for the bug report. I broke the
:autoextend:max:2000M
feature when I added multiple tablespaces in 4.1.1.
The bug is fixed in 4.1.8.
Unfortunately, InnoDB does not automatically add 2000 MB files if you
specify the max. You have to add additional ibdata files manually, as
Sasha Pachev wrote:
It can be even simpler than that, if you stay withing the same
branch. Just replace mysqld and share/english/errmsg.sys ( or
share/your langauge/errmsg.sys if you want them in your native
language) with the files from the new version.
This is for the source, binary or
Look at
Created_tmp_disk_tables 14768
Created_tmp_tables 269520
Created_tmp_files 3
Increase tmp_table_size = 64M:
it's used to stop going to disk and some internal mysql operations.
Handler_read_rnd_next 58229817
Your tables are not
Hello,
I have a query that works fine on 4.0.20 but doesn't work in 4.1.7.
I want to search all the rows of table AULE that don't have a record
in table OCCUPAZIONI so the query is:
select AUL_ID, OCC_ID from
AULE
left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10'
where
Shaun wrote:
Hello,
I was wondering if a more knowledgeable person could help me out with my
configuration and let me know how I could further optimize MySQL. Here's the
hardware on my dedicated server:
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz
Store month*100+day
1999-03-01 -- 301
you can also say it's your birthday today when 02/29 doesn't exists!
Santino
At 13:05 -0500 18-11-2004, [EMAIL PROTECTED] wrote:
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQLselect dayofyear('1999-03-01'),
Thanks for your help Dathan, I will make the required changes. I just have
one other problem.
I'm not sure what queries don't use an index. I've attmepted to turn on the
slow-queries-log, but nothing ever shows up in the file. Here's what I have
in the my.cnf
[mysqld]
skip-locking
set-variable
Shaun wrote:
Thanks for your help Dathan, I will make the required changes. I just have
one other problem.
I'm not sure what queries don't use an index. I've attmepted to turn on the
slow-queries-log, but nothing ever shows up in the file. Here's what I have
in the my.cnf
[mysqld]
skip-locking
So all I have to do, using the binary, is to take the mysqld file and
replace my old one (from 4.1.3 to 4.1.7, for example).
And for the source, I compile in another directory, then take the mysqld
file and replace my old one?
Is that documented somewhere?
Do not forget to replace errmsg.sys
Thanks, I changed the permissions, restarted, and everything is working
great now. Thanks a lot.
Shaun wrote:
Thanks for your help Dathan, I will make the required changes. I just
have
one other problem.
I'm not sure what queries don't use an index. I've attmepted to turn on
the
When you set a field to auto-increment, can you tell it where to start?
I'm trying to set up multimaster replication, but I'm worried about
auto-increment collisions.
Q: If server A starts auto-incrementing at 0, and server B starts
auto-incrementing at some point higher than the maximum
Can someone explain how I can make a combination of two fields be a
unique index.
For example, in my table I have an OrderID field and a LineItems field.
Individually the fields are not unique but when combined they are. I'd
like to create a unique index of the two together but not
5001
Peter
-Original Message-
From: Robinson, Eric [mailto:[EMAIL PROTECTED]
Sent: 18 November 2004 21:35
To: [EMAIL PROTECTED]
Subject: Auto-Increment Starting Point? (Multimaster Replication
Question)
When you set a field to auto-increment, can you tell it where to start?
Dear list,
i have some small tables but for some reason the mysql took very long to
find the results. my query looks
like below and mysql'e explain is attached for better format. Thanks for
your help!
select teu.name, eca.owner_id, ece.value
fromtyped_enterprise_unit teu,
ALTER TABLE tablename ADD UNIQUE (Column1, Column2);
Best Regards,
Andrew
Can someone explain how I can make a combination of two fields be a
unique index.
For example, in my table I have an OrderID field and a LineItems field.
Individually the fields are not unique but when combined they
Hello,
i have 2 questions about Replication.
1) I managed to make one slave and one master. The replication works fine and
it updates only one table from one database (cause of the
replicate-do-table=db.table1). The first question is can I tell mysql that
table1 from master to be named table2
Julian,
maybe the secondary index tree LOGIN_NAME is so small that it fits
completely in the buffer pool? Then there is no file I/O, and the scan is
faster.
For this reason, COUNT(secondary_index_column) can be faster than COUNT(*),
which is performed on the PRIMARY, clustered index.
Best
Lakshmi,
- Original Message -
From: [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, November 18, 2004 1:07 PM
Subject: Reg Table spaces and Rollback segments in MySQL 4.0.21
Hi ALL,
We are using MySQL 4.0.21 with InnoDB. For creating the
tablespace mentioned
Can anyone tell me why this makes sense? I have a SELECT which uses an indexed
datetime field called Start with a BETWEEN range. If I select on this with no
LIMIT, it does a full scan of the 9391282 records in the DB (key=NULL).
However, if I do a limit of any value LESS than the number of
There have been a couple of threads about the lack of spatial support with
InnoDB tables. What is the timeline for its implementation? Paul?
thanks in advance, Chuck
At 21:02 -0800 11/18/04, Chuck Han wrote:
There have been a couple of threads about the lack of spatial
support with InnoDB tables. What is the timeline for its
implementation? Paul?
Dunno.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL
Im trying not to list the rows that has zero values.
this is my current select statement:
select id,country_city from rates_ocean order by country_city
The fields that i have to detect are:
fieldvalue1
fieldvalue2
fieldvalue3
how can i do this on mysql, ex:
select country from rates (if
89 matches
Mail list logo
