Re: [libreoffice-users] Re: LO Base Problem [SOLVED]
Thanks Alex and Paul!!! We have cured the problem!! Alex - your correction still gave me the SQL Error as before. Paul - your correction was on the nail!! Worked like a charm and my RecordID now Auto-Increments!! I appreciate GREATLY all this help guys!! As I mentioned I'm not - and don't want to be a DB Admin - I just need to be a DB user, as I have been for the past year or so. Yes I do my own set-up etc as best I can but am rather lost when the wheels fall off. Thanks again Guys - Greetings from South Africa IanW Pretoria RSA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 22/02/2015 22:36, Ian Whitfield a écrit : |ALTER TABLE Members CHANGE RecordID1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY Try it again, adding the length of field to the INT definition ALTER TABLE Members CHANGE RecordID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
I just checked on my MySQL, and the correct syntax is modify, not change, for the alter table statement. Change is for changing the name, I think. Also, the size of the int data type isn't necessary, but specifying primary key if the field is already the primary key results in a Multiple primary key defined error. Just omit the primary key part if it is already defined as the primary key, it will keep that constraint. So the correct syntax should be: alter table Members modify RecordID int unsigned not null auto_increment; Hope that does the trick. Paul On Mon, 23 Feb 2015 22:20:22 +0100 Alex Thurgood alex.thurg...@gmail.com wrote: Le 22/02/2015 22:36, Ian Whitfield a écrit : |ALTER TABLE Members CHANGE RecordID1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY Try it again, adding the length of field to the INT definition ALTER TABLE Members CHANGE RecordID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 21/02/2015 22:52, Ian Whitfield a écrit : Hi Ian, Any chance you can give us the full output of : describe FedSaints.Members; from the mysql command line interface ? You can also obtain the same information via MyAdmin, I'm just not sure which tab you have to click on (as it varies depending on the version of phpMyAdmin you are running). Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
On 02/22/2015 03:20 PM, Alex Thurgood wrote: You could try using : ALTER TABLE Members CHANGE RecordID1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY; Thanks Alex I gave this a try in MyAdmin and got ... Error *SQL query:* || |ALTER TABLE Members CHANGE RecordID1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY | *MySQL said: *Documentation http://localhost/phpmyadmin/url.php?url=http%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.1%2Fen%2Ferror-messages-server.htmltoken=8d06c7c288ebb2e01f21b98220996b09 |#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY' at line 1 | My SQL is Ver 5.1.73 and MyAdmin 4.2.10.1 IanW Pretoria RSA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
Thanks Alex But I fear this is getting nowhere. As per your suggestion I built a new ODB file and it showed the same problem. i have run the MySQLcheck program and it confirms the DB is OK. Maybe it is a funny in Base itself??? And it may come right with the next release?? In itself the problem does not make my DB unusable and I must press on with it so I guess the best is to just put the RecordID number in by hand and keep a paper record of the last used number. Seems ridicules but I guess it's what we all call a Workround. I really do appreciate your help and assistance nonetheless. With best wishes IanW Pretoria RSA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 20/02/2015 22:24, Ian Whitfield a écrit : FedSaints.Members OK So, on the face of it, your data tables are consistent and error free (with regard to their definitions). The problem then, would appear to lie with your ODB file. Something, some setting, or control, is unhappy with the change in the RecordID field and its new definition. Ideally, you would compare the contents of the before and after ODB files, e.g. using a diff command on the unzipped files present in the ODBs. That would point to where things have changed, and possibly how you might correct the problem. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 19/02/2015 15:00, Ian Whitfield a écrit : Hi Ian, But when I go back into my Database with the Base Front End the RecordID is still marked as 'No Auto-Increment' and still will not change!! What am I doing wrong and how do I urgently fix this?? Did you read Fernand's comment ? You need to look very carefully through your form's properties and controls to check where and if the form controls might have referenced the old field as this is stored in the ODB xml. I would add the following questions : - your old RecordID was just an INT that you filled in yourself - so now that you have converted it to INT AUTO_INCREMENT, did you regenerate the values, or have you just carried on from the previous lastinsert_id() of the RecordID field ? If the new values of your newly redefined field do not match the values you had originally, there will naturally be problems with form data representation... Ways around this : - reindex your table using the appropriate mysql command ; - run mysqlcheck from the command line I don't use phpMyAdmin a great deal, so I don't know whether you have access to these commands from that UI - if not, you will have to get your hands dirty and use the console/terminal, which is what most db admins do anyway. If your data _is_ coherent, then the problem lies with your form and/or LO. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 19/02/2015 15:00, Ian Whitfield a écrit : A quick, alternative possibility : Create a new ODB file that connects to your database. Save it under a new name. Open the newly named (and currently empty, at least with regard to forms, queries, etc) ODB file. Open the old ODB file. Try dragging and dropping your form from the old ODB file to the Forms window of the new ODB file. Now try opening that form in the new ODB file. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
Hi Alex
I really appreciate your help here!!
But still no luck - My replies are below.
A quick, alternative possibility :
Create a new ODB file that connects to your database. Save it under a
new name.
Open the newly named (and currently empty, at least with regard to
forms, queries, etc) ODB file.
I tried this and the ID field comes through as 'No increment' and still
will NOT change!!
Did you read Fernand's comment ?
Yes - There is only ONE field on my Form that calls this field. The one
I'm trying to change.
your old RecordID was just an INT that you filled in yourself -
No - it was empty, like all my fields), and I imported my old data from
a CSV file and then started to work. That,s when I discovered I had not
set it to Auto Increment.
so now that you have converted it to INT AUTO_INCREMENT, did you regenerate the
values, or have you just carried on from the previous lastinsert_id() of
the RecordID field ?
I can not change it so have not regenerate it.
All I can do at the moment is enter the next number by hand.
Ways around this :
- reindex your table using the appropriate mysql command ;
- run mysqlcheck from the command line
I'm NOT a DB Admin at all I just need this DB to keep all the details of my
Group. The DB is just a very important tool to me!!
I tried to run the 'mysqlcheck' command and I don't know what the output is but
this is what I got ...
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
- -
all-databases FALSE
all-in-1 FALSE
auto-repair FALSE
character-sets-dir(No default value)
compress FALSE
databases FALSE
debug-check FALSE
debug-infoFALSE
default-character-set (No default value)
fast FALSE
fix-db-names FALSE
fix-table-names FALSE
force FALSE
extended FALSE
host (No default value)
write-binlog TRUE
port 0
quick FALSE
silentFALSE
socket(No default value)
ssl FALSE
ssl-ca(No default value)
ssl-capath(No default value)
ssl-cert (No default value)
ssl-cipher(No default value)
ssl-key (No default value)
ssl-verify-server-certFALSE
use-frm FALSE
user (No default value)
Thanks again
IanW
Pretoria RSA
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 20/02/2015 15:00, Ian Whitfield a écrit : I tried to run the 'mysqlcheck' command and I don't know what the output is but this is what I got ... That is the output of the built-in help, which gets displayed when you run mysqlcheck without any parameters. Try : mysqlcheck -A -p the '-p' is only necessary if you need a password to access your database server. Preferably, you should run the above as superuser : sudo mysqlcheck -A -p The tables of all databases to which you have access rights, will be analyzed. Any errors in table data consistency will be displayed next to the given table. If you are using mysql, you really should learn the command line tools to administer your mysql server and databases. If you have no access to UI tools for whatever reason, it can save your bacon on more than one occasion. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
On 02/20/2015 04:47 PM, Alexander Thurgood wrote: Try mysqlcheck -A -p Thanks for the explanation Alex. This is what I get . FedSaints.Members OK mysql.columns_priv OK mysql.db OK mysql.eventOK mysql.func OK mysql.general_log Error: You can't use locks with log tables. status : OK mysql.help_categoryOK mysql.help_keyword OK mysql.help_relationOK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.servers OK mysql.slow_log Error: You can't use locks with log tables. status : OK mysql.tables_priv OK mysql.time_zoneOK mysql.time_zone_leap_secondOK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_typeOK mysql.user OK IanW Pretoria RSA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
Hi All I've spent today trying to cure this problem. I have only ONE table in my Database and needed to make the RecordID field Auto-incrementing. (I forgot when I set it up) Using the Base Front End it will *NOT* change the setting - as soon as you save it reverts to 'No' Using MyAdmin I ran - ALTER TABLE `Members` MODIFY `RecordID` INT AUTO_INCREMENT PRIMARY KEY; and got a positive return from that SQL statement. This is the only Primary Key and there are no other Auto-Incrementing fields. But when I go back into my Database with the Base Front End the RecordID is still marked as 'No Auto-Increment' and still will not change!! What am I doing wrong and how do I urgently fix this?? Thanks for any pointers. IanW Pretoria RSA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: LO Base Problem
On 02/18/2015 05:35 PM, Alexander Thurgood wrote: Le 18/02/2015 15:23, Ian Whitfield a écrit : Yesterday I found I had not set my Keyfield (RecordID) to Auto Increment so I set a new field with phpMyAdmin called 'id'. And this is now working fine and Auto incrementing. I deleted the old RecordID field. Why did you do that ? Why not just alter the existing Keyfield (RecordID) to autoincrement ? I tried several times and the Auto Increment just reverted to 'NO' every time I saved it. When you talk about the Keyfield, do you mean that this was a PRIMARY KEY or was it a reference for other tables (FOREIGN KEY)? Yes I mean the Primary Key - I have no Foreign Keys Did you attribute PRIMARY KEY status to the new field that you created? Did you recreate any references (FOREIGN KEYS) to other tables ? I deleted the old Primary key and marked the new field as Primary If you didn't, then by deleting your original RecordID field, you probably deleted the primary key and/or any references as well, and in all likelihood that is the reason why the forms that relied on that key to be set no longer work. In which case should I rather export my data out and start again?? My feeling is this might be the best and quickest way to go. Thanks for the help. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: LO Base Problem
Le 18/02/2015 15:23, Ian Whitfield a écrit : Hi Ian, Yesterday I found I had not set my Keyfield (RecordID) to Auto Increment so I set a new field with phpMyAdmin called 'id'. And this is now working fine and Auto incrementing. I deleted the old RecordID field. Why did you do that ? Why not just alter the existing Keyfield (RecordID) to autoincrement ? When you talk about the Keyfield, do you mean that this was a PRIMARY KEY or was it a reference for other tables (FOREIGN KEY)? Did you attribute PRIMARY KEY status to the new field that you created? Did you recreate any references (FOREIGN KEYS) to other tables ? If you didn't, then by deleting your original RecordID field, you probably deleted the primary key and/or any references as well, and in all likelihood that is the reason why the forms that relied on that key to be set no longer work. This is all a guess though without knowing exactly how your original RecordID field was defined and whether or not it had any relationships to other tables. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
