Re: myisam parameters in innodb db
Gleb Paharenko wrote: Hello. Use skip-innodb, this should prevent MySQL from InnoDB initialization. Remigiusz Soko$owski [EMAIL PROTECTED] wrote: Hello! Do You know if myISAM-specific settings have any impact on database performance, if the only MyISAM database is mysql? I know that there are some default settings and I wonder if for example memory buffers are allocated even if they are not used? Is it necessary to disable those settings? I see I was not clear enough - I use InnoDB and don't want to disable it - thee point is all my databases except mysql are of innoDB type. In such circumstances the question is if default myISAM settings affect performance of database - are they only maximal values dynamically allocated or inflexible parameters (what would mean that every connection is net_buffer + thread_stack + read_buffer + any_other_buffer of which only some are used and some are loss of memory)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default connection charset
Hi, I have mysql 4.1.12 started with the default charset 'koi8r' and I have the following problem: when connecting from PHP, the default results and connection charsets become latin1, so all not-latin characters get lost (they are displayed as ?). Of cause, if I explicitely set these charsets to koi8r in the PHP scripts after the mysql_connect statement, it starts working correctly, but the problem is, there are so many php scripts calling mysql_connect function that I cannot change them all. I tried adding the default-character-set options into my.cnf in the [mysql] section and it workso for mysql command line but not for the php. So is there any way of setting the default connection charset which will work from php? Or the only way is downgrading to mysql 4.0 to make the existing php script work? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)718-3322, 718-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A more general REPLACE(STR,FROM_STR,TO_STR) function
Hi all, sometimes life would be easier with a more general 'REPLACE' function available. That's when I find mysqlf dumping a database, editing with sed and reloading. My feature request: a string editing function similar to sed's s/regexp/replacement/ command to work on the contents of CHAR, VARCHAR and TEXT columns. In case of parse errors it should return the unchanged string as a default. What do others think? Any comments are welcome. Thomas Spahni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode UTF-8 database
Hi, I've read the whole chapter 10 but I haven't found any instructions on how to setup a UTF-8 database... Is there any guide on how to setup this ? Thanks for any help Regards Roberto Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. First read: http://dev.mysql.com/doc/mysql/en/charset.html Roberto Jobet [EMAIL PROTECTED] wrote: Hi, I'm using MySQL 4.1.11 I need to setup a multilingual database to support a lot of foreign languages. Do I have to setup a UTF-8 unicode database ? Does anyone have a step-by-step guide on how to achieve this ? Thanks for any help Regards Roberto -- 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] 6X velocizzare la tua navigazione a 56k? 6X Web Accelerator di Libero! Scaricalo su INTERNET GRATIS 6X http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection error from c application
Dear Guys, I have been stucked in loading file using load data local infile command. At first it is loading a null value on my first record followed with one record from my input file. The rest are ignored and i've been receiving lots of warnings. I tried converting the text file into a csv and was successful in loading all the files but with a lot of null values and data going another field. I used the fields terminated by ',' lines terminated by ',' instead of '\r\n' because the loading problem. I tried reformatting my txt and csv file, check its spacing(tabs) one by one but still the data seem to go haywire. I'm actually building an application that will accept continuous data (syslog) using c++ that I need to store in Mysql. But just want to handle one thing at a time so I'm just testing how it will accept loading file from the mysql prompt. I'm running on windows XP, mysql server 4.1.11. Kindly refer to the table structure and the attached file. I know you guys are experience and must have encounter this matter before. Will appreciate all your help to a new developer like me, Thanks, Elizabeth mysql show create table log8; | log8 |CREATE TABLE `log8` ( `log_date` date default '-00-00', `log_time` time default '00:00:00', `device_id` varchar(17) default NULL, `log_id` varchar(11) NOT NULL default '', `type` varchar(7) default NULL, `subtype` varchar(7) default NULL, `pri` varchar(6) default NULL, `vd` varchar(4) default NULL, `serial_number` int(11) default NULL, `duration` int(5) default NULL, `policyid` tinyint(3) default NULL, `proto` tinyint(3) default NULL, `service` varchar(10) default NULL, `status` varchar(6) default NULL, `src` varchar(15) default NULL, `srcname` varchar(15) default NULL, `dst` varchar(15) default NULL, `dstname` varchar(15) default NULL, `src_int` varchar(8) default NULL, `dst_int` varchar(8) default NULL, `sent` int(11) default NULL, `rcvd` int(11) default NULL, `sent_pkt` tinyint(4) default NULL, `rcvd_pkt` tinyint(4) default NULL, `src_port` int(11) default NULL, `dst_port` int(11) default NULL, `vpn` varchar(5) default NULL, `tran_ip` varchar(15) default NULL, `tran_port` int(5) default NULL, `dir_disp` varchar(5) default NULL, `tran_disp` varchar(5) default NULL, PRIMARY KEY (`log_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 1 row in set (0.08 sec) mysql On 6/27/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Send an example of your file and your table's definition (use SHOW CREATE TABLE for this purpose). Please, next time send your replies to the list. Hope you're doing great today. Back to my question last week, I've been encountering this problem of loading my file using the load data local infile command for a particular text file. I cannot determine the error in my sql codes since i find no problem loading other text file into the database. mysql load data local infile 'syslog5.txt' into table log4 fields terminated by ' \t' lines terminated by '\n'; Query OK, 2 rows affected, 513 warnings (0.03 sec) Records: 20 Deleted: 0 Skipped: 18 Warnings: 505 I'm not familiar with this warnings myself. I can only load the first line in the syslog5.txt and the rest are ignored. From the two rows only the first line is stored and the rest are ignored. The result of my select statement from the log4 table includes all null value on the first row and the value of the second line for the second row. Would appreciate if you can teach me how to debug the warning or maybe enlighten me on the cause of the problem. Thanks for all your help. Elizabeth On 6/24/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What 'SHOW WARNINGS' reports? Elizabeth Bonifacio [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] 6/23/2005 15:45:50FGT2002A0420075722010001traffic allowed notice root3656393 180 1 17 53020/udp accept 192.168.2.6 192.168.2.6 83.217.132.783.217.132.7internal external183 69 1 1 32193 53020 n/a 202.189.48.98 61114 org noop 6/23/2005 15:45:50FGT2002A0420075722010001traffic allowed notice root3655880 584 1 17 36480/udp accept 192.168.2.6
Simple GROUP / ORDER problem
Hello, If got a simple sessions table basically holds a session id and datetime field for last modification also a session type, so I can have several records with the same session id, with different types and different modification time. I want to get the latest modified record for any given session, and I'm using SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC on this example data set : session_id | type | date_modified d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Although this returns the individual sessions in the correct order, the group by is returning the earliest record for that session_id so I get: d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Rather than d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Which is what I want, I'm sure this is a misunderstanding on my part, but does any one have any ideas? All the best, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Underscore functions as a wildcard ?
We stumbled upon the following 'feature' of MySQL: If, for example user 'x' has a database called 'user_data' he is able to create a table called user2data and so on without create privileges. It looks like the underscore is used as some kind of wildcard, now is the question: is this correct or is this something that should not be? Kind regards, Jeroen Bosch
4.0 4.1 migration and timestamps
Hi! I'm a hosting provider with several thousands of Mysql Databases under 4.0 version. I'm specially worried about TIMESTAMP format changes you did in 4.1. I'll have to update database server soon to have my systems updated. I think that if i update my server version thousands of tables are going to have problems with TIMESTAMP types. I was looking for solutions as to put +0 when selecting TIMESTAMP fields. But I can't imagine to change that in thousands lines of PHP code by hand, I'm sure lots of my customers will have no idea about how to fix that mistake so I'll have to fix their PHP code!. I'm almost sure that all ISP hosting providers are having or will have this problem soon. Anyone have a good idea to update servers to 4.1? Do you think that should I update my servers, wait for a better solution in another version... What do you think I must to do? _ Don't just search. Find. Check out the new MSN Search! http://search.msn.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problems upgrading from 4.0 to 4.1 with TIMESTAMPS
Hi! I'm a hosting provider with several thousands of Mysql Databases under 4.0 version. I'm specially worried about TIMESTAMP format changes you did in 4.1. I'll have to update database server soon to have my systems updated. I think that if i update my server version thousands of tables are going to have problems with TIMESTAMP types. I was looking for solutions as to put +0 when selecting TIMESTAMP fields. But I can't imagine to change that in thousands lines of PHP code by hand, I'm sure lots of my customers will have no idea about how to fix that mistake so I'll have to fix their PHP code!. I'm almost sure that all ISP hosting providers are having or will have this problem soon. Anyone have a good idea to upgrade servers to 4.1? Do you think that should I update my servers, wait for a better solution in another version... What do you think I must to do? _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help me for God!!
Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple GROUP / ORDER problem
Try adding max(session_modified). SELECT *,max(session_modified) AS LastMod FROM translines GROUP BY session_id ORDER BY session_modified DESC On Jun 29, 2005, at 8:00 AM, Lee Denny wrote: Hello, If got a simple sessions table basically holds a session id and datetime field for last modification also a session type, so I can have several records with the same session id, with different types and different modification time. I want to get the latest modified record for any given session, and I'm using SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC on this example data set : session_id | type | date_modified d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Although this returns the individual sessions in the correct order, the group by is returning the earliest record for that session_id so I get: d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Rather than d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Which is what I want, I'm sure this is a misunderstanding on my part, but does any one have any ideas? All the best, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Carlos J Souza wrote: Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil Whats the error? -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple GROUP / ORDER problem
There may well be a way to do what you want but I'd like to make a brief point then let other people with more time give you the answer you want. Simply put, GROUP BY is NOT intended to give you a specific record out of a group the way that you are trying to do. It's job is to do SUMMARIZATION of a group of records. For example, a typical use of GROUP BY would be something like this: select deptno, sum(salary) from employee group by deptno Given a table of employee rows with one row for each employee and where each row contained at least the employee's department number and salary, this query would group all employees together based on their department number then compute and report the total salary paid to each department. Therefore, GROUP BY is telling the database to add up the salaries for each of the people that belong to the first department and report it, then do the second department, then the third department, and so on until all departments have been handled. The query does NOT return the rows of SPECIFIC employees. The query you want, on the other hand, is trying to choose a particular one of the rows in your group and is not doing summarization at all. In short, I don't think you want to be doing a GROUP BY at all. Instead, I think you want to use ORDER BY and perhaps some WHERE clauses to do what you want to do. Just something for you to think about Rhino - Original Message - From: Lee Denny [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 29, 2005 8:00 AM Subject: Simple GROUP / ORDER problem Hello, If got a simple sessions table basically holds a session id and datetime field for last modification also a session type, so I can have several records with the same session id, with different types and different modification time. I want to get the latest modified record for any given session, and I'm using SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC on this example data set : session_id | type | date_modified d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Although this returns the individual sessions in the correct order, the group by is returning the earliest record for that session_id so I get: d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Rather than d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Which is what I want, I'm sure this is a misunderstanding on my part, but does any one have any ideas? All the best, Lee -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.5/32 - Release Date: 27/06/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.8.6/33 - Release Date: 28/06/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Carlos J Souza wrote: The error as follows: when installation is try to finish, the install try a start de new service MYSQL 4 and does not success I try the install and reinstall many times and nothing. Regards On Wed, 29 Jun 2005 08:39:51 -0400, JamesDR wrote: Carlos J Souza wrote: Dear Friends, Iam trying to install MySQL 4.1.x in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Try install and reinstall many times and all fail My WIndows is a 2003 Server SP1 I Need a help about it. Regards for all. Carlos J Souza From Brazil Whats the error? -- Thanks, James Is this the error number/message? It almost sounds like you are missing some info in your cnf/ini. Also check out perms on the data/(cnf/ini) directories. Short of the exact error message/number from windows/mysql it'll be hard to figure out what the error is. Check your event log and mysql's error logs for any details in the mysql.err log in your data dir. -- Thanks, James -- 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 migration and timestamps
Hi, I'll tell you what did my provider (http://www.claranet.fr ) to upgrade from 3.23 to 4.1. They setup a new server with new hardware and a clean new 4.1.11 on it. Then I have available the old 3.23 and the new 4.1. They let me the choice on which server, the DB are created. So the customer has to do the migration by himself (dump data from 3.23 and pipe it to 4.1, after PHP code upgrade). That's the most flexible solution, you let the old server up and running, and buy a new one (thus encouraging customer to migrate, since it will be faster). Your customers will be able to migrate at their own pace, and even downgrading to 4.0 in your case, if something goes wrong. (and yes I agree, the timestamp + 0 (don't forgot any space) is the most hitting change between 4.0 and 4.1, I had to review all timestamp field cause the app rely on it heavily ! ) Good luck ! 2005/6/25, Rubas rubas rubas [EMAIL PROTECTED]: Hi! I'm a hosting provider with several thousands of Mysql Databases under 4.0 version. I'm specially worried about TIMESTAMP format changes you did in 4.1. I'll have to update database server soon to have my systems updated. I think that if i update my server version thousands of tables are going to have problems with TIMESTAMP types. I was looking for solutions as to put +0 when selecting TIMESTAMP fields. But I can't imagine to change that in thousands lines of PHP code by hand, I'm sure lots of my customers will have no idea about how to fix that mistake so I'll have to fix their PHP code!. I'm almost sure that all ISP hosting providers are having or will have this problem soon. Anyone have a good idea to update servers to 4.1? Do you think that should I update my servers, wait for a better solution in another version... What do you think I must to do? _ Don't just search. Find. Check out the new MSN Search! http://search.msn.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: A more general REPLACE(STR,FROM_STR,TO_STR) function
That I'd love to have a regex_replace available in MySQL !! 2005/6/29, Thomas Spahni [EMAIL PROTECTED]: Hi all, sometimes life would be easier with a more general 'REPLACE' function available. That's when I find mysqlf dumping a database, editing with sed and reloading. My feature request: a string editing function similar to sed's s/regexp/replacement/ command to work on the contents of CHAR, VARCHAR and TEXT columns. In case of parse errors it should return the unchanged string as a default. What do others think? Any comments are welcome. Thomas Spahni -- 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]
mysqld error [Can't start server: can't create PID file: No such file or directory]
Description: 050628 17:29:14 mysqld started 050628 17:29:14 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... 050628 17:29:14 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 36808. InnoDB: Doing recovery: scanned up to log sequence number 0 43655 050628 17:29:14 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 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 3 5 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 7 5 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 050628 17:29:14 InnoDB: Started; log sequence number 0 43655 050628 17:29:14 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2) 050628 17:29:14 [ERROR] Can't start server: can't create PID file: No such file or directory 050628 17:29:14 mysqld ended How-To-Repeat: [EMAIL PROTECTED] log]# cd /usr/local/mysql [EMAIL PROTECTED] mysql]# ./bin/mysqld_safe --user=mysql [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 050628 17:53:10 mysqld ended Fix: I wish I knew #I have verified (chgrp, chown) that mysql is the owner and group of /usr/local/mysql/* [EMAIL PROTECTED] lib]# ll -d /var/lib/mysql drwx-- 4 mysql mysql 4096 Jun 28 17:53 /var/lib/mysql [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# ll total 20532 -rw-rw 1 mysql root 2368 Jun 28 17:53 antest1.err -rw-rw 1 mysql mysql 10485760 Jun 28 14:03 ibdata1 -rw-rw 1 mysql mysql 5242880 Jun 28 17:53 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Jun 28 14:03 ib_logfile1 drwx-- 2 mysql mysql 4096 Jun 28 14:00 mysql srwxrwxrwx 1 mysql mysql0 Jun 28 17:53 mysql.sock drwx-- 2 mysql mysql 4096 Jun 28 14:00 test [EMAIL PROTECTED] run]# pwd /var/run [EMAIL PROTECTED] run]# ll -d mysql drwxrwxrwx 2 mysql mysql 4096 Jun 28 16:58 mysql NOTE *** /var/run/mysql is empty. I tried creating the mysqld.pid file (with mysql as owner and group) but still get the same problem. Submitter-Id: submitter ID Originator:Ralph J. Jackson 407.381.7797x568 Organization: JHT, Inc. MySQL support: none Synopsis: mysqld refuses to continue to run. Severity: critical Priority: medium | high Category: mysql Class: support Release: mysql-3.23.58 (Source distribution) Environment: machine, os, target, libraries (multiple lines) System: Linux antest1 2.6.11-1.35_FC3 #1 Mon Jun 13 00:52:08 EDT 2005 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/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.3/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.3 20050227 (Red Hat 3.4.3-22.fc3) Compilation info: CC='i386-redhat-linux-gcc' CFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE' CXX='i386-redhat-linux-g++' CXXFLAGS='-O2 -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jun 24 10:23 /lib/libc.so.6 - libc-2.3.5.so -rwxr-xr-x 1 root root 1505808 Apr 7 06:58 /lib/libc-2.3.5.so -rw-r--r-- 1 root root 2409956 Apr 7 06:03 /usr/lib/libc.a -rw-r--r-- 1 root root 204 Apr 7 05:29 /usr/lib/libc.so Configure command: ./configure '--build=i386-redhat-linux' '--host=i386-redhat-linux' '--target=i386-redhat-linux-gnu' '--program-prefix=' '--prefix=/usr' '--exec-prefix=/usr' '--bindir=/usr/bin' '--sbindir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--includedir=/usr/include' '--libdir=/usr/lib' '--libexecdir=/usr/libexec' '--localstatedir=/var' '--sharedstatedir=/usr/com' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--without-readline' '--without-debug' '--enable-shared' '--with-extra-charsets=complex' '--with-bench' '--localstatedir=/var/lib/mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-mysqld-user=mysql' '--with-extra-charsets=all' '--with-innodb' '--enable-local-infile' '--enable-large-files=yes' '--enable-largefile=yes' '--with-berkeley-db' '--enable-thread-safe-client' 'CFLAGS=-O2 -g -pipe -m32 -march=i386 -mtune=pentium4 -D_GNU_SOURCE
MySQL Character Set
I need in depth information on the character set used by MySQL including invisible/escape characters/codes. Is there online documentation that I can read on this? Thanks. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple GROUP / ORDER problem
Lee Denny [EMAIL PROTECTED] wrote on 06/29/2005 08:00:49 AM: Hello, If got a simple sessions table basically holds a session id and datetime field for last modification also a session type, so I can have several records with the same session id, with different types and different modification time. I want to get the latest modified record for any given session, and I'm using SELECT * FROM translines GROUP BY session_id ORDER BY session_modified DESC on this example data set : session_id | type | date_modified d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 d36631973996623650e5e1caae5686ca 2 2005-06-29 11:34:41 d36631973996623650e5e1caae5686ca 1 2005-06-29 10:50:41 d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Although this returns the individual sessions in the correct order, the group by is returning the earliest record for that session_id so I get: d36631973996623650e5e1caae5686ca 3 2005-06-29 10:50:41 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Rather than d36631973996623650e5e1caae5686ca 1 2005-06-29 11:40:00 09ebae8272301839c519cc3bb0ca 2 2005-06-28 20:38:18 Which is what I want, I'm sure this is a misunderstanding on my part, but does any one have any ideas? All the best, Lee This is a very FAQ. It is the group-wise maximum problem and it is well documented here: http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine PS - In fact, this problem is so frequently asked, you are the second person in the last 24 hours to post it (different fields, of course, but same problem)
Re: connection error from c application
Your syslog8.txt file has two problems: 1. The first field, log_date, is in m/d/ format. It will not be converted by MySQL automatically. It needs to be in -mm-dd format. For this reason, all records will have the default value of '-00-00' for that field. 2. The primary key on your table is log_id. In the sample you sent, all of the log_id's are the same. The first row is inserted, and all others with that ID are skipped. kgt Elizabeth Bonifacio wrote: Dear Guys, I have been stucked in loading file using load data local infile command. At first it is loading a null value on my first record followed with one record from my input file. The rest are ignored and i've been receiving lots of warnings. I tried converting the text file into a csv and was successful in loading all the files but with a lot of null values and data going another field. I used the fields terminated by ',' lines terminated by ',' instead of '\r\n' because the loading problem. I tried reformatting my txt and csv file, check its spacing(tabs) one by one but still the data seem to go haywire. I'm actually building an application that will accept continuous data (syslog) using c++ that I need to store in Mysql. But just want to handle one thing at a time so I'm just testing how it will accept loading file from the mysql prompt. I'm running on windows XP, mysql server 4.1.11. Kindly refer to the table structure and the attached file. I know you guys are experience and must have encounter this matter before. Will appreciate all your help to a new developer like me, Thanks, Elizabeth mysql show create table log8; | log8 |CREATE TABLE `log8` ( `log_date` date default '-00-00', `log_time` time default '00:00:00', `device_id` varchar(17) default NULL, `log_id` varchar(11) NOT NULL default '', `type` varchar(7) default NULL, `subtype` varchar(7) default NULL, `pri` varchar(6) default NULL, `vd` varchar(4) default NULL, `serial_number` int(11) default NULL, `duration` int(5) default NULL, `policyid` tinyint(3) default NULL, `proto` tinyint(3) default NULL, `service` varchar(10) default NULL, `status` varchar(6) default NULL, `src` varchar(15) default NULL, `srcname` varchar(15) default NULL, `dst` varchar(15) default NULL, `dstname` varchar(15) default NULL, `src_int` varchar(8) default NULL, `dst_int` varchar(8) default NULL, `sent` int(11) default NULL, `rcvd` int(11) default NULL, `sent_pkt` tinyint(4) default NULL, `rcvd_pkt` tinyint(4) default NULL, `src_port` int(11) default NULL, `dst_port` int(11) default NULL, `vpn` varchar(5) default NULL, `tran_ip` varchar(15) default NULL, `tran_port` int(5) default NULL, `dir_disp` varchar(5) default NULL, `tran_disp` varchar(5) default NULL, PRIMARY KEY (`log_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 1 row in set (0.08 sec) mysql On 6/27/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Send an example of your file and your table's definition (use SHOW CREATE TABLE for this purpose). Please, next time send your replies to the list. Hope you're doing great today. Back to my question last week, I've been encountering this problem of loading my file using the load data local infile command for a particular text file. I cannot determine the error in my sql codes since i find no problem loading other text file into the database. mysql load data local infile 'syslog5.txt' into table log4 fields terminated by ' \t' lines terminated by '\n'; Query OK, 2 rows affected, 513 warnings (0.03 sec) Records: 20 Deleted: 0 Skipped: 18 Warnings: 505 I'm not familiar with this warnings myself. I can only load the first line in the syslog5.txt and the rest are ignored. From the two rows only the first line is stored and the rest are ignored. The result of my select statement from the log4 table includes all null value on the first row and the value of the second line for the second row. Would appreciate if you can teach me how to debug the warning or maybe enlighten me on the cause of the problem. Thanks for all your help. Elizabeth On 6/24/05, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What 'SHOW WARNINGS' reports? Elizabeth Bonifacio [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]
Re: A more general REPLACE(STR,FROM_STR,TO_STR) function
Can I vote for just enabling FULL regex support? Let's get it all working (pattern matches (already done), substring replaces, pattern extraction, everything else...). Shawn Pooly [EMAIL PROTECTED] wrote on 06/29/2005 09:31:24 AM: That I'd love to have a regex_replace available in MySQL !! 2005/6/29, Thomas Spahni [EMAIL PROTECTED]: Hi all, sometimes life would be easier with a more general 'REPLACE' function available. That's when I find mysqlf dumping a database, editing with sed and reloading. My feature request: a string editing function similar to sed's s/regexp/replacement/ command to work on the contents of CHAR, VARCHAR and TEXT columns. In case of parse errors it should return the unchanged string as a default. What do others think? Any comments are welcome. Thomas Spahni
Re: Help me for God!!
Hi Carlos, My suggestion for this problem is, just uninstall the MySQL Server and manually delete all the corresponding files, restart the system once (not a must, but it's a advisable), then u install the MySQL, hope now it'll definitely work. This is happening because of mis-configuration b/w the prev and current installations. Thanks and Regards, Ashok. Carlos J Souza wrote: The error as follows: when installation is try to finish, the install try a start de new service MYSQL 4 and does not success I try the install and reinstall many times and nothing. Regards Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Underscore functions as a wildcard ?
I recall seeing this feature discussed on Bugtraq a few weeks ago. IIRC there are updated MySQL versions that fix this bug. What version of MySQL are you running? -Original Message- From: Jeroen Bosch [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 29, 2005 6:59 AM To: mysql@lists.mysql.com Subject: Underscore functions as a wildcard ? We stumbled upon the following 'feature' of MySQL: If, for example user 'x' has a database called 'user_data' he is able to create a table called user2data and so on without create privileges. It looks like the underscore is used as some kind of wildcard, now is the question: is this correct or is this something that should not be? Kind regards, Jeroen Bosch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld error [Can't start server: can't create PID file: No such file or directory]
Hello.
to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
MySQL tries to create mysqld.pid in /var/run/mysqld ('d' at the end) not
in /var/run/mysql directory.
Description:
050628 17:29:14 mysqld started
050628 17:29:14 InnoDB: Database was not shut down normally!
.
IInnoDB: Apply batch completed
050628 17:29:14 InnoDB: Started; log sequence number 0 43655
050628 17:29:14 [ERROR] /usr/local/mysql/bin/mysqld: Can't create/write
to file '/var/run/mysqld/mysqld.pid' (Errcode: 2)
050628 17:29:14 [ERROR] Can't start server: can't create PID file: No
such file or directory
050628 17:29:14 mysqld ended
How-To-Repeat:
[EMAIL PROTECTED] log]# cd /usr/local/mysql
[EMAIL PROTECTED] mysql]# ./bin/mysqld_safe --user=mysql
[EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from
/var/lib/mysql
STOPPING server from pid file /var/run/mysqld/mysqld.pid
050628 17:53:10 mysqld ended
Fix:
I wish I knew
#I have verified (chgrp, chown) that mysql is the owner and group of
/usr/local/mysql/*
[EMAIL PROTECTED] lib]# ll -d /var/lib/mysql
drwx-- 4 mysql mysql 4096 Jun 28 17:53 /var/lib/mysql
[EMAIL PROTECTED] mysql]# pwd
/var/lib/mysql
[EMAIL PROTECTED] mysql]# ll
total 20532
-rw-rw 1 mysql root 2368 Jun 28 17:53 antest1.err
-rw-rw 1 mysql mysql 10485760 Jun 28 14:03 ibdata1
-rw-rw 1 mysql mysql 5242880 Jun 28 17:53 ib_logfile0
-rw-rw 1 mysql mysql 5242880 Jun 28 14:03 ib_logfile1
drwx-- 2 mysql mysql 4096 Jun 28 14:00 mysql
srwxrwxrwx 1 mysql mysql0 Jun 28 17:53 mysql.sock
drwx-- 2 mysql mysql 4096 Jun 28 14:00 test
[EMAIL PROTECTED] run]# pwd
/var/run
[EMAIL PROTECTED] run]# ll -d mysql
drwxrwxrwx 2 mysql mysql 4096 Jun 28 16:58 mysql
NOTE ***
/var/run/mysql is empty. I tried creating the mysqld.pid
file (with mysql as owner and group) but still get the same pro
Ralph Jackson [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]
Re: MySQL Character Set
Hello. This page contains information about escape characters: http://dev.mysql.com/doc/mysql/en/string-syntax.html The general information about character set could be found at: http://dev.mysql.com/doc/mysql/en/charset.html Asad Habib [EMAIL PROTECTED] wrote: I need in depth information on the character set used by MySQL including invisible/escape characters/codes. Is there online documentation that I can read on this? Thanks. - Asad -- 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: Unicode UTF-8 database
Hello. If you want to setup such kind of database you could put all necessary variables to your configuration file, then restart the server and create a new database. For example: [mysqld] default_character_set=utf8 [client] default_character_set=utf8 Check with show variables like '%char%'; that all your connection variables have utf8 value. Roberto Jobet [EMAIL PROTECTED] wrote: Hi, I've read the whole chapter 10 but I haven't found any instruct= ions on how to setup a UTF-8 database... Is there any guide on how = to setup this ? Thanks for any help Regards Roberto Gle= b Paharenko [EMAIL PROTECTED] wrote: Hello. First = read: http://dev.mysql.com/doc/mysql/en/charset.html =0D = Roberto Jobet [EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me for God!!
Hello. Very often similar error occurs when you didn't remove service from previous MySQL installation. See: http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html Carlos J Souza [EMAIL PROTECTED] wrote: Dear Friends, =A0 Iam trying to install MySQL 4.1.x in Windows 2003 =A0Server, and on error occurs when installation try a =A0start de service. The service does not start and =A0installation don't finish. =A0 I Try install and reinstall many times and all fail =A0 My WIndows is a 2003 Server SP1 =A0 =A0I Need a help about it. =A0 =A0 =A0Regards for all. =A0 Carlos J Souza From Brazil -- 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: default connection charset
Privet. As I know, PHP usually takes the character set from the server. Please send part of your configuration file related to character sets. Varshavchick Alexander [EMAIL PROTECTED] wrote: Hi, I have mysql 4.1.12 started with the default charset 'koi8r' and I have the following problem: when connecting from PHP, the default results and connection charsets become latin1, so all not-latin characters get lost (they are displayed as ?). Of cause, if I explicitely set these charsets to koi8r in the PHP scripts after the mysql_connect statement, it starts working correctly, but the problem is, there are so many php scripts calling mysql_connect function that I cannot change them all. I tried adding the default-character-set options into my.cnf in the [mysql] section and it workso for mysql command line but not for the php. So is there any way of setting the default connection charset which will work from php? Or the only way is downgrading to mysql 4.0 to make the existing php script work? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)718-3322, 718-3115(fax) -- 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: Underscore functions as a wildcard ?
Hello. Could you give a test case? For example I've done these steps, and user u1 couldn't create tables in other databases. grant all privileges on `user\_data`.* to 'u1'@'localhost' identified by 'u1'; grant select on user2data.* to [EMAIL PROTECTED]; Then I've logged in as u1. use `user\_data`; create table a1(a int); create table user2data.user2data( a int); ERROR 1142 (42000): CREATE command denied to user 'u1'@'localhost' for table 'user2data' User u1 is able to create tables only in his database (user_data). Jeroen Bosch [EMAIL PROTECTED] wrote: We stumbled upon the following 'feature' of MySQL: If, for example user 'x' has a database called 'user_data' he is able to create a table called user2data and so on without create privileges. It looks like the underscore is used as some kind of wildcard, now is the question: is this correct or is this something that should not be? -- 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: Underscore functions as a wildcard ?
This is not a bug. Mysql uses _ as the single-character wildcard, and % as the multi-character wildcard in pattern matches. This is clearly explained in the manual http://dev.mysql.com/doc/mysql/en/grant.html: Note: the '_' and '%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a '_' character as part of a database name, you should specify it as '\_' in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO Michael John Trammell wrote: I recall seeing this feature discussed on Bugtraq a few weeks ago. IIRC there are updated MySQL versions that fix this bug. What version of MySQL are you running? -Original Message- From: Jeroen Bosch [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 29, 2005 6:59 AM To: mysql@lists.mysql.com Subject: Underscore functions as a wildcard ? We stumbled upon the following 'feature' of MySQL: If, for example user 'x' has a database called 'user_data' he is able to create a table called user2data and so on without create privileges. It looks like the underscore is used as some kind of wildcard, now is the question: is this correct or is this something that should not be? Kind regards, Jeroen Bosch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help benchmarks other than OSDB for mysql-cluster
Hi everybody, i would like to find benchmarks whose work with mysql-max. I tested osdb 0.14 (http://osdb.sourceforge.net/). But i search other benchmarks to have others results. I work on mysql-cluster. If you know some of them please send me an email to [EMAIL PROTECTED] cordially, Etienne
RE: Underscore functions as a wildcard ?
Just so. What I was referring to were these reports: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=285276 http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2004-0957 As documented, this is clearly a feature, and not a bug. -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 29, 2005 10:27 AM To: John Trammell Cc: mysql@lists.mysql.com Subject: Re: Underscore functions as a wildcard ? This is not a bug. Mysql uses _ as the single-character wildcard, and % as the multi-character wildcard in pattern matches. This is clearly explained in the manual http://dev.mysql.com/doc/mysql/en/grant.html: Note: the '_' and '%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a '_' character as part of a database name, you should specify it as '\_' in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO Michael John Trammell wrote: I recall seeing this feature discussed on Bugtraq a few weeks ago. IIRC there are updated MySQL versions that fix this bug. What version of MySQL are you running? -Original Message- From: Jeroen Bosch [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 29, 2005 6:59 AM To: mysql@lists.mysql.com Subject: Underscore functions as a wildcard ? We stumbled upon the following 'feature' of MySQL: If, for example user 'x' has a database called 'user_data' he is able to create a table called user2data and so on without create privileges. It looks like the underscore is used as some kind of wildcard, now is the question: is this correct or is this something that should not be? Kind regards, Jeroen Bosch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Issues on Debian-AMD64 - looking for ideas or help.
On Tue, Jun 28, 2005 at 10:37:43PM -0700, mike wrote: I'm compiling my mysql server from source. I have started noticing this as of 4.1.10 - in fact, I was running 4.1.12 and it's changed the LinuxThreads detection (since LT does not exist on amd64) - and 4.1.12 kept locking up after only a few minutes of uptime consistently. Once I figured this out, I rolled back to my 4.1.10 with a small patch to the configure script to force the calling of CFLAGS=$CFLAGS -DUSE_MUTEX_INSTEAD_OF_RW_LOCKS -DPTHREAD_ADAPTIVE_MUTEX_INITIALIZER_NP I'm wondering though - am I missing something here? This is a bug in the version of glibc being shipped with Ubuntu, and apparently Debian as well. Here's the Ubuntu bug for tracking it: https://bugzilla.ubuntu.com/show_bug.cgi?id=11730 There's also a Debian bug for it, but I can't find it right now. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
effective handling of fuzzy dates (MySQL/PHP)
Hello, Please excuse me if this has been posted in the wrong spot. I'm storing historical information in a MySQL database and am using the date field formatted in -MM-DD. For the most part, the data entered is correct and uses the full -MM-DD format, however, I've got numerous dates that are incomplete. For example: an event happened in 1967, but the exact month and day are not known. I've been storing that data, in other forms, as 1967-00-00 but now dropping it into MySQL, when extracting that information using PHP, I'm running into errors where a date like '1967-00-00' would be deemd as having occured in 1966? As far as the data goes, here's some sample dates that I need to be able to handle. AS ENTERED EQUIVILANT LONG FORM --- 1965-08-12 August 12, 1965 1965-08-00 August 1965 1965-00-00 1965 Is this an effective means of handling this? Are there better methods? How can I get PHP to properly convert those dates? (I am using the ADODB-TIME.PHP class library too to help avoid the 1970-01-01 date restrictions that PHP internally has) Thanks for your help! _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: effective handling of fuzzy dates (MySQL/PHP)
On 6/29/05, me you wrote: For the most part, the data entered is correct and uses the full -MM-DD format, however, I've got numerous dates that are incomplete. For example: an event happened in 1967, but the exact month and day are not known. I've been storing that data, in other forms, as 1967-00-00 but now dropping it into MySQL, when extracting that information using PHP, I'm running into errors where a date like '1967-00-00' would be deemd as having occured in 1966? As far as the data goes, here's some sample dates that I need to be able to handle. AS ENTERED EQUIVILANT LONG FORM --- 1965-08-12 August 12, 1965 1965-08-00 August 1965 1965-00-00 1965 I prefer to store fuzzy dates as both a date and an interval (precision). You can also use a start and an end date instead of a start and an interval. So in the case of your three examples I would store: 1965-08-12 0 August 12, 1965 1965-08-00 1 monthAugust 1965 1965-00-00 1 year1965 Advantage of doing this is that you can also store dates with a precision of 'second half of 1913'. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Ordinal number within a table
It's probably much faster to do this in perl, since the use of @row means MySQL can't use its query cache. -- Jamie McCarthy http://mccarthy.vg/ [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL Syntax Errors
671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 676 $result = $sth-execute(); 678 Insert must of have worked! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. 676 $result = $sth-execute(); At the end are my print messages from a perl program using MySQL (v 4.1, how do I tell for sure?) with the DBI interface. The first integer on the left is the line number. I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) This indicates success. But then it tells me I have a syntax error! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. Most of the time, everything works fine. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Syntax Errors
Siegfried Heintze [EMAIL PROTECTED] wrote on 06/29/2005 03:09:28 PM: 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 671 Did not find any old versions with SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26', attempt to insert one: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) 676 $result = $sth-execute(); 678 Insert must of have worked! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. 676 $result = $sth-execute(); At the end are my print messages from a perl program using MySQL (v 4.1, how do I tell for sure?) with the DBI interface. The first integer on the left is the line number. I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) This indicates success. But then it tells me I have a syntax error! DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? A syntax error is not your first error message. I see a message that you are attempting to duplicate a key value that already exists. Are you sure that your initial check is returning FALSE when you look for a matching record? Have you considered using the INSERT ... ON DUPLICATE KEY format or possibly the INSERT IGNORE format? Either one of those will let you deal with the case of what you should do if you attempt to create a record that would duplicate an existing records PK values. I don't use DBD or I could offer better advice. However, some database libraries force you to execute your commands one at a time. Could this be what is happening to you bewteen lines 683 and 686? I would also check (print so that you can see) the full text of the statement you are attempting to execute in line 686. It could be that you have a mismatched set of single quotes. You have to remember to escape all of the special characters used in a string literal or it will corrupt your statement. If, for example, you are building an INSERT statement that contains the name of a buisiness plus some other fields and that business has an apostrophe in its name, that apostrophe needs to be escaped or it will break your INSERT statement. I don't get a syntax error every time. Most of the time, everything works fine. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
(oops, corrections to that last email message) RE: SQL Syntax Errors
Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. Thanks, Siegfried --- 683 insert failed: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) /try delete: 'DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' select='SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26''Duplicate entry '209689-2005-06-26' for key 1 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 209689' at li at ./crawl-hot-jobs.pl line 686. Use of uninitialized value in concatenation (.) or string at ./crawl-hot-jobs.pl line 707. 707 $nDBVersion[0] = 1 version=1 nDBVersion=() fkJobId = 209689 No need to update database,it is more recent. -- 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: MAC OS X backup after crash
Michael, You are very correct - I renamed those fields and boom - it started working like magic, thank you so much. I have another question - the database in question that got crashed had mysql root password but the guys I took over from forgot the password and didn't do any mysql dump. So, I had to manually copy the files i.e MYD, MYI and frm files to the database directories after creating them. However, the application that is using the database requires a certain user account in the mysql database and I can't get to it without the password, so I had to replace my new server mysl database files from the old server and it worked. However - I can't administer the mysql server without the root password. Anyone has any idea how I can retrieve the root password? __ 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]
Wanted: Help with 'ON DUPLICATE KEY' syntax
Thanks for deciphering that terrible message, Shawn. I accidentally must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=2 DBD::mysql::st execute failed: 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 'ON DUPLICATE KEY UPDATE cJobTitle=2' at line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wanted: Help with 'ON DUPLICATE KEY' syntax
Hi, Are you using MySQL-4.1 ? (ON DUPLICATE KEY syntax has been introduced in 4.1) Regards, Jocelyn Siegfried Heintze wrote: Thanks for deciphering that terrible message, Shawn. I accidentally must have hit the paste key too many times. Anyway, here is my new insert statement: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (211584,'2005-06-26',2) ON DUPLICATE KEY UPDATE cJobTitle=2 DBD::mysql::st execute failed: 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 'ON DUPLICATE KEY UPDATE cJobTitle=2' at line 1 at ./crawl-hot-jobs.pl line 675. I'm looking at the documentation on http://dev.mysql.com/doc/mysql/en/insert.html and I don't see what I am doing wrong. Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAC OS X backup after crash
Alla-amin wrote: Michael, You are very correct - I renamed those fields and boom - it started working like magic, thank you so much. I have another question - the database in question that got crashed had mysql root password but the guys I took over from forgot the password and didn't do any mysql dump. So, I had to manually copy the files i.e MYD, MYI and frm files to the database directories after creating them. However, the application that is using the database requires a certain user account in the mysql database and I can't get to it without the password, so I had to replace my new server mysl database files from the old server and it worked. However - I can't administer the mysql server without the root password. Anyone has any idea how I can retrieve the root password? This is covered in the manual http://dev.mysql.com/doc/mysql/en/resetting-permissions.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (oops, corrections to that last email message) RE: SQL Syntax Errors
Siegfried Heintze wrote: Sorry, I accidentally pasted some garbage at the beginning of that last email message. Here is what I intended: I first check to see if the record exists: SELECT cJobTitle FROM jobtitlecount WHERE fkJobPosting = 209689 AND dtSnapShot = '2005-06-26' When I don't find an entry, I try an insert: INSERT INTO jobtitlecount (fkJobPosting, dtSnapShot, cJobTitle) VALUES (209689,'2005-06-26',1) The execute function indicated success. But then (sometimes) it tells me I have a duplicate entry: DBD::mysql::st execute failed: Duplicate entry '209689-2005-06-26' for key 1 at ./crawl-hot-jobs.pl line 675. How could this be? Either you incorrectly interpreted the SELECT result, or someone/something inserted a row into the table between your SELECT and your INSERT, or one of your queries isn't what you think it is. It would help if you would show us your real code (not an edited version). Now here is another example where I detect a duplicate and delete the statement before trying to insert: DELETE jobtitlecount WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151 DBD::mysql::st execute failed: 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 'WHERE dtSnapShot ='2005-06-26' AND fkJobPosting = 211151' at li at ./crawl-hot-jobs.pl line 686. I don't see any syntax error in that DELETE statement: do you? Yes. The syntax is DELETE FROM tablename You are missing the FROM. See the manual for details http://dev.mysql.com/doc/mysql/en/delete.html. The primary key consists of two fields: fkJobPosting (integer) and dtSnapshot (date). Now if my SQL had a syntax error, would it not give me an error every time? So why do I get syntax error? I don't get a syntax error every time. These errors are very eratic and I cannot discern what is different when these errors occur. Thanks, Siegfried --- Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
