texts: Adding a 'last time occurred' flag column

texts:
I need to add a column into a table that contains 3 columns:

GroupKey - Shows the number of an item.
Status - the number of the status that the group was according the insert date. The status number can be changed back and forward several times.
Insert date - the time & date that the status changed.

This 3 columns never change. its like a "log" table , rows can be only added and not deleted or changend

The fourth column (calculated Column) that I need is a flag column that will show if the status was the last time the status has been changed based on the insert_date.

If it is the last status then show 1, else show 0. I hope I explained this well enough. Here is an example:


texts: Allow a SQL Server job to run for specific time before killing it

texts:
SQL Server 2016 Standard Ed
We have a clean up job that runs some pretty simple sql:
use productionDB
go

delete from transactionaltable where
createdat < DATEADD(day, -21, GETDATE())
go

This runs every weeknight, and has been great.... until last night, when an (apparent) lock escalation or conflicting jobs caused it to hang, locking the table and causing all kinds of mess.
I thought to myself: "Hey, there must be some some magic I can put on the job or on the step, a property like 'kill if running for more than ten minutes'"  But I can find no such.
Is the only technique to:

Fire the real job at 0100
Fire a kill job at 0110  // a safety that rarely gets a hit

The kill job to be built with this technique:
USE msdb ;
GO
EXEC dbo.sp_stop_job N'Your Job Name' ;
GO

Or is there a better/cleaner way to do this?
SOLUTION
Stop using that crude WHERE statement and start deleting rows in small batches, using a method like that described here.


texts: Using CREATE TYPE within Stored Procedure Call

texts:
I have a SQL Server 2012 database, and I generally use stored procedures that belong to a role with grant execute permission. I just created a stored procedure (sp1) that calls another stored procedure (sp2).

In the called stored procedure (sp2) I have a CREATE TYPE statement. When I attempt to run it from a C# console job, I get:

  "CREATE TYPE permission denied in database  error"

This is using a SQL Server account. The job runs when I use sa. What permissions are required to perform this type of operation? I prefer to use the minimum permissions possible.


texts: Check the data latency between two Always On Availability Group servers in ASYNC mode

texts:
I have implemented Always on Availability Group in asynchronous mode between two servers primary and secondary.

When i tried to check the data latency between the servers in performance monitor using performance counters like MS SQL :availability replica,MS SQL:database replica and MS SQL:databases ,I'm not able to see any results in the graph.

But the graph is showing results for other performance counters.I've tried to get the data latency using DMV sys.dm_hadr_database_replica_states.

Can any one tell me what is the issue ?

How can I check the data latency between the two servers in performance monitor using graphs
as shown in the  msdn article ?


texts: Recursion on deletion trigger that calls a function

texts:
I have the following schema:

CREATE FUNCTION myfunc() Returns trigger as $myfunc$
    DECLARE
        someVal integer;
    BEGIN
        SELECT Count(*) into someVal
        FROM MyTable
        WHERE someAttribute = OLD.someAttribute;
        IF someVal = 100 then

            -- Recursion, don't do this
            -- DELETE FROM MyTable
            -- WHERE someAttribute = OLD.someAttribute;

            -- delete from MyTable2
            DELETE FROM MyTable2
            WHERE someAttribute = OLD.someAttribute;
        END IF;
        RETURN OLD;
    END;
$myfunc$ LANGUAGE plpgsql;

I have oversimplified what I did in my code, so let me clarify it.

I am registering this function with the following code:

CREATE TRIGGER myfunc BEFORE DELETE ON MyTable
FOR EACH ROW EXECUTE PROCEDURE myfunc();

So, when someone deletes a value in my relationship and if some condition occurs, I want to delete other values as well. Think about this: I have a hundred 10's in my database. If the user deletes one of them, it will be 99 and I want to delete all 99 of them because less than 100 attributes are no use to me (don't say why I have said I have oversimplified this).

So, If I delete from the same table inside the IF condition, I am encountering infinite loop, and the program stops when the stack overflows. What should I do? I need to delete those values from MyTable and MyTable2.


texts: How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?

texts: Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the optimizer takes?


texts: Properly test queries that are slow on first run

texts:
How can you test a query that's only slow on first run?¹ Are CHECKPOINT and DBCC DROPCLEANBUFFERS the right tool? If so, is running this as sysadmin all that's needed or there's something else involved?

CHECKPOINT;
DBCC DROPCLEANBUFFERS;
GO

I'm asking before I can't make the query slow any more, unless I restart the SQL Server instance (which is of course painfully slow).

(1) Nothing subtle: 35 s to 51 s on first run, around 0.02 s afterwards


texts: How can I find the backup that requires the least amount of archivelogs to restore?

texts:
Oracle 11.2.0.3 with daily full backup and hourly archivelog backup.  RMAN with a catalog.

If I take a backup at 11pm at night and it finishes 5am the next day and  I want to restore a backup to a different system, how can I identify either a time for 'set until' or an SCN that will require the least amount of archivelogs to be replayed?  11pm? After 5am?  RMAN is my least understood part of the database.


texts: Table Design For Patterned Data Using Variable Bucket Ranges

texts:
Looking for table design for this scenario to be implemented on SQL Server 2012 instance.

Scenario

There are a total of seven types of reported data, each with variable number buckets of information.

Each bucket identifies a range such as <5, 5-10, 11-15, or >15. But ranges are specific to each data type and the next type of data will have different bucket ranges; such as <200, 200-210, >210. Hence, making each of the ranges unique to each type of data being collected.

The only commonality is that the values which will fall into each bucket are the same; a decimal value.

Example Data

Trivialized example for car information which each type of data has its own range buckets.

Reported data types: RPM, Wind Resistance and Lateral Accelerations.
RPM has these Range buckets: <1000, 1000-4000, 4000-5000,>5000 and the corresponding values found in each bucket are .80, .90, 1.0, .98.
Wind Resistance

  <5, 5-10, >10 and corresponding values 2 4 15

Lateral Accelerations

  <0.2, .2-.4, .4 - .6, .6 - .8 >.8 corresponding values .2, .3, .8, .1, .2

So looking at the data types, RPM has 5 buckets, Wind Resistance only has 3 and Lateral Accelerations has 6 buckets.

Table Design Attempted

So please review this attempt and suggest an alternative if possible.

X is a placeholder for the table name related to the ultimate data type.

X_Data
PK - X_id          int
FK - OperationsId  int      -- This associates data to a specific process (a report to be printed out)
     Bucket1       decimal
     Bucket2       decimal
     ...
     BucketN decimal  -- N would be the number which is only needed for `X` data.

This would be repeated for a total of seven tables.

Summary

