DB2 LOAD: Poor performance while loading table with large BLOBs I need help regarding LOAD-command with BLOBs.

I notice very poor performance while loading a table which contains large BLOBs.
While tracing the OS, I only have about 15MB/sec I/O-speed.
But in other cases I have normal I/O-rates > 200MB/sec. (e.g. creating DB-backups, creating tablespaces, copy files etc.).

I already tried to use different extent-sizes, page-sizes and some LOAD-parameters like "DISK_PARALLELISM", "SORT BUFFER", "DATA BUFFER", "SAVECOUNT" etc. to improve LOAD-performance but I was not able to speed up the LOAD.
The target table is empty and has no indices defined.
Source and target are located on separate RAID-volumes with dedicated physical drives.

Any hints to improve LOAD performance or to further investigate the problem are very welcome.

Thank you in advance.

Ronny

My setup:
- SLES 11 SP2
- DB2 9.7 FP8
- Raid:
    Source: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
    Target: 2 dedicated Disks, Raid-0 (Striping) with 64 KB stripe-size
- File-System: EXT3
- Tablespace-Definition:
    CREATE LARGE TABLESPACE LOB IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY DATABASE USING (FILE 'xxx' 20000000) PREFETCHSIZE AUTOMATIC BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY OFF;

- LOAD-Definition:
    LOAD FROM xxx OF IXF LOBS FROM xxx MODIFIED BY lobsinfile METHOD N (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) SAVECOUNT 1 ROWCOUNT 250 MESSAGES /xxx/CONTENT.log REPLACE INTO CONTENT (ID, CONTENT, SEGMENTGROUPID, SEGMENTNUMBER) NONRECOVERABLE;

