How to copy table data from one db to another? How can I do this when the table has a constraint and already exists in both databases? I need to rewrite the data in the table on my server with the data in the table on my development computer.

I have tried various forms of pg_dump, using both plain-text and binary formats. I have also tried using the --clean and --data-only options, but what always gets me when I try to restore is the constraint on the table.

pg_restore -O -d database_name dump.pg

Results in this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2409; 0 56001 TABLE DATA attendees
pg_restore: [archiver (db)] COPY failed for table "attendees": ERROR:  duplicate key value violates unique constraint "attendees_pkey"
DETAIL:  Key (id)=(237) already exists.
CONTEXT:  COPY attendees, line 1
WARNING: errors ignored on restore: 1

I'm trying to do this using just pg_dump/pg_restore with no editing of the server db between running the two commands. In other words, I don't want to have to go in and manually drop table attendees cascade before running the restore.

Is this possible? If so, how?

No, you probably can't do that automatically.  If you are trying to dump and restore only fragments of the dependency chains, pg_dump and pg_restore have no way of knowing what your intentions are.  You are most likely going to need to spell them out for it, by supplying your own supplementary pre/post commands.

It is possible you could make a custom file to supply to pg_restore's -L command which correctly drops and recreates the constraints you need.


Cannot get SQLCMD to work regardless of what I try Regardless of what I do, I cannot get SQLCMD to work. I am trying to run a script that is 200mb+ in size and cannot be run from the sql server application so I need to do this via SQLCMD, however whenever I try to login or run the script it immediately closes.

I've followed the sqlcmd utility by msdn.

I've tried to opoen it via trusted command SQLCMD -E
I get:

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I've tried via explicit server, username and password declaration:

C:\Users\MyUserName\Documents>SQLCMD -S DESKTOP-1N3OD6V\SQLEXPRESS -U DESKTOP-1N3OD6V\MyUserName-P MyPassword
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'DESKTOP-1N3OD6V\MyUserName'..

SQLCMD, when invoked without specifying a server name, tries to connect to a default instance on the local machine. If you don't have one, it displays the first error you are getting. Use the -S parameter to specify a server name.

To use Windows authentication, use the -E flag and you don't need to provide username or password. Example: SQLCMD -Smyserver\myinstance -E

To use SQL Server authentication, use the -U and -P parameters to supply credentials. Example: SQLCMD -Smyserver\myinstance -UsomeUser -PhisPassword

If you specify -U you cannot use a windows user name: this is for SQL Server logins. If you want to impersonate a different windows user, start a command prompt impersonating that user first.

Example:

Open command prompt
Type runas /USER:domain\username cmd
Type the password for your user
Run SQLCMD

You can achieve the same result right clicking the link for cmd and selecting "Run as different user..."


mysql innodb db full text search FTS_DOC_ID column I am adding full text search to already existing mysql tables that have primary auto incrementing key of id integer.

When I add the full text index I get a warning:

/*!50604 ALTER TABLE `phppos_additional_item_numbers` ADD FULLTEXT INDEX item_number_search (`item_number`)*/;

Warning (Code 124): InnoDB rebuilding table to add column FTS_DOC_ID

I cannot simply refactor all my code to add a column FTS_DOC_ID to remove this warning. Is it bad to just allow this warning to happen?

Reading the manual it does not seem to be anything bad. MySQL just informs you about an additional action it had to do.

  InnoDB uses a unique document identifier referred to as a Document ID
  (DOC_ID) to map words in the full-text index to document records where
  the word appears. The mapping requires an FTS_DOC_ID column on the
  indexed table. If an FTS_DOC_ID column is not defined, InnoDB
  automatically adds a hidden FTS_DOC_ID column when the full-text index
  is created.

If you preferred to not get the warning, then you would have to manage the column yourself which looks like a lot of work which is currently automated:

  If you choose to define the FTS_DOC_ID column yourself, you are
  responsible for managing the column to avoid empty or duplicate
  values. FTS_DOC_ID values cannot be reused, which means FTS_DOC_ID
  values must be ever increasing.

One side effect of the actions producing this warning is that the table has to be rebuild - that might take some time for tables already containing many records. But the same will be true if you added the column yourself, so it does not make a difference for you.


How do I optimize query where searching for 'is not null' on either of two linked tables I have a query like this:

SELECT * FROM my_first_table a   -- > 1 million records
INNER JOIN my_second_table b     -- > 1 million records
    on a.id = b.a_id
LEFT OUTER JOIN my_third_table c -- > 1 million records
    on a.id = c.a_id
WHERE   b.my_column_x IS NOT NULL
OR      c.my_column_y IS NOT NULL
AND     b.start_date  > isnull(@startdate, '17530101')
AND     b.end_date    < isnull(@enddate, '99991231')

my_column_x and my_column_y are both indexed, however as I'm doing an OR IS NOT NULL across both of these columns, neither index is being used, and the query is extremely slow.

I'm also doing a date range check. b.start_date and b.end_date are both datetime columns, and are indexed. The query accepts inputs where both the start and end dates can be null or a specified datetime.

How can I optimize the query? I'm thinking there must be a way I can do some kind of separate sub-selects for IS NOT NULL on my_second_table.my_column_x and my_third_table.my_column_y then combine those sub-selects in order to be able to join in the way I'm trying to do in the original query, so that the indexes on b.my_column_x and b.my_column_y are used.

Biggest mistake is you are using * in Select.

  Do you require column from all three table in your resultset ?

Once you should try this,

if(@startdate is null)
set @startdate='17530101'

if(@enddate is null)
set @enddate='99991231'

SELECT * FROM dbo.my_first_table a   -- > 1 million records
INNER JOIN dbo.my_second_table b     -- > 1 million records
    on a.id = b.a_id and b.my_column_x IS NOT NULL
LEFT OUTER JOIN dbo.my_third_table c -- > 1 million records
    on a.id = c.a_id and c.my_column_y IS NOT NULL
WHERE
 b.[start_date]  > @startdate
AND     b.[end_date]    < @enddate


Connecting to an external database with pgAdmin III I'm trying to connect to an external database from pgAdmin III (which is installed on both machines).
The client complains:

  Access to database denied

  The server doesn't grant access to the database: the server reports

FATAL: no pg_hba.conf entry for host "192.168.108.161",
                                user "postgres",
                            database "postgres",
                                 SSL off

  To access a database on a PostgreSQL server, you first have to grant primary access to the server for your client (Host Based Authentication).
  PostgreSQL will check the pg_hba.conf file if a pattern that matches your client address / username / database is present and enabled before any SQL GRANT access control lists are evaluated.

  The initial settings in pg_hba.conf are quite restrictive, in order to avoid unwanted security holes caused by unreviewed but mandatory system settings.
  You'll probably want to add something like

host all all 192.168.0.0/24 md5

  This example grants MD5 encrypted password access to all databases to all users on the private network 192.168.0.0/24.
  You can use the pg_hba.conf editor that is built into pgAdmin III to edit the pg_hba.conf configuration file.

  After changing pg_hba.conf, you need to trigger a server configuration reload using pg_ctl or by stopping and restarting the server process.

Where the server explicitly states that all connections from the internal network are accepted.

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             192.168.0.0/24          md5

I have already restarted the postmaster for the changes to take effect, and have gone so far as to restart the entire machine.
What else could be going wrong here?

This line:

 host    all             all             192.168.0.0/24          md5

will let through connections from IPs matching 192.168.0.X where X is any byte.

The IP address of your error message being 192.168.108.161, it does not match this pattern because 108 is not 0.

To enable addresses like 192.168.X.Y, you'd need a /16 instead of /24 meaning that the first 16 bits only are fixed.

Like this:

 host    all             all             192.168.0.0/16          md5

Don't forget to reload Postgresql. From the official docs, use pg_ctl reload. If that doesn't work, then there are other ways to do it listed in this question.


Is replication from SQL Server 2008 to PostgreSql possible? Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.

Only ORACLE and IBM-DB2 are officially supported as subscribers. Refer here and here for more details.

Also, from sql server 2012, this is being depreciated as well.

Heterogeneous replication to non-SQL Server subscribers is deprecated. Oracle Publishing is deprecated. To move data, create solutions using change data capture and SSIS.
 Caution :
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Below is a blog post that I found that might be useful to you:  Replicating from MS SQL Server to PostgreSQL


Is a millions-row query fetched in the SQL Studio client slower than other applications? I'm not sure if want I'm thinking makes sense. I have a SQL Server database table with 30 million rows.

If I do a select A, B, C, D from huge_table --- (about 30 million--- total 4 GB footprint) --- does it take additional time for the SQL Studio Client to actual display all 30 million of these than it would an application to fetch (and process) the exact same query? (with no display requirements).

I mean -- just to fetch two columns --- a datetime and an int -- from this 30 million record table that has 8 columns total  --- well it takes 6 minutes to pull up all the records in SQL Studio. That's with no additional joins, calculations, or even every field.

Granted, it probably depends on my local client hardware (decent enough) -- and also the database is in Europe, and I'm in the United States -- though in practice I've found that maybe makes a query 1.5x slower at most.

I suppose I'm used to dealing with smaller datasets (like 2 million).

Is this simply an inherent lag in SQL studio, or does it suggest a reasonable fetch time for a similar hardware spec application?

I'm wondering how to 'speed up' this query, which by design, is a full scan. I can chunk it into smaller pieces, but the total time would be the same.

Should I aggregate or pre-calculate the 30 million? To be honest, that will not save time since with all the necessary dimensions, the 30 million would condense to maybe 25 million plus aggregate processing time - it would take longer.

Indices wouldn't help with full table fetches. Right? You're saying return these fields from the full table.

What's a reasonable time to fetch 30 million records? Select A,B,C from big_table?

does it take additional time for the SQL Studio Client to actual display all 30 million of these than it would an application to fetch (and process) the exact same query? (with no display requirements)

In short: It depends on what the application is doing with the data (aside from not displaying it), but yes SSMS will take some time to prepare and display the results. If you set SSMS to display client stats (one of the options in the default toolbars, or from the Query menu) you'll see "client processing time" as a separately counted value.

In most circumstances the fetch will be no slower from the database's point of view, because SQL Server's engine is running the same statements and sending the same resulting data over the same network, but there will be a delay on the client at the end as the results are parsed and prepared for display.

