Re: create database+tables
Do this. Save the create tables commands in a textfile on your harddrive called 'C:\tables.sql' Load mysql from the command line. @ the prompt, type the following mysql>create database `myDBName`; mysql>use `myDBName`; mysql>source C:\tables.sql; that's it! On Apr 2, 2005 10:26 AM, Niki Lampropoulou <[EMAIL PROTECTED]> wrote: > better description>> > > instructions to be followed for installation of ALICE > PHP chatbot. It is the first time I am using MySQL> > 2. Create a database for the program to use in MySQL. > 3. Create the tables in the new database using db.sql > which is in the sql directory. > > tables > > # > # Table structure for table `bot` > # > > CREATE TABLE bot ( > id int(11) NOT NULL auto_increment, > bot tinyint(4) NOT NULL default '0', > name varchar(255) NOT NULL default '', > value text NOT NULL, > PRIMARY KEY (id), > KEY botname (bot,name) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `bots` > # > > CREATE TABLE bots ( > id tinyint(3) unsigned NOT NULL auto_increment, > botname varchar(255) NOT NULL default '', > PRIMARY KEY (botname), > KEY id (id) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `conversationlog` > # > > CREATE TABLE conversationlog ( > bot tinyint(3) unsigned NOT NULL default '0', > id int(11) NOT NULL auto_increment, > input text, > response text, > uid varchar(255) default NULL, > enteredtime timestamp(14) NOT NULL, > PRIMARY KEY (id), > KEY botid (bot) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `dstore` > # > > CREATE TABLE dstore ( > uid varchar(255) default NULL, > name text, > value text, > enteredtime timestamp(14) NOT NULL, > id int(11) NOT NULL auto_increment, > PRIMARY KEY (id), > KEY nameidx (name(40)) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `gmcache` > # > > CREATE TABLE gmcache ( > id int(11) NOT NULL auto_increment, > bot tinyint(3) unsigned NOT NULL default '0', > template int(11) NOT NULL default '0', > inputstarvals text, > thatstarvals text, > topicstarvals text, > patternmatched text, > inputmatched text, > combined text NOT NULL, > PRIMARY KEY (id), > KEY combined (bot,combined(255)) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `gossip` > # > > CREATE TABLE gossip ( > bot tinyint(3) unsigned NOT NULL default '0', > gossip text, > id int(11) NOT NULL auto_increment, > PRIMARY KEY (id), > KEY botidx (bot) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `patterns` > # > > CREATE TABLE patterns ( > bot tinyint(3) unsigned NOT NULL default '0', > id int(11) NOT NULL auto_increment, > word varchar(255) default NULL, > ordera tinyint(4) NOT NULL default '0', > parent int(11) NOT NULL default '0', > isend tinyint(4) NOT NULL default '0', > PRIMARY KEY (id), > KEY wordparent (parent,word), > KEY botid (bot) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `templates` > # > > CREATE TABLE templates ( > bot tinyint(3) unsigned NOT NULL default '0', > id int(11) NOT NULL default '0', > template text NOT NULL, > pattern varchar(255) default NULL, > that varchar(255) default NULL, > topic varchar(255) default NULL, > PRIMARY KEY (id), > KEY bot (id) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `thatindex` > # > > CREATE TABLE thatindex ( > uid varchar(255) default NULL, > enteredtime timestamp(14) NOT NULL, > id int(11) NOT NULL auto_increment, > PRIMARY KEY (id) > ) TYPE=MyISAM; > # > > > # > # Table structure for table `thatstack` > # > > CREATE TABLE thatstack ( > thatid int(11) NOT NULL default '0', > id int(11) NOT NULL auto_increment, > value varchar(255) default NULL, > enteredtime timestamp(14) NOT NULL, > PRIMARY KEY (id) > ) TYPE=MyISAM; > > Send instant messages to your online friends http://uk.messenger.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT with ON DUPLICATE error
Hi, This is a known bug which will be fixed in MySQL 4.1.11. See http://bugs.mysql.com/bug.php?id=8675 and http://bugs.mysql.com/bug.php?id=8147 Regards, Jocelyn Eli wrote: Hi, I got this table: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | id | int(10) unsigned| | PRI | 0 | | | value| varchar(128)| YES | | NULL| | +--+-+--+-+-+---+ I try this query: INSERT INTO tbl (id,value) (SELECT ref_id,'fixed value' FROM another_tb WHERE id>'100') ON DUPLICATE KEY UPDATE id=id; but I get the next error: ERROR 1110 (42000): Column 'id' specified twice When I remove the fields list (id,value), then it works, and as expected the duplicated key rows are not changed. It doesn't matter if I use INSERT-SELECT or regular SELECT. Is this a bug? -thanks, Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT with ON DUPLICATE error
Hi, I got this table: +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | id | int(10) unsigned| | PRI | 0 | | | value| varchar(128)| YES | | NULL| | +--+-+--+-+-+---+ I try this query: INSERT INTO tbl (id,value) (SELECT ref_id,'fixed value' FROM another_tb WHERE id>'100') ON DUPLICATE KEY UPDATE id=id; but I get the next error: ERROR 1110 (42000): Column 'id' specified twice When I remove the fields list (id,value), then it works, and as expected the duplicated key rows are not changed. It doesn't matter if I use INSERT-SELECT or regular SELECT. Is this a bug? -thanks, Eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
It took me awhile to understand this, but it does appear to be the correct solution in my case. Thank you. Bartis, Robert M (Bob) wrote: Sounds like you need a 1:N relationship table to hold userInfo separate from either the user or group table. Adding a infoIdentifier would allow the number of rows added for a specific user to be sized based on the specific user needs. This is effect would be the "key" part of a key-value pair, normally associated with associative arrays. The userSpecificInformation would hold the value portion of the information. UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation user table: id (pk) name any other user info only dependant on the user group table: id (pk) name usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation -- == Eric Gorr = http://www.ericgorr.net = ICQ:9293199 == "Therefore the considerations of the intelligent always include both benefit and harm." - Sun Tzu == Insults, like violence, are the last refuge of the incompetent... === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: [Q] Database design
Sounds like you need a 1:N relationship table to hold userInfo separate from either the user or group table. Adding a infoIdentifier would allow the number of rows added for a specific user to be sized based on the specific user needs. This is effect would be the "key" part of a key-value pair, normally associated with associative arrays. The userSpecificInformation would hold the value portion of the information. UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation > user table: > id (pk) > name > any other user info only dependant on the user > > group table: > id (pk) > name > > usergroup table: > user_id (pk) > group_id (pk) > any info specific to individual user/group combo UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation -Original Message- From: Eric Gorr [mailto:[EMAIL PROTECTED] Sent: Saturday, April 02, 2005 2:59 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: [Q] Database design Tom Crimmins wrote: > user table: > id (pk) > name > any other user info only dependant on the user > > group table: > id (pk) > name > any other group info only dependant on the group > > usergroup table: > user_id (pk) > group_id (pk) > any info specific to individual user/group combo Yes, if I understand what you are saying here correctly, I considered this. However, the problem is that the columns corresponding to "any info specific to individual user/group combo" is not guaranteed to be consistent across groups. Well, to be more precise, the type for each column will be the same, but the number of required columns (call this number N) will be different. It is for this reason that it seemed necessary to have a separate table per group. Now, if I could decide what the maximum number of required columns would be, then I could see using this design, but this is simply not possible. I am, of course, limited by the maximum number of columns (call this number X) allowed within a mySQL database. The required number of columns for a particular group could be anywhere between 1 and X. However, it just seemed like a bad idea to use that large of a table when the vast majority of it would go unused and much of it would likely never be used at all. But, perhaps I am wrong and it would simply not be an issue. I suppose it would be possible to dynamically size 'usergroup table' based on the current max N across all groups. Basically, if N changes for a particular group, look at the value of N for all groups, take the max and size 'usergroup table' accordingly. Is this what you would do? -- 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: Grants not entirely propagated to slaves?
What version of MySQL are you using? Also, are you issuing only "GRANT .." statements or modifying the privilege tables manually as well? Search for 'GRANT': http://dev.mysql.com/doc/mysql/en/replication-features.html Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 1 Apr 2005, Nico Sabbi wrote: > Hi, > it seems my Grants are not entirely propagated from the master to the slave > (some are active, some are not). > The slave is configured to replicate all databases, and the replication > client > has all privileges on the master. > > What is necessary to propagate every single grant? > > Thanks, > > -- > Nico Sabbi - Officine Digitali - Bologna > Tel. 051 - 4187565 > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug with some collations?
I have table with only one column set to latin1_general_ci collation, and I get following error: *Illegal mix of collations (latin1_general_cs,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='* Client (PHP5 with mysql functions) uses utf-8 character set. MySQL is 4.1.10a. When I change collation of that column everything works fine. MySQL server knows client's character set, it knows column's character set but refuses to do conversion. I tried using other latin1 collations and here's result. latin1_bin OK latin1_danish_ci BAD latin1_general_ci BAD latin1_general_cs BAD latin1_german1_ci BAD latin1_german2_ci OK latin1_spanish_ci BAD latin1_sweedish_ci OK All latin2 collations are OK. Possible cause of error is utf8 word containing characters that don't exist in latin1 character set. For example: "SELECT * FROM table WHERE word='abaÅur'". But shouldn't all latin1 collatins return same error? I also noticed that PHP's mysqli query function doesn't fail with error. It just returns empty set. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
Tom Crimmins wrote: user table: id (pk) name any other user info only dependant on the user group table: id (pk) name any other group info only dependant on the group usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo Yes, if I understand what you are saying here correctly, I considered this. However, the problem is that the columns corresponding to "any info specific to individual user/group combo" is not guaranteed to be consistent across groups. Well, to be more precise, the type for each column will be the same, but the number of required columns (call this number N) will be different. It is for this reason that it seemed necessary to have a separate table per group. Now, if I could decide what the maximum number of required columns would be, then I could see using this design, but this is simply not possible. I am, of course, limited by the maximum number of columns (call this number X) allowed within a mySQL database. The required number of columns for a particular group could be anywhere between 1 and X. However, it just seemed like a bad idea to use that large of a table when the vast majority of it would go unused and much of it would likely never be used at all. But, perhaps I am wrong and it would simply not be an issue. I suppose it would be possible to dynamically size 'usergroup table' based on the current max N across all groups. Basically, if N changes for a particular group, look at the value of N for all groups, take the max and size 'usergroup table' accordingly. Is this what you would do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
On Saturday, April 02, 2005 12:48, Eric Gorr wrote: > Peter Brawley wrote: >> Eric, >> >> If I understand you correctly, you propose to encode user and group >> info as table names. That's a mistake. To use an RDBMS like MySQL >> effectively, you want to encode your information as rows of data in >> tables whose names and structures do not vary. > > Thank you for your comments. Would this design be better? > > ( assume that one of the group names will be 'Group_A' ) > > Database > >Table_Groups > group name > # of user columns > >Table_Group_A_users > username > Column 1 Data > Column 2 Data > ... > Column N Data > > I am still encoding group info into a table name, but I am unsure of > how to avoid this and not have a table with a lot of wasted space. > >> May I suggest you read >> some of the tutorials listed at >> http://www.artfulsoftware.com/dbresources.html, and/or read >> http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. > > Thank you for the pointers. > > Unfortunately, http://www.artfulsoftware.com/dbresources.html seems to > have a couple of broken links (Codd's Rules)...any idea where this > information can be found? user table: id (pk) name any other user info only dependant on the user group table: id (pk) name any other group info only dependant on the group usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo pk = primary key This third table is called a linking table. It allows you to deal with a many-to-many relationship. This setup allows a group to have multiple users, and users can belong to multiple groups. You will need to look into joins to see how to query these tables effectively. For example to find out what users are in group A. SELECT u.name FROM user as u INNER JOIN usergroup as ug ON (u.id = ug.user_id) INNER JOIN group as g ON (ug.group_id = g.id) WHERE g.name = 'A' -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
Eric, You do not want to encode data in table names. Mainly you want to create the least redundant set representation of your data, realise that representation as fixed related tables, then use SQL to fill & query those tables. Do a little reading and play with a few toy designs before you start on your own problem. Two of those Codd's Rules sites have gone away but there are loads of them on the web, eg it took a few secs to find http://www.itworld.com/nl/db_mgr/05072001/ http://members.aol.com/mbtexts/120.html and the rules are covered too in http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. PB Eric Gorr wrote: Peter Brawley wrote: Eric, If I understand you correctly, you propose to encode user and group info as table names. That's a mistake. To use an RDBMS like MySQL effectively, you want to encode your information as rows of data in tables whose names and structures do not vary. Thank you for your comments. Would this design be better? ( assume that one of the group names will be 'Group_A' ) Database Table_Groups group name # of user columns Table_Group_A_users username Column 1 Data Column 2 Data ... Column N Data I am still encoding group info into a table name, but I am unsure of how to avoid this and not have a table with a lot of wasted space. May I suggest you read some of the tutorials listed at http://www.artfulsoftware.com/dbresources.html, and/or read http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. Thank you for the pointers. Unfortunately, http://www.artfulsoftware.com/dbresources.html seems to have a couple of broken links (Codd's Rules)...any idea where this information can be found? No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
Peter Brawley wrote: Eric, If I understand you correctly, you propose to encode user and group info as table names. That's a mistake. To use an RDBMS like MySQL effectively, you want to encode your information as rows of data in tables whose names and structures do not vary. Thank you for your comments. Would this design be better? ( assume that one of the group names will be 'Group_A' ) Database Table_Groups group name # of user columns Table_Group_A_users username Column 1 Data Column 2 Data ... Column N Data I am still encoding group info into a table name, but I am unsure of how to avoid this and not have a table with a lot of wasted space. May I suggest you read some of the tutorials listed at http://www.artfulsoftware.com/dbresources.html, and/or read http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. Thank you for the pointers. Unfortunately, http://www.artfulsoftware.com/dbresources.html seems to have a couple of broken links (Codd's Rules)...any idea where this information can be found? -- == Eric Gorr = http://www.ericgorr.net = ICQ:9293199 == "Those who would sacrifice a little freedom for temporal safety deserve neither to be safe or free." -- Benjamin Franklin == Insults, like violence, are the last refuge of the incompetent... === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] Database design
Eric, If I understand you correctly, you propose to encode user and group info as table names. That's a mistake. To use an RDBMS like MySQL effectively, you want to encode your information as rows of data in tables whose names and structures do not vary. May I suggest you read some of the tutorials listed at http://www.artfulsoftware.com/dbresources.html, and/or read http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf. PB - Eric Gorr wrote: Not having done much database design in the past, I have what should be a fairly simple design question. I usage of mySQL will revolve around a common group + user system. There can be multiple groups and each group will contain some subset of users. Each group will have a custom set of data whose values vary per user. So basically a sample structure might look like this: (some details intentionally left out) Database Table_Group_A # of user columns Table_Group_A_UserX Column 1 Data Column 2 Data ... Column N Data One probably incorrect thought on my part is that it would not be necessary to store the usernames in Table_Group_A of those users who belong to that group. But, thinking about it more, it seems like a good idea. My original intent was to simply look for tables named Table_Group_A_* and extract the username from the table name... Does anyone have any recommends concerning this kind of design? I would like to be able to lay things out in mySQL as cleanly as possible. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 4/1/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create database+tables
better description>> instructions to be followed for installation of ALICE PHP chatbot. It is the first time I am using MySQL> 2. Create a database for the program to use in MySQL. 3. Create the tables in the new database using db.sql which is in the sql directory. tables # # Table structure for table `bot` # CREATE TABLE bot ( id int(11) NOT NULL auto_increment, bot tinyint(4) NOT NULL default '0', name varchar(255) NOT NULL default '', value text NOT NULL, PRIMARY KEY (id), KEY botname (bot,name) ) TYPE=MyISAM; # # # Table structure for table `bots` # CREATE TABLE bots ( id tinyint(3) unsigned NOT NULL auto_increment, botname varchar(255) NOT NULL default '', PRIMARY KEY (botname), KEY id (id) ) TYPE=MyISAM; # # # Table structure for table `conversationlog` # CREATE TABLE conversationlog ( bot tinyint(3) unsigned NOT NULL default '0', id int(11) NOT NULL auto_increment, input text, response text, uid varchar(255) default NULL, enteredtime timestamp(14) NOT NULL, PRIMARY KEY (id), KEY botid (bot) ) TYPE=MyISAM; # # # Table structure for table `dstore` # CREATE TABLE dstore ( uid varchar(255) default NULL, name text, value text, enteredtime timestamp(14) NOT NULL, id int(11) NOT NULL auto_increment, PRIMARY KEY (id), KEY nameidx (name(40)) ) TYPE=MyISAM; # # # Table structure for table `gmcache` # CREATE TABLE gmcache ( id int(11) NOT NULL auto_increment, bot tinyint(3) unsigned NOT NULL default '0', template int(11) NOT NULL default '0', inputstarvals text, thatstarvals text, topicstarvals text, patternmatched text, inputmatched text, combined text NOT NULL, PRIMARY KEY (id), KEY combined (bot,combined(255)) ) TYPE=MyISAM; # # # Table structure for table `gossip` # CREATE TABLE gossip ( bot tinyint(3) unsigned NOT NULL default '0', gossip text, id int(11) NOT NULL auto_increment, PRIMARY KEY (id), KEY botidx (bot) ) TYPE=MyISAM; # # # Table structure for table `patterns` # CREATE TABLE patterns ( bot tinyint(3) unsigned NOT NULL default '0', id int(11) NOT NULL auto_increment, word varchar(255) default NULL, ordera tinyint(4) NOT NULL default '0', parent int(11) NOT NULL default '0', isend tinyint(4) NOT NULL default '0', PRIMARY KEY (id), KEY wordparent (parent,word), KEY botid (bot) ) TYPE=MyISAM; # # # Table structure for table `templates` # CREATE TABLE templates ( bot tinyint(3) unsigned NOT NULL default '0', id int(11) NOT NULL default '0', template text NOT NULL, pattern varchar(255) default NULL, that varchar(255) default NULL, topic varchar(255) default NULL, PRIMARY KEY (id), KEY bot (id) ) TYPE=MyISAM; # # # Table structure for table `thatindex` # CREATE TABLE thatindex ( uid varchar(255) default NULL, enteredtime timestamp(14) NOT NULL, id int(11) NOT NULL auto_increment, PRIMARY KEY (id) ) TYPE=MyISAM; # # # Table structure for table `thatstack` # CREATE TABLE thatstack ( thatid int(11) NOT NULL default '0', id int(11) NOT NULL auto_increment, value varchar(255) default NULL, enteredtime timestamp(14) NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I am stupid>>
I dont know how to do this very basuc, just atrated working with MySQL and not sure about what I am supposed to do.. Please help!! 2. Create a database for the program to use in MySQL. 3. Create the tables in the new database using db.sql which is in the sql directory. niki Send instant messages to your online friends http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Q] Database design
Not having done much database design in the past, I have what should be a fairly simple design question. I usage of mySQL will revolve around a common group + user system. There can be multiple groups and each group will contain some subset of users. Each group will have a custom set of data whose values vary per user. So basically a sample structure might look like this: (some details intentionally left out) Database Table_Group_A # of user columns Table_Group_A_UserX Column 1 Data Column 2 Data ... Column N Data One probably incorrect thought on my part is that it would not be necessary to store the usernames in Table_Group_A of those users who belong to that group. But, thinking about it more, it seems like a good idea. My original intent was to simply look for tables named Table_Group_A_* and extract the username from the table name... Does anyone have any recommends concerning this kind of design? I would like to be able to lay things out in mySQL as cleanly as possible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Assertions
I'm taking a database course and am curious about the support for assertions in MySQL. It appears that they were added to SQL92, but I don't see any information about them in the MySQL documentation (either to say they are supported or to say they are not). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
Stefano, The behaviour you are describing is normal, assuming that the column in your MySQL table is defined as CHAR(255) or VARCHAR(255). You didn't say which version of MySQL you are using. However, unless you are using MySQL 5.0.3 or later, 255 is the largest size available for a CHAR or VARCHAR column. (Starting with version 5.0.3, the maximum size of a VARCHAR is 65,536.) Assuming you are not on 5.0.3 or later, you should change your table definition to use one of the BLOB or TEXT datatypes: TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB or TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. You should look at the definitions of these column types in the manual - http://dev.mysql.com/doc/mysql/en/storage-requirements.html - to see which one best suits your requirements; only you know the largest value that you want to store in the column. Basically: - TINYBLOB and TINYTEXT are for values less than 256 characters long (which is no better than CHAR or VARCHAR in your case) - BLOB and TEXT are for values less than 65536 characters long - MEDIUMBLOB and MEDIUMTEXT are for values less than 16777216 characters long - LONGBLOB and LONGTEXT are for values less than 4294967296 characters long Rhino - Original Message - From: <[EMAIL PROTECTED]> To: Sent: Saturday, April 02, 2005 6:51 AM Subject: Load data infile and text fields First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields < of 256 char, but I cannot import this text field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load data infile and text fields
What is the structure of the table you are importing to? you might have merely hit the natural limit of the column type. - michael dykman On Sat, 2005-04-02 at 06:51, [EMAIL PROTECTED] wrote: > First of all I hope you can be patient for my english > I'm working with data import into mysql from a txt file. I'm using LOAD > DATA INFILE > command but I cannot correctly import a text column of 595 characters. > I receive this (very large) file from an external organization and this > file is made > without separators bitween fields. I know only the exact lenght of each > field. All is fine for fields < of 256 char, but I cannot import this text > field of 595 characters. It's imported truncated at 255th character. > Help me please! > Stefano (osso) -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DESPERATE: Disconnect from Database Question
Michael This is a client side problem. Whatever is pooling/managing your connections is not doing a very good job of it. You haven't mentioned how you are managing your connections within your Tomcat app. I know Tomcat has a managed connection pool but I'm less than familiar with it. Personally, I use a connection manager of my own design for all my Java apps or Servlets. You might get some joy from explicitly closing your Statements when you are done with them, but that is a shot in the dark. You might have better luck on a JDBC list or a Tomcat list; in any case you will need to provide less detail about the nature of the application and more specifics on how you acquire your connections. Feel free to send me that information while you are at it; I might be able to help. - michael dykman On Wed, 2004-09-01 at 15:11, Michael McQuade wrote: > Hi folks, heres a real stupid question, but Im baffled. I am running > MySQL v . 4.1.3b-beta-nt , JVM 1.4.2_05-b04 by SUN, > Tomcat 5.0.27,Windows 2000 Server > > Im attempting to run SERVLETS for my application. > > I have a HTML form to kick off my application in Tomcat App Manager It > has 2 options on it,Company Login and User Login. > Company Login opens a new window to accept the Login-id and password, calls > a Servlet to Open MySql, update it, close it then closes the window > returning to the HTML form > > User Login opens a new window to accept the Login-id and password, calls a > Servlet to Open MySql, update it, close it then continues on into the > application > > My problem is this, I log into the company, it accepts all the data, opens, > updates, closes Myql all with a Sqlstate = 0 (perfect) > > I then log attemp to log into the USER and when it attempts to open the > database, it returns SQLSTATE = 08002 (connection already in use)... Any > other calls to database result in SQLSTATE = 08000 (connection exception) > > if I back out to Tomcat App Manger, Stop the Application, then Start the > application, go back into the HTML screen and choose LOGIN USER, I accept > the variables, open the database, update it, and close it, all with > SQLSTATE = 0. > > It seems, that the connection is not being released after I do updates, I > get a ONE TIME connect, update... > > My code for the disconnect is pretty simple > > commit > > disconnect all > > So I thought, maybe I need a QUIT statement, I changed to.. > > Commit > Quit > Disconnect all > > The QUIT command throws an SQLSTATE = 42000 (invalid syntax) > > Is QUIT not supported under MYSQL > > can anyone ofer me CLUES as to why it may not be Disconnected successfuly > > Im desperate here > > Mike -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie :create table multi, index
- Original Message - From: "Aji Andri" <[EMAIL PROTECTED]> To: Sent: Saturday, April 02, 2005 2:13 AM Subject: Newbie :create table multi, index > hi seniors, > I assume that English is not your first language. "Seniors" means "old people" and some people would be offended by being called old if they are not old ;-) > I'm trying to create a table, here my table > properties, > > create table user ( > UserID int primary, > Password varchar (20), > User_stats int multi > ); > > i'm still confuse in User_stats properti's that is > multi, > what really use 'multi' is ? > and what the conection between primary key and index > I don't know what you are trying to do with the word 'multi'. If you look at the article in the manual that describes CREATE TABLE - http://dev.mysql.com/doc/mysql/en/create-table.html - the word 'multi' is not one that belongs in a CREATE TABLE statement. I assume that 'user_stats' is the name that you want to give to your third column and you want the datatype to be 'int'. That's fine, but I don't see what you want the word 'multi' to do in that statement. The primary key clause in your statement tells MySQL that the columns named in the clause uniquely identify each row in the table. For example, if you say primary key(UserID) you are saying that each row of the table has a different, non-null, value in UserID. In other words, each row in the table can be uniquely identified by the value of UserID; you will never get two or more rows that have the same UserID. This is very important because most updates will be made based on the primary key value and you will want to be sure that only the desired row gets updated. An index is a shortcut that helps the database find rows more quickly. However, if you have no indexes, the database will still be able to find the rows that satisfy your query. An index in a database is very similar to an index in a reference book: if you have an index in a book, you can look up the information you want in the index and you will see that the information can be found on page 27; then, you can go to page 27 and find the exact information. If you don't have an index, you can still find the information but you'll have to read every page of the book to find it, which will usually take much longer. Database designers usually created indexes on the primary keys of tables. In fact, some databases *automatically* build primary key indexes. However, indexes don't have to be unique. You can create indexes on non-unique columns of a table if you like. For instance, if you had a column listing the name of the city in which your customers live, you can create an index on that column. Then, if you want to find all the rows where the city is Tokyo, the index will help the database find all the Tokyo customers faster. Non-unique indexes normally have to be built manually via CREATE INDEX statements. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.1 - Release Date: 01/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
where is my data?
Hi, I have suse 9.2 and MySQL 4.21 My basedir is /var/lib/mysql datadir /data/mysql/mysqldata Both the directories and subdirectories and files are owned by user mysq,l group mysql with rights 755. So I guess I have got the permissions part right or so I hope. Everything was working fine till I decided to move the data directory from its previous location to the above one. I had millions of problems after moving the data directory and the database won't start at all. Just to make things work I made the base and data dirs 777. Finally, I can now start it using `mysql.server start` But the good part ends here. I can only see mysql and test databases and i have to log on as root to mysql. My earlier users and databases won't show. Although I have the datafiles at the specified location. This might be coz I ran mysql_install_db again. If I change the rights of base and data dirs to 755, it won't work. I still can't start using mysqld_safe. My /etc/my.cnf has following: [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock user=mysql datadir=/data/mysql/mysqldata bdb_home=/data/mysql/mysqldata [mysql_server] basedir = /var/lib/mysql [mysql.server] basedir = /var/lib/mysql [mysqld_safe] err-log=/var/lib/mysql/mysqld.log innodb_data_home_dir=/data/mysql/mysqldata innodb_data_file_path=ibdata1:10M:autoextend innodb_log_group_home_dir=/data/mysql/ innodb_log_arch_dir=/data/mysql/ and so on.. anyway, so this my.cnf doesn't seem to make any difference. when i try mysqld_safe & it gives Starting mysqld-max daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 307: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied /usr/bin/mysqld_safe: line 313: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied STOPPING server from pid file /var/lib/mysql/www.eh3.uc.edu.pid tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied 050402 07:39:03 mysqld ended tee: /var/lib/mysql/www.eh3.uc.edu.err: Permission denied So I try mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log & and I get 050402 07:40:29 mysqld started 050402 7:40:29 InnoDB: Started 050402 7:40:29 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist 050402 7:40:29 Aborting 050402 7:40:29 InnoDB: Starting shutdown... 050402 7:40:31 InnoDB: Shutdown completed 050402 7:40:31 /usr/sbin/mysqld-max: Shutdown Complete 050402 07:40:31 mysqld ended and when i try to specify basedir with above command like mysqld_safe --user=mysql --err-log=/var/lib/mysql/mysqld.log --basedir=/var/lib/mysql & i get 050402 07:38:28 mysqld started 050402 7:38:28 Can't find messagefile '/var/lib/mysql/share/mysql/english/errmsg.sys' 050402 7:38:28 Aborting 050402 07:38:28 mysqld ended wonderful From what I understand, mysqld_safe is supposed to read these options from my.cnf. but its not and i have to specify them on command line. I have spent 4 days and sacrificed an enticing surfing trip on this and feel like i m the dumbest guy on face of earth. every problem seem to spawn off a new one as soon as its solved.. I will really appreciate if someone could just point out the exact problem to me and give a direction. Thanks in advance Kaustubh _ Screensavers unlimited! http://www.msn.co.in/Download/screensaver/ Download now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load data infile and text fields
First of all I hope you can be patient for my english I'm working with data import into mysql from a txt file. I'm using LOAD DATA INFILE command but I cannot correctly import a text column of 595 characters. I receive this (very large) file from an external organization and this file is made without separators bitween fields. I know only the exact lenght of each field. All is fine for fields < of 256 char, but I cannot import this text field of 595 characters. It's imported truncated at 255th character. Help me please! Stefano (osso) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
