Re: Installation Gone Haywire
-Original Message- From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tue, 27 Feb 2007 7:34 PM Subject: Re: Installation Gone Haywire have you run mysql_install_db on your production server? I ran that, but I still got the same problem: # Starting mysqld daemon with databases from /var/db/mysql STOPPING server from pid file /var/db/mysql/example.com.pid 070228 02:07:27 mysqld ended [1]Done /usr/local/bin/mysqld_safe TIA, Tony Check out the new AOL. Most comprehensive set of free safety and security tools, free access to millions of high-quality videos from across the web, free AOL Mail and more.
charactersets
hi, by now i've used mysql 4.0 now i have to switch to 5.0. my problem is that the old databases are in latin1 and all my php code does not care about character sets as it just used to work ... i was able to load the datat to mysql 5.0 with latin1 encoding. aleast mysql shows latin1 as characterset for my tables. but php connects with UTF-8 and the default character set i UTF-8 too. i've tried to set the session charset to latin1 without success. so here are my questions: 1. is ist possible to switch mysql 5.0 completely to UTF-8 ? 2. an option would be to convert my data from latin1 to UTF-8 how can i do this ? TIA matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation Gone Haywire
-Original Message- From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tue, 27 Feb 2007 7:34 PM Subject: Re: Installation Gone Haywire have you run mysql_install_db on your production server? Let me add more to my response. After running your command, I have these three problems (from the error log): 1) MySQL still complains about the 0 size of the ibdata1 file I added to satisfy its complaint for the same; 2) MySQL complains and shuts down because it demands a pid file, but when I added one it just deleted it and still shut down; 3) MySQL now is asking for a general_log.frm file. Here is my question/problem: Why is MySQL asking for all these files?? It didn't build them out when I built the FreeBSD port. It didn't ask for them when I built a running copy on this server before I accidentally deleted (and then rebuilt from a backup) the /usr/local/include dir. It didn't ask for them when I built a running copy on another identical server. Nor did I run your command on any of those other installations, and everything worked fine. So, why does it ask for them now? This seems like a fundamental problem. Awaiting Help, Tony # Starting mysqld daemon with databases from /var/db/mysql STOPPING server from pid file /var/db/mysql/example.com.pid 070228 02:07:27 mysqld ended [1]Done /usr/local/bin/mysqld_safe TIA, Tony Check out the new AOL. Most comprehensive set of free safety and security tools, free access to millions of high-quality videos from across the web, free AOL Mail and more.
Does casing of Table names matter on Linux
Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Pl. help me, Thanks, Abhishek jain
Re: Does casing of Table names matter on Linux
On Wed, 2007-02-28 at 15:05 +0530, abhishek jain wrote: Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Yes.. There is a difference. Casing Matters in *nix. However there is an option for turning if off. # http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html lower_case_table_names = 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored proc permissions question
I have a set of tables that contain sensitive user information. I need to use this data for validation BUT I don't want the end user to have access to read this data. In MSSQL I used to be able to create a stored proc to do the work (even though the user didn't have access to the table). I was wondering if there is a way to do this in MySQL. In a nut shell, this table contains user account information included encoded password. I want to be able to pass a username and password to the stored proc and return the valid user ID (or no RS of none is found). Basically, here is the stored proc. It works, but only for root. Is it possible to allow user to execute this without having SELECT access to the table users? If so, how? CREATE [EMAIL PROTECTED] PROCEDURE `sp_testlogin`( P_user_name VARCHAR(32), P_password VARCHAR(32) ) BEGIN SELECT user_id, user_fullname, date_last_login FROM users WHERE user_name = P_user_name AND user_password = P_password; END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does casing of Table names matter on Linux
Hi, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Yes, this is a problem on Linux if you're using MyISAM etc, anything that gets mapped to a file system filename. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ 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: Does casing of Table names matter on Linux
On Wed, Feb 28, 2007 at 03:05:54PM +0530, abhishek jain wrote: Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Pl. help me, Hi, Windows is by the way not case sensitive, Linux/Unix is always case sensitive. The MySQL tables are files in filesystem in restrictions for the os you use. It is better, maybe for portable code, to use general uppercase or lowercase names for tables. Greetings Mario -- - | havelsoft.com - Ihr Service Partner für Open Source | | Tel: 033876-21 966 | | Notruf: 0173-277 33 60 | | http://www.havelsoft.com| | | | Inhaber: Mario Günterberg | | Mützlitzer Strasse 19 | | 14715 Märkisch Luch | - pgpWjdEfW5beo.pgp Description: PGP signature
Re: charactersets
On Wed, Feb 28, 2007 at 09:39:40AM +0100, Matthias Henze wrote: hi, 1. is ist possible to switch mysql 5.0 completely to UTF-8 ? Yes. 2. an option would be to convert my data from latin1 to UTF-8 how can i do this ? Export your 4.x databse with mysqldump and convert it with iconv. Greetings Mario -- - | havelsoft.com - Ihr Service Partner für Open Source | | Tel: 033876-21 966 | | Notruf: 0173-277 33 60 | | http://www.havelsoft.com| | | | Inhaber: Mario Günterberg | | Mützlitzer Strasse 19 | | 14715 Märkisch Luch | - pgp7hqGVSbbap.pgp Description: PGP signature
Re: Stored proc permissions question
On 2007-02-28 Gary W. Smith wrote: Basically, here is the stored proc. It works, but only for root. Is it possible to allow user to execute this without having SELECT access to the table users? If so, how? Do you know about the SQL SECURITY { DEFINER | INVOKER } options to CREATE PROCEDURE? With them it should be possible to * deny selects to users on the tables * allow selects to users to this procedure * having the procedure itself working with the rights of root/definer bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: myisamcheck
Payne [EMAIL PROTECTED] writes: Hi, I am currently using SuSE and like to be able use myisamcheck when start up mysql. Is there a command that I can added to my init.d script that would do that. MySQL Debian package[0] does this. The init.d script is just a bash script, so you may add whatever you want. [0] http://packages.qa.debian.org/m/mysql-dfsg-5.0.html Thanks, Payne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Practice: Timezones and web development
Does anyone have any resources, guides, insight into the best practice for storing date/time information when developing a custom web app? I am mainly concerned with how the TZ should be stored? Should it go in as UTC and the code accounts for the user TZ? How does one handle tracking the users Daylight Savings etc Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from SPARC/Solaris to AMD x86
Hi list, As part of a server virtualization project, we are going to migrate MySQL from SPARC/Solaris 8 to AMD/x86 Solaris 10. I seem to remember that it is possible to: - stop the SPARC server - move the mount point of the MySQL data directory to the Solaris/x86 server - start the AMD x86 server ...without running into architecture (little/big endian) issues. Am I right? Is this written out somewhere on dev.mysql.com? Thanks, Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Key buffer partially unused - why
Hi, I have allocated 500MB to key_buffer_size, but only 324MB is in use (64%). Am I right to assume that this can mean one of the following: (1) all indexes have already been cached and together they are just 324 MB, Do find /var/lib/mysql -name *.MYI -ls (if you're using only MyISAM) to get a first hint on how much indices you actually have. Heh, thanks. I could have thought about that out myself. /sheepish (2) there is a limiting variable (open_files, inodb_open_files, for example) that prevents the key buffer to be more fully utilized. You can check at least open_files in SHOW STATUS IIRC. Key buffer usage is often hitting 100%, and just 0.01 tables are opened per second. Number of queries/second is 269. BTW, try mysqlreport to find other bottlenecks. Really good tool :) Martijn -- The information contained in this communication and any attachments is confidential and may be privileged, and is for the sole use of the intended recipient(s). Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please notify the sender immediately by replying to this message and destroy all copies of this message and any attachments. ASML is neither liable for the proper and complete transmission of the information contained in this communication, nor for any delay in its receipt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL require patch for Daylight Savings Time 2007 change
I am running Apache HTTP 1.3 with PHP 4.2.2 and MySQL 3.23. Do I need to apply any kind of patches to my MySQL related to Daylight Savings Time 2007 change? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com http://www.NaviSite.com This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited.
Re: Adding a new autoincrement field to an existing table
Grant Griffith wrote: I am trying to add an autoincrement field to a table that already exists and I keep receiving errors when trying to do it. Can someone point me in the right direction on how I can do this? I have access via Webadmin and phpMyAdmin, so I can try it however I need to. ALTER TABLE [table] ADD [fieldname] [integer type] not null auto_increment key; or if you want it to be the primary key: ALTER TABLE [table] ADD [fieldname] [integer type] not null auto_increment primary key; Keep in mind that you can have only one auto_increment column and it must be part of a key. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: Does MySQL require patch for Daylight Savings Time 2007 change
As far as I know, MySQL does not need a TZ patch (unless you use specific/custom Time Zone information), but your OS does. The best way to check is to run: mysql SHOW VARIABLES LIKE 'TIME_ZONE'; If it says SYSTEM, then you need only patch your OS. (Patching the OS is [OT] for this list). Here's the section from the manual in regards to 3.23 Time Zones. http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html J.R. From: Causevic, Dzenan [EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:29 AM To: mysql@lists.mysql.com Subject: Does MySQL require patch for Daylight Savings Time 2007 change I am running Apache HTTP 1.3 with PHP 4.2.2 and MySQL 3.23. Do I need to apply any kind of patches to my MySQL related to Daylight Savings Time 2007 change? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited.
RE: Does MySQL require patch for Daylight Savings Time 2007 change
Version 4.1.3 - relies on the O/S for DST Quote from http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html For versions of MySQL older than 4.1.3 that do not have time zone support, the server always tracks the operating system time (much like a time zone setting of SYSTEM in 4.1.3 and up). Assuming that the server host itself has its operating system updated to handle any changes to Daylight Saving Time rules, the MySQL server should know the correct time. Version = 4.1.3 - DST is set in the DBMS Quote from http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html The operating system time affects the value that the MySQL server uses for times if its time zone is set to SYSTEM. Make sure that your operating system is using the latest time zone information. For most operating systems, the latest update or service pack prepares your system for the time changes. Check the Web site for your operating system vendor for an update that addresses the time changes. If your system draws its time from a public clock on the internet, it should adjust the time automatically. -Original Message- From: Causevic, Dzenan [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:29 AM To: mysql@lists.mysql.com Subject: Does MySQL require patch for Daylight Savings Time 2007 change I am running Apache HTTP 1.3 with PHP 4.2.2 and MySQL 3.23. Do I need to apply any kind of patches to my MySQL related to Daylight Savings Time 2007 change? NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et nest destiné qu'à la personne à qui il est adressé. Si vous nêtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement lexpéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt derreur puisque les informations quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Does MySQL require patch for Daylight Savings Time 2007 change
Okay but after I patch OS do I still need to load time zone tables with following command: shell mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Is this still necessary? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:40 AM To: mysql@lists.mysql.com Subject: re: Does MySQL require patch for Daylight Savings Time 2007 change As far as I know, MySQL does not need a TZ patch (unless you use specific/custom Time Zone information), but your OS does. The best way to check is to run: mysql SHOW VARIABLES LIKE 'TIME_ZONE'; If it says SYSTEM, then you need only patch your OS. (Patching the OS is [OT] for this list). Here's the section from the manual in regards to 3.23 Time Zones. http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html J.R. From: Causevic, Dzenan [EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:29 AM To: mysql@lists.mysql.com Subject: Does MySQL require patch for Daylight Savings Time 2007 change I am running Apache HTTP 1.3 with PHP 4.2.2 and MySQL 3.23. Do I need to apply any kind of patches to my MySQL related to Daylight Savings Time 2007 change? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited. This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get query result...
What I want is to get next result: Col.B | Col.A | Desription not in result query A | -8 | -10-(-2) B | -2 | -3-(-1) You need an ordering key; otherwise order is indeterminate. Still, comparisons based on row sequence are often easier to code in the application. A SQL hack is possible, but it may not perform very well. If the ordering key is a column k which, following your example, takes the values 1,2,3,4, and if your table follows the pattern of your example, this query yields your desired result: SELECT t1.b, t1.a AS B, (SELECT t2.a FROM tbl AS t2 WHERE t2.k=(t1.k-1)) AS delta FROM tbl AS t1 WHERE MOD(t1.k,2)=0; If your MySQL version is pre-4.1, you could accomplish this with user variables, but it would be messier. PB - aljosa wrote: I have table with next data: Col.A | Col.B | Col.C -10 | A | D -2 | A | R -3 | B | D -1 | B | R What I want is to get next result: Col.B | Col.A | Desription not in result query A | -8 | -10-(-2) B | -2 | -3-(-1) I am trying for several days without any progress. Can anybody halp me? Actualy I Have a problem with result Col.A. Thanks -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.412 / Virus Database: 268.18.4/705 - Release Date: 2/27/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does MySQL require patch for Daylight Savings Time 2007 change
I don't believe so, since the version of MySQL you are using relies on the system's time. I am using 4.1 and I am setup to use system's time and my timezone tables in the mysql schema are empty. I would take a look at those on your installation. You can also run the 2nd command below to see if the system returns proper time, both unix timestamp should be equal... mysql SHOW VARIABLES LIKE 'time_zone'; +---++ | Variable_name | Value | +---++ | time_zone | SYSTEM | +---++ 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 01:00:00'),UNIX_TIMESTAMP('2007-03-11 02:00:00'); +---+--- + | UNIX_TIMESTAMP('2007-03-11 01:00:00') | UNIX_TIMESTAMP('2007-03-11 02:00:00') | +---+--- + |1173592800 | 1173596400 | +---+--- + 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); +---+--- + | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+--- + |1173596400 | 1173596400 | +---+--- + 1 row in set (0.00 sec) -Original Message- From: Causevic, Dzenan [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:22 AM To: mysql@lists.mysql.com Subject: FW: Does MySQL require patch for Daylight Savings Time 2007 change Okay but after I patch OS do I still need to load time zone tables with following command: shell mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Is this still necessary? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:40 AM To: mysql@lists.mysql.com Subject: re: Does MySQL require patch for Daylight Savings Time 2007 change As far as I know, MySQL does not need a TZ patch (unless you use specific/custom Time Zone information), but your OS does. The best way to check is to run: mysql SHOW VARIABLES LIKE 'TIME_ZONE'; If it says SYSTEM, then you need only patch your OS. (Patching the OS is [OT] for this list). Here's the section from the manual in regards to 3.23 Time Zones. http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html J.R. NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et nest destiné qu'à la personne à qui il est adressé. Si vous nêtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement lexpéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt derreur puisque les informations quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored proc permissions question
Do you know about the SQL SECURITY { DEFINER | INVOKER } options to CREATE PROCEDURE? With them it should be possible to * deny selects to users on the tables * allow selects to users to this procedure * having the procedure itself working with the rights of root/definer I did not, but now I do. Do in this case, I have two users. User 1 svreader has limited select access to the database (specifically tables that do prevent disclosure of information). User 2 is svreaderauth has full select access to the database. If I'm understanding the docs correct (which I'm probably not), I should create the procedure with [EMAIL PROTECTED] CREATE [EMAIL PROTECTED] PROCEDURE sp_testlogin( P_user_name VARCHAR(32), P_password VARCHAR(32) ) BEGIN SELECT user_id, user_fullname, date_last_login FROM users WHERE user_name = P_user_name AND user_password = P_password; END Is this correct? The next question is what permissions do I need to give [EMAIL PROTECTED] to just access that one permission? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rename a mysql v3 service when installing
Hy! How can I modify mysql's 3 service name via my.ini? If I try to install a new mysql, verion 3, on a computer that already has a mysql 4 or mysql 5(registered to start as a service with the name Mysql (not mysql4 or mysql5) WinMySQLAdmin 1.3 get confused and doesn't start mysql 3 because The service is already installed yes but this is mysql 5 .. not mysql3 that I need :( Before starting WinMySQLAdmin My.ini looks like this: [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-opt.exe [mysqld] #bind-address=10.0.0.2 #language=C:/MYSQL/share/your language directory #slow query log#= #tmpdir#= port=3306 #set-variable=key_buffer=16M If I am using another port(for mysaql3) I can use 2 mysqls but they cannot have the same name in services list(I need both of them because my aplication uses only mysql 3 and the other mysql is for another aplication that I cannot modify). How can I pre-modify my.ini so when winmysqladmin(from mysql v.3) starts and registers mysql 3 will use another name in services list, like mysql3 or something, but not 'mysql', or... can I start winmysqladmin 1.3(from mysql3) with some parameters to install mysql 3 with another name then 'MYSQL'? :) Thanks! -- View this message in context: http://www.nabble.com/Rename-a-mysql-v3-service-when-installing-tf3310033.html#a9207368 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does MySQL require patch for Daylight Savings Time 2007 change
Hang on a second! Doesn't EST transition to DST at 03:00? Shouldn't that query (the one for testing MySQL's time zones) be SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); ? Nonetheless, it doesn't work for me. I get a one-hour difference, just as though the DST transition never existed. My time_zone variable is set to SYSTEM, and I've verified using zdump (Linux) that the time zone tables on my server are correct. I have loaded the latest time zone information using mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql although it gave me some errors about Riyadh. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:55 AM To: Causevic, Dzenan; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change I don't believe so, since the version of MySQL you are using relies on the system's time. I am using 4.1 and I am setup to use system's time and my timezone tables in the mysql schema are empty. I would take a look at those on your installation. You can also run the 2nd command below to see if the system returns proper time, both unix timestamp should be equal... mysql SHOW VARIABLES LIKE 'time_zone'; +---++ | Variable_name | Value | +---++ | time_zone | SYSTEM | +---++ 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 01:00:00'),UNIX_TIMESTAMP('2007-03-11 02:00:00'); +---+- -- + | UNIX_TIMESTAMP('2007-03-11 01:00:00') | UNIX_TIMESTAMP('2007-03-11 02:00:00') | +---+- -- + |1173592800 | 1173596400 | +---+- -- + 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); +---+- -- + | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+- -- + |1173596400 | 1173596400 | +---+- -- + 1 row in set (0.00 sec) -Original Message- From: Causevic, Dzenan [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:22 AM To: mysql@lists.mysql.com Subject: FW: Does MySQL require patch for Daylight Savings Time 2007 change Okay but after I patch OS do I still need to load time zone tables with following command: shell mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Is this still necessary? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:40 AM To: mysql@lists.mysql.com Subject: re: Does MySQL require patch for Daylight Savings Time 2007 change As far as I know, MySQL does not need a TZ patch (unless you use specific/custom Time Zone information), but your OS does. The best way to check is to run: mysql SHOW VARIABLES LIKE 'TIME_ZONE'; If it says SYSTEM, then you need only patch your OS. (Patching the OS is [OT] for this list). Here's the section from the manual in regards to 3.23 Time Zones. http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html J.R. NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le prisent courriel contient des renseignements de nature priviligiie et confidentielle et nest destini qu'` la personne ` qui il est adressi. Si vous njtes pas le destinataire privu, vous jtes par les prisentes avisis que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez regu ce courriel par erreur, veuillez en aviser immidiatement lexpiditeur et le supprimer de votre systhme. Notez que la transmission de
RE: Stored proc permissions question
The next question is what permissions do I need to give [EMAIL PROTECTED] to just access that one permission? The sound of that thump thump thump is my head hitting the wall. There is like a single small line in the GRANT section of the how to that mentioned TABLE|PROCEDURE|FUNCTIO but none of the samples use this. GRANT EXECUTE ON PROCEDURE testdb.sp_testlogin TO [EMAIL PROTECTED]; ^ The mysql team might want to add a sample like this to the documentation so people can see how to fine tune the permissions for SP, etc. Everything seems to be working fine now Here is what I did: Created user svspexec with select, insert update, delete on testdb Created procedure with definer [EMAIL PROTECTED] Added execute procedure for testdb.sp_testlogin to user [EMAIL PROTECTED] I can now execute the store proc and get the expected result back without having to have select access to the table. I did fine many people saying that I need select access to mysql.proc for the svreader account (the account to execute the sp) but it seems to work fine without it. It this a bug that was fixed or do I have a open permission issue on my box somehow? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored proc permissions question
On 2007-02-28 Gary W. Smith wrote: ... If I'm understanding the docs correct (which I'm probably not), I should create the procedure with [EMAIL PROTECTED] CREATE [EMAIL PROTECTED] PROCEDURE Better add SQL SECURITY DEFINER to it. .. The next question is what permissions do I need to give [EMAIL PROTECTED] to just access that one permission? I've seen the table mysql.procs_priv, it's probably defined there. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored proc permissions question
Better add SQL SECURITY DEFINER to it. I noticed that it works with and without this. I have added it to the procedure. Another quick question though. Since I have added the end user that will execute the procedure it works fine, until I drop the procedure and recreate it then I have to read the execute permission for that user. Is this normal behavior? I know that I should probably be using alter instead but I was just wondering. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does MySQL require patch for Daylight Savings Time 2007 change
You have both compare 0100 vs 0200 and 0200 vs 0300 in my reply... It wasn't clear because of emails being wrapped at 80 chars. And it still doesn't work after loading the tables with mysql_tzinfo_to_sql And /etc/localtime is a symlink to the proper tz file ? Jean-Sébastien Pilon Systems Administrator Penson Financial Services Canada Inc. % Tel.: 514.841.9724 #269 % Cell: 514.771.0365 -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 12:13 PM To: Jean-Sebastien Pilon; 'Causevic, Dzenan'; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change Hang on a second! Doesn't EST transition to DST at 03:00? Shouldn't that query (the one for testing MySQL's time zones) be SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); ? Nonetheless, it doesn't work for me. I get a one-hour difference, just as though the DST transition never existed. My time_zone variable is set to SYSTEM, and I've verified using zdump (Linux) that the time zone tables on my server are correct. I have loaded the latest time zone information using mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql although it gave me some errors about Riyadh. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:55 AM To: Causevic, Dzenan; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change I don't believe so, since the version of MySQL you are using relies on the system's time. I am using 4.1 and I am setup to use system's time and my timezone tables in the mysql schema are empty. I would take a look at those on your installation. You can also run the 2nd command below to see if the system returns proper time, both unix timestamp should be equal... mysql SHOW VARIABLES LIKE 'time_zone'; +---++ | Variable_name | Value | +---++ | time_zone | SYSTEM | +---++ 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 01:00:00'),UNIX_TIMESTAMP('2007-03-11 02:00:00'); +---+- -- + | UNIX_TIMESTAMP('2007-03-11 01:00:00') | UNIX_TIMESTAMP('2007-03-11 02:00:00') | +---+- -- + |1173592800 | 1173596400 | +---+- -- + 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); +---+- -- + | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+- -- + |1173596400 | 1173596400 | +---+- -- + 1 row in set (0.00 sec) -Original Message- From: Causevic, Dzenan [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:22 AM To: mysql@lists.mysql.com Subject: FW: Does MySQL require patch for Daylight Savings Time 2007 change Okay but after I patch OS do I still need to load time zone tables with following command: shell mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Is this still necessary? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:40 AM To: mysql@lists.mysql.com Subject: re: Does MySQL require patch for Daylight Savings Time 2007 change As far as I know, MySQL does not need a TZ patch (unless you use specific/custom Time Zone information), but your OS does. The best way to check is to run: mysql SHOW VARIABLES LIKE 'TIME_ZONE'; If it says SYSTEM, then you need only patch your OS. (Patching the OS is [OT] for this list). Here's the section from the manual in regards to 3.23 Time Zones. http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html J.R. NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail.
Another stored proc question
This is a follow-up to the earlier stored proc question. I have a stored proc, with user level permissions for execute. If I drop the stored proc and then create it again, the user level execute permissions go away. What is the proper way to edit/alter a store proc without losing these permissions? Given this is the create syntax used: DELIMITER $$ CREATE [EMAIL PROTECTED] PROCEDURE sp_testlogin( P_user_name VARCHAR(32), P_password VARCHAR(32) ) SQL SECURITY DEFINER BEGIN SELECT * FROM users WHERE user_name = P_user_name ; END$$ DELIMITER ; I would think that I could use ALTER in this way: DELIMITER $$ ALTER PROCEDURE sp_testlogin( P_user_name VARCHAR(32), P_password VARCHAR(32) ) SQL SECURITY DEFINER BEGIN SELECT user_id, last_login FROM users WHERE user_name = P_user_name ; END$$ DELIMITER ; But I receive: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( Can someone provide me an example of SP modification using alter (or whatever method works). Thanks, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP: mysql stoped sudenly , Database page corruption on disk or a failed
Dear Friends: since 2 weeks ago the mysql is stoping sudenly, and into the error log is as lines below, Database page corruption on disk or a failed , file read of page 34, how I can know what table is in page 34 ? I've used mysqlcheck, without any result, all is OK for it, mysql 4.01 is running on windows 2003 and the ibdata1of innodb is 20 GB Thanks in advance Jaime 070228 17:44:04 InnoDB: Page checksum 75769562, prior-to-4.0.14-form checksum 3189442283 InnoDB: stored checksum 4088203197, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 36808, low 4 bytes of lsn at page end 0 InnoDB: Page number (if stored to page already) 34, InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 34. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: See also http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. 070228 17:45:07 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 070228 17:45:07 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 29 4030208738. InnoDB: Doing recovery: scanned up to log sequence number 29 4030343563 070228 17:45:07 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed 070228 17:45:09 InnoDB: Flushing modified pages from the buffer pool... 070228 17:45:09 InnoDB: Started; log sequence number 29 4030343563
RE: Does MySQL require patch for Daylight Savings Time 2007 change
As for me, /etc/localtime - /usr/share/zoneinfo/EST5DST Which should be correct for my system as I am located in that time zone. I don't understand why MySQL doesn't give the same UNIX_TIMESTAMP for 2007-03-11 02:00:00 and 2007-03-11 03:00:00. Just to make sure, I tried it with one minute earlier and one minute later and got the same results. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 12:53 PM To: mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change You have both compare 0100 vs 0200 and 0200 vs 0300 in my reply... It wasn't clear because of emails being wrapped at 80 chars. And it still doesn't work after loading the tables with mysql_tzinfo_to_sql And /etc/localtime is a symlink to the proper tz file ? Jean-Sébastien Pilon Systems Administrator Penson Financial Services Canada Inc. % Tel.: 514.841.9724 #269 % Cell: 514.771.0365 -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 12:13 PM To: Jean-Sebastien Pilon; 'Causevic, Dzenan'; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change Hang on a second! Doesn't EST transition to DST at 03:00? Shouldn't that query (the one for testing MySQL's time zones) be SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); ? Nonetheless, it doesn't work for me. I get a one-hour difference, just as though the DST transition never existed. My time_zone variable is set to SYSTEM, and I've verified using zdump (Linux) that the time zone tables on my server are correct. I have loaded the latest time zone information using mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql although it gave me some errors about Riyadh. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:55 AM To: Causevic, Dzenan; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change I don't believe so, since the version of MySQL you are using relies on the system's time. I am using 4.1 and I am setup to use system's time and my timezone tables in the mysql schema are empty. I would take a look at those on your installation. You can also run the 2nd command below to see if the system returns proper time, both unix timestamp should be equal... mysql SHOW VARIABLES LIKE 'time_zone'; +---++ | Variable_name | Value | +---++ | time_zone | SYSTEM | +---++ 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 01:00:00'),UNIX_TIMESTAMP('2007-03-11 02:00:00'); +---+- -- + | UNIX_TIMESTAMP('2007-03-11 01:00:00') | UNIX_TIMESTAMP('2007-03-11 02:00:00') | +---+- -- + |1173592800 | 1173596400 | +---+- -- + 1 row in set (0.00 sec) mysql SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),UNIX_TIMESTAMP('2007-03-11 03:00:00'); +---+- -- + | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+- -- + |1173596400 | 1173596400 | +---+- -- + 1 row in set (0.00 sec) -Original Message- From: Causevic, Dzenan [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 10:22 AM To: mysql@lists.mysql.com Subject: FW: Does MySQL require patch for Daylight Savings Time 2007 change Okay but after I patch OS do I still need to load time zone tables with following command: shell mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql Is this still necessary? ___ Dzenan Causevic Web Applications Developer NaviSite, Inc. 315-453-2912 x5346 (Office) 315-278-7371 (Cell) www.NaviSite.com -Original Message- From: J.R. Bullington [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 9:40 AM To:
char vs. varchar
Hi all Which of these two tables wiil yield the best performance in a table with about 6 million entries (for rapid selects on either field): table_using_char field1 char(50), field2 char(50), filed 3 char(50), separate unique indexes on all 3 fields table_using_varchar field1 varchar(50), field2 varchar(50), field3 varchar(50), separate unique indexes on all 3 fields My gut feeling is the char table should be faster since each record will then be an exact length, but does this reasoning apply since I will be indexing each field and they are also unique (so any one select will only ever return exactly 1 or 0 results)? Thanks Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does MySQL require patch for Daylight Savings Time 2007 change
You ran your zdump -v against this file ? -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 1:41 PM To: Jean-Sebastien Pilon; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change As for me, /etc/localtime - /usr/share/zoneinfo/EST5DST Which should be correct for my system as I am located in that time zone. I don't understand why MySQL doesn't give the same UNIX_TIMESTAMP for 2007-03-11 02:00:00 and 2007-03-11 03:00:00. Just to make sure, I tried it with one minute earlier and one minute later and got the same results. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 12:53 PM To: mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change You have both compare 0100 vs 0200 and 0200 vs 0300 in my reply... It wasn't clear because of emails being wrapped at 80 chars. And it still doesn't work after loading the tables with mysql_tzinfo_to_sql And /etc/localtime is a symlink to the proper tz file ? Jean-Sébastien Pilon Systems Administrator Penson Financial Services Canada Inc. % Tel.: 514.841.9724 #269 % Cell: 514.771.0365 NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et nest destiné qu'à la personne à qui il est adressé. Si vous nêtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement lexpéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt derreur puisque les informations quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Does MySQL require patch for Daylight Savings Time 2007 change
[EMAIL PROTECTED] [~]# zdump -v /etc/localtime |grep 2007 /etc/localtime Sun Mar 11 06:59:59 2007 UTC = Sun Mar 11 01:59:59 2007 EST isdst=0 gmtoff=-18000 /etc/localtime Sun Mar 11 07:00:00 2007 UTC = Sun Mar 11 03:00:00 2007 EDT isdst=1 gmtoff=-14400 /etc/localtime Sun Nov 4 05:59:59 2007 UTC = Sun Nov 4 01:59:59 2007 EDT isdst=1 gmtoff=-14400 /etc/localtime Sun Nov 4 06:00:00 2007 UTC = Sun Nov 4 01:00:00 2007 EST isdst=0 gmtoff=-18000 Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 2:37 PM To: Jerry Schwartz; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change You ran your zdump -v against this file ? -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 1:41 PM To: Jean-Sebastien Pilon; mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change As for me, /etc/localtime - /usr/share/zoneinfo/EST5DST Which should be correct for my system as I am located in that time zone. I don't understand why MySQL doesn't give the same UNIX_TIMESTAMP for 2007-03-11 02:00:00 and 2007-03-11 03:00:00. Just to make sure, I tried it with one minute earlier and one minute later and got the same results. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Jean-Sebastien Pilon [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 28, 2007 12:53 PM To: mysql@lists.mysql.com Subject: RE: Does MySQL require patch for Daylight Savings Time 2007 change You have both compare 0100 vs 0200 and 0200 vs 0300 in my reply... It wasn't clear because of emails being wrapped at 80 chars. And it still doesn't work after loading the tables with mysql_tzinfo_to_sql And /etc/localtime is a symlink to the proper tz file ? Jean-Sébastien Pilon Systems Administrator Penson Financial Services Canada Inc. % Tel.: 514.841.9724 #269 % Cell: 514.771.0365 NOTICE: This email contains privileged and confidential information and is intended only for the individual to whom it is addressed. If you are not the named addressee, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this transmission by mistake and delete this communication from your system. E-mail transmission cannot be guaranteed to be secured or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. AVIS: Le présent courriel contient des renseignements de nature privilégiée et confidentielle et nest destiné qu'à la personne à qui il est adressé. Si vous nêtes pas le destinataire prévu, vous êtes par les présentes avisés que toute diffusion, distribution ou reproduction de cette communication est strictement interdite. Si vous avez reçu ce courriel par erreur, veuillez en aviser immédiatement lexpéditeur et le supprimer de votre système. Notez que la transmission de courriel ne peut en aucun cas être considéré comme inviolable ou exempt derreur puisque les informations quil contient pourraient être interceptés, corrompues, perdues, détruites, arrivées en retard ou incomplètes ou contenir un virus. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Certification
As I understood to attain MySQL Developer you must pass 2 exams... DEV1 and DEV2... What I did not understood, yet... if there is any limit in the timespan between DEV1 and DEV2. Let's say I'll take DEV1 in late March... how many months can pass so that when i'll take DEV2 the results for DEV1 are still valid ? And is the list still ON ? ... I'm subscribed to the list... but didn't got any messages... Thanx for the answers ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Daylight Savings Time Patch - easy check
What version of MySQL are you using? I'm running 4.1.21, and that check doesn't work even after I've updated (I think) the time zone tables. I should probably eyeball the output of mysql_tzinfo_to_sql. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Sent: Saturday, February 24, 2007 4:28 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch - easy check Ryan Stille wrote: Paul DuBois wrote: At 4:40 PM -0600 2/20/07, Ryan Stille wrote: Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Yes, reload them. :-) After that, they're current! ... After digging around on the net for a while I found an easy way to tell if your MySQL installation is ready for the new daylight savings time. SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00'); This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. I get the correct result on both of my machines. On one of them I've run the suggested |mysql_tzinfo_to_sql command, on the other, the time zone tables are completely empty! Any wisdom on these time zone tables - are they ever used, should I populate them or not? -Ryan | -- 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: MySQL Daylight Savings Time Patch - easy check
I am on 4.1.20-1. Maybe your OS isn't patched? Try this: SELECT @@global.time_zone; If you get back SYSTEM, then MySQL is looking to the OS for timezone data. And its only loaded when MySQL starts, so if you haven't restarted MySQL since you patched your OS, you need to do that. -Ryan Jerry Schwartz wrote: What version of MySQL are you using? I'm running 4.1.21, and that check doesn't work even after I've updated (I think) the time zone tables. I should probably eyeball the output of mysql_tzinfo_to_sql. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Sent: Saturday, February 24, 2007 4:28 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch - easy check Ryan Stille wrote: Paul DuBois wrote: At 4:40 PM -0600 2/20/07, Ryan Stille wrote: Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Yes, reload them. :-) After that, they're current! ... After digging around on the net for a while I found an easy way to tell if your MySQL installation is ready for the new daylight savings time. SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00'); This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. I get the correct result on both of my machines. On one of them I've run the suggested |mysql_tzinfo_to_sql command, on the other, the time zone tables are completely empty! Any wisdom on these time zone tables - are they ever used, should I populate them or not? -Ryan | -- 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: MySQL Daylight Savings Time Patch - easy check
On Wed, February 28, 2007 14:10, Ryan Stille said: I am on 4.1.20-1. Maybe your OS isn't patched? Try this: SELECT @@global.time_zone; Won't help if you are on debian which is still on 4.0. If you get back SYSTEM, then MySQL is looking to the OS for timezone data. And its only loaded when MySQL starts, so if you haven't restarted MySQL since you patched your OS, you need to do that. -Ryan Jerry Schwartz wrote: What version of MySQL are you using? I'm running 4.1.21, and that check doesn't work even after I've updated (I think) the time zone tables. I should probably eyeball the output of mysql_tzinfo_to_sql. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Sent: Saturday, February 24, 2007 4:28 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch - easy check Ryan Stille wrote: Paul DuBois wrote: At 4:40 PM -0600 2/20/07, Ryan Stille wrote: Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Yes, reload them. :-) After that, they're current! ... After digging around on the net for a while I found an easy way to tell if your MySQL installation is ready for the new daylight savings time. SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00'); This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. I get the correct result on both of my machines. On one of them I've run the suggested |mysql_tzinfo_to_sql command, on the other, the time zone tables are completely empty! Any wisdom on these time zone tables - are they ever used, should I populate them or not? -Ryan | -- 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]
Re: MySQL Daylight Savings Time Patch - easy check
That was interesting. I have 2 rhel 3 servers and they both have been update to handle the DST. They both yield different results when I ran the command: SERVER=RALPH +---+---+ | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+---+ | 1173607200 | 1173610800 | +---+--- and this for the other server : SERVER=MAGGIE +---+---+ | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+---+ |1173607200 | 1173607200 | +---+--- Uhm. Nestor On 2/28/07, William R. Mussatto [EMAIL PROTECTED] wrote: On Wed, February 28, 2007 14:10, Ryan Stille said: I am on 4.1.20-1. Maybe your OS isn't patched? Try this: SELECT @@global.time_zone; Won't help if you are on debian which is still on 4.0. If you get back SYSTEM, then MySQL is looking to the OS for timezone data. And its only loaded when MySQL starts, so if you haven't restarted MySQL since you patched your OS, you need to do that. -Ryan Jerry Schwartz wrote: What version of MySQL are you using? I'm running 4.1.21, and that check doesn't work even after I've updated (I think) the time zone tables. I should probably eyeball the output of mysql_tzinfo_to_sql. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Sent: Saturday, February 24, 2007 4:28 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch - easy check Ryan Stille wrote: Paul DuBois wrote: At 4:40 PM -0600 2/20/07, Ryan Stille wrote: Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Yes, reload them. :-) After that, they're current! ... After digging around on the net for a while I found an easy way to tell if your MySQL installation is ready for the new daylight savings time. SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00'); This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. I get the correct result on both of my machines. On one of them I've run the suggested |mysql_tzinfo_to_sql command, on the other, the time zone tables are completely empty! Any wisdom on these time zone tables - are they ever used, should I populate them or not? -Ryan | -- 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]
case sensitivity in stored procedure formal arguments
Could someone explain the logic of how case sensivity works on stored procedure formal argument names? Example: CREATE PROCEDURE sp_Test ( IN col1 INT, IN col2 INT ) BEGIN INSERT INTO Table SET COL1 = col1, COL2 = col2; END We've found that this is problematic in some cases because of the case insensitivity of column names, so I'm looking to see if there's a general logic for how to name formal arguments that have a similar name to a table column name (we ended up putting a '_' in the beginning of the argument name). thanks! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
on duplicate key question
This is somewhat related to my last question RE conflicting procedure argument names, but in regards to multi-row inserts... suppose the following procedure: DROP PROCEDURE IF EXISTS sp_ImportedUpdate | CREATE PROCEDURE sp_ImportedUpdate () DETERMINISTIC CONTAINS SQL MODIFIES SQL DATA BEGIN INSERT INTO Destination SELECT ID, CONTACT_NAME, CONTACT_EMAIL, CONTACT_PHONE, ADDRESS_1, ADDRESS_2, CITY, PROVINCE, POSTAL_CODE, COUNTRY, CIRCUIT_ID, TIME_ZONE FROM ToBeUpdated ON DUPLICATE KEY UPDATE ID = VALUES(ID), CONTACT_NAME = VALUES(CONTACT_NAME), CONTACT_EMAIL = VALUES(CONTACT_EMAIL), CONTACT_PHONE = VALUES(CONTACT_PHONE), ADDRESS_1 = VALUES(ADDRESS_1), ADDRESS_2 = VALUES(ADDRESS_2), CITY = VALUES(CITY), PROVINCE = VALUES(PROVINCE), POSTAL_CODE = VALUES(POSTAL_CODE), COUNTRY = VALUES(COUNTRY), CIRCUIT_ID = VALUES(CIRCUIT_ID), TIME_ZONE = VALUES(TIME_ZONE); END | - Here, 'ToBeUpdated' is a view which highlights changes between to tables with the same primary keys, but possibly different values in other columns. The naming scheme here can be tricky, and on first glance given previous problems, it would look to fail, because the column name in VALUES() is the same as the one on the left. However, this actually works as intended, where the changed values are the ones being returned by VALUE. Can someone explain why this is? I'd like to have a solid understanding of the scoping of procedure variables which happen to be the same as column names. thank you! -lev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch - easy check
Is MySQL on each one set to the same time zone? SELECT@@global.time_zone; -Ryan Néstor wrote: That was interesting. I have 2 rhel 3 servers and they both have been update to handle the DST. They both yield different results when I ran the command: SERVER=RALPH +---+---+ | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+---+ | 1173607200 | 1173610800 | +---+--- and this for the other server : SERVER=MAGGIE +---+---+ | UNIX_TIMESTAMP('2007-03-11 02:00:00') | UNIX_TIMESTAMP('2007-03-11 03:00:00') | +---+---+ |1173607200 | 1173607200 | +---+--- Uhm. Nestor On 2/28/07, William R. Mussatto [EMAIL PROTECTED] wrote: On Wed, February 28, 2007 14:10, Ryan Stille said: I am on 4.1.20-1. Maybe your OS isn't patched? Try this: SELECT @@global.time_zone; Won't help if you are on debian which is still on 4.0. If you get back SYSTEM, then MySQL is looking to the OS for timezone data. And its only loaded when MySQL starts, so if you haven't restarted MySQL since you patched your OS, you need to do that. -Ryan Jerry Schwartz wrote: What version of MySQL are you using? I'm running 4.1.21, and that check doesn't work even after I've updated (I think) the time zone tables. I should probably eyeball the output of mysql_tzinfo_to_sql. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Ryan Stille [mailto:[EMAIL PROTECTED] Sent: Saturday, February 24, 2007 4:28 PM To: mysql@lists.mysql.com Subject: Re: MySQL Daylight Savings Time Patch - easy check Ryan Stille wrote: Paul DuBois wrote: At 4:40 PM -0600 2/20/07, Ryan Stille wrote: Is there an easy way to test to see if MySQL already has the proper tables loaded? -Ryan Yes, reload them. :-) After that, they're current! ... After digging around on the net for a while I found an easy way to tell if your MySQL installation is ready for the new daylight savings time. SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'), UNIX_TIMESTAMP('2007-03-11 03:00:00'); This should return the same value, even though you are feeding it different times, because this is when the 1 hr change occurs. I get the correct result on both of my machines. On one of them I've run the suggested |mysql_tzinfo_to_sql command, on the other, the time zone tables are completely empty! Any wisdom on these time zone tables - are they ever used, should I populate them or not? -Ryan | -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does casing of Table names matter on Linux
On 2/28/07, Ow Mun Heng [EMAIL PROTECTED] wrote: On Wed, 2007-02-28 at 15:05 +0530, abhishek jain wrote: Hi Friends, I have been developing one application over Windows and now i want to move/port that into linux, i want to know will the casing of the table name matter on linux ie if i have table name as tab1 and i execute query like select * from TAb1 ,will it make an effect. I have found that this is not a problem on windows but it is on linux, i want a sol. for that a i can not change all the table names as in some places it is in small case and in other places it is in capital case. Yes.. There is a difference. Casing Matters in *nix. However there is an option for turning if off. # http://dev.mysql.com/doc/refman/5.0/en/name-case-sensitivity.html lower_case_table_names = 1 Hi Friends, Thanks for the mails. Now i have made a mistake.In mine code i have somwhere capital case and in other place the small case for the table names. I cannot change the case in mine code and cannot resist the change to the Linux . What is the remedy now i mean can something be done now to make linux be case insensitive, Pl. help me, Thanks, Abhishek jain