texts:
I'm busy trying to migrate an informix database schema (with no data) to an informix database running on a different server.

I have exported the schema from the database using

dbschema -t all -d olddb schema.sql

Then I am trying to import it into my new database

dbimport /tmp/schema.sql -d newdb

I am getting the error

Cannot open sql script file

Looking through informix forums they are saying that this is because it cannot find the sql file but how can that be when I a specifying where it is?

Also if I go into the directory where the sql file is and try

dbimport schema.sql -d newdb

I get the same error


texts: The multi-part identifier "xxx" could not be bound

texts:
The following is a HAVING clause from a small part of a very large stored procedure which I have inherited from some other devs (external);

HAVING (SELECT COUNT(*) FROM
(
    SELECT *
    FROM dbo.ContractDailyRoomAllocation da2
    WHERE da2.ContractId = DA.ContractId
    AND da2.RoomTypeId = DA.RoomTypeId
    AND da2.Date >= @FromDate AND da2.Date < @ToDate
    AND da2.IsSold = 0
    AND da2.ReleaseDay <= DATEDIFF("d", @TodayDate, da2.Date)) T) = @StayDates
    AND ( (@AllowOnRequestBookings = 'False'
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)
      >= 0 ) )
      AND ( (@AllowOnRequestBookings = 'True'
      OR dbo.GetAvailablePackageRoomCount(da2.ContractId,da2.RoomTypeId,@FromDate,@ToDate)
      > 0 )
)

The last 2 AND's give me the following error messages:

  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
  The multi-part identifier "da2.ContractId" could not be bound.
  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 176
  The multi-part identifier "da2.RoomTypeId" could not be bound.
  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
  The multi-part identifier "da2.ContractId" could not be bound.
  Msg 4104, Level 16, State 1, Procedure GetAvailableHotelRooms, Line 177
  The multi-part identifier "da2.RoomTypeId" could not be bound.

Any ideas?


texts:
I posted this a while back: Can't seem to understand why STUFF isn't working and @bluefeet was able to help me out very graciously...

Now I've got a similiar issue with this STUFF()!  Basically I have three tables one table has Company names Company.  Another table has Issues Issue and finally the third table has Issues that happened in a Company, call it PAndCIssueLocation.

For instance assume you have an issue that happened at two plants...so you end up with one Issue record like so:

Issue Table
ID  Title
1   Robot is down

And you've got a company table:

Company Table
ID  Name
1   Auburn Hills
2   Detroit

And finally the third table:

IssueID  CompanyID
1        1
1        2

That is Issue ID = 1 happened at two company locations (Detroit, Auburn Hills).  I was trying to use STUFF in a report so I can comma seperate these two items (company names) however, what is happening is the same company name is repeating itself X amount of times (I believe X is the total number of issues in my database).  Here is the code I have:

SELECT
    i.IssueID AS IssueID,
    i.PAndCAuditDate AS AuditDate,
    i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
    i.CustomerIssueNumber AS OldId,
    i.PAndCValidatedBy AS ValidatedBy,
    i.PAndCValidatedComments AS ValidatedComments,
    i.Title AS Title,
    i.OpenDate AS IssueOpenDate,
    i.PAndCAuditors AS Auditors,
    i.PAndCAuditees AS Auditees,
    i.Description AS Description,
    i.PAndCRootCause AS RootCause,
    i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
    i.PAndCPreventiveActions AS PreventiveActions,
    DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
    i.TargetDate AS IssueTargetDate,
    i.ClosedDate AS IssueClosedDate,
    CASE WHEN i.ClosedDate IS NULL AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0) < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0) THEN 1 ELSE 0 END AS IsOverdue,
    s1.CompanyHasType
    --s1.Name
FROM
    Issue i
LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID

Notice this portion where the STUFF is happening:

LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID
) s1 ON
s1.IssueID = i.IssueID

This is the portion that is giving me an issue.  I know I have to somehow relate the issue back but I don't know exactly how!

Any help is much appreciated...


texts: Oracle 10.2.0.1.0 listener process stopped

texts:
When I am checking my servers I see that there is an error about listener process stopped.
I logged in and checked the status of listenere. please see the below status;

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
TNS-12541: TNS:no listener
  TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
      Solaris Error: 146: Connection refused
        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
          TNS-12541: TNS:no listener
            TNS-12560: TNS:protocol adapter error
              TNS-00511: No listener
                Solaris Error: 2: No such file or directory>

I am thinking of just running the start command but my concern is why is this issue arised ?


texts: What is the better way to handle a different types of measurement in grocery store items database

texts:
While designing a DB for grocery shop, I came across a requirement of "same product available in different measurements" - e.g., Lets say, Green Peas available in 250gms, 500gms, 1Kg and so.

My question is :
Is it good practice to have duplicate entry of product with different measurement in products table or keeping the measurements in different table with product id?


texts: Use users / logins from another database

texts:
Is it possible to use logins/users from one database(F) in another(S) one? I know I can copy it but, when I add user to (F) database I have to copy again them all (or add the same user to (S) database) - I want to avoid that...

All I want is to know if is it possible to set Security/Users to Users from another database.


texts: How to check if a record is linked with two or more values in a left join

texts:
In MySQL, I have a table of products connected with a different table with categories.

I often show the list by making a LEFT JOIN and GROUPING the results:

e.g. LEFT JOIN gives me:

Name | Category
----------------
Cool cap | clothes
Cool cap | hats

Then GROUPING by ID gives me 1 result.

The question: How can I show a list of products with category='clothes' AND category='hats'? Because the item won't show up because each category shows on a new line.


texts: How I can place connection metadata in order to trace my queries on RDS performance insights fot my postgresql db?

texts:
In my infrastructure I need to trace my queries to the execution location from AMAZON performance insights in case of a huge database load so I can refactor them. Each query is dynamically generated hence in my codebase are hard to be traced, also we are in a refactorring process as well.
Therefore I want to place on each query extra metadata so I can trace them better from my RDS perfomrance insights panel in case of a high load. Do you have any idea how I can do this?
The trace needs to be in the following 3 axis:

Project
File
Line

So if I could provide this info at query execution level in my code I could trace them better.


texts: Oracle 11gR2 + RedHat 6.5 cannot start sqlplus

texts:
I cannot start sqlplus
I've read everything I found, tried everything and still I am getting "command not found"

I have a user oracle, I did export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

.bash_profile is:

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
umask 022
# User specific environment and startup programs

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_HOME

PATH=$PATH:$HOME/bin

export PATH


texts: How get in SQL pairs of connections in a FW Logs

texts:
I have a database with orign_ip, destination_ip, and other columns.
I am looking for a way to get in SQL just the connections between two IP's, I need to get just the list.
This is my first step :

SELECT DISTINCT orig_h , resp_h from gustavo.conn;
 - 15.214.135.70                15.214.159.100
 - 15.214.136.45                15.214.137.70

But How I can get from this result the just the rows that match with

15.214.135.70               15.214.159.100


texts: Three-sided many-to-many relationship design

texts:
I have 3 entity that have a relation to each other. My entities are:

OPTION
PLAN
PRICE

Relationships:

PLAN and OPTION have a many-to-many relationship
Map of PLAN and OPTION has a many-to-many relation with PRICE

any plan has some options. an option of one plan has some price.
My possible solutions:

I can create an PLAN_OPTION_MAP table and then map it with PRICE and create PLAN_OPTION_MAP_PRICE_MAP table.

I can create an PLAN_OPTION_PRICE_MAP table with id of all 3 tables that primary key is made by composite of them

Which is the better solution?


texts: Issues deploying SSIS package to SQL Server in Azure AD Domain Services (AADDS) environment

texts:
We use Azure Active Directory (Azure AD) in my organisation, and we recently deployed Azure AD Domain Services AADDS) to handle a SQL Server 2019 we use internally.
My Azure AD domain is (fictitiously) contoso.com, while my AADDS is aaddscontoso.com. Microsoft strongly recommends that users do not create their managed domain (the AADDS deployment) with the same domain name as their Azure deployment. They also strongly recommend against using something like contoso.local. So of I went to create our AADDS as aaddscontoso.com and everything seems to be working perfectly.
The Azure AD works with AADDS, (at least with the Vanilla way we have set-up) is:

All of all laptops, desktops, etc are joined to the Azure AD through the Azure AD join (the cloud method)
We can access the managed domain and its hots (including the SQL server) in our Azure Network via Azure VPN. For instance, right now I am VPN into our Azure network and I can remote desktop to the SQL Server.

Here is where the problem starts. Today I just noticed that trying to deploy an SSIS package from Visual Studio from my laptop (whilst connected on VPN) won't work.
I tried using Windows Authentication and the authentication fails with the following error:

The target principal name is incorrect.  Cannot generate SSPI context.
(Microsoft SQL Server, Error: 0)

So I tried using the Active Directory Password Authentication option, then I got the following error:

A connection was successfully established with the server, but then an
error occurred during the login process. (provider: SSL Provider,
error: 0 - The certificate chain was issued by an authority that is
not trusted.) (Microsoft SQL Server, Error: -2146893019)

Then I tried to be clever and create a SQL Login with the right administrative rights and the connection worked. However it fails on the deployment of the package, saying that you cannot use a SQL account for this. At least this proves that the connection to the server is not the issue.
I THINK that this problem could be simplified as follows: I want to deploy an SSIS package from the PC in DOMAIN A to a SQL server found in DOMAIN B. How can I do that? (Granted that this is not really it, but SSIS seems to think it is).


texts: How to recover SA account password without restoring it?

texts:
I forgot my SA password and resetting the password is out of the question because internal applications are using it and will will affect them. I also don't have an option to change the SA credentials for those applications.

This is why I must know what the password is WITHOUT restoring it.

Is there any method for that?


texts: Oracle systimestamp result differs in db server and client

texts:
I am getting some strange output while executing the below command on db server and on client machine connected to same db server.

On DB server

SQL> select systimestamp from dual;

systimestamp

25-JUN-15 06.16.13.424135 PM +08:00

