How to determine Integer or String based SQL Injection? Page 23 of this document said that Injection Type determines if you need a ' or not
https://defcon.org/images/defcon-17/dc-17-presentations/defcon-17-joseph_mccray-adv_sql_injection.pdf
Integer Injection:
http://[site]/page.asp?id=1 having 1=1--

Column '[COLUMN NAME]' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
String Injection:
http://[site]/page.asp?id=x' having 1=1--

Column '[COLUMN NAME]' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Determining this is what determines if you need a ' or not.
Let's test this theory on this site.
http://testphp.vulnweb.com/listproducts.php?cat=1

Please take note that this is not a real shop. This is an example PHP application, which is intentionally vulnerable to web attacks. It is intended to help you test Acunetix. It also helps you understand how developer errors and bad configuration may let someone break into your website. You can use it to test other tools and your manual hacking skills as well.
Test 1
http://testphp.vulnweb.com/listproducts.php?cat=1 having 1=1--

No Error, does that mean this is Integer Based SQL Injection?
If I use ' in the parameter, I'll be getting the following error. Does that this is not String Based SQL Injection?
Test 2
http://testphp.vulnweb.com/listproducts.php?cat=1' having 1=1--

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' having 1=1--' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74
Test 3
http://testphp.vulnweb.com/listproducts.php?cat=1' having 1=1--+

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' having 1=1--' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74
Test 4
http://testphp.vulnweb.com/listproducts.php?cat=1' having 1=1-- -

Error
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' having 1=1-- -' at line 1 Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /hj/var/www/listproducts.php on line 74

Simple. Look at the value in parameter cat=1.
If the value is a number, then it's an integer.
If the value is a letter, then it's a string.
In this case, 1 is a number, and yes ... it's an integer.
To answer your question, yes this is an Integer Based SQL Injection and ' not required.


Designing an activity feed I'm trying to design an activity feed, but I have a small problem.

Here's my current table.

activity

id
user_id
status
activitytype_id (status, video, photo, etc)
created_at
location
video

I didn't add some unrelated columns to make it short.

This works when I want to create a feed for the friends' posts of a user. I basically create an array with PHP and check for user_id's in the activity table and sort them by date.

But a user also follows groups and events, this is the tricky part. Do I need to create a second query for groups and events and merge with the user posts together?

What's the best way to include them (groups and events) in the feed? I can add a column to the activity called event_post and group_post and add posts id's as foreign keys to the activity table (null by default), but I'd like to hear your opinions.

communities

id
communitytype_id (group or event)
name
created_at
user_id (FK from users table, owner of the group or event)

Database design is tricky. First some suggestions for you:

Make sure you understand properly how tables works and what constraints and keys are. Do you know what a unique constraint and a non-null constraint is, how primary keys and foreign keys work?
Don't try to skimp on the number of tables. Beginners often have far too few tables in their databases. This actually makes the database more complicated and less efficient, not the reverse. You need to have tables for any 'entity' which exists in your system, even if it's not a concrete 'thing', and even if it's not visible to the user. You are also probably going to want some tables which just express relationships between other entities. Trying to do without these will tie you in knots.
Tables in relational databases are very good at storing exactly one different kind of 'thing'. They are very bad at storing more than one completely different kind of thing. It's hard to build tables which work like objects in OO languages, with inheritance and polymorphism.
Do you definitely need to use a relational database? You might find that a noSQL solution such as MongoDB is what you need. Typically Mongo (there are lots of similar ones) is used to store collections of 'documents'. It is much harder to store relationships between several collections. But it's much easier to store lots of 'things' together, each of which has slightly different types of information. For example, a document could contain either fields for user information, or for group information, or for event information.

Getting back to your problem, one of the following might work for you.

If an activity belongs to a single user, AND a single group, AND a single event, you probably want to have a columns in the activity table for each different type of foreign key. You then probably should do one search for activity by users that are followed, one for groups, and one for events.
If an activity can belong to multiple users, groups and events, you should have 'relation tables'. Each of these will be called something like activity_user, and will have a column which is a foreign key to user, and a column which is a foreign key to activity. You link one to the other by creating a new row in this table. One user can have multiple activities, and one activity can have multiple users, and so on. You query the list of activity which belongs to a user by JOINing user, activity and activity_user all together.
If the activity belongs to all groups that its user belongs to, you will want a similar association table between groups and users. You should probably look up all the users a group contains, and then look for all their activity.
If each activity either belongs to a single user, OR a single group, OR a single event, you should probably divide your activity table into three. Call them useractivity, groupactivity and eventactivity. You do a different query for each of them and then combine the results. These are not 'relation tables' but tables which contain the actual data in each case. This means that it possible to enforce each activity only being associated to one thing. There are other ways of doing this, such as having three foreign key columns, and adding constraints that exactly one of them should be non-null, but they are much uglier and more breakable. One advantage of having these extra tables is that you can them easily add some information to one type of activity which doesn't have to be in the others.


Mysql - unknown variable 'table_cache=64' I use Mamp Pro 3. I upgraded Mamp's MySql to 5.6.24 using this script https://gist.github.com/tobi-pb/b9426db51f262d88515c.

After that I ran the Mamp Pro and MySql cannot be started. So I looked the mysql_error.log and found the following error:

2015-06-15 01:24:55 13139 [ERROR] /Applications/MAMP/Library/bin/mysqld: unknown variable 'table_cache=64'
2015-06-15 01:24:55 13139 [ERROR] Aborting

After I google about this error, I get to know that I have to delete 'table_cache=64' variable from my.cnf (MySql Configuration) file. So I deleted this variable from /Applications/MAMP/tmp/mysql/my.cnf file.

But the problem is that the same error still occurs when I start the MySql again. So I looked at the my.cnf file, 'table_cache=64' re-appears automatically again. I deleted it again but it automatically appeared again when I start the MySql.

How should I solve this 'table_cache=64' problem?

Version 5.1.3 "Renamed the table_cache system variable to table_open_cache. Any scripts that refer to table_cache should be updated to use the new name."  However, it did not become an error until years later, in 5.7.6.  Change to table_open_cache.

However, what does the line in my.cnf look like?  Sounds like there is something else wrong.  Change it to

table_open_cache = 200

(If you have a tiny machine, 64 might be better.  But in today's machines, a few hundred is a better value.)


Any Open Source / free ETL out there? I was using Pentaho Data Integration even before Pentaho bought it and call it that.
I have the last free version.

I went on their website recently to see if they had released another version only to find out my favorite open source etl is not much open anymore and not quite free.

Does any of you know of alternatives in affordable, easy to use ETL tools?

Isn't Kettle (Pentaho Data Integration Community Edition) still available without licensing fees?

Kettle 4.1 was released to the community as a stable release in fourth quarter of 2010. The community is currently working on a minor patch release to 4.1. Kettle continues to be a community supported product. Kettle may be a more appropriate tool in your sandbox or development environments.

However, in a production environment you may find that PDI Enterprise Edition with technical support from Pentaho more appropriate for your mission critical systems.

After discussion in comments below:
 Pentaho Data Integration (PDI - ak "Kettle") is still maintained (4.4 was release 2012-09) and Open Source. It can be downloaded from here: http://sourceforge.net/projects/pentaho/files/Data%20Integration


Barman not able to connect: fe_sendauth: no password supplied I'm trying to setup streaming replication to a Barman server.

Barman config is pretty much basic, I'm using backup_method = postgres.

[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
log_level = DEBUG
archiver = true
backup_method = postgres
backup_options = concurrent_backup
compression = gzip
minimum_redundancy = 1
retention_policy = RECOVERY WINDOW OF 7 DAYS
retention_policy_mode = auto
streaming_archiver = true
wal_retention_policy = main
configuration_files_directory = /etc/barman.conf.d

I'll call the DB server pg

[pg]
description = "pg"
ssh_command = ssh postgres@pg.example.com
conninfo = user=barman dbname=postgres host=pg.example.com port=5432

I've configure hba rules and setup a .pgpass file in barman's home /var/lib/barman/.pgpass.

A test connection to pg server works just fine:

barman@b01:~$ psql -c 'SELECT version()' -U barman -h pg.example.com postgres
                                                             version
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Debian 10.7-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)

However barman check pg fails miserably and I have no idea why:

$ barman check pg
Server pg:
        WAL archive: FAILED (please make sure WAL shipping is setup)
        PostgreSQL: OK
        is_superuser: OK
        PostgreSQL streaming: FAILED (fe_sendauth: no password supplied)
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
        pg_basebackup: OK
        pg_basebackup compatible: FAILED (PostgreSQL version: None, pg_basebackup version: 11.2-1.pgdg90+1))
        pg_basebackup supports tablespaces mapping: OK
        archive_mode: OK
        archive_command: FAILED (please set it accordingly to documentation)
        pg_receivexlog: OK
        pg_receivexlog compatible: FAILED (PostgreSQL version: None, pg_receivexlog version: 11.2-1.pgdg90+1))
        receive-wal running: FAILED (See the Barman log file for more details)
        archiver errors: OK

Is there a better way how to debug authentication issues? Is the .pgpass even loaded under barman check?

pg.example.com:5432:postgres:barman:s3cr3tP2ssw0rd

After checking barman's source code, I've realized that barman is actually overwriting the conninfo string:

    # Override 'dbname' parameter. This operation is required to mimic
    # the behaviour of pg_receivexlog and pg_basebackup
    self.conn_parameters['dbname'] = 'replication'

Which isn't very intuitive, thus the dbname in .pgpass should include replication db:

pg.example.com:5432:replication:barman:s3cr3tP2ssw0rd