If the above table design is not optimal, what design would be best to handle at 7 minimum and 10 maximum data reporting needs. Each data need has 3-10 individual buckets of differing sizes and each of the final data in a *bucket` is of type decimal.

There is an overall pattern, but within the pattern there exists variability to be addressed...hence I wonder if any one else designed for such a scenario.


texts: In SQL, is it composite or compound keys?

texts:
About SQL (Computing/Databases):

When we have two or more fields in a table that, together, identify its records uniquely, what's the proper way of calling them? Composite or compound keys?

I've seen on the web both uses so I'm not really sure.


texts:
I need 1100 columns in a single table.I already have 928 columns in same table, now I need to add new 150 columns.

I know SQL Server allow maximum 1024 columns.

Now I will not create new table. If I create new table and join my require table, I need to make changes in many places and this is not possible for me. So I want to add all columns in same table(require table).I know this is so tough to execute this table but presently I need to serve this requirement.


texts: Can't alter database roles on a restored database

texts:
My SSMS Login (using an AD group) is allocated all server roles apart from sysadmin so I have quite a lot of access.

Using this Login, I have restored a database from its .bak file. However, my Login wasn't mapped to a User before this database was backed-up so when it is restored, I am not mapped to any Users. But, if I go into database Properties > Files, the Owner is me (although it is my personal Windows accounts not my AD Group Login I use within SQL).

I'm not sure how this affects things but it means I can still read/write to all the tables in the database, even though my Login is not a User in this database. I therefore then want to create a User on this database for my AD Group Login which works successfully. But then when I try to allocate this new User to any database roles, it says that I do not have permission.

What steps am I missing? What permissions do I need to allocate database roles to a new User on this restored database?


texts: How can I investigate why Sql Server is chosing the "wrong" Index?

texts:
I have a Transaction table with about 200 million records, one primary key clustered on Id and 2 indexes:

IX_SiloId_ChangedTime_IncludeTime
IX_SiloId_Time_IncludeContent

I run these 2 statements before I proceed with the actual query to update statistics
Update STATISTICS dbo.[Transaction] IX_SiloId_ChangedTime_IncludeTime WITH FULLSCAN
Update STATISTICS dbo.[Transaction] IX_SiloId_Time_IncludeContent WITH FULLSCAN

This is my query:
DECLARE @Query SiloTimeQueryTableType -- (SiloId, Time) with primary key clustered on SiloId
INSERT INTO @Query VALUES
(1, '2020-12-31'), -- 1000 total values, though it's still the same problem with just one

SELECT  t.*
FROM    [Transaction] t
INNER JOIN @Query q
    ON t.SiloId = q.SiloId
WHERE
    t.Time >= q.Time

Now what happens is for whatever reason Sql Server choses IX_SiloId_ChangedTime_IncludeTime. It then takes forever. If I use WITH (INDEX(IX_SiloId_Time_IncludeContent)) I get the result right away.
The correct index is quite obvious here, but yet SQL Server choses the one that is not even indexed on Time.
I cannot understand this behaviour, but from what I read it is best to avoid hints for Indexes, though I made this Index with this query in mind.
So the question is: what can I do to try to figure out why SQL Server prefers the "wrong" index even though a much better one exists and I just run full statistics update?
Query plan for the forced index (here from the temp table instead of TVP to check if this changes anything as the answer suggested, the result seems to be the same):

Query plan without forced index:

https://www.brentozar.com/pastetheplan/?id=rJOt3G00P
https://www.brentozar.com/pastetheplan/?id=ByFshGAAP (this one is live, as it takes too long)


texts:
I have 2 tables that are used in a view.

If the data does not exist in List tbl then use TaxRiskList instead.

However, TaxRiskListID has a value of 1, which is also a value in VendorID in List tbl.

How can I ensure correct value is returned?

SELECT r.RecipientID, r.[Email], r.[Name], COALESCE(l.VendorName,trl.TaxRiskListName) as VendorName
FROM [Fund.Accounting.Model.Vendors].[Recipients] r
LEFT JOIN
        [Fund.Accounting.Model.Vendors].List l
        ON l.VendorID = r.VendorID
    LEFT JOIN
        [Fund.Accounting.Model.Vendors].TaxRiskList trl
        ON trl.TaxRiskListID = r.VendorID

As an example, in table C the VendorID should actually relate to table A.

However, because table B has the same VendorID value i.e. 5, then table B value is always returned in the select query.

The VendorID column is a FK for table A and table B. So in reality the value 5 could be in table C for both tbl A and tbl B. I guess I need another column to flag what the related tbl is?


texts: Design a relational database (Access) that identifies breaches in records based on user defined limits

texts:
I am trying to create a structured database in Microsoft Access that monitors trading risk. Let's say an organisation wants to analyse all of the trades that it has in its books at any given point in time. In this case, a trade would be an exchange of goods for some form of collateral. Collateral can be stocks, equities, bonds cash etc... basically something that has value that can be traded.
Each trade will have its own unique set of limits. For example, the securities (collateral) that make up each trade can only be equities from approved indices (NDX Index, HSI Index , and so on...). Another example may be that a maximum of 10% of the trade amount can be made up by each security.
When the trade data is imported into the database, I want to flag or extract all of the trades where the unique limits for each trade have been breached. I don't necessarily want to delete the trade records, but I want to possibly group them into a separate table for easy viewing.
Question on Database Structure
I have attached a picture of the (simplified) proposed database schema with the tables in question. Let's say I want to set a limit on the acceptable indices that the particular trade can accept. For this limit on index example, is the structure of the database schema correct for enabling the creation of a query that checks for a given trade:  The limit of acceptable indices to be made of of 80% Primary and 20% Secondary and of those indices, I want indices that are restricted to Spain and Japan?

If the securities associated with a given trade can change on a daily basis, I want to ensure that all of these new securities in this particular trade follow each of the limits imposed on the trade. In this case, a limit on acceptable indices, however different trades may have a different set of limits not only for indices, but for concentration etc.. I don't want to have to write unique queries for each trade (for example, I may want securities of a different trade to be 100% in primary indices and that the are all indices from USA).
If the limits for indices, concentration, approved countries etc. for a given trade are breached for the securities that are contained in that trade, then I want these particular trades flagged to the user.
Note: I have been exploring the option of putting all of the limits as attributes for each trade, but I don't know how I can create a generalized query structure that allows me to identify limit breaches without creating massive queries.
Any suggestions would be greatly appreciated.


texts: Indexing from start or when performance problem arises?

texts:
My question is regarding use of indexes.

Should I start indexing right from the start or when performance problem arises?
We can also create temporary index while executing a query. What are the pros and cons of such techniques?


texts: How do I allow users to see grants, view definitions, PL/SQL, etc in a database without granting them access to change those objects

texts:
As a developer, I need to be able to access the design of the database/database objects from production so I can ensure I'm working with the latest and greatest version of the objects I'm developing changes for (helps to avoid unforeseen problems at deployment).

Also as a developer, I should not have access to modify, execute, update, delete, etc those objects - that's the job of the production deployment team.

As a DBA, how can I provide access to the design of the database so developers can validate the changes they are working on without also granting them carte blanche access to the database objects?

As a specific example, our DBA's have sufficiently locked down the production environment so that, within TOAD, I can't see the PL/SQL body of a package, can't see all of the grants providing access to an object, etc.  The user account being used just has limited access to use some of the objects but can't view the extended details a developer would need to develop changes.

I'd love to have things set up so we have dictionary view access that allows us to see all the grants, the view scripts, the PL/SQL package bodies, table definitions, etc without also giving us more access than we should have in production (ie, we shouldn't be able to change any of those things)

Is there a role that provides this access, a privilege or series of privileges we would need?  If there isn't one, what's a good alternative method for getting this kind of information?


texts:
I've a large Db and most of tables have relationship with each other.
I wanna make Merge replication with another SQL Server. The problem is that when i create Merge replication it gives error "tblItem couled not be dropped, it has some foriegn key constraints". How can i avoid this problem and is there way to do it without dropping any article.


texts:
When we run database jobs, we get the following error:
ORA-02800: Requests timed out
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
For 12 hours, database jobs failing. Although there were no errors, some of our database jobs that were running per minute or hourly stopped working or started running at random times. One of the database jobs we did drop + create has been fixed, but the errors I mentioned in others continue to be getting.
The database jobs effect same table.
Any solution please?
Best regards,


texts: MIN, DATEADD, DATEPART, DATEDIFF functions cause table scan

texts:
A performance issue has been brought to my attention.

It appears an application executes a query from time to time that takes nearly 3 minutes to execute successfully.

The column is of data type 'datetime, not null'

The column is the clustered index of the table.

The query does not use this index however, it uses a non-clustered index of another column that just happens to include this datetime column.  The other column is not referenced in this query.

Example query:

SELECT MIN(DATEADD(second, DATEPART(second, [tablename].[DateAndTime]),
    DATEADD(minute, DATEDIFF(minute, 0, [tablename].[DateAndTime]), 0))) AS
    [Mindate],
    MAX(DATEADD(second, DATEPART(second, [tablename].[DateAndTime]),
    DATEADD(minute, DATEDIFF(minute, 0, [tablename].[DateAndTime]), 0)))
    AS [Maxdate]
FROM [dbo].[tablename] [tablename]
GROUP BY ();

the resulting output looks like this.

Min Date                  Max Date
2015-04-01 00:00:00.000   2015-10-05 11:56:27.000

The only thing I can think its doing this is because the query has to peer inside each row and pick apart the date into sections, it has no choice but to go through every row to do this.

I am hoping there is some way the query could be re-written so that it would simply just use the index to bring back the min and max values in the DateAndTime column and maybe format the output last?


texts: SSAS Tabular QueryMode Confusion. Why Hybrid mode is useful?

texts:
I read couple of articles about QueryMode in Tabular. I finally noticed that hybrid modes (DirectQuery with In-Memory and In-Memory with DirectQuery) are NOT useful for my case. Actually they are not working the way I like.

We have huge Tabular databases and we use In-Memory for all partitions and concern is we run out of memory at some point.

My thought is, if I could use DirectQuery for old partitions and In-Memory for recent partitions (newer dates), this would help us to have fast performing  reports for recent dates and keep data for recent dates in RAM and leave older data which are not accessed often to use DirectQuery.

Does my thought make sense? Is there any way we use Tabular databases this way?

Thanks for sharing your thoughts.


texts: Create SSIS package to copy data between SQL Server 2008 installs on different machines?

texts:
I'm new to the whole SSIS world. I'm trying to create a process to copy data between SQL Server 2008 environments. I want to be able to run a job to export data from my prod database and then use that export to load the data into multiple dev instances of the database. I'm imagining two processes - one export and one import - that can be run separately.

The first question I have is what is the best format for the exported data?

I'm about 90% complete with a process to export the data to flat files, one flat file per table, but I'm wondering if that's the best way to handle it. Would it be faster to write to Excel or another format?

My process uses the Execute SQL task to get a list of all the table names and then uses a Foreach Loop containing a script task that writes the flat files. I may need to exclude some tables which is why I'm using the Execute SQL task to get the table names.

My second question is, once I have the data exported, what's the best way to import it?

I have a query that will order the table names so they get processed in the proper order for foreign keys, so I guess I'll have a similar process as above, where I have an Execute SQL task to run that query, then another Foreach to loop through the table names, and a script task to read the files and populate the tables. Is there a faster/better way to handle that import?


texts: How to have a fail-over resilient SQL Server view over linked mirrored database table

texts:
Say I have a set of servers with mirrored application databases and a set of statistics servers where a couple of the tables are exposed from the application database as views (with some tenant specific filtering). The view looks something like

... as select * from [server].[table] where tenant_id = xyz

Is there a way to create the view such that it doesn't depend on which of the applications server is primary. The view stops working after every fail-over and someone has to fail-back the DB.

What is the best practice when it comes to situations like this? Any not-so-best solution?


texts: postgresql 12.3 convert existing DB to use case-insensitive collation?

texts:
postgresql 12.3 in a container with Alpine Linux 3.10
I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch.
Also, instead of setting this for the whole db, can I add this collation to a field in an existing table, or to a table?


texts: Reverse pattern matching: find the longest prefix

texts:
I have a database with the phone rates. Rather simple one.

Code     Price
----     -----
1        0.005
18       0.002
1888     0.000

Now I have a phone number arriving to the database: 18127551221. I want to do a query and get the price of the call.

I want to be able to find longest possible match in the "Code" column. Length of the Code can be up to 6 symbols so I want something versatile.

Any ideas?


texts: How do DELETE / TRUNCATE affect table / tablespace?

texts:
I am piggybacking off of this question, but asking it from the Oracle perspective.

Specifically, how does a DELETE/TRUNCATE affect the size of the table and/or tablespace?


texts: DB2 9.7 - Find rows inserted by a specific trigger

texts: We are auditing a table TRANSACTIONS that inserts a row in HISTORY_TRANSACTIONS when specific columns are changed. These inserts happen through a number of triggers defined on TRANSACTIONS table. Is it possible to find out which trigger inserted a specific record in HISTORY_TRANSACTIONS ? (seems impossible, but I wanted to confirm that no system tables could achieve this as well?)


texts: What would be a query to list a blocking chain in Sybase ASE 16?  In SQL server I'd use something like this: https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/


texts:
Setup:

Windows Server 2008 R2
SQL Server 2008 R2 SP1
240GB RAM
TempDB is 8x16GB data files w/out auto-grow (128GB total)
Physical/Stand-alone Server

This server is used for ETL processing. We just installed more RAM in this server for a total of 240GB RAM. SQL Server services are the only real things running.

The memory shows up fine in the BIOS, OpenManage and Windows.

If I configure SQL Server to use a Min/Max of 70/100GB memory, we have no issues. However, once I increase that to 120/150GB, I get the following error when I run one of our ETL processes:

  Could not allocate space for object '<temporary system object:
  422234507706368>' in database 'tempdb' because the 'PRIMARY'
  filegroup is full. Create disk space by deleting unneeded files,
  dropping objects in the filegroup, adding additional files to the
  filegroup, or setting autogrowth on for existing files in the
  filegroup. (Msg 1105, State 2, Procedure Unknown, Line 1)

We have never run into this issue prior to changing the memory configuration. After re-configuring back to the original 70/100GB, we do not receive this error.

Things I've tried:

Set the TempDB data files to auto-grow. This simply results in the files auto-growing until disk-capacity is reached and then fails.
Add more TempDB data files. Same error as shown.
Increase the TempDB size to 8x32GB (256GB total)

I'm at a loss as to what could be causing this problem.


texts:
I need to move an SQL Server 2000 database to MySQL. I'm running into several complications. I'm no expert on SQL Server. To get going I'd like to have a csv-dump of structure and data.

First issue, 3 tables which are clearly in use by the application are not listed in the Tables view. When generating a diagram, a popup appears stating that those tables couldn't be loaded. The weird thing is that data from those tables is actually returned in the application (not cache).

The second issue is the use of DTS. I'm using the wizard to export the DB to text. I'm selecting "Copy table(s) and view(s) from the source database".
Then, I get to choose the file type, delimiters etc. There's a drop-down which has the first table selected. My problem is, I can only select 1 table but I need to export the entire DB. When continuing, the server will only export the first table. How do I get it to export the entire DB? Doing it table by table will take far too much time.


texts: Does the scope for 'Alter Any Database' operate similar to 'Create Any Database'?

texts: Does the scope for 'Alter Any Database' operate similar to 'Create Any Database', as in, only those databases created by Login-A under the 'Create Any Database' umbrella can be altered, and not the other databases on the server?


texts: Truncating a table with a foreign key constraint - SQL Server

texts:
I have two tables in a SQL Server Database, StudentAttendance and Attendance with the following structure:

Attendance

AttendanceID
TimeTableID
AttendanceDate
UserName

StudentAttendance

StudentAttendanceID
AttendanceID
StudentID
IsPresent

I run the following query and it works:

TRUNCATE TABLE StudentAttendance

But the following query gives me an error:

TRUNCATE TABLE Attendance

  Msg 4712, Level 16, State 1, Line 4 Cannot truncate table 'Attendance'
  because it is being referenced by a FOREIGN KEY constraint.

Why do I get the above error even though the Attendance table is empty?


texts: Is there harm in applying passwords to built-in Oracle Roles like DBA, Connect, etc

texts:
First, thanks for any help you may be able to provide.  Oracle 12.1.0.2 on Windows x64

Is there any harm in applying passwords (for example: alter role connect identified by "my_password") to all built-in roles? I ask as an audit is complaining that these built-in roles (such as connect, dba, java_admin, etc.) by default have no password requirement.

I've checked documentation and cannot find any information as to what subtle or not so subtle repercussions may occur if I do password protect these roles. Again thanks for any help you can provide.


texts: How to represent NULL in relational algebra?

texts:
In the 3rd part of the question below:

  Study the tables given below and write relational algebra  expressions for the queries that follow.

STUDENT(+ROLLNO, NAME, AGE, GENDER, ADDRESS, ADVISOR)
COURSE(+COURSEID, CNAME, CREDITS)
PROFESSOR(+PROFID, PNAME, PHONE)
ENROLLMENT(+ROLLNO, +COURSEID, GRADE)

  Primary keys are preceded by a +. ADVISOR is a foreign key referring to PROFESSOR table. ROLLNO and COURSEID in ENROLLMENT are also foreign keys referring to THE primary keys with the same name.

  Names of femal students
  Names of male students along with advisor name
  Roll Number and name of students who have not enrolled for any course.

How do I represent NULL marks in relational algebra?

I projected STUDENT NAME and ROLLNO after assigning COURSEID from ENROLLMENT table as

COURSEID = NULL

Is this valid?


texts:
So I am not a coder, clearly. I'm hoping that someone would be kind enough to understand my situation and help me ask the right questions to have a DB designed for my needs.

I currently use Excel to store Property Addresses, Phone Numbers (lots of them) and Names of Persons. Depending on the Sheet, these could easily reach into several thousand unique lines of information.

The issue I have right now, is that Excel is seemingly taking things into its own hands and moving cells up from their original location. This of course is causing serious problems with matching up. I have seen data be off by 1 line up to 8 lines up from its original position. I can't get an explanation from any experts, as to why.

Of course, I have to delete items as they are no longer needed or there are duplicate phone numbers. Every line of data is kept in its own cell, within the same line.

What I need to know is the best and most secure way to store this data and not be worried about it becoming out of alignment, because I delete something. The information needs to be searchable by phone number and be able to delete and move from one sheet or table, without any issues. What should I be asking a developer to create for me?

I obviously don't know the lingo, but if it's explained once, I will be able to follow to a point. Someone mentioned storing the Properties individually and not in lines of data, if that makes sense.

Any guidance would be greatly appreciated!


texts: Failed to start service, error 2. TNS-12560: TNS:protocol adapter error TNS-00530: Protocol adapter error

texts:
i have start oracle services but its giving error:
windows could not start the
OracleOraDb11g_home1ClrAgent Service on local computer.
Error2: the system cannot find the file specified.

Also there are multiple services OracleORaDbHome1 and OracleOraDbhome2.


texts: Generating a generic INSERT/UPDATE Trigger to conform data?

texts:
I am trying to generate triggers on a number of tables to force data to upper-case and to trim leading and trailing spaces when data is inserted or updated.  The issue I am having is determining in the trigger a general way to tie the INSERTED pseudotable to the base table.  Not all of the tables have the same PK name although many do have a unique column called _DataChanges_RowID in each table which can be used.  I know that I can inspect the PK and generate teh triggers using those columns, but before I look at that, I wanted to know if there is a generic way to join the INSERTED pseudotable to the base table which is simpler and column agnostic.

DECLARE @TriggerTemplate AS VARCHAR(MAX) =
'CREATE TRIGGER <<SchemaName>>.<<TriggerName>> ON <<FullTableName>>
AFTER INSERT, UPDATE
AS BEGIN
    IF (ROWCOUNT_BIG() = 0)
        RETURN;
    IF TRIGGER_NESTLEVEL(( SELECT object_id FROM sys.triggers WHERE QUOTENAME(name) = ''<<TriggerName>>'' ), ''AFTER'', ''DML'') < 1
    UPDATE <<FullTableName>>
    SET <<SQLColumnUpdate>>
    WHERE <<FullTableName>>._DataChanges_RowID IN (SELECT _DataChanges_RowID FROM INSERTED);
END;
GO
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''INSERT'';
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''UPDATE'';
GO
';

WITH cols AS
    (
        SELECT
            FullTableName = QUOTENAME(S.name) + '.' + QUOTENAME(T.name),
            SchemaName = QUOTENAME(S.name),
            TableName = QUOTENAME(T.name),
            ColumnName = QUOTENAME(C.name)
        FROM
            sys.columns C
            INNER JOIN sys.tables T
                ON C.object_id = T.object_id
            INNER JOIN sys.schemas S
                ON T.schema_id = S.schema_id
        WHERE
            C.is_computed = 0
            AND C.system_type_id IN (
                    167,  -- varchar
                    175,  -- char
                    231,  -- nvarchar
                    239   -- nchar
                )
    )
, TablesAndColumns AS (
    SELECT
        cols.FullTableName
        , cols.TableName
        , cols.SchemaName
        , TriggerName = QUOTENAME('TRG_DATA_CONFORM_' + cols.FullTableName)
        , SQLColumnUpdate = STUFF((SELECT ', ' + c2.ColumnName + ' = UPPER(LTRIM(RTRIM(' + c2.ColumnName + ')))'
               FROM cols c2
              WHERE c2.FullTableName = cols.FullTableName
              ORDER BY c2.ColumnName
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')
    FROM cols
    GROUP BY cols.FullTableName, cols.SchemaName, cols.TableName
)
SELECT FullTableName
    , SQLTrigger = REPLACE(REPLACE(REPLACE(REPLACE(@TriggerTemplate, '<<SQLColumnUpdate>>', SQLColumnUpdate), '<<TriggerName>>', TriggerName), '<<FullTableName>>', FullTableName), '<<SchemaName>>', SchemaName)
    , KeyOK = CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE QUOTENAME(TABLE_SCHEMA) = SchemaName AND QUOTENAME(TABLE_NAME) = TableName AND COLUMN_NAME = '_DataChanges_RowID') THEN 1 ELSE 0 END
FROM TablesAndColumns
;


texts: Will redirecting queries to dev/null warm up indexes?

texts:
I'm trying to warm up the buffer pool. Will running a query and re-directing the output to /dev/null suffice?

e.g. mysql -u username -p -e "select id from tbl where 1=1" > /tmp/results.txt

Thanks


texts: Unpivot with rows converted to columns as well as columns converted to rows

texts:
I've tried to understand unpivot examples on the web but they don't quite result in what I'm trying to do, which is to have specific rows turned into columns and columns turned into rows.   To try and illustrate what I mean I have included a picture...

I believe that the data in a given column needs to be of the same type,  so if that's the case then it's ok to have all the columns as varchar.   The data in the source table will not need to be part of any sums,  just displayed as-is.  I am just trying to 'rotate' this to display the data in a vertical way for end-user consumption.


texts: How would you optimize this 2 milion records table or queries?

texts:
I have a relatively large InnoDB table that contains domain names, I want to track when they expire, their traffic rank, owner email, and many parameters like that.

Currently I have around 2 million records, I intend to keep it under 5 million.

I wonder if there is anything else I can do to optimize it, currently a query like this takes around 4-8 seconds:

SELECT *
FROM domains
WHERE domain_whois_date > 0
ORDER BY domain_whois_status asc
LIMIT 500

I consider that memory and caching related options I set in my.cnf work decently because if I quickly run the same query again it loads in few ms. But table updates very often and most queries are not cached and take longer (so I understand).

So far my best solution is to create a memory based table, a mirror of domains called domains_cache and run searches in that; but seems to crash often, I think it's because this memory table takes almost 100% of ram.
Is this the right approach ?

Main InnoDB table takes around 500MB, memory table with same data uses around 2GB.
Running on CentOS virtual machine, 2 cores, 4GB ram.


texts: oracle bulk loader processing columns with newline characters

texts:
I'm trying to load a file with description columns which may contain newline characters. The file is Unix format utf16, tab separated and description columns are enclosed in ". The problem is bulk loader assumes the newline is end of record even though it is enclosed in the " characters, and it would create more rows than there should be.

Here's an example of the record I tried to load:

key_column description
1   "very
long
description"
2   "some more ""
description"
3   "even more '
description"

Here's what I have for my control file:

load data
CHARACTERSET UTF16
infile 'C:\LoadWSRworkfolder\book1.txt'
badfile 'C:\LoadWSRworkfolder\wsl2106.bad'
append into table load_table1
fields terminated by "  "
optionally enclosed by '"'
trailing nullcols
(
key_column,
description char(4000)
)


texts: Does MySQL close cursor if stored procedure encounters an exception?

texts:
I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines.

Assuming, that CURSOR is opened when DBMS raises MYSQLEXCEPTION and goes to EXIT HANDLER if it is declared...

Approach #1:

DELIMITER $$

CREATE PROCEDURE `test1`()
BEGIN
DECLARE `EOS` BOOLEAN DEFAULT FALSE;

DECLARE `buffer` INT UNSIGNED;

DECLARE `MyCursor` CURSOR FOR
SELECT
    `id`
FROM
    `MyTable`
LIMIT 50;

DECLARE EXIT HANDLER FOR MYSQLEXCEPTION
    BEGIN
    ROLLBACK;

    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Oops... FATAL ERROR!', MYSQL_ERRNO = 5656;
    END;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET `EOS` = TRUE;

START TRANSACTION;

OPEN `MyCursor`;

cycle: LOOP

    FETCH `MyCursor` INTO `buffer`;

    IF `EOS`
    THEN LEAVE cycle;
    END IF;

    -- INSERTs to another tables, UPDATEs of another tables and DELETEs from to another tables
    -- Section that might throw a MYSQLEXCEPTION

END LOOP cycle;

CLOSE `MyCursor`;

COMMIT;

END$$

DELIMITER ;

Note that if MYSQLEXCEPTION will be thrown in commented section, then MyCursor probably will not be closed. Or it will be closed and deallocated in this case?

Somewhere I've seen following...

Approach #2:

DELIMITER $$

CREATE PROCEDURE `test2`()
BEGIN
DECLARE `EOS` BOOLEAN DEFAULT FALSE;

DECLARE `buffer` INT UNSIGNED;

DECLARE `MyCursor` CURSOR FOR
SELECT
    `id`
FROM
    `MyTable`
LIMIT 50;

DECLARE EXIT HANDLER FOR MYSQLEXCEPTION
    BEGIN
    ROLLBACK;

    CLOSE `MyCursor`; --   <---- is this even needed here ?

    SIGNAL SQLSTATE 'ERR0R' SET MESSAGE_TEXT = 'Oops... FATAL ERROR!', MYSQL_ERRNO = 5858;
    END;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET `EOS` = TRUE;

START TRANSACTION;

OPEN `MyCursor`;

cycle: LOOP

    FETCH `MyCursor` INTO `buffer`;

    IF `EOS`
    THEN LEAVE cycle;
    END IF;

    -- INSERTs to another tables, UPDATEs of another tables and DELETEs from to another tables
    -- Section that might throw a MYSQLEXCEPTION

END LOOP cycle;

CLOSE `MyCursor`;

COMMIT;

END$$

DELIMITER ;

And now I'm a bit confused about CURSOR handling.

Question: Which of this approaches is more accurate and reliable? If I'm not closing CURSOR in EXIT HANDLER will it impact anything? And if it is, how "bad" might be consequences?


texts: How to restore database from a Mac OS X disk image?

texts:
So I did take the ship with brave Capitan, and the ship ran aground... My Mac OS 10.11 tried to upgrade to 10.11.1 and freeze my Macbook! There was no backup, Time Machine stop working, so I did a dmg disk image of all and did the manual installation of the OS. Now I'm installing all things needed, one by one (there is no migration from dmg disk image). I've just downloaded MAMP, and than occurs to me... how to recover the databases still stored in the dmg file?

Is it just copy and paste a file? Witch one? Any suggestions?


texts:
My team foundation server is running its databases locally and the person who originally installed the system decided to just use LocalSystem as the service account for SQL Server instead of actually planning the accounts out.

I am trying to create a backup plan (via the TFS power tools Backup Plan extension in the TFS admin console) and it is requiring a domain account in order to complete the plan.

My plan is to quiesce tfs, go into SQL Server Configuration Manager, change the service accounts to a plain 'no rights other than domain user' account, start the mssql services and thne unquiesce tfs.

Are there any problems or concerns I should be aware of before doing this? Will simply changing the LocalSystem account to a domain account cause any problems?


texts: how to insert values in a table with foreign key field

texts:
I am creating a database where I have a table with a foreign key. I am using the following command:

insert into ques_bank
    (q_no, uid, question)
values
    (NULL, select uid from user_info where username ='more','jdkjfsd dkskdsn skdjsd');

But it gives a syntax error.
I am having troubles with foreign key. Any help will be appreciated.


texts: Backup SQL Server 2017 to remote directory

texts:
There is SQL Server instance with 4 DBs.
There is remote directory '\u12160.your-backup.com\backup\SQL' which can be accessed ONLY with User/Password (no user auth or anything other).

I want to backup DBs on schedule with SQL Server Agent job like this

EXEC sp_BackupDatabases @backupLocation='\\u12160.your-backup.com\backup\SQL', @backupType='F'

but it throws error

Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 1

Is it possible somehow to pass username/password? Or are there any other ways to backup straight into remote directory&

Thank you.


texts: SSMS Restore Database wizard slow to respond

texts:
I've just noticed this recently on one of the SQL Server 2012 SP3 instances that I oversee.

When I try to restore a database using SSMS 2012 (by right-clicking on the database,and choosing Tasks>Restore>Database), the Restore Database window (where you can pick the source, and destination database, etc.) takes a very long time before it finally shows up on the screen. I'm talking more than 10 minutes here at times.

I've noticed this both using my local SSMS, and on the SSMS installed on the Windows Server where the SQL Server instance is installed.

I am only noticing this on one specific instance. I don't see the same slow response issue when doing a Restore on the gui on the other instances.

Have any of you seen this before? I'd appreciate the help. Thanks.


texts:
Sometimes I experience slow writing performance on my dedicated (SSD Software Raid 1) server with PostgreSQL 9.5. I ran pg_test_fsync and results are not really good. Actually there are much better on my laptop.

5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                                n/a*
        fdatasync                           142.923 ops/sec    6997 usecs/op
        fsync                               286.576 ops/sec    3489 usecs/op
        fsync_writethrough                            n/a
        open_sync                                    n/a*
* This file system and its mount options do not support direct
I/O, e.g. ext4 in journaled mode.

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
        open_datasync                                n/a*
        fdatasync                           249.736 ops/sec    4004 usecs/op
        fsync                               296.435 ops/sec    3373 usecs/op
        fsync_writethrough                            n/a
        open_sync                                    n/a*
* This file system and its mount options do not support direct
I/O, e.g. ext4 in journaled mode.

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
         1 * 16kB open_sync write                    n/a*
         2 *  8kB open_sync writes                   n/a*
         4 *  4kB open_sync writes                   n/a*
         8 *  2kB open_sync writes                   n/a*
        16 *  1kB open_sync writes                   n/a*

Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
        write, fsync, close                 266.308 ops/sec    3755 usecs/op
        write, close, fsync                   0.513 ops/sec  1949165 usecs/op

Non-sync'ed 8kB writes:
        write                            145663.483 ops/sec       7 usecs/op

I also ran dd to see writing speed:

sudo dd if=/dev/zero of=/tmp/output bs=8k count=10k; rm -f /tmp/output
10240+0 records in
10240+0 records out
83886080 bytes (84 MB, 80 MiB) copied, 0.0765848 s, 1.1 GB/s

Once again. Writing speed is better on my laptop. Do you have any idea how to fix it?

P.S. I've been using zfs filesystem.


texts: Cannot open database “user_auth.mdf” requested by the login. The login failed. Login failed for user 'web_user'

texts:
I am trying to deploy my first VB.NET application on GODADDY. I created a SQL server database and was able to create tables using SQL Server Management Studio. I created a user web_user and was able to login to the GODADDY database using that user. However when my website tries to connect with this database using the same user id and password, I get the following error:

  Cannot open database "user_auth.mdf" requested by the login. The login failed. Login failed for user 'web_user'.

My connection string is mentioned below. It is similar to what was recommended by GODADDY:

<add name="ConnectionString"
     connectionString="Server=XYZ;Database=user_auth.mdf;User ID=web_user;Password=zzz;
     Trusted_Connection=False" providerName="System.Data.SqlClient" />

Please help. Its driving me nuts. Stack Trace is:

  [SqlException (0x80131904): Cannot open database "user_auth.mdf" requested by the login. The login failed.
  Login failed for user 'web_user'.]


texts: Advice on diagnosing a "sometimes" slow query

texts:
I have a stored procedure which returns results from an indexed view via a covering index. Usually, it runs fast (~10ms), sometimes it can run up to 8 seconds.

Here's an example random execution (note: this isn't a slow one, but the query text is the same apart from the value passed through):

declare @p2 dbo.IdentityType
insert into @p2 values(5710955)
insert into @p2 values(5710896)
insert into @p2 values(5710678)
insert into @p2 values(5710871)
insert into @p2 values(5711103)
insert into @p2 values(6215197)
insert into @p2 values(5710780)

exec ListingSearch_ByLocationAndStatus @statusType=1,@locationIds=@p2

Here's the SPROC:

ALTER PROCEDURE [dbo].[ListingSearch_ByLocationAndStatus]
    @LocationIds IdentityType READONLY,
    @StatusType TINYINT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      -- lots of fields
    FROM        [dbo].[ListingSearchView][a] WITH (NOEXPAND)
    INNER JOIN  @LocationIds [b] ON [a].[LocationId] = [b].[Id]
    WHERE       [a].[StatusType] = @statusType
    OPTION (RECOMPILE);

(note: i added the OPTION (RECOMPILE) hint recently after some advice, but it hasn't helped.

Here's the covering index (note: the view also has a clustered index on ListingId, which is unique)

CREATE NONCLUSTERED INDEX [IX_ListingSearchView_ForAPI] ON [dbo].[ListingSearchView]
(
    [LocationId] ASC,
    [StatusType] ASC
)
INCLUDE ( -- all the fields in the query) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

I put a profiler trace on, with showplan XML statistics.

Here's a slow one (6 seconds), and the relevant plan:

Looks exactly as i'd expect, and is the same plan when the query is fast.

Here's the zoom in on the costly part of the plan, if that helps:

Here is the full schema of the view/backing tables, if that helps: https://pastebin.com/wh1sRcbQ

Notes:

Indexes have been defrag'd, statistics up to date.
Originally query was inline against the view, but i moved to SPROC to try and help stabilize. Hasn't helped.
Adding WITH OPTION (RECOMPILE); hint (didn't work, so can't be parameter sniffing?)
Other queries in the system also sometimes run slow, and they also have no obvious issues in their plan.
Could be locking? Not sure how to confirm.

Any ideas on what i could try next?

Thanks


texts: simple RECOVERY mode, but with not static data

texts:
About recovery modes, I know that if you want point in time recovery, we should use FULL recovery mode. if this is not necessary, you can use SIMPLE recovery mode.

But, I read about Simple recovery mode, and it says that we should use this if point in time recovery is not important and if your data is static.

Here we don't need LOG backups, we only use Full backups and Diffs, and I thought that should be good for performance and disk space to use Simple recovery databases. But As I said, we don't have static data. there's a lot of insert, updates and development updates ( yes I don't understand why they don't need point in time recovery , but, anyway...).

My question is, is still a good Idea to use simple recovery, or by not having static data, the log will still grow, and etc?


texts: PostgreSQL return second 'Group' as an array

texts:
I have some tables that include websites and visitors and I am trying to write a query that produces a 'top 10' for each site, the tables look something like this...

websites
-------
website_id (pk)
url
description

visitors
-------
visitor_id (pk)
ip_address

website_visits
--------
visit_id (pk)
website_id (fk)
visitor_id (fk)
visit_date
visit_duration

I know that I can use two GROUP BY 's to produce a single list ordered by website using the query below...

SELECT w.website_id, v.visitor_id, count(wv.visit_id)
FROM website_visits ws, websites w, visitors v
WHERE wv.website_id = w.website_id and wv.visitor_id = v.visitor_id
GROUP BY w.website_id, v.visitor_id
ORDER BY w.website_id ASC, count(wv.visit_id) DESC

But I would like to improve this by a) only showing the top ten for each site, and b) grouping each top 10 result into an array, so that the results look something like this:

website_id    top_ten
432           [{visitor_id:354,visits:958},{visitor_id:987,visits:971}...etc]
325           [{visitor_id:456,visits:955},{visitor_id:1056,visits:465}...etc]

(I have used JSON here but it could be in any format)

I have experimented with using the SELECT ARRAY(...) function in a subquery but I cannot seem to get it working and I am not sure if thats the correct approach?

Can someone advise? Thanks

Using PostgreSQL 9.0 by the way


texts: Database MySQL - Transpoing Table from one form to other

texts:
SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
         CONCAT(
           'MAX(IF(function_NAME = ', function_NAME,
           ',budget, NULL)) AS ', function_NAME))
INTO @sql
FROM country_report dr JOIN function_master am
    ON dr.function_name = am.func_NAME
 WHERE am.func_CATEGORY = 'FUN';

     SELECT country_location, time_of_day,@sql
               FROM country_report dr
               WHERE daily_param = 1
                AND country_location in ('hong kong','tokyo')
              GROUP BY country_location, time_of_day;

output -

| country_location | time_of_day | @sql |
| hong kong        | sod         | MAX(IF(FUNCTION_NAME = Dance program,budget, NULL)) AS Dance program,MAX(IF(FUNCTION_NAME = singing,budget, NULL)) AS singing
| hong kong        | eod         | MAX(IF(FUNCTION_NAME = Dance program,budget, NULL)) AS Dance program,MAX(IF(FUNCTION_NAME = singing,budget, NULL)) AS singing

but required output is

| country_location | time_of_day | Dance program | singing |
| hong kong        | sod         | 3             | 4       |
| hong kong        | sod         | 4             | 4       |

Please suggest


texts: "INSERT INTO res_partner () VALUES ()" failure (autoincrement in postgres)

texts:
This is DDL for the table:

CREATE TABLE public.res_partner (
    id serial NOT NULL,
    CONSTRAINT res_partner_pkey PRIMARY KEY (id)
);

This is SQL:

insert into public.res_partner () values ();

and the error:

SQL Error [42601]: ERROR: syntax error at or near ")"
  Position: 33

$ psql --version
psql (PostgreSQL) 11.1 (Ubuntu 11.1-1.pgdg18.04+1)

I see that this statement is absolutely legal. My code worked 2-3 days ago (PHP with Doctrine). But now I cannot execute this statement from dBeaver client and from my app.


texts: SQL Rebuild Index, Recovery Model, and db log filesize?

texts:
I have a database that we currently run transaction log backups throughout the day, every 30 minutes to be exact, and we run a full backup each day at 2am.

Every Saturday at 3am, we have a job setup to rebuild the indexes on all tables.

That being said, doing the index rebuild causes our transaction log to grow greatly. I'm toying with different ideas to alleviate the extra drive space needed (approximately 25gb post re-index).

I was considering setting the database recovery model to simple prior to the rebuild task, to prevent all of the index rebuilding from being logged, and then setting it back to full once the rebuild is complete.

Is anyone else using this method? Or can anyone provide insight/advice as to why this may be a bad idea? Or any tips about how to handle huge log files while performing db maintenance tasks?


texts: I have the result set above that I stuffing into a table variable @ApptTemp, and trying to transform it into something similar to the one below.  Note that of the 4 rows that have a description of Staff Meeting, that they are 2 different events.  One starting at 8:00 AM, and one starting at 10:00 AM.  I have tried a few things, PIVOT , and a WHILE loop with no success.  I was thinking of using a CURSOR, but I have not written one that advanced before.  Any suggestions would be greatly appreciated.


texts: truncate table in mysql 8.0 is slower than older version

texts:
I have few hundreds of tests which use mysql. I was using Mysql 5.7 (percona) and I changed it to Mysql 8.0 . My problem is that before every test I truncate ALL my tables. This was fine with 5.7 but mysql 8.0 takes 5 seconds to truncate all 60 tables.
This is mysql config which I use for both version:
[mysqld]
skip-external-locking
key_buffer_size = 32M
max_allowed_packet  = 1G
thread_stack        = 512K
thread_cache_size       = 8
expire_logs_days    = 2
max_binlog_size         = 100M
max_connections = 1200

tmp_table_size                                  = 512M
max_heap_table_size                             = 512M

skip-name-resolve

innodb_buffer_pool_size=256M
innodb_log_buffer_size=32M
innodb_log_file_size=64M

innodb_flush_method=O_DIRECT
innodb_thread_concurrency=64
innodb_write_io_threads=32
innodb_read_io_threads=32
innodb_flush_log_at_trx_commit                  = 2
wait_timeout = 1800
interactive_timeout = 1800
innodb_file_per_table = 1

P.S: This is not just truncate, create table and alter table also take a lot more time compared to previous version.
P.S 2: I tested this both on my system and GitHub's Action. Same result for both.


texts: Single table or multiple table design when records have a clear distinction but are queried together?

texts:
Let's say I have the following types of recurring data (all types periodically come in).

Post (Title, Summary, Text, Timestamp)
Image (URL, Caption, Timestamp)
Event (StartDate, EndDate, Title, Description, Location)

As the style of the data suggests, we can split them into their own separate tables, Post, Image and Event respectively. However the majority of the queries on these data are of the "timeline" variety (Return me all posts, images and events within a given timeframe sorted by time), though there are also some queries that work on a single type alone.

Someone suggested that we combine all three into a single table and encode the unique attributes into a json field (We're using Postgres 9.3+) rather than use a UNION of the three tables.

So according to that model, we'd have

Info (Type, Timestamp, Attributes)

where Attributes is a JSON field which will have different keys based on the Type value.

Which is the better approach here?

P.S. This is a simple example for explaining the question but in reality there are closer to 9 different "types" of data and the number of unique attributes is much larger. We might need to join on one of those unique attributes as well in the future (not sure if Postgres supports this).


texts:
In my data model I have some static data that is added to over time as the system changes.  For instance:

create table region (id SERIAL,crname TEXT);

customer has a link to the region, my system adds regions over time.  new regions are manually added by the dba executing an insert statement provided by the dev team for a release.  I'm looking for a Chris Date style perspective on how this should be modeled and implemented.  Should the key be managed by a database sequence where each database instance can have different keys for region "South West Denver" depending on when they are added, or is it acceptable to have key 32 for "South West Denver" in every instance of the region table for dev/test/qa/pre-prod etc? Effectively not using a sequence or database managed key.


texts:
How to measure the delay between secondary node to catch up with the primary node?
I am using red gate to monitor the databases and this is what mentioned there
Guideline values: To estimate how long a secondary replica will take to catch up with the primary replica, divide the log send queue by the rate of log bytes received.

Based on this graph can you advise me how can I measure this delay ? it is not clear for me how to divide these 2 values
If there is another way to measure the delay I will appreciate to share


texts:
I'm loading 2 csv files into 2 tables in SQL Server Data Tools using SSIS, and the 2nd one is connected to the 1st via a foreign key constraint. The problem is that a row of the 2nd table has a key that is missing from the 1st table, so the foreign key constraint fails and an error message is presented.

Is there a way to check the foreign key constraint before insertion and catch the error so that it doesn't interrupt the insertion of the rest of the rows?


texts: pg_dump - how to split it into directories and files?

texts:
I am looking for a way to dump PostgreSQL12 database model into a directory/file structure, instead of a single file - for versioning purposes.
I found this old thread, which mentions this exact case and describes a --split flag to be used with pg_dump.
However, this option does not exist in the current pg_dump for PostgreSQL12. I also tried the option --format=directory but that's not it. How do I achieve this effect?


texts: How to make subtraction without repeating the subqueries?

texts:
I need to efficiently obtain:

  players who play more than one world cup and who have never been champion.

primary-key | foreign-key | pk-and-fk

PLAYER<player_id, player_name, country_player, ...>

WORLD<year, winner, ...>

PLAYED_WORLD<player, world_year, ...>

In the first operand of the subtraction I obtain the players with more than 1 world cup no matter if they are champions.
In the second operand, I get the players with more than 1 world cup that have already won.

The following query works for me but my idea is to optimize it to be more efficient, since the difference between both operands is nothing more than: WHERE country_player = winner

SELECT *
FROM        (SELECT player_name
        FROM    (SELECT player_id, world_year, player_name, country_player
                FROM    (SELECT player, world_year
                        FROM    (SELECT player
                                FROM played_world
                                GROUP BY player
                                HAVING count(player) > 1) AS playerMoreThan1
                        NATURAL JOIN played_world) AS worldPlayers
                JOIN player ON player= player_id) AS infoWorldPlayers
        JOIN world ON world_year = year) noChampions
EXCEPT      (SELECT player_name
        FROM    (SELECT player_id, world_year, player_name, country_player
                FROM    (SELECT player, world_year
                        FROM    (SELECT player
                                FROM played_world
                                GROUP BY player
                                HAVING count(player) > 1) AS playerMoreThan1
                        NATURAL JOIN played_world) AS worldPlayers
                JOIN player ON player = player_id) AS infoWorldPlayers
        JOIN world ON world_year = year
        WHERE country_player = winner)

Is there a strategy to reuse the queries of the first operand?

If you need the design and the initial load of the db the attached in the publication.


texts: Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?

texts:
I kick off the following command in my app:

WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)

When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE

Note: moved from StackOverflow.


texts: MySQL 8: Most performant query to obtain latest version of each record


texts: Programmatically Check Dynamic File Name For Invalid Characters

texts:
I have a SQL Script that runs every night and will generate files showing each Sales Reps production for the previous day.  This works 99% of the time, the 1% it does not work is when one of the variables has invalid characters, which of course would generate an invalid file name.  Is there a t-sql way of checking if @filename is a valid string and if it is not replace @filename with a generic naming convention so the procedure does not error?

Sample syntax below:

`Declare @RepName NVARCHAR(500), @filename nvarchar(4000), @Detailedinfo varchar(50)

