SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Good Will Punting: Aug 30, 2009; And as the curtain falls
Just know you did it all except lift weights

Thanks for the help, folks. Became exponentially clearer after your wording and I understand it considerably better now.

# ? Jun 21, 2012 18:36

Adbot: ADBOT LOVES YOU

# ? May 16, 2024 05:31

NovemberMike: Dec 28, 2008

Is there an easy query to select rows A through N from a table using MySQL?

# ? Jun 22, 2012 15:23

RoadCrewWorker: Nov 19, 2007; camels aren't so great

NovemberMike posted:

Is there an easy query to select rows A through N from a table using MySQL?

Yes.

# ? Jun 22, 2012 15:26

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

SQL code:

SELECT * FROM table WHERE row_id BETWEEN "A" AND "N"

? You've given us so little to go on, so, there's a query.

# ? Jun 22, 2012 15:28

NovemberMike: Dec 28, 2008

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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:

SELECT * 
FROM table 
WHERE row_id > N
LIMIT 20

# ? Jun 22, 2012 15:42

NovemberMike: Dec 28, 2008

Thanks, that does what I want it to do. Much appreciated.

# ? Jun 22, 2012 15:45

Dad Jokes: May 25, 2011

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

McGlockenshire: Dec 16, 2005; GOLLOCKS!

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

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

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.

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.

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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:

[foo@darkstar ~]$ sudo /etc/init.d/mysqld restart
Timeout error occurred trying to stop MySQL Daemon.
Stopping mysqld:                                           [FAILED]
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]
[foo@darkstar ~]$

Can someone please explain why this is happening, because Google is being no help. This is going to be somewhat untenable if I can't restart the server once it's in production. So far the only solution we've found is rebooting it; it has a PID but no sock, but I can't kill the process.

# ? Jun 25, 2012 14:35

McGlockenshire: Dec 16, 2005; GOLLOCKS!

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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

Zombywuf: Mar 29, 2008

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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

Fruit Smoothies: Mar 28, 2004; The bat with a ZING

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

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

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.

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?

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

Zombywuf: Mar 29, 2008

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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

Janitor Prime: Jan 22, 2004; PC LOAD LETTER

What da fuck does that mean; Fun Shoe

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

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.

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

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

As in paid support? Anyone know how much that costs, because their site is being coy.

# ? Jun 27, 2012 20:57

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Well, we're talking about Oracle here...

You might want to touch base with Percona instead.

# ? Jun 28, 2012 04:54

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

McGlockenshire posted:

Well, we're talking about Oracle here...

You might want to touch base with Percona instead.

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

McGlockenshire: Dec 16, 2005; GOLLOCKS!

Don't mean switching, they're a consultant shop with a good reputation.

# ? Jun 28, 2012 06:01

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

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

Muffinpox: Sep 7, 2004

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

Boogeyman: Sep 29, 2004; Boo, motherfucker.

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

baquerd: Jul 2, 2007; by FactsAreUseless

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

Boogeyman: Sep 29, 2004; Boo, motherfucker.

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

Canine Blues Arooo: Jan 7, 2008; when you think about it...i'm the first girl you ever spent the night with; Grimey Drawer

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:

        public double[] dbConnect(string sqlQuery, int totalColumns) // For an Array of Double values.
        {
            int i = 0;
            Double[] results;
            results = new double[totalColumns];

            var connection = new SQLiteConnection(@"Data Source=LolToolDB; Version=3");
            SQLiteCommand command = new SQLiteCommand(sqlQuery, connection);

            connection.Open();

            SQLiteDataReader reader = command.ExecuteReader();

            while(reader.Read())
            {
                Console.WriteLine(i + "-1: " + reader.GetDouble(i + 2));
                results[i] = reader.GetDouble(i + 2);
                i++;
            }
            reader.Close();
            connection.Close();

            return results;
        }

When I query the database, it returns an error saying that the tables I'm querying were not found, but they most certainly exist, which makes me think something else is wrong. The DB is in the right directory (and by default has no extension I guess?) and I've done a ton of Googling looking for a really simple example of a connection without finding a single one that either A) offers a really simple example and no explanation or B) offers a really complicated example that's four stories over my head with an explanation that is meaningless to me.

Canine Blues Arooo fucked around with this message at 08:02 on Jul 9, 2012

# ? Jul 9, 2012 05:41

Thel: Apr 28, 2010

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

Canine Blues Arooo: Jan 7, 2008; when you think about it...i'm the first girl you ever spent the night with; Grimey Drawer

Thel posted:

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

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

Sab669: Sep 24, 2009

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

Sub Par: Jul 18, 2001; Dinosaur Gum

Depends on your DBMS. I've used this on SQL Server:

code:

select 
	sys.all_objects.name TableName
	,cols.name ColumnName
	,DB_NAME() as DatabaseName 
from sys.all_objects 
inner join 
	(
		select * from sys.all_columns where name = '<COLUMN NAME HERE>'
	) Cols
on 
	cols.object_id = sys.all_objects.object_id
where sys.all_objects.type_desc like '%TABLE%'

# ? Jul 9, 2012 18:29

Sab669: Sep 24, 2009

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

Sub Par: Jul 18, 2001; Dinosaur Gum

Sab669 posted:

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.

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

McGlockenshire: Dec 16, 2005; GOLLOCKS!

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

wolrah: May 8, 2006; what?

Thel posted:

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

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

Adbot: ADBOT LOVES YOU

# ? May 16, 2024 05:31

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

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

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »