Trouble getting oracle DB working

Configuration
1

Hi,

Been using home assistant for a while now, but the default DB is getting large for me and I want to try doing some more intensive queries and additional logging soon. Since I use oracle alot for other stuff, I thought maybe worth trying to get that setup at home, and after a bit of config I think I’m there, and can see connections coming in.

Problem I’ve now got is what looks like schema creation, I’m stuck with these errors:
tail: hass.log: file truncated
2019-02-20 19:56:09 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: None
2019-02-20 19:56:09 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (cx_Oracle.IntegrityError) ORA-01400: cannot insert NULL into (“HASS”.“SCHEMA_CHANGES”.“CHANGE_ID”) [SQL: ‘INSERT INTO schema_changes (schema_version, changed) VALUES (:schema_version, :changed) RETURNING schema_changes.change_id INTO :ret_0’] [parameters: {‘schema_version’: 7, ‘changed’: datetime.datetime(2019, 2, 20, 19, 56, 9, 375077), ‘ret_0’: <cx_Oracle.STRING with value [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]>}] (Background on this error at: http://sqlalche.me/e/gkpj)

I can see that the tables have been created though which is positive at least, but is there some issue with how they’ve been made - should these allow nulls? I have tried manually updating table config but still no joy, but that might be the wrong direction anyway.

Appreciate it might not be a common DB backend choice but any pointers/suggestions would be gratefully received!

2

IntegrityError

Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails.

This error is a DBAPI Error and originates from the database driver (DBAPI), not SQLAlchemy itself.

I use Postgresql. Let me know if a schema dump would be useful to you.

3

Yeah, the error seems feasible since there is a column defined in schema as not allowing null values, I guess that could be causing the integrity check fail.

Would be a help if you could share your schema definition for that table, then I can see if the schema isn’t created correctly in my setup, or it might just be that I need to populate it with some data - I could dump out from the file DB and import it over I guess.

4

Here’s my schema definition. Hope it helps.

--
-- PostgreSQL database dump
--

-- Dumped from database version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)
-- Dumped by pg_dump version 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: events; Type: TABLE; Schema: public; Owner: homeassistant
--

CREATE TABLE public.events (
    event_id integer NOT NULL,
    event_type character varying(32),
    event_data text,
    origin character varying(32),
    time_fired timestamp with time zone,
    created timestamp with time zone,
    context_id character(36),
    context_user_id character(36)
);


ALTER TABLE public.events OWNER TO homeassistant;

--
-- Name: events_event_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.events_event_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.events_event_id_seq OWNER TO homeassistant;

--
-- Name: events_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.events_event_id_seq OWNED BY public.events.event_id;


--
-- Name: recorder_runs; Type: TABLE; Schema: public; Owner: homeassistant
--

CREATE TABLE public.recorder_runs (
    run_id integer NOT NULL,
    start timestamp with time zone,
    "end" timestamp with time zone,
    closed_incorrect boolean,
    created timestamp with time zone
);


ALTER TABLE public.recorder_runs OWNER TO homeassistant;

--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.recorder_runs_run_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.recorder_runs_run_id_seq OWNER TO homeassistant;

--
-- Name: recorder_runs_run_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.recorder_runs_run_id_seq OWNED BY public.recorder_runs.run_id;


--
-- Name: schema_changes; Type: TABLE; Schema: public; Owner: homeassistant
--

CREATE TABLE public.schema_changes (
    change_id integer NOT NULL,
    schema_version integer,
    changed timestamp with time zone
);


ALTER TABLE public.schema_changes OWNER TO homeassistant;

--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.schema_changes_change_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.schema_changes_change_id_seq OWNER TO homeassistant;

--
-- Name: schema_changes_change_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.schema_changes_change_id_seq OWNED BY public.schema_changes.change_id;


--
-- Name: states; Type: TABLE; Schema: public; Owner: homeassistant
--