On Client Machine

SQL> select systimestamp from dual;

SYSTIMESTAMP

25-JUN-15 02.16.57.936662 AM -08:00

Below output for Reference

SQL>  select dbtimezone, sessiontimezone from dual;

DBTIMEZONE                     SESSIONTIMEZONE

+00:00                            +08:00


texts: Primary Key Type's Effect on Performance in Oracle?

texts:
In MS SQL Server, it is well documented that the type of the primary key can have a dramatic effect on performance. Using narrower types and sequential values significantly improves insert performance when using SQL Server, which is why using integers instead of guids for surrogate keys tends to make sense.

However, I cannot find any references to the same discussion for Oracle. Someone asked me to look at a schema for them that uses varchar2(32)'s for all of the primary keys, which seems much wider than needed. Does this matter in Oracle?

Bonus points for anyone who can refer me to a good blog like Kimbery Tripp's but in Oracle world.


texts: Optimizing the read of child records of many parents

texts:
The main table has a complex query that selects the jobs that are to be displayed on the current workstation.  In addition to the main job record there are a couple of child tables with a few dozen records per parent.  All of this data must be pulled in in order to display the current status of the job.

The child queries are Select * From Stations Where RecordID in ([list of IDs of the jobs being displayed]).  The profiler is flagging this as bad.  In digging I realized there wasn't an index on RecordID column, I added it--and it didn't help one bit.

How would I go about improving the performance here?  Long ago I tried reading the child records with the parents, that bombed horribly.  I can't imagine doing separate queries for each RecordID wouldn't introduce more wait time even if the database was more efficient.

Any other approaches here?

Edit:  While investigating the temporary tablesI stumbled upon the Explain command and now I think the profiler might be flagging it erroneously--when I fed my query into the workbench with an explain it came back with only one line, almost 16,000 rows.  That's correct, though, I need them.  (I can't cache the results as a string in the main record because an important piece of the display is how it compares to the schedule.)

Is there nothing to be done then?


texts: Deploying a cube where database name has been changed

texts:
I have an issue where I have renamed the database for which my cube deploys to from, say, SalesAndStuff to Sale.

This has been no issue, I have changed the database name in Management Studios etc.

My issue is that when I try to deploy my cube now from Visual Studio I get the error that

  The database with the name of 'SalesAndStuff' already exists in the 'abc'
  server

Which I have read is because SSAS figures that the database I wish to deploy my cube to should be to the database with the same name as the database ID created when I created the cube originally.

This name is SalesAndStuff. I have configured my cube under Project -> Cube properties -> Deployment -> Deploy to database Sale but for some reason this isn't recognized.

Any ideas how I can avoid this issue? Am I required to change my deployment properties to the original database, and subsequently rename the database in management studio?

The cube is used by many people who uses it for Excel reports etc. so I don't want to alter any Connection strings towards the cube.


texts: I have a software system which queries data via OLEDB from an external SQL Server 2005. The query request is sent every 15 minutes. I want to verify that the SQL Server is receiving the query requests. How can I?


texts:
I've just upgraded our Data warehouse to SQL 2016. I'm seeing some really interesting graphs in the Query Store (I love this feature!). Below is the weirdest example i've seen.
22 plans for the same query.

It's making me consider performance tuning of my ETL process and the pros and cons of temporary tables and how you could influence execution plan behavior.

My ETL process uses a number of stored procedures which use mix of standard and temporary #tables as staging tables. The #tables are typically used once and then dropped. Some are only a few thousand rows. Some are millions. SSMS advises that there are missing indexes, but on smaller tables would they make enough of difference to be worth the effort of adding them? Are better statistics sufficient?

I've just read this Brent Ozar blog post about Statistics on Temp tables, and Paul White's article on Temporary Tables in Stored procedures

It says that statistics are created automatically, when the #table is queried, and then presumably used by the optimizer.

My questions are: Is there much point or benefit in creating an index on a #table. And/or: Is it worth explicitly updating statistics as a step in the stored procedure before using it in queries given they're only used once.

Are the additional steps and overhead worth it? Would it result in significantly better or different execution plans?


texts: MySQL lower_case_table_names - Tables vs Views

texts:
I've set lower_case_table_names=2 in my.ini.

When I create tables, the case is respected. However, when creating views, everything is changed to lower case.

Also, when I generate my scripts using dbForge, the tables keep their case and the views are in lower case.

Is this normal/expected? How can this be fixed?

Update #1

Tried generating scripts for my functions and there too the case is respected.

Update #2

Creating the following table: TableCamelCase, will show up in both dbForge and Workbench respecting the case specified.

Creating the following view: ViewCamelCase, will show up in lower case in both dbForge and Workbench.

The generate scripts from dbForge will respect the case for the table and match the lower case of the view.


texts: If archiving is disabled and the last redo log file is full, why LGWR continues by overwriting the first available active file, not inactive file?

texts:
I found the following sentence from Oracle documentation.

  If archiving is disabled (the database is in NOARCHIVELOG mode), then when the last redo log file is full, LGWR continues by overwriting the first available active file.

My question is, active log file must be used when Oracle doing instance recovery, so if active log files are overwritten, this will cause some problem if you encountered an instance failure now. If so, why LGWR writes to the first active (instead of inactive) log files?

Thanks.


texts: Is it useful to have the SQL Server instance root directory on a separate drive?

texts:
I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), however I've recently been given a pre-installed machine which is running an instance name other than the default and they've configured the instance root directory to be on the D drive along with the mdf files. This means that on what would normally be a relatively clean drive with just folders and database files on it I now have a full installation of the SQL Server binaries as well.

i.e. I now have the following :

C:\Program Files\Microsoft SQL Server\ --Base Install
D:\Microsoft SQL Server\MSSQL10_50.MyInstance --Instance Binaries
D:\Microsoft SQL Server\MSSQL10_50.MyInstance\MSSQL\DATA --Data Files
E:\Microsoft SQL Server\MSSQL10_50.MyInstance\MSSQL\LOGS --Log Files

Where normally I would run with something like :

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\ --Base Install & Default Instance Binaries
D:\MSSQL\DATA --Data Files
E:\MSSQL\LOGS --Log Files

I can understand why having a separate instance binary folder is necessary, but I can't see why it would be useful to put all those binaries on a separate drive.

Can anyone tell me why it might be a reasonable thing to do? Or perhaps it just makes no difference at all? To me it just seems terribly untidy...


texts:
I'm busy setting up a MS SQL Server instance on a Windows Server

The server is Windows Server 2012 R2 and the version of MS SQL Server is 2014 Essentials.

I've installed MS SQL and created my database, I've also installed HeidiSQL to test making connections to the server.

All this software is running off of the same machine.

When I run the MS SQL Management studio it connects to

Server name    : SPKWINVMSERVER\SQLSERV
Authentication : Windows Authentication

So I've tried to create a login using the management studio

right click Security > New  > Login

and I've created the login as

Login name : mssqladmin
password   : #FongKong123

I gave this user all the server roles for testing and it seemed to create ok.

However when I try to connect to it using this Login I've just created using HeidiSQL it fails

The connection details I'm using in HeidiSQL to connect are

Network type : Microsoft SQL Server (TCP/IP)
Hostname     : SPKWINVMSERVER\SQLSERV
User         : mssqladmin
Password     : #FongKong123
Port         : 1433

The above connection seems to work when connecting to the SQL Server on the server itself.

If trying to connect with another computer on the network (also using HeidiSQL) I imagine I won't be able to use SPKWINVMSERVER\SQLSERV as the hostname but it doesn't work on IP address which is in this case 192.168.20.67

Network type : Microsoft SQL Server (TCP/IP)
Hostname     : 192.168.20.67\SQLSERV
User         : mssqladmin
Password     : #FongKong123
Port         : 1433

What did I miss?


texts: What happens as a database grows to the HDD's lmit?

texts:
I'm running a PostgreSQL database and from what I read, the database is unlimited in size. Is there any material I can read to understand what occurs when the HDD fills to capacity? What sort of monitoring must be done in order to insure continuous operation?

One solution would be to create an arbitrary limit of free space and once that free space has been reached, you would force the user to purge off older data. Is that a proper solution? I'm interested in learning what other solutions exist and how databases are properly maintained.


texts: I am using some commands on mysql client numerous no of times. i want to work smartly with less usage of keyboard. Is there any way to create aliases for mostly used commands like 'show slave status\G','show databases;', 'show tables;' on mysql client just like aliases in bash. Thanks in advance.


texts: Optimised Schema for City-to-City Distance Table

texts:
This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relationships for the data represented below. I want to scale it vertically (new rows), instead of adding new columns as city names horizontally (new columns).

So far, I could only deduce two tables:

City tables with primary key. (6 rows)
Distance table with primary key. (13 rows)

How would I relate these two tables? Or should I proceed with one table as is in the representation below?


texts:
We have a table that is append+read only. We'd like to have multiple applications consume all new rows that show up in that database, in a cheap and easy way (without change tracking).

The simple suggestion of having each app record the last ID doesn't seem safe. Suppose 3 connections (2 writers and 1 readers) do this:

W1: BEGIN TRAN, INSERT 1000 Records (IDs 1-1000)
W2: BEGIN TRAN, INSERT 1000 Records (IDs 1001-2000)
W2: COMMIT
R1: SELECT * WHERE Id > 0 (last sync value)
W1: COMMIT (or possibly ROLLBACK)

In this case, the reader would get records 1001-2000 and save 2000 as the highest ID. Obviously that's not correct. Assuming the various writers are running at varying speeds (so we can never assume they'll commit in order, due to latency or perf), what options are there?

Must we resort to something like change tracking, or require every application to save the last (batch*writers*x) IDs and re-read them to make sure they didn't miss any?

Rollback and other gaps in the sequence (MSSQL can jump the IDs by 1000; pretty sure other DBs do that under certain situations) make it complicated to try to guess if rows were skipped. Plus the batch inserts might not be a proper batch; they could consist of multiple INSERT statements, meaning each tx might not even have a contiguous block of IDs assigned.

