[CalEvans]

Quote:

Is their any way to update without having a primary key/ And why set it up this way?

A qualified no.

#1 You should never create a table without a primary key.

You MAY be able to update and build a where clause that includes enough of your fields so that the resulting update only updates 1 record.

Code:

Update myBrokenTable set name='Cal' where address='myaddress' AND city='myCity' AND zip='myZip' AND phone='myPhone';

This may work but unless you've got one of those fields set to unique then there is no guarantee that the same address won't be in your table more than once. (In that case all, records would be updated.)

Quote:

PHPMyAdmin allows updates without a primary key.

And so does Access but you don't see me running to use it either. Just because a client (phpMyAdmin is a client) will allow you to do something does not mean that it's a good thing to do. I'm not sure how phpMyAdmin accomplishes this feat of magic because SQL by it's very nature does not have record or row numbers. Therefore to issue an update you have to construct an UPDATE statement.

A PK using MySQL's auto_increment feature would solve this problem for you in a painless way.

Sorry that's not the answer you were probably looking for but it is the correct answer.

HTH,

=C=

[CalEvans]

SQLyog cannot find an instance of mysql server running at the host you hvae entered.

Contact your ISP and make sure you have the name correct. Make sure you have the port number correct.

=C=

[CalEvans]

Yuri,

Don't take any of this the wrong way. (Just by saying that I've ensured you will but I tried.)

