Welcome to WebmasterWorld Guest from 54.158.194.80

Forum Moderators: open

Message Too Old, No Replies

Database Problems HELP

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

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


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?
4:11 pm on Nov 9, 2016 (gmt 0)

Administrator from US 

WebmasterWorld Administrator not2easy is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 27, 2006
posts:3720
votes: 205


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...]

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

Senior Member

WebmasterWorld Senior Member Top Contributors Of The Month

joined:Apr 1, 2016
posts: 1863
votes: 470


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.
4:31 am on Nov 10, 2016 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:8167
votes: 609


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.
9:53 am on Nov 10, 2016 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1787
votes: 266


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.
11:22 am on Nov 10, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


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
12:07 pm on Nov 10, 2016 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1787
votes: 266


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.
2:08 pm on Nov 10, 2016 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:Sept 25, 2005
posts:1787
votes: 266


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.
6:04 pm on Nov 22, 2016 (gmt 0)

Moderator

WebmasterWorld Administrator webwork is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 2, 2003
posts:7984
votes: 64


So, Ron, was the mystery solved and what was the answer?
8:19 pm on Nov 22, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


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.