What will GRANT  ON ALL TABLES IN SCHEMA  TO  do? It seems obvious that PostgreSQL will GRANT some privileges ON ALL TABLES IN some schema TO some role when I'll command.

But, will it make it permanent for the schema that on all tables within it, they will be granted the indicated privileges to the indicated roles, so new tables will be affected in the future?.

Or it works as a batch command so after a new table is created in the schema I have to GRANT the indicated privileges to the indicated roles again?.

so new tables will be affected in the future?

No, to affect new tables set DEFAULT PRIVILEGES:
PostgreSQL CREATE TABLE creates with incorrect owner

Note that default privileges are set per role.


Use --databases on mysqldump or not? I have seen this two ways:

mysqldump -u username -ppassword --databases mydb >with.sql
mysqldump -u username -ppassword mydb >without.sql

I did a linux 'diff' on the two and the with.sql file has this:

< CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
<
< USE `mydb`;

While the without.sql doesn't. I have used --databases on mysqldump but I have seen tutorials showing it without when doing a mysqldump on a single database.

Why are they different? Which one is most common to use? I'm preparing a mysqldump of a database for a client which might run it in Linux or Windows, so is there a concern of being more portable?

mysqldump -u username -ppassword --databases mydb >with.sql

This gives you dump with top most statement

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb`
    /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

In this case client doesn't need to create a database prior to restoration and database will create with default properties .

So in this case you can't change the name of database at another end. Moreover, while using replication some filtering rules will not work.

mysqldump -u username -ppassword mydb >without.sql

In this case table will create in any database, so prior to restoration you need to select the database in which you want this dump to be restored.

This will facilitate you to restore the dump with database name of your choice.

If you will not execute

        use databasename;

prior to restoration, it will throw the error and dump not executed.

So I must say, difference lies on your requirement.


Progress OpenEdge - how to remove lock from table? I have some kind of bug leaving lock on table in application that is connecting to Progress OpenEdge Database. Because of the lock I'm not able to test what app is doing wrong there (since I can't test it more than once because of lock and lock seems to last for very long).

Is there a way to remove all locks from table?

You can use locking hints to select from tables that have locks on them.

select * from tblUser with (NOLOCK) ;

This is ok practice with adhoc sql.

Note Don't use in productioncode without reading and understanding documentation fully.

If you want to kill blocking processes:  http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx


Unusual issue with localhost:3000 after watching ruby on rails 4 tutorial: Lynda.com I'm totally new to programming, and I've started my training by watching the ruby on rails 4 essential training tutorial at lynda.com. I got to the section where I was supposed to create a web server by first opening the code in my database.yml file, then entering my password for mysql, then starting a server by typing "rails server" in the command line. It didn't work at localhost:3000. I tried granting privileges to the databases in mysql to the account, and I even tried changing the password, but it still doesn't work. When I go to localhost:3000, it still says : Access denied for user 'root'@'localhost' (using password: NO).

Also, I have a host name written in the code, but no socket. I don't know if this changes anything, I may just have a newer version of mysql than the guy in the video.

Here is the code:

# MySQL.  Versions 4.1 and 5.0 are recommended.
#
# Install the MYSQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   http://dev.mysql.com/doc/refman/5.0/en/old-client.html
development:
  adapter: mysql2
  encoding: utf8
  database: #my_cms_development
  pool: 5
  username: root
  password: snapple187
  host: localhost
# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: mysql2
  encoding: utf8
  database: my_cms_test
  pool: 5
  username: root
  password: snapple187
  host: localhost

production:
  adapter: mysql2
  encoding: utf8
  database: #my_cms_production
  pool: 5
  username: root
  password: snapple187
  host: localhost

Thank you for taking the time to help solve this issue.

This is how I have always set up my rails projects, using SQLite in your test and development will save you this heartache!

#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: mysql2
  encoding: utf8
  database: #your db name
  username: #your db username
  password: #your db password
  host: #your db hostname
  port: 3306


SQL Server 2008 R2 Service Broker Troubleshooting I set up Service Broker between two databases on separate instances, one on a 2008 R2 Cluster the other on a separate box also 2008 R2.  I have gone through the process of setting up all the objects (users, message types, contracts, queues, services, endpoints, routes, remote service bindings, and certificates), but I am getting 5 errors when I run ssbdiagnose.

ssbdiagnose -E -d MyDatabase CONFIGURATION FROM SERVICE /test/initiator TO SERVICE /test/target

http://jeffreylangdon.com/ssb.png  -- tried to post the error image, but apparently not an option since I am new to this forum.

Starting from the top error I have no idea why the Service Initiator can't be found considering I am running ssbdiagnose on the box where it resides.

Any direction would be appreciated.

Thanks,

Jeff Langdon

@jlangdon

The way you run ssbdiagnose in the post it would connect to the database MyDatabase on localhost and search for the initiator and target service. You mention that you've set up for two different instances, so the correct command line should be something like:

ssbdiagnose CONFIGURATION\
  FROM SERVICE /test/initiator -E -S <instance1> -d <db1>\
  TO SERVICE /test/target -E -S <instance2> -d <db2>

(I used the unix style command line terminators to prettify the code. You should use a single line).

When the connection params are pulled out ahead of the CONFIGURATION keyword they apply to every connection (FROM, TO).


"SQL command not properly ended" with trigger Problem
No matter what I do, I can not get my trigger to work. It seems to always complain about SQL command not properly ended, ignoring SQL statement and so on.
Research
I have spent hours looking into why this issue is occurring and I still have come up empty handed. This is my first time creating a TRIGGER so I have a feeling it has something to do with the placing of things.
What I have tried
I have tried many many things. I have tried adding a comma to the last variable in the brackets, I have tried adding ; to random statements and so on. This is my trigger:
CREATE OR REPLACE TRIGGER create_base_invoice
AFTER INSERT ON reservation
BEGIN
    INSERT INTO
        invoice
    VALUES
        (i.resnum, i.bnum, i.rnum, c.cfname, c.clname, i.bstartdate, null, null)
    SELECT
        i.resnum, i.bnum, i.rnum, c.cfname, c.clname, i.bstartdate
    FROM
        INSERTED i, CUSTOMER c
    WHERE(
       (i.cfname = c.cfname)
       AND
       (i.clname = c.clname)
        );
END create_base_invoice;

I do not understand the point of the SELECT command in my case and I have tried removing it and I do not get a different error so I am not sure if I need that there or not.
The reason I added it was due to stumbling across a post with a very similar scenario of mine and seeing the answer contain that SELECT statement which at the time I was missing.
The reason I need the where claus is because the Reservation table does not actually hold the customer name and surname (which we need in this case). It just holds the customer number. Here is the reservation table:
RESNUM
CNUM
BNUM
RNUM
BSTARTDATE
BDURATION
BOCCUPANTS
BSTATUS
BPAYMENT

I removed the VALUES clause, and now the error is

3/78 PL/SQL: ORA-01747: invalid user.table.column, table.column, or column specification

I removed ,null ,null too. It then complains

missing VALUES keyword

So, what I am trying to do is create a invoice based off of a reservation. When someone books a reservation, the trigger then  saves certain variables from the reservation (resnum, bnum, rnum, bstartdate, cnum) and then it finds the customers full name based off their customer number (cnum).
All of this is saved into invoice to make a base invoice. Then I have another procedure that adds the checkout date and calculates total price of stay. We do not save cnum into invoice.
Just to clarify, cnum is just used to essentially search for the customer and find their first and last name which is then saved onto the invoice.

(Edited following comments and after checking what INSERTED is in SQL Server)
The INSERTED table in SQL Server represents the rows that were inserted by the triggering statement. Oracle doesn't have this. Instead, a row-level trigger can refer to the column values of each row affected by the trigger, using :new and :old values. (Only :new values are populated for inserts, and only :old for deletes.) Therefore I think what you need is something like this:
create or replace trigger create_base_invoice
after insert on reservation
for each row
begin
    insert into invoice
         ( resnum
         , bnum
         , rnum
         , cfname
         , clname
         , bstartdate)
    select :new.resnum
         , :new.bnum
         , :new.rnum
         , c.cfname
         , c.clname
         , :new.bstartdate
    from   customer c
    where  c.cnum = :new.cnum;
end create_base_invoice;

This creates an invoice for each new reservation, including details looked up from the customer table.

I do not understand the point of the SELECT command

There are two flavours of insert:
insert into tablename (col1, col2, col3) values (val1, val2, col3)

and
insert into tablename (col1, col2, col3)
select t2.col1, t2.col2, t2.col3
from   othertable t2
where  ...

One is for inserting fixed values, the other is for loading the results from a query.


How do you copy a TDE-encrypted SQL Server database using T-SQL programatically? I need to copy a TDE-encrypted SQL Server database to a separate, non-encrypted, SQL Server instance using T-SQL, but only by reading the schema of the source database.  This is because I'm going to use this T-SQL in an Execute SQL Task on SSIS, and it needs to be able to account for changes to the production database without further maintenance to the SSIS package.

The database is fairly large and has all types of constraints (foreign, primary, default), indexes, views, user-defined functions, etc, so I will need to make sure I grab everything.

Unfortunately because it is TDE-encrypted, I cannot use backup/restore, attach/detach, or copy-move because it will throw a "security certificate not found" exception.

Is this possible using just T-SQL?  Thank you in advance.

Using just T-SQL?  Probably but not easily.  A much better solution would be to simply put the certificate for the database on the destination server so that you can restore the database as needed to the destination server.

Given that your database has TDE enabled, I assume that there's PII in there that you have to keep encrypted.  By moving it to another server without encryption you've probably just broken your data encryption policy.


You could use EXISTS instead of IN. That's likely to perform better.

SELECT *
       FROM "wallet"
            LEFT JOIN "amount"
                      ON "wallet"."id" = "amount"."walletId"
                         AND EXISTS (SELECT *
                                            FROM "order"
                                            WHERE "order"."id" = "amount"."orderId"
                                                  AND "order"."status" IN ('new',
                                                                           'accepted'))
       WHERE "wallet"."locked" = false;

To further improve the performance of the above maybe try indexes on "wallet" ("locked", "id"), "amount" ("walletid", "orderid") and order ("id", "status"). Unless you have them already, of course.

Check the plan if you're unsure which version performs better and what indexes help.


Can the OUTPUT clause create a table? I'm doing an update like this:

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID

And I want to use the OUTPUT clause to back up my changes.

UPDATE dbo.Table1
SET BirthDate = b.BirthDate
OUTPUT
    inserted.Id, inserted.BirthDate AS New_BirthDate,
    deleted.BirthDate AS Old_BirthDate
    INTO OutputTable
FROM Table1 a
JOIN Table2 b
    ON a.ID = b.ID

What I want to know is if there is a way for the OUTPUT clause to create the table OutputTable or do I have to make sure it already exists before running the statement?

AFAIK, the target table must exist, though the documentation is not explicit and probably should be. I can't see anything in the syntax diagram that would support any type of extension to create the output table at the same time.


SSRS - Data missing when running report from server I have an extremely strange issue with an SSRS report i'm working on. We use SQL Server 2012 availability groups and as number of groups and the nodes they run on has swelled, confusion on the part of users accessing them has increased, so I've created a report that, for a given DB, returns the correct primary and lists other related DBs as well.

The issue relates to displaying the AG. certain records were returning a null AG name, and having done some investigation, I've narrowed it down to the results from the following

Select * from sys.availability_groups AG

I have created a simple table in SSRS to take these results and when running the query in SSMS and BIDS, I get 13 records. When I deploy it and run the report, I get somewhere between 10 and 12 records, depending on the node I run it against. I am frankly at a loss how to proceed from here. I feel I've reduced the complexity of the query down to it's most simple form. I've checked there is no filtering taking place. Even performing a count(*) instead yields a result of 10 rather than 13. There is also no commonality to what is being omitted; naming conventions are standard across all AGs, they're not all on the same primary, but other AGs on the same primary do show up, and as I said, running against different primaries varies the number of records returned

Any suggestions?

So for anyone who is interested, I figured out this was a permissions issue. As this is a report, it runs under a limited permissions login. I'd granted that login read permissions to Master and View Server State, which I thought would be enough, and sure enough, the report loads, but it seems like for some reason, if you don't have View Any Definition, your results may not be complete. I suspect (though I don't really have time to prove it) that each visible AG had at least one DB with some sort of relevant permissions, bringing it into view, but anyway, View Any Definition fixed it :) thanks for all the suggestions though


MS SQL Login without password I need to login to MS Sql from ODBC to my MS Sql instance on Linux server. Following are my question

Is there a way to do it without Active Directory (maybe with LDAP or ADLDS if it does not change user profile)?
I created a SQL Server Login without password by deselecting enforce password policy. I can finish adding ODBC and Test Connection is successful, but when I run the program it gives an error the the login has failed and in the SQL Server Log it shows:

  Login failed for user 'gp'. Reason: Password did not match that for the login provided".

The program I am running is a old program and I don't have the source code to change anything in it. Currently it works with Windows Authentication on a server running Windows Server and Microsoft SQL Server.

You might want to have a look at How to transfer logins and passwords between instances of SQL Server to migrate the gp user from the old SQL Server instance to the new SQL Server instance.

Analysis

The application will be logging in with the gp account and a pre-defined password stored in the application.

Because you have not provided a password for the new user you created on the new instance the login is failing.

Solution

You will have to create the SQL Server login with the above mentioned method provided by Microsoft. Essentially the scripts create two procedures on the source instance in the master database. When you execute EXEC sp_help_revlogin it creates an output containing a CREATE LOGIN ... statement for each SQL Server Login which contains the SID and encrypted password of the original logins.

  The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.

You then create the required SQL Server Login on the target SQL Server Instance by running the corresponding CREATE LOGIN.... statement for the gp login on the target instance.


Does MySQL support replicating all databases? I’ve been doing some investigation into properly configuring MySQL replication.

From what I understand after reading the documentation, you can only replicate specific databases, and each database name must be specified: In the master configuration, in order for the relevant database writes to be written to the binary logs, and in the slave configuration, in order to replay those logs onto the slave.

Is this correct, or is it possible to configure MySQL to replicate any and all databases?

To answer your question about how MySQL replicates all databases, here is how it works...

Master has Binary Logging enabled
Master records completed SQL Statements
Master will inject 'USE dbname;' among the SQL Statements it records
Slave connects to Master (IO Thread)
Slave IO Thread requests next SQL statement from the Binary Logs
Slave IO Thread copies it to its Relay Logs
Slave SQL Thread Processes its Relay Log Entries FIFO (Queue)

Once USE dbname; is issued out of the relay logs,  multiple SQL statements will be processes chronologically until another USE dbname; (a different database) is issued from the Relay Logs.

To see this, pick any Binary Log (except the Current One) on the Master, and run this:

mysqlbinlog binarylogname > SQLStatements.sql

To see all the binary logs and the current binary log on the Master, run these:

SHOW BINARY LOGS;
SHOW MASTER STATUS;

Long story short: By default, all DBs are replicated due to the USE dbname; command within the binary logs on the master and the relay logs on the slave.

Abdul's answer shows how to filter in or filter out databases from being processed by the slave.


Can we pass a varchar/text datatype in to a variable in pgbench? --pgbench sql script

[pg93@db1 load_test]$ cat get_email.sql
\setrandom cnt 1 100000
\set v_username 'user_' || :cnt

begin;
select get_user_email(:v_username);
end;

get_user_email() is a function which can get user's email.

--pgbench

[pg93@db1 load_test]$ nohup pgbench -c 4 -T 10 -d proxy -U proxy  -N -n -f get_email.sql  >  get_email.out  2>&1 &

--the get_email.out file says

nohup: ignoring input
client 0 executing \setrandom cnt 1 100000
client 0 executing \set v_username 'user_' || :cnt
invalid input syntax for integer: "'user_'"
invalid input syntax for integer: "'user_'"
set: unsupported operator ||

It's seem that can only use integer datatype.

--data

db2=> select * from users limit 3;
 username |      email
----------+-----------------
 user_6   | user_6@163.com
 user_19  | user_19@163.com
 user_20  | user_20@163.com

get_user_email() is a function which can get user's email.

According to the documentation, pgbench variables are supposed to be boolean, integer, float, or variable-references.

However it appears to be possible to pass arbitrary text to --define so long as you don't try to use pgbench operators that will treat it as a numeric value.

pgbench doesn't support psql-style :'quoted_variable' syntax for literals or :"quoted_variable" for identifiers. So you have to quote the value you pass.

e.g. if you invoke pgbench with --define "v_username='fred'" then the variable's string value will be 'fred', including the quotes. You can then write:

\setrandom cnt 1 100000

begin;
select get_user_email('user_' || :v_username);
end;

Note that the concatenation is done in SQL, as pgbench expression language doesn't have string operators.


Trouble recovering data from corrupted PostgreSQL tables I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble.

When I do select count(*) One table returns:

  ERROR:  could not access status of transaction 2619431010
  DETAIL: Could not open file "pg_clog/09C2": No such file or directory.

The other one returns:

  ERROR:  invalid page header in block 94206 of relation
  base/11974232/11975439

Is there a way I can work around and get my data back?

The very first thing you should do when you encounter database corruption is to capture a filesystem copy of the data directory tree while PostgreSQL is not running and save it somewhere safe.

Once you have done that, if you do not have a backup which you can restore you might want to try pg_resetxlog.  Note that the documentation has this recommendation:

  You should immediately dump your data, run initdb, and reload. After
  reload, check for inconsistencies and repair as needed.

Read over the documentation page for pg_resetxlog until you understand the options and which might be best for your situation.

Finally, make sure you understand how the database was corrupted.

Usually this is due to bad hardware or drivers.
Sometimes it is due to usage of non-default PostgreSQL settings which allow DBAs to reduce reliability to improve performance.  Some shops with redundant servers find this a worthwhile trade-off, because losing one server in a large redundant server farm is no big deal.  You may hear this sort of configuration referred to as "DBAs running with scissors" and consists primarily of turning off fsync, full_page_writes, and synchronous_commit -- all of which are needed to ensure persistence of committed transactions.  The first two are required to prevent database corruption on a crash of the OS or hardware.  You should not turn these off unless it is OK to lose the entire PostgreSQL instance.
Rarely, people delete some integral part of their database, such as the pg_xlog or pg_clog subdirectory.
Even more rarely, there is a PostgreSQL bug which caused the corruption.  Because any report of such a bug is treated very seriously, they tend to get fixed very quickly and are generally encountered these days by people running outdated versions which are out of support.  Without knowing the exact version (the output from SELECT version();), it's hard to know whether you are vulnerable to a known, fixed bug.  Possibly relevant to this particular error message is whether you used pg_upgrade and exactly which version of that you used.


Using column store indexes on table What is the purpose of column store index on database tables, and in what situations should they be used (with OLTP)?

SQL Server columnstore indexes are created for datawarehouse purposes.

When should I use this?

I would use a CLUSTERED columnstore index standard for most fact tables, because you will get a better read performance & it is good for writing data to it! note: there is a big difference in the way you insert data. you should read up on that on this post.
http://www.nikoport.com/2013/07/06/clustered-columnstore-indexes-part-6-observing-the-behavior/

However, since the SQL Server 2016 you can use columnstore indexes to combine a OLAP workload on your OLTP system.

This is done by using the filtered NON-CLUSTERED columnstore index. The way to do this is by filtering your data between hot and warm data. The filtered columnstore index will then keep a copy of the filtered warm data part.

What is Hot and Warm data?

F.E. You have a customer who buys one banana, then he wants to change his order and makes it one banana and 2 apples, then he cancels one apple. This is typical hot part of your data. Which has a lot of update and inserts and deletes. After he pays for this order the order becomes a warm portion of the data which means only that the data will not change frequently.

The trick is putting a non-clustered index with a filter to only have the warm data inside your non-clustered columnstore index. In this case it could be a flag payed or something similar. This is something that is different in every company.

Why should you use this?

You will get an incredible read speed activity due to the batch mode processing.This will greatly increase your large read queries on the system.

NON-Clustered columnstore trick

There is also a trick using an empty filtered non-clustered columnstore index.
F.e.

CREATE NONCLUSTERED COLUMNSTORE INDEX TESTCCI on dbo.TEST
(
ID
) where ID = -1 and ID = -2

This will create an empty nonclustered columnstore index (SQL Server will also not read any data to create the index). After you do this your read queries will be able to benefit from batch mode processing and thus greatly improving your big read queries on your oltp system.


Quorum Configuration I have watched many sample videos in youtube.com and discovered that in this videos in windows failover cluster dashboard configuration looks like this :

Quorum Configuration: Node and Disk Majority (Cluster Disk 1)

But on my dashboard it looks like:

Witness: Disk (Cluster Disk 1)

What is the cause of this difference. I am using Windows Server 2012 R2 Standard Edition

They changed the layout of the cluster manager screens (and some of the terminology) from Windows Server 2008 R2 -> 2012. It's not a meaningful difference; watch newer videos.


check constraint does not work? I have the following table.

create table test (
   id smallint unsigned AUTO_INCREMENT,
   age tinyint not null,
   primary key(id),
   check (age<20)
);

The problem is that the CHECK constraint does not work on the age column. For example, when I insert 222 for the age field MySQL accepts it.

CHECK constraints are not implemented in MySQL. From CREATE TABLE

  The CHECK clause is parsed but ignored by all storage engines. See Section 12.1.17, “CREATE TABLE Syntax”. The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, “MySQL Differences from Standard SQL”.

It's also been a reported bug for almost 8 years...


SQL Server - monitor changes on linked server We have a local SQL Server database which we have full control over, and in this local server we are linking a remote SQL Server database, to which we have only read access (no schema changes, no creation of triggers, stored procs, views, etc.).  I'm not sure if we are able to enable distributed transactions on the remote server, but I can check if that is important to know.

A 3rd party applies inserts/updates/deletes to the remote server tables, and we want to know when something changes in the remote server (ideally in real-time, but polling might be sufficient), so we can re-process some data in the local server.

What would be our main options for achieving this?  We would prefer lighter-weight solutions if possible (i.e. not SSIS - because none of us have really used SSIS).

Are you looking to synchronize the data locally?  Ideally, you would accomplish this through some form of replication from the source.

If you have a local copy of all tables, you can create a script or stored procedure to check for records deleted, inserted, or updated by comparing to your copy of the data.  This would have to be done on a table by table basis.  If there are hundreds of tables, then this approach is probably not feasible.

It helps if there is a time stamp of some sort on the table row.  That will assist in determining when an update occurred.  Inserts and deletes are easier as you just find records that exist at the source but not in the destination and vice versa.

Schedule your script to run at defined intervals via SQL Agent.


Group by with showing full rows in Postgresql I have a table like below with two columns A, B:

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,1),
  (1,1),
  (1,2),
  (1,2)
) AS t(a,b);

I want the result like below:

A B
1 1
1 1
sum B = 2

1 2
1 2
sum B = 4

How I can do it in PostgresSQL. Thanks everyone a lot.

You just want this

SELECT a,b,sum(b)
FROM foo
GROUP BY a,b;

 a | b | sum
---+---+-----
 1 | 2 |   4
 1 | 1 |   2

If you don't want to reduce the row set, you can do this..

SELECT a,b,sum(b) OVER (PARTITION BY a,b)
FROM foo;

 a | b | sum
---+---+-----
 1 | 1 |   2
 1 | 1 |   2
 1 | 2 |   4
 1 | 2 |   4


Best way to design a database with one person with different roles We've been browsing for a while looking for an answer to this, but any of them suited exactly my needs, I hope is a relevant question.

We have a database for managing books. In this database, a same person can be author and editor, or author and translator.

We have chosen this schema:

However, wen we design the view for books, that have to have the title, the author, the translator, etc., we have to do it like this, in order to get the name of the person related to the role:

But we know that it's not the best way. I think is a relevant question, because this problem is a very common one in order to design a good and solid database, and this knowledge is not always specified in manuals.

Thank you!

Just to help other people with same doubts: the good answer is ypercube's commentary.


Postgresql Physical Reads and Buffer Reads in PostgreSQL how to identify the number of page reads from disk or Shared Buffer
How to find the Hit Ratio for the Shared Buffers
Any queries to support this.

If you want to know it per database:
SELECT datname, blks_read, blks_hit
FROM pg_stat_database;

If you are interested in values per table, use
SELECT * FROM pg_statio_all_tables;

If you want to have statistics per query, install pg_stat_statements and use
SELECT query, shared_blks_read, shared_blks_hit
FROM pg_stat_statements;

Don't forget that since PostgreSQL uses buffered I/O, a “block read” might come from the kernel cache rather than from disk.


Correct way to design a shopping cart websites database I am in a web applications course. My instructor really didn't like the layout of my database, saying it was incorrect in pretty much every way. Fast forward 2 months when I'm remaking the website in a new language. I can't for the life of me see what's wrong. What is the correct way of making this layout? I have to use these and only these tables.

regUsers:
un
addr
PK uID
email
etc

Links to orders on mutual PK uID

orders:
orderID
pk uID

Links to orderDetail on orderID

orderDetail:
PK orderID
total
items (list)
date
address

Each item in items links to products. Not sure how to write a formal link between a list of Product IDs to the product table in MS SQL server 2008

product:
PK prodectNo
Pname
notes
price
imgpath
stock

Which links to productType on productNo PK

Product Type:
productNo
type

The main problem with your schema is the OrderDetail table: you have mixed data which is atomic (the date, the address) along with repeating data - the products ordered.

Your Orders table should be as follows

OrderId (primary key)
OrderNumber
UserID (foreign key to users)
OrderDate

(address really belongs to the user)

whereas the OrderLines table should be as follows

OrderLine (primary key)
OrderId (foreign key to orders)
PartID (foreign key to products)
Quant
Price
Discount
Total (although this can be calculated as quant * price * (100 - discount) * 0.01)

Each order line has a total but there should not be a 'total' field in the orders table; instead, calculate this from the orderlines table.

The 'OrderLine' field might be redundant, if 'OrderId' and 'PartId' can be used as a composite primary key, which would mean that no part can appear twice in the same order. This might seem reasonable, but if you add fields such as 'supply date' (the date by which the customer wants to receive each part), you would have problems if a customer orders 100 widgets to be supplied by the end of December and another 100 widgets to be supplied by the end of January. The surrogate key (orderline) is probably the safest and easiest option.


Is saving disk space still considered an advantage of normalization? I have read a while back that one of the advantages of normalization is to save disk space (since normalization minimizes redundancy), but is saving disk space still considered an advantage of normalization in current time where disk storage is very cheap?

Normalization was never about saving disk space. In his 1971 normalization paper E.F.Codd wrote: "It is important to remember that we are not making a case for or against any physical storage structures in this paper"[1] (emphasis in the original).

Normalization is all about the best logical representation for data and by no means implies that a normalized database will take up less space than a denormalized one.

Given the range of advances in database storage techniques since then (e.g. compressed formats, join indexes, columnar storage) there is probably even less correlation between normalization and storage size today than there was 50 years ago.

[1] Normalized Data Base Structure: A Brief Tutorial


MySQL converting column of type TEXT to TINYTEXT truncates value at non-intuitive position, why? I recently ran into a problem where a table had a column of type TEXT inadvertently converted to type TINYTEXT (MySQL version 5.6.24). We were able to restore any truncated data, but the confusing thing was was that we only noticed the problem when certain values for the column started appearing to be truncated at seemingly random positions in the text string.

After trying to wrap my head around what happened I finally realized that each value originally >= 256 chars was truncated to a length equal to the difference between its original length and the nearest lower multiple of 256. Eg:

Original Length Truncated Length Difference
257             1                256
350             94               256
597             85               512

I then confirmed this is exactly what happens when you convert a TEXT column to TINYTEXT. It's as if it iteratively removes chunks of 256 bytes until the total length is < 256. But why wouldn't it simply just truncate the value to 255 bytes to begin with? Seems like a more efficient operation, but I have no idea about how the internals work.

I couldn't find an explanation on the MySQL documentation. I mean, either way if you have values that exceed the max length you're going to lose data; obviously one wouldn't do this on purpose, but figuring out what happened would have been a bit less confusing if all of the truncated values had length 255.

EDIT: I confirmed this behavior for both the MyISAM and InnoDB storage engines.

EDIT AGAIN: Code to reproduce scenario:

create temporary table tmp (
    text_col text
)
collate = 'utf8_general_ci'
engine = InnoDB;

create temporary table results (
    current_value text,
    length_text_col int(11),
    char_length_text_col int(11),
    text_col_type char(8)
)
collate = 'utf8_general_ci'
engine = InnoDB;

insert into tmp
    (text_col)
values
    (repeat('a', 257)), -- 1 byte/character
    (repeat('ア', 86)); -- 3 bytes/character

insert into results
select
    text_col as current_value,
    length(text_col) as length_text_col,
    char_length(text_col) as char_length_text_col,
    'TEXT' as text_col_type
from
    tmp;

alter table tmp
change column text_col text_col tinytext;

insert into results
select
    text_col as current_value,
    length(text_col) as length_text_col,
    char_length(text_col) as char_length_text_col,
    'TINYTEXT' as text_col_type
from
    tmp;

select * from results;

The above code will select:

current_value  length_text_col  char_length_text_col text_col_type
aaaaaaaaaa...  257              257                  TEXT
アアアアア....  258              86                   TEXT
a              1                1                    TINYTEXT
               2                2                    TINYTEXT

There is essentially no good reason to ever use TINYTEXT.  And there are some obscure drawback.

If you are using utf8 or utf8mb4, then you may have "multi-byte" characters.  TINYTEXT is limited to 255 bytes.  VARCHAR(255), which is similar in many ways, is limited to 255 characters.

If your text is any any Asian language, then only about 85 characters can be stored in TINYTEXT.  For VARCHAR(255), a full 255 characters can be stored.

More

I agree that the ALTER is messing thing up in a strange way.  Here's a shorter example:

DROP TEMPORARY TABLE tmp;
create temporary table tmp (
    text_col text
) collate = 'utf8_general_ci' engine = InnoDB;

insert into tmp
    (text_col)
values
    (repeat('a', 257)), -- 1 byte/character
    (repeat('ア', 86)); -- 3 bytes/character

SHOW CREATE TABLE tmp\G
SELECT text_col, length(text_col) FROM tmp;
alter table tmp
    change column text_col text_col tinytext;
SELECT text_col, length(text_col), HEX(text_col) FROM tmp;  -- Strangly truncated
SHOW CREATE TABLE tmp\G

The HEX comes back with the first two bytes of Katakana 'A', which is E382A2, so I will guess that the ALTER did some kind of modulo -- picking (258 % 256 == 2) bytes to keep.

Fails for me on 5.5.43, 5.6.22 and 8.0.2.  On the other hand, MariaDB 10.0.28 and 10.3.7 complain about the ALTER:

mysql> alter table tmp
    ->     change column text_col text_col tinytext;
ERROR 1406 (22001): Data too long for column 'text_col' at row 1

File a bug at bugs.mysql.com.


Never do SQL Server patching? I am aware of Brent's post Why Nobody Ever Patches Their SQL Servers in this regard.....and with one of my friend, please assume one such scenario, where nearly 100 SQL Servers are never patched. Assume that no bugs or performance issues have ever been reported by any user. So, is there a cool way for my friend to identify across all the servers - where he is unaware - and few of the servers may already be hitting some bugs? In other words, how to pro-actively find the bugs across 100 SQL Servers?

One plan is to read release notes of those bugs where bug could be identified using a server trigger on occurrence basis (may be) .... capture that in local tables....SSIS those at a central location.

But, does this plan seem worth?

Special interest on those bugs where some impact on data by any chance. (FYI, DBCC CheckDB with purity runs error-less). Or some possible loss to business.

And finally assuming that, indeed, some bugs detected in some SQL Servers - so, how and why to treat them as "threat" when no issue reported by users ever from many years?

I assume you're talking about me, and I'm not sure what post you mean, but here is my thought:

Patch your SQL Servers regularly.

I'm such a huge fan of patches that I wanted to make it easier for people to find the most current updates for SQL Server, so I built SQLServerUpdates.com.

If you ever want justification for why you should be patching, just go back through the list of hotfixes in each cumulative update that you've skipped. You'll find bugs that deliver incorrect query results, and that's usually all management needs to hear in terms of why we need to patch.

Here are just a few fixed SQL Server 2017 bugs for incorrect query results - not an old, dusty version of SQL Server, but the brand new one they just released:

All transactions reported as completed even though some fail while trying to commit in SQL Server 2017
Incorrect behavior when you use memory-optimized tables with "where exists" statement in SQL Server 2016 or 2017
Returns incorrect results when computed column is queried after installing hotfix that's described in KB 3213683 and enabling TF 176 in SQL Server 2016 or 2017
Queries against PolyBase external tables return duplicate rows in SQL Server 2016 and 2017
Incorrect MDX query results if a table cross join is run on a table hierarchy member in SSAS
A data mask on a floating points column is not applied in SQL Server 2017 and SQL Server 2016
Divide-by-zero error when a parallel query is forced to run in serial mode in SQL Server 2017

And all that was only the first cumulative update. Want your query results to be accurate? Get your patching on.


Failed to create appdomain "master.sys[runtime].2" I am getting this error in SQL Server log:

  Failed to create appdomain "master.sys[runtime].2"
  Could not load file or assembly System.Data . Not enough storage is available to process this command

After this error, my web application is not working. Following are the server specs:

SQL Version : SQL 2012 (SP1) - 11.0.3128.0 Intel(x86) Standard Edition
RAM : 64 GB (Max Memory: 2147483647)
Hard Disk Memory Size Available : 600 GB
Effective memory in use by instance:
(1) physical_memory_in_use_Gb : 2.94 (2)locked_page_allocations_Gb:0 3)virtual_address_space_committed_Gb: 2.98

I have searched on this. But not got any proper solution. Please help me.

I finally understand why your server uses only 3Gb of RAM, you have 32-bit version of SQL Server: SQL 2012 (SP1) - 11.0.3128.0 Intel(x86) and it just cannot use more than 4Gb.

You should use 64-bit version of the Server to resolve your problem.

In previous versions (until 2012) it seems -g parameter could be useful:
SQL Server 2012 Memory

  -g startup parameter

  We used the -g startup option to change the default value of a region
  in SQL Server user address space known as "Memory-To-Reserve". This
  region was also known as "memory-to-leave or MTL.  The
  "Memory-To-Reserve" (or) -g configuration option are relevant only for
  a 32-bit instance of SQL Server.

  Multi pages allocation and CLR was part of Mem-to-reserve (-g)  in In
  previous SQL Server versions until SQL Server 2008 R2 , From Denali
  they are part of BPOOL (Controlled by Max server memory)  So you may
  have to remove –g if you have set it to give space for multipage
  allocator or CLR in earlier versions and migrating to Denali now.

But since you are on 2012 it seems the only way is to install SQL Server x64


Filter 1 columns data into 3 columns I need to filter data in 1 column and have an output of three columns (Column A,Column B and Column C).

The output columns should display values of 11 for column A, values of 12 for column B, and Total of A & B for column C.

The current query I have written:

select count(zcsms_studentgender) as MaleStudents,
       count(zcsms_studentgender) as FemaleStudents,
       count(zcsms_studentgender) as TotalStudents
from zcsms_studentdefinition
where (zcsms_studentgender = '11' or zcsms_studentgender = '12')
and zcsms_studentactive = 'Y';

I can't seem to add filtering into the first two columns (MaleStudents & FemaleStudents) to display only count values of 11 (male) and 12 (female).

It's a report based on student gender. The value for male students is 11, the value for female student is 12. These are stored in 1 table, 1 column. My output has to filter these into a report that will display as Column A (Male Students count) Column B (Female Students count) and Column C
(total count of A and B).

Just use SUM() and a CASE statement:

SELECT sum(case when zcsms_studentgender=11 then 1 else 0 end) as malestudents,
       sum(case when zcsms_studentgender=12 then 1 else 0 end) as femalestudents,
       sum(case when zcsms_studentgender in (11,12) then 1 else 0 end) as totalstudents
FROM zcsms_studentdefinition
WHERE zcsms_studentactive = 'Y';


Ubuntu broken need to recover mysql db With a recent Ubuntu upgrade it has broken my system. I now only have access to the filesystem through a live CD. I had a MySQL database on the system with many objects and views I would hate to lose. Can I recover this somehow? I don't care about the data; I just need the DDL structure of everything.

If you want to recover by filetype, please note the following structure in MySql database file system:

A MySQL table is the combination of three files:

The FRM file is the table definition.

The MYD file is where the actual data is stored.

The MYI file is where the indexes created on the table are stored.

Source:
[1] https://stackoverflow.com/questions/484750/restoring-mysql-database-from-physical-files
What this does not provide are the views. I did search a while and could not find where they are.
Same source tells how to restore the database from files. Copy them to  /var/lib/mysql/ on machine that has same version of Mysql installed.
That is needed to get more complex structures out of the files, and if the file sizes don't limit you, it would be also the most convenient way to do this.
Note you should close the server from running before changes in filesystem.


MySQL server connections capped at 1013 For the last few days I've been trying to figure out why my MySQL read slave servers are capped at 1012+1 (+1 for the superuser) connections when I have the max_connections set to 5000. It seems that no matter what I change, the "Historic max_used_connections" never increases beyond 1013.

The mysql read slaves support a high traffic web site. I have a cluster of 3 read only slaves. Each read slave seems to be capped at 1013 max connections. I can take two out of the cluster so all connections go to one and still only 1013. No error messages show up in the mysql log file.

A mysqladmin status on one of the read slaves shows this:
Uptime: 1661  Threads: 128  Questions: 17634224  Slow queries: 0  Opens: 2441  Flush tables: 1  Open tables: 938  Queries per second avg: 10616.630

Same server, mtop shows this:
load average: 0.05, 0.15, 0.19 mysqld 5.1.66-log up 0 day(s),  0:28 hrs
106 threads: 4 running, 906 cached. Queries/slow: 1.6K/0 Cache Hit: 99.96%
Opened tables: 0  RRN: 278  TLW: 1.0K  SFJ: 0  SMP: 0  QPS: 1

Each server has 12 dual-core Intel CPU's (24 cores) with 140 GB of RAM running RHEL 6.3, Kernel 2.6. (x86_64)

Any idea what would be limiting/capping the connections at 1012+1 ?

EDIT
Also wanted to add two things:

1) I am running the "free/community" edition of MySQL that you get from a Red Hat 6.3 YUM install. Could there be some limitation in that version?

2) I've changed the /etc/security/limits.conf file on the MySQL read slaves, increasing values for users mysql and root (did not help my situation):

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1161534
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 10240
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 32768
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

We had the same issue.  Put this at the top of the mysql start script.

ulimit -u 102400 -n 102400

Has to be at mysql start time.


Type conversion in expression may affect “CardinalityEstimate” - on a computed column? I had a look at this question:

Type conversion in expression may affect “CardinalityEstimate” in query plan choice?

But that was related to collation and not with computed columns.

I have the following table definitions that are used in a query below that is giving me a query hint

  Type conversion in expression
  (CONVERT(varchar(10),[t].[FLTCD_FLT_DATE],112)) may affect
  "CardinalityEstimate" in query plan choice

Please note the computed column flightReference

CREATE TABLE [dbo].[repl_Transportation] (
[FLIGHT#]          INT                              NOT NULL,
[FLTCD_FLT_DATE]   DATETIME                             NULL,
[FLTCD_DEP_GATE]   CHAR(3)                              NULL,
[FLTCD_ARR_GATE]   CHAR(3)                              NULL,
[FLTCD_SEQUENCE]   CHAR(1)                              NULL,
[DIRECTION]        CHAR(1)                              NULL,
[PNR_NUMBERS]      VARCHAR(70)                          NULL,
[HK]               NUMERIC(3,0)                         NULL,
[Create_Date]      DATETIME                         NOT NULL,
[Modify_Date]      DATETIME                             NULL,
[flightReference]  AS (substring(((CONVERT([varchar](10),[FLTCD_FLT_DATE],
 (112))+[FLTCD_DEP_GATE])+[FLTCD_ARR_GATE])+[FLTCD_SEQUENCE],(3),(13))) PERSISTED,
CONSTRAINT   [PK_FLIGHT#]  PRIMARY KEY CLUSTERED    ([FLIGHT#] asc))

IF OBJECT_ID('[dbo].[repl_Transportation_Details]') IS NOT NULL
DROP TABLE [dbo].[repl_Transportation_Details]
GO
CREATE TABLE [dbo].[repl_Transportation_Details] (
[FLT_LEG_ID]     INT                              NOT NULL,
[FLIGHT#]        INT                              NOT NULL,
[LEG_NO]         TINYINT                              NULL,
[AIRLINE_CODE]   CHAR(2)                              NULL,
[AIRLINE_FLTNO]  VARCHAR(6)                           NULL,
[DEP_DATE_TIME]  DATETIME                             NULL,
[ARR_DATE_TIME]  DATETIME                             NULL,
[DEP_AIRPORT]    CHAR(3)                              NULL,
[ARR_AIRPORT]    CHAR(3)                              NULL,
[Create_Date]    DATETIME                             NULL,
[Modify_Date]    DATETIME                             NULL,
CONSTRAINT   [PK_FLT_LEG_ID]  PRIMARY KEY CLUSTERED    ([FLT_LEG_ID] asc))

When I run the following query I get a warning on the select:
The query plan is here

  Type conversion in expression
  (CONVERT(varchar(10),[t].[FLTCD_FLT_DATE],112)) may affect
  "CardinalityEstimate" in query plan choice

    SELECT  t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    MIN(td.DEP_DATE_TIME)     AS departDate,
    MAX(td.ARR_DATE_TIME)     AS arrivalDate,
    CASE WHEN
     PNR_NUMBERS IS NULL OR PNR_NUMBERS = ''
     THEN 'Paper Ticket'
     ELSE 'E-Ticket'
    END          AS ticketType,
    'US'         AS source,
    0          as sourceId,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE
 FROM        repl_Transportation      AS t
 LEFT JOIN repl_Transportation_Details    AS td ON td.FLIGHT# = t.FLIGHT#
 GROUP BY t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE

When I remove the MIN and MAX columns from the select,
there is no more any warnings, the query plan is here

    SELECT  t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    --MIN(td.DEP_DATE_TIME)     AS departDate,
    --MAX(td.ARR_DATE_TIME)     AS arrivalDate,
    CASE WHEN
     PNR_NUMBERS IS NULL OR PNR_NUMBERS = ''
     THEN 'Paper Ticket'
     ELSE 'E-Ticket'
    END          AS ticketType,
    'US'         AS source,
    0          as sourceId,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE
 FROM        repl_Transportation      AS t
 LEFT JOIN repl_Transportation_Details    AS td ON td.FLIGHT# = t.FLIGHT#
 GROUP BY t.FLIGHT#,
    t.flightReference,
    PNR_NUMBERS,
    DIRECTION,
    FLTCD_DEP_GATE,
    FLTCD_ARR_GATE

Is there any way I can get rid of this warning?

Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it?

I had a look at this question:
Type conversion in expression may affect “CardinalityEstimate” in query plan choice?
But that was related to collation and not with computed columns.

It's the same issue. The warning is informational, to help you understand any issues you might notice with cardinality estimation.

Is there any way I can get rid of this warning?
Is it a persisted column, the conversion has been already done, why exactly is the query optimiser going on about it?

Just because the computed column is persisted, does not mean the optimizer will choose to use the persisted value. SQL Server routinely expands computed columns (persisted or not) into the underlying definition at the start of the compilation process. Later on, it may, or may not, match expressions back to the available persisted computed columns. When the optimizer encounters the expression, it may add a warning to the plan.
The only way to prevent this expansion and match-back is to enable trace flag 176 on suitable versions.

When I remove the MIN and MAX columns from the select, there is no more any warnings

With this change, the optimizer is able to simplify the query to remove the unnecessary join. The result is a TRIVIAL plan, with no cost-based choices, so there is no warning about plan choices.


SQL Statements truncated in log files Does PostgreSQL cut statements if there is a subselect in it?

We use log_min_duration_statement = 20ms to find statements which need a long time.

But unfortunately the statements are truncated:

2014-11-20 14:09:25 CET foo3_egs_p foo3_egs_p [22106] LOG:  duration: 99210.696 ms  statement: SELECT "foo3_ticket"."tickettype_id", "foo3_tickettypegroup_tickettypeen"."tickettypegroup_id", "foo3_tickettype"."ordner_id", COUNT("foo3_ticket"."id") AS "pk__count" FROM "foo3_ticket" INNER JOIN "foo3_tickettype" ON ("foo3_ticket"."tickettype_id" = "foo3_tickettype"."id") LEFT OUTER JOIN "foo3_tickettypegroup_tickettypeen" ON ("foo3_tickettype"."id" = "foo3_tickettype group_tickettypeen"."tickettype_id") WHERE ((

The line ends with WHERE ((.

That's sad, because I would like to copy the statement to debug it with EXPLAIN ANALYZE

Why does the above statement get truncated? Any way to get the whole statement?

This is a legacy environment with PostgreSQL 9.1.2

Answer to own question: I was blind. The statements are not truncated, but they contain newlines. There was some pre preprocessing done before I looked at them. And this preprocessing was done lines based, so they looked like being cut.


PostgreSQL Replication delay debugging I've got a cluster of 9.1 servers running streaming replication and using WAL-E based log shipping for when that falls behind.

Generally speaking it's a pretty solid setup and does not fall behind, however we appear to have one slave in the cluster that consistently falls behind when replicating in the wee hours of the morning. It always catches up again shortly after but I have not yet been able understand why it does this.

There does not appear to be consistency with regards to the timing apart from it happens when our application is not handling a lot of load. Is it possible that I'm seeing this occur because of lack of data to be replicated?

Here's what the relevant portions of the postgresql.conf and recovery.conf look like

postgresql.conf

archive_mode = on
archive_command = '. /mnt/data/postgresql/.profile && wal-e wal-push %p'

recovery.conf

recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=master_db_ip_address port=5432 user=postgres'
restore_command = '. /mnt/data/postgresql/.profile && wal-e wal-fetch "%f" "%p"'

To add a little more context, you can see the replication delay for this server is creeping up as time goes on.

This should not occur. The streaming replication delay should be < 1-2s most of the time (based on past experience with this cluster)
There are no scheduled jobs running on the db cluster at the time these spikes in the replication timing occur.

What are some good strategies for debugging this kind of issue?

EDIT

I had it suggested to me that my methodology for computing the replication delay may be incorrect, here's what I'm using:

SELECT EXTRACT(EPOCH FROM NOW() - pg_last_xact_replay_timestamp())

Maybe you are experienceing what is described at the end of the following article:

http://www.niwi.be/2013/02/16/replication-status-in-postgresql/

  In a very busy database, with many writes per second, this number will remain fairly accurate. However, in a system where there are few writes, the "replication_delay" will continually grow because the last replayed transaction timestamp isn't increasing (this is generally the same limitation as MySQL's SHOW SLAVE STATUS output).


Recursive Join to get Spans I am pretty new to working in SQL at and have a gaps and islands problem in which I am trying to get a series of consecutive months of membership. Any member may have multiple memberships and their membership may have gaps or overlap. My thought was to order memberships by member and plan and work backward within this group month by month until I find a gap, then assign the begin and end date of the membership.

Here's a sampling of my data:

Member  YYYYMM      Plan
1       201601      123
1       201602      123
2       201505      123
2       201506      123
2       201510      123
2       201511      123
2       201512      123
1       201510      456
1       201511      456
1       201512      456
1       201601      456

The above data would represent memberships for two separate members to same/different plans for different time spans. Now, I need to get it to look more like this:

Member  YYYYMM_Begin    YYYYMM_End    Plan
1       201601          201602        123
2       201505          201506        123
2       201510          201512        123
1       201510          201601        456

So far, I've got a recursive join that partitions based on Member and Plan and ranks them in descending order by YYYYMM, which seems to be working. But I'm stuck on how to figure out the begin and end dates for consecutive months of membership.

SELECT DISTINCT A.Member, A.Plan, A.YYYYMM, B.Rank
FROM Member_Hist A
 JOIN (SELECT
    MH.Member,
    MH.Plan,
    MH.YYYYMM,
RANK() OVER (PARTITION BY Member, Plan ORDER BY YYYYMM DESC) AS Rank
FROM Member_Hist MH) B ON A.Member = B.Member AND A.Plan = B.Plan
ORDER BY Member ASC, Plan ASC, YYYYMM DESC

Edit: I'm using Oracle, not MySQL; Also, I only have read permission.
Edit: Sorry, NOT using MySQL or SQL Server

This is a Gap & Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions

Here is how the query works:

YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format.
then ROW_NUMBER() will number them by group of Member and [Plan]
consecutive months will have consecutive rn and once rn is removed from the date, they will have identical values for DATEADD(month, rn, l1.dt)

Member  Plan    dt          rn  => DATEADD(month, rn, l1.dt)
2       123     2015-12-01  1   => 2016-01-01
2       123     2015-11-01  2   => 2016-01-01
2       123     2015-10-01  3   => 2016-01-01
2       123     2015-06-01  4   => 2015-10-01
2       123     2015-05-01  5   => 2015-10-01

DATEADD(month, rn, l1.dt) is finally used to group them along with Member and Plan

Query Oracle:

WITH dates AS(
    SELECT DISTINCT Member, "Plan"
        , dt = CAST(YYYYMM || '01' as date)
    FROM data
), list AS (
    SELECT *
        , rn = ROW_NUMBER() OVER(PARTITION BY Member, "Plan" ORDER BY dt DESC)
    FROM dates d
)
SELECT l1.Member
    , MIN(l1.dt) as YYYYMM_Begin
    , MAX(l1.dt) as YYYYMM_End
    , l1."Plan"
FROM list l1
GROUP BY Member, "Plan", ADD_MONTHS(l1.dt, rn);

Oracle [SQL Fiddle][]

Query SQL Server:

WITH dates AS(
    SELECT DISTINCT Member, [Plan]
        , dt = CAST(YYYYMM+'01' as date)
    FROM @data
), list AS (
    SELECT *
        , rn = ROW_NUMBER() OVER(PARTITION BY Member, [Plan] ORDER BY dt DESC)
    FROM dates d
)
SELECT l1.Member
    , MIN(l1.dt) as YYYYMM_Begin
    , MAX(l1.dt) as YYYYMM_End
    , l1.[Plan]
FROM list l1
GROUP BY Member, [Plan], DATEADD(month, rn, l1.dt);

SQL Server SQL Fiddle

Output:

Member  YYYYMM_Begin    YYYYMM_End  Plan
1       2016-01-01      2016-02-01  123
1       2015-10-01      2016-01-01  456
2       2015-05-01      2015-06-01  123
2       2015-10-01      2015-12-01  123


Will “Oracle Express 11gR2” installation mess with the "Opera PMS" software As in the title, I want to install "Oracle Express 11gR2" on a Win7 machine that runs Opera PMS Software (pms for Hotel Management) via Internet Explorer.

I want to write a Python script that will use cx_oracle module. I tried first with "instant client": I put its PATH in windows environment and followed the tutorial, but I still have an import error with cx_oracle module or an error that tells me there is no Oracle application on the computer.

It seems I have to install the Oracle express instead of using the "instant client". But first, I must be sure there will be no dependencies mixing up and messing the system. Can I do that blindly? I can give further info if needed

It's hard to know what new software breaks prior to testing it out on your system, so it's just best to create a Recovery Point on your PC prior to doing so to be safe.  After you create a manual Recovery Point, perform your install, reboot, and test stuff out.  If you feel dependencies were affected and you're unable to resolve them, you can always recover your system back to the recover point, effectively rolling back the installation (and any damage it caused).


Temporarily compressing MySQL binlogs I am currently importing a pretty big MySQL database dump. This takes time and during this time I need some extra disk space. Will I break something in MySQL if I temporarily gzip a couple of it's binlogs? I'm assuming I'm not going to compress binlogs that are purged, nor written, nor read by the MySQL instance.

Example:

$ ls
mysql-bin.000340
mysql-bin.000341
mysql-bin.000342
mysql-bin.000343.gz
mysql-bin.000344.gz
mysql-bin.000345.gz
mysql-bin.000346
mysql-bin.000347
mysql-bin.000348
mysql-bin.index

Yes, it is perfectly safe to do so as long as you don´t gzip the file which is currently written (the last file).

Also in case of a replication setup ensure that the files you are gzipping are already fetched by the slave. You can verify this by checking the output of SHOW SLAVE STATUSon the slave, look here for Master_Log_File, this will give you the logfile of the master which is currently be fetched by the slaves io-thread. Every preceding file is absolutely safe to gzip.


How to enable email notification for failover in SQL Server We have a few servers in a High Availability group and need to configure email notification for any failover that happens on the server.

You can set up a SQL Agent alert for event 1480 that filters on the string

"RESOLVING" to "PRIMARY"

The reason for the filter is that 1480 events cover all state transitions.  If a secondary loses connection for a bit, it will go into resolving, then back to secondary, so if you don't use a filter you'll get alerts on more than just failover.

USE [msdb]
GO

EXEC msdb.dbo.sp_add_alert @name=N'HA Error Number 1480 - R2P filter',
        @message_id=1480,
        @severity=0,
        @enabled=1,
        @delay_between_responses=60,
        @include_event_description_in=1,
        @notification_message=N'Failover Detected!',
        @event_description_keyword=N'"RESOLVING" to "PRIMARY"',
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'
GO


Which of these queries is best for performance? Which of these queries are best for performance ? Sometimes I wonder if SHORT scripts really is the best thing to focus on. These scripts performs the same task. With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method? Between this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        INNER join products P ON p.productID = q.productID
        LEFT JOIN colors C ON C.colorID = q.colorID
        LEFT JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID

and this:

SELECT p.productID, p.product, C.color, C.colorID, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
        --WHERE q.productID = @productID
    UNION
    SELECT p.productID, p.product, NULL, NULL, S.size, S.sizeID, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN sizes S ON S.sizeID = q.sizeID
    WHERE /* q.productID = @productID AND */ q.sizeID IS NOT NULL AND q.colorID IS NULL
    UNION
    SELECT p.productID, p.product, C.color, C.colorID, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
        INNER JOIN colors C ON C.colorID = q.colorID
    WHERE /* q.productID = @productID AND */ q.colorID IS NOT NULL AND q.sizeID IS NULL
    UNION
    SELECT p.productID, p.product, NULL, NULL, NULL, NULL, q.qty From quantities q
        inner join products P ON p.productID = q.productID
    WHERE /* q.productID = @productID AND */ q.colorID IS NULL AND q.sizeID IS NULL

EDIT:

  SQL Server parse and compile time:     CPU time = 32 ms, elapsed time
  = 65 ms.

  (10 row(s) affected) Table 'sizes'. Scan count 1, logical reads 21,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count
  1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical
  reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'products'. Scan count 0, logical reads 20, physical reads 1,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table 'quantities'. Scan count 1, logical reads 2,
  physical reads 1, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 12
  ms.

  (10 row(s) affected) Table 'products'. Scan count 0, logical reads 20,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'quantities'. Scan
  count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob
  logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table
  'Worktable'. Scan count 0, logical reads 0, physical reads 0,
  read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0. Table 'sizes'. Scan count 0, logical reads 18,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0. Table 'colors'. Scan count
  0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical
  reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

It seems to me that it's the second query that is best for performance, the bigger query with unions.

Do anyone of you got a clue why this happends or do I have to provide with more information (table info and such things) ?

Execution plan:

Thanks!

Sometimes I wonder if SHORT scripts really is the best thing to focus on.

The size of a script has little to do with how efficiently the query will execute. A more compact statement will likely consume fewer resources in terms of compilation, but (re)compilation is usually a rare occurrence in a live system.

Fewer table accesses is usually desirable, though, and this does lead to more compact code.

Very generally speaking, a smaller execution plan will yield better results, and a lower estimated cost will yield better results. Again, though, it's highly situational. Cost estimates in particular can be way off in some cases. It's important to measure the actual execution time, because at the end of the day, that's what matters.

  With left joins i can achieve what i want with just a few lines. But then I tried with a longer script, using unions. Which is the best method?

First of all, we need to know how much data will be in these tables in a real system. Right now there's so little it will be difficult to use the STATISTICS TIME performance metrics to figure out a winner -- the results that come back will be dominated by factors other than the query execution. With more data, it's likely the plans will change, thus rendering the comparison here moot.

Having said that, by looking at the query plans as they are now from a logical point of view, the first one is the winner.

You can see that the Clustered Index Scan of quantities appears once in the first plan, while it appears four times in the second one. The second plan also contains an expensive Distinct Sort as a result of using UNIONs (this operator could be eliminated by using UNION ALLs instead, which won't change the results).

The first query could also probably be improved, by getting index seeks on the colors and sizes tables, instead of table scans. It might be worth trying a hash match plan as well (which is what you'll probably see when quantities and products are larger), but for tables this small, the startup cost may be too much overhead to be of benefit.

What I would suggest you do is run each of the statements you want to test 10,000+ times in a loop, figure out the average execution time, and then compare.


Purpose of owning a schema in SQL Server I've been spending the last few days cleaning up database permissions, grouping them all into AD groups and removing orphaned users.

This has lead me to a recurring scenario:

Find a user that owns a schema
Transfer schema ownership to SA
Remove user

The schema's are not subject to regular change, and most of the time it's a user owning the dbo schema.

I've been researching what the reasoning might be behind giving these users ownership of schema s, however I can't really find a proper reason. Most documentation speaks of schema membership, using schema's for permissions, but I'm hard pressed finding much about schema ownership.

What might be a correct reason for giving users ownership of schema's?
Are there any hidden dangers in removing schema ownership from orphaned users?

An owner of a database object has full rights on it automatically and unrevokably. But of course you can give an user all rights to a schema without making him the owner.


Amazon RDS MySQL performance issues I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance.

It seems that all queries, but especially select statements, are performing very slow. If I check this issue on my local PC, it is working very well. But when I try to get data from RDS instance, it is slow. Some of the select statements takes 2-3 seconds to fetch data.

I have properly tuned up all table indexes, and normalized/de-normalized as required. I have made all necessary settings on RDS custom parameter group eg. max_connection, buffer etc. (don't know if I am something missing), but it has not helped. Can someone help me figure out the issue?

When you create an RDS instance, you pick an instance size, a database size, and optionally determine if you need specific IOPS.

I would start by looking at the RDS monitors, and see if there are any parameters that are hitting limits.  You may need to increase the instance size or IOPS to increase performance.


How to collect NTFS file properties and insert into SQL Server table I have to provide reports on file system usage.

I'm collecting statistics on file server usage down to individual file level so we can see who is using what files/folders, how much storage they're using, how many files they have, when they were created and last used.

To do this I have 2 powershell scripts.

The first reads through the file system and captures the attributes I want and saves them to a file.

dir -rec G:\ | Select LastWriteTime, Directory, Name, Extension, Length, @{Name="Owner";Expression={get-acl $_.FullName| select Owner}} | export-csv FileInfo.csv

The 2nd script reads the csv file and inserts the data into a table.

Once the data is in SQL I can parse the text and split it into various columns and and then produce a variety of reports and analyse the data in different ways. My approach works but it's cumbersome.

Is there a better way to collect NTFS information and save it into SQL Server? What are the alternatives? SSIS?

Edit: Could this all be combined to operate together in a single process?

SSIS is well equipped to handle CSV files and load them into SQL Server.

You can have a very simple package using the Flat File Source.

The dialogue and setup is a familiar windows "wizard" like process, and most of it is automated... what you need to pay attention to is that it has correctly guessed your file for lengths and data types. You can either adjust the settings in the connection manager or you can later change data types with SSIS tasks. Note that if you have say 10,000 rows of integers and then start getting characters the flat file source may easily assign an integer data type to that column, then fail when it encounters the characters. Thus with large files that may not be well structured you have to pay more attention to these settings. The Suggest Types... button allows you to increase the number of inspected rows, but I have found that even this can still recommend the wrong data types.

SSIS is a huge tool and you can perform data clean-up tasks or even split data into different tables from the single CSV. If you have different tables use tasks like Multicast or Conditional Split. You may also find that Data Conversion and Derived Column can help you efficiently produce the data you need as it moves through your package.

I wouldn't do much more than clean, split, modify, and load the data into SQL Server with SSIS though. SQL Server is highly optimized to produce aggregates, sorts, etc., while SSIS is less capable for such tasks. Tasks like Aggregate are blocking transforms which essentially means it can stall your SSIS package and consume a lot of memory.

As an example the below SSIS dataflow performs the following tasks:

Reads a CSV file
Creates derived columns which are just trimmed versions of the originals
Performs a look-up to see if the record already exists in the destination
If the record was not found then it is inserted in the destination


Take action from extended event? I've seen it mentioned a few times that you can configure an event session to take an action when an event fires. Does anyone have any information on how to do this?  I know that you can use the service broker to take an action on an event, but I don't consider that to be part of an event session.

Erin Stellato's Stairway to Extended Events is a good starting point here (it's behind a registration wall, but it's worth it.) She explains how you can use the SSMS GUI to create an Extended Events session that will trigger a dump, for example.

Understand that taking an action like creating a dump will be very performance-intensive.


MySQL: How to drop all tables starting with a prefix? In my database there is a lot of tables starting with _elgg , now I want to drop all tables with this prefix. Can anyone give me a solution ?

Thanks in advance!

First do a

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '\_elgg%'
AND TABLE_SCHEMA = 'your_database_name';

and check if this returns the correct statements for every table. If you want to execute those statements, do

SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '\_elgg%'
AND TABLE_SCHEMA = 'your_database_name'
INTO OUTFILE '/tmp/whatever_filename';

SOURCE /tmp/whatever_filename;

Another way: On the command line, to do it in one rush, you can do

mysql -B databasename -uroot -prootpassword --disable-column-names  -e `mysql -B databasename -uroot -prootpassword --disable-column-names  -e "SELECT CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '\_elgg%' AND TABLE_SCHEMA = 'your_database_name';"`


How to create an Access query to find # symbol into a field? It seems easy but I'm missing something. I have a MySQL database that is connected from access through ODBC connector.

I'm looking for the fields where there are # symbol but I'm getting zero results, although I have a field with # (Access query).

Data

id    obs
1     #rejected

Access Query where I do not have any results

SELECT id, obs
FROM Person
WHERE obs Like '*#*'

+info added from answers

MySQL Query works fine

SELECT id, obs
FROM Person
WHERE obs Like '%#%'

SELECT id, obs
FROM Person
WHERE obs LOCATE('#',obs) >0

Which is the escape character in Access?
Any ideas to fix Access query?

For Access you can use:

WHERE obs LIKE '*[#]*'

There are three wildcard characters in Access that can be used with LIKE operator:

`*` for a string of arbitrary length (equivalent ANSI: `%`)
`?` for a single character (equivalent ANSI: `_`)
`#` for a single numeric digit
`[` for escaping

