Database Problems HELP - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Database Problems HELP

captainron19

1:42 pm on Nov 9, 2016 (gmt 0)

10+ Year Member

I am completely database illiterate. I needed a database built for my fire department website and I went with the site "Freelancer" to enlist someone to build it for me. They built it and it worked beautifully. Was able to have all members of department in there and allow sorting by name, start year, rank, station assigned to etc.

After about a week all of the data disappeared. Of course I did not have it backed up so i had to re-enter all the data for 120 people ! I got smart and backed it up. About a week later all data was gone again. I reached out to the freelancer that built it and they restored data (of course they asked for payment to do additional work) but I informed him the database is not working up to my expectations as it is dumping the data.

About 2-3 weeks went by with everything being well and yesterday the data was all lost again.

I reached out to the guy again and he requested root access in to my server (I have a VPS) and I obviously do not feel comfortable with providing access. I at first was worried this may be a little ploy of them dumping the data to get more work from me and get more money. I have changed the cpanel password after he logged in to design the DB but not sure if something shady is going on.

Can anyone provide input on what my next course of action should be to find out why the DB is losing its data?

not2easy

4:11 pm on Nov 9, 2016 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

When you changed the cpanel password, did you also change the password/username on the database? When you say you backed up your database, is it in .sql file format?

BTW - there is a list of highly qualified and reputable people you can hire who are members of WebmasterWorld. The various specialties are listed at the bottom of each page, in the footer. You can see some PHP wizards right here: [webmasterworld.com...]

;)

NickMNS

2:09 am on Nov 10, 2016 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

If you only have 120 records do you really need a database at all? You could store the data in a JSON file. It would look something like:
{"0":
{
"name":"John Smith",
"start year": 2000,
"rank": "chief",
"station assigned": "Station 51"
},
1:
{
"name":"Mike Brown",
"start year": 2010,
"rank": "assistant chief",
"station assigned": "Station 51"
},
Then using Javascript you can access all the data in one file stored on your server. It can be sorted as needed and updated. There is no need to carry all the overhead of maintaining database.

tangor

4:31 am on Nov 10, 2016 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

I agree that 120 records, or up to 100,000 plus records---depending on data type and number of fields---in a flat file db makes sense. Perl, php, Python and others can be used to manage and return results from that with very little overhead or cost. .... and the data is on YOUR system at all times.

robzilla

9:53 am on Nov 10, 2016 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

There's not that much overhead to a MySQL database, or work required for maintaining (a small) one, and out of the box databases are easier to manage, search and expand than flat files, in my opinion. Obviously, what you're experiencing is not supposed to happen. Did your freelancer also build a management panel (in PHP, for example) to modify the information stored in the database? Is it password-protected or can anyone with the URL access it? Inadequately protected administration panels can sometimes be crawled by robots (rogue or legitimate), which can cause data loss. Also, is the database connected to the frontend of your website? If SQL queries are not well written, SQL injection (manual or automated) may be possible, which could also lead to data loss.

Since you're running your own VPS, make sure the firewall is set up properly so MySQL and other services used locally cannot be accessed from the internet.

captainron19

11:22 am on Nov 10, 2016 (gmt 0)

10+ Year Member

Thanks for the replies guys here is some mroe info

As far as the password when I changed it on cpanel after the work was done. I did not change any other passwords for the DB but the setup worked just fine after the pw change. The data only dumped about a week after building the DB then it disappeared about 2-3 weeks after restoring the data form a backup.

A DB management page was setup for me and it is password protected.

I never had any experience with a JONS file but willing to give it a try so i will have to learn more on that but will it allow for users to sort data? Right now we can sort by last name (ascending or descending order) and also sort by rank, start year, station etc

robzilla

12:07 pm on Nov 10, 2016 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

Yes, but the logic will have to be moved from the MySQL engine to PHP entirely, so much of your system will have to be rewritten. JSON is simply a format for storing data, much like XML, which you already seem to be using.

robzilla

2:08 pm on Nov 10, 2016 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Top Contributors Of The Month

So (I can't edit my post) if what you have now works for you with the exception of the data somehow disappearing, all you need to do is find out why that is happening. If you don't have the expertise, you can't really avoid having to give someone else access to your server.

Webwork

6:04 pm on Nov 22, 2016 (gmt 0)

WebmasterWorld Administrator

10+ Year Member

Top Contributors Of The Month

So, Ron, was the mystery solved and what was the answer?

captainron19

8:19 pm on Nov 22, 2016 (gmt 0)

10+ Year Member

Dont know.... been 2 weeks now and the data is holding. I contacted the guy that built the DB for me on freelancer website and all he did was restore it from a backed up version. He of course wanted full access to my server (I have a VPS) and I did not feel comfortable giving him access..... (Think he is from the Middle East or somewhere close) - I did not really know how to access the logs of the server and what to look for so i did contact my server company and gave them the details along with the approx date that the data dumped. They said they inspected the logs and found nothing out of the ordinary.