We're currently using MSSQL 2012 if there's a hack specific to that system, but I'm more interested in general.


texts:
I have two tables, Results and Teams

Teams has the following columns:

team_id
team_name

Results has the following columns:

match_id
home_team (foreign key references teams [team_id])
away_team (foreign key references teams [team_id])
score
yellow_cards
red_cards

When I write query:

select team_name, score
FROM teams, results
WHERE results.home_team = teams.team_id
  AND results.away_team = teams.team_id

it would give me empty set.

Is it issue with the fact that I use same foreign key for two different columns in the same table?


texts: Cartesian Product SQL / Inserts with multiple Values Clauses

texts:
I want to create cartesian product sql query.But,I'm getting the error.Where I wrong?

alter table #Personel
(
Ad varchar(100)
)

alter table #Masa
(
 MasaAdi varchar(100)
)

insert into #Personel values('deneme1'),('deneme2'),('deneme3')
insert into #Masa values('Masa1'),('masa2'),('masa3')
select Ad,MasaAdi from
(
   select ROW_NUMBER() over(partition by MasaAdi order by ID,Ad ) as Sira
      ,ID,Ad,MasaAdi
   from (
   select newid() as ID,Ad,MasaAdi from #Personel
   left outer join #Masa on 1=1
   ) t
) t2 where Sira = 1 order by Ad


texts: SQL Server Concurrent Inserts and Deletes

texts:
From: SQL Server 2014 Concurrent input issue

Follow up question:
How do we Delete and Reinsert rows in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc? Do we still use (UPDLOCK, SERIALIZABLE) or another lock?

We have two tables: Order and OrderLineDetail. Table Order is the parent which stores general information; and OrderLineDetail is the child table, since orders have multiple detail line items.

Orders can be updated, so we first check if OrderId is present in the table, and insert or update accordingly.

Due to customer modifications, line issue, server busy, etc, the Orders files can be sent more than once. We have a time Lastfiledatetime column. If the incoming timestamp is older, it has no effect on the table.

For the OrderLineDetail table, sometimes we don't have natural keys or surrogate keys in our files, so we delete all the child OrderLineDetail items, and repopulate (this is an xml file older legacy system).

Create Table Orders
(OrderId bigint primary key,  -- this is in xml files
 Lastfiledatetime datetime null
)

Create Table OrderLineDetail
(OrderLineDetailid bigint primary key identity(1,1),  -- this is Not in the xml files
 OrderLineDescription varchar(50),
 OrderLineQuantity int,
 OrderId bigint foreign key references Orders(OrderId),
 Lastfiledatetime datetime
)

We work in a multithreading, parallel processing environment, using SQL Server 2016 Read Committed Snapshot Isolation level.

To update the Order parent, we do this:

BEGIN TRANSACTION;

IF NOT EXISTS
(
    SELECT *
    FROM Order WITH (UPDLOCK, SERIALIZABLE)
    WHERE Order ID=@OrderID
)
    INSERT INTO Order () VALUES ()
ELSE
    UPDATE Order
    SET ...
    WHERE OrderID=@OrderID
    AND LastFileDatetime<@CreatedTime;

COMMIT TRANSACTION;

New question

How do we Delete and Reinsert the child OrderLineDetail table in a parallel, multithreading environment while avoiding racing conditions, deadlocks, etc?
Do we still use (UPDLOCK, SERIALIZABLE) or another lock?

BEGIN TRANSACTION;

IF EXISTS
(
    SELECT *
    FROM OrderLineDetail WITH (UPDLOCK, SERIALIZABLE)
    WHERE Order ID=@OrderID
)
    DELETE OrderLineDetail
    WHERE OrderId=@OrderId
    AND LastFileDatetime<@CreatedTime;

    INSERT INTO OrderLineDetail () VALUES ()

COMMIT TRANSACTION;


texts:
Column a has category ids, column b has set orders:

+-------------+-----------+
| category_id | set_order |
+-------------+-----------+
|          80 | null      |
|          81 | null      |
|          82 | null      |
|          83 | 2         |
|          84 | null      |
|          85 | 4         |
|          86 | null      |
|          87 | null      |
|          88 | null      |
|          89 | null.     |
|          90 | null      |
+-------------+-----------+

Desired output is:

+----+
| 90 |
| 83 |
| 89 |
| 85 |
| 88 |
| 87 |
| 86 |
| 84 |
| 82 |
| 81 |
| 80 |
+----+

Please help how to write the order by clause. 83 has value 2 in column b, hence is to appear 2nd in ordering. 85 has value 4 in column b, hence is to appear 4th in ordering.


texts: Oracle RAC with single node Data Guard physical standby

texts:
Best practices for Oracle 11gR2 Data Guard implies that the primary and standby databases should have the same layout, eg. RAC to RAC.

What special considerations would one need to take into account to replicate a RAC database to a single node physical standby?

Any reason why this would be a very bad idea?


texts: How to properly restore database from file groups backups?

texts:
I have very (from my point of view) strange error while trying to restore SQL server DB from a series of filegroup backups.

Here is my corrent config:

I have DB with 2 files 2 filegroups and 1 log file.
1st group (called PRIMARY is not read-only and contains my current data).
2nd is (called HISTORICAL) is read-only.

I also have partitioning.

Because of lack of space I make following:

After some  service operations (moving data from PRIMARY to HISTORICAL FG) I make FULL Database backup (each month).
After that make FULL backup of both groups and then I make log backup.
I make PRIMARY FG full backup each night.
I make PRIMARY FG differential backup each hour.
I make t-log backup every 15 minutes.

My strategy is to minimize space required for backups.

In case of faliour I wil restore FULL backup from beginning of the month. After that I will restore HISTORICAL backup (which is only one for each month). After that I will restore full PRIMARY FG backup from desired time (I am not sure if I have to restore PRIMARY FG backup after FULL DB backup). As last step I will restore t-log backup and return DB in operational state (if needed).

I do that operations in the past. Right now I receive error that the LSN that I tried to restore is too recent. Why this happends?


texts:
I have inherited this huge Excel file that has a connection to the database and it will retrieve data on opening. This file is huge and it takes a long time to update (hours) and I'd like to know where in the process I am . My questions are:

Is there a way to see the query that is running by this Excel connection?
Is there a way to find out execution status, i.e. what percentage is done?

Thank you,


texts: SQL - Add column if match else, on aggregate data


texts:
I see my mysql log file and found approx 90% of below mention entries

2018-03-06 10:42:37 14950 [ERROR] Slave I/O: error connecting to master 'ad@16.8.2.3:3306' - retry-time: 60  retries: 399,
 Error_code: 2003

Unfortunately, i dont have access on above machine and i have dropped the user ad@16.8.2.3 but still its showing log entries. I want mysqld.log file must ignore this entry. Any idea


texts: MS Access: adding column if not exist (or try/catch it)

texts:
this is an easy question, but I can't find out the solution...

I have to check if a column exists and add it if the test is negative. How can I do with only sql code?

This is the syntax for SQL Server:

IF NOT EXISTS (
  SELECT *
  FROM   sys.columns
  WHERE  object_id = OBJECT_ID(N'[dbo].[Person]')
         AND name = 'ColumnName'
)
BEGIN
    --STUFF HERE
END

and for MS Access, using only SQL code... what is the right syntax to do it?

UPDATE: also a way to do a try cath statement would be ok, I only need to not add anything if it doesn't exist... so, also a try catch is ok, I think. Also try catch is easily possible to use in sql server... and for access?

UPDATE 2: I have done this:

If Not Exists (Select Column_Name
           From INFORMATION_SCHEMA.COLUMNS
           Where Table_Name = 'TabTessereVeicoli'
           And Column_Name = 'TAGA')
begin

    ALTER TABLE TabTessereVeicoli ADD TAGA text(25) NULL;

end

but I get the error "SQL statement not valid. Expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE." Why? How can I do an alter table after an IF?


texts: SQL Server 2016 full backup and log shipping and newbie questions

texts:
As most likely in a lot of places, I find myself in the roll of "DBA" by default rather than by skill.

I currently am setting up new SQL Servers using SQL Server 2016 Standard Edition. I have been able to successfully set up the log shipping agents and schedules to where it took a full back up at start, and now does log shipping every minute, and does a restore on the secondary every 5 minutes. I have verified its working as expected by altering the primary database and, after the 5 minute boundary has passed, I can see the changes in the secondary database.

First question I have is, once the restore on the secondary has been done, can the transaction log files be deleted?

Next question is, if I wanted to combine the transaction log shipping with a full/differential backup plan, is there anything out there that explains better about how to set this up. I have read multiple articles, but each seems to be missing one component or the other and its making it difficult for me to understand how these differential backup components work together.


texts:
I've looked around for solutions to lock a whole database, and the only one I found so far is FLUSH TABLES WITH READ LOCK.

This is fine for backups, but unfortunately I can't use that to prevent other accesses to the database while I'm patching my database with schema changes. If I run an ALTER TABLE after FLUSH TABLES WITH READ LOCK, I get the following message:

  Can't execute the query because you have a conflicting read lock

Is there a way to just prevent other database connections from accessing the database temporarily while I'm patching the database?

I don't want to actively refuse the other connections, I'd just like to put them "on hold" until the patching is finished (a matter of seconds).

How can I do that?


texts: How to have a one-to-one relation to either one of two other tables

texts:
Suppose I have three tables

orders  table, fields: name, address, country, paid, shipped, amount
paypal payment table, fields: order_id
stripe payment table, fields: customer_id, cvc_valid, address_valid, zip_valid

Each order should be associated with only one payment table, paypal or stripe
Question is: How do I associate an Order with its payment when it is possible that the payment is either in the Paypal table or in the Stripe table?
I thought of adding an enum field to the Orders table to decide whether to look up the payment in the Paypal table or to look up in the Stripe table. Are there better methods than this?
I would like to use either MariaDB, MySQL, or SQLite.


