Re: why is this not an error?
In the last episode (Aug 29), Jason Pyeron said: Why is this not an error? mysql select count(*) paths; +---+ | paths | +---+ | 0 | +---+ 1 row in set (0.00 sec) Why should it be? You've simply omitted the optional AS keyword in a standard no-table SELECT query. There's no table in the query, so count(*) returns 0 (I suppose you could argue it should return NULL), and you've renamed the column, so that's why MySQL calls it paths in the output. It's no different from SELECT version() v; ++ | v | ++ | 4.1.12 | ++ 1 row in set (0.00 sec) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES_DECRYPT output
Hi,
I use the following to connect my application to the MySQL server:
Public ConnectionString As String = driver={MySQL ODBC 3.51
Driver};server=localhost;uid=root;database=mydatabase
As you can see the version of the Connector is 3.51. I use MySQL version
4.1.11.
Martin
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: AES_DECRYPT output
Date: Fri, 26 Aug 2005 17:14:28 +0300
Hello.
How does your application connect to MySQL server (MyODBC or
Connector/Net)? What versions of MySQL Server and Connector do you
use?
M DR [EMAIL PROTECTED] wrote:
Hi,
I'm currently using AES Encryption to encrypt some of my data (using it
in a
ASP.NET application). As this is all text strings, I was wondering if
there
is a possibility to automatically have the output in the ASP.NET String
format instead of the system.byte[] format, so I won't have to format
every
item out of the database.
For example a query like:
SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename ORDER BY Field_ID
returns the system.byte[] format, while I'd like to see the String
format as
output. Is this possible?
Btw, the strange thing is, when I don't include a ORDER BY (or GROUP BY)
function, it already returns a String format. Any idea how this is
possible?
Thanks,
Martin
_
MSN Webmessenger doet het altijd en overal http://webmessenger.msn.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]
_
Gebruik MSN Webmessenger op je werk en op school
http://webmessenger.msn.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mark a single row?
Hallo, I would like a single row in a table, and not more than one, to be used as a preferred value in another application. Is this possible, and, if yes, how? Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mark a single row?
[EMAIL PROTECTED] wrote: Hallo, I would like a single row in a table, and not more than one, to be used as a preferred value in another application. Is this possible, and, if yes, how? add a column 'preferred', tinyint(1) NOT NULL. To change the preferred value: START TRANSACTION UPDATE table SET preferred=0 UPDATE table SET preferred=1 WHERE column_id=1234 COMMIT -- Jasper Bryant-Greene Freelance web developer http://jasper.bryant-greene.name/ If you find my advice useful, please consider donating to a poor student! You can choose whatever amount you think my advice was worth to you. http://tinyurl.com/7oa5s -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Performance Problems - Help !!
Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Performance Problems - Help !!
my.cnf add this: skip-name-resolve under [mysqld] On 8/29/05, Callum McGillivray [EMAIL PROTECTED] wrote: Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Best Wishes, Xiaodong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Hello. Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Jan Roehrich [EMAIL PROTECTED] wrote: I have a strange problem setting up replication. I followed each step of setting up replication in the mysqld documentation (http://dev.mysql.com/doc/mysql/en/replication-howto.html). But there are some error messages on the salve: 050828 13:22:58 [ERROR] Slave I/O thread: error connecting to master '[EMAIL PROTECTED]:3306': Error: 'Can't connect to MySQL server on 'alturo.vlugnet.org' (13)' errno: 2003 retry-time: 60 retries: 86400 When I try to connect to the master using same hostname, same user, same password with the mysql client it works without problems. I tried to drag the problem with ethereal. It seems that the slave doesn't even try to connect to the master. There are not even connection attempts between master and slave on port 3306. Also have a look at http://forums.mysql.com/read.php?26,36766 it seems that he has the same problem. As slave I'm using mysql version 4.1.12 on Fedora Core 4. Master is 3.23.58 on Fedora Core 2. Any ideas? Regards Jan -- 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: AES_DECRYPT output
Hello.
Your versions seem to be rather fresh. If you are able to make a
reproducible test case you may want to report a bug at:
http://bugs.mysql.com
M DR [EMAIL PROTECTED] wrote:
Hi,
I use the following to connect my application to the MySQL server:
Public ConnectionString As String = driver={MySQL ODBC 3.51
Driver};server=localhost;uid=root;database=mydatabase
As you can see the version of the Connector is 3.51. I use MySQL version
4.1.11.
Martin
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: AES_DECRYPT output
Date: Fri, 26 Aug 2005 17:14:28 +0300
Hello.
How does your application connect to MySQL server (MyODBC or
Connector/Net)? What versions of MySQL Server and Connector do you
use?
M DR [EMAIL PROTECTED] wrote:
Hi,
I'm currently using AES Encryption to encrypt some of my data (using it
in a
ASP.NET application). As this is all text strings, I was wondering if
there
is a possibility to automatically have the output in the ASP.NET String
format instead of the system.byte[] format, so I won't have to format
every
item out of the database.
For example a query like:
SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename ORDER BY Field_ID
returns the system.byte[] format, while I'd like to see the String
format as
output. Is this possible?
Btw, the strange thing is, when I don't include a ORDER BY (or GROUP BY)
function, it already returns a String format. Any idea how this is
possible?
Thanks,
Martin
_
MSN Webmessenger doet het altijd en overal http://webmessenger.msn.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]
_
Gebruik MSN Webmessenger op je werk en op school
http://webmessenger.msn.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: Key Buffer Size
Hello. =09To give you an idea of my database, I use Linux box with 6GB ram, MySQL version 4.0.15. Most of my configuration is in lines with my-huge.cnf. Your version of MySQL is rather old, I strongly recommend you to upgrade to the latest release (4.1.14 now). If your server has x86 architecture, your mysqld process might not be able to use all 6G of your RAM. Greetings, =09In my database, all the tables use innodb engine. Since I don't use MYISAM, Is it safe to make the key_buffer size to 8 MB? In my opinion, it is safe. Manoj [EMAIL PROTECTED] wrote: Greetings, =09In my database, all the tables use innodb engine. Since I don't use MYISAM, Is it safe to make the key_buffer size to 8 MB? =09To give you an idea of my database, I use Linux box with 6GB ram, MySQL version 4.0.15. Most of my configuration is in lines with my-huge.cnf. Thanks in advance for your help. Cheers Manoj -- 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: [Replication] Problem connecting to master
Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Master: -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=bin-log server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Slave: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host=82.165.25.207 master-port=3306 master-user=replication master-password=hw4Gabfy [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- Could not see skip-networking anywhere. Greetings Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
not null values
i want to make it so that i will have to fill in all the attributes in .so i put not null for all the attributes. Is this correct ?? when for example do not fill in user_name , all the other values is accepted and stored in the databaseplease advise create table user_details (user_id varchar(10) not null primary key, user_name varchar(50) not null, user_email varchar (30) not null, user_contact int not null, user_username varchar (20) not null, user_password varchar (20) not null ); thank you... 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]
RE: not null values
Use the keyword 'default' to make the attributes default to the values you want. Use some thing like this user_name varchar(50) default 'default_value' not null sujay -Original Message- From: joshua pereira [mailto:[EMAIL PROTECTED] Sent: Monday, August 29, 2005 5:24 PM To: mysql@lists.mysql.com Subject: not null values i want to make it so that i will have to fill in all the attributes in .so i put not null for all the attributes. Is this correct ?? when for example do not fill in user_name , all the other values is accepted and stored in the databaseplease advise create table user_details (user_id varchar(10) not null primary key, user_name varchar(50) not null, user_email varchar (30) not null, user_contact int not null, user_username varchar (20) not null, user_password varchar (20) not null ); thank you... 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]
manual is garbled
Hi, MySQL online manual of Japanese edition is garbled. http://dev.mysql.com/doc/mysql/ja/index.html I think the manuals other than English are garbled. What'a happen? Hirofumi Fujiwara[EMAIL PROTECTED], [EMAIL PROTECTED] Time Intermedia Corporationhttp://www.timedia.co.jp/ Corporate Strategy Department Knowledge Engineering Center 26-27 Saka-machi Shinjuku-ku, Tokyo 160-0002 Japan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not null values
joshua pereira wrote: i want to make it so that i will have to fill in all the attributes in .so i put not null for all the attributes. Is this correct ?? when for example do not fill in user_name , all the other values is accepted and stored in the databaseplease advise create table user_details (user_id varchar(10) not null primary key, user_name varchar(50) not null, user_email varchar (30) not null, user_contact int not null, user_username varchar (20) not null, user_password varchar (20) not null ); thank you... Perhaps you were expecting an error? You should read http://dev.mysql.com/doc/mysql/en/constraint-invalid-data.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump: Error 2013
Hello I am using mysqldump to backup my entire database (about 40GB total) each night. I dump each table separetly, so that if mysqldump crashes in the middle somewhere, the rest of the database still gets backed up. Most of the tables are fairly small. About 20GB of the database is spread across more than a hundred tables. However, one table is very large - it accounts for the other 20GB of the dataset. When backing up this table, I get this error message every night: /usr/local/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `DocumentVariants` at row: 13456 The table actually has 94,916 rows in it. There are no entries in the mySQL server log and nothing in /var/log/messages. There is plenty of disk space available on the backup drive. The file is about 4.5GB when this happens, which is about 1/5 of the total table size. The table itself contains a lot of binary data in a longblob field, in case that makes any difference. wait_timeout on my server is set to 86400, and the whole backup takes less than an hour, so the timeout is not the problem. Has anyone else had similar problems? Can anyone shed some light on how to successfully back up this database? Thanks! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ smime.p7s Description: S/MIME cryptographic signature
Re: [Replication] Problem connecting to master
Hello. Could not see skip-networking anywhere. --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. Jan Roehrich [EMAIL PROTECTED] wrote: Please send your configuration files for master and slave. Check that you don't have skip-networking in your slave's configuration file. Master: -- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-bin=bin-log server-id=1 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid --- Slave: --- [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock old_passwords=1 server-id=2 master-host=82.165.25.207 master-port=3306 master-user=replication master-password=hw4Gabfy [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid -- Could not see skip-networking anywhere. Greetings Jan -- 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: mysqldump: Error 2013
Gustafson, Tim [EMAIL PROTECTED] wrote on 08/29/2005 09:24:36 AM: Hello I am using mysqldump to backup my entire database (about 40GB total) each night. I dump each table separetly, so that if mysqldump crashes in the middle somewhere, the rest of the database still gets backed up. Most of the tables are fairly small. About 20GB of the database is spread across more than a hundred tables. However, one table is very large - it accounts for the other 20GB of the dataset. When backing up this table, I get this error message every night: /usr/local/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `DocumentVariants` at row: 13456 The table actually has 94,916 rows in it. There are no entries in the mySQL server log and nothing in /var/log/messages. There is plenty of disk space available on the backup drive. The file is about 4.5GB when this happens, which is about 1/5 of the total table size. The table itself contains a lot of binary data in a longblob field, in case that makes any difference. wait_timeout on my server is set to 86400, and the whole backup takes less than an hour, so the timeout is not the problem. Has anyone else had similar problems? Can anyone shed some light on how to successfully back up this database? Thanks! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ The one thing I can think of is to check that you are not trying to buffer your output. Use the quick option when you start mysqldump (to skip memory buffering the dump file) and write the data straight to disk as it arrives. With a 20GB file it will be very easy to exceed available system memory allocation limits. With the buffering turned off, you shouldn't hit that limit. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: mysqldump: Error 2013
Shawn, Thanks. I should have included the switches I was using to make the backup. I'm using --opt --quote-names, and according to the manual, --opt includes --quick. Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, August 29, 2005 9:35 AM To: Gustafson, Tim Cc: mysql@lists.mysql.com Subject: Re: mysqldump: Error 2013 Gustafson, Tim [EMAIL PROTECTED] wrote on 08/29/2005 09:24:36 AM: Hello I am using mysqldump to backup my entire database (about 40GB total) each night. I dump each table separetly, so that if mysqldump crashes in the middle somewhere, the rest of the database still gets backed up. Most of the tables are fairly small. About 20GB of the database is spread across more than a hundred tables. However, one table is very large - it accounts for the other 20GB of the dataset. When backing up this table, I get this error message every night: /usr/local/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `DocumentVariants` at row: 13456 The table actually has 94,916 rows in it. There are no entries in the mySQL server log and nothing in /var/log/messages. There is plenty of disk space available on the backup drive. The file is about 4.5GB when this happens, which is about 1/5 of the total table size. The table itself contains a lot of binary data in a longblob field, in case that makes any difference. wait_timeout on my server is set to 86400, and the whole backup takes less than an hour, so the timeout is not the problem. Has anyone else had similar problems? Can anyone shed some light on how to successfully back up this database? Thanks! Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ The one thing I can think of is to check that you are not trying to buffer your output. Use the quick option when you start mysqldump (to skip memory buffering the dump file) and write the data straight to disk as it arrives. With a 20GB file it will be very easy to exceed available system memory allocation limits. With the buffering turned off, you shouldn't hit that limit. Shawn Green Database Administrator Unimin Corporation - Spruce Pine smime.p7s Description: S/MIME cryptographic signature
Re: mysqldump: Error 2013
Gustafson, Tim wrote: When backing up this table, I get this error message every night: /usr/local/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `DocumentVariants` at row: 13456 The table actually has 94,916 rows in it. There are no entries in the mySQL server log and nothing in /var/log/messages. There is plenty of disk space available on the backup drive. The file is about 4.5GB when this happens, which is about 1/5 of the total table size. The table itself contains a lot of binary data in a longblob field, in case that makes any difference. Does the size of the contents of that field exceed your defined max_allowed_packet size? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump: Error 2013
No, max_allowed_packet is 100 megabytes, and the maximum data field in the database is 50MB right now, and most are well below 10MB. Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Monday, August 29, 2005 9:51 AM To: mysql@lists.mysql.com Subject: Re: mysqldump: Error 2013 Gustafson, Tim wrote: When backing up this table, I get this error message every night: /usr/local/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `DocumentVariants` at row: 13456 The table actually has 94,916 rows in it. There are no entries in the mySQL server log and nothing in /var/log/messages. There is plenty of disk space available on the backup drive. The file is about 4.5GB when this happens, which is about 1/5 of the total table size. The table itself contains a lot of binary data in a longblob field, in case that makes any difference. Does the size of the contents of that field exceed your defined max_allowed_packet size? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
Re: Mark a single row?
Hallo Jasper, Op 29 Aug 05 schreef Jasper Bryant-Greene aan mysql: I would like a single row in a table, and not more than one, to be used as a preferred value in another application. JBG add a column 'preferred', tinyint(1) NOT NULL. Thank you! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
--skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Error 2013
Gustafson, Tim wrote: No, max_allowed_packet is 100 megabytes, and the maximum data field in the database is 50MB right now, and most are well below 10MB. mmm. OK, not having any more bright ideas :-) I would try dumping it using an explicit `--where=` claus to guarantee fixed ordering, and see if it fails on the same row every time. HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] Problem connecting to master
Jan Roehrich wrote: --skip-networking can be specified as the command line option. Use: show variables like 'skip_networking'; to check if it is enabled. mysql show variables like 'skip_networking'; +-+---+ | Variable_name | Value | +-+---+ | skip_networking | OFF | +-+---+ It can be a firewall problem... Are you sure that the slave didn't try to connect to the master, and got no response, or got a connection refused? Check the bin-log of the slave for errors in the bin-log. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too Many Queries Have Writing To Net Status
Hi All, We have a production Database, running mysql 4.0.17 , on a Poweredge 2650 with 3 GB RAM, and dual Xeon 2.4 GHZ, the server averages between 100 and 200 qps ( ,also and the CPU/MEM load is pretty low and is extremely fast except for once in a while may be 1 in 1 queries take mote than 3 seconds to execute (slow_query_time), and the status of the query is always writing to net Initially the NIC was an Intel 10/100, then the slow queries were a lot more, then I started using the Broadcom Gigabit port that comes with the 2650, and recently I also started load balancing the traffic between the two Broadcomm NIC'S using the Basp Module, so that I effectively have a 200 MBPS full duplex link( the switch connected has only FE ports), with this setting the slow queries have decreased a lot, but they do come up once in a while.. There is no lock time in any of them, I have tried using both InnoDB and MyISAM, but they dont seem to go away, so I was wondering why there are still few queries which still have writing to Net in the status line and sometimes take 30-50 seconds to execute, note that if the queries are run in any mysql client they run in less than 0.005 seconds, so I am pretty sure its the network thats the bottleneck, and buying a Gigabit capable switch is not an option now, also sometimes the traffic on the server goes upto 35MBPS. So why is mysql not sending the results back to the client and sometimes waiting soo long, also while mysql is waiting for a long query( in the Writing to Net status) hundreds of other queries are executed extrelmely fast. I am not very familiar with how mysql handles network packets, and why its holding back ... Any help would be appreciated .. Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Error 2013
Hello. Has anyone else had similar problems? Can anyone shed some light on how to successfully back up this database? Have a look here: http://dev.mysql.com/doc/mysql/en/gone-away.html Hello I am using mysqldump to backup my entire database (about 40GB total) each night. I dump each table separetly, so that if mysqldump crashes in the middle somewhere, the rest of the database still gets backed up. Most of the tables are fairly small. About 20GB of the database is spread across more than a hundred tables. However, one table is very large - it accounts for the other 20GB of the dataset. When backing up this table, I get this error message every night: /usr/local/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `DocumentVariants` at row: 13456 The table actually has 94,916 rows in it. There are no entries in the mySQL server log and nothing in /var/log/messages. There is plenty of disk space available on the backup drive. The file is about 4.5GB when this happens, which is about 1/5 of the total table size. The table itself contains a lot of binary data in a longblob field, in case that makes any difference. wait_timeout on my server is set to 86400, and the whole backup takes less than an hour, so the timeout is not the problem. Has anyone else had similar problems? Can anyone shed some light on how to successfully back up this database? Thanks! Gustafson, Tim [EMAIL PROTECTED] wrote: -- 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]
Incorrect column name
I have a database on a mysql server (version 4.0.20-max) with this table: mysql show create table Dsmaccnt\G *** 1. row *** Table: Dsmaccnt Create Table: CREATE TABLE `Dsmaccnt` ( ... ... `Termination ` tinyint(3) unsigned NOT NULL default '0', ... ... ) TYPE=MyISAM 1 row in set (0.01 sec) mysql The field `Termination ` cause me some trouble: If i try to recreate from dump (mysqldump from 4.0) the table 'Dsmaccnt', on another server (version 4.1), i've got this error: ERROR 1166: Incorrect column name 'Termination ' On the same server (4.0.20-max) if i try to create a dummy table with same blank-added field i've got same error: mysql create table prova (`Termination ` int); ERROR 1166: Incorrect column name 'Termination ' mysql How is possible that i have this table with this field? (The database was created by others admin, not by me). There are some particular SQL mode that permit this? Thank in advance. Francesco. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AES_DECRYPT output
Hi,
Thanks for your answers,
What is according to you the bug? Should it always return a string format
value? Or should it always return a system.byte[] format value?
And I guess you also refer to the strange behavior that it's different when
adding an ORDER BY or GROUP BY, right?
Kind regards,
Martin
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: AES_DECRYPT output
Date: Mon, 29 Aug 2005 11:27:08 +0300
Hello.
Your versions seem to be rather fresh. If you are able to make a
reproducible test case you may want to report a bug at:
http://bugs.mysql.com
M DR [EMAIL PROTECTED] wrote:
Hi,
I use the following to connect my application to the MySQL server:
Public ConnectionString As String = driver={MySQL ODBC 3.51
Driver};server=localhost;uid=root;database=mydatabase
As you can see the version of the Connector is 3.51. I use MySQL version
4.1.11.
Martin
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: AES_DECRYPT output
Date: Fri, 26 Aug 2005 17:14:28 +0300
Hello.
How does your application connect to MySQL server (MyODBC or
Connector/Net)? What versions of MySQL Server and Connector do you
use?
M DR [EMAIL PROTECTED] wrote:
Hi,
I'm currently using AES Encryption to encrypt some of my data (using
it
in a
ASP.NET application). As this is all text strings, I was wondering if
there
is a possibility to automatically have the output in the ASP.NET
String
format instead of the system.byte[] format, so I won't have to format
every
item out of the database.
For example a query like:
SELECT AES_DECRYPT(Fieldname, '123') FROM Tablename ORDER BY Field_ID
returns the system.byte[] format, while I'd like to see the String
format as
output. Is this possible?
Btw, the strange thing is, when I don't include a ORDER BY (or GROUP
BY)
function, it already returns a String format. Any idea how this is
possible?
Thanks,
Martin
_
MSN Webmessenger doet het altijd en overal
http://webmessenger.msn.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]
_
Gebruik MSN Webmessenger op je werk en op school
http://webmessenger.msn.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]
_
Speel online games via MSN Messenger http://messenger.msn.nl/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect column name
Francesco Dalla Ca' wrote: I have a database on a mysql server (version 4.0.20-max) with this table: mysql show create table Dsmaccnt\G *** 1. row *** Table: Dsmaccnt Create Table: CREATE TABLE `Dsmaccnt` ( ... ... `Termination ` tinyint(3) unsigned NOT NULL default '0', ... ... ) TYPE=MyISAM 1 row in set (0.01 sec) mysql The field `Termination ` cause me some trouble: If i try to recreate from dump (mysqldump from 4.0) the table 'Dsmaccnt', on another server (version 4.1), i've got this error: ERROR 1166: Incorrect column name 'Termination ' On the same server (4.0.20-max) if i try to create a dummy table with same blank-added field i've got same error: mysql create table prova (`Termination ` int); ERROR 1166: Incorrect column name 'Termination ' mysql How is possible that i have this table with this field? (The database was created by others admin, not by me). There are some particular SQL mode that permit this? Thank in advance. Francesco. There was a bug (See http://bugs.mysql.com/bug.php?id=3914 and http://bugs.mysql.com/bug.php?id=2985) that allowed such names. It was fixed in 4.1.19 http://dev.mysql.com/doc/mysql/en/news-4-0-19.html. I expect this column was created while you were running 4.1.18 or earlier, before you upgraded. I suppose your best bet is to fix the column name. Something like ALTER TABLE Dsmaccnt CHANGE `Termination ` Termination tinyint(3) unsigned NOT NULL default '0'; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Incorrect column name
mysql create table prova (`Termination ` int); ERROR 1166: Incorrect column name 'Termination ' The manual says: Database, table, and column names should not end with space characters. http://dev.mysql.com/doc/mysql/en/legal-names.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Error 2013
Hassan Schroeder wrote: Does the size of the contents of that field exceed your defined max_allowed_packet size? Gustafson, Tim wrote: No, max_allowed_packet is 100 megabytes, and the maximum data field in the database is 50MB right now, and most are well below 10MB. Tim Gustafson MEI Technology Consulting, Inc [EMAIL PROTECTED] (516) 379-0001 Office (516) 908-4185 Fax http://www.meitech.com/ I believe it's the size of the row, not the size of a single field, that matters. Is it possible you have a row which exceeds max_allowed_packet size? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump: Error 2013
I believe it's the size of the row, not the size of a single field, that matters. Is it possible you have a row which exceeds max_allowed_packet size? No. There is one blob fields (always less than 50MB) and like 10 other fields, all integers. smime.p7s Description: S/MIME cryptographic signature
Re: mySQL Performance Problems - Help !!
If you suddenly are spiking in unauthenticated connections, you may be the target of a network attack. This could be just a random probe, you may be a random target or someone may be targeting you. Although if someone were specifically targeting you, you would probably be down. I would check where the connections are coming from, especially if this amount of traffic is not typical. Perhaps you can set a filter on your router to limit the number of connections from that IP range or deny that range altogether. Normally you don't allow direct access to MySQL from the outside, access is usually done through SendMail/ Postifx or Apache (PHP, Perl, etc), but your requirements may be different. On Aug 29, 2005, at 4:58 AM, Callum McGillivray wrote: Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- 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]
Index update process 20+ hrs
Guys, I have a huge table to which I'm attempting to update the foreign key and index. It is taking me more than 20 hrs to complete the process and would like to know if someone can point me in the right direction. Please let me know of any additional information that I should provide. mysql show index from parts; +---++---+--+---+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+---+---+-+--++--++-+ | parts | 0 | PRIMARY |1 | prtPNID | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|1 | prtPN | A | 326773 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|3 | prtMfg| A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtSort |1 | prtSort | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |1 | prtSKU| A | 18 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |2 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|1 | prtStripped | A | 230663 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|3 | prtMfg| A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |1 | prtDesc | A | 301636 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtWrtyTypeID |1 | prtWrtyTypeID | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |1 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |2 | prtMfg| A | 46132 | NULL | NULL | | BTREE | | +---++---+--+---+---+-+--++--++-+ mysql explain select * from parts; ++-+---+--+---+--+-+--+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+-+--+-+---+ | 1 | SIMPLE | parts | ALL | NULL | NULL |NULL | NULL | 3921279 | | ++-+---+--+---+--+-+--+-+---+ 1 row in set (0.00 sec) Files from mysqldata directory -rw-rw 1 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 8.9G Aug 29 12:32 parts.ibd DB MySQL: 4.1.11 OS: Solaris 9 Hardware: SUN 2900; 32GB RAM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
Clyde Lewis [EMAIL PROTECTED] wrote on 08/29/2005 01:47:11 PM: Guys, I have a huge table to which I'm attempting to update the foreign key and index. It is taking me more than 20 hrs to complete the process and would like to know if someone can point me in the right direction. Please let me know of any additional information that I should provide. mysql show index from parts; +---++---+--+--- +---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+--- +---+-+--++--++-+ | parts | 0 | PRIMARY |1 | prtPNID | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|1 | prtPN | A | 326773 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|3 | prtMfg| A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtSort |1 | prtSort | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |1 | prtSKU| A | 18 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |2 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|1 | prtStripped | A | 230663 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|3 | prtMfg| A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |1 | prtDesc | A | 301636 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtWrtyTypeID |1 | prtWrtyTypeID | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |1 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |2 | prtMfg| A | 46132 | NULL | NULL | | BTREE | | +---++---+--+--- +---+-+--++--++-+ mysql explain select * from parts; ++-+---+--+---+--+- +--+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+- +--+-+---+ | 1 | SIMPLE | parts | ALL | NULL | NULL |NULL | NULL | 3921279 | | ++-+---+--+---+--+- +--+-+---+ 1 row in set (0.00 sec) Files from mysqldata directory -rw-rw 1 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 8.9G Aug 29 12:32 parts.ibd DB MySQL: 4.1.11 OS: Solaris 9 Hardware: SUN 2900; 32GB RAM Would you be so kind as to also provide the results of SHOW CREATE TABLE parts\G and SHOW VARIABLES LIKE '%buff%'; and SHOW TABLE STATUS LIKE 'parts'\G Based on the sizes of your fields, you may just have a lot of data to index. The buffer settings will tell us how much space you have allocated for sorting and key creation. How fast are your disks and what else is using them right now? Make sure your anti-virus scanner is not checking every write from mysql to your data directory (that can really slow you down, especially during an index rebuild). Please respond to the whole list and not just to me. I have a busy afternoon and may not be able to get back to you very soon. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Index update process 20+ hrs
This is a test server with nothing else hitting the box. I imported a
dump of a production database and is trying to find a way to reduce the
time it takes to rebuild the index on the mentioned table
Hard Drive spec:
Clarion EMC Hard drive 100 GB internal 3.5 Fibre Channel 1 rpm buffer
CREATE TABLE `parts` (
`prtStore` smallint(5) unsigned NOT NULL default '0',
`prtMMfg` char(3) NOT NULL default '',
`prtMfg` char(3) NOT NULL default '',
`prtPN` char(28) NOT NULL default '',
`prtStripped` char(28) NOT NULL default '',
`prtSort` char(75) NOT NULL default '',
`prtPNID` char(36) NOT NULL default '',
`prtSKU` char(14) NOT NULL default '',
`prtPic` char(20) NOT NULL default '',
`prtDesc` char(40) NOT NULL default '',
`prtDesc2` char(40) NOT NULL default '',
`prtStkQty` int(10) NOT NULL default '0',
`prtRsvQty` int(9) unsigned NOT NULL default '0',
`prtSupFlag` enum('N','Y') NOT NULL default 'N',
`prtAltMfg` char(3) NOT NULL default '',
`prtAltPN` char(28) NOT NULL default '',
`prtVD` tinyint(1) unsigned NOT NULL default '2',
`prtVQ` tinyint(1) unsigned NOT NULL default '0',
`prtMClass` char(1) NOT NULL default '',
`prtSClass1` char(1) NOT NULL default 'N',
`prtSClass2` char(1) NOT NULL default '',
`prtUClass` char(1) NOT NULL default '',
`prtProd` char(3) NOT NULL default '',
`prtGroup` char(10) NOT NULL default '',
`prtRptGroup` char(8) NOT NULL default '',
`prtPNcode` char(3) NOT NULL default '',
`prtDept` char(3) NOT NULL default '',
`prtCat` char(3) NOT NULL default '',
`prtOrderType` char(10) NOT NULL default '',
`prtDNR` enum('N','Y') NOT NULL default 'N',
`prtStockingCode` char(1) NOT NULL default '',
`prtExclude` char(3) NOT NULL default '',
`prtTax` enum('S','T','E') NOT NULL default 'S',
`prtNet` enum('N','Y') NOT NULL default 'N',
`prtMinSell` int(8) unsigned NOT NULL default '0',
`prtPerCar` smallint(5) unsigned NOT NULL default '0',
`prtMinStock` int(9) unsigned NOT NULL default '0',
`prtMaxStock` int(9) unsigned NOT NULL default '0',
`prtOrdPnt` int(9) unsigned NOT NULL default '0',
`prtLeadTime` tinyint(2) unsigned NOT NULL default '0',
`prtSMeasure` char(2) NOT NULL default 'EA',
`prtPMeasure` char(2) NOT NULL default 'EA',
`prtBMeasure` char(2) NOT NULL default 'EA',
`prtPackQty` int(7) unsigned NOT NULL default '0',
`prtPurchQty` smallint(3) unsigned NOT NULL default '1',
`prtMinBuyQty` smallint(3) unsigned NOT NULL default '1',
`prtMaxBuyQty` int(10) unsigned NOT NULL default '0',
`prtWeight` int(9) unsigned NOT NULL default '0',
`prtFreight` int(9) unsigned NOT NULL default '0',
`prtFrghtPPD` enum('Y','N') NOT NULL default 'Y',
`prtOnOrder` int(9) unsigned NOT NULL default '0',
`prtInBound` int(9) unsigned NOT NULL default '0',
`prtWHloc` smallint(5) unsigned NOT NULL default '0',
`prtWHzone` char(3) NOT NULL default '',
`prtVBO` int(9) unsigned NOT NULL default '0',
`prtCBO` int(9) unsigned NOT NULL default '0',
`prtPriVC` char(3) NOT NULL default '',
`prtPriVType` enum('M','S') NOT NULL default 'M',
`prtSecVC` char(3) NOT NULL default '',
`prtSecVType` enum('M','S') NOT NULL default 'M',
`prtStocked` enum('Y','N') NOT NULL default 'Y',
`prtReal` enum('Y','N') NOT NULL default 'Y',
`prtActive` enum('Y','N') NOT NULL default 'Y',
`prtCpnStk` enum('N','Y') NOT NULL default 'N',
`prtCpnVerify` enum('Y','N') NOT NULL default 'Y',
`prtCpnPNID` char(36) NOT NULL default '',
`prtLastSold` date default NULL,
`prtLastReturn` date default NULL,
`prtLastReceived` date default NULL,
`prtLastInventory` datetime default NULL,
`prtCreated` date default NULL,
`prtRetCore` int(10) unsigned NOT NULL default '0',
`prtTotRetCore` int(10) unsigned NOT NULL default '0',
`prtRetDef` int(10) unsigned NOT NULL default '0',
`prtTotRetDef` int(10) unsigned NOT NULL default '0',
`prtRetNew` int(10) unsigned NOT NULL default '0',
`prtTurns` int(9) unsigned NOT NULL default '0',
`prtFixedFee` int(9) unsigned NOT NULL default '0',
`prtFFFlag` enum('N','Y') NOT NULL default 'N',
`prtVOC` int(10) unsigned NOT NULL default '0',
`prtAreaPop` char(3) NOT NULL default '',
`prtMfgPop` char(3) NOT NULL default '',
`prtVehPopulation` int(5) unsigned NOT NULL default '0',
`prtAvgAge` int(6) unsigned NOT NULL default '0',
`prtForecast` int(6) unsigned NOT NULL default '0',
`prtNewPrice1` int(9) unsigned NOT NULL default '0',
`prtNewPrice2` int(9) unsigned NOT NULL default '0',
`prtNewPrice3` int(9) unsigned NOT NULL default '0',
`prtNewPrice4` int(9) unsigned NOT NULL default '0',
`prtNewPrice5` int(9) unsigned NOT NULL default '0',
`prtNewPrice6` int(9) unsigned NOT NULL default '0',
`prtNewPrice7` int(9) unsigned NOT NULL default '0',
`prtNewCurCost` int(9) unsigned NOT NULL default '0',
`prtNewAvgCost` int(9) unsigned NOT NULL default '0',
`prtNewLastCost` int(9) unsigned NOT NULL default '0',
`prtNewVCost1` int(9) unsigned NOT NULL default '0',
`prtNewVCost2` int(9) unsigned NOT NULL default '0',
`prtNewVCost3` int(9)
Re: Mark a single row?
[EMAIL PROTECTED] wrote: Hallo Jasper, Op 29 Aug 05 schreef Jasper Bryant-Greene aan mysql: I would like a single row in a table, and not more than one, to be used as a preferred value in another application. JBG add a column 'preferred', tinyint(1) NOT NULL. Thank you! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) It's hard to say without knowing just what you mean by used as a preferred value in another application, but I suspect that adding a whole column for this may not the best way to go. You'd be storing a lot of 0s just to keep one 1. The simplest solution may be to code the preferred row's id in your app. Your instinct to keep this value in the db is probably a better idea, however, especially if the preferred id could ever change. An alternative to adding a column would be to add a table. Something like: CREATE TABLE pref_value (pref_id INT); INSERT INTO pref_value (pref_id) VALUES (id_of_preferred_row); You could then look up the preferred id from the pref_value table and then use it, or simply join the pref_value table to your existing table. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mark a single row?
Hallo Michael, Op 29 Aug 05 schreef Michael Stassen aan [EMAIL PROTECTED]: I would like a single row in a table, and not more than one, to be used as a preferred value in another application. JBG add a column 'preferred', tinyint(1) NOT NULL. MS It's hard to say without knowing just what you mean by used as a MS preferred value in another application, but I suspect that adding a MS whole column for this may not the best way to go. You'd be storing a MS lot of 0s just to keep one 1. The simplest solution may be to code the MS preferred row's id in your app. Your instinct to keep this value in the MS db is probably a better idea, however, especially if the preferred id MS could ever change. It won't change very often, but there certainly is a chance. That's why I decided to keep it in the database somehow, rather than hard-coding it. Also, this way the users can change it without having to mess with the code. The amount of 0s would not be more than about 40, so not a big disaster. MS An alternative to adding a column would be to add a table. MS Something like: MSCREATE TABLE pref_value (pref_id INT); MSINSERT INTO pref_value (pref_id) VALUES (id_of_preferred_row); But this method is much more elegant. I wonder why I couldn't think of it myself... Thank you very much! Groetjes, Hans. --- GoldED+/LNX 1.1.5/050823 * Origin: The Wizard is using MBSE/Linux (2:280/1018) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
Get used to it, I've got similar tables, mine have less columns, but more records, my biggest takes a week to restore a mysqldump Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index update process 20+ hrs
If you are doing big updates on indexes, like during an import, you want to adjust your variables accordingly. The variables that would speed inserts and index updates are key_buffer_size bulk_insert_buffer_size if using MYISAM table types: myisam_max_sort_file_size myisam_sort_buffer_size Read this part of the online manual: http://dev.mysql.com/doc/mysql/en/insert-speed.html Tuning your variables specifically for importing can give you a dramatic performance boost. Then set them back to production values when you are done. On Aug 29, 2005, at 1:47 PM, Clyde Lewis wrote: Guys, I have a huge table to which I'm attempting to update the foreign key and index. It is taking me more than 20 hrs to complete the process and would like to know if someone can point me in the right direction. Please let me know of any additional information that I should provide. mysql show index from parts; +---++---+--+--- +---+-+--++--+ +-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++---+--+--- +---+-+--++--+ +-+ | parts | 0 | PRIMARY |1 | prtPNID | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|1 | prtPN | A | 326773 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 0 | prtUPN|3 | prtMfg | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtSort |1 | prtSort | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |1 | prtSKU | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | sku1 |2 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|1 | prtStripped | A | 230663 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtIPN|3 | prtMfg | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |1 | prtDesc | A | 301636 | NULL | NULL | | BTREE | | | parts | 1 | prtDESC |2 | prtStore | A | 3921279 | NULL | NULL | | BTREE | | | parts | 1 | prtWrtyTypeID |1 | prtWrtyTypeID | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |1 | prtStore | A | 18 | NULL | NULL | | BTREE | | | parts | 1 | prtStore |2 | prtMfg | A | 46132 | NULL | NULL | | BTREE | | +---++---+--+--- +---+-+--++--+ +-+ mysql explain select * from parts; ++-+---+--+---+--+- +--+-+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+---+--+- +--+-+---+ | 1 | SIMPLE | parts | ALL | NULL | NULL |NULL | NULL | 3921279 | | ++-+---+--+---+--+- +--+-+---+ 1 row in set (0.00 sec) Files from mysqldata directory -rw-rw 1 mysqlmysql18K Aug 25 17:24 parts.frm -rw-rw 1 mysqlmysql 8.9G Aug 29 12:32 parts.ibd DB MySQL: 4.1.11 OS: Solaris 9 Hardware: SUN 2900; 32GB RAM -- 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: Index update process 20+ hrs
I'd try setting innodb_buffer_pool_size=8G in my.cnf -- Alexey Polyakov
Re: AES_DECRYPT output
Hello. And I guess you also refer to the strange behavior that it's different when adding an ORDER BY or GROUP BY, right? Yes, in my opinion, ORDER BY shouldn't change the column's type. M DR [EMAIL PROTECTED] wrote: Hi, Thanks for your answers, What is according to you the bug? Should it always return a string format value? Or should it always return a system.byte[] format value? And I guess you also refer to the strange behavior that it's different when adding an ORDER BY or GROUP BY, right? Kind regards, Martin -- 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: Index update process 20+ hrs
On Monday 29 August 2005 04:55 pm, Alexey Polyakov wrote: I'd try setting innodb_buffer_pool_size=8G in my.cnf Then it will run out of memory. You only get 2gig for bufferpools no matter how much more you have. -- === Jabber: tradergt@(smelser.org|jabber.org) Quote:Jesus loves me, this I know / For the Voices tell me so. === pgp8vTR4kVuQ5.pgp Description: PGP signature
