Global Unique Identifiers
Hi All, I'm designing a database where it is required for each row in a table to have a unique identifier accross a bunch of running MySQL servers on a network. I'm considering two options: 1. Have a two-column primary key, where the first column is a normal auto incremented ID field, the second identifies the database and contains a constant ID per server. Something like this: CREATE TABLE MyTable ( ROWID int unsigned not null auto_increment, DBID int unsigned not null, AnyData varchar(10) not null, PRIMARY KEY (ROWID, DBID) ); INSERT INTO MyTable (DBID, AnyData) VALUES (8, 'Any text'); 2. I would use a traditional one-column binary primary key populated by the built-in uuid() fuction of MySQL. Like this: CREATE TABLE MyTable ( ID binary(36) not null, AnyData varchar(10) not null, PRIMARY KEY (ID) ); INSERT INTO MyTable (ID, AnyData) VALUES (uuid(), 'Any text'); In my view both solutions have their adventages and disadvantages. The first is more optimal in storage space and speed, the second is easier to maintain, administer and query. And there is another aspect, which is needed to be tested, I guess... How fast is the second solution when I execute complex queries based on primary key relations? Does it pay to use the ease of the second solution? Anyone has any experience in similar problems? What is your opinion? Thanks, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL+InnoDB Licenses
Hi All, I would have a question about licensing MySQL. I am writing an application that relies on MySQL+InnoDB (uses MySQL as a database backend). I will distribute my program under GPL (get fees only for official support). Do I or my client have to buy MySQL+InnoDB licenses in this case? Thank you, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large tables
Hi All, I would like to know how big is the biggest database that can be handled effectively by MySQL/InnoDB. Like physical size, number of tables, number of rows per table, average row lenght, number of indexes per table, etc. Practically, what if I have a master/detail table-pair, where the master contains about 30 million rows, the detail in average contains 50 row for each master row, so about 1.5 billion rows in total? I know that the performace heavily relies on the hardware, but let's assume that the MySQL server runs on a high-end machine with about 2GB or RAM. Does anyone have experience with extremely large databases? Thank you, Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large tables
Hi, Thanks, but I already checked the manual about these aspects, and I'm doing heavy tests for a while about the performance of MySQL (with InnoDB tables) with big databases. By the way, the indexing seems to be working on bigint fields, so probably the size of the int field is not a limit. However, I'm more interested in practical experience with huge databases. How effective is MySQL (with InnoDB) working with tables containing millions or rather billions of rows? How about the response time of queries, which return a few dozens of rows from these big tables when using two, three or more table joins? What is a suggester hardware when we're expecting the database to grow extremely large? How stable is MySQL and the InnoDB engine with terrabytes behind? Etc, etc, etc... (Of course we assume that the database structure is very well designed and using optimal indexing.) So, if anybody has practical eperience about these issues, please give me some advice. Thank you, Daniel On Sat, 09 Apr 2005 12:50:42 -0400, Michael Stassen wrote: On Apr 9, 2005, at 8:05 AM, olli wrote: hi, if your table is indexed, i think it can theoretically hold 4.294.967.295 rows, because that's the maximum for an unsigned integer value in mysql and indexing doesn't work with bigint types as far as i know. but, i'm not really sure about that. I would suggest checking the manual, where answers are readily available, rather than speculating. MySQL limits tables to 8 million terabytes, so table size is currently limited by your OS/filesystem, in practice. Since most tables aren't that big, the default structure for a MyISAM table limits you to 4Gb in size. You can change this at table creation (preferred), or later with an ALTER statement, using the AVG_ROW_LENGTH and MAX_ROWS options. You can check the maximum size of an already defined table with SHOW TABLE STATUS. References: http://dev.mysql.com/doc/mysql/en/table-size.html http://dev.mysql.com/doc/mysql/en/full-table.html http://dev.mysql.com/doc/mysql/en/create-table.html http://dev.mysql.com/doc/mysql/en/show-table-status.html Am 09.04.2005 um 11:42 schrieb Daniel Kiss: Hi All, I would like to know how big is the biggest database that can be handled effectively by MySQL/InnoDB. Like physical size, number of tables, number of rows per table, average row length, number of indexes per table, etc. Practically, what if I have a master/detail table-pair, where the master contains about 30 million rows, the detail in average contains 50 row for each master row, so about 1.5 billion rows in total? I know that the performace heavily relies on the hardware, but let's assume that the MySQL server runs on a high-end machine with about 2GB of RAM. Performance is largely determined by db design, indexing, and how your queries are written. For example, a full table scan of your detail table would look at 50 times more rows than an index lookup of just the rows which match the one master row in question. Quadrupling your disk speed wouldn't help much relative to adding the index. In other words, all else being equal, faster hardware gives faster performance, but optimizing your table structure, indexes, and queries has a bigger impact on performance. Tuning your server parameters to match your usage and resources can also make a big difference. If you haven't yet chosen your high-end machine with about 2GB of RAM, you should know that Which hardware is best? is a FAQ on this list, so you should be able to find numerous suggestions by searching the list archives http://lists.mysql.com/mysql. Does anyone have experience with extremely large databases? You may find Jeremy Zawodny's book, High Performance MySQL, useful. http://www.oreilly.com/catalog/hpmysql/ Thank you, Daniel Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Collations
Hi Everyone, I used MySQL 4.1.0 and I've just upgraded to 4.1.2. I have noticed that several character sets and collations are just disappeared somehow from the new version. I have checked it with the 'show collation' and 'show character set' command. Is there any switch or configuring option to get back those collations I previously used? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA LOCAL INFILE
Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA LOCAL INFILE
Thanks for the help. The problem was the line terminating character. My source files are in DOS format '\r\n' and not the default posix one '\n'. Thanks again. If it's an option, I would run your datafile through a processor (sed on unix, ultraedit on windows) to search and replace the string with \ and try it with fields terminated by ',' optionally enclosed by '' as mentioned by Mike Johnson's posting (escaped by '\' is default) -Original Message- From: Daniel Kiss [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 3:02 PM To: [EMAIL PROTECTED] Subject: LOAD DATA LOCAL INFILE Hi all, How should I set the parameters of the LOAD DATA LOCAL INFILE command for a comma separated text file like this: Text field,.4,123 Text field with included quote,,45 Text field with , a comma between quotes,1.2,44 Thanks, Dan -- 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]
Version 4.1.1
Hi all, Does anyone know when MySQL 4.1.1 will be released? I was told that around November 15th. Well, it's almost December now. Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: rounding off
Hi! Use the round() function. round(14.9564, 2) = 14.96 round(32.1123, 2) = 113.11 Bye Hello, I need the ability to round off dollar amounts to the nearest 100th of a dollar amount, IE $14.9564 to $14.96 or $132.1123 to $113.11 can this be accomplished with MySQL SQL function ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Configuring built in OpenSLL
Hi all, I would like to know how to configure MySQL with the --with-vio and --with-openssl options. I have a MySQL-4.1 installed on a Red Hat 9 system from .rpm packages. When I use the show variables command, it returns have_opensll = NO. Does it mean that the ssl support is not compiled into this binary, or I just need to switch it on somehow? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL connection
Hi all, I would like to know how to set up the ssl parameters in the my.cnf file. Where can I find a complete example for this, or where is the detailed description of it in the MySQL documentation? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deleting files related to mysql
These are the InnoDB data and log files. They are there because probably you have the InnoDB feature switched on. You can delete them (all of them) safely, if you do not use InnoDB tables, but if you don't swicth off the InnoDB feature (in my.cnf for example), the system will recreate them after restarting the MySQL server. Bye, Hello, in my /var/db/mysql there is a whole lot of files that have spiderman-bin.001 all the way to 038 Can I safely delete these files ? I also have these 25088 Sep 12 23:41 ib_arch_log_00 5242880 Nov 19 16:18 ib_logfile0 5242880 Sep 12 23:41 ib_logfile1 10485760 Nov 19 16:16 ibdata1 208758 Nov 19 16:18 log.01 can any of these be deleted ? Mark DeWar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Client library
Hi all, I have already asked it a few days ago, but I got no answer. Does anyone know how to obtain the newest version of libmysql.dll and the header file(s) for it? Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysql.dll
Hi all, I would like to know who develops and how to obtain libmysql.dll, and is there any Object Pascal header file for this library, or I have to make my own? Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlfront versus mysqlcc
Try SQLyog at www.sqlyog.com It is pretty good, and full of interesting features. Bye I currently use the excellent mysqlfront which is sadly no longer supported. I've tried mysqlcc but it seems non-intuitive, and missing loads of features. It also has a problem working with old versions of mysql 3.23.47 Or have I missed something, does anyone recommend mysqlcc??? zzapper -- vim -c :%s/^/WhfgTNabgureRIvzSUnpxre/|:%s/[R-T]/ /Ig|:normal ggVGg? http://www.vim.org/tips/tip.php?tip_id=305 Best of Vim Tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing Data Directory
Hi all, How can I change the directory of the databases? I have tries the basedir switch in the my.cnf [mysqld] section, but it did not work. How should I do it? Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connection speed
Hi all, I have a MySQL 4.1 installed on a RedHat 9 machine with two interfaces. One of them connects to my local network and the other connects to the internet. My problem is that it takes very long time (5-10 seconds or more) to connect to the MySQL from the local network. It takes long time to connect from the internet as well, but it is still faster then from the local network. (Weird, isn't it?) Is there any chance to speed up the connection, because this is a critical issue to me. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection speed
It is definitely not a DNS problem. I use IP addresses. Any other idea? Thanks Sounds like a DNS problem to me. Daniel Kiss wrote: Hi all, I have a MySQL 4.1 installed on a RedHat 9 machine with two interfaces. One of them connects to my local network and the other connects to the internet. My problem is that it takes very long time (5-10 seconds or more) to connect to the MySQL from the local network. It takes long time to connect from the internet as well, but it is still faster then from the local network. (Weird, isn't it?) Is there any chance to speed up the connection, because this is a critical issue to me. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ROWID
No. There is no physical address of a row in MySQL. On Thu, Nov 06, 2003 at 09:59:41AM -0800, Wan, Wenhua wrote: Hi there, Both Oracle and Informix use ROWID to uniquely represent the location of each row of data in a table. ROWID is basically a hidden column or pseudocolumn for each table, and it is the fastest way to retrive a row from a table. Does MySql have similar field? If is, what's the name and how to access it? Thank you very much in advance for your advice. http://www.mysql.com/doc/search.php?q=rowid Ok, so that search produces this: If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, you can also refer to it as _rowid (new in Version 3.23.11). But that's not what a ROWID is compared to what I think the original poster was looking for. In Oracle for example, a ROWID is the unique address of a row in the database. Every row, unique key or not has a unique address. Is there such a thing in MySQL? ROWIDs are extremely useful for guaranteeing that you are manipulating the exact row that you think you are. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: default-character-set=latin5
Have you tried this setting in the my.cnf file? [mysqld] default-character-set=latin5 Good luck Hi, I am from turkey.I have used mysql for a longtime.I downloaded mysql_win 4.0.16 and then setup,But I can't startup mysql with latin5 support.What can I do..? How can I recompile mysql with character_set=latin5 or where can I find it which complied mysql with latin5. OS=winxp Web server=Apache Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 / Stored Procedures / SAP DB (MaxDB)
Hi all, I was reading the SAP DB chapter on mysql.com. I did not find all the answers for my questions, and maybe one of you knows them. So, I am in the designing phase of a robust enterprise database applications with a lot of modules. I am planning to use MySQL/InnoDB as a database server, but I really miss the stored procedure feature. I tried to find a way of surmounting this problem with an Apache/PHP server intalled next to the MySQL, so the functions of my stored procedures would be performed by a php engine. I know it has some problems, just like that the php scripts will run out of the transaction context, so I must be very careful with this. I know that MySQL 5 will support stored procedures, just like SAP DB supports them now. Does anyone know how SAP DB (or MaxDB) will be integrated with MySQL? Will it be supported just like InnoDB? So I would be able to access my SAP DB tables and SAP stored procedures through the MySQL interface? Or this will be a completely separate product? To be frank, I don't really want to give up using MySQL interface, because I have a really good and reliable way of accessing MySQL from my client applications. On the other hand, as for me, InnoDB is a perfect thing. I love to use it. So if MySQL 5 will come out in the near future, I think I'm gonna use it with the MySQL 5 stored procedure feature, if it will be reliable enough. What is your opinion? Do you know when MySQL 5 will be announced? Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Login
Hi, Does MySQL use other port(s) for network communicaton than the default 3306, or this is the only one. The reason why I ask it that I set up a firewall and I left this port open. But somehow, I cannot login from outside. The firewall is definitely configured well, and lets the 3306 port packages go through normally. Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Login
Hi, Yes, I have configured MySQL users as well. Anyway, I have a user who is allowed from anywhere. I have no other idea. The problem must be the firewall somehow, because if I switch it off, everything is ok. But when I switch it back on (with port 3306 opened) my MySQL server becomes unreachable from outside. That's why I asked that MySQL used other ports than 3306. Thanks, niel Hi! Have you added users that are allowed to log in from hosts other than localhost? Regards, Chris Daniel Kiss wrote: Hi, Does MySQL use other port(s) for network communicaton than the default 3306, or this is the only one. The reason why I ask it that I set up a firewall and I left this port open. But somehow, I cannot login from outside. The firewall is definitely configured well, and lets the 3306 port packages go through normally. Thanks, niel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Next release of v4.1
Hi all, Does anyone know when the next release of MySQL version 4.1 will come out? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SubQuery bug again in 4.1
Hi all, I think you misunderstood me. :-) I try to be more detailed, and I will also give an example. So, I have these two tables: CREATE TABLE main ( ID int not null, Value int, primary key (ID)) Type = InnoDB; CREATE TABLE sub ( MainID int not null, KeyDate date not null, SubValue int not null, primary key (MainID, KeyDate), foreign key (MainID) references main (ID)) Type = InnoDB; In the tables I have these lines: INSERT INTO main VALUES (1, null), (2, null), (3, null), (4, null), (5, null); INSERT INTO sub VALUES (1, '2001-01-01', 5), (1, '1999-01-01', 10), (2, '2001-01-01', 3), (2, '2001-01-02', 4), (4, '2001-01-01', 8); Now, I want to update the `Value` fields in the `main` table for ALL records to contain the latest `SubValue` from the `sub` table. So I want this to be in the `main` table: ID Value -- - 1 5 2 4 3 null 4 8 5 null -- - For this I need to select the most recent `SubValue` from the sub table (e.g.: The `SubValue` with the latest `KeyDate` for a specified `MainID`). I can do that this way (if you have other idea, tell me! :-)): select SubValue from sub where MainID = xxx order by KeyDate desc limit 1 (xxx means an ID from the `main` table) This query obviously returns with one record or null so I expect that this won't be a problem if I use this query as a subquery. (By the way, I get the same weird behavior in any subquery expression where I use the limit parameter.) Now, I want to update my `main` table to get the result above. update main set Value = (select SubValue from sub where MainID = main.ID order by KeyDate desc limit 1) This update sequence runs well, but the result in the `main` table will be this: ID Value -- - 1 5 2 4 3 4 4 4 5 4 -- - which is absolutelly not what I expected. Thanks for your help in advance, Dani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SubQuery bug in 4.1
Hi all, I have two tables CREATE TABLE main ( ID int not null, Value int ); CREATE TABLE sub ( mainID int not null, KeyDate date not null, SubValue int not null ); I want the Value field in the main table to be set to the latest SubValue in the sub table. I suppose this syntax should work. But it does not, and sets the Value fields to incorrect values. update main set Value = (select SubValue from sub where main.ID = sub.mainID order by KeyDate desc limit 1) Any ideas? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql scripts
Hi all, I have a question about mysql client. How can I use it in such a way that it just executes a script on a specified database and then exists? Thanks, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
License
Hi all, If I develop a software for my company that works with MySQL and this software will be used only inside the company, do I have to purchase commercial license? Thanks, Dan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thist script does not run
Hi all, I am trying to run this script below on a MySQL-4.0.11-NT server. It stops at the UserLogs table, but if I swap the last two tables' creation order, it runs correctly. Can anyone help me with this? Thanks Dan /* Begin */ CREATE TABLE `Branches` ( `Id` int unsigned not null, /* Unique id of the branch */ `Name` varchar(40) not null, /* The name of the branch */ `Address1` varchar(40) not null, `Address2` varchar(40) not null, `Address3` varchar(40) not null, `Address4` varchar(40) not null, `PostCode` varchar(10) not null, `Phone` varchar(15), `Fax` varchar(15), `Manager` varchar(40) not null, /* The name of the branch manager */ primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of branches'; CREATE TABLE `ClientGroups` ( `Id` int unsigned not null, /* Unique id of the client group */ `Name` varchar(40), /* Name of the client group */ primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of groups clients and suppliers can belong to'; CREATE TABLE `Clients` ( `Id` int unsigned not null, /* Unique id of the client */ `Name` varchar(40) not null, /* Name of the client */ `Address1` varchar(40) not null, `Address2` varchar(40) not null, `Address3` varchar(40) not null, `Address4` varchar(40) not null, `PostCode` varchar(10) not null, `Email` varchar(60) not null, `Phone` varchar(15), `Fax` varchar(15), `InitDate` datetime, `CreditLimit` double, `InsCreditLimit` double, `Status` int not null, `RegNumber` varchar(10), `SalesContactName` varchar(40), `SalesEmail` varchar(60), `SalesMobile` varchar(15), `AccountsContactName` varchar(40), `AccountsEmail` varchar(60), `AccountsMobile` varchar(15), primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of customers and suppliers'; CREATE TABLE `Currency` ( `CurrencyId` varchar(3) not null, /* Common id of the currency */ `Short` varchar(3), /* Short sign of the currency */ `Format` int not null, /* Display format of the currency */ `NegFormat` int not null, /* Display format of negative currency */ `UseShort` enum('F', 'T') not null default 'F', /* Use the short sign of currency is available */ primary key (`CurrencyId`) ) COMMENT = 'Data of currencies'; CREATE TABLE `ProductGroups` ( `Id` int unsigned not null, /* Unique id of the article group */ `Name` varchar(40) not null, /* Name of the article group */ `LoyaltyPercent` double, /* Percentage of loyalty points for the products that belongs to this group */ `Memo` mediumtext, `ValueFactor` double, primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of product groups'; CREATE TABLE `Products` ( `Id` varchar(15) not null, /* The unique id of the product */ `Description` varchar(50) not null, /* The description (name) of the stock */ `ShortDescription` varchar(20) not null, /* The short description of the stock, used on reports */ `Unit` varchar(5) not null, /* The product unit (piece, metre, etc.) */ `VAT` double not null, /* VAT in percent. (Gross = Net * (100 + VAT) / 100 ) */ `TotalStock` double not null, /* Total stock in all warehouses */ `LoyaltyPercent` double, /* The percentage of loyalty points for this product */ `BoxQuantity` double, `ValueFactor` double, primary key (`Id`), index Description (`Description`) ) COMMENT = 'Data of the products'; CREATE TABLE `UserGroups` ( `Id` int unsigned not null, /* The unique id of the group */ `Name` varchar(40) not null, /* The name of the group */ `Memo` mediumtext, primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of user groups'; CREATE TABLE `LoyaltyPoints` ( `ClientId` int unsigned not null, `CurrId` varchar(3) not null, `LoyaltyPoints` double not null, primary key (`ClientId`, `CurrId`), index CurrId (`CurrId`), foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade on update cascade, foreign key (`CurrId`) references `Currency` (`CurrencyId`) on update cascade ) COMMENT = 'Data about loyalti points belonging to clients and currency types'; CREATE TABLE `TransArchive` ( `Id` int unsigned not null auto_increment, /* See table TransActual */ `BranchId` int unsigned not null, `ProductId` varchar(15) not null, `TransId` int not null, `Date` datetime not null, `Quantity` double not null, `Description` varchar(40), primary key (`Id`), index ProductId (`ProductId`, `Date`, `TransId`), index BranchId (`BranchId`, `ProductId`, `Date`, `TransId`), foreign key (`ProductId`) references `Products` (`Id`) on update cascade, foreign key (`BranchId`) references `Branches` (`Id`) on update cascade ) COMMENT = 'Data of pruduct transactions before the last stock count'; CREATE TABLE `TransActual` ( `Id` int unsigned not null auto_increment, /* The unique id of the row */ `BranchId` int unsigned not null, /* The id of the branch the row belongs to */ `ProductId` varchar(15) not null, /* The id of the product the row belongs
Thist script does not run 2
Hi all, Sorry, I forgot to tell, that my default table type is InnoDB. I am trying to run this script below on a MySQL-4.0.11-NT server. It stops at the UserLogs table, but if I swap the last two tables' creation order, it runs correctly. Can anyone help me with this? Thanks Dan /* Begin */ CREATE TABLE `Branches` ( `Id` int unsigned not null, /* Unique id of the branch */ `Name` varchar(40) not null, /* The name of the branch */ `Address1` varchar(40) not null, `Address2` varchar(40) not null, `Address3` varchar(40) not null, `Address4` varchar(40) not null, `PostCode` varchar(10) not null, `Phone` varchar(15), `Fax` varchar(15), `Manager` varchar(40) not null, /* The name of the branch manager */ primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of branches'; CREATE TABLE `ClientGroups` ( `Id` int unsigned not null, /* Unique id of the client group */ `Name` varchar(40), /* Name of the client group */ primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of groups clients and suppliers can belong to'; CREATE TABLE `Clients` ( `Id` int unsigned not null, /* Unique id of the client */ `Name` varchar(40) not null, /* Name of the client */ `Address1` varchar(40) not null, `Address2` varchar(40) not null, `Address3` varchar(40) not null, `Address4` varchar(40) not null, `PostCode` varchar(10) not null, `Email` varchar(60) not null, `Phone` varchar(15), `Fax` varchar(15), `InitDate` datetime, `CreditLimit` double, `InsCreditLimit` double, `Status` int not null, `RegNumber` varchar(10), `SalesContactName` varchar(40), `SalesEmail` varchar(60), `SalesMobile` varchar(15), `AccountsContactName` varchar(40), `AccountsEmail` varchar(60), `AccountsMobile` varchar(15), primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of customers and suppliers'; CREATE TABLE `Currency` ( `CurrencyId` varchar(3) not null, /* Common id of the currency */ `Short` varchar(3), /* Short sign of the currency */ `Format` int not null, /* Display format of the currency */ `NegFormat` int not null, /* Display format of negative currency */ `UseShort` enum('F', 'T') not null default 'F', /* Use the short sign of currency is available */ primary key (`CurrencyId`) ) COMMENT = 'Data of currencies'; CREATE TABLE `ProductGroups` ( `Id` int unsigned not null, /* Unique id of the article group */ `Name` varchar(40) not null, /* Name of the article group */ `LoyaltyPercent` double, /* Percentage of loyalty points for the products that belongs to this group */ `Memo` mediumtext, `ValueFactor` double, primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of product groups'; CREATE TABLE `Products` ( `Id` varchar(15) not null, /* The unique id of the product */ `Description` varchar(50) not null, /* The description (name) of the stock */ `ShortDescription` varchar(20) not null, /* The short description of the stock, used on reports */ `Unit` varchar(5) not null, /* The product unit (piece, metre, etc.) */ `VAT` double not null, /* VAT in percent. (Gross = Net * (100 + VAT) / 100 ) */ `TotalStock` double not null, /* Total stock in all warehouses */ `LoyaltyPercent` double, /* The percentage of loyalty points for this product */ `BoxQuantity` double, `ValueFactor` double, primary key (`Id`), index Description (`Description`) ) COMMENT = 'Data of the products'; CREATE TABLE `UserGroups` ( `Id` int unsigned not null, /* The unique id of the group */ `Name` varchar(40) not null, /* The name of the group */ `Memo` mediumtext, primary key (`Id`), index Name (`Name`) ) COMMENT = 'Data of user groups'; CREATE TABLE `LoyaltyPoints` ( `ClientId` int unsigned not null, `CurrId` varchar(3) not null, `LoyaltyPoints` double not null, primary key (`ClientId`, `CurrId`), index CurrId (`CurrId`), foreign key (`ClientId`) references `Clients` (`Id`) on delete cascade on update cascade, foreign key (`CurrId`) references `Currency` (`CurrencyId`) on update cascade ) COMMENT = 'Data about loyalti points belonging to clients and currency types'; CREATE TABLE `TransArchive` ( `Id` int unsigned not null auto_increment, /* See table TransActual */ `BranchId` int unsigned not null, `ProductId` varchar(15) not null, `TransId` int not null, `Date` datetime not null, `Quantity` double not null, `Description` varchar(40), primary key (`Id`), index ProductId (`ProductId`, `Date`, `TransId`), index BranchId (`BranchId`, `ProductId`, `Date`, `TransId`), foreign key (`ProductId`) references `Products` (`Id`) on update cascade, foreign key (`BranchId`) references `Branches` (`Id`) on update cascade ) COMMENT = 'Data of pruduct transactions before the last stock count'; CREATE TABLE `TransActual` ( `Id` int unsigned not null auto_increment, /* The unique id of the row */ `BranchId` int unsigned not null, /* The id of the branch the row belongs to */ `ProductId`
Re: Counting null values
Hi Octavian, Try this: select FieldName, count(*) from TableName group by FieldName Bye, Danny At 16:39 2003.02.08._+0200, you wrote: Hi all, I have a table where I have something like this: | abc | | abc | | xxx | | null | | null | | null | I want to count these lines to give the result 5, meaning a distinct count for values which are not null, and counting all the null values. This means 1 for abc, one for xxx, and 3 for null values. Can you tell me what sql query should I use for counting this? Thank you. Teddy, Teddy's Center: http://teddy.fcc.ro/ Email: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
4.1 features
Hi all, Can anyone tell where can I find some documentation about the new features released in MySQL 4.1? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB on update cascade
Hi all, Does anyone know why 'on update cascade' option does not work in InnoDB foreign keys? Is it supported anyway? Thanks, Daniel mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: LEGAL information about MySQL.
Hi, Well I think the licence rules of MySQL are not that comlicated than you wrote below. I studied this a few month ago, and I interpret it much simplier. 1. You develop a software under GPL which uses MySQL (connects to, includes, uses, etc. whatever). In this case you don't need any licence for MySQL, of course. 2. You develop a commercial application (and you do not distribute the source code of it). In this case you have two choices depending on your application. a) Your application needs the MySQL server, because it is a database handler application. In this case you have to buy licence(s), but it is not you, who has to buy it. Your purchaser is the one who has to do this if he wants to use your application. b) Your application CAN use MySQL server as an alternative SQL server, but is is able to use other(s), too. For example, your application can use MySQL and PostgreSQL and MS SQL for the same task as alternatives. In this case your purchaser don't need to buy any MySQL licence to use it. So this is how I interpret the licence stategy of MySQL AB. Please correct me if i have mistaken in something. Bye, Daniel At 10:41 2002.12.11.dø+0200, you wrote: Hi all, First I want to say I am a MySQL fan. I think MySQL AB interpretation of GPL creates confusion too. Do they intend to crete and keep alive this confusion ? What for?... Let me show you something taken from MySQL Reference Manual for version 4.0.2-alpha You can use the MySQL software for free under the GPL: . * When you distribute the MySQL source code bundled with other programs that are not linked to or dependent on MySQL Server for their functionality even if you sell the distribution commercially. Consider the following exaples: PROGRAM1 EMBEDS_MySQL { do things ... } 2) PROGRAM2 { CONECT_TO_MySQL .. } where: CONNECT_TO_MySQL = querying MySQL using it's user interfaces. EMBEDS_MySQL = linking to MySQL, depends on MySQL ... anything but CONNECT_TO MYSQL. I thing PROGRAM1 must be distributet under GPL but PROGRAM2 can be distributed with any license. I may be wrong but I think MySQL ab doesn't say clearly( at least in the user's manual) what I just said above. More than that, reading the manual creates me the impresion that I must distribute the PROGRAM2 under GPL( this also is not explicitly said). Of course, they can define MySQL as a system insted of defining it as a database engine. This way, doing anything with MySQL, even just CONNECTING_TO_MYSQL, the result is a new system( MySQL+my PROGRAM2) that should be released under GPL. This definitoin of MySQL would be just a joke (define and give me the source codes of your system). Why MySQL AB does not try to explicitly define what the words dependent on MySQL mean? Do they want to create confusion? Again, what for? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to get MySQL to list current db's ?
Hi, At 11:35 2002.12.09._-0500, you wrote: Is there a way to ask MySQL to list the db's that are currently created? Yes: SHOW DATABASES Good luck, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using Delphi with MySql to store binary data
Hi, Read a blob field (containing any kind of data) from the database is easy. You have to do the same thing as in case of other field types. And if you use TBlobField field type (or dynamic typecast), this field type has a .SaveToStream (.SaveToFile) method. You can use this to save the data to anywhere. To write a blob field in a query, you should use parameters. For example: insert into Table values (:BlobValue) Then you have to set the value of this parameter like this: .ParamByName('BlobValue').LoadFromStream(SourceStream, ftBlob); This methods loads the content of SourceStream to the parameter from the cursor to the end of the stream. (you can use .LoadFromFile method the same way). Then you can just execute the query. Good luck, Daniel p.s.: You must do it the same way in case of any SQL servers. At 07:34 2002.11.28._+0200, you wrote: Hi, I would like to know how to store binary data as images, sounds, Rich Text Format, pdf-files, as BLOB in MySQL. (from Delphi). Sincerely, Adrian Tarniceru - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Where is 4.1 source
Hi all, Where can I download MySQL version 4.1 source code? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Version 4.1
Hi all, I would like to know when MySQL version 4.1 will be available for windows as compiled install kit? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reg: InnoDB
Hi, InnoDB is under GPL as well as MySQL. Anyway InnoDB is included in any distributions of MySQL-Max on any OS. Bye, Daniel At 11:50 2002.11.07. +0530, you wrote: Hello All, I would like to know whether MySQL-Max or MySQL 4 with InnoDB is freely available or InnoDB is available in free of cost and where can I download it Regards, Charitha - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [newbie question] how do you change the default charset of MySQLunder windows
Hi Paul, Was the table01 created before you did the new settings and restart the service? Because if yes, youhave to recreate the table or rebuild the indexes. Anyway, I don't really know how chinese double-byte character representation works. Fortunately the Hungarian language uses the latin characters. In spite of this I have problems with sorting Hungarian strings also. :-) Good luck, Daniel At 16:13 2002.10.30. +0800, you wrote: Hi Daniel, Actually I've tried the following in the my.ini file: [mysqld] default-character-set=big5 [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-nt.exe but when I open up the command prompt and enters the mysql monitor, try the following I got into something like this: mysql update table01 set field03 = some chinese chars; Note- 1) field03 is of type char(10); 2) the chinese character I've enter just happened to have one of its internal double-byte representation as a back-slash (doh!) Any suggestion on how to fix this? Thanks a lot! Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [newbie question] how do you change the default charset of MySQLunder windows
Hi You have to write a line like this into your my.cnf (in the C: root directory): default-character-set=... or whatever you like. You can find examples in your mysql directory for the my.cnf file. Oh, yes! You're from Hong Kong. So maybe you need this: default-character-set=big5 Bye, Daniel At 14:47 2002.10.30. +0800, you wrote: Hi all, I've checked the doco and found that it can be changed by ./configure under Linux, but how do I change the default charset of MySQL under Win2K. Do I have to re-compile the source or better yet can I change it on the fly or using WinMySQLAdmin or something? TIA Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [newbie question] how do you change the default charset of MySQLunder windows
Hi Paul, I does not matter if you use the my.ini in the Windows directory or the my.cnf in the c: root, but the last one will be used first. You my.cnf (and my.ini also) should look like this: ---stat of my.cnf [mysqld] default-character-set=hungarian default-table-type=InnoDB innodb_data_file_path = ibdata1:10M:autoextend:max:2000M innodb_data_home_dir = c:\mysql\ibdata innodb_log_group_home_dir = c:\mysql\ibdata\logs innodb_log_arch_dir = c:\mysql\ibdata\logs set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=8M set-variable = innodb_log_buffer_size=1M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=48M set-variable = innodb_additional_mem_pool_size=2M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 ---end of my.cnf This is a simple my.cnf I use under WinNT for InnoDB tables as default table type, and 'hungarian' as default character set. Basically you need only the first two lines. But the point is, that the default-character-set line must be in the [mysqld] section, because the mysql daemon (or service under WinNT) will take only commands only in this section into consideration. Bye, Daniel At 15:44 2002.10.30. +0800, you wrote: Thanks Daniel, I've looked for the my.cnf file but couldn't find it but I found a my.ini under the c:\Winnt directory, with the following content: [WinMySQLAdmin] Server=c:/mysql/bin/mysqld-nt.exe I've tried appending the line default-character-set=big5 to it and restarted MySQL but doesn't seem to do the trick. Should I create a my.cnf in the C: root instead with the default-character-set to it? Thanks a lot! Paul - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 30, 2002 3:06 PM Subject: Re: [newbie question] how do you change the default charset of MySQL under windows Hi You have to write a line like this into your my.cnf (in the C: root directory): default-character-set=... or whatever you like. You can find examples in your mysql directory for the my.cnf file. Oh, yes! You're from Hong Kong. So maybe you need this: default-character-set=big5 Bye, Daniel At 14:47 2002.10.30. +0800, you wrote: Hi all, I've checked the doco and found that it can be changed by ./configure under Linux, but how do I change the default charset of MySQL under Win2K. Do I have to re-compile the source or better yet can I change it on the fly or using WinMySQLAdmin or something? TIA Paul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: help needed
Hi Sheela, At 01:49 2002.10.11. -0700, you wrote: hi, I am using mysql while creating a table I want to give column descriptioneither by using a query or GUI ex create table t1( a bigint not null ' here i wamt to tell what for the a is ) pl help me thanks sheela You can add comments to your sql statements like this: /* comment here */ For example: create table t1 (a bigint not null, /*comment1*/ b int, /*comment1*/ c char /*comment1*/ ) Good luck, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Unsort Single Column
Is there primary key on field Value? Or any index? Daniel At 14:37 2002.10.04._ +0800, you wrote: Hi, My tables is something like this Value Count B 10 C 20 A 15 D 8 When I query using SELECT Value, Count FROM Table, the data is appearing exactly at the order above, however when I select only a single column using SELECT Value FROM Table, the data get sorted. Instead of Value B C A D It's now Value A B C D Can I get the order back to BCAD? Even the best Programming Language can't prevent Programmer from making mistakes, so if you want to become a Programmer, you just have to learn not to make mistakes - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How to create a table from other tables
Hi! It sounds like a simple UNION ALL thing, but I think I don'tunderstand well what your problem is. Can you give an example? Daniel At 12:59 2002.09.26. +0800, you wrote: Dear all i had two tables, tableA Table B. I want to create a permanent table call Table C which the first two field will be equal to Table A's first 2 field and last field will be equal to Table B's first field. can anyone pls tell me what query should i use? it will be nice if you can provide me a simple example! Thx jack [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM to InnoDB
Hi! At 10:12 2002.09.24._ +0100, you wrote: Question is, will this require any SQL code changes to our application? We make heavy use of features like temporary tables, autoincrement columns etc. Would if be as simple as dumping the database and restoring it to InnoDB tables? The simple answer is: No. You don't have to change any code in your application, InnoDB will works with the actual code, because it is 100% compatible with MyISAM. But!!! If you want to use special features of InnoDB table types (which are not in MyISAM), you might need changing your code. For example when you decide to use transactions (non-autocommit mode) or foreign key constraints. Bye, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: transactions...
Hi! At 00:56 2002.09.23._ -0300, you wrote: Do I loose too much performance using InnoDB tables in autocommit mode instead of using MyISAM tables? The real quiestion is: Why do you want to use InnoDB tables when you don't want to use its transaction safe features? Anyway yes. I'm sure it is not worth using InnoDB tables in autocommit mode. If you want to do this use MyISAM instead. Bye, Daniel (mysql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: The table is full
Hi, There's no table size limit, but if you use InnoDB, maybe you need to extend the size of your tablespaces, because they CAN be full. Check out your my.cnf Good luck, Daniel At 08:21 2002.09.06._ -0300, you wrote: Im inserting 6.000.000 records on a table... but in 3.500.000 the mysql print: The table is full. The tables of mysql have a record limit to insert ? My system is MySQL 3.23.52/InnoDB My table handler is InnoDB but in MyISAM the mysql print the message too... tnks. - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes look: cannot open my eyes - Reply: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table partitioning functionality
Hi, No, I'm afraid. MyISAM tables cannot be cut in several pieces. But check ot the InnoDB table type, because they work very similar to Oracle. InnoDB uses tablespaces, and there can be many of them, maybe with autoextend functionality. I know it is not the same you would like, but maybe you can use this feature for your task. Bye, Daniel At 12:28 2002.09.05. +0200, you wrote: Hi, Does MySQL offering a similar functionality as Oracle does with table partitioning? Oracle can split a table into several pieces where the pieces are - usually - identified by a range of valus. For example, one may split a table holding data on a daily basis into monthly partitions. Oracle will store the data in the partition identified by month of the new inserted data. As the tablespace used by each partition can be specified this is very usefull to spread the disk IO to several harddisks. The main reason for partitioning a table is to make VERY large tables more performant. Oracle fetches only the partions needed by the SQL statement, this means a full table scan will only process partitions identified by the WHERE clause and not the full range of data. If MySQL does not offer this or a simmilar functionality, how does MySQL perform on very large tables (I'm talking here about 10 Gigs+ of data stored in a single fact table)? Best Regards, Wolfgang - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql password ( )
Hi, The password() function is a one way encoder. In other words it just generates some kind of checksum of the input parameter. So you cannot decrypt them, but it is much safer than encrypting and decrypting strings, because even the attacker cannot decrypt them. :-) You can use it this way for example: Let's say my password is: abcdef Password('abcdef') - 0bc7a0b7062090d5 (You must store this checksum in the database.) When you want to check if the password entered by the user is correct, you need to do this: The password entered by the user: abcdeg You call the password function: Password('abcdeg') - 0bc7a2b806208ed6 Compare the stored checksum and this one: NOT EQUAL - entered password is bad Notice that if there is only a small difference between the right and the entered words the checksum will be different in many aspects. That's why it is quite safe. But you can get better protecting if you use the MD5 function. It works the same way than the password function, but generates 32 character long checksum instead of 16. At 10:47 2002.08.28._ -0600, you wrote: I have used the mysql password(\$pass \) function in the past to encrypt passwords into the db. but can not decrypt them if needed. I know this is not something new. Is there a better way to protect passwords in the db and then decrypt them if needed ? Thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql password ( )
Securest? Yes, maybe. But first of all the security of a web site does not depend on the password encoding system (PASSWORD or MD5) you use in the MySQL database. (I mean firewalls, good OS, so on.) At 00:32 2002.08.29. -0400, you wrote: so md5 would be the securest way to handle password security for a website? Randy - Original Message - From: Daniel Kiss [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 29, 2002 12:19 AM Subject: Re: mysql password ( ) Hi, The password() function is a one way encoder. In other words it just generates some kind of checksum of the input parameter. So you cannot decrypt them, but it is much safer than encrypting and decrypting strings, because even the attacker cannot decrypt them. :-) You can use it this way for example: Let's say my password is: abcdef Password('abcdef') - 0bc7a0b7062090d5 (You must store this checksum in the database.) When you want to check if the password entered by the user is correct, you need to do this: The password entered by the user: abcdeg You call the password function: Password('abcdeg') - 0bc7a2b806208ed6 Compare the stored checksum and this one: NOT EQUAL - entered password is bad Notice that if there is only a small difference between the right and the entered words the checksum will be different in many aspects. That's why it is quite safe. But you can get better protecting if you use the MD5 function. It works the same way than the password function, but generates 32 character long checksum instead of 16. At 10:47 2002.08.28._ -0600, you wrote: I have used the mysql password(\$pass \) function in the past to encrypt passwords into the db. but can not decrypt them if needed. I know this is not something new. Is there a better way to protect passwords in the db and then decrypt them if needed ? Thanks Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
libmysql
Hi, I'm interested in what are the differences between different versions of libmysql.dll's and libmysql.so's? And are their versions the same under linux and windows? Where can I get their header files in C or Object Pascal? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: temporaere Dateien.
Hi, Please write in English to this mailing list, or go to a German one. Vielen Danke, :-) Bye Daniel At 11:04 2002.08.15. +0200, you wrote: Hallo, Ich wuerde gern wissen, warum und wann MySQL temporaere Dateien erzeugt. Ausserdem wie vermeide ich, dass meine Festplatte bei der Ausfuehrung meiner Applikation, nicht voll wird, wenn so viele temporaere Dateien reingelegt werden. Vielen Danke. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Benchmark
Hi, Where can I find some benchmark test result documentation. First of all, I'm interested in comparing MySQL, Oracle and MSSQL speed in different situations. Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL logo
Hi, I'm planning to develop a commercial application with MySQL database engine. I would like to include the logo of MySQL on the splash screen and about box of the software. Do I need any permission to use the logos of MySQL. Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
String collating support
Hi, I've read in the MySQL manual that If the sorting rules for your language are too complex to be handled with the simple sort_order[] table, you need to use the string collating functions I also read that the best documentations about how to use this feature are the already implemented character sets. (czech, gib5, gbk, sjis, tis160) So, my native language is in this category. Can somebody help me, how to create character sets using this feature? Thanks, Daniel SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Czech complex character set
Hi, I would like to get the Chech complex character set. Where can I find it? Thanks, Daniel SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Delphi MySQL Access Components
Hi, Can someone tell me what is the best MySQL native component set for Delphi? It does not matter if it is commercial, but I need one urgently. Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Delphi MySQL Access Components
Hi, Yes, I'm using it actually, but I ask te author if MySQL features will be extended to stored procedures, when will he come out with the new version. He told me that he finished the development of the component set and gave it to sourcesorge.net to continue it. Anyway, the newest version of it is still in beta state. And it never will be finished. So, any other ideas? Thanks, Daniel At 17:22 2002.07.24. +0800, you wrote: Use WinZeos component for delphi 6 Search it on GOOGLE It helps me too R.B.Roa Traffic Management Engineer PhilCom Corporation Tel. No.(Office) (088) 858-1028 (Home) (088) 858-8889 Mobile No. (63) (919-3085267) -Original Message- From: Daniel Kiss [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, July 24, 2002 4:23 PM To: [EMAIL PROTECTED] Subject:Delphi MySQL Access Components Hi, Can someone tell me what is the best MySQL native component set for Delphi? It does not matter if it is commercial, but I need one urgently. Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Sorting order in MySQL
Nikolaos and Victoria, Victoria! Just like Nikolaos, I also don't know what you mean send new character sets as seen with the explanations. The first thing I have to mention that I'm not sure how MySQL string comparison works, but I think it just compares the characters at the same position (by the actual char set). Am I right? Because if I am, the problem is not the actual character sets distributed with MySQL. The problem is the METHOD of string comparison. (In case of Hungarian and in case of Greek also, I think. And maybe in case of other languages.). So, I think we should discuss it in details, but first of all I need some detailed manual about string comparison in MySQL. Where can I find it? Thank you, Daniel At 09:41 2002.07.22. +0300, Nikolaos Georgiafentis wrote: Victoria, i did not get it. What do you mean by Nicolaos and Daniel, can you send new character sets as you see them with the explanations?. Please could you be more specific? (SQL,QUERY) With thanks Georgiafentis Nikolaos Project Manager [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] iTEL LTD 409 Vouliagmenis Ave. Ilioupoli, 16346 Tel: +30 10 9790050 Fax: +30 10 9790051 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Foreign key definitions
Hi, I just would like to ask that what are the following foreign key definition extensions good for exactly? I mean how does these SQL syntaxes work? MATCH FULL | MATCH PARTIAL ON DELETE/UPDATE RISTRICT/CASCADE/SET NULL/NO ACTION/SET DEFAULT Thx, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB free space
Hi, How can I ask the MySQL server how much free space is in InnoDB tablespaces? Thx, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sorting order in MySQL
Hi all, I (like almost everyone else) use different character set than latin1. I have noticed that the Hungarian sorting does not work properly. I have checked he .conf files, and if I see it well, the way of comparing character fields is very simple. Just comparing he characters at the same position from left to right, just like in English. I don't speak too many languages, so I don't know this problem is in other cases, but in the Hungarian languages sometimes you have to do more than this. Sometimes you have to check two or more characters next to each other at the same time. So, instead of too long explaination: Is there any way in MySQL (for example write an UDF for comparing texts for sorting) to change the sorting order? Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL Database Designer
Hi all, I have created a Database Designer for the MySQL databases on Windows. I wanted to attach it to this mail, but it's more than 300K, so I wouldn't be a good idea to send it to the mailing list, I think. But I would like some of you to check it out. So, if you would like to be a beta tester :-), just send me an empty mail to [EMAIL PROTECTED] with the subject: MyDBDesigner and I'm gonna send the program to you. When you tested it I would be very pleased to write your notices and suggestions to make the program better and more useful. Thanks, Daniel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php