texts: Why are column store indexes disabled automatically when a database is restored?

texts:
I have recently done a server migration where we backed up and restored all the databases from a SQL Server 2012 version to SQL Server 2019. However the restored databases appear to all have their column store indexes disabled by default.

Is this expected behaviour? If so, why?

These are archive databases used for selects only, rebuilding every clustered index is going to take a significant amount of time. Any other options?

(sorry, I asked this in the wrong place here. Can that question be removed?)


texts: Postgres: Arrays instead of intermediate tables for many-to-many relations

texts:
I have a PostgreSQL 9.3.7 database scheme representing a directed graph of vertices and edges with some many-to-many relations. I'm only ever interested in getting all vertices that a given vertex points to or getting all vertices that point to a given vertex.

For each many-to-many relationship, I could simply give each of the two tables a bigint[] (array) column to represent what the object points to or is pointed from. When adding an object, I would go through all its bigint[] columns I'm inserting and append itself to the corresponding bigint[] columns (inverse relations) in other tables. Edit: Actually, I have some other many-to-many relations that need to be flexible, so I use separate tables for those. But I am guarenteed that for the ones in question, I only need the queries described above.

This seems more efficient than the conventional approach of making a separate table for the relationship, plus it's less complicated. But I've never heard of anyone doing this, so I'm worried. Would such an approach have any flaws that I'm not considering?


texts: What's better here - a single column or multicolumn primary key?

texts:
Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to another group.

When trying to decide on a primary key for the item table, what should I use?

Should I make up an artificial global serial key like this:

CREATE TABLE items
(
    item serial PRIMARY KEY,
    group integer NOT NULL REFERENCES groups(group),
);

... or should I rather use a composite key and per-group item serial like this:

CREATE TABLE items
(
    group integer NOT NULL REFERENCES groups(group),
    item integer NOT NULL,

    PRIMARY KEY(group, item)
);

The reason why I'm leaning more towards the second solution is that the post URL will always show the group and item, so it makes sense to have both of them as the composite primary key. In case of the first solution, the URL contains superfluous information because the group ID can already be deduced from the item ID alone. The URL structure is given, however, and cannot be changed.

The disadvantage of the second solution is that you have to manage a per-group serial (i.e. each item integer should start from 0 for each group).

What's better in terms of best practices, normalization and performance? Or is it simply a matter of taste?


texts: Performance of an index on a column which is always null

texts:
I'm using a custom ORM framework which creates a column for all tables in the database called 'owningObject'. The column is a varchar(255) and the value is always NULL. The ORM is also creating an index on this column for all my tables.

Is there any performance risk to having an index on a column when the value is always NULL? I'm considering removing the index from these tables (I can't remove the column itself because the ORM requires it).


texts: Why MySQL doesn't use indexes in view with union select?


texts: Does SQL Server 2008 have an equivalent to Oracle's “table%rowtype” syntax or (Data Type)?

texts:
In Oracle PL/SQL scripts, you can declare a variables using the syntax tablename%rowtype for its type. This will make that variable have the same "type" as a row of that table

(i.e. a it will be a structure with the same field names and types).

This is a convenient way to transfer data between database tables and PL/SQL.

I am looking at doing the same thing (Data Type) in Microsoft SQL Server 2008. Is there an equivalent feature?


texts: DNA sequence data - requirement for a long and wide table of experimental data

texts:
I need to build a database to hold data obtained from DNA sequencing experiments.

1) 5 million columns of data in one sample, and hundreds of samples in total

2) A few data points are null

3) Calculate the median, standard deviation etc. of each column every week

What would be an optimal design for this data and help to shorten the time consumed in analysis?

Any suggestion(s) would be appreciated.

Data type: DNA sequencing data. Break the whole chromosome into 1kb windows and calculate the mean depths of sequencing etc.

Columns: bed1, bed2...bed10000...

Rows: sample1, sample2...

Even if the focus is only on genes, there would be several thousand columns.

I wonder if there is any tool or solution to hand.


texts: Queries running very slowly in Amazon RDS PostgreSQL read-replica

texts:
We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS.

At the beginning, when there were few users, we deployed using one EC2 instance and an RDS instance with a PostgreSQL database. After some time the number of users increased and we were facing problems like very slow response, time-out in different pages etc. Due to performance issue we took the following measures:

We started using Redis cache for frequently accessed data (ElasticCache in AWS)
We deployed in two different EC2 instances (one for web platform and another one for API for mobile application).
We created a read-replica in RDS and added a Database router which chooses to use master database only for write operations and all read operations are performed in read-replica database.

This solution was working good for fast few weeks. After some time, all read operations became too slow. At this time, number of database connections to master database was on average 2-3 and spiked to 5-7 at times. But due to slow query execution in read replica database, 30-50 connections were common in read-replica database.

Queries using JOIN and Aggregates often failed with the following error in read-replica:

canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed.

But generally all queries were very slow in read-replica compared to master database, even the simplest SELECT queries.

To be sure that the problem is not with a specific read-replica instance, we created another read-replica RDS instance (say read-replica-2) and pointed all read operations to read-replica-2 DB. This configuration began to perform well at the beginning but performance declined significantly within one day (for the first read-replica, it took 3-4 weeks).

After that, we modified our database router to randomly peak either one of the read-replica and read-replica-2 for any read operations, but still all queries to both of the read-replica databases are being executed very slowly. We checked by switching read operations to master database, and same read operations are being executed smoothly in master database without any problem.

Some server load related information:

At peak hours, 500-1000 users use the system, most of them are from mobile application (through API EC2 instance).
At peak hours, few users access web platform. But they often perform heavy DB intensive tasks (like bulk import and export of data).
At off-peak hours (in a 6 hours window at local night time), some heavy DB intensive cron jobs are performed in the system for report generation and maintenance.

What should be a suitable architecture for us given the scenario? Are we missing something obvious? What can cause same queries run fast in master database and very slowly in read-replica database in RDS?


texts: Ignore a database in an Availability group when using @AvailabilityGroups = instead of @Databases with ola-hallengren backup solution

texts:
I’m using the @AvailabilityGroups= parameter to backup availability groups, is it possible to NOT backup (exclude) a database in the Availability group?

If using the @Databases parameter I could use - but I am using the @AvailabilityGroups parameter instead.

Thanks


texts: Implementing User Privacy Settings with MySQL Database

texts:
I am actually working on a social website. Not big, aimed at a very small group of folks. And I am curious, to how I can best implement Privacy settings for my users in a MySQL driven database.
For example, on websites, like, Facebook, Google+, and probably other social websites, you find privacy settings and users can set these for their objects (e.g. content they share).

My issue isn’t writing the code in the language I am doing it in, but my issues are actually structuring up a DB schema that allows me to manage users’ settings. I have never done something like this, and would appreciate any help; also it must be scalable to some extent. Thanks a lot.

This is a JSON output I found from some website, and it looks like each part: allow, deny, description, friends, and value are all columns. However, in MySQL, you cannot really store large amount of comma-separated values, there's simply no space in a single column to do so.

{
  "privacy": {
    "allow": "",
    "deny": "299716926803603",
    "description": "Contacts; Except: Restricted",
    "friends": "ALL_Contacts",
    "value": "CUSTOM"
  },
  "created_time": "2015-09-04T13:01:40+0000",
  "id": "100002958951118_777860722322552"
}

I did something like this, but it simply doesn't work.

Privacy table:

allow       string
deny        string
description string
friends     string
id          int
object_id   int
owner_id    int
value       string

Not sure about this Privacy Table sample.

I'd appreciate a good solution, that is applicable, and most of the users in this community will benefit from this post.


texts: Adding a new database to an existing sql cluster

texts:
I got a requirement in my company, where a third party application requires just one database. The Application owner told me that he is OK with the database being created in an existing SQL Server instance on a cluster.  I have two questions regarding this

1) Can I use the existing drives (Data,log) drives, create a new database and map the mdf and ldf to the existing mountpoints?

2) Do I need to create another MSDTC drive for this application?

Please let me know. Thanks in advance!


texts:
I wanted to know when you are at read committed isolation level and in one session someone started an explicit transaction which has a select statement, say it takes 20 seconds. Can someone from a different session will be able to update the records from that table while the select statement is still executing.
I was discussing this with someone and according to them it will be able to update those records because that's where update locks come into play and gives the exclusive lock to the update while the query is still executing.
I can't find any documentation on this and also don't know how to test this


texts:
Why does NOT IN performs INDEX SCAN instead of INDEX SEEK?

Here's my current index on the table,

CREATE NONCLUSTERED INDEX [idx_dmcasarms_CourseList_cDesc]
ON [dbo].[courselist]
(
    [c_desc] ASC,
    [c_code] ASC
)

SQL Statement,

SELECT  c_code CourseCode
FROM    courselist
WHERE   c_desc = 'BACHELOR OF SCIENCE IN INFORMATION TECHNOLOGY'
GO

SELECT  c_code CourseCode
FROM    courselist
WHERE   c_desc NOT IN ('PRE SCHOOL', 'BASIC EDUCATION', 'SCIENCE HIGH SCHOOL')
GO

and Execution Plan,

Is there anyway I can optimize this?


texts: what monitor before applying changes on the server? - looking for a basic answer - starting point only

texts:
these settings were decision of our development team:

there are 2 settings:

1 - max degree of parallelism

2 - optimize for ad hoc workloads

On OUR_DEV_SERVER:

1.  Run the following script:
--//activate ‘Optimize for ad hoc workloads’
sp_configure 'show advanced options',1
GO
reconfigure
GO

--// Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.
sp_configure 'optimize for ad hoc workloads',1
GO
reconfigure
go

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 6;
GO
RECONFIGURE WITH OVERRIDE;
GO

what should I monitor BEFORE and AFTER the changes in order to conclude whether these changes are worth applying in LIVE?

For how long should I monitor if the purpose of the monitoring is ONLY to prove THESE changes above?

I have put a simple question, expecting just a starting point. I have nothing in place at the moment, and have not started this task so far.