If you want to search for one of those wildcards, you have to enclose them in brackets: [ ]

You can find more info in the msdn article: Separate But Equal Wildcard Characters in SQL Server and Access


Database schema design implementation feedback I'm having difficulty on designing a database schema design with the right relationships. I'm currently trying to design a database system where a question can be answer via a  live_stream. The general idea is that a user can ask a question. Then a user can answer the question and set a live_stream. Many users can join that live_stream. Here is the diagram. https://dbdiagram.io/d/5f2a06ce7543d301bf5de918

I have added a link to the schema design where you can navigate the relationships. But basically the idea is as follows.

A user can create many questions. user 1 -> * question

A question belongs to a user

A user can like a question user 1 -> * likes,

A question can have many likes by users. question 1 -> * likes

A question has one answer question 1 -> 1 answer

and an answer belongs to a user user 1 -> answer

Here is something I have struggle with. Basically I have created a join table for a many to many relationship between users and answers. Using live_streams as the join table. The way I see it is that many users can watch the explanation of the live_stream for the answer and the answer can have many users watch the live_stream. Also I added presenter to the live_streams table. The presenter is also a user.

Many users can join a live stream users 1 -> * live_streams

A live stream belongs to an answer answers 1 -> * live_streams

A user is a presenter of the live stream user 1 -> 1 live_stream

