Re: Another Inserting Multiple Values with Set Problem
Hi Victor,
You have given space after the comma(shown here: ('Small, Medium, XSmall')).
Take out the space and try, it will work.
Example:
mysql> insert into products(sizes) values ('Small,Medium,small,medium');
Query OK, 1 row affected (0.05 sec)
Regards,
Thiyaghu CK
www.mafiree.com
On Fri, Jan 8, 2010 at 12:26 PM, Victor Subervi wrote:
> OK, guys, I'm totally confused:
>
> mysql> insert into products (SKU, Category, Name, Title, Description,
> Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee,
> ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
> "10", "5", "2", "('Small, Medium, XSmall')", "('teal_E2725B, black_FF,
> olive_6B8E23, yellow_9ACD32')");
> Query OK, 1 row affected, 2 warnings (0.00 sec)
>
> mysql> show warnings;
>
> +-+--+---+
> | Level | Code |
> Message |
>
> +-+--+---+
> | Warning | 1265 | Data truncated for column 'sizes' at row
> 1|
> | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
> row 1 |
>
> +-+--+---+
> 2 rows in set (0.00 sec)
>
> mysql> truncate products;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into products (SKU, Category, Name, Title, Description,
> Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee,
> ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
> "10", "5", "2", "('Small', 'Medium', 'XSmall')", "('teal_E2725B',
> 'black_FF', 'olive_6B8E23', 'yellow_9ACD32')");
> Query OK, 1 row affected, 2 warnings (0.00 sec)
>
> mysql> truncate products;
> Query OK, 0 rows affected (0.00 sec)
>
> mysql> insert into products (SKU, Category, Name, Title, Description,
> Price,
> SortFactor, Availability, OutOfStock, Weight, ShipFlatFee,
> ShipPercentPrice,
> ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
> "prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
> "10", "5", "2", "Small, Medium, XSmall", "teal_E2725B, black_FF,
> olive_6B8E23, yellow_9ACD32");
> Query OK, 1 row affected, 2 warnings (0.00 sec)
>
> mysql> insert into products (sizes, colorsShadesNumbersShort) values
> ('Large, Small','aqua_7FFFD4, fuchsia_FF77FF');
> Query OK, 1 row affected, 6 warnings (0.01 sec)
>
> mysql> show warnings;
>
> +-+--+---+
> | Level | Code |
> Message |
>
> +-+--+---+
> | Warning | 1364 | Field 'SKU' doesn't have a default
> value |
> | Warning | 1364 | Field 'Name' doesn't have a default
> value |
> | Warning | 1364 | Field 'Title' doesn't have a default
> value|
> | Warning | 1364 | Field 'Description' doesn't have a default
> value |
> | Warning | 1265 | Data truncated for column 'sizes' at row
> 1|
> | Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
> row 1 |
>
> +-+--+---+
> 6 rows in set (0.00 sec)
>
> mysql> describe products sizes;
>
> +---+--+--+-+-+---+
> | Field |
> Type | Null
> | Key | Default | Extra |
>
> +---+--+--+-+-+---+
> | sizes |
> set('XSmall','Small','Medium','Large','XLarge','XXLarge','XXXLarge') | YES
> | | NULL| |
>
> +---+--+--+-+-+---+
> 1 row in set (0.00 sec)
>
> mysql> describe products colorsShadesNumbersShort;
>
> +--+---+--+-+-+---+
> | Field|
> Type
> | Null | Key | Default | Extra |
>
> +--+---+--+-+-+---+
> | colorsShadesNumbersShort |
>
> set('black_FF','yellow_9ACD32','teal_E2725B','olive_6B8E23','green_00A550','white_0F4D92','silver_708090','red_FE2712','lime_32CD32')
> | YES | | NULL|
Re: Another Inserting Multiple Values with Set Problem
OK, guys, I'm totally confused:
mysql> insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
"prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
"10", "5", "2", "('Small, Medium, XSmall')", "('teal_E2725B, black_FF,
olive_6B8E23, yellow_9ACD32')");
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+-+--+---+
2 rows in set (0.00 sec)
mysql> truncate products;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
"prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
"10", "5", "2", "('Small', 'Medium', 'XSmall')", "('teal_E2725B',
'black_FF', 'olive_6B8E23', 'yellow_9ACD32')");
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> truncate products;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into products (SKU, Category, Name, Title, Description, Price,
SortFactor, Availability, OutOfStock, Weight, ShipFlatFee, ShipPercentPrice,
ShipPercentWeight, sizes, colorsShadesNumbersShort) values ("prodSKU1",
"prodCat1", "name1", "title1", "descr", "12.34", "500", "1", "0", "2.5",
"10", "5", "2", "Small, Medium, XSmall", "teal_E2725B, black_FF,
olive_6B8E23, yellow_9ACD32");
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> insert into products (sizes, colorsShadesNumbersShort) values
('Large, Small','aqua_7FFFD4, fuchsia_FF77FF');
Query OK, 1 row affected, 6 warnings (0.01 sec)
mysql> show warnings;
+-+--+---+
| Level | Code |
Message |
+-+--+---+
| Warning | 1364 | Field 'SKU' doesn't have a default
value |
| Warning | 1364 | Field 'Name' doesn't have a default
value |
| Warning | 1364 | Field 'Title' doesn't have a default
value|
| Warning | 1364 | Field 'Description' doesn't have a default
value |
| Warning | 1265 | Data truncated for column 'sizes' at row
1|
| Warning | 1265 | Data truncated for column 'colorsShadesNumbersShort' at
row 1 |
+-+--+---+
6 rows in set (0.00 sec)
mysql> describe products sizes;
+---+--+--+-+-+---+
| Field |
Type | Null
| Key | Default | Extra |
+---+--+--+-+-+---+
| sizes |
set('XSmall','Small','Medium','Large','XLarge','XXLarge','XXXLarge') | YES
| | NULL| |
+---+--+--+-+-+---+
1 row in set (0.00 sec)
mysql> describe products colorsShadesNumbersShort;
+--+---+--+-+-+---+
| Field|
Type
| Null | Key | Default | Extra |
+--+---+--+-+-+---+
| colorsShadesNumbersShort |
set('black_FF','yellow_9ACD32','teal_E2725B','olive_6B8E23','green_00A550','white_0F4D92','silver_708090','red_FE2712','lime_32CD32')
| YES | | NULL| |
+--+---+--+-+-+---+
1 row in set (0.00 sec)
How, o how, do I insert into sets???
TIA,
V
RE: table export in cron
On Thu, 7 Jan 2010 15:48:24 +0200, "machiel.richards" wrote: > Hi All > > What the Oracle guys found was that some of the fields were blank even > though the fields were configured as not nullable. > > I found that where this is the case, the users entered a blank space > which is then counted as a character and is thus not null. > > This is causing all kinds of havoc for them as Oracle apparently still > sees this as nulls. I'm not sure what export method you're using, but you should be able to fix this by specifying to quote fields. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Probability Selects
On 1/7/10 3:10 PM, "Matt Neimeyer" wrote: > I'm trying to select names at random from a table that contains the > name and the frequency at which it is actually used in society. The > table is defined as follows: > > CREATE TABLE `MaleNames` ( > `Name_ID` int(11) NOT NULL auto_increment, > `Name` char(50) default NULL, > `Frequency` decimal(5,3) default NULL, > PRIMARY KEY (`Name_ID`) > ) > > Some examples: > > 1, Aaron, 0.240 > 3, Abe, 0.006 > 13, Adrian, 0.069 > > What's the best way to select names at random from this but still take > into account frequency of use? after reading the source, i'd avoid using MySQL's RAND(). you can probably easily get better quality pseudorandom number in your app's environment. to get a random row, "LIMIT 1, r", where offset r is a random number between 0 and (tablelength - 1), should work. if you must use RAND(), FLOOR(tablelength*RAND()) will work for r. if you want a random row from a constrained subset of rows based on frequency, e.g. "WHERE Frequency > 0.001", you can include that condition in the query and repeat it until you get a non-empty response. but if the constrained subset is only a small fraction of the table's rows then it might be more efficient to compute its size (with COUNT() in another query or a subquery) and use that in generating the offset r. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Probability Selects
http://www.greggdev.com/web/articles.php?id=6 > -Original Message- > From: Matt Neimeyer [mailto:m...@neimeyer.org] > Sent: Thursday, January 07, 2010 12:10 PM > To: mysql@lists.mysql.com > Subject: Probability Selects > > I've tried Googling till my brain is fried and I'm obviously missing > something because I'm not finding anything useful. > > I'm trying to select names at random from a table that contains the > name and the frequency at which it is actually used in society. The > table is defined as follows: > > CREATE TABLE `MaleNames` ( > `Name_ID` int(11) NOT NULL auto_increment, > `Name` char(50) default NULL, > `Frequency` decimal(5,3) default NULL, > PRIMARY KEY (`Name_ID`) > ) > > Some examples: > > 1, Aaron, 0.240 > 3, Abe, 0.006 > 13, Adrian, 0.069 > > What's the best way to select names at random from this but still take > into account frequency of use? > > Thanks in advance! > > Matt > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=dae...@daevid.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Probability Selects
I've tried Googling till my brain is fried and I'm obviously missing something because I'm not finding anything useful. I'm trying to select names at random from a table that contains the name and the frequency at which it is actually used in society. The table is defined as follows: CREATE TABLE `MaleNames` ( `Name_ID` int(11) NOT NULL auto_increment, `Name` char(50) default NULL, `Frequency` decimal(5,3) default NULL, PRIMARY KEY (`Name_ID`) ) Some examples: 1, Aaron, 0.240 3, Abe, 0.006 13, Adrian, 0.069 What's the best way to select names at random from this but still take into account frequency of use? Thanks in advance! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: table export in cron
Hi Machiel, all! machiel.richards wrote: > Hi All > > [[...]] > > What the Oracle guys found was that some of the fields were blank even > though the fields were configured as not nullable. > > I found that where this is the case, the users entered a blank space > which is then counted as a character and is thus not null. > > This is causing all kinds of havoc for them as Oracle apparently still > sees this as nulls. In SQL, both an empty string "" and a string with just a blank " " are well-defined values, different from the unknown NULL. AFAIR, Oracle had a tradition of treating empty and/or blank strings as NULL. I don't know whether that still holds, and whether it can be influenced by some setting. Which value would be used on the Oracle side for such fields? You could always import into a stage table on the Oracle side which allows NULL, then update to some non-NULL default value, then transfer into the final one. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: table export in cron
Hi All Well I had some fun writing this cron , easy enough with all the help you guys gave me... Just some interesting info I want to share, maybe someone came across this and can also give some thoughts... What the Oracle guys found was that some of the fields were blank even though the fields were configured as not nullable. I found that where this is the case, the users entered a blank space which is then counted as a character and is thus not null. This is causing all kinds of havoc for them as Oracle apparently still sees this as nulls. Regards -Original Message- From: Nicola Salvemini [mailto:nicolasalvem...@yahoo.it] Sent: 07 January 2010 3:45 PM To: sureshkumar...@gmail.com; Mikhail Berman; machiel.richards Cc: mysql@lists.mysql.com Subject: Re: table export in cron - machiel.richards wrote > > How can we do this when running in a cron script? > Hi! You can use following command in a shell script running from cron: echo "select * from into outfile '/path/to/output/file' fields terminated by '|';" | mysql --user= --password= ; Bye! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: table export in cron
- machiel.richards wrote > > How can we do this when running in a cron script? > Hi! You can use following command in a shell script running from cron: echo "select * from into outfile '/path/to/output/file' fields terminated by '|';" | mysql --user= --password= ; Bye! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Are there any difference between max_connection and max_user_connection?
It should be identical select statements which will improve performance and not the updates. Go through the below url for more information about optimization and performance http://dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html On Thu, Jan 7, 2010 at 4:42 PM, F.A.I.Z.A.L wrote: > hi > > i can see many 'updates' is going every min. so is it good to increase this > query_cache_size ?.. > > what are the other action i can take to improve the performance of mysql > server > > environment > version : 5.0.22 > platform : redhat 5 > > > > Cheers > Faizal S > GSM : 9840118673 > Blog: http://oradbapro.blogspot.com > > > -- Thanks Suresh Kuna MySQL DBA
Re: Are there any difference between max_connection and max_user_connection?
hi i can see many 'updates' is going every min. so is it good to increase this query_cache_size ?.. what are the other action i can take to improve the performance of mysql server environment version : 5.0.22 platform : redhat 5 Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
Re: Are there any difference between max_connection and max_user_connection?
If the queries are identical then Yes. If not it will degrade the query performance. Sent from BlackBerry® on Airtel -Original Message- From: "F.A.I.Z.A.L" Date: Thu, 7 Jan 2010 15:51:19 To: Suresh Kuna Cc: Subject: Re: Are there any difference between max_connection and max_user_connection? hi suresh every 1 min nealy 40 to 80 users will hit the database. so, if i increase query_cache_size it will avoid physical i/o and buffer missing? Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Jan 7, 2010 at 1:23 PM, Suresh Kuna wrote: > Yes it won't allow except 1 connection for root user. > We have to monitor the MySQLD server and the queries runnning at that time. > As of now, increase the key_buffer_size to 64 or 128 mb according to your > indexes created and physical memory available. > and query_cache_size is purely depends on the queries that your application > hits the database. > > > > > On Thu, Jan 7, 2010 at 12:20 PM, F.A.I.Z.A.L wrote: > >> hi Suresh >> >> thanks. max_connection=100(default). so, it won't allow user more than >> 100? >> >> and one more clarification. i am facing performance issue in mysqld. some >> time mysqld consuming 100% cpu. so what i have to do now? >> >> and the below parameters are in default values. if i increase the value. >> it will help performance? >> >> query_cache_size = 0 >> key_buffer_size = 8m >> sort_buffer_size = 2m >> innodb_buffer_pool_size = 8m >> >> read_buffer = 2M >> write_buffer = 2M >> >> >> thanks in advance.. >> >> Cheers >> Faizal S >> GSM : 9840118673 >> Blog: http://oradbapro.blogspot.com >> >> >> On Thu, Jan 7, 2010 at 11:27 AM, Suresh Kuna wrote: >> >>> Hi Faizal, >>> Max_connections are the connections for the overall MySQLD server. >>> Max_user_connections are the connections for the particular user ( i.e >>> for an account ) of the MySQLD server. >>> >>> Suresh Kuna >>> MySQL DBA >>> >>> On Thu, Jan 7, 2010 at 11:00 AM, F.A.I.Z.A.L wrote: >>> Dear all I can see max_connection and max_user_connection set to default value. but daily i can see 40 to 70 users connection to the database. how? when max_user_connections=0. what is the different between these two parameters.. max_connection=100 max_user_connections=0 thanks in advance. Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com >>> >>> >>> >>> -- >>> Thanks >>> Suresh Kuna >>> MySQL DBA >>> >> >> > > > -- > Thanks > Suresh Kuna > MySQL DBA >
Re: Are there any difference between max_connection and max_user_connection?
hi suresh every 1 min nealy 40 to 80 users will hit the database. so, if i increase query_cache_size it will avoid physical i/o and buffer missing? Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Thu, Jan 7, 2010 at 1:23 PM, Suresh Kuna wrote: > Yes it won't allow except 1 connection for root user. > We have to monitor the MySQLD server and the queries runnning at that time. > As of now, increase the key_buffer_size to 64 or 128 mb according to your > indexes created and physical memory available. > and query_cache_size is purely depends on the queries that your application > hits the database. > > > > > On Thu, Jan 7, 2010 at 12:20 PM, F.A.I.Z.A.L wrote: > >> hi Suresh >> >> thanks. max_connection=100(default). so, it won't allow user more than >> 100? >> >> and one more clarification. i am facing performance issue in mysqld. some >> time mysqld consuming 100% cpu. so what i have to do now? >> >> and the below parameters are in default values. if i increase the value. >> it will help performance? >> >> query_cache_size = 0 >> key_buffer_size = 8m >> sort_buffer_size = 2m >> innodb_buffer_pool_size = 8m >> >> read_buffer = 2M >> write_buffer = 2M >> >> >> thanks in advance.. >> >> Cheers >> Faizal S >> GSM : 9840118673 >> Blog: http://oradbapro.blogspot.com >> >> >> On Thu, Jan 7, 2010 at 11:27 AM, Suresh Kuna wrote: >> >>> Hi Faizal, >>> Max_connections are the connections for the overall MySQLD server. >>> Max_user_connections are the connections for the particular user ( i.e >>> for an account ) of the MySQLD server. >>> >>> Suresh Kuna >>> MySQL DBA >>> >>> On Thu, Jan 7, 2010 at 11:00 AM, F.A.I.Z.A.L wrote: >>> Dear all I can see max_connection and max_user_connection set to default value. but daily i can see 40 to 70 users connection to the database. how? when max_user_connections=0. what is the different between these two parameters.. max_connection=100 max_user_connections=0 thanks in advance. Cheers Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com >>> >>> >>> >>> -- >>> Thanks >>> Suresh Kuna >>> MySQL DBA >>> >> >> > > > -- > Thanks > Suresh Kuna > MySQL DBA >