I prefer not to rely on third party tools - for the purpose of this exercise.


texts: SQL stored procedure to produce custom XML text

texts:
I have around 30 tables from which I need to generate a specific XML format, which is consistent through the tables.

With my current solution I have a stored procedure for each 30 tables where I do:

select '<TOP (id column)="' + id column + '"/>'
+ '<FIELD fieldname="(fieldname)" value="' + fieldname + '"/>'
+ '<FIELD fieldname="(fieldname1)" value="' + fieldname1 + '"/>'
+ '<FIELD fieldname="(fieldname2)" value="' + fieldname2 + '"/>'
+ '<FIELD fieldname="(fieldname3)" value="' + fieldname3 + '"/>'
+ '<FIELD fieldname="(fieldname4)" value="' + fieldname4 + '"/>'
+ '<FIELD fieldname="(fieldname5)" value="' + fieldname5 + '"/>'
+ </TOP>
from dbo.table

where the parantheses values are hardcoded for each table. So for example in one table (id column) is CUSTOMER_ID. While in a different table (id column) is STORE_ID. and (fieldname) is for example CUSTOMER_NAME. So they are all hardcoded column names in the <"FIELD"> brackets and primary key in <"TOP">.

So my stored procedure with all 30 tables is around 1000 lines long, as every column and primary key is hardcoded for each table.

I'm certain there is a dynamic way to achieve this functionality where columns/rows are somehow iterated over, but I can't figure it out.


texts: Wal segment still exists on master, but logs on slave and master say it's been removed

texts:
I'm trying to set up streaming replication on Postgresql 9.5

The master and slave are configured as below and WAL files are accumulating on the master. However, something is wrong as I get complaints that the WAL files are missing:

Slave:

  FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000200000059000000BA has already been removed

Master:

  repuser@[unknown] ERROR:  requested WAL segment 0000000200000059000000BA has already been removed

The WAL file does exist on the master, and the slave will restore happily if I ship the WAL files over and use the restore_command option in recovery.conf.

postgres$ ls -l /db/archivedir/0000000200000059000000BA
-rw------- 1 postgres postgres 16777216 Mar 31 10:18 /db/archivedir/0000000200000059000000BA

Master config - postgresql.conf:

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /db/archivedir/%f && cp %p /db/archivedir/%f'
max_wal_senders = 3

The master also has a replication slot configured:

brp=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn
----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------
brp_uk    |        | physical  |        |          | f      |            |      |              |

Slave - postgresql.conf:

hot_standby = on

Slave config - recovery.conf:

standby_mode = on
primary_conninfo = 'host=xxx port=5434 user=repuser password=xxx'
trigger_file = '/tmp/postgresql.trigger.5434'
primary_slot_name = 'brp_uk'

Then the pg_basebackup is run and the slave started.

The slave has all the data as of the time of the backup, but no new data from the WAL files, and the error above.

What have I mis-configured?


texts: How to CREATE TABLE ... LIKE without the NOT NULL Constraints?

texts:
I'm trying recreate a table's structure inside a function by using some dynamic SQL.

EXECUTE 'CREATE TABLE ' || my_table_name || '_bk' ||
    ' (like ' || _my_table_name || ')';

That will be similar to:

CREATE TABLE my_table_bk
(like my_table);

But I need to discard all constraints. Using EXCLUDING CONSTRAINTS in the Like Options, it still copy the NOT NULL Constraints (Documentation confirms this behavior):

CREATE TABLE my_table_bk
(like my_table EXCLUDING CONSTRAINTS);

The question is, how can I recreate the table structure without the NOT NULL constraints, or, in alternative, remove all NOT NULL constraints in a table?


texts: Is it possible to replicate wide table in MS SQL?

texts:
The reason of me using sparse columns is to overcome limit of 1024 columns in a table (I know that generally it's not a good reason and there are downsides, but in my situation it's good option compared to software rewrite option and I didn't write that software, so don't tell me it's bad design, I know).

My customer uses transactional table replication for backup (which means they replicate whole table). So I wonder if replication will work after I start adding those extra sparse columns above 1024 limit.

So far I found 3 limits:

1) This says that I cannot replicate column sets.

2) This says that SQL Server snapshot or transactional publication limit is 1000 columns.

3) From my experiments I found that I cannot add more than 1024 sparse columns if I don't have column set defined.

From these 3 limits I conclude that I cannot replicate wide master table into wide slave table.
Is my conclusion correct ?

Is it possible to replicate non-wide parts of master row to combine into one wide row in slave table ? Non-wide I mean < 1000 columns.

If possible then how ? Are there any gotchas for replicating wide tables ?

Update:

4) It looks like custom replication SP is one possible answer. Did anybody try to replicate more than 1000 columns with custom replication SPs ?


texts: How to prevent memory optimized tables from blowing out OPT_MEMORY_OPTIMIZED\$HKv2

texts:
Microsoft SQL Server 2017 database, Developer edition. Just updated to latest version (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64).

It uses memory optimized tables. When I run a very large stored procedure which deletes many records in many tables, I find that the database creates more and more big files in the directory C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\OPT_MEMORY_OPTIMIZED\$HKv2, this until the disk is full.

I use CHECKPOINT in strategic places in the stored procedure. But this doesn't stop the growth of that directory.

I tried to solve this problem by setting a limit to size of the OPT_MEMORY_OPTIMIZED database file. However, after the limit was reached, SQL Server then started reporting

  "Cannot continue the execution because the session is in the kill state."

The only way I found to get out of this situation was to delete the database and restore a backup.

How can I stop the growth of the OPT_MEMORY_OPTIMIZED\$HKv2 directory?


texts: Better database design approach to store thousands of users and their validations (composed by many attributes) about thousands of elements

texts:
I’m looking for the best database design approach of a future web application that will store thousands of films and where (maybe) thousands of users will save their own films validations with up to thirty attributes each validation (attributes are likely to increase as the app grows). Validations are private; they’re not shared with other users.

With this scenario, when I designed the database months ago I ended up that the best approach would be the following:

One table for “users” containing UserID, nickname, password...
As many tables as necessary like “users_xxxx” to store related data of all users together.
One table to store “films” (FilmID, Name...)
UserID-numbered-tables like “film_validations_[user-ID]” with the following columns: FilmID and the thirty attributes (at least). So it could store the validation of all films of a unique user.

Sadly, the PROS I think I am getting with this approach are merely based on suppositions, not on performance tests (I am too novice to know how to do this kind of task). And the CONS about maintenance and performance are an imminent incoming headache for sure, like:

Thousands of tables to store all film validations.
Almost impossible to offer statistics based on global users film validations. Because it will be really hard to join too many tables, also having in mind the total will grow as new users sign up into the application.
Any day I want to offer more film validation attributes I will have to massively execute ALTER TABLE.
The DBMS has to operate with thousands of tables and open them before it can execute the queries.

I am still aware about these problems, but I really can’t figure out how to design it better.

Designing a single table with the UserID column, plus the thousands FilmID as columns (these would contain a Boolean value), plus the tens of film attributes looks really weird.

If we accept this approach, here is another problem: a heavy amount of columns, which in addition, will grow in the future.

Although I have read that a database has no problem managing millions of records in a single table (it would contain the thousands of film validations of [I hope] thousands of users), and the MySQL columns limit is much higher than my requirements (4.096 columns as described here), I don’t know if the performance would be poor.

Other design errors I consider about the single table approach are:

Each record would contain as many empty columns as films minus one, which is awful. I mean, each film validation would carry the rest of FilmID columns, and that has no sense because it wouldn’t be neither necessary nor optimised.
Querying for films with a validation would be a waste of memory. I guess I would have to code a query within a for loop to check which of the thousands FilmID columns contains a “true” value. In my current design, film validations are inserted into the table “film_validations_[user-ID]” as the user fill them. So with just a “SELECT * FROM film_validations_[user-ID]” is enough because I am sure it only contains rated films.

I am a recent graduate student as a web developer. Although I think I have read and learned enough about database normalization (Wikipedia, many StackOverflow Q&A before asking you, and other sources) to consider the single table approach as a bad option in this scenario, maybe in this case is the opposite having in mind all its future maintenance benefits.

Well, after some months, I am still wondering what would be the best approach designing this kind of database. Right now I’m working on moving the unfinished web application fully coded as procedural to a MVC pattern. So I think this is the right moment to correct my database design if necessary.

That’s why am asking for your suggestions based on professional expertise.

Thank you very much in advance.


texts: Create Replica Sets on an existing sharded MongoDB

texts: How do I convert an existing mongodb instance that has sharding enabled for some of its collections to a replica set? I am able to do it the other way around, but I am not able to figure out how to add replication to existing sharded mongo instances.


texts: How to resolve an SQL Server "Target principal name is incorrect" error invoked after upgrading from SQL Server 2016 to 2017?

texts:
I am experiencing a "Target principal name is incorrect" error after upgrading SQL Server 2016 to 2017. The SQL Server 2016 instance has been uninstalled. The certificate which is registered in SQL Server Configuration Manager Protocols and is used to enable wire encryption is named with the FQDN of the server. It is the exact same certificate that was working successfully with SQL Server 2016.

I have noticed since the upgrade that I cannot logon to SQL Server 2017 using the FQDN of the server. I can only logon using [HOSTNAME]\[INSTANCE_NAME]. Why has this behaviour changed and how can it be restored? I am wondering whether restoring this behaviour will simultaneously resolve this connection error.


texts:
I am kind of new to SQL Server.

I am trying to shrink my MDF files, however I am unable to do it.

There is no error message.
Any idea why this is the case ?


texts: Quickly clone a database in SQL Server Management Studio

texts:
For testing purposes, I often have to clone databases (onto the same server). Currently I do this by creating a backup and restoring it as a new database.

I would love to have such a functionality available as a simple right-click in SQL Server Management Studio (SSMS): right-click a database, choose "Clone" and be done.

Unfortunately I found no such or a similar function in SSMS.

My question:

