LO Base Problem

Ian_Whitfield · February 18, 2015, 2:24pm

*Hi All*

I have been rebuilding/revamping my Database over the past few weeks and everything has been going great.
I'm using LO 4.3.5.2 and MySQL 5.1.73 on PClinuxOS (latest).

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.

Now many of my fields are greyed out in the Form and will not accept input and all my drop-down boxes are inactive.

I created a new temporary Form (using the Wizard) and it works fine and displays all fields. But as soon as I replace a field with a drop-down or Combo Box box that box becomes inactive again and is greyed out and will not display the options.

I've looked everywhere in the field properties but can find no obvious cause.

Anyone have any ideas please?? Thanks.

IanW
Pretoria RSA

iplaw67 · February 18, 2015, 3:35pm

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

SOS · February 18, 2015, 4:36pm

*Hi All*

I have been rebuilding/revamping my Database over the past few weeks and everything has been going great.
I'm using LO 4.3.5.2 and MySQL 5.1.73 on PClinuxOS (latest).

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.

Now many of my fields are greyed out in the Form and will not accept input and all my drop-down boxes are inactive.

probably in your "data" properties from the greyedout ListBoxcontrols you have used in a sql statement the name off the deleted field or the data connection is based on this deleted fields ?

Hope it helps

fernand

Ian_Whitfield · February 18, 2015, 5:55pm

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.

Ian_Whitfield · February 19, 2015, 2:00pm

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

iplaw67 · February 20, 2015, 8:59am

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

iplaw67 · February 20, 2015, 9:05am

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

Ian_Whitfield · February 20, 2015, 2:00pm

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-info FALSE
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
silent FALSE
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-cert FALSE
use-frm FALSE
user (No default value)

Thanks again

IanW
Pretoria RSA

iplaw67 · February 20, 2015, 2:47pm

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

Ian_Whitfield · February 20, 2015, 9:24pm

Thanks for the explanation Alex. This is what I get .....

FedSaints.Members OK
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can't use locks with log tables.
status : OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
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_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

IanW
Pretoria RSA

iplaw67 · February 21, 2015, 2:46pm

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

Ian_Whitfield · February 21, 2015, 9:53pm

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

iplaw67 · February 22, 2015, 1:20pm

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

Ian_Whitfield · February 22, 2015, 9:36pm

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.html&token=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

iplaw67 · February 23, 2015, 9:20pm

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

Paul16 · February 23, 2015, 9:48pm

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

Stefan_v_Wachter · February 24, 2015, 8:25am

Hi,
I have read that I can write extensions for LO in Java. How can I use these new java functions as event handlers for base forms? Do I have to write wrappers in Basic? I have some experience with Java and the Netbeans IDE so I would prefer Java instead of Basic. Is there a sample project anywhere? (I am on MacOSX 10.9.5)

Thanks in advance, Stefan

Ian_Whitfield · February 24, 2015, 1:40pm

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

Tom_Davies1 · February 25, 2015, 10:42am

Hi
I am pretty sure it can be done. Somewhere there is a sub-menu showing the
different languages that can be used and i vaguely remember another
language it allowed was Python.

I just have no idea how to go about this. Even for a macro it might be
best to contact the devs irc (or is it iirc?) or mailing list or something
to see if you can get help from them. Usually this mailing list is
excellent at giving advice on macros and you night find some post that
gives the link to Andrew Pitonyak's guide to macro programming. I think he
focusses on Basic but hopefully that should give a clue about what to do
with the 'finished' code. ( ime these things are never finished as there
is always something top tweak or a bug-fix or something new to add in! ).
I am a tad busy today but hopefully later i might be able to dig out the
link to our wiki-page to get the macro guide.
Regards from
Tom

Tom_Davies1 · February 26, 2015, 5:16pm

Hi
I have just realised that the subject-line says "macros" but the contents
of the initial email said "Extensions". These are very different things!

As i understand it macros are contained within one or many documents.
Extensions are like Add-ons or Plug-ins and change the actual program
itself.
Regards from
Tom