I'm still a but skeptical about the way live_streams table works as a join table between users and answers. Is still clear to me that many users can join a live stream. but not sure if the relationship between answers and live_streams should be a one to many, but I see it as live_stream belongs to answer.
The user presenting the live stream has to be the person who answered the question. So if the answer belongs to a user, that same user should be the presenter in the live_streams table. The answers table works more as setting the time, description and name of when the live stream is going to take place.
Is this correct? I'm open to feedback as to how to improve the design or if there is any errors in the logic.

Ok, given there are no restrictions on the behavior of someone asking a question, we can forgo a lot of trouble migrating the UserId through the data model.
I don't think you're too far from the mark, just some unnecessary Id columns that aren't providing value.
This should satisfy the requirements as you've described them:

Some notes:

I changed Like to Vote since that gives more flexibility.
If we do need to restrict the behavior of the person asking the question, we would need to use their UserId in the primary key, or change the primary key from QuestionId to something like (UserId,AskDatetime) or (UserId,QuestionNumber).
If you do choose to allow more than one answer per question, you would change the primary key of Answer to (QuestionId,UserId) (although you might want to rename UserId to something like AnswererUserId).  To denote an answer as accepted, you'd need to create an AcceptedAnswer table where QuestionId is the primary key and AcceptedAnswer would have the relation to LiveStream.
The one 'oopsie' that could occur with this data model is logging that the presenter (the person who answered the question) also attended their own Livestream.  If you were doing attendee counts, you'd need to query Answer to make sure you don't count them.  If that is very mission critical, you'd move the UserId of the answerer to the primary key of Answer and migrate that through LiveStream to LiveStreamAttendee and place a check constraint to ensure the UserId of the attendee is not the UserId of the answerer.