As Chris already mentioned LOAD is a utility and therefore the UTIL_HEAP_SZ must be set high enough (but don't ask too much, always monitor the memory free space with top). You need a lot of memory for a fast load (see DATA BUFFER below).

Here are things that speed up the load:

More containers for the tablespace where the table is (best on separate luns / disks)
DATA BUFFER parameter (default 25% of UTIL_HEAP_SZ), make it bigger 400-800 (4K) per CPU core available.
ANYORDER (allows LOAD to abandon the input order, bad if you load in clustering order) can give 30% boost
FASTPARSE (use only if data is clean e.g. unloaded from other table) can give 15% boost
SAVECOUNT - the higher the better I use #input records/10, so for 100M records the savecount is 10000000 - in your case 1 the LOAD has to update the restart data with every record = SLOW!

Note: always collect statistics with STATISTICS YES during load. It is faster than LOAD + RUNSTATS

Good luck and let us know your improvements!


SQL Application Role access-control I have been researching the use of Application Roles within Microsoft SQL Server and was wondering if there is a way to limit a user to only be able to set specific application roles. As I understand, any user of the database with sys.sp_setapprole execute permissions can set any application role they choose, as long as they provide the application role name and password. To me this seems like a trade-off in security.

For example, let's say John works in Accounting and uses an application named Accounting.exe, which is hard-coded to set the Accounting_appRole once connected to the AdventureWorks Database. Bill works in Finance and uses an application named Finance.exe, which is hard-coded to set the Finance_appRole once connected to the AdventureWorks Database.
Both John and Bill must, at a minimum, have execute permissions for the sys.sp_setapprole stored procedure of the AdventureWorks Database. Therefore, if John from accounting e-mails the Accounting.exe application to Bill in finance, Bill can execute the Accounting.exe application without issue, which is undesired.

I think there should be a way to limit who can set a specific application role, does anyone know of a way? All I can come up with is modifying the sp_setapprole stored procedure to use SUSER_SNAME() to limit the value(s) of @rolename that can be used.

I wouldn't recommend messing with the system stored procs.  You could wrap the sp_setapprole call in a stored proc and have your app call that proc instead.  The wrapper proc would have the password hard-coded and use EXECUTE AS to impersonate a user who can use sp_setapprole.  Then you could limit access to the wrapper proc.  Another advantage to this approach is that the password doesn't go out across the network, nor is it included in your .exe file.

EDIT:

My mistake, you can't wrap the call directly.  But you can wrap a modified version of the T-SQL that it runs.  Looking at the version in my 2008r2 server, I see that the proc calls SETUSER intead of EXECUTE AS.  Sad to see deprecated code in system stored procs.  For yours, you may want to use EXECUTE AS instead.

You could also grant rights on whatever your app needs to function to a SQL user who can't login, give certain users rights to impersonate that user, and have your app EXECUTE AS the login-less user.  This route still requires cookie handling to set the context back.  In fact, it's very similar to a customized version of sp_setapprole, since both require some sort of impersonation.

Or, you could also create a database role and grant the role to authorized users.  This seems like it might be more like what you want.  Application roles are designed for app-defined rights.  It seems like you really want user-defined rights.


Mysql tar ball installation I need to install two different versions of Mysql in one server OS RHEL6 and it will be separated by user called mysqlprod and mysqlpreprod.

I need to separate their binaries, softwares,data directory and mysqldaemons.

I assume tar ball installation is a best way to deviate the installations. But I don't find tar ball for Mysql 5.6.17 in http://dev.mysql.com/downloads downloads. Its just rpms and I cannot do "rpm --basedir=" because it says rpm cannot be relocatable.

Where can I find tar ball binaries that is provided in http://dev.mysql.com/downloads ? or anyother way to achieve my above setup in Mysql 5.6.17 latest GA? Please advise.

The tarballs are at the bottom of the list of downloads after you select "Linux - Generic" from the operating system drop-down... not under "Red Hat Enterprise Linux."

Assumuming you are 64-bit, the one you're looking for is called "Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive."


Startup oracle database 12c manually with SQL*Plus command not when OS loads I changed oracle instance to manually start from services.msc (service name is "OracleServiceORCL"), Because I want to start it up manually with SQL*Plus commands whenever I want, not with OS load.

Now I can't do that in SQL*Plus and I got this error:

  ORA-12560: TNS:protocol adapter error

So I start it manually from services.msc and when database got ready I shut it down with SQL*Plus command. After that I realize that the service is still running, with very low consuming of resources.

I think this called "idle instance".

How can I automatically be in an idle instance state after I logged in to my Windows 8.1 to start oracle database whenever I want with SQL*Plus commands, not manually from services.msc?

You can configure this behaviour through the Administration Assistant, where you can choose to start (or not to start) the database instance with the Windows service.

Alternatively, you can do this with oradim, e.g:

oradim -edit -sid ORCL -startmode manual

The Windows service should be set to start automatically, and with the above setting, the database instance will not start with it.


PostgreSQL vs. Oracle High Availability solutions? PostgreSQL has a matrix of different high availability options which represent many different ways of building replication into an RDBMS.

Here is the PostgreSQL  High Availability, Load Balancing, and Replication Feature Matrix

Questions

Which of the approaches in the PostgreSQL High Availability Matrix are supported by Oracle?
Does oracle do high availability with techniques that are not available with PostgreSQL?

Oracle's Data Guard replication is similar to PostgreSQL's "Hot/Warm Standby Using PITR", which is built-in to the database as of PostgreSQL 9.0.  Version 9.1 adds synchronous replication too.  One advantage that PostgreSQL has over Oracle here is that Sync Rep can be controlled on a per-transaction basis.  You can have a fully synchronous "Important!" Transaction followed by an asynchronous "OK to lose" one in Postgres.

Oracle's RAC is similar to what PostgreSQL is labelling "Shared Disk Failover" in that grid.  The main difference is that RAC is fully integrated into Oracle's product, while "Shared Disk Failover" just describes a method of doing something.  You have to assemble the necessary clusterware software around that for PostgreSQL, and there are a variety of advanced things RAC does you'll be hard pressed to duplicate in PostgreSQL.  I regularly hear that most of those things are so complicated to setup that few Oracle installations get them right either--just because RAC is built-in doesn't mean it sets itself up automatically.

The main thing you can do in Oracle that is very hard to duplicate as well in PostgreSQL is multi-master replication.  It's possible to do multi-master in PostgreSQL, but only with add-on software like Bucardo.  And all such programs still have more restrictions on what you can do with them than the Multi-Master Oracle installations provide.


Cant initialize MySQL. "mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended" I am doing a fresh install of MySQL on Fedora 22.
After I run mysql_install_db I try to start the daemon using mysqld_safe but it does not work. The system says:

mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

How do I get this to work?

Here are the mariadb.log contents:

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[Note] /usr/libexec/mysqld (mysqld 10.0.23-MariaDB) starting as process 20443 ...
[Note] InnoDB: Using mutexes to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 128.0M
[Note] InnoDB: Completed initialization of buffer pool
[ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
[ERROR] InnoDB: The system tablespace must be writable!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] mysqld: File '/var/lib/mysql/aria_log_control' not found (Errcode: 13 "Permission denied")
[ERROR] mysqld: Got error 'Can't open file' when trying to use aria control file '/var/lib/mysql/aria_log_control'
[ERROR] Plugin 'Aria' init function returned error.
[ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
[Note] Plugin 'FEEDBACK' is disabled.
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
[Note] /usr/libexec/mysqld: Shutdown complete
mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

The error says:

  The system tablespace must be writable

Please check the permission of the data directory of MySQL


How would I store the result of a select statement so that I can reuse the results to join to different tables? How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor.

Below is some pseudo code, in this example I have kept the Select statement simple but in real life it is a long query with multiple joins, I have to use the identical SQL twice to join to 2 different tables and as it is quite long and can be changed in the future hence I want to be able reuse it.

I have tried creating a view and storing the results of the select statement in it but it seems I can't create a view inside the cursor loop, when I tried I am getting "Encountered the symbol "CREATE"" error.

DECLARE TYPE cur_type IS REF CURSOR;
CURSOR PT_Cursor IS

    SELECT * FROM Table1

    PT_Cursor_Row   PT_Cursor%ROWTYPE;

BEGIN
 OPEN PT_Cursor;

 LOOP
    FETCH PT_Cursor INTO PT_Cursor_Row;
    EXIT WHEN PT_Cursor%NOTFOUND;

    Select ID From Table2 --this is actually a long complext query
        INNER JOIN Table3 ON Table2.ID = Table3.ID
    WHERE Table2.ID = PT_Cursor_Row.ID

    Select * From Table2 --this is actually a long complext query
        LEFT JOIN Table4 ON Table2.ID = Table4.ID
    WHERE Table2.ID = PT_Cursor_Row.ID

END LOOP;

 CLOSE PT_Cursor;
END;

You may store the results you want to reuse into a nested table type. In the following example, I use a nested table of number(22,0). A nested table in PL/SQL is not a real table, it just exists in memory like an array.

-- Creating some test tables and data
create table table1 (row_id number(22,0));
create table table2 (row_id number(22,0));

insert into table1 values(1);
insert into table1 values(2);
insert into table1 values(3);
insert into table2 values(1);
insert into table2 values(2);

---------

-- Create a nested table type
CREATE OR REPLACE TYPE type_table_number AS TABLE OF NUMBER(22);

declare
 v_tab type_table_number;
 cursor c is
   select row_id
   from   table2
   where  row_id in (SELECT row_id FROM TABLE (v_tab));
begin
  -- Select row_ids into the table v_tab
  -- (this is not a real table, the data is stored in memory)
  select row_id
  bulk   collect into v_tab
  from   table1
  WHERE  row_id in (1,2);
  -- Select some data from a table and use the content
  -- of the in memory table (v_tab) as search criteria:
  for v_rec in c loop
    DBMS_OUTPUT.PUT_LINE(v_rec.row_id);
  end loop;
  -- Select some other data from another table and also
  -- use the same content of the in memory table:
  -- ...
END;
/


Restore a database with compression on a non-enterprise edition SQL Server Is it possible to restore a compressed an object from backup without the Enterprise Edition?

I'm getting the following error after a RESTORE DATABASE operation.

Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 2
Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'def' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 2
Database 'abc' cannot be started because some of the database functionality is not available in the current edition of SQL Server.

I can think of two solutions but I don't know whether it's possible or how to do it if so:

Decompress the object from the backup without Enterprise Edition using some sort of utility.
Remove the offending object from the backup using some sort of utility so I can use the other non-compressed data. (Not sure how it would handle references such as foreign-keys though.)

Would it be possible to somehow do this in SQL Server? It's refusing to load the entire database even though not everything is compressed. Is there a way to force SQL Server to attempt to load the other tables?

No !

But, you can :

1 script our your database schema and use BCP out and bcp in method  or

2 restore the database as a copy on EE or Dev edition, remove all enterprise features and backup the database and restore it on standard edition.


Difficult multi-directional join I'm being asked to create what (at first) seemed to be a fairly simple/straight forward query. However, I am running into some problems caused by the link relationships between the tables being queried. And the wide range of data to be included on the report from various different tables.
This is a postgresql database that contains information on cooks who sell food items. Simple enough. The requested report queries for personal information on the cooks, their available menus, food items on those menus, and the last time that item was purchased...data which resides on 6-7 different tables.
I'm having a really difficult time JOINing all of these tables together in a way that returns the data in an acceptable way. Here is a visualization of the tables I'm working with and their link relationships:

As you can see, everything originates and links back to the user's entry on the account table. From there, sellers have an entry on the "store" table, which links to pretty much everything else:

'store_address' and 'address' tables contain address info.
'menu' table contains the names of the menus- each store can have multiple menus which offer certain food items.
kitchen_item table contains all of the specific food items available on each of the various menus.
'menu_item' table is what's used to link the food items on kitchen_item table to it's menu on the menu table.
'orders' tracks order information for each item sold

So I'm having trouble joining all of these tables together correctly. I need to be able to show each seller (account table), their personal info (address table), their available menus (menu table), the items on each of those menus (kitchen_items table), and the date of that item's last sale (orders table).
When I simply left join everything, every possible menu contains every possible food item... I attempted a combination of left joins, right joins, full joins, etc... but I think this one might be a little above my skill level... given the layout of the relationships.
So, would anybody be so kind as to demonstrate these tables might be joined together?
the joins:
select
account.id, account.email, account.firstname, account.lastname,
address.address, address.address_2, address.city, address.state,
menu.name,
kitchen_item.name,
orders.placed
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
left join orders on store.id = orders.store_id
join store_address on store.id = store.address.store_id
join address on store_address.address_id = address.id
group by table.value, table.value...

When I run the query with the tables joined like above, it's just missing random entries... and I'm not sure why.
The data is for a report. Tabular "everything together" or split into subject areas is fine - though split into subject areas might look nice. The only requirement is a column for each data point.
The last part I'm trying to accomplish is to join the table "orders" to obtain the newest single entry in orders.placed for each already existing row/food item. I'd imagine I need to write a subquery?
I completely agree with splitting the query up, though this is strictly the query that the individual wants. I would guess they're going to want me to implement some sort of " case when rowcount() >1 = '' " to clear out all of the duplicate entries and turn it into whitespace.

I've since worked out the specific issue this post focused on (the joins)... and have moved on to a new set of issues with it. So to keep the post on topic I'm going to answer/close it up. Nobody disputed the way I ended up joining the tables or suggested a "more correct" or efficient way, so I guess what I have can be considered correct for this situation.
Maybe somebody else who lands on this page will find my visual diagram and subsequent joins helpful in visualizing/joining their own tables... who knows.
Anyways, this is what worked for me:
from account
left join store on account.id = store.account_id
left join menu on store.id = menu.store_id
left join menu_item on menu.id = menu_item.menu_id
left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
join store_address on store.id = store_address.store_id
join address on store_address.address_id = address.id


SQL Server: Get latest backup restore We had a SQL Server crash and most of the data is unobtainable. I have found some old full backups and some differential backups in different servers.

Is there a way to tell which differential belongs to which full backup?

From your description, you have a set of files some of which are full backups and some of which are differentials. If it were me, I'd use the various backup metadata interrogation commands inside the dbatools powershell module. Something like:

$bh = Get-DbaBackupInformation -SqlInstance yourRestoreServer -Path directoryForYourBackups;
$bh | Restore-DbaDatabase -SqlInstance yourRestoreServer -OutputScriptOnly;

That first command will go through and do a restore headeronly on all of the files and the second should select the most recent one(s) to do a restore on (which includes matching differential backups to full if that's an option).

If you don't already have the dbatools module installed, check out the installation instructions.


How to monitor a file share witness for accessibility In the context of a SQL Server Availability Group, what's the best way to monitor a file share witness for accessibility?

I want to make sure the witness can be accessed by the Availability Group replicas, which is something different than just being available.  For instance, the account used to run SQL Server needs access rights to the file share.

Edit: I recently noticed some events logged by the cluster worthy of being monitored.

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Date:          6/1/2019 1:23:09 PM
Event ID:      1564
Task Category: File Share Witness Resource
Level:         Critical
Keywords:
User:          SYSTEM
Computer:      node1.contoso.com
Description:
File share witness resource 'File Share Witness' failed to arbitrate for
the file share '\\servername\sharename'. Please ensure that file share
'\\servername\sharename' exists and is accessible by the cluster.

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Date:          6/1/2019 1:23:08 PM
Event ID:      1562
Task Category: File Share Witness Resource
Level:         Warning
Keywords:
User:          SYSTEM
Computer:      node1.contoso.com
Description:
File share witness resource 'File Share Witness' failed a periodic health
check on file share '\\servername\sharename'. Please ensure that file
share '\\servername\sharename' exists and is accessible by the cluster.

The file share witness does not need to be accessible by the SQL Server service account. The FSW is used by the Windows Server Failover Clustering cluster to achieve a quorum and avoid split-brain scenarios. The cluster machine account is the AD principal that needs access to the FSW and if it did not have access you wouldn't be able to set it up as a witness in your cluster.

From an Availability Group perspective, it does not care about access to the FSW. The cluster cares about this and when it fails (and the quorum is lost) it signals the AG to shut down to avoid split-brain. The AG only cares about the SQL Server specific health checks (latency, connectivity to replicas etc) and the signals received from the WSFC cluster regarding cluster health.

If the cluster CNO loses rights to the FSW share then that resource would go offline in the cluster. This won't necessarily trigger any action in the cluster but should log alerts and events in the Windows Event Log for which you can monitor.


SQL server performance and tuning I am working on a system, there is a cisco load balancer that can handle 40K requests.

There are two app servers. There are 12 or so web services.

and there is a SQL server with 2TB of data.

Bottle neck here is SQL server, it is on a single machine with Quad Core CPU, and 80 GB ram.

There are about 2.5 million items stored in database.

The kind of queries are , select product with some properties which are not indexed.

There is a huge index and we don't want to index everything, because index fragmentation is really hurting performance.

I thought about caching, distributed caching, but some of the queries are not very convenient for caching, such as give me companies that starts with foo. There is a huge combination there.

How would you go working on this issues?

There is more select than insert. mostly select. There are several small select. and there are some joins with 3 tables.

With 2.5 million items I would expect your indexes to be rather large.  Particularly if you are indexing on large fields.  Also just as an FYI if your primary key is large that doesn't help either.  Any non clustered index has the primary key (assuming that is your clustered index) in it regardless of if you include it or not.  It has to as a reference back to the table.

So if you have a large primary key (and only if you have a large primary key) you might consider creating an identity column and changing that to your clustered primary key.  You can keep your existing primary key as a non-clustered unique index.  Also if you are worried about index fragmentation REBUILD/REORG it.  It's part of the ALTER INDEX command in SQL 2008 and up.  You can even do it on line.

My suggestion to you is to add the indexes you need (within reason).  Generally 3 or 4 non clustered indexes isn't going to hurt performance.  Honestly you can probably get away with 10 or so without a problem if you really need it.  Test on your system as mileage may vary.  Personally I would make sure that you have a clustered index as well.  Also add a maintenance job of some type to do your index REBUILD/REORG.  There are a number of them out there that will even check your current fragmentation and REBUILD or REORG intelligently as needed.


Does clustering provide disaster recovery in MS SQL Server? I mean can the nodes of MS SQL Server failover clustering be geographically separated and besides high availability also provide disaster recovery?

By itself, no: clustering relies on shared storage. A failover cluster with shared storage looks like this:

All 4 of the components - the 2 servers, the switch, and the shared storage - should be located in the same data center.

Disaster recovery is about maintaining availability even when you lose a data center. That means you need the same infrastructure pictured here, but in yet another data center, with some kind of mechanism keeping the data in sync between those environments.

Options include:

Storage replication
Always On Availability Groups
Log shipping
Database mirroring

But all of these are above and beyond plain ol' failover clustering.


Promote MySQL Table Partition to Table My Google Searching skills were not enough to find a solution to this. If possible, how can I promote a partition on a table to a table on its own?
Let's assume I have part0, part1, and part2. Is there any ALTER TABLE that I can give that converts part0 to a new table?
My thought process was, since the data is already existing in a separate file on disk, the table creation process may be faster than creating a new table using a SELECT query.

Yes, a PARTITION is a TABLE.  However, it takes some special commands to turn a partition into a table or vice versa.
See "transportable tablespaces" and FLUSH TABLES ptable FOR EXPORT; ALTER TABLE ptable DISCARD TABLESPACE; ALTER TABLE ptable IMPORT TABLESPACE.  The details are different between MySQL 5.6 and 5.7+.
http://mysql.rjweb.org/doc.php/partitionmaint#use_cases_for_partitioning has links to a few places that discuss the techniques.


How can I create a table to lookup values for combinations of varying sized sets of numbers? I have a table of products, each with it's own id (int) as primary key:

product_id  name
3           product1
4           product2
6           product3
10          product4

I have a table of packaging types, each with it's own id (int) as primary key that these products may be packaged/shipped in:

packaging_id   name
15             Box
20             Bag
21             Padded Mailer
26             Tube

I need a table(s) to lookup which packaging to use for a given combination of products.  For example, product_ids 3 and 6 will fit together in a bag (package_id 20).  Likewise, product_ids 3, 4, and 10 may require a Box (packaging_id 15).  The combination may be any number of product_ids.  A pseudo query would look like this:

SELECT packaging_id FROM lookuptable WHERE product_id = 3 AND product_id = 6

In other words, I must be able to query a specific combination of n product_ids and retrieve a packaging_id. Something like this would work if the number of product_ids in the WHERE clause was fixed, e.g. only two products could ever fit in a package:

product_id1   product_id2  packaging id
3             6            20

However, there may be n product_ids to query, so this won't work.

Another solution may be to create some type of hash for the primary key, like this:

product_combination_id   packaging_id
00030006                 20

But I feel like there is a more robust solution.  Ideas?

Thanks,
Casey

I'll try with a table structure this way:

create table packaging_group
(
    pack_group_id int primary key,
    product_id int foreign key references products(product_id),
    packaging_id int foreign key references packages(package_id)
);

No matter how many groups and how many products fit a package. You can always add/remove products to an specific package.


Loading multiValued fields into Solr via flat file, and possibly value position preservation in those fields A hobby web project of mine uses data from many sources, some flat, some highly normalized. My ETL process boils all sources down to one logical table (which is soon to include some multi-valued fields). The final table is loaded into a few separate data presentation environments (Solr or MySQL based). My Solr environment demands a denormalized model (it just loads the transformed output table directly as a flat file), and my MySQL environments can each use a highly normalized model (loading involves some normalization). Both presentation environment load processes are very simple right now.

To facilitate the use of flat files throughout my ETL process (important because I use a variety of tools to summarize and cleanse the data, and make heavy use of pipelines for performance reasons), I've been able to reduce multi-valued fields from my sources to a single field using grouping or best candidate selection.

Now I'm designing for the multi-valued fields I need to preserve in my output. The MySQL load process is easy because I've already normalized it with these fields in mind. But I'm having trouble with the Solr part:

I have zero experience with "multiValued" fields in Solr
the flat file Load process is currently very simple, I'd like to preserve as much simplicity as possible
my multiValued fields must be coordinated by position within a record, e.g.

record X, field A, position 3 is a license #
record X, field B, position 3 is the corresponding state for that license

How might I design my ETL for the multi-valued fields I plan to preserve?

Some ideas I've come up with that I'm hitting roadblocks on:

introduce an "additive" record using the same document key that occupies its own row in the flat file

no idea where to start on this one

pre-assemble values into a single field, parse using Solr, e.g.

{[[123],[456]];[[MA],[CA]]} -> "123,MA;456,CA"
not sure if Solr can unpack a string and preserve position info appropriately across fields

I've been unable to find information about coordinating multiValued field values from separate fields. Allowing for the fact that this might not be natively supported by Solr, I'm still interested in solutions to loading multiValued fields into Solr in a way that's nearly as simple as loading a flat file.

Getting multiValued data into Solr via CSV:

The solr documentation describes a "split" function in UpdateCSV. Essentially, it parses a field value using a second CSV parser. See Solr - UpdateCSV - split. The parameters look like so (adjust field name, separator, and encapsulator as necessary):

f.fieldA.split=true&f.fieldA.separator=%2C&f.fieldA.encapsulator='

Getting multiValued data from separate fields to maintain position:

Since asking this question, I've done some reading about dimensional models. It seems that what I was trying to do is poor design, because it places too many expectations on the application, to much complexity in the warehouse, or both.

When trying to preserve the relationships between two field values on a single record, it's better to store them separately as well as together. Here's a comparison of my former input to the new input:

Former CSV input:

name|licenseState|licenseType
Josh|MA,CA|123,456
Fred|MD,OH|789,123

Transformed CSV input:

name|licenseState|licenseType|licenseStateType
Josh|MA,CA|123,456|MA123,CA456
Fred|MD,OH|789,123|MD789,OH123

This way your application can use the licenseState and licenseType dimension values independently, or it can use the licenseStateType dimension values, all without requiring complicated app or warehouse logic.


Postgres use multiple tablespaces in a single database Is it possible to allocate tables directly to tablespaces within a single database/schema in postgres? Based on the docs, tablespace is a create database argument, but are there any workarounds?

Yes it's possible.

As documented in the manual you can specify a tablespace when creating a table.

However, tablespaces are rarely used in Postgres as they don't really provide any advantage.

One of the few reasons to use them is, when you can't easily expand the size of your filesystem where the data directory is located.


Nightly Excel Spreadheet import into mssql database I am currently working with a nightly import that I need to create, but am not sure what the best route would be to update/insert into the current table. This is all done in MS SQL Server 2012 and pulling the Excel file from another server. I am trying to figure out how I can loop through the columns and pull out the data I need. If I could rearrange the data, I would but am currently stuck with what I have.

In my current table tblHW I have Columns such as PmpCount, , NumberStages, Pmpmodel_pmp1, serialnum_Pmp1, pmpModel_pmp2, Pmpmodel_pmp2, serialnum_pmp2, partnum_motor1, serialnumberMotor1, etc…. I apologize in advance for not being able to post a real table or a picture.

Example:

|Name      | PmpCount| numstages| pmpmodel_pmp1| stages_pmp1| Sn_pmp1|

|AN 91-23G |        4|       500|        FX2347|         250|  354197|

|BR DN 895R|        5|       521|         D2442|          45|  875164|

|ALN 1-60J |        5|       521|        H21342|          95|  594126|

--

|pmpmodel_pmp2| stages_pmp2| sn_pmp2| Partnum_mtr1| sn_mtr1|

|FX2347       |         250|  354198|         NULL|    NULL|

|FX17500      |         143|  102547|        M7544| 4512241|

|FX17500      |         143|  458790|        M7544| 4512364|

The information I want to move into tblHW comes from the tbl Pull_Down. Here is the setup:

|Name      | Run_ID               | Part1| SN1     |    Attribute1_7|

|AN 21-919G| Oct 08, 2013 / 100845|   BOD|       NA|           3RD U|

|FR 55-013A| Oct 17, 2013 / 100853|   Pmp| 2EA3A022|              78|

|FR 55-013A| Oct 01, 2014 / 101383|   Cbl|      N/A|        REDALEAD|

|FR 43-223J| Apr 03, 2013 / 100594|   BOD|       NA|           3RD U|

|VH 204    | May 17, 2014 / 101145|   BOD|    3RD U|

|Part2| SN2      | Attribute2_7| Part3   | SN3     | Attribute3_7|

|Pmp  |  2EA3F379|           78|      Pmp| 2EA3N380|          117|

|Pmp  |  2EA3C020|          117|      Pmp| 2EA3Y021|          117|

|MLE  | J14312161|          120|      BOD|      N/A|        3RD U|

|Other|        NA|          Pmp| 2EA2X774|       78|

|BOD  |      NULL|          Pmp| 2EA4F075|       38|

A bit more information. I am receiving this information in the form of five excel spreadhsheets each with over 400 columns. The columns giving me the biggest headache are the 20 part columns that I need to place into the SQL table.

I need to somehow move each row into the tblHW but need to do something like this:

The first row AN 21-919G needs to have SN1 to be inserted into sn_mtr1 since it is a BOD, SN2 into SN_pmp1 since it is a PMP, and SN3 into sn_pmp2 since it is the second PMP here. I also need to get the pmp count, in this case 2 and then add the attribute1_7 and attribute2_7 to put into numstages when the prts are PMP.

I think SSIS is really what you want to use here.    To just do a very basic import of Excel into SQL Server this is a great link to use - Simple Talk 10 Simple Steps

From there, you will then need to use a combination of Conditional Splits as well as MultiCast in order to move the data to different fields/locations depending on your various rules.    Lastly for your PMP count, it seems like using the RowCount task should cover what you are looking to do with that.

There are dozens of ways to do these things even if you decide to go about this using SSIS.    You can load your data into Staging tables so that everything is an insert and then you run Execute SQL Tasks to do all of the individual updates.   Or you could use that MultiCast/Conditional Split approach and then use SQL Server Destinations or you could use OLE DB Commands in order to get the information either loaded or update individual records based on the data that you have available to you within the data flow.   There are certainly performance issues to deal with when using those OLE DB Commands, since they are working each thing row by row, but they are fairly clear to understand within the Data flow task.

You will have to work through what works best for you.   I have tried to include links of what I feel are good examples to show you how to do these kinds of things (Excel import, Multicast, Conditional Split, RowCount and OLE DB Command), so hopefully between that this will get you where you need to go.


MySQL cluster write performance with Galera (MariaDB) I am currently evaluating the migration from our current database server to a three node cluster.

Our current server is a VM running MariaDB 10.1 with asynchronous replication to another server in order to perform backups of the database without any performance hits.

The read to write ratio is about 70/30, we are running an IoT platform with many sensors sending raw data that is written to the database before it is consolidated and presented to the end user.

Because of the high write to read ratio, I want to make sure that the cluster can perform at least as well as our current server on writes before migrating.

For this test, I started a VM on the Google Compute Engine with 16 vCPUS, 60GB of RAM and SSD local storage with 10,000 read IOPS and 15,000 write IOPS.

On this VM running Debian 8.3 I installed MariaDB 10.1 (which includes the Galera cluster), in order to run some benchmarks with Sysbench 0.5.

These are the MySQL parameters that I changed from the default mysql.cnf file:

[mysqld]
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=2G
innodb_buffer_pool_size=4G
sync_binlog=0

When running the update test from sysbench, I am noticing a huge overhead from enabling wsrep on the single node that I have currently deployed.

This is the command that I am using to run the benchmark:

sysbench --db-driver=mysql --num-threads=32 --max-time=30 --max-requests=0 --oltp-tables-count=32 --oltp-table-size=100000 --oltp-test-mode=complex --test=/root/sysbench/sysbench/tests/db/update_index.lua --mysql-db=sbtest --oltp-skip-trx=on run

With wsrep_on=OFF, I get around 54000 updates per second on the benchmark.

Setting wsrep_on to ON, I get around 19000 updates per second.

I was expecting a performance hit from enabling synchronous replication, but I did not expect it to be this much.

It doesn't look like there's a bottleneck from the CPU nor the IO, so why is the performance hit from enabling wsrep so high?

Data Warehousing (your app) and OLTP are somewhat different.  So, I question the relevancy of the benchmark.  OLTP, if I am not mistaken, involves very short 'transactions', which can suffer from the cross-node syncing done at COMMIT by Galera's wsrep.  On the other hand, your insertion of new datapoints could be done in batches of, say, 1000, and not have to worry about the commit overhead.

With 60GB, innodb_buffer_pool_size should be more like 45G.  And set innodb_buffer_pool_instances=16.

On the surface, 19K/sec (per node?) seems faster -- 19K*3 > 54K.  But maybe I am misreading things.

Let's see your SHOW CREATE TABLE for the Fact table.  There are a number of mistakes that can cause severe performance problems in your type of application.

When do you do the "consolidating"?  And how?  Summary table (which I assume is what you are talking about) is key to efficiency.  Sometimes it is even better store the summary data, yet toss the raw data.

Check out my discussion of High speed ingestion and Summary tables.  My point in those are that there are significant speedups that can (and should) be made aside from the underlying structure (hardware, Galera, etc).

Your Galera settings are in the right direction; there may be extra tips in Galera.

More questions (which will lead to more discussion): How many clients are pumping data into the table?  How many rows are inserted at once?  How easy would it be to gather the rows and batch-insert them?  Is any Normalization occurring during the insertion?  Does it make sense to do the summarization in parallel with the insertion?  Did you intend to have clients writing to all 3 nodes?  Do you have some form of Proxy between the clients and the nodes?


how to set/change the font style in database? This question may be duplicate with another but my question is this.

I want to set or change the font style in header of table (like hindi font or any other) of database. I am using sql server 2008 on my system. I have no idea to changed the font (like kruti dev 010 or dev lys 010 or etc). I want some headers of the table in english and some other in hindi language.

I hope this information will be complete to understand.

please reply if any one have some ideas about such kind of things.

thanks

When using SQL, you can use any characters almost: that is, the sysname datatype which is nvarchar(128)

Example (copied Hindi and Urdu text from Wikipedia)

CREATE TABLE मानक (
    اردو int NOT NULL PRIMARY KEY,  --SQL editor reads *only* the column name right to left
    foobar int NULL
    );
SELECT TOP 1 * FROM sys.objects O ORDER BY O.create_date DESC;
DROP TABLE मानक;

If you mean in the GUI, then this should be Windows locale dependent.

If you mean the GUI designers, then frankly I don't know because I use raw SQL.

If you mean the actual SQL language, then it is English

Edit: Under Tools.. Options, you can select fonts. This may be what you want:


SQL Server - server-side tracing I've successfully started some server-side tracing on a customer's SQL Server instance thanks to a few great posts out there, but since that's really my first ever in a "real-life" situation, I was wondering about a few things.

1) I've defined a trace file's "base" name in the script that creates the trace. I'm starting the trace at 6am and turning it off again at 7pm everyday. What I'd really love to do is set the trace file's base name to include the date, e.g. I would love to make it be

 (some directory)\MyTrace-2012-Apr-19

and so forth - so that each day's trace files would be easily recognizable. Is there a way to achieve this?? Would I have to actually create and drop the trace every day to get this??

2) If I turn on and off the trace on a daily basis, will my trace file from yesterday (MyTrace.trc) be reused today, up to its defined max size? Or will SQL Server create a new trace file (MyTrace_1.trc, MyTrace_2.trc) every day when it starts up?

3) Also, I had trouble trying to go get that trace file, even after 7pm - it seems as if it was still locked by some process and I couldn't just copy it elsewhere... I was under the assumption that once the tracing is stopped, the file would be "freely" available - am I wrong?

