texts: Normalization of database according to the given data

texts:
I'm creating new database based on existing database, performing some normalization. Here is the table containing multiple information, it's basically for booking trips etc

I've divided all the info into some tables, I need some expert advice whether is it correct or I need to make some changes?


texts:
As part of our regular development cycle we need to take a copy of production into development databases.

To do this, we take a copy of production backups and move them to the Development Server. Then we use the RESTORE WITH MOVE function to define the where the data and logs should be stored.

This restore is very time consuming, and generally takes 2 to 3 times as long as a standard restore. This is an expensive overhead for us, as it often has to be done several times during the day.

I was wondering whether there is a more efficient way to perform this task.

I have tried writing the backup to several files to improve performance, and whilst this speeds up the backup, the RESTORE WITH MOVE seems unaffected.

Any help is greatly appreciated.


texts: Create Materialized View from foreign table

texts:
I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server.

What do I have to add to make the query access the foreign database and the table there?

CREATE MATERIALIZED VIEW mv_table_1 AS
  SELECT *
  FROM public.mv_table_1 --The schema & table from the different DB
WITH DATA;

I tried using the fully qualified table name (db name before the schema name) but this results in an error:

  References to other dbs are not implemented


texts: Executing TSQL script generated from SSMS in SQLCMD. Failing on quotes

texts:
I am attempting to deploy a database to a machine by executing a script generated by the "Generate Scripts" tool in SQL Server Management Studio.

Here is the command I've issued:

sqlcmd -S LOCALHOST\sqlexpress -I -U user -P ******** -i
    C:\Rollouts\NI-9-25-2012_10-42-AM\Rollout.sql > rolloutlog.txt