Does such a "quick clone" function exist or could I implement one by myself (or maybe through some add-in?)

Update 1:

I've found a SO question and answer on using the Copy wizard.

This looks promising but takes a lot of wizard steps. Maybe it is possible to do this whole thing with a single click?


texts: Making sense of INNODB buffer pool stats

texts:
After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. Here's the output from show engine innodb status\G (we're running v5.5)

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 1758417
Buffer pool size   393215
Free buffers       853
Database pages     360515
Old database pages 133060
Modified db pages  300
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7365790, not young 23099457
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1094342, created 185628, written 543182148
0.00 reads/s, 0.00 creates/s, 37.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 360515, unzip_LRU len: 0
I/O sum[2571]:cur[0], unzip sum[0]:cur[0]

I wanted to know how well we're utilizing the buffer cache. After initially glancing at the output, it appeared that we are indeed using it, based off of the Pages made young and not young have numbers in them and Buffer pool hit rate is 1000 / 10000 (which I saw elsewhere on the web that this means it's being used pretty heavily. True?)

What's throwing me through a loop is why the young-making rate and not are both at 0/1000 and the young/s and non-young/s accesses are both at 0. Those would all indicate that it's not being used at all, right?

Can anyone help make sense of this?


texts: Named point in the transaction log? Does it exist?

texts:
Is it possible to create a named point in the transaction history of the database, such that a subsequent connection could retrieve data at that named point in the log?

For example, lets say there is a loader process that is writing to multiple tables fairly often. At some point, a reader process creates a named point in the log, lets call it point A. The loader does some more updates and now we are at point B. Then another reader process comes along, and can select data as point of A.

Is such a thing possible in SQL server?

Unfortunately, the tables / loader doesn't have a bi-temporal structure, so the reader can't supply an as of datetime as a predicate on the select statements.


texts: I am trying to insert large blobs into MySQL but fails because of ER_NET_PACKET_TOO_LARGE. I don't want to change the setting of the MySQL that's why I was hoping if this approach is supported here?


texts: How to know if mysql tmpdir is being used

texts:
I set my MySQL tmpdir setting to use /run/mysqld which is a tmpfs, however while running a query I know copies data to a temp table (which takes 5-6 seconds based on MySQL's INFORMATION_SCHEMA.PROFILE records) I am not seeing any temporary file(s) being written out to that directory when doing an ls -la at the command prompt.

Additionally, the query speed did not change when moving my tmpdir from an on-disk location to an in memory location (tmpfs) which it clearly should have improved.

Question: How do I know for sure that my tmpdir is being used?


texts: Attempting to Restore Database From ibd Files but Receiving Errors on Tablespace Import

texts:
Recently, a customer (who didn't take backups of their system for whatever reason) had a database disaster so their production database had all of its files removed EXCEPT its ibd files. I have recovered databases using tablespace files through discarding and importing before but I have not had any luck and only hit errors when I try.

All tables except for a small number are InnoDB and the database engine is MariaDB 10.0.13.

When I do an import for any table, I receive this in return: ERROR 1034 (HY000): Incorrect key file for table <table>; try to repair it. Being an InnoDB table, repair doesn't work.

Cursory research showed that as most likely being a disk space error. I had plenty of space but I stopped Maria, cleaned the temp directory and started it again then ran right into the same issue.

I pulled the entire data folder in-house to work with it. I replaced the missing files for the database in question and Maria started without any problems but I still cannot manipulate the data. Not even a select will run without crashing.

I tried another approach by comparing the table locations of the same table on a fresh database in a hex editor then updating the old ibd file to use the new tablespace ID. Maria was able to start but I get InnoDB: No B+Tree found in tablespace in the logs when the service starts.

I checked in innodb_sys_tables, innodb_sys_tablespace and innodb_sys_indexes in information_schema and nothing for that database is referenced. I'm reasonably certain that is a bad thing.

Are there any options left to attempt data recovery or has the production data been lost?


texts: Using PGPool with RDS Slave replicas, any advice?

texts:
I have a Postgres 9.4 RDS instance with Multi AZ, and there's a slave, read-only replica.

Up to this point the load balancing was made in the business layer of my app, but it's inneficient and I was hoping to use PGPool so the app interacts with a single postgres connection.

It turns out that using PGPool has been a pain in the ass. If I set it to act as a load balancer, simple SELECT queries throw errors like:

 SQLSTATE[HY000]: General error: 7
 message contents do not agree with length in message type "N"
 server sent data ("D" message)
 without prior row description ("T" message)

If I set it to act in a master / slave mode with stream replication (as suggested in Postgres mail list) I get:

psql: ERROR:  MD5 authentication is unsupported
in replication and master-slave modes.
HINT:  check pg_hba.conf

Yeah, well, pg_hba.conf if off hands in RDS so I can't alter it.

If I disable master-slave behavior, I can connect using psql, but the performance is slow and the app outputs messages like

DEBUG:  SimpleForwardToFrontend: packet:D length:28
DEBUG:  pool_use_sync_map: we cannot use sync map because STREAM: 0 query in progress: 1 doing extended query: 0
DEBUG:  reading backend data packet kind
DETAIL:  backend:0 of 2 kind = 'D'
DEBUG:  read_kind_from_backend max_count:1.000000 num_executed_nodes:1
DEBUG:  processing backend response
DETAIL:  received kind 'D'(44) from backend

Is the performance slow because of the verbosity, or are those messages realing some king of configuration problem?

Has anyone got PGPool to work in RDS? Are there other tools that can act as a middleware to take advantage of read replicas in RDS?


texts: Function Variable Number of Arguments plsql

texts:
I am looking to do a function with non fixed number of arguments like decode function.
The syntax for the DECODE function in Oracle/PLSQL is:

DECODE( expression , search , result [, search , result]... [, default] )

I want to do the same [] for example:

SUM(expresion1, expresion2, [expresion]...)

A practical result could be:

SELECT SUM(1,1) FROM dual;
SELECT SUM(1,1,1,1,1,1,1,1,1) FROM dual;
etc

Is that posible in PL/SQL or is this limited to Oracle built-in functions?


texts: How to get the type of geometry column in postgres?

texts:
I need to determine the type of geometry column and doing it with the following query:

SELECT type
FROM   geometry_columns
WHERE  f_table_schema = 'public'
       AND f_table_name = 'table_name'
       AND f_geometry_column = 'col_name'

It works fine for regular geometries, like Point or Polygon. But then there are also some fancy geometries like PointZ. How do I determine if column has it? Above mentioned query returns just POINT for it.


texts: How do I get a list of values for column x GROUPed BY column y?

texts:
I have the following table structure and data (using Oracle DB 12c):

CREATE TABLE authors (
    aid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT authors_pk PRIMARY KEY NOT NULL,
    fname VARCHAR2(100 CHAR) NOT NULL,
    sname VARCHAR2(150 CHAR) NOT NULL,
    dob NUMBER(4),
    gender CHAR(1 CHAR)
);

CREATE UNIQUE INDEX authors_uind ON authors (fname, sname, dob, gender); --- Combination of columns must be unique

INSERT INTO authors(sname, fname, dob, gender) VALUES('Codd', 'Edgar F', 1923, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Date', 'Chris J', 1941, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Darwin', 'Hugh', 1943, 'M');
INSERT INTO authors(sname, fname, dob, gender) VALUES('Lions', 'John', 1937, 'M');

CREATE TABLE publications (
    pid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT publications_pk PRIMARY KEY NOT NULL,
    title VARCHAR2(150 CHAR) NOT NULL,
    written NUMBER(4)
);
CREATE INDEX publications_ind ON publications (title, written);

INSERT INTO publications(title, written) VALUES('A Relational Model of Data for Large Shared Data Banks', 1970);
INSERT INTO publications(title, written) VALUES('The Relational Model for Database Management', 1990);
INSERT INTO publications(title, written) VALUES('An Introduction to Database Systems', 2003);
INSERT INTO publications(title, written) VALUES('The Third Manifesto', 2000);
INSERT INTO publications(title, written) VALUES('Temporal Data and the Relational Model', 2002);
INSERT INTO publications(title, written) VALUES('Database in Depth: Relational Theory for Practitioners', 2005);
INSERT INTO publications(title, written) VALUES('Commentary on UNIX', 1976);

CREATE TABLE author_publications (
    aid NUMBER(38) REFERENCES authors (aid),
    pid NUMBER(38) REFERENCES publications (pid),
    CONSTRAINT author_publications_pk PRIMARY KEY (aid, pid)
);

INSERT INTO author_publications(aid, pid) VALUES(1, 1);
INSERT INTO author_publications(aid, pid) VALUES(1, 2);
INSERT INTO author_publications(aid, pid) VALUES(2, 3);
INSERT INTO author_publications(aid, pid) VALUES(2, 4);
INSERT INTO author_publications(aid, pid) VALUES(2, 5);
INSERT INTO author_publications(aid, pid) VALUES(2, 6);
INSERT INTO author_publications(aid, pid) VALUES(3, 4);
INSERT INTO author_publications(aid, pid) VALUES(3, 5);
INSERT INTO author_publications(aid, pid) VALUES(4, 7);

It's possible for one author to have written multiple books and one book to have been written by multiple authors.

What should the query look like so that I get a single row per book (for all books), with the names of the authors concatenated together. EG:

| Written | Title | PID | Authors |
|---------|-------|-----|---------|
| 1990 | THE THIRD MANIFESTO | 4 | DATE, DARWIN |
| 2002 | TEMPORAL DATA AND THE RELATIONAL MODEL | 8 | DARWIN, DATE |

Note 1: The order of the authors' names in the fourth column is not important, nor is a comma after the name of a single author in a row.

Note 2: The use of a GROUP BY clause is not required if the desired result can be achieved by a different means.

The query I have is as follows:

--- Get a list of author names per title
SELECT p.written, upper(p.title), p.pid, concat(upper(a.sname), ', ')
FROM publications p INNER JOIN author_publications apub on p.pid = apub.pid
INNER JOIN authors a on apub.aid = a.aid
GROUP BY p.pid
order by p.written, upper(p.title)
;

The error message I get is  "not a GROUP BY expression". (I don't understand what particular part of that statement fails or why.) The line in question is the start of the SELECT statement.


texts:
We have a application that creates SSRS Schedules 2 times a year and it does create one for each of our customers.

Each schedulation results in a SQL Agent job with a GUID as name and the description This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job..

The schedulations just run one time and then just collects dust.

Is there any way to remove all old jobs and information about the schedulations in the SSRS database?


texts: A Huge Database: Picking the right schema and data model

texts:
We want to store some genomic variant data but there are some problems, more important ones like problem of the data's immense size and variability.

1) Variant data can be huge. For example, a single individuals variant data could feasibly some day require a million rows of data in a table, or require of a gigabyte of raw storage on disk. Multiply this over several thousand individuals, and you could potentially end up with terabytes worth of information that you need to make sense of.

2) Each client and/or system we integrate with, will expose or want to see data slightly differently depending on their needs and use cases. This can potentially lead to hundreds of fields that we might need to store, all of which might need to be in different configurations based on the clients needs. So this variant data model will need to keep this in mind in order to remain easy to use, expandable and most importantly, scalable in the long term.

What do you think is better for such a problem? We were thining of having some coulmns in each table that point to an external database or even a file, where we save the huge BLOB data?


texts: Install standalone instance on cluster server with SQL Server

texts:
I've a cluster instance in 2016 SP1 version on Windows 2016 server. All work fine.
I want to install a new instance in standalone mode (version 2008) on one server of this cluster. The data disk of this new instance will be localdisk.

I want to know if the standalone installation will work without broke or impact cluster instance.


texts: MySQL syntax error 1064 near IN stored procedure

texts:
an aspiring DBA Here.

I'm trying to create a stored procedure for a query I'm running. It is a memsql server which is MySQL based. The procedure is as such:

delimiter //
CREATE PROCEDURE calc_campaign (IN usr VARCHAR(38))
BEGIN

Awesome SELECT statement that uses a variable

END //

The error description is:

ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'IN usr VARCHAR(38)) BEGIN my_statement; END'
at line 1

The syntax I used seems to correspond with this:
 https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html
 And with pretty much every other procedure I saw on Stack forums.

What is it I'm missing here? Thanks for any help!


texts: What is difference between Oracle Apps and Oracle Application Server?

texts: I am very new to oracle, could anybody tell me what Oracle Apps and Oracle Application Server are.


texts: Error when executing "create pfile from spfile"

texts:
I just installed Oracle database 12cR2 and am trying to startup a database.

I am trying to create a pfile from spfile using following query.

create pfile from spfile;

Then I get the following errors.

create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?=/dbs/spfile@.ora'
ORA-27077: too many files open

Can anybody tell me what's going on here or what I am doing wrong?


texts:
We have configured Always on Group Database with Full Recovery mode and secondary replica is in sync mode with primary server.Now we have configured logshipping in primary server to supply t-log backups to DR site.Always on group backup preference is set to primary.While executing job its showing success but no t-log backup file is being generated in share folder.In error logs there is a message showing as:

  A transactional log backup could not be generated because database is in either NORECOVERY mode or STANDBY mode

Can anyone help me out with this?


texts: Limiting user access to tables based on a ROLE

texts:
I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:

Created a database role called NO_HR
Added the tables to this role under Securables that should be blocked
Set all permissions to DENY for these tables
Assigned my users this role under Database Users -> Role Members

I would expect the user to have whatever public access is allowed to the database but be denied access to the tables as defined in the role.

Problem is, users can access all tables as if the rules in the role are not taken into account. My question: How do I block access to tables based on role membership?

A few facts:

SQL Server 2005 Standard
Windows 2003 R2 Enterprise
Users are DOMAIN logins
Any help would be appreciated.

SG


texts: How is the "Adjust memory quotas for a process" user right assignment used in SQL Server 2019?

texts:
I am looking at the Configure Windows Service Accounts and Permissions doc (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15) for SQL Server 2019 and it states that the SQL Service Account needs to have the Adjust memory quotas for a process permission.
Can anyone confirm why this is required and what it's function is within the SQL Engine. In my Dev environment, I do not have it enabled and all seems to be functioning correctly.


texts: Keeping "SQL Trace was started" and "SQL Trace stopped" from filling up my SQL logs

texts:
I have a SQL2008 server that runs a custom application that starts and then stops a SQL trace every 60 seconds (it does some reporting and summarization on the trace data).

The problem is that my SQL logs are filling up with the "SQL Trace ID 1 was started" and "SQL Trace stopped" entries. This makes it hard to browse through and identify other unusual events.

I can't find any way to turn logging of this event off. Any ideas?

For your reference, I did find a couple of partially-related articles:

Microsoft KB 307538 mentions that this was "fixed" (to add logging) in SQL2000 SP2
This newsgroup question asks the same question I had, the only answer given is using the "-n" startup parameter to keep all SQL events from showing up in the Application Event log. That's not what I want, though. I want to keep these from being logged in the SQL log at all.

EDIT with some additional curious findings:

This same application is also running on a SQL 2000 server (with SP4). Strangely, the trace start/stop events do not show up in the SQL logs on that server, although they do show up in the Application event log as "19030: SQL Trace started" and "19031: SQL Trace stopped".

If there were a way to revert SQL2008 to this behavior (write to app log, but not SQL log), then that would be nice :)