You'd have to substitute the datetime in the sp_trace_create
That is, drop and create each day unfortunately (as you noted). There is no "alter trace"
Overwritten. The 1, 2, etc suffix is for rollover: not for trace startup
It isn't locked.
The NTFS ownership and permissions are not inherited from the parent folder. You need to change this using Explorer or via scripting (cacls, subinacl) (not sure exactly sorry, been some time)


Force a Member to Become Primary in mongodb I looked into answers but nothing works for me in mongo 2.6.11 cluster 'nms' (members nms01m and nms02) below. I want my current secondary nms01m to become primary, and now nms02 is primary instead of nms02 (after nms01m failure last night). I tried stopping nsm02, restarting nms01m , changing priority, rs.reconfig(cfg, {force : true}) but nothing helps. It is always a struggle for me

nms:SECONDARY> rs.conf()
{
        "_id" : "nms",
        "version" : 193970,
        "members" : [
                {
                        "_id" : 0,
                        "host" : "nms02:27017",
                        "priority" : 2
                },
                {
                        "_id" : 1,
                        "host" : "nms01m:27017"
                }
        ]
}

The reason nms02 becomes Primary when it joins the replicaset is due to having the higher priority setting.
According to the documentation:

Specify higher values to make a member more eligible to become primary, and lower values to make the member less eligible.
snip
A priority of 0 makes it impossible for a member to become primary.


