Re: Double indexes on one field
Yannick Warnier wrote: Le lundi 03 octobre 2005 à 12:29 +0200, Martijn Tonies a écrit : Using PhpMyAdmin, I seldom get the warning message: PRIMARY and INDEX keys should not both be set for column `ID` I understand its meaning, but I was wondering to what extent having a field indexed AND being a primary key might slow down/speed up my queries. Is that gonna take twice the time if I am searching on the ID field, just because there are two indexes? I'd like to have a rough idea of how serioulsy I need to avoid these. Creating a PRIMARY KEY will automatically create an index. Why would you create a second index for that field? I'm asking myself the same question. I am working on someone else's database. Thanks both, Yannick Is it a multi-field index ? some versions of phpmyadmin show that messages also if only one field is duplicated. example: CREATE TABLE `tab_sint` ( `id_cns` tinyint(3) unsigned NOT NULL default '0', `anno_dep` smallint(4) unsigned zerofill NOT NULL default '', `data_dep` smallint(4) unsigned zerofill NOT NULL default '', `particolare` mediumint(8) unsigned NOT NULL default '0', `generale` mediumint(8) unsigned NOT NULL default '0', UNIQUE KEY `idx_cns_gen_anno` (`anno_dep`,`id_cns`,`generale`,`particolare`), UNIQUE KEY `idx_cns_par_anno` (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=87189031 ; This schema issue the warning on 'id_cns' but really make sense having 2 indices here since they serves different kind of querys and constraints. Regards, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table names with periods
Chance Ellis wrote: [snip] of CREATE TABLE entries. I then import those files with a cron script into mysql. This is where I am looking to convert IP address into something [snip] There is a world of possibilities at this point, one is this: sed \ --expression='s/@@@NEEDTOREPLACETHIS\([0123456789]\).\([0123456789]\)\.\([0123456789]\)\.\([0123456789]\)*NEEDTOREPLACETHIS@@@/\1_\2_\3_\4/' \ SAVEDFILENAME \ | mysql -ublabla -psecret dbname You can do quite everyting from a cron script, also create sql query from normal syslog logs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 examples of data with different encodings
Hi all, Willing to test an upgrading path from 4.0 to 4.1 database of MySQL. Could someone provide a link or some slice of unload from a 4.0 with encodings different from latin1 ? Thanks in advance, Francesco R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT alternative
Vladimir B. Tsarkov wrote: Hello! I've heard that LIMIT is a MySQL specific, and cannot be used in any other DBMS. Is there any portable alternative to LIMIT? I'd like to create a portable PHP pager for a web site, but all the tutorials that I've found, contain solutions based on the LIMIT usage. Thanks! take a look at the code of adodb http://adodb.sourceforge.net and to it's implementation of: SelectLimit($sql,$numrows=-1,$offset=-1,$inputarr=false) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Native XML Support
Martijn Tonies wrote: Does MySQL 5 provide native XML support? ie, can I have a stored procedure return an XML string instead of a recordset? Can I pass in an XML string/doc and have the DB update relational tables based on it? native xml support, now, that's probably the funniest thing I've heard all day :-) What would that actually be? something like this I suppose http://mysql.r18.ru/~bar/myxml/XMLXpathSupportInMySQL.pdf With regards, Martijn Tonies -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Sebastian wrote: i have this query: SELECT COUNT(*) AS score FROM downloads WHERE date_add(dateline, interval 1 hour) = now() GROUP BY filename ORDER BY score DESC unfortunately for other reasons i had to change `dateline` to unix timestamp so this query is no longer able to run as intended. can anyone help with a work around? btw, i am using php to run queries if that helps find a solution. If the database server and the webserver are not on the same box you probably should use the same source for the timestamps. Translated in plain english the insert query is created on the downloads server and don't use MySQL function to insert the date you should use php time() function to retrieve your data. $sql= SELECT COUNT(*) AS score FROM downloads WHERE dateline = . (time() - 3600) . GROUP BY filename ORDER BY score DESC ; Also in a ntp syncronized network a difference of one or two second is not unusual. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Sorry I don't understud this, why it's impossible ? If the PK is auto-generated from MySQL it will have progressive numbers, and it's always possible to force a lower, non-existant number in the PK. Any thoughts/ideas ? We need to track the modification to the records too so the route has been to keep them all in a different, specular databases. If the real table look like this: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ); The backup one look like this: CREATE TABLE `users` ( `del__id` int(11) NOT NULL auto_increment, `del__ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `del__flag` char(1) default 'D', `del__note` mediumtext, `id` int(11) NOT NULL auto_increment, `ts` datetime NOT NULL default '-00-00 00:00:00', `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`del__id`) ); That is the first one whit del__* fields added but all indexed removed. Having the same name and similar schema for the two tables make easier have a photo of the database in a defined time slice. Usefull with small, not very often changing databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Mark Dacasco wrote: SHOW VARIABLES LIKE ''%char%; character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_server latin1 character_set_system utf8 SHOW CREATE TABLE `table1`; CREATE TABLE `main_peeps` ( `id` int(11) unsigned NOT NULL auto_increment COMMENT 'Primary Key', `test` varchar(255) NOT NULL default '' COMMENT 'Test', PRIMARY KEY (`id`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='COMMENT HERE' Please, send the output of the following statements: show variables like '%char%'; show create table table1; adding to the section [mysqld] of the file my.cnf character-set-server = utf8 solves the problem ? -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
useful links: http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html http://dev.mysql.com/doc/mysql/en/Charset-server.html http://dev.mysql.com/doc/mysql/en/Charset-map.html http://dev.mysql.com/doc/mysql/en/Option_files.html http://bugs.mysql.com/bug.php?id=3611 I've found this in my php.ini, should _not_ be related but it can worth a try (apache restart required). === php.ini === [PHP] default_charset = utf-8 [mbstring] mbstring.internal_encoding = utf-8 === php.ini === and finally SHOW VARIABLES LIKE '%char%'; look like this now character_set_client utf8 character_set_connection utf8 character_set_database utf8 character_set_results utf8 character_set_server utf8 character_set_system utf8 right ? Mark wrote: I didn't find any option for character-set-server so I add it under mysqld as you've stated. I also found default-character-set so I also set its value to utf8. It didn't work. I tried adding a dash (utf-8) to it. It didn't work also. Yes, I did restart MySQL after saving the configuration. What does that error mean, by the way? The first link provided answer to this question. adding to the section [mysqld] of the file my.cnf character-set-server = utf8 solves the problem ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: free MySQL conversion to MSSQL tool
[EMAIL PROTECTED] wrote: Does anyone know a free tool to convert MySQL to MSSQL Thank you Andrew phpMyAdmin (http://www.phpmyadmin.net/) version 2.6.3-pl1 has the option to export the data in mssql format (and much others). You need a php enabled web server able to connect to your mysql database. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql v5.0
Gyurasits Zoltán wrote: Hello All! I have a question. I'm using the mysql 4.0 but I want change 5.0 because I would like to use some features. (Subselect etc) Maybe some of features you want are already included in 4.1 series, try a search before switch to 5.0 for a production system. Was 5.0 ever used in bigger system? yes it has, configured as replication slave. some thousand of query/hour , some gigs of data. Is 5.0 more realible than 4.0? obviously not, it's still beta software. Also beta software is subject of bigger changes than stable. Where can I find information about critical bugs and errors of 5.0? Search bugs.mysql.com for bugs open on 5.0 (298 atm) http://bugs.mysql.com/search.php?search_for=status=Activeseverity=limit=10order_by=cmd=displaydirection=ASCbug_type=Anyphp_os=phpver=5.0bug_age=0; Thanx! Zoli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-2: Socket Problem
[EMAIL PROTECTED] wrote: When i try mysql -h server --port=3306 --protocol=TCP -u user --password=xxx it works! The real problem is when using stunnel, i have to do as if i will connect to a local server... On windows it works but not on linux... :( Original Message Subject: Re: Socket Problem (20-juil.-2005 13:10) From:[EMAIL PROTECTED] To: [EMAIL PROTECTED] Hello. First check if you are able to establish TCP connection to port which is linked by stunnel. Telnet or netcat could be helpful in this case. Use --host=xxx --protocol=tcp --port=xxx to explicitly specify connection parameters. See: http://dev.mysql.com/doc/mysql/en/can-not-connect-to-server.html [EMAIL PROTECTED] wrote: Hi, i try to use mysql with stunnel (http://stunnel.org) in order to encrypt my mysql connections. I know that mysql-4.x supports SSL but i can't compile it on my windows box. When i try to use stunnel on my windows box, it works, i just have to use this command : mysql -u user --password=xxx and with stunnel, i'm connected to the remote server and communications are encrypted. but on my linux box, my stunnel config file is the same as on my windows box but i've got this message : Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld. sock' It is normal since i don't have a mysql server on my linux box so this socket doesn't exists... I try with --protocol=TCP option but now i have : Can't connect to MySQL server on localhost' Do you have an idea? Thanks in advance and sorry for my english! lm. Readed fast the whole story so if this is out of topic please forgive me. MySQL on Linux connect to the server using the socket by default. i.e. a connection issued with mysql -hlocalhost will never touch network. To connect using network you need to specify a network address like: mysql -h127.0.0.1 ^ On windows the socket doesn't exist so it use networking ... and work. HIH Francesco Riosa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know if a migration might have problems
Nuno Pereira wrote: Hi all, I have a MySQL server version 4.1.10a, for pc-linux-gnu (i686) installed (from official binary RPMs) and running. My question is if there are problems from updating the server to the most current version of the 4.1.x series, specially things that fail to work, or database curruption. I supose that there are not because they are from the same serie and the first has a minor version of 10a, but I want to be sure. Where can I find an official, current/updated, compiled list of migration problems from any version to another? This is for future updates. http://dev.mysql.com/doc/mysql/en/news-4-1-x.html http://dev.mysql.com/doc/mysql/en/news-4-1-10.html http://dev.mysql.com/doc/mysql/en/news-4-1-11.html http://dev.mysql.com/doc/mysql/en/news-4-1-12.html http://dev.mysql.com/doc/mysql/en/mysql-cluster-news-4-1-11.html http://dev.mysql.com/doc/mysql/en/mysql-cluster-news-4-1-12.html Should answers the question. They are a list of changes and bugs fixed. A database update can always raise incompatibility issues. (someting related to entropy laws) Only few of these can be marked as sure incompatibility, these ones generally are done in beta stage. -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Complexity (big 'O')
Dan Bolser wrote: Hello, I am interested in the theoretical time / space complexity of SQL queries on indexed / non-indexed data. I think I read somewhere that a JOIN on an indexed column is something like O[mn*log(mn)] (m rows joined to n). I assume without an index it is just O[m*n] Specifically I want to know the complexity of a query that does a 'cross tabulation' SELECT X, SUM(if(Y=1,Z,0)) AS s1, SUM(if(Y=2,Z,0)) AS s2, SUM(if(Y=3,Z,0)) AS s3, ... FROM T1 GROUP BY X; Assuming both X and Y are indexed, how does the complexity grow with increasing 's' (more if clauses). More basic, what is the complexity of the group by statement? Can anyone point me to a good online guide to complexity of SQL? Thanks very much for any suggestions :) Dan. It's a bit more complex than that, I'm not an expert of mathematics, so here I'll try to explain things as I know them, hope to give you all the elements needed to calculate the space complexity again. First the previous query don't use indexes at all, you can see this from the output of : EXPLAIN SELECT ... GROUP BY X; To take advantage from indexes the query could be written as: SELECT X, 1 AS Y, SUM(Z) AS s1 FROM T1 WHERE Y=1 GROUP BY X UNION SELECT X, 2 AS Y, SUM(Z) AS s1 FROM T1 WHERE Y=2 GROUP BY X UNION SELECT X, 3 AS Y, SUM(Z) AS s1 FROM T1 WHERE Y=2 GROUP BY X this way whatever the complexity is it will end with the summa of all query, in this case 3 * complexity Now, how to build indexes: The first place to look is the WHERE clause, it's the first used to cut unwanted data. The index will contain Y at his inside, to be more exact the index *must* have Y as first member to be used. Then examine the GROUP BY clause, to group the database must order for the content of the groups. In this case we want to index for X. There is a problem here, generally databases can't use two index for a single table (not totally true, take it as is for now). As a result of this we *must* create an index that contain the ordered couple Y,X . The analisys can finish here, but there is still space for another optimization, this one must be evaluated every time knowing the shape of the table, the amount of data contained etc. We can see that the only other element of the query is Z . Indexes are kept separated from data on the disk, so if all the data needed is contained into the index we can avoid a second disk read for the data. having an index on (Y,X,Z) in this order permit to access only the indexes and not the table data. play with EXPLAIN to learn more on how indexes are used, it's very informative. HTH Francesco Riosa -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: xxx-bin.0000xxx files
Philippe de Rochambeau wrote: My version of mysql creates all sorts of xxx-bin.xxx files in the /var/lib/mysql directory. I regularly remove the oldest files for space purposes. What exactly are these files and what is the proper way to manage them? Thanks. Philippe If you don't need them comment out the log-bin directive in [mysqld] section of my.cnf config file. To remove them a PURGE MASTER LOGS or RESET MASTER query is better than remove them by hand. For more information on binary logs have a look at http://dev.mysql.com/doc/mysql/en/binary-log.html official MySQL documentation. Regards Francesco -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: file my.cnf is missing for Solaris 8, for mysql 5.0.6
James Black wrote: I am getting the following error, when I try to use bin/mysqld_safe, Could not open require defaults file: $MYSQL_HOME/data/my.cnf Fata error in defaults handling. Program aborted Don't remember where I've read it but mysql binary packages don't read anymore the my.cnf from $MYSQL_HOME/data/my.cnf . for alternative paths try #mysqld --help --verbose | grep my.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlxml
mel list_php wrote: Hi guys, I was trying to download the mysqlxml patch for mysql 5.0 but didn't succeed from the url: http://d.udm.net/bar/myxml/mysqlxml.tar.g does anybody know where I could find it? Did anybody tried to use it or have any link to a doc/tutorial in addition to the presentation of Alexander Barkov (http://mysql.r18.ru/~bar/myxml/XMLXpathSupportInMySQL.pdf)? Thanks, Melanie try http://mysql.r18.ru/~bar/myxml/ _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- . These pages are best viewed by coming to my house and looking at . . my monitor. [S. Lucas Bergman (on his website)]. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Opteron HOWTO?!
Greg Whalin wrote: Care to share any secrets? You guys are running Suse w/ 2.4 kernel yes? Any specifics as far as kernel/glibc/gcc versions. Are you running mysql 4.1.*? Are you using NPTL? You using the binary from mysql, or building yourself? Are you running Innodb or Myisam. You mentioned reiserfs correct? Any problems w/ ext3? Sorry to bombard you w/ questions, but we have had nothing but horrible performance using Opterons, and any specifics you can give would help to clear up this mess. I know that I am not the only person who is seeing this flakyiness. Thanks, Greg # emerge --info Portage 2.0.51-r15 (default-linux/amd64/2004.3, gcc-3.4.3-hardenednossp, glibc-2.3.4.20050125-r0, 2.6.11-rc2-mm1 x86_64) = System uname: 2.6.11-rc2-mm1 x86_64 AMD Opteron(tm) Processor 246 Gentoo Base System version 1.6.9 ... sys-devel/autoconf: 2.59-r6, 2.13 sys-devel/automake: 1.7.9-r1, 1.8.5-r3, 1.5, 1.4_p6, 1.6.3, 1.9.4 sys-devel/binutils: 2.15.92.0.2-r4 sys-devel/libtool: 1.5.10-r5 virtual/os-headers: 2.6.8.1-r3 ... CFLAGS=-Os -march=opteron -mtune=opteron CHOST=x86_64-pc-linux-gnu ... CXXFLAGS=-Os -march=opteron -mtune=opteron ... USE=nptl nptlonly # mount | grep DB /dev/sda5 on /DB type reiserfs (rw,noatime,notail) # mysqld --version mysqld Ver 4.1.10-log for pc-linux-gnu on x86_64 (Still Not g.o Linux mysql-4.1.10) Regards, Francesco Riosa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1.10 + DBD-MySQL 2.9003 Incompatibility
Jason Johnson ha scritto: I am running MySQL 4.1.10 and ActivePerl 5.8.6.811 on Windows 2003. When I upgraded MySQL from an older version, everything using DBD-MySQL complained about not being compatible and MySQL is telling me to upgrade to a newer client (in this case, obviously, my client being DBD-MySQL). Does anyone know which version I should be using of DBD-MySQL and in what repository I might find it? If so, how do I go about installing it with PPM (I've installed may different packages using PPM, just curious if there is any particular thing I may need to do when upgrading DBD-MySQL)? A side note, I had this same problem with PHP4, when I upgraded to PHP5 and tossed PHP's dll of the MySQL libraries in the correct location, everything worked fine. This is why I must assume that there is a DBD-MySQL package that has the correct implementation for the new version of MySQL. Any help would be much appreciated. Thanks in advance, Jason on linux DBD-mysql-2.9004 work fine, think it should be the same for windows Regards Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Authorizing Problem in MySQL 4 with Mandrake 10
Prabath Ranasinghe (by way of Prabath Ranasinghe [EMAIL PROTECTED]) ha scritto: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hash: SHA1 Hi All, I installed a fresh copy of Mandrake Linux 10.0 with MySQL. I tried to login to MySQL server ,But it says that authorization is failed. My hostname is localhost and user is root.I entered the root password. Here is the log :- [EMAIL PROTECTED] html]# mysql -h localhost -u root -p Enter password: ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Please help me to solve this problem. Best Regards, Prabath. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFCHRXVIlUQPx/AjvYRAo2fAKCJ+DWXNgSbfW3RySeJGADUSytjWACgxHYt SHHwE2P22LankU/ovx/s28M= =k/T0 -END PGP SIGNATURE- If you still have NO password trying to use it will give you an error ;) try # mysql -h localhost -u root regards Francesco Riosa -- No problem is so formidable that you can't walk away from it. ~ Charles M. Schulz But sometimes run fast is better ~ Francesco R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Effect of VARCHAR length?
Mike Rains ha scritto:
I've just been wondering if the length parameter of a VARCHAR column has
any effect on storage efficiency or space requirements. Afaik, VARCHAR
columns only store the amount of data actually written into them and
require no significantly more memory. So to be especially flexible with
a particular table column, could I just define it VARCHAR(255) and face
no further disadvantage of it?
mysql CREATE TABLE vc (
- vc1 VARCHAR(5),
- vc2 VARCHAR(255)
- );
Query OK, 0 rows affected (0.16 sec)
mysql INSERT INTO vc (vc1, vc2) VALUES
- ('this is a test', 'this is another, longer test');
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql SELECT * FROM vc;
+--+--+
| vc1 | vc2 |
+--+--+
| this | this is another, longer test |
+--+--+
1 row in set (0.00 sec)
From this, we can see how defining the field as VARCHAR(5) limits the
maximum length to 5 characters; we can assume, too, that it will
likewise chop off any strings longer than 255 characters in vc2 the
same way. The length parameter simply provides the upper limit of the
string that might be stored in that field, useful in some instances,
irrelevant in others.
All VARCHARs/TINYTEXTs are stored with a single-byte length prefix,
regardless of how long you let them be (less than 256, of course),
plus the string it's storing. So, for maximum flexibility less than
256 characters, use VARCHAR(255) and don't worry about it.
I'm just curious to know if the length of the indexes on a varchar
column work in the same way or if they have a fixed lenght.
anybody knows ?
--
No problem is so formidable that you can't walk away from it.
~ Charles M. Schulz
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
Sorry for the private answer hitted the wrong replay button.
It's possible for you unload data with an SQL like this ?
SELECT list, of, fields,
MD5 ( CONCAT ( list, of, fields ) )
INTO OUTFILE 'file_name'
FROM tab
if not (probably, you have csv files), you must use a shell script like
this (maybe slow)
#! /bin/sh
export SEP=;
while read myline ; do
echo ${myline}${SEP}$(echo ${myline} | md5sum | cut
--characters=-32)
done /etc/fstab
in M$ windows you must find an alternative.
BIG WARNINGs!
- This solution implies that forever you will be sticked to the same
method / program
- the md5 produced from the shell script will be different from the one
produced from the database
shaun thornburgh ha scritto:
Hi,
Thanks for your reply, but the problem I am facing is that there may
be duplicate values in the uploaded file and I dont want these to appear
in my table...
From: Bastian Balthazar Bux [EMAIL PROTECTED]
To: shaun thornburgh [EMAIL PROTECTED]
Subject: Re: LOAD DATA INFILE using 4.0.17
Date: Tue, 15 Feb 2005 23:32:56 +0100
shaun thornburgh ha scritto:
Hi,
I have a table with 26 fields, each row in this table must be
unique. I can't define all of the fields to be primary keys as the limit
is 16. Therefore before I insert data I have to check that each row is
unique. Data is to be inserted into this table from files uploaded to
the server - CSV files. Is it possible to use the LOAD DATA INFILE
statement to check for unique rows, or does anyone have a better
solution to my problem!
Thanks for any advice offered.
make a table of 27 fields ;) , use the MD5() function
http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add
a unique index on the column that holds the md5 sum.
Regards, Francesco
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Experience with MySQL 5
It work with php too (at least with phpMyAdmin) Martin ha scritto: I've been running it for a while. Admittedly, I don't do anything truly heavy with it -- it is part of my non-critical dev stuff. I've had no issues with it, reliability wise, running it as the backend to a Tomcat 5 install and about to switch it over to a Tomcat 5.5. Basically, on that box, I keep everything as the latest binaries available. I haven't worked with it through PHP, but I certainly have through JDBC, and the ODBC. No issues to date, really. Martin C.F. Scheidecker Antunes wrote: Hello, Does anyone has experience with MySQL 5.x? How reliable is it? Is it compatible with JDBC, PHP and other drivers and connectors? Thank you, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting first non-null values from a set of rows
Eli ha scritto: Hello, Say I get these rows in a regular query: col1col2col3 --- NULL B1 NULL NULLNULLNULL A3 B3 NULL A4 NULLC4 A5 B5 C5 NULLB6 C6 (It's important to keep the rows in that order). I want to get 1 row of the first non-null values from every column.. the row: A3 B1 C4. Like the COALESCE function in MySQL, but on rows. Please help... -thanks, Eli SELECT * FROM tab WHERE ISNULL(col1) LIMIT 1 UNION SELECT * FROM tab WHERE ISNULL(col2) LIMIT 1 UNION SELECT * FROM tab WHERE ISNULL(col3) LIMIT 1 In your case this will return only 2 rows because union remove duplicates and row 2 satisfy both query 2 and 3 Hope it helps francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR 1189 (08S01): Net error reading from master
Just a shot in the dark do you have skip-networking or bind-address uncommented in your my.cnf ? Reading better what you write I suppose the answer is yes ;) can you connect from one server to the other using the replication user ? Olivier Kaloudoff ha scritto: Hi, As no one answered to the question below, I'm wondering wether it's a FAQ ... or a bug for which I should fill a report .. My network is ok between the two machines, I can ssh from one to another and any traffic can flow. But this error prevents me to start replication at all.. Olivier On Tue, 8 Feb 2005, Olivier Kaloudoff wrote: Hi, I have some problems here with two mysql servers, version 4.1.8; on the master server, binary logs are activated, free disk space is not null; [EMAIL PROTECTED]:~# ls -l /repl/mysql/data/master.* -rw-rw 1 mysql mysql 1814256 2005-02-08 11:43 /repl/mysql/data/master.01 -rw-rw 1 mysql mysql 16 2005-02-07 22:28 /repl/mysql/data/master.index [EMAIL PROTECTED]:~# df -h /repl/mysql/data/ Sys. de fich. Tail. Occ. Free. %Occ. Monté sur /dev/hd0/repl04,0G 607M 3,4G 15% /repl0 the correct grant has been typed on the master server to allow the slave to connect and replicate; (launched with --skip-name-resolve) mysql grant replication slave, file on *.* to [EMAIL PROTECTED] identified by 'pass'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) now I change the master to, and ask for the master (in production), to give his data to the slave; mysql change master to MASTER_HOST='db0', MASTER_USER='repl', MASTER_PASSWORD='pass', MASTER_LOG_FILE='master.01'; Query OK, 0 rows affected (0.03 sec) the only thing I can get is: mysql load data from master; ERROR 1189 (08S01): Net error reading from master Any ideas ? is this a bug in 4.1.8 ? Regards, Olivier Kaloudoff CKR Solutions Open Source Mandelieu, France http://www.ckr-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