There are a few caveats: one being that if the application is not pulling the whole results down at once but instead opening a cursor and pulling rows down individually (or a block at a time) then it may impose more effective load on the server as it may end up holding locks for much longer. In this case the application will get the results slower because of the extra latency from trips to the server to request new rows. It may appear faster to the user though, because the application can start displaying information from the first rows as soon as they are available instead of waiting for the whole resultset to transfer. Getting the data in smaller blocks will increase the amount of bandwidth used if you are using a compressed VPN or tunnel to connect to the server, because the smaller segments may contain less redundancy for the compression algorithm to take advantage off.

  Should I aggregate or pre-calculate the 30 million?

We know nothing of your data, or what you are trying to do with it. What calculations? What aggregations? Without much more information we can't help there beyond wild guessing. The best thing would probably be to try it and see.

  Indices wouldn't help with full table fetches.

They might if you are requesting the data be presented in a specific order. Or if you are only requesting some of the fields ("some fields from all rows" could become an index scan instead of a table scan and require less data pages to be read).

  What's a reasonable time to fetch 30 million records? Select A,B,C from big_table?

There are too many variables for use to even guess an answer to that.

  the database is in Europe, and I'm in the United States

For remote databases using some form of link compression will help with large results, though be aware that this will increase latency for small requests.


Is it possible to compare timestamps between rows? I have a MySQL table reqs with (this is simplified from actual):

| Field                 | Type                  | Null | Key | Default | Extra |
+-----------------------+-----------------------+------+-----+---------+-------+
| time                  | datetime              | YES  |     | NULL    |       |
| user                  | varbinary(40)         | YES  |     | NULL    |       |
| foo                   | mediumint(8) unsigned | YES  |     | NULL    |       |
| bar                   | varbinary(20)         | YES  |     | NULL    |       |
| other fields          | ...

I want to find duplicate user-foo-bar combinations near one another in time — say, within 60 seconds of one another. So I want something like

select [whatever] from reqs inner join (
select user,foo,bar, count(*) as count from reqs
group by user,foo,bar having count>1
) as dups
where dups.user=reqs.user
and dups.foo = reqs.foo
and dups.bar=reqs.bar
and [something indicating the times are near each other];

— or at least that's the idea (in other words, I'm not sure whether the above is the way to structure the query, but the above is representative of the result I want from the query).

How would I do this?

Note that reqs is a huge table, so minimizing joins would be ideal.

The problem is, when you GROUP on user,foo,bar you are not able to get a list of each of those times to compare.

Something like this might work, but no idea on the performance on a large table. You might want to only do this on a subset (ie tuples just from the last 30 days)

SELECT A.*,B.time FROM reqs A
INNER JOIN (SELECT user,foo,bar,time FROM reqs) B
 USING (user,foo,bar)
WHERE A.time!=B.time AND ABS(TIMESTAMPDIFF(SECOND, TIMESTAMP(A.time), TIMESTAMP(B.time))) < 60
GROUP BY A.user,A.foo,A.bar,A.time;


How do i find out the most active database? How do i find out the most active database?

I mean to say I have 10 DBs and some transactions keep on happening on these DBs.

I want to know which is less used or which DB has less no of transactions or less active through out the day

Capture this:

Select  instance_name As Database_Name,
        cntr_value As TranSec
From    sys.dm_os_performance_counters where counter_name = 'Transactions/sec'
And     instance_name <> '_Total'
Order By instance_name

Then run it again at the end of the day. Subtract one set of values from the other. That will give you the most transactions.

You can look for a similar counter for logins/sec but it may be misleading depending on how the app connects to the database and if it keeps the connection open or not.

Another good way is by the disk IO but this can be misleading too as a lot can show up and be caused by backups/maintenance.

Select  Db_Name(mf.database_id) As Database_Name,
        Sum(a.num_of_reads) As Reads,
        Sum(a.num_of_bytes_read) As Bytes_Read,
        Sum(a.num_of_writes) As Writes,
        Sum(a.num_of_bytes_written) As Bytes_Written
From    sys.master_files mf
Cross Apply sys.dm_io_virtual_file_stats(mf.database_id, mf.file_id) a
Group By Db_Name(mf.database_id)
Order By 1

Again you'd run it in the morning and again in the afternoon and subtract to compare.


Identifying root cause of high cpu load I need to research what caused a high load in our Oracle database some days ago. I wish to see a list of  of high-load SQL statements. We don't use any Oracle tools like  ADDM or AWR.

I tried to research AWR tables. Could someone give me any clue which criteria should I put in query?

You might look at trying out Luca Canali's PerfSheet4 tool, which helps extract and visualize data from AWR tables in Excel. It comes with a video and should help you get started with some of the necessary data gathering and analysis for troubleshooting.

You probably want to start with Query: Top 3 wait events and CPU option from the sheet which according to the blog post does:

  Extracts data from dba_hist_system_event and dba_hist_sysstat. This query selects the top 3 non-idle wait events and the CPU usage for each instance. It computes delta values and rates (delta value over delta time).

You can also look at the queries in the spreadsheet, and use those as examples for your own queries into the AWR tables, should you need something more customized to your particular problem. Hope that helps. =)


variable table and column names in dynamic SQL I'm setting up a stored procedure to handle the reseeding "bug" present in SQL Server 2012. Note that as a business we have opted not to change the trace flag setting, as only a couple databases on this instance have applications that rely on sequential identity values and we'd rather not make a global change for just those DBs. I've been informed that it is too time consuming to ask developers to change their code and use programmed id values rather than relying on system generated ones, but I digress.

What I'm attempting to do is get a list of all tables and their respective columns that use Identity and the maximum identity value currently in that column, then, running through a loop, use DBCC CHECKIDENT to re-seed the table with the next highest value. The procedure will be set to run at instance startup after a failover or power failure.

At the end of all this I'd expect that the max_identity_value column would be updated, but they all remain null. In fact the @sql variable is null at the point where it is being executed. Can anybody tell me what I'm doing wrong?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
GO

declare @update_tracker int = 1, -- identify which row is being updated in the loop
        @total_rows int = 1, -- total rows in identity columns table
        @max_identity_id bigint = 0,
        @table_name nvarchar(max), -- table that has a column with IsIdentity=1
        @column_name nvarchar(max) -- column that has IsIdentity=1

declare @sql nvarchar(500)

set @sql = 'select max(' + @column_name + ') from [' + @table_name + ']'

DECLARE @IdentityColumns table
    (
    column_identifier int identity, -- Oh the irony of using identity in a script that fixes a "bug" in the identity columns of other tables
    table_name varchar(max),
    column_name varchar(max),
    max_identity_value bigint,
    is_identity bit
    )

insert into @IdentityColumns
select I.table_name, I.column_name, NULL, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity')
from INFORMATION_SCHEMA.COLUMNS I
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

set @total_rows = (select max(column_identifier) from @IdentityColumns)
set @table_name = (select table_name from @IdentityColumns where column_identifier = @update_tracker)
set @column_name = (select column_name from @IdentityColumns where column_identifier = @update_tracker)

--select @sql

while @update_tracker <= @total_rows
begin

exec (@sql)

set @update_tracker = @update_tracker + 1
set @table_name = (select table_name from @IdentityColumns where column_identifier = @update_tracker)
set @column_name = (select column_name from @IdentityColumns where column_identifier = @update_tracker)

end

select * from @IdentityColumns

Your set @sql statement includes 2 null variables so @sql is null.


sp_databaserestore: "stopat" gives error "no database backup found" Unfortunately I get:

  “No backups for “database xxx” found in “\xxx\xxx$\…”

as soon as I add the @StopAt = ‘2019-03-27T04:05:20 Parameter.

When I comment this Parameter, everything works fine, except that only the 1st logfile after the full backup gets restored – and not all log Backups in the same backup Directory.

Also tried with the date Format in the scripts example: @StopAt = 20192703040520, but I get same error. What am I doing wrong ? Name of Backup files (trn) is like this: tools_backup_2019_03_27_110112_9746376.trn

added 28032019:

First of all thanks for your answers so far !!!

SQL Server version:  2016 standard (13.0.5216.0)

@StopAt = '20190328100000', (set to correct format ? server default language is: german !

this query on same  server:

SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();

gives this result:

2019-03-28 16:10:22.8098949
2019-03-28 16:10:22.8098949 +01:00
2019-03-28 15:10:22.8098949
2019-03-28 16:10:22.807
2019-03-28 16:10:22.807
2019-03-28 15:10:22.807

My goal is to restore "point in time" to march, 28th, 10 am

exact statement:

EXEC dbo.sp_DatabaseRestore
        @Database = 'tools',
        @BackupPathFull = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @BackupPathDiff = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @BackupPathLog = '\\dicvm.de\backupsql$\SV10RXVDB24\tools\',
        @RestoreDiff = 0,
        @ContinueLogs = 1,
        @RunRecovery = 1,
        @TestRestore = 0,
        @StopAt = '20190328100000',
        @RunCheckDB = 0,
        @Debug = 1,
        @help = 0,
        @Execute = 'N';

...changing setting for @ContinueLogs, @RunRecovery or @Execute doesn't change anything, error (still):

  Nachricht 50000, Stufe 16, Status 1, Prozedur sp_DatabaseRestore,
  Zeile 486 [Batchstartzeile 0] No backups for "tools" found in
  "\dicvm.de\backupsql$\SV10RXVDB24\tools\"

files in @BackupPathFull directory are as follows (full- and log-Backups only, no diff Backups):

Verzeichnis von \\dicvm.de\backupsql$\SV10RXVDB24\tools

28.03.2019  11:01    <DIR>          .
28.03.2019  11:01    <DIR>          ..
27.03.2019  05:01            14.848 tools_backup_2019_03_27_050103_2371617.trn
27.03.2019  11:01            13.824 tools_backup_2019_03_27_110112_9746376.trn
27.03.2019  17:01             9.728 tools_backup_2019_03_27_170102_2790723.trn
27.03.2019  22:01         1.297.920 tools_backup_2019_03_27_220002_2539287.bak
27.03.2019  23:01            14.336 tools_backup_2019_03_27_230102_5656503.trn
28.03.2019  05:01            13.312 tools_backup_2019_03_28_050103_8892863.trn
28.03.2019  11:01            12.800 tools_backup_2019_03_28_110103_0664649.trn

@laughingVergil: So I assume there is a full backup  - earlier than the @StopAt parameter's time as of 27.03.2019  22:01 - in the target directory (?)
And: Yes: Your assumption is right: I am using Brent Ozar's First Responder Kit in the newest version.

I don't think the problem is with sp_DatabaseRestore but there are alternatives as you can see on this question (includes the script):

How do I create an automated SQL Server restore script for multiple backups?

Before you run your restore:

1) you must check that all your backups exist in the server