CREATE TABLE public.states (
    state_id integer NOT NULL,
    domain character varying(64),
    entity_id character varying(255),
    state character varying(255),
    attributes text,
    event_id integer,
    last_changed timestamp with time zone,
    last_updated timestamp with time zone,
    created timestamp with time zone,
    context_id character(36),
    context_user_id character(36)
);


ALTER TABLE public.states OWNER TO homeassistant;

--
-- Name: states_state_id_seq; Type: SEQUENCE; Schema: public; Owner: homeassistant
--

CREATE SEQUENCE public.states_state_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.states_state_id_seq OWNER TO homeassistant;

--
-- Name: states_state_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: homeassistant
--

ALTER SEQUENCE public.states_state_id_seq OWNED BY public.states.state_id;


--
-- Name: events event_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.events ALTER COLUMN event_id SET DEFAULT nextval('public.events_event_id_seq'::regclass);


--
-- Name: recorder_runs run_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.recorder_runs ALTER COLUMN run_id SET DEFAULT nextval('public.recorder_runs_run_id_seq'::regclass);


--
-- Name: schema_changes change_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.schema_changes ALTER COLUMN change_id SET DEFAULT nextval('public.schema_changes_change_id_seq'::regclass);


--
-- Name: states state_id; Type: DEFAULT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.states ALTER COLUMN state_id SET DEFAULT nextval('public.states_state_id_seq'::regclass);


--
-- Name: events events_pkey; Type: CONSTRAINT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (event_id);


--
-- Name: recorder_runs recorder_runs_pkey; Type: CONSTRAINT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.recorder_runs
    ADD CONSTRAINT recorder_runs_pkey PRIMARY KEY (run_id);


--
-- Name: schema_changes schema_changes_pkey; Type: CONSTRAINT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.schema_changes
    ADD CONSTRAINT schema_changes_pkey PRIMARY KEY (change_id);


--
-- Name: states states_pkey; Type: CONSTRAINT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.states
    ADD CONSTRAINT states_pkey PRIMARY KEY (state_id);


--
-- Name: ix_events_context_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_context_id ON public.events USING btree (context_id);


--
-- Name: ix_events_context_user_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_context_user_id ON public.events USING btree (context_user_id);


--
-- Name: ix_events_event_type; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_event_type ON public.events USING btree (event_type);


--
-- Name: ix_events_time_fired; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_events_time_fired ON public.events USING btree (time_fired);


--
-- Name: ix_recorder_runs_start_end; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_recorder_runs_start_end ON public.recorder_runs USING btree (start, "end");


--
-- Name: ix_states_context_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_context_id ON public.states USING btree (context_id);


--
-- Name: ix_states_context_user_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_context_user_id ON public.states USING btree (context_user_id);


--
-- Name: ix_states_entity_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_entity_id ON public.states USING btree (entity_id);


--
-- Name: ix_states_entity_id_last_updated; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_entity_id_last_updated ON public.states USING btree (entity_id, last_updated);


--
-- Name: ix_states_event_id; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_event_id ON public.states USING btree (event_id);


--
-- Name: ix_states_last_updated; Type: INDEX; Schema: public; Owner: homeassistant
--

CREATE INDEX ix_states_last_updated ON public.states USING btree (last_updated);


--
-- Name: states states_event_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: homeassistant
--

ALTER TABLE ONLY public.states
    ADD CONSTRAINT states_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.events(event_id);


--
-- PostgreSQL database dump complete
--
1 Like
5

Thanks - yeah that was a help, it looks like I have matching schema setup for those columns not allowing null values.

I’ve raised a bug report, maybe oracle db setup isn’t working/supported

6

Just to update incase helps anyone, I got it working by manually updating part of the schema based on @ceh output. I think Oracle changed a bit around version 11 to 12 and upwards, on how it handles incrementing columns, and maybe the default definition isn’t quite right for newer versions. Switching those columns to be NUMBER GENERATED ALWAYS AS IDENTITY, solved the issue.

I’ve since imported all the old sqlite DB contents into the Oracle DB and all seems to be working pretty well so far.

1 Like
7