I'm receiving errors like this:

  Sqlcmd: Error: Syntax error at line 39488 near command ''' in file
  'C:\Rollouts\ NI-9-25-2012_10-42-AM\Rollout.sql'.

When I view this line with a text editor all signs point to an issue with quotation marks.

The line in question looks like this:

  $(''.cat-menu-new'').slideDown(''slow'');

Is there a switch that I should be executing or generating the script with to support quotes within the script?


texts: Get Table Name from Transaction Log query

texts:
I can't figure out why this join isn't working to return the table name when querying the transaction log file.

SELECT [Transaction ID],
    [Current LSN],
    PartitionId,
    sp.object_id,
    so.[name],
    [Operation],
    [Context],
    [AllocUnitName],
    [Begin Time],
    [End Time],
    [Transaction SID],
    [Num Elements] ,
    [RowLog Contents 0],
    [RowLog Contents 1],
    [RowLog Contents 2],
    [RowLog Contents 3]
 FROM fn_dump_dblog(NULL,NULL,'DISK',1
    ,'D:\Users\Chad\SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\OTIS_LogBackup_2016-02-02_13-36-21.bak'
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL) as logF
    LEFT OUTER JOIN sys.partitions sp ON sp.partition_id = logF.PartitionId
    LEFT OUTER JOIN sys.objects so on so.object_id = sp.object_id
WHERE
    [Transaction ID] = '0000:00d15747';

And here are the results. You can see the partition Id is populated:

And when I execute this query to get the table name, it works using that partition id found in the prior results:

SELECT so.*
FROM sys.objects so
INNER JOIN sys.partitions sp on so.object_id = sp.object_id
WHERE sp.partition_id = 72057594059489280

So why can't it find the table name when I join in the original statement above?


texts: Is there a way to execute sql statement in sql server based on sql server version?

texts:
I would like to create computed index on a table if sql server is 2008 or newer and a simple index if sql server is 2005 or older:

-- check for sql server version
if (select cast(left(cast(serverproperty('productversion') as varchar), 4) as decimal(5, 3))) >= 10
        CREATE unique nonclustered index ix1_table
            ON table (column1, column2)
            WHERE column1 is not null and column2 is not null
        ELSE
            CREATE nonclustered index ix1_table
                ON table (column1, column2)

problem is that the whole statement is evaluated and on sql server 2005 this throws an error

  Incorrect syntax near the keyword 'WHERE'.

Is it possible to somehow create different index based on sql server version?

Thanks

Dragan


texts: PostgreSQL covering index on large table as alternative to CLUSTER command

texts:
I'm looking for an alternative to executing the CLUSTER command on large tables to keep data efficiently organized on disk, since it requires an ACCESS EXCLUSIVE lock on tables being clustered, making them unavailable for a considerable amount of time.

After reading about covering indexes I got the idea of creating a btree multi-column index specifying all of my table columns, and not just those relevant for the query condition.

The benefits, I suppose, would be twofold:

The query plan would be optimized due to the condition relevant columns being specified first on the index, according to the query's expected data fetch pattern.
All remaining columns would also be included in the index, meaning this index would be a copy of the table and already organized efficiently on disk due to the btree structure.

I believe queries over this index would be very efficient and since it is covering the whole table wouldn't need to access table data scattered on disk.

Would like to ask if this a reasonable solution, and what are the potential downsides to this approach? (besides the additional storage space required for the covering index and expected - small? - decrease in insert/update/delete performance)


texts: How to see special characters in MySQL Workbench query results?

texts:
In my Database, I made a small mistake by inserting a blank (space character) after a value like this:
INSERT INTO MYTABLE(country_name, code, value) values ('France', 'FR ', 345);

When making a simple query in mySQLWorkbench:
SELECT * from MYTABLE;

The table shows
-------------------------------
| country_name | code | value |
-------------------------------
| France       | FR   |   345 |
-------------------------------
etc...

The space character is not visible in the output.
Because we were able to debug our application we found the extra space character but if you don't know what to look for, it is very difficult to catch that kind of error.
Is there any way to let mySQLWorkbench to display those special characters?
As a workaround I used this query:
SELECT t.id, CONCAT("'",  t.country_name, "'") AS Country,
    CONCAT("'",  t.code, "'") AS CountryCode,
    t.value
FROM MYTABLE t

But you have to know which column is a VARCHAR and which are not (e.g. value is a number).
The version I use is: MySQLWorkbench 6.0.8.11354 build 833


texts: When shrinking log files what difference does TRUNCATEONLY make?

texts:
When using DBCC SHRINKFILE (Transact-SQL) to shrink log files does adding TRUNCATEONLY do anything different? if so what?

Scenario:
I have used DBCC LOGINFO and discovered I have too many VLFs, I only have a single .ldf I am not making any changes to the data files. My database is in FULL recovery and I can run t-log backups on demand. I plan to use Fix_VLFs.sql from the tiger team to rebuild my .ldf

When you look at the solutions generated by Fix_VLFs.sql it lists both options for shrinking the number VLFs

USE [MyDB];
DBCC SHRINKFILE (N'MyDB_log', 1, TRUNCATEONLY);

and

DBCC SHRINKFILE (N'MyDB_log', 1);

The solutions are listed for use when the database is in FULL or SIMPLE recovery, respectably.  But I can't see how FULL or SIMPLE would make any difference on if you should use TRUNCATEONLY or not.  Nor Can I see how TRUNCATEONLY would make any difference for any log file shrink.

VLFs are wrap around files (Transaction Log Physical Architecture) as I understand it the original equipment VLFs('0" in CreateLSN) will not be deleted with SHRINKFILE. Additionally only those VLFs later in the LSN chain than the last one with a DBCC LOGINFO 'status' of '2' are available to be deleted.

In other words, the only option is to delete VLFs from the end of the wrap around, you need to work the current VLF (last status 2) around to the front (original equipment) of wrapped file, run the t-log backup and then you can use SHRINKFILE to delete everything except the original equipment VLFs.  This would seem to be the only option without regard for the use of TRUNCATEONLY.


texts: SQL Server 2008 R2 Restore a back up to a new database

texts:
I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records.

What is the best way to do this?

Basically I want to get some records from the old backup update old values to the current database without doing a restore, what are my options?


texts: How to read and increment a number in text file using TSQL

texts:
I have a text file C:\abc.txt with a number 5 in the first line. I need to increment it into 6.

Please help me with the T_SQL syntax.

I have tried the following query to read the value but not able to update it.

DROP TABLE #UserLocation

CREATE TABLE #UserLocation(UserId numeric(3,0))

BULK INSERT #UserLocation
    FROM 'C:\ABC\ABC.txt'
    -- Path to the file.If this file is on server,be sure this file exists on the server.
    WITH
    (
        ROWTERMINATOR ='\n',
        --  New Line Feed (\n) automatically adds Carrige Return (\r)
        FIELDTERMINATOR = '\t',
        --delimiter
        FIRSTROW = 1
    )

PRINT 'File data copied to Temp table'

SELECT * FROM #UserLocation


texts: Can I upgrade SSIS 2008 packages directly to SSIS 2017 SSISDB?

texts:
I'm planning a mifgration of a SQL Server 2008 instance running the DB engine and SSIS.

The SSIS is deployed to the file system.

Am I able to copy those SSIS packages to a directory on my new SQL Server 2017 machine, open them in SSDT 2019 and then deploy to SSISDB?

Or is there some other intermediate step or process I should follow?


texts: How to check RDS MySQL database synchronization status

texts:
We were creating a read replica for an RDS MySQL (5.7.28) instance, and before the read replica became available one of our developer accidentally connected to the read replica and was able to write to the read replica (though the read replica should have prevented any writing operation). Later on he discovered the master instance and the read replica were out of sync since the read replica had the record while the master instance didn't.

We are wondering if there is a way to check the synchronization status between the master and read replica. We have explored 3rd party options such as mysqlrplsync from mysql-utilities and pt-table-checksum from Percona toolkits but they require the database user to have SUPER privilege, which RDS does not grant. Is there an RDS native tool to perform such a check?

Thank you.


texts: I have multiple ibdata files : What should I do with them?

texts:
I have 3 different ibdata files on disk. What is the difference between them? Can I remove some of them? The goal is to migrate the files from C: to E:...

I'm on MySQL 5.0.x...

Directory of C:\MySQL Datafiles

11/19/2012  12:05 PM       287,309,824 ibdata1
               1 File(s)    287,309,824 bytes

Directory of C:\Program Files\MySQL\Enterprise\Monitor\mysql\data

11/19/2012  12:05 PM     6,561,988,608 ibdata1
               1 File(s)  6,561,988,608 bytes

Directory of C:\Program Files\MySQL\MySQL Server 5.0\data

02/08/2008  09:00 AM        10,485,760 ibdata1
               1 File(s)     10,485,760 bytes


texts: Restricting users to execute queries that bloat up tempdb

texts:
we have many a times faced a scenario where bad queries using tempdb just bloats up this system database filling 700 GB of tempdb in like couple of hours.

I know there is a scope to tune them up. But as of now there are quite a few no of users and handling the queries involves lot of time for now.

So can we use a process like RG to restrict these users to bloat tempdb or any other way. Just looking if there is any such alternative available?


texts: Is it better to use an ID (int) or a Name (varchar) as PK for a table?

texts: Let's suppose I have an ORDERS_LINE table and a PRODUCT table and the ORDERS_LINE table must have the information of which product was ordered. I prefer to use a Primary Key based on the Name since it will be easier to read in the other table and it reduces the chance to have duplicated items. But this is my opinion, I don't really know if there is a standard or if one approach is more efficient than the other.


texts:
I'm trying to write a query for a report to show how much free space is in our data files so we can plan storage for the future. I'm pulling data out of sys.database_files, and I can narrow it down to just type 0 (Rows) data - we're not worried about log growth at this point.

The problem I'm having is that a number of databases have a full text catalog. The Full Text Catalog file is also displaying as type 0. We want to exclude these from the report as well. Is there anyway we can tell from sys.database_files (or join back to that) so we can just see the data files?

I notice any newly created Full Text Catalog files in SQL Server 2016 will have type = 4, but that doesn't really help me on 2014 (or upgraded databases in 2016).


texts: Optimizing a query that retrieves five rows for each letter of the alphabet

texts:
A site that I'm currently working on will execute a query 26 times but only changes a letter within the where clause. I am looking to make this more efficient by running only one query.

Here is an example query:

SELECT table1.id, table1.name, table1.url
FROM table1
       LEFT JOIN table2 ON table1.aid = table2.aid
WHERE LOWER(table1.name) LIKE 'a%'
  AND table1.rank > 0
  AND table1.view = 1
ORDER BY table2.important DESC, table1.rank ASC
LIMIT 5

I have managed to create a temporary table in which would return each letter of the alphabet however I cannot figure out how to fetch the letter and push it into the where clause.

I will end up creating a table for the alphabet but this is how it currently runs (for just now):

SELECT *
FROM (SELECT 'a' AS letter
    UNION SELECT 'b'
    UNION SELECT 'c'
    ...
    UNION SELECT 'y'
    UNION SELECT 'z') AS alphabet

Clarification: I didn't really explain what I am looking to do. I am looking to pull 5 rows for each letter.

Any and all help would be greatly appreciated.


texts: SQL Server 2017 not occupying free memory

texts:
Recently one of our read servers (SQL Server 2017 CU2 - yes, way out of date) had its Page Life Expectancy drop from an avg of 15K seconds to 250 seconds.

It is one of 2 load balanced read servers and its twin did not experience this drop.

What I'm finding interesting is that the perfmon counter "Free Memory (KB)" is sitting around 303,100,000, while its twin is occupying far more free memory: its counter is at 30,125,800 (an order of magnitude smaller).

Both servers have target server memory of 409,000,000.

My assumption is that the PLE plummeted because SQL Server is not occupying the free space and therefore not caching like it did. How do I track down what is going on?


texts:
After i created the tablespace named "rman" and created the user named"rman" and accredited to the user.

I connected to the recovery catalog database successfully.

But when i got into the next step.It mentioned ERROR.

Anyone could help me?
Thanks


texts:
I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as the clustered ssis. However if they are on different nodes there seems to be an aunthentication problem and it loses my credentials. For some reason it tries to use Anonymous Logon instead. Here is the full error:

The SQL server specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in Server 2005 Books Online.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)

I would appreciate anyones help on this problem.

Thanks,


texts: How to simulate bad backup file with 'ultraedit'

texts:
I want to simulate bad backup file with ultreadit, but I don't know how as I know nothing about ultreadit. Please help me with that.

I simply want to make the SSMS fail to read the pages of a bad file.


texts: How to detach a sql server 2008 database that is not in database list?

texts:
I installed sql server 2008 on windows 7 then i create a Database! after 2 days, i reinstall the windows and install the sql server! now i am trying to attach my database file, but i encounter to bellow error. i think that the files are like a attached file and i can't attach them. what is difference of an attached file and a not attached file? how can i to attach this file? Please Help Me.

Error Text:

  TITLE: Microsoft SQL Server Management Studio

  Attach database failed for Server 'AMIR-PC'. (Microsoft.SqlServer.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Unable to open the physical file "F:\Company.mdf". Operating system
  error 5: "5(Access is denied.)". (Microsoft SQL Server, Error: 5120)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=5120&LinkId=20476


texts: Weird planning time data dependency in Postgres

texts:
We got a fairly large table holding session data in Postgres. Recently, we found that the planning time for the most common query on this particular table increased over time, at one point to ~130ms which starved the whole instance. The planning time went back to normal after vacuuming and analyzing but is slowly creeping up again. What's particularly strange though is that the planning time seems to depend on the input data:

explain analyze select * from django_session where expire_date > now() limit 1;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=207) (actual time=0.008..0.009 rows=1 loops=1)
   ->  Seq Scan on django_session  (cost=0.00..6357552.08 rows=129398117 width=207) (actual time=0.008..0.008 rows=1 loops=1)
         Filter: (expire_date > now())
 Planning Time: 7.201 ms
 Execution Time: 0.022 ms

In contrast to:

explain analyze select * from django_session where expire_date > now() + interval '2 days' limit 1;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=207) (actual time=0.008..0.009 rows=1 loops=1)
   ->  Seq Scan on django_session  (cost=0.00..6681337.26 rows=128206390 width=207) (actual time=0.008..0.008 rows=1 loops=1)
         Filter: (expire_date > (now() + '2 days'::interval))
 Planning Time: 0.053 ms
 Execution Time: 0.020 ms

This cut-off of about 2 days between increased and normal planning time exists regardless of what type of comparison is made (i.e. <, >, = all have the same effect).

The table is cleaned up every day by deleting old sessions that are expired.

Given the range of expiry_date of up to a year in the future could it be that Postgres' heuristic assumes that an expiry_date in the near future is an unlikely input? Why does this only happen when querying for this particular column and none of the others in the same table?


texts:
as part of the process to add articles to my publication
I use the stored procedure sp_addarticle:

use [Returns]
exec sp_addarticle @publication = N'USReturns', @article = N'tblReturnsContainerTypeDescr', @source_owner = N'dbo', @source_object = N'tblReturnsContainerTypeDescr', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tblReturnsContainerTypeDescr', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotblReturnsContainerTypeDescr]', @del_cmd = N'CALL [sp_MSdel_dbotblReturnsContainerTypeDescr]', @upd_cmd = N'SCALL [sp_MSupd_dbotblReturnsContainerTypeDescr]'
GO

I have publications with over hundreds of articles.
I save the scripts, but I would like them to be properly formatted.
How can I format my scripts in SSMS?

I wanted to see my scripts in this way:

use [Returns]
exec sp_addarticle
 @publication = N'USReturns',
 @article = N'tblReturnsContainerTypeDescr',
 @source_owner = N'dbo',
 @source_object = N'tblReturnsContainerTypeDescr',
 @type = N'logbased',
 @description = N'',
 @creation_script = N'',
 @pre_creation_cmd = N'drop',
 @schema_option = 0x000000000803509F,
 @identityrangemanagementoption = N'none',
 @destination_table = N'tblReturnsContainerTypeDescr',
 @destination_owner = N'dbo',
 @status = 24,
 @vertical_partition = N'false',
 @ins_cmd = N'CALL [sp_MSins_dbotblReturnsContainerTypeDescr]',
 @del_cmd = N'CALL [sp_MSdel_dbotblReturnsContainerTypeDescr]',
 @upd_cmd = N'SCALL [sp_MSupd_dbotblReturnsContainerTypeDescr]'
GO

what is working for me at the moment,
is copying and pasting the script into word
then following this link:
Special characters you can use with Find and Replace in Word

I can put all the "@" in a new line and other similar tricks:

Is there any way of doing this level of formatting using SSMS?


texts: How we can design best optimized solution: Generate huge random numbers and check in database table whether this numbers are there or not

texts:
We have one table like:

Table Name:

random_number_series

Its columns are as below:

number      [numeric(10)]
name        [varchar(10)]
status      [tinyint(1)]
type        [tinyint(1)]
createdDate [datetime]

This table have around 250-300 million rows.

Now our requirement is that we will generate random numbers in big quantity i.e. 1 million. We want to check that this generated numbers are there in above table (with number column) or not.

How best we can do this?

Note:

-> Random numbers will be generated in application logic.

-> I want to know strategy / pseudocode how best we can design?


texts: In a database trigger on event create - How do I reference the created object?

texts:
Given the following trigger:

CREATE OR REPLACE TRIGGER
  TRG_CREATE_TABLE_INITRANS
  AFTER CREATE ON SCHEMA
BEGIN
  IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
  exec 'alter table '||<table_owner>||'.'||<table_name>||' initrans 10'
  END IF;
END;

How do I access the object owner and the object name which triggered the trigger?


texts:
I have two unique indexes on columns a and b. I need something like this:

insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'

So generally I want to make different update based on conflict target - syntax above is not supported (only one on conflict statement is supported). Is there any other way to do this?


texts:
I have created an application in .Net with Microsoft SQL Server for desktop with a local database and an online application with an online database.

Data entry will be done on both platforms in real-time.

How can I synchronize the desktop database with the online database (as primary) in real-time without any data loss?


texts: Migration from Sybase to a different platform

texts: How feasible it is move the Sybase database to a different more popular platform such SQL Server or any other. Which one is preferred? Is it feasible in the first place?


texts: Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

texts:
I'm using PostgreSQL 9.3.5.

Suppose I have the following table:

CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point);

I then insert a row, leaving pt NULL:

INSERT INTO point_test (name) VALUES ('me');

Then, I want to update pt using the array-like syntax:

UPDATE point_test SET pt[0] = 42, pt[1] = -42 WHERE name = 'me';

which appears to succeed (UPDATE 1) - EXPLAIN VERBOSE shows the following:

Update on pg_temp_65.point_test  (cost=0.00..11.75 rows=1 width=538)
  ->  Seq Scan on pg_temp_65.point_test  (cost=0.00..11.75 rows=1 width=538)
        Output: name, (pt[0] := 42::double precision)[1] := (-42)::double precision, ctid
        Filter: ((point_test.name)::text = 'me'::text)

However, pt is still NULL. If I use a slightly different syntax, it works in this case:

UPDATE point_test SET pt = '(42,-42)' WHERE name = 'me';

results in the point (42,-42) as expected.

Further, now that there is something in the field, I can update the point using the first syntax:

UPDATE point_test SET pt[0] = 84, pt[1] = -84 WHERE name = 'me';

results in the point (84,-84) as expected.

Why do the behaviors of the two syntaxes differ only when the pt column is NULL?


texts: Will reloading a table cause recompiles downstream?

texts:
We have a vendor that is requesting we truncate a data table, and reload it with the same data daily. The table is 90k rows and approx 10mb. The reason they want this done is so the ETL program picks up that the table has been updated. This table is used in a number of queries.

The question I have is will the changes made to this table cause a ripple effect of recompiles in query plans where this table is used. Or will SQL be smart enough to see that the table contents haven't changed, and avoid the recompiled?

Yes, we know how stupid this is.


texts:
Is there a standard way to detect errors when using mysql from cli in this way:

mysql --host=$db_host --user=$db_user --password=$db_password -v -v -v $database < file.sql

What I mean is I want to know what I can search for in the output. For example a warning message always has Warning. Does error always have Error in the output?


texts: Are there standard SQL functions with side effects?

texts:
Do the SQL standards define functions with side-effects?

For example, do they have functions for writing into files* or to update values in certain columns of a table when you do something like

SELECT myfunction(params...);

I've seen these sometimes, but I am just curious if the SQL standards do the same.

* This is not a question about PostgreSQL specifically. I'm only using the side-effects examples I see in PostgreSQL.


texts: How to set up an SQL Server authentication login for a database instead of using Windows Authentication

texts:
Not sure what I am doing wrong so I have included the parameters I used which I used the existing login and this article for reference:
http://itproguru.com/expert/2014/09/how-to-fix-login-failed-for-user-microsoft-sql-server-error-18456-step-by-step-add-sql-administrator-to-sql-management-studio/
Whatever I do I get the Error: 18456 message.


texts: Where is osmtrip_total function on Postgres OSM with postGIS

texts:
I have install osm2pgsql and uploaded pbf files to Postgres

osm2pgsql -s -C 1600 -H postgres-test.eu-central-1.rds.amazonaws.com -d gis -U root -W CN.pbf

I set up postGIS on rds using this description:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS

I am tryig to build out a new server built by previous dev that setup the database.  In code the previous dev runs this query:

"SELECT * FROM osmtrip_total(%s::json, %s, %s, %s, %s, %s)",
                           (json.dumps(segments), 30, 90, 0, 180, 1.08)

Looking at the database osmtrip_total is a function.  Refer to the image.

My question is where did those functions come from?  How do install?  Docs anywhere?


texts: What are the CLOUD_DATABASEPROPERTYEX and GET_CLOUD_PARTITION_MAX_SIZE functions?

texts:
Using SSMS 2014, intellisense shows that the following two built-in functions are available, and even has signature info for the parameters and datatypes:

CLOUD_DATABASEPROPERTYEX
GET_CLOUD_PARTITION_MAX_SIZE

Attempting to SELECT them while connected to SQL Server 2016 (RTM) gives the following error:

  Msg 195, Level 15, State 10, Line xxxxx
  'CLOUD_DATABASEPROPERTYEX' is not a recognized built-in function name.

While this is mostly expected (since I assume these would be for Azure SQL Database), what is not expected is that I cannot find any documentation for them anywhere. I have searched MSDN, here, and the Googlez and nothing, nada, zip, zilch, (and so on). If these are for internal use only, why show up via intellisense?


texts: mongo2.6 local file size on secondary is much larger than primary

texts:
I set up a new standalone mongo2.6 and copy one folder(database) from another machine to its data path. (cause I enabled directoryperdb)

Then I set up another machine to make them as a replica set.
After rs.initiate and data sync finished, I found the local file size on primary and secondary are:

primary: 232G
secondary: 316G

I found similar question here
https://stackoverflow.com/questions/14843229/mongodb-replica-set-disk-size-difference-in-primary-and-secondary-nodes

But as above answer, the primary should use more disk space than the secondary?

I need to migrate about 25T data from the old mongo 2.6 cluster.
If every database local file size take so more disk space in the secondary, then I need to take this as a spec consideration in the new bought machine :(


texts: Security setting for SQL Server services

texts:
I'm currently starting to upgrade out SQL Server estate to 2012, and historical people have created two domain accounts for the database services MSSSQLServer and one for the SQL Server agent to operate under.

However I've always been concerned that in most case these have been given sysadmin rights on the SQL Server and would like to change this.  However I'm unable to find anywhere were best practice is detailed on this subject, I really want to lower this account to its minimal security requirements for starting and stopping the database but will that be enough for it to function?

Hope someone can help :-)


texts: enforcing data integrity for sequential relations

texts:
These are my tables:

create table trips (
  trip_id serial primary key,
  trip_nm text
);

create table trip_segments (
  segment_id serial primary key,
  departure_ts timestamp with time zone, -- departure time
  arrival_ts   timestamp with time zone, -- arrival time
  trip_id integer references trips (trip_id)
);

An insert would look like:

insert into trips (trip_nm) values ('my trip');

-- first segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 12:00', '2013-01-30 20:00', trip_id);

-- second segment
insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 21:00', '2013-01-30 22:00', trip_id);

Given the above data: I want to ensure that a departure time in the second segment is after the arrival time of the first segment.

Which means the following insert should fail for the second segment:

insert into trip_segments (departure_ts,arrival_ts,trip_id) values
                          ('2013-01-30 19:55', '2013-01-31 22:00', trip_id);

There could be many segments for one trip. There are many trips.

So, how do I do that?

My first thought was to use a check constraint function for the trip_segments table, that looks like:

create function previous_ts(trip_id integer)
  returns timestamp with time zone as $$

  /* do some hackery here using the lag() function */

  return previous_arrival_ts;

$$ language 'sql';

Okay, so I haven't implemented that, but that would be the idea. However, I read (on stackoverflow, lost the answer) that using
a check constraint function is, and I'm para-phrasing, a really bad idea.

Ok, so there must be a better way, maybe using a trigger, I don't know. What's the best way to solve this problem?

I'm using postgresql 9.5


texts: How do I get postgres to log the stored procedures it is running?

texts:
I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log.

I've got it logging all the statements with:

log_statement=all

but all that is logging is the statements sent to the server, not the triggered stored procedures that are running because of them.

I'm trying to find out which which functions are being fired because of triggers.  I wouldn't mind know which triggers are firing, but most interested in knowing which functions are getting called because of them.

I've inherited this code base and it has a LOT of stored procedures, I'd just like to know what's getting run, I can turn on logging in those procedures if I need it, but I don't want to go annotating all the stored procs to see all the statements they are executing.


texts: Azure Virtual Machine - local temp storage (D: drive) - how much IOPS it can handle?

texts:
Virtual machine size DS3 (under older generation sizes)
Azure Portal (when deploying VM) shows that DS3 supports up to 16 data disks / up to 12800 IOPS - thats fine,
But what I am interested in is it's local/temp storage 28 GB D: drive
Documentation shows that this local/temp drive is SSD, but what I can't seem to find is information on how many IOPS this drive can handle ?
More specifically, if my TempDB has requirement of IOPS up to 1900, can D: drive on DS3 VM handle that requirement ?
From this source https://docs.microsoft.com/en-us/azure/virtual-machines/sizes-previous-gen I can guess that D: is 3200 IOPS but not sure I understand it correctly...


texts: Oracle Transportable Database export ORA-39340: unsupported object, INDEX

texts:
I want to get data dump from PDB(Transportable Database). DB version 12.2.0.1.0. Enterprise Edition.
Before I get export I applied oracle 27162931 and 27162975 opatch. Then I altered tablespaces except sysaux and system, temp and undotbs1:

alter tablespace users read only;

Next I logged in to the database using sysdba and then created directory and granted read, write access:

CREATE OR REPLACE DIRECTORY exp_dir AS 'D:\export_db';

GRANT READ, WRITE ON DIRECTORY exp_dir TO system;

Then I logged from system and ran export command like below,

expdp system/XXXXXX@D1943 dumpfile=DB12cdmp.dmp directory=exp_dir transportable=always full=y version=12.2.0.1.0 logfile=exp_test1.log

But I'm getting error like below,
Processing object type

  DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX

  ORA-39340: unsupported object, INDEX:"ABC"."SYS_IL0000903003C00006$$"
  will be skipped. ORA-39340: unsupported object,
  INDEX:"ABC"."SYS_IL0000903008C00002$$" will be skipped.


texts: CHECKDB problem - SQL files (mdfs,ldfs etc.) in Azure Files

texts:
Anybody out there using Azure Files (https://docs.microsoft.com/en-us/azure/storage/files/storage-files-introduction) for SQL data and log file storage? I've got one SQL Server using an AF share in this manner and am having issues running CHECKDB on its databases. Has anyone out there used Azure Files for SQL stuff and, if so, have you run into the following problem?

When attempting to run CHECKDB on master:

Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked.
See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

When attempting to run CHECKDB on user database:

DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.


texts:
Here is the problem: I have a table called "flags" with 54 columns:

PVKEY   PEOPLEKEY   ADDRESSKEY  FLAGTYPE    FLG1    FLG2    FLG3    FLG4    FLG5    FLG6    FLG7    FLG8    FLG9    FLG10   FLG11   FLG12   FLG13   FLG14   FLG15   FLG16   FLG17   FLG18   FLG19   FLG20   FLG21   FLG22   FLG23   FLG24   FLG25   FLG26   FLG27   FLG28   FLG29   FLG30   FLG31   FLG32   FLG33   FLG34   FLG35   FLG36   FLG37   FLG38   FLG39   FLG40   FLG41   FLG42   FLG43   FLG44   FLG45   FLG46   FLG47   FLG48   FLG49   FLG50

If I do this:

select * from flags where PVKEY = 902180

it takes forever. If I do for any other PVKEY, the query runs in less then 1 second.

If I do:

select * from flags

It runs fast until reachs that PVKEY.

I've try to UPDATE or DELETE, with no sucess, it never ends the execution.

The only query that works for this row is:

select PVKEY from flags where PVKEY = 902180

It returns me: 902180

I'm running SQL Server 2012.

Any ideas, please?


texts: Full-text search does not work if query begins from 0

texts:
I have a table with users and their addresses. Some flats looks like "p06"

This query does not find anything:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +06' IN BOOLEAN MODE);

This query finds "Washington street, h.36, fl.p06":

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +p06' IN BOOLEAN MODE);

But this query finds only Washington street, h.36, fl.6:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +6' IN BOOLEAN MODE);

This query works well and finds Washington street, h.36, fl.16:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +16' IN BOOLEAN MODE);

p.s. ft_min_word_len = 1

I wish to get all results by query "+Wash* +36 +6". And find "Washington street, h.36, fl.p06" by query "+Wash* +36 +06"

Sure it could be wrong queries for my task


texts:
I'm trying to setup a linux server with low memory < 1G+- available, so the goal is not to exceed 512mb of total mysql usage. I have below simple setup:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

max_connections=1501 #we only use up to 200, setting this to avoid too many conn error
innodb_flush_log_at_trx_commit=0 #we are not doing data critical application, we will sacrifice this to hit higher throughput, as we focus more on throughput
innodb_buffer_pool_size=128M
innodb_log_file_size=64M
max_allowed_packet=2G #due to large queries during data migration
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

As you can see, the buffer pool size is only configured to 128M, and file is at 64m. innodb_buffer_pool_instances is 1 by default, checked this using show variables too.

But based on my TOP graph, the mysql will occasionally use up to 700MB or my RAM, does anyone have a good guess what went wrong?

Thank you.

UPDATE:

SHOW VARIABLES LIKE '%buffer%';

Variable_name   Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_chunk_size   134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    1
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_log_buffer_size  16777216
innodb_sort_buffer_size 1048576
join_buffer_size    262144
key_buffer_size 8388608
myisam_sort_buffer_size 8388608
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    131072
read_rnd_buffer_size    262144
sort_buffer_size    262144
sql_buffer_result   OFF

SHOW GLOBAL STATUS LIKE '%buffer%';

Variable_name   Value
Innodb_buffer_pool_dump_status  Dumping of buffer pool not started
Innodb_buffer_pool_load_status  Buffer pool(s) load completed at 190216  0:54:10
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data   5230
Innodb_buffer_pool_bytes_data   85688320
Innodb_buffer_pool_pages_dirty  0
Innodb_buffer_pool_bytes_dirty  0
Innodb_buffer_pool_pages_flushed    340624
Innodb_buffer_pool_pages_free   2903
Innodb_buffer_pool_pages_misc   58
Innodb_buffer_pool_pages_total  8191
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   128
Innodb_buffer_pool_read_ahead_evicted   0
Innodb_buffer_pool_read_requests    1251760651
Innodb_buffer_pool_reads    4327
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   2331193


texts: Postgres log not captured few Sql queries in logs even all logging parameters enabled

texts:
It supposed to capture SQL statment after duration in logs,not sure why it is not capturing.

log_min_duration=0
log_statement='mod'
log_duration=on

Log:

2020-02-29 21:00:00.001 UTC,"user1","postgres",2393,"11.12.0.02:41776",5ec3f35d.959,9836739,"SELECT",2020-05-19 14:55:25 UTC,72/29255787,0,LOG,00000,"duration: 0.051 ms  execute _PLAN0x7fc06c1907c0: SELECT user.USEID, user.USERID, CASE WHEN TIME Is NULL AND ENDTIME Is NULL THEN user.STATUS  WHEN TIME Is NOT NULL AND ENDTIME Is NULL THEN (CASE WHEN  current_timestamp at time zone 'utc'  < TIME THEN 1 ELSE 2 END)     WHEN TIME Is NOT NULL AND ENDTIME Is NOT NULL THEN (CASE WHEN  current_timestamp at time zone 'utc'  BETWEEN TIME AND ENDTIME THEN 2 ELSE 1 END)       FROM  USER WHERE user.USER =$1 AND user.ORGN = $2 ","parameters: $1 = 'LD86B6JDOVTG2+PYUN8BYWPZHA+2MTED5AJDDEXPJCILJIZGBJ==', $2 = 'T'",,,,,,,,""
2020-02-29 21:00:00.002 UTC,"user1","postgres",2797,"11.12.0.12:42600",5ec3f404.aed,9950426,"SAVEPOINT",2020-05-19 14:58:12 UTC,176/29142796,2200812732,LOG,00000,"duration: 0.031 ms  statement: RELEASE _EXEC_SVP_0x7f577c1121f0;SAVEPOINT _EXEC_SVP_0x7f577c11210",,,,,,,,,""
2020-02-29 21:00:00.002 UTC,"user1","postgres",2326,"11.12.0.02:41610",5ec3f34f.916,9822750,"UPDATE",2020-05-19 14:55:11 UTC,23/29235490,2200812735,LOG,00000,"**duration: 0.753 ms",,,,,,,,,""**


texts:
I have two backups created via mysqlhotcopy:

backup-before-deletion from August 7, 2014 and
backup-after-deletion from February 3, 2016

A deletion event occurred on July 17, 2015 which resulted in the deletion of about 15,000 rows across 7 different tables.

Because this segment of data was hardly ever touched it wasn't noticed until a recent audit.

The good news is the deletion event can be simulated but it would have to be done on the backup-before-deletion.

What is the most cost effective way (paid for tools are on the table) to restore the deleted data?


texts: How to reduce the mdf file size in this database which I have 50 GB reserved space

texts:
I have a DB with 50 GB of size I need to reduce the size of it.
How to reduce the file size of it?

I already did this: shrink | files | selected 'Data' as filetype.
but still it shows 50 GB Reserved space is using.


texts:
i have the following table. and i wan't to repartition it, adding a few more partitions to shard the overall size.

however i don't find any information on how to add partitions in this situation.

i always did it with downtime, dumping it, truncating it, dropping it, recreating it with new partitions, and importing the dump, BUT as the table grows, the downtime grows, so i am asking if there is a re-partition command in mysql, with no-to-low downtime. ?

thx in advance.

CREATE TABLE `wp_postmeta` (
  `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`meta_id`),
  KEY `post_id` (`post_id`),
  KEY `meta_key` (`meta_key`(191)),
  KEY `bk1` (`post_id`,`meta_key`(191),`meta_value`(191)),
  KEY `bk2` (`meta_key`(191),`meta_value`(191))
) ENGINE=InnoDB AUTO_INCREMENT=12833303 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
(PARTITION p1 VALUES LESS THAN (50000) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (100000) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (150000) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (200000) ENGINE = InnoDB,
 PARTITION p5 VALUES LESS THAN (250000) ENGINE = InnoDB,
 PARTITION p6 VALUES LESS THAN (300000) ENGINE = InnoDB,
 PARTITION p7 VALUES LESS THAN (350000) ENGINE = InnoDB,
 PARTITION p8 VALUES LESS THAN (400000) ENGINE = InnoDB,
 PARTITION p9 VALUES LESS THAN (450000) ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN (500000) ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN (550000) ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN (600000) ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN (650000) ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN (700000) ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN (750000) ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN (800000) ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN (850000) ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN (900000) ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (950000) ENGINE = InnoDB,
 PARTITION p20 VALUES LESS THAN (1000000) ENGINE = InnoDB,
 PARTITION p21 VALUES LESS THAN (1050000) ENGINE = InnoDB,
 PARTITION p22 VALUES LESS THAN (1100000) ENGINE = InnoDB,
 PARTITION p23 VALUES LESS THAN (1150000) ENGINE = InnoDB,
 PARTITION p24 VALUES LESS THAN (1200000) ENGINE = InnoDB,
 PARTITION p25 VALUES LESS THAN (1250000) ENGINE = InnoDB,
 PARTITION p26 VALUES LESS THAN (1300000) ENGINE = InnoDB,
 PARTITION p27 VALUES LESS THAN (1350000) ENGINE = InnoDB,
 PARTITION p28 VALUES LESS THAN (1400000) ENGINE = InnoDB,
 PARTITION p29 VALUES LESS THAN (1450000) ENGINE = InnoDB,
 PARTITION p30 VALUES LESS THAN (1500000) ENGINE = InnoDB,
 PARTITION p31 VALUES LESS THAN (1550000) ENGINE = InnoDB,
 PARTITION p32 VALUES LESS THAN (1600000) ENGINE = InnoDB,
 PARTITION p33 VALUES LESS THAN (1650000) ENGINE = InnoDB,
 PARTITION p34 VALUES LESS THAN (1700000) ENGINE = InnoDB,
 PARTITION p35 VALUES LESS THAN (1750000) ENGINE = InnoDB,
 PARTITION p36 VALUES LESS THAN (1800000) ENGINE = InnoDB,
 PARTITION p37 VALUES LESS THAN (1850000) ENGINE = InnoDB,
 PARTITION p38 VALUES LESS THAN (1900000) ENGINE = InnoDB,
 PARTITION p39 VALUES LESS THAN (1950000) ENGINE = InnoDB,
 PARTITION p40 VALUES LESS THAN (2000000) ENGINE = InnoDB,
 PARTITION p41 VALUES LESS THAN (2050000) ENGINE = InnoDB,
 PARTITION p42 VALUES LESS THAN (2100000) ENGINE = InnoDB,
 PARTITION p43 VALUES LESS THAN (2150000) ENGINE = InnoDB,
 PARTITION p44 VALUES LESS THAN (2200000) ENGINE = InnoDB,
 PARTITION p45 VALUES LESS THAN (2250000) ENGINE = InnoDB,
 PARTITION p46 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

for more details:


texts: RAID 10 array configuration for SQL Server

texts:
I have a question regarding RAID 10 and SQL Server. My plan is to purchase an HP DL380 G9 with the 24 drive config. The question is how to break up the array.

The drives will be divided between the 3 bays to even out the traffic on the controller. Keeping in mind random IOPs are better with additional drives in the array, would be be better to:

Make 1 large RAID 10 array with logs, tempdb, and data split into separate logical drives. This would be 8-12 drives.
Make 2-3 smaller RAID 10 arrays and split up logs, tempdb, and data files by array. This setup would be 4 drives in each RAID 10 array.

The 8-12 drives would be better performing overall (our read/writes are probably 50/50). In scenario 2 would the controller serving 3 different logical drives take a performance hit?

We will use MS SQL Standard 2012 with the max memory (64GB + RAM for OS). Enterprise is probably not an option due to cost.


texts: PostgreSQL failsafe start after disk crash

texts:
We use PostgreSQL to store logs.

A disk crashed and some files in the file system were corrupt.

I read this: https://wiki.postgresql.org/wiki/Corruption

But this page does not solve my needs.

I don't care if some transactions got lost.

I care for availability.

Above page suggests "Consider contacting a professional support service provider".

That's not an option.

I see two ways to solve this:

Solution 1: If PostgreSQL does not start, then rename the data directory. Init postgres, create required DB and tables.

Solution 2: Use a tool which can start a corrupted DB. It does not matter if some transactions got lost.

Is there such a tool which can start a corrupted DB?

Given my current context (DB exists to be store logs), what other solutions could be used to ensure the DB is up again soon after a corruption?


texts:
I'm looking to keep a simple database for movies, with details like Name, YearofRelease, Genre etc. The key factor is the genres entry, where a movie could fall into multiple genres. Using a sql based approach, one way is to have a separate table for each genre and enter to movie id. Using a nosql mongodb approach, the generes can be stored as an array.
From the purpose of both storing and searching by genres, what is the better approach?


texts:
I have an application that is running on a device that has two partitions: C and D. The application runs off of C while D contains the SQL Server database (mdf file). In addition, D is also encrypted using BitLocker.

We are using the code-first approach where the database is created on the fly, based on the specified entities within our code. If the database has not yet been created and I turn BitLocker on, the application cannot create the DB and I get a 'Directory lookup exception', with the message 'This drive is locked by BitLocker'. Though, as soon as I turn off BitLocker, it gets created as expected and everything is fine. I turn on BitLocker again, and this time I get a different exception, 'the login failed for user sa'. Once again, this works when I turn BitLocker off. Is there something different that I need to do since we are using the SQL sa user to login?

According to Using SQL Server 2012 Standard with Bitlocker

I  need to make sure that the SQL Server Service account is the one used for turning BitLocker on. I've changed the account (Services -> Sql Server -> Properties -> LogOn) to the user account I used for turning BitLocker on, but that does not seem to do the trick.


texts: Linux command to kill all MySQL sessions from a specific User?

texts:
How can i kill all sessions from a user in mysql using a linux command line command?
 I have a user that is using all on my database connections and insted of killing one by one i want to make a script to kill them all !


texts: Mysql causing high load average with large amount of server resources left

texts:
I've have 2 servers running a application and the server that runs the mysql sgbd is with the load average around 3 times more than the webserver. I think that is something wrong because i have a lot of resources (RAM) left on the server. The load average is about 3~4 and the server is increasing it's response time. Here is the output of mysqltuner.pl:

[--] Up for: 9h 10m 55s (42M q [1K qps], 4M conn, TX: 19B, RX: 6B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 10.2G global + 6.6M per thread (800 max threads)
[OK] Maximum possible memory usage: 15.4G (49% of installed RAM)
[OK] Slow queries: 0% (5K/42M)
[OK] Highest usage of available connections: 36% (294/800)
[OK] Key buffer size / total MyISAM indexes: 100.0M/34.8M
[!!] Key buffer hit rate: 73.3% (45K cached / 12K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 3M sorts)
[OK] Temporary tables created on disk: 0% (54 on disk / 52K total)
[OK] Thread cache hit rate: 99% (478 created / 4M connections)
[!!] Table cache hit rate: 0% (400 open / 78K opened)
[OK] Open file limit used: 0% (3/4K)
[OK] Table locks acquired immediately: 100% (37M immediate / 37M locks)
[OK] InnoDB buffer pool / data size: 10.0G/1.0G
[OK] InnoDB log waits: 0

Server Configuration (note: this server runs ONLY the mysql server):

Intel Xeon E5-2620v3 (6 cores - 12 Threads)
2x 16GB DDR4-2133 ECC (Total: 32GB)
2x 256 GB SSD 2.5" (RAID1)

Based on this Server Configuration, i'm wondering if my my.cnf is with the best options and i want know if there is any sugestions to update this based on the Server Configuation and mysqltuner.pl output. Here's my my.cnf:

skip-external-locking
skip-name-resolve
lower_case_table_names          = 1

wait_timeout                    = 4
interactive_timeout             = 15

key_buffer_size                 = 100M
join_buffer_size                = 4M

max_allowed_packet              = 1M
thread_stack                    = 256K
thread_cache_size               = 250

myisam-recover                  = BACKUP
max_connections                 = 800

#Qcache not enabled due to high tax of inserts/updates
query_cache_limit               = 1M
query_cache_type                = 0
query_cache_size                = 0

log_error = /var/log/mysql/error.log

log_slow_queries                = /var/log/mysql/mysql-slow.log
long_query_time                 = 1

expire_logs_days                = 10
max_binlog_size                 = 100M

join_buffer_size                = 4M

tmp_table_size                  = 30M
max_heap_table_size             = 30M

#InnoDB config's
innodb_commit_concurrency       = 0
innodb_io_capacity              = 90000
innodb                          = ON
innodb_flush_method             = O_DIRECT
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_doublewrite              = 1
innodb_additional_mem_pool_size = 64M
innodb_thread_concurrency       = 12
innodb_log_file_size            = 512M
innodb_log_buffer_size          = 24M
innodb_read_io_threads          = 12
innodb_buffer_pool_size         = 10G
innodb_write_io_threads         = 12
innodb_log_files_in_group       = 2

Note: I have hide some unuseful parameters of my my.cnf.

UPDATE

I'm using Mysql Server 5.5 and i've now increase my table_open_cache (it has it's default value of 64 in the moment of the post). Now, mysql tune shows the following line when talk about table cache:

[OK] Table cache hit rate: 99% (1K open / 1K opened)

About the ENGINE point, i have 87 tables and only 11 of them are MyISAM (12% of the entire database + those tables are "Select/Insert" tables), the other ones are all InnoDB (with a hight tax of select/insert/update). I don't have ANY DELETE clause on my system, instead i set a flag to a row saying that it was deleted (so i can delete it in a non-busy time). I've a lot of queries (especially queries like Select count(*)) tooking more than 1.5, 2 seconds to execute.

About innodb_io_capacity, the server disks are all SSD that has 100.000IOPS.


texts: MySQL : How to restore mysql dump (.sql) ignoring DROP TABLE statement?

texts:
I am trying to restore multiple mysql dump (.sql) files. Data in each dump file are from the same table, but with different ID (primary key) range.

For example,

> 1.sql (0 < ID && ID <= 100)
> 2.sql (100 < ID && ID <= 200)
> ...

I have 100 files like the above, each containing 'DROP TABLE IF EXISTS' and 'CREATE TABLE' statements. Because of the two statements, I can't simply automate the mysql restore command.

> root$> mysql < dump.sql

Since every time I execute the sql, it will drop the table and delete all data restored. So I need something that I can ignore the DROP and CREATE statements while doing ...

> root$> mysql < dump.sql

Has anyone done this before?

FYI, my OS is Amazon Linux AMI.


texts: How To Store EAV Data When It Has Nested Objects/Entities

texts:
I have json data that have both nested objects and attributes. Eg of one json object:

(After json_decode into an array:)

Array
(
    [attributename1] => attributevalue1
    [attributename2] => attributevalue2
    [NestedEntity1] => Array (

                [0] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                             )
                [1] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                             )
                [2] => Array (
                            [attributename1] => attributevalue1
                            [attributename2] => attributevalue2
                            [NestedEntity1.2] => Array (

                                    [0] => Array (
                                                [attributename1] => attributevalue1
                                                [attributename2] => attributevalue2
                                                 )
                                    [1] => Array (
                                                [attributename1] => attributevalue1
                                                [attributename2] => attributevalue2
                                                 )

                             )

                  )

) )

Note: entities can be further nested in nested entities

My question is: How can I store this data into MySQL?

If it's just attributes, it's easy with a table in MySQL set up for EAV data. But if I have nested objects, I am quite lost as to how to store this. An important thing is that I'd have to be able to pivot the table and make queries like "SELECT * FROM entity WHERE A LIKE B etc.

I suspect I might have to split the nested entities into new tables of their own, then create one-to-many relationship between the "parent" and "child". However:
(1) I have no clue as to do this such that any json data can be accomodated. This probably means some program has to detect automatically a nested object.
(2) ALthough I am open to populating the data form a single json object into multiple tables, one representing each (nested) entity, I was thinking if it might be possible to populate everything into a single table in MySQL. This would prevent the need for creating new table schemas when the need arises.


texts: I've seen those error codes with prefix HY like HYT00 in MS-SQL or HY000 in MySQL, but, what do they stand for? why are they similar?


texts: How to find the count of words (all and unique) in a column row-wise?

texts:
I have a column in my Postgresql database named Description which contains text. For some analytics purpose, I want to find total number of words (seperated by spaces) - all words and unique words - in that description and set those counts in columns word_count and unique_word_count. For eg.

         id         |  Description
    ----------------+---------------------------------------
         1          |  What a great day
         2          |  This is a product. It is useful

I want to have following output:

         id  | word_count | unique_word_count |  Description
    ---------+------------+-------------------+---------------
         1   |  4         | 4                 | What a great day
         2   |  7         | 6                 | This is a product. It is useful

The unique_word_count for id = 2 is 6 because the word is has been repeated 2 times.


texts: Shell script for backup binlog files for every 8 hours in MySQL

texts:
I'm writing a shell script for point in time recovery for MySQL Instance. Our requirement is backing up Binlog for every 8 Hours (Daily 3 times) using mysqlbinlog and it should backup binlog starting from last day full database backup time to current time.

Suppose if full database backup happens at 2:00 AM morning and binlog backup happens at 6 AM, 12 PM and 6 PM, then first binlog backup should backup from 2:00AM to 6:00 AM etc.

After completing daily full database backup, it will store output of "Show master Status" in a file called "master_status.txt" and I'm calling it from binlog backup script as below, but whenever there are more than 1 binlog file generated b/w full db backup and binlog backup then script is not taking binlog backup for all binlogs.

binlog=`cat master_status.txt | awk '{print $1}'`
dat="$(date +'%d_%m_%Y_%H_%M_%S')"
mysqlbinlog -u$DB_USER -p$DB_Password "$bin_log" --to-last-log >> "binlog_file_$dat".sql

Version : 5.5.x
Engines: InnoDB and MyISAM


texts: Struggling with normalisation (database design critique)

texts:
I'm relatively new to database design and have been reading up a lot on best practice, and I'm starting to design a schema for a new application that will be developed and I am struggling with a few concepts. Here is the database at the moment:

The red line denotes the PK and FK relationships.

Client.Client_ID PK -> Contracts.Client_ID FK
Client.Client_ID PK -> Sites.Client_ID FK
Sites.Site_ID PK -> Contracts.Contract_ID FK

In its simplest form:

A client can have a contract OR a site can have a contract. The database will be expanded in future to include other tables that will also have a contract associated with them (say for example a Software table that is renewed yearly will have a link to Contracts.

Where I am struggling:

1) Should I have the Contract_ID for Client and Sites stored in the Client table and Sites table respectively and have that as the foreign key or is the way I've done it the best way?

2) How does this design look in general. Am I missing certain best practices etc?

Any input is much appreciated


texts: Find time between status changes for each unit on each incident

texts:
I have a table called History that holds unit status changes when they are assigned to incidents. Each unit can change status from DP to AK to ER to AR. Statuses can be skipped.

The table is called history. The fields are Unit, Datetimestamp, Status, Incident.

I want to find the average time between each of the statuses for every unit for an incident. e.g. for the UNIT E04 on INCIDENT F141000001 I want to know the time between DP to AK, AK to ER, ER to AR. Then use this to derive the average time between each of these status changes for all the units in the table.


texts: Merge replication fails on executing query

texts:
My merge replication was working fine this morning. Since an hour or 2 ago, my synchronisation status windows keeps showing this message:

Thread id 3108 will wait for 15 second(s) before retrying the query on Publisher 'MSI-SQL-MAS01'.

Can anyone tell me what's going on? The database is 500Gb in size and it's also pretty heavily used. However, the replication must work, we need to keep the database at the subscriber in sync.

Also this is what I see after a minute or so:

A query executing on Publisher 'MSI-SQL-MAS01' failed because the connection was chosen as the victim in a deadlock. Please rerun the merge process if you still see this error after internal retries by the merge process.

Like I just said, the replication was working fine this morning. I also restarted the merge agent like the error suggested but with no results.

I also tried

update sysmergepublications set [generation_leveling_threshold] = 0

at both subscriber and publisher database but no luck it seems.. only getting the following error sometimes too:

The merge process is retrying a failed operation made to article 'xx' - Reason: 'The merge process was unable to synchronize the row.'.

Thanks in advance.

I don't see my former error anymore. The only thing I get now is:

"The merge process is retrying a failed operation made to article 'xx' - Reason: 'The merge process was unable to synchronize the row.'."

The profiler makes no sense to me. I don't see any relevant information.

If I reinitialize the subscription, what does it do? I hope I do not need a new snapshot for that because that would be impossible at this time. The database is over 500Gb in size and the snapshot takes over 9 hours to complete. The database is in production all the time and very heavily used, so a lock on the database to create a snapshot is really a no-go.

Alright this is what I see in the conflict viewer:

The same row was updated at both 'master_server' and 'slave_server'. The resolver chose the update from 'master_server' as the winner.

How is it possible that the same row gets updated at both master and slave server while our application is connected to the master server?
It looks like something is updating the same row at both servers with different content and made merge agent crash?


texts: Text index using text_pattern_ops with utf-8 vs. default operator class with C?

texts:
What is happening with each operator class, and why is it necessary to use text_pattern_ops when using utf-8?
When using the default operator class with C, what happens to the text column indexes if the database is dumped to another Postgres that uses utf-8? Is it simply a matter of creating new indexes with text_pattern_ops?
Does the default operator class with C perform any better or worse than text_pattern_ops?

I'm considering changing the RDS client_encoding parameter to C so it wouldn't be necessary to use text_pattern_ops everywhere, but I'd like to find out if any disadvantages (other than not supporting emojis and other languages) or complications may exist.

Related docs: https://www.postgresql.org/docs/current/static/indexes-opclass.html


texts: SQL Server Agent Job Step "Run As" dropdown

texts:
When creating a job step in SQL Server 2008 R2 Agent, the third dropdown in the General page is "Run as:" - but this is empty, and seems to always be empty! What is it for?

Thanks


texts: Dynamically Define a Range in a Dimension

texts:
I have an issue that I face every time I decide to build a cube, and I haven't found a way to overcome it yet.

The issue is how to allow the user to define a range of things automatically without having the need to hardcode them in the dimension. I will explain my problem in an example.

I have a table called Customers:

this is the data in the table:

I want to display the data in a pivot style and group up the Salary and Age in defined ranges like below:

I wrote this script and defined the ranges:

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = case
        when cast(salary as float) <= 500 then
            '0 - 500'
        when cast(salary as float) between 501 and 1000 then
            '501 - 1000'
        when cast(salary as float) between 1001 and 2000 then
            '1001 - 2000'
        when cast(salary as float) > 2000 then
            '2001+'
        end,
        [AgeRange] = case
        when cast(age as float) < 15 then
            'below 15'
        when cast(age as float) between 15 and 19 then
            '15 - 19'
        when cast(age as float) between 20 and 29 then
            '20 - 29'
        when cast(age as float) between 30 and 39 then
            '30 - 39'
        when cast(age as float) >= 40 then
            '40+'
        end
  FROM [Customers]
GO

My ranges are hard coded and defined. When I copy the data to Excel and view it in a pivot table, it appears like below:

My problem is I want to create a cube by converting the Customers table into a fact table and create 2 dimension tables SalaryDim & AgeDim.

The SalaryDim table has 2 columns (SalaryKey,SalaryRange) and the AgeDim table is similar (ageKey,AgeRange). My Customer fact table has:

Customer
[CustId]
[CustName]
[AgeKey] --> foreign Key to AgeDim
[Salarykey] --> foreign Key to SalaryDim

I still have to define my ranges inside these dimensions. Every time I connect an Excel pivot to my cube, I can only see these hardcoded defined ranges.

My question is how to define ranges dynamically from the pivot table directly, without creating the range dimensions like AgeDim and SalaryDim. I don't want to only be stuck to the ranges defined in the dimension.

The range defined is '0-25' , '26-30' , '31- 50'. I might want to change it to '0-20', '21-31' , '32-42' and so on, and users request different ranges every time.

Every time I change it, I have to change the dimension. How can I improve this process?

It would be great to have a solution implemented in the cube, so that whatever BI client tool that connects to the cube can define the ranges, but I wouldn't mind if there is a good way using Excel only.


texts: SQL Server unusual behavior on LOB insertion

texts:
We are facing unusual behavior on a SQL Server 2008 R2 instance. We have a small .NET application, which we use to insert images and pdf files inside database as VARBINARY. Sometimes, we are facing query timeouts when the file size is bit large, sometimes larger than 3 MB, and sometimes this behavior is observed with 4 to 5 MB files.

IIS is installed on a separate machine.

Through trace we have found that no such insertion query is received on the SQL Server end during save image process from application.

This could be an application server issue, but it's the fifth time that we have resolved this issue by restarting SQL Server services. Why so?

During this problem rest of discrete data operations and small LOB insertion queries work fine.

Version: SQL Server 2008 R2 Service Pack 3
Edition: Standard
Windows Server 2008 R2 Enterprise (SP1)
Intel(R) Xeon(R) CPU E5-2630 v2 @2.60Ghz (2 Processors)
Memory 36GB to SQL Server (out of total 64GB)

(We have requested IT to assign at least 20GB more to SQL Server as it's a dedicated DB instance.)

Optimize for adhoc option is set to 1. Adhoc query plans size is about 18% of total cached plans. We are going to clean these adhoc plans periodically using Kimberly's script. I have verified that things are normal on wait stats and performance counters side, and nothing received on Trace when query is submitted from app. LOB ndf file is on local drives.

Current workaround
Thanks Kin for this idea. This time, when we faced the same issue, we just refreshed the system cache by executing the following DBCC statement, and issue is resolved:

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL

We are still in search of permanent solution as periodically clearing system cache is not possible for some of workloads (specially 24x7 cases).


texts:
After reading following blogs, I understand that hash match aggregate causes blocking. Using appropriate indexes it can be made as stream aggregate.

Blocking/non-blocking aggregate operators
The blocking nature of aggregates - Rob Farley
Hash Aggregate-Craig Freedman

I have a database that has over 200 tables which has been created years back. I am trying to find all queries with group by that is currently working with hash match aggregate operator. One possibility I found is using dmv like below. But I don't know how to filter it to list only queries with hash match aggregate operators. How to achieve this? Also, on a big picture level, what are the other options to get this information other than following dmv?

SELECT cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.TEXT LIKE '%GROUP%'


texts: Change MAMP to use a lower Mysql 5.1 version

texts:
I need to have the same development environment for my PHP program. My clients test server is running on Mysql 5.1 while my local is using MAMP running Mysql 5.5.

How do downgrade my mysql database to used  mysql 5.1

Thanks.


texts:
I have a view definition that references a linked JDE server. Querying the view is now giving some very strange results. If I perform a SELECT * I get all the results (619 rows) back but if I try SELECT TOP 1000 * or query for each column individually I get the following error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'A' to data type int.

UPDATE: Thanks for the suggestions. It turned out to be a data issue where someone has added codes A, G, and R to the JDE table used to hydrate a relationship that requires an int code in SQL Server. It doesn't explain the weird behaviour but it does address it.


texts: SQL Server Login auditing capturing Logon events when set to Failed logins only

texts:
I have an issue where within my SQL Server 2016 Standard instance, Login auditing is set to Failed logins only but within the ERRORLOG file, it is capturing Both failed and succesful logins. Once I observed the behavior, I thought that the reason for this was that I had an ON ALL SERVER FOR LOGON trigger enabled.

This trigger was created to prevent usage of a SQL Server credential when connecting with SQL Server Management Studio that originates from a host_name that has not been delegated access. This was added to allow access from specific terminals outside of the domain (but within the same network) that the SQL Server is hosted on.

This is the trigger:

USE master;
GO
CREATE TRIGGER SSMSRemoteBlock
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT
                *
            FROM
                sys.dm_exec_sessions es
            WHERE
                es.login_name = 'ExternalDBA'
                AND es.host_name NOT IN ('ExternalHost1', 'ExternalHost2')
                AND es.program_name LIKE 'Microsoft SQL Server Management Studio%')
    ROLLBACK;
END;

However, even after dropping the trigger, restarting MSSQLSERVER, and even the entire server, Logon events are still being captured. I dug even deeper and I browsed to Software\Microsoft\MSSQLServer\MSSQLServer within the registry, and the AuditLevel REG_DWORD was not explicitly specified. So, I manually added it with a value of 2 for Failed logins only. Restarted MSSQLSERVER again to no avail. Set the value to 0 for None and restarted... still capturing all login events.

What else could contribute to this behavior?


texts: I'm trying to figure out a "safe" way of allowing a non-SUPER user to execute a specific command requiring SUPER privileges, i.e. SET SESSION binlog_format = 'MIXED'. I don't want to grant the user SUPER privileges for security reasons. Can this be accomplished using a stored procedure or any other method?


texts: PGPool II - Failover when current master node dies

texts:
I have a 2 node high-availability postgresql 9.5 cluster. I'm using streaming replication, PGPool II (3.5.4 [ekieboshi]), and a virtual (or floating) secondary IP all hosted on aws to accomplish this.

I've configured the PGPool Watchdog and it is running well but only seems to be executing a failover script on the failed node when the postgresql service instance dies.

I'd like to be able to execute a failover script on the slave node when the master becomes unreachable. Is there any hook for this in PGPool II? If not, is this not really even a good idea? Thanks in advance for your help.


texts: How to get (format) a Firebird v3 DATEDIFF() to the number of years, months and days all together?

texts:
In Firebird 3 I can use DATEDIFF() with either YEAR, MONTH or DAY to get the number of years, months or days respectively.

I need a way to get all-in-one years, months and days.

For example: 412 days should be represented as 1 year, 1 month and 16 days.


texts: Clustered columnstore index on small tables

texts:
Clustered column store indexed tables in general are useful for large tables. Ideally with million of rows.
And also useful with queries, which selects only the subset of available columns in such tables.
What happens if we break these two "rules"/best practices?

Like having a clustered column store indexed table which will only store few thousand, or hundreds of thousands of rows max.
And running queries against those clustered column store table where all the columns are needed.

My tests don't reveal any performance degradation comparing to row stored clustered index table.
Which is great in our case.
Is there any "long term" effects breaking these two rules? Or any hidden pitfalls which haven't showed up just yet?
Context why is it needed: I designed a database model which will be used for many instances of different vendor databases. The schema remains the same in every database, but different vendors have different amount of data. Hence few small vendors may end up with small amount of data (<1 000 000) in their tables. I can't allow myself to keep up two different database for row-store and column-store model.


texts: How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument?

texts:
I am trying to run, via a linux shell script, a Postgresql command that reads a CSV and updates a table with it.
The difficulty I am facing, is that this shell script has to pass an argument to the SQL program, argument that contains the path where the CSV is stored.
I just can't make it...
The PSQL command in my shell script is:
PSQL -f ${UNXSQLPATH}/new_nomenclature.sql - v v1=${UNXDATAPATH}

And the new_nomenclature.sql program contains the following line:
COPY nomenclature FROM :v1||'new_nomenclature.csv' CSV DELIMITER ';' HEADER ENCODING 'LATIN9';

Note: when I hardcopy the path in the SQL program, everything works fine.


texts: Postgres primary key sorted in the reverse order. Will it use the index efficiently?

texts:
My Rails database is backed by Postgres database.  As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.

My list views show records in the reverse chronological order.  So I just sort my result set in the reverse order of the primary key.

My question is: will the query use the primary key index?  And if yes then as efficiently?  How do I verify that?

Thanks in advance for your time.

Bharat


texts:
I have 2 servers with master-master replication using Percona MySQL 5.6 and I am having replication lag in the second server: db-02, however db-01 never had a lag situation.

What could be causing this lag? It rises sometimes up to >1000 secs of lag and sunddenly it fells to 0 secs. I have checked it with Percona's pt-heartbeat tool and is real lag.

This are the server configurations, both have 8 cores, 32GB RAM and SSD disks:

DB-01 (Xeon E3-1245 V2 @ 3.40GHz)

[mysqld]
server-id              = 1
log_bin                = /var/lib/mysql/mysql-bin.log
binlog-ignore-db       = mysql
binlog-ignore-db       = test
datadir                = /var/lib/mysql

expire-logs-days = 10

auto_increment_increment= 2
auto_increment_offset   = 1

max_connections=3000

query_cache_size=0
query_cache_type=0

innodb_file_per_table
innodb_file_format=barracuda
innodb_flush_method=O_DIRECT
innodb_log_file_size=128M
innodb_thread_concurrency=0

innodb_flush_log_at_trx_commit=1
sync_binlog=1

# ~90% of memory
innodb_buffer_pool_size=27000M

# 4 * # of CPU cores
innodb_read_io_threads=32
innodb_write_io_threads=32

innodb_io_capacity = 5000

DB-02 (i7-4770 CPU @ 3.40GHz)

// same that db-01 but with this options different:
server-id               = 2
auto_increment_offset   = 2

The output of SHOW SLAVE STATUS on each server:

DB-01

               Slave_IO_State: Waiting for master to send event
                  Master_Host: db-02
              Master_Log_File: mysql-bin.000022
          Read_Master_Log_Pos: 3348962
               Relay_Log_File: db-01-relay-bin.000042
                Relay_Log_Pos: 3349125
        Relay_Master_Log_File: mysql-bin.000022
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3348962
              Relay_Log_Space: 3349462
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
        Seconds_Behind_Master: 0
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:

DB-02

               Slave_IO_State: Waiting for master to send event
                  Master_Host: db-01
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 38905060
               Relay_Log_File: db-02-relay-bin.000048
                Relay_Log_Pos: 17834447
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 28196753
              Relay_Log_Space: 28542928
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
        Seconds_Behind_Master: 310
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:


texts:
I have 2 tables:

PRODUCTS

PREFIX      SUFFIX      ATTR1   ATTR2
-----------------------------------------------------
001         AAA         50      100
001         BBB         20      300
010         ACC         50      500
100         ABC         10      200
100         CBA         30      200
100         BBB         10      100

OPTIONS

PREFIX      SUFFIX      OPT_CODE
----------------------------------------
001         AAA         A1
001         AAA         A2
001         AAA         A3
001         BBB         A2
001         BBB         A1
001         BBB         A3
100         ABC         A1
100         CBA         B1
100         BBB         A2

Created with this DDL:

create table products (
    prefix VARCHAR2(10),
    suffix VARCHAR2(10),
    attr1  VARCHAR2(100),
    attr2  VARCHAR2(100)
    )

create table options (
    prefix VARCHAR2(10),
    suffix VARCHAR2(10),
    opt_code VARCHAR2(2)
    )

I have created a primary key in the products table:

alter table products add constraint products_pk primary key (prefix, suffix);

and a primary key in the options table:

alter table options add constraint options_pk primary key (prefix, suffix, opt_code);

When I try and add a foreign key to the options table like so:

ALTER TABLE options ADD CONSTRAINT fk_options_products FOREIGN KEY (prefix, suffix)
   REFERENCES products (prefix, suffix);

I am getting this error:

SQL Error: ORA-02298: cannot validate (PRODDB.FK_OPTIONS_PRODUCTS) - parent keys not found
02298. 00000 - "cannot validate (%s.%s) - parent keys not found"
*Cause:    an alter table validating constraint failed because the table has
           child records.
*Action:   Obvious

I validated that all the child records (in OPTIONS) have entries in the parent table (PRODUCTS) but not all the entries in PRODUCTS have child entries in OPTIONS.

My understanding of that error - you get that when you don't have a parent record for a child entry.

Am I missing something here?


texts: How do I count the number of columns in each table?

texts:
I want to write a script to listing the tables in my database with the number of columns in that table.

Like this:

table name      number
---------       --------
table1            1
table2            13
table3            2
table4            6


texts: Finding integer database columns that may have their data type changed to reduce size

texts:
I have a database schema that I've inherited where all the integer columns have been defined as int but because of business rules many could be replaced by tinyiny and other data types that would require less storage. While I don't expect much of a performance improvement by using the smaller types my reasons for wanting to do so are:

The application uses SQL Server Express so I'd like to make the most of the 10GB database size before needing to purge data.
The application is shipped with a full initial copy of the database and updates are synced using the Microsoft Sync Framework. Some client PCs are on relatively slow Internet connections so the smaller the database size the better.

I was wondering if there was some way to quickly identify columns that may be 'oversized'? I realize that the results will need to be carefully reviewed to make sure that the column will never exceed the limits of the new data type.


texts: ORDER BY usage in subquery (derived table) SQL standard allowed or not

texts:
MariaDB documentation is mentioning that ORDER BY clause in subqueries (derived table) are never allowed by SQL standards.

Meaning the SQL query

SELECT
   field1
 , field2
FROM (
  SELECT
       field1
     , field2
  FROM
   table1
  ORDER BY field2
) alias

is never allowed by SQL standards

  A "table" (and subquery in the FROM clause too) is - according to the
  SQL standard - an unordered set of rows. Rows in a table (or in a
  subquery in the FROM clause) do not come in any specific order. That's
  why the optimizer can ignore the ORDER BY clause that you have
  specified. In fact, the SQL standard does not even allow the ORDER BY
  clause to appear in this subquery

see source

But when i look into the SQL 92 standard. (i know it's old)

4.9  Tables

  ...
  ...
     A table is either a base table, a viewed table, or a derived table.
           A base table is either a persistent base table, a global tempo-
           rary table, a created local temporary table, or a declared local
           temporary table.
  ...
  ...
          A derived table is a table derived directly or indirectly from one
           or more other tables by the evaluation of a <query expression>.
           The values of a derived table are derived from the values of the
           underlying tables when the <query expression> is evaluated.
  ...
  ...

  The simply underlying tables of derived tables and cursors are
           defined in Subclause 7.9, "<query specification>", Subclause 7.10,
           "<query expression>", and Subclause 13.1, "<declare cursor>". A
           viewed table has no simply underlying tables.

They mention cursors let see what they say about cursors and what they are.

4.21 Cursors

  ...
  ...

  A cursor in the open state identifies a table, an ordering of the
           rows of that table, and a position relative to that ordering. If
           the <declare cursor> does not include an <order by clause>, or
           includes an <order by clause> that does not specify the order of
           the rows completely, then the rows of the table have an order that
           is defined only to the extent that the <order by clause> specifies
           an order and is otherwise implementation-dependent.

So they mention here a cursor in open state can be a table is either a base table, a viewed table, or a derived table. if you look how they define what a table is in section 4.9 Tables

If i read this i could be interpreting it as using ORDER BY in derived table (like the SQL query above) is perfect valid by SQL standards and it's up to vendors if they would allow ORDER BY there as a option.
But i would be interpreting it wrong english is not mine mother laungauge, so the question is iám interpreting it correctly?


texts: How to bucketize using Query optimal bucketization algorithm

texts:
I am trying to implement query optimal bucketization algorithm (QOB). The input of the algorithm is D=(V,F) where V is the value of the attribute and F is the frequency of occurrence.
For EXAMPLE
D = {(1, 4), (2, 4), (3, 4), (4, 10), (5, 10), (6, 4), (7, 6), (8,
2), (9, 4), (10, 2) } is the INPUT. The OUTPUT of the algorithm is the minimum bucket cost and partition point.

Say the maximum number of buckets allowed is 4 (M=4). The minimum value of the cost function comes out to be = 120 after executing the algorithm.

In the given algorithm H matrix stores bucket costs and OPP matrix stores optimal partition points. Now I have got H matrix and OPP matrix. I didn't understand how to use this OPP matrix to make partitions. In the given example the partition is done like this

{1, 2, 3 }, {4, 5, 6 }, {7 }, {8, 9, 10 }

And minimum cost of bucketization is 120. Please help me find the partition points using OPP matrix.

I have attached the screen shot which shows the details of H and OPP matrix


texts: What is the correct way to store multiple values of an entity's property with a "primary" or "default" one?

texts:
In previous database model I've had an entity that had a simple name property. Later I'd find out that a single name was no longer sufficient - rarely entities had multiple names and it was time to rethink the model.

Now, the obvious answer would be to just add a 1:n relation to a table holding the names and ditch the name column, but I need to also keep track of a default or primary name value and I always need to have exactly one - preferably enforced by the database. So I can't just do what I described.

I also need to be able to easily search the names, so the solution to keep the name column while having the additional table hold only the "extra" names would make that slightly more inconvenient (and it just doesn't feel right).

Now there are two actual solutions I'd imagine as working and correct - we can either have an extra column in the names table that would say whether a name is a primary name (it could as well work as a sorting column actually representing the priority of the name with the "default" or "primary" being the one with highest or lowest priority). Or we could keep the "name" column in the entity and have that be the reference to whatever name is the primary name.

Now, I can't really decide between the two. The first option feels right, but (assuming we use the "primary" column for only denoting whether it is a primary name or not) we can't use simple MySQL constraints to ensure that there is always exactly one primary name (no more, no less). Triggers could be used to check that though I believe, and if we used the column for sorting / preference we could easily put a unique index on the relation column and the sorting column to make sure there is always some name with highest priority. But we still can't enforce a name to be there for every entity.

Now, having a reference to the "primary" name from the entity while having any additional names just be there in the names table seems like the logical solution. But there's, once again, a catch: what if the name I reference as "primary" doesn't belong to this entity (i.e. it references a different instance of the entity? I could enforce this in the application fairly easily, but I'd certainly prefer the database to also enforce this, as it is its responsibility.

So tell me, what is the correct way to do this? I feel like it must be a very common scenario. Is there perhaps any other solution that I'm missing?


texts: sp_send_dbmail with multiple reply_to addresses

texts:
I got this error when I specified multiple email addresses in reply_to

  The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2014-08-19T12:54:35). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
  )

here is the sql snippets

EXEC msdb..sp_send_dbmail
@profile_name = 'Company',
@recipients = 'hi@sg.com',
@subject = 'PO delivery dates confirmation',
@body = 'test',
@body_format = 'HTML',
@importance = 'Normal',
@reply_to = '2@sg.com;3@sg.com'

I can't use distribution group because the reply_to is dynamic base on the query. what would be the work around for this?


texts: Finding the name of the supplier that supplies the most parts

texts:
I have the following schema:

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

Now what I'm trying to do is get the name of the supplier that has the most items for sale in the Catalog. This is an example instance:

What I have so far is:

SELECT S.sid, COUNT(*) AS NUM
FROM Catalog C, Suppliers S
WHERE C.sid=S.sid
GROUP BY S.sid

Which returns each supplier id and the number of parts they supply, which sounds like a good start; however, I'm not sure where to go from there.

I tried:

SELECT Y.sid, MAX(Y.Num)
FROM (SELECT S.sid, COUNT(*) AS NUM
      FROM Catalog C, Suppliers S
      WHERE C.sid=S.sid
      GROUP BY S.sid) Y

But that returned:

| sid | MAX(Y.NUM) |
+-----+------------+
| 1   |     5      |

...which is wrong.

I really feel like I'm over complicating this and that there is a simple solution that I'm missing. Any help is appreciated.


texts: What is PostGIS doing with ST_DistanceSphere?

texts:
PostGIS ST_Distance_Sphere and ST_DistanceSphere return the same thing in PostGIS 2.2.

ST_DistanceSphere says

  Uses a spherical earth and radius derived from the spheroid defined by the SRID.

ST_Distance_Sphere says,

  Uses a spherical earth and radius of 6370986 meters

I'm looking into this answer, specifically

SELECT ST_DistanceSphere( ST_MakePoint(0,0), ST_MakePoint(0,1) );
 st_distancesphere
-------------------
   111195.07973463
(1 row)

Currently that returns 111195.07973463, it seems it should return

SELECT ((6370986 * 1 * pi()) / 180) AS distance_on_sphere
 distance_on_sphere
--------------------
   111194.682298463
(1 row)

That's a difference of 0.397436166531406, almost half a meter. Wolfram Apha shows

111194.6822984634581396675695437019275676654860169289329663

Which puts Wolfram at a more precise version of my napkin math and on par with MySQL ((6370986 * 1 * pi()) / 180) and validates that ST_DistanceSphere is borked.

What's going on here?

POSTGIS="2.2.2 r14797" GEOS="3.5.1-CAPI-1.9.1 r4246" PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.9.3" LIBJSON="0.12.1" RASTER


texts: Does a full transaction log cause a rollback?

texts: Will a transaction that is trying to write something into the transaction log be rolled back if the transaction log is full?


texts:
I'm using Oracle 11g R2 on RHEL5.

How can I find what is being audited? I want to know what actions (like create, insert, delete, update etc.) are being audited or logged for a user, say scott.


texts: Why set Auto Update Statistics to False?

texts:
I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been implemented.

I'm seeing daily blanket index rebuilds (I can deal with this one) and also daily manual updating of statistics.

Around half of the databases have been set to Auto Update Statistics = False, for reasons which are not clear other than I am told it is to reduce 'Performance Issues'...

I always thought, and worked to, best practice of setting this to True and felt the Manual Update was not necessary if this setting was True. Am I wrong?

Can anyone explain what the benefit would be in having this set as False, but doing a daily manual update instead?

I should mention that some of the databases are highly transactional (millions of Inserts, Deletes, Updates per day) Others are low in terms of transaction rates, and some are all but read-only. There is no rhyme or reason though as to which have the Auto Update setting set to False. It appears to be a lottery.


texts: Linking Oracle 10g XE with SQL server using ODBC

texts:
I am connecting Oracle 10 XE with an SQL server 2008 using ODBC in windows 7.

I have successfully done following steps:

Configured ODBC and test it, runs ok!
Configured listener in tnsnames.ora and listener.ora
Started listener from command prompt
Created database link from interface SQL command line

After creating database link, when i issue a query to SQL server an error is returned: ORA-12154: TNS:could not resolve the connect identifier specified.

Below are the contents of *.ora files:

***************************** listener.ora *****************

LISTENERSQLSERVERDSN =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENERSQLSERVERDSN=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=SQLSERVERDSN)
         (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
         (PROGRAM=hsodbc)
       )
      )

************************tnsnames.ora************

SQLSERVERDSN  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521))
    (CONNECT_DATA=
    (SERVER=DEDICATED)
    (SID_NAME= SQLSERVERDSN))
    (HS=OK)
  )

*********************sqlnet.ora***********

SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Please help since i have tried almost every solution found.

thanks,


texts: Creating a Persisted Computed Column with a function

texts:
I am working with the programmers on a database solution.  They want to add a computed column to mimic the old keys for the older queries, procedures, and systems and index it.  The new keys will be GUIDS.

To do this, they want to create a function for the computed column that creates a value and persist it.  It will not let them persist the column.  I don't have any warm fuzzies about the idea and I also can not find any info on the web about the technique (is it a technique?).

I am thinking they need to add a trigger instead. Does anyone have any ideas?

The function will be run as this:

(SELECT [INT Identity field] FROM TABLE WHERE [GUID COLUMN] = @GUIDKEY

It returns an INT Identity field based on the GUID.

This will be run on ever insert into a related table.  SO  if Table One holds the primary key, the related table Two will update (using the GUID passed in) to get the key from the Table one and insert it into table two.