2) you must check the total disk space required by the restored-to-be-database is available on your drives

3) who is currently using the database - you will need exclusive access to it, if it already exists

4) I also save synonyms, triggers, indexes and permissions before I do the restore, so that I can re-apply these things later on if I need to.

5) questions for you:
Are you using the WITH MOVE option to ensure the database files are allocated to specific drive(s) / mount-points which have enough free space?

Also, look into the use of WITH RESTART for your restores...

The backup or restore was aborted - what could it be?


How to reduce table duplication, while ensuring integrity of the database? For example, in my app I have some class called Contact:

case class Contact(name: String, email: String, phone: String)

And several other classes have one-to-many relation to that contact (for example, there are Companies and Houses).

Currently, I have database table for Contacts set up as something like this:

CREATE TABLE `Contacts` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `entityId` bigint(20) NOT NULL,
 `entityType` tinyint(4) NOT NULL,
 `name` varchar(300),
 `phone` varchar(300),
 `email` varchar(300),
  PRIMARY KEY (`id`)
);

where entityType is storing the "code" that determines the entity type this contact belongs to (1 for Company, 2 for House, etc), and entityId points to the row in Companies or Houses table. Companies and Houses tables are defined as follows:

CREATE TABLE `Companies` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `Houses` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `address` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

So, when I need to get all contacts for some Company (which has id = 1234 in this example), I execute the following query:

select * from Contacts c where c.entityType = 1 and c.entityId = 1234;

Now, I understand that this is awful - at the very least, I can't create a foreign key on entityId, thus I can't ensure that my data is consistent.

The alternative will be to create a separate Contacts table for each entity type (CompanyContacts, HouseContacts, etc) but that will result in lots of duplication in both database and application code.

Is there a better way to do this?

I might suggest to reorganize schema, to make it more scalable.

DROP columns entityId and entityType.
CREATE an Objects (or Entities) table which will store public / shared information for all objects / entities. It's primary key will migrate to Companies and to Houses with 1:1 relation. For one object might exist only one company OR only one house. Also, you will have to remove AUTO_INCREMENT from Companies and Houses, because in this case they will no longer generate identifiers themselves.
Add foreign key to Contacts table from shared Objects table - contacts might exist for any type of object.

Structure might look like this:

You might obtain entity type by following query:

SELECT
    cnt.*,
    CASE
        WHEN NOT cmp.id IS NULL THEN 1
        WHEN NOT hs.id IS NULL THEN 2
    END as entityType
FROM
    Contacts as cnt
        INNER JOIN Objects as obj ON cnt.objectId = obj.id
        LEFT JOIN Companies as cmp ON cmp.id = obj.id
        LEFT JOIN Houses as hs ON cmp.id = hs.id

Also, note that entityId becomes objectId in this case.

Here is a CREATE-script for benchmarking and test:

