|
Thanks for the help, folks. Became exponentially clearer after your wording and I understand it considerably better now.
|
# ? Jun 21, 2012 18:36 |
|
|
# ? Jun 5, 2024 05:09 |
|
Is there an easy query to select rows A through N from a table using MySQL?
|
# ? Jun 22, 2012 15:23 |
|
NovemberMike posted:Is there an easy query to select rows A through N from a table using MySQL?
|
# ? Jun 22, 2012 15:26 |
|
SQL code:
|
# ? Jun 22, 2012 15:28 |
|
Golbez posted:? You've given us so little to go on, so, there's a query. Basically, I want to grab twenty actual rows so that they can be displayed. I would use the primary key for the table (and it really should work most of the time) but it looks like it's possible for rows to be delete, so I could end up with a query that has less than 20 elements returned. If I can just grab 20 real rows starting at row N then that should behave better.
|
# ? Jun 22, 2012 15:35 |
|
NovemberMike posted:Basically, I want to grab twenty actual rows so that they can be displayed. I would use the primary key for the table (and it really should work most of the time) but it looks like it's possible for rows to be delete, so I could end up with a query that has less than 20 elements returned. If I can just grab 20 real rows starting at row N then that should behave better. SQL code:
|
# ? Jun 22, 2012 15:42 |
|
Thanks, that does what I want it to do. Much appreciated.
|
# ? Jun 22, 2012 15:45 |
|
I've just started teaching myself how to use MySQL with PHP, but as a student, this is my first time doing anything with databases or with anything resembling SQL, and I'm a bit lost as to where to start. While I've been using W3schools' tutorial so far, I know avoiding SQL injection is a big thing, and I've seen mentions of using mysqli or PDO instead while I was looking up injection prevention, so now I'm really not sure where I should be starting in terms of self-teaching myself this stuff. Can anyone point me in the right direction on online tutorials for me to learn the basics? I've been starting off with this tutorial, and I was going to move onto this tutorial and this tutorial afterwards, but I'm open to suggestions if anyone knows of anywhere better to start. Dad Jokes fucked around with this message at 04:02 on Jun 25, 2012 |
# ? Jun 25, 2012 03:59 |
|
Stop using w3schools for anything. They are poo poo and should be ignored. As you have noticed, they kind of lack anything regarding modern practices, and completely ignore everything about SQL injection, XSS or even basic security practices. Other than the manual, I don't have anything immediately available for you tutorial wise.
|
# ? Jun 25, 2012 06:05 |
|
Dad Jokes posted:I've just started teaching myself how to use MySQL with PHP, but as a student, this is my first time doing anything with databases or with anything resembling SQL, and I'm a bit lost as to where to start. While I've been using W3schools' tutorial so far, I know avoiding SQL injection is a big thing, and I've seen mentions of using mysqli or PDO instead while I was looking up injection prevention, so now I'm really not sure where I should be starting in terms of self-teaching myself this stuff. Like McGlockenshire says, be really wary of W3Schools. They post a lot of inaccurate or just plain dumb stuff. Here is an article that came up when I searched "PDO for MySQL users": http://wiki.hashphp.org/PDO_Tutorial_for_MySQL_Developers
|
# ? Jun 25, 2012 08:47 |
|
Just upgraded to MySQL 5.5 on our slave server, and things were accidentally set for InnoDB to be the default table. Whoops; not remotely optimized for that, and we have a shitton of back replication to get through. I change it to MyISAM and restart the server. Er.code:
|
# ? Jun 25, 2012 14:35 |
|
There should be an error log in your data directory (/var/lib/mysql?) with information on what's gone wrong. It's probably named hostname.err
|
# ? Jun 25, 2012 18:56 |
|
McGlockenshire posted:There should be an error log in your data directory (/var/lib/mysql?) with information on what's gone wrong. It's probably named hostname.err Well, once I stopped restarting it while it was trying to create an InnoDB table, it's let me restart things just fine. The error log was of no help, it would simply say "mysqld is already running" or something similar.
|
# ? Jun 25, 2012 19:02 |
|
Golbez posted:Well, once I stopped restarting it while it was trying to create an InnoDB table, it's let me restart things just fine. The error log was of no help, it would simply say "mysqld is already running" or something similar. The problem was a timeout while it attempted a clean shutdown, presumably it was in the middle of a multi GB transaction at the time. The second problem was a bad init script that still tried to restart after a failed shutdown.
|
# ? Jun 25, 2012 19:59 |
|
Zombywuf posted:The problem was a timeout while it attempted a clean shutdown, presumably it was in the middle of a multi GB transaction at the time. The second problem was a bad init script that still tried to restart after a failed shutdown. If a multi-GB transaction won't allow it to shut down cleanly, how do I kill a multi-GB transaction that needs to be killed?
|
# ? Jun 25, 2012 20:01 |
|
I'm working independantly on a project at two locations. At home, I have a VM running my MySQL instance. At work, I use the webserver there which is inaccessible from home. I use GIT to sync changes made to the code, but I'm at an early stage in development, and the table structure changes quite a bit. Is there an easy way to track changes of databases? At the moment I'm using phpMyAdmin and just dropping / recreating databases when something changes because I'm lazy. I hate this solution, and I'm worried it'll bite me when this goes to production. Any advice?
|
# ? Jun 25, 2012 20:14 |
|
Fruit Smoothies posted:I'm working independantly on a project at two locations. At home, I have a VM running my MySQL instance. At work, I use the webserver there which is inaccessible from home. I use GIT to sync changes made to the code, but I'm at an early stage in development, and the table structure changes quite a bit. We we first started our project it was just like that. I finally got fed up of all the problems and changed everything to use liquibase. I can't stop recommending it to anyone who will listen, it's a god send. It even has a useful export function that exports your schema into it's weird XML format. That will save you a bunch of time from trying to recreate the database from scratch. As a plus it can help you migrate to another DBMS if you decide to in the future. Janitor Prime fucked around with this message at 21:23 on Jun 25, 2012 |
# ? Jun 25, 2012 21:12 |
|
Golbez posted:If a multi-GB transaction won't allow it to shut down cleanly, how do I kill a multi-GB transaction that needs to be killed? Generally the db needs to unwind what it's done so far, which will take as long as doing what it's done so far.
|
# ? Jun 26, 2012 21:23 |
|
Zombywuf posted:Generally the db needs to unwind what it's done so far, which will take as long as doing what it's done so far. True, that makes sense. And in reading that again, my concern is moot - I'll never (hopefully) be running a transaction that large, because those tables don't need transactions. They were however turned on by default which started the whole problem to begin with.
|
# ? Jun 26, 2012 21:45 |
|
OK, now I'm really confused. We have a process - admittedly inefficient, and I'm fixing it - that runs about a quarter million tiny update queries. These finished on the 5.0 master in half an hour; the newly-5.5 slave has been working on them for six hours. The key seems to be "query end" - each one spends over a tenth of a second in this state on the slave, which is really dragging things down as 10 queries a second means, well... six hours. The master spends less than .06 seconds in the entire query, and this one is spending .13 seconds (89% of the query) in "query end". Did 5.5 change something that my 5.0 configuration is interfering with? I'm at my wits end, as this is really starting to slow down some reports we have that are inefficient like this. I will change the reports, but I also want to find out what went wrong.
|
# ? Jun 27, 2012 19:58 |
|
Golbez posted:OK, now I'm really confused. We have a process - admittedly inefficient, and I'm fixing it - that runs about a quarter million tiny update queries. These finished on the 5.0 master in half an hour; the newly-5.5 slave has been working on them for six hours. The key seems to be "query end" - each one spends over a tenth of a second in this state on the slave, which is really dragging things down as 10 queries a second means, well... six hours. The master spends less than .06 seconds in the entire query, and this one is spending .13 seconds (89% of the query) in "query end". This is one of those things that I don't think you'll get much help from these forums. I'd ask the MySQL mailing list and then escalate to MySQL support if nothing comes up.
|
# ? Jun 27, 2012 20:08 |
|
As in paid support? Anyone know how much that costs, because their site is being coy.
|
# ? Jun 27, 2012 20:57 |
|
Well, we're talking about Oracle here... You might want to touch base with Percona instead.
|
# ? Jun 28, 2012 04:54 |
|
McGlockenshire posted:Well, we're talking about Oracle here... The best solution we have presented to us right now is to drive the other server to Wisconsin and have that function as our report server. Any viable solution has to take less time than that drive. Switching to Percona doesn't qualify. I think I may have gotten an ulcer stressing over this today.
|
# ? Jun 28, 2012 05:41 |
|
Don't mean switching, they're a consultant shop with a good reputation.
|
# ? Jun 28, 2012 06:01 |
|
Oh, right, I knew they had their own but I forgot about consulting. I fixed it: Disabling the binary log on the slave fixed it. This makes me worry about when we upgrade the master to 5.5, but for now things are working much much better. Does anyone know of any reports of binlog performance on MySQL 5.1-5.5?
|
# ? Jun 29, 2012 14:37 |
|
Good Will Punting posted:I'm having some trouble understanding a few of the more difficult relational algebra examples posted for a lecture in one of my classes. I semi-understand them (the logic and results, but not the order in which nested expressions are examined) but the full logic behind some isn't quite clicking. I'd post them here but I'm paranoid about sharing any proprietary teaching material, so if someone is really experienced in this area and wouldn't mind helping out briefly, it would be much appreciated. Thanks in advance. You can read relational algebra the same way you read SQL, set->selection->projection works pretty much like from->where->select. Basically just start from the set and selection and projection to figure out what each nested statement will give you then work backwards through the nesting.
|
# ? Jul 3, 2012 20:13 |
|
I'm pretty sure I already know the answer to this one, but I'd like to double check to be sure. Say I have a table in a MSSQL database that has these fields: Id ParentId Data1 Data2 Data3 Data4 Data5 Created LastModified Deactivated On this table, I have two indexes, one on ParentId and one on both ParentId and Deactivated. Since both of these indexes list ParentId first, the one that only indexes ParentId is redundant, right? Would there be any instance where MSSQL would choose to use the index that only contains ParentId, or can I scrap it?
|
# ? Jul 5, 2012 18:34 |
|
Boogeyman posted:On this table, I have two indexes, one on ParentId and one on both ParentId and Deactivated. Since both of these indexes list ParentId first, the one that only indexes ParentId is redundant, right? Would there be any instance where MSSQL would choose to use the index that only contains ParentId, or can I scrap it? Only thing that comes to mind is it the first one is clustered and the second is non-clustered.
|
# ? Jul 5, 2012 18:36 |
|
baquerd posted:Only thing that comes to mind is it the first one is clustered and the second is non-clustered. True, guess that would make a difference. Supposing they're both non-clustered, the one that only contains ParentId would be redundant, correct?
|
# ? Jul 5, 2012 18:49 |
|
E: Forget everything I had here previously. I've ported everything to SQLite and have new problems. My current connection code looks like this: code:
Canine Blues Arooo fucked around with this message at 08:02 on Jul 9, 2012 |
# ? Jul 9, 2012 05:41 |
|
What username are you using to connect to your SQLite database? Does it have permission to access those tables? (I may be blowing smoke here since I have not the foggiest idea how security in SQLite works.)
|
# ? Jul 9, 2012 09:38 |
|
Thel posted:What username are you using to connect to your SQLite database? Does it have permission to access those tables? None, and maybe this is where I blew it, but I think that you don't actually need to give it credentials since it's supposed to be a single-user solution.
|
# ? Jul 9, 2012 15:35 |
|
Is there a way to query a database for all of the tables that contain a column named x? I'm trying to clean up some dummy-data from some old tests on this application and it'd be nice to quickly just grab a list of all the tables rather than manually going through to make sure I get them all.
|
# ? Jul 9, 2012 17:09 |
|
Depends on your DBMS. I've used this on SQL Server:code:
|
# ? Jul 9, 2012 18:29 |
|
Have my babies? Thanks! e; Actually, question. I don't understand the where clause of the statement. I'm guessing its just looking at all the system objects and some must be marked as tables, so its just looking for that? I don't touch SQL too much and when I do its nothing sophisticated at all. Sab669 fucked around with this message at 18:44 on Jul 9, 2012 |
# ? Jul 9, 2012 18:42 |
|
Sab669 posted:Have my babies? Thanks! It's the type description for the objects, so yeah some of them are SYSTEM_TABLE, FOREIGN_KEY_CONSTRAINT, or STORED_PROCEDURE. You just want the tables. I think there's a more elegant way to get at this but I hacked that together one time long ago and it's served me well so I use it. There are also undocumented SQL Server stored procedures that help you iterate through all tables in a database or all databases on a server, you may want to look into those.
|
# ? Jul 9, 2012 19:13 |
|
Sab669 posted:Is there a way to query a database for all of the tables that contain a column named x? FWIW, the cross-database way to do this is to use the information schema.
|
# ? Jul 9, 2012 19:18 |
|
Thel posted:What username are you using to connect to your SQLite database? Does it have permission to access those tables? SQLite databases are just files and there's no security to speak of. Your permissions to the file are it. Canine Blues, if you're using dotConnect as I think you are, the problem may be that you have "Data Source=" in your SQLiteConnection line rather than "DataSource=" wolrah fucked around with this message at 23:54 on Jul 9, 2012 |
# ? Jul 9, 2012 23:51 |
|
|
# ? Jun 5, 2024 05:09 |
|
I'm not really a Lunix guy but someone asked me to look over their host's install for security reasons. The one thing that sticks out to me is that they're running MySQL 5.0.95-community on Red Hat 2.6.18-194.11.3.el5. The thing is from what I can tell the latest version of MySQL is 5.5.25a, and that anything <5.1 is actually pretty shoddy from a security standpoint. Is this an opinion you guys share, or am I just being too picky?
|
# ? Jul 10, 2012 20:44 |