It will come to no surprise to anyone who regularly reads this board that I disagree with you. (I can see Ritesh cringing already) SQLyog is an excellent tool to allow you to execute SQL in a graphical window against a backend server. I hope it never becomes More Access like or even more like M$ Query Designer. (Which started life as a tool that looked very much like SQLyog. It was called iSQL. But now it's a bloated pig of a program that has wizards for everything and can be used by any idiot to destroy a database.)

IMHO: (I represent no one but myself here)

1:Dropping and re-adding the column IS the way to reset the auto_increment field. This is the way MySQL is designed. Even if SQLyog could make it easier, I wouldn't want them to. It's not that it needs to be needlessly difficult, but given the serious nature of most production databases, all operations you can do from a command window need to require thought.

2: There absolutely should be no easier way of moving records from one table to another other than using DML. If you need to do it, there are SQL commands to do it. (and quite honestly, the are pretty easy).

3: Even though I think it's a BAD idea, there already is a way to empty a table. (SHIFT-+DEL) But truncating the table takes more thought and with a command this destructive, more thought==good.

humbly,

=C=

[CalEvans]

Quote:

I telnet the host and connect to my sql and do a grant all privileges on cibal to 'mike' identified by 'losangeles' I still get the same error.

Very close but you need to specify a host.

Either grant all for [email protected] or if you are on dialup and 180-126-117-22.uc.nombres.ttd.es is not your dedicated hostname, try grant on 'mike'@'%'

HTH,

=C=

[CalEvans]

Pretty much the error message says it all. Whatever server you entered, it can't find. This is the MySQL libraries returning this error to SQLyog which is simply reporting it to you. (in other words, this is a MySQL error, check the MySQL manual for details)

=C=

[CalEvans]

If I enter:

Code:

create table UPPERCASE_TEST (                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
              `testID` bigint(22) NOT NULL default '0') Type=InnoDB;

in the SQLyog window and then press F5, it creates a table named UPPERCASE_TEST for me. WHen I refresh the list of tables, it's there.

What version of SQLyog and MySQL?

=C=

[CalEvans]

Sunjomar,

I would suggest that if this is important to you, you design an interface to your database in a program that will allow you to do this. I do this in PHP however, I have used Visual FoxPro to interface with MySQL on rare occasions and I've been told that Access can do this. If your database is primarily accessed over the Internet then I would suggest building your user management interface in the same language/script that you developed your web pages in. It is, however, up to you, the developer, to enforce data validation. MySQL or any database engine will simply allow you to build the constraints and then fail gracefully if your users submit data that does not meet those constraints.

As Ritesh said, it is not the place of SQLyog to do this. Programs like Lotus Approach and M$ Access do this by mixing a database front-end with the back-end engine. While this makes for simple programming it makes it very difficult (as you are finding) to migrate these programs and your skills to a true database engine.

SQLyog is a development tool and not a end-user tool for the most part. It and programs like it are designed to allow developers to manipulate data and DDL easily without having to code their programs in a language. However, once you move outside of the realm of developers it quickly becomes useless or worse, dangerous.

HTH,

=C=

[CalEvans]

Quote:

I made a fairly simple database just to test out sqlyog, when exporting csv, the column headers are lined up properly, but all the values are stuffed in the first column.

What settings did you use?

What version fo SQLyog ?

Code:

I am running xp, sqlyog crashed twice in two hours usage time.

Gotta be external influences. SQLyog is one of only 3 programs I have open 24×7. (Homesite and DBDesigern4 in case you are interested.) I shut down my email client more than I shut down SQLyog. 🙂

What kind of erros are you getting when it crashes OR what are you doing when it crashes?

=C=

[CalEvans]

1: Is this on a server you control or one that you rent? If the latter, have any upgrades been made of late?

2: If it is under your control, can you use SQLyog to attach to the server and execute the query? Does it work correctly? Does it give an error?

I don't use phpMyAdmin but I do a lot of php work.

What is happening here is that phpMyAdmin assumes that it has a MySQL resultset and it is tying to free it. There are 3-4 things you could do:

1: Stop using myPHPAdmin and use SQLyog. (obligatory plug for our sponsor)

2: Find the line in the code and change it to

…@mysql_free_result… This will get rid of the error

3: Add error_reporting (E_NONE) to the top of /home/ktxd/public_html/myphp/libraries/build_dump.lib.php. This will hide this and all other errors.

4: Fix the problem and submit the patch to myPHPAdmin. Obviously, this needs a check before trying to free the resultset. You could probably throw an is_object() in there to make sure it's valid before trying to free it.

=C=

[CalEvans]

This is an issue with MySQL. myPHPAdmin works around it but traditionally, clients have not had to work around it as the servers have dealt properly with it.

I'm hoping that this will be resolved in the 4.x brance of MySQL but it's really not the client's (SQLyog) issue to resolve.

=C=

[CalEvans]

Not without writing a program that sits and watches both databases and compares them at regular intervals.

2-way replication is very tricky because you have to deal with issues like what if the same record is changed in both databases; who wins?

I'm not familiar with Filemaker but if it has it's own language and can read and write ODBC sources, your best bet may be to write a program in Filemaker to watch itself and update MySQL when necessary.

HTH,

=C=

[CalEvans]

Not sure what a 2013 is and I'm just too lazy to check the docs for you but a common problem is that MySQL's security is by user AND by host. Check your permissions for the user you are using and from the host you are connecting from to make sure you have the rights to connect form that host.

=C=

[CalEvans]

Sailor,

1: Are the flat files 1 per table?

2: Do they replace the entire contents of the table?

If the answer to those two questions are yes then I have a couple of ideas

First, If the files are coming into you as flat files then check out mysql docs to see how to load a flat file into a table. This is the easiest way to get data into the system from a flat file. If you can't get that to work then it gets a bit trickier.

10,500 records is not a lot of records. I currently update a database with about 1.5 million records from FoxPro DBFs every evening. I do it in a CRON job. DO you have access to a crontab on your account with aPlus?

It won't help you but I use a program called dbf2mysql to do the import. takes about 30 minutes to do the entire thing. The answer to both questions above in my case is yes, that's what makes it work so well.

If either of the questions above is no then you are going to have to write some code. (or hire a programmer) I'd use PHP since' it's easy to work with. Then you read in the fields one row at a time and use whatever logic you need to determine what table it goes in and/or does it replace an existing record or is it a new record.

Another advantage of PHP is that if you do not have access to a crontab then you can make the update a webpage. Then you can setup a task in your windows scheduler to call your webpage (www.mydomain.com/myNightlyUpdate.php) and it will do the job. Hey, it's not pretty but it would work.

In short, it is possible to do what you want done but how easy it is depends on a lot of information you didn't provide.

HTH,

=C=

[CalEvans]

Also, what does your mysql error log show?

=C=

[CalEvans]

Greetings peter and welcome to the wonderful world of SQL!

Quote:

surely beats working from the dull and uninspiring command line

BACK OFF! cli's rule. 😀 but if you must use a gui, SQLyog is head and shoulders above anything else on the market.

Quote:

I tried to update a single table database (which had no primary key) from one of my practical sessions an learnt that this was not permitted. Such databases are read-only and cannot be altered. Is there any way around this??

Your information is incorrect, you can have an updatable table without having a primary key. there is no concept that I know of in SQL for a read-only table. There are tables that you may not have INSERT or UPDATE permissions to but that's on a user-by-user basis. Check your settings or have your admin check them. (check the manual at MySQL for more info on user permissions)

Quote:

I tried the 'export resultset' (using the HTML option) to a file on the desktop but failed to see or find the file anywhere.

What version of SQLyog are you using? I just tried exporting a resultset using 3.11 and other than the fact that it's a bit confusing because the filename is blank AND non-editable (you have to push the button) it works just fine. (Admittedly, I only tried exporting as HTML)

Quote:

Finally when I did get to copy and paste, the query results (not using text view) to a textfile, I noticed that the columns in the first line did not line up with the rest of the data. Is this normal?

I'm really not sure what the problem is here. My guess is the answer to your question is Maybe. The Columns are delimited, not fixed width, unless you specified fixed width. If you specified Fixed Width then they should line up.

Anyhow, I hope something I've said may have helped. (except for the cli crack, that was just for fun.) Keep posting if you have other questions or I've not been clear on something.

=C=

[Author]

Posts