Function to show when a field's value was last updated
Hi, I need it some info to help a client defend against a legal challenge. Is there a MySQL function that will allow me to ascertain the date and time that a particular field's value was last updated. I can't find anything in the MySQL documentation. Thanks very much. Mark -- Fast, reliable, web hosting. http://www.loosesparks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
--- Kishore Jalleda [EMAIL PROTECTED] wrote: check the permissions on the mysql data dir, may be the user mysql or who ever runs mysql does not have sufficient privileges Kishore Jalleda I agree with Kishore, to elaborate his answer a little longer I would ask the following. - is the hardware all ok? - You said you created the table before, so u must had have access to the directories from the OS prespective, did you use the same method to connect to the server at the time of creation and at the time to alter it? - did u alter the tablespaces location? the configuration files? I am not 100% familiar with the Windows OS but I would expect the full path to the files not a relative one in the error message. - can u select anything from the different tables? - did you use the same userid, passsword and connection method to create the database and to try to alter it? Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's ||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson || __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with foreign keys, new to mysql
ps, I meant to delete the note from my draft folder... sorry Enrique Sanchez. Enrique Sanchez Vela email: [EMAIL PROTECTED] - It's often easier to fight for one's ||We live in the outer space principles than to live up to them||Rev. Kay Greenleaf Adlai Stevenson || __ Yahoo! Music Unlimited Access over 1 million songs. Try it free. http://music.yahoo.com/unlimited/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query help
Hello, CREATE TABLE t ( dt datetime ) Please assume this is the table structure and contains thousands of records. And I want to list them only last 12 months (that mean last 1 year) records exactly. For that I tried using the following query, but it list sometimes 13 months when the current date is in the middle of the month. SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); I request you to help me. And thanking you for the consideration. Sincerely, R.Kumaran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: move log files to a different disk.
Hi Green, Thanks for guiding me to the manul, i some how missed this section of the manual.I was more keen on adding and removing log files url. Thanks once again. regards anandkl On 10/18/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Ananda Kumar [EMAIL PROTECTED] wrote on 10/17/2005 09:16:25 AM: Hi Friends, Any help on this would be of great help. regards anandkl On 10/17/05, Ananda Kumar [EMAIL PROTECTED] wrote: Hi All, I am using version 5 of mysql on lunix. I have place my log files in the same disk of datafiles, can you please let me know how i can move log files into a different disk. regards anandkl Did you RTFM? http://dev.mysql.com/doc/refman/5.0/en/server-options.html http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html http://dev.mysql.com/doc/refman/5.0/en/innodb-start.html These all have options to control where different log files wind up. You didn't say which log files you wanted to move so I had to give you all of them... Shawn Green Database Administrator Unimin Corporation - Spruce Pine
query help
Hello, CREATE TABLE t ( dt datetime ) Please assume this is the table structure and contains thousands of records. And I want to list them only last 12 months (that mean last 1 year) records exactly. For that I tried using the following query, but it list sometimes 13 months when the current date is in the middle of the month. SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); I request you to help me. And thanking you for the consideration. Sincerely, R.Kumaran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restore of mysqldump-ed data is corrupted
I made backup of database issuing 'mysqldump --opt --skip-extended-insert' from command line on 4.1.5-gamma-log, FreeBSD. When I try to restore data from backup into same server, data is corrupted! How is this possible? What is the purpose of this program if it cannot restore the very same data that he stored on the very same server!? This is the same problem that Delyan Toshev reported on September 18 2005 4:27pm but that post is still unanswered: Hi, Database is MySQL 4.1.5-gamma, running on Linux. All charset variables are set to default 'latin1', except 'character_set_system', which is utf8. A database was created and running in this environment, but the data it gets (from php) were in utf8. Mysqldump is used without any options to export database to sql text file. When importing back into the same database some characters (non latin,cyrillic in fact) get broken. These are exactly 4 characters that broke, but it is no fun, you know. Any combination of charset setings and database creation both from mysql client and server did not work over this problem. Now my question is: what is the encoding that mysqldump puts data in? I cannot decode it with anything! To me it looks like UTF8, but not quite. Somebody mentioned on this list before, that when nothing is specified when dumping data that is not in the default character set, mysqldump insert something other?! What is this exactly? It seems strange, because importing back only few characters are broken, other non-latin are intact. But it is not working OK, in fact it made the data corrupted. Exporting back after import confirm this, since the corrupted characters are replaced by one and the same combination - 0xFFED0 0x3F - for capital letters (0x0410, 0x41D), and 0xFFED1 0x3F for small letters 0x0441 and 0x044F. I know that database and dump should be with specified correct charset, but I look into way to recover in this case, since data are already corrupted and original database is not available. Thanks, Delyan
Re: Function to show when a field's value was last updated
Unfortunately that would have to be designed into the system into a field. When updated, the instructions would be to instruct that field to update. Cheers Mark: I need it some info to help a client defend against a legal challenge. Is there a MySQL function that will allow me to ascertain the date and time that a particular field's value was last updated. I can't find anything in the MySQL documentation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Function to show when a field's value was last updated
news [EMAIL PROTECTED] wrote on 17/10/2005 15:45:15: I need it some info to help a client defend against a legal challenge. Is there a MySQL function that will allow me to ascertain the date and time that a particular field's value was last updated. I can't find anything in the MySQL documentation. It is almost certainly not possible. If you look in the manual for the storage space occupied by each field, you will see there is no space to store any form of timestamp. Since MySQL does not store the data you want, it cannot extract it for you. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql connectivity error
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/can-not-connect-to-server.html Hiu Yen Onn wrote: hi all, i have a mysql which only localhost can be logged in. if i have created an account [EMAIL PROTECTED], then, i cant login in as that host. mysql -u user -h mymachine.com -p ERROR 2003 (HY000) Cant connect to the MySQL server on 'x.x.x.x' (111) please advise. thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. I don't think that it is possible to recover the data from the new table, because ALTER operation creates a new table and fills it with data (and truncates the data). But why did the system backup not help you? Could you describe the situation more in detail. If you haven't flushed you binary logs, you may want to make an attempt to recover the data from them. Jim Seymour wrote: My bad. I was renaming some columns in a table. I incorrectly set the type to decimal(4,2) and the data was truncated/hosed. Is there a way to recover the data. I tried a system backup from yesterday. That changed nothing. I have already set the column type back to the correct settings. I am running MySQL v5.0.13 on a Debian Etch(Testing) box. TIA, Jim Seymour -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: configuration directives 4.0.18
Hello. I strongly recommend you to upgrade to the latest release (4.1.14 now). If you still want the manual for you version, perhaps you could get it from the appropriate distribution from: http://downloads.mysql.com/archives.php Luke Vanderfluit wrote: Hi. Where can I find a good explanation/description of the configuration directives for mysql as they appear in my.cnf? I've downloaded the 4.1 manual but can't seem to find a description of the directives. BTW. Is there a manual for 4.0.18 or 4.0 version? Thanks. Kind regards. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help me please
Hello. The start point for you in such kind of problems should be researching the output of the following statement: SHOW VARIABLES LIKE '%char%'; Please, run it from different environments (PHP, MySQL Query Browser), and send results to the list. Include the CREATE statement for you tables which hold Latvian characters. Aleksejs Pavlans wrote: Hellou! I have MySQL(charset is utf-8) + PHP 5.0.5. Latvian symbols(auczsnsczikl) not correctly viewed in MySQL Query Browser, but in 'phpmyadmin' its ok. When i use php to viewed data with latvian symbols in IE, then latvian symbols(aui.) is converted to ? symbols. Please Help! Ar cienu, Aleksejs! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb buffer pool allocation on Window 2000
Hello. It is a bit strange, usually MySQL should be able allocate up to 2G of memory. Check that summary memory usage of MySQL is less than 2G. You may be interested in PAE extention. But to be able to use it you should compile MySQL yourself. See: http://www.microsoft.com/whdc/system/platform/server/PAE/PAEmem.mspx http://dev.mysql.com/doc/mysql/en/Memory_use.html http://lists.mysql.com/mysql/185635 Jonathan Stockley [EMAIL PROTECTED] wrote: I have a Windows 2000 host with 3.5 G of ram. I have configured MySQL 4.1.14 with innodb_buffer_pool = 1024M, when I start mysqld I get the following error: 051013 16:46:00 InnoDB: Error: cannot allocate 1073758208 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 16975556 bytes. Operating system errno: 8 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We keep retrying the allocation for 60 seconds... InnoDB: Fatal error: cannot allocate the memory for the buffer pool 051013 16:47:00 [ERROR] Can't init databases 051013 16:47:00 [ERROR] Aborting 051013 16:47:01 [Note] C:\Program Files\MySQL\bin\mysqld: Shutdown complete If I lower the value to 512M it comes up ok. Not being an in depth Windows admin, What do I look for in the registry or elsewhere to allow MySQL to have a 1024M innodb_buffer_pool? Thanks, Jo -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AW: limited threads to two but 25 waiting?!
Hello. Very often InnoDB provides a better performance in environment where a lot of concurrent SELECTs and UPDATEs. Check if the problem still exists if you change the engine of you tables from MyISAM to InnoDB. M.E. Koch wrote: Hello: After haveing a look at the `show processlist` I realized that querys will be locked if there is an update or insert statement otherwise the `STATUS` is as regular copying to temp, sorting etc. So that not all querys will be locked automatically. (But when checking there where lots of insert intos so that every query seemd to be locked) some select querys `SELECT kk2.k_id FROM katalog_katalog kk LEFT JOIN katalog_katalog kk1 ON k_id=kk1.kat_id LEFT JOIN katalog_katalog kk2 ON kk1.k_kat_id=kk2.kat_id WHERE kk.kat_id=34678 AND kk1.k_kategorie_id=56 AND kk2.k_kategorie_id=24; An insert into/update looks like this: INSERT INTO katalog SET kategorie_id=36, titel=foo foo bar, artikel=bar bar bar foo The Update gets an additonal `WHERE id=1234` about the create statements: Two of many tables hopefully give an insight //- snip CREATE TABLE `katalog` ( `id` int(11) NOT NULL auto_increment, `kategorie_id` int(11) NOT NULL default '0', `datum` datetime NOT NULL default '-00-00 00:00:00', `titel` varchar(200) collate latin1_german2_ci default NULL, `untertitel` text collate latin1_german2_ci, `einleitung` text collate latin1_german2_ci, `artikel` text collate latin1_german2_ci, `quelle` text collate latin1_german2_ci, `bild_id` int(11) default NULL, `txt1` text collate latin1_german2_ci, `txt2` text collate latin1_german2_ci, `txt3` text collate latin1_german2_ci, `txt4` text collate latin1_german2_ci, PRIMARY KEY (`id`), KEY `kategorie_id_idx` (`kategorie_id`), KEY `datum_idx` (`datum`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1 ; and CREATE TABLE `katalog_katalog` ( `kat_id` int(11) NOT NULL default '0', `kategorie_id` int(11) NOT NULL default '0', `k_id` int(11) NOT NULL default '0', `k_kategorie_id` int(11) NOT NULL default '0', KEY `kat_id_idx` (`kat_id`), KEY `kategorie_id_idx` (`kategorie_id`), KEY `k_id_idx` (`k_id`), KEY `k_kategorie_id_idx` (`k_kategorie_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci PACK_KEYS=1; -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL connection error
Hello.
It is a bit confusing for me. Really, MySQL has all permissions for
reading those files. Do you have SELinux enabled? Sometimes it is
the source of the problems, you should have it properly configured.
Israel Fern$ndez Cabrera [EMAIL PROTECTED] wrote:
List: MySQL General Discussion« Previous Message
From: Israel Fernández Cabrera Date: October 17 2005 10:18pm
Subject: SSL connection error
Get Plain Text
Hi all
I'm back with a new subject may be the last one was not attractive :)
I'm using mysql 4.1.11-2 in Fedora Core 4. I need to set up mysql
connections over SLL I follow the mysql manual instructions, create
certificates and keys for the CA, the client and the server and modify
the /etc/my.cnf file with the ssl-ca, ssl-cert and ssl-key for the
client and the mysqld sections of the my.cnf file.
My problem is that mysqld log an error describing that it has no
permission to read the certificate file, I've been with this for more
than 3 days.
I'm attaching ls output, my.cnf file, mysqld.log file and a fragment
of the mysqld strace output with the open syscall returning error.
Thanks in advance for your time and interest
best regards
--
Israel Fdez. Cabrera
[EMAIL PROTECTED]
#ls / | grep etc
drwxr-xr-x 83 root root 12288 Oct 15 16:50 etc
#ls /etc | grep pki
drwxr-xr-x 7 root root4096 Oct 14 17:51 pki
#ls /etc/pki
total 104
drwxr-xr-x 3 root root 4096 Oct 14 21:46 CA
drwxr-xr-x 3 root root 4096 Oct 8 16:54 dovecot
-rwxr-xr-x 1 root root 1088 Oct 8 16:54 gencert.sh
-rwxr-xr-x 1 root root 1056 Oct 8 16:54 gencert.sh~
-rw-r--r-- 1 root root 236 Oct 8 16:54 index.txt
-rw-r--r-- 1 root root 21 Oct 8 16:54 index.txt.attr
-rw-r--r-- 1 root root 21 Oct 8 16:54 index.txt.attr.old
-rw-r--r-- 1 root root 118 Oct 8 16:54 index.txt.old
drwxr-xr-x 2 root root 4096 Oct 8 16:54 newcerts
drwxr-xr-x 2 root root 4096 Oct 8 16:54 rpm-gpg
-rw-r--r-- 1 root root3 Oct 8 16:54 serial
-rw-r--r-- 1 root root3 Oct 8 16:54 serial.old
drwxr-xr-x 5 root root 4096 Oct 14 17:51 tls
#ls /etc/pki/tls
total 40
lrwxrwxrwx 1 root root 19 Oct 8 16:54 cert.pem - certs/ca-bundle.crt
drwxr-xr-x 2 root root 4096 Oct 15 14:18 certs
drwxr-xr-x 2 root root 4096 Oct 8 16:54 misc
-r--r--r-- 1 root root 7998 Oct 14 17:59 openssl.cnf
drwxr-xr-x 2 root root 4096 Oct 8 16:54 private
#ls /etc/pki/tls/certs
total 492
-rw-r--r-- 1 root root 427833 Oct 8 16:54 ca-bundle.crt
-rw-r--r-- 1 root root3617 Oct 14 21:46 client-cert.pem
-rw-r--r-- 1 root mysql887 Oct 8 16:54 client-key.pem
-rw-r--r-- 1 root mysql769 Oct 8 16:54 client-req.pem
-rw-r--r-- 1 root root 610 Oct 8 16:54 make-dummy-cert
-rw-r--r-- 1 root root2240 Oct 8 16:54 Makefile
-rw-r--r-- 1 root root3617 Oct 14 21:46 server-cert.pem
-rw-r--r-- 1 root root 887 Oct 14 21:46 server-key.pem
-rw-r--r-- 1 root mysql769 Oct 8 16:54 server-req.pem
open(/etc/pki/tls/certs/server-cert.pem, O_RDONLY) = -1 EACCES (Permission
denied)
write(2, Error when connection to server ..., 42) = 42
write(2, 1872:error:0200100D:system libra..., 122) = 122
write(2, 1872:error:20074002:BIO routines..., 70) = 70
write(2, 1872:error:140AD002:SSL routines..., 88) = 88
write(2, Unable to get certificate from \'..., 68) = 68
open(/etc/pki/CA/cacert.pem, O_RDONLY) = -1 EACCES (Permission denied)
open(/etc/pki/tls/cert.pem, O_RDONLY) = -1 EACCES (Permission denied)
time([1129246383]) = 1129246383
open(/dev/urandom, O_RDONLY|O_NONBLOCK|O_NOCTTY) = -1 EACCES (Permission
denied)
open(/dev/random, O_RDONLY|O_NONBLOCK|O_NOCTTY) = -1 EACCES (Permission
denied)
open(/dev/srandom, O_RDONLY|O_NONBLOCK|O_NOCTTY) = -1 ENOENT (No such file
or directory)
socket(PF_FILE, SOCK_STREAM, 0) = 3
connect(3, {sa_family=AF_FILE, path=/var/run/egd-pool}, 19) = -1 ENOENT (No
such file or
directory)
close(3)= 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
Thread
* SSL connection error - Israel Fernández Cabrera, October 17 2005 10:17pm
© 1995-2004 MySQL AB. All rights reserved.
* MySQL.com Home
* Site Map
* Contact Us
* Privacy Policy
* Trademark Info
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange error in query with joins and subquery
(response interspersed)
Jasper Bryant-Greene [EMAIL PROTECTED] wrote on 10/17/2005 07:49:36 PM:
Just recently (possibly since upgrading to MySQL 5.0.13 RC), I've been
getting the following error with queries like the one below.
Unknown column 'photos.id' in 'on clause' (1054)
SELECT photos.*
FROM photos
LEFT JOIN tags_photos ON tags_photos.photo = photos.id
^
I suspect that the error isn't here -
WHERE 1
AND tags_photos.tag IN (6)
AND NOT EXISTS (
SELECT 1
FROM tags AS t
LEFT OUTER JOIN tags_photos AS t2 ON (
t.id = t2.tag AND t2.photo = photos.id
^
it's here -
)
WHERE t.id IN (6)
AND t2.tag IS NULL
)
GROUP BY photos.id
This was working previously, and there is definitely an `id` column in
the `photos` table. The table definitions follow:
CREATE TABLE `photos` (
`id` int(10) unsigned NOT NULL auto_increment,
`filename` varchar(100) NOT NULL,
`caption` varchar(200) NOT NULL,
`description` text NOT NULL,
`user` int(10) unsigned NOT NULL default '0',
`exif` longtext NOT NULL,
`uploaded` int(10) unsigned NOT NULL default '0',
`modified` int(10) unsigned NOT NULL,
`privacy`
enum('public','friends-family','friends','family','private') NOT NULL
default 'public',
`rights` enum('copyright','creative-commons','public-domain') NOT
NULL default 'copyright',
`notes` text NOT NULL,
`offensive` tinyint(1) unsigned NOT NULL,
`resizeMode` varchar(5) NOT NULL,
`allowOriginal` tinyint(1) NOT NULL default '1',
`licenseURL` varchar(200) NOT NULL,
`licenseName` varchar(50) NOT NULL,
`licenseButton` varchar(250) NOT NULL,
PRIMARY KEY (`id`),
KEY `user` (`user`),
KEY `uploaded` (`uploaded`),
KEY `privacy` (`privacy`),
KEY `modified` (`modified`),
CONSTRAINT `photos_ibfk_1` FOREIGN KEY (`user`) REFERENCES `users`
(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `tags_photos` (
`tag` int(10) unsigned NOT NULL default '0',
`photo` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`tag`,`photo`),
KEY `photo` (`photo`),
CONSTRAINT `tags_photos_ibfk_1` FOREIGN KEY (`tag`) REFERENCES `tags`
(`id`),
CONSTRAINT `tags_photos_ibfk_2` FOREIGN KEY (`photo`) REFERENCES
`photos` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `tags` (
`id` int(10) unsigned NOT NULL auto_increment,
`tagName` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
In this case, I don't think that the query parser is correctly parsing
your EXISTS subquery within the context of the outer query. It's probably
a bug and you should submit a test case for resolution. To get your query
to work in the mean time, don't use subqueries use joins. I *think* you
are looking for photos that are not tagged so here is how I would re-write
your query. Let me know if I guessed incorrectly.
SELECT DISTINCT photos.*
FROM photos
LEFT JOIN tags_photos tp
ON tp.photo = photos.id
LEFT JOIN tags t
on tp.tag = t.id
WHERE tags_photos.tag IN (6)
AND t.id IS NULL;
I also made a few editorial changes:
1) using a GROUP BY with no aggregate functions (sum(), avg(), min(),
max(), etc) is illogical as your results will be indeterminate. If you do
not want duplicate rows, you have two options: deduplicate your data or
use the DISTINCT modifier.
2) WHERE 1 is always true so it plays no useful part in your WHERE
clause. I removed it.
3) Technically, the IN comparator should be used with lists of values and
an = should be used for comparing single values. However, I understand the
pattern you are using and single values are legal in an IN comparison is
allowed on MySQL (not so with other RDBMSs) so it can stay.
As I said above, if I didn't divine the correct question your query was
designed to answer, just fill me in and I will happily rewrite the query
to fit.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Function to show when a field's value was last updated
I need it some info to help a client defend against a legal challenge. Is there a MySQL function that will allow me to ascertain the date and time that a particular field's value was last updated. I can't find anything in the MySQL documentation. MySQL doesn't store change histories. That's the job of the application software. PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Creating a table on MySQL from Access
Is there a way I can create a table on MySQL using Access and establish a link remotely? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help
[EMAIL PROTECTED] wrote on 10/18/2005 06:28:03 AM: Hello, CREATE TABLE t ( dt datetime ) Please assume this is the table structure and contains thousands of records. And I want to list them only last 12 months (that mean last 1 year) records exactly. For that I tried using the following query, but it list sometimes 13 months when the current date is in the middle of the month. SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); I request you to help me. And thanking you for the consideration. Sincerely, R.Kumaran Assume for a moment that 1 CALENDAR YEAR = 12 months = 365 DAYS = 8760 HOURS = 525600 MINUTES = 31536000 seconds (ignoring leap years) The following calculation is correct: mysqlSELECT NOW(), NOW() - INTERVAL 1 YEAR; +-+-+ | NOW() | NOW() - INTERVAL 1 YEAR | +-+-+ | 2005-10-18 09:38:07 | 2004-10-18 09:38:07 | +-+-+ 1 row in set (0.09 sec) That is exactly 12 months ago (to the second). I don't understand the problem. What kind of date-range calculation would you like to have? If you do not want to go back one whole year, how far do you want to go back? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Creating a table on MySQL from Access
Scott Hamm [EMAIL PROTECTED] wrote on 10/18/2005 09:46:20 AM: Is there a way I can create a table on MySQL using Access and establish a link remotely? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html You can write some VBA code to send to MySQL the CREATE TABLE command through an ADODB.Connection object. After that, you will need to figure out how to create a linked table programmatically as I have never tried. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: ALTER TABLE - how to fix truncated data?
Gleb, I don't think that it is possible to recover the data from the new table, because ALTER operation creates a new table and fills it with data (and truncates the data). But why did the system backup not help you? Could you describe the situation more in detail. If you haven't flushed you binary logs, you may want to make an attempt to recover the data from them. As a sidenote - shouldn't MySQL raise an error when data gets truncated? If the truncation happens silently, chances are you'll never find out until someone does a report or whatever?! With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help
R, I tried using the following query, but it list sometimes 13 months when the current date is in the middle of the month. SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); Try... DATE_ADD( dt, INTERVAL IF(YEAR(NOW())%40 AND YEAR(NOW())%100=0,366,365) DAY ) = NOW() PB - grKumaran wrote: Hello, CREATE TABLE t ( dt datetime ) Please assume this is the table structure and contains thousands of records. And I want to list them only last 12 months (that mean last 1 year) records exactly. For that I tried using the following query, but it list sometimes 13 months when the current date is in the middle of the month. SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); I request you to help me. And thanking you for the consideration. Sincerely, R.Kumaran -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Creating a table on MySQL from Access
Possible solutions. Has not tried all of it myself. 1. Create ODBC connection to your MySQL database, using MySQL Connector/ODBC - this works, sometimes gives problems 2. Try to use MS-Access Pass-Through query to create tables - never tried this way. 3. Do search for link tables in MS Visual Basic Help for Ms-Access to find a way to link the tables. You might not need to link tables. Possible solution that came out of the named above search: VB HELP SourceConnectStr, SourceDatabase Properties See AlsoApplies ToExampleSpecificsYou can use these properties to access external data when you can't link the external tables to your database. The SourceConnectStr property specifies the name of the application used to create an external database. The SourceDatabase property specifies the external database in which the source tables or queries for a query reside. Note The SourceConnectStr and SourceDatabase properties apply to all queries except data-definition, pass-through, and union queries. Setting You use a string expression to set the value of the SourceConnectStr and SourceDatabase properties. You can set these properties in the query's property sheet or in SQL view of the Query window. In the SQL statement, the properties correspond to the IN clause. Note If you are accessing multiple database sources, use the Source property instead of the SourceConnectStr and SourceDatabase properties. Remarks You must use the SourceConnectStr and SourceDatabase properties to access tables from external databases that were created in applications that don't use linked tables (linked table: A table stored in a file outside the open database from which Access can access records. You can add, delete, and edit records in a linked table, but you cannot change its structure.). The following are examples of these property settings: For a Microsoft Access database, the SourceDatabase property setting is the path and database name (for example, C:\Accounts\Customers). Microsoft Access adds the file name extension automatically. The SourceConnectStr property doesn't have a value for a Microsoft Access database. For a database created by a product such as Paradox, the SourceDatabase property setting is the path (for example, C:\Pdoxdata). The SourceConnectStr property setting is the specifier for the database type (for example, Paradox 3.x;). For a list of specifiers, see the ADO Connect property. The following example uses dBASE IV tables in the C:\Dbdata directory as the source of data for the query. SELECT Customer.COMPANYNAM, Orders.ORDERID, Orders.ORDERDATE FROM Customer INNER JOIN Orders ON Customer.CUSTOMERID = Orders.CUSTOMERID IN 'C:\Dbdata'[dBASE IV;]; For an Open Database Connectivity (ODBC) database, the SourceConnectStr property setting is the name of the source database and any additional information required by the product, such as a logon identification (ID) and password. For example, for a Microsoft SQL Server database the setting might be: ODBC;DSN=salessrv;UID=jace;PWD=password;DATABASE=sales; The SourceDatabase property doesn't have a value for an ODBC database. VB HELP Mikhail Berman -Original Message- From: Scott Hamm [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 18, 2005 9:46 AM To: 'Mysql ' Subject: Creating a table on MySQL from Access Is there a way I can create a table on MySQL using Access and establish a link remotely? -- Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access 2002 hangs with MyODBC 3.51.11
Hello list I recently update my Access 2002 with Office SP3 (Access 10.6501...) and all connections with MySQL through MyODBC 3.51.11 have problems I install the Access on a pc without updating and the driver works fine. This version of Access is 10.25... something. I download again MyODBC driver but I get the same problem: access still hangs up. In the event viewer there is the following problem: Faulting application msaccess.exe, version 10.0.6501.0, faulting module msjet40.dll, version 4.0.9025.0, fault address 0x000e9f60. Does any body know what's the problem? Thank you Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query help
grKumaran wrote: Hello, CREATE TABLE t ( dt datetime ) Please assume this is the table structure and contains thousands of records. And I want to list them only last 12 months (that mean last 1 year) records exactly. For that I tried using the following query, but it list sometimes 13 months when the current date is in the middle of the month. SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); I request you to help me. And thanking you for the consideration. First, don't do that. Your WHERE clause calculates a value based on the contents of a row, then compares the calculated value to a constant. An index on your dt column cannot be used to find matching rows in this case, so a full table scan is required. Instead, you should rewrite your query to make a direct comparison of the dt column to a calculated constant. The following query is equivalent to yours, in that it matches the same rows, but it can use the index on dt: SELECT * FROM t WHERE dt = NOW - INTERVAL 1 YEAR; Peter Brawley wrote: R, Try... DATE_ADD( dt, INTERVAL IF(YEAR(NOW())%40 AND YEAR(NOW())%100=0,366,365) DAY ) = NOW() (This won't use an index either.) You think it's a leapyear problem? I suspect Shawn is right that the real problem is that the requirements are not as stated. The poster says he wants only last 12 months (that mean last 1 year), but then states that running the query in the middle of a month gives the wrong results. My guess is that he wants something other than precisely the last year's results. Perhaps he wants a year ending on the last day of the current or previous month. Perhaps the start date shouldn't be precisely 1 year ago today, but at the start of a month. Consider: SET @last_month_end = LAST_DAY(CURDATE() - INTERVAL 1 MONTH) + INTERVAL 1 DAY - INTERVAL 1 SECOND; SET @cur_month_yr = LAST_DAY(CURDATE()) - INTERVAL 1 YEAR + INTERVAL 1 DAY; SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH) - INTERVAL 1 YEAR + INTERVAL 1 DAY; SELECT @last_month_yr, @cur_month_yr, @last_month_end; ++---+-+ | @last_month_yr | @cur_month_yr | @last_month_end | ++---+-+ | 2004-10-01 | 2004-11-01| 2005-09-30 23:59:59 | ++---+-+ 1 row in set (0.00 sec) Now something like SELECT * FROM t WHERE dt BETWEEN @cur_month_yr AND NOW(); or SELECT * FROM t WHERE dt BETWEEN @last_month_yr AND @last_month_end; or some similar combination may do what you want. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Non-linear degradation in bulk loads?
Jon, using a 4 GB buffer pool is safe in a 64-bit computer, and allocating 50 % - 80 % of memory to the buffer pool is recommended in an InnoDB-only server. Regards, Heikki Oracle/Innobase - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; mysql@lists.mysql.com mailto:mysql@lists.mysql.com Lähetetty: Tuesday, October 18, 2005 4:39 AM Aihe: RE: Non-linear degradation in bulk loads? Side question: If I use a 64-bit MySQL build on a 64-bit kernel, is it safe and sane to allocate say, 6GB to the InnoDB buffer pool? On an 8GB box, 64-bit software stack, what is the optimum memory allocation for a pure-InnoDB (* - MyISAM used only for grant tables) mysql server running as the sole application on the machine? -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Monday, October 17, 2005 10:55 AM To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, I am not 100 % sure that the problem we saw was in a 64-bit Linux. It might have been 32-bit. Anyway, since CentOS is a clone of RHEL, this might be the same file cache phenomenon. I do not know if one can force the file cache to stay smaller than 4 GB. You can try running some dummy programs that occupy a few GB of memory. Regards, Heikki Oracle/Innobase - Alkuperäinen viesti - Lähettäjä: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED] mailto:[EMAIL PROTECTED]; mysql@lists.mysql.com mailto:mysql@lists.mysql.com Lähetetty: Monday, October 17, 2005 8:49 PM Aihe: RE: Non-linear degradation in bulk loads? Sorry to spam the group, but I just noticed that I asserted we were on a 2.4 kernel -- we're on 2.6.9-11 as part of CentOS 4.1... -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 2:23 AM To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? Jon, your hardware/OS combination is quite new and unusual. It might indeed be an OS problem. We observed from a 64-bit RHEL 4 that when the file cache of the OS grew bigger than 4 GB, then the file I/O performance dropped to one tenth of the normal. You would not expect that kind of behavior from a 64-bit OS. When you see the slowdown, what does 'top' say about the OS file cache size? The database is our main sites database but we've dramatically reduced the load on that machine over the past couple months through careful optimization of our code. The box is a dual, dual-core Opteron, 8GB of RAM running a 32-bit Linux 2.4 kernel and MySQL 4.0.20 (32-bit of course). We have 1GB allocated to the buffer pool, and our usual 1GB * 3 log files. 8 I/O threads. Load on the box sits at around 6-7, with a large (50%) amount of time spent in wait state, but actual disk throughput to our software RAID array (No longer on a SAN...) is quite low -- 6-9k blocks/s out, 1-6k blocks/s in. Regards, Heikki - Original Message - From: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 14, 2005 10:39 PM Subject: RE: Non-linear degradation in bulk loads? I've tried tweaking the structure of the schema to have, for example, a = PRIMARY KEY index on email, no other indexes, and then insert in sorted = order -- made no improvement whatsoever. Another clue that leads me to = believe that this may be an OS issue: Starting a large cp on the same = box (from a local filesystem other than the one the InnoDB data pool was = on, to NFS) caused MySQL to become COMPLETELY backlogged (we went from = ~15-20 connections at any given instant to 750 (our max_connections = setting)). -JF -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 12, 2005 8:15 AM To: mysql@lists.mysql.com mailto:mysql@lists.mysql.com Subject: Re: Non-linear degradation in bulk loads? =20 Jon, =20 hmm... maybe one of the indexes inevitably is in a random order. =20 Please post a typical =20 SHOW INNODB STATUS\G =20 when the inserts happen slowly. =20 What is your my.cnf like? =20 Regards, =20 Heikki Innobase/Oracle =20 - Alkuper=E4inen viesti - L=E4hett=E4j=E4: Jon Frisby [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Vastaanottaja: Heikki Tuuri [EMAIL PROTECTED];=20 mailto:[EMAIL PROTECTED];=20 mysql@lists.mysql.com mailto:mysql@lists.mysql.com L=E4hetetty: Wednesday, October 12, 2005 3:08 AM Aihe: RE: Non-linear degradation in bulk loads? =20 =20 Two solutions: 1) sort the rows to be inserted on the key
Fw: query help
- Original Message - From: grKumaran [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 18, 2005 16:08 Subject: query help : Hello, : : CREATE TABLE t ( : dt datetime : ) : : Please assume this is the table structure and contains thousands of records. : And I want to list them only last 12 months (that mean last 1 year) records : exactly. : : For that I tried using the following query, but it list sometimes 13 months : when the current date is in the middle of the month. : : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); : : I request you to help me. And thanking you for the consideration. : Thanking you all people. I think I am not clear in last mail, here I go in more detail. Sample records: 2004-05-25 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 Let us assume we are in any date of May month, then I want the records starts from June 01 to the current datetime. Required query should bring the following result -- if we are on 25th day or later of May month 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 -- if we are before 25th day of May. 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 Once again thanking you all the people. And forgive me for any mistakes in my English. Sincerely, R. Kumaran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL connection error
First of all thanks for you answer...
would you like to give me some hinst about what should me condifured in SELinux?
best regards
Israel
On 10/17/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
It is a bit confusing for me. Really, MySQL has all permissions for
reading those files. Do you have SELinux enabled? Sometimes it is
the source of the problems, you should have it properly configured.
Israel Fern$ndez Cabrera [EMAIL PROTECTED] wrote:
List: MySQL General Discussion« Previous Message
From: Israel Fernández CabreraDate: October 17 2005 10:18pm
Subject: SSL connection error
Get Plain Text
Hi all
I'm back with a new subject may be the last one was not attractive :)
I'm using mysql 4.1.11-2 in Fedora Core 4. I need to set up mysql
connections over SLL I follow the mysql manual instructions, create
certificates and keys for the CA, the client and the server and modify
the /etc/my.cnf file with the ssl-ca, ssl-cert and ssl-key for the
client and the mysqld sections of the my.cnf file.
My problem is that mysqld log an error describing that it has no
permission to read the certificate file, I've been with this for more
than 3 days.
I'm attaching ls output, my.cnf file, mysqld.log file and a fragment
of the mysqld strace output with the open syscall returning error.
Thanks in advance for your time and interest
best regards
--
Israel Fdez. Cabrera
[EMAIL PROTECTED]
#ls / | grep etc
drwxr-xr-x 83 root root 12288 Oct 15 16:50 etc
#ls /etc | grep pki
drwxr-xr-x 7 root root4096 Oct 14 17:51 pki
#ls /etc/pki
total 104
drwxr-xr-x 3 root root 4096 Oct 14 21:46 CA
drwxr-xr-x 3 root root 4096 Oct 8 16:54 dovecot
-rwxr-xr-x 1 root root 1088 Oct 8 16:54 gencert.sh
-rwxr-xr-x 1 root root 1056 Oct 8 16:54 gencert.sh~
-rw-r--r-- 1 root root 236 Oct 8 16:54 index.txt
-rw-r--r-- 1 root root 21 Oct 8 16:54 index.txt.attr
-rw-r--r-- 1 root root 21 Oct 8 16:54 index.txt.attr.old
-rw-r--r-- 1 root root 118 Oct 8 16:54 index.txt.old
drwxr-xr-x 2 root root 4096 Oct 8 16:54 newcerts
drwxr-xr-x 2 root root 4096 Oct 8 16:54 rpm-gpg
-rw-r--r-- 1 root root3 Oct 8 16:54 serial
-rw-r--r-- 1 root root3 Oct 8 16:54 serial.old
drwxr-xr-x 5 root root 4096 Oct 14 17:51 tls
#ls /etc/pki/tls
total 40
lrwxrwxrwx 1 root root 19 Oct 8 16:54 cert.pem - certs/ca-bundle.crt
drwxr-xr-x 2 root root 4096 Oct 15 14:18 certs
drwxr-xr-x 2 root root 4096 Oct 8 16:54 misc
-r--r--r-- 1 root root 7998 Oct 14 17:59 openssl.cnf
drwxr-xr-x 2 root root 4096 Oct 8 16:54 private
#ls /etc/pki/tls/certs
total 492
-rw-r--r-- 1 root root 427833 Oct 8 16:54 ca-bundle.crt
-rw-r--r-- 1 root root3617 Oct 14 21:46 client-cert.pem
-rw-r--r-- 1 root mysql887 Oct 8 16:54 client-key.pem
-rw-r--r-- 1 root mysql769 Oct 8 16:54 client-req.pem
-rw-r--r-- 1 root root 610 Oct 8 16:54 make-dummy-cert
-rw-r--r-- 1 root root2240 Oct 8 16:54 Makefile
-rw-r--r-- 1 root root3617 Oct 14 21:46 server-cert.pem
-rw-r--r-- 1 root root 887 Oct 14 21:46 server-key.pem
-rw-r--r-- 1 root mysql769 Oct 8 16:54 server-req.pem
open(/etc/pki/tls/certs/server-cert.pem, O_RDONLY) = -1 EACCES (Permission
denied)
write(2, Error when connection to server ..., 42) = 42
write(2, 1872:error:0200100D:system libra..., 122) = 122
write(2, 1872:error:20074002:BIO routines..., 70) = 70
write(2, 1872:error:140AD002:SSL routines..., 88) = 88
write(2, Unable to get certificate from \'..., 68) = 68
open(/etc/pki/CA/cacert.pem, O_RDONLY) = -1 EACCES (Permission denied)
open(/etc/pki/tls/cert.pem, O_RDONLY) = -1 EACCES (Permission denied)
time([1129246383]) = 1129246383
open(/dev/urandom, O_RDONLY|O_NONBLOCK|O_NOCTTY) = -1 EACCES (Permission
denied)
open(/dev/random, O_RDONLY|O_NONBLOCK|O_NOCTTY) = -1 EACCES (Permission
denied)
open(/dev/srandom, O_RDONLY|O_NONBLOCK|O_NOCTTY) = -1 ENOENT (No such file
or directory)
socket(PF_FILE, SOCK_STREAM, 0) = 3
connect(3, {sa_family=AF_FILE, path=/var/run/egd-pool}, 19) = -1 ENOENT
(No such file or
directory)
close(3)= 0
socket(PF_FILE, SOCK_STREAM, 0) = 3
Thread
* SSL connection error - Israel Fernández Cabrera, October 17 2005
10:17pm
(c) 1995-2004 MySQL AB. All rights reserved.
* MySQL.com Home
* Site Map
* Contact Us
* Privacy Policy
* Trademark Info
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
Fw: Access 2002 hangs with MyODBC 3.51.11
The attachment was stripped by the list manager: forwarded to list for general information Shawn - Forwarded by Shawn Green/Unimin on 10/18/2005 12:07 PM - Shawn Green/Unimin 10/18/2005 11:48 AM To nikos [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: Access 2002 hangs with MyODBC 3.51.11 nikos [EMAIL PROTECTED] wrote on 10/18/2005 10:58:51 AM: Hello list I recently update my Access 2002 with Office SP3 (Access 10.6501...) and all connections with MySQL through MyODBC 3.51.11 have problems I install the Access on a pc without updating and the driver works fine. This version of Access is 10.25... something. I download again MyODBC driver but I get the same problem: access still hangs up. In the event viewer there is the following problem: Faulting application msaccess.exe, version 10.0.6501.0, faulting module msjet40.dll, version 4.0.9025.0, fault address 0x000e9f60. Does any body know what's the problem? Thank you Nikos Microsoft distributed a security hotfix, updating MSJET40.DLL to version 4.0.9025.0, which broke every version of MyODBC I test with (I have gone back as far as 3.51.2). Actually, AFAIK, it may only break the process of creating linked tables from MS Access. All other functions may remain functional. However, that's primarily what my users need the library to do, so for me it's broken. Related MySQL bug reports: http://bugs.mysql.com/bug.php?id=9932 http://bugs.mysql.com/bug.php?id=11698 http://bugs.mysql.com/bug.php?id=12386 Workaround: On each affected user's machine, replace newer MSJET40.DLL with previous version (4.0.8618.0), attached. (attachment probably stripped during list distribution). This effectively undoes part of the security hotfix but restores the ability to create and repair table links from MS Access through MyODBC to MySQL. Due to Microsoft's file protection system (FPS), you have to replace the backup version first. The backup can be in an i386 folder, a DLLCACHE folder or in several other possible places (depending on the version of the OS and how FPS has been configured). After you make sure that MS Access is turned off, you can replace the main version in the SYSTEM32 folder. Check the version tab in the properties of each file after you make the copy to make sure your intended changes have taken effect. [attachment msjet40.dll deleted by Shawn Green/Unimin] Last I heard, MySQL was still working on a full fix for the problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Fw: query help
[EMAIL PROTECTED] wrote on 10/18/2005 12:01:50 PM: - Original Message - From: grKumaran [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 18, 2005 16:08 Subject: query help : Hello, : : CREATE TABLE t ( : dt datetime : ) : : Please assume this is the table structure and contains thousands of records. : And I want to list them only last 12 months (that mean last 1 year) records : exactly. : : For that I tried using the following query, but it list sometimes 13 months : when the current date is in the middle of the month. : : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); : : I request you to help me. And thanking you for the consideration. : Thanking you all people. I think I am not clear in last mail, here I go in more detail. Sample records: 2004-05-25 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 Let us assume we are in any date of May month, then I want the records starts from June 01 to the current datetime. Required query should bring the following result -- if we are on 25th day or later of May month 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 -- if we are before 25th day of May. 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 Once again thanking you all the people. And forgive me for any mistakes in my English. Sincerely, R. Kumaran Thank you for the better explanation. I think that you want the following: The 12 months of data just before the beginning of the next month (all of this month all of the way back to the next month for the previous year, excluding later dates within the current month. As an example: a) Assume we have a table that contains daily records from 2004-01-01 to 2005-10-18. b) You want to run a report based on the date 2005-05-25. You have already said that you did not want to see the dates from 2004-05-26 to 2005-05025. I think what understand that you want to see is from 2004-06-01 to 2005-05-25. Use Michael's formula: SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH) - INTERVAL 1 YEAR + INTERVAL 1 DAY; To compute the earliest date and use the target date as the other end. So for instance, for the target report date of 2005-05-25, combining Michael's formula and your query would look like: SET @report_target_date = '2005-05-25'; SET @last_month_yr = LAST_DAY(@report_target_date - INTERVAL 1 MONTH) - INTERVAL 1 YEAR + INTERVAL 1 DAY; SELECT ... FROM ... WHERE datefield BETWEEN @last_month_yr AND @report_target_date; Does this do what you wanted? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Fw: query help
Hi, The following query will probably work but I think it will be easier to pass the minimum date from your application. SELECT * FROM t WHERE (year(dt)=year(Now()) and dtNow()) or (year(dt)=year(Now())-1 and month(dt)month(Now())) Also you might want to check the other Date and Time functions http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html I'm sure there is a better solution then the above, and the query will be much more optimized if you can send the minimum date from your application like this. SELECT * FROM t WHERE dt$date; Shawn's idea is also good - I just saw his response using the LAST_DAY() function. -- Dobromir Velev On Tuesday 18 October 2005 19:01, grKumaran wrote: - Original Message - From: grKumaran [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 18, 2005 16:08 Subject: query help : Hello, : : CREATE TABLE t ( : dt datetime : ) : : Please assume this is the table structure and contains thousands of records. : And I want to list them only last 12 months (that mean last 1 year) records : exactly. : : For that I tried using the following query, but it list sometimes 13 months : when the current date is in the middle of the month. : : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); : : I request you to help me. And thanking you for the consideration. Thanking you all people. I think I am not clear in last mail, here I go in more detail. Sample records: 2004-05-25 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 Let us assume we are in any date of May month, then I want the records starts from June 01 to the current datetime. Required query should bring the following result -- if we are on 25th day or later of May month 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 -- if we are before 25th day of May. 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 Once again thanking you all the people. And forgive me for any mistakes in my English. Sincerely, R. Kumaran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LASSO TIPS for MYSQL: 3.2 IF I ONLY HAD A...
- Hi and Welcome to - LASSO TIPS FOR MYSQL: 3.2 The FIRST LASSO TIPS FOR MYSQL - http://www.listsearch.com/lassotalk.lasso?id=143312 The LAST LASSO TIPS FOR MYSQL - http://www.listsearch.com/lassotalk.lasso?id=154176 10 LASSO RESOURCES - http://www.listsearch.com/lassotalk.lasso?id=143018 THE LASSO RESOURCES ADDENDUM - http://www.listsearch.com/lassotalk.lasso?id=143417 OMNIPILOT RESOURCE LIST - http://www.omnipilot.com/Resources+for+Beginners.2225.lasso http://www.omnipilot.com/Tip+of+the+Week.1768.lasso http://www.omnipilot.com/Hosting+Providers.1744.lasso http://www.omnipilot.com/Frequently+Asked+Questions.1791.lasso Secondly, My apologies for not getting this out on time yesterday...my servers suffered an outage...so consequently my collective hair has been in piles on the floor folks! And when your servers goo PF on a saturday night when you're deep in the middle of an email 'discourse' of questionable nature, that's probably a good sign to stop! I digress. However all is well and my servers are back online. And soon to get a major upgrade. I'll be in MASSACHUSETTS the week of NOV 1st - 7th (2005). Ive got a number of clients and appointments but if anyone wants to get together for a lil chat/wrap/coding session ping me. Thirdly, Many people have asked me to write a book, I'll tell you straight up, Im not in the market to write a book, that's Duncan Cameron's area, not mine. However, if you're in the market for a tiny lil PDF with a few tips that haven't been published here and there and something with a few more examples, methodologies and practices, please by all means let me see a show of hands (digitally speaking). Perhaps I'll have something ready by summit time. Just in time for my class on Lasso for Newbies. http:// www.lassosummit.com/ Feb 17 - 19th, 2006! TODAYS TIP: IF IF IF IF IF IF IF ONLY... I could while away the hours. Conferrin' with the flowers, Consultin' with the rain. And my head, I'd be scratchin', While my thoughts were busy hatchin', If I only had a brain. - From IF I only had a brain - Wizard of OZ, circa 1933. While I'd love to sit here and yap about the scarecrow, the tin man or the cowardly lion, and that fine hottie Dorothy, somehow or another I think your head you'd be scratchin' while your thoughts were busy hatchin', Has the boy gone crazzah!! Well no I haven't gone crazy but I am on to somethin' here. The IF proposition. The conditional. You don't think about it until you actually need it, and then you begin to wonder well how the hell do I use it ? More over WHY and WHERE do you use it ? Hence this week's lil foray into the seemingly mundane...the ALMIGHTY, ALL PURPOSE, 'IF' CONDITIONAL! PART ONE: THE IF ANDS BUTS of IT ALL. The vaunted IF. What is it and why do you use it ? In short simple language, the IF is a method to branch your logic into a situation to cover a particular scenario or likelihood that a user may experience while using your application. To use the word, to define the word is a bad habit and doens't really help but in this case, I think it does: what if... That's the premise of the conditional IF statement, what if this or that happens. With Lasso there are several different kinds of conditional statements, the IF is only one of them, however it is the most ubiquitous. For the purposes of this article we're focusing solely on the IF statement. Why ? Because as you'll see, in its simplicity it is not only exceptionally powerful, and useful but its come a long way in what can be done with itso without further adieu, may I introduce to you the star of today's game, (a drum roll please) the IF statement! ++ TO SEE THE REST OF THIS LASSO TIP ++ THE CURRENT LASSO TIP for MYSQL: http://www.listsearch.com/lassotalk.lasso?id=154555 THE LAST 5 LASSO TIPS for MYSQL: LTƒM 3.1: Three Lil Toys - http://www.listsearch.com/ lassotalk.lasso?id=154176 LTƒM 3.0: The Lasso App - http://www.listsearch.com/ lassotalk.lasso?id=153881 LTƒM 2.9: The Login Routine - http://www.listsearch.com/ lassotalk.lasso?id=153613 LTƒM 2.8: SQL and Lasso (pt2) - http://www.listsearch.com/ lassotalk.lasso?id=149158 LTƒM 2.7: SQL and Lasso (pt1) - http://www.listsearch.com/ lassotalk.lasso?id=148892 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: query help
: : Hello, : : : : CREATE TABLE t ( : : dt datetime : : ) : : : : Please assume this is the table structure and contains thousands of : records. : : And I want to list them only last 12 months (that mean last 1 year) : records : : exactly. : : : : For that I tried using the following query, but it list sometimes 13 : months : : when the current date is in the middle of the month. : : : : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); : : : : I request you to help me. And thanking you for the consideration. : : : : Thanking you all people. I think I am not clear in last mail, here I go : in : more detail. : : Sample records: : 2004-05-25 : 2004-06-25 : 2004-07-25 : 2004-08-25 : 2004-09-25 : 2004-10-25 : 2004-11-25 : 2004-12-25 : 2005-01-25 : 2005-02-25 : 2005-03-25 : 2005-04-25 : 2005-05-25 : : Let us assume we are in any date of May month, then I want the records : starts from June 01 to the current datetime. : : Required query should bring the following result : -- if we are on 25th day or later of May month : 2004-06-25 : 2004-07-25 : 2004-08-25 : 2004-09-25 : 2004-10-25 : 2004-11-25 : 2004-12-25 : 2005-01-25 : 2005-02-25 : 2005-03-25 : 2005-04-25 : 2005-05-25 : : -- if we are before 25th day of May. : 2004-06-25 : 2004-07-25 : 2004-08-25 : 2004-09-25 : 2004-10-25 : 2004-11-25 : 2004-12-25 : 2005-01-25 : 2005-02-25 : 2005-03-25 : 2005-04-25 : : Once again thanking you all the people. And forgive me for any mistakes : in : my English. : : Sincerely, : R. Kumaran : : : : Thank you for the better explanation. I think that you want the following: : The 12 months of data just before the beginning of the next month (all of : this month all of the way back to the next month for the previous year, : excluding later dates within the current month. : : As an example: : a) Assume we have a table that contains daily records from 2004-01-01 to : 2005-10-18. : b) You want to run a report based on the date 2005-05-25. : : You have already said that you did not want to see the dates from : 2004-05-26 to 2005-05025. I think what understand that you want to see is : from 2004-06-01 to 2005-05-25. : : Use Michael's formula: : SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH) : - INTERVAL 1 YEAR + INTERVAL 1 DAY; : : To compute the earliest date and use the target date as the other end. So : for instance, for the target report date of 2005-05-25, combining : Michael's formula and your query would look like: : : SET @report_target_date = '2005-05-25'; : SET @last_month_yr = LAST_DAY(@report_target_date - INTERVAL 1 MONTH) : - INTERVAL 1 YEAR + INTERVAL 1 DAY; : : SELECT ... : FROM ... : WHERE datefield BETWEEN @last_month_yr AND @report_target_date; : : Does this do what you wanted? : Hello Folks, It is great help. Thank you all and specially for Michael and Shawn. It does work properly in 4.1.9-max. But I think, LAST_DAY function does not availble in old MySQL version 4.0.??, so there I have problem, actually our webserver is still in that version. So I request any help to alter the query to work in previous version too, and it will be greatly appreciated. Thanks in advance for the answer. Greetings, R.Kumaran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
one product in more categories
Hi to all! I have tables products and categories CREATE TABLE categories ( cat_id INT(6) NOT NULL AUTO_INCREMENT, cat_name VARCHAR(45) NULL, cat_description TEXT NULL, cat_parent INTEGER(4) UNSIGNED NULL, cat_status ENUM(0,1) NULL DEFAULT 0, PRIMARY KEY(cat_id), INDEX ac_categories_index1(cat_status) ); CREATE TABLE products ( product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT, product_no VARCHAR(12) NULL, product_name VARCHAR(45) NULL, product_description TEXT NULL, product_colors TEXT NULL, product_includes TEXT NULL, product_catalog VARCHAR(45) NULL, product_status ENUM(0,1) NULL, product_supplier VARCHAR(45) NULL, product_start_date DATE NULL, product_exp_date DATE NULL, product_on_sale ENUM(0,1) NULL, PRIMARY KEY(product_id), INDEX ac_products_index1(product_start_date, product_exp_date), INDEX ac_products_index2(product_status), INDEX ac_products_index_onsale(product_on_sale) ); Since one product can be in more then one category, is it correct to create thirs table with those info? CREATE TABLE ac_products_categories ( pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT, ac_products_product_id INTEGER(8) UNSIGNED NOT NULL, p_id INTEGER(6) UNSIGNED NULL, c_id INTEGER(6) UNSIGNED NULL, PRIMARY KEY(pc_id), INDEX pc_index(p_id, c_id), ); Or, there is better solution? Thanks! -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 10/18/2005 01:50:20 PM: Hi to all! I have tables products and categories CREATE TABLE categories ( cat_id INT(6) NOT NULL AUTO_INCREMENT, cat_name VARCHAR(45) NULL, cat_description TEXT NULL, cat_parent INTEGER(4) UNSIGNED NULL, cat_status ENUM(0,1) NULL DEFAULT 0, PRIMARY KEY(cat_id), INDEX ac_categories_index1(cat_status) ); CREATE TABLE products ( product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT, product_no VARCHAR(12) NULL, product_name VARCHAR(45) NULL, product_description TEXT NULL, product_colors TEXT NULL, product_includes TEXT NULL, product_catalog VARCHAR(45) NULL, product_status ENUM(0,1) NULL, product_supplier VARCHAR(45) NULL, product_start_date DATE NULL, product_exp_date DATE NULL, product_on_sale ENUM(0,1) NULL, PRIMARY KEY(product_id), INDEX ac_products_index1(product_start_date, product_exp_date), INDEX ac_products_index2(product_status), INDEX ac_products_index_onsale(product_on_sale) ); Since one product can be in more then one category, is it correct to create thirs table with those info? CREATE TABLE ac_products_categories ( pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT, ac_products_product_id INTEGER(8) UNSIGNED NOT NULL, p_id INTEGER(6) UNSIGNED NULL, c_id INTEGER(6) UNSIGNED NULL, PRIMARY KEY(pc_id), INDEX pc_index(p_id, c_id), ); Or, there is better solution? Thanks! -afan I think you may have just one too many columns. I can understand c_id and p_id. Those point to categories.cat_id and products.product_id. I understand creating a column to identify the association (pc_id). But what is the column ac_products_product_id for? I would have probably defined it this way CREATE TABLE ac_products_categories ( p_id int unsigned not null, c_id int unsigned not null, PRIMARY KEY(p_id, c_id), INDEX(c_id, p_id) ); The PK ensures that each product can only be associated with any category only once. The other key makes reverse lookups blindingly fast (if you know the category and want a list of all of the products). Forward lookups are covered by the PK. I didn't identify the association with it's own column because there is nothing else this association carries with it (no other data ABOUT the association). If you wanted to add something like who assigned this product to this category or what date it was added, then I may have left it in. To answer your bigger question, YES! This is a proper way of creating a many-to-many relationship in MySQL. Good job! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Fw: query help
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote on 10/18/2005 12:01:50 PM: - Original Message - From: grKumaran [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 18, 2005 16:08 Subject: query help : Hello, : : CREATE TABLE t ( : dt datetime : ) : : Please assume this is the table structure and contains thousands of records. : And I want to list them only last 12 months (that mean last 1 year) records : exactly. : : For that I tried using the following query, but it list sometimes 13 months : when the current date is in the middle of the month. : : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) = NOW(); : : I request you to help me. And thanking you for the consideration. : Thanking you all people. I think I am not clear in last mail, here I go in more detail. Sample records: 2004-05-25 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 Let us assume we are in any date of May month, then I want the records starts from June 01 to the current datetime. Required query should bring the following result -- if we are on 25th day or later of May month 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 2005-05-25 -- if we are before 25th day of May. 2004-06-25 2004-07-25 2004-08-25 2004-09-25 2004-10-25 2004-11-25 2004-12-25 2005-01-25 2005-02-25 2005-03-25 2005-04-25 Once again thanking you all the people. And forgive me for any mistakes in my English. Sincerely, R. Kumaran Thank you for the better explanation. I think that you want the following: The 12 months of data just before the beginning of the next month (all of this month all of the way back to the next month for the previous year, excluding later dates within the current month. As an example: a) Assume we have a table that contains daily records from 2004-01-01 to 2005-10-18. b) You want to run a report based on the date 2005-05-25. You have already said that you did not want to see the dates from 2004-05-26 to 2005-05025. I think what understand that you want to see is from 2004-06-01 to 2005-05-25. Use Michael's formula: SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH) - INTERVAL 1 YEAR + INTERVAL 1 DAY; To compute the earliest date and use the target date as the other end. So for instance, for the target report date of 2005-05-25, combining Michael's formula and your query would look like: SET @report_target_date = '2005-05-25'; SET @last_month_yr = LAST_DAY(@report_target_date - INTERVAL 1 MONTH) - INTERVAL 1 YEAR + INTERVAL 1 DAY; SELECT ... FROM ... WHERE datefield BETWEEN @last_month_yr AND @report_target_date; Does this do what you wanted? Shawn Green Database Administrator Unimin Corporation - Spruce Pine If I understand correctly, report_target_date is always today (the day we run the query), so I'll simply use CURDATE() or NOW(), as appropriate. I think the above query will be close, but not quite right, for three reasons: 1) Peter Brawley is right that we should take leap year into account. That requires changing the order of operations: SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) - INTERVAL 1 MONTH + INTERVAL 1 DAY; 2) If today is any day in May, the report is supposed to start with June 1 of the previous year, not May 1 of the previous year. Hence: SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY; 3) dt is a DATETIME, so we will miss today's entries (unless they were at exactly midnight) if the end of the range in the WHERE clause is a DATE instead of a DATETIME. Try this instead: SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY; SELECT * FROM t WHERE dt BETWEEN @start and NOW(); Of course, so long as there are no rows with dt in the future, this simplifies to SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY; SELECT * FROM t WHERE dt = @start; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
Oops! My bad! :) original name for categories and products are ac_categories and ac_products. but I wanted to simplify and deleted ac_ part - but not on all places. And my simplified query become mess query. Sorry. But, you got a point. And I got the answer. And ac_products_categories table doesn't have any other association. It doesn't matter who, when, why added a prodcut to particular category. Thanks Shawn. [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 10/18/2005 01:50:20 PM: Hi to all! I have tables products and categories CREATE TABLE categories ( cat_id INT(6) NOT NULL AUTO_INCREMENT, cat_name VARCHAR(45) NULL, cat_description TEXT NULL, cat_parent INTEGER(4) UNSIGNED NULL, cat_status ENUM(0,1) NULL DEFAULT 0, PRIMARY KEY(cat_id), INDEX ac_categories_index1(cat_status) ); CREATE TABLE products ( product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT, product_no VARCHAR(12) NULL, product_name VARCHAR(45) NULL, product_description TEXT NULL, product_colors TEXT NULL, product_includes TEXT NULL, product_catalog VARCHAR(45) NULL, product_status ENUM(0,1) NULL, product_supplier VARCHAR(45) NULL, product_start_date DATE NULL, product_exp_date DATE NULL, product_on_sale ENUM(0,1) NULL, PRIMARY KEY(product_id), INDEX ac_products_index1(product_start_date, product_exp_date), INDEX ac_products_index2(product_status), INDEX ac_products_index_onsale(product_on_sale) ); Since one product can be in more then one category, is it correct to create thirs table with those info? CREATE TABLE ac_products_categories ( pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT, ac_products_product_id INTEGER(8) UNSIGNED NOT NULL, p_id INTEGER(6) UNSIGNED NULL, c_id INTEGER(6) UNSIGNED NULL, PRIMARY KEY(pc_id), INDEX pc_index(p_id, c_id), ); Or, there is better solution? Thanks! -afan I think you may have just one too many columns. I can understand c_id and p_id. Those point to categories.cat_id and products.product_id. I understand creating a column to identify the association (pc_id). But what is the column ac_products_product_id for? I would have probably defined it this way CREATE TABLE ac_products_categories ( p_id int unsigned not null, c_id int unsigned not null, PRIMARY KEY(p_id, c_id), INDEX(c_id, p_id) ); The PK ensures that each product can only be associated with any category only once. The other key makes reverse lookups blindingly fast (if you know the category and want a list of all of the products). Forward lookups are covered by the PK. I didn't identify the association with it's own column because there is nothing else this association carries with it (no other data ABOUT the association). If you wanted to add something like who assigned this product to this category or what date it was added, then I may have left it in. To answer your bigger question, YES! This is a proper way of creating a many-to-many relationship in MySQL. Good job! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: query help
Dobromir Velev wrote: Hi, The following query will probably work but I think it will be easier to pass the minimum date from your application. SELECT * FROM t WHERE (year(dt)=year(Now()) and dtNow()) or (year(dt)=year(Now())-1 and month(dt)month(Now())) Also you might want to check the other Date and Time functions http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html I'm sure there is a better solution then the above, and the query will be much more optimized if you can send the minimum date from your application like this. SELECT * FROM t WHERE dt$date; Shawn's idea is also good - I just saw his response using the LAST_DAY() function. The lack of optimization is not due to mysql, rather than the application, calculating the min date, it is because you are comparing a *function of a column* to a constant, instead of the value of a column to a (calculated) constant. That is to be avoided if at all possible. It's a simple as this: SELECT * FROM t WHERE col1 + 2 6; cannot use the index on col1 to select rows, so it does a full-table scan, but SELECT * FROM t WHERE col1 6 - 2; can use the index on col1 to select just the matching rows. You should always make every effort to put the functions on the constant side of the comparison, not the column side. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
- Original Message - From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, October 18, 2005 1:50 PM Subject: one product in more categories Hi to all! I have tables products and categories CREATE TABLE categories ( cat_id INT(6) NOT NULL AUTO_INCREMENT, cat_name VARCHAR(45) NULL, cat_description TEXT NULL, cat_parent INTEGER(4) UNSIGNED NULL, cat_status ENUM(0,1) NULL DEFAULT 0, PRIMARY KEY(cat_id), INDEX ac_categories_index1(cat_status) ); CREATE TABLE products ( product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT, product_no VARCHAR(12) NULL, product_name VARCHAR(45) NULL, product_description TEXT NULL, product_colors TEXT NULL, product_includes TEXT NULL, product_catalog VARCHAR(45) NULL, product_status ENUM(0,1) NULL, product_supplier VARCHAR(45) NULL, product_start_date DATE NULL, product_exp_date DATE NULL, product_on_sale ENUM(0,1) NULL, PRIMARY KEY(product_id), INDEX ac_products_index1(product_start_date, product_exp_date), INDEX ac_products_index2(product_status), INDEX ac_products_index_onsale(product_on_sale) ); Since one product can be in more then one category, is it correct to create thirs table with those info? CREATE TABLE ac_products_categories ( pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT, ac_products_product_id INTEGER(8) UNSIGNED NOT NULL, p_id INTEGER(6) UNSIGNED NULL, c_id INTEGER(6) UNSIGNED NULL, PRIMARY KEY(pc_id), INDEX pc_index(p_id, c_id), ); Or, there is better solution? Thanks! -afan When there is a many-to-many relationship between two tables, the traditional solution is to create a table, usually called an association table (or an intersection table) to show how they are related. Typically, the association table contains the primary key from each of the original tables. The primary keys from the two original tables, are, of course, foreign keys in the new table that point back to the original tables. The primary key of the new table is the *combination* of the primary keys of the original tables. For example, let's imagine that we have Projects and Employees and that any given project can have many employees on it and that any given employee can be working on multiple projects. That is a true many-to-many relationship. The appropriate way to represent this in the database would be along these lines: PROJECT Table [Primary key: Projno] == ProjnoProjectName[Other columns describing project] ------ A New Marketing System B Payroll System Revisions C New Inventory System EMPLOYEE Table [Primary key: Empno] === EmpnoLastname[Other columns describing employee] --- 1Jones 2Brown 3Smith 4Malone PROJECT_EMPLOYEE Table [Primary key: Projno + Empno; Foreign key #1: Projno; Foreign key #2: Empno] = ProjnoEmpno[Other columns describing *combination* of project and employee] --- A1 A3 B2 C2 C3 In other words, Project A is staffed by employees 1 and 3; Project B is staffed by employee 2 alone; Project C is staffed by employees 2 and 3. (Employee 4 doesn't have a project right now; maybe she is on maternity leave?]. In a properly designed table, the only other columns that should be in the association table are ones that have some connection to BOTH the Project and the Employee. For instance, if you wanted to indicate the percentage of the employee's week that should be dedicated to each project, you could add a column for that: ProjnoEmpnoTime_Pct_Per_Week --- --- A140 A360 B280 C220 C340 In other words: employee 1 spends 40% of his time each week on Project A; employee 2 spends 80% of her time on Project B and 20% on Project C; and employee 3 spends 60% of his time on Project A and 40% on Project C. This percentage should NOT be in the Project or in the Employee tables because it refers to the COMBINATION of Project and Employee; its only proper place is in the association table. In your particular situation, the only columns you really need in your association table are product_id and cat_id. The other columns are unnecessary; in fact, it's probably a bad idea to have them there at all. Also, the primary key in the association table must be BOTH product_id and cat_id, not just the product_id. I'm a big user of Referential Integrity so I'd also define product_id and cat_id as two separate foreign keys in the association table; this will necessitate using InnoDB as your storage engine but it will ensure that your association table only ever contains valid data, which is very useful in my opinion.
Re: Fw: query help
grKumaran wrote: Hello Folks, It is great help. Thank you all and specially for Michael and Shawn. It does work properly in 4.1.9-max. But I think, LAST_DAY function does not availble in old MySQL version 4.0.??, so there I have problem, actually our webserver is still in that version. So I request any help to alter the query to work in previous version too, and it will be greatly appreciated. Thanks in advance for the answer. Greetings, R.Kumaran I was afraid that might be the case. Usually the manual says when a function was introduced, but not so for LAST_DAY(). In that case, you can use DATE_FORMAT to accomplish the same thing: SET @start = DATE_FORMAT(CURDATE() - INTERVAL 1 YEAR + INTERVAL 1 MONTH, '%Y-%m-01'); SELECT * FROM t WHERE dt = @start; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SSL connection error
Hello. First check the contents of /var/log/messages. SELinux usually puts there some notes about what it is doing. I'm not a SELinux expert, but quick search at fedora.redhat.com has shown me that I should use system-config-securitylevel utility for configuring SELinux. Israel Fernández Cabrera wrote: First of all thanks for you answer... would you like to give me some hinst about what should me condifured in SEL= inux? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restore of mysqldump-ed data is corrupted
Hello. This is the same problem that Delyan Toshev reported on September 18 2005 4:27pm but that post is still unanswered: Are you sure that you have the same problem (the same character set settings, same broken cyrillic characters and so on)? If not, please, provide as much info as you can. Delyan was using cyrillic symbols with latin1 encoding, which is not designed for this purposes. 4.1.5-gamma-log is a very old release. Check if the problem remains if you use mysqldump which is shipped with 4.1.14 distribution. gioklio wrote: I made backup of database issuing 'mysqldump --opt --skip-extended-insert' from command line on 4.1.5-gamma-log, FreeBSD. When I try to restore data from backup into same server, data is corrupted! How is this possible? What is the purpose of this program if it cannot restore the very same data that he stored on the very same server!? This is the same problem that Delyan Toshev reported on September 18 2005 4:27pm but that post is still unanswered: Hi, Database is MySQL 4.1.5-gamma, running on Linux. All charset variables are set to default 'latin1', except 'character_set_system', which is utf8. A database was created and running in this environment, but the data it gets (from php) were in utf8. Mysqldump is used without any options to export database to sql text file. When importing back into the same database some characters (non latin,cyrillic in fact) get broken. These are exactly 4 characters that broke, but it is no fun, you know. Any combination of charset setings and database creation both from mysql client and server did not work over this problem. Now my question is: what is the encoding that mysqldump puts data in? I cannot decode it with anything! To me it looks like UTF8, but not quite. Somebody mentioned on this list before, that when nothing is specified when dumping data that is not in the default character set, mysqldump insert something other?! What is this exactly? It seems strange, because importing back only few characters are broken, other non-latin are intact. But it is not working OK, in fact it made the data corrupted. Exporting back after import confirm this, since the corrupted characters are replaced by one and the same combination - 0xFFED0 0x3F - for capital letters (0x0410, 0x41D), and 0xFFED1 0x3F for small letters 0x0441 and 0x044F. I know that database and dump should be with specified correct charset, but I look into way to recover in this case, since data are already corrupted and original database is not available. Thanks, Delyan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. As a sidenote - shouldn't MySQL raise an error when data gets truncated? MySQL raises a warning after such ALTER operation. See: mysql create table dt(a decimal(4,1)); Query OK, 0 rows affected (0.01 sec) mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ mysql select version(); +-+ | version() | +-+ | 5.0.13-rc-debug-log | +-+ Martijn Tonies wrote: As a sidenote - shouldn't MySQL raise an error when data gets truncated? If the truncation happens silently, chances are you'll never find out until someone does a report or whatever?! With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
For the same project (below) I have problem with building table for
product prices.
In regular online store, price is usually part of the products table.
But, I need a solution for multiple prices. E.g.
QTY -2550 100 200
Price - $1.59 $1.39 $1.19 $0.99
Also, if product is On Sale I need to be shown both prices: regular and
sale price
QTY -2550 100 200
Price -$1.59 $1.39 $1.19 $0.99
Sale - $0.99 $0.99 $0.99 $0.99
What would be structure of Quantity and Price tables?
My guess:
CREATE TABLE ac_quantities (
quantity_id INT(8) NOT NULL AUTO_INCREMENT,
quantity INTEGER(6) NOT NULL,
product_id INTEGER(8) NOT NULL,
PRIMARY KEY(quantity_id),
INDEX ac_quantities_index1(quantity)
);
CREATE TABLE ac_prices (
price_id INT(8) NOT NULL AUTO_INCREMENT,
price FLOAT(10,2) NOT NULL,
product_id INTEGER(8) NOT NULL,
product_type ENUM('regular','sale') NOT NULL DEFAULT 'regular',
PRIMARY KEY(price_id),
INDEX ac_prices_index1(price)
);
How close am I?
:)
[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 10/18/2005 01:50:20 PM:
Hi to all!
I have tables products and categories
CREATE TABLE categories (
cat_id INT(6) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(45) NULL,
cat_description TEXT NULL,
cat_parent INTEGER(4) UNSIGNED NULL,
cat_status ENUM(0,1) NULL DEFAULT 0,
PRIMARY KEY(cat_id),
INDEX ac_categories_index1(cat_status)
);
CREATE TABLE products (
product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL,
product_includes TEXT NULL,
product_catalog VARCHAR(45) NULL,
product_status ENUM(0,1) NULL,
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
product_on_sale ENUM(0,1) NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
INDEX ac_products_index_onsale(product_on_sale)
);
Since one product can be in more then one category, is it correct to
create thirs table with those info?
CREATE TABLE ac_products_categories (
pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
p_id INTEGER(6) UNSIGNED NULL,
c_id INTEGER(6) UNSIGNED NULL,
PRIMARY KEY(pc_id),
INDEX pc_index(p_id, c_id),
);
Or, there is better solution?
Thanks!
-afan
I think you may have just one too many columns. I can understand c_id and
p_id. Those point to categories.cat_id and products.product_id. I
understand creating a column to identify the association (pc_id). But what
is the column ac_products_product_id for?
I would have probably defined it this way
CREATE TABLE ac_products_categories (
p_id int unsigned not null,
c_id int unsigned not null,
PRIMARY KEY(p_id, c_id),
INDEX(c_id, p_id)
);
The PK ensures that each product can only be associated with any category
only once. The other key makes reverse lookups blindingly fast (if you
know the category and want a list of all of the products). Forward lookups
are covered by the PK.
I didn't identify the association with it's own column because there is
nothing else this association carries with it (no other data ABOUT the
association). If you wanted to add something like who assigned this
product to this category or what date it was added, then I may have left
it in.
To answer your bigger question, YES! This is a proper way of creating a
many-to-many relationship in MySQL. Good job!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
- Original Message -
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, October 18, 2005 4:26 PM
Subject: Re: one product in more categories
For the same project (below) I have problem with building table for
product prices.
In regular online store, price is usually part of the products table.
But, I need a solution for multiple prices. E.g.
QTY -2550 100 200
Price - $1.59 $1.39 $1.19 $0.99
Also, if product is On Sale I need to be shown both prices: regular and
sale price
QTY -2550 100 200
Price -$1.59 $1.39 $1.19 $0.99
Sale - $0.99 $0.99 $0.99 $0.99
What would be structure of Quantity and Price tables?
My guess:
CREATE TABLE ac_quantities (
quantity_id INT(8) NOT NULL AUTO_INCREMENT,
quantity INTEGER(6) NOT NULL,
product_id INTEGER(8) NOT NULL,
PRIMARY KEY(quantity_id),
INDEX ac_quantities_index1(quantity)
);
CREATE TABLE ac_prices (
price_id INT(8) NOT NULL AUTO_INCREMENT,
price FLOAT(10,2) NOT NULL,
product_id INTEGER(8) NOT NULL,
product_type ENUM('regular','sale') NOT NULL DEFAULT 'regular',
PRIMARY KEY(price_id),
INDEX ac_prices_index1(price)
);
How close am I?
:)
Your guess at the solution is probably usable but it could cause
difficulties that can be avoided with a different design.
You could do what you want in a single table. It would look like this:
PRICE Table (Primary Key: Product_ID + Quantity)
===
Product_IDQuantityRegular_PriceSale_Price
--
1 251.59 0.99
1 501.39 0.99
1 100 1.19 0.99
1 200 0.99 0.99
2 25 3.99 -
2 50 3.59 -
The dash in the sale price is used to denote a null. As you probably know, a
null is a special value that means unknown or not applicable. Here, a null
in a sale price means that there is no sale price for this combination of
product_ID and quantity; people have to pay the regular price.
I should mention one other possibility that you may want to consider in your
design.
In the example you've given, the sale price was the same for that product
regardless of the quantity; I'm guessing that is NOT the normal situation
and that sale price varies with quantity most of the time, at least
somewhat. For example, the sale price might be 0.99 for up to 100 items but
then 0.79 for quantities over 100. The design I just stated should work fine
for that case. However, if the sale price was always the same for a given
product regardless of the quantity, you could do the table a bit
differently: omit the Sale_Price column and create a row that was
specifically for the sale price; it could use a reserved quantity like 0 to
indicate that it was a sale price row:
PRICE Table (Primary Key: Product_ID + Quantity)
===
Product_IDQuantityRegular_Price
-
1 251.59
1 501.39
1 100 1.19
1 200 0.99
1 0 0.99
Here, the last row indicates that the sale price for Product 1 is 0.99
regardless of the quantity. This makes the table a bit smaller but still
shows all the data. You just have to remember that your query always
searches for a quantity of 0 to obtain the sale price.
Rhino
[EMAIL PROTECTED] wrote:
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote on 10/18/2005 01:50:20 PM:
Hi to all!
I have tables products and categories
CREATE TABLE categories (
cat_id INT(6) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(45) NULL,
cat_description TEXT NULL,
cat_parent INTEGER(4) UNSIGNED NULL,
cat_status ENUM(0,1) NULL DEFAULT 0,
PRIMARY KEY(cat_id),
INDEX ac_categories_index1(cat_status)
);
CREATE TABLE products (
product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL,
product_includes TEXT NULL,
product_catalog VARCHAR(45) NULL,
product_status ENUM(0,1) NULL,
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
product_on_sale ENUM(0,1) NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
INDEX ac_products_index_onsale(product_on_sale)
);
Since one product can be in more then one category, is it correct to
create thirs table with those info?
CREATE TABLE ac_products_categories (
pc_id INTEGER(6) UNSIGNED NOT NULL AUTO_INCREMENT,
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
p_id
Re: one product in more categories
afan, For the same project (below) I have problem with building table for product prices. In regular online store, price is usually part of the products table. But, I need a solution for multiple prices. E.g. QTY -2550 100 200 Price - $1.59 $1.39 $1.19 $0.99 Also, if product is On Sale I need to be shown both prices: regular and sale price QTY -2550 100 200 Price -$1.59 $1.39 $1.19 $0.99 Sale - $0.99 $0.99 $0.99 $0.99 First two footnotes to the excellent advice offered by Rhino Shawn on your categories, products products_categories tables: 1. It will be best to type the primary foreign keys identically--all unsigned, or all not. 2. To avoid rounding errors, use DECIMAL rather than FLOAT for money columns. Before you model extended price computations, you have to ask answer crucial questions: (i) do you know in advance all the kinds of price extensions that can come up? (ii) do you want the price rules to be (a) in the database or (b) in the app? (iii) if the answer to (ii) is (a), do you want the rules in stored procedures, or in tables which application code must parse? (iv) does the app need to track price history (eg so it can recreate a price computation from six months ago)? Suppose the answers are those that most conventionally apply: only qty and sale will ever come up, the rules will be in the app, and you can leave history to the backups. Then you can take a very simple, semi-normalised approach (leaving out some details): CREATE TABLE extended_prices ( epid INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, qty_up_to SMALLINT NOT NULL, price_per DECIMAL(10,2) NOT NULL, price_per_sale DECIMAL(10,2) NULL ); which permits you to enter whatever (qty cutoffs, price, sale) combos are desired for any desired products, and find them for any product with a very simple query. There is a risk, though: in six months the client may find that new price extensions are needed, and/or that she needs history after all. Now, add the wrinkles that other possible, but presently unidentified price extensions (eg 'special promotions', 'coupons', c) will be required, and that history must be tracked. Now you need at least, again normalising only partly... CREATE TABLE pricemodtypes ( pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY, name CHAR(20) ) CREATE TABLE extended_prices ( epid INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL, pricemodtype_id INT NOT NULL, qty_up_to SMALLINT NOT NULL, begindate DATE NOT NULL, enddate DATE NOT NULL, price_per DECIMAL(10,2) NOT NULL, price_per_mod DECIMAL(10,2) NULL ); the query to retrieve all the prices for a product is more complicated but still straightforward. Of course wrinkles multiply as if conjured by a Sorcerer's Apprentice. Perhaps we should pause here for a breath. Is this the info you need? PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access 2002 hangs with MyODBC 3.51.11
nikos wrote: Hello list I recently update my Access 2002 with Office SP3 (Access 10.6501...) and all connections with MySQL through MyODBC 3.51.11 have problems I install the Access on a pc without updating and the driver works fine. This version of Access is 10.25... something. I download again MyODBC driver but I get the same problem: access still hangs up. In the event viewer there is the following problem: Faulting application msaccess.exe, version 10.0.6501.0, faulting module msjet40.dll, version 4.0.9025.0, fault address 0x000e9f60. Does any body know what's the problem? Thank you Nikos I've got Access 2002 SP3 ( unfortunately ). I'm not getting these hangs though. Check that you're following http://dev.mysql.com/doc/refman/4.1/en/programs-known-to-work-with-myodbc.html to the letter. Also don't use MyODBC-3.51.11 - there are a lot of problems with this version. 3.51.06 worked well for me. Alternatively, you can track down 3.51.12 - there was a link posted to it in the MyODBC mailing list. It also works for me, but it's not actually released yet, so you might be better going with 3.51.06. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
difference between max_connection and max_user_connection
Are there any difference between max_connection and max_user_connection? thanks! _ 享用世界上最大的电子邮件系统― MSN Hotmail。 http://www.hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I can't find any variables used show variables
My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used show variables.Weither there is a lack of manual updating? thanks _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the difference between server option and Server System Variables
liulian, In mysql,server system variables's function like server option function. How do i choosing between the server opiton and the system variables. thanks! MySQL has more than 300 system variables that can be set in the option file my.cnf/ini, on the server command line, and/or with SELECT statements. Some can be set with all those methods, some with just one or two of them. What do you want to know? PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: difference between max_connection and max_user_connection
At 10:13 +0800 10/19/05, ⧠àÆ wrote: Are there any difference between max_connection and max_user_connection? thanks! The difference is explained here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
the difference between server option and Server System Variables
In mysql,server system variables's function like server option function. How do i choosing between the server opiton and the system variables. thanks! _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't find any variables used show variables
At 10:11 +0800 10/19/05, ⧠àÆ wrote: My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used show variables.Weither there is a lack of manual updating? Did you look here? http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: the difference between server option and Server System Variables
I want to know when i need using the server option and when i need the server system variables.In other words,can i using server system variables replace all server options. thanks. From: Peter Brawley [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: 王 旭 [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: the difference between server option and Server System Variables Date: Tue, 18 Oct 2005 21:20:32 -0500 liulian, In mysql,server system variables's function like server option function. How do i choosing between the server opiton and the system variables. thanks! MySQL has more than 300 system variables that can be set in the option file my.cnf/ini, on the server command line, and/or with SELECT statements. Some can be set with all those methods, some with just one or two of them. What do you want to know? PB -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ 与联机的朋友进行交流，请使用 MSN Messenger: http://messenger.msn.com/cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't find any variables used show variables
My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used "show variables".Weither there is a lack of manual updating? Some system variables can be read by running mysqld --help or mysqladmin variables from a command line. Some can be SET and SELECTed in a mysql client, some can be displayed, by the command SHOW VARIABLES in a mysql client. System variables can be set in option files or on the server command line. For how to use option files see http://dev.mysql.com/doc/refman/5.0/en/option-files.html, for command line options see http://dev.mysql.com/doc/mysql/en/Server_options.html, for use of such variables in db admin see http://dev.mysql.com/doc/mysql/en/MySQL_Database_Administration.html, about System Variables see http://dev.mysql.com/doc/mysql/en/System_Variables.html, for the SHOW VARIABLES command see http://dev.mysql.com/doc/mysql/en/SHOW_VARIABLES.html and in the INNODB section http://dev.mysql.com/doc/mysql/en/InnoDB.html. PB No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Input on Materialized Views
Hi all, We are a group of three students in Professor Ric. Holt's Software Architecture class at the University of Waterloo. As our project, we would like to examine the MySQL source to determine the best way to implement materlialized views. What we would like to know is: 1. How feasible would implementing materialized views be? 2. Any reasons why materialized views haven't been added already? 3. Any web pages or archived messages relating to this. (I have search the archives and didn't find anything.) 4. Any comments at all relating to this endeavor. Many thanks, -Andrew Roth, on behalf of group members Steve Hanov and Hobbe Smit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't find any variables used show variables
yes. I look reference manual of mysql5.0.12. For example,there a variables named bdb_..,but i can't find these variables used show variables. From: Paul DuBois [EMAIL PROTECTED] To: 猝 嗥 [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: I can't find any variables used show variables Date: Tue, 18 Oct 2005 21:20:20 -0500 At 10:11 +0800 10/19/05, 猝 嗥 wrote: My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used show variables.Weither there is a lack of manual updating? Did you look here? http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ 免费下载 MSN Explorer: http://explorer.msn.com/lccn/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can't find any variables used show variables
At 13:49 +0800 10/19/05, ⧠àÆ wrote: yes. I look reference manual of mysql5.0.12. For example,there a variables named bdb_..,but i can't find these variables used show variables. Your server probably doesn't have BDB support compiled in. From: Paul DuBois [EMAIL PROTECTED] To: ý Á [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: I can't find any variables used show variables Date: Tue, 18 Oct 2005 21:20:20 -0500 At 10:11 +0800 10/19/05, ý Á wrote: My kernel version is 5.0.12. When i learn the manual,i didn't find any variables in the manul when i used show variables.Weither there is a lack of manual updating? Did you look here? http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
