Re: ALTER TABLE - how to fix truncated data?
Gleb, As a sidenote - shouldn't MySQL raise an error when data gets truncated? MySQL raises a warning after such ALTER operation. See: mysql create table dt(a decimal(4,1)); Query OK, 0 rows affected (0.01 sec) mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why i can't connect to the mysql server from a client pc?
hi, mysql is a very goode software,so i made mysql_3.23_58 and mysql_4.1 in two of my server, and i changed the root password, but why i can't connect to the mysql server from a client pc(use mysqlcc or any other tools)? the error message is: [206] ERROR 1130: Host '218.4.**.***' is not allowed to connect to this MySQL server please tell me what's wrong with it,how to solve this problem! zhoubin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why i can't connect to the mysql server from a client pc?
zhou bin wrote: hi, mysql is a very goode software,so i made mysql_3.23_58 and mysql_4.1 in two of my server, and i changed the root password, but why i can't connect to the mysql server from a client pc(use mysqlcc or any other tools)? the error message is: [206] ERROR 1130: Host '218.4.**.***' is not allowed to connect to this MySQL server Probably user 'root' is only allowed to access MySQL from 'localhost' (which it should be for security reasons). You can learn more about the privilege system and how to grand users access from various locations to various parts of the database at: http://dev.mysql.com/doc/refman/5.0/en/privilege-system.html http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html http://dev.mysql.com/doc/refman/5.0/en/user-account-management.html http://dev.mysql.com/doc/refman/5.0/en/account-management-sql.html etc. Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Access 2002 hangs with MyODBC 3.51.11
Dear sir Thank you for your answer. You help me much. I replace msjet40.dll with an oldest from ServicePackFiles/ and works fine -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 18, 2005 7:12 PM To: mysql@lists.mysql.com Subject: Fw: Access 2002 hangs with MyODBC 3.51.11 The attachment was stripped by the list manager: forwarded to list for general information Shawn - Forwarded by Shawn Green/Unimin on 10/18/2005 12:07 PM - Shawn Green/Unimin 10/18/2005 11:48 AM To nikos [EMAIL PROTECTED] cc mysql@lists.mysql.com Subject Re: Access 2002 hangs with MyODBC 3.51.11 nikos [EMAIL PROTECTED] wrote on 10/18/2005 10:58:51 AM: Hello list I recently update my Access 2002 with Office SP3 (Access 10.6501...) and all connections with MySQL through MyODBC 3.51.11 have problems I install the Access on a pc without updating and the driver works fine. This version of Access is 10.25... something. I download again MyODBC driver but I get the same problem: access still hangs up. In the event viewer there is the following problem: Faulting application msaccess.exe, version 10.0.6501.0, faulting module msjet40.dll, version 4.0.9025.0, fault address 0x000e9f60. Does any body know what's the problem? Thank you Nikos Microsoft distributed a security hotfix, updating MSJET40.DLL to version 4.0.9025.0, which broke every version of MyODBC I test with (I have gone back as far as 3.51.2). Actually, AFAIK, it may only break the process of creating linked tables from MS Access. All other functions may remain functional. However, that's primarily what my users need the library to do, so for me it's broken. Related MySQL bug reports: http://bugs.mysql.com/bug.php?id=9932 http://bugs.mysql.com/bug.php?id=11698 http://bugs.mysql.com/bug.php?id=12386 Workaround: On each affected user's machine, replace newer MSJET40.DLL with previous version (4.0.8618.0), attached. (attachment probably stripped during list distribution). This effectively undoes part of the security hotfix but restores the ability to create and repair table links from MS Access through MyODBC to MySQL. Due to Microsoft's file protection system (FPS), you have to replace the backup version first. The backup can be in an i386 folder, a DLLCACHE folder or in several other possible places (depending on the version of the OS and how FPS has been configured). After you make sure that MS Access is turned off, you can replace the main version in the SYSTEM32 folder. Check the version tab in the properties of each file after you make the copy to make sure your intended changes have taken effect. [attachment msjet40.dll deleted by Shawn Green/Unimin] Last I heard, MySQL was still working on a full fix for the problem. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Input on Materialized Views
Andrew Roth wrote: Hi all, We are a group of three students in Professor Ric. Holt's Software Architecture class at the University of Waterloo. As our project, we would like to examine the MySQL source to determine the best way to implement materlialized views. It would be wise to hang around and see if someone from MySQL AB has time to answer the questions, but here's my personal view... I had to look up materialized views and if I read it correctly what is said about this at http://www.akadia.com/services/ora_materialized_views.html: 1. How feasible would implementing materialized views be? It would be quite an adventure, but they might be useful in certain situations (low concurrency, but need for more speed or higher concurrency and less need for accuracy). 2. Any reasons why materialized views haven't been added already? Since 'views' are only introduced in MySQL 5.0 I guess that: - there has not been time yet to think about materialized views - no programmer has had the time to build it - no-one has sponsored to hire extra programmers to build it 4. Any comments at all relating to this endeavor. Good luck? ;-) I think that you have to make changes in a lot of areas. At least you will have to think of: - storage engine(s) (updating materialized views on commit) - query optimizer (trying to rewrite the query to use MVs instead) - adding handling of the new keywords - sceduling of refreshes during off-peak time Kind regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
In article [EMAIL PROTECTED], Martijn Tonies [EMAIL PROTECTED] writes: mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): CREATE TEMPORARY TABLE t1 ( i TINYINT ); INSERT INTO t1 VALUES (42); SELECT * FROM t1; -- Ok, shows 42 UPDATE t1 SET i = 4242; -- SHOW WARNINGS; SELECT * FROM t1; -- Oops, shows 127 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restore of mysqldump-ed data is corrupted
Gleb Paharenko wrote: Hello. Are you sure that you have the same problem (the same character set settings, same broken cyrillic characters and so on)? If not, please, provide as much info as you can. Delyan was using cyrillic symbols with latin1 encoding, which is not designed for this purposes. 4.1.5-gamma-log is a very old release. Check if the problem remains if you use mysqldump which is shipped with 4.1.14 distribution. Let's go step by step: - database server is the same MySQL 4.1.5-gamma - 'All charset variables are set to default 'latin1', except 'character_set_system', which is utf8' is the same - 'data it gets (from php) were in utf8' is the same - 'Mysqldump is used without any options' is the same - 'When importing back into the same database some characters (non latin,cyrillic in fact) get broken' this not the 'same': I get many other characters broken not only cyrillic. Users stores data in many encodings: hebrew, thai, cyrillic, etc... When you say: Delyan was using cyrillic symbols with latin1 encoding, which is not designed for this purposes. what do you really mean? I cannot have different symbols (hebrew, thai, cyrillic) in same table!? I wouldn't say so. This project started when MySQL didn't have support for UTF-8 encoding and because of that all tables are by default latin1. 4.1.5-gamma-log is a very old release So what? The server is the same one on which backup was made. Check if the problem remains if you use mysqldump which is shipped with 4.1.14 distribution Unfortunately I cannot do this. What do you suggest me as solution, how I can get my data back? I cannot believe that is not possible to restore data the same way it was stored. The fact is that utf8 data was stored in latin1 tables and I want that data back in same way. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): Yeah yeah ... so the MySQL design philosophy is that users never make mistakes... Guess they want to sell support contracts, ey? Seriously, if you give a user enough rope to hang themselves AND hand them a chair to stand on, better make sure you have a way to deal with the corpses. CREATE TEMPORARY TABLE t1 ( i TINYINT ); INSERT INTO t1 VALUES (42); SELECT * FROM t1; -- Ok, shows 42 UPDATE t1 SET i = 4242; -- SHOW WARNINGS; SELECT * FROM t1; -- Oops, shows 127 Yeah, total rubbish. Do this in your application - by accident - and explain your boss that the totals are wrong or that he had an input value of 4242 and got 127 in return. Rubbish. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Martijn Tonies wrote: | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 Could be me ... but isn't this a little too late? eg: AFTER you have lost your data? IMO, it should raise an error UNLESS you force it to truncate the data. This would contradict the MySQL design philosophy (others call it simply gotcha) that the user should know what he's doing and the DBMS tries its best to obey. Consider this (version 4.1.14): Yeah yeah ... so the MySQL design philosophy is that users never make mistakes... Even in more serious cases MySQL silently modifies data and structures: A large database with an INTEGER column with NULL-'values' allowed was modified to include this field in the PRIMARY key. The column definition was automagically modified to NOT NULL and all NULL-'values' where converted to 0 (zero). Yeah, emmm, well, we actually used the NULLs as no value (like it is supposed to be used AFAIK) and there was no way anymore to distinguish between NULL and 0. Luckily this was done on a test database and we only had to spend half an hour or so to restore the table from a backup. It would have been very nice to know of this action before it was completed, to say the least. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. I fully agree. Maybe an option SQL_IGNORE_WARNINGS or something along those lines should be introduced to force the execution of such queries. At least most users will be prevented from shooting themselves in the foot unless they specificly specify to do so. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple INNER JOINS
Hi, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. SELECT P.*, Tax_Band, Property_Type FROM Properties P INNER JOIN Tax_Bands USING(Tax_Band_ID) INNER JOIN Property_Types USING(Property_Type_ID) WHERE P.Property_ID = 3 Here is the error message I am getting: Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause' Thanks for your advice. # -- MySQL dump -- # # Table structure for table 'Properties' # CREATE TABLE Properties ( Property_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Name_Or_Number varchar(50), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(12), Sale_Price int(11), Asking_Price int(11), Years_On_Lease int(11), Tax_Band_ID int(11), Property_Type_ID int(11), Number_Of_Bedrooms int(11), Number_Of_Bathrooms int(11), Number_Of_Receptions int(11), Internal_Square_Footage int(11), Internal_Square_Meters int(11), Price_Per_Square_Foot decimal(6,2), Price_Per_Square_Meter decimal(6,2), Additional_Notes text, PRIMARY KEY (Property_ID) ); # # Table structure for table 'Property_Types' # CREATE TABLE Property_Types ( Property_Type_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Type varchar(50), PRIMARY KEY (Property_Type_ID) ); # # Table structure for table 'Tax_Bands' # CREATE TABLE Tax_Bands ( Tax_Band_ID int(11) DEFAULT '' NOT NULL auto_increment, Tax_Band varchar(50), PRIMARY KEY (Tax_Band_ID) ); # --- Dump ends --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
As a sidenote - shouldn't MySQL raise an error when data gets truncated? MySQL raises a warning after such ALTER operation. See: mysql create table dt(a decimal(4,1)); Query OK, 0 rows affected (0.01 sec) mysql insert into dt set a=999.1; Query OK, 1 row affected (0.00 sec) mysql alter table dt change a a decimal(2,1); Query OK, 1 row affected, 1 warning (0.02 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql show warnings; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1264 | Out of range value adjusted for column 'a' at row 1 | +-+--+-+ btw, it seems MySQL CAN do errors, check: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Jigal notified me of this. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Input on Materialized Views
Just like Jigal, I also had to lookup the term materialized view. For the sake of others on the list trying to follow along: a materialized view is basically a self-updating snapshot of a table (or tables) usually containing some sort of intermediate statistical computations involving GROUP BY. For instance, if you have the raw log of visitors to your web site stored in your database and you frequently ran queries that created summarations by day, you could save yourself a lot of processing time by periodically precomputing a daily table from your raw logs showing various statistics broken down for each date. Well, a materialized view would be one way to implement that daily table. Jigal van Hemert [EMAIL PROTECTED] wrote on 10/19/2005 03:51:26 AM: Andrew Roth wrote: Hi all, We are a group of three students in Professor Ric. Holt's Software Architecture class at the University of Waterloo. As our project, we would like to examine the MySQL source to determine the best way to implement materlialized views. It would be wise to hang around and see if someone from MySQL AB has time to answer the questions, but here's my personal view... I had to look up materialized views and if I read it correctly what is said about this at http://www.akadia.com/services/ora_materialized_views.html: 1. How feasible would implementing materialized views be? It would be quite an adventure, but they might be useful in certain situations (low concurrency, but need for more speed or higher concurrency and less need for accuracy). The feasability is directly proportional to the sum of your ambition and talent. It is probable that MySQL will have this feature at some point. You are in the position to make it happen sooner rather than later. 2. Any reasons why materialized views haven't been added already? Since 'views' are only introduced in MySQL 5.0 I guess that: - there has not been time yet to think about materialized views - no programmer has had the time to build it - no-one has sponsored to hire extra programmers to build it (To echo Jigal): Views and triggers are new to v5.0. They are still massaging the last few kinks out of the basic behaviors. Tying them together to form a materialized view is not on the workplan (yet). 3. Any web pages or archived messages relating to this. (I have search the archives and didn't find anything.) Nope, too new. You are the first. 4. Any comments at all relating to this endeavor. Good luck? ;-) I think that you have to make changes in a lot of areas. At least you will have to think of: - storage engine(s) (updating materialized views on commit) - query optimizer (trying to rewrite the query to use MVs instead) - adding handling of the new keywords - sceduling of refreshes during off-peak time Kind regards, Jigal. Good Luck! You are quickly going to exceed the general level of knowledge supported on this list. If you start running into specific problems, you may want to shift this thread to the INTERNALS list, instead. Please keep us informed of your progres, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Multiple INNER JOINS
Shaun, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. That query generates no error in 5.0.13. There have been several cascading join bugs, some fixed, some not. What version are you running? PB - Shaun wrote: Hi, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. SELECT P.*, Tax_Band, Property_Type FROM Properties P INNER JOIN Tax_Bands USING(Tax_Band_ID) INNER JOIN Property_Types USING(Property_Type_ID) WHERE P.Property_ID = 3 Here is the error message I am getting: Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause' Thanks for your advice. # -- MySQL dump -- # # Table structure for table 'Properties' # CREATE TABLE Properties ( Property_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Name_Or_Number varchar(50), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(12), Sale_Price int(11), Asking_Price int(11), Years_On_Lease int(11), Tax_Band_ID int(11), Property_Type_ID int(11), Number_Of_Bedrooms int(11), Number_Of_Bathrooms int(11), Number_Of_Receptions int(11), Internal_Square_Footage int(11), Internal_Square_Meters int(11), Price_Per_Square_Foot decimal(6,2), Price_Per_Square_Meter decimal(6,2), Additional_Notes text, PRIMARY KEY (Property_ID) ); # # Table structure for table 'Property_Types' # CREATE TABLE Property_Types ( Property_Type_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Type varchar(50), PRIMARY KEY (Property_Type_ID) ); # # Table structure for table 'Tax_Bands' # CREATE TABLE Tax_Bands ( Tax_Band_ID int(11) DEFAULT '' NOT NULL auto_increment, Tax_Band varchar(50), PRIMARY KEY (Tax_Band_ID) ); # --- Dump ends --- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple INNER JOINS
Hi Peter, I am using version 3.23.54, unfortunately I have no control over this :( Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Shaun, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. That query generates no error in 5.0.13. There have been several cascading join bugs, some fixed, some not. What version are you running? PB - Shaun wrote: Hi, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. SELECT P.*, Tax_Band, Property_Type FROM Properties P INNER JOIN Tax_Bands USING(Tax_Band_ID) INNER JOIN Property_Types USING(Property_Type_ID) WHERE P.Property_ID = 3 Here is the error message I am getting: Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause' Thanks for your advice. # -- MySQL dump -- # # Table structure for table 'Properties' # CREATE TABLE Properties ( Property_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Name_Or_Number varchar(50), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(12), Sale_Price int(11), Asking_Price int(11), Years_On_Lease int(11), Tax_Band_ID int(11), Property_Type_ID int(11), Number_Of_Bedrooms int(11), Number_Of_Bathrooms int(11), Number_Of_Receptions int(11), Internal_Square_Footage int(11), Internal_Square_Meters int(11), Price_Per_Square_Foot decimal(6,2), Price_Per_Square_Meter decimal(6,2), Additional_Notes text, PRIMARY KEY (Property_ID) ); # # Table structure for table 'Property_Types' # CREATE TABLE Property_Types ( Property_Type_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Type varchar(50), PRIMARY KEY (Property_Type_ID) ); # # Table structure for table 'Tax_Bands' # CREATE TABLE Tax_Bands ( Tax_Band_ID int(11) DEFAULT '' NOT NULL auto_increment, Tax_Band varchar(50), PRIMARY KEY (Tax_Band_ID) ); # --- Dump ends --- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple INNER JOINS
It looks like MySQL is interpreting your short form join syntax a different way than you expect. Try using the long form by specifying which tables and fields you want to join. SELECT ... FROM Properties P INNER JOIN Tax_Bands ON P.Tax_Band_ID=Tax_Bands.Tax_Band_ID INNER JOIN Property_Types ON P.Property_Type_ID=Property_Types.Property_Type_ID ... On Oct 19, 2005, at 6:49 AM, Shaun wrote: Hi, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. SELECT P.*, Tax_Band, Property_Type FROM Properties P INNER JOIN Tax_Bands USING(Tax_Band_ID) INNER JOIN Property_Types USING(Property_Type_ID) WHERE P.Property_ID = 3 Here is the error message I am getting: Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause' Thanks for your advice. # -- MySQL dump -- # # Table structure for table 'Properties' # CREATE TABLE Properties ( Property_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Name_Or_Number varchar(50), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(12), Sale_Price int(11), Asking_Price int(11), Years_On_Lease int(11), Tax_Band_ID int(11), Property_Type_ID int(11), Number_Of_Bedrooms int(11), Number_Of_Bathrooms int(11), Number_Of_Receptions int(11), Internal_Square_Footage int(11), Internal_Square_Meters int(11), Price_Per_Square_Foot decimal(6,2), Price_Per_Square_Meter decimal(6,2), Additional_Notes text, PRIMARY KEY (Property_ID) ); # # Table structure for table 'Property_Types' # CREATE TABLE Property_Types ( Property_Type_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Type varchar(50), PRIMARY KEY (Property_Type_ID) ); # # Table structure for table 'Tax_Bands' # CREATE TABLE Tax_Bands ( Tax_Band_ID int(11) DEFAULT '' NOT NULL auto_increment, Tax_Band varchar(50), PRIMARY KEY (Tax_Band_ID) ); # --- Dump ends --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Checking Multiplicity Constraints and Retrieving Details from Error Messages
As part of my 4th Year Group Design Project, I am required to build a database system that will validate and then store the data. As such I am currently investigating different DB, to choose the most suitable one. I like many features of MySQL but there are a couple of things I am not very clear about that I would need for the project. First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. Second, is there any way of getting more details out of an error message? So for example, when doing a bulk upload to the database, rather than just getting Cannot add or update a child row: a foreign key constraint fails I would like to know which particular insert statement (out of the 1000 I have) caused the problem, or which field in this statement broke the constraint. Any help would be much appreciated. Ledina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
As part of my 4th Year Group Design Project, I am required to build a database system that will validate and then store the data. As such I am currently investigating different DB, to choose the most suitable one. I like many features of MySQL but there are a couple of things I am not very clear about that I would need for the project. First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. The way to do this would be via CHECK constraints, but MySQL doesn't support them. Second, is there any way of getting more details out of an error message? So for example, when doing a bulk upload to the database, rather than just getting Cannot add or update a child row: a foreign key constraint fails I would like to know which particular insert statement (out of the 1000 I have) caused the problem, or which field in this statement broke the constraint. Check for innodb status in the documentation. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple INNER JOINS
Hi Shaun, I don't have a 3.23 installation to test it on just now. If Brent's suggestion fails, try changing the join pattern to tb INNER JOIN p INNER JOIN pt (if you can't beat it, join it :-) ). PB - Shaun wrote: Hi Peter, I am using version 3.23.54, unfortunately I have no control over this :( "Peter Brawley" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Shaun, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. That query generates no error in 5.0.13. There have been several cascading join bugs, some fixed, some not. What version are you running? PB - Shaun wrote: Hi, I am having problems with the following query: I am trying to join Tax_Bands and Property_Types to Properties but the query seems to be joning Tax_Bands to Properties. SELECT P.*, Tax_Band, Property_Type FROM Properties P INNER JOIN Tax_Bands USING(Tax_Band_ID) INNER JOIN Property_Types USING(Property_Type_ID) WHERE P.Property_ID = 3 Here is the error message I am getting: Unknown column 'Tax_Bands.Property_Type_ID' in 'on clause' Thanks for your advice. # -- MySQL dump -- # # Table structure for table 'Properties' # CREATE TABLE Properties ( Property_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Name_Or_Number varchar(50), Address_Line_1 varchar(50), Address_Line_2 varchar(50), City varchar(50), County varchar(50), Postcode varchar(12), Sale_Price int(11), Asking_Price int(11), Years_On_Lease int(11), Tax_Band_ID int(11), Property_Type_ID int(11), Number_Of_Bedrooms int(11), Number_Of_Bathrooms int(11), Number_Of_Receptions int(11), Internal_Square_Footage int(11), Internal_Square_Meters int(11), Price_Per_Square_Foot decimal(6,2), Price_Per_Square_Meter decimal(6,2), Additional_Notes text, PRIMARY KEY (Property_ID) ); # # Table structure for table 'Property_Types' # CREATE TABLE Property_Types ( Property_Type_ID int(11) DEFAULT '' NOT NULL auto_increment, Property_Type varchar(50), PRIMARY KEY (Property_Type_ID) ); # # Table structure for table 'Tax_Bands' # CREATE TABLE Tax_Bands ( Tax_Band_ID int(11) DEFAULT '' NOT NULL auto_increment, Tax_Band varchar(50), PRIMARY KEY (Tax_Band_ID) ); # --- Dump ends --- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.3/141 - Release Date: 10/18/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.4/143 - Release Date: 10/19/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Input on Materialized Views
Thanks for the clarification and comments. I should reiterate that for our project, we do *not* need to actually implement materialized views, but only suggest refactoring and/or new components required to implement them. I think implementing it would too time consuming for a group of three students in one term, but we can definitely post our findings here. We may also have some interesting MySQL source architecture visualizations to show. To make things a bit simpler, we've decided not to consider the query optimizer or scheduling updates. Instead, we'll focus on the storage engine and adding the keyword. My understanding of materialized views for the purposes of our assignment is that it's a view that caches the data when queried. If the data changes, the cache will have to be recomputed (or at least invalidated). -Andrew On 10/19/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Just like Jigal, I also had to lookup the term materialized view. For the sake of others on the list trying to follow along: a materialized view is basically a self-updating snapshot of a table (or tables) usually containing some sort of intermediate statistical computations involving GROUP BY. For instance, if you have the raw log of visitors to your web site stored in your database and you frequently ran queries that created summarations by day, you could save yourself a lot of processing time by periodically precomputing a daily table from your raw logs showing various statistics broken down for each date. Well, a materialized view would be one way to implement that daily table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Input on Materialized Views
How you redefined your project definitely makes your goal much easier to reach. As I see it, most of what you need to do is related to figuring out how to create disk-cached VIEWs and how to invalidate the cache whenever any of the VIEW's underlying data has changed. This does not necessarily mean that you need to invalidate the cache for ANY change on a base table (though, that would also be a valid approach and much simpler to do) but rather only if one of the rows cached by the VIEW had changed. You will probably be able to extend the existing VIEW management code in the 5.0 branch to achieve your project goal. So... which of the many storage engines are you hoping to extend? It would be nice if you could do them all but I think we all anticipate that InnoDB will pose special challenges. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Andrew Roth [EMAIL PROTECTED] wrote on 10/19/2005 11:06:33 AM: Thanks for the clarification and comments. I should reiterate that for our project, we do *not* need to actually implement materialized views, but only suggest refactoring and/or new components required to implement them. I think implementing it would too time consuming for a group of three students in one term, but we can definitely post our findings here. We may also have some interesting MySQL source architecture visualizations to show. To make things a bit simpler, we've decided not to consider the query optimizer or scheduling updates. Instead, we'll focus on the storage engine and adding the keyword. My understanding of materialized views for the purposes of our assignment is that it's a view that caches the data when queried. If the data changes, the cache will have to be recomputed (or at least invalidated). -Andrew On 10/19/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Just like Jigal, I also had to lookup the term materialized view. For the sake of others on the list trying to follow along: a materialized view is basically a self-updating snapshot of a table (or tables) usually containing some sort of intermediate statistical computations involving GROUP BY. For instance, if you have the raw log of visitors to your web sitestored in your database and you frequently ran queries that created summarations by day, you could save yourself a lot of processing time by periodically precomputing a daily table from your raw logs showing various statistics broken down for each date. Well, a materialized view would be one way to implement that daily table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' -- again
Hi list, I've got a query coming out of sugarCRM that is generating this error: MySQL error 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'UNION' I recently converted the entire database to utf8 - made sure all the connections are utf8 etc -- made php use utf8 - set the doctype on the page to utf8 -- when I run the same query in the mysql monitor it runs fine - when apache/php run it it fails to deal with the collation. the data was converted via mysqldump to text file and reimporting changing all tables/database to utf. fwiw: the query looks like this: ( SELECT meetings.id , meetings.name , meetings.status , ' ' contact_name , ' ' contact_id , meetings.date_start , meetings.parent_id , meetings.parent_type , meetings.time_start , 'meetings' panel_name FROM meetings where ( meetings.parent_id= '63301596-6175-1b89-75df-431283170495' AND meetings.parent_type='Opportunities' AND meetings.deleted=0 AND (meetings.status='Planned')) AND meetings.deleted=0 ) UNION ALL ( SELECT tasks.id , tasks.name , tasks.status , CONCAT(CONCAT(jt0.first_name , ' '), CONCAT(jt0.last_name , ' ')) contact_name, tasks.contact_id , tasks.date_due , tasks.parent_id , tasks.parent_type , tasks.time_due , 'tasks' panel_name FROM tasks LEFT JOIN contacts jt0 ON jt0.id= tasks.contact_id AND jt0.deleted=0 where ( tasks.parent_id= '63301596-6175-1b89-75df-431283170495' AND tasks.parent_type='Opportunities' AND tasks.deleted=0 AND (tasks.status='Not Started' OR tasks.status='In Progress' OR tasks.status='Pending Input')) AND tasks.deleted=0 ) UNION ALL ( SELECT calls.id , calls.name , calls.status , ' ' contact_name , ' ' contact_id , calls.date_start , calls.parent_id , calls.parent_type , calls.time_start , 'calls' panel_name FROM calls where ( calls.parent_id= '63301596-6175-1b89-75df-431283170495' AND calls.parent_type='Opportunities' AND calls.deleted=0 AND (calls.status='Planned')) AND calls.deleted=0 ) and in this case it doesn't return anything - which is correct given the data. we are using: mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i386) Server characterset:utf8 Db characterset:utf8 Client characterset:utf8 Conn. characterset:utf8 I've seen some stuff that versions before 4.1.11 suffered from collation issues - is this likely to my case or can anyone see some other path through this - we have a single production database that we are very reluctant to update at this time. should i just switch back to latin1 ? thanks mysql list - you guys rock, lost in translation Jeff
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 19 Oct 2005, at 15:58, Martijn Tonies wrote: Second, is there any way of getting more details out of an error message? So for example, when doing a bulk upload to the database, rather than just getting Cannot add or update a child row: a foreign key constraint fails I would like to know which particular insert statement (out of the 1000 I have) caused the problem, or which field in this statement broke the constraint. Check for innodb status in the documentation. Is the innodb status the only way. I am looking for performance too and I'm worried that parsing the whole log for the relevant error, or even using regular expressions on it might be a bit slow. Thanks again for your help, Ledina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
Thanks guys for really detailed answers.
After your emails I talked to project supervisor and found that there is
some changes in the project:
(i) do you know in advance all the kinds of price extensions that can
come up?
- I hope I know them now :(
(ii) do you want the price rules to be (a) in the database or (b) in the
app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no
rule in making prices for different number of items in pack. next price
is NOT for x% lower or for $x lower. There is no rule. If you were
thinking on something else please explain. Thanks.
(iv) does the app need to track price history (e.g. so it can recreate a
price computation from six months ago)?
- This would be actually more online catalog where visitor/customer will
create an inquiry. And we don't need to track a purchase history in this
case.
But, Peter's 2nd part is actually touching the change in the project:
product can have more then 2 prices. E.g. if you select shirt with your
logo embroidered - it's one price. If your logo will be screened on the
shirt - other price. And then if the shirt is on sale - 2 more prices
Total 4 different prices have to be shown on catalog.
The solution:
CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
name CHAR(20)
)
CREATE TABLE extended_prices (
epid INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
pricemodtype_id INT NOT NULL,
qty_up_to SMALLINT NOT NULL,
begindate DATE NOT NULL,
enddate DATE NOT NULL,
price_per DECIMAL(10,2) NOT NULL,
price_per_mod DECIMAL(10,2) NULL
);
will be fine?
Actually, there is what I have for the moment for my DB:
categories and subcategories:
CREATE TABLE ac_categories (
cat_id INT(6) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(45) NULL,
cat_description TEXT NULL,
cat_parent INTEGER(4) UNSIGNED NULL,
cat_status ENUM('0','1') NULL DEFAULT 0,
PRIMARY KEY(cat_id),
INDEX ac_categories_index1(cat_status)
);
CREATE TABLE ac_products (
product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL, // since there is hundreds of different
colors and color combination, we will have colors as description
product_includes TEXT NULL, // shows what is includes in price (e.g
how many colors for logo and how much costs additional color)
product_catalog VARCHAR(45) NULL, // products are in groups of
catalogs - for internal use
product_status ENUM('0','1') NULL, // is product available (visible at
front end)
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
);
since, one product can be in more than one category:
CREATE TABLE ac_products_has_ac_categories (
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
ac_categories_cat_id INT(6) NOT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX ac_products_has_ac_categories_FKIndex1(ac_products_product_id),
INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id)
);
CREATE TABLE ac_extended_prices (
epid INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
ac_pricemodtypes_pricemodtype_id INT(8) NOT NULL,
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
product_id INTEGER(8) UNSIGNED NULL,
pricemodtype_id INTEGER(8) UNSIGNED NULL,
qty_up_to INTEGER(8) UNSIGNED NULL,
begindate DATE NULL,
enddate DATE NULL,
price_per DECIMAL(10,2) NOT NULL,
price_per_mod DECIMAL(10,2) NULL,
PRIMARY KEY(epid),
INDEX ac_extended_prices_index_date(begindate, enddate),
INDEX ac_extended_prices_index_qty(qty_up_to),
INDEX ac_extended_prices_FKIndex1(ac_products_product_id),
INDEX ac_extended_prices_FKIndex2(ac_pricemodtypes_pricemodtype_id)
);
CREATE TABLE ac_pricemodtypes (
pricemodtype_id INT(8) NOT NULL AUTO_INCREMENT,
name CHAR(40) NULL,
PRIMARY KEY(pricemodtype_id)
);
Your opinion?
Thanks for help.
-afan
Peter Brawley wrote:
afan,
For the same project (below) I have problem with building table for
product prices.
In regular online store, price is usually part of the products table.
But, I need a solution for multiple prices. E.g.
QTY -2550 100 200
Price - $1.59 $1.39 $1.19 $0.99
Also, if product is On Sale I need to be shown both prices: regular
and sale price
QTY -2550 100 200
Price -$1.59 $1.39 $1.19 $0.99
Sale - $0.99 $0.99 $0.99 $0.99
First two footnotes to the excellent advice offered by Rhino Shawn
on your categories, products products_categories tables:
1. It will be best to type the primary foreign keys identically--all
unsigned, or all not.
2. To avoid rounding errors, use
ARCHIVE storage engine and INSERT DELAY in MySQL 4.1
Apparently ARCHIVE tables do not support INSERT DELAYED. Why?
In documentation
(http://dev.mysql.com/doc/refman/4.1/en/archive-storage-engine.html) it
says that it should be possible.
Example of the error that I am getting:
INSERT DELAYED INTO audit_log VALUES
('db','user','549220','address_id','757812','5214');
ERROR 1031 (HY000): Table storage engine for 'audit_log' doesn't have
this option
Mihail
--
Mihail Manolov
Government Liquidation, LLC
Special Projects Leader
202 558 6227
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 10/19/05, Martijn Tonies [EMAIL PROTECTED] wrote: First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. In MySQL triggers are the only way. In SQL you might be able to use an assertion depending on your exact needs. (Don't know if there is any database that actually implements them per the standard.) The way to do this would be via CHECK constraints, but MySQL doesn't support them. CHECK constraints won't work. If I have a parent table and need to maintain a multiplicity of 1 to 3 children in the child table, how is a CHECK going to stop somebody from deleting all rows in the child table? Jochem
implicit cast forces table scan?
Hi, I am currently using mysql 4.0.18 as distributed with red hat Linux. I find when I perform a select on a varchar(30) field, the index is used only if I have quoted the value in the where clause. Otherwise, mysql performs a table scan. The queries in question are: This query uses the index: mysql explain - select itran_log_date, itran_log_actionid from itran_log where - itran_log_actionid = 170807; This query performs a table scan: mysql explain - select itran_log_date, itran_log_actionid from itran_log where - itran_log_actionid = 170807; My question is this: is the issue here that mysql is converting every single itran_log_actionid value, from all 1.5 million rows, and hence the index is not useful and not used? My initial assumption was that the constant value 170807 in the second query, would be converted to text before the query was executed, and so the index could be used. This does not seem to be the case. I ask both for my own edification, and also because it seems to me this should be mentioned in the manual for newbies like myself. thanks, Olaf Details on versions, table structures, indexes, etc. below == == $ rpm -qa | grep -i mysql MySQL-shared-compat-4.0.15-0 MySQL-client-4.0.18-0 php-mysql-4.1.2-7.2.6 MySQL-server-4.0.18-0 $ /usr/bin/mysql -V /usr/bin/mysql Ver 12.22 Distrib 4.0.18, for pc-linux (i686) == mysql describe itran_log; ++--+--+-++- ---+ | Field | Type | Null | Key | Default| Extra | ++--+--+-++- ---+ | itran_user_id | varchar(100) | | || | | itran_log_date | date | | MUL | -00-00 | | | itran_log_time | time | | | 00:00:00 | | | itran_log_filename | varchar(100) | | || | | itran_log_action | varchar(25) | | MUL || | | itran_log_actionid | varchar(30) | | MUL || | | itran_site_id | varchar(100) | YES | MUL | NULL | | | itran_log_instructions | text | | || | | itran_log_id | bigint(20) | | PRI | NULL | auto_increment | ++--+--+-++- ---+ mysql show indexes from itran_log; +---++---+--+--- -+---+-+--++--++ -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name| Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+--- -+---+-+--++--++ -+ | itran_log | 0 | PRIMARY |1 | itran_log_id | A | 1500793 | NULL | NULL | | BTREE | | | itran_log | 1 | itran_site_id_ix |1 | itran_site_id | A |NULL | 15 | NULL | YES | BTREE | | | itran_log | 1 | itran_log_action_ix |1 | itran_log_action | A |NULL |3 | NULL | | BTREE | | | itran_log | 1 | itran_log_actionid_ix |1 | itran_log_actionid | A |NULL | NULL | NULL | | BTREE | | | itran_log | 1 | itran_log_date_ix |1 | itran_log_date | A |NULL | NULL | NULL | | BTREE | | +---++---+--+--- -+---+-+--++--++ -+ mysql explain - select itran_log_date, itran_log_actionid from itran_log where - itran_log_actionid = 170807; +---+--+---+--+-+--+ -+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+ -+-+ | itran_log | ALL | itran_log_actionid_ix | NULL |NULL | NULL | 1500775 | Using where | +---+--+---+--+-+--+ -+-+ 1 row in set (0.02 sec) mysql explain - select itran_log_date, itran_log_actionid from itran_log where - itran_log_actionid = 170807; +---+--+---+---+ -+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+ -+---+--+-+ |
Re: one product in more categories
No. It doesn't work. First, I found one error: there are two columns for
same thing in ac_products ac_products_product_id and product_id.
Second, ac_extended_prices table doesn't fit with multiple solutions
:(
[EMAIL PROTECTED] wrote:
Thanks guys for really detailed answers.
After your emails I talked to project supervisor and found that there
is some changes in the project:
(i) do you know in advance all the kinds of price extensions that can
come up?
- I hope I know them now :(
(ii) do you want the price rules to be (a) in the database or (b) in
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no
rule in making prices for different number of items in pack. next
price is NOT for x% lower or for $x lower. There is no rule. If you
were thinking on something else please explain. Thanks.
(iv) does the app need to track price history (e.g. so it can recreate
a price computation from six months ago)?
- This would be actually more online catalog where visitor/customer
will create an inquiry. And we don't need to track a purchase history
in this case.
But, Peter's 2nd part is actually touching the change in the
project: product can have more then 2 prices. E.g. if you select shirt
with your logo embroidered - it's one price. If your logo will be
screened on the shirt - other price. And then if the shirt is on sale
- 2 more prices Total 4 different prices have to be shown on catalog.
The solution:
CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
name CHAR(20)
)
CREATE TABLE extended_prices (
epid INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
pricemodtype_id INT NOT NULL,
qty_up_to SMALLINT NOT NULL,
begindate DATE NOT NULL,
enddate DATE NOT NULL,
price_per DECIMAL(10,2) NOT NULL,
price_per_mod DECIMAL(10,2) NULL
);
will be fine?
Actually, there is what I have for the moment for my DB:
categories and subcategories:
CREATE TABLE ac_categories (
cat_id INT(6) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(45) NULL,
cat_description TEXT NULL,
cat_parent INTEGER(4) UNSIGNED NULL,
cat_status ENUM('0','1') NULL DEFAULT 0,
PRIMARY KEY(cat_id),
INDEX ac_categories_index1(cat_status)
);
CREATE TABLE ac_products (
product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL, // since there is hundreds of different
colors and color combination, we will have colors as description
product_includes TEXT NULL, // shows what is includes in price (e.g
how many colors for logo and how much costs additional color)
product_catalog VARCHAR(45) NULL, // products are in groups of
catalogs - for internal use
product_status ENUM('0','1') NULL, // is product available (visible
at front end)
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
PRIMARY KEY(product_id),
INDEX ac_products_index1(product_start_date, product_exp_date),
INDEX ac_products_index2(product_status),
);
since, one product can be in more than one category:
CREATE TABLE ac_products_has_ac_categories (
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
ac_categories_cat_id INT(6) NOT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX ac_products_has_ac_categories_FKIndex1(ac_products_product_id),
INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id)
);
CREATE TABLE ac_extended_prices (
epid INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
ac_pricemodtypes_pricemodtype_id INT(8) NOT NULL,
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
product_id INTEGER(8) UNSIGNED NULL,
pricemodtype_id INTEGER(8) UNSIGNED NULL,
qty_up_to INTEGER(8) UNSIGNED NULL,
begindate DATE NULL,
enddate DATE NULL,
price_per DECIMAL(10,2) NOT NULL,
price_per_mod DECIMAL(10,2) NULL,
PRIMARY KEY(epid),
INDEX ac_extended_prices_index_date(begindate, enddate),
INDEX ac_extended_prices_index_qty(qty_up_to),
INDEX ac_extended_prices_FKIndex1(ac_products_product_id),
INDEX ac_extended_prices_FKIndex2(ac_pricemodtypes_pricemodtype_id)
);
CREATE TABLE ac_pricemodtypes (
pricemodtype_id INT(8) NOT NULL AUTO_INCREMENT,
name CHAR(40) NULL,
PRIMARY KEY(pricemodtype_id)
);
Your opinion?
Thanks for help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. In MySQL triggers are the only way. In SQL you might be able to use an assertion depending on your exact needs. (Don't know if there is any database that actually implements them per the standard.) The way to do this would be via CHECK constraints, but MySQL doesn't support them. CHECK constraints won't work. If I have a parent table and need to maintain a multiplicity of 1 to 3 children in the child table, how is a CHECK going to stop somebody from deleting all rows in the child table? That depends on your check constraint implementation. Firebird, for example, allows you to reference other tables in SQL statement in your CHECK constraints. So, you could do: exists( select count(*) as cnt from mychildtable where parentid = mytable.parentid and cnt between 1 and 3) (don't know if this syntax is exact, but you get the idea) With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Turning tables on their side
I suspect that this is the wrong list for this kind of question, but if someone could point me to appropriate sources, I would very much appreciate it. I am new to SQL but inherited project designed by someone who doesn't seem answer his email anymore. Essentially date were collected on the web using PHP inserting things into a MySQL data base. It took me time, but I now have a handle on what is in which of the 15 tables involved. Each response to each question by each respondent produced its own record (row). That is, I have something like respondent_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL Richmond and so on for other respondent_ids as well. What I would like to get for my users is something that looks like respondent_id q201 q202 q203 ... --- 23 text-for-ans56 text-for-ans20 text-for- answer1 ... 24 text-for-ans52 text-for-ans21 text-for- answer0 ... So instead of having a record for each response, I'd like to have a single record for each respondent that shows all of that respondents responses. For someone who knows SQL this should be easy. I suspect that a group by respondent_id clause will play a role, but I just don't see it. As I said, references to books or sites that I should learn from would also be welcome. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one product in more categories
afan,
snip But, Peter's 2nd part is actually touching the change in the
project:
product can have more then 2 prices. E.g. if you select shirt with your
logo embroidered - it's one price. If your logo will be screened on the
shirt - other price. And then if the shirt is on sale - 2 more prices
Total
4 different prices have to be shown on catalog.
Right, you have to treat price extension types as data, as suggested,
but already you have the previously unanticipated wrinkle that multiple
extensions can apply to one item, eg silkscreen the logo or embroider
it and/or it is on sale And you can be entirely sure that more
wrinkles are on the way.
That's where the question of rules comes in. Suppose when you arrive at
work tomorrow, you're told that the system has to learn how to price
some screened names by the letter and others by a single price for a
whole name. To avoid having to write a lot of awkward literal code for
all such wrinkles, you need a way of storing such rules in the
pricemodtypes table. A simple pricemodtype example might be name='logo',
method='screen', price_type='per letter', unit_price=whatever; another
might be 'screen+sale'; another might be name='logo', method='embroider,
on sale', price_type='whole', unit_price=whatever. And so on. You know
this detail, I don't. Here is one general approach: (i) collect all the
price mods the boss can tell you about, and turn them into the smallest
possible set of parameteristed formulas, (ii) create procemodtypes
columns for all the parameters you need, (iii) write generic code which
simply reads the rules and computes the parameterised prices, (iv) test
the result with the boss to ensure that you have all his rules right,
then (v) tell the boss that in the future, his rules have to fit into
those params or he has to pay for big app enhancements.
The alternative is to code every subtype literally in pricemodtypes.
Only you have heard all the specs, only you have talked with your boss,
so only you know what the details are going to be, and which if any can
be parameterised as above.
PB
-
[EMAIL PROTECTED] wrote:
Thanks guys for really detailed answers.
After your emails I talked to project supervisor and found that there
is some changes in the project:
(i) do you know in advance all the kinds of price extensions that can
come up?
- I hope I know them now :(
(ii) do you want the price rules to be (a) in the database or (b) in
the app?
(iii) if the answer to (ii) is (a), do you want the rules in stored
procedures, or in tables which application code must parse?
- Those two I really didn't get. If you thought on this: there is no
rule in making prices for different number of items in pack. next
price is NOT for x% lower or for $x lower. There is no rule. If you
were thinking on something else please explain. Thanks.
(iv) does the app need to track price history (e.g. so it can recreate
a price computation from six months ago)?
- This would be actually more online catalog where visitor/customer
will create an inquiry. And we don't need to track a purchase history
in this case.
But, Peter's 2nd part is actually touching the change in the
project: product can have more then 2 prices. E.g. if you select shirt
with your logo embroidered - it's one price. If your logo will be
screened on the shirt - other price. And then if the shirt is on sale
- 2 more prices Total 4 different prices have to be shown on catalog.
The solution:
CREATE TABLE pricemodtypes (
pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
name CHAR(20)
)
CREATE TABLE extended_prices (
epid INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
pricemodtype_id INT NOT NULL,
qty_up_to SMALLINT NOT NULL,
begindate DATE NOT NULL,
enddate DATE NOT NULL,
price_per DECIMAL(10,2) NOT NULL,
price_per_mod DECIMAL(10,2) NULL
);
will be fine?
Actually, there is what I have for the moment for my DB:
categories and subcategories:
CREATE TABLE ac_categories (
cat_id INT(6) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(45) NULL,
cat_description TEXT NULL,
cat_parent INTEGER(4) UNSIGNED NULL,
cat_status ENUM('0','1') NULL DEFAULT 0,
PRIMARY KEY(cat_id),
INDEX ac_categories_index1(cat_status)
);
CREATE TABLE ac_products (
product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
product_no VARCHAR(12) NULL,
product_name VARCHAR(45) NULL,
product_description TEXT NULL,
product_colors TEXT NULL, // since there is hundreds of different
colors and color combination, we will have colors as description
product_includes TEXT NULL, // shows what is includes in price (e.g
how many colors for logo and how much costs additional color)
product_catalog VARCHAR(45) NULL, // products are in groups of
catalogs - for internal use
product_status ENUM('0','1') NULL, // is product available (visible
at front end)
product_supplier VARCHAR(45) NULL,
product_start_date DATE NULL,
product_exp_date DATE NULL,
PRIMARY KEY(product_id),
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 10/19/05, Martijn Tonies [EMAIL PROTECTED] wrote: First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. The way to do this would be via CHECK constraints, but MySQL doesn't support them. CHECK constraints won't work. If I have a parent table and need to maintain a multiplicity of 1 to 3 children in the child table, how is a CHECK going to stop somebody from deleting all rows in the child table? That depends on your check constraint implementation. Firebird, for example, allows you to reference other tables in SQL statement in your CHECK constraints. So, you could do: exists( select count(*) as cnt from mychildtable where parentid = mytable.parentid and cnt between 1 and 3) That doesn't help: check constraints are evaluated only on insert and update, not on delete. That's why you need an assertion. Jochem
Re: one product in more categories
I don't think you have a clear enough mental picture of what your different pricing structures are. You describe: a) fundamental unit price b) price breaks due to volume discounts c) price breaks due to coupons d) price increases based on options. Options include: embroidered logo, screened logo, total colors =2, total colors = 3, etc e) price breaks due to a sale being in effect. I cannot imagine that you have the ability to define EVERY combination of base price + volume discount + coupon + options ahead of time. So long as you can tally them up when it becomes time to construct the unit price during the checkout phase, you should have the information you need. A sale price can either be a fixed deduction (across all volume levels) , a percentage deduction( across all volume levels), a fixed deduction for certain volumes, or a percentage deduction for certain volumes. Nothing says you can't have two sales going on for the same item at the same time (10% additional off on all sales 200 units, 20% off on all sales = 200 units) Coupons (special offers, one-time discounts, Sales Rep credits, etc.) can apply to one or more products, depending on how they are defined. Typically, a user will need to add the coupon to their cart during checkout in order for it to count towards the purchase. While considering whether to allow the coupon into the cart of not, your application will need to decide: a) is the coupon valid (in date, applies to 1+ products in this purchase) b) which products it is valid for c) how much discount and for how many product units should the discount apply. It's basically a bookkeeping thing but you need enough information in the coupon table to be able to make these decisions. What I am saying is start simple, like with your volume pricing table. Make sure you can put into and get out of it all of the information you will need to determine the correct unit price for the sales ticket. Then, start with your add-ons and product options (colors, logos, etc.) Some product-productvolume-addon-addonvolume prices will be nothing (if you buy 200 shirts, embroidered logos are free). You will need a table that can store all of the definitions for the price breaks. That doesn't mean that each kind of product-feature-volume-price break cannot apply to more than one product. In the example I listed, you may have a generic rule that all shirts (of which you could have 50 styles) may qualify for the free logo upgrade if bought in lots of 200 or more. What you would have is a many-to-many relationship between your option costs and your products. If I have confused you, I didn't mean to. I just want you to sit down, with pencil and paper (or use a whiteboard or any means you are comfortable with) and determine how many bits of information you would need if you didn't have a computer to help you out. If all you had was lists of things on paper, what lists of what things would you need (as a person) to completely fill out an order and bill it correctly to the user? By putting yourself in the role of your program an modelling out what information you must have to make certain decisions, you will make many insights as to what your database design needs to be. The longer you wait to do this analysis, the harder it is going to be to fix your design. What you have to remember is that sales people do nothing but think up new ways of screwing us up. You as the DBA have to allow yourself the freedom to define almost any combination of product+price+discount+premium so that no matter what they think of, we can make it fit in the database. It's the application's responsibility to assemble the correct price based on available information and if that information is not available (re: in the database) people will complain. An online catalog is one of the most complex data structures just because of the flexibility it needs to have. In response to request to review your table structures: extended_prices: a) I would include the lower-bound of a pricing level as well, that way you don't have to find the max-of-group just to know which pricing tier you are in. b) I would also allow for some way to define a default set of prices. How would you define a set of default prices with this table the way it is (small begindate and huge enddate values?) ac_categories: a) consider using pre-ordered tree traversal as another way to define your categories heirarchies (http://www.sitepoint.com/article/hierarchical-data-database/2) ac_products: a) colors: probably needs to be on a separate table and linked with the product. CREATE TABLE ac_products_colors ( ac_products_id INT NOT NULL, ac_colors_id INT NOT NULL, PRIMARY KEY (ac_product_id, ac_colors_id) ); This gets trickier if you allow only certain color combinations. b) The optimizer will not use an index if it thinks that more than 30% of the rows match the value(s) it is looking for.
Re: implicit cast forces table scan?
On Wednesday 19 October 2005 01:15 pm, Olaf Faaland wrote: The queries in question are: This query uses the index: mysql explain - select itran_log_date, itran_log_actionid from itran_log where - itran_log_actionid = 170807; This query performs a table scan: mysql explain - select itran_log_date, itran_log_actionid from itran_log where - itran_log_actionid = 170807; My question is this: is the issue here that mysql is converting every single itran_log_actionid value, from all 1.5 million rows, and hence the index is not useful and not used? My initial assumption was that the constant value 170807 in the second query, would be converted to text before the query was executed, and so the index could be used. This does not seem to be the case. I ask both for my own edification, and also because it seems to me this should be mentioned in the manual for newbies like myself. It doesnt know what value your giving it. If it thought to assume converting the data, you could have 17h120, and it would fail converting the data. Mysql, nor any DB for that matter, should not, and do not, assume anything. It just happens in the case your dealing with numeric data. If thats the case, you should have made the column numeric in type. (int whatever) Jeff Jeff pgpNnLJGNfYAz.pgp Description: PGP signature
Re: Turning tables on their side
The person you inherited from formatted the data correctly in my opinion. With the existing format, you can index all the data with a minimum number of indexes and quickly compile results. It can scale to any number of questions without having to modify the underlying data structure. It can also easily answer queries like, who missed one or more questions? What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. The model you are envisioning would also be difficult to query to determine missed questions. I would use the presentation layer (i.e. Perl, PHP, Python, Ruby, Java, etc) to pivot the data for display. That's where you also add things like coloring to highlight errors or interesting information. On Oct 19, 2005, at 3:24 PM, Jeffrey Goldberg wrote: I suspect that this is the wrong list for this kind of question, but if someone could point me to appropriate sources, I would very much appreciate it. I am new to SQL but inherited project designed by someone who doesn't seem answer his email anymore. Essentially date were collected on the web using PHP inserting things into a MySQL data base. It took me time, but I now have a handle on what is in which of the 15 tables involved. Each response to each question by each respondent produced its own record (row). That is, I have something like respondent_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL Richmond and so on for other respondent_ids as well. What I would like to get for my users is something that looks like respondent_id q201 q202 q203 ... -- - 23 text-for-ans56 text-for-ans20 text-for- answer1 ... 24 text-for-ans52 text-for-ans21 text-for- answer0 ... So instead of having a record for each response, I'd like to have a single record for each respondent that shows all of that respondents responses. For someone who knows SQL this should be easy. I suspect that a group by respondent_id clause will play a role, but I just don't see it. As I said, references to books or sites that I should learn from would also be welcome. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
[mailed and posted] On Oct 19, 2005, at 3:34 PM, Brent Baisley wrote: The person you inherited from formatted the data correctly in my opinion. I agree. What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. I'm sorry that I didn't make the question clear. My goal is to export an MS-Excel file that looks like my target. I do not wish to change how things are done in the DB. The end-users will want a spreadsheet like that for doing their analysis. Not for queries. I'm using phpmyadmin which will do an Excel export of a table for me. I just need to create the temporary table long enough to do the export. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ALTER TABLE - how to fix truncated data?
Hello. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. MySQL 5.0 has this ability. Check STRICT_ALL_TABLES and STRICT_TRANS_TABLES SQL modes at: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html CREATE TEMPORARY TABLE t1 ( i TINYINT ); INSERT INTO t1 VALUES (42); SELECT * FROM t1; -- Ok, shows 42 UPDATE t1 SET i = 4242; -- SHOW WARNINGS; SELECT * FROM t1; -- Oops, shows 127 Yeah, total rubbish. Do this in your application - by accident - and explain your boss that the totals are wrong or that he had an input value of 4242 and got 127 in return. Rubbish. If a value doesn't fit (in the domain of tinyint), an exception should be raised. Plain and simple. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: restore of mysqldump-ed data is corrupted
Hello. what do you really mean? I cannot have different symbols (hebrew,thai, cyrillic) in same table!? I wouldn't say so. This project started when We have a lots of changes in MySQL 4.1. These are include the different character set support. In MySQL 4.1 during the work data might have a lot of transformations. See: http://dev.mysql.com/doc/refman/5.0/en/charset.html http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html The preferred way in my opinion is to use UTF8 if you have mix of different languages. Are you able to get a correct data in some way (through PHP for example)? Or every method you have tried produces a wrong data? mysqldump outputs the data in UTF8 by default, you can change the output encoding using --default-character-set option. See also about SET NAMES clause. gioklio wrote: Let's go step by step: - database server is the same MySQL 4.1.5-gamma - 'All charset variables are set to default 'latin1', except 'character_set_system', which is utf8' is the same - 'data it gets (from php) were in utf8' is the same - 'Mysqldump is used without any options' is the same - 'When importing back into the same database some characters (non latin,cyrillic in fact) get broken' this not the 'same': I get many other characters broken not only cyrillic. Users stores data in many encodings: hebrew, thai, cyrillic, etc... When you say: Delyan was using cyrillic symbols with latin1 encoding, which is not designed for this purposes. what do you really mean? I cannot have different symbols (hebrew, thai, cyrillic) in same table!? I wouldn't say so. This project started when MySQL didn't have support for UTF-8 encoding and because of that all tables are by default latin1. 4.1.5-gamma-log is a very old release So what? The server is the same one on which backup was made. Check if the problem remains if you use mysqldump which is shipped with 4.1.14 distribution Unfortunately I cannot do this. What do you suggest me as solution, how I can get my data back? I cannot believe that is not possible to restore data the same way it was stored. The fact is that utf8 data was stored in latin1 tables and I want that data back in same way. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ARCHIVE storage engine and INSERT DELAY in MySQL 4.1
Jeff Smelser wrote:
On Wednesday 19 October 2005 01:18 pm, Mihail Manolov wrote:
Apparently ARCHIVE tables do not support INSERT DELAYED. Why?
In documentation
(http://dev.mysql.com/doc/refman/4.1/en/archive-storage-engine.html) it
says that it should be possible.
Example of the error that I am getting:
INSERT DELAYED INTO audit_log VALUES
('db','user','549220','address_id','757812','5214');
ERROR 1031 (HY000): Table storage engine for 'audit_log' doesn't have
this option
do show create table audit_log, and post, plz.
Jeff
There you go:
CREATE TABLE `audit_log` (
`db_host` varchar(64) NOT NULL default '',
`table_name` varchar(255) NOT NULL default '',
`record_id` int(11) NOT NULL default '0',
`field_name` varchar(255) NOT NULL default '',
`field_value` varchar(255) NOT NULL default '',
`user` varchar(255) NOT NULL default ''
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
Mihail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
I agree with Brent. One particular bit of SQL you may find helpful is this: concat(ifnull(a_id,),ifnull(a_text,)) concat with anything and a null value will produce a null value. That snippet of sql code will help you get one answer from the 2 the original database had. Unless there's ever an answer_id AND an answer_text, although the example doesn't support that. so what you want is for something like php to take the result of: select respondent_id,question_id,concat(ifnull(answer_id,),ifnull(answer_text,)) as answer from test order by respondent_id,question_id; (which, in your example, gets you:) +--+--+---+ | r_id | q_id | answer| +--+--+---+ | 23 | 201 | 56| | 23 | 202 | 20| | 23 | 203 | 1 | | 23 | 204 | Arlington | | 24 | 201 | 52| | 24 | 202 | 21| | 24 | 203 | 0 | | 24 | 204 | Richmond | +--+--+---+ and process each row -- compare the respondent_id to a variable to see if you're still on the same respondent, and use the question_id to put the answer (id or text) into a hash or array. -Sheeri On 10/19/05, Brent Baisley [EMAIL PROTECTED] wrote: The person you inherited from formatted the data correctly in my opinion. With the existing format, you can index all the data with a minimum number of indexes and quickly compile results. It can scale to any number of questions without having to modify the underlying data structure. It can also easily answer queries like, who missed one or more questions? What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. The model you are envisioning would also be difficult to query to determine missed questions. I would use the presentation layer (i.e. Perl, PHP, Python, Ruby, Java, etc) to pivot the data for display. That's where you also add things like coloring to highlight errors or interesting information. On Oct 19, 2005, at 3:24 PM, Jeffrey Goldberg wrote: I suspect that this is the wrong list for this kind of question, but if someone could point me to appropriate sources, I would very much appreciate it. I am new to SQL but inherited project designed by someone who doesn't seem answer his email anymore. Essentially date were collected on the web using PHP inserting things into a MySQL data base. It took me time, but I now have a handle on what is in which of the 15 tables involved. Each response to each question by each respondent produced its own record (row). That is, I have something like respondent_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL Richmond and so on for other respondent_ids as well. What I would like to get for my users is something that looks like respondent_id q201 q202 q203 ... -- - 23 text-for-ans56 text-for-ans20 text-for- answer1 ... 24 text-for-ans52 text-for-ans21 text-for- answer0 ... So instead of having a record for each response, I'd like to have a single record for each respondent that shows all of that respondents responses. For someone who knows SQL this should be easy. I suspect that a group by respondent_id clause will play a role, but I just don't see it. As I said, references to books or sites that I should learn from would also be welcome. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning tables on their side
You want a Pivot Table. Excel will do this nicely (assuming you have 65536 rows or less), but SQL does not provide a mechanism to do this. If you want a web based interface you can look at Jtable. (I *think* that's what it's called -- it's a Java web app that provides an HTML pivot table interface...) -JF -Original Message- From: Jeffrey Goldberg [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 19, 2005 12:24 PM To: mysql@lists.mysql.com Subject: Turning tables on their side I suspect that this is the wrong list for this kind of question, but if someone could point me to appropriate sources, I would very much appreciate it. I am new to SQL but inherited project designed by someone who doesn't seem answer his email anymore. Essentially date were collected on the web using PHP inserting things into a MySQL data base. It took me time, but I now have a handle on what is in which of the 15 tables involved. Each response to each question by each respondent produced its own record (row). That is, I have something like respondent_idquestion_id answer_id answer_text 23 201 56 NULL 23 202 20 NULL 23 203 1 NULL 23 204NULL Arlington 24 201 52 NULL 24 202 21 NULL 24 203 0 NULL 24 204NULL Richmond and so on for other respondent_ids as well. What I would like to get for my users is something that looks like respondent_id q201 q202 q203 ... -- - 23 text-for-ans56 text-for-ans20 text-for- answer1 ... 24 text-for-ans52 text-for-ans21 text-for- answer0 ... So instead of having a record for each response, I'd like to have a single record for each respondent that shows all of that respondents responses. For someone who knows SQL this should be easy. I suspect that a group by respondent_id clause will play a role, but I just don't see it. As I said, references to books or sites that I should learn from would also be welcome. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Turning tables on their side
Create an Excel spreadsheet. Import the raw data, structured as-is, into a worksheet. Select all the relevant columns. Go to Data - Pivot Table and Pivot Chart Report. Click Finish. From the PivotTable Field List, drag the respondant ID into the box labeled Drop Row Fields Here, then drag question ID into the box labeled Drop Column Fields Here. Voila. -JF -Original Message- From: Jeffrey Goldberg [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 19, 2005 1:44 PM To: Brent Baisley Cc: mysql@lists.mysql.com Subject: Re: Turning tables on their side [mailed and posted] On Oct 19, 2005, at 3:34 PM, Brent Baisley wrote: The person you inherited from formatted the data correctly in my opinion. I agree. What you are trying to do is store the data as you see it, which is rarely a normalized data model. Your presentation layer should handle the formatting for the user. I'm sorry that I didn't make the question clear. My goal is to export an MS-Excel file that looks like my target. I do not wish to change how things are done in the DB. The end-users will want a spreadsheet like that for doing their analysis. Not for queries. I'm using phpmyadmin which will do an Excel export of a table for me. I just need to create the temporary table long enough to do the export. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking Multiplicity Constraints and Retrieving Details from Error Messages
On 19 Oct 2005, at 20:30, Jochem van Dieten wrote: On 10/19/05, Martijn Tonies [EMAIL PROTECTED] wrote: First of all, is there any way of limiting the number of rows in a table, referencing to the same element of another table? For example, force a manager not to have more than 10 employees under his control. In a way this can be seen as checking the multiplicity of the relation between the two tables. I know one way would be using triggers, but I was wondering if there was a way of specifying this when the table is constructed. The way to do this would be via CHECK constraints, but MySQL doesn't support them. CHECK constraints won't work. If I have a parent table and need to maintain a multiplicity of 1 to 3 children in the child table, how is a CHECK going to stop somebody from deleting all rows in the child table? That depends on your check constraint implementation. Firebird, for example, allows you to reference other tables in SQL statement in your CHECK constraints. So, you could do: exists( select count(*) as cnt from mychildtable where parentid = mytable.parentid and cnt between 1 and 3) I am not sure you could use that. To start with I don't think CHECK supports subqueries, but even if it did I would need to specify that the items in the table referencing the same parent as the one to be inserted are between 1 and 3. I am not sure you can specify that with a query similar to the above. parentid = mytable.parentid I don't think would work as you are specifying another column as supposed to a value. I really cannot think of a way to specify that constraint using check statement. Or am I being really stupid and missing some crucial point here? Many thanks, Ledina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ARCHIVE storage engine and INSERT DELAY in MySQL 4.1
Jeff Smelser wrote: I would highly suspect this is a bug.. I would submit one.. unless someone else knows better.. Not real sure why you really need delayed, archive is suppose to be much faster on inserts then even myisam. Jeff Thanks Jeff! It's the way our code is written, and I just changed the table type and began getting this error. We have lots of data to insert into this table, therefore the optimal option is to use DELAYED and insert them in blocks. Not sure why they say that ARCHIVE storage engine is a new feature in 5.0? Mihail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select query taking too long
hi All, I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? The 2 tables are RPTACCESS and RECORD_OF_ACCESS. I need to pull out rows based on these conditions: 1. The ROA and RPTACCESS tables should have the same value for the columns LOGINID, APPLICATIONNAME, EID. 2. The SYSTEM column in the ROA table should have the value Remove All 3. The ACCOUNTSTATUS column in the ROA table should have the value D The query works fine - I mean functionally it is correct - when I have 20-30 rows it returns within 5 seconds. But it takes very long on our QA environment which has around 100,000+ records for both tables. Here is the query: (I originally had select count(*) and later changed it to select count(eid) hoping it would make it faster...but in vain) select count(eid) from RPTACCESS where upper(applicationname) in ( select upper(r.applicationname) from rptaccess r, record_of_access roa where roa.system = 'Remove All' and roa.accountstatus = 'D' and r.eid = roa.eid and r.loginid = roa.loginid and upper(r.applicationname) = upper(roa.applicationname) ) and upper(eid) in ( select upper(r.eid) from rptaccess r, record_of_access roa where roa.system = 'Remove All' and roa.accountstatus = 'D' and r.eid = roa.eid and r.loginid = roa.loginid and upper(r.applicationname) = upper(roa.applicationname) ) and upper(loginid) in ( select upper(r.loginid) from rptaccess r, record_of_access roa where roa.system = 'Remove All' and roa.accountstatus = 'D' and r.eid = roa.eid and r.loginid = roa.loginid and upper(r.applicationname) = upper(roa.applicationname) ) and upper(profilecode) in ( select upper(r.profilecode) from rptaccess r, record_of_access roa where roa.system = 'Remove All' and roa.accountstatus = 'D' and r.eid = roa.eid and r.loginid = roa.loginid and upper(r.applicationname) = upper(roa.applicationname) ); Please help Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query taking too long
On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? You have answered your own question! The problem is that there are no indexes on your tables. Indexes are designed to speed SELECT queries up, so not having indexes will cause your SELECT queries to slow down. Define indexes on the columns you are querying against; I'd start with accountstatus, eid, loginid, applicationname, profilecode... From the names I'd suggest some of those might be UNIQUE indexes or PRIMARY KEYs. -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query taking too long
Unfortunately, I cannot create indexes for these tables. These are on production and I cannot modify the tables in anyway. Also, none of the columns are unique in nature - they just serve as a reporting store. Is there anyway that I can tune the select query itself and hope some performance enhancement?? (Maybe I am scanning the tables once too many.. or) Need help please. Thanks, Anoop On 10/19/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? You have answered your own question! The problem is that there are no indexes on your tables. Indexes are designed to speed SELECT queries up, so not having indexes will cause your SELECT queries to slow down. Define indexes on the columns you are querying against; I'd start with accountstatus, eid, loginid, applicationname, profilecode... From the names I'd suggest some of those might be UNIQUE indexes or PRIMARY KEYs. -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
[posted only] On Oct 19, 2005, at 4:07 PM, Jon Frisby wrote: Create an Excel spreadsheet. Import the raw data, structured as-is, into a worksheet. Select all the relevant columns. Go to Data - Pivot Table and Pivot Chart Report. Click Finish. From the PivotTable Field List, drag the respondant ID into the box labeled Drop Row Fields Here, then drag question ID into the box labeled Drop Column Fields Here. Voila. Thank you so much. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Turning tables on their side
[posted only] On Oct 19, 2005, at 3:48 PM, sheeri kritzer wrote: One particular bit of SQL you may find helpful is this: concat(ifnull(a_id,),ifnull(a_text,)) concat with anything and a null value will produce a null value. That snippet of sql code will help you get one answer from the 2 the original database had. Thank you. I can immediately see several places where that will come in handy Unless there's ever an answer_id AND an answer_text, although the example doesn't support that. Well, there shouldn't be any cases like that, but I'll can run a quick query to check. Thank you for all of your help. -j -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database won't load after machine reboot
Hi. I have a database that is used with wordpress blogging software. Yesterday the server that it's running on reset itself. Since then I can't access the database. What could be going on here? Any ideas? Thanks. Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database won't load after machine reboot
Hi. For clarity, I'm running mysql 4.0.20 And I did start the mysql daemon. Luke Vanderfluit wrote: Hi. I have a database that is used with wordpress blogging software. Yesterday the server that it's running on reset itself. Since then I can't access the database. What could be going on here? Any ideas? Thanks. Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query taking too long
Im a little confused by the query you posted.. it looks like it would work, although with many redundant subqueries to get there. From your requirement, I don't understand why you needs to wrap it in a self- referencing subquery.. Why does this not give you the same logical value? select count(r2.eid) from rptaccess r2, record_of_access roa where roa.system = 'Remove All' and roa.accountstatus = 'D' and r2.eid = roa.eid and r2.loginid = roa.loginid and upper(r2.applicationname) = upper(roa.applicationname) Having said that: if your original query takes 5 seconds in your 30 record QA environment, adding a few indexes as recommended will take it down to the order of maybe a few hundred milliseconds. Adding indexes to production, while not to be taken lightly, is not something to be shy away from. for records on the order of a few hundred K, it would be a matter of a minute or so and the odds of the action breaking anything are very nearly nil. On Wed, 2005-19-10 at 19:39 -0400, Anoop kumar V wrote: Unfortunately, I cannot create indexes for these tables. These are on production and I cannot modify the tables in anyway. Also, none of the columns are unique in nature - they just serve as a reporting store. Is there anyway that I can tune the select query itself and hope some performance enhancement?? (Maybe I am scanning the tables once too many.. or) Need help please. Thanks, Anoop On 10/19/05, Jasper Bryant-Greene [EMAIL PROTECTED] wrote: On Wed, 2005-10-19 at 17:39 -0400, Anoop kumar V wrote: I have 2 tables used for reporting and there are no primary keys or indexes for either. I am trying to run a select query to identify some rows that need to be removed. But for around 100,000 rows the query is taking too long. Can somebody please help me in tuning this query? You have answered your own question! The problem is that there are no indexes on your tables. Indexes are designed to speed SELECT queries up, so not having indexes will cause your SELECT queries to slow down. Define indexes on the columns you are querying against; I'd start with accountstatus, eid, loginid, applicationname, profilecode... From the names I'd suggest some of those might be UNIQUE indexes or PRIMARY KEYs. -- Jasper Bryant-Greene General Manager Album Limited e: [EMAIL PROTECTED] w: http://www.album.co.nz/ p: 0800 4 ALBUM (0800 425 286) or +64 21 232 3303 a: PO Box 579, Christchurch 8015, New Zealand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks and best regards, Anoop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database won't load after machine reboot
Do you have any log messages associated with it? (not sure for win / os x for linux look in /var/lib/mysql/hostname.err) Hi. For clarity, I'm running mysql 4.0.20 And I did start the mysql daemon. Luke Vanderfluit wrote: Hi. I have a database that is used with wordpress blogging software. Yesterday the server that it's running on reset itself. Since then I can't access the database. What could be going on here? Any ideas? Thanks. Kind regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database won't load after machine reboot
Do you have any sample of error output? This would be useful. Thanks David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 11:22 AM To: mysql@lists.mysql.com Subject: Re: database won't load after machine reboot Hi. For clarity, I'm running mysql 4.0.20 And I did start the mysql daemon. Luke Vanderfluit wrote: Hi. I have a database that is used with wordpress blogging software. Yesterday the server that it's running on reset itself. Since then I can't access the database. What could be going on here? Any ideas? Thanks. Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database won't load after machine reboot
Hi. Logan, David (SST - Adelaide) wrote: Do you have any sample of error output? This would be useful. I think the problem started where I have inserted the lines. Thanks. / = 050914 04:44:49 mysqld restarted 050914 4:44:53 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 050918 0:27:35 Aborted connection 30 to db: 'unconnected' user: 'luke' host: `loc alhost' (Got timeout reading communication packets) 050923 9:01:48 Aborted connection 5931 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5934 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5937 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5940 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5943 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5946 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 051015 14:53:20 Aborted connection 14332 to db: 'comstechIntDB' user: 'luke' host: / = `localhost' (Got an error writing communication packets) 051019 11:36:44 mysqld started 051019 11:36:44 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 11:36:44 InnoDB: Flushing modified pages from the buffer pool... 051019 11:36:44 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 14:04:27 mysqld started 051019 14:04:27 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 14:04:27 InnoDB: Flushing modified pages from the buffer pool... 051019 14:04:27 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 18:55:31 /myProgs/mysql/libexec/mysqld: Normal shutdown 051019 18:55:31 InnoDB: Starting shutdown... 051019 18:55:33 InnoDB: Shutdown completed 051019 18:55:33 /myProgs/mysql/libexec/mysqld: Shutdown Complete 051019 18:55:33 mysqld ended 051019 19:39:17 mysqld started 051019 19:39:18 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 19:46:11 /myProgs/mysql/libexec/mysqld: Normal shutdown 051019 19:46:11 InnoDB: Starting shutdown... 051019 19:46:14 InnoDB: Shutdown completed 051019 19:46:14 /myProgs/mysql/libexec/mysqld: Shutdown Complete 051019 19:46:14 mysqld ended 051019 19:46:20 mysqld started 051019 19:46:20 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 Thanks David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 11:22 AM To: mysql@lists.mysql.com Subject: Re: database won't load after machine reboot Hi. For clarity, I'm running mysql 4.0.20 And I did start the mysql daemon. Luke Vanderfluit wrote: Hi. I have a database that is used with wordpress blogging software. Yesterday the server that it's running on reset itself. Since then I can't access the database. What could be going on here? Any ideas? Thanks. Kind regards. -- Luke
RE: database won't load after machine reboot
Hi Luke, According to the log 051019 19:46:20 mysqld started 051019 19:46:20 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 mysqld is sitting there fat, dumb and happy waiting for somebody to talk to it at either /tmp/mysql.sock or on port 3306. Are you able to connect to other databases? Have you tried connecting via the mysql client and seeing if you can get to the databases that way? This would give you an error message which could help identify what the issue is. eg. $ mysql -u user -p -h mysql hostname -D database name You could run mysqlcheck -u root -p -h mysql hostname database name and this will tell you if there is any corruption in any table. Earlier in the piece you had a crash, thats fine as InnoDB did the right thing and recovered nicely (as it should) and since then the database has been restarted a few times with no issue. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 11:45 AM To: mysql Subject: Re: database won't load after machine reboot Hi. Logan, David (SST - Adelaide) wrote: Do you have any sample of error output? This would be useful. I think the problem started where I have inserted the lines. Thanks. / = 050914 04:44:49 mysqld restarted 050914 4:44:53 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 050918 0:27:35 Aborted connection 30 to db: 'unconnected' user: 'luke' host: `loc alhost' (Got timeout reading communication packets) 050923 9:01:48 Aborted connection 5931 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5934 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5937 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5940 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5943 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5946 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 051015 14:53:20 Aborted connection 14332 to db: 'comstechIntDB' user: 'luke' host: / = `localhost' (Got an error writing communication packets) 051019 11:36:44 mysqld started 051019 11:36:44 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 11:36:44 InnoDB: Flushing modified pages from the buffer pool... 051019 11:36:44 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 14:04:27 mysqld started 051019 14:04:27 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 14:04:27 InnoDB: Flushing modified pages from the buffer pool... 051019 14:04:27 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 18:55:31 /myProgs/mysql/libexec/mysqld: Normal shutdown 051019 18:55:31 InnoDB: Starting shutdown... 051019 18:55:33 InnoDB: Shutdown completed 051019 18:55:33 /myProgs/mysql/libexec/mysqld: Shutdown Complete 051019 18:55:33 mysqld ended 051019 19:39:17 mysqld started 051019 19:39:18 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 19:46:11 /myProgs/mysql/libexec/mysqld: Normal shutdown 051019 19:46:11 InnoDB: Starting shutdown... 051019 19:46:14 InnoDB: Shutdown completed 051019 19:46:14 /myProgs/mysql/libexec/mysqld: Shutdown Complete 051019 19:46:14 mysqld ended 051019 19:46:20 mysqld started 051019 19:46:20 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version:
Re: database won't load after machine reboot
Hi David. Logan, David (SST - Adelaide) wrote: Hi Luke, According to the log 051019 19:46:20 mysqld started 051019 19:46:20 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 mysqld is sitting there fat, dumb and happy waiting for somebody to talk to it at either /tmp/mysql.sock or on port 3306. Are you able to connect to other databases? Yes. Have you tried connecting via the mysql client and seeing if you can get to the databases that way? This would give you an error message which could help identify what the issue is. Yes. mysql -u luke -p blog ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'blog' eg. $ mysql -u user -p -h mysql hostname -D database name You could run mysqlcheck -u root -p -h mysql hostname database name and this will tell you if there is any corruption in any table. Earlier in the piece you had a crash, thats fine as InnoDB did the right thing and recovered nicely (as it should) and since then the database has been restarted a few times with no issue. That's right. So mysql is fine. Just can't connect to the database name blog. I'm certain of the right username and password. So hmmm Kind regards. Luke. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 11:45 AM To: mysql Subject: Re: database won't load after machine reboot Hi. Logan, David (SST - Adelaide) wrote: Do you have any sample of error output? This would be useful. I think the problem started where I have inserted the lines. Thanks. / = 050914 04:44:49 mysqld restarted 050914 4:44:53 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 050918 0:27:35 Aborted connection 30 to db: 'unconnected' user: 'luke' host: `loc alhost' (Got timeout reading communication packets) 050923 9:01:48 Aborted connection 5931 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5934 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5937 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5940 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5943 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5946 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 051015 14:53:20 Aborted connection 14332 to db: 'comstechIntDB' user: 'luke' host: / = `localhost' (Got an error writing communication packets) 051019 11:36:44 mysqld started 051019 11:36:44 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 11:36:44 InnoDB: Flushing modified pages from the buffer pool... 051019 11:36:44 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 14:04:27 mysqld started 051019 14:04:27 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 14:04:27 InnoDB: Flushing modified pages from the buffer pool... 051019 14:04:27 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 18:55:31 /myProgs/mysql/libexec/mysqld: Normal shutdown 051019 18:55:31 InnoDB: Starting shutdown... 051019 18:55:33 InnoDB: Shutdown completed 051019 18:55:33 /myProgs/mysql/libexec/mysqld: Shutdown Complete 051019 18:55:33 mysqld ended 051019 19:39:17 mysqld started 051019 19:39:18 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 19:46:11
RE: database won't load after machine reboot
Hi Luke, Have you tried doing another GRANT statement to ensure the name and password are indeed correct? That would be my next step. That way at least you are sure that the name/password combination are correct. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 12:00 PM To: mysql Subject: Re: database won't load after machine reboot Hi David. Logan, David (SST - Adelaide) wrote: Hi Luke, According to the log 051019 19:46:20 mysqld started 051019 19:46:20 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 mysqld is sitting there fat, dumb and happy waiting for somebody to talk to it at either /tmp/mysql.sock or on port 3306. Are you able to connect to other databases? Yes. Have you tried connecting via the mysql client and seeing if you can get to the databases that way? This would give you an error message which could help identify what the issue is. Yes. mysql -u luke -p blog ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'blog' eg. $ mysql -u user -p -h mysql hostname -D database name You could run mysqlcheck -u root -p -h mysql hostname database name and this will tell you if there is any corruption in any table. Earlier in the piece you had a crash, thats fine as InnoDB did the right thing and recovered nicely (as it should) and since then the database has been restarted a few times with no issue. That's right. So mysql is fine. Just can't connect to the database name blog. I'm certain of the right username and password. So hmmm Kind regards. Luke. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 11:45 AM To: mysql Subject: Re: database won't load after machine reboot Hi. Logan, David (SST - Adelaide) wrote: Do you have any sample of error output? This would be useful. I think the problem started where I have inserted the lines. Thanks. / = 050914 04:44:49 mysqld restarted 050914 4:44:53 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 050918 0:27:35 Aborted connection 30 to db: 'unconnected' user: 'luke' host: `loc alhost' (Got timeout reading communication packets) 050923 9:01:48 Aborted connection 5931 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5934 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5937 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5940 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5943 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 050923 9:01:48 Aborted connection 5946 to db: 'unconnected' user: 'luke' host: `l ocalhost' (Got an error reading communication packets) 051015 14:53:20 Aborted connection 14332 to db: 'comstechIntDB' user: 'luke' host: / = `localhost' (Got an error writing communication packets) 051019 11:36:44 mysqld started 051019 11:36:44 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 11:36:44 InnoDB: Flushing modified pages from the buffer pool... 051019 11:36:44 InnoDB: Started /myProgs/mysql/libexec/mysqld: ready for connections. Version: '4.0.20-log' socket: '/tmp/mysql.sock' port: 3306 051019 14:04:27 mysqld started 051019 14:04:27 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 6648179 InnoDB: Doing recovery: scanned up to log sequence number 0 6648179 InnoDB: Last MySQL binlog file position 0 5263271, file name ./bench-bin.015 051019 14:04:27 InnoDB: Flushing modified pages from the buffer pool... 051019 14:04:27 InnoDB: Started
Spatial Extensions to make a Dealer Locator?
I'm in the midst of the age-old problem of finding the right SQL to make a Dealer Locator by ZIP code. Like Steffan's posts from a couple weeks ago, I have the ZIP code data in one table with latitudes and longitudes, and the table of Dealers with ZIP codes in a separate table. While researching this I came across a reference to MySQL's Spatial Extensions: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions-in-mysql.html This is WAY over my head. The reference I found said these extensions are immature. I'm not even exactly sure what an extension is: does it mean that my ISP (PowWeb) probably doesn't have it installed? Has anyone else who has tackled this application found the Spatial Extensions to be useful, or is it better to stick with the basics and go the route discussed in Steffan's recent thread? - Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database won't load after machine reboot
Hi David. Thanks for your help. Logan, David (SST - Adelaide) wrote: Hi Luke, Have you tried doing another GRANT statement to ensure the name and password are indeed correct? That would be my next step. That way at least you are sure that the name/password combination are correct. I can't access the database as root anymore. Accessing as other users doesn't allow me to set any GRANT statements. Is there any way I can reset the root password? (Ouch). Kr. Luke. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database won't load after machine reboot
Hi Luke, Yep, there is a procedure in the manual http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html here. This has all the steps you will need to get root back. Hope it all ends up ok! Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 12:17 PM To: mysql Subject: Re: database won't load after machine reboot Hi David. Thanks for your help. Logan, David (SST - Adelaide) wrote: Hi Luke, Have you tried doing another GRANT statement to ensure the name and password are indeed correct? That would be my next step. That way at least you are sure that the name/password combination are correct. I can't access the database as root anymore. Accessing as other users doesn't allow me to set any GRANT statements. Is there any way I can reset the root password? (Ouch). Kr. Luke. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial Extensions to make a Dealer Locator?
Brian I'm in the midst of the age-old problem of finding the right SQL to make a Dealer Locator by ZIP code. Like Steffan's posts from a couple weeks ago, I have the ZIP code data in one table with latitudes and longitudes, and the table of Dealers with ZIP codes in a separate table. For great circle distance given latitudes longitudes you may find (as we did) that the current MySQL implementation of OpenGIS is simultaneously overkill (because of complexity) and underkill ( for features maturity). There are several reliable formulas for great circle distance including a few quoted here. PB Brian Dunning wrote: I'm in the midst of the age-old problem of finding the right SQL to make a Dealer Locator by ZIP code. Like Steffan's posts from a couple weeks ago, I have the ZIP code data in one table with latitudes and longitudes, and the table of Dealers with ZIP codes in a separate table. While researching this I came across a reference to MySQL's Spatial Extensions: http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions-in-mysql.html This is WAY over my head. The reference I found said these extensions are immature. I'm not even exactly sure what an extension is: does it mean that my ISP (PowWeb) probably doesn't have it installed? Has anyone else who has tackled this application found the Spatial Extensions to be useful, or is it better to stick with the basics and go the route discussed in Steffan's recent thread? - Brian -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.344 / Virus Database: 267.12.4/143 - Release Date: 10/19/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database won't load after machine reboot
Hi David. Logan, David (SST - Adelaide) wrote: Hi Luke, Yep, there is a procedure in the manual http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html here. This has all the steps you will need to get root back. Hope it all ends up ok! That went well (resetting the root password). Then the grant statement did the trick. Thanks. Kind regards. Luke. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Luke Vanderfluit [mailto:[EMAIL PROTECTED] Sent: Thursday, 20 October 2005 12:17 PM To: mysql Subject: Re: database won't load after machine reboot Hi David. Thanks for your help. Logan, David (SST - Adelaide) wrote: Hi Luke, Have you tried doing another GRANT statement to ensure the name and password are indeed correct? That would be my next step. That way at least you are sure that the name/password combination are correct. I can't access the database as root anymore. Accessing as other users doesn't allow me to set any GRANT statements. Is there any way I can reset the root password? (Ouch). Kr. Luke. -- Luke