texts: how to add trigger in mysql workbench, without UDF, using EER design

texts:
I have designed a simple database using EER in workbench. However, i want to have a minimum value for the attribute salary (compared with min_value attribute from artist table). Here is the schema:

and here is the equivalent in the EER mysql workbench:

I need to check that the salary of each artist that acts on a movie, has a minimum value (lets say 1000), but how am i suppose to do that? I have checked in the options and all i see is:

-- Trigger DDL Statements
DELIMITER $$

USE `Askisi_3db`$$

Should i use something like:

CREATE TRIGGER min_salary
BEFORE INSERT OR UPDATE OF
salary, min_salary ON acts, artist
FOR EACH ROW
WHEN (new.salary < min_salary)
BEGIN
RAISE_APPLICATION_ERROR (-20004, ‘Violation of Minimum Actor Salary’);
END;
.
run;

The problem is that the attribute is in a N-M relation and has to check keys in other tables. Salary matters only for artists that participate as actors in a movie and not for directors or writers. However not every artist must have a min_salary (may be null for some who participate as directors or writers). Is this feasible only via UDF fucntions or front end design? I would really like a simple and tidy solution :)
Please don't give suggestions about how the schema could be better, all i ask is on this particular design how to implement the check between salary and min_salary


texts:
Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints:

ERROR:  insert or update on table "documents" violates foreign key constraint "fk_rails_d4abdc7f58"
Key (typ_document_id)=(7) is not present in table "typ_documents".

Yet, querying afterwards

SELECT * from typ_documents;

getting a result

  7 | Test Internal | 2013-07-04 08:36:16.026295 | 2013-07-04 08:36:16.026295

The only assumption that I can follow is that, when the table documents is being loaded, the order of loading is such that typ_documents is happening after and the error is arising.  Yet, this error is not occurring consistently and the data dump is in alphabetical order of table names.  Thus, this is a weak assumption.

Removing the foreign key constraint from rails would overcome the problem, but that is a weak reaction.

Update
Data from the pg_dump file  (redacted to omit the long full text)

COPY documents (id, titolo, abstract, full_text, typ_document_id, idioma_id, competitor_id, created_at, updated_at) FROM stdin;
[...]
5   Conservazione Finocchio con lavaggio    La prova è stata [...redacted...] 3 ppm\r\n15\t15\r\n0,75\r\n30 sec\t60 sec\t90 sec 7   1   \N  2013-07-08 10:49:53.393598  2013-07-11 16:07:31.540986

COPY typ_documents (id, nome, created_at, updated_at) FROM stdin;
[...]
7   Test Internal   2013-07-04 08:36:16.026295  2013-07-04 08:36:16.026295

How can this issue be debugged/overcome?


texts: High CPU usage by GHOST CLEANUP after DROP FK and DELETE

texts:
I am trying to replace data in table referenced by FKs in other tables on SQL Server. There are more than 1M rows in both referenced and referencing table and I want to make the replacement atomic. Obvious solution would be:

BEGIN TRAN
ALTER TABLE TabReferencing NOCHECK CONSTRAINT MyForeignKey
DELETE FROM TabReferenced
INSERT INTO TabReferenced (...)
ALTER TABLE TabReferencing CHECK CONSTRAINT MyForeignKey
COMMIT

If those statements (executed one-by-one or in batch, over network on on local SQL Server) take longer than a few seconds to complete, things go nasty. GHOST CLEANUP background thread on server starts up few moments after DELETE and starts using 100% CPU and quite a lot of IO, degrading overall performance to unacceptable level (500 inserts/min compared to 50000 inserts/min without cleanup).

So far I have tried following workarounds:

Disabling FK before beginning transaction and re-enabling it after COMMIT works fine (quick enough), but if process (or network) fails during inserts, I end up with table with no FKs.
Deleting data from both referencing and referenced table and NOT touching FK works fine, but I don't want to repopulate referencing table due to it's size.
Dropping and re-creating FK instead of dis/enabling in transaction makes no difference.
Dropping and re-creating FK and using TRUNCATE instead of DELETE works fine - at least until I need DELETE with a WHERE clause.
Disabling GHOST CLEANUP thread (DBCC TRACEOFF(661, -1)) makes original batch complete at full speed, but it's not the real solution for production environment.

Why does disabling FK and deleting table's content trigger GHOST CLEANUP and just deleting table or truncating table does not? Why does cleanup with disabled/dropped FK takes way longer than same cleanup with FK enabled?

There are no triggers in DB. All FKs have empty ON UPDATE/DELETE action.


texts: SQL Server : ALTER TABLE tablename SWITCH PARTITION 2 to tempempty