DECLARE cursor1 CURSOR FOR

  Select Distint repname, saleinfo from dailysalesjournal;

  OPEN cursor1

  FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo

  WHILE @@FETCH_STATUS = 0
  BEGIN

    Set @filename = 'C:\Test\GeneratedFromSQLProc\'+@RepName+'\SaleInfo'+@Detailedinfo+'.csv'

    Print @filename

FETCH NEXT FROM cursor1 INTO @RepName, @Detailedinfo

END

CLOSE cursor1

DEALLOCATE cursor1  `


texts: Error 7399 when the Access file has a password

texts:
I'm trying to create a linked server to access an old MS Access database (.mdb file) on SQL Server 2012, but I always get the error 7399 (authentication denied). So I tested with another mdb file that has no password and, in this case, the linked server worked perfectly. I've already given all permissions to the file's folder.

I tried to deny the inprocess in the ACE.OLEDB driver, but, if I do that, I got a new error

  7302 (SQL Server could not create a driver instance to this linked
  server).

I can't see what I'm doing wrong. I've searched about this a lot, and I still didn't find out a solution.


texts:
I am having the following issue of SSIS AND XLSM load time issue Visual Studio 2010

I want to speed this up

I am reading a .xlsm as my excel source

I have noticed its loading / reading every row of the excel...even though they are blank

e.g I only have 300 + rows of data at the moment, but it loading every row even though they are blank e.g going pass the 400 row where there is no data yet

I don't want it reading 200,000+ rows before it actually loads the 300

how do I stop this please team


texts: We are currently running SQL Server 2008 R2 and want to upgrade to 2014 but we need to leave some DBs at 2008 so we have decided to create a separate 2014 server and just migrate some DBs to it.  Is there a way to script all the server settings such as Linked servers, users, scheduled jobs without doing each one individually?  Can I clone or copy the whole instance somehow.  Is there a better way to do this?


texts: Selectively ignoring tuples from a table

texts:
The problem that we are tackling with a data mining application is best described with an illustrative example.
There is a sample table myTable, which is defined as follows:
CREATE TABLE myTable
(
  id        INT UNSIGNED AUTO_INCREMENT,
  colA      VARCHAR(8),
  colB      VARCHAR(12),
  revFlag   CHAR(8), -- 'REVISED' or any other value, including NULL

  PRIMARY KEY(id)
);

Any tuple with a revFlag value of REVISED takes precedence over any other tuple with the same value for colA, as long as the revFlag value of the latter tuple is not REVISED.  In other words when we select rows from the table we skip all rows for which the revFlag value is not REVISED and there exists a row with the same value for colA for which the revFlag value is REVISED.
We populate the table as follows:
INSERT INTO myTable(colA, colB) VALUES ('XSR0KA3V', 'OLD-O7RAR81X'),
('4F2JG71O', 'OLD-E71BE63L'), ('MML3HN48', 'OLD-B02PFB63'),
('5H0MWVSB', 'OLD-V70XLGHT'), ('JW73ZX0J', 'OLD-KME1GXQF'),
('XZV0EY0G', 'OLD-N06BURDF'), ('9HBQZ88V', 'OLD-76HSPUAL'),
('YI5AT6G4', 'OLD-X8KAWD7Z');

INSERT INTO myTable(colA, colB, revFlag) VALUES
('XSR0KA3V', 'NEW-O7RAR81X', 'REVISED'),
('MML3HN48', 'NEW-B02PFB63', 'REVISED'),
('9HBQZ88V', 'NEW-76HSPUAL', 'REVISED'),
('YI5AT6G4', 'NEW-X8KAWD7Z', 'XYZ'),
('Z8H2B5KY', '3RINJV0K', 'REVISED');

Naturally SELECT * FROM myTable yields the following:
+----+----------+--------------+---------+
| id | colA     | colB         | revFlag |
+----+----------+--------------+---------+
|  1 | XSR0KA3V | OLD-O7RAR81X | NULL    |
|  2 | 4F2JG71O | OLD-E71BE63L | NULL    |
|  3 | MML3HN48 | OLD-B02PFB63 | NULL    |
|  4 | 5H0MWVSB | OLD-V70XLGHT | NULL    |
|  5 | JW73ZX0J | OLD-KME1GXQF | NULL    |
|  6 | XZV0EY0G | OLD-N06BURDF | NULL    |
|  7 | 9HBQZ88V | OLD-76HSPUAL | NULL    |
|  8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL    |
|  9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ     |
| 13 | Z8H2B5KY | 3RINJV0K     | REVISED |
+----+----------+--------------+---------+

We would like to design a query that does not return any tuples that are REVISED by other tuples.  In our case the output should look like this:
+----+----------+--------------+---------+
| id | colA     | colB         | revFlag |
+----+----------+--------------+---------+
|  2 | 4F2JG71O | OLD-E71BE63L | NULL    |
|  4 | 5H0MWVSB | OLD-V70XLGHT | NULL    |
|  5 | JW73ZX0J | OLD-KME1GXQF | NULL    |
|  6 | XZV0EY0G | OLD-N06BURDF | NULL    |
|  8 | YI5AT6G4 | OLD-X8KAWD7Z | NULL    |
|  9 | XSR0KA3V | NEW-O7RAR81X | REVISED |
| 10 | MML3HN48 | NEW-B02PFB63 | REVISED |
| 11 | 9HBQZ88V | NEW-76HSPUAL | REVISED |
| 12 | YI5AT6G4 | NEW-X8KAWD7Z | XYZ     |
| 13 | Z8H2B5KY | 3RINJV0K     | REVISED |
+----+----------+--------------+---------+


texts: Cleanup / Prune Unreferenced Data in Dimension Tables

texts:
We have a star schema data warehouse running on MySQL 5.6. We keep a rolling 18 months of data in our fact tables using partitions by month. We have a number of dynamic dimension tables that are referenced by multiple fact tables. However, we have no easy way to remove the rows from dimension tables that are no longer referenced by any fact table. Quick summary looks like this:

dim_url - 1B rows - 360GB
fact_ranks - 2.3B rows - 240GB
fact_seen - 2.8B rows - 295GB

Currently we are attempting to use a combination of Percona Archiver and triggers to generate "used dimension keys" tables, so we can do the process online. We then use the key table to build a new dimension that only has referenced rows. However, we have been unable to complete this process in production and estimate it could take up to a month.

This has to be a common problem with a more elegant solution.


texts: SQL Server 2008 R2 - Merge Replication - Can the Distribution Database size cause replication problems?

texts:
Setup:

We have a SQL Server 2008 R2 instance running on a central server.
The server is a merge-replication publisher.
We have 'N' subsribers running SQL Server 2008 Express. 'N' can grow to any number, but today is approximately 12.
The subscribers are connected via wireless network. Wired network is not an option in this environment.

We have found that after the 12th subscriber was added, that SQL Server Replication ground to a halt, and affected all subscribers.

It has been suggested that the size of the distribution database is the culprit here. At last report, the database had topped 150GB in size (MDF and LDF combined). The merge-history table contained over 1 million rows. I do not know what the partition's storage capacity is, but am fairly certain we aren't at the limit yet.

Can the size of this database be causing our problems? If so, what can we do about it (running the cleanup wizard did not seem to help)? If not, what diagnostics should we be doing to find the real cause?

Please note that I am a Software Developer, not a DBA, so you may have to dumb-down your answers a bit.


texts: The logical order of execution plan of the SQL query with more than one join

texts:
I know the logical order of execution of the SQL query which is:

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

What will happen if there are more than one join in the query for instance if we have a query like this:

SELECT *
FROM user_branch T1
INNER JOIN dimcustomer2 T2
   ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
   ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM

some example data:

customer_guarantee:    CUSTOMER_NUM      BRANCH_CODE
                      -------------------------------
                           A                X
                           B                X
                           C                Y
                           D                Z

 user_branch:           USER_ID          BRANCH_CODE
                      --------------------------------
                           U1               Y

 dimcustomer2:         CUSTOMER_NUM      BRANCH_CODE
                      --------------------------------
                           A                Y
                           B                Y
                           C                Y
                           D                Z

How will this execute? Which join will be executed first? And what if there are
different kind of joins in the query? What would be the order of executing joins in that case? Thanks in advance.


texts: Best way to synchronize several databases in different computers

texts:
I must to do a POS program for college in Java, the problem is that I don't know how to link the different computers to a master computer that handles the changes of the database and sends them to the others PCs .

I've thought the best way is that every computer has their own copy of the database and the changes are made directly on this database and later sends them to the master PC, which return the changes made by others computers.
Too, I could do it using only one database in the master, but I don't know if this are going to respond well when all the computers are working at the same time.

Do you have any alternative? or It'll work fine with whatever of the ways I've thought?


texts: Why does a count obtain an ExclusiveLock in Postgresql?

texts:
And what does this mean for other CRUD operations going against that table?

I have this query (actually I'm trying to write it 9 ways from Sunday to get it to return in less than an hour):

SELECT COUNT(*)
FROM (SELECT DISTINCT listing_id
      FROM apps where subscription_id = 1298) AS temp

It churns and churns. When I look at a process list for the server, I find eight records. One for the table and six for the indexes. And apparently one for the statement itself.

It says Locked Object and the object name, and AccessShareLock next to the table and indexes. Then there is something that says LockedTransaction and next to it it says ExclusiveLock.

What does this ExclusiveLock mean, and will it keep other CRUD operations from running against that table?


texts:
With the time data type (I'm using Microsoft SQL Server 2008), is there any way to take only the minute part out of it? I tried to pass the time to datepart and datediff function but both refused to work.

Example: I want to get 15 from 04:15


texts: MySQL: Incorrect binary log format - OFF.000

texts:
I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file.

mysql> show binary logs;
+------------+------------+
| Log_name   | File_size  |
+------------+------------+
| OFF.000084 | 1073744171 |
| OFF.000085 | 1073741909 |
| OFF.000086 | 1073742904 |
| OFF.000087 | 1073745627 |
| OFF.000088 | 1073742910 |
| OFF.000089 | 1073742081 |
| OFF.000090 | 1075694984 |

mysql> show variables like "%log_bin%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

bash-4.1# grep log-bin /etc/mysql/conf.d/mycustom.cnf
log-bin = /var/log/mysql/acct-db1-bin.log

Haven't restarted or done anything on MySQL service, but wondering how the format of binary logs got changed.

I have a setup a new slave to this master, and don't have any issue with the replication. But I am thinking whether it will cause any issue in future.


texts: SQLcmd error 'Failed to initialize sqlcmd library with error number -2147467259'

texts:
I'm trying to update some maintenance plans to make use of Ola Hallengren's IndexOptimize,
but also mail the result of the operation using msdb.dbo.sp_send_dbmail.
Old T-SQL:
declare @indexQuery nvarchar(1000);

set @indexQuery = 'USE [MyDB];
    SELECT i.index_id, i.name, s.avg_fragmentation_in_percent
    FROM sys.dm_db_index_physical_stats (
      DB_ID(),
      OBJECT_ID(N''MyTable''),
      DEFAULT, DEFAULT, DEFAULT) s, sys.indexes i
    WHERE s.object_id = i.object_id
      AND s.index_id = i.index_id;';

exec msdb.dbo.sp_send_dbmail
    @profile_name = 'Local Mail Profile'
   ,@recipients = 'someone@somewhere.co.uk'
   ,@subject = 'Re-index Started'
   ,@query = @indexQuery
   ,@attach_query_result_as_file = 1
   ,@query_attachment_filename = 'Reindex BEFORE.txt'

...and this works perfectly fine.
But if I modify @indexQuery thusly:
set @indexQuery = 'IndexOptimize ''MyDB''';

...to make use of the IndexOptimize procedure, the result is:

Failed to initialize sqlcmd library with error number -2147467259.

I can't seem to find anything related to the error number anywhere to work out what the problem is.
Can anyone please shed any light on it for me?


texts: Duplicate rows with a few changes in a table with a composite primary key, but only if target does not exist

texts:
Consider the following table:

CREATE TABLE [xxx].[MyTable](
    [period] [date] NOT NULL,
    [genusId] [int] NOT NULL,
    [subjectId] [int] NOT NULL,
    [waitingStageId] [tinyint] NOT NULL,
    [value] [tinyint] NULL,
    [savedOn] [datetime2](2) NULL,
    [savedBy] [nvarchar](50) NULL,
 CONSTRAINT [PK_ExpectedActivity] PRIMARY KEY CLUSTERED
(
    [period] ASC,
    [genusId] ASC,
    [subjectId] ASC,
    [waitingStageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 92, 1, 1, CAST(N'2017-05-29T13:45:20.6300000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 96, 1, 1, CAST(N'2017-05-29T13:45:21.2400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 128, 0, 2, CAST(N'2017-05-29T13:45:21.6500000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-05-01' AS Date), 71, 128, 1, 3, CAST(N'2017-05-29T13:45:21.4400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 35, 0, 1, CAST(N'2017-05-26T20:05:40.2800000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 94, 0, 3, CAST(N'2017-05-26T20:05:40.6700000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 73, 94, 1, 1, CAST(N'2017-05-26T20:05:40.4800000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 23, 0, 6, CAST(N'2017-05-26T20:03:26.5400000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 47, 0, 6, CAST(N'2017-05-26T20:03:26.7700000' AS DateTime2), NULL)
GO
INSERT [xxx].[MyTable] ([period], [genusId], [subjectId], [waitingStageId], [value], [savedOn], [savedBy]) VALUES (CAST(N'2017-06-01' AS Date), 74, 67, 0, 3, CAST(N'2017-05-26T20:03:27.1800000' AS DateTime2), NULL)
GO

I am trying to create a stored procedure that will allow us to duplicate values from the previous month into the current month -- BUT ONLY if there isn't a value for the current month already.

DROP TABLE IF EXISTS #PreviousMonth
DECLARE @schemaName AS varchar(5) = 'xxx' -- To be used later as a parameter in stored procedure
DECLARE @lastMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) AS DATE) --First day of previous month
DECLARE @currentMonth AS DATE = CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) --First day of current month

-- Add rows for existing month into a temporary table
SELECT @currentMonth AS period, genusId, subjectId, waitingStageId, value, GETDATE() AS savedOn, '<Automated by Stored Procedure>' AS savedBy INTO #PreviousMonth FROM [xxx].[ExpectedActivity]
  WHERE period in(@lastMonth)

SELECT * INTO xxx.MyTable FROM #PreviousMonth
-- Here is where I got stuck. I tried WHERE caluses and NOT EXISTS but my skills are limited.

This is as far as I managed to get unfortunately. My skill is limited where it gets to do an INSERT with a condition with the NOT EXIST condition.

How can I make sure that I only insert rows in the xxx.MyTable where rows with the same primary key (period AND genusId AND subjectId AND waitingStageId) does not exist already?


texts: How do I merge three update query into one single update query

texts:
I am trying to update a promotion table with a single update statement. Although the three update statements below can do it, I want to merge the three statements into one. How can I do that?
UPDATE promos SET next_promotion = (last_promotion + 4) WHERE grade_level IN (14,15,16,17,18)
UPDATE promos SET next_promotion = (last_promotion + 3) WHERE grade_level IN (7,8,9,10,11,12,13)
UPDATE promos SET next_promotion = (last_promotion + 2) WHERE grade_level IN (1,2,3,4,5,6)


texts: In postgresql.conf, does the order of the listed parameters matter?

texts:
I'm relatively new to the DBA field and I have read the official documentation/guides on this topic, but I apologize if my question seems weird.
I have deployed PostgreSQL 12 on my AWS instance and tried to install the pgaudit extension.
In the postgresql.conf configuration file you need to add shared_preload_libraries = 'pgaudit' and restart the PostgreSQL service to apply the change.
I added shared_preload_libraries = 'pgaudit' in the very end of the config file and restarted pg_ctl, which caused the service to crash. Postgresql started operating normally once I commented out the added line.
Later I found the shared_preload_libraries parameter in that config file, it was already there, located a few blocks above, but also commented out. Once I uncommented it and set its value to 'pgaudit', my postgresql service started working normally and I managed to create an extension.
My question is: does it really matter in what order you list the parameters? Why didn't it work when I added the line in the end of the file, but worked, when the line was somewhere in the middle?


texts: how to write pl/sql named programme for this

texts:
I have a table M_PARAMETER which has a column MMRUN.

When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification.

emp,dept,salgrade table do not modify users when MMRUN='Y'


texts:
I want to edit some of InnoDB variables ,I go to the .my.cnf which is located in home/.my.cnf I found the file just empty.

And when I check SHOW VARIABLES in MySQL it gives me all the variables and their values.

Why the file is empty and how can I edit the needed variables ? and can that affect my database or damage it in anyway?

NEEDED Variable:

innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 2G
innodb_log_file_size = 1G
innodb_log_buffer_size = 8M


texts: How to limit sql server table to 100,000 rows max

texts:
I know that this is impossible but still need a solution.
I need to limit the number of rows in a table to 100,000 - having rows moderated by a datetime2 column. Newest rows come in, the old ones disappear.
This way I will not have to write a batch task that periodically deletes the unneeded rows.
Also the solution needs to be deployed into a production environment where there are 1,000,000 rows already and it will have to delete the existing ones.


texts:
Using NVl in a query didn't considering indexes . But after creating below index it using newly created index.Please enlighten me.

CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1));

CREATE TABLE TEST_TABLE (
ID NUMBER,
NAME VARCHAR2(20)
) ;

INSERT INTO TEST_TABLE VALUES(1,'A');
INSERT INTO TEST_TABLE VALUES(2,'A');
INSERT INTO TEST_TABLE VALUES(3,'A');
INSERT INTO TEST_TABLE VALUES(4,'A');
INSERT INTO TEST_TABLE VALUES(5,'A');
INSERT INTO TEST_TABLE VALUES(6,'A');
INSERT INTO TEST_TABLE VALUES(7,'A');
INSERT INTO TEST_TABLE VALUES(8,'A');
INSERT INTO TEST_TABLE VALUES(9,'A');
INSERT INTO TEST_TABLE VALUES(10,'A');

SELECT * FROM TEST_TABLE
WHERE NVL(ID,1) = 1;

CREATE INDEX IDX_1 ON TEST_TABLE (ID);

CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1));

Thanks,


texts: If MAX(id) != COUNT(id), does that mean rows have been deleted?

texts:
The question pretty much says it all. We're talking MySQL here, version 5.1.

What I'm wondering is whether this would strictly means rows have been deleted, or if there are other possible scenarios that could cause this: weird locking or race conditions, etc. that force the table's ID counter to increment multiple times for a single insertion.


texts: Does SQL Server ignore STATISTICS for varchar columns as this article suggests?

texts:
(I don't believe this question is a duplicate of this question from 8 years ago, as I'm not asking about the advantages of oversized columns, I'm asking about the behaviour demonstrated in the linked article below.)
This recent (2017) article from SQLPerformance.com demonstrates how varying the maximum length n for a varchar(n) column affects query plan row-size estimates and sorting-buffer size estimates that can lead to subpar performance and memory allocation warnings.
In it, the author claims (emphasis mine):

From here we see that, the bigger the column definition, the higher the estimated row and data size. In this simple query, the I/O cost (0.0512731) is the same across all of the queries, regardless of definition, because the clustered index scan has to read all of the data anyway.

But there are other scenarios where this estimated row and total data size will have an impact: operations that require additional resources, such as sorts.

When I read that claim (in bold) I was surprised because I thought that SQL Server would get fairly accurate row-size estimates from its sampled STATISTICS objects maintained on those same tables. Especially given the SELECT AVG(LEN(email)) query in the article shows that no column has a value exceeding 77 characters.
The article also explicitly performs an ALTER INDEX ALL ON dbo.Table REBUILD - which this DB.SE posting says will will also automatically update STATISTICS.
(though I'm surprised that the word "statistics" doesn't appear anywhere in the SQLPerformance article at all - so maybe in the author's case the statistics weren't updated at all due to some machine-configuration and they didn't notice?)
Does SQL Server only use the varchar column length limit for row-size estimates? If not, then why does the SQLPerformance article describe the same?


texts:
I am trying to delete multiple records from view
The view has Instead of Trigger defined ..
The view has records consolidated from multiple tables.

 [db2inst1@testServer001 ~]$ db2 "delete from view_XXYY where alarm_mark_id in (52609,52610)"

I get  the following warning

 SQL0437W  Performance of this complex query might be sub-optimal.  Reason
 code: "3".  SQLSTATE=01602

 [db2inst1@testServer001 ~]$ db2 ? SQL0437W

  SQL0437W  Performance of this complex query might be sub-optimal. Reason
  code: "<reason-code>".

Explanation:

  Query optimization is one of the factors that affect application
  performance. The SQL and XQuery compiler performs several steps to
  produce an access plan that can be executed.

how do do it in an Optimal way ?


texts:
I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery model is SIMPLE and the .ldf is at 6Gb.

For whatever reason, the .mdf is 7.5Tb. Now, there are only maybe 2-3 additional columns in ANALYSIS tables and not many NVARCHAR(MAX) columns which, from what I (may have mistakenly understood - please correct me if I'm wrong) may be causing additional space allocation. That's after shrinking the database just now - it was at ~9Tb prior to that. Any thoughts?

And, please, let me know if you have additional questions - I'm very new to database administration and optimization efforts (I usually don't do this side of the job :)).

Many thanks!

Andrija


texts: Should I create a extra relation-table when I have two columns in one table referencing to one column in another table

texts:
My problem is best explained by example:

Suppose I have a database about chess games and players.

Which one of the following structures should I choose

Option 1)

player (player_id,name... etc)
game (game_id,date... etc)
game_player (game_id,player_id,white)

where game_player.white is True/False depending if the player was playing black or white

Option 2)

game (game_id,black_player,white_player,date... etc)
player (player_id,name... etc)

I think Option 2) is the best because I know that every game has exactly 2 players and one of them is black and one is white.  However, somehow the queries in this setup don't feel so natural to me. Even in simple queries I have to join to player table twice and need conditions like game.black_player = player.player_id

Option 1) feels good because it's immediately clear where each column is  referencing. However, otherwise I don't like option 1) so much.

So which one is the best practice and why? Or is there even better Option 3)?
If I choose option 2) how should I name the black_player and white_player columns to be clear that they are referencing to the player table.

Hopefully my question makes sense.

I realize that this is a very basic question but I can't stop thinking about this and didn't find anything by searching.


texts:
Until now, I have a single MariaDB 10.1 node running as Galera Cluster. So, now I want to join the next one.

systemctl start mysql

initiates the snapshot state transfer (which takes about 10 hours for 200 GBytes).

The config of donor and joiner are quite similar:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
skip-external-locking
secure_auth = off
skip-name-resolve
bind-address        = 0.0.0.0
max_connections     = 100
connect_timeout     = 5
wait_timeout        = 600
max_allowed_packet  = 16M
thread_cache_size       = 128
sort_buffer_size    = 4M
bulk_insert_buffer_size = 16M
tmp_table_size      = 32M
max_heap_table_size = 32M
myisam_recover_options = BACKUP
key_buffer_size     = 128M
table_open_cache    = 400
myisam_sort_buffer_size = 512M
concurrent_insert   = 2
read_buffer_size    = 2M
read_rnd_buffer_size    = 1M
query_cache_limit       = 1G
query_cache_size        = 200M
log_warnings        = 2
slow_query_log=1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 3
log_slow_verbosity  = query_plan
server-id       = 103
log_bin         = /var/log/mysql/mariadb-bin
log_bin_index       = /var/log/mysql/mariadb-bin.index
expire_logs_days    = 3
max_binlog_size         = 100M
binlog_format       = ROW
default_storage_engine  = InnoDB
relay_log       = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
log_slave_updates
default_storage_engine  = InnoDB
innodb_buffer_pool_size = 110G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_file_format  = Barracuda
innodb_open_files   = 400
innodb_io_capacity  = 400
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="dbcluster1"
wsrep_cluster_address="gcomm://"
wsrep_node_name="db1"
wsrep_node_address="db1"
wsrep_replicate_myisam=on
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:pa55w0rt
[sst]
streamfmt=tar
transferfmt=nc
rlimit=3000k
bind-address=0.0.0.0
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
[mysql]
[isamchk]
key_buffer      = 16M

On the second node, db1 is replaced by db2, it has another server-idand

wsrep_cluster_address="gcomm://db1,db2"

After doing the SST, the server shuts down cleanly, and the position is stored in /var/lib/mysql/xtrabackup_binlog_pos_innodb. However, when I start the server again,

systemctl start mysql

it does not perform an incremental state transfer, but again flushes the entire data directory /var/lib/mysql and starts the SST again.

What do I have to do to join the cluster correctly (with incremental state transfer)?


texts: MySQL percona 5.6 to MySQL 5.5 replication

texts:
Scenario 1 I am having a MySQL DB on 5.5 which is compliance, So need to upgrade it to MySQL 5.6. while doing this we see that 5.6 is running slow, were as it runs perfectly on 5.5 backup.

Scenario 2 We have installed 5.6 percona MySQL on one system & just want to know if we can replicate on MySQL 5.5 using MySQL replication.

Please advice what issue will be faced in this 2 scenario. any suggestion is acceptable.


texts: Proper way to map IDs: By adding additonal columns for each external column or by inserting new rows

texts:
I am working on a new database structure in which we get external IDS from a few different sources and map them against our internal ID in one table.

Method 1

In the first version the table structure is shown below by the following "create database query":

create table external_id_mapping(
id  int not null auto_increment,
internal_id int,
external_id varchar(150),
external_source_name varchar(60),
varchar(70),
primary key(id))

In which case we would be able to look our own internal_ID dependant on the external through these types of queries:

select internal_id
from external_id_mapping
where external_id = 4214
and external_source_name = 'source_name_x'

Each new source would have a new row with an external_id and a source_name. External_id cannot be integer as it needs to cover every possible variation of external_ids we are using, some of which are integers others which are strings.

Method 2

In the other method the database structure would look like this:

create table external_id_mapping(
internal_id int,
external_id_x int,
external_id_z int,
external_id_y varchar(150),
primary key(internal_id))

Thus query would look like this:

select internal_id
from external_id_mapping
where external_id_x = 4214

Each internal_id represents its own row and gets matched for different columns against different external_ids. If we in the future wants to use additional external sources we would create new columns.

In terms of inserting new data in the table, I highly prefer the latter method sicne I can use "replace into" and overwrite the existing primary key ID.

Whereas in the first method, the ID is auto-incremented and it needs to be looked up before we know whether it needs to be inserted or updated.

However, which method is generally considered best practice?


texts: Database Mirroring error, but everything looks OK

texts:
I'm setting up a Mirroring study session in separated servers.

Everything looks ok, the firewall in my machine is disabled and in the another server it has an inbound\outbound rule to accept port 5022.

I could check that both endpoints are created and STARTED in both servers
with SELECT * FROM sys.database_mirroring_endpoints.

I tried with both configurations, by using services account, and leaving them blank.

I checked with port query that the server is listening on this ports:

What more can I check?

The reason I'm asking this question is because I read every other question about mirroring here, but none of them could help me.

In a post, I read that someone fixed the error with:

  Go to services.msc and check the sql server is running under which
  account. Make sure that sql server and sql server agent services
  should run with same credentials.

But I use different credentials for AGENT and SQL SERVER.

Is this a problem? I've never seen this before.

EDIT2:

I could fix this error ( it still shows me this error, but if I run the mirror again, it shows me error 1431.)

Using ALTER DATABASE [Database_Name] SET PARTNER OFF didn't solve the problem. it just restart the task, and errors and errors again.


texts: Is there any scenario where the existence of one or more Indexes in a table can prejudice it?

texts:
The title sums it up.

I've learned and always heard that indexes in tables improve CRUD operations. A developer that I met last weekend told me that he does not like Indexes because they are bad - yes, "bad" does not clarify anything but we did not had time to discuss it further (we were at a party).

Anyway, maybe because my lack of experience, I do not know of a scenario where Indexes can cause troubles during CRUD operations, but maybe there are a few out there. I'm asking this question to know if there are any...


texts: Performing wildcard search using the json_contains function in mysql 5.7 and above

texts:
I have some nested json data that i get using the json_object() function. Now I need to to check whether a certain value exists for a particular key which I am using as follows in my query:-

select json_object(..) from tblxyz
  where json_contains(columnname, '{"MyName" : "FirstName LastName"}')

The problem with this approach is that I require the entire string "FirstName LastName" to find this row in the actual result.
Now I need to perform a wild card search. That is:-

select json_object(..) from tblxyz
  where json_contains(columnname, '{"MyName" : "FirstName"}')

should also return this entry to me in the final result. Can someone please suggest any workaround?


texts:
Intuitively, adding the option ON UPDATE CASCADE to a foreign key constraint will have the effect of updating all referencing columns with the updated value of the key. Would that really work?

Example lifted from manual:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    ## HERE'S THE LINE THAT I MODIFIED FROM THE MANUAL, IT USED TO SAY 'ON DELETE CASCADE'
    order_id integer REFERENCES orders ON UPDATE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

I am using Postgres 9.1.


texts: Having a PostgreSQL database exposed to the internet

texts:
I've seen on DBA and other sites that exposing a database to the Internet is a particularly bad idea, but I noticed that PostgreSQL has SSL-certificate-only authentication. Does this make it any safer to expose a database to the internet (cert-only auth from non-localhost connections) or would it still be better to establish a VPN, or tunnel through SSH etc?

If the latter is safer, what exactly makes it safer? I feel like I am overlooking something glaringly obvious in this regard.

I've set up a handful of databases before but never actually needed to set one up that requires some sort of remote administration (“remote” in this case meaning over the Internet or WAN rather than a LAN).


texts: Why getting Error accessing Cassandra with DataStax ODBC Driver?

texts:
We are using Cassandra 2.2.11, and trying to configure DataStax ODBC driver. We are getting the following error message:

  SQLSTATE: HY000[DataStax][CassandraODBC] (10) Error while executing a query in Cassandra: [33554688] : Received error response 'Username and/ or password are incorrect' (0x02000100)


texts: in sqlite how to check the table is empty or not?

texts:
I need the check the table is empty or not
 employee table contains columns are id name

id  name
1   john
2   hunter
3   smith
4   will


texts: Getting Data Back after Wrong Data Restore in SQL Server

texts:
I would like to inquire if there is an possible way to return lost data that stored in a Sql server Database after making a wrong database restore?

I know it is extremely hard to get a positive solution but any kind of help will be appreciated.