CREATE TABLE IF NOT EXISTS `Objects` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `shared_property1` VARCHAR(45) NULL,
  `shared_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Companies` (
  `id` BIGINT NOT NULL,
  `company_name` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Companies_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Houses` (
  `id` BIGINT NOT NULL,
  `address` VARCHAR(300) NULL,
  `private_property1` VARCHAR(45) NULL,
  `private_property2` VARCHAR(45) NULL,
  `etc` VARCHAR(45) NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `FK_Houses_Objects`
    FOREIGN KEY (`id`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `Contacts` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `objectId` BIGINT NOT NULL,
  `name` VARCHAR(300) NULL,
  `phone` VARCHAR(300) NULL,
  `email` VARCHAR(300) NULL,

  PRIMARY KEY (`id`),

  INDEX `FK_Contacts_Objects` (`objectId` ASC),

  CONSTRAINT `FK_Contacts_Objects`
    FOREIGN KEY (`objectId`)
    REFERENCES `Objects` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


MongoDB specifying shard key and sharding strategy What is the syntax to state that a collection is to be sharded as location based, while using the range based sharding strategy. I know you have to use the the sh.shardCollection() command, but what arguments do I use in it?

As per MongoDB documentation here Ranged-based sharding involves dividing data into contiguous ranges determined by the shard key values. In this model, documents with “close” shard key values are likely to be in the same chunk or shard. This allows for efficient queries where reads target documents within a contiguous range. However, both read and write performance may decrease with poor shard key selection.

Ranged sharding is most efficient when the shard key displays the following traits:

Large Shard Key Cardinality
Low Shard Key Frequency
Non-Monotonically Changing Shard Keys

For Example

A sharded cluster using the field X as the shard key. If the values for X have a large range, low frequency, and change at a non-monotonic rate, the distribution of inserts may look similar to the following:

Use the sh.shardCollection() method, specifying the full namespace of the collection and the target index or compound index to use as the shard key.

sh.shardCollection( "database.collection", { <shard key> } )

For further your ref here and here


Redundant transpose. Case is not enough to solve my problem I have db problem in transposing rows to columns. I am half way through the result but getting redundant data. My table:

EMP_ID  EMP_NAME   SAL_PAID
01      ABC        JAN
01      ABC        FEB
01      ABC        MAR
02      PQR        JAN
02      PQR        MAR
03      XYZ        FEB

Result Table:

EMP_ID  EMP_NAME   JAN    FEB    MAR    APR
01      ABC         Y      Y      Y      N
02      PQR         Y      N      Y      N
03      XYZ         N      Y      N      N

I have used case and then the result is as below:

EMP_ID  EMP_NAME   JAN    FEB    MAR    APR
01      ABC         Y      N      N      N
01      ABC         N      Y      N      N
01      ABC         N      N      Y      N
02      PQR         Y      N      N      N
02      PQR         N      N      Y      N
02      PQR         N      N      N      N
03      XYZ         N      Y      N      N
03      XYZ         N      N      N      N

I have tried this for quite a bit now. Thank you.

Telling us that you are using a CASE statement but not showing us the actual code that you're using doesn't help us much.  Assuming that "Result Table" is the desired output and that you want to use the old-style MAX(CASE syntax to do your pivot rather than using the newer PIVOT operator

SELECT emp_id,
       emp_name,
       nvl( max( case when sal_paid = 'JAN' then'Y' else NULL end ), 'N' ) jan,
       nvl( max( case when sal_paid = 'FEB' then'Y' else NULL end ), 'N' ) feb,
       nvl( max( case when sal_paid = 'MAR' then'Y' else NULL end ), 'N' ) mar,
       nvl( max( case when sal_paid = 'APR' then'Y' else NULL end ), 'N' ) apr
  FROM your_table
 GROUP BY emp_id, emp_name


In Extended Events what is the difference between rpc_completed and sql_batch_completed? I understand that rpc_completed is defined as the completion of a remote procedure call and sql_batch_completed is the completion of a t-sql batch.  However can anyone explain in concrete terms what the difference is?  When should I use one and when should I use the other?  Will most/all SQL commands trigger both events?

The difference is how the command is executed by the client. The client can directly execute a stored procedure or the client can send a sql batch.

To call a stored procedure from .NET you would use

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;

and to execute a batch you would use

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.Text;

There are other things you need to set up like the connection, but the above shows the difference between the two execution modes.


Postgres inherit from one role and not another I'm a little new with postgres but have a decent understanding of roles. What I have are users that inherit from their normal "data_analyst" group. This group is used to manage all data analyst users with basic permissions. But I have a few users that will have elevated privileges. My initial thought was to create a new group for the elevated users and when they need their elevated privileges they would run SET ROLE elevated_role. They want to have to explicitly call those privileges.

They would essentially be a part of two roles, their limited role that they are granted and inherit from and the privileged role they have to set The business and users don't want their elevated privileges to be inherited to their main role. But this is problem based on my understanding of roles. If the user has been granted inherit they wouldn't need to SET their role because they would already have inherited it. How could I set this up? Is there another way I'm not thinking of?

I solved this by nesting the two roles together.

    CREATE role elevated_role nologin noinherit;

    GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA db to elevated_role;
    GRANT usage ON SCHEMA db to elevated_role;

    CREATE role normal_role nologin noinherit;
    GRANT usage ON SCHEMA db to normal_role;
    GRANT select on ALL tables in SCHEMA db to normal_role;

    GRANT elevated_role TO normal_role;

    CREATE new_user with password 'some_pass';
    GRANT normal_role TO new_user;

This allows me to manage the users in a group by just adding and deleting them from the group but gives them separate roles that they have to explicitly set if they want to insert, delete, etc.


SQL query output to a file is failing with error code 9 when the data contains French and English characters with size > 32k Database: Oracle 12 C

I have a MESSAGE table with BODY VARCHAR2(32000), METADATA VARCHAR2(32000).

Initially I had a problem when both columns have 32k+32k chars of data. I had fixed it by applying the TO_CLOB function and I am able to write the output to the file without truncating the result. (This is working only when

All are English characters(Eg: SQL query result record contains around 64 K characters)
Combination of French & English characters when the SQL query result record < 32K characters)

But when the BODY part is having French & English characters with expected SQL query result record > 32K characters, In this case, my query execution is failing with error code : 9.

Please help me on how to fix this issue without truncating the data.
Below is my Unix script code

export PATH

export LD_LIBRARY_PATH

export ORACLE_ACCESS

#export NLS_LANG=.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

output=$(sqlplus -S "$ORACLE_ACCESS" << EOF
    WHENEVER OSERROR EXIT 9;

    whenever sqlerror exit sql.sqlcode;

    set NEWPAGE none

    set pagesize 0

    set trimspool on

    set trimout on

    SET LINESIZE 32767

    SET LONG 500000000

    SET LONGCHUNKSIZE 100000000

    set echo off

    set heading off

    set feedback off

    set term off

    set recsep off

    set serveroutput on size unlimited

    SPOOL $FILE_NM APPEND

    select '02~^'||TO_CLOB(EVENT_ID)||'~^'||BODY||'~^'||META_DATA||'~^'||'|^#' from MESSAGE where ((CREATE_TS >= TO_DATE('01-Jan-2025','DD-MON-YYYY') and CREATE_TS < TO_DATE('01-Jan-2025','DD-MON-YYYY')+1) ORDER BY ROWID;

    SPOOL OFF

    exit;

EOF

)

I removed this line and its working fine now. It is writing all kind of characters to the file.(English,French,Chinese)

WHENEVER OSERROR EXIT 9;


Recommended hardware configuration Analysis and Reporting Services What would be a good way to calculate the recommended minimum hardware configuration for a Windows 2008 R2 64bit that will be running SQL Server 2008 R2 Analysis and Reporting Services?  The database server is on another machine.

The current configuration is :

Virtual Machine running on a vmware7 host

2 vCPUs
4GB of RAM
80GB of Hard Disk space.

4GB of RAM appears to be insufficent.

No one has the answer to this question?  Perhaps I need to provide more information?  Is there a white paper on minimum requirement for SSAS and SSRS?

What started this question was essentially the customer saying, hay the RAM on my machine is all used up.  I checked out the processes and noticed owstimer.exe.  After a quick search I found that owstimer.exe is a sharepoint component.  I say that with some doubt because I have not verified that information.

So I have a Windows 2008 R2 64bit, 4Gb of Ram, 4vCPUs on a machine with SSAS and SSRS.  Among the processes I find one called owstimer.exe which might belong to sharepoint.  I read somewhere that sharepoint should be installed on a system with a minimum of 8Gb of RAM.

http://technet.microsoft.com/en-us/library/cc262485.aspx

But sharepoint isn't on this machine just SSAS and SSRS.

The databases for this client are stored on a dedicated SQL Server 2008 R2.

More information.  I can at limit SSRS and SSAS via their configuration files.

For SSRS

Control SSRS according to memory pressure

http://msdn.microsoft.com/en-us/library/ms159206.aspx

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config.

MemorySafetyMargin 80 /MemorySafetyMargin -- % of memory reserved for
the system

MemoryThreshold 90 /MemoryThreshold --  If Report server memory use
reaches this value, the report server slows down request processing
and changes the amount of memory allocated to different server
applications

Min and Max Memory control in SSRS

WorkingSetMaximum 2097152 WorkingSetMaximum>
WorkingSetMinimum 1048576 WorkingSetMinimum>

So we're going to try this out on SSRS and see how it goes.

Intuitively, if I was doing an OLAP solution for a retail chain, I'd say your infrastructure is really inappropriate for a system with substantial data volumes.  This sort of kit has trouble with the data volumes you get in insurance, which is probably a couple of orders of magnitude smaller than I would expect to see in retail.

As gbn states in the comments, SSRS is just a web application.  You can set up a farm - start with one server with a few GB of RAM and a couple of virtual CPUs.  Monitor it, and expand it if it's overloaded.

The amount of disk space used by SSAS depends on the volume and the aggregations you put on the cubes.  The storage format is quite compact - more so than SQL Server, but if you have large volumes of data it will start to get quite big.  If it's getting into the 100+GB range then you should also look at partitioning.

A surprisingly applicable generic solution

Now, your client probably doesn't want to hear this, but VMs are not my recommended hardware configuration for any business intelligence solution.  They work OK for transactional applications or anything that is not too computationally intensive.  BI for a retail chain is probably a bit aggressive for this sort of infrastrucutre.

As a generic 'starter for 10', My recommended configuration is a bare metal 2-4 socket server like a HP DL380 or DL580, and direct attach SAS storage.  The principal reason for this is that a machine of this sort is by far the best bang for buck as a B.I. platform and has a relatively modest entry price.  If you put a HBA on the machine then you can mount a LUN off the SAN for backups.

IOPS for IOPS, this sort of kit is an order of magnitude cheaper than any SAN-based virutal solution, particularly on sequential workloads like B.I.  The entry level for a setup of this configuration is peanuts - maybe £10-20,000 - and it's a lot cheaper and easier to get performance out of something like this than a VM based solution.  For a first approximation, the only situation where this kit is inappropriate for B.I. work is when the data volumes get too large for it, and you need something like Teradata or Netezza.

What can you do with your VMs, though?

A good start would be more RAM - try 32GB.  SSAS is a biblical memory hog, and you've got slow infrastructure.  If you're stuck with a VM, put as much RAM on it as possible.  Disk access is slow on VMs.
A second avenue is to partition the cube over multiple LUNs, where the LUNs are on separate physical arrays.  Not all SANs will give you this much control, though.  80GB is getting into the range where you might get a benefit from parititioning, particularly if you've got a platform with slow I/O.
Tune the buggery out of your cube aggregations - try usage based optimisation.  The more hits you can get from aggregations the more efficient the server will be.

Without measuring your workload, I doubt anyone here is in a position to make recommendations that are any more specific than that.  Although generic, the pointers above should be a reasonable start if you haven't implemented them yet.


GROUP BY on a dependent field Let's say I have a SELECT statement as follows:

SELECT ..., field1
  FROM ...
 GROUP BY field1

Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent on field1 (for example, field1 and field2 could be from the same table and field1 is the primary key of that table). I now have two equivalent ways to add field2 to the query:

Option 1:

SELECT ..., field1, field2
  FROM ...
 GROUP BY field1, field2

Option 2:

SELECT ..., field1, MIN(field2)  /* or some other, arbitrary aggregate function */
  FROM ...
 GROUP BY field1

Again, since field2 functionally depends on field1, both queries should return the same result set.

Are there any good reasons to prefer one option over the other?

The answer can be platform-specific. Although the results are the same, your performance might be very different.

I would suggest another approach, because it seems to clearly document the following intent: calculate some aggregates, then decorate them with another field(s). Here is an example:

WITH CustomerTotals AS(
SELECT CustomerID,
SUM(Amount) AS TotalAmount
FROM Orders
GROUP BY CustomerID
)
SELECT Customers.Name, CustomerTotals.TotalAmount
FROM  CustomerTotals JOIN Customers
  ON CustomerTotals.CustomerID = Customers.CustomerID


Clustered Index fragmentation vs Index with Included columns fragmentation I have a relative large table (for me)  with 40 million rows that is expected to grow to 80 to 120 million rows in a period of two weeks (during a campaign).
Tip
--------------
Id          int (clustered index)
UserId      int
TipIndex    smallint
Value       binary(8)
LastChanged datetime2(3)

every user has between 1 to 400 tips that will be inserted at random during this period
UserId + TipIndex is unique
I never query Tips directly on the Identity Key (Id)
99% of the times I query on UserId
I need all columns
I often query on 1 UserId (per page view), sometimes on a batches of 10.000s for statistics
this is a high traffic site during this period and it should be able to handle 30.000 queries on UserId per minute
Id is at the moment my Clustered Index, because I read that it leads to the least fragmentation.

So I'm hosting on SQL Azure and Azure already recommended adding an Index with included columns.
I was always hesitant on using UserId,TipIndex as a Clustered Index, as Tips will be added at random. Meaning that I'm afraid of huge fragmentation problems etc.
My questions:

Doesn't an Index with included columns have the exact same problem?
Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
How to prevent fragmentation?

I know in the end the answer is always "depends" or that I should measure it. But as I'm a solo developer and not with a lot of resources I am hoping on someone with more experience that has a gut feeling for this, so my first attempts have a higher chance of going in the right direction.

Your questions:
Doesn't an Index with included columns have the exact same problem?
Yes.
Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
Yes
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
I would, yes.
How to prevent fragmentation?
There are a couple of different types of fragmentation to consider. One is when you only have part of your pages being used because you’ve had page splits. If you have a lot of inserts, this will happen. Don’t stress too much. The other is when you have pages where the subsequent page is in a different extent. Again, I wouldn’t worry too much. If your data is mostly in the buffer cache, it doesn’t really matter if it moves across extents.
So... don’t worry about it too much. But don’t bother having a complete copy of the data in a way that you won’t actually be querying it.


Accessing a network share via a SQL Server 2012 Availability Group If you have a SQL Server Cluster called CLS, with machines CLS1 and CLS2, accessing:

\\CLS\network\path

is equivalent to accessing either

\\CLS1\network\path

or

\\CLS2\network\path

depending on whether CLS1 or CLS2 is the currently active instance.

The same behavior isn't occurring on a SQL Server 2012 Availability Group I've set up to try out AGs. I have three machines called PRI, SEC1 and SEC2 in an availability group called AG. I can access the databases via AG, and can remote desktop AG, but cannot access network shared via AG.

Are you supposed to be able to, by default, access networks shares via the AG?
If not, can this behavior be enabled?

Edit: I am primarily interested in the administrative shares (c$, d$ etc.)

CLS is not an alias for either CLS1 or CLS2. CLS is a cluster, not a real machine. You cannot access \\CLS\c$ simply because no such thing exists. There is only \\CLS1\C$ and \\CLS2\C$ and you can access either of them. You should figure out which one is the one you're interested and access that.


how to script out proxy accounts? I have servers where I use proxy accounts to run ETL processes or even to backup SSAS databases using powershell through a job.

I can see what AD Accounts are related to my proxies:

use msdb
go

select
    s.job_id
    ,s.name as [job_name]
    ,js.step_id
    ,js.step_name
    ,js.subsystem
    ,js.proxy_id
    ,js.command
    , c.credential_identity
from sysjobs S
INNER JOIN sysjobsteps JS ON S.job_id = js.job_id
INNER JOIN sysproxies p ON js.proxy_id = p.proxy_id
INNER JOIN sys.credentials as c on c.credential_id = p.credential_id

I can script out roles and logins and even table types but now I need to script out my proxies.

QUESTION:
How to script out proxies?

I Want to script out the following:

I have got this so far:

IF OBJECT_ID('TEMPDB..#tmp_sp_help_proxy','U') IS NOT NULL
   DROP TABLE #tmp_sp_help_proxy

create table #tmp_sp_help_proxy(proxy_id int null,
                               name nvarchar(128) null,
                               credential_identity nvarchar(128) null,
                               enabled tinyint null,
                               description nvarchar(1024) null,
                               user_sid varbinary(40) null,
                               credential_id int null,
                               credential_identity_exists int null)

insert into #tmp_sp_help_proxy(proxy_id, name, credential_identity, enabled, description, user_sid, credential_id, credential_identity_exists)
exec msdb.dbo.sp_help_proxy

SELECT
tshp.name AS [Name],
tshp.proxy_id AS [ID],
CAST(tshp.enabled AS bit) AS [IsEnabled],
ISNULL(tshp.description,N'''') AS [Description],
tshp.credential_id AS [CredentialID],
ISNULL(sc.name,N'''') AS [CredentialName],
ISNULL(tshp.credential_identity,N'''') AS [CredentialIdentity]
FROM
#tmp_sp_help_proxy AS tshp
INNER JOIN sys.credentials AS sc ON sc.credential_id = tshp.credential_id

DBATools has a convenient command Copy-DbaAgentProxy in its PoSh module that I highly recommend.

By default, all proxy accounts are copied. The -ProxyAccounts parameter is auto-populated for command-line completion and can be used to copy only specific proxy accounts.
If the associated credential for the account does not exist on the destination, it will be skipped. If the proxy account already exists on the destination, it will be skipped unless -Force is used.

Or, to generate the files, you can also use Get-DbaAgentProxy and Export-DbaScript.


What are the differences between Cognos and SPSS modeler products? My school wants to start doing some data mining, and probably go with an IBM product. I've done a fair amount of searching, but the differences between the two products seem cloudy to those of us who haven't had experience with data mining. Can anyone offer some help, or direction, as to what the differences in these products are? Although the main goal is helping with retention and graduation rates, BI features in general are also of interest to the school.

The differences between Cognos and Modeler are very large.  Cognos is mainly a summary and reporting tool.  It does not do any statistical analysis to speak of (except when you use the part of SPSS Statistics that is optionally integrated into one of the Studios).  Modeler is a full data mining tool with many modeling capabilities.  Modeler can read and write Cognos data.


Why can't I create a database in a 107GB dbspace I created for my instance? 11.70.FC6 Developer Edition on Windows 7 Pro 64-bit:

Downloaded via IIUG link and installed it.
I created a custom DSS instance named "server" with a 128GB dbspace named "datadbs".
Install wizard created a Windows user "informix".
I connected to instance "server", logging in as "informix".
Executed an SQL script "CREATE DATABASE voters IN datadbs NLSCASE INSENSITIVE;"
This failed (see first image), then tried creating the db via dbaccess menu.
In dbaccess, dbspaces options did not show "datadbs", only root, phys, log & sbs dbs',
but when I do a dir of the dbspaces it shows "datadbs" with 107GB (see second image).
I set the following values in "onconfig.server" (see third image), but still no dice!

How did you create the dbspace ? I'm a bit astonished that you showed up a directory listing instead of the output from onstat -d - just creating a file does not do the job.
Run onstat -d and see if there is a dbspace called "datadbs". If not, cd to the directory where the dbspace should reside, create a suitable file (i. e. echo gaga >datadbs content does NOT matter) and create the dbspace using "onspaces". This should look similar to

onspaces -c -d datadbs -p c:\dbspaces\datadbs -o 0 -s 128000000

For more options enter onspaces -?

Hope this will help


How do I properly design a many-to-many (charges/payments) accounting system? This is just as much a db/programming question as an accounting/math question, so I'm actually posting to all relevant boards.  I appreciate your patience in advance.

Basically I'm having trouble wrapping my head around a payments ledger.  My system has three tables:  CHARGES, PAYMENTS and a PAYMENTS_TO_CHARGES.  The issue is that part of the system requirement calls for both the ability to directly associate a payment with a CHARGE as well as allow members to post overpayments (or credits) to their account.  So there's the need to track credits then apply them as needed to future CHARGES.

Perhaps I'm thinking about things from the wrong standpoint, but I'm picturing CHARGES as a DEBIT and PAYMENTS as a CREDIT.  However, it would appear that they can both have DEBIT/CREDIT depending on your accounting approach.

So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc.  I'm trying to work out the DB tables and business logic but every time I solve one problem, a reverse (or converse) item presents itself.  Do I allow the many-to-many relationship to track positive and negative numbers per transaction?  Do I create a fake universal charge that all credits apply to?  Do I create a completely separate CREDITS table?  As you can tell, I'm likely overthinking this, but I'm certainly befuddled.

If all transactions were guaranteed 1-to-1, it would obviously be much simpler.  Unfortunately they're not

See also:

https://softwareengineering.stackexchange.com/questions/72178/how-do-i-properly-design-a-many-to-many-charges-payments-accounting-system

and

https://stackoverflow.com/questions/5543025/need-to-reconcile-accounts-on-a-poorly-designed-system

So, my question -- officially -- is what is the best way to go about tracking/reconciling payments, and charges, while still allowing for overpayments, etc.

This is the problem. There are several styles of accounting. Are you using double-entry? I would recommend learning double-entry accounting. It's complicated but it works.

Is this for an actual assignment or for learning?

Here's a system that may be easy enough to dig into to give you the concepts you need to move forward? I know the author (by way of this podcast but not personally) but I haven't looked at the codebase. I just know that it does DE accounting.

http://twit.tv/floss150


How to know there is a maintenance job running on a postgresql database I have a python script on postgresql database which is fetching records from sql server database and inserting it to postgresql DB after some filtering. this jobs is running by cron every two minutes. I want to know when there is maintenance job such as autovacuum is running and if running forget fetching records. Is it possible to detect in python?

In current versions of PostgreSQL, you can look at the *pg_stat_activity* view to find autovacuum tasks.  They will have *current_query* fields that start with "Mark autovacuum entries in pg_stat_activity that look like this:

autovacuum: VACUUM t

A query to count how many of those you have might look like this:

SELECT count(*) FROM pg_stat_activity WHERE position('autovacuum:' IN current_query) = 1;

And you can have your Python code check if the value returned is >0; if so, autovacuum is running.  You might want to track down and avoid running concurrently with manual VACUUM calls, too.


SQL Server 2008R2 database migration to cloud: case-insensitive collation changed to case-sensitive Recently felt the fall out (crappy legacy code base) of a database migration by a third party vendor where the collation was swapped from case-insensitive to case-sensitive. I am wondering if the collation was deliberately changed during the migration? I would have thought that in any database migration the collation would remain unchanged (i.e. stay the same as the source database collation). Does the server which the database is hosted automatically override the collation maybe?

There is a lot of information missing from the question, such as:

What does "cloud" mean, exactly?

Microsoft Azure SQL Database
Microsoft Azure SQL Database Managed Instance
Microsoft Azure VM with SQL Server installed
Amazon (AWS) RDS
Amazon (AWS) VM with SQL Server installed?
something else

What is meant by "migration"?

Database was detached / attached, or backed-up / restored
Objects and data were moved via scripting out and applying at the destination, or directly ETLed
Data Migration Assistant
SSDT and BACPAC file
Azure Database Migration Service

What is meant by "database"?

An actual Database that resides in an Instance of SQL Server
An Instance of SQL Server that contains Databases

How does "crappy legacy code base" factor into this Collation issue? Or is it just venting / kvetching?

Generally speaking, there is no obvious / default answer since there is no single mechanism / approach for migrating data, nor is there a singular definition of "cloud" in this context. I would expect that some options (such as restoring from a .bak backup file) would keep the Database's default Collation intact, but that does not imply anything about the Instance-level Collation.

What this comes down to is: even without having more detailed information to go on, since there is a Collation difference at some level (Instance and/or Database), it would seem that the vendor that performed the migration was not careful to ensure that either a) the exact same Collation was being used if given a choice to set a new Collation, or b) that you were notified that there would be a change of Collation at the specific level(s) if there was no choice and it was going to be different than the current Collation(s).

I would definitely contact the vendor regarding this issue. Typically, if there is a default Collation that cannot be changed, it is only the Instance-level, and is case-insensitive:

Azure SQL Database Managed Instances use: SQL_Latin1_General_CP1_CI_AS (unfortunately)
Partially-Contained Databases use: Latin1_General_100_CI_AS_WS_KS_SC
SQL Server Express Local DB uses: SQL_Latin1_General_CP1_CI_AS (unfortunately)

References:

SQL Server database migration to Azure SQL Database
Migrate your SQL Server database to Azure SQL Database using DMA
SQL Server instance migration to Azure SQL Database Managed Instance


Get normalized representation of SET datatype In MySQL you can SELECT SET values from a table using this syntax:

CREATE TABLE `test_set` (
  `int` int(11) DEFAULT NULL,
  `set_2` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SELECT * FROM test_set WHERE set_2 & 1;

or equivalent using = operator.

For a given table:

int | set_2
-----------
12  | a,b
-----------
34  | c,b

I'd like to make a query that would return:

int | set_2
-----------
12  | a
-----------
12  | b
-----------
34  | c
-----------
34  | b

How would such query look in mysql/mariadb? According to the book I am reading, it should be something in the lines of:

SELECT T.int, S as set_value
FROM test_set T, T.set_2 as S

Similarly, set_gen() function can be applied to reverse normalized representation to the one containing sets:

SELECT T.int, set_gen(T.set_2)
FROM test_set T
GROUP BY T.int

What is the syntax for denormalization in mysql/mariadb?

SELECT DISTINCT `int`, SUBSTRING_INDEX(SUBSTRING_INDEX(`set_2`, ',', `mask`), ',', -1) `set`
FROM `test_set`,
      -- generate masks based on SET values list count
     (SELECT 1 `mask` UNION
      SELECT 2 UNION
      SELECT 3) `mask`
WHERE `set_2` & 1;

fiddle

If server version allows build mask in recursive CTE (dynamic count may be used)


How to speed up rebuilding a database from scratch/scripts Our Database exists in many different scripts within our source control. i.E. every table has it's own scripts for creating the table, the indexes and so on.
When needed, we can rebuild the entire dev-database by starting a powershell script which will run through every script by feeding it to sqlcmd.exe.

This process recently took about 30 to 40 minutes. We discovered that we were using the old 2008R2 Version of sqlcmd, thanks to a mistake in the local path variables, which pointed to the old version.
After fixing this the entire process was reduced to about 8 minutes.

I am now wondering if there are any "switches" I might have overlooked which could speed up this process even more.

The Database is currently set to compat level 100 (for stupid reasons), multi_user mode and simple recovery model. The database runs locally and no user will be connecting during the rebuild process.

I did try setting it to single_user mode but ran into an issue. Apperently the sequential process of firering one sql-script after another seems to be too quick(?) for it's own good. Somehow one connection is not closed fast enough before the next one is established.

Are there any other options either on query or database level that could speed up a database rebuild process in this scenario?

Applying schema scrips ends up being a lot of small transactions, and that can cause lots of log file waits.  You can try running the scripts in a transaction, but not all changes are compatible with transactions.

So an easy way make the scripts never wait on log flushes is to to turn on Delayed Durability while running the scripts.

EG

ALTER DATABASE current SET DELAYED_DURABILITY = disabled;

go

declare @i int = 0
while @i < 10000
begin

    exec ('
    drop table if exists mt;
    create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
    ')
    set @i += 1;

end

go

ALTER DATABASE current SET DELAYED_DURABILITY = forced;

go

declare @i int = 0
while @i < 10000
begin

    exec ('
    drop table if exists mt;
    create table mt(id int, a int, b datetime, c char(2), d uniqueidentifier);
    ')
    set @i += 1;

end


Upgrade Single Config Server to Replica Set Recently we wanted to upgrade our Config Server to Replica Set. Since it is a development environment, we only have one Config Server running.

However, the official mongoDB documentation "Upgrade Config Servers to Replica Set" is written for three Config Servers.

How can we upgrade a configuration with only one Config Server?

Add a the following line in config file

replication:
   replSetName: csReplSet

restart your instance
login and use admin
initiate to replica
add members
configure priority
check your replica cluster.


Performing frequent backups of a large MySQL slave We replicate our production MySQL databases (120 GB on production) to a dedicated local desktop machine.  The local slave is a 2014-era machine with 8 GB of RAM.

A full mysqldump of the databases on the slave takes just under 48 hours.  The resulting dumps, after zipping, are 5.4 GB.  This process yields usable dumps, but the duration of the dump process prevents us from doing daily dumps of the database.

At most, it seems that we can only expect to achieve a full dump every other day with our current setup.  Something needs to change in our approach in order to facilitate daily dumps again.

One possibility is simply adding another slave and having the two slaves alternate days of dumping.

A different approach (which I loathe, but could technically work) would be to do a full dump every, say, week, and then also keep all of the binary logs in case more fine-grained data is needed.  (We could restore the weekly dump and then execute the binary logs up until a certain timestamp)

I suppose a totally different approach would be to use a RAID array and hot-swap out drives to effectively get a "snapshot" of the slave at the time the drive was pulled.  (This seems like it may work but also feels quite risky and like a gross abuse of what RAID is supposed to do)

Another completely different approach would be to simply "prioritize" certain tables/databases that are dumped every day, while other "less important" tables/databases are dumped every week.

What am I missing?  Is there a "canonical" solution for archiving daily snapshots of "large" MySQL slaves?  This seems like something that would be encountered quite frequently by those maintaining larger databases.

Is the best option really to just continue throwing more hardware at the problem?

With 120GB, there are two ways to get a daily dump.  (Or even hourly.)

With Replication, a Slave machine can be always tracking what is on the Master.  That is, it is an up-to-the-minute dump of the Master, even without pushing the "dump now" button.

With LVM (available on some OSs), you can take a full disk "snapshot" 'instantly', then at your leisure copy the dump to somewhere else.

Perhaps the faster way to use mysqldump with multiple machines is to use

mysqldump ... | gzip | something-to-copy-to-other-machine

By zipping the file before hitting the network, you 'minimize' the bandwidth needed.

See also Percona's xtrabackup for other techniques -- especially full versus incremental backups.


Is there a way to make pg_dump ignore errors instead of stopping? I'm running a pg_dump of my postgres 9.4 DB and occassionally it'll hit an error if some other process has an exclusive lock on a table.  Is there a way for the pg_dump to just ignore that table and proceed with the rest of the database instead of just stopping in its tracks?

This functionality is missing from pg_dump and the right solution would be to enhance it by adding a flag with something like --ignore-errors, the comments miss the point that backing up some tables is better than none.
In a scenario where you only want to restore some tables this would be helpful.

There are few options using which you can implement a workaround:

Use the parameter below to reduce timeout to 10 sec, log the table and dynamically exclude that table from a new pg_dump command.
--lock-wait-timeout=timeout
--exclude-table=table
If you have a standby DB, use that to do your backups and you will have lesser chance of this issue happening.
If the issue is due to some unlogged stage tables, you can use the parameter below to ignore them ( I have not tested this so not 100% sure)
--no-unlogged-table-data
Write a wrapper script and backup each schema or table, this way if the failure happens you will miss one schema or table, you will have to make sure to implement a similar wrapper for restore as doing it by hand would be problematic, also this would not be consistent at a DB level.


SQL Server 2012 Data Warehousing Overall Flow I am studying Data Warehousing in SQL Server 2012

I am attempting to learn the various tools included by practicing on the different tools separately.

I have practiced with:

AdventureWorks samples using Integration Services.
Analysis Services for creating cubes, mining structures and data quality services.
Creating Domains and Knowledgebases with Integration services.

Can anyone give me some clear directions on how to start my first Data Warehousing project?  Which tools do I use, and in what order?

Not 100% what you are getting at, whether you're talking about the order of developing a data warehouse or how the data flows into the warehouse and on to other things.

There is a camp that models all the data into the data warehouse (using dimensional modeling techniques, and only generally informed by the users roles and domain expertise) and then the end users build their analytics on it.

There is another camp that gathers end-user requirements and then builds the warehouse to meet that, identifying the data in the source system.

I tend to be more in the first camp, because users only know what they want once they play with the data and see what's available.


Need to change alphanumeric characters to next character I have 2 columns Name, Passport No. in a table with more than 100k rows.

Name   Passport No.
KOP    A245
COS    C990

After the update it should look like this.

Name   Passport No.
LPQ    B356
DPT    D001

Update current character with the next character in the database. I don't want to select anything from table, I want to update the table.

Please don't ask me why.

Use a numbers table to split your string and a case statement to figure out what the values your characters should have and for xml path to rebuild the string.

Put the logic in a function so it can be reused for multiple columns.

create function dbo.SS(@S varchar(max))
returns table as return
(
  select
  (
    select case
            when S.C = '9' then '0'
            when S.C = 'Z' then 'A'
            when S.C like '[A-Z,0-9]' then char(ascii(S.C) + 1)
            else S.C -- Keep not A-Z,0-9 as is
          end
    from Number as N
      cross apply (select substring(@S, N.N, 1)) as S(C )
    where N.N between 1 and len(@S)
    for xml path(''), type
  ).value('text()[1]', 'varchar(max)') as C
)

This would be your update statement.

update YourTable
set Name = (select C from dbo.CC(Name)),
    [Passport No.] = (select C from dbo.CC([Passport No.]))


In InnoDB, how does fuzzy checkpointing's recovery consistency work? I've looked the documentation quite thoroughly but still cannot figure out so asking this question.

In InnoDB, I understand that any updates against buffer pool are tracked by redo log, which gets persisted on the disk, and the redo logs are used for recovery in the case of crash.

Occasionally, the MySQL server also flushes the dirty pages of the Buffer Pool.  Under its normal operation, MySQL server only flushed some part of dirty pages, and they call it "fuzzy checkpointing".  Under this procedure, the current Buffer Pool is claimed to be recoverable by reading the content of pages on disk and then applying all the redo logs whose LSN is greater than the last checkpoint.

My question is, how does the MySQL server chooses which dirty pages to flush, and also supports the crash-recoverability?

From some googling, I understood that by utilizing the dirty page's first modification LNS number, one can know which pages should be flushed so that   the checkpoint LNS can be incremented.

But the dirty page with earliest uncheckpointed redo log can also have been modified by the latest transaction and thus have future content compared to the earliest uncheckpointed redo log.  I assume it is very difficult (if possible) to redo from the disk persisted buffer pool pages if those pages includes such future contents.

So question:

How does the MySQL server chooses which pages to flush, and also support crash recovery?

(I'm pretty sure of the following.)

InnoDB does not depend on "dirty" pages for recovery.  Recovery is guaranteed by what is stored in iblog* and the double-write buffer.

The presumption is that the information about a transaction can be more compactly stored, and more rapidly written to disk, in the redo log (versus the actual table).

The log files are overwritten, but not until LNS says it is OK.  So, the optimal dirty page to flush is either the "least recently used" or the one with the oldest position in the log.  I don't know what algorithm it uses to decide between these conflicting things.

If there is a lot of activity causing the percentage of the buffer_pool to be 'too close' to 100% dirty, InnoDB shifts gears and becomes more aggressive at flushing dirty pages.  This, also, is a tradeoff.

Note also that the writing of changes to non-unique secondary indexes is also 'delayed'.  This is in the "change buffer", which (by default) occupies 25% of the buffer_pool.  The hope with that is that the updates can be somewhat sorted and written to disk with fewer read-modify-write cycles.  Again, recovery does not depend on this having been completely flushed to disk, and the redo log is the critical part.

The double-write buffer protects against "torn-pages".  This is a potentially disastrous situation where the disk subsystem write can't write all 16KB in an atomic operation.  A few newer disks guarantee atomicity, so the setting can be turned off.

InnoDB is crash-proof.  But, it is also "fast" because of delaying I/O, together with these various techniques that work efficiently under high load.


Preventing orphaned users in SQL Server 2008 R2 I've recently had a situation where a database was refreshed from production to staging, and the users on the database ended up orphaned.

These are sql server users, not mapped to windows credentials.
The database users use the same credentials on both PRD and STG.
The database users are called the same on both PRD and STG.
The SQL Server logins are called the same on both PRD and STG.
The database was refreshed by using a RESTORE with REPLACE and MOVE to rename the data files.

As I understand it, this is because the SID's of the logins don't match between our two environments.

How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward?

My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' but this has the disadvantages of needing to drop the logins first, and I'm not entirely sure that manually defining an SID is healthy / wise.

Maybe there is a way to have the logins migrated with the backup? (if sp_help_revlogin has been available to manually copy logins, why don't backups have this option)

As I understand it, this is because the SID's of the logins don't
  match between our two environments.

You understand correctly!

  How could I make it so that the SID's of the sql server logins on our
  staging environment are the same as on our production environment
  going forward? My thought is to use CREATE LOGIN WITH SID ='SID value
  from PRD' but this has the disadvantages of needing to drop the logins
  first, and I'm not entirely sure that manually defining an SID is
  healthy / wise.

If the logins are already created, you'll have to drop them anyway - so that's a moot point. You can use the SSIS migrate logins task, sp_helprevlogin, CREATE LOGIN WITH SID =, and lastly you can make the database partially contained.

  Maybe there is a way to have the logins migrated with the backup? (if
  sp_help_revlogin has been available to manually copy logins, why don't
  backups have this option)

Logins are a server scoped principal and can have a 1 to many relationship with users. Thus it doesn't make sense to include a server scope item with a database scoped item, that's why users exist. What you're looking for is called partially contained databases, which let a special type of user exist which has a passed and can be used like a login though it lives in the database and not at the server level. It's only good for that database and there are some requirements.

https://msdn.microsoft.com/en-us/library/ff929071.aspx


Regarding create index and lock word in SQL statement used for SQL Server This way someone creates an index.

CREATE TABLE [dbo].[QueueSlow](
[QueueID] [int] IDENTITY(1,1) NOT NULL,
[QueueDateTime] [datetime] NOT NULL,
[Title] [nvarchar](255) NOT NULL,
[Status] [int] NOT NULL,
[TextData] [nvarchar](max) NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_QueueSlow] ON [dbo].[QueueSlow]
(
[QueueID] ASC
)
GO
CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE ( [Title])
GO

I have question that when creating non-clustered index like

CREATE NONCLUSTERED INDEX [IX_QuerySlow] ON [dbo].[QueueSlow]
(
[QueueDateTime] ASC,
[Status] ASC
)
INCLUDE ( [Title])
GO

so what is the meaning of INCLUDE([Title]) - does it mean a non-clustered index will be created on Title column?

Here is a SQL statement with lock keyword.

declare @Batch table (QueueID int, QueueDateTime datetime, _
Title nvarchar(255), TextData nvarchar(max) )

insert into @Batch
select Top (@BatchSize) QueueID, QueueDateTime, Title, TextData from QueueSlow
WITH (UPDLOCK, HOLDLOCK)
where Status = 0
order by QueueDateTime ASC

When inserting data into a table variable then why would anyone use lock like

WITH (UPDLOCK, HOLDLOCK)

What is the meaning of WITH (UPDLOCK, HOLDLOCK) ??

Is this statement WITH (UPDLOCK, HOLDLOCK) compatible with SQL Server 2000/2005 ?

Looking for a discussion. thanks

Three questions in one ....

OK, here are some answers:

The INCLUDE(Title) option is new in SQL Server 2005. It does NOT create an index on that column - the index is created on (QueueDateTime, Status) only. But in the leaf level of that non-clustered index, the Title column's value is included - so if you have a query something like:

SELECT QueueDateTime, Title
FROM dbo.YourTable
WHERE QueueDateTime > (some value) AND Status = 1

then this query can be satisfied by using just the index leaf level pages - the index covers the query - you don't need to go back to the actual data tables to get your answer to your query.

This can very significantly improve performance, and increase scalability, too.

See a good explanation of the benefits of including columns in non-clustered indexes here.

As for the WITH (UPDLOCK, HOLDLOCK) - you should check out this other SO question on the topic and the excellent answer provided by Andrew. And here's yet another question on the same topic with an excellent answer by Remus Rusanu.

Both table hints were available at least since SQL Server 2000 (or possibly even earlier - I can't remember versions earlier than that, though).


cassandra in memory tables I am planning to use Cassandra in memory tables for my particular work load. THe problem is I am worried if the node fails then what will happen to the data in memory that was not flushed to disk ?

Would I loose that data, or not ?

Would I loose that data, or not ?

Good question!  As outlined in the DataStax documentation section on The Write Path To Compaction, you would not lose data.

As you can see, when Cassandra processes a write operation, data is written to both the commit log (on-disk) and to the memtable (which you referred to).  In case of a "plug out of the wall" event, the writes are still in the commit log.  When Cassandra restarts on a node, it first replays the commit log, and verifies that all of its data have been written to disk.  If it finds data in the commit log that was not flushed to disk, it writes to disk at that time.


indexes with unspecific columns I have a third party database in which a large number of the tables have a 'companyID' column, and most of the queries against those tables include companyID = @companyID in the where clause.

[EDIT: In addition to the where clause, the companyID is generally also used in the order by clause of most queries: e.g. order by companyId, otherField1, otherField2 etc..
]

In our organisation the companyID column is always populated with 1 (though there is an outside chance that may change at some point in the future).

Now I am looking at creating some indexes on these tables.  SQL Server recommends some 'missing indexes' and all the recommendations contain the companyID field as the first column in the index.

Going on the principle of specifying most selective column first I would think that I should put the companyID column last, or perhaps not even have it in the index at all.  But then I'm wondering why SQL Server's missing indexes view always suggests putting it first - surely SQL Server is intelligent enough to know that every row contains the same value for that column?

The SQL Server knows only about the column content if you're creating a index or a statistic for this column. If the companyID is always populated with 1 you never want to create an index for it - not even as last part of a multi column index. The recommended indices are sometimes stupid and should be considered as guess.


UPDATE a column in one table according to a column in another In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the values for the native_name column from the native_name_aliases table into the dpoint table. Here is my attempt:

update dpoint
set native_name = (select
                      nna.native_name
                    from
                        native_name_aliases as nna
                    LEFT JOIN
                      dpoint as d
                    ON
                      d.dpoint_id = nna.dpoint_id)
where building_id = 42;

I get an error message:

  more than one row returned by a subquery used as an expression

My native_name_aliases table has fields id, native_name, dpoint_id, building_id.
And the dpoint table has dpoint_id, building_id, native_name and a bunch of other irrelevant fields.

Where am I going wrong?

Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after:

UPDATE dpoint d
SET    native_name = nna.native_name
FROM   native_name_aliases nna
WHERE  d.building_id = 42
AND    d.dpoint_id = nna.dpoint_id
AND    d.native_name IS DISTINCT FROM nna.native_name;

You did not provide table definitions and Postgres version, so I can't tell for sure.

The last line is to prevent expensive "empty" updates. Details:

How do I (or can I) SELECT DISTINCT on multiple columns?


MongoDB merge two collections and drop documents with same value in field In my database I have two collections, but some documents were added (possibly) at different times to both collections.

I could use mongodump and then mongorestore for merging. But then I have the same documents that were added to both collections as duplicates in my new collection. mongorestore --drop does not help neither, because the documents not necessarily have the same _id.

How to drop a document when a document with userid exists already?

There might be more elegent solutions for this, but let me answer how I merged a new collection into an original collection without importing the duplicates.

Compare userid fields in both collections and take the difference.
Use mongos $out operator to save documents to a difference collection. This new collection has all documents of the new collection, except of the duplicates.
mongodump the difference collection and mongorestore it into the original collection.

from pymongo import MongoClient
import subprocess

# collection names
db_name = 'db'
col_original = 'col_original'
col_new = 'col_new'
field = 'userid'

# Get ids from collection
def get_ids(db, col):
    docs = db[col].aggregate([
        {'$project':
         {'_id': 0,
          'id': '$' + field}}
    ])
    docs = list(docs)
    ids = [x['id'] for x in docs]
    return ids

# Connect to MongoDB
client = MongoClient('mongodb://localhost')
db = client[db_name]

# Get difference in ids
ids_new = get_ids(db, col_new)
ids_original = get_ids(db, col_original)
ids_diff = list(set(ids_new).difference(ids_original))

# Get all documents with userid that are in col_new, but not in
# col_original. Hence, all duplicates are skipped.
db[col_new].aggregate([
    {'$match': {
        field: {'$in': ids_diff}}},
    {'$out': 'col_diff'}])

# Use mongodump to save col_diff
subprocess.check_output(['mongodump',
                         '-d',
                         db_name,
                         '-c',
                         'col_diff'])

# Merge col_diff into col_original
subprocess.check_output(['mongorestore',
                         '-d',
                         db_name,
                         '-c',
                         col_original,
                         'dump/' + db_name + '/col_diff.bson'])


MySQL - automatically update all references to specific table to use on delete cascade? I have a specific table for one of my software packages on a MySQL 5.1 box which I need to drop a few hundred entries. These are user names with names in uppercase, the application is case specific and expects lower. This table is referenced by a ton of other tables however, some of which may have references back to these users.

Is there a ready way to adjust the foreign keys for all tables in my database which reference a given column 'username' in table 'logins' to have the on delete cascade attribute set? I don't see a ready way of doing so.

What I need is to change all the other tables with foreign keys depending on this table to utilize an on delete cascade. I'm hoping to do so without manually changing a hundred tables.

The select I'm utilizing is

delete from user where user_name REGEXP '^.*[A-Z].+$';

The issue is that the application has created duplicate user IDs in the user_id field. I only want to retain the ID number for properly created usernames.

The select above targets exactly what I want to get rid of. I don't have access to the application source which created these entries.

WARNING !!!
Before you do anything, mysqldump the entire database or tarball /var/lib/mysql
If you are concerned about case sensitivity, you need to hunt down the usernames in a special way
I tried this little experiment
use test
DROP TABLE rolando;
CREATE TABLE rolando
(id int not null auto_increment,
name varchar(20),primary key (id));
INSERT INTO rolando (name) VALUES
('rolando'),('Rolando'),
('ROLANDO'),('ROLANDO'),
('rolando'),('rolando');

I loaded the sample data
mysql> use test
Database changed
mysql> DROP TABLE rolando;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE rolando
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO rolando (name) VALUES
    -> ('rolando'),('Rolando'),
    -> ('ROLANDO'),('ROLANDO'),
    -> ('rolando'),('rolando');
Query OK, 6 rows affected (0.08 sec)
Records: 6  Duplicates: 0  Warnings: 0

I ran these queries (please note subtle differences)
mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,MD5(name),COUNT(1) FROM rolando GROUP BY name,MD5(name);
+---------+----------------------------------+----------+
| name    | MD5(name)                        | COUNT(1) |
+---------+----------------------------------+----------+
| ROLANDO | b3f7ba680fe83ab0b5174737e8d536a2 |        2 |
| Rolando | e0732d78dc135b8fcc33ec79bfa89d1f |        1 |
| rolando | e80d4ab77eb18a4ca350157fd487d7e2 |        3 |
+---------+----------------------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT name,MD5(name)=MD5(LOWER(name)) from rolando;
+---------+----------------------------+
| name    | MD5(name)=MD5(LOWER(name)) |
+---------+----------------------------+
| rolando |                          1 |
| Rolando |                          0 |
| ROLANDO |                          0 |
| ROLANDO |                          0 |
| rolando |                          1 |
| rolando |                          1 |
+---------+----------------------------+
6 rows in set (0.00 sec)

mysql> SELECT name,COUNT(1) FROM rolando GROUP BY name;
+---------+----------+
| name    | COUNT(1) |
+---------+----------+
| rolando |        6 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT name,(name LIKE lower(name)) like1,
    -> (name = binary LOWER(name)) like2 FROM rolando;
+---------+-------+-------+
| name    | like1 | like2 |
+---------+-------+-------+
| rolando |     1 |     1 |
| Rolando |     1 |     0 |
| ROLANDO |     1 |     0 |
| ROLANDO |     1 |     0 |
| rolando |     1 |     1 |
| rolando |     1 |     1 |
+---------+-------+-------+
6 rows in set (0.00 sec)

mysql>

Give this view of my experiment, a username with all uppercase or mixed case can be equal to an all lowercase UNLESS YOU USE BINARY operator with the comparison
I do not think BINARY can be applied to ON DELETE CASCADE.
However, you could something like this: To delete all usernames that are lowercase only, try running one of the following:
DELETE FROM logins WHERE username = BINARY LOWER(username);

or
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

If you run this now, you might nix all usernames in the other tables. Run this instead
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE username = BINARY LOWER(username);

or
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM logins WHERE MD5(username) = MD5(LOWER(username));

You will have to go to all tables that have a username in it and execute
SET FOREIGN_KEY_CHECKS=0;
DELETE FROM <whatevertable1> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable2> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
DELETE FROM <whatevertable3> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';
.
.
.
DELETE FROM <whatevertableN> WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP '^.*[A-Z].+$';

Since you have a ton of other tables, use the INFORMATION_SCHEMA to script it for you:
DELETE_SQLFILE=/root/SensitiveDelete.sql
MYSQL_CONN="-uroot -ppassword"
echo "SET FOREIGN_KEY_CHECKS=0;" > ${DELETE_SQLFILE}
mysql ${MYSQL_CONN} -ANe"SELECT CONCAT('DELETE FROM ',table_schema,'.',table_name,' WHERE user_name = BINARY LOWER(user_name) AND user_name REGEXP ''\^.*[A-Z].+$''\) FROM information_schema.columns WHERE column_name='user_name'" >> ${DELETE_SQLFILE}
cat ${DELETE_SQLFILE}

If the file is correct to you, run it in the mysql client like this
mysql ${MYSQL_CONN} -A < ${DELETE_SQLFILE}

Give it a Try !!!


How much RAM is required for 5000 concurrent users MySQL Going by this :
When should I think about upgrading our RDS MySQL instance based on memory usage?
for max_connections to be set at 5000 concurrent users,how much should be the RAM capacity be ?

If I add per connection over head for 5000 users, as per what I set the values as shown below, I get :

[ read_buffer_size (0.125) MB + sort_buffer_size (2 MB)  +  join_buffer_size (2 MB)  +  read_rnd_buffer_size (1 MB) +   thread_stack (0.25 MB) +    binlog_cache_size (0.03125 MB) ] * 5000

= ~ 26 GB.

And as per my understanding, innodb_buffer_pool_size we normally give as 75% of RAM - for optimal performance.

So my question is, per connection over head (26 GB) + 75% of RAM = RAM, then RAM capacity should be ~ 104 GB ??

Can any one pls help me out on this.

Your problem is the innodb buffer size - that is a lot more dependant on database patterns than users. If your database is only 20gb there is no sense in a larger buffer.

And the sensible size there can not be determined from user count only.

So, given you need something for the OS and MySql as a server, then 26gb for the connections - and man, I don't do MySql, but in SQL Server world 5000 connections would be crazy and be for like 100.000 users with connection pooling at the same time.

Assuming that is not just crazy bad programming - I would go with 32 to 64gb memory and the best xeon chips I can get my hands on....  RAM is not your problem here, processing 5000 concurrent SQL statements is. As a start - 64gb memory are cheap in such a server. If the database is larger, then 128gb to 256gb memory would be good. But your problem is not memory, if you really have 5000 concurrent users doing things and not just keeping passive users connected, which is bad programming.


list of items or menu in mysql workbench i am creating a database for an android tourism app.
i am working on MySQL workbench, i want to create a menu in my database,
for example i want to list the cities of my country, or list the branches of any place around the country.
i read about ENUM & SET datatypes here, and how they are evil to my database
http://dev.mysql.com/doc/refman/5.0/en/set.html

what do you all think, what datatype do i need to represent a menu in my database?

Use a collection of primitive types appropriate to the type stored in the database. If you have more than one column from a table then use a collection of a class that you define that will hold all of the primitive types as properties of that class--if appropriate.  Bind your menu UI to the class/collection.


Is a Self-defining Table of Domains Normalized? I don't really know the official name of what I've made but it can't be the first time this has been made, so apologizes up front for not knowing every term.

My question, is a table as outlined below normalized?

pk    fk    description
1     1     domains
2     1     people
3     1     cars
4     2     tom
5     2     smith
6     3     vmw
7     2     betty
8     3     ford

The main 'rule' this table confuses me about is A table should store only data for a single type of entity. It stores domain information, but it stores multiple domains' information.

A example use for this table, fetch the domain of people.

select * from domains where fk = 2

which returns

pk    fk    description
4     2     tom
5     2     smith
7     2     betty

The fk is the 'domain' the item is in, which is the pk of the domain. This is what to me is self-defining because this table defines which domains it contains.

This is a called a "self-referencing table", or "single-table hierarchy". You normally query them with a recursive CTE because you want the children, not just on a single conditional.

Note, normally you place the fk of the root as null

CREATE TABLE foo(pk,fk,description)
AS VALUES
    ( 1 , null , 'domains' ),
    ( 2 , 1 , 'people' ),
    ( 3 , 1 , 'cars' ),
    ( 4 , 2 , 'tom' ),
    ( 5 , 2 , 'smith' ),
    ( 6 , 3 , 'vmw' ),
    ( 7 , 2 , 'betty' ),
    ( 8 , 3 , 'ford' );

WITH RECURSIVE t(pk, fk, description, level) AS (
    SELECT pk, fk, ARRAY[description], 0
    FROM foo
    WHERE fk IS NULL
    UNION ALL
        SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
        FROM t
        JOIN foo ON (foo.fk = t.pk)
)
SELECT *
FROM t;

 pk | fk |      description       | level
----+----+------------------------+-------
  1 |    | {domains}              |     0
  2 |  1 | {domains,people}       |     1
  3 |  1 | {domains,cars}         |     1
  4 |  2 | {domains,people,tom}   |     2
  5 |  2 | {domains,people,smith} |     2
  6 |  3 | {domains,cars,vmw}     |     2
  7 |  2 | {domains,people,betty} |     2
  8 |  3 | {domains,cars,ford}    |     2
(8 rows)

Now you can find all {domains,cars} like this.

WITH RECURSIVE t(pk, fk, description, level) AS (
     SELECT pk, fk, ARRAY[description], 0
     FROM foo
     WHERE fk IS NULL
     UNION ALL
      SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
      FROM t
      JOIN foo ON (foo.fk = t.pk)
    )
    SELECT *
    FROM t
    WHERE description @> ARRAY['domains','cars'];
 pk | fk |     description     | level
----+----+---------------------+-------
  3 |  1 | {domains,cars}      |     1
  6 |  3 | {domains,cars,vmw}  |     2
  8 |  3 | {domains,cars,ford} |     2
(3 rows)

But the real advantage comes with just a bit more complexity.

INSERT INTO foo(pk,fk,description) VALUES
  ( 9, 8, 'Mustang' );

Now rerunning that same query you ge t

 pk | fk |         description         | level
----+----+-----------------------------+-------
  3 |  1 | {domains,cars}              |     1
  6 |  3 | {domains,cars,vmw}          |     2
  8 |  3 | {domains,cars,ford}         |     2
  9 |  8 | {domains,cars,ford,Mustang} |     3
(4 rows)

Mustang is under ford, and it's all in the domain of cars.

You may also want just the last item of the array in the result set, or a trail of pkids. All of those are minor variations of this same pattern utilizing Recursive CTEs.

All examples tested with PostgreSQL


Appending imported data in Postgres This is what I typically use to import csv data into my postgres database..

\COPY master_list FROM '/root/raw/file.txt' DELIMITER ',' CSV;

Usually it's into a blank table, but in this case, I need to append the data into a table with existing data.

Will that same command suffice, or should I make changes since it's an append?

Try it and see.

You'll find that it works perfectly.

Now, if you want to insert data ignoring duplicates, that's another problem entirely.


Good tool for simulating many threads doing INSERTS UPDATES SELECTS and DELETES on an ODBC database? I've done load testing on a web application already using tools like JMeter, and tested my database for transactional integrity.

But are there any tools for testing an ODBC connected database (like MS-Access) using just the database?

I'd like to be able to simulate a real world situation of multiple connections reading and writing to and from the database.  I want to use this tool to prove something will work before I put it into production.

The IT shop I work for "bleeds blue" (ie, sticks mostly to IBM tooling), so the performance software we use is IBM's Rational Performance Tester (RPT). IBM's stuff isn't (usually) free or cheap, but RPT has helped us to populate our databases through our applications (mostly because the relationships among the tables is too complex to do nicely via script).

That being said, we are also considering using a tool like the following data-generator, in order to better be able to mass insert data into our database for performance testing.