Differentiate between a row modification and add? Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted?

I know minus can extract any rows that were modified in any way (add/delete/edit), but right now I feel the only way to differentiate an "edit" among these 3 is to compare "hard values" shared between corresponding records and detect the column change...

Take below for example:

Table GarageBefore
__________________________
|Make |Color |VIN |Driver |
|AUDI |GRAY  |650 |SMITH  |
|BMW  |SILV  |808 |JOHNS  |
|BMW  |GREE  |754 |CARRO  |
|CHEV |WHIT  |532 |JONES  |
|_________________________|

Table GarageAfter
__________________________
|Make |Color |VIN |Driver |
|AUDI |GRAY  |650 |SMITH  |
|BMW  |SILV  |303 |JOHNS  |
|BMW  |GREE  |754 |CARRO  |
|BUIC |BLAC  |400 |JAMES  |
|CHEV |WHIT  |532 |JONES  |
|_________________________|

Desired Result

(Modified Existing Rows?)
|BMW  |SILV  |303 |JOHNS  |

(Added Rows?)
|BUIC |BLAC  |400 |JAMES  |

From the datasets you posted, no. You could add a column "modified" which contains a flag that tells you whether the row was modified or not. That column could be maintained via a trigger on the table, or, in my opinion better, you could encapsulate inserts/updates to that table in a stored procedure and have the procedure maintain that column.


Creating database level user in MYSQL Our MySql production server has two different databases in it.
For reports I want the user to only be able to see reports database and not main database. I created A user  'username@%' for the reports plugin in the server and gave it vie,select,execute privileges on reports db.
 Now when the developer connects to database using those credentials from reports plugin, they report that they are able to see the other database and all it's tables as well.
What should I do to prevent them from seeing the other database. In short: when developer logs in from reporting plugin, they should only see the report database.

you can create user on database level by using the following command:

grant all privileges on MyDB.* to MyUser@'host' identified by 'password';

Note: you can change all privileges to any privileges you want, but you need to make sure its enough for the user to log in and get needed data.


Oracle expdp puts INDEXES to LOGGING mode I've created a table in schema LARRY

create global temporary table TT1 (N number);
create index TT_IX1 on TT1 (N);

then export it, and try to import in schema LARRY2:

expdp LARRY/LARRY   directory=DATA_PUMP_DIR dumpfile=01.dmp logfile=01.dmp.exp tables=TT1
impdp LARRY2/LARRY2 directory=DATA_PUMP_DIR dumpfile=01.dmp logfile=01.dmp.imp remap_schema=LARRY:LARRY2 transform=DISABLE_ARCHIVE_LOGGING:Y,oid:N

But I've got an error in Oracle 12c:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX:"LARRY2"."TT_IX1" failed to create with error:
ORA-14451: unsupported feature with temporary table
Failing sql is:
 ALTER INDEX "LARRY2"."TT_IX1"  LOGGING

Also I've checked

SELECT force_logging FROM v$database;

It gives NO.

I tried to search for this problem in the Internet but didn't find the answer. I'm curious why do Oracle trying to put INDEXES (and indexes of TEMPORARY tables too) to the LOGGING mode? And how to fix it?

This is caused by transform=DISABLE_ARCHIVE_LOGGING:Y. Because of that parameter, the database creates indexes with NOLOGGING property to avoid generating redo, and at the end, it changes the index to the original value, which is in this case LOGGING.

