Re: How to include a dynamic function result in a view?
I'm using 5.0.9 beta standard. I'll try to upgrade and will re-post if I still get erroneous results. s.m. Am Mon, 15 Aug 2005 18:53:40 +0200 hat Gleb Paharenko <[EMAIL PROTECTED]> geschrieben: Hello. What version of MySQL do you use? On my 5.0.11 I have a correct result: mysql> show create table dateTest\G; *** 1. row *** View: dateTest Create View: CREATE ALGORITHM=UNDEFINED VIEW `test`.`dateTest` AS select sql_no_cache `test`.`tDay`.`theDate` AS `theDate`,`test`.`tDay`.`a` AS `a` from `test`.`tDay` where (`test`.`tDay`.`theDate` > (now() - interval 1 day)) [EMAIL PROTECTED] wrote: I have a table with date values: ++ | theDate| ++ | 2005-08-15 | | 2005-08-16 | | 2005-08-14 | ++ I can execute this select statement on it: select * from tDay where theDate > date_sub( now(), interval 1 day); And I can create a view from it: create view dateTest as select * from tDay where theDate > date_sub( now(), interval 1 day); On creating the view, the date_sub function is replaced by its result, though, so that the view does not generate dynamic results: show create table dateTest; CREATE ALGORITHM=UNDEFINED VIEW `kurse`.`dateTest` AS select sql_no_cache `kurse`.`tDay`.`theDate` AS `theDate` from `kurse`.`tDay` where (`kurse`.`tDay`.`theDate` > 20050814) How can I avoid this replacement and make this view a truly dynamic one? s.m. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0 -> 4.1 lose timestamp
Hunter Peress wrote: Yes theres all this talk about timestamp returning different results in 4.1 vs. 4.0 but i took the MYI and MYD and frm files from a winnt running 4.0.14 and transferred to a 4.1.11-Debian_4-log and all the timestamp fields are NULL it seems all the other data is fine. Any ideas? i suppose i may just have to dump/load instead of copy files. If you dump them, and reload them, they are usually fine. If you want to retrieve the correct timestamp (as before), just do SELECT timestamp + 0 as timestamp FROM ... (for instance) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql@lists.mysql.com
mysqld fails with permision denied (/var); runing as a user >Description: mysql configures for runing in user space; makes; and installs; tests wor, but mysqld wont run >How-To-Repeat: I built mysql-4.1.14-nightly-20050808 from source, using ./configure '--prefix=/usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local' '--with-mysqld-user=jc' then make then make install Then I initialize with bin/mysql_install_db --datadir=local --user=jc this responds with: You can start the MySQL daemon with: cd /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local ; /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe & This command fails with the following erros: mkdir: cannot create directory `/var/lib/mysql': Permission denied chown: cannot access `/var/lib/mysql': No such file or directory Starting mysqld daemon with databases from /var/lib/mysql /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe: line 329: /var/log/mysqld.log: No such file or directory /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe: line 337: /var/log/mysqld.log: No such file or directory STOPPING server from pid file /var/run/mysqld/mysqld.pid tee: /var/log/mysqld.log: Permission denied 050812 12:13:16 mysqld ended tee: /var/log/mysqld.log: Permission denied >Fix: >Submitter-Id: >Originator: >Organization: James Cooper,-_|\ E-mail: [EMAIL PROTECTED] Curtin University / \ Phone: +61 8 9266 2948 GPO Box U1987, PERTH $_,-._/ Fax:+61 8 9266 2819 WA 6845, Australia v CRICOS provider code: 00301J > >MySQL support: none >Synopsis: mysql configures, makes, and installs; tests work, but mysqld won't run. >Severity: critical >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-4.1.14-nightly-20050808 (Source distribution) >C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >C++ compiler: g++ (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >Environment: System: Linux ladybird 2.6.10-csck #5 SMP Fri May 6 16:49:29 WST 2005 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.4/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 May 6 17:04 /lib/libc.so.6 -> libc-2.3.5.so -rwxr-xr-x 1 root root 1505808 Apr 7 18:58 /lib/libc-2.3.5.so -rw-r--r-- 1 root root 2409956 Apr 7 18:03 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 7 17:29 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local' '--with-mysqld-user=jc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql installs; tests work, but mysqld won't run.
mysqld fails with permision denied (/var); runing as a user >Description: mysql configures for runing in user space; makes; and installs; tests wor, but mysqld wont run >How-To-Repeat: I built mysql-4.1.14-nightly-20050808 from source, using ./configure '--prefix=/usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local' '--with-mysqld-user=jc' then make then make install Then I initialize with bin/mysql_install_db --datadir=local --user=jc this responds with: You can start the MySQL daemon with: cd /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local ; /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe & This command fails with the following erros: mkdir: cannot create directory `/var/lib/mysql': Permission denied chown: cannot access `/var/lib/mysql': No such file or directory Starting mysqld daemon with databases from /var/lib/mysql /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe: line 329: /var/log/mysqld.log: No such file or directory /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe: line 337: /var/log/mysqld.log: No such file or directory STOPPING server from pid file /var/run/mysqld/mysqld.pid tee: /var/log/mysqld.log: Permission denied 050812 12:13:16 mysqld ended tee: /var/log/mysqld.log: Permission denied >Fix: >Submitter-Id: >Originator: >Organization: James Cooper,-_|\ E-mail: [EMAIL PROTECTED] Curtin University / \ Phone: +61 8 9266 2948 GPO Box U1987, PERTH $_,-._/ Fax:+61 8 9266 2819 WA 6845, Australia v CRICOS provider code: 00301J > >MySQL support: none >Synopsis: mysql installs; tests work, but mysqld won't run. >Severity: critical >Priority: low >Category: mysql >Class: sw-bug >Release: mysql-4.1.14-nightly-20050808 (Source distribution) >C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >C++ compiler: g++ (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >Environment: System: Linux ladybird 2.6.10-csck #5 SMP Fri May 6 16:49:29 WST 2005 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.4/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux Thread model: posix gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 May 6 17:04 /lib/libc.so.6 -> libc-2.3.5.so -rwxr-xr-x 1 root root 1505808 Apr 7 18:58 /lib/libc-2.3.5.so -rw-r--r-- 1 root root 2409956 Apr 7 18:03 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 7 17:29 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local' '--with-mysqld-user=jc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple my.cnf files
Hi Clyde, On Mon, 2005-08-15 at 19:56 -0400, Clyde Lewis wrote: > Does anyone have any experience using multiple my.cnf files on a single box? > If so, how is the my.cnf specified during startup and shutdown? Try starting mysqld with --defaults-file=/path/to/my.cnf > I need multiple my.cnf files to test ibbackup software because it does not > currently support using mysqld_multi. > CL Petr -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.1.13 Problems
On Tuesday 09 August 2005 08:17, Gleb Paharenko wrote: > Hello. > > > I have noticed that since the update i have multiple instances of mysql > > running which seem to be causing the problem ps uax output below > > I usually see a lot of mysqld processes (these are > just different threads of the same process), and I don't see anything > wrong with it: > > [EMAIL PROTECTED] gleb]$ ps uax |grep gleb |grep mysqld |wc -l > 23 > > > 4.1.13 from the Mysql site however since the upgrade i have been having > > problems trying to write lines in tables . > > Could you provide more information about this problem? > > Peter Nikolic <[EMAIL PROTECTED]> wrote: > -- > 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 Hi . sorry bout the delay replying have been rather busy on a car rally Right i think i have solved the problem i was having by completely deleting all mysql instances and all files associated with it and re-installing 4.1.13 , I still have the multiple instances that i never used to get on the version supplied by Suse out of the box but that was never stable for me . For now 4.1.13 is running and seems ok i have anothetr problem now but that is comming in a seperat mail to the list Thanks pete . -- If Bill Gates had gotten LAID at High School do YOU think there would be a Microsoft ? Of course NOT ! You gotta spend a lot of time at your school Locker stuffing underware up your ass to think , I am going to take on the worlds Computer Industry ---:heard on Cyber Radio.:--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
now i am getting confused (i could not think of a better title)
Hi folks I have several small data bases running on mysql 4.1.13 no problem brought a book read it yes well i have a data base consisting of 3 tables , radios , users , events radios contains 4 fields csgn , setid , sernum cmmnts. this is populated and is more a lookup table for the radio info then there is events this contains 5 fieldsevnt , eloc , edate , cllct_date , rtn_date and lastly users this contains 6 fields sur_nme , fr_name , ph_w , ph_h , ph_m , addrss Using knoda (part of kde) i have created an form that looks all very nice (to me ) the selection of the radio works enters the right info into the form i can enter users data and event data but when i try to save the data and insert a new row i am unable to my question is How do i go about saving data to multiple tables from one entry form or should this happen automatically The book i purchased "Beggining SQL" i did not find any help at all i could not find anything on mysql in my local store or a bit further out any help pointers much appreaceted Thanks Pete . -- If Bill Gates had gotten LAID at High School do YOU think there would be a Microsoft ? Of course NOT ! You gotta spend a lot of time at your school Locker stuffing underware up your ass to think , I am going to take on the worlds Computer Industry ---:heard on Cyber Radio.:--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to arrange my table in another direction
Hello. Your question isn't clear enough for me. Do you want that values in 'type' column of the first table to become column names in the second table? Or do you want just to see results in a row? $$ $ <[EMAIL PROTECTED]> wrote: > [-- text/plain, encoding 8bit, charset: gb2312, 17 lines --] > > My table is: > type price > car1000 > bike 100 > > > I want the result: > > car bike > 1000100 > > I don't know how to get that result. > > > - > DO YOU YAHOO!? > $$$2G$pop3$$ -- 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: Post-Installation Setup Problems: error[2002 1604]
Hello. MySQL 3.xx is almost out of support now. Even no bugs fixed in 3.23 (only very critical ones). General reasons about old software and it's problems can be used in your dialog with hosting provider. Usually providers leave servers with MySQL 3.xx in use and provide a new one with MySQL 4.1. Clients port their applications to the new server by themselves (if they want it, of course). > > it get worse . most of my commercial work is running on >servers with ver 3.23.X (sun and RH servers). I enjoy a >great relationship with my domain hoster (who is also a >programmer who uses mysql a lot), but he has been very >cautious about upgrading any of his servers, being >concerned about code breaking, etc. > > What argument might be provided to him to upgrade and what caveats > might be cited? > > Thanks very much for the pointer to the docs. > regards > tim > -- 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: mysql installs; tests work, but mysqld won't run.
Hello. Specify the correct value for datadir when you start mysqld_safe. For these purposes I normally use configuration file, and launch mysqld_safe with --defaults-file=/path/to/my/file command line option. See: http://dev.mysql.com/doc/mysql/en/program-options.html James Cooper <[EMAIL PROTECTED]> wrote: > mysqld fails with permision denied (/var); runing as a user > >>Description: >mysql configures for runing in user space; makes; and installs; tests >wor, but mysqld wont run > > >>How-To-Repeat: > I built mysql-4.1.14-nightly-20050808 from source, using > ./configure > '--prefix=/usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local' > '--with-mysqld-user=jc' > > then make > then make install > Then I initialize with > bin/mysql_install_db --datadir=local --user=jc > > this responds with: > You can start the MySQL daemon with: > cd /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local ; > /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe > & > > This command fails with the following erros: > > mkdir: cannot create directory `/var/lib/mysql': Permission denied > chown: cannot access `/var/lib/mysql': No such file or directory > Starting mysqld daemon with databases from /var/lib/mysql > /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe: > line 329: /var/log/mysqld.log: No such file or directory > /usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local/bin/mysqld_safe: > line 337: /var/log/mysqld.log: No such file or directory > STOPPING server from pid file /var/run/mysqld/mysqld.pid > tee: /var/log/mysqld.log: Permission denied > 050812 12:13:16 mysqld ended > tee: /var/log/mysqld.log: Permission denied > > >>Fix: > > >>Submitter-Id: >>Originator: >>Organization: > James Cooper,-_|\ E-mail: [EMAIL PROTECTED] > Curtin University / \ Phone: +61 8 9266 2948 > GPO Box U1987, PERTH $_,-._/ Fax:+61 8 9266 2819 > WA 6845, Australia v CRICOS provider code: 00301J >> >>MySQL support: none >>Synopsis: mysql installs; tests work, but mysqld won't run. >>Severity: critical >>Priority: low >>Category: mysql >>Class: sw-bug >>Release: mysql-4.1.14-nightly-20050808 (Source distribution) > >>C compiler:gcc (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >>C++ compiler: g++ (GCC) 3.4.4 20050721 (Red Hat 3.4.4-2) >>Environment: > > System: Linux ladybird 2.6.10-csck #5 SMP Fri May 6 16:49:29 WST 2005 i686 > i686 i386 GNU/Linux > Architecture: i686 > > Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc > /usr/bin/cc > GCC: Reading specs from /usr/lib/gcc/i386-redhat-linux/3.4.4/specs > Configured with: ../configure --prefix=/usr --mandir=/usr/share/man > --infodir=/usr/share/info --enable-shared --enable-threads=posix > --disable-checking --with-system-zlib --enable-__cxa_atexit > --disable-libunwind-exceptions --enable-java-awt=gtk --host=i386-redhat-linux > Thread model: posix > gcc version 3.4.4 20050721 (Red Hat 3.4.4-2) > Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' > ASFLAGS='' > LIBC: > lrwxrwxrwx 1 root root 13 May 6 17:04 /lib/libc.so.6 -> libc-2.3.5.so > -rwxr-xr-x 1 root root 1505808 Apr 7 18:58 /lib/libc-2.3.5.so > -rw-r--r-- 1 root root 2409956 Apr 7 18:03 /usr/lib/libc.a > -rw-r--r-- 1 root root 204 Apr 7 17:29 /usr/lib/libc.so > Configure command: ./configure > '--prefix=/usr/staff/tango/jc/src/fedora/mysql-4.1.14-nightly-20050808/local' > '--with-mysqld-user=jc' > > > -- 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: Why can't I revoke usage from user?
Hello. 'USAGE' means that user doesn't have any privileges. Use 'DROP USER'. See: http://dev.mysql.com/doc/mysql/en/drop-user.html Gu Lei <[EMAIL PROTECTED]> wrote: > Hello everyone: > > Why can't I revoke usage from user? > mysql> show grants for ''@'172.20.16.110'; > +--+ > | Grants for @172.20.16.110| > +--+ > | GRANT USAGE ON *.* TO ''@'172.20.16.110' | > +--+ > 1 row in set (0.00 sec) > > mysql> revoke usage on *.* from ''@'172.20.16.110'; > Query OK, 0 rows affected (0.00 sec) > > mysql> show grants for ''@'172.20.16.110'; > +--+ > | Grants for @172.20.16.110| > +--+ > | GRANT USAGE ON *.* TO ''@'172.20.16.110' | > +--+ > 1 row in set (0.00 sec) > > 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: Post-Installation Setup Problems: error[2002 1604]
Tim Johnson wrote: it get worse . most of my commercial work is running on servers with ver 3.23.X (sun and RH servers). I enjoy a great relationship with my domain hoster (who is also a programmer who uses mysql a lot), but he has been very cautious about upgrading any of his servers, being concerned about code breaking, etc. What argument might be provided to him to upgrade and what caveats might be cited? Details on how to upgrade are available at: http://dev.mysql.com/doc/mysql/en/upgrade.html It seems that the 4.0.x branche will not live very long anymore, so upgrading to 4.1.x (via 4.0) might be a good idea if he wants to upgrade. http://dev.mysql.com/doc/mysql/en/news.html Lists all the changes in each version. The major features can be found in the D.2 and D.3 documents (for 4.1 and 4.0). As a hoster he will probably also need to support PHP, etc. There is also a nice book by Adam Trachtenberg: "Upgrading to PHP5" (O'Reilly, July, 2004, 352 p., ISBN 0596006365) which also contains information on how to migrate to PHP5/MySQL4.1 in several steps. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I revoke usage from user?
Hi, > Why can't I revoke usage from user? > mysql> show grants for ''@'172.20.16.110'; > +--+ > | Grants for @172.20.16.110| > +--+ > | GRANT USAGE ON *.* TO ''@'172.20.16.110' | > +--+ > 1 row in set (0.00 sec) > > mysql> revoke usage on *.* from ''@'172.20.16.110'; > Query OK, 0 rows affected (0.00 sec) > > mysql> show grants for ''@'172.20.16.110'; > +--+ > | Grants for @172.20.16.110| > +--+ > | GRANT USAGE ON *.* TO ''@'172.20.16.110' | > +--+ > 1 row in set (0.00 sec) Try: revoke all from ... 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: Why can't I revoke usage from user?
Gleb, > Hello. > > 'USAGE' means that user doesn't have any privileges. Use 'DROP USER'. > See: > http://dev.mysql.com/doc/mysql/en/drop-user.html I disagree here. "drop user" will drop the user, revoking "usage" can be something different. If, for example, you do "grant usage on dbname.*" this is different from then "just" a user without "usage" granted to that database. Besides, having a user without privileges might be useful as well :-) 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]
query
Hi, I want to create a query that selects the diference between the value of a field from the current record and the value of the same field from the previous record. Is this possible? It should return a list of diferences. Or, at least I should be able to do that query to do this diference between 2 specified records. Thank you. Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why can't I revoke usage from user?
Martin, Comments in-line: > -Original Message- > From: Martijn Tonies [mailto:[EMAIL PROTECTED] > Sent: 16 August 2005 10:35 > To: mysql@lists.mysql.com > Subject: Re: Why can't I revoke usage from user? > > Gleb, > > > Hello. > > > > 'USAGE' means that user doesn't have any privileges. Use > 'DROP USER'. > > See: > > http://dev.mysql.com/doc/mysql/en/drop-user.html > > I disagree here. > > "drop user" will drop the user, revoking "usage" can be > something different. You can't actually revoke USAGE, without dropping the user. > > If, for example, you do "grant usage on dbname.*" this is > different from then "just" a user without "usage" granted to > that database. USAGE is a global level privilege, whilst you can "GRANT USAGE ON test.* ..", it's actually ignored. > > Besides, having a user without privileges might be useful as well :-) Exactly what USAGE is for.. ;) Here's a quick example: mysql> create user frank; Query OK, 0 rows affected (0.00 sec) mysql> show grants for frank; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'frank'@'%' | +---+ 1 row in set (0.00 sec) mysql> show databases; ++ | Database | ++ | information_schema | | Tracking | | mysql | | test | ++ 4 rows in set (0.00 sec) mysql> grant usage on test.* to frank; Query OK, 0 rows affected (0.00 sec) mysql> show grants for frank; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'frank'@'%' | +---+ 1 row in set (0.00 sec) mysql> revoke usage on *.* from frank; Query OK, 0 rows affected (0.00 sec) mysql> show grants for frank; +---+ | Grants for [EMAIL PROTECTED]| +---+ | GRANT USAGE ON *.* TO 'frank'@'%' | +---+ 1 row in set (0.00 sec) Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.13-nt-log --Help
Hi, I am running MySQL 4.0.13-nt-log on Windowns XP and I need help on the following: 1) I have written a program in vc++ 6 which writes data to a mysql table; it runs and even make some entries into the table (about 10 per half-day), but after some time (18 hours or less) mysql disconnects; any attempts to reconnect are unsuccessful; when I restart the computer, mysql starts fine. What could the problem? Is it to do with my program or mysql itself? 2) How do I find out when mysql stopped and the cause? where can I find the log file if there is? Thanks. Emmanuel __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can I use the information from "SHOW STATUS" in a SELECT statment ?
Hello to everyone on [EMAIL PROTECTED], In 2 words, what I wish to do is something like : mysql> SELECT @@Qcache_queries_in_cache / @@Qcache_hits; Is it possible ? Thank you. -- Cheers, Maxim Vexler (hq4ever). Do u GNU ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cast and numeric conversion
Hi, I've got two columns, one is 'integer' (and store negative value), the other one 'tinyint unsigned' (only > 1 stored in it), when I multiply these columns, quantity with negative value does not show up. (I expect -50*10 to give : -500, but the field stay blank.) I've look a bit at the documentation, but can't find anything on type promotion. Where can I find this ? -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query
On 16/08/2005, "Octavian Rasnita" wrote: > I want to create a query that selects the diference between the value > of a field from the current record and the value of the same field > from the previous record. How do you define "current record" and "previous record"? (relational databases are not spreadsheets) -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why can't I revoke usage from user?
Hi there, > > > > > > 'USAGE' means that user doesn't have any privileges. Use > > 'DROP USER'. > > > See: > > > http://dev.mysql.com/doc/mysql/en/drop-user.html > > > > I disagree here. > > > > "drop user" will drop the user, revoking "usage" can be > > something different. > > You can't actually revoke USAGE, without dropping the user. > > > If, for example, you do "grant usage on dbname.*" this is > > different from then "just" a user without "usage" granted to > > that database. > > USAGE is a global level privilege, whilst you can "GRANT USAGE ON test.* > ..", it's actually ignored. I thought that on a database level, it created a record in "db" with all "N" for each privilege? Either way: rather stupid statement then. Why doesn't it raise an error if it's invalid/ignored? Guess it's another MySQL Gotcha?? This, however, works in MySQL 5 (latest) and creates a row in "db": grant usage on test.* to [EMAIL PROTECTED] with grant option All privileges "N" except "grant_priv". Which is useless as well, in this case, correct? > > Besides, having a user without privileges might be useful as well :-) > > Exactly what USAGE is for.. ;) 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: French Characters, Still no answer
Sorry, dont know how I missed: http://lists.mysql.com/mysql/187794 The old msql was 4.0 and the new one is 4.1. When we first imported the data the database was in latin1(my mistake) and we put it into production as everything seemed to work fine. We have one site that uses french characters sometimes and they compained 1 week later. By that time it was too late to roll back so now we have this problem. We tried converting the database over to Utf8 but it did not seem to fully work. We can put the french characters in, even from the tomcat server, but when they come back out to the webpage(we have a content management system) they are garbled. One night we did some testing and hooked the tomcat to the old database and everything worked fine with french characters so it has to be the new database. If no one has anymore ideas I think our best route is to dump it, install 4.0 on this server and import it back in. Everything looks ok as far as Utf8 on the tables and such, I feel that we are just missing one key thing that is garbling the characters. Also when we use EMS mysqlmanager to view the data, it is fine, it is only when it comes back to the tomcat server it seems. Thanks for your ideas thus far, James - Original Message - From: "Gleb Paharenko" <[EMAIL PROTECTED]> To: Sent: Monday, August 15, 2005 1:41 PM Subject: Re: French Characters, Still no answer > Hello. > > You've already got a good answer: > http://lists.mysql.com/mysql/187794 > > > Subscribe to mysql list or use web interface. Why are you mixing latin1 > with utf8 in the same column? You can check if something is wrong with your > connection variables using the following statement: > > show variables like '%char%'; > > Use mysql command line client or mysql-query-browser (or other client > software which correctly handles UTF8 data) to check if import of your > data was performed successfully. In such a way you can localize the > problem, and find whether it is related to Tomcat or wrong data in > MySQL. > > > > > >I am still having trouble with french characters if anyone has ANY ideas, > > please help. > > > > We have installed the newest version of MySql and cannot get it to play > > nice > > with French characters. Our older version worked fine. The problem > > may (or > > may not) be that when we put the dump into the new database(yes its > > default > > charset is Utf8) the default character set for the table is Utf8 but > > some > > fields are like this: > > > > 'Story' longtext character set latin1 NOT NULL > > > > We tried linking our tomcat to the old database on the other server > > through > > this tomcat and everything works fine but when we link the tomcat back > > to > > the new database, it will not play nice with french characters. (they > > come > > out as outlined squares etc) > > > > Any ideas would be greatly appreciated > > James > >"James Sherwood" <[EMAIL PROTECTED]> wrote: > > > -- > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French Characters, Still no answer
Hello, I dont think we want to go this route as it was working fine on the 4.0 database on the other server. I also might add that the old server was linux, and the new one is Windows 2000 Server Thanks, James - Original Message - From: "Ace Dimitrievski" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: Sent: Monday, August 15, 2005 6:38 PM Subject: Re: French Characters, Still no answer > Im not sure if my experience will be of any help but > here it is: > I used a database with utf8 as default charset (you > can easily set this with mysql administrator) and the > columns that I needed to store Macedonian cyrilic > characters were varchar. Some letters were stored as > ?. When I changed the column type to varbinary I was > able to store/retreive all the letters properly. > However if you change the type of the columns you > cannot use the mysql adminisators backup sql queries > to restore a database that contains utf8 characters > (at least I failed). Using a PHP script will help. For > those that use mySQL with some script language using > base64 encoding/decoding funtions for non latin1 > letters is the safest way (however you cannot strictly > predict the lenght of the encoded string based on the > lenght of the plaintext). > best regards > --- > Ace Dimitrievski, > research assistant, > Faculty of Electrical Engineering Skopje > --- [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: > > The older version is 4.xx.xx im not sure how to > tell. > > > > New version is 4.1.12 > > > > Thank you, > > James > > > > > > - Original Message - > > From: "Bruce Dembecki" <[EMAIL PROTECTED]> > > To: "James Sherwood" <[EMAIL PROTECTED]> > > Cc: > > Sent: Monday, August 15, 2005 2:50 PM > > Subject: Re: French Characters, Still no answer > > > > > > > Still no answer, perhaps, but ther'es still no > question. > > > > > > Per my earlier response... What version of MySQL > is the old version > > > you refer to, what version is the new version you > refer to? With that > > > information someone here is more likely to be able > to tell you > > > something useful... without that you're not likely > to get much of a > > > response. > > > > > > Best Regards, Bruce > > > > > > On Aug 15, 2005, at 7:59 AM, James Sherwood wrote: > > > > > > > I am still having trouble with french characters > if anyone has ANY > > > > ideas, please help. > > > > > > > > We have installed the newest version of MySql > and cannot get it to > > > > play nice > > > > with French characters. Our older version > worked fine. The > > > > problem may (or > > > > may not) be that when we put the dump into the > new database(yes its > > > > default > > > > charset is Utf8) the default character set for > the table is Utf8 > > > > but some > > > > fields are like this: > > > > > > > > 'Story' longtext character set latin1 NOT NULL > > > > > > > > We tried linking our tomcat to the old database > on the other server > > > > through > > > > this tomcat and everything works fine but when > we link the tomcat > > > > back to > > > > the new database, it will not play nice with > french characters. > > > > (they come > > > > out as outlined squares etc) > > > > > > > > Any ideas would be greatly appreciated > > > > James > > > > > > > > > > > > > -- > > > 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] > > > > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > -- > 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]
Re: query
Hi, I define the previous record by date. For each record corresponds a date which is unique and the previous record is that that contains the yesterday date. Thank you. Teddy - Original Message - From: "Felix Geerinckx" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 16, 2005 2:45 PM Subject: Re: query > On 16/08/2005, "Octavian Rasnita" wrote: > > > I want to create a query that selects the diference between the value > > of a field from the current record and the value of the same field > > from the previous record. > > How do you define "current record" and "previous record"? > (relational databases are not spreadsheets) > > -- > felix > > -- > 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]
Re: query
do something like: SELECT t1.date, (t1.field - t2.field) as diff FROM your_table t1, your_table t2 where t1.date = t2.date + 1; On 8/16/05, Octavian Rasnita <[EMAIL PROTECTED]> wrote: > Hi, > > I define the previous record by date. For each record corresponds a date > which is unique and the previous record is that that contains the yesterday > date. > > Thank you. > > Teddy > > - Original Message - > From: "Felix Geerinckx" <[EMAIL PROTECTED]> > To: > Sent: Tuesday, August 16, 2005 2:45 PM > Subject: Re: query > > > > On 16/08/2005, "Octavian Rasnita" wrote: > > > > > I want to create a query that selects the diference between the value > > > of a field from the current record and the value of the same field > > > from the previous record. > > > > How do you define "current record" and "previous record"? > > (relational databases are not spreadsheets) > > > > -- > > felix > > > > -- > > 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] > > -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query
You can use INTERVAL i.e. Lets say you have a table mysql> CREATE TABLE foo (bar int(14), fdate date ); Query OK, 0 rows affected (0.27 sec) mysql> INSERT INTO foo > VALUES (1, now()), (25,now() - INTERVAL 1 DAY), (15,now() - INTERVAL 2 DAY); mysql> SELECT f1.bar - f2.bar AS diff , f2.fdate -> FROM foo AS f1 ->INNER JOIN foo AS f2 ->ON (f1.fdate = f2.fdate + INTERVAL 1 DAY); +--++ | diff | fdate | +--++ | -24 | 2005-08-15 | | 10 | 2005-08-14 | +--++ 2 rows in set (0.00 sec) -Original Message- From: Octavian Rasnita [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 7:51 AM To: mysql@lists.mysql.com; Felix Geerinckx Subject: Re: query Hi, I define the previous record by date. For each record corresponds a date which is unique and the previous record is that that contains the yesterday date. Thank you. Teddy - Original Message - From: "Felix Geerinckx" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 16, 2005 2:45 PM Subject: Re: query > On 16/08/2005, "Octavian Rasnita" wrote: > > > I want to create a query that selects the diference between the value > > of a field from the current record and the value of the same field > > from the previous record. > > How do you define "current record" and "previous record"? > (relational databases are not spreadsheets) > > -- > felix > > -- > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why does this fail
Can anyone explain why this SQL statement would fail? A friend was asked it in a pre-interview assessment and it threw him a bit. SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from PrsnAddress T2) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005
Re: Why does this fail
For a start, spot the missing AS... raz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does this fail
First glance -- It's missing a comma (,) between T1.BldgName and T1.StreetName, Missing a period (.) in PstlAddr T1 and in PrsnAddress T2 Also, how does the system know the table names if you are putting them first, (i.e. shouldn't it be BldgName.T1 not vice versa)? You don't define what T1 is, unless they have somehow skipped using the AS statement (and without seeing the table structure, you don't know if this is an alias or the true table name). Of course, I am not the expert on this list, just some common SQL sense. J.R. -Original Message- From: Andrew Hargreaves [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 16, 2005 9:14 AM To: mysql@lists.mysql.com Subject: Why does this fail Can anyone explain why this SQL statement would fail? A friend was asked it in a pre-interview assessment and it threw him a bit. SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from PrsnAddress T2) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does this fail
> Can anyone explain why this SQL statement would fail? A friend was asked it > in a pre-interview assessment and it threw him a bit. > > SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 > Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from > PrsnAddress T2) Assuming there is no typo, the missing comma after T1.BldgName means the T1 from T1.StreetName will be used as an alias. Then there's a ".StreetName" left, which is invalid syntax. Assuming there is a typo, and there's supposed to be a comma after T1.BldgName, then there is no syntax error, provided you are using version 4.1 or later. The query might not do what you expect (depending on what you expect) because there is nothing to relate T1 to T2. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does this fail
[snip] Can anyone explain why this SQL statement would fail? A friend was asked it in a pre-interview assessment and it threw him a bit. SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from PrsnAddress T2) [/snip] Probably because there is no correlation in the sub-query, where the sub references any column of the first table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does this fail
Isn't AS optional? -Original Message- From: Raz [mailto:[EMAIL PROTECTED] Sent: 16 August 2005 14:19 To: Andrew Hargreaves Cc: mysql@lists.mysql.com Subject: Re: Why does this fail For a start, spot the missing AS... raz -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cast and numeric conversion
> I've got two columns, one is 'integer' (and store negative value), the > other one 'tinyint unsigned' (only > 1 stored in it), when I multiply > these columns, quantity with negative value does not show up. > (I expect -50*10 to give : -500, but the field stay blank.) > I've look a bit at the documentation, but can't find anything on type > promotion. Where can I find this ? http://dev.mysql.com/doc/mysql/en/cast-functions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I use the information from "SHOW STATUS" in a SELECT statment ?
I don't know if you can do it directly in a mysql shell like that (like you would with v$, dba_ in Oracle) but if you call a show command from PERL (via DBI) it hashes the result just like it were from a select. may not be the most elegant solution but its the best I've come up with though I'll happily blush in embarassment if there's a "direct" way I've not found... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cast and numeric conversion
Scott Noyes wrote: I've got two columns, one is 'integer' (and store negative value), the other one 'tinyint unsigned' (only > 1 stored in it), when I multiply these columns, quantity with negative value does not show up. (I expect -50*10 to give : -500, but the field stay blank.) I've look a bit at the documentation, but can't find anything on type promotion. Where can I find this ? http://dev.mysql.com/doc/mysql/en/cast-functions.html thank you ;) -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: now i am getting confused (i could not think of a better title)
Peter >How do i go about saving data to multiple tables from >one entry form or should this happen automatically If the model is that a user can have radios which in turn can have events, you need a userid column in users and radios, and a radioid column in radios and events. You need to read a bit about relational design (eg http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.html). To add a user, a radio and an event, save the user info, get the user id from that insert, save the radio info, get back the radioid from that insert, then save the event info. To add a radio, retrieve the userid from the user's input and offer the user a blank radio form. And so on. PB - Peter Nikolic wrote: Hi folks I have several small data bases running on mysql 4.1.13 no problem brought a book read it yes well i have a data base consisting of 3 tables , radios , users , events radios contains 4 fields csgn , setid , sernum cmmnts. this is populated and is more a lookup table for the radio info then there is events this contains 5 fieldsevnt , eloc , edate , cllct_date , rtn_date and lastly users this contains 6 fields sur_nme , fr_name , ph_w , ph_h , ph_m , addrss Using knoda (part of kde) i have created an form that looks all very nice (to me ) the selection of the radio works enters the right info into the form i can enter users data and event data but when i try to save the data and insert a new row i am unable to my question is How do i go about saving data to multiple tables from one entry form or should this happen automatically The book i purchased "Beggining SQL" i did not find any help at all i could not find anything on mysql in my local store or a bit further out any help pointers much appreaceted Thanks Pete . No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.9/72 - Release Date: 8/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does this fail
"Andrew Hargreaves" <[EMAIL PROTECTED]> wrote on 08/16/2005 09:13:56 AM: > Can anyone explain why this SQL statement would fail? A friend was asked it > in a pre-interview assessment and it threw him a bit. > > SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from PstlAddr T1 > Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from > PrsnAddress T2) > > Several people have already responded with the comma vs. alias (T1.BldgName...). Moving on... I believe that if there is a single record in PrsnAddress, the whole query will return nothing. That is because the inner query of the EXISTS clause is not linked to any term from the outer query (as others have mentioned). However, the converse should be true that if PrsnAddress is empty, then you will get the results you expect (WHERE T1.City='London'...) because the NOT EXISTS would evaluate as TRUE. This is a good example why all developers should test for both positive and negative results from their queries. If someone had only tested with an empty PrsnAddress table, they could have been tricked into thinking the query operated correctly.
Re: MySQL 4.0.13-nt-log --Help
Emmanuel Mwesigwa <[EMAIL PROTECTED]> wrote on 08/16/2005 04:57:53 AM: > Hi, > > I am running MySQL 4.0.13-nt-log on Windowns XP and I > need help on the following: > > 1) I have written a program in vc++ 6 which writes > data to a mysql table; it runs and even make some > entries into the table (about 10 per half-day), but > after some time (18 hours or less) mysql disconnects; > any attempts to reconnect are unsuccessful; when I > restart the computer, mysql starts fine. What could > the problem? Is it to do with my program or mysql > itself? > > 2) How do I find out when mysql stopped and the cause? > where can I find the log file if there is? > > Thanks. > > Emmanuel Are you making and breaking connections to the database only as often as you need them or are you trying to maintain one long continuous connection? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Dates & Schedule Problems
Hello.
I was able to make a query which retrieves dates where the TA is
available only for specific TA. To list all TA and periods of time
where they're available I wrote a stored procedure which works in
MySQL 5.
Here is the definition and test data in my tbl_notavailable
(I've changed some field names, like TAid to id, but they have
the same meaning):
*** 1. row ***
Table: tbl_notavailable
Create Table: CREATE TABLE `tbl_notavailable` (
`id` int(11) default NULL,
`startdate` datetime default NULL,
`enddate` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
id startdate enddate
1 2005-01-01 14:30:00 2005-01-01 15:23:00
1 2005-01-01 18:30:00 2005-01-02 09:23:00
1 2005-01-02 15:30:00 2005-01-02 16:03:00
1 2005-01-02 18:30:00 2005-01-03 09:03:00
2 2005-01-01 13:40:00 2005-01-01 14:15:00
2 2005-01-01 19:40:00 2005-01-02 10:15:00
2 2005-01-02 11:30:00 2005-01-02 12:01:00
2 2005-01-02 15:30:00 2005-01-02 16:21:00
2 2005-01-02 22:30:00 2005-01-03 10:21:00
And this is the results:
mysql> call p_avail();
+--+-+-+
| id | startdate | enddate |
+--+-+-+
|1 | 2005-01-01 15:23:00 | 2005-01-01 18:30:00 |
|1 | 2005-01-02 09:23:00 | 2005-01-02 15:30:00 |
|1 | 2005-01-02 16:03:00 | 2005-01-02 18:30:00 |
|2 | 2005-01-01 14:15:00 | 2005-01-01 19:40:00 |
|2 | 2005-01-02 10:15:00 | 2005-01-02 11:30:00 |
|2 | 2005-01-02 12:01:00 | 2005-01-02 15:30:00 |
|2 | 2005-01-02 16:21:00 | 2005-01-02 22:30:00 |
+--+-+-+
I have a help table tbl_available with the same definition
as tbl_notavailable.
My stored procedure:
CREATE PROCEDURE p_avail()
DETERMINISTIC READS SQL DATA
BEGIN
DECLARE pID INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE pCOUNTER INT DEFAULT 0;
DECLARE pCOUNT INT DEFAULT 0;
DECLARE pTSTART DATE DEFAULT '-00-00';
DECLARE pTEND DATE DEFAULT '-00-00';
DECLARE cur1 CURSOR FOR SELECT DISTINCT id
FROM tbl_notavailable ORDER BY id;
DECLARE cur2 CURSOR FOR
SELECT t1.enddate AS TSTART
,(
SELECT t2.startdate AS T2END
FROM tbl_notavailable t2
WHERE t2.startdate > TSTART
AND id = pID
ORDER BY t2.startdate ASC LIMIT 1
) AS TEND
FROM tbl_notavailable t1
WHERE id = pID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
TRUNCATE TABLE tbl_available;
SELECT COUNT(DISTINCT id) FROM tbl_notavailable INTO pCOUNT;
OPEN cur1;
SET pCOUNTER = 0;
loop1: LOOP
FETCH cur1 INTO pID;
SET done=0;
OPEN cur2;
hile1: WHILE done =0 DO
FETCH cur2 INTO pTSTART,pTEND;
IF (done = 1) THEN
LEAVE hile1;
END IF;
INSERT INTO tbl_available SET id = pID,
startdate = pTSTART, enddate = pTEND;
END WHILE hile1;
CLOSE cur2;
SET pCOUNTER = pCOUNTER+1;
IF( pCOUNTER = pCOUNT ) THEN
LEAVE loop1;
END IF;
END LOOP loop1;
CLOSE cur1;
DELETE FROM tbl_available WHERE enddate IS NULL;
SELECT * FROM tbl_available;
END
Here is the query which retrieves the available dates for specific id,
say, 2:
SELECT t1.enddate AS TSTART
,(
SELECT t2.startdate AS T2END
FROM tbl_notavailable t2
WHERE t2.startdate > TSTART
AND id = 2
ORDER BY t2.startdate ASC LIMIT 1
) AS TEND
FROM tbl_notavailable t1
WHERE id = 2;
"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> wrote:
> Hello all,
>
> I have a little table o TAs (teach assistants) with the following MySQL
> schema:
>
> tbl_NotAvailable {
>TAid - int(11)
>StartDate - DateTime
>EndDate - DataTime
> }
>
> This table logs the times where TAs are NOT available. So If a TA is not
> a
Re: French Characters, Still no answer
Hello. > Also when we use EMS mysqlmanager to view the data, it is fine, it is only > when it comes back to the tomcat server it seems. That means that data on the server is ok. Check the values of your character_set_client, character_set_connection and character_set_results variables from Tomcat's application. See: http://dev.mysql.com/doc/mysql/en/charset-connection.html > If no one has anymore ideas I think our best route is to dump it, install > 4.0 on this server and import it back in. Perform an import so that all fields in your table have an utf8 character set. And tune properly the connection variables. "James Sherwood" <[EMAIL PROTECTED]> wrote: > Sorry, dont know how I missed: http://lists.mysql.com/mysql/187794 > > The old msql was 4.0 and the new one is 4.1. > > When we first imported the data the database was in latin1(my mistake) and > we put it into production as everything seemed to work fine. > > We have one site that uses french characters sometimes and they compained 1 > week later. By that time it was too late to roll back so now we have this > problem. > > We tried converting the database over to Utf8 but it did not seem to fully > work. > > We can put the french characters in, even from the tomcat server, but when > they come back out to the webpage(we have a content management system) they > are garbled. > > One night we did some testing and hooked the tomcat to the old database and > everything worked fine with french characters so it has to be the new > database. > > If no one has anymore ideas I think our best route is to dump it, install > 4.0 on this server and import it back in. > > Everything looks ok as far as Utf8 on the tables and such, I feel that we > are just missing one key thing that is garbling the characters. > > Also when we use EMS mysqlmanager to view the data, it is fine, it is only > when it comes back to the tomcat server it seems. > > Thanks for your ideas thus far, > James > > -- 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: lvm+innodb
On Mon, 2005-08-15 at 20:01 -0700, Patrick Michael Kane wrote: > Did you ever get any responses on how to get innodb to a consistent > on-disk state for LVM snapshotting? > > FYI, under LVM2 read/write snapshots are allowed. > I never got any replies. If you look closely into my rambling posting, you will see that I am aware of the LVM2 option, and that I solicit feed back on whether it's a "good idea" to mount snapshots r/w. Regards, ..jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can I use the information from "SHOW STATUS" in a SELECT statment ?
> -Original Message- > From: Sid Lane [mailto:[EMAIL PROTECTED] > Sent: 16 August 2005 14:54 > To: mysql@lists.mysql.com > Subject: Re: Can I use the information from "SHOW STATUS" in > a SELECT statment ? > > I don't know if you can do it directly in a mysql shell like > that (like you would with v$, dba_ in Oracle) but if you call > a show command from PERL (via DBI) it hashes the result just > like it were from a select. > > may not be the most elegant solution but its the best I've > come up with though I'll happily blush in embarassment if > there's a "direct" > way I've not found... No you are absolutely correct, there is no way to for instance, use a SHOW command in a sub query, or even in a cursor on version 5.. Of course, if you are using version 5 then a lot of the SHOW commands are also being migrated to the INFORMATION_SCHEMA, although not all of them as yet (such as SHOW FULL PROCESSLIST) - and you can use those just like the DBA_ views in Oracle. So, the only fall back is to use an external script/application. I'm not sure whether this is planned to be supported or not, but it would be nice to grab the output of a SHOW command in a cursor, I was discussing this in #mysql on freenode last night as it happens.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change Datatype (time)
Is there a function that I can use in a query to return values in a different datatype? I've got a column which is time datatype and I'd like to return the values in a vb-compatible datatype. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Change Datatype (time)
> -Original Message- > From: Dennis Olvany [mailto:[EMAIL PROTECTED] > Sent: 16 August 2005 16:43 > To: mysql@lists.mysql.com > Subject: Change Datatype (time) > > Is there a function that I can use in a query to return > values in a different datatype? I've got a column which is > time datatype and I'd like to return the values in a > vb-compatible datatype. > You can use DATE_FORMAT() for this: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clever dump using Java
Hello, I would like to export a part of my MySQL database as a SQL script. I thought about mysqldump.exe but it's too simple. I need to export rows from one table which matchs simple criteria and all rows from other tables which are in relation with them. The ideal solution would be to make a SELECT query and pass it to *something*. This *something* would execute the query, analyze found rows and return SQL script creating these rows. In current project I'm using Hibernate but this part can be done using any tool or pure JDBC. How would you handle such a task? -- Best regards, Konrad Billewicz Akonet Sp. z o.o. ul. Rozbrat 32/37 00-429 Warszawa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change Datatype (time)
DATE_FORMAT() Doesn't seem like the most elegant solution, but it does seem to work. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I use the information from "SHOW STATUS" in a SELECT statment ?
Speaking of Perl scripts that do this: http://codenode.com/mysqlreport I almost never use SHOW STATUS directly anymore since most the values have to be transformed one way or another to really be meaningful. -Daniel Sid Lane wrote: I don't know if you can do it directly in a mysql shell like that (like you would with v$, dba_ in Oracle) but if you call a show command from PERL (via DBI) it hashes the result just like it were from a select. may not be the most elegant solution but its the best I've come up with though I'll happily blush in embarassment if there's a "direct" way I've not found... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ASP time difficulties
I've got a column called time with the datatype time. When querying the
column from asp/ado the values are the current date with a vartype of date.
query browser: select time from syslog.mailinfo limit 1
result:
"time"
"03:18:26"
asp/ado:
<%set conn=server.createobject("adodb.connection")
conn.connectionstring="dsn=mysql2"
conn.open
set rs=server.createobject("adodb.recordset")
rs.open "select time from syslog.mailinfo limit
1",conn%><%=rs("time")%><%rs.close
conn.close%>
result:
8/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Clever dump using Java
this is not exactly what you are looking for, but it is where we started on the task. http://public.pdinc.us/cordova a xml style sheet could easily be added, to create SQL DDL statements. as far as the data rows that is easy as pie too. Let us know and we could give cvs access and help on this. On Tue, 16 Aug 2005, Konrad Billewicz wrote: Hello, I would like to export a part of my MySQL database as a SQL script. I thought about mysqldump.exe but it's too simple. I need to export rows from one table which matchs simple criteria and all rows from other tables which are in relation with them. The ideal solution would be to make a SELECT query and pass it to *something*. This *something* would execute the query, analyze found rows and return SQL script creating these rows. In current project I'm using Hibernate but this part can be done using any tool or pure JDBC. How would you handle such a task? -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French Characters, Still no answer
I think I have found the problem: Doing Select @@character_set_database , results, client and connection it all returns latin1 I tried set character_set_x = utf8; for them all but it did not work, they stayed latin1 and need to be utf8 Any ideas how to change this? Thanks, James - Original Message - From: "Gleb Paharenko" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 16, 2005 11:16 AM Subject: Re: French Characters, Still no answer > Hello. > > > Also when we use EMS mysqlmanager to view the data, it is fine, it is only > > when it comes back to the tomcat server it seems. > > That means that data on the server is ok. Check the values of your > character_set_client, character_set_connection and character_set_results > variables from Tomcat's application. See: > http://dev.mysql.com/doc/mysql/en/charset-connection.html > > > > If no one has anymore ideas I think our best route is to dump it, install > > 4.0 on this server and import it back in. > > Perform an import so that all fields in your table have an utf8 character > set. And tune properly the connection variables. > > > "James Sherwood" <[EMAIL PROTECTED]> wrote: > > Sorry, dont know how I missed: http://lists.mysql.com/mysql/187794 > > > > The old msql was 4.0 and the new one is 4.1. > > > > When we first imported the data the database was in latin1(my mistake) and > > we put it into production as everything seemed to work fine. > > > > We have one site that uses french characters sometimes and they compained 1 > > week later. By that time it was too late to roll back so now we have this > > problem. > > > > We tried converting the database over to Utf8 but it did not seem to fully > > work. > > > > We can put the french characters in, even from the tomcat server, but when > > they come back out to the webpage(we have a content management system) they > > are garbled. > > > > One night we did some testing and hooked the tomcat to the old database and > > everything worked fine with french characters so it has to be the new > > database. > > > > If no one has anymore ideas I think our best route is to dump it, install > > 4.0 on this server and import it back in. > > > > Everything looks ok as far as Utf8 on the tables and such, I feel that we > > are just missing one key thing that is garbling the characters. > > > > Also when we use EMS mysqlmanager to view the data, it is fine, it is only > > when it comes back to the tomcat server it seems. > > > > Thanks for your ideas thus far, > > James > > > > > > > -- > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: French Characters, Still no answer
On Tue, 16 Aug 2005, James Sherwood wrote: I think I have found the problem: Doing Select @@character_set_database , results, client and connection it all returns latin1 I tried set character_set_x = utf8; for them all but it did not work, they stayed latin1 and need to be utf8 Any ideas how to change this? Which version of the Conenctor J are you using?, is it the latest? -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rcs udf for MySQL
Anyone out there heard of a rcs udf (or similar) for MySQL? Google is not being nice to me, too much noise in the results. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
thanks to Warren for his comment on the examples... i modified my code trying to get rigth result, but still no luck. doing a deeper research on comparing the data stored into the database and the data stored in the wav file at the same time, i notice that there is a character that is not properly represented, in fact, is not represented at all! this character is: \ (backslash). it is never written in the database, resulting on the missmatch of the sizes of the data (wavfile=176684 bytes, database=14181 bytes) i don't know how a simple character can create such a difference in the sizes! :S i was wondering if this problem may be related with the null endind problem suggested earlier by Warren. am i rigth? does anybody have any suggestions??? as you can see, i really need some help, and would be thankfull for any reply... karima On 8/15/05, Warren Young <[EMAIL PROTECTED]> wrote: > > Karima Velasquez wrote: > > do you know about any sample code on > > using BLOB columns using c++ to create querys??? > > C++, eh? I happen to be the MySQL++ maintainer. Two of its example > programs, cgi_image and load_file, deal with BLOBs. > > http://tangentsoft.net/mysql++/ > > Notice the automatic escaping, and the use of C++ strings, both of which > render the null issue irrelevant. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: French Characters, Still no answer
We are using 3.0.9 and the latest is 3.1.10 But we connect to the old 4.0 database fine, would the connector matter? James - Original Message - From: "Jason Pyeron" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 16, 2005 1:59 PM Subject: Re: French Characters, Still no answer > > > On Tue, 16 Aug 2005, James Sherwood wrote: > > > I think I have found the problem: > > > > Doing Select @@character_set_database , results, client and connection it > > all returns > > latin1 > > > > I tried set character_set_x = utf8; for them all but it did not work, > > they stayed latin1 and need to be utf8 > > > > Any ideas how to change this? > > > > Which version of the Conenctor J are you using?, is it the latest? > > > -- > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > - - > - Jason Pyeron PD Inc. http://www.pdinc.us - > - Partner & Sr. Manager 7 West 24th Street #100 - > - +1 (443) 921-0381 Baltimore, Maryland 21218 - > - - > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > > This message is for the designated recipient only and may contain > privileged, proprietary, or otherwise private information. If you > have received it in error, purge the message from your system and > notify the sender immediately. Any other use of the email by you > is prohibited. > > -- > 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]
Re: convert varchar to char
Pooly wrote: Damnit ! Thanks for pointing it, I forgot these things. But it's a bit more subtle : If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. I get it working with : ALTER TABLE sessions MODIFY id char(32) NOT NULL DEFAULT '', MODIFY ip char(8) NOT NULL DEFAULT '0'; show create table sessions; CREATE TABLE `sessions` ( `id` char(32) NOT NULL default '', `user_id` int(6) NOT NULL default '0', `ip` char(8) NOT NULL default '0', `lastseen` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `expire` timestamp NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`), KEY `user_id2` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 and now I've got fixed-length rows ! or you could have just done: alter table sessions row_format=fixed; -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does this fail
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 16 August 2005 15:04 To: Andrew Hargreaves Cc: mysql@lists.mysql.com Subject: Re: Why does this fail "Andrew Hargreaves" <[EMAIL PROTECTED]> wrote on 08/16/2005 09:13:56 AM: > Can anyone explain why this SQL statement would fail? A friend was > asked it > in a pre-interview assessment and it threw him a bit. > > SELECT T1.BldgName T1.StreetName, T1.Locality, T1.PostTown from > PstlAddr T1 > Where T1.City='London' AND NOT EXISTS (SELECT T2.Pstl_Address_PK from > PrsnAddress T2) > > Several people have already responded with the comma vs. alias (T1.BldgName...). Moving on... I believe that if there is a single record in PrsnAddress, the whole query will return nothing. That is because the inner query of the EXISTS clause is not linked to any term from the outer query (as others have mentioned). However, the converse should be true that if PrsnAddress is empty, then you will get the results you expect (WHERE T1.City='London'...) because the NOT EXISTS would evaluate as TRUE. This is a good example why all developers should test for both positive and negative results from their queries. If someone had only tested with an empty PrsnAddress table, they could have been tricked into thinking the query operated correctly. Thanks for all your replies. The typos were my fault as I was in a bit of a rush and didn't check the syntax properly. I think the main problem relates to the correlation of the sub qurty as was mentioned previously. An interesting question nonetheless. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_hex_string() c-api
Hello all, can somebody explain to me the c-api mysql_hex_string(). when i debug my example in DDD i get the full sql-dml (insert) as a hex-value; which this function returns. putting a 0x in front of the hex and use it in a mysql_real_query() returns an error. or am i doing it wrong! should i use the values which are used in the insert-string and convert them into hex using this function? the example provided in the documentation is not realy helpfull though. hopefully i am clear, otherwise please let me know. Thank you. Danny Stolle Netherlands. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GIS Applications
Is there any one out there using MySql for GIS applications? Any one using the spatial features? Just taking an informal survey. -- http://www.douglassdavis.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_hex_string() c-api
At 19:43 +0200 8/16/05, Danny Stolle wrote: Hello all, can somebody explain to me the c-api mysql_hex_string(). when i debug my example in DDD i get the full sql-dml (insert) as a hex-value; which this function returns. putting a 0x in front of the hex and use it in a mysql_real_query() returns an error. or am i doing it wrong! should i use the values which are used in the insert-string and convert them into hex using this function? the example provided in the documentation is not realy helpfull though. hopefully i am clear, otherwise please let me know. It take an argument string such as "abc" and returns it as a string representing the hex digits for the characters in the string. Input: "abc" Output: "616263" You should take that result and insert it into a SQL statement where a data value is allowable, preceded by "0x". It's not intended that you pass mysql_hex_string() a complete SQL statement. -- 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]
security question CAN-2005-0709 CAN-2005-0710 CAN-2005-0711
Hi, I have installed binary mysql version 3.23.58 downloaded from www.mysql.org. In changelog from the documentation say that the release is from september 2003 and the security bug is in March 2005. What can I do ? How mysql provide updates? Thanks!! = Security info: http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0709 http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0710 http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0711 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rcs udf for MySQL
Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 01:01:03 PM: > > Anyone out there heard of a rcs udf (or similar) for MySQL? > > Google is not being nice to me, too much noise in the results. > > -- rcs=? Reaction Conrol System? Rear Cannon Sights? udf = User Defined Function? (just making sure) What is it, what does it do and where did you hear of such a thing? Maybe we could help if we had more information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: security question CAN-2005-0709 CAN-2005-0710 CAN-2005-0711
Alejandro <[EMAIL PROTECTED]> wrote on 08/16/2005 03:01:59 PM: > Hi, > > I have installed binary mysql version 3.23.58 downloaded from www.mysql.org. > In changelog from the documentation say that the release is from > september 2003 and the security bug is in March 2005. > What can I do ? How mysql provide updates? > Thanks!! > > = > Security info: > http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0709 > http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0710 > http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0711 > MySQL has moved WELL past the 3.23.x lineage and is getting close to retiring the 4.0.x lineage (it's only a rumor). So I suggest you update your installation, paying attention to all of the version-to-version gotchas listed here: http://dev.mysql.com/doc/mysql/en/upgrade.html There is little to no activity in support of the 3.23.x version of MySQL. Is there a "VERY GOOD" reason why you cannot or do not want to upgrade? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: character encoding
Karima Velasquez wrote: character is: \ (backslash). Of course that will cause problems. This is the escape character in SQL, used extensively when inserting BLOB data. Again, I believe MySQL++ would have prevented this problem, because its escape manipulator would have escaped the backslashes. If you don't want to use MySQL++, then use mysql_escape_string() (or mysql_real_escape_string()) in the C API, which MySQL++'s escape manipulator is based on. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rcs udf for MySQL
sorry, RCS= Revision Control Sytems it is what backends things like cvs. and yes user defined functions http://www.cs.purdue.edu/homes/trinkle/RCS/ On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 01:01:03 PM: Anyone out there heard of a rcs udf (or similar) for MySQL? Google is not being nice to me, too much noise in the results. -- rcs=? Reaction Conrol System? Rear Cannon Sights? udf = User Defined Function? (just making sure) What is it, what does it do and where did you hear of such a thing? Maybe we could help if we had more information. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inf and -inf values
Hi all, I'm trying to INSERT values inf or -inf into a field (double) in MySQL version 4.1.13 but it has not been possible. MySQL deny the insertion. Can anyone give me any suggestions to solving this problem ? Thanks -- / (=\Daniel Guariz Pinheiro \=) /Laboratório de Bioinformática /=) Fundação Hemocentro de Ribeirão Preto (=/Rua Tenente Catão Roxo, 2501 Ribeirão Preto - SP - Brazil / Phone: (55) 16 21019365 Fax: (55) 16 21019309 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: security question CAN-2005-0709 CAN-2005-0710 CAN-2005-0711
I agree with you, I will upgrade . Thanks for the advice. On 8/16/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > Alejandro <[EMAIL PROTECTED]> wrote on 08/16/2005 03:01:59 PM: > > > > Hi, > > > > I have installed binary mysql version 3.23.58 downloaded from > www.mysql.org. > > In changelog from the documentation say that the release is from > > september 2003 and the security bug is in March 2005. > > What can I do ? How mysql provide updates? > > Thanks!! > > > > = > > Security info: > > > http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0709 > > > http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0710 > > > http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0711 > > > > MySQL has moved WELL past the 3.23.x lineage and is getting close to > retiring the 4.0.x lineage (it's only a rumor). So I suggest you update your > installation, paying attention to all of the version-to-version gotchas > listed here: > > http://dev.mysql.com/doc/mysql/en/upgrade.html > > There is little to no activity in support of the 3.23.x version of MySQL. Is > there a "VERY GOOD" reason why you cannot or do not want to upgrade? > > 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: rcs udf for MySQL
After following your link, I am sure that some RCS systems that use MySQL as a backend *may* use UDFs as part of their persistence logic, but those would be specific to the RCS product you are curious about. There aren't any "generic" UDFs that will apply to all verson contol front-ends. Does the one in the link use any UDFs? I couldn't tell from what I read and to be perfectly honest, I couldn't figure out from the reading that RCS actually used MySQL at all. Is this what you wanted to know or have I still missed something vital to the question? It's been one of those days for me. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 02:15:34 PM: > sorry, > > RCS= Revision Control Sytems > > it is what backends things like cvs. > > and yes user defined functions > > http://www.cs.purdue.edu/homes/trinkle/RCS/ > > On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: > > > Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 01:01:03 PM: > > > >> > >> Anyone out there heard of a rcs udf (or similar) for MySQL? > >> > >> Google is not being nice to me, too much noise in the results. > >> > >> -- > > > > rcs=? Reaction Conrol System? Rear Cannon Sights? > > > > udf = User Defined Function? (just making sure) > > > > What is it, what does it do and where did you hear of such a thing? Maybe > > we could help if we had more information. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > -- > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > - - > - Jason Pyeron PD Inc. http://www.pdinc.us - > - Partner & Sr. Manager 7 West 24th Street #100 - > - +1 (443) 921-0381 Baltimore, Maryland 21218 - > - - > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- > > This message is for the designated recipient only and may contain > privileged, proprietary, or otherwise private information. If you > have received it in error, purge the message from your system and > notify the sender immediately. Any other use of the email by you > is prohibited. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Innodb Settings
Hello I am looking to determine the best Innodb Settings for our MySQL Instances. Specifically, the settings for innodb_buffer_pool_size and innodb_log_file_size. I have read the manual and understand how they work. I am looking for additional information that would help me determine their values instance by instance. In other words, if I only have 2 Innodb tables with low data and xaction rates, then I should have them set to x. If there is some additional determining factors beside the number of tables, rows and expected transaction rate, I would like to know them as well. Thanks in advance! Gabe <><><><><><><><><><><><><><><><><><><><><> "There are no problems, only solutions." Gabe Tucker Bloomberg LP (609) 750 6668 - P (646) 268 5681 - F <><><><><><><><><><><><><><><><><><><><><> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_hex_string() c-api
I thought it was something like that ... I just found it strange allready. I'll give it a try, thanx. Danny Paul DuBois wrote: At 19:43 +0200 8/16/05, Danny Stolle wrote: Hello all, can somebody explain to me the c-api mysql_hex_string(). when i debug my example in DDD i get the full sql-dml (insert) as a hex-value; which this function returns. putting a 0x in front of the hex and use it in a mysql_real_query() returns an error. or am i doing it wrong! should i use the values which are used in the insert-string and convert them into hex using this function? the example provided in the documentation is not realy helpfull though. hopefully i am clear, otherwise please let me know. It take an argument string such as "abc" and returns it as a string representing the hex digits for the characters in the string. Input: "abc" Output: "616263" You should take that result and insert it into a SQL statement where a data value is allowable, preceded by "0x". It's not intended that you pass mysql_hex_string() a complete SQL statement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cannot drop database
On Aug 15, 2005, at 5:07 AM, Logan, David (SST - Adelaide) wrote: Hi Gary, If you are running unix (or variants thereof), you can go to the data directory and remove it at the operating system level if the mysql client can't do it. Not sure about windows though but I would think the same thing would apply. If you do that and then do a show databases, it should be gone. David is right in that once you do this it won't show in the show databases list any more, but if you use a data engine that stores data in a shared table space (such as InnoDB to name one) doing this deletes the database as far as MySQL is concerned, but the data is still sitting in the shared spaces with no practical way of getting it out and freeing your space. The correct way will be to make sure you enclose the database name in backticks as previously mentioned in this thread. Best Regards, Bruce Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Gary Huntress [mailto:[EMAIL PROTECTED] Sent: Monday, 15 August 2005 9:09 AM To: mysql@lists.mysql.com Subject: cannot drop database I need to drop a database named ÃáãÃáà using the mysql client. I'm getting "you have an error in your sql syntax" for the command DROP database ÃáãÃáÃ; I'm sure this is a character set issue. How can I drop this database? Regards, Gary H. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[sorta off topic] Re: rcs udf for MySQL
On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: After following your link, I am sure that some RCS systems that use MySQL as a backend *may* use UDFs as part of their persistence logic, but those would be specific to the RCS product you are curious about. There aren't any "generic" UDFs that will apply to all verson contol front-ends. Does the one in the link use any UDFs? I couldn't tell from what I read and to be perfectly honest, I couldn't figure out from the reading that RCS actually used MySQL at all. Is this what you wanted to know or have I still missed something vital to the question? It's been one of those days for me. Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 02:15:34 PM: http://www.cs.purdue.edu/homes/trinkle/RCS/ RCS does not use MySQL or any other SQL engine. I am looking to use a SQL backend for RCS. RCS is a specific tool unlike source control management (SCM), it is a text file database. There are no SQL based RCS implimentations that I know of, that was why I asked in the first place. A RCS file is comprised of: meta data bout the state of the file current source 0..N reverse deltas from current source meta data about the various revisions represented, including who, when, why. ex: head1.2; access; symbols; locks; strict; comment @# @; 1.2 date2005.07.31.20.05.02;author jpyeron; state Exp; branches; next1.1; 1.1 date2005.07.07.05.20.31;author jpyeron; state Exp; branches; next; desc @@ 1.2 log @my comment about this version is a BLOB TEXT delimited by the (at)s @ text @Here it the contents of my file it is many lines long line 3 line 4 line 5 line 6 line 7 line 8 line 9 then terminated by the (at) @ 1.1 log @Initial version @ text @d9 1 a9 1 line 9 original version @ -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Saving creation date
Hi, I want to save date and time of the creation of a row in a field. That could be handled by a timestamp, I know that. But I don't want the value to be updated automatically after an update. I tried . creation datetime not null default now() . in the create table statement, but got an error. I know, that I'm able to use creation=now() in the insert statement, but parts of the code are encrypted and I can't change them. So I need a way to handle that without modifying the statements. Got any ideas? Frank Busch
Re: [sorta off topic] Re: rcs udf for MySQL
Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 04:17:15 PM: > On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: > > > After following your link, I am sure that some RCS systems that use MySQL > > as a backend *may* use UDFs as part of their persistence logic, but those > > would be specific to the RCS product you are curious about. There aren't > > any "generic" UDFs that will apply to all verson contol front-ends. Does > > the one in the link use any UDFs? I couldn't tell from what I read and to > > be perfectly honest, I couldn't figure out from the reading that RCS > > actually used MySQL at all. > > > > Is this what you wanted to know or have I still missed something vital to > > the question? It's been one of those days for me. > > > > Jason Pyeron <[EMAIL PROTECTED]> wrote on 08/16/2005 02:15:34 PM: > > > >> http://www.cs.purdue.edu/homes/trinkle/RCS/ > > RCS does not use MySQL or any other SQL engine. > > I am looking to use a SQL backend for RCS. > > RCS is a specific tool unlike source control management (SCM), it is a > text file database. There are no SQL based RCS implimentations that I know > of, that was why I asked in the first place. > > > > A RCS file is comprised of: > > meta data bout the state of the file > current source > 0..N reverse deltas from current source > meta data about the various revisions represented, >including who, when, why. > > ex: > > head1.2; > access; > symbols; > locks; strict; > comment @# @; > > > 1.2 > date2005.07.31.20.05.02;author jpyeron; state Exp; > branches; > next1.1; > > 1.1 > date2005.07.07.05.20.31;author jpyeron; state Exp; > branches; > next; > > desc > @@ > > > 1.2 > log > @my comment about this > version is a BLOB TEXT > delimited by the (at)s > @ > text > @Here it the contents of my file > it is many lines long > line 3 > line 4 > line 5 > line 6 > line 7 > line 8 > line 9 then terminated by the (at) > @ > > > 1.1 > log > @Initial version > @ > text > @d9 1 > a9 1 > line 9 original version > @ > > Now... don't be so hasty. MS SQL has a form of RCS you can use to check in and check out stored procedures and other design elements using Visual Source Safe (VSS). VSS acutally uses extended stored procedures (the mutant cousins of UDFs) to perform the locking and unlocking of various database elements. I think this might count as one version of a database-based RCS (even though I think the deltas are stored elsewhere as text). The metadata headers you describe should be easily convertable to database fields and the actual delta data could either be stored in a blob (one blob per set of changes) or separately listed in their own table (one set of delta rows per set of changes). Doing an RCS with a database is not complex to design but implementaion may be slower than you like just due to the overhead of running a SQL query (or mulitple queries) vs. the speed of directly parsing local text files. A future optimization may be for you to use the HANDLER interface with MyISAM tables in order to quickly retrieve your "delta blobs". Just something to think about. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: how to arrange my table in another direction
Hello. >I want the values in the 'type' column of the first table to be the >column name in the second table. Please, answer next time to the list as well. In my opinion, it is easier to prepare the create statement on the client side than on the server. And does this table have any sense, considering the relational model? -- 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: [sorta off topic] Re: rcs udf for MySQL
On Tue, 16 Aug 2005 [EMAIL PROTECTED] wrote: Now... don't be so hasty. MS SQL has a form of RCS you can use to check in and check out stored procedures and other design elements using Visual Source Safe (VSS). VSS acutally uses extended stored procedures (the mutant cousins of UDFs) to perform the locking and unlocking of various database elements. I think this might count as one version of a database-based RCS (even though I think the deltas are stored elsewhere as text). I was looking for "RCS", I am looking to retool our CVS repository. The metadata headers you describe should be easily convertable to database fields and the actual delta data could either be stored in a blob (one blob per set of changes) or separately listed in their own table (one set of delta rows per set of changes). Started on this a few hours ago. Paused to look at Subversion, they use Berkley DB. Doing an RCS with a database is not complex to design but implementaion may be slower than you like just due to the overhead of running a SQL query (or mulitple queries) vs. the speed of directly parsing local text files. It would be a lot faster, since you could do the delta row set collation faster, if stored properly. See page 8 (section 3.2) of "RCS A System for Version Control" by Walter F. Tichy 1991. A future optimization may be for you to use the HANDLER interface with MyISAM tables in order to quickly retrieve your "delta blobs". Just something to think about. Using Innodb, for transactions. -jason -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner & Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: character encoding
uhmmm... i'll give a try to mysql_real_escape_string(), which i understand formats the data into a valid sql string (without any special characters, like \ and ' ). shoud i use an analog function when retriving the data? because if i "encode" the data, shouldn't i "decode" it? if so, which function should i use? thanks for your replys karima On 8/16/05, Warren Young <[EMAIL PROTECTED]> wrote: > > Karima Velasquez wrote: > > character is: \ (backslash). > > Of course that will cause problems. This is the escape character in > SQL, used extensively when inserting BLOB data. Again, I believe > MySQL++ would have prevented this problem, because its escape > manipulator would have escaped the backslashes. > > If you don't want to use MySQL++, then use mysql_escape_string() (or > mysql_real_escape_string()) in the C API, which MySQL++'s escape > manipulator is based on. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > >
Re: character encoding
Karima Velasquez wrote: uhmmm... i'll give a try to mysql_real_escape_string(), which i understand formats the data into a valid sql string (without any special characters, like \ and ' ). shoud i use an analog function when retriving the data? because if i "encode" the data, shouldn't i "decode" it? The mysql_real_escape_string() function simply escapes special characters. It will turn your single backslash into a double backslash, which is interpreted as an actual backslash rather than a special character. When you retrieve the data there is no need to decode it with any special function. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to arrange my table in another direction
Hello. >Please, answer next time to the list as well. In my opinion, it is easier to prepare the create statement on the client side than on the server. And does this table have any sense, considering the relational model? Sorry,I think you misunderstand my purpose,table2 is my query result,in fact my trouble is id customer goods1 price1 goods2 price2 goods3 price3 1 miketv 100 car 100 bike 20 2 jin pc 200psp 200 x360 299 i can't change the table,so I want a query,after the query,the value in goods1 goods2 become the column name,then their value are price1 and price2,i try many methods,but i can't get the result - DO YOU YAHOO!? 雅虎免费G邮箱－No.1的防毒防垃圾超大邮箱
Re: 4.0 -> 4.1 lose timestamp [fixed]
the issue was because i was viewing the database through cocoamysql, which registered the timestamps as null. stupid me viewing the database through the commandline showed up fine --- Hunter Peress [EMAIL PROTECTED] Web Programer The Santa Fe New Mexican, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT question - query for records over a series of dates
I have a table full of data... a log of sorts. Each row has a timestamp. I want to generate some reports based on this data. For example I want a COUNT(*) of the rows for each day for the past week, 30 days, 12 months etc. I have no problem generating the query but I am stuck on a creative way to deal with the periods with no data. For example: SELECT COUNT(*) as hits, DATE(date_impression) as date FROM table GROUP BY date +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now the problem is that there could be days with no data (the 14th in my example). Ideally I want to show the last 7 days (or what ever period I want) and show the COUNT(*) including the days with no data like... +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | | 0 | 2005-08-14 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now I can manipulate the data afterwards (i.e. look for empty rows via PHP where this is going to end up...) but it would be much easier to get the data direct from MySQL. Is there any (easy) way to do this in MySQL? Dan T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
Dan Tappin wrote: I have a table full of data... a log of sorts. Each row has a timestamp. I want to generate some reports based on this data. For example I want a COUNT(*) of the rows for each day for the past week, 30 days, 12 months etc. I have no problem generating the query but I am stuck on a creative way to deal with the periods with no data. For example: SELECT COUNT(*) as hits, DATE(date_impression) as date FROM table GROUP BY date +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now the problem is that there could be days with no data (the 14th in my example). Ideally I want to show the last 7 days (or what ever period I want) and show the COUNT(*) including the days with no data like... +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | | 0 | 2005-08-14 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now I can manipulate the data afterwards (i.e. look for empty rows via PHP where this is going to end up...) but it would be much easier to get the data direct from MySQL. Is there any (easy) way to do this in MySQL? Dan T Not that I know of. When I have to do things like this, I write a script which does a separate query per day. Unfortunately PHP's date functions are pretty horrible, so I also use mysql as a calculator to find the next day in my loop. This is terribly inefficient, but very easy, and our server is high powered and under no load :) Maybe someone has a better idea though. -- 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]
Re: how to arrange my table in another direction
>My table is: >type price >car 1000 >bike 100 >I want the result: >car bike >1000 100 You seem to want to turn rows into columns. That's often called a "pivot table". There's a step-by-step for pivot tables at http://www.artfulsoftware.com/queries.php#24. There're other pivot table examples on the same page. PB No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 8/15/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT question - query for records over a series of dates
Dan Tappin wrote: I have a table full of data... a log of sorts. Each row has a timestamp. I want to generate some reports based on this data. For example I want a COUNT(*) of the rows for each day for the past week, 30 days, 12 months etc. I have no problem generating the query but I am stuck on a creative way to deal with the periods with no data. For example: SELECT COUNT(*) as hits, DATE(date_impression) as date FROM table GROUP BY date +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now the problem is that there could be days with no data (the 14th in my example). Ideally I want to show the last 7 days (or what ever period I want) and show the COUNT(*) including the days with no data like... +---+-+ |hits |date | +---+-+ |39 | 2005-08-12 | |27 | 2005-08-13 | | 0 | 2005-08-14 | |38 | 2005-08-15 | |28 | 2005-08-16 | +---+-+ Now I can manipulate the data afterwards (i.e. look for empty rows via PHP where this is going to end up...) but it would be much easier to get the data direct from MySQL. Is there any (easy) way to do this in MySQL? Dan T Add a table: CREATE TABLE `dates` (`date` DATE, UNIQUE KEY `date_idx` (`date`) ); Insert one row into dates for each day. Now you can use something like this: SELECT dates.date, COUNT(*) as hits FROM dates LEFT JOIN table on dates.date = DATE(table.date_impression) WHERE dates.date BETWEEN '2005-08-12' AND '2005-08-16' GROUP BY dates.date; Populating the dates table initially is a small (one-time) pain. You could keep it filled with a once-a-day script to insert the current date. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
perl run-all-test error
hello i just installed mysql-standard-4.1.13-pc-linux-gnu-i686.tar.gz then try typing the command perl run-all-test when this is error message came up. below is the error message. install_driver(mysql) failed: Can't load '/usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi/auto/DBD/mysql/mys ql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.5/i386-linux-thread-multi/DynaLoader.pm line 230. at (eval 51) line 3 Compilation failed in require at (eval 51) line 3. Perhaps a required shared library or dll isn't installed where expected at /usr/src/webserver/mysql-standard-4.1.13-pc-linux-gnu-i686/sql-bench/server- cfg line 247 what library i'm missing. also can you give me the URL address. rgds, Joeffrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
