Table caching
Hello I have a relatively simple table of 200+ network devices and 60+ sites. This is accessed about 30 times a minute by various perl scripts to pick up device/site information. Will I see any noticable benefit from creating a cached copy of this table as a MEMORY table? The data doesn't change often, so slightly stale cached data isn't an issue. Peter. -- Peter Hicks | e: [EMAIL PROTECTED] | g: 0xE7C839F4 | w: www.poggs.com A: Because it destroys the flow of the conversation Q: Why is top-posting bad? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Thank you Mathias once again
I suggest you to test after optimize table ... and analyze table ...
if you stay with the problem and you can give me a set of data, i'll try to
help
more.
Initially while posting... i tried to reduce one table to avoid
complexity from the actual query. I'm listing the tables below. I'm
also attaching some sample data.
-
CREATE TABLE `qb_test_result` (
`id` int(11) NOT NULL auto_increment,
`testId` smallint(4) NOT NULL default '0',
`studentId` int(11) NOT NULL default '0',
`marks` smallint(4) NOT NULL default '0',
`startTime` int(20) default NULL,
`endTime` int(20) default NULL,
`percentage` float NOT NULL default '0',
`status` enum('FAIL','PASS','POST','UNCOMPLETE') NOT NULL default
'UNCOMPLETE',
PRIMARY KEY (`id`),
KEY `testId` (`testId`),
KEY `testStudent` (`id`,`testId`,`studentId`)
) TYPE=MyISAM;
CREATE TABLE `qb_test_result_details` (
`sequenceId` int(20) NOT NULL default '0',
`resultId` int(20) NOT NULL default '0',
`questionId` int(20) NOT NULL default '0',
`viewStatus` enum('NV','V','A') NOT NULL default 'NV',
`bookMark` enum('Y','N') NOT NULL default 'N',
`correct` enum('Y','N') NOT NULL default 'N',
`postMarks` int(11) NOT NULL default '0',
KEY `resultId` (`resultId`)
) TYPE=MyISAM;
CREATE TABLE `ums_user` (
`id` bigint(255) NOT NULL auto_increment,
`firstName` varchar(255) NOT NULL default '',
`lastName` varchar(255) NOT NULL default '',
`userName` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`companyId` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `userName` (`userName`),
KEY `roleId` (`id`)
) TYPE=MyISAM ;
CREATE TABLE `qb_question` (
`id` int(11) NOT NULL auto_increment,
`question` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`marks` int(11) NOT NULL default '0',
`detailedAnswer` text NOT NULL,
`author` int(11) NOT NULL default '0',
`testId` smallint(4) NOT NULL default '0',
`loId` int(11) NOT NULL default '0',
`needWrittenAnswer` enum('Y','N') NOT NULL default 'Y',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `testId` (`testId`,`archive`)
) TYPE=MyISAM;
CREATE TABLE `qb_test` (
`id` smallint(4) NOT NULL auto_increment,
`categoryId` int(11) NOT NULL default '0',
`title` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`instructions` text NOT NULL,
`author` int(4) NOT NULL default '0',
`type` enum('PUBLIC','POST','PRE','REV') default NULL,
`duration` smallint(6) NOT NULL default '0',
`passrate` float NOT NULL default '0',
`showDetails` enum('Y','N') NOT NULL default 'Y',
`showRandom` enum('Y','N') NOT NULL default 'Y',
`showAssessment` enum('N','Y') NOT NULL default 'N',
`noOfQuestions` int(11) NOT NULL default '0',
`dateAvailable` datetime NOT NULL default '-00-00 00:00:00',
`companyId` int(11) NOT NULL default '0',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `title` (`title`,`author`,`type`),
KEY `categoryId` (`categoryId`),
KEY `companyAuthor` (`companyId`,`author`)
) TYPE=MyISAM;
-
Now the below query is not using the index testStudent in qb_test_result.
-
EXPLAIN
SELECT ums_user.firstName, qb_test.title testName, SUM(
qb_question.marks ) maxMarks, qb_test_result.percentage
FROM qb_test_result, qb_test_result_details, qb_test, qb_question,
ums_user
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test_result.studentId = ums_user.id
AND qb_test.id = qb_question.testId
AND qb_question.id = qb_test_result_details.questionId
AND qb_test.companyId =1
AND qb_test.author = 2
GROUP BY qb_test_result.id
-
How ever when i removed qb_test_result.percentage...it does..
-
EXPLAIN
SELECT ums_user.firstName, qb_test.title testName, SUM(
qb_question.marks ) maxMarks
FROM qb_test_result, qb_test_result_details, qb_test, qb_question,
ums_user
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test_result.studentId = ums_user.id
AND qb_test.id = qb_question.testId
Re: Indexing not working
Thank you Clark for your time Try using straight joins to force MySQL to join from qb_test_result first. The order chosen by the optimizer has no use for keys from this table. I tried to force indexes.. but it didn't help Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table caching
In the last episode (Jun 22), Peter Hicks said: I have a relatively simple table of 200+ network devices and 60+ sites. This is accessed about 30 times a minute by various perl scripts to pick up device/site information. Will I see any noticable benefit from creating a cached copy of this table as a MEMORY table? The data doesn't change often, so slightly stale cached data isn't an issue. 30 times a minute is only 1 query every 2 seconds. Unless you mean each script is doing 30/minute and you have 50 scripts, I don't think you really need to worry about optimizing mysql. What's the total time spent waiting for mysql vs the total runtime of the script? Anyway, it's easy enough for you to test: copy the table to a backup name, ALTER TABLE devices ENGINE=MEMORY, then benchmark. If you are doing the same queries repeatedly, try enabling the query cache first. Also make sure your tables are appropriately indexed. If the table's small, chances are mysql has fully cached the index and the OS has cached the table. If they are simple queries, appropiate multi-column indexing will let mysql return results directly from the index. -- 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: connection error from c application
Dear Gleb,
Thanks, i will try that as well, so far my program can access the
server now by using the old password. Now my problem is when i load
file, its only reading the first line and some more insert a null
record before the ist record.
here is the result i get after loading the text file:
mysql load data local infile 'syslog5.txt' into table log4 fields
terminated by ' \t' lines terminated by '\n';
Query OK, 2 rows affected, 513 warnings (0.03 sec)
Records: 20 Deleted: 0 Skipped: 18 Warnings: 505
the first record shows all null values on all columns
the second record is correct but it is the first line in my text file
the rest of the lines in the text file is ignored.
I tried creating a table for my pc event logs, its all working fine,
can load, and query from the c application.
Would appreciate if you guys can help.
Elizabeth
On 6/23/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
I've compiled your code using libraries and includes from
MySQL-4.1.6 (yes, my testing environment isn'tperfect :) and
successfully connected to MySQL-4.1.10. Old passwords was off,
so I used a 4.1. authorization protocol.
Elizabeth Bonifacio [EMAIL PROTECTED] wrote:
Dear All,
I'm new into mysql and has been encountering huge problem in
connecting to the database from the c application. The code execute
with an error message :
Failed to connect to database: Error:
Client does not support authentication protocol requested by server;
consider upgrading MySQL client
Is this a bug?
I'm running the MySql server version 4.1 with server and client both
on same computer running on windows XP.
I have no problem connecting to the server using root with a windfall
password but I cannot connect from the c application below.
I have only one user in the server (root,windfall) and has been
successful in accessing mysql from the client side except when
connecting from a c application.
here is the application which I compile using Visual C++ compiler:
#include stdio.h
#include winsock.h
#include iostream.h
#include mysql.h
int main()
{
MYSQL mysql;
MYSQL_ROW row;
MYSQL_RES *result;
unsigned int num_fields;
unsigned int i;
int num =3D 0;
int iRetValue =3D 0;
mysql_init(mysql);
//printf(%s,mysql );
if (!mysql_real_connect(mysql,localhost,root,windfall,mysql,3306,
NULL,0))
{
fprintf(stderr, Failed to connect to database: Error: \n%s\n,
mysql_error(mysql));
}
else
{
printf(SUCCESS\n);
iRetValue =3D mysql_query(mysql, SELECT * FROM user);
if( iRetValue !=3D 0 )
{
printf(Query Not Executed Properly.Please Check The Syntax.\n);
}
//here goes the error message :o)
else
{
result =3D mysql_store_result(mysql);
num =3D mysql_field_count(mysql);
printf(Number Of Rows :%d\n,num );
num_fields =3D mysql_num_fields( result);
printf(Number Of Coloumns :%d\n,num_fields );
while ((row =3D mysql_fetch_row(result)))
{
unsigned long *lengths;
lengths =3D mysql_fetch_lengths(result);
for(i =3D 0; i num_fields; i++)
{
printf([%.*s] \t, (int) lengths[i], row[i] ? row[i] : NULL);
}
printf(\n);
}
}
}
}
I would appreciate if you guys can help. thanks.
Elizabeth
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade mysql 3.23.58 to 4.1
I have on my system, Fedora core 3, MySql 3.23.58 Working with wikipedia database I get one error an one advise to upgrade to Mysql 4.xx Can I have old mySQL and the new one on my system? It is necessary to recreate databases and reload the data? What other kind of problem is suppose to find on my way during upgrade? Thanks, MT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 Win 2003 server install problem
Dear Friends, Iam trying to install MySQL 4.1.2 in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Need a help about it. Regards for all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 Win 2003 server install problem
hi, if u'r uninstalled the mysql and try to reinstalling means, try the following. 1.Delete all the corresponding files that are created by the prev installation after the uninstallation. 2.Install the MySQL again. Now it'll work. This is due to some configuration prob between the prev and new installation. Try it, all the best. Thanks and Regards, Ashok. --- Carlos J Souza [EMAIL PROTECTED] wrote: Dear Friends, Iam trying to install MySQL 4.1.2 in Windows 2003 Server, and on error occurs when installation try a start de service. The service does not start and installation don't finish. I Need a help about it. Regards for all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unicode UTF-8 database
Hi, I'm using MySQL 4.1.11 I need to setup a multilingual database to support a lot of foreign languages. Do I have to setup a UTF-8 unicode database ? Does anyone have a step-by-step guide on how to achieve this ? Thanks for any help Regards Roberto Navighi a 4 MEGA e i primi 3 mesi sono GRATIS. Scegli Libero Adsl Flat senza limiti su http://www.libero.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAC OS X backup after crash
I have to ask, are you using the same version of MySQL? I think 10.1 came with v3 and 10.3 come with v4.0. You may need to setup 10.1.5 on an old machine to dump the database for importing. On Jun 23, 2005, at 4:53 PM, Alla-amin wrote: Hi everyone, I just subscribed to the list and some of the articles are very helpful. I'm facing some problem on my mysql database server. One of our Raid drives crashed and we had to backup the whole data to another drive (only files), we did no mysqldump. Now I want to restore a certain database from the crashed drive to the new server. I compressed it and uncompressed it in the data folder of the new server but the server only sees the database but no info. The 1st dbase was on MAC 10.1.5 and the current one is one 10.3. Any help please. Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[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: Indexing not working
Sajith A wrote: Thank you Clark for your time Try using straight joins to force MySQL to join from qb_test_result first. The order chosen by the optimizer has no use for keys from this table. I tried to force indexes.. but it didn't help Thank you Since the query you supplied cannot use an index, force index won't help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAC OS X backup after crash
Yes, 10.1.5 runs mysqk version 3, it was actually running 3.23.51 and 10.3 runs 4.0.14 I think. Ok - the database in question have been backedup with files such as table1.frm.gz table1.MYD.gz table1.MYI.gz and so on.gz When I create a new database on the new server and copy these files to the the new database, mysql doesn't recognize them. When I rename table1.frm to table1.frm and copy it to the new server database - mysql says that there is no data there. I haven't tried this on a 10.1.5 system yet - will do that asap. Alex Dehaini App Engineer NAS GLOBAL NETWORKS Mobile = +233-24-877231 Office = +233-217012800/2 Email = [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAC OS X backup after crash
Alla-amin wrote: Yes, 10.1.5 runs mysqk version 3, it was actually running 3.23.51 and 10.3 runs 4.0.14 I think. Ok - the database in question have been backedup with files such as table1.frm.gz table1.MYD.gz table1.MYI.gz and so on.gz When I create a new database on the new server and copy these files to the the new database, mysql doesn't recognize them. You need to unzip them, and verify correct ownership and permissions. When I rename table1.frm to table1.frm and copy it to the new server database - mysql says that there is no data there. I haven't tried this on a 10.1.5 system yet - will do that asap. Alex Dehaini App Engineer NAS GLOBAL NETWORKS Mobile = +233-24-877231 Office = +233-217012800/2 Email = [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexing not working
Sajith A [EMAIL PROTECTED] wrote on 06/24/2005 02:07:22 AM:
Thank you Mathias once again
I suggest you to test after optimize table ... and analyze table ...
if you stay with the problem and you can give me a set of data,
i'll try to help
more.
Initially while posting... i tried to reduce one table to avoid
complexity from the actual query. I'm listing the tables below. I'm
also attaching some sample data.
-
CREATE TABLE `qb_test_result` (
`id` int(11) NOT NULL auto_increment,
`testId` smallint(4) NOT NULL default '0',
`studentId` int(11) NOT NULL default '0',
`marks` smallint(4) NOT NULL default '0',
`startTime` int(20) default NULL,
`endTime` int(20) default NULL,
`percentage` float NOT NULL default '0',
`status` enum('FAIL','PASS','POST','UNCOMPLETE') NOT NULL default
'UNCOMPLETE',
PRIMARY KEY (`id`),
KEY `testId` (`testId`),
KEY `testStudent` (`id`,`testId`,`studentId`)
) TYPE=MyISAM;
CREATE TABLE `qb_test_result_details` (
`sequenceId` int(20) NOT NULL default '0',
`resultId` int(20) NOT NULL default '0',
`questionId` int(20) NOT NULL default '0',
`viewStatus` enum('NV','V','A') NOT NULL default 'NV',
`bookMark` enum('Y','N') NOT NULL default 'N',
`correct` enum('Y','N') NOT NULL default 'N',
`postMarks` int(11) NOT NULL default '0',
KEY `resultId` (`resultId`)
) TYPE=MyISAM;
CREATE TABLE `ums_user` (
`id` bigint(255) NOT NULL auto_increment,
`firstName` varchar(255) NOT NULL default '',
`lastName` varchar(255) NOT NULL default '',
`userName` varchar(50) NOT NULL default '',
`password` varchar(50) NOT NULL default '',
`email` varchar(100) NOT NULL default '',
`companyId` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `userName` (`userName`),
KEY `roleId` (`id`)
) TYPE=MyISAM ;
CREATE TABLE `qb_question` (
`id` int(11) NOT NULL auto_increment,
`question` text NOT NULL,
`url` varchar(255) NOT NULL default '',
`file` varchar(255) NOT NULL default '',
`marks` int(11) NOT NULL default '0',
`detailedAnswer` text NOT NULL,
`author` int(11) NOT NULL default '0',
`testId` smallint(4) NOT NULL default '0',
`loId` int(11) NOT NULL default '0',
`needWrittenAnswer` enum('Y','N') NOT NULL default 'Y',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `testId` (`testId`,`archive`)
) TYPE=MyISAM;
CREATE TABLE `qb_test` (
`id` smallint(4) NOT NULL auto_increment,
`categoryId` int(11) NOT NULL default '0',
`title` varchar(80) NOT NULL default '',
`description` text NOT NULL,
`instructions` text NOT NULL,
`author` int(4) NOT NULL default '0',
`type` enum('PUBLIC','POST','PRE','REV') default NULL,
`duration` smallint(6) NOT NULL default '0',
`passrate` float NOT NULL default '0',
`showDetails` enum('Y','N') NOT NULL default 'Y',
`showRandom` enum('Y','N') NOT NULL default 'Y',
`showAssessment` enum('N','Y') NOT NULL default 'N',
`noOfQuestions` int(11) NOT NULL default '0',
`dateAvailable` datetime NOT NULL default '-00-00 00:00:00',
`companyId` int(11) NOT NULL default '0',
`archive` enum('Y','N') NOT NULL default 'N',
PRIMARY KEY (`id`),
KEY `title` (`title`,`author`,`type`),
KEY `categoryId` (`categoryId`),
KEY `companyAuthor` (`companyId`,`author`)
) TYPE=MyISAM;
-
Now the below query is not using the index testStudent in
qb_test_result.
-
EXPLAIN
SELECT ums_user.firstName, qb_test.title testName, SUM(
qb_question.marks ) maxMarks, qb_test_result.percentage
FROM qb_test_result, qb_test_result_details, qb_test, qb_question,
ums_user
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test_result.studentId = ums_user.id
AND qb_test.id = qb_question.testId
AND qb_question.id = qb_test_result_details.questionId
AND qb_test.companyId =1
AND qb_test.author = 2
GROUP BY qb_test_result.id
-
How ever when i removed qb_test_result.percentage...it does..
-
EXPLAIN
SELECT ums_user.firstName, qb_test.title testName, SUM(
qb_question.marks ) maxMarks
FROM qb_test_result, qb_test_result_details, qb_test, qb_question,
ums_user
WHERE qb_test_result.id = qb_test_result_details.resultId
AND qb_test_result.testId = qb_test.id
AND qb_test_result.studentId = ums_user.id
AND qb_test.id = qb_question.testId
AND qb_question.id =
Pseudo-Spatial data MySQL
I'm running MySQL 4.0.18-nt accessing the database primarily through Java with MySQL/Connector J (similar vintage). I am administering this myself, and can upgrade as needed. I know I'm a bit behind the times. I apologize for a bit of background, skip to the end for the question I am using a database to track large numbers of computer analysis runs. The analyses are treated abstractly as black boxes (tasks) with n-inputs and m-outputs. Inputs and outputs are both treated as quantities. At the time of the database design, we have no idea how many inputs or outputs any task will have. An output of one task may be an input to another task. So, there are tables for tasks (T) and quantities (Q), and then input T/Q and output T/Q tables to create the many-to-many pairing required. As cases are run, records are entered into a case (C) table, and the settings of the inputs and outputs from the run are stored in pairs in a Case/Quantity table. The general idea is to keep a record of many computer analysis runs and interpolate new results instead of running the code again. So, if the computer analysis is something like... f = sin(x)*cos(y) And we'll pretend that this is a very expensive analysis to perform, we want to get as much out of the runs we've already made as possible. Of course, in the real world, there may be a dozen or twenty input quantities and a similar number of outputs... I need to be able to perform nearest-neighbor type searches based on the Case/Quantity values for the inputs. My plan was to just request _all_ the data from the database (making the query easy), storing the data in a K-D Tree in my application, performing the more detailed searches there. Then, I read a random (ancient) blog entry about R-Trees in MySQL 4.1 (http://jeremy.zawodny.com/blog/archives/000418.html). Which got me thinking Is there some way to more effectively store this type of data in MySQL. I don't need R-Trees (though they'll work), my data are all points, not rectangles... So, is there some way to tell MySQL that I'll be doing these indirect searches on the values of the quantities, so that MySQL can do this much more efficiently? I'd rather not change my database schema, but will consider it if required. Recall that we don't know the input dimensionality of the problem at the time the database is created. Thanks in advance, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set on 4.1 and ujis support
Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Binlog/Replication and SET @variables
Hello, i have a problem with my replication setup and SET statments. The SET statments seem to be not logged with the queries. An example: CREATE TABLE blablub (ID INT NOT NULL auto_increment PRIMARY KEY, bla text, blub text); SET @bla='bla'; SET @blub='blub'; INSERT INTO blablub VALUES(NULL, @bla, @blub); Now the table blablub on the Master: ++--+--+ | ID | bla | blub | ++--+--+ | 1 | bla | blub | ++--+--+ And on the Slave: ++--+--+ | ID | bla | blub | ++--+--+ | 2 | NULL | NULL | ++--+--+ Here's the binlog entry for the INSERT: #050624 16:46:54 server id 1 log_pos 9162 Intvar SET INSERT_ID=1; # at 9190 #050624 16:46:54 server id 1 log_pos 9190 Query thread_id=7 exec_tim e=0 error_code=0 SET TIMESTAMP=1119624414; INSERT INTO blablub VALUES(NULL, @bla, @blub); Same should happen when the binlog is replayed while restoring a backup. I'm using mysql release 4.0.24. regards, Markus -- Siemens AG ITO AS 4 Internetteam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Pseudo-Spatial data MySQL
Rob McDonald [EMAIL PROTECTED] wrote on 06/24/2005 10:37:39 AM: I'm running MySQL 4.0.18-nt accessing the database primarily through Java with MySQL/Connector J (similar vintage). I am administering this myself, and can upgrade as needed. I know I'm a bit behind the times. I apologize for a bit of background, skip to the end for the question I am using a database to track large numbers of computer analysis runs. The analyses are treated abstractly as black boxes (tasks) with n-inputs and m-outputs. Inputs and outputs are both treated as quantities. At the time of the database design, we have no idea how many inputs or outputs any task will have. An output of one task may be an input to another task. So, there are tables for tasks (T) and quantities (Q), and then input T/Q and output T/Q tables to create the many-to-many pairing required. As cases are run, records are entered into a case (C) table, and the settings of the inputs and outputs from the run are stored in pairs in a Case/Quantity table. The general idea is to keep a record of many computer analysis runs and interpolate new results instead of running the code again. So, if the computer analysis is something like... f = sin(x)*cos(y) And we'll pretend that this is a very expensive analysis to perform, we want to get as much out of the runs we've already made as possible. Of course, in the real world, there may be a dozen or twenty input quantities and a similar number of outputs... I need to be able to perform nearest-neighbor type searches based on the Case/Quantity values for the inputs. My plan was to just request _all_ the data from the database (making the query easy), storing the data in a K-D Tree in my application, performing the more detailed searches there. Then, I read a random (ancient) blog entry about R-Trees in MySQL 4.1 (http://jeremy.zawodny.com/blog/archives/000418.html). Which got me thinking Is there some way to more effectively store this type of data in MySQL. I don't need R-Trees (though they'll work), my data are all points, not rectangles... So, is there some way to tell MySQL that I'll be doing these indirect searches on the values of the quantities, so that MySQL can do this much more efficiently? I'd rather not change my database schema, but will consider it if required. Recall that we don't know the input dimensionality of the problem at the time the database is created. Thanks in advance, Rob If I remember right, the R-TREES are associated with the GIS extensions to MySQL. I could be wrong but that's how I remember it (and I had a hard time finding a reference to them in the official online manual. Can anyone help?) I guess as long as your _results_ are points of no more than two dimensions, you could try using the spatial extensions to store them. However, you make it sound as though you have an N-dimensional input space and an M-dimensional output space. I don't think the GIS extensions to MySQL cover the cases where M or N are greater than 2. from http://dev.mysql.com/doc/mysql/en/gis-class-geometry.html (Describing the Geometry class) ... All calculations are done assuming Euclidean (planar) geometry. # Its coordinates in its Spatial Reference System, represented as double-precision (eight-byte) numbers. All non-empty geometries include at least one pair of (X,Y) coordinates. Empty geometries contain no coordinates. ... A geometry can have a dimension of ?1, 0, 1, or 2 ... If I am right, you are asking for help to determine all of the points in an X-dimensional space that reside within a certain radius of a particular point, or the nearest C points to a particular location in X-dimensional space. One way to minimize your search targets T(0...n) is to define an X-dimensional hypercube around your target point P(0) by taking your original coordinates and looking +/- some value for each dimension. Then, you would compute the distance from your P(0) to each T(x), sort the results and pick the nearest by limiting to however many you wanted. In my opinion, you can store the data two different ways: flat or normalized. In the flat model, you create one column for each dimension. This is sometimes faster to work with but takes up more room. The down-side to this arrangement is: should you ever need to increase the number of dimensions you are storing, you will have to change the table structure to do it. The normalized model creates list-pairs (I think this is what you are doing) and can consume as much room as the flat model, depending on the density of your high dimensional data. What I mean is that if you create a results table to store up to 25 dimensions, and most of your data only had 8, you have 17 blank dimensions being stored for each row of data. This kind of situation would be optimized by using the Normalized data method. However, if you frequently store 20 dimensions or more for
Re: MySQL Binlog/Replication and SET @variables
On Jun 24, 2005, at 10:56 AM, Markus Benning wrote: i have a problem with my replication setup and SET statments. The SET statments seem to be not logged with the queries. This is a replication limitation in versions prior to 4.1: Update statements that refer to user variables (that is, variables of the form @var_name) are badly replicated in 3.23 and 4.0. This is fixed in 4.1. Note that user variable names are case insensitive starting from MySQL 5.0. You should take this into account when setting up replication between 5.0 and an older version. http://dev.mysql.com/doc/mysql/en/replication-features.html --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Remote Connection?
hi, i try to access the MySQL server remotely using Command prompet how can i do so? i mean i want to run all MySQL command on my hosting server ... _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Remote Connection?
Most of MySQL commands have --host option that allows you to address remote MySQL server. One needs: * To have MySQL server be running on all machines you want to reach * Each remote host must allow remote users, from your hosting server, to execute commands on that server. -Original Message- From: Badr Al-Muzini [mailto:[EMAIL PROTECTED] Sent: Friday, June 24, 2005 11:51 AM To: mysql@lists.mysql.com Subject: MySQL Remote Connection? hi, i try to access the MySQL server remotely using Command prompet how can i do so? i mean i want to run all MySQL command on my hosting server ... _ Don't just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- 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: upgrade mysql 3.23.58 to 4.1
Yes You can have both versions, infact this is the preferred way to upgrade, but the only thing is that have the new version install in /usr/local/mysql2/ or what ever you want and change the port that the newer daemon listens on instead of the default 3306, here's a good link from the doc's of mysql... http://dev.mysql.com/doc/mysql/en/upgrading-from-3-23.html Hope this helps, Kishore Jalleda On 6/24/05, mm [EMAIL PROTECTED] wrote: I have on my system, Fedora core 3, MySql 3.23.58 Working with wikipedia database I get one error an one advise to upgrade to Mysql 4.xx Can I have old mySQL and the new one on my system? It is necessary to recreate databases and reload the data? What other kind of problem is suppose to find on my way during upgrade? Thanks, MT -- 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]
Fw: Pseudo-Spatial data MySQL
If I remember right, the R-TREES are associated with the GIS extensions to MySQL. I could be wrong but that's how I remember it (and I had a hard time finding a reference to them in the official online manual. Can anyone help?) From what I've been able to dig up, this is correct. I was hoping that there was some generalized functionality also available. As you say, documentation is scarce on these features. I guess as long as your _results_ are points of no more than two dimensions, you could try using the spatial extensions to store them. However, you make it sound as though you have an N-dimensional input space and an M-dimensional output space. I don't think the GIS extensions to MySQL cover the cases where M or N are greater than 2. Yes, I'm looking for N-D input space, where N is unknown until runtime. I agree that the GIS stuff won't help me out. If I am right, you are asking for help to determine all of the points in an X-dimensional space that reside within a certain radius of a particular point, or the nearest C points to a particular location in X-dimensional space. One way to minimize your search targets T(0...n) is to define an X-dimensional hypercube around your target point P(0) by taking your original coordinates and looking +/- some value for each dimension. Then, you would compute the distance from your P(0) to each T(x), sort the results and pick the nearest by limiting to however many you wanted. In my opinion, you can store the data two different ways: flat or normalized. In the flat model, you create one column for each dimension. This is sometimes faster to work with but takes up more room. The down-side to this arrangement is: should you ever need to increase the number of dimensions you are storing, you will have to change the table structure to do it. The normalized model creates list-pairs (I think this is what you are doing) and can consume as much room as the flat model, depending on the density of your high dimensional data. I have devised a system that can handle dynamic N input dimensions. I'm not sure if it matches your normalized scheme, but it certainly isn't the flat scheme. What I mean is that if you create a results table to store up to 25 dimensions, and most of your data only had 8, you have 17 blank dimensions being stored for each row of data. This kind of situation would be optimized by using the Normalized data method. However, if you frequently store 20 dimensions or more for each data point then the flat table is more space efficient because you eliminate the duplication of the parent_id field for each row. As I said, it's all dependent on how flexible you need your results to be and the nature of your data that will determine which method is more flexible. The flat table model has another drawback, there is a limit to how many columns you can index on any one table. With the normalized data, you shouldn't run into that problem. Am I on the right track or have I lost my way? I think you're on the right track, and I appreciate all the input. The primary clarification that the GIS functionality is limited to 2D was the main question. Thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RPM install fails
I'm trying to upgrade a factory installed 3.23.58 to 4.0 and then 4.1. rpm -i MySQL-server-4.0.24-0.i386.rpm results in error: Failed dependencies: MySQL conflicts with mysql-3.23.58-16.FC2.1 MySQL-server conflicts with mysql-server-3.23.58-16.FC2.1 How do I get the install to work? Thanks Eldon Ziegler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
install issue
After I installed mysql 4.0.18 on Windows XP I could not use it. It seems the mysql database does not show up. I looked in the data directory and it is there, but if I do a show databases from the mysql client only the test database shows up. any thing else I try causes errors. any ideasI bet it is simple. Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: install issue
u might want to check the permissions in the data directory, may be the user under which mysql runs does not have the proper permissions on the databases.. Kishore Jalleda On 6/24/05, Ron Day [EMAIL PROTECTED] wrote: After I installed mysql 4.0.18 on Windows XP I could not use it. It seems the mysql database does not show up. I looked in the data directory and it is there, but if I do a show databases from the mysql client only the test database shows up. any thing else I try causes errors. any ideasI bet it is simple. Ron -- 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]
Doubt on MySQL for Linux...
Hi friends, I'm now started to using the Redhat-Linux. Now i want to start MySQL on Linux. I'm not having much idea about Linux. I'm new to this. For Linux which MySQL installation i've to choose from the site. there are lot of binary distributions, i don't which one is compatible for my OS. Linux and H/W specification of my system is as follows. 1. Redhat Linux 9 2. Intel PIII And i also want to know abt how to install and configure MySQL for using that in 'C'. Pls guide me in this. Thanks and Regards, Ashok Kumar.P.S. Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt on MySQL for Linux...
Ashok Kumar wrote: Hi friends, I'm now started to using the Redhat-Linux. Now i want to start MySQL on Linux. I'm not having much idea about Linux. I'm new to this. For Linux which MySQL installation i've to choose from the site. there are lot of binary distributions, i don't which one is compatible for my OS. Linux and H/W specification of my system is as follows. 1. Redhat Linux 9 2. Intel PIII And i also want to know abt how to install and configure MySQL for using that in 'C'. Pls guide me in this. Thanks and Regards, Ashok Kumar.P.S. Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com Ashok, I suggest that you carefully read the documentation on http://dev.mysql.com/doc/mysql/en/index.html Here you can find the installation program and tutorials to help you get through MySql If you are new to Linux, then perhaps reading some stuff about linux first. Good luck and enjoy :^) Best Regards, Danny Stolle Netherlands EmoeSoft (http://www.emoesoft.nl) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAC OS X backup after crash
You can't transfer database files directly between versions. The file format is different. You need to dump the data to a text file and import it. This isn't like FileMaker, Word or Excel where it will realize it's an older version and convert it. On Jun 24, 2005, at 9:42 AM, Alla-amin wrote: Yes, 10.1.5 runs mysqk version 3, it was actually running 3.23.51 and 10.3 runs 4.0.14 I think. Ok - the database in question have been backedup with files such as table1.frm.gz table1.MYD.gz table1.MYI.gz and so on.gz When I create a new database on the new server and copy these files to the the new database, mysql doesn't recognize them. When I rename table1.frm to table1.frm and copy it to the new server database - mysql says that there is no data there. I haven't tried this on a 10.1.5 system yet - will do that asap. Alex Dehaini App Engineer NAS GLOBAL NETWORKS Mobile = +233-24-877231 Office = +233-217012800/2 Email = [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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: install issue
Hello. Use the latest release (4.1.12 now). Have you tried to connect using the root account? Ron Day [EMAIL PROTECTED] wrote: After I installed mysql 4.0.18 on Windows XP I could not use it. It seems the mysql database does not show up. I looked in the data directory and it is there, but if I do a show databases from the mysql client only the test database shows up. any thing else I try causes errors. any ideasI bet it is simple. Ron -- 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: compiling + making source for 5.7 on alpha
Hello. Compiling MySQL on some systems could be a hard task. Please give us more information about what you're doing and your building environment. Have you tried GNU make? Simon Tierney [EMAIL PROTECTED] wrote: Can anyone advise please, tried binary installation but other users have reported encountering similar problems so I thought I'd try a source installation. Adjusted the pentiumpro flags out and configure reported no errors -when I use make I get *** no targets. stop. The makefile is in there, am I overlooking something elementary? -- 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: could a Trigger execute a outside command?
Hello. I don't know any SQL statements which could launch extern commands, at least in MySQL. So in triggers it is impossible as well. Ted Zeng [EMAIL PROTECTED] wrote: Hi, I am using MySql on Mac OS X. I am wondering if I could execute a command (shell, perl) by a Trigger. I read the manual and it seems this is impossible. ted -- 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: MySQL Remote Connection?
Hello. Do you have a shell account on that server? Are you allowed to connect to your server from remote machines (ask support of your hosting about this). Badr Al-Muzini [EMAIL PROTECTED] wrote: hi, i try to access the MySQL server remotely using Command prompet how can i do so? i mean i want to run all MySQL command on my hosting server ... _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ -- 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: RPM install fails
Hello. It is a FAQ. Use -U. Search in archives at: http://lists.mysql.com/mysql about possible problems. Eldon Ziegler [EMAIL PROTECTED] wrote: I'm trying to upgrade a factory installed 3.23.58 to 4.0 and then 4.1. rpm -i MySQL-server-4.0.24-0.i386.rpm results in error: Failed dependencies: MySQL conflicts with mysql-3.23.58-16.FC2.1 MySQL-server conflicts with mysql-server-3.23.58-16.FC2.1 How do I get the install to work? Thanks Eldon Ziegler -- 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: MAC OS X backup after crash
Hello. What is in MySQL error log? Did you use InnoDB tables? Alla-amin [EMAIL PROTECTED] wrote: Hi everyone, I just subscribed to the list and some of the articles are very helpful. I'm facing some problem on my mysql database server. One of our Raid drives crashed and we had to backup the whole data to another drive (only files), we did no mysqldump. Now I want to restore a certain database from the crashed drive to the new server. I compressed it and uncompressed it in the data folder of the new server but the server only sees the database but no info. The 1st dbase was on MAC 10.1.5 and the current one is one 10.3. Any help please. Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.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: Character Sets in 3.23.58
Hello. Complete and rich character set support appeared only in MySQL 4.1. So if it is possible move to 4.1. Andrew Mull [EMAIL PROTECTED] wrote: Hello, We are running mySQL 3.23.58 on a RedHat server. We have a database that needs to store Chinese (big5) and Vietnamese (Unicode-8 should work)how do I get it to store alternative character sets? The docs online only relate to ver 4so the character set attribute doesn't work. Any help would be appreciatedI'm new to this multiple language thing. Thanks! -Andy -- 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: MySQL Binlog/Replication and SET @variables
Hello. It seems like a documented bug. Switch to 4.1.12. See: http://dev.mysql.com/doc/mysql/en/replication-features.html Markus Benning [EMAIL PROTECTED] wrote: Hello, i have a problem with my replication setup and SET statments. The SET statments seem to be not logged with the queries. An example: CREATE TABLE blablub (ID INT NOT NULL auto_increment PRIMARY KEY, bla text, blub text); SET @bla='bla'; SET @blub='blub'; INSERT INTO blablub VALUES(NULL, @bla, @blub); Now the table blablub on the Master: ++--+--+ | ID | bla | blub | ++--+--+ | 1 | bla | blub | ++--+--+ And on the Slave: ++--+--+ | ID | bla | blub | ++--+--+ | 2 | NULL | NULL | ++--+--+ Here's the binlog entry for the INSERT: #050624 16:46:54 server id 1 log_pos 9162 Intvar SET INSERT_ID=1; # at 9190 #050624 16:46:54 server id 1 log_pos 9190 Query thread_id=7 exec_tim e=0 error_code=0 SET TIMESTAMP=1119624414; INSERT INTO blablub VALUES(NULL, @bla, @blub); Same should happen when the binlog is replayed while restoring a backup. I'm using mysql release 4.0.24. regards, Markus -- 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: Unicode UTF-8 database
Hello. First read: http://dev.mysql.com/doc/mysql/en/charset.html Roberto Jobet [EMAIL PROTECTED] wrote: Hi, I'm using MySQL 4.1.11 I need to setup a multilingual databa= se to support a lot of foreign languages. Do I have to setup a UTF-= 8 unicode database ? Does anyone have a step-by-step guide on how t= o achieve this ? Thanks for any help Regards Roberto=0A=0A=0A=0A= =0ANavighi a = 4 MEGA e i primi 3 mesi sono GRATIS. =0AScegli Libero Adsl Flat senza lim= iti su http://www.libero.it=0A -- 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: Character set on 4.1 and ujis support
Hello. What do your 'show' statements return when you execute them from the perl script? Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- 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: upgrade mysql 3.23.58 to 4.1
Hello. Read: http://dev.mysql.com/doc/mysql/en/upgrade.html http://dev.mysql.com/doc/mysql/en/multiple-servers.html And search in archives about possible troubleshooting: http://lists.mysql.com/mysql/ mm [EMAIL PROTECTED] wrote: I have on my system, Fedora core 3, MySql 3.23.58 Working with wikipedia database I get one error an one advise to upgrade to Mysql 4.xx Can I have old mySQL and the new one on my system? It is necessary to recreate databases and reload the data? What other kind of problem is suppose to find on my way during upgrade? Thanks, MT -- 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: connection error from c application
Hello.
What 'SHOW WARNINGS' reports?
Elizabeth Bonifacio [EMAIL PROTECTED] wrote:
Dear Gleb,
Thanks, i will try that as well, so far my program can access the
server now by using the old password. Now my problem is when i load
file, its only reading the first line and some more insert a null
record before the ist record.
here is the result i get after loading the text file:
mysql load data local infile 'syslog5.txt' into table log4 fields
terminated by ' \t' lines terminated by '\n';
Query OK, 2 rows affected, 513 warnings (0.03 sec)
Records: 20 Deleted: 0 Skipped: 18 Warnings: 505
the first record shows all null values on all columns
the second record is correct but it is the first line in my text file
the rest of the lines in the text file is ignored.
I tried creating a table for my pc event logs, its all working fine,
can load, and query from the c application.
Would appreciate if you guys can help.
Elizabeth
On 6/23/05, Gleb Paharenko [EMAIL PROTECTED] wrote:
Hello.
=20
=20
=20
I've compiled your code using libraries and includes from
=20
MySQL-4.1.6 (yes, my testing environment isn'tperfect :) and
=20
successfully connected to MySQL-4.1.10. Old passwords was off,
=20
so I used a 4.1. authorization protocol.
=20
=20
=20
=20
=20
Elizabeth Bonifacio [EMAIL PROTECTED] wrote:
=20
Dear All,
=20
=20
I'm new into mysql and has been encountering huge problem in
=20
connecting to the database from the c application. The code execute
=20
with an error message :
=20
Failed to connect to database: Error:
=20
Client does not support authentication protocol requested by server;
=20
consider upgrading MySQL client
=20
Is this a bug?
=20
=20
I'm running the MySql server version 4.1 with server and client both
=20
on same computer running on windows XP.
=20
=20
I have no problem connecting to the server using root with a windfall
=20
password but I cannot connect from the c application below.
=20
=20
I have only one user in the server (root,windfall) and has been
=20
successful in accessing mysql from the client side except when
=20
connecting from a c application.
=20
=20
here is the application which I compile using Visual C++ compiler:
=20
=20
#include stdio.h
=20
#include winsock.h
=20
#include iostream.h
=20
=20
#include mysql.h
=20
=20
int main()
=20
{
=20
=20
MYSQL mysql;
=20
MYSQL_ROW row;
=20
MYSQL_RES *result;
=20
=20
unsigned int num_fields;
=20
unsigned int i;
=20
int num =3D3D 0;
=20
int iRetValue =3D3D 0;
=20
=20
mysql_init(mysql);
=20
//printf(%s,mysql );
=20
=20
if (!mysql_real_connect(mysql,localhost,root,windfall,mysql,33=
06,
=20
NULL,0))
=20
{
=20
fprintf(stderr, Failed to connect to database: Error: \n%s\n,
=20
mysql_error(mysql));
=20
}
=20
else
=20
{
=20
printf(SUCCESS\n);
=20
iRetValue =3D3D mysql_query(mysql, SELECT * FROM user);
=20
=20
if( iRetValue !=3D3D 0 )
=20
{
=20
printf(Query Not Executed Properly.Please Check The Syntax.\n);
=20
}
=20
//here goes the error message :o)
=20
else
=20
{
=20
result =3D3D mysql_store_result(mysql);
=20
=20
num =3D3D mysql_field_count(mysql);
=20
printf(Number Of Rows :%d\n,num );
=20
=20
num_fields =3D3D mysql_num_fields( result);
=20
printf(Number Of Coloumns :%d\n,num_fields );
=20
=20
while ((row =3D3D mysql_fetch_row(result)))
=20
{
=20
unsigned long *lengths;
=20
lengths =3D3D mysql_fetch_lengths(result);
=20
=20
for(i =3D3D 0; i num_fields; i++)
=20
{
=20
printf([%.*s] \t, (int) lengths[i], row[i] ? row[i] : NULL);
=20
}
=20
printf(\n);
=20
}
=20
}
=20
}
=20
}
=20
=20
I would appreciate if you guys can help. thanks.
=20
=20
Elizabeth
=20
=20
=20
=20
--
For technical support contracts, goto https://order.mysql.com/?ref=3Densi=
ta
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
=20
=20
=20
=20
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
m
=20
--
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
Re: Character set on 4.1 and ujis support
Hi, you may use somethinh lik ethis : $dbh-do(SET character_set_results=ujis'); look at http://dev.mysql.com/doc/mysql/en/charset-general.html Hope that helps Mathias Selon Gleb Paharenko [EMAIL PROTECTED]: Hello. What do your 'show' statements return when you execute them from the perl script? Batara Kesuma [EMAIL PROTECTED] wrote: Hi, I just moved my DB from 4.0 to 4.1.11. I used mysqldump to dump the data first, and then inserted it to the new DB. The character set of the data is EUC-JP (ujis). My problem is, I can see the character correctly if I connect to mysql server using mysql client. For example: # mysql -e SELECT name FROM USER LIMIT 1; test_db But, when I call it from Perl DBI, I get data with wrong character set (ex. The character displayed as ??? mark). Here is my setting: mysql show variables like %character%; +--++ | Variable_name| Value | +--++ | character_set_client | ujis | | character_set_connection | ujis | | character_set_database | ujis | | character_set_results| ujis | | character_set_server | ujis | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.01 sec) mysql show variables like %collation%; +--+--+ | Variable_name| Value| +--+--+ | collation_connection | ujis_japanese_ci | | collation_database | ujis_japanese_ci | | collation_server | ujis_japanese_ci | +--+--+ 3 rows in set (0.00 sec) What did I do wrong? How can I fix this problem? Thank you very much. --bk -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do you think about PostgreSQL and mysql?
How do you think about PostgreSQL and mysql? want to know which is good? How to choose database for the web?
Re: How do you think about PostgreSQL and mysql?
Hi Stony, I didn't use PostgreSQL before, so couldn't comment. For MySQL, you have to beware of the following: - Store procedure, views, triggers is supported only in version 5, which is still in development phase. - clustering / failover feature is only out for about 9 months, so the stability in this area is yet to be proved. Other than that, it is easy to use and supported by many different programming language including C/C++, Java, python, .NET and etc. We are using MySQL in our server side product. So far, no single problem is found. -- Ka Chun How do you think about PostgreSQL and mysql? want to know which is good? How to choose database for the web? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem with mysqldump when there is a merge table
Hello, I have a database (on Win2k) with merge table. Mysqldump output includes some path information on the merged tables such as DATA DIRECTORY and INDEX DIRECTORY DROP TABLE IF EXISTS `rptpricing1996`; CREATE TABLE `rptpricing1996` ( `PricingId` int(11) NOT NULL default '0', `commodity` char(22) NOT NULL default '', `variables` char(7) NOT NULL default '', `PricingDt` date NOT NULL default '-00-00', `PricingHighPrice` decimal(12,2) default NULL, `PricingLowPrice` decimal(12,2) default NULL, `PricingAvgPrice` decimal(12,2) default NULL, PRIMARY KEY (`PricingId`), KEY `commodityDate` (`commodity`,`PricingDt`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='C:\SDVI\DB\mysql\data\dbsdvi\' INDEX DIRECTORY='C:\SDVI\DB\mysql\data\dbsdvi\'; When trying to load the database back (mysql dbsdvi dbsdvi.dump) the path information produces errors as mysql treats the \ character as an escaped character. Removing the path information eliminates the error, but how do I tell mysqldump not to output the path information? regards, nyem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why donn't work mysql_real_connect
Dear friends,
This is my first cpp module working with mySQL.
It looks like the error is located in
MYSQL *m=mysql_real_connect(myDB, host, user, passwd, ...
There is a way to find out step by step which parameters is wrong?
I am working with Fedora core 3
and MySQL 3.23.58
Thanks,
MT
++ CPP module +++
#include /usr/include/mysql/mysql.h
int modulMySQL() {
printf(modulMySQL 01\n); fflush(stdout);
// MYSQL *mysql_init(MYSQL *mysql)
// ===
MYSQL *myDB;
MYSQL *mysql_init(myDB);
printf(modulMySQL 02\n); fflush(stdout);
// MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char
// *user, const char *passwd, const char *db, unsigned int port,
// const char *unix_socket, unsigned long client_flag)
//
char *host=NULL, user[]=myname, passwd[]=mypass;
char db[]=ro;
unsigned int port=3306;
const char *unix_socket=NULL;
unsigned long client_flag=0;
MYSQL *m=mysql_real_connect(myDB, host, user, passwd,
db, port, unix_socket, client_flag);
printf(modulMySQL 03\n); fflush(stdout);
// int mysql_ping(MYSQL *mysql)
// ===
int p=mysql_ping(myDB);
printf(Return Values - Zero if the server is alive.p=[%i]\n, p);
}
+ Compilation Execution +++
CFG=/usr/lib/mysql/mysql_config
sh -c gcc -o myC `$CFG --cflags` -lstdc++ 01.cpp `$CFG --libs`
./myC 01_out.txt
++ 01_out.txt ++
modulMySQL 01
modulMySQL 02
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup and Maintenance Strategies
What have people done in the past regarding backup strategies? Is it adequate enough to rely on filesystem backups for mysql? Basically such that we can restore MySQL to the last filesystem backup. Is there a reason not to do this? I don't have any mission critical data and data that is lost since the last backup is acceptable. -James