hey @vb216, I stumbled upon your thread – how has your oracle db been performing over the past few months? did you move to anything else?

Also, Oracle announced this week an always free tier for their autonomous database and thought I’d try it out with Home Assistant. I provisioned one to my cloud account and have been trying to get the recorder to connect to it and create the tables. I did something similar with an AWS RDS mariaDB instance and it worked great.

After doing some research in the SQLalchemy docs, it looks like this is the connection string format:
oracle+zxjdbc://user:pass@host/dbname

However, I’m not getting anything when I look for the homeassistant table in SQL Developer. Any thoughts?

8

hey @LUNZ it’s been going pretty well! haven’t swapped to anything else, been pumping alot of data into it and performance has been good.

I connect with connection string like this:
recorder:
db_url: oracle+cx_oracle://hass:hass@oracle:1521/?service_name=hass_db

Maybe worth checking you can connect into the DB with sqlplus or sqldeveloper first, it took me a while to get the DB setup properly on that front.

Only other thing needed was to manually setup the schema, there was as little bit of work there needed.

Hope that helps!

1 Like
9

thanks for the reply!

I couldn’t get it to work but I’m still trying. One thing with the autonomous Databases is they use a wallet.zip file in the connection process with sqldeveloper. I’m not sure if there are other ways to connect with just username/password or if home-assistant can use the wallet.zip files.

Also, can you please export your schema (as a file with no data) and share it with me so I can import it into my database?

What I ended up doing in the meantime is setting up a MariaDB with a docker-compose file and connecting to that. It’s fine, but still looking to ultimately move it to the autonomous DB.

10

Hello @vb216 and @LUNZ,

I am trying to get recorder to send data to my oracle db but I am getting the following error:
(Recorder) [homeassistant.components.recorder] Error during connection setup: No module named ‘cx_Oracle’

I have setup an instant client and cx_oracle installation (https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html) and I can get the myscript.py to work.

But when I try through HA it does not seem to know about the cx_Oracle, did you encounter a simular issue?

Many thanks

11

Doesn’t ring a bell I’m afraid. Are you running HA in a under a separate virtual env, as it sounds like it can’t find the module still? Assuming your using same syntax shown earlier to point it to the DB, then I can only guess it’s something about the module installation.

12

Hi, I was trying to implement oracle database with homeassistant. I managed to connect to the oracle database but it is trying to the database upgrade for the first time but the Script is failing as it is not oracle script. Did you encounter the same issue?

2020-04-15 13:30:26 WARNING (Recorder) [homeassistant.components.recorder.migration] Database is about to upgrade. Schema version: 0
2020-04-15 13:30:26 ERROR (Recorder) [homeassistant.components.recorder.util] Error executing query: (cx_Oracle.DatabaseError) ORA-00904: “END”: invalid identifier
[SQL: CREATE INDEX ix_recorder_runs_start_end ON recorder_runs (“start”, end)]
(Background on this error at: http://sqlalche.me/e/4xp6)
2020-04-15 13:30:26 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (cx_Oracle.DatabaseError) ORA-00904: “END”: invalid identifier

Thanks,
Jason

13

That doesn’t ring a bell I’m afraid, but maybe as I imported data in from another database, so it didn’t try to do any upgrade.

Could be worth a try - use the default DB type, dump out the data, and import it in to Oracle

14

Hi,

I am trying to connect Oracle DBA with no luck so far. I keep getting error:
“(Recorder) [homeassistant.components.recorder] Error during connection setup: No module named ‘cx_Oracle’.”

I see that you are at least able to connect database. What configuration for Home Assistant are you using?

I Have tried two setups - one running HassOS on Virtualbox, another one - Hass in Docker on Raspberry PI 4. For both setups I get the same error: No module named ‘cx_Oracle’

15

hi Mmodestas,

I am running my homeassistant via virtual box and using virtual env.

I have to install cx_Oracle via virtual env. using the instruction from https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html

Thanks,
Jason

16

Thank you for reply. I will try this approach.