Processing occasionally fails with "Unexpected end of file has occurred." I've got an Analysis Services database that's used for reporting on email activity. It mainly counts incoming and outgoing messages, who they're to and from, etc.

Fact table: 11,367,910 rows
Address dimension table: 386,015 rows
To-address fact table: 21,303,290 rows (used for many-to-many intermediate measure group)
Date dimension table: 9,132 rows

It's a simple structure, but there's a lot of data in it. The two measure groups have 6 yearly partitions with varying numbers of rows in them. The whole thing takes about 30 minutes to process fully.

But that's not my problem (at least I don't think it is). Seemingly randomly, the scheduled processing task will fail with the error Unexpected end of file has occurred. The following elements are not closed: root, return, ExecuteResponse, soap:Body, soap:Envelope. Line 3707, position 1. The line number can vary, but the error is the same. I've tried hunting around, but I can't find any info on what this indicates. Anybody else run into this error?

I eventually managed to track this down to low memory. I was under the mistaken impression that 32-bit Analysis Services could use AWE memory, but alas it does not. I changed the processing strategy from full processing, to incremental processing of only the appropriate partitions. That seems to have reduced the memory requirements, and resolved the issue (hasn't failed in quite some time now).


MYSQL Datediff between two dates based on year I have two dates date column like '2017-12-29' and '2018-01-05' the datediff between these dates is 8, but i want to know the datediff which fall on 2018 which should be 5. I'm working on mysql.


Innodb_table_lock status can anyone explains to me what will happen if i set innodb_table_locks=ON or innodb_table_locks=OFF ?
The pros and cons of the action.

Thanks.

The Benefits of Employing Table Locking
Every time you make a change to a table, MySQL must eventually commit that change to disk. Making a change to a table that has indexes requires even more work as MySQL needs to perform the additional step of updating the indexes. The effect of this additional step can be compounded when you make numerous changes to the data at one time, such as by performing an UPDATE to key (i.e., code) fields.  In this case, MySQL might have to perform hundreds or even thousands of write operations.

When we lock a table, MySQL won't update the index keys for the locked table until we unlock it. That can result in substantial time savings. Depending on the complexity of the indexing, judicious use of table locking can speed things up drastically.

Disadvantages of Table Locking

Table locking still allows multiple processes to read from a table at the same time, but if a process wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During write actions, all other sessions that want to access this particular table must wait until the write is done.

2.Table locking runs into trouble when the disk runs out of free space and needs to make room before the session can proceed. When that happens, all sessions that want to access the affected table(s) are made to wait until more disk space becomes available.

SELECT statements that take a long time to run prevent other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session waits for exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions.


best approach to model relation between customers - dimension dim_customer (SCD2) which can be a HEAD/SUB customer Hi there we have the concept of having department. each department can either be its own head or it can have a head department. (similar to each person in a organisation is a employee but some employee they have head/supervisor or some employee they act as a supervisior for a certain time that can also change over time if employees are leaving)
so we have a dim_department (SC2)
dim_department

so it is an SCD2 which can slowly change over time if the name is changing ect
so let's say the dep1 is now becoming the head of the dep2
later there will be another department "dep3" be open and this will then become the head
so the head of dep2 is changing
what would be the best approach to model that
I guess there are several options/solutions to solve that problem
so I could
first option
add the head SK in the dim_department
so every time a Head is changing it would lead to an entry in dim_customer in our case a new entry for dep3 would be created and a new entry (slowly changing entry) for dep1 with a closing old current_yn and creating new current_yn

second option I thought
create a bridge table or factless fact table

or am I totaly wrong?
a third option would just be to put the Head SK in the fact tables
so we would than have

another options
a snowflake dimension ? hmmm wondering if that would work with an SCD2 dimension?
so in a nutshell it is on this SCD2 table a relation with itself
what I see as a problem with option 1 is that I have first to handle the HEAD entries because the attributes could change (because of changing name etc) and would generate a new SK and than I would need to lookup that key when handling the sub
what do you think?
is this total bullshit or totaly on the wrong path?
Thx

All you need is your one-to-many bridge table, and it really only needs the head_dep_key field and the dep_key field. That can be joined back to your dim_department table when needed and should be able to answer all questions you'd ask of this data.


View: Ignore a left join if it is not used? I have a web service that references a view called gis_sidewalks_vw.

create table gis_sidewalks (
    id number(10,0),
    last_edited_date date
    );
insert into gis_sidewalks (id, last_edited_date) values (1, TO_DATE('2019/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (2, TO_DATE('2019/02/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (3, TO_DATE('2019/03/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into gis_sidewalks (id, last_edited_date) values (4, TO_DATE('2019/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;

create table maximo_assets (
    id number(10,0),
    lastsyncdate date
    );
insert into maximo_assets (id, lastsyncdate) values (1, TO_DATE('2019/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (2, TO_DATE('2019/03/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (3, TO_DATE('2019/02/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
insert into maximo_assets (id, lastsyncdate) values (4, TO_DATE('2019/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss'));
commit;

create or replace view gis_sidewalks_vw as (
select
    s.id,
    s.last_edited_date as gis_last_edited_date,
    a.lastsyncdate as maximo_lastsyncdate,
    case
        when s.last_edited_date > a.lastsyncdate then 1
    end sync_needed
from
    gis_sidewalks s
left join
    maximo_assets a
    on s.id = a.id
);

select * from gis_sidewalks_vw;

         ID GIS_LAST_EDITED_DATE MAXIMO_LASTSYNCDATE SYNC_NEEDED
 ---------- -------------------- ------------------- -----------
          1 01-JAN-19            01-APR-19
          2 01-FEB-19            01-MAR-19
          3 01-MAR-19            01-FEB-19                     1
          4 01-APR-19            01-JAN-19                     1

The view has a left join and a calculated column:

case
    when s.last_edited_date > a.lastsyncdate then 1
end sync_needed

...

left join
    maximo_assets a

Scenario:

The view & the web service are multi-purpose.

Purpose #1:

Serve up only the rows where sync_needed = 1 to a cron task (synced weekly to a separate database).

Purpose #2:

Serve up all the rows in the view to a web map (map is in constant use).

Problem:

In purpose #1, it makes sense to join to the maximo_assets table and generate the calculated column.

However, in purpose #2, it does not make sense to join to the maximo_assets table and generate the calculated column.

Unsurprisingly, with purpose #2, I am experiencing performance issues in the web map due to the unnecessary join.

Question:

Is there a way to design the view so that it ignores the join to the maximo_assets table if the join is not being used?

For example:

select
    id,
    gis_last_edited_date
    --maximo_lastsyncdate
    --sync_needed
from
    gis_sidewalks_vw

Think about it. Such a join without further information can not be skipped. Even if you do not select columns from maximo_assets, the join to it may increase the number of rows returned, because for 1 ID in gis_sidewalks, you can have multiple rows in maximo_assets with the same ID.

If the IDs are unique (or PK), and you make this known to the database by creating the proper constraints, then the above is not true anymore, and it will be able to skip the maximo_assets table:

alter table gis_sidewalks add primary key (id);
alter table maximo_assets add primary key (id);

select
    id,
    gis_last_edited_date--,
    --maximo_lastsyncdate,
    --sync_needed
from
    gis_sidewalks_vw;

        ID GIS_LAST_
---------- ---------
         1 01-JAN-19
         2 01-FEB-19
         3 01-MAR-19
         4 01-APR-19

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f4p3b2huhfk5a, child number 0
-------------------------------------
select     id,     gis_last_edited_date--,     --maximo_lastsyncdate,
  --sync_needed from     gis_sidewalks_vw

Plan hash value: 1031306697

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| GIS_SIDEWALKS |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The performance with the subquery you posted will not scale well.

Also, multi-purpose SQL is the root of a lot of performance problems, so handle with care.


Alternative approaches to "primary" record in child table I have a database with a Customers table, and two child tables Addresses and Contacts, as customers often have multiple locations and contacts.

However we also need a "primary address" and a "primary contact" for each customer that we use as the default.

My current design was to put a PrimaryContactID and a PrimaryAddressID in the Customers table (both are nullable), but I was never very comfortable with this design - a parent table referencing the child table doesn't feel right.

The alternative approach I considered was an IsPrimary bit field on the child tables. This would need a trigger that would unset the IsPrimary value on the other records and maintain integrity but could lead to problems if a query tried to set multiple records as primary for the same customer: I'd have to write the trigger to fail in these situations.

Any ideas/comments or other approaches I should consider?

The other design option that came in my mind is to use CustomerID column in child table like Addresses and Contacts so that you will be making entry first in Customer table and that CustomerID will be referred in other table.

As suggested in comment by  @a_horse_with_no_name, you can create Unique Index as

CREATE UNIQUE INDEX UniquePrimaryAddresss ON Addresses(CustomerID) WHERE IsPrimary = 1

Similar indexes on Contacts table as well


Overriding a row FORMAT_STRING with a column FORMAT_STRING in MDX Does anyone have an idea how you could override the format string on a row with a format string on a column?

I have a query like the following:

WITH
    MEMBER [Measures].[ROH Total Sold Gross]                AS [Measures].[ROH Total Sold Dollars] - [Measures].[ROH Total Sold Cost], FORMAT_STRING='Currency'

    MEMBER [ClosedDate].[Date].[MTD]                        AS AGGREGATE([ClosedDate].[Date].[2017-07-01]:[ClosedDate].[Date].[2017-07-31])
    MEMBER [ClosedDate].[Date].[PrevMTD]                    AS AGGREGATE([ClosedDate].[Date].[2017-06-01]:[ClosedDate].[Date].[2017-06-30])

    MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD]          AS [ClosedDate].[Date].[MTD] - [ClosedDate].[Date].[PrevMTD]
    MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %]        AS [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD], FORMAT_STRING='Percent'
SELECT
    {
        [ClosedDate].[Date].[MTD],
        [ClosedDate].[Date].[PrevMTD],
        [ClosedDate].[Date].[MTD vs. Prev. MTD],
        [ClosedDate].[Date].[MTD vs. Prev. MTD %]
    } ON COLUMNS,
    NON EMPTY
    {
        [Measures].[ROH Count],
        [Measures].[ROH Total Sold Gross]
    } ON ROWS
FROM
    [ServiceDept]

The ROH Count row is properly formatted as an integer, with the exception of the MTD vs. Prev. MTD % column, which is properly formatted as a percentage.

The problem is the ROH Total Sold Gross row, where all columns are formatted as currency.

Has anyone done anything like this before?

After doing a bit more research, I found that the SOLVE_ORDER member property can get SSAS to use the format string from the MTD vs. Prev. MTD % member.

The following code worked for me:

MEMBER [ClosedDate].[Date].[MTD vs. Prev. MTD %] AS
  [ClosedDate].[Date].[MTD vs. Prev. MTD] / [ClosedDate].[Date].[MTD],
  FORMAT_STRING='Percent', SOLVE_ORDER=1


test data to generate high index fragmentation level I want to test the rebuild and reorganize operations for the indexes in sql server and for that I need to populate the db with test data that generates high fragmentation levels/percentages. Does anyone have a hint for me on how to do this? I heard about backwards insert, does this hep? If yes, how can I achieve this?
Many thanks!

You can use Paul Randal's script or similar to it.

The idea here is to create two similar table (of the same size) with clustered PK on identity (or other ever increasing key), then to drop the first created table and to SHRINK database.

Since SHRINK takes the pages from the end of data file and moves them to file beginning your second clustered index will be "inverted" and will be perfectly fragmented as the physical order now will be perfectly inverse to its logical order.

Why you should not shrink your data files

-- Create the 10MB filler table at the 'front' of the data file
CREATE TABLE [FillerTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'filler');
GO

-- Fill up the filler table
INSERT INTO [FillerTable] DEFAULT VALUES;
GO 1280

-- Create the production table, which will be 'after' the filler table in the data file
CREATE TABLE [ProdTable] (
    [c1] INT IDENTITY,
    [c2] CHAR (8000) DEFAULT 'production');
CREATE CLUSTERED INDEX [prod_cl] ON [ProdTable] ([c1]);
GO

INSERT INTO [ProdTable] DEFAULT VALUES;
GO 1280

-- Check the fragmentation of the production table
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO

-- Drop the filler table, creating 10MB of free space at the 'front' of the data file
DROP TABLE [FillerTable];
GO

-- Shrink the database
DBCC SHRINKDATABASE ([DBMaint2008]);
GO

-- Check the index fragmentation again
SELECT
    [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats (
    DB_ID (N'DBMaint2008'), OBJECT_ID (N'ProdTable'), 1, NULL, 'LIMITED');
GO


Is it OK to use WITH INIT for my Transaction Log Backup to avoid having it grow too large? I'm still struggling to understand SQL's backup jobs.

We currently have a SQL Server 2005 instance using the FULL recovery model.

We do a full database backup every week.

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase.Bak'
WITH INIT, NAME = N'MyDatabase.Bak'

And a differential backup once a day

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_Diff.Bak'
WITH INIT, DIFFERENTIAL, NAME = N'MyDatabase_Diff.Bak'

Now I am trying to figure out how to schedule Transaction Log Backups every hour, so at most we would lose an hours worth of work.

BACKUP LOG [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_Log.Bak'

My problem is, our storage space is limited and I don't want the log file to grow too large. Can I use WITH INIT on the transaction log backup to force it to create a new file every hour? Or do I need all the Transaction Log backups since the last Differential backup to restore to a specific point in time during the day?

And if I do need to keep all copies since the last differential, is there a way to tell it to reset anytime I do a backup?

No, do not use WITH INIT for transaction log backups to the same file. If you do that, you may as well not take them in between full/diff backups.

Typically you will backup the log to a unique file each time. Each one will have some form of a timestamp in the name and have a .trn extension rather than .bak. This is mostly for management purposes - it makes it really easy to identify when a log backup was taken, when it is safe to delete, whether it should be part of your point-in-time recovery operation, etc.


Installation and shell to move physical and logical SQL Server in azure IaaS VM Firts of all excuse my english is not my primary language.

We are creating an azure template to deploy an IaaS machine with disks C and D with windows server, and the idea is also deploy an installation of sql server, so initially the engine binaries and files will be deployed in the C and D drive drives.

We have some questions to see if it is possible to do in azure

Is it possible to install through a template not only the OS but also SQL Server?
If the previous question is possible to do after the installation of SQL Server, is there a script or a guide to create a script to move the logical and physical sql server files without having to enter sql server management studio and execute a t-sql script or move the files manually?. The idea behind this is to automate as much as possible the deploy of the Virtual Machine and to move the files to his respective volumes to fullfill SQL Server best practices regarding the separation of the log data from the user data and tempdb and also the restart of the instance once the previous steps are done.
or is there a way to do the install of the OS and SQL Server and in the template specify wich files of the database have to go to an specific disk and avoid the movement in a further step of the deployment of the machine

thanks in advance

It's typically not necessary to use custom templates and installation for SQL Server on Azure Virtual Machines.  The Azure Marketplace has an extensive collection of pre-configured templates for SQL Server on Azure Virtual Machines, which you can deploy through the Azure Portal or through Powershell.  These are built and maintained by the SQL Server engineering team, and definitely the easiest way to deploy and run SQL Server on Azure VMs.


SQL Server Mirroring disconnected for 1 of 5 mirroried DBs after initial setup completes Environment: 2 Servers SQL 2008 R2 RTM Standard Edition, Windows Server 2008 R2 SP1 standard edition. Both servers using the same domain service account for SQL with sysadmin access and local Administrator group access on both servers.

4 databases are successfully mirrored without issue, all database owners are sa.

When we attempt to setup mirroring on the 5th database it successfully starts and then within a minute goes to a (Principal disconnected) state on Primary and stays at (Mirror, Syncronizing / Restoring) on the mirror. The Mirroring monitor just shows "Disconnected" from the beginning of the event for the fifth database.

I have run a SQL Trace while setting up mirroring and the only thing in them is "Attention" with the service account and host name, no other details.

The Windows Application logs on both Primary and Mirror server show SQL Server event ID 1438. We are not using a witness and have never attempted to set one up in this environment.

Any suggestions on what to check would be appreciated

You may not have enough available threads.
See how many are available with sys.dm_os_sys_info.  See how many are being used with sys.dm_os_schedulers (sum of rows in column current_workers_count)

Database mirroring has the following requirements for worker threads
Principal server: 1 global thread and 2 threads per each of the mirrored databases.
Mirror server: 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.
On a 4 CPU server it requires 1 global thread+ (2+ 1) threads /mirrored db.
On a 6-8 CPU server it requires 1 global thread +(2+2) threads /mirrored db.
https://support.microsoft.com/en-us/kb/2001270

You may not have enough RAM.  You may not have enough network bandwidth. Your disk subsystem may be overwhelmed. Your servers may have too heavy of a load to support another mirrored database.


Calling another function inside a postgres function I have a function A and function B.

Function A Name is f_a() and the code is:

Select id,address from A;

Function B Name is f_b() code is:

Select C.address,B.name
From
(Select id,name from B,
(Select * from f_a()) as C
Where C.id = B.id;

However, when I execute the select * from f_b() the Error message says C.id does not exist. How can I run the function f_a() inside f_b().

Above function A and B are functions which returns table. I omitted the function head and end.

Your syntax is twisted in several ways. If the return type of function A is defined as RETURNS TABLE (id int, address text), it could work like this:

Select A.address, B.name
FROM   f_a() A
JOIN   B USING (id);

Aside: While your identifiers look alright for the purpose, I strictly use legal, lower case identifiers to avoid complications.


SQL Server 2005 encrypted database works on new PC without restore keys! Something tells me that it was not normal.

Let me take it from the begining.
In an SQL Server Express 2005 I needed to encrypt one column. I did the following.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD ='blahblah';

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah'
WITH SUBJECT = 'Password',
EXPIRY_DATE = '12/31/2099';

CREATE SYMMETRIC KEY my_symmetric_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE my_certificate;

I created procedures to encrypt, decrypt the column and everything worked OK.

I then took the database (SQL Server 2005, Mixed Mode) and restored it to a new PC with an SQL Server 2008 Express.

WITHOUT restoring KEY and Certificate, I launched my application which use the stored procedures to encrypt/decrypt and SURPRISINGLY everything worked in the new database!! I could decrypt previously encrypted data successfully.

Is this normal??

What is the use of backing-up the Key and the Certificates then?

Thanx in advance

Yes, it is normal. The certificates and keys are part of you database.

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah' ...;

CREATE SYMMETRIC KEY my_symmetric_key
... ENCRYPTION BY CERTIFICATE my_certificate;

Your posted code creates a symmetric key encrypted with a certificate encrypted with a password. The master key is not involved in any way. This arrangement requires you to provide the certificate decryption password in the session. Your application must provide it, or your stored procedures do.


postgresql pg_dump out of memory is it possible to limit somehow usage of resources for pg_dump?

The problem is, whole DBase is 775GB and i have two very large tables:

pg_largeobject table is – 390GB

pg_largeobject statistics:
Index scans              778313505
Index tuples fetched    1079812292
Tuples inserted          201022148
Table size                  395 GB
Indexes size               6900 MB

hive.lob_messages – 265GB.

Index scans             194377937
Index tuples fetched    183894199
Tuples inserted          16902844
Table size  8127 MB
Toast table size     272573947904
Indexes size              3090 MB

In all cases I am executing this pg_dump command:

pg_dump -U postgres -d hive -Fc -v -f /nfs/hive_dump_10/hive_full_20191016_1945.dmp 2> /nfs/hive_dump_10/hive_full_20191016_1945.log

When i try to run pg_dump, to dump all the data it is killed by "out of memory".
My Postgre SQL server have had 20gb of RAMs and when I started a pg_dump, it kills after about ~30minutes. saying out of memory.

DB with 20GB of rams and pg_dump is killed by this *.log line:

pg_dump: reading large objects

Kern.log:

Oct 15 09:00:18 hive-psql kernel: [21504626.583951] Out of memory: Kill process 30663 (pg_dump) score 750 or sacrifice child
Oct 15 09:00:18 hive-psql kernel: [21504626.584000] Killed process 30663 (pg_dump) total-vm:19151692kB, anon-rss:16873728kB, file-rss:0kB, shmem-rss:0kB

Here is ZABBIX monitoring tool showing my three times tried a pg dump with 20GB rams:

So to cope with the problem i gave my all resources, additional +20 GB ram (40gb in total), and the it kills after ~3hours by this *.log line:

pg_dump: dumping contents of table "hive.lob_messages"

And kern.log says:

Oct 16 23:19:01 hive-psql kernel: [15614.408113] Out of memory: Kill process 2693 (pg_dump) score 840 or sacrifice child
Oct 16 23:19:01 hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) total-vm:38363104kB, anon-rss:34020140kB, file-rss:24kB, shmem-rss:0kB

ZABBIX window again, with 40GB in total:

I do not have any more resources for that.
So what are my options? My DB grows everyday, thank you.
I think that additional 20GB will be suffice, 60GB of rams it should be enough . But is this OK situation? What to do when my DB became like ~2TB in size and etc...

Other question, is my hive.lob_messages table is okay?
Table size and toast table size is different.

What the graph shows is pg_dump probably starting at 19:45 and having a normal memory consumption until 20:00. At 20:00, it appears that ~30 GB are suddenly allocated and not much is released until 23:15 when presumably the Out-Of-Memory condition occurs.

From the comments, the kernel says:

hive-psql kernel: [15614.408169] Killed process 2693 (pg_dump) total-vm:38363104kB, anon-rss:34020140kB, file-rss:24kB, shmem-rss:0kB

The anon-rss of pg_dump at about 36GB shows that it is indeed pg_dump that over-allocates. There is something wrong in pg_dump needing that much memory.

Your database surely has many large objects, some of them may be very large (the theorical maximum per object is 2TB) but pg_dump processes large objects by:

opening a cursor on SELECT oid FROM pg_largeobject_metadata ORDER BY 1 and reading it by chunks of 1000 entries: that should never blow up.
for each large object, loop on lo_read to fetch the contents into a buffer of 16384 bytes: that should never blow up.

The fact that the table itself weighs 390 GB should not be a reason for pg_dump to do large allocations.

  But, first time pg_dump was killed at "retreiveing large objects"

Assuming pg_dump displays this with the -v options (by the way, you did not mention what options were passed), it should display "reading large objects" instead.


SQL, creating a new table with auto generated passords from other tables i have this sql below which i have retrieved from our student database. It retreives all that i need, but i would like to create a new table which includes all the tables plus one column with auto generated passwords.

Is this possible?

SELECT
    CurrentPupil.SchoolID AS 'SchoolID',
    CurrentPupil.PupilID AS 'PupilID',
    PupilPersonalDetails.Surname AS 'Surname',
    PupilPersonalDetails.Forename AS 'First_Name',
    PupilPersonalDetailsGenderLookupDetails.Description AS 'Gender',
    ContactMaster.LastName AS 'Contact_Last_Name',
    ContactMaster.FirstName AS 'Contact_First_Name',
    ContactMasterGenderLookupDetails.Description AS 'Contact_Gender',
    ContactMaster.LastName2 AS 'Contact_Last_Name_2',
    ContactMaster.FirstName2 AS 'Contact_First_Name_2',
    ContactMasterGender2LookupDetails.Description AS 'Contact_Gender_2',
    ContactEmailInformation.EmailID AS 'Contact_Email_Address',
    PupilCurrentSchoolYearGroupLookupDetails.Description AS 'Year_Group',
    PupilCurrentSchoolFormSchoolLookupDetails.Description AS 'Form',
    ContactMaster.ContactID
FROM
    win2k12engage.EngageSpratton.dbo.CurrentPupil
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilPersonalDetails AS PupilPersonalDetails ON CurrentPupil.SchoolID = PupilPersonalDetails.SchoolID
AND CurrentPupil.PupilID = PupilPersonalDetails.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilPersonalDetailsGenderLookupDetails ON PupilPersonalDetailsGenderLookupDetails.LookupDetailsID = PupilPersonalDetails.Gender
AND PupilPersonalDetailsGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilContacts AS PupilContacts ON CurrentPupil.SchoolID = PupilContacts.SchoolID
AND CurrentPupil.PupilID = PupilContacts.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactMaster AS ContactMaster ON PupilContacts.ContactID = ContactMaster.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.ContactEmailInformation AS ContactEmailInformation ON ContactMaster.ContactID = ContactEmailInformation.ContactID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGenderLookupDetails ON ContactMasterGenderLookupDetails.LookupDetailsID = ContactMaster.Gender
AND ContactMasterGenderLookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS ContactMasterGender2LookupDetails ON ContactMasterGender2LookupDetails.LookupDetailsID = ContactMaster.Gender2
AND ContactMasterGender2LookupDetails.LookupID = '20003'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.PupilCurrentSchool AS PupilCurrentSchool ON CurrentPupil.SchoolID = PupilCurrentSchool.SchoolID
AND CurrentPupil.PupilID = PupilCurrentSchool.PupilID
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.LookupDetails AS PupilCurrentSchoolYearGroupLookupDetails ON PupilCurrentSchoolYearGroupLookupDetails.LookupDetailsID = PupilCurrentSchool.YearGroup
AND PupilCurrentSchoolYearGroupLookupDetails.LookupID = '3004'
LEFT OUTER JOIN win2k12engage.EngageSpratton.dbo.SchoolLookupDetails AS PupilCurrentSchoolFormSchoolLookupDetails ON PupilCurrentSchoolFormSchoolLookupDetails.LookupDetailsID = PupilCurrentSchool.Form
AND PupilCurrentSchoolFormSchoolLookupDetails.LookupID = '1002'
WHERE
    ('%wc%' = '%wc%')

*************************************UPDATE***************************
Hi Guys back to my desk. I am adding one additional column to the current query and have that additional column contain a random string of data that would be a password...also
the tables / columns above are from a database that i cannot write to but only read, so i use the win2k12engage.EngageSpratton.dbo.fieldname to connect to my other database Eletter.dbo.newtable where i want to have all the fields above plus one more column with the password.

Cheers

Many thanks in advance
Vitor

I don't have a Database to test this on, but Potentially, you could use DEFAULT, RAND() and REPLACE() to do this.

CREATE TABLE Users(
     ID int NOT NULL,
     LastName varchar(255) NOT NULL,
     FirstName varchar(255),
     Password varchar(255) DEFAULT REPLACE(FLOOR(RAND()*(987654321-123456789+1))+123456789,'1','!')
 );

Just keep adding more REPLACE to make the Password a bit more Complex with each.


update table with replace string1 string2 I need replace string1 to string2 in column big tabe.
My table weight 1.7GB. For update I will be use:
UPDATE table SET column = REPLACE( column, 'search', 'replace' ) ;

So, I have several questions:

Will table lock during procedure? Should I hide public part my site or not?
How long time this procedure can executing?
How better run this? Maybe 'screen'? if my ssh connection will closed.

Mysql 5.5.53

See http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks for a technique of chunking a table.  It is aimed at DELETE, but can easily be adapted to UPDATE.

This should avoid the timeouts and hangs.


Calculating Margin in CASE Statement and GROUP BY Clause I think I am running into an error in how I have chosen to aggregate my columns in a group by statement.

I am working out Margin % and grouping into months using a case statement like so:

SUM(CASE WHEN MONTH(DateProcessed) = 1 AND Revenus <> 0 THEN (Revenue - Cost) / Revenue ELSE 0 END AS [January]

So my equation ends up as: SUM( (Revenue - Cost) / Revenue )

Which in months with multiple transactions gives me the wrong value

I am sure my equation needs to be: SUM(Revenue - Cost) / SUM(Revenue)

But I cannot format my case statement like that. How would you work out this sort  of equation?

Thanks,

Here's one way.

select
 (select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 1) as jan
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 2) as feb
,(select (sum(Revenue) - sum(Cost)) / sum(Revenue) from tmp where month(DateProcessed) = 3) as mar

If using the common table expression approach and more than a month is expected or included then a sum will be needed.

;with CTE_summary as (
    select
         month(DateProcessed) as monthProcessed
        ,sum(isnull(Revenue, 0.0)) as Revenuesum
        ,sum(isnull(Cost, 0.0)) as Costsum
    from tmp
    where year(dateprocessed) = 2015
    group by month(DateProcessed)
)
select
     sum(case when monthProcessed = 1 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [jan]
    ,sum(case when monthProcessed = 2 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [feb]
    ,sum(case when monthProcessed = 3 then (Revenuesum - Costsum) / Revenuesum else 0.0 end) as [mar]
    --,...
from CTE_summary


Update statement errors, parenthesis, failed validation ORACLE i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value.

Original statement

The statement below works flawlessly,

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) +
   NVL((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);

Modified version not working
This is the modified version,  i want to multiply the first two select with a value in each field for in flight and outfight seats no.

UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL =
   NVL(((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0) * NVL(R.IN_FLIGHT_SEATS_NO,0)) +
   NVL(((SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.OUT_FLIGHT_ID), 0)* NVL(R.OUT_FLIGHT_SEATS_NO,0)) +
   NVL((SELECT AC.ACC_PRICEPN FROM ACCOMMODATION AC WHERE AC.ACC_ID = R.ACC_ID), 0);

Editor, that higlights matching braces should do the trick.
There are just too many of them. And... You left out the default values in outer nvl() or nvl keyword itself?

...
NVL(
 "nvl here?"(
  (SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0
 )
 * NVL(R.IN_FLIGHT_SEATS_NO,0)
",default value here?") + ...

Go, have a break. This happens to me very often :-)


What indexes do I need to define for my bridging tables? I have three tables in my database:

users:

user_id (PK)

roles:

role_id (PK)

users_roles:

user_id (PK, FK)
role_id (PK, FK)

I've done plenty of research surrounding indexing columns but I can't find a definitive answer to my question which is what columns should be indexed in this bridging scenario when I have a compound primary key. I need to be able to conduct WHERE queries using both user_id and role_id in the bridging table. I've read mixed answers and don't understand how many indexes need to be defined.

My current indexes for this bridging table include:

PRIMARY (user_id, role_id)
INDEX (user_id)
INDEX (role_id)

I assume ^ is incorrect.

By default PRIMARY KEY is already a clustered indexed. It will also speed up queries executed against first column in an index, but not the other.

So if you index is defined on user_id, role_id (in that order), index will work also for user_id.

In your situation I'd go with default index of compound key, then, if needed, take a closer look at long running queries and benchmark if separate index on role_id is necessary.


How to ensure all records of a header are under the same fiscal year? Say I have the following schema:

BUDGET_YEAR_TARGET
    INTEGER ID

BUDGET_DATE_TARGET
    INTEGER ID
    INTEGER BUDGET_YEAR_TARGET_ID--Foreign key
    DATE DATE

How can I ensure that any records of BUDGET_DATE_TARGET that share a BUDGET_YEAR_TARGET_ID will have DATEs under the same Fiscal Year (with Oct 1 as new year day)?

For example, this would be valid:

ID BUDGET_YEAR_TARGET_ID DATE
1  1                     Jan 1, 2020
2  1                     Jul 1, 2020
3  2                     Jan 1, 2020
4  2                     Aug 1, 2020
5  3                     Nov 1, 2020

If I were to insert a new record with a DATE of Dec 1, 2020, I'd like it to succeed if the BUDGET_YEAR_TARGET_ID is 3 but fail if it is 1 or 2.

How can I achieve that?

A before insert trigger might be an option...

However, a "dates table" aka "calendar table" is much more flexible, regardless of the DBMS you are using.

Basically, it's a pre-generated table of dates from say 1900-01-01 to 2200-12-31.  In addition to various date columsn, it would contain various flag columns (weekday, weekend, holiday) and other helpful columns (Fiscal year, Quarter).

A bit of searching will turn up lots of articles about using them.

For Db2 for i specifically, if you have the Web Query product installed, IBM provides a routine (LOAD_DATE_CONVERSION_TABLE) to generate one (QWQCENT.DATE_CONV) for you.  See chapter 5 of the Redbook - IBM DB2 Web Query for i

For your case, you could define a unique key constraint on your dates table over (date, fiscal_year) and then use that as a FK constraint in your BUDGET_DATE_TARGET.

However, you may find you don't need BUDGET_DATE_TARGET or BUDGET_YEAR_TARGET at all once you've got the dates table.


Postgresql not able to start I am facing below error during postgresql db start.
FATAL: the database system is starting up
FATAL: failed to re-find parent key in index "16619" for split pages 915114/915115

What is the solution of this error?

You are facing data corruption, and it seems that your database had crashed before and now the recovery process is failing.
If you cannot restore from a backup, you will have to find a professional who can salvage as much from the wreck as possible. A Q&A forum will not be able to guide you through this.
Apart from that, you should figure out what may have caused the data corruption, so that you can avoid it in the future. Often the cause is hardware problems.


MySQL 5.5 Fail start Fedora 16 I installed mysql and mysql-server from the repos (MySQL version 5.5).
Then tried to start it, but got an error.

[root@server]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Job failed. See system logs and 'systemctl status' for details.

Here is the log:

121118  2:41:38 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121118  2:41:38 [Note] Plugin 'FEDERATED' is disabled.
121118  2:41:38 InnoDB: The InnoDB memory heap is disabled
121118  2:41:38 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121118  2:41:38 InnoDB: Compressed tables use zlib 1.2.5
121118  2:41:38 InnoDB: Using Linux native AIO /usr/libexec/mysqld: Can't create/write to file '/tmp/ibhsfQfU' (Errcode: 13)
121118  2:41:38  InnoDB: Error: unable to create temporary file; errno: 13
121118  2:41:38 [ERROR] Plugin 'InnoDB' init function returned error.
121118  2:41:38 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
121118  2:41:38 [ERROR] Unknown/unsupported storage engine: InnoDB
121118  2:41:38 [ERROR] Aborting

121118  2:41:38 [Note] /usr/libexec/mysqld: Shutdown complete

121118 02:41:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Fresh installation, nothing changed prior to that, just ran yum update.

Here is the systemctl status trace

[root@linyansho /]# systemctl status mysqld.service
mysqld.service - MySQL database server
  Loaded: loaded (/lib/systemd/system/mysqld.service; disabled)
  Active: failed since Sun, 18 Nov 2012 02:45:19 +0300; 5min ago
  Process: 864 ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID (code=exited, status=1/FAILURE)
  Process: 863 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 842 ExecStartPre=/usr/libexec/mysqld-prepare-db-dir %n (code=exited, status=0/SUCCESS)
  CGroup: name=systemd:/system/mysqld.service

Ensure you have enough space in / and data dir.

Kill all processes for mysql in ps -ef | grep -i mysql
If you see any any ibdata file generated under your default data directory you may remove them also iblogfile1,2 since its a fresh installation no need to safe backup them.
chown -R mysql:root /var/lib/mysql
chmod -R 775 /var/lib/mysql also the same permissions to /etc/my.cnf.
Now try to start the instance by issuing /etc/init.d/mysql start and tail -f /var/log/mysqld.log. It should start without any issues if you get any error message .


Forcing IP Whitelisting Globally We utilize a Software that when creating a new user, automatically makes their host a wildcard (%), and we have no method to change this.

Is there anyway to enforce IP Whitelisting via MySQL Globally, to only allow our companies IP to reach our server?

If you know the exact IP of the incoming MySQL users, you can do the following

Suppose the user is called datacustomer and the incoming user's IP is 10.200.210.252. From what you are saying, there should be a MySQL user within mysql.user called datacustomer@'%'. You cansee that user by running this query

SELECT * FROM mysql.user
WHERE user='datacustomer' AND host='%';

If one row appears, you can change the host of that user

UPDATE mysql.user
SET host='10.200.210.252'
WHERE user='datacustomer' AND host='%';
FLUSH PRIVILEGES;

You'll have to repeat these steps with every MySQL user.


Message system - delete message, or flag for deletion? I'm wondering, what is the best approach about message deletion in a mysql based message system.

Plan A:
When the sender sends a message, it will be saved in 2 versions. One for the sender and other for the recipient. This way everyone can immediately delete his own message from inbox/outbox.
This approach has an advantage of immediately delete a row in database, but there are 2 time more rows for same number os messages, than the plan B.

Plan B:
The sent message is saved once, the sender and recipient see the same message. They only mark that row for deletion and a cron job has to delete only those rows, that are marked by everyone for deletion.
Advantages: only one row/message,
Disadvantages: messages are deleted rarely, because they need to be marked by two users.

Are there other advantages/disadvantages for these plans?
Is there a better way to do this? The goal is to make a fast system, optimization for speed is significant.

Ultimately it will depend on where you want the responsibility for deleting the emails to reside, system or user? If your main goal is performance then the fastest way to do this would be to only store a single copy of the message and then send the clients a reference to this message instead of the message itself. The client can then download the message when a user clicks on it. Once a message has been downloaded by all recipients then it can be flagged for deletion by the system regardless of whether the clients decide to delete their local copy or not.

I would look at how other messaging systems work and decide whether the method they use suits your purpose. For example Microsoft Exchange uses Single Instance Storage, a description of this is below:

  Exchange utilizes what Microsoft terms a single-instance message store. This single-instance message store works on a per database basis. What does this mean? If an e-mail message is sent to multiple mailboxes that are all in the same database, the message is stored once and each mailbox has a pointer to the message. The transaction is also logged in the transaction logs for the Storage Group that contains the database. However, if the e-mail message is sent to multiple mailboxes that are located in different databases, the message is copied to each database and written to the transaction logs for each Storage Group that contains the database with a copy of the message.

As the designer of the system it is ultimately up to you to make the call based on the requirements you are given. That being said, my personal preference is to put the ownership on the clients to tidy themselves up so that you can worry about important things instead. You could implement some type of archiving system if you wanted to so that once it is downloaded by all clients it gets archived for a set period of time before being removed - maintaining a good backup strategy is also important in these scenarios.

Remember, although speed is important you can only go as fast as your network allows you to under a given load - something a lot of companies seems to overlook.

I hope this helps you.


DBCC INPUTBUFFER shows Event Type as Language Event for an unused Database In my production SQL Server 2016 server,i had few dummy databases created as part of migrating from SQL Server 2008.
I was cleaning up all the unwanted databases and i dropped few databases.But when i tried to drop an unused database ,i get the error message "Cannot drop the database as it is currently in use".
So i checked the process/session using that database.
I see that spid 144 uses it and when i did DBCC INPUTBUFFER(144),i see an Event Type as Language Event.

I am not sure what it means.

I tried to kill the spid earlier ,but in few seconds a new session started and i am still not able to drop that database.

This particular database is not in use for atleast 6 months now and that is why i tried to KILL the process using that db,cause i was sure that it won;t affect any.

Any idea why a new session is getting created and the database is always in use.

Your session has a connection context to the database. In order to drop a database, you need to remove all connections (including your own query window & Object Explorer connections):

USE master;
GO
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE dbname;

(See also this question.)

This is much cleaner and immediate than playing KILL-a-spid (whack-a-mole) for individual sessions and hoping you get your alter in before another session establishes connectivity.

We can't tell you why this session is getting created, something that is calling dtb.compat... which isn't anything built into SQL Server, so it must be a user or some application. You can get more detail when you catch this query in the act, like host name, app name, and other details, by looking at sys.dm_exec_connections, sys.dm_exec_connections, sys.dm_exec_requests, etc.

Your query against sysprocesses is problematic for two reasons. (1) dbid there is unreliable. A session can definitely hold locks against a database with a different dbid indicated there (think cross-database joins). (2) sysprocesses is deprecated:

As for the Language Event question, this just means that the last command that session sent to SQL Server was query text. The other option (other than No Event) is an RPC Event, which in simplest terms means a procedure call. See the DBCC INPUTBUFFER topic in the documentation for more details.


Very slow JOIN + ORDER BY query only in dev environment I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query.

This query is taking 75 seconds to run in the new dev environment:

SELECT `orders`.*, `users`.`user_flag`
FROM `orders`
LEFT JOIN `users` ON `users`.`id` = `orders`.`user_id`
WHERE `cancelled` = '0' AND `is_estimate` = '0'
ORDER BY `orders`.`updated_date` DESC, `orders`.`order_date` DESC, `orders`.`creation_date` DESC
LIMIT 30;

It is a new installation of MySQL via MAMP Pro on a Mac
The version of MySQL is 5.7.25

...

Exactly the same query on exactly the same database schema takes only 24 ms on my old machine which has this version:

mysql  Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using  EditLine wrapper

So presumably something about the configuration of MySQL on the new machine is causing this slow query with a join and ORDER BY.

What my.cnf settings should I be looking for?

Further tests

(I cannot change the code as it is production code but did these tests to narrow down the problem)

Removing the JOIN brings the query time down to about 13ms

SELECT `orders`.*
FROM `orders`
WHERE `cancelled` = '0' AND `is_estimate` = '0'
ORDER BY `orders`.`updated_date` DESC, `orders`.`order_date` DESC, `orders`.`creation_date` DESC
LIMIT 30;

Removing the ORDER BY statement brings the time down to about 200ms (still too slow)

SELECT `orders`.*, `users`.`user_flag`
FROM `orders`
LEFT JOIN `users` ON `users`.`id` = `orders`.`user_id`
WHERE `cancelled` = '0' AND `is_estimate` = '0'
LIMIT 30;

(No JOIN or ORDER BY and it runs in 1.5ms)

The old MySQL config contains:

[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1

max_allowed_packet = 64M

The new MySQL config has lots in it:

# The following options will be passed to all MySQL clients
[client]
password    = MAMP_root_password_MAMP
#port       = 9999
socket      = /Applications/MAMP/tmp/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
#port       = 9999
socket      = /Applications/MAMP/tmp/mysql/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 64M

explicit_defaults_for_timestamp = 1

table_open_cache = 64

MAMP_bind-address_MAMP

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
MAMP_skip-networking_MAMP

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

secure_file_priv=""

server-id   = 1

innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
sync_binlog = 0

[mysqldump]
password = MAMP_root_password_MAMP
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
myisam_sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

I noticed in mysql_error.log hundreds of entries like this:

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name
table_name. Please run mysql_upgrade

I ran this:

/Applications/MAMP/Library/bin/mysql_upgrade --host=localhost -uroot -proot

The problem now seems to be resolved – the initial query above is now taking about 20ms.

Perhaps it was corrupted on installation?


Character Conversion Issue Importing From CSV File After doing a load of CSV files, there are various words that are "written" to the database incorrectly.

Some examples:

Diã¡ria should be Diária
Crã©dito should be Crédito
Ligaã§ãµes should be Ligações
Usuã¡rio should be Usuário
Nãºmeros should be Números

Is there any way to translate the symbols to the correct characters?

I've done several tests with different collations and functions that could search the internet, but without success.

there are various words that are "written" to the database incorrectly.

No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the characters are being written incorrectly to the data file. Either way, SQL Server is doing exactly what it is being asked to do.

This is a simple encoding issue. The data was originally exported as UTF-8, but then that UTF-8 encoded file was read into SQL Server as if it were an Extended ASCII file using Code Page 1252. There are three clues which indicate that this is the issue:

Most characters come through correctly. The ones that don't are the accented characters.
The characters that are incorrect are coming through as two characters instead of one.
You mentioned that exporting to an "ASCII" file worked.

UTF-8 is a multi-byte encoding: it uses a different number of bytes depending on the character being encoded. The first 128 Code Points (U+0000 - U+007F), which contain the US English alphabet, all use 1 byte. Code Points above that range take 2 - 4 bytes. This is why some characters transferred between systems as expected: N (upper-case Latin "N") is 0x4E in UTF-8 as well as in Code Page 1252 (in fact, it's 0x4E across all 8-bit Code Pages supported by SQL Server). This is one of the reasons that UTF-8 is so popular. However, the accented characters are not 1 byte in UTF-8:

The á ( U+00E1 ) is encoded as two bytes in UTF-8: 0xC3 and 0xA1. When those two bytes are read by something expecting Code Page 1252, they are interpreted as being Ã ( 0xC3 on Code Page 1252 ) and ¡ ( 0xA1 on Code Page 1252 ). Then, either you or your import process, lower-cased the Ã (probably because it was in the middle of a word) which is how you ended up with: Usuã¡rio.
The ú ( U+00FA ) is encoded as two bytes in UTF-8: 0xC3 and 0xBA. When those two bytes are read by something expecting Code Page 1252, they are interpreted as being Ã ( 0xC3 on Code Page 1252 ) and º ( 0xBA on Code Page 1252 ). Then, either you or your import process, lower-cased the Ã (probably because it was in the middle of a word) which is how you ended up with: Nãºmero.

Your choices are:

Encode the file as (Extended) ASCII using Code Page 1252 (as it is exported), and do not change how it is being read into SQL Server. (it sounds like you have already done this).
Continue exporting the file using the UTF-8 encoding, but change how the file is being read into SQL Server by specifying that it is encoded as UTF-8. Please note that for anyone using BCP.exe, BULK INSERT, or OPENROWSET(BULK...), this option only became available starting in SQL Server 2016. The Code Page to use is 65001 (which usually means UTF-8 with Byte Order Mark, but I am not sure if SQL Server requires the Byte Order Mark in these cases).


Which version is the right one to begin with Oracle I see many versions of oracle 8i , 11g and 12c.

I am familiar with SQL Server 2012 and SQL.

Which version of Oracle should I start with?

Start here: 11G 2 Day DBA

Best of luck!


MySQL: Adding an IF condition to trigger? We've got a trigger that is overwriting the uuid value before insert.

The vers.1 of our API depended on the DB to generate the UUID's, but I'm writing v2 which is using a library that generates the UUID's in the code before sending the INSERT query.
As such, I need to update our trigger so that the new uuid's created via my Model can get stored without being overwritten.

I'm working in PhpMyAdmin (for ease) and our original trigger looks like this:

DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_m3_company` BEFORE INSERT ON `company`
 FOR EACH ROW SET NEW.uuid = UUID()
//

Obviously, the delimiter is set to //.

My latest attempt looks like:

DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_company` BEFORE INSERT ON `company`
 FOR EACH ROW BEGIN
  IF(NEW.uuid IS NULL OR NEW.uuid = '') THEN
   SET NEW.uuid = UUID();
  END IF;
//

But it's throwing an error saying You have an error in your SQL syntax ... near '' at line 5

Unless I'm counting wrong, line 5 is the SET line. No quotes used there.
What am I missing?

You forgot END //

DROP TRIGGER IF EXISTS `init_uuid_company`//
CREATE TRIGGER `init_uuid_company` BEFORE INSERT ON `company`
 FOR EACH ROW BEGIN
  IF(NEW.uuid IS NULL OR NEW.uuid = '') THEN
   SET NEW.uuid = UUID();
  END IF;
 END //


Mysql Slave - Stop applying changes from a particular user I have a mysql master and 3 slave. In the master im running Archive commands to delete old records everyday.

But I don't want to Purge the records on the Slave 3. Im using this as a Archive DB.

So, How can I achieve this?

Im using a seperate user for archive.

Is there any options to prevent this operation from the Particular slave?
Or any way to achieve this?

I think you'll be able to achieve this goal but there isn't really anything native that will give you this functionality out of the box.

You won't be able to use replication filters because they're not granular enough to filter by DML type. So that's off the list early.

You might approach it (optionally using partitions based on your archive routine) dropping the data (partition) outside the bounds of binary logging (SET SESSION LOG_BIN=0) and therefore not sending the DELETE/DROP messages through the replication topology. This could be managed through MySQL's built-in EVENTS scheduler or could be something that runs outside of MySQL in a script controlled through crond.

A quick scan of the documentation of Percona Toolkit's pt-archiver suggests that you could run archival jobs that connect to >1 mysqld instance and can do so in a manner that the session evades the binary logging (see DSN option 'b').

A word of warning, topologies of this design can be pretty delicate especially if you forget that the downstream replicas have more data in certain tables. I have completely forgotten about this when running tools such as pt-online-schema-change where the table is rebuilt on the MASTER and the REPLICA's version of the table ends up the same as the MASTER's.

Good luck!


Where has STATMAN gone in SQL Server 2014? So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be able to tell when SQL Server is updating/creating statistics.

Previously, when compiling a plan, if SQL Server needed additional statistics (we have autostats on) we could see in sp_whoisactive that STATMAN was busily working away doing his thing. Now all we get is a blank sql_text column, no query plan and a huge number of reads and only by looking at the locks do we see that there are some statistics somewhere being generated (but not which columns on what table).

Does anyone have any insight into the changes here? Is there any way to tell what stats the optimizer has asked for? I know we can once the plan is compiled (thank you Paul White) but we have a fairly large data warehouse, so sometimes the stats collection operation takes a significant amount of time!

UPDATE
It looks like while sp_whoisactive no longer shows you the stats being created/updated, you can use a dmv to find out what's going on with async stats:

SELECT  time_queued,
        OBJECT_NAME(object_id1),
        ss.name,
        sc.name,
        auto_created,
        user_created,
        no_recompute,
        has_filter,
        is_temporary,
        is_incremental
FROM    sys.dm_exec_background_job_queue bq
JOIN    sys.stats ss ON ss.object_id = bq.object_id1
                        AND ss.stats_id = bq.object_id2
JOIN    sys.stats_columns stc ON stc.object_id = ss.object_id
                                 AND stc.stats_id = ss.stats_id
JOIN    sys.columns sc ON sc.column_id = stc.column_id
                          AND sc.object_id = stc.object_id
WHERE   in_progress = 1

The way to kill the background jobs is via the KILL STATS JOB command to terminate the stats process.

I still see StatMan operations captured if I use SQL Sentry Plan Explorer PRO against SQL Server 2014:

   click to enlarge

So, not exactly sure what sp_whoisactive is doing differently in this case (I've never used it to analyze statistics creation), but I can assure you that SQL Server 2014 still uses StatMan.


Querying Cassandra without a partition key I read Cassandra's documentation on the internal steps it performs when  querying data. It looks like Cassandra relies on the Partitioner and Replication Strategy to process queries. I am still confused that the Partitioner needs to know the Partition Key. If the query has the Paritition Key, the internal query process looks straightforward. However, if the query expects a result set instead of a deterministic row like below.

SELECT * FROM <table>

In this case, when there is no Primary Key specified in the WHERE clause, how does the Coordinator know which nodes to send the requests to?
If multiple rows are returned, which may be distributed in different nodes, how are these rows aggregated and returned to client?

when there is no Primary Key specified in the WHERE clause, how does the Coordinator know which nodes to send the requests to?

It doesn't.  The (node chosen as the) coordinator has to scan all rows for that table on each and every node.  That's why unbound queries are considered to be an anti-pattern in Cassandra, as they incur a lot of network time.  Especially in larger clusters.  Also, the coordinator will have to do extra work as it has to assemble and return the result set.

  If multiple rows are returned, which may be distributed in different nodes, how are these rows aggregated and returned to client?

They are not really so much aggregated, as they are returned in order by the hashed token value of their partition key.

Consider an unbound query run against a table named crew, with a partition key of crewname.  When I run the CQL token() function on that key, you can see that the rows returned are indeed ordered by their token.

aploetz@cqlsh:presentation> SELECT crewname,token(crewname),firstname,lastname
FROM crew;

 crewname | token(crewname)      | firstname | lastname
----------+----------------------+-----------+-----------
    Simon | -8694467316808994943 |     Simon |       Tam
    Jayne | -3415298744707363779 |     Jayne |      Cobb
     Wash |   596395343680995623 |     Hoban | Washburne
      Mal |  4016264465811926804 |   Malcolm |  Reynolds
     Zoey |  7853923060445977899 |      Zoey | Washburne
 Sheppard |  8386579365973272775 |    Derial |      Book

(6 rows)

It works this way, because Cassandra makes certain nodes primarily responsible for certain token ranges.  It then becomes a simple task for the coordinator to return the result set in that order.  If there multiple rows with the same partition key, the results will additionally be sorted by the clustering keys within each partition key.


Memory usage of PostgreSQL I'm using postgreSQL 9.3 database and I have increased the shared_buffers and effective_cache_size in the settings.

Will postgreSQL always take this amount of memory or only when needed?

I'm running my database on an Amazon EC2 instance and I will monitor the memory with a CloudWatch custom metric and I'm now thinking about it if the memory I set in the configuration will always be reserved i.e. appears to be used in the cloudWatch metric.

If this is the case, how can I monitor the actual used memory of postgreSQL?

shared_buffers are statically taken at startup and are never resized.
effective_cache_size is just a hint to the optimizer. it is never allocated. it merely gives a hint of what is going on. so shared_buffers is what you see as taken.


Oracle ASMM - Are specific values ignored? I've inherited an 11.2.0 DB and I'm trying to do some performance tuning.

At present, it's started from a pfile and it looks like ASMM is enabled (SGA_TARGET, SGA_MAX_TARGET and PGA_AGGRIGATE_TARGET are all set). My question is, there are SGA structures also set in the pfile, are they ignored in favour of the ASMM?

E.g. I have the following:

  shared_pool_size         = 800M
  large_pool_size          = 32M
  java_pool_size           = 160M

Probably a fundamental question, but I'm struggling to get a definitive answer online.

These are minimum values for those components.

http://docs.oracle.com/cd/E18283_01/server.112/e16638/memory.htm#i58728

"If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly."


Can we change Log Shipping backup file naming convention Seems the Log Shipping backup files naming convention (especially the date-time stamp in the name) is followed by UTC time, i believe that is default and standard.

I would like to clarify on following cases:

I'm wondering if there any possibility to change the naming convention to follow the date-time as per local time-zone from the server where the BACKUP performed.
Is the naming convention (UTC time stamp as suffix) really matter, since anyway the Log Shipping copy/restore job considers the LSN between each backup file to maintain valid log backup chain.

The aim is to avoid issues during compressing process that i'm following to maintain one zip file per day, and as per following screenshot it would combine additional files (20191015xxxxx) which are belongs different date (2019-10-16)

You cannot automatically change the naming convention since it's controlled by sqllogship.exe (and if you manually change the file names I believe it will cause those files to be skipped.)

The naming convention apparently does matter regardless of the LSNs: https://www.sqlservercentral.com/forums/topic/log-shipping-skipping-files-with-different-naming-convention

I would personally just let them group based on their modified dates (since this is the true order they should be in) but if you want to compress them grouped by their name then you should control that with your compression tool / process, as opposed to the naming scheme SQL Server uses. (It would be simple enough to codify a small script to do this.)


How to downgrade SQL Server 2012 Enterprise to 2012 Express? I would like to say that as per company policy we are downgrading SQL Server 2012 Enterprise to SQL Server 2012 Express.

Any suggestion would be appreciated.

The very first thing you would need to check on that instance would be for all the databases hosted, using any enterprise features from sys.dm_db_persisted_sku_features. In case the database is using any of such enterprise features, you will need to thoroughly verify , but not sure if express can get you there and most probably downgrade wont be that easy.

In addition its better to go through Supported Version and Edition Upgrades while you consider to change the edition and compare features.

There is no direct way to do the downgrade but you can follow the steps of copying the system databases of the enterprise edition instance to the instance of expressed one.Also making sure the new instance being the same with latest update to date with same patch details.

Please refer to below articles which will help you in analyzing how to analyse the downgrade process.

Downgrade enterprise SQL server to standard ( Steps can be somewhat same for express edition as well, just look for features that will support you're application in case you go with express)

Changing SQL Server Editions: Standard, Enterprise, Evaluation and More

Also, make note is good to test it out first for one of you're small servers on a test ENV with such downgrade activity.


Improve BLOB Writing Performance in Oracle 11g I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes.

Is there any buffer that i can resize to improve inserts into this table?
Does the lob buffer still apply for Oracle 11g ?

Thx.

In Oracle, LOB (including BLOB) is stored as:

in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is enabled, unless you specify DISABLE  STORAGE IN ROW
normal LOB - stored in a separate segment, outside of table, you may even put it in another tablespace; for these:

a minimum of CHUNK bytes are allocated and entirely redo-logged (even if LOB has only 1 byte)
there is an internal intermediate index behind a LOB column, which gets contentious on updates and may practically serialize them
access is multi-level and thus relatively slower
NOCACHE is the default
with CACHE option, the CACHE_SIZE_THRESHOLD is not taken into account, so a large LOB can waste your cache

The Metalink note ID 66431.1 describes this and may be of interest to you, if you have access there.


SQL Server 2005 onwards- Capturing Baseline I want to design a process by which I can capture the baseline data of multiple SQL Server instances (2005 onwards) in a single database from where I can get all the info and alerts.

As of now I want to capture info about the below counters:

Free space available on disks
Database File and Log space available
Wait statistics
CPU and memory counters

Is there any free tool that I can refer to and modify if needed or any useful article that can help me to set this up?

As of now I am referring to the article by Erin Stellato in SQLServerCentral.com.

Any help would be greatly appreciated.

Check out Rodney Landrum's SQL Server Tacklebox. It's a free book from Red Gate that includes scripts to do that:

http://www.red-gate.com/community/books/sql-server-tacklebox


ROUTINE TO SELECT FOM A TABLE AND UPDATE ANOTHER TABLE I do not understand how to update a table from another table after a table has been modified.

I have a table with 3 columns like id(INT), distname(VARCHAR)UNIQUE and score(INT), and I have another table with id(INT), name(VARCHAR), points(INT), penalizes(INT)

each time a name gets some points they go to the second table.

I want to write a routine that makes the following

SELECT DISTINCT(name) AS distname,
       SELECT(SUM(points)-SUM(penalizes)) AS score
    FROM table2

and updates the table1 each time before someone makes a select on table1, or each time someone enters data into table2

How would this look like?

The update command for Table 1 to update all names score

Would look like

UPDATE table1 as t1
INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
  FROM table2
  GROUP BY name) t2
ON t1.distname = t2.distname SET t1.score = t2.score;

But better would be a trigger, which update only the rows in table1, username is actualy updated or inserted

DELIMITER $$

CREATE TRIGGER after_table2_Insert
    AFTER INSERT
    ON tabel2 FOR EACH ROW
BEGIN
    UPDATE table1 as t1
    INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
      FROM table2
      Where name = NEW.name
      GROUP BY name ) t2
    ON t1.distname = t2.distname SET t1.score = t2.score;

END$$

DELIMITER ;

And something similar for updating table2

DELIMITER $$

CREATE TRIGGER after_table_update
AFTER UPDATE
ON table FOR EACH ROW
BEGIN
        UPDATE table1 as t1
        INNER JOIN (SELECT DISTINCT(name) AS distname, SUM(points)-SUM(penalizes) AS score
          FROM table2
          Where name = NEW.name
          GROUP BY name ) t2
        ON t1.distname = t2.distname SET t1.score = t2.score;
END$$

DELIMITER ;


SSIS Row Count: Getting a null variable error where there is clearly a selected variable Validation error. Build Files Count VIE [245]: The variable "(null)" specified by VariableName property is not a valid variable. Need a valid variable name to write to.

From what I can tell, this error is thrown when a variable is not assigned to the VariableName property; however, I definitely have a variable assigned, as seen in the image below:

I've deleted the Row Count component and remade it, but the error continues to show up. Here is a snapshot of the Data Flow in question:

I'm not sure if its inclusion in a Conditional Split may be causing this error, but none of the other Row Count components seem to be throwing this error.

Try setting the ValidateExternalMetaData property False and then try, quite possible due to run time allocations.

Also set DelayValdiations to True, wherever applicable.

If it does not solve, then let me know!!


Keep trailing zeros on Timestamp SELECT This is the issue:
I am reading rows with a timestamp column from a table and I need to make sure that the database returns the timestamp with a fixed width. But I am encountering instances where the PostgreSQL database returns the timestamp without the trailing zeros.

The table I am working on contains UUID, Varchar, Smallint and Timestamp.
I understand that I can use "to_char" as explained here, but what I am really looking for is a way tell PostgreSQL to always return the Timestamp in a given format, regardless of zeros.

Here is an example of the output from the Timestamp column I get, which contains a trimmed Timestamp:

"2014-09-26 17:17:45.999"
"2014-11-26 17:17:46"
"2015-03-26 17:17:46.001"
"2015-04-26 17:17:46.002"
"2015-05-26 17:17:46.003"

This will naturally cause a problem for me since the function that is reading from the database expects to get a timestamp in a given format.

Is there a way to make the PostgreSQL always return a timestamp with a fixed width on SELECT * FROM [table] queries?

Below you see how the fields are created:

"created_UTC" timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'),
"last_modified_UTC" timestamp NOT NULL DEFAULT (now() AT TIME ZONE 'UTC')

It seems to me that you need to set the Timestamp column to CURRENT_TIMESTAMP(3). This would set the column to return the time to the three digit precision you are looking for.


Select multiple columns as JSON in a single query How can I combine several columns (of varying types, e.g. int, datetime, nvarchar) as a single JSON-formatted expression in a select query? For example, I have columns col1, col2 and want the query to return a single column where each row like this:

{col1: val1, col2: val2}

Can I use FOR JSON? I'm using SQL Server 2017, but the database I'm querying is 2008 (100) compatibility mode.

All the JSON syntax works regardless of compatibility level.

CREATE TABLE dbo.x(col1 char(4), col2 char(4));
GO
INSERT dbo.x(col1, col2) VALUES('val1','val2');
GO
SELECT * FROM dbo.x FOR JSON AUTO;

Results:

[{"col1":"val1","col2":"val2"}]

If you need it without the square brackets, you'll need to abstract it away so you can assign a column name to the JSON output:

;WITH x(y) AS (SELECT * FROM dbo.x FOR JSON AUTO)
  SELECT PARSENAME(y,1) FROM x;

Results:

{"col1":"val1","col2":"val2"}

And without quotes:

;WITH x(y) AS (SELECT * FROM dbo.x FOR JSON AUTO)
  SELECT REPLACE(PARSENAME(y,1),'"',' ') FROM x;

Results:

{ col1 : val1 , col2 : val2 }

And yes it works with all types:

DROP TABLE dbo.x;
GO
CREATE TABLE dbo.x(col1 char(4), col2 char(4), col3 int, col4 date);
GO
INSERT dbo.x(col1,col2,col3,col4) VALUES('val1', 'val2', 5, GETDATE());
GO
SELECT * FROM dbo.x FOR JSON AUTO;

Results:

[{"col1":"val1","col2":"val2","col3":5,"col4":"2019-02-11"}]

This assumes you don't have the closing square bracket ] in your data. If you do, you'll either need to double it on extraction, before FOR JSON looks at it, or else use a more tedious method than PARSENAME() to removing the outer square brackets.


Match string pattern to any array element I'm trying to match a string pattern to any string in an array:

SELECT 'abc' LIKE ANY('{"abc","def"}') -- TRUE

-- BUT

SELECT 'ab%' LIKE ANY('{"abc","def"}') -- FALSE, I expect TRUE

What is wrong with the second query?

It would be nice if PostgreSQL allowed ANY on the left hand operand, but sadly it doesn't.  So you need the commutator of LIKE.  PostgreSQL doesn't come with one, but you can create your own.

You need a function that reverses the order of arguments to LIKE, and then use that to create an operator that can be used in conjunction with ANY.  User created operators have to be named with symbols, not text, so I'm picking '<~~'.  '~~' is the built-in synonym for LIKE, so I'm adding '<' as a pneumonic to make it "go the other way".  You can pick any unused name you want, though.

create function reverse_like (text, text) returns boolean language sql as $$ select $2 like $1 $$;
create operator <~~ ( function =reverse_like, leftarg = text, rightarg=text );
SELECT 'ab%' <~~ ANY('{"abc","def"}');

You should probably tag the function as immutable, and if you are using v9.6 or above, also as parallel safe.

create or replace function reverse_like (text, text) returns boolean language sql as
 $$ select $2 like $1 $$ immutable parallel safe;


Full Database backup internals My understanding of a full backup is (based on https://technet.microsoft.com/en-us/library/2009.07.sqlbackup.aspx - most of the below is copied, from this link):

Force a database checkpoint and make a note of the log sequence number at this point. This flushes all updated-in-memory pages to disk before anything is read by the backup to help minimize the amount of work the recovery part of restore has to do.
Start reading from the data files in the database.
Stop reading from the data files and make a note of the log sequence number of the start of the oldest active transaction at that point
Read as much transaction log as is necessary.

Backing up enough of the transaction log is required so that recovery can successfully run during the restore and so that all pages in the database are at the same point in time—the time at which the data reading portion of the backup operation completed. The transaction log from the beginning of the oldest active (or uncommitted) transaction to the end of the backup, is required to allow recovery to run. The transaction log back from the backup checkpoint to the end of the backup is required to allow all pages to be brought to the same point in time and for transactional consistency.

The minimum log sequence number (LSN) of the transaction log that is included in the full backup is MIN (LSN of backup checkpoint, LSN of oldest active transaction) and it could be for a transaction that began even before the backup began. This ensures that the restored copy of the database (or whatever you restored—page, file, filegroup, database) is completely consistent.

Questions when I read This book from Microsoft:

The book from Microsoft suggests that MIN LSN - "Determines the minimum LSN required for the log backup by taking the earliest in time between the backup end LSN and the oldest active transaction’s LSN." Is this incorrect and should it have been "start" instead of end?
Same page, the para above that on the book says that the back-up end LSN is - "LSN of the start of the oldest active transaction" - but should'nt it be end of the backup?
Same page, notice also that the image is incorrect - see Transactions A and B start twice - I think the 2nd one should be transaction end instead of start?

Bit confused.

I would try to answer the question as whole. Yes I believe book has some discrepancy as far as my knowledge goes.

One thing for sure is that the backup explanation pic mentioned in the book has some typos. It forgot to use word commit and instead continued with word starts. Like Xact A Starts is a transaction that started much before backup and somewhere at last it committed and still the pic shows Xact S starts it should be Xact A commits similarly for transaction B. This is what you mentioned in point 3

Now let me give you very clean definition of what is MIN LSN here. The MIN LSN would be MINIMUM of (Oldest active transaction which is when transaction C starts , LSN when backup started and checkpoint was fired, in this case red line showing checkpoint). This is always how minimum LSN in case of backup would be calculated. Please note Transaction A started before backup but committed before backup ends so it would not be oldest active transaction and also as per backup operation the pages were read so this would not be the oldest active transaction.

Now let us go to definitions

Backup Start LSN: This is basically "Checkpoint LSN"( As mentioned by Paul Randal in Understanding SQL Server Backups) this is recorded when backup starts and checkpoint is fired.

Backup End LSN: This is basically reading the transaction log again and recording 2 things

The LSN of oldest active transaction.
The LSN when backup operation started, For this I am not sure whether this happens or not IMHO this should be same as Checkpoint LSN or Backup start LSN but may be SQL Server is doing this 2nd time to confirm. I believe it does only what I have mentioned in point 1.

Minimum LSN: The would be minimum of ( LSN of oldest active transaction, Checkpoint/backup start LSN ). The definition in the BOOK is little bit confusing.


ERROR 1130 (HY000) not allowed to connect to this MariaDB server facing issue in granting permission I am trying to connect to mariadb running different container.Getting below error
$ mysql -u root -h test-mysql --port 3306

ERROR 1130 (HY000): Host 'test_1_73f6dacd1d23.test_bluenet' is not allowed to connect to this MariaDB server

Mariadb Server:
I tried the following
MariaDB [(none)]> SELECT host, user FROM mysql.user;

+--------------+------+
| host         | user |
+--------------+------+
| 00a7a6a8d0af |      |
| 00a7a6a8d0af | root |
| 127.0.0.1    | root |
| ::1          | root |
| localhost    |      |
| localhost    | root |
+--------------+------+
6 rows in set (0.001 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'root'@'%';

ERROR 1133 (28000): Can't find any matching row in the user table

The GRANT statement doesn't work because the user@host combination root@'%' doesn't exist (yet). You can create it and then re-run the GRANT:

CREATE USER root@'%' IDENTIFIED BY 'your_secret_password_here';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The WITH GRANT OPTION part gives the user the power to GRANT privileges to other users.

Note that in order to be able to connect from the other container, you may also have to remove or comment out any restrictions in your configuration (i.e. files like /etc/my.cnf, /etc/my.cnf.d/server.cnf, /etc/mysql/*.cnf) such as any bind-address settings:

bind-address  = 127.0.0.1

Comment out with a '#':

# bind-address  = 127.0.0.1

... and then restart MariaDB.


Spatialite: use of virtual spatial index for distances I'm currently learning to use spatial access methods for query optimization. I'm going through the examples in spatialite cookbook and got stuck in here
http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/pp-adjacent.html

According to the tutorial, in order to find the pairs of populated places that their distance is < 1km we have to do this query

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
    populated_places AS pp2
    WHERE GeodesicLength(
      MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
      AND pp1.id <> pp2.id
    AND pp2.ROWID IN (
      SELECT pkid
      FROM idx_populated_places_geometry
      WHERE pkid MATCH RTreeDistWithin(
       ST_X(pp1.geometry),
       ST_Y(pp1.geometry), 0.02))
    ORDER BY 2;

Which throws an error because of no longer use of geocallback functions RTree***. I checked the update on this and this has been substituted by the use of virtual spatial index. We have to use a subquery of the form

    SELECT ROWID
    FROM SpatialIndex
    WHERE
       f_table_name = <table_name>
       AND search_frame = <some_geometry>

So i tested this

    SELECT pp1.name AS "PopulatedPlace #1",
      GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
      pp2.name AS "PopulatedPlace #2"
    FROM populated_places AS pp1,
    populated_places AS pp2
    WHERE GeodesicLength(
      MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
      AND pp1.id <> pp2.id
    AND pp2.ROWID IN (
      SELECT ROWID
      FROM SpatialIndex
      WHERE
       f_table_name ='populated_places'
       AND search_frame = pp1.geometry)
    ORDER BY 2;

And the result set was awfully wrong

   PopulatedPlace #1    Distance (meters)   PopulatedPlace #2
              Ariano    0.000000             Ariano
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
   Campolongo Maggiore  0.000000             Campolongo Maggiore
              Ariano    0.000000             Ariano

Could please someone explain this behavior and/or give an example of proper use of the virtual spatial index in order to obtain distance queries?

I know it is late but I recently stumbled accross the same problem. It is not working in your example because the search_frame parameter act as a filter and is equivalent to the function RTreeIntersect.

Here since you use a point it will only look for point wich intersect togetether (when using their bounding box).
The idea is to use a bouding box centered on the pp1 point, with side the length of the desired distance.

SELECT pp1.name AS "PopulatedPlace #1",
  GeodesicLength(MakeLine(pp1.geometry, pp2.geometry)) AS "Distance (meters)",
  pp2.name AS "PopulatedPlace #2"
FROM populated_places AS pp1,
populated_places AS pp2
WHERE GeodesicLength(
  MakeLine(pp1.geometry, pp2.geometry)) < 1000.0
  AND pp1.id <> pp2.id
AND pp2.ROWID IN (
  SELECT ROWID
  FROM SpatialIndex
  WHERE
   f_table_name ='populated_places'
   AND search_frame = ST_Expand(pp1.geometry, 500.0)
ORDER BY 2;

To do that we simply use the function ST_Expand which return the bounding box envelopping the geometry (here it is the point) and expand it uniformly.

So we filter the search as follow : only pp2 point inside the square centered on pp1 with 1000 meter side length will be considered.


Creating Duplicate Table in SQL SERVER 2014 I am trying to create a duplicate table in SQL SERVER. I changed the table name but all other objects like constraints are same. Do i have to change all the objects as they exists in the base table or i can have same objects name?
I know it might sound a silly question but i am new to this.

Explicitly named constraints must be uniquely named within a database.

If they are not unique, you'll see errors like this:

  Msg 2714, Level 16, State 5, Line 10
  There is already an object named 'PK_SomeTable' in the database.
  Msg 1750, Level 16, State 0, Line 10
  Could not create constraint. See previous errors.

This is especially pertinent for creating #temp tables.  Never specify the name of a constraint when creating a #temp table, since concurrent executions of the code will fail with the above error.

Take for example:

CREATE TABLE dbo.SomeTable
(
    ID int NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
);

CREATE TABLE dbo.SomeTable2
(
    ID int NOT NULL
        CONSTRAINT PK_SomeTable
        PRIMARY KEY CLUSTERED
);

The command fails because PK_SomeTable is defined twice, once in each table.  However, if we re-code the 2nd statement:

CREATE TABLE dbo.SomeTable2
(
    ID int NOT NULL
        CONSTRAINT PK_SomeTable2
        PRIMARY KEY CLUSTERED
);

Now, it works.

For #temp tables, you'd use the following construct:

CREATE TABLE #SomeTable
(
    ID int NOT NULL
        PRIMARY KEY CLUSTERED
);


DB Schema for online market I'm just search for advice. I have . Every Product type has its own set of characteristics. The Big problem is when someone add new Product type, aslo need create new table with product characteristics. I can see only one solution without DBA, saving all products characteristics in one table but this is not good.

"Entity-Attribute-Value" or EAV design pattern allows you to create new attributes for products without modifying the database structure.

Aaron Bertrand has an excellent article on the advantages and pitfalls of this design over on SQLBlog.org.

A simplified example, based on your requirements:

CREATE TABLE dbo.Products
(
    ProductID int NOT NULL
        CONSTRAINT PK_Products
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.AttributeTypes
(
    AttributeTypeID int NOT NULL
        CONSTRAINT FK_AttributeTypes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , AttributeTypeName nvarchar(100) NOT NULL
);

CREATE TABLE dbo.ProductAttributes
(
    ProductAttributeID int NOT NULL
        CONSTRAINT PK_ProductAttributes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductID int NOT NULL
        CONSTRAINT FK_ProductAttributes_ProductID
        FOREIGN KEY
        REFERENCES dbo.Products(ProductID)
    , AttributeTypeID int NOT NULL
        CONSTRAINT FK_ProductAttributes_AttributeTypeID
        FOREIGN KEY
        REFERENCES dbo.AttributeTypes(AttributeTypeID)
    , AttributeValue nvarchar(100) NOT NULL
);

This will insert some sample data:

INSERT INTO dbo.Products (ProductName)
VALUES ('Oven Trays')
    , ('Cars')
    , ('Vacuum Cleaners');

INSERT INTO dbo.AttributeTypes (AttributeTypeName)
VALUES ('Manufacturer')
    , ('purpose')
    , ('diameter');

INSERT INTO dbo.ProductAttributes (ProductID, AttributeTypeID, AttributeValue)
VALUES (1, 1, 'Farberware')
    , (2, 1, 'General Motors')
    , (3, 1, 'Hoover')
    , (1, 2, 'cookies')
    , (2, 2, 'driving')
    , (3, 2, 'cleaning floors')
    , (1, 3, '12"');

This shows how we get desired output:

SELECT p.ProductName
    , att.AttributeTypeName
    , pa.AttributeValue
FROM dbo.Products p
    INNER JOIN dbo.ProductAttributes pa ON p.ProductID = pa.ProductID
    INNER JOIN dbo.AttributeTypes att ON pa.AttributeTypeID = att.AttributeTypeID
ORDER BY p.ProductName
    , att.AttributeTypeName;

╔═════════════════╦═══════════════════╦═════════════════╗
║   ProductName   ║ AttributeTypeName ║ AttributeValue  ║
╠═════════════════╬═══════════════════╬═════════════════╣
║ Cars            ║ Manufacturer      ║ General Motors  ║
║ Cars            ║ purpose           ║ driving         ║
║ Oven Trays      ║ diameter          ║ 12"             ║
║ Oven Trays      ║ Manufacturer      ║ Farberware      ║
║ Oven Trays      ║ purpose           ║ cookies         ║
║ Vacuum Cleaners ║ Manufacturer      ║ Hoover          ║
║ Vacuum Cleaners ║ purpose           ║ cleaning floors ║
╚═════════════════╩═══════════════════╩═════════════════╝


Creating a bar chart from datatable I'm writing a console application that fetches data from database (SQL) and writes to an excel sheet. Now I want to add a chart in the workbook according to the datatable values. How can I create chart in the sheet before saving? How will I use dataset values to create chart?

Thank you.

If you are using csv , you can not do this. But if you are willing to use other tools like Microsoft SQL Server Reporting Services or Crystal Reports you may do this.

SSRS Chart Example, here another one with code example.

If you can not install Reporting Services Server or do not want to, you can still use it for basic reporting purposes. Take a look at here,Microsoft Reporting without SQL Server Reporting Services.

Note that , I gave you examples for reporting services since it is easy to use out of box and in most cases free. But there are a lot of other tools exists which may be more suitable/powerful for your use case.


is it good to add index on column used in group by clause? I have a query like-

select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc;

will it be faster if I am going to add index on x?

Adding an index on X will improve your performance here a bit, ideally you'd want a composite key for X then Z as you want to do analysis for each X of everything where Z is over the 100 mark so for the index to be truly effective you need both.

At that point however you're building an index for a specific query so will the additional processing on insert / updates / deletes / re indexing / storage space all worth it for the one query (if its something that's going to be used over and over and in other locations then it may be, it may also be useful in different queries you already have)

Depending on the rest of your table structure and content you could have your clustered key as X,Z and that would do the job. if you've got columns a,b,c,d,e,f,g where a is an identity field that you're clustered on then this as a secondary index would help.

Ultimately however I've found the only way to know if an index is going to help, is to test it. The best will in the world can't ensure every specific index will help in the overall system only testing can do that.


MYSQL wont use index in "IN(SELECT ..)" sub clause I have two tables in MYSQL with target fields set as index / primary index already. Then I ran the below sql query.

  EXPLAIN
  SELECT charge FROM ec_provider_account_option
  WHERE accountPermissionId='4'
  AND accountTypeId
      IN (select accountType from ec_provider where id = 3)

According to the result no index ref used obviously. The in clause "select accountType from ec_provider where id = 3" uses index by querying alone.

Plus, if I replace the subsquery in(select .. from ..) with in(1,2,3), it will return the same result, and using index ref.

 EXPLAIN
    SELECT charge FROM ec_provider_account_option
    WHERE accountPermissionId='4' and accountTypeId
          IN(1,2,3)

How comes that ?

That's right, so just use a join - MUCH faster:

SELECT charge FROM ec_provider_account_option a,
                   ec_provider b
  WHERE a.accountTypeId = b.accountType
  AND a.accountPermissionId = '4'
  AND b.id = 3;


PostgreSQL - getting Too many client repeatedly I am running PSQL with PostGis extension. After the start of server, after about 3 weeks, I start getting fatal errors for connections with "too many clients already". When I restart the server, problem is gone, but is back again after aprox. 3 weeks.

If I run pg_stat_activity, I got

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

"active";2
"idle";944

And all idle queries are the same - SELECTs.

I cannot debug this problem and restarting server all over is not a real solution.

Your application has a connection leak.
There is no way to fix this on the database side - you'll have to change the application so that it closes connections again.

I guess you set max_connections so high to work around this problem (1000 is too high).


OrderBy int slow for large table join I have roughly 1.5 million records.  This query works fine for smaller data, but it gets a second slower for every few hundred thousand records. For 1.5 million, the query is noticeably struggling.

I mocked up a whole new Code First project to verify the problem is independent of my implementation, and to provide this example query:

    SELECT TOP 1 Widgets.*
    FROM [WidgetSandbox].[dbo].[Widgets] Widgets
    INNER JOIN [WidgetSandbox].[dbo].[Status] Statuses ON Widgets.StatusId = Statuses.Id
    INNER JOIN [WidgetSandbox].[dbo].[Colors] Colors ON Widgets.ColorCode = Colors.ColorCode
    INNER JOIN [WidgetSandbox].[dbo].[Sizes] Sizes ON Widgets.SizeId = Sizes.Id
    WHERE Statuses.Name = 'Available'
    AND Colors.Name = 'Red'
    ORDER BY Sizes.DiameterInches

DiameterInches is an int, as a metaphor for "PriorityLevel" in my actual code.

If I comment out ORDER BY Sizes.DiameterInches, it returns immediately, but if I want to find "the smallest available red widget", then it crawls.

Is there a better way?

Execution plan XML: https://gist.github.com/RobertBaldini/57c8b61d135cc5c84c38b2da243611ad

DDL: https://gist.github.com/RobertBaldini/3740c7bb85eea47d7fe63cb8602ac2d6

Repo (data loader takes several minutes): https://github.com/RobertBaldini/WidgetSandbox

You should retype the various Name columns from nvarchar(max) to nvarchar([right size]). It's unlikely that names will be up to 2GB in length, and making them max sized prevents them being used as a key in an index. A good general rule of thumb is to avoid large object data types wherever possible.

You'll probably need to make that change to the EF code, but in T-SQL:

-- Guessing at 100 characters maximum
ALTER TABLE dbo.[Status]
ALTER COLUMN Name nvarchar(100) NOT NULL;

ALTER TABLE dbo.Colors
ALTER COLUMN Name nvarchar(100) NOT NULL;

ALTER TABLE dbo.Sizes
ALTER COLUMN Name nvarchar(100) NOT NULL;

ALTER TABLE dbo.Widgets
ALTER COLUMN Name nvarchar(100) NOT NULL;

I have also changed the column definition to NOT NULL there.

Anyway, given indexes:

CREATE NONCLUSTERED INDEX IX_dbo_Sizes__DiameterInches
ON dbo.Sizes (DiameterInches);

CREATE NONCLUSTERED INDEX IX_dbo_Widgets__SizeId_ColorCode_StatusId__Name
ON dbo.Widgets (SizeId, ColorCode, StatusId)
INCLUDE (Name);

...you should end up with a plan that avoids lookups and sorts, something like:

With the small number of rows in Status and Colors, it is probably not worth indexing their Name columns right now, but that could change over time. In any case, if the name columns should be unique, you should constrain them to be so using a unique constraint or unique nonclustered index, for example:

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Colors__Name
ON dbo.Colors (Name);

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Status__Name
ON dbo.[Status] (Name);

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Sizes__Name
ON dbo.Sizes (Name);

CREATE UNIQUE NONCLUSTERED INDEX UQ_dbo_Widgets__Name
ON dbo.Widgets (Name);

Second approach

With the same indexes, if you are able to change the SQL, you could also separate out the Color and Status lookups:

DECLARE
    @StatusId integer,
    @ColorCode nvarchar(6);

SELECT @StatusId = ST.Id
FROM dbo.[Status] AS ST
WHERE ST.Name = N'Available';

SELECT @ColorCode = C.ColorCode
FROM dbo.Colors AS C
WHERE C.Name = N'Red';

SELECT TOP (1)
    W.*
FROM dbo.Widgets AS W
JOIN dbo.Sizes AS S
    ON W.SizeId = S.Id
WHERE
    W.StatusId = @StatusId
    AND W.ColorCode = @ColorCode
ORDER BY
    S.DiameterInches;

This makes for a simpler set of operations that also happen to make life easier for the optimizer:

Note also that Unicode string literals should be prefixed with N for correct data typing.

Oh, and you should also patch your instance. It is currently SQL Server 2008 Service Pack 1 - Service Pack 4 has been available for some time now.