I thought about that, which is why I was asking about your version and such. However, even when an issue is closed, it doesn’t mean it can’t resurface. The kicker for me was the Tuya USB extenders as I’ve never heard of them, but I do know that both Xiaomi and Tuya both like to use Xbee-ish boards in some of their products, so I wasn’t 100% certain that wasn’t the issue.
I’m actually hopeful that it is just a corrupt DB. There are some methods to take the zigbee.db file, copy it to your local machine and try to repair it using any Sqlite tool (there’s TONS of them available for nearly every operating system). That could work.
Otherwise, if you have backups of your /config directory (or snapshots), you might be able to pull an older version of your zigbee.db and replace the one you have with an older version when things were working properly.
Worst case, stopping HA, deleting (or renaming without the .db extension) zigbee.db and then restarting HA will take you back to scratch and you’ll have to repair everything.
Thanks again So start with the repair or the zigbee database do you think? Find a way to copy of to my local machine and replace it back. Is it as simple as that or do I need to start/stop services etc. Sorry for the dumb questions, unlike you I don’t write code
PS where in the world are you if you don’t mind me asking?
You can do this in a number of ways; SSH, rsync, samba, etc. Whatever you use to get to your .yaml config files, that’s the mechanism that you would use.
You’ll want to shut down HA before fiddling with the database. You can leave it up when you copy the db to your local machine, but you’ll want to shut it down before replacing/deleting the zigbee.db file on your HA instance.
Thanks again. I have a developer that works for me he knows databases backwards so I have engaged him to help a little here. He is going to compare a database from a while back. In the meantime do you think these look corrupt or so you think it’s just weird characters. He Fragma integrity test and it ran OK.
Yeah, but what table within zigbee.db? For instance this is the endpoints table. I’ve never seen weird characters like that in any of the tables in the DB.
It’s the attributes table. I can share stuff with you privately if that helps and happy to pay for someone’s time as it’s better than grief from the better half
Ok. The attributes table shouldn’t have weird characters in it like that… BUT… to be fair, I don’t know what the Tuya/Xiaomi devices throw in there, but your Philips and Gledopo devices shouldn’t be throwing foreign characters in there like that.
I’m going to go out on a limb and say that there either was some corruption that happened at some point or currently.
So, if you have the time, what you could do is keep that file backed up on your system. Shutdown HA, kill the zigbee.db file there and then restart HA. Pair a couple of devices (I’d go with devices that are close to you that are easy to pair) and then grab a copy of that generated zigbee.db and examine it in the same way. If there are no wonky characters, then the database is absolutely the culprit and that’s going to really suck, because repairing 150+ devices is just craptastic.
No payment needed at all! I’m happy to help as much as I can.
As a reference, this is what Philips devices look like in my attributes table:
Thanks mate. You are restoring my faith in forums
I am at work at preset so will go home tonight and try. My other thought was to search for one of the mac addresses and see if it’s one of the failing devices. If there a way of doing that? Obviously the attributes table in the zigbee.db is not good as it shows weird characters.
Yup. If you are using something like DB Browser for SQLite, you can use the following SQL:
SELECT * FROM attributes where ieee = '[insert mac here]'
You can also select against the devices table as well.
Also, you might want to hop on the HA Discord and jump in the #zigbee channel. There’s tons of Zigbee gods there that might be able to help out further as well.
Thanks. We had another look at the zigbee.db. All the weird characters are appearing in the ‘default light link group’. None appear to be in the groups that I know are the issue.
I will try the discord forum, just never had any luck there as I am not technical enough!
What I find the weirdest of all here is that although in HA the lights that are issues you cannot individually turn them on or off you can use the zha zigbee group and they work normally!!?
Last question then eh! Then you can go and eat your dinner I imagine! What would you do first?
A: Do you think I should try deleting individual values from the attributes table, or should I try deleting all attribute values related to individual MAC addresses (that have weird characters in the value column).
Don’t edit the tables directly. Bad things will happen. I would start with a clean db on your HA instance, pair a couple of devices, check the newly created DB and see if there are weird characters in there again. That should pinpoint the DB as being the culprit here.
I’d reach out to him (Adminiuga is the other person I would recommend) at the same time as this might be something they have seen before.
Also, @Hedda (also a REALLY good Zigbee person) might chime in as well (hint, hint)
Alright I will have a play tonight and hope one of the gods chimes in. Is it usually OK to go to discord and point to thread here or re-post the whole lot do you think?
Group messages are sent via a broadcast. It is perfectly reasonable that a group message may work while direct messages to a device may not. The database is not involved in message transmissions in any way at all.
Look at the network visualization and figure out which device(s) are the parent of the lights that direct communication don’t work for. Then power cycle that device. There are many bad routing devices with firmwares that crash or otherwise disrupt message transmissions. Usually a quick power cycle will correct it.
Hey David. Thanks for the reply’s. I have largely power cycled bulbs where possible and turning off the main circuit breaker(s), re-pairing and reloading ZHA. No sooner do I do this but some of the same and others will drop off within minutes or hours. They will either or both become ‘unknown’ or show connected but become individually unresponsive - while remaining working as a group.
The kicker for me was the Tuya USB extenders as I’ve never heard of them, but I do know that both Xiaomi and Tuya both like to use Xbee-ish boards in some of their products, so I wasn’t 100% certain that wasn’t the issue.
So start with the repair or the zigbee database do you think? Find a way to copy of to my local machine and replace it back. Is it as simple as that or do I need to start/stop services etc. Sorry for the dumb questions, unlike you I don’t write code 