But for indexes on GTTs, specifying NOLOGGING or LOGGING is not supported.
Data Pump manages to create the index without specifying NOLOGGING (because that would not make sense), but at the end, it still tries to change its property to LOGGING.

This bug was reported more than 2 years ago, and it is said to be fixed in 12.2.

  Bug 18366521 : IMPDP ENDS WITH ERROR USING TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

I have just tried this in a 12.2 instance, and it is indeed fixed.

A workaround is to handle the GTTs seperately, without specifying transform=DISABLE_ARCHIVE_LOGGING:Y.


Oracle DATE column - How to convert to UTC I have an oracle 10g table with a DATE column type.  I need to compare it in a where clause to a value generated by a script that looks like this:  TIMESTAMP '2016-05-12 08:00:00.258000 +00:00'.  I cannot control the generated TIMESTAMP part, and when I do:

RECORDEDTIME >= TIMESTAMP '2016-05-12 08:00:00.258000 +00:00' it uses the localtime of the RECORDEDTIME which means I am 4 hours off.  What function do I call on RECORDEDTIME to get it in UTC matching the other value in the WHERE clause (i.e. it needs to subtract 4 hours as I'm in EST)


Why is Postgres on 64-bit CentOS 6 significantly slower than Postgres on 32-bit CentOS 6 We have some Postgres + PostGIS applications that run well on CentOS 6 32-bit machines.

We've recently been testing them on CentOS 6 64-bit machines, with similar configuration (all our machines are managed by Puppet), and the applications run significantly slower.

Even loading the database schemas take several times as long. (On the 32-bit machines, this takes 7 seconds to load PostGIS, the schema, and fixtures; on the 64-bit machines, this takes 50-110 seconds.)

We initially had the problems with virtual servers, so ran tests on a physical machine and found the same problems. Note that the physical 64-bit machine is slower than virtual 32-bit machines.

The databases are not large at all.

We've experimented with various parameters on postgresql.conf and not gotten any improvement.

Is this a known issue with Postgres or PostGIS on 64-bit CentOS?

If not, how do we diagnose this?

Start by using a current PostgreSQL version, preferably a PGDG build. See http://yum.postgresql.org.

Then compare with the same hardware.

There is no known significant performance difference with 64-bit vs 32-bit PostgreSQL on non-Windows platforms.

Personally, I suspect that the 64-bit CentOS might have transparent huge pages enabled, which does cause severe performance regressions with PostgreSQL. See https://dba.stackexchange.com/a/34169/7788, this mailing list post, and Google.

You might also be having issues with the zone reclaim mode for NUMA; Linux's default works very poorly for databases that use shared memory. Set vm.zone_reclaim_mode = 0 in sysctl.conf and apply the changes.


How one can install a given R package in SQL Server I was asked to write a R code to do computations. I managed to do it such that it works well in R. Next, I was asked to do the same function in SQL Server. I was unable to do it as it requires a package psych that SQL Server won't find. How can I get R running properly in SQL Server such that it find the package psych? Microsoft SQL Server Management Studio v17.0 outputs

  An external script error occurred:
  Error in library(psych) : there is no package called 'psych'
  Calls: source -> withVisible -> eval -> eval -> library`

How one can install a given R package in MSSQL?

See installation of R services step by step Here & Here

  How can I get R running properly on MSSQL?

So far, we have installed the components required for running R Services; now to enable the R Services feature itself you need to explicitly enable it with the following command before you can invoke R scripts in SQL Server.

--Execute this script to verify if R Services in enabled

EXEC sp_configure  'external scripts enabled';
Go

To Verify R services

--Execute this script to enable R Services on the SQL Server   instance

EXEC sp_configure  'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE
Go

--Execute this script to verify if R Services in enabled

EXEC sp_configure  'external scripts enabled';
Go

To Verify R services

Finally, you need to restart SQL Server service, which will automatically restart the related SQL Server Trusted Launchpad service (it runs advanced analytics extensions to enable integration with Microsoft R Open using standard T-SQL statements; disabling this service will make the Advanced Analytics features of SQL Server unavailable) as well for executing external R scripts.

At this time, if you execute the following command again, you will notice 1 for both config_value and run_value, which means you are not good to execute R script on this SQL Server instance:

--Execute this script to verify if R Services in enabled

  EXEC sp_configure  'external scripts enabled';
  Go

SQL Server 2016 includes a new system stored procedure (sp_execute_external_script) to enable you to execute external scripts inside the database engine. The stored procedure loads and executes a script written in a supported language from an external location.

For your further ref here


PostgreSQL select count with dynamic date range Firstly, I am new to this forum. I hope that someone can help or advise me on my issue.

Given a User createdAt date and the current date. I would like to retrieve the sum of Friends records for the user with 10 linear time intervals between these dates.

So if the createdAt was 30 days ago then I want the sum of users Friends over EVERY 3 day interval.

User Table

╔════╤══════════╤════════════════════════════╗
║ id │ name     │ createdAt                  ║
╠════╪══════════╪════════════════════════════╣
║ 1  │ Timothee │ 2018-03-01 13:02:20.904+00 ║
╚════╧══════════╧════════════════════════════╝

Friends Table

╔════╤════════╤══════════╤════════════════════════════╗
║ id │ userId │ friendId │ createdAt                  ║
╠════╪════════╪══════════╪════════════════════════════╣
║ 1  │ 1      │ 234      │ 2018-03-20 15:41:51.779+00 ║
╟────┼────────┼──────────┼────────────────────────────╢
║ 2  │ 1      │ 254      │ 2018-03-20 16:16:34.698+00 ║
╟────┼────────┼──────────┼────────────────────────────╢
║ 3  │ 1      │ 288      │ 2018-03-15 15:17:39.907+00 ║
╟────┼────────┼──────────┼────────────────────────────╢
║ 4  │ 1      │ 293      │ 2018-03-07 16:15:49.379+00 ║
╚════╧════════╧══════════╧════════════════════════════╝

Expected Output

╔═╤═══════╤════════════════════════════╤════════════════════════════╗
║ │ count │ startDate                  │ endDate                    ║
╠═╪═══════╪════════════════════════════╪════════════════════════════╣
║ │ 0     │ 2018-03-01 13:02:20.904+00 │ 2018-03-03 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 0     │ 2018-03-03 13:02:20.904+00 │ 2018-03-05 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 0     │ 2018-03-05 13:02:20.904+00 │ 2018-03-07 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-07 13:02:20.904+00 │ 2018-03-09 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-09 13:02:20.904+00 │ 2018-03-11 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-11 13:02:20.904+00 │ 2018-03-13 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 1     │ 2018-03-13 13:02:20.904+00 │ 2018-03-15 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 2     │ 2018-03-15 13:02:20.904+00 │ 2018-03-17 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 2     │ 2018-03-17 13:02:20.904+00 │ 2018-03-19 13:02:20.904+00 ║
╟─┼───────┼────────────────────────────┼────────────────────────────╢
║ │ 4     │ 2018-03-19 13:02:20.904+00 │ 2018-03-21 13:02:20.904+00 ║
╚═╧═══════╧════════════════════════════╧════════════════════════════╝

I can get the desired day interval with the following SQL-Server Query:

@DECLARE @EndDate DATE = GETDATE();
WITH start as
(SELECT createdAt
 FROM user where id = '123'
)
SELECT diff/10
FROM (
    SELECT DATEDIFF(days, start, @EndDate)
) as diff

How would this be in PostgreSQL?

I also know that I can retrieve the count of records between a static date range with SQL-Server Query:

SELECT count(*)
FROM friends
WHERE userId = '123'
AND date BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()

How would this be in PostgreSQL?

But what I am struggling with is the combining of these two things.

How can I retrieve the sum of records over ALL date intervals between the two dates?

I hope the question is understandable, thank you for any help or advice.

Postgres allows to generate a series of dates using generate_series() function.

select generate_series(createdAt, current_date,
       (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
       , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
from   users
where  id = 1;

It returns:

ddate                   | ddays
:---------------------- | :-----
2018-03-01 13:02:20.904 | 2 days
2018-03-03 13:02:20.904 | 2 days
2018-03-05 13:02:20.904 | 2 days
2018-03-07 13:02:20.904 | 2 days
2018-03-09 13:02:20.904 | 2 days
2018-03-11 13:02:20.904 | 2 days
2018-03-13 13:02:20.904 | 2 days
2018-03-15 13:02:20.904 | 2 days
2018-03-17 13:02:20.904 | 2 days
2018-03-19 13:02:20.904 | 2 days

Then you can use previous query to count friends between this intervals:

with x as
(
    select generate_series(createdAt, current_date,
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       (select count(*)
        from   friends
        where  createdAt >= ddate
        and    createdAt <  ddate + ddays) friends
from x;

Final result:

ddate                   | friends
:---------------------- | ------:
2018-03-01 13:02:20.904 |       0
2018-03-03 13:02:20.904 |       0
2018-03-05 13:02:20.904 |       0
2018-03-07 13:02:20.904 |       1
2018-03-09 13:02:20.904 |       0
2018-03-11 13:02:20.904 |       0
2018-03-13 13:02:20.904 |       0
2018-03-15 13:02:20.904 |       1
2018-03-17 13:02:20.904 |       0
2018-03-19 13:02:20.904 |       2

Or if you prefer a join instead of a correlated scalar subquery:

with x as
(
    select generate_series(createdAt, current_date,
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends
from   x
left join   friends
on     createdAt >= ddate
and    createdAt <  ddate + ddays
group by ddate
order by ddate;

This last option can be further modified to returned running totals, as per your expected output:

with x as
(
    select generate_series(createdAt, current_date,
           (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval) ddate
           , (date_part('day', current_date::timestamp - createdAt::timestamp) / 10)::int * '1 day'::interval as ddays
    from   users
    where  id = 1
)
select ddate,
       count(friends.*) as friends,
       sum(count(friends.*)) over (order by ddate) as friends_total
from   x
left join   friends
on     createdAt >= ddate
and    createdAt <  ddate + ddays
group by ddate
order by ddate;

Result:

ddate                   | friends | friends_total
:---------------------- | ------: | ------------:
2018-03-01 13:02:20.904 |       0 |             0
2018-03-03 13:02:20.904 |       0 |             0
2018-03-05 13:02:20.904 |       0 |             0
2018-03-07 13:02:20.904 |       1 |             1
2018-03-09 13:02:20.904 |       0 |             1
2018-03-11 13:02:20.904 |       0 |             1
2018-03-13 13:02:20.904 |       0 |             1
2018-03-15 13:02:20.904 |       1 |             2
2018-03-17 13:02:20.904 |       0 |             2
2018-03-19 13:02:20.904 |       2 |             4

dbfiddle here


How often does SQL Server refresh group policy? Does SQL Server check against the Windows GPO on the local server it is running on or with the domain controller?
Does SQL Server update the GPO at its own interval or immediately when the server has its GPOs applied? If it has its own interval, is there a specific refresh rate? I am having difficulty finding docs on this so I figured I would ask the community.
Thanks

SQL Server doesn't really interact directly with a domain controller, or even read group policies directly.   That is all handled by Windows.


Parameter value in Data Factory Triggered pipeline I have a pipeline configured in azure data factory which basically does create a backup file (JSON) from a cosmosDB dataset and it's saved in  a blob storage, my problem comes when I want to schedule the copy task in a trigger, I see that I have to specify the value for windowStart (parameter already defined to name the JSON file with the date of the execution. ) as shown below:

Destination path:

And as you can see when I want to trigger it does ask me to specify the value for windowStart.

I tried below functions to specify that with no success so far:

"@{pipeline().parameters.windowStart}"
"@{formatDateTime(pipeline().startTime,'o')"
"@{formatDateTime(utcnow(),'yyyy-MM-dd')}"
"@{formatDateTime(trigger().startTime,'yyyy-MM-dd')}"

And the error I always get is as shown below:

Have anyone any idea or way to fix that, or have someone faced the same issue before ?

Finally I was able to fix this by creating the trigger using a JSON code as shown below:

{
    "name": "yourTriggerName",
    "properties": {
        "runtimeState": "Started",
        "pipelines": [
            {
                "pipelineReference": {
                    "referenceName": "YourPipelineName",
                    "type": "PipelineReference"
                },
                "parameters": {
                    "windowStart": "@trigger().scheduledTime"
                }
            }
        ],
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Day",
                "interval": 1,
                "startTime": "2018-07-11T17:00:00Z",
                "timeZone": "UTC",
                "schedule": {
                    "minutes": [
                        20
                    ],
                    "hours": [
                        19
                    ]
                }
            }
        }
    }
}

And making emphasis of course, in line below:

"parameters": {
                        "windowStart": "@trigger().scheduledTime"

After that, the copy activity started to work as expected.


VIM returns "Error detected while processing" and "Sorry, the command is not available in this version" When I run VIM with \e inside of psql, I get

Error detected while processing /home/ecarroll/.vimrc:
line   11:
E319: Sorry, the command is not available in this version: call plug#begin('~/.vim/plugged')

However I don't get this error outside of PSQL. Further visual block mode in VIM doesn't work when I try to replace.

Single-User Solution

Run select-editor and select the proper version of vim. This is what psql does on initial run. This will drop a file ~/.selected_editor which will be used by psql when it calls sensible-editor.

Multi-User Ubuntu/Debian

If you're on Debian it's best to set it system-wide, especially if you're the only user on the system.

Have psql delegate to the system. To configure psql to use your default editor, run

echo SELECTED_EDITOR="editor" > ~/.selected_editor

Configuring Debian and Ubuntu system-wide for the right editor, simply run

update-alternatives --config editor

Or set it your self,

update-alternatives --set editor /usr/bin/vim.gtk3

Checking

You can always check what version of vim you're using by issuing :version, this should say something like this

Huge version with GTK3 GUI.

If it doesn't, and it instead says

Huge version without GUI.

Or,

Small version without GUI.

You're likely without the needed features. Visual block replace specifically requires -vreplace


Distribute database elements I have  questions regarding the distributed database elements and environment :

What is the exact meaning of a transaction processor and where does it exists in a distributed environment ?

What is the exact meaning of a data processor and where does it exists in a distributed environment ?

where does a DDBMS exists in a distributed database environment ?

Your question is a little generic / open ended but here's the best I can answer your questions:

"Transaction Processor" is dedicated to tracking the transactions that occur in the primary database on one server so that it can keep distributed databases in sync with the primary database via the "Data Processor".

It can exist on the primary server or any of the other servers, but usually lives on the primary.

The "Data Processor" is the distributor of the data changes from the primary database to the distributed secondary databases. It's job is to actually move the transactions (or data) from the primary source server and sync it to the distributed secondary servers. It also can live on any of the databases (or its own database) and is usually chosen based on performance reasons.

DDBMS stands for Distributed Database Management System. So it is the very concept / architecture you're asking about. It is composed of multiple databases across multiple servers (usually one server being the primary and the other servers being secondaries). The job of DDBMS is to keep multiple copies of the same database across multiple servers in sync with each other.


What is connection time? We are currently trialling ManageEngine  SQLDBManager Plus. On the dashboard for a server it shows connection time in milliseconds (currently 15ms).

Can anyone tell me what Connection Time represents and/or what I could use this metric for?

We are monitoring a MS SQL Server 2005 database if that makes any difference.

As per the help section on their website, they describe Connection Time as the following:

  Connection Time: Displays the time taken to get connected to the SQL database server.

From that brief description, I'd be willing to say that it is the time duration between initiating the connection to the SQL Server instance.

If this is the case, then the impacts here would be the physical network latency and the server capabilities for a client response.  For further investigation into this metric, I'd consult the actual vendor for more details and acceptable statistics.


Is there a way to get a history of transaction log used space in SQL Server? I would like to be able to look back and see what the used space was in transaction log over a given period. Not interested in growths or file size as they don't tell me how much space is actually being used.

I am switching from simple model to full recovery model and need an idea of how large the transaction log will get.

Nothing is logged by default, generally speaking. So, unless you have something keeping track of the amount of log generated, you are out of luck.

Ordinarily, we would recommend you to check the size of your log backups, but as I understand it, you don't have those and the purpose here is to determine how much space you would need for those. Catch 22. :-)

Even with some active monitoring, you would have to check this pretty frequently, since you never know when checkpoint kicks in and "truncate the log". There's a perfmon counter you can use for this: "Log File(s) Used Size (KB)". And you can grab the value from sys.dm_os_performance_counters. I.e., pretty easy to create a job that uses a SELECT to check the used space in the tlog for selected databases, store it in a table and execute that job every minute, or whatever frequency you find suitable.


How do pages get updated checksum when switching page verification mode from torn page to checksum? Have an older but large db (6tb on the san) that still has its page verification option set to TORN_PAGE_DETECTION. How do you apply the new checksum to all existing pages when changing page verification to checksum mode?  Is there any big overhead to be wary of when making this change to page verification mode?  Does this need to be done during a long maintenance window?
SQL 2014
Current DB Compat Level = 120

It won’t happen all at once. Each page will get changed independently when it is modified through some other action (e.g. insert or update).
This was done precisely so that this change would not be a size-of-data operation.
Of course you could force it to be immediate for a given table by rebuilding, but the big cost there would be the rebuild, since you’re writing all of the page contents anyway. Switching those pages to checksum as a part of the process is an almost-zero-cost add-on, like how much gas mileage you lose if you add a kid to the carpool.


Given an arbitrary pair of origin & destination, how do I design a system that can effectively query all paths that cross the pair from a GPS dataset? Data
I have a data set containing more than 8000 taxi drivers' GPS information, including time stamp, longitude, latitude, speed.

Objective
I intend to design a system which can query all taxi trajectories that cross any given pair of origin and destination and output these trajectories effectively.

Question
The queried origin-destination pair is random, lengths of trajectories (number of the pairs of longitude and latitude) do vary, not very structured, how could I achieve the objective?

A DB with spatial capabilities built-in should be able to do that. One option would be PostGIS. Check out this link for example queries using it.


SQL Developer text of status bar My version of SQL Developer (4.0.2.15) has white text on a light blue background (see below). I have been unable to find in the preferences to change this so that it is more readable. Does anybody known how to make the text a difference colour.

SQL Developer 3.x had a "theme" option, but that's been removed in 4.0, so no luck there.

You could try changing the "Look and Feel" option to Windows (I don't know what you get on other OSes).

Settings here:

Result should match your OS color scheme. I get this for example, which is a bit more readable than the default white on blue:


SQL Server Instance Root Directory & Shared Feature Directory on Partition with 64k Allocation Unit Size? I am in the process of setting up new test servers to migrate to SQL Server 2017. For certain reasons I dont have the option to have a separate drive partition for just SQL binaries so my options are to put the SQL binaries on C:\ with default allocation unit size or G:\ with 64k allocation unit size along with the data files.

I would prefer to avoid C:\ and put the binaries on G:\ but am not sure if there would be any negative performance impact with them being on a disk with 64k format.

Is one better than the other or does it not really make any difference for the binaries?

No it does not make any practical difference for the binaries. Unlike the data files, the binaries are largely loaded to memory on start up and not read from disk again during normal operation.


Can't define a foreign key In MySQL I've two tables:

Monitors:
    Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
    GroupId: INT NULL
    ....

MonitorGroups:
    Id: INT PRIMARY KEY NOT NULL AUTOINCREMENT
    Name: VARCHAR(50)

I need to define this constraint:

ALTER TABLE Monitors ADD CONSTRAINT monitor_group_fk FOREIGN KEY (GroupId) REFERENCES MonitorGroups (Id);

But I get the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`zm`.`#sql-475_1c237`, CONSTRAINT `monitor_group_fk` FOREIGN KEY (`GroupId`) REFERENCES `MonitorGroups` (`Id`))

At the moment there's no record in MonitorGroups table and GroupId of rows of table Monitors are not set. I need it to be legal. In another words, I need MySQL to keep track of the field GroupId of table Monitor only if it's set. I would like to define the constraint as:

ALTER TABLE Monitors ADD CONSTRAINT monitor_group_fk FOREIGN KEY (GroupId) REFERENCES MonitorGroups (Id) ON UPDATE CASCADE ON DELETE SET NULL;

so that if a group id (Id field) changed in table MonitorGroups, MySQL change it automatically in table Monitors and if a group was deleted from table MonitorGroups, MySQL automatically set GroupId of corresponding rows in table Monitors to null.
What's wrong and how to reach this goal?

There is da ROW in the table Monitors, which has a value in the column "GroupId" for which the foreign key constraint fails (=it does not exist in the table MonitorGroups).
Check if there is a GroupId whithout a corresponding Id-Value in table Groups.


What are all these databases with funny names? I help support SQL Server installation that hosts SharePoint and various related services. I never really looked at the database side of things until today when I noticed a whole bunch of databases with what look like GUIDs appended onto the database name. Can someone explain what these are? I tried to google for it but I didn't know what to google for and nothing came up from obvious google searches.

Sharepoint System Databases. And they are pretty self explanatory, or? Search service, secure store, session state - easy to understand.

THe rest is a GUID that is used in case multiple instances are installed - the Guid makes the database name unique.


Creat db snapshot of a db in restoring state Is it possible to create a snapshot of a SQL db that is in a restoring state?

No, you cannot. The database has to be online.

As Scott referenced


ORA-01406 while building a Sphinx index We are getting ORA-01406 error while trying to build an index over Oracle DB using Sphinx:

root@bdt:~# ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/ NLS_LANG=AMERICAN_AMERICA.UTF8 indexer bsrpIndex --rotate
Sphinx 2.2.6-id64-release (r4843)
Copyright (c) 2001-2014, Andrew Aksyonoff
Copyright (c) 2008-2014, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'bsrpIndex'...
ERROR: index 'bsrpIndex': sql_fetch_row: [Oracle][ODBC][Ora]ORA-01406: fetched column value was truncated
.
total 648 docs, 16082835 bytes
total 18.330 sec, 877377 bytes/sec, 35.35 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
root@bdt:~#

We are using Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.
We understand that this error relates to the buffer size on client side but we are not sure how to configure it. We are using the ODBC driver. Basically, two fields that prevent the Sphinx index to be created are of VARCHAR2(4000) and VARCHAR(100) types. If we remove them from the query in Sphinx config the index builds without an error.

So, the question is: what can we do to extend this client side buffer to handle big columns data properly?

I resolved the very same error by changing:

NLS_LANG=AMERICAN_AMERICA.UTF8

to

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

After this i got another subsequent error:

WARNING: '...' column truncated (buffer=..., got=...); consider revising sql_column_buffers

I've fixed it as described in Sphinx Documentation


Issue using COPY command with AWS Redshift I am trying to copy a CSV file from my PC, which has SSH running, to AWS Redshift.  I have copied the Redshfit public key to authorized_keys for the appropriate user.

copy contact (id,name)
from 's3://mybucket/m1.mfs'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=yyy''
region 'us-west-2'
csv ssh

The manifest file, which has been uploaded to S3, is as follows:

{
"entries": [
{"endpoint":"73.29.89.30",
"command": "/bin/cat /home/user23/rfr/src/redshift/Contact2.csv",
"mandatory":true,
"username": “user23"}
]
}

The error I get is:

  Error: ERROR: Manifest file is not in correct json format**

From what I can see, the file is in the correct format. I think the issue may be something else, but how do I debug this? The endpoint is valid, the command is valid, and the username is valid.

Perhaps the JSON you have is not quite correct. When I paste your JSON into jsonlint.com I get the following:

Parse error on line 7:
...        "username": “user23"} ] }
-----------------------^
Expecting 'STRING', 'NUMBER', 'NULL', 'TRUE', 'FALSE', '{', '['

The AWS Redshift documentation on COPY SSH parameters has the following

  The quote characters must be simple quotation marks (0x22), not slanted or "smart" quotation marks.

So double check you quote marks around the username you've provided, and if they are how you've provided them above, change it from to “user23" >> "user23" (note the first quote mark is different).

The first quote mark in your code is unicode : U+201C and not U+0022


Trace unclosed connections I get timeout errors every couple of days or so between IIS7 and an SQL 2008 Server. The error message I get is:

System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Sometimes this sorts itself out, other times I need to manually restart SQL Server. Is there any way I can trace down the cause of this error. I'm 90% sure it's somewhere without our ASP.NET webforms app where a connection is being opened and not closed, but I'm at a loss to pin down the cause.

You can query sys.dm_exec_sessions on the last_request_end_time column to find open yet "sleeping" connections.

From this, you can find out the last SQL executed

SELECT session_id, TEXT
FROM sys.dm_exec_connections c
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS ST
WHERE c.session_id = <suspicious one from sys.dm_exec_sessions>


AVG function returns wrong average I have a simple table with a column of type REAL.

There are 2 rows in the table and the values are 0.23 and 0.24. If I run the following:

SELECT AVG(MyColumn) FROM dbo.MyTable

I would expect to get a result of 0.235 but actually I get 0.2349999901234.

Can somebody please tell me why?

Stop using REAL and use DECIMAL instead. REAL and FLOAT are approximate data types and can't represent all numbers in the nice, rounded way you are used to. Compare these:

DECLARE @a REAL = 0.23, @b REAL = 0.24;
SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x;

Result:

0.234999999403954

Now using DECIMAL instead:

DECLARE @a DECIMAL(10,4) = 0.23, @b DECIMAL(10,4) = 0.24;
SELECT AVG(r) FROM (SELECT r = @a UNION SELECT r = @b) AS x;

Result:

0.235000

Some related reading:

http://web.archive.org/web/20130205023610/http://classicasp.aspfaq.com/general/why-does-3-2-1-5-4-7000000000000002.html
http://web.archive.org/web/20120208202619/http://databases.aspfaq.com/database/what-datatype-should-i-use-for-numeric-columns.html
https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type

Decimal is not perfect, either; you can lose precision if you need more decimal places than supported, for example. But for most applications this is not an issue.


Finding the same value in a column and only returning rows without the same value I have a query that i have to find the DiscountPercent that are the same and not show the rows with the same DiscountPercent. I have tryd this and it brings back the values that are the same

SELECT
    ProductName,DiscountPercent
FROM
    Products
WHERE
    DiscountPercent in (SELECT DiscountPercent
                        FROM Products
                        GROUP BY DiscountPercent
                        HAVING COUNT(*) < 1)
ORDER BY
    ProductName;

What i want to accomplish is to bring in the products that don't have the same discount percentage. I don't think i am doing this the right way.What is the correct way of doing this?

This is some data in the ProductName and in the DiscountPercentage

ProductName        DiscountPercent
Fender Precision     30.00
Fender Stractocaster 30.00
Gibson Les Paul      30.00
Gibson SG            52.00
Hofner Icon          25.00
Ludwig 5-piece       30.00
Rodriguez Cabelle    39.00
Tama 5-piece         15.00
Washburn D10         0.00
Yamaha FG700s        38.00

The Desired result that must use a subquery

ProductName       DiscountPercent
Fender Precision    30.00
Gibson SG           52.00
Hofner Icon         25.00
Rodrgiguez Cabelle  39.00
Tama 5-piece        15.00
Washiburn D10       0.00
Yamaha FG700s       38.00

SELECT MIN(ProductName), DiscountPercent
   FROM Products
   GROUP BY DiscountPercent;

You might find the output from this interesting:

SELECT DiscountPercent,
       GROUP_CONCAT(ProductName)
   FROM Products
   GROUP BY DiscountPercent;


How to recover data from corrupted SQL Server database? We had several power outages and server rebooted couple times which seemed to cause issues with one of the databases.  We tried detaching at attaching database again but it looks like database is corrupted and we’re getting the

Msg 5172, Level 16, State 15, Line 1

Claiming that database header is invalid. Is there anything we can to do repair database or extract data from MDF file?  We do have a backup but it’s like 2 weeks old and doesn’t contain all data.

I’d try the procedure described below or third party tool such as ApexSQL Recover if that doesn’t work and database is severely corrupted.

There might be other options out there. This is something that worked for me once but there are no guarantees that it will work at all times.

Stop SQL Server instance -> Copy MDF and LDF files to another location -> Delete original MDF and LDF files -> Start SQL Server instance again -> Create new database with exact same name and file names -> Stop SQL Server -> overwrite newly created MDF and LDF.

After this your database should be back online. If it is then go ahead and put it into EMERGANCY mode and SINGLE USER mode.

Finally go ahead and execute DBCC CHECKDB like this

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

If you can get to this and execute last command successfully you should be good. If not then your only options are restoring from backup and/or using 3rd party tools.


How do I find the first row of the longest occurrences of repeating numbers? I have a table with the following structure:

id,center,shelf_id,occupied,remaining
1,East,1_1_1,1,0
2,East,1_1_2,1,0
3,East,1_1_3,1,4
4,East,1_1_4,1,0
5,East,1_1_5,1,0
6,East,1_2_1,1,3
7,East,1_2_2,1,0
8,East,1_2_3,0,4
9,East,1_2_4,0,4
10,East,1_2_5,0,4
11,East,1_3_1,0,4
12,East,1_3_2,0,4
13,East,1_3_3,0,4
14,East,1_3_4,0,4

I would like to find the beginning of the longest string of repeating numbers from that position to the end of the table.

For the example above, the mysql statement should return row id of 8 because from row 8 to the end of the table the value in the occupied and remaining columns are 0 and 4.

Any help on writing this as a mysql select statement would be greatly appreciated!

For MySQL version 8+

WITH
cte1 AS
(
SELECT id,
       CASE WHEN     occupied  = LAG(occupied) OVER (ORDER BY id)
                 AND remaining = LAG(remaining) OVER (ORDER BY id)
            THEN 0
            ELSE 1
            END values_differs
FROM test
),
cte2 AS
(
SELECT id,
       SUM(values_differs) OVER (ORDER BY id) group_num
FROM cte1
ORDER BY id
)
SELECT MIN(id)
FROM cte2
GROUP BY group_num
ORDER BY COUNT(*) DESC LIMIT 1;

fiddle


IN ( SELECT ... ) has always been poorly optimized in MySQL.  There were improvements in 5.6, but it does not seem to cover your case.  File a bug with http://bugs.mysql.com if you like.

It is often better to change to a JOIN since it optimizes better.  (I assume your real query is more involved, and you simplified it for this Forum.)

You can verify whether it does a table scan:

FLUSH STATUS;
perform the operation
SHOW SESSION STATUS LIKE 'Handler%';

Meanwhile, the "rows" in EXPLAIN is pessimistic in some situations.  I would trust the Handler counts when in doubt.  With 15 rows instead of 10, I got (excluding 0 values):

| Handler_commit             | 1     |
| Handler_external_lock      | 4     |
| Handler_read_first         | 1     |
| Handler_read_key           | 18    |
| Handler_read_rnd_next      | 16    |
| Handler_update             | 2     |
| Handler_write              | 2     |

That smells like a tmp table with 2 rows, plus a full scan (maybe twice?) of 15 rows, plus some other miscellany.


Procedure to find a specific EXPRESSION in all tables in a specific schema in ORACLE Hope you're doing well.
Since I'm new to Oracle database and PL/SQL it's a bit difficult for me to understand complicated queries and now I'm trying to figure out what this procedure exactly does.I think it tries to find a specific expression but I do not understand the way  it tries to do that
This is the procedure body:

    create or replace procedure find_expr is
      a number;
      begin
        for rec in (select 'select count(*) from ' || table_name || ' where ' ||
                            column_name || ' like ''%05510%''' col_name
                    from user_tab_columns t) loop
    EXECUTE IMMEDIATE rec.col_name
      into a;

    if a > 0 then
      insert into find_expr_log values (sysdate, substr(rec.col_name, 22, 50));
      commit;
    end if;

  end loop;
exception
  when others then
    null;
end;

I have a different approach to the same.

Adding the answer on top of the correct answer, just to reduce the manual work.

The following query will directly identify the number of rows in each table which is matching the criteria.

select table_name, column_name,
  to_number(xmlquery('/ROWSET/ROW/C/text()'
    passing xmltype(dbms_xmlgen.getxml(
      'select count(1) as c '
      || 'from ' || table_name || ' WHERE ' || column_name || ' like ''%05510%'''))
  returning content)) as c
from user_tab_columns
WHERE DATA_TYPE IN ('VARCHAR2','NUMBER')  -- ALL DATATYPES YOU WANT TO SEARCH
ORDER BY 3 DESC;

Cheers!!


Will replication commands build up if the distribution agent is stopped? Assuming I have a transactional publication with a single article. There is a Publisher (PUB1) with two Subscribers (SUB1 AND SUB2), and a separate Distributor (DIST1) box is used. I would like to stop replication to one subscriber (SUB1), while the other continues to process commands (SUB2). Additionally, I would like to be able to start replication back up to SUB1 and have it pick up where it left off.

Based on this MSDN page, I would have Snapshot, Log Reader, and Distribution agents. I know I can stop the Distribution agent for SUB1 only and SUB2 will continue to get/process replication commands.

The thing that is unclear is what happens to the commands destined for SUB1. From my test, the commands seem to be applied when I start the Distribution agent. I'm concerned that they could get deleted from DIST1 after a set amount of time, even though all subscribers have not received the commands (yet).

So would the commands build up on DIST1 until both SUB1 and SUB2 are up to date and running, or is there the potential for commands to be lost? Also, is there a point at which I can't simply start the Distribution agent back up (i.e. does SQL Server decide at a certain point that it must re-initialize the articles in order to start synchronizing again)?

Yes, the transactional replication data will build up while waiting for distribution to start again.  Of course, if not tracked it is possible that a long delayed set of transactional replication data can fill the log space that you have available, thus causing you problems.

Replication is hostage to the resources available, including the log space since the replication data is read from the transaction log.

For what it is worth, we once kept a replication open for a couple of days until a network problem could be resolved.  (Plenty of log space at that time.)

If the replication process fails (for any reason) that would cause loss of data then you need to redo the steps to establish the replication landscape.


in-memory SQL database system In an application, I am using mysql's TEMPORARY TABLE. Since this application does not need permanent tables, I wonder if I can find an alternative RDMS designed for memory tables. I hope to find an in-memory database system, which is

Lightweight
Faster than mysql temporary table
Having an API for C (my programming language)

and no special features is needed, just creating table and performing SQL queries. FK or other advanced features are not needed.

You have two options
OPTION #1 : Create a RAM Disk
RAMDISK_SIZE=32g
service mysql stop
mkdir /var/tmpfs
echo "none   /var/tmpfs  tmpfs  defaults,size=${RAMDISK_SIZE} 1 2" >> /etc/fstab
mount -t tmpfs -o size=${RAMDISK_SIZE} none /var/tmpfs
cp -R /var/lib/mysql/* /var/tmpfs
mv /var/lib/mysql /var/lib/mysql_old
ln -s /var/tmpfs /var/lib/mysql
chown -R mysql:mysql /var/tmpfs
chown -R mysql:mysql /var/lib/mysql
service mysql start

If this does not work, you can reverse it out
service mysql stop
rm -f /var/lib/mysql
mv /var/lib/mysql_old /var/lib/mysql
service mysql start

You can set the RAMDISK_SIZE to your liking
OPTION #2 : Use FUSION IO
Mount /var/lib/mysql on a FusionIO Disk (all memory, CPU aggressive).
Have fun clearing this with your CFO.
EPILOGUE
Both of these options allow you to use MyISAM and InnoDB as you normally would. The goal is simply to place the entire datdair in RAM.
Give it a Try !!!


SSMS 2012 - loaded saved query not executable ("invalid object name" error) I have multiple saved queries (each one was working fine when I developed them).
But when I load them in SSMS they immediately show error in every table name that's in the script. Moreover, I can't select the relevant DB from "Available databases" box (or any DB on that server, for that matter) and USE [DB_NAME] command does not work, too.

However, if I'll navigate in "Object Explorer" to any table in any DB on the Server I want and do even a simple Select Top 1000, then paste in whole query that's not working - voila - it runs fine.

Is it important for this that I'm connected to multiple SQL servers on different machines at the same time? In case it's needed - all SQL Server 2012.

Does anyone have a thought as to what's happening? It's extremely annoying to go through all the above steps each time I need a query ran. Thanks!

You might need to add "GO" after your "USE [DB_NAME]" statement.
Some commands need to be first in the batch.
With GO, the USE DB command is executed as first batch, with another batch following it.

So:

USE [DB_NAME]
GO

CREATE PROCEDURE...


Does any SQL engine have navigation support in CHECK clauses? Imagine a simple database to track personal spending. The most basic form of the model should let you create any number of accounts and any number of transactions (specifying the spending amount, date and associated account).

Also, some transactions may be part of a transfer between different accounts and not a legitimate expense/income on its own (for instance a money cash out from your checking account), these should be linked together somehow. I could start modelling the application with the following schema:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY
);

CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  amount INT NOT NULL,
  date DATE NOT NULL,
  account INT NOT NULL REFERENCES accounts
);

CREATE TABLE transfers (
  outgoing_tx INT REFERENCES transactions,
  incoming_tx INT REFERENCES transactions,
  PRIMARY KEY (outgoing_tx, incoming_tx)
);

Now comes the question part. When I find myself modelling a domain like this I tend to heavily rely on CHECK clauses to further constrain the schema to the domain and ban unwanted values/states at runtime.

For instance, in this example I wouldn't care for empty transactions:

ALTER TABLE transactions
ADD CONSTRAINT no_pointless_txs CHECK (amount <> 0);

However, when it comes to rules that involve more than a single nice little row I always feel like I need to write too much boilerplate code. For transfers you would want to check that the transactions they point to belong to different accounts, are from the same day and have the same amount, except outgoing_tx must be negative and incoming_tx positive.

This is more or less what I would like to write, i.e. I'd like to navigate through foreign keys to access other table columns by doing select/joins under the hood:

ALTER TABLE transfers
ADD CONSTRAINT no_same_accounts
  CHECK (outgoing_tx->account->id <> incoming_tx->account->id),

ADD CONSTRAINT no_different_dates
  CHECK (outgoing_tx->date = incoming_tx->date),

ADD CONSTRAINT correct_balance
  CHECK (outgoing_tx->amount < 0 AND 0 = outgoing_tx->amount + incoming_tx->amount);

Instead, every time I want to enforce rules like these I have no option but to write lenghty boilerplate PL/SQL functions such as get_date(int) or get_account_id(int) to do the queries manually.

CREATE OR REPLACE FUNCTION get_account_id(tx_id int) RETURNS int AS $$
  SELECT account FROM transactions WHERE id = $1;
$$ LANGUAGE SQL;

-- Every other function...

ALTER TABLE transfers
ADD CONSTRAINT no_same_accounts
  CHECK (get_account_id(outgoing_tx) <> get_account_id(incoming_tx));

-- Every other constraint using its corresponding function...

So I guess I just want to know if there are easier, more mantainable ways to go about this kind of use case, and OCL-style navigation came to my mind.

It appears you are looking for a SQL database that implements the SQL Standard CREATE ASSERTION statement. AFAIK there is no mainstream SQL database that actually supports this.

Therefore, the more complex data integrity rules that you wish to implement must be done so programmatically. This can be done using triggers in the DBMS or procedurally either using stored procedures in the database or within the external application.

However it is implemented, careful consideration needs to be made to ensure the ACID properties are followed to ensure the reliability of the database transactions. In particular this may mean explicit locking mechanisms are employed wherever the constraints are validated.

There is generally also the requirement to ensure that the constraints are enforced as efficiently as possible.

For an in-depth examination of the implementation of complex integrity constraints using DBMS triggers I would suggest reading Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars.


SQL Server Database Keeps Semi-Detaching Itself Forgive me if I am not using all the proper terms but I have databases that will detach themselves from SQL Server. In SQL Server Management Studio the plus sign will be missing next to the database, or the database will have no tables.

Now the fix for this is easy, simply detach and attach the database and we're back up and running. BUT what is the cause for this?

You have "auto close" switched on probably

Turn it off: it isn't a good idea And again

ALTER DATABASE MyProblemDatabase SET AUTOCLOSE OFF;