Mysql limitation in State table

MrKen · Post by **MrKen** » Mon Dec 07, 2009 5:50 am

Hi guys,

Have just been trying to setup a 'Day' state and a 'Night' state, which produced unusual results. Been having the problem with monitors being disabled when they should be enabled, but that one I may be able to deal with. But that's not the reason that I write.

The problem is that I currently have 33 monitors, and when I change State, the last 10 or 11 monitors turn red as if they don't exist. I queried the db and found that in the State table those last monitors are not listed. Why? Because the datatype for the Definition field is tinytext. Google/mysql tells me that tinytext has a maximum length of 255 characters.

So I think I need to change tinytext to something else. What would you suggest? Text?

And please excuse my ignorance, but is this the way to go?

alter table States change Definition ???????

Any assistance appreciated.

zm v1.22.3

MrKen · Post by **MrKen** » Fri Dec 11, 2009 4:04 am

Is nobody using more than 20 cameras?

Nobody ever had this issue before?

Nobody got any mysql advice?

Any help would be greatly appreciated.

Thanks.

coke · Post by **coke** » Fri Dec 11, 2009 4:11 pm

26 on my 1.24.1 install, perhaps a version upgrade might help you, wouldn't be surprised if it was fixed by then. Meanwhile, if you've got a desktop on it, install the mysql gui tools, "MySQL Administrator" will let you edit the tables without speaking SQL. (though learning SQL's a good thing to do)

If it's headless, install the tool on another machine and point it at the server.

Another decent, cross-platform SQL tool is DBVisualizer. It's not open source, but there's a free version that works great. I use it to muck with a POS system (ms sql), a wiki (mysql), and occasionally ZM.

MrKen · Post by **MrKen** » Sun Dec 13, 2009 5:13 am

Thanks for the reply Coke. Actually, I already have some basic mysql skills and have fixed the problem already.

I searched the forum and googled zoneminder, but couldn't find any mention of problems with the field type tinytext. I just wasn't too sure what I should upgrade tinytext to. I changed it to text.

I would be interested in the result of: mysql>describe State; on your install. Is the Definition field-type tinytext or text? If yours is tinytext, and you are using States, then I'd also be interested to know if all 26 cameras show up when you mysql>select * from State;
In my case, the last 10 or 11 cameras didn't make it into the limited field size of 255 characters, this in turn made the use of States totally useless.

After changing the field to text, then manually inserting my desired States into the db, everything is working fine. Added the cronjobs for Day State and Night State, and all is cruising along nicely.

Once again, thanks for your reply.

MrKen