texts:
Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file group that I am going to be removing. I also create all the indexes on the empty table so it matches exactly the data table on that partition.

Once the empty table and indexes are created, I issue the command

alter table mydatatable switch partition 2 to emptytable

The command takes forever to run and after reading everything I could about the switching, I thought it was only moving meta data over and a very quick step.

My plan is to swap the data to the empty one and then truncate the empty table and remove the file group.

To add a little more details. The data table started off as a non-partitioned table. Due to the number of r5ecords in it, we had to partition it. So I created the partition schema, function and all of the file groups. I dropped the clustered index and all other indexes and recreated them all. The partition process looks to be successful.

When I run the alter table command, it feels like there is data being transferred and not just meta data.


texts: how to select records from two columns where one column consist word from second column?

texts:
In postgres I want to select rows where column1 matches column2 for corresponding row and returns result having same values as well as records consisting word from column1.
select * from table_name where "column1" ILIKE "column2";

i am using above query but it returns exact matches, it should also return rows consisting words from column1 in column2.
column1 | column2
hello   | hello
please  | please guys
help    | help me
query   | website

my above query returns
column1
hello

i want it to return
column1
hello
please
help


texts: Heroku PostgresApp - Fails to start with edited postgresql.conf

texts:
I am trying to tune my local pg running from the Heroku Postgress App.

I am seeing strange errors in the OSX console whenever I change some of the internal settings:

 PM Postgres[78709]: Could not connect the action selectPostgresStatus: to target of class AppDelegate

In the above case, setting wal_buffers to any value other than the default in the conf results in PG being inaccessible.

Other config settings have a similar effect.

Is there a trick to configuring Postgres.app?


texts: Can you create a new filegroup for a table in a database that is live?

texts:
I have a SQL Server 12.0.5223.6 instance with a database on it.

I'm wondering if I can create a filegroup for one (or more) tables while it's running? Or does it have to have no data in the tables?

I could take the DB offline, but I can't take it offline for long. Right now all the data is inside Data.mdf. I'm hoping to split out some data from maybe three or four tables into separate filegroups.

Could this be done?


texts:
This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive.

Is it possible to recover data from the original data files to transfer to another instance of Postgres?


texts: Stop PostgreSQL from logging statements on error

texts:
My postgresql 9.3 logs are sprinkled with entries like these (redacted to protect the guilty):

STATEMENT:  INSERT INTO table (key, value) VALUES ($1, $2)
ERROR:  duplicate key value violates unique constraint "table_pkey"
DETAIL:  Key (key)=(xyz) already exists.
STATEMENT:  INSERT INTO table (key, value) VALUES ('xyz', '{...json...}')

I know the source of the problem (a race condition where two app servers do the same work) and that code gracefully handles the insertion failure, but for the life of me I can't seem to get PostgreSQL to stop logging the statement which generated the error to begin with.

I've set log_statements for this db to 'none' (which persists across connections):

show log_statement;
 log_statement
---------------
none
(1 row)

But to no avail. These entries still keep hitting the logs. I wouldn't mind except the "values" from the logged statement are  JSON blobs which aren't tiny. How can I keep PostgreSQL from logging whenever an insert violates the unique constraint?


texts: Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine

texts:
I have an extremely large dataset (250M Rows in one table, 170G on disk) on a very powerful desktop computer that I'm utilizing to clean-up the data and do analysis on it.
As part of this analysis:

I need to run updates on the entire database to find things like phone numbers in a non-standard format, and convert them to a standardized format.
I need to be able to run an analysis on things like, give me all the records which share the same phone number with another record and similar queries.

All fields I will primarily querying have been setup as indexes, but the only unique index is the primary key.
The powerful desktop machine I have is a Ryzen processor with 4 CPUS with 4 cores per CPU, and it has 64GB of RAM. There is also a dedicated 2TB SSD drive for the database (OS is running on a different drive).
I currently am operating off of a default Xampp Configuration on Windows 10 Pro running MariaDB 10.4.17.
What changes do you recommend to the MariaDB or computer setup in order to get the best performance out of this scenario and hopefully make things go faster. I've had some update queries take over 16 hrs. I know I could likely optimize those queries more; but I also imagine there are a lot of settings in the default Xampp config that could be significantly tweaked.

Update
I'm kind of surprised by the responses. I know I can make queries even better, etc; but there isn't one database setting I can tweak to make things run a little bit smoother or make it more geared towards the size and type of data I have?


texts: SOA and Oracle cascade data between two pdb

texts:
I am Java developer and need help for design and manage database .
Please view this picture :

I have two service named :
1) Authentication Service (Register new user and login)
2) Customer service (Record User Information like address,phone,....)

my question is :
what happened in customer database if authentication service remove a user from database ?
How can manage dirty data from customer pdb ?

Note 1 : two services have not any access to other Oracle pdbs .
Note 2 : customer is child of authentication (Father) so authentication service can not connect to child web services (Only customer can connect to authentication service)

Update : This is only a simple example for describe what i want to do .


texts: Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)?

texts:
So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins?

Is the only solution to add all potential Windows logins and set them to restricted privileges as a preventative (or reactive) procedure?


texts: What is a Compressed Prefix B+-Tree (CPBTree) in SAP HANA?

texts:
I'm studying the SAP HANA main memory database.
There is an index called CPBTree in it. In its documentation, it is described as follows:

CPB+-tree stands for Compressed Prefix B+-Tree; this index tree type
is based on pkB-tree. CPB+-tree is a very small index because it uses
'partial key' that is only part of full key in index nodes.

This is a bit vague. There are no other explanations about the CPBTree's structure on the Internet.
Is there anyone who can provide an explanation more or refer me to good documentation/URLs/&c.?


texts: Mysql 1 way replication master lost data replication still has

texts: Had a master mysql on (windows) improper shutdown. Many records could not be recovered and investigation found slave had records master did not have. Did not restore over master - left as was. As if by magic, about 6 weeks later the missing data from master re-appeared on the master, and now master>slave agree, with some lingering discrepancies. We're talking thousands of rows across many schemas and many tables within each schema. Looking for theories and data mining is not revealing much of anything. 1st question is knowing MySQL tries to automatically recover data, could something have happened where mysql cached it, then released it 6 weeks later. Anybody encountered this or any known issues like this?


texts: MySQL backing up MyISAM tables by copying the physical files - and automating the process

texts:
I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly basis. Currently I do some updates once a week, and then I go to the data folder and copy the physical folders representing the databases to another drive on the server, and then rar everything up.

This process takes about 45 minutes and during that time certain functionality of my website cannot be used as during the copying, the tables get blocked. I have seen that you can LOCK and FLUSH tables so that they can still be used while they are being copied. So does LOCKing the tables allow concurrent SELECTs?

I do not know exactly how to go about this and I would greatly appreciate if anyone could help me with how I could synchronize the lock/flush statements with the copying of the physical data and then the subsequent unlocking, and how I could possibly automate (possibly script to a dos batch file) this process?

Thanks in advance, Tim


texts: why is io_stall_writes_ms so much higher for tempdb?

texts:
We have the user and system data files on the same disk drive.  The ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) is below 2 for the user files but the tempdb files are typically over 400.  I see that on a few servers and I'm curious if there is a reason it takes longer to write to tempdb than a regular database data file.

SELECT DISTINCT UPPER(LEFT(mf.physical_name, 1)) AS Directory,
( io_stall_write_ms / ( 1.0 + num_of_writes ) ) as result,
io_stall_write_ms, num_of_writes,
fs.database_id,
fs.[file_id]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]

Thank you,


texts: How can I use a default value in a Select query in PostgreSQL?

texts:
I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this?

E.g. something like this:

SELECT MAX(post_id) AS max_id DEFAULT 0 FROM my_table WHERE org_id = 3

And if there is no rows with org_id = 3 in the table I want to return 0.


texts: ERROR InnoDB: Trying to access page number .. in space .., space name..., which is outside the tablespace bounds

texts:
I am running a mysql database server (5.7) on a virtual ubuntu 16.04 host.

This host collects syslog messages from my other network hosts, adds the values to a pipe and creates mysql records for each forwarded message.

In general this works as expected, but since starting this project it happened twice that my mysql instance dies with the following error:

[ERROR] InnoDB: Trying to access page number 208260 in space 70, space name DBNAME/TABLENAME, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
[ERROR] InnoDB: Server exits.

I am somehow used to mysql but never realized this particular error on aby of my servers.

At that point i am not able to start the mysql server again without altering the config.

After re-starting the server (using innodb_force_recovery = 2) i can connect to the mysql server and switch to the related database.

If i then try to show all tables of this particular db i get:

mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: syslog-ng

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

Questions:

what might cause such outside bounds issues?
How can i prevent it from happening again?


texts: Why would recompile query hint result in different plan for same adhoc statement after freeproccache?

texts:
This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general.

I've got an adhoc sql statement that gets a different query plan solely because of  OPTION(recompile). The batch statement is never (in practice) being reused as it is using non-parameterized dynamic sql generation where the statement changes with dates / other parameters. I've confirmed this is the case by checking the cached plan_handle, always a different ID as values in batch change.

When I do dbcc freeproccache I expect that the query plan I get for the statement would be exactly the same as one with "option (recompile)" query hint added. But its not, its quite different actually (much much faster).

Does anyone know why addition of a query hint would cause the engine to pick a different query plan?

The statement is something like this

dbcc freeproccache
go
sp_executesql '
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate
and exists(
select A
union all select B
union all select C
)'

all I have to do is change the statement to use recompile query hint and I get different (much better) query plan.

dbcc freeproccache
go
sp_executesql '
declare @begindate datetime
declare @enddate datetime
select @begindate='1/1/2011'
select @enddate='2/1/2011'
select count(*) from tableA where tableA.datecolumn between @begindate and @enddate
and exists( select A
union all select B
union all select C
) OPTION (RECOMPILE)'

As code is passing in the date values I can't use a plan guide to override the default behavior, looks like code change will be required. But I wish I knew where to look on why recompile would force a different plan to be used.