Re: History of changed rows
Thanks for all of you. I will test the suggestions and then reply. CPK -- Keep your Environment clean and green.
History of changed rows
Hi all. How can we manage the history of changed rows in the database. I have some idea but not yet implemented. By using triggers to make a copy of the row being changed to the another table in the same db. Is there any way to only save the changed fields data and field name? Any other idea? Thanks CPK -- Keep your Environment clean and green.
UTF-8 sorting
Hi, I am getting a problem while making a sort on any column having character set as UTF-8. The soring from a view or a query through ODBC is very slow. I have data in us-english and marathi languages at a time. Sorting is proper but very time consuming! What to do to solve this problem? Your Help is needed. Thanks CPK -- Keep your Environment clean and green.
MySQL database synchronization
I have a problem related with 2 mysql database synchronization. We are using a 256kbps internet at our mfg. site and a 2mbps internet connection at our HO. We are using MySQL5.0.45 for our ERP application. We want to work from both locations at a time through ERP software. For this we are trying to synchronize both servers are site and at HO. what will be the best solution for this? 1. Replication, (is it possible over 256kbps connection?) 2. Manual synchronization (using Navicat/SQLyog like software) 3. Using Binlogs (applying binlog to the another db) 4. any other We need urgent help regarding this. Thanks in advance and regards CPK -- Keep your Environment clean and green.
bulk updates/inserts and triggers
Hello, I am facing a problem related with triggers and bulk updates/inserts. I have two tables one is having 'transactions' and another is 'documents'. Each record in transactions table relates with a document by DocId. Foreign keys are created. I have activated triggers for transactions table for after insert, afters update, before delete. trigger makes a sum of amount in all transactions for a document referenced by docId in that perticular transaction and stores it in another temporary table and then updates the amounts in documents table. When I will go for inserting 10K records at a time, the insert becomes too slow. Why? Is there any solution to this? I am using MySQL 5.0.45 on Redhat Ent. Linux 5 - 64bit, 4 GB RAM, Xeon procesors and MySQL Connector ODBC 5.1 Beta, MS Acess 2003 on WindowsXp SP2. I also tried to inster this records directly by passing queries to MySQL, but still it is slow! Please help. Thanks CPK the scripts are as follows- -- -- Table structure for cb_canebills -- CREATE TABLE `cb_canebills` ( `ID` int(11) NOT NULL auto_increment, `AssociateSCPId` int(11) default NULL, `PeriodNo` int(11) default NULL, `SlipCount` int(11) default NULL, `TotalWeight` decimal(9,3) default NULL, `NormalWeight` decimal(9,3) default NULL, `JalitWeight` decimal(9,3) default NULL, `NormalAmount` decimal(15,2) default NULL, `JalitAmount` decimal(15,2) default NULL, `NormalRate` decimal(15,2) default NULL, `JalitRate` decimal(15,2) default NULL, `PayRate` decimal(15,2) default NULL, `TotalAmount` decimal(15,2) default '0.00', `HAmount` decimal(15,2) default NULL, `TAmount` decimal(15,2) default NULL, `HComissionAmount` decimal(15,2) default NULL, `TComissionAmount` decimal(15,2) default NULL, `TotalAgainstAmount` decimal(15,2) default '0.00', `NetPayAmount` decimal(15,2) default '0.00', `BankID` int(11) default '0', `BankAccNo` decimal(20,4) default NULL, `CaneBillNo` varchar(20) collate utf8_unicode_ci default NULL, `CaneBillDate` date default NULL, `AssociateType` int(11) default NULL, `CrushSeason` int(11) default NULL, `ChequeNo` varchar(10) collate utf8_unicode_ci default NULL, `ChequeDate` date default NULL, `ChequeAmount` decimal(15,2) default NULL, `BankOrCashAmount` decimal(15,2) default NULL, `InstallmentNo` int(11) default NULL, `tmpTS` timestamp NULL default '1999-11-11 11:11:11', `CreatedBy` int(11) default NULL, `CreatedTimeStamp` datetime default NULL, `LastModifiedBy` int(11) default NULL, `LastModifiedTimeStamp` datetime default NULL, `Locked` tinyint(4) default NULL, `CaneBillRemark` varchar(300) collate utf8_unicode_ci default NULL, `CoBranch` int(11) default NULL, `CoYear` int(11) default NULL, `CaneBillStatus` int(11) default NULL, `AccVoucherCreated` tinyint(4) default NULL, `Approved` tinyint(4) default NULL, `ApprovedBy` int(11) default NULL, `IsTemplate` tinyint(4) default NULL, `ReportH_RCS` int(11) default NULL, `ReportF_RCS` int(11) default NULL, `CaneBillCurrency` int(11) default NULL, `CaneBillExchangeRate` decimal(15,2) default NULL, `LastAccDate` date default NULL, `Billed` tinyint(4) default NULL, `tmpSelect` tinyint(4) default '0', `DocType` int(11) default NULL, `BillFromDate` date default NULL, `BillToDate` date default NULL, `Partial` tinyint(4) default NULL, `IsTemp` tinyint(4) default NULL COMMENT 'Temprory bill or not', `PaidThroughBankAccount` int(4) default NULL COMMENT 'bank account(associates) through which payment is issued', `BCId` int(11) default NULL, `PaymentThroughLedger` int(11) default NULL, `DCLogId` int(11) default NULL, `tmpSelectedByUser` int(11) default NULL, `AllowAllUsersToView` tinyint(4) default '-1' COMMENT 'view this doc to all while browsing except than created/Last', PRIMARY KEY (`ID`), KEY `First_billID` (`ID`), KEY `First_billP_no` (`PeriodNo`), KEY `FKAssociate` (`AssociateSCPId`), KEY `Indbcid` (`BCId`) ) ENGINE=InnoDB AUTO_INCREMENT=9455 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- -- Table structure for cb_canebilltransactions -- CREATE TABLE `cb_canebilltransactions` ( `CaneBillTransactionId` int(11) NOT NULL auto_increment, `DocId` int(11) NOT NULL, `DocType` int(11) default NULL, `AgainstId` int(11) NOT NULL, `AgainstAmount` decimal(15,2) unsigned zerofill default '0.00 ', `LineRemark` varchar(300) collate utf8_unicode_ci default NULL, `CreatedBy` int(11) default NULL, `CreatedTimeStamp` datetime default NULL, `LastModifiedBy` int(11) default NULL, `LastModifiedTimeStamp` datetime default NULL, `Locked` tinyint(4) default NULL, `tmpTS` timestamp NULL default '1999-11-11 11:11:11', `BankId` int(11) default NULL, `LoanSchemeId` int(11) default NULL, `DetailsForExtras` varchar(30) collate utf8_unicode_ci default NULL, `AddOrSubstract`
Storing Devnagari unicode data in MySQL
Namaskar, I am using Windows Xp SP2 and Mysql 5.0.45 and MyODBC 3.51.19 with Microsoft Marathi Indic IME 1 version 5. I am storing data in both languages i.e. Marathi and English. So I changed database character set to 'utf8 -- UTF-8 Unicode' and collation to 'utf8_unicode_ci'. Also I changed the field properties charset to 'utf8' and collation to 'utf8_unicode_ci' for all varchar and text fields. I am using MS Access 2003 as a front-end. When I am entering data in Marathi I can read and write data in Access tables, but when I am entering data I can view data in Marathi but after storing it in MySQL tables, I can not read data and just see question marks for the characters I entered. This is not happening only when I am storing data using Access as a front-end, but also I tried it by using other mysqk GUI tools, but i get same wrong results. Can any one please help me. Please give the reasons behind it and the solution to it. Thanks for your sincere help. Regards, CPK -- Keep your Environment clean and green.
Re: Storing Devnagari unicode data in MySQL
Thanks for your help. I upgraded MyODBC 3.51.19 to 5.1 beta, and tried it for Devnagari data entry and it worked well. But now a new problem of showing newly added record as '#Deleted' is there. and even after adding a time stamp field in the table, this problem exists,(this problem is solved in 3.51.19). Can any one please help? Thanks again CPK On 9/28/07, Jerry Schwartz [EMAIL PROTECTED] wrote: Try upgrading to a version 5 of the ODBC connector. It worked for our Chinese data. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Friday, September 28, 2007 1:55 PM To: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Storing Devnagari unicode data in MySQL Namaskar, I am using Windows Xp SP2 and Mysql 5.0.45 and MyODBC 3.51.19 with Microsoft Marathi Indic IME 1 version 5. I am storing data in both languages i.e. Marathi and English. So I changed database character set to 'utf8 -- UTF-8 Unicode' and collation to 'utf8_unicode_ci'. Also I changed the field properties charset to 'utf8' and collation to 'utf8_unicode_ci' for all varchar and text fields. I am using MS Access 2003 as a front-end. When I am entering data in Marathi I can read and write data in Access tables, but when I am entering data I can view data in Marathi but after storing it in MySQL tables, I can not read data and just see question marks for the characters I entered. This is not happening only when I am storing data using Access as a front-end, but also I tried it by using other mysqk GUI tools, but i get same wrong results. Can any one please help me. Please give the reasons behind it and the solution to it. Thanks for your sincere help. Regards, CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Fwd: more options for MySQL tools by MySQL
Hello all, I want to suggest one thing relating to MySQL Tools for 5.0(Administrator, Query browser etc.) - A new feature can be added to use command line options to synchronize structure and data, data transfer in many formats and scheduling above tasks on windows/linux and other OS. If these features will be available it will be better for users such as me to use only MySQL tools for all the needs. Please reply Thanks CPK -- Keep your Environment clean and green.
more options for MySQL tools by MySQL
Hello all, I want to suggest one thing relating to MySQL Tools for 5.0(Administrator, Query browser etc.) - A new feature can be added to use command line options to synchronize structure and data, data transfer in many formats and scheduling above tasks on windows/linux and other OS. If these features will be available it will be better for users such as me to use only MySQL tools for all the needs. Please reply Thanks CPK -- Keep your Environment clean and green.
Re: Synchronizing two MySQL servers over slow network
Thank you very much. The VPN bandwidth will be 128/256 kbps. Also we have to make updates to both locations simultaneously and our application is provided with locking mechanism for updating records and resolving conflicts to update same record. Also please give the details about term active/active and how it could be implemented? It can be possible to update the data to both servers and keep another for reporting only purpose. Is it possible to make HO as Master and Reporting as Slave and Site Server as another master and same Reporting as Slave. i.e. Reporting will be the salve for both masters in Replication? Thanks again CPK On 8/26/07, Gary W. Smith [EMAIL PROTECTED] wrote: for generating Statutory reports. Also cluster can not be a solution as it requires min. 100 MB network. Says who? But clustering won't help. You are looking for active/active, which could be accomplished but this would possibly lead to specific conflicts if people are trying to edit the same record. You've also failed to mention the speed you are talking about. My home office is a replication point for our large database at our CO. I do this over Cable. You could try active/active, assuming you work out some type of conflict resolution plan. If each site will generally be editing their own data, but combined for reporting only, then active/active should be fine, even if you are talking about 20KB/s. Can we generate scripts on windows to sync them manually? Thanks CPK On 8/25/07, Craig Huffstetler [EMAIL PROTECTED] wrote: I would probably recommend replication. It's not that bad to setup and once it catches up on the slave then it will continue to be an easy sync in the future as long as both are running. How big is the database you wish to synchronize? What connection are both servers on? Is there anyway possible to disable to VPN between the two D.B. servers? Perhaps a firewall in between is in use and you can simply setup access rules for both to talk to each other? The VPN tunnel is most likely a big slow down. On 8/25/07, C K [EMAIL PROTECTED] wrote: Hi, I have a problem as below- We have a MySQL server for our ERP database. Now we have to implement the ERP for Head office. HO is away from the current setup and connection between the two is through VPN at slow speed. How can we synchronize the two MySQL servers? Replication, Cluster, or manually? Thanks for your replies. CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Synchronizing two MySQL servers over slow network
Hi, I have a problem as below- We have a MySQL server for our ERP database. Now we have to implement the ERP for Head office. HO is away from the current setup and connection between the two is through VPN at slow speed. How can we synchronize the two MySQL servers? Replication, Cluster, or manually? Thanks for your replies. CPK -- Keep your Environment clean and green.
Re: Synchronizing two MySQL servers over slow network
We are using ODBC and Win Server 2K3. Also the database size is not so big ( i.e. upto 300 MB). The major problem is that both the databases at two servers will be same in structure and will be used by local group of users for daily operations. One of them must be treated as a MASTER for data. i.e. accounting will carried out at both, but only HO database will be considered for generating Statutory reports. Also cluster can not be a solution as it requires min. 100 MB network. Can we generate scripts on windows to sync them manually? Thanks CPK On 8/25/07, Craig Huffstetler [EMAIL PROTECTED] wrote: I would probably recommend replication. It's not that bad to setup and once it catches up on the slave then it will continue to be an easy sync in the future as long as both are running. How big is the database you wish to synchronize? What connection are both servers on? Is there anyway possible to disable to VPN between the two D.B. servers? Perhaps a firewall in between is in use and you can simply setup access rules for both to talk to each other? The VPN tunnel is most likely a big slow down. On 8/25/07, C K [EMAIL PROTECTED] wrote: Hi, I have a problem as below- We have a MySQL server for our ERP database. Now we have to implement the ERP for Head office. HO is away from the current setup and connection between the two is through VPN at slow speed. How can we synchronize the two MySQL servers? Replication, Cluster, or manually? Thanks for your replies. CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Password storage
Friends, I have one question - How to store passwords in MySQL database table in a secure way so that no one can see the password(understand the password string)? Please help Thanks CPK -- Keep your Environment clean and green.
Re: Password storage
Thanks to all, but the problem is that I am using external programs to insert data and I can't use MySQL functions directly. Can I call/implement such type of functions using MS Access 2003? Thanks CPK The md5 function encrypts the input string. - With Warm Regards, Sudheer. S www.binaryvibes.co.in www.lampcomputing.com -- Keep your Environment clean and green.
Fwd: [commercial] MySQL cluster setup and support
-- Forwarded message -- From: C K [EMAIL PROTECTED] Date: Aug 13, 2007 11:37 AM Subject: [commercial] MySQL cluster setup and support To: [EMAIL PROTECTED] Dear all, we are a medium sized company in India having mfg. facilities about 65 kms. from Pune and Head Office in Pune. We are running a mysql based ERP system for our operations. Now we are shifting some of our departments to Pune and requires support to connect two mysql database servers through VPN/Leased Line. If any commercial service provider from PUNE or nearby cities in INDIA only can give such service related to MySQL cluster/replication etc., please submit your proposals to [EMAIL PROTECTED] Please call 9975844665 for more details. Thank you. CPKulkarni -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Re: MySQL database synchronizing from 2 locations
Hello, As per your suggestions I tried to get some correct solution for the problem, but there is a big problem for replication and it is network connection. Though Internet is available to the Mfg. Site, it is not having good speed and continuous. So that replication may not be a good choice. As we are using Auto-increment fields for each table and it is Primary Key and also physical records are already marked with this PK. Is there any other solution for this? Please give the details. Thanks CPK -- Keep your Environment clean and green.
calling stored procedure trough ODBC
Hello, I am using MySQL 5.0.17 and MyODBC 3.51.14. I ma using MS Access 2003 as front-end Can I call stored procedures from Access ? how ? Also can we use ODBC driver for calling stored procedures or linking SP to Access? Please help Thanks CPK -- Keep your Environment clean and green.
MySQL database synchronizing from 2 locations
Hello, My client has a mfg. unit at 65 Km from a city in India. He wants to connect to his corporate office in the city. Both offices will use same data and same ERP system. He is using Win 2K3 server and MySQL 5.0.17. Is it possible to make them synchronized at a particular or regular intervals? How? Please give details. Options I think - Replication (is it possible for Windows?) Cluster (Is it possible?) Manual Sync by using Navicat or any other tool (other tools please) Please help. Prior Thanks, CPK -- Keep your Environment clean and green.
User Administration
Today I tried to set few user rights for a user. What I need is- There a database called 'mydb' Two users for Mysql 5 on Win 2003 server. One is root and second is 'systemuser'. 'systemuser' do not have right for anything on Mysql, information_schema, but should have only SELECT, INSERT, UPDATE and DELETE rights for 'mydb'. I tried it for a long, but didn't. I have to give SELECT as global privilege. Also I have to set INSERT, UPDATE and DELETE as global privilege. But due to this 'systemuser' can change data in 'mysql' database and I do not want this. If remove any of above from global privilege and set these to only 'mydb' it doesn't work. 'systemuser' will be used to connect to mysql as a default username for many software users. I need to give only SELECT, INSERT, UPDATE and DELETE rights for only 'mydb' while connecting from any location. What can I do? Please help. Thanks, CPK -- Keep your Environment clean and green.
MySQL cluster for windows
I have read some where that MySQL cluster will be available in 5.1 release, will it? Is there some progress in this regard? Thanks CPK -- Keep your Environment clean and green.
row lock
My client got a strange problem today. Mysql returned err no 1205. lock wait timeout exceeded. why? this error occured while updating the record. This error isproduced for a single record. I have checked if that record is in use while updating etc., but this record is not in use. I wait for some time again try to complete ithe operation. but isn't. Then I duplicated the record with new rimery key Id and then deleted the first record. Then changwd the new Id to old id. and it worked well. I can't understand why theabove error occuerred and the solution to it. can you please help and give the details. Thanks and regards cpk -- Keep your Environment clean and green. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing 2nd instance on windows.
Thanks to all for so good responce. Now I will experiment with it and reply earliest. Thanks CPK -- Keep your Environment clean and green.
Re: Installing 2nd instance on windows.
Dear friends, thank you for your response. but the problem is that when I try to install MySQL 5.0 from windows .msi installer on windows XP with MySQL 5.0 already installed, the installer does not shows any option regarding new installation. I can just rapair/remove the installation. Why? As I know we can install multiple instances of MySQL running for different ports, how to make it available on Windows? I need to run two different mysql servers on same machine at different ports(3306, 3307 etc) is it possible and how? Thanks again, CPK Keep your Environment clean and green.
Installing 2nd instance on windows.
Is it possible to install more than instances on Linux of MySQL 5.0? I am using WinXP SP 2 and MySQL 5.0.17. Thanks CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green.
Installing 2nd instance on windows.
Is it possible to install more than instances on Linux of MySQL 5.0? I am using WinXP SP 2 and MySQL 5.0.17. Thanks CPK -- Keep your Environment clean and green.
ODBC connector 3.51/5.0
Dear developers from MySQL, can i know when the MySQL connector/ODBc 5.0 will be released. I am facing few major problems regarding 3.51.12 on windows as application crash and wrong data display. please reply thanks, CPK -- Keep your Environment clean and green.
Re: ODBC connector 3.51/5.0
I HAVE TO TRY IT OUT. BUT THE PROBLEM IS THAT I HAVE MYSQL 5.0.17 AND I THINK 3.51.06 CAN NOT CONNECT TO 5.0.17. BUT I HAVE TO TRY. THANKS AND REGARDS CPK On 2/14/07, Ron Alexander [EMAIL PROTECTED] wrote: CPK, I'm not from MySQL but I had the same issue with MyODBC-3.51.12. The way I resolved the problem was to rollback to MyODBC-3.51.06. It resolved the issue. I hope this helps. Ron -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 1:01 PM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: ODBC connector 3.51/5.0 Dear developers from MySQL, can i know when the MySQL connector/ODBc 5.0 will be released. I am facing few major problems regarding 3.51.12 on windows as application crash and wrong data display. please reply thanks, CPK -- Keep your Environment clean and green. -- Keep your Environment clean and green.
No of columns in a table
I need information for Maximum no. of columns in a table in MySQL with diff. engines. Where can I get it? Thanks and regards, CPK
MySQL Error for Blobs
I got an error while entering an BMP image of 1.7MB size in a mediumblob field through MS Access 2003 with MyODBC 3.51.12 on Windows XP SP2 also with MySQL Query Browser and Navicat GUI tool. Navicat returned the error as 'Got a packet bigger than Max_allowed_packet bytes'. What this means?. Access and Query browser simple gives error as MySQL has gone away? Why? please help. Also please give a solution if we have to insert images/Other Objects of size more than 1 MB what we have to do using ODBC driver with/without MS Access VB.net? Thanks CPK
check bugs.
please check bugs and please give the answer Bug ID=24216 and 24216 CPK
Views accessed as table in MS Access
Hello, I am using MySQL 5.0.17 and MyODBC 3.51.12 with MS Access 2003/Xp with Jet 4.0. Currently it is not giving the normal problem of Access while inserting new records (#deleted), but when a View created in database is linked with MS Access it is not giviing correct results. e.g. There are two tables 'Accdocs' ad 'Accdoctransactions'. Each Accdoctransactions has a reerence in Accdocs (MasterDocId--AccdocId). I have to access all records from Accdocs and Accdoctransactions. When I create a view for the same, it is giving correct results when the view is opened using MySQL Query Browser. Then if I link that table in any MS Access file, as a linkedtable, it is not gving me correct results. Even I create the same view as a query it gives same results as Query browser. What is going wrong? If I use MS SQL Server 2000 then it gives correct results for above situation. Is it MySQL ODBC driver probelm? and if yes it is solved in latest ODBC 5 version? or is it Access error? Please help. It's urgent. Thanks, CPK
SQL Query help
Friends, I am developing a database for accounting software. I have one problem regarding calculation of balances on daily basis for all ledgers. I am using Access 2003 as frontend. While designing I found that maintaining of daily balances is impossible to client's requirements. But as the solution I to execute two SQL queries for 365 times to calculate Opening and closing balances. what i need is a hint/example to write a function/SQL statement to run these queries in single/minimum iterations. table format: LedgerID | Opening Credit | Opening Debit | Current Credit | Current Debit | Closing Credit | Closing Debit | Date Previous dates closing balance should be the opening for next date. Please suggest the answer. Thanks, CPK
Re: MS Access gives error no. -7776.
Today I also carried out some thing same as you. I already have Primary Keys in all tables. Then I added a temporary timestamp field with default as '1999-11-11 11:11:11'. Then I updated all such columns with above value. There is no 'on_update current timestamp' settings, just defined default value. Then I relinked all tables and now it is working ok. Now I will try it on other open source databases. Note that there is no foreign key defined yet. It is working OK on subforms also. Only one thing is that it is giving error for 'Save Record' action now. I also got jet 3.0 manual from microsoft's site. There are clear definations for such errors. But this page is out dated and I could not find any manual for jet 4.0.Please check it. I will try to send it's link in next email. Regards, CPK On 7/6/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 5:45 PM To: C K; mysql@lists.mysql.com Subject: Re: MS Access gives error no. -7776. C K wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: Contrary to the advice given on working with MS Access, I've found that I can *only* get things to work with Access 2003 if I remove the timestamp field. Also, make sure your primary key column isn't larger than an int ( ie don't use int unsigned, and don't use bigint ). I recently converted an application with 1M+ records to use mysql as the backend. I had similar errors until I: Make sure every table haa a unique key field Convert all timestamp fields to datetime (via alter table) Add one (and only one) last_changed timestamp, and fill it with a valid stamp Null out datetime fields that have '-00-00 00:00:00' as their value. Refresh the ODBC links. Access likes to use the primary key field + the last_changed timestamp in it's update queries so that it can easily manage optimistic locking. It issues update queries like Update some_table ... where primary_key = 27 and last_changed = '2006-12-01 23:52:55'. If the query doesn't affect any rows, then Access knows that someone else changed the record since it was initially retrieved, and can then prompt the user accordingly. Access also throws errors when it finds that timestamp or datetime fields have '-00-00 00:00:00'. Immediately after I loaded the mysql tables with the access data, I ran an 'update $full_table set $full_column = NULL where $full_column = '-00-00 00:00:00' on every datetime column in the database. In the case of the special last_changed column, I set all records to '1980-01-01 00:00:00' immediately following the conversion. Since this column was added by the conversion, it is never referenced in any forms, which seems to help things. The bad news is that all of the automatic date fields that were in the original .mdb file now have to be filled in with 'now()' in all sorts of places like forms and insert queries. I also had some trouble where a data entry subform would not allow additions. The 'add record' part of the record navigator would be grayed out. Usually, this was because I had neglected to have a primary key + last_changed timestamp on the table in question. In some cases, it was because a data source for a subform was a query. What I did to get those to work was to change the datasource to the table, and supply a proper filter/link. That aside, I feel much better having the data backed by mysql. The application has now been running successfully for about three weeks and users do not notice any difference in performance. Our regular backup capability now encompasses backup and restore for the data, which is great for us. Regards, Rich -- 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: MS Access gives error no. -7776.
It's link is- http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp CPK * On 7/6/06, C K [EMAIL PROTECTED] wrote: Today I also carried out some thing same as you. I already have Primary Keys in all tables. Then I added a temporary timestamp field with default as '1999-11-11 11:11:11'. Then I updated all such columns with above value. There is no 'on_update current timestamp' settings, just defined default value. Then I relinked all tables and now it is working ok. Now I will try it on other open source databases. Note that there is no foreign key defined yet. It is working OK on subforms also. Only one thing is that it is giving error for 'Save Record' action now. I also got jet 3.0 manual from microsoft's site. There are clear definations for such errors. But this page is out dated and I could not find any manual for jet 4.0.Please check it. I will try to send it's link in next email. Regards, CPK On 7/6/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: -Original Message- From: Daniel Kasak [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 04, 2006 5:45 PM To: C K; mysql@lists.mysql.com Subject: Re: MS Access gives error no. -7776. C K wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: Contrary to the advice given on working with MS Access, I've found that I can *only* get things to work with Access 2003 if I remove the timestamp field. Also, make sure your primary key column isn't larger than an int ( ie don't use int unsigned, and don't use bigint ). I recently converted an application with 1M+ records to use mysql as the backend. I had similar errors until I: Make sure every table haa a unique key field Convert all timestamp fields to datetime (via alter table) Add one (and only one) last_changed timestamp, and fill it with a valid stamp Null out datetime fields that have '-00-00 00:00:00' as their value. Refresh the ODBC links. Access likes to use the primary key field + the last_changed timestamp in it's update queries so that it can easily manage optimistic locking. It issues update queries like Update some_table ... where primary_key = 27 and last_changed = '2006-12-01 23:52:55'. If the query doesn't affect any rows, then Access knows that someone else changed the record since it was initially retrieved, and can then prompt the user accordingly. Access also throws errors when it finds that timestamp or datetime fields have '-00-00 00:00:00'. Immediately after I loaded the mysql tables with the access data, I ran an 'update $full_table set $full_column = NULL where $full_column = '-00-00 00:00:00' on every datetime column in the database. In the case of the special last_changed column, I set all records to '1980-01-01 00:00:00' immediately following the conversion. Since this column was added by the conversion, it is never referenced in any forms, which seems to help things. The bad news is that all of the automatic date fields that were in the original .mdb file now have to be filled in with 'now()' in all sorts of places like forms and insert queries. I also had some trouble where a data entry subform would not allow additions. The 'add record' part of the record navigator would be grayed out. Usually, this was because I had neglected to have a primary key + last_changed timestamp on the table in question. In some cases, it was because a data source for a subform was a query. What I did to get those to work was to change the datasource to the table, and supply a proper filter/link. That aside, I feel much better having the data backed by mysql. The application has now been running successfully for about three weeks and users do not notice any difference in performance. Our regular backup capability now encompasses backup and restore for the data, which is great for us. Regards, Rich -- 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: MS Access gives error no. -7776.
Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: d -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 8:29 AM To: mysql@lists.mysql.com Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hello, I recently converted a good size (1M records) access database to use MySQL as the backend. I had to do a number of conversion steps. All tables must have a primary key. After the tables were exported, I checked them to see that they did with a perl script. If they did not, I added a field called primary_key not null auto_increment and made it a primary key. I also had trouble getting forms to allow me to add records. I found that having the data source be a query, rather than a table (possibly with a filter) was the cause. All tables must have one and only one timestamp field. This was the worst issue. Access wants one timestamp field that auto updates so that it can do it's optimistic locking routine. I had to write a script to go through my database and convert all timestamp fields to datetime fields. Then, I had to go back and add a last_changed timestamp field to every table. The big issue here is that timestamp fields cannot be set to default to CURRENT_TIME or now(). I had to work through all of the forms and queries in the database and explicitly set them to now() as needed. Be careful of bit fields. In general, the conversion tool I used to initially populate the MySQL tables (sqlYog) seems to want to set bit fields to unsigned. This is bad for access, as it uses -1 for true. Make sure your bit fields get set to unsigned in the Mysql table definition. The other thing I've noticed is that datatype mappings aren't necessarily intuitive. I think currency fields wind up something like 19/4 in the mysql table. I tried changing their definition to something like 9/2, but then access starts throwing errors. Lastly, the original creator of our database did not set up any relationships. I had to manually add all of the foreign key constraints, and in order to do that I had to clean up the child tables such that there weren't any records that had missing parent records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MS Access gives error no. -7776.
On 7/4/06, C K [EMAIL PROTECTED] wrote: Thanks for your sugesstions. I tried to search this issue on microsoft's website. and found that this problem is related with timestamp fields. I have tried to connect from access 2k and 2003 to mysql database. It works well for all the things. but gives above error only when control jumps to subform with diff. table as it's recordsource. Strange thing is that on few PCs it gives error and on some it not gives any error. I have installed WinXP with SP2, Access 2003 with JET 4 sp 8. Also this error does not occurs when I used SQL server 2005 express edition I am tring to use seperate forms for dataentry. Thanks again CPK On 7/3/06, Duzenbury, Rich [EMAIL PROTECTED] wrote: d -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Sunday, July 02, 2006 8:29 AM To: mysql@lists.mysql.com Subject: MS Access gives error no. -7776. Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Hello, I recently converted a good size (1M records) access database to use MySQL as the backend. I had to do a number of conversion steps. All tables must have a primary key. After the tables were exported, I checked them to see that they did with a perl script. If they did not, I added a field called primary_key not null auto_increment and made it a primary key. I also had trouble getting forms to allow me to add records. I found that having the data source be a query, rather than a table (possibly with a filter) was the cause. All tables must have one and only one timestamp field. This was the worst issue. Access wants one timestamp field that auto updates so that it can do it's optimistic locking routine. I had to write a script to go through my database and convert all timestamp fields to datetime fields. Then, I had to go back and add a last_changed timestamp field to every table. The big issue here is that timestamp fields cannot be set to default to CURRENT_TIME or now(). I had to work through all of the forms and queries in the database and explicitly set them to now() as needed. Be careful of bit fields. In general, the conversion tool I used to initially populate the MySQL tables (sqlYog) seems to want to set bit fields to unsigned. This is bad for access, as it uses -1 for true. Make sure your bit fields get set to unsigned in the Mysql table definition. The other thing I've noticed is that datatype mappings aren't necessarily intuitive. I think currency fields wind up something like 19/4 in the mysql table. I tried changing their definition to something like 9/2, but then access starts throwing errors. Lastly, the original creator of our database did not set up any relationships. I had to manually add all of the foreign key constraints, and in order to do that I had to clean up the child tables such that there weren't any records that had missing parent records. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MS Access gives error no. -7776.
Dear Friends, I have connected MS Access to MySQL 5.0.17 with MyODBC 3.51.12 with MS Jet SP 8. It is giving error -7776 (There is no message for this error) while jumping from a form to a subform having two different tables for these tow forms as recordsource. Can any one please help me. It's urgent. Thanks in advance CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Right column type for OLE objects
But what about size. I reffered MySQL manual for 5.0, but didn't find that what is he maximum size of binary data we can store in such column. Please help Thanks and regards, CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what is this? -- errno=2006 errmsg=Server gone
I also got this error when uploading a PDf file of about 2.5 MB size. I am using MS access 2003 and MySQL 5.0. Also I tried to upload a file from Navcat and SQLyog, but it doesn't work. Smaller size, upto 500 KBs will work fine. CPK On 4/17/06, Martin Olsson [EMAIL PROTECTED] wrote: Hi, I'm still struggling with the errno=2006 err=server is gone. I thought I could provide some more info if that makes the problem any more clear: Basically, I'm uploading a file to a database. With no modifications to the form or the handler script it works on _some_ images and breaks on some. It's not a image size issue, I can happily upload some images larger than 2mb and it breaks on a 57kb image. Further I have examples of gif/jpg/png that works so it's not a file format issue either. The server runs locally (just as apache) and there is just one of them (no master/slave and/or replication stuff). I'm running everything on a win2k system with all service packs. I certainly wasn't shutting the server down at the moment and this error is 100% reprod on the specific files that break it. --- Joerg Bruehe; you pointed out that: this message is issued if the client gets an error reported while sending a command to the server. How can I determine if this is in fact the case? And in particular, how can I get my hands on the exact error sent? In general, is there any types of logging I can check and/or enable to further dig into the cause of this problem? regards, martin Martin Olsson wrote: Hi, I get this weird error message: ErrNo: 2006 Error: MySQL server has gone away. What does it mean? I couldn't find anything useful on google and the error message isn't exactly verbose.. :) regards, martin -- 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]
Right column type for OLE objects
Dear friends, I need to store OLE objects such as Word, Excel OpenOffice, Autocad documents in the database. Which will be the correct column type for it? Thanks and regards, CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reserevd Error -7776 -- Urgent
Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reserevd Error -7776 -- Urgent
On 4/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK Have you read through this: http://dev.mysql.com/doc/refman/4.1/en/msaccess.html or this: http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html or checked here: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp (quoted) -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned. My quick suggestion: Make sure that all of your timestamp and datetime fields are within their expected ranges. I am using MySQL server 5.0.17 and ODBC 3.51.12 for ODBC connection. When I update an y data from MySQL Administrator or SQLYog it gets updated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reserevd Error -7776 -- Urgent
I also tried folloing and got results as below: 1) I updated the default for timestamp filed as CURRENT TIMESTAMP as mentioned in MySQL 5.1 manual. But this not worked. 2) I dropped timestamp fiield from that table and refreshed link, then I can ork properly. Now I can insert and update reocrds without any problem. But this against the comment and responce from MySQL community that it is necessary to have a timestamp field to properly view and edit data in linked MysQL table. Else it will give he error as #deleted, But isn't it strange that now it is not giving me any such error. ? Why? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
