texts: Why SQL Server Kerberos authentication does not work?

texts:
I am trying to find out why there is no Kerberos authentication on my SQL instance :

SELECT COUNT (auth_scheme) as nb, auth_scheme --net_transport, client_net_address
FROM sys.dm_exec_connections
GROUP BY auth_scheme

  nb    auth_scheme

  2 (Unknown)

  151   NTLM

  4 SQL

Using KerberosConfigurationManager, requiered SPNs seems to be correctly and automatically registered:

Here's the error log regarding the SPN for the instance :

Could you provide ideas to investigate why there is no Kerberos authentication?

All the conditions list at this Microsoft Docs page have been met.

EDIT :

Cannot add the output because this is confidential info, but the instance is correctly registered :

  MSSQLSvc/hostname.domainName.fr:instanceName


texts:
Question about Index usage:

If I create a nonclustered index like

IX_LastName_Age

Where it is created by LastName ASC and Age ASC, and there is clustered index on ID field.

Now if I run a query

Select Age from dbo.user where Age > 10

Would the execution plan use "IX_LastName_Age" or will it scan from Clustered Index?


texts: Why isn't my count aggregate respecting my pivot columns?

texts:
In the pivot query below, why is every cell in a given row identical?

I was able to fix the query by replacing count(voo) with sum(voo), but I don't understand why count(voo) does not respect the AppVersion column.  I tried to follow Microsoft's Complex PIVOT Example, which also used Count.

select pv.EventDate, [VersionAA],[VersionBB],[VersionCC],[VersionDD]
from
(
    select ah.EventDate,ah.AppVersion,1 voo
    from AppHistory ah
    where EventDate > '2018-02-04' and reason = 1
) source
PIVOT(
    count(voo) --Fix: Replace "count(voo)" with "sum(voo)"
    For AppVersion in ([VersionAA],[VersionBB],[VersionCC],[VersionDD])
) as pv
order by EventDate

Example output with count(voo):

+------------+-----------+-----------+-----------+-----------+
| EventDate  | VersionAA | VersionBB | VersionCC | VersionDD |
+------------+-----------+-----------+-----------+-----------+
| 2018-02-05 |      1315 |      1315 |      1315 |      1315 |
| 2018-02-06 |      1200 |      1200 |      1200 |      1200 |
+------------+-----------+-----------+-----------+-----------+

Corrected output with sum(voo):

+------------+-----------+-----------+-----------+-----------+
| EventDate  | VersionAA | VersionBB | VersionCC | VersionDD |
+------------+-----------+-----------+-----------+-----------+
| 2018-02-05 |        12 |        18 |       300 |         3 |
| 2018-02-06 |        22 |        30 |       310 |         5 |
+------------+-----------+-----------+-----------+-----------+

My intent, which was met by sum(voo), was to to create a query which returned:
1) One row per day
2) One column per AppVersion (happily, the list of AppVersions is static)
3) Each cell contains a count of how many rows within AppHistory match the date and AppVersion of the column.

For reference AppHistory looks something like this:

CREATE TABLE [dbo].[AppHistory](
    [UserID] [bigint] NOT NULL,
    [AppVersion] [varchar](100) NOT NULL,
    [Reason] [int] NOT NULL,
    [EventDate] [datetime] NOT NULL,
    [TotalActions] [int] NOT NULL,
    [created] [datetime] NOT NULL
)

EDIT

I tried to reproduce the problem using a small amount of data and failed.  In fact, the behavior goes away if I run select * into #AppHistory from AppHistory where EventDate > '2018-02-04' and reason = 1 and then replace AppHistory with #AppHistory in my queries.  So now I'm even more confused.  CheckDB says the database is fine.

I honestly didn't think of this until now, but AppHistory is a bit crazy:
A) Has almost 1 billion rows
B) Has a partition scheme
C) Has a clustered columnstore index.

That said, creating a new table with the same partition scheme and columnstore index also failed to reproduce this behavior.  So, now I'm even more mystified.

Query Plans:
(Bad Result)Plan using Count
(Good Result)Plan using Sum


texts: Microsoft SQL Server Metadata Corruption

texts:
we have a bad incident right now.

One of our larger SQL databases has broken metadata.

unfortunately the warnings were ignored and they worked a week on the broken SQL database.

Is it possible to reconstruct this database in a trustworthy way?

What experiences do you had with this problem?

Thank you for your answers.


texts:
I'm using mongodb for saving log data.

When I have to find something from log data, which could be over than 1,000,000 documents, all the process work properly but the issue is that memory usage doesn't get down even though the process has been finished.

When I enter 'top' command on the server that runs mongod process, it shows resource information like below screenshot.

Memory usage for the mongod process was less than 10%, but after using a lot of find() queries, it increased to 38.9% and never decreasing. What causes this kind of issue and what do I do to decrease the memory usage of mongod process except restarting?

I didn't know that this question is not appropriate for stackoverflow site, but when I asked the same question in stackoverflow someone replied me like this.
"Of course it doesn't, it's a database. They are meant to consume memory and hold on to it"

But what I did with my code was just using .find() query, nothing was added to database. Does it mean that only using find() query could have caused increasing of  memory usage and not releasing it? I don't understand help me please


texts:
When we define a table, multiple times we assign default value of a column as NULL. I have

How NULL is different than empty value('') as well as 0?
Is there any impact of NULL value in query performance?
How it affects the index created on that column?

Thanks...


texts: What are drawbacks of setting REPLICA IDENTITY FULL in PostgreSQL table

texts:
I want to use the wal2json extension to capture the changes in the PostgreSQL database.
I need to find the diff between old and new rows.
Having searched the Internet for an answer, I realized that this can be achieved by setting the REPLICA IDENTITY FULL option on the table of interest.

I think this is exactly what I need, but I wonder are there any significant drawbacks of this setting?


texts: What permissions are required to view the contents of a catalog on a remote server?

texts:
I defined a linked server from a SQL Server box to a MySQL box.  I went through a very detailed set of instructions and steps, everything works like a charm when I am logged in.

The linked server definition specifies a MySQL account to access the MySQL database (all connections).

When I use my own (administrator) account to login, the links works great.  I can both query the tables and view a list of tables in the remote catalog.

When I login with a read-only account, I can run queries against the link, but am unable to view a list of tables (in SSMS).

What extra permission do I need to grant to this read-only SQL login in order to get it to fully work?

Update
I found a work-around:

exec sp_Tables_ex 'LinkesServername'

Works good enough.


texts:
I have a MySQL database running on AWS RDS. I also have a process which runs a SELECT query from a server called SOME_EC2.

  "SELECT command denied to user 'myuser'@'SOME_EC2_HOSTNAME' for
  table 'mytable'") [SQL: u'SELECT mydb.mytable.my_column AS my_column
  FROM mydb.mytable']

I can connect to the database using mysql client and then run the SELECT query from a separate server (more on this later) and also from my laptop using the following command.

mysql -u myuser -pMY_PASSWORD -h MY_DB_HOSTNAME mydb

But I get access denied if I use this command:

mysql -u myuser@'SOME_EC2_HOSTNAME' -pMY_PASSWORD -h MY_DB_HOSTNAME mydb

ERROR 1045 (28000): Access denied for user 'myuser@SOME_EC2_HOSTNAME'@'my_laptop'
(using password: YES)

Is this valid? Perhaps grants limited hosts.

So here is the output of SHOW GRANTS:

mysql> SHOW GRANTS FOR myuser;
+------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                  |
+------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mydb'@'%' IDENTIFIED BY PASSWORD <secret>                   |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `mydb`.* TO 'mydb'@'%' |
| GRANT SELECT ON `anotherdb`.`another_table` TO 'mydb'@'%'                           |
+------------------------------------------------------------------------------------------+
3 rows in set (0.02 sec)

Okay, so it should allow me to run SELECT from any host.

What do people think is happening here? Is hostname implicitly added in the first command (to match what was given in the SELECT error) so I can rule out hostname issue?


texts: Handling time zones in data mart/warehouse

texts:
We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and in books), a common solution seems to be to have a separate date and time dimension as well as a timestamp in the fact tables.

However, the question I am having hard time answering is what good does the date and time dimensions actually do for me considering my dynamic time zone requirements? A time dimension makes a little more sense but I'm having hard time with the date dimension. A general design approach for a date dimension usually includes properties such as day name, day of week, month name, etc. The problem I am having with all that is that 11:00 PM on Tuesday, December 31, 2013 in UTC is Wednesday, January 1st, 2014 in all time zones that are after UTC+2.

So if I will have to do all these time zone conversions on each and every query (and report) then what is the point of having and storing these properties that I will probably never use (seems like)? Some people suggest having fact rows for each time zone but that seems ridiculous to me. We need to be able to store millions of records each month.

Others suggest having a time zone bridge table which although makes some sense, it also seems like extra complexity and extra joins to accomplish something that my client apps and reports should easily be able to figure out from a date (reporting will be primarily web-based where there are a myriad of libraries to aide in converting, displaying and formatting dates).

The only thing I can think of is the ease and possibly performance of grouping by date and hour but how bad of a practice is to group by datepart (we're using MS SQL but we will be querying millions of rows) or should we consider just extremely simple date and time dimensions with not much more than hour, day, month and year numbers for the most part as most literals such as Monday wouldn't mean much when time zones come into play?


texts: Oracle Loop through values from one table and join with another table

texts:
I have a table (table1) with fields package_id and contract_id.

Data in table 1 is like that 1 package_id can have 1 or more contract_id associated with it.

I have another table (table2) with contract_id and status. status can be ('active' or 'dead')

I need to find the package_id which doesn't have any contract_id associated with it which is 'ACTIVE' or in other words I need to find package_id which has all contract_id associated with it in 'DEAD' state.

Please help.


texts: Is there a -w equivalent for isql when already connected?

texts:
The default output (text) width in sybase isql is 80. It can be changed with the -w flag when isql is started on a command line:

isql -w 200 -S... -U... -P...

Now, I am wondering if there is a possibility to change the text width when I am already connected. I would have expected something like

1> set columnwidth 200
2> go

or something similar. Yet, in the documentation I am unable to find something in this direction. So, does that mean that this is impossible or am I looking in the wrong place?


texts: MySQL InnoDB does not return the last row on select with DESC order

texts:
We have a table scheme like following:

CREATE TABLE IF NOT EXISTS `offers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `campaign_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `price` double NOT NULL,
  `ip` varchar(15) NOT NULL,
  `cdate` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `campaign_id` (`campaign_id`,`price`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin5 AUTO_INCREMENT=190514 ;

On each new offer by the user we check if the last order is given by the same user with:

"select user_id from offers where campaign_id='".$campaign['id']."' order by id desc limit 1"

if the user_id is same we prevent the new offer to protect the user from accidental double clicks.

if there is not any problem with the offer we insert the offer with:

"insert into offers(campaign_id,user_id,price,ip,cdate) values (".$campaign['id'].",".$user['id'].",'".$price."','".$_SERVER['REMOTE_ADDR']."',".time().")"

But the problem is the select only returns the last inserted row after about 1 second. That means the user can insert multiple offers if they click the button too fast.

We use 5.5.30-30.2-log Percona Server as our database server.
Following is our my.cnf file:

[mysqld]
datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-external-locking           = 1
skip-name-resolve
open-files-limit                = 40000
max_heap_table_size             = 64M
tmp_table_size                  = 64M
log-error                       = /var/log/mysqld.log
thread-cache-size               = 50
table-cache                     = 4096
table-open-cache                = 4096
table-definition-cache          = 512
query-cache-size                = 0
query-cache-limit               = 16M
query-cache-type                = 0
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 8M
join-buffer-size                = 1M
tmp-table-size                  = 64M
max-heap-table-size             = 64M
back-log                        = 100
max-connections                 = 10000
max-connect-errors              = 10000
max-allowed-packet              = 256M
interactive-timeout             = 360
wait-timeout                    = 360
innodb                          = FORCE
key-buffer-size                 = 32M
myisam-sort-buffer-size         = 4M
innodb-buffer-pool-size         = 60G
innodb-log-file-size            = 256M
innodb_log_files_in_group       = 2
innodb-log-buffer-size          = 4M
innodb-file-per-table          = 1
innodb-thread-concurrency       = 8
innodb-flush-log-at-trx-commit  =2
server-id                       = 1
slow-query-log                  = 1
slow-query-log-file             = /var/lib/mysqllogs/slow-log


texts: MySql - Update the defualt table schema on production server

texts:
The story starts with a MySql table called table on the production server:

As you can see, I f***ed up when specifying defaults - 0000-00-00 00:00:00 is not a valid MySql datetime.

I need to change all the Datetime Defaults => 1000-01-01 00:00:00

I also need to change some of the Null column values.

If I go ahead and run the script to do this:

ALTER table
CHANGE COLUMN
date_created DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00:',
date_lastmodified DATETIME NOT NULL DEFAULT '1000-01-01 00:00:00',
so on...;

Do I run the risk of corrupting data in the database?

MySql Version 5.0.4-beta-nt


texts: SQL Server Management Data Warehouse creates a lot of sleeping user connections

texts:
I am wondering if someone already experience this weird scenario regarding my SQL Server Management Data Warehouse; it creates a lot of user connections. Around 2500 connections which were all sleeping.

When I turned off the data collector, all the connections were disconnected but I am wondering what the problem is supposed to be.

My MDW Repo is running under SQL 2014 and is getting around 100+ instances.
The server that I am having a problem with is running SQL Server 2012 SP2 CU 12.


texts: How do you aggregate values from jsonb_array_elements into a separate list grouping by another column?


texts: Rolling count of total transactions over time

texts:
I need to get a set of total transactions over time on a weekly interval. Essentially I need a total-transactions-to-date column. When I group by WEEK(Date) I get the amount of transactions for that week, but need to also get the transactions from any time before that week as well.

So let's say I have a table like this:

TransactionID  | Date
---------------|-----------
             1 | 8-04-2014
             2 | 8-05-2014
             3 | 8-18-2014
             4 | 8-18-2014
             5 | 8-20-2014

I want to have a select statement that would provide me with something like

TotalTransactionsToDate | Week | Year
------------------------|------|------
                      2 |    1 | 2014
                      5 |    3 | 2014

I am using MySql 5.5.38


texts: What are the best practices for backing up a secured SQL Server 2012?

texts:
I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of.

I'm looking for some form of documentation that will tell me the steps I need to take/things I need to keep in mind to properly back up a secured (with a 3rd party certificate) SQL Server 2012.

What, other than the actual database information, do I need to send to my backup solution so I can ensure minimal downtime in a worst case scenario (knock on wood)?


texts: If the standby server is promoted to master, will it work without any replica?

texts:
If I have 1 master and 1 standby server, and my master server is down and the standby server is promoted to master. Does my new master work fine without a replica?
Does it act like a standalone server?


texts: How to keep an Oracle 11g instance lean?

texts:
As a Mac user I run a local Oracle Enterprise Linux (OEL) Developer Day VM that houses an Oracle 11g instance for development/unit testing.  I frequently refresh the data in the schemas (sometimes multiple times a day) and do not have a need for snapshots or backups.  The number of rows in each schema's tables are orders of magnitude smaller than those of a production instance.

I've had to extend the tablespaces' size more than a few times and I suspect I have improper resource allocation to begin with (SGA, PGA) for the way in which I'm using the instance.  I have a similar concern about my sizing/configuration of the VM itself since I'm only using it to house the instance, not for SQL Developer, TimesTen, etc..

I'm looking for recommendations/resources to help me

Optimize my database instance for a small amount of data in the first place
'Clean' my database instance so that I don't need to increase data file size
Modify my OEL VM to most efficiently run the only thing I
need it to run -- the Oracle instance

For example, I have turned off snapshot collection.  But I think there are other (more?) impactful changes I can make to help increase the performance and reduce the footprint of VM.


texts: How to represent multiple unique constraints across hierarchical tables?

texts:
My (shortened) table definition is:

CREATE TABLE bus
(
    id integer NOT NULL PRIMARY KEY,
    name character varying NOT NULL CHECK (name<>'')
);

CREATE TABLE network_node
(
    id integer NOT NULL,
    name character varying NOT NULL CHECK (name<>''),
    ecu integer NOT NULL REFERENCES ecu,
    bus integer NOT NULL REFERENCES bus,
    UNIQUE(ecu,name),
    UNIQUE(bus,name),
);

CREATE TABLE controller_application
(
    id integer NOT NULL,
    name character varying NOT NULL CHECK (name<>''),
    network_node integer NOT NULL REFERENCES network_node,
    address smallint NOT NULL CHECK (name BETWEEN 0 AND 253),
    bus integer NOT NULL REFERENCES bus,
    UNIQUE(network_node,name),
    UNIQUE(bus,address),
);

The relationship I am trying to represent in the database is:

A bus contains zero or multiple network_nodes
A network_node owns zero or multiple controller_applications
Indirectly controller_applications relate to exactly one bus.
An ecu contains zero or multiple network_nodes

The constraints I am trying to represent in the database are:

network_node names have to be unique per ecu
network_node names have to be unique per bus
The addresses of the controller_applications have to be unique on each bus

You will also see that there is currently redundant hierarchical information in the structure, as controller_applications and network_nodes both contain the bus field.


texts: When should you shrink your transaction log?

texts: We do log backups every 3 minutes but despite that the transaction log has still managed to grow to 200+ gb on disk.  Its almost completely empty after log backup.  How do you know if your transaction log has become too big and needs shrinking?  How do you determine the optimal size?


texts:
I exported a database to a transportable tablespace and send the data to a customer.  They received the error: ORA-28365 Wallet is not open.
Do I need to close the wallet before creating the export?  Is there something on the customer's end that they can do to allow them to import this data?

Haven't had this problem with any other customer since switching to 12c in the last 6 months so this is strange to me that this is the only customer having this problem?

Thanks for any insight!


texts: Snapshot replication - How to detect when a snapshot has arrived on the Subscriber from the Subscriber DB

texts:
I have snapshot replication set up on a server which publishes to a second server as part of a scheduled SQL Server Agent job. When the snapshot finishes, I would like to detect when each table has been updated or when all tables have been updated.

The best solution I have found so far is to put a flag into a placeholder table before the SQL Server Agent job which is replicated to the second server and then have a job on the second server that runs every minute to check to see whether a new snapshot has arrived.

Does anyone have a better solution or a way to identify when a snapshot has been processed onto a new server?


texts: How can I use Query notification and what are the requirement?

texts:
I want to use Query notification in my app to get the notification from database for updated data, so that app will fire the query to get updated data instead of monitoring the database for every 5 sec. I am using ms sql server 2012 express edition and Am using apache tomcat server to deploy my application(webMathematica), I use user custom tag of jsp to embed my application to tomcat server.

I need to configure client application to send notification request to database, but I have no idea about how to configure my client application to use query notification.

Suggest me good material to use query notification.


texts:
I have a list of positions:

pos
123
100024
1025
5426

I want to define a range of these positions

E.g.

100-1000

1001-2500

etc...

So when i do a Select of how many positions there are in that range counts the number of elements in the range.

I've tried with:

SELECT pos , generate_seres(max(pos)::int, min(pos)::int, 1000::int)
as pos_int
FROM varianti
group by pos
ORDER BY pos;

But i don't think that is correct...

Is there a way?


texts: Best design for a many:many relationship issue

texts:
We have three types of entity, each with their own table:

table:A(a_id), table:B(b_id), table:C(c_id)

Each of these entities can have any number of email addresses and linked Twitter accounts.

table:EmailAddresses, table:TwitterAccounts

What is the best way to relate these tables where the foreign key could be that of table A, B or C?

I considered this:

table:EmailAddresses(belongs_to_type,belongs_to_id), table:TwitterAccounts(belongs_to_type,belongs_to_id)

Where belongs_to_type would be A, B or C and belongs_to_id would be the respective foreign key.

So if A(1) has an email address, that entry would in the EmailAddresses table would look like EmailAddresses(A,1).


texts: PostgreSQL Upsert not working on Partitioned Table

texts:
Have a table like this:

CREATE TABLE aggregated_master (
  "user"       BIGINT,
  type         TEXT,
  date         TIMESTAMP,
  operations   BIGINT,
  amount       NUMERIC,
  PRIMARY KEY ( "user", type, date )
);

This table is the master from which a lot of partitions inherit.
The partitions are done by MONTH in the DATE field.
For example: Partition for Aug-2017 would be agg_201708 and it's PK would be pk_agg_201708
There is the usual trigger BEFORE INSERT to redirect the insert to the proper partition.

The thing is that I want to do an UPSERT into this table.
The DO CONFLICT part is not working.

The code first was like this

INSERT INTO aggregated_master (user, type, date, oeprations, amount)
SELECT user, type, date, SUM(ops), SUM(amt)
FROM ...
WHERE ...
GROUP BY USER, TYPE, DATE
ON CONFLICT ON CONSTRAINT pk_aggregated
DO UPDATE SET operations = EXCLUDED.operations
          ,   amount = EXCLUDED.amount

But then I noticed that the constraint (pk_aggregated) is the one on the master table, and not on the child table where the insert will really be performed, due to the trigger.

I changed the clause CONFLICT to:

ON CONFLICT (user, type, date)

Which are the fields of the PK, but this doesn't work either.

Any idea how to make this work ?


texts: Is there any point in undersizing VARCHAR columns?

texts:
Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not.

I would like to give the question of VARCHAR size a little twist and hope to get some insight in this:

Given (multiline) free-text fields (not short stuff like names) that you want to store in an (Oracle) database, is there any point (wrt. performance or otherwise) in not maxing out the VARCHAR capacity (VARCHAR2(4000) on Oracle) but choosing a smaller value such as 1024 or 512 because that will likely be sufficient in 98% of the cases anyway.


texts:
I am trying to join tables for a search form, and when I do the search exports the result x3 the output could you guys give me a hand please?

   $raw_results = mysqli_query($db,"SELECT * FROM inventoryuser, inventorypc
      WHERE (`firstname` LIKE '%".$query."%')
      OR (`lastname` LIKE '%".$query."%')
      OR (`department` LIKE '%".$query."%')
      ")
      or die(mysqli_error($db));

The output

I need to search inventorypc for pc_model and pc_serial that's under a different table.

My tables are:

CREATE TABLE `inventoryuser` (
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL,
  `lastname` VARCHAR(30) NOT NULL,
  `department` VARCHAR(30) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inventorypc` (
  `pcid` INT(11) NOT NULL AUTO_INCREMENT,
  `pc` VARCHAR(30) NOT NULL,
  `jcid` VARCHAR(30) NOT NULL,
  `job_number` VARCHAR(30) NOT NULL,
  `product_id` VARCHAR(30) NOT NULL,
  `pc_model` VARCHAR(30) NOT NULL,
  `pc_serial` VARCHAR(30) NOT NULL,
  `software` VARCHAR(30) NOT NULL,
PRIMARY KEY (`pcid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I the code I have is

  $query = $_GET['query'];
  // gets value sent over search form

  $min_length = 2;
  // you can set minimum length of the query if you want

  if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

      $query = htmlspecialchars($query);
      // changes characters used in html to their equivalents, for example:  0){ // if one or more rows are returned do following

          echo "

                    First Name
                    Last Name
                    Company Laptop
                    Company Mobile
                    Department
                    Actions

          ";

              while($results = mysqli_fetch_array($raw_results)){
              // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop

              echo "

                      ".$results['firstname']."
                      ".$results['lastname']."
                      ".$results['pc']."
                      ".$results['mobile']."
                      ".$results['department']."

                        View
                        Edit
                        Delete

                ";
              }

          }
          else{ // if there is no matching rows do following
              echo "No results";
          }

      }
      else{ // if query length is less than minimum
          echo "Minimum length is ".$min_length;
      }

      echo "
      ";


texts:
Given the situation below, can I specify that the pre-configured linked server login should not be used for a particular remote stored procedure call?

I have two SQL Server instances:

[LOCAL] running SQL Server 2012 with database [A] and
[REMOTE] running SQL Server 2008R2 with database [B].

[REMOTE] is already set up as a linked server on [LOCAL].  All users who can access [LOCAL].[A] can access [REMOTE].[B] (though not all its objects) through the SQL Server login GenericUser, which is already set up through a command on [LOCAL] such as:

EXEC sp_addlinkedsrvlogin @rmtsrvname  = N'REMOTE',
                          @useself     = 'FALSE',
                          @rmtuser     = N'GenericUser',
                          @rmtpassword = N'TheGenericUserPassword'

This login works fine.  For authorization reasons, however, I would like to restrict access to a stored procedure [dbo].[RemoteSP] on [REMOTE].[B] to one particular Windows Authentication user, [MYDOMAIN\OneUser].  [REMOTE].[B].[dbo].[RemoteSP] will be called from within a stored procedure [dbo].[LocalSP] on [LOCAL].[A], which [MYDOMAIN\OneUser] may execute (as can other users, who will not call [RemoteSP], as shown below).

The local stored procedure looks like:

CREATE PROCEDURE [dbo].[LocalSP]
    @Id INT
AS
SET NOCOUNT ON
-- Do some stuff, then:
IF SUSER_NAME() = N'MYDOMAIN\OneUser'
BEGIN
    -- This syntax is invalid:
    EXEC [REMOTE].[B].[dbo].[RemoteSP] @MyVar = @Id AS SELF
END

Access is granted (e.g., by GRANT EXEC ON [dbo].[RemoteSP] TO [MYDOMAIN\OneUser]) on [REMOTE], and the user [MYDOMAIN\OneUser] exists on [REMOTE] and in [REMOTE].[B].

Adding WITH EXECUTE AS 'MYDOMAIN\OneUser' to the [RemoteSP] definition, as this answer suggests, is unsatisfactory because I still need only [MYDOMAIN\OneUser] to have access to the stored procedure.  Although this question may be somewhat related, it is unanswered, and the linked chat room has been deleted.

I do not want to change the linked server login that [MYDOMAIN\OneUser] would use for all queries, just this one.  I also would prefer to avoid a syntax like:

EXEC (N'EXEC [B].[dbo].[RemoteSP] @MyVar = ?', @Id) AS LOGIN = N'MYDOMAIN\OneUser' AT [REMOTE]

if for no other reason than there is little reason to use a dynamic string -- I have the stored procedure name, etc.


texts: Select after an Uncommited Update in Python-MySQLdb

texts:
May be this is a newbie question but want to confirm as this is important for my app:

I'm using MySQLdb on Python and my code looks like:

db = MySQLdb.connect("localhost","root","",test)
cursor = db.cursor()

cursor.execute("UPDATE users SET address = 'Something' WHERE name = 'Someone'")
db.query("SELECT * FROM users WHERE address = 'Something'")
sample=db.store_result().fetch_row(maxrows=0)

So the question is, will sample contain the row that was changed by the uncommitted update?

In the real app, I am making some complex queries with subclauses like GROUP_CONCAT, etc. Will all these work with uncommitted updates? (In the sense that can these work on the rows updated by uncommitted updates)


texts:
The Change Tracking feature of Sql Server allows to identify the modified rows of a modified database copy.

This is obviously helpful for sync jobs, as the database can then be synced incrementally.

My question is: Is there a tool that uses this feature to actually do incremental syncs?

I know about

SSDT: can sync, but isn't interested in change tracking data as far as I know.
MS Sync Framework: is a framework, I'm looking for an application.

Do I really have to write something myself?

Is there perhaps some cool SQL script that does that if the source is brought in as a linked server?


texts: Will a clustered index speed up this query? (Inner join with a where clause)

texts:
I'm inserting data into a new table in a new database, based on a select to an existing table in an other database. The existing table has some 1500 million rows, and I am inner joining a table with about 10% of that. Both existing tables, are indexed on C5, while the the new table is indexed on C2, which is an unixtime column (I am going to do aggregates over time with the new table). This is taking horrendously long time. Its been running for over 3 hours, and the new table is just over 750 mb big, I expect it to be about 50GB when its filled up. This is also about 50% of the original table.

Is it because I'm indexing the new unixtime column?

Or could it be that I've not indexed the C8 column, that I use in my inner join? If I clustered C8 with C5, I'd probably get a faster inner join?

Here is the query:

INSERT INTO NewTable (C1, C2, C3, C4, C5, C6, C7)
SELECT OldTable1.C1, OldTable1.C2, OldTable1.C3, OldTable1.C4, OldTable1.C5, OldTable1.C6, OldTable1.C7
FROM OldTable1
INNER JOIN OldTable2.C5 ON OldTable2.C5 = OldTable1.C5
WHERE OldTable2.C8 >= 70 AND OldTable2.C8<= 89;


texts: Postgres - Get the query cost assign to a variable

texts:
To get the estimated cost of the query, i using EXPLAIN SELECT column FROM table;, to get the current cost of the query, i am using EXPLAIN ANALYSE SELECT column FROM table;, my question is how to get the cost of the query automatically, without having to run the explain for each query manually.

I need something like:

DECLARE cost integer;
DECLARE highercost integer;
DECLARE query text;

highercost := 0;
i := 0;
query = '';

WHILE i < array_length( queryarray ,1) LOOP

    cost := explain analyse queryarray[i];

    IF cost > highercost THEN

        highercost := cost;
        query := queryarray[i];

    END IF;

    i := i+1;

END LOOP;

The idea is to create a script to check the querys in a log and run in psql, or copy the log querys to a table in the database and run with plain SQL to verify the most expensive ones, at the moment is just what i seek, no need to worry about the real cost of the query ( "cost" X "times executed per minute"), cost of INSERT, UPDATE, and DELETE among other things.

I hope this is possible, if not, there is another way to search for expensive query without checking one by one?


texts: mysql assign different partition for specific DB

texts:
I am using mysql version 5.5.54-0ubuntu0.12.04.1 (Ubuntu) with few DB & one of the DB is growing very rapidly and eating up disk space.

I have mounted secondary disk & would like this specific DB only to use this new added disk.

can someone please provide some suggestions how it can be done? can I move single DB to new disk or I have to move all DBs / mysql to new ? how?


texts:
How can I retrieve the cto column using an @variable?

For example, for @Variable = '2019-03-02' the expected output is'MAR 1 - MAR 7'.


texts: Update matching keywords from first table to second table in SQL

texts:
I have two tables where 1st table is Englishgermankwds_tbl and 2nd is Kwd_UploadRecored the data of the tables is as follows

Table Englishgermankwds_tbl

English_Keywords                 German_Keywords
architecture                     Architektur
Arrival                          Ankunft
aspirations                      Lebensziel
attire                           Kleidung
Blueprint                        Technische Zeichnung
Carrying                         Tragen
caucasian appearance             Europäischer Abstammung
cheerful                         fröhlich
clothes                          Kleidung
color image                      Farbbild
day                              Tag
Development                      Entwicklung
differential focus               Geringe Tiefenschärfe
focus on foreground              Fokus auf den Vordergrund
front view                       Vorderansicht
Full Length                      Ganzkörperansicht
growth                           Wachstum
Happiness                        Glücklichsein,Glück
Hardhat                          Bauarbeiterhelm

Table Kwd_UploadRecored

ID  Primary_Kwd                             Sec_Kwd                                         Main_Kwd
1   Man,One Man,architecture,Boy            Arrival,Sigle Man , Business Man ,Male          aspirations,One Person
2   Woman,attire,Girl                       Girl,Girls,Female,Blueprint,Carrying,           Teenage Girl,Only Girls
3   Grand father,Man,caucasian appearance   cheerful, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,color image               growth,Babies,Child,Happiness                   Children,Toddlers,differential focus,

I want to update the all English keywords of Kwd_UploadRecored  to German  which is matched from the English keywords of  Table Englishgermankwds_tbl

I want the expected result like-  Kwd_UploadRecored table would be

ID  Primary_Kwd                                 Sec_Kwd                                         Main_Kwd
1   Man,One Man,Architektur,Boy                 Ankunft,Sigle Man , Business Man ,Male          Lebensziel,One Person
2   Woman,Kleidung,Girl                         Girl,Girls,Female,Technische Zeichnung,         Teenage Girl,Only Girls
3   Grand father,Man,Europäischer Abstammung    fröhlich, Family,Fatherhood,Family Member,      Male Parent,
4   Baby ,clothes,Farbbild                      Wachstum,Babies,Child,Glücklichsein             Children,Toddlers,differential focus,

Please help how to achieve this.


texts:
I have a RDS instance that has workload at specific time window, however, cpu utilisation is a constant 20% even outside this window.

There is no replication configured.

Can't find a solution for this case.

Spec:

The database has more or less 2GB with a 300GB of storage (900 IOPS)

The instance type is a db.t2.medium


texts: When does query data get aged evicted out of cache in MySQL query runs slow then fast then slow again

texts:
Running Virtual Machine -  5.6.19 MySQL Community Server on Linux

When does the data / queries get aged out of the buffer pool in MySQL? I have a dev server with 1GB was using default sized buffer pool - discovered default is really small.

On Dev VM MySQL Server, ran through a series of tests for a query.
The first time through it takes 4+ seconds. (slower disks)  Afterwards it takes under 100 milliseconds. But slow again later about a few hours later.

The same query in production VM MySQL is about 1+ second then sub 100 milliseconds. (It's on faster disks)

Normally I expect slowness first time due to slow disks, but remain fast after so wondering if data is aged out of cache too early. (For example in MS SQL there is a value called "page life expectency" that gives an esimate of how long data lives in cache)

So..
Increated the RAM and Buffer pool 3gb Ram/2.8GB buffer pool.  Restarted the MySQL Server ran the tests same thing occurs still.  Slow first, then fast, then later on slow again. There are not any large queries running on the dev server.  I check the process list in mysql

But...
Same results

I fairly this is attributed to slower disks, developers have indicated the Server has gotten slower and slower over time.  Our SAN has filled up some 70%+  capacity...while year before 45%+ of capacity.

I am looking for things I can do to keep developers more at ease.
Is there any type of monitoring to help?
Any recommened mysql tools to check queries also?
(still learning all of them)

I ran an "explain" and query is using indexes.
The tables are roughly 300K records in table A, 75k for tableB,tableC
It's pretty small using indexes.

Thanks for suggestions.


texts:
The plan attached should not take more than a minute to run, but sometimes it takes hours. The I/O on the two outer clustered index scans balloons out of control and the query crawls. Can someone explain to me why this is happening and how to fix it?

https://www.brentozar.com/pastetheplan/?id=Hyp7qKFEx


texts: A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

texts:
I have the following tables in my postgres database

- power_relations        - (id, properties)
- power_relation_members - (id, power_relation_id, member_id, member_type)
- power_lines            - (id, geom)
- power_nodes            - (id, geom)

A power_relation can have multiple members which can either be power_nodes or power_lines. I use power_relation_members to track this polymorphic many-to-many relation.

The query I need to write has to return all the power_lines that belong to power_relations that contains power_lines matching a condition.

For example, if I have a

powerlines:

id, geom
----------
1, abc
2, d
3, g
4, j
5, m
6, abc

power_relation_members:

member_id, power_relation_id
----------------------------
1, r_1
2, r_1
3, r_1
4, r_2
5, r_2

power_relations:

id, properties
--------------
r_1, "relation1"
r_2, "relation2"

In the above data, line 1 and 6 have the geom = 'abc'. Lines 1 - 3 are related to relation 1, lines 4-5 to relation 2 and no relation for line 6.
I would want to get all the power_lines that are related (via power_relation to the power_line whose geom is abc. That means, I need lines 1, 2, 3 as results from my query.

I could achieve my result in two steps:

Get the distinct id's of power_relations that have power_lines that match my condition
Get all the power_lines that are related to the power_relations whose id is in the ids fetched from step 1.

Instead, I wanted to do this all in one query by doing the step 1 in a subquery. This includes joining the same tables in the subquery and its outer query. This is how my query looks and it gives me the results as I expected:

SELECT DISTINCT l.id FROM powerline l
JOIN power_relation_members m
ON (l.id = m.member_id AND m.member_type = 'way')
JOIN power_relations r
ON (m.power_relation_id = r.id
    AND r.id in (
        SELECT DISTINCT s_r.id FROM powerline s_l
        JOIN power_relation_members s_m
        ON (s_l.id = s_m.member_id AND s_m.member_type = 'way')
        JOIN power_relations s_r
        on (s_m.power_relation_id = s_r.id)
        WHERE s_l.geom = 'somestring'
    )
);

You may see the simulation in the fiddle here http://sqlfiddle.com/#!15/2493b

I'm not very familiar with how the database's perform and I would like to know if this single query is optimal compared to the two step individual queries or if there is a better and optimal way to achieve the same.

Thank you.


texts: Can I exchange partition between two table with partition with same name?

texts:
I am trying  to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table partition x. x is the name of the partition.

I have tried to do it by using an auxiliary table. Is there a way to exchange them directly or any other solution with better performance?


texts: Help me build a join condition based on a time period in a many-to-many relationship

texts:
I have a certain problem and I wonder if you could help me.
There are 3 tables (it's a somewhat simplified view and the column names aren't real obviously):

table1 contains info about employers, the columns are:

    id int PK,
    site_id int,
    company_name varchar(whatever),
    ceo_name varchar(whatever),
    ceo_start_date date,
    ceo_end_date date

table2 contains info about employees, the columns are:

    id int PK,
    token_id int,
    employee_name varchar(whatever),
    employee_activity varchar(whatever),
    employee_activity_date date

table3 joins the two above (many-to-many relationship), the columns are:

    id int PK,
    site_id int,
    token_id int

The table1 can be joined with the table3 by means of the site_id column.
The table2 can be joined with the table3 by means of the token_id column.
I need to make a report which shows contents of the employee_activity, but broken down based on the term of office of the ceo_name.
The term of office of the ceo_name is defined by the ceo_start_date and ceo_end_date and the employee_activity_date column can be compared
against that period to find out under which CEO the activity was performed.
I'd appreciate any help. I'm struggling with constructing the correct join condition.
Also I'm talking about SQL Server platform as a main tool, but mySQL is also an option.
Thanks in advance


texts: MySQL select data over time that matches pattern over several rows

texts:
We currently log data from our plants running remotely for diagnostic purposes on MySQL.

I have been asked to come up with a typical running profile for several variables. For this, I would require to get a pattern where, say, our plant is running in mode 400 for atleast 2 hours (we currently have the datetime column as primary key).

Would it be possible to come up with a query that says "show rows where plant is running in step 400 for more than 2 hours"?

Thanks :)

EDIT: Sorry, but one more query to add to that, is it possible to search where a step change occurs, like "search for * where value changes from 0 to 100"?

EDIT2:
From a comment - an example would be:

datetime             plant_status incomer_power
2014-09-04 16:16:57     222     -67.06115
2014-09-04 16:17:27     222     -67.5517
2014-09-04 16:17:57     222     -68.0331
2014-09-04 16:18:27     222     -67.99048
2014-09-04 16:19:27     400     -158.7233
2014-09-04 16:19:57     400     -158.9488
2014-09-04 16:20:27     400     -159.0716

This is only a small snapshot of the data. But this data goes on for days. I would like a query, that would bring up the rows when plant_status changed to 400 (only 1 event in case of this example @ 2014-09-04 16:19:27).


texts: Setting drive Allocation unit size in virtual envionrment

texts:
Environment details:

One physical host disk (RAID 10)
Multiple virtual servers of varying types (app, web, SQL, file) all using same host disk
using Hyper-V

Questions:

Is it still beneficial to set an 'allocation unit size' of 64k on SQL VM's logical drives, if the physical host they use is set at 4k and cannot be changed?
Could mis-alignment between virtual logical drives and physical disk actually
cause performance degradation?


texts: Why is the query_hash zero for EXEC statements?

texts:
I'm setting up a monitoring system for SQL Server using Extended Events to find heavy queries as 'production feedback' for our developers. I'm using the events sp_statement_completed and sql_statement_completed, with predicate filters on cpu_time, logical reads, etc.  I was hoping to aggregate the results on database_name and query_hash like demonstrated in numerous examples all over the internet, but in the results I see that query_hash is 0 for all statements using EXEC, like in the table below (timestamp and queryhash shortened for readability).

name                       timestamp      query_hash plan_handle        statement
sql_statement_completed    2016...6414    0          050056019600764... exec Shared.dbo.SyncFirm
sql_statement_completed    2016...9946    0          06003d00e01e730... exec spSetUserAuth @userid;
sql_statement_completed    2016...7184    0          0600e30028c9da0... exec spSetUserAuth @userid;
sp_statement_completed     2016...0409    9826...578 0600c00028e6aa0... SELECT obfuscated_columns FROM dbo.SomeTable
sp_statement_completed     2016...1448    8660...775 060084006d2d660... INSERT INTO dbo.SomeTable ( obfuscated_columns)  EXEC(@sql)
sql_statement_completed    2016...7752    0          0600f9006c23f03... exec spSetUserAuth @userid;
sql_statement_completed    2016...1443    1304...641 06005a0008a9b11... select SUBQ.ontrackstatus, COUNT(SUBQ.ontrac

All results do have a value for plan_handle and they're all different, so a lot of plans are being generated. Other statements without query_hash (that I've seen) include ALTER INDEX, CHECKPOINT, UPDATE STATISTICS, COMMIT TRANSACTION, FETCH NEXT FROM Cursor, some INSERTs, SELECT @variable, IF(@variable = x).

Does anybody know why the query_hash is 0? I'm probably missing the point somewhere about the SQL Query Analyzer and EXEC, but I'm not able to find any clues to point me in the right direction. If the results I'm having are 'normal', then how to best aggregate the results?  Wouldn't grouping by statement include literals, whitespace, etc... which is removed when calculating query_hash?

EDIT: as I see it now, EXEC SomeStoredProcedure, starts a stored procedure (obvious), and the individual statements in that stored procedure end up in the event session as sp_statement_completed events, and those all have a query_hash.

So for sp_statement_completed (i.e. 'real' queries), I can aggregate on query_hash and database_name, and for sql_statement_completed without query_hash (the EXEC SomeStoredProcedure), I can use the client_connection_id to group the statements within a specific execution of a stored procedure, to see what's the most costly part of the procedure.


texts: Removing/ Undoing Database addition to Alwayson Groups

texts:
I am about to create a new database for an AAG group, and I want to know in case of a fall back plan where the data synchronization fails, would removing the database from the AAG and then dropping it from the primary would suffice? Please advice

Thanks!


texts: Working of window functions and idea window size for window function

texts:
I am not able to understand the concept of window functions. How exactly they work and what are the pros and cons of such technique?

I have read that using limit and offset is slow but its still used. So decision between limit/offset and window functions depend upon size of data to get from the db or window functions should always be used?

What is ideal window size to get maximum performance from this? Can it be the case where if number of rows to get is less then simply use limit/offset?

My use case basically requires sorting data on date and then bring the latest data first. How window functions fit in this scenario.


texts:
I have a series of tables in my database that are using DATE as the primary key. I would like to reconfigure the tables so that an AUTO_INCREMENT ID field is the primary key.

What is the best way to change the tables so the first record ID in each table start at 1 and auto_increment by 1 up to, and beyond, the latest records?

Subsequently, two of the tables do not have data going back as far as the other tables so I'd like to have their starting ID fields begin at a certain integer and auto_increment from there.

MySQL version: 5.5.45
Engine: MyISAM
Interface: phpmyadmin


texts: Pass 'interval' value to date_trunc function in PostgreSQL procedure

texts:
I am fetching the "age" of two timestamp columns (End and Start) and further choosing it as to_char 'MM' format. So I have the difference of the months from two timestamp columns.

The syntax in the above statement goes like :

i = (select to_char(age(End,Start),'MM'));

interval_value = (select i || ' month');

Also tried: interval_value = i || '   Month'

Now,

Passing the value of 'i' in another function date_trunc of PostgreSQL.

xyz = (select date_trunc('month',Start::date) + interval_value::text::interval);

The data types for the above variables are:

i numeric :=0

xyz date;

interval_value varchar;

But it doesn't seem to work.

Is there any alternate approach I could use here. The main idea is to get the difference from two timestamps in months and then further passing the difference into the date_trunc function.


texts: Moving a very large (40 TB) DB from a 2008 server to a 2016 server

texts:
The Situation:
Our 2008 MSSQL server is old and rapidly running out of drive space.  We have a new 2016 server ready, we just need to move a 40 TB database from the old server to the new one.
The DB, of course, has dozens of tables, but the EVENTS table is the one that is eating up all the space.  The EVENTS table is partitioned by month, and the partitions are spread out on multiple logical drives (we kept adding disks until we physically ran out of space).

The Question:
How do we move this massive database from the old server to the new server with a minimal amount of down time?  Outside of not upsetting the users, the table grows by about 15 GB per day, so every day spent offline creates a significant backlog of records that needs to be worked through.

The backup/restore method doesn't really seem feasible, as we have no place to store the backup.

Is it possible to move a partition at a time? (My research so far leads me to believe it is not)

We are a bit overwhelmed by the task, and any advice would be appreciated.


texts: Cannot remove Database from Distributed AlwaysOn Group on Secondary Windows Cluster

texts:
We have Distributed AG across two windows clusters.

Clus01 - Has an AG set up with no DB inside it (AG1)
Clus02 - Has an AG set up with no DB inside it (AG2)
Clus01 - Has a DistAG set up with AG1 (primary) and AG2 (secondary)

We

Joined Clus02 to the DistAG
Added a database to AG1 on Clus01
Restored this DB in recovery to AG2
Added this DB to AG2 on Clus02 (Showing as Primary, but really is secondary within the DistAG)

DB on Clus02 was now working as expected...any updates on DB on Clus01 would use DistAG to update Clus02 DB.

We often refresh this environment from live (this is preprod).  so we have to restore the DB onto CLus01.

For our normal AGs, we just remove the DB from CLUS01, restore to CLUS01, backup and restore to CLUS02 and join to AG again.

For DistAG, I cannot

Remove the DB from AG2 on CLUS02.

ALTER AVAILABILITY GROUP [AG_G2CoreReporting]
REMOVE DATABASE [Genesis];
GO

We get the error

  Msg 41190, Level 16, State 8, Line 6
  Availability group 'AG2' failed to process remove-database command.  The local availability replica is not in a state that could process the command.  Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.

Remove the DB from AG1 on CLUS01.  DB on AG2 on CLUS02 goes into not syncronizing state and I cannot remove (same error as above), so cannot restore DB over the top.
Remove the replica on the DistAG by running on CLUS01

ALTER AVAILABILITY GROUP [DistAG]
REMOVE REPLICA ON N'AG2';

We get the error

  Msg 15151, Level 16, State 1, Line 5
  Cannot alter the availability group 'AG2', because it does not exist or you do not have permission.

Is there anyone that knows how I can refresh an environment with DistAG without having to remove the whole DistAG and set it up again?


texts: Best way to store information about a list of materials and their quantity

texts:
I want to store some information about a game. Specifically information about certain buildings that can be built in it.
Said buildings require materials to be built. I am trying to come up with a good DB model for storing that information in my database.
For example, one building needs 10 wood to be built. I thought about having a "Buildings" table with columns for ID, name, icon and material cost. However I'm usually used to referencing common values from another table. This is to say I would have a separate table named "Materials" with columns ID, name and icon and the "Buildings" table would have a column "MaterialsRequired" which would hold a reference to the ID of the needed material in the "Materials" table. But I have no idea how to do that (which is a best practice, afaik) AND store the needed amount of said material.


texts: Is it possible to have read/write race conditions in a percona cluster?

texts:
I had a discussion today regarding an incident in a percona cluster. The setup was as follows:

A three node percona cluster load-balanced by a haproxy. Both reads and writes where balanced by the proxy to all three nodes.

The application (php) using the database, was coded as such, that it could perform a write on the database, and then immediately try to read the new record. The guy told me that there were cases that, if the immediate read (after the write) was balanced by the proxy to a different node than the one used for the write operation, it would not locate the record.

Is this possible in a percona cluster? As far as I know, the node that executes a write query, it must first receive confirmation by all percona nodes prior returning success to the client issuing the query. So how is this possible, even if the read query lands on a different server than the one used to perform the write operation?


texts: SQL Server Metric Report Information For Management

texts:
We have a quarterly metric meeting and I report the metrics for all of our production SQL Servers.  The people that are looking at these reports are mostly management (some technical, but others are not so much) and want to see a nice overall picture of the performance of the SQL Servers, but I really don't want to give the generic metrics of CPU, number of db, disk space usage, etc.

Currently, I have some numbers like average batches per database, restore time for databases and I do have the number of databases (even though I said I really didn't want that one).

Any suggestions on other metrics that may be interesting to show managment that would be easy for them to understand and actually show the health of the SQL Servers?


texts: Counting the number of calls of a stored procedure

texts:
I have a CLR sp in SQL Server 2008 R2, and I want to count how often it is called, in order to create a statistic on data quality. (The sp allows manual correction of data).

How can I let a counter go up everytime the CLR sp is called? Do I necessarily have to change the sp itself?

Suggestions are appreciated.


texts: Is it viable to connect RethinkDB to SQL Server?

texts:
I'm adapting a nodejs project for a client.

This project uses RethinkDB as it's database server, obviously NoSQL, it serializes data from Android systems, such as state, battery life, software isntalled, etc.

Now, my client would like to have this data on SQL Server for reporting purposes.

I thought about refactoring the app to use it on SQL Server, but it's quite a refactoring, we'd like to cut developing times, so, from my pov, there two possible ways to accomplish this:

Connecting the RethinkDB to SQL Server and from there make the reports
Find a way to create them from RethinkDB itself.

Note that we are working on a Windows Server 2012, I've installed RethinkDB there.

I hope I've explained myself.

Any ideas?

Thanks in advance!


texts: Stuck REDO Thread of an availability group

texts:
We are facing issues with REDO queue fluctuating as we have readable secondary configuration.
Based on my understanding in newer versions of SQL Server, redo threads have been made parallel. Current version is SQL Server 2017 running on VM with 64 cores.
I am seeing problems when select queries on the secondary run on the same cpu/scheduler where AG threads are running. I am assuming AG thread yields its scheduler and takes a back seat to let other queries run. And I believe this is the problem because queries coming in seem to be hitting same cpu_id where AG threads are doing redo work.
Example below: spid 91 is select query on cpu_id 6 and at same time spid 123 and 144 running threads for AG's as found from sys.dm_exec_requests dmv. REDO queue starts building up when 91 came up.

I am not aware if there is any process which makes your queries go to different cores rather than one where AGs thread might be running.

Can we control this to make queries hit other schedulers and not one
where AG working?

I see lots of waits on PARALLEL_REDO_TRAN_TURN when queries running on secondary while AG threads are suspended. Can trace flag help as mentioned here

There might be some many other schedulers available. I am not able to understand why certain process is stuck in slot of Numa. I guess newer versions have all soft numa enabled so that makes bucket of 8 soft numas. 8*8 schedulers for 64 available. Is disabling soft numa a good idea here?

Database isolation is RCSI but I don't think that matters here because internally it's changed to snapshot based on design of readable secondaries.


texts: Getting full (purpose, usage) Table names in SAP Hana

texts:
Each table in SAP Hana has a purpose, for example, BSEG is Accounting Document Segment and BKPF is Accounting Document Header.

Is there anything in Hana (along the lines of Select * From Tables) or a full list elsewhere on the Intertubes, that will get me the purpose\usage of each table in Hana?

I'm trying to put together a Workflow for Alfred that'll allow me to look up the definitions and it would be nice if I can get it to tell me "Look Up BSEG (Accounting Document Segment)" as I'm searching inside the workflow.

I've already got the list of names, just really could do with that purpose\usage if possible!


texts: InnoDB ibdata1 corrupted, innodb_force_recover not working

texts:
I think I have checked every post in the internet.
Thing is my MySQL (MariaDB) won't come up and after much testing I have seen it is because of the InnoDB files.
I have tried with different innodb_force_recover and also with mysqld_safe, also with online tools and with every tut I have found.
I'm pretty desperate at this point, so any kind of help is welcome.
The log that matters is this one:

2018-11-27 15:00:16 140309240685120 [Note] InnoDB: innodb_empty_free_list_algorithm has been changed to legacy because of small buffer pool size. In order to use backoff, increase buffer pool at least up to 20MB.
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: The InnoDB memory heap is disabled
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Compressed tables use zlib 1.2.8
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Using Linux native AIO
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Using SSE crc32 instructions
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Completed initialization of buffer pool
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Highest supported file format is Barracuda.
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: The log sequence number 1154517884 in ibdata file do not match the log sequence number 1345427345 in the ib_logfiles!
2018-11-27 15:00:16 140309240685120 [Note] InnoDB: Restoring possible half-written data pages from the doublewrite buffer...
InnoDB: Page directory corruption: infimum not pointed to


texts: Set permissions on all objects for SQL users

texts:
I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to create new databases so I have to run this every time I create a new database. Here is an idea of what it looks like (note: there is a chunk missing from the beginning that drops all users and recreates the necessary users; this is necessary since the SID's change):

CREATE PROCEDURE usp_SetPermissions
AS
 BEGIN

DECLARE @CurrentId INT
DECLARE @ObjectName NVARCHAR(128)
DECLARE @Message NVARCHAR(160)
DECLARE @Error INT
DECLARE @Sql NVARCHAR(256)

CREATE TABLE #tmpDbObjects
(
   ID INT IDENTITY(1,1),
   ObjectName NVARCHAR(128),
   Completed BIT
)

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed
FROM sys.objects
WHERE [type] = 'U' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
 BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME())
    EXEC sp_sqlexec @Sql

    -- Update object completion
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

INSERT #tmpDbObjects(ObjectName, Completed)
SELECT DISTINCT [Name], 0 As Completed
FROM sys.objects
WHERE [type] = 'P' AND is_ms_shipped <> 1

WHILE EXISTS (SELECT 1 FROM #tmpDbObjects)
     BEGIN
    -- Pick first uncompleted object
    SELECT TOP 1 @CurrentId = ID,
                 @ObjectName = ObjectName
    FROM #tmpDbObjects

    -- Grant permissions to DB user
    SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME())
EXEC sp_sqlexec @Sql

    -- Update object completion
    DELETE #tmpDbObjects
    WHERE [Id] = @CurrentId

    -- Clear variables
    SET @Sql = NULL
    SET @CurrentId = NULL
 END

This scripts continues on to do similar things for all the views, functions, etc. in the database. Any ideas for speeding this thing up or is there a better way to do this?


texts:
What is the difference between using First..Last and 1..count in Oracle?

FOR i IN l_collection.FIRST..l_collection.LAST LOOP

or

FOR i IN 1..l_collection.COUNT LOOP


texts: Can I change database compatibility level from 80 to 90 or higher with log shipping?

texts:
I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance.
Many backups (like full, differential, and log) will be taken while using the new compatibility level 90 or 100. This database is also configured with log shipping.
My question is can I change compatibility level back to 80 safely without threats or must I take any considerations?


texts: What is the difference between PostgreSQL 9.0 Replication and Slony-I?

texts: Is there still any use for Slony-I, and if so, what is it? For clarification, from 9.0 Postgresql supports built in streaming replication.


texts:
I'm modelling data with multiplicity like this:

Each Composition/Anthology related pair must share a Composer. Also, each Anthology must contain at least one Composition. How would you recommend I model this?

Here is one possible representation with nearly enforced consistency (it doesn't enforce the 1+ Composition : 0+ Anthology multiplicity). However it duplicates FK_Composer lots of places (which as a side annoyance breaks some of my Entity Framework features).

Composer    Composition    junction          Anthology
--------    -----------    --------------    ---------
                           FK_Anthology   -> PK
PK       <- FK_Composer <- FK_Composer    -> FK_Composer
            PK          <- FK_Composition

Note: I'm also trying to hack this out at the business logic and ORM layers and have hit obstacles there as well.

https://stackoverflow.com/questions/16273702/model-entity-framework-many-many-plus-shared-relation
https://stackoverflow.com/questions/16312984/custom-entity-framework-many-to-many-navigation-property


texts: Azure SQL - dataspace used vs max dataspace

texts:
I have a single Azure SQL database in Azure with the following database data space

As I read the documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management the "Used space" will keep growing, and I myself need to shrink it. That is perfectly fine, and as expected. I can also see that the allocated space is a bit larger than the used space. This is how i hope it works:

when used space is nearing the allocated space, some more space is allocated
when used space is at max size we have no more space available thus we run out of space

I am a bit scared that I have misunderstood something and my database (and site) will stop working when used space is at allocated space. I am kind of new into the world of Azure.

It was something i just noticed today. I have a monitor set up to monitor data space used (in percent), and i will be alerted when it hit max. But i will never hit max if it doesn't grow beyond allocated space.


texts: Can lowering fill factor result in more page splits?

texts:
We've been having a problem with page splits on a table that is a particular nuisance - its an audit log of activities in the database and has grown over 1TB.  The main indexes are on the record type, which is an NVARCHAR(100) - because you need that when there are 5 record types - it makes much more sense than a TINYINT, and a record id - which is an NVARCHAR(200) instead of the integer key of the record.

They are also covering indexes, with the key, old value, new value, etc - very wide.

This is an old system, and unfortunately the code for this auditing is everywhere rather than centralized in a procedure. It can't be changed, and we're going thru the painful process of a long micro-services re-write.

So, I have reduced the fill factor on two of the indexes from 100% to 85%.

And the page splits have gotten worse. I'd say around 3x more page splits.

Is this a common outcome? Most advice says to reduce fill factor to improve page split performance.  I can understand why it may do this, because of the width of the data in the keys.

Would the advice be to reduce the fill factor further, or to put it back to what it was?


texts:
Let me start out by clarifying that this is not a duplicate question, nor a potential duplicate for that matter. I have tried implementing every answer to every single variant that already exists of this problem on StackOverflow and DBA Stack Exchange, without any luck.

I've been struggling with this problem for the past two days (working on it for approximately 7 hours a day), and even after Googling the issue, it appears that no one else are having the same exact variant of my problem.

What am I trying to do?

In SSIS, I am trying to read from a CSV file, and insert the rows from it into an OLE DB database. For that, I have made the simplest setup ever, seen below.

Flat File Source - reads the CSV rows.
Derived Column - currently does nothing (it's just there for experimenting).
Data Conversion - currently does nothing (it's just there for experimenting).
OLE DB Destination - stores the rows in the database.

When I try to run it, it stops executing at my OLE DB Destination with the following error-message.

  There was an error with input column "Amount" (187) on input "OLE DB Destination Input" (51). The column status returned was: "The value could not be converted because of a potential loss of data.".

The column that fails (Amount) is currently of type DT_STR. It appears to be the type that I have the most faith in right now.

What have I tried?

I've tried using the Flat File Connection's "Suggest Types" function on the column that is failing. This made it recommend the Single byte signed int datatype.

Stops at my Flat File Source.
The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried using the Derived Column to cast the column into a DT_I4.

Stops at my Derived Column.
The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried using the Data Conversion to cast my column's value to a DT_I4.

Stops at my Data Conversion.
The error is Data conversion failed. The data conversion for column "Amount" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

I've tried changing the length of my DT_STR value in the source and the destination.

Stops at the source or the destination depending on the settings.

I've tried connecting using an Excel Source connector instead with and without the IMEX=1 added to the connection string. No luck.

My programming sense tells me that I am screwed. I have never ever encountered such a big problem for such a simple thing.

And why is it that I can't just ignore the "potential" loss of data? It's really frustrating to say the least.

My system

It's a Windows Server 2008 R2 machine with SQL Server 2008 installed. The machine itself is fully updated through Windows Update.


texts: sql command utility version is not working

texts:
I have two versions of SQL Command Utility installed on my system as mentioned below(I checked the versions by going to Control Pannel->ProgramsandFeatures).

Microsoft Command Line Utilities 11 for SQL Server
Microsoft Command Line Utilities 15 for SQL Server

However, when I go and check the version in command prompt by typing 'sqlcmd -?' it shows the Version 12.0.2000.8 NT.
Could anyone advise why the system is not taking the version 15 and how it will utilize version 15? Since I want to use '-G' switch but it is not available in the current installed version (12.0.2000.8 NT).


texts: Transferring concepts - users/groups/permissions of db objects from mysql to postgresql

texts:
This seemed like a better fit here than stackoverflow, so here goes:

I've been using mysql for a while since on most of the cheap webhosts it is the provided database. However, now I've started seriously developing web apps I've been using postgresql locally for my database, with a view to using it properly on the clients installations once I'm comfortable.

The only thing I've not yet grasped (of the basics, of course) is the concept of users/groups/permissions. In mysql, I create users and I can GRANT them permissions on the various tables I wish them to have access to.

How does this concept work in Postgres? Does it? On my local machine I've been allowing the user apache access to my tables so that my django apps can work, via:

echo "GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE $table TO $2"
echo "GRANT SELECT,INSERT,UPDATE,DELETE ON TABLE $table TO $2" | psql $1
echo "GRANT SELECT,UPDATE ON SEQUENCE ${table}_id_seq TO $2"
echo "GRANT SELECT,UPDATE ON SEQUENCE ${table}_id_seq TO $2" | psql $1

(part of a bigger script)

Is this secure? I'm pretty certain per-app users sounds like a more sensible idea, but then I don't know.

So, I guess that makes my questions:

How do users/groups/permissions work in postgresql?
How do they compare to mysql?
What is considered best practise?


texts: How can I manage Primary Key length limit of 900 bytes when using hirarchyid 2 columns?

texts:
I have to define a Primary key on a table which has following 4 columns and its data types.

GSiteID hirarchyid,,
SiteID varchar(10),
GComID hirarchyid,
ComID nvarchar(10)

As we know there is limit of 900 bytes when we design any key on a table. whereas hirarchyid is a variable data type consumes 892 bytes so naturally my above case exceed limit of 900 bytes. In some of the posts I read we can use ToString() and other binary formatter to reduce the size of key length.

Can any one explain me or refer such article which shows this case where I can use above two alternatives of ToString and binary formatter which is more compressed to define my key? If someone provides me exact syntext to form my above four field as key in the same seq.

It would be helpful to me. I have already seen examples illustrating use of tostring() into select claues but I want explicit example of define a unique key or index key for hirarchyid so I can manage my key in 900 bytes limit.

As I know that max 10 level is max my tree will evolve so please consider this also while providing suggestion.


texts: How to most efficiently find out if a record has child records?

texts:
I am writing a query that returns a single record from the parent table. I would like to also return in this query if it has any children. This is a one to many relationship.

parent:
 -parent_id
 -name

child:
-child_id
-name
-parent_id

My first instinct is to write the following query:

select name, (select count(child_id) from child c  where c.parent_id=p.parent_id) children
     from parent p
     where name like 'some name'

But I was wondering if there was a more efficient way to do this, since I don't actually care about the count, just whether or not it has children. Any pointers?


texts: How do you grant SQL Server 2016 user-permissions to access local files?

texts:
I'm trying to develop a SSDT sln, where a .csv is parsed. However I get the error:

Operating system error code 5(Access is denied.)

According to this post: https://stackoverflow.com/questions/14555262/cannot-bulk-load-operating-system-error-code-5-access-is-denied it's a question of adding users. That specific user didn't exist on my local machine. I tried adding ALL the security principles from my Windows 10 machine and it worked. So one of the list of about 60 or so principles is the correct user to give access to SQL Server 2016 but I would prefer not to go through one by one...

This answer: https://social.technet.microsoft.com/Forums/Windows/en-US/58992456-ec8a-41e8-825b-1e56a075073a/sqlservermssqlusercomputernameinstancename-account-do-not-create-by-default?forum=sqlsetupandupgrade mentions a different user, but I can't find that user either. There are multiple users in the form MSSQLSERVER0x (I think I have the R server installed). But adding the group these users are part of doesn't help either

==== EDIT ====

It worked when I added the 'Users' group - which has 3 members:

my login user
NT AUTHORITY\Authenticated Users
NT AUTHORITY\INTERACTIVE

None of these principles appeared in the list of principles that I could add directly to folder permissions. What are no 2 and 3? And why didn't the security settings tab find ALL the users that were listed in the Local Users and Groups\Users\ folder (because it listed some that were)?


texts: Create Drop Temporary Table very slow vs Truncate

texts:
I've got this stored Procedure that is called by an event that runs every 1 minute.

The stored procedure selects some data from one device and puts it in a temporary table.
Than it picks up other comparable devices and puts that data into another temporary table.

Unfortunately I have to use cursors as I have to go through that data back and forth at times.

I Solved this issue by creating this solution which works well. But I have a problem with transactions which is due to truncating the temporary tables.
As we all know mysql forces a commit WHEN using truncate on a table (even on temporary tables). I've been looking all morning for a solution for this as I just had a deadlock and some data wasn't rolled back.

So and after making some tests I can see the solution would be to do create and drop tables instead of doing truncates. So I made a stored procedure that compared creating inserting dropping a temporary table against truncating inserting one hundred times each. The result was 9seconds against 125ms.

This is obviously not acceptable in a stored procedure already taking between 1 seconds and 10 seconds at times.

Please note the main table where the data is fetched in the first place is very big and grows very fast (several millions rows). Comparing data on the main table is not an option.

Did anyone came across something like this? If so how did you solve it? Thank you.


texts: How to simplify a carry over values process?

texts:
I have created a "sample case" here for Postgres 10.0 (actually I am using AWS equivalent for 10.1):

https://www.db-fiddle.com/#&togetherjs=EKTJ6eQ62V

where you can find the table:

create table test(l text, v1 integer, v2 real, monthly_date date);

insert into test
    values
    ('a', 2, 1.3, '2001-01-01'),
    ('a', 1, 2.2, '2001-02-01'),
    ('a', 5, 6.2, '2001-04-01'),
    ('b', 3, 9.0, '2001-03-01');

The expected output is:

l   v1  v2  monthly_date
a   2   1.3 2001-01-01T00:00:00.000Z
a   1   2.2 2001-02-01T00:00:00.000Z
a   0   2.2 2001-03-01T00:00:00.000Z
a   5   6.2 2001-04-01T00:00:00.000Z
a   0   6.2 2001-05-01T00:00:00.000Z
a   0   6.2 2001-06-01T00:00:00.000Z
b   3   9   2001-03-01T00:00:00.000Z
b   0   9   2001-04-01T00:00:00.000Z
b   0   9   2001-05-01T00:00:00.000Z
b   0   9   2001-06-01T00:00:00.000Z

In a monthly time range that goes from '2001-01-01' to '2001-06-01', if there are missing following months, they get filled with the previous month values. The only difference is in the column 'v1' where the value for the missing month gets replaced with 0.

The query that I am using at the moment is:

WITH
    md AS (
      SELECT *,
      LEAD(monthly_date) OVER (PARTITION BY l ORDER BY monthly_date) AS next_date
      FROM test
    ),
    calendar AS (
      select interval_date::date
      from generate_series('2001-01-01'::date, '2001-06-01'::date, '1 month'::interval) interval_date
    )
    select T.l, coalesce (m2.v1, 0) as v1, T.v2, T.interval_date as monthly_date
    from (
        SELECT m.l, m.v1, m.v2, c.interval_date
        FROM calendar c
        JOIN md m
            ON c.interval_date BETWEEN m.monthly_date AND
            (CASE WHEN m.next_date IS NULL THEN '2001-06-01' ELSE m.next_date - '1 month'::interval END)
    ) T
    LEFT JOIN md m2 ON m2.l = T.l AND m2.monthly_date = T.interval_date;

which works fine but it is using a "LEFT JOIN" to inject the value '0' for the column 'v1'. Is there a better (possibly more efficient) way to do that?


texts:
SELECT MAX(COUNT), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count
    FROM `library`.`lms_book_details`
    GROUP BY supplier_id
) GROUP BY A.id;

While executing the query, MySQL gives an error:

  Every Derived table must have its own alias.

How do I add an alias to this?


texts: Does Galera Cluster rsync only updated files or all files

texts:
We're currently setting up a galera cluster using rsync for sst.

The datadir is currently 20Gig and right now we're only updating about 4 (very large) tables.

I know that rsync normally only transfers file changes also that galera has rsync setup to ignore timestamps.

If we start sst, does rsync transfer the whole 20gig or only the files for the tables that have been updated?

Thanks


texts: Unable to configure oracle 11g net server

texts: I installed Oracle 11g on my Windows Server 2008 workstation. After I try to configure oracle net server with port number 1521. But ONS error say to me as "Use another port number. This port is already in use.". How can I solve this problem?


texts: How PostgresSQL stores duplicated VARCHAR values in a column

texts: I have a VARCHAR(255) column in a table with about 50 million rows. In that column there is only 10 unique values, which act as labels. I'm wondering whether Postgres does something smart and stores only those 10 values and just references them in each row or is the data stored as 50 million values?


texts: Why do some write sometimes go faster than expected for MEDIUMTEXT

texts:
We are running a MySQL Innodb database, our server has 4gb memory.  We are trying to improve the speed at which a 800KB XML File is stored in a MEDIUMTEXT column.  Currently, we have this query

CREATE DEFINER=`username`@`localhost` PROCEDURE `API_LogApiRequest`(sRequest
  MEDIUMTEXT, sResponse MEDIUMTEXT)

BEGIN
   DECLARE sUUID VARCHAR(36);
   SELECT UUID() INTO sUUID;

   INSERT INTO ApiRequest
   (
       Id,
       Request,
       Response
   )
   VALUES
   (
       sUUID,
       sRequest,
       sResponse
   );

   SELECT sUUID AS 'UUID';
END;

The sResponse data can be as much as 800kb, and we are seeing it takes the following on 10 repeat calls.  These calls were all made with the same data.

  0.687 sec

  0.078 sec

  0.188 sec

  0.718 sec

  0.047 sec

  0.031 sec

  0.610 sec

  0.313 sec

  0.141 sec

  0.032 sec

Here is the Innodb status

INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 0, seg size 2, 560 merges
merged operations:
insert 560, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 49919, node heap has 1 buffer(s)
0.00 hash searches/s, 23.83 non-hash searches/s

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 25772032; in additional pool allocated 0
Dictionary memory allocated 130139
Buffer pool size   1536
Free buffers       1024
Database pages     511
Old database pages 0
Modified db pages  11
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1902256, not young 73968112
3.33 youngs/s, 52.77 non-youngs/s
Pages read 23821066, created 4126940, written 19043946
25.28 reads/s, 6.89 creates/s, 41.11 writes/s
Buffer pool hit rate 903 / 1000, young-making rate 12 / 1000 not 204 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 511, unzip_LRU len: 0
I/O sum[3805]:cur[242], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 7228, state: sleeping
Number of rows inserted 1340396, updated 304, deleted 1287534, read 12921295
2.78 inserts/s, 0.00 updates/s, 1.33 deletes/s, 4.11 reads/s

Here is my table structure

CREATE TABLE `ApiRequest` (
 `ApiRequestId` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
 `Request` mediumtext COLLATE utf8_unicode_ci NOT NULL,
 `Response` mediumtext COLLATE utf8_unicode_ci NOT NULL,
 `EventTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ApiRequestId`),
  KEY `IDX_EventTime` (`EventTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

What do I need to change to improve the performance?


texts: Postgres function/procedure won't return value

texts:
I'm new to PostgreSQL, coming from SQL server and I'm having trouble creating a function and/or procedure. First of all, may I just clarify that the only difference between a function and a procedure is indeed the support for transactions?
Now going back to my problem, I want a simple func/proc that returns the current connected user. Not using it anyhow, so any other example would work, it's only for learing the syntax as pgsql is different from t-sql. Here is my code, which I took from a tutorials website:
CREATE OR REPLACE FUNCTION dev.get_user()
RETURNS VARCHAR AS $user$
DECLARE
    $user$ VARCHAR;
BEGIN
    SELECT current_user AS user;
    RETURN user;
END;
$user$ LANGUAGE plpgsql;

I'm getting this and I don't know why:
ERROR:  syntax error at or near "VARCHAR"
LINE 4:  $user$ VARCHAR;

If I remove line 4, it creates it successfully, but when I run it, it returns this error:
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

I'm on pgsql 12.3, if it makes any difference. Thank you.


texts:
I've postgresql 9.4.

I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation.

But anyway, I can't understand in a simple way what's the right way to have a master postgresql server, and a slave postgresql server that is updated from the master (as soon as possible, to reduce data loss).

Also I would need a way to do automatic promotion of the slave to master in case of failing master.

Should I use Repmgr? Or I should just use Postgresql's functions?

Thanks


texts:
I freshly installed Oracle XE 11.2.0.2.0 and found out that the TNS Listener service fails to start. At first I tried to start the Windows service by hand. After the failure this is the entry in the event log:

<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
 <System>
  <Provider Name="Application Error" />
  <EventID Qualifiers="0">1000</EventID>
  <Level>2</Level>
  <Task>100</Task>
  <Keywords>0x80000000000000</Keywords>
  <TimeCreated SystemTime="2012-12-18T07:11:23.000000000Z" />
  <EventRecordID>6195</EventRecordID>
  <Channel>Application</Channel>
  <Computer>[removed]</Computer>
  <Security />
 </System>
 <EventData>
  <Data>tnslsnr.exe</Data>
  <Data>11.2.0.2</Data>
  <Data>4e2e9556</Data>
  <Data>orahasgen11.dll</Data>
  <Data>11.2.0.1</Data>
  <Data>4ce506a3</Data>
  <Data>c0000005</Data>
  <Data>0002fbdd</Data>
  <Data>1084</Data>
  <Data>01cddceee2b90e19</Data>
  <Data>C:\oraclexe\app\oracle\product\11.2.0\server\BIN\tnslsnr.exe</Data>
  <Data>C:\oraclexe\app\oracle\product\11.2.0\server\BIN\orahasgen11.dll</Data>
  <Data>20df4569-48e2-11e2-87b0-1c6f650a0fc2</Data>
 </EventData>
</Event>

In a second go I tried STATUS and START in LSNRCTL.EXE:

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   32-bit Windows Error: 61: Unknown error

... and START:

LSNRCTL> START
Starting tnslsnr: please wait...

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   32-bit Windows Error: 109: Unknown error

Google found a few forum entries with people having a similar error but all of them would either have an inner error different from 109 or not get any reasonable answer at all...

Any ideas? I know that before installing Oracle XE on my computer, an oracle 11.2.0 client was already on it (part of the image).

ORACLE_HOME and TNS_ADMIN point to that client. Would that have any impact? I hope the two Oracle instances are smart enough for each to run independently!

Update: It does! I changed the ORACLE_HOME and now the listener runs. But I can't go and edit the ORACLE_HOME every time I need to start the listener or run SQLPlus from my client installation. I really wonder what an architecture would install a product with its own listener but the listener is not able to run without a global environment variable being set to a certain directory, especially since we know there can be more than 1 oracle installations on one system...


texts: SQL Select that inserts a new row based on a delimiter

texts:
I have a table with the following sample values:

ID VALUE
1 |856|421|
2 |123|795|7125|
3 |96412|85251|2245|
4 |4845|88422|9155|15154|
5 |1165|98742|

How can I do write a select statement where the result will be:

    ID Value
    1 |856|
    1 |421|
    2 |123|
    2 |795|
    2 |7125|
    3 |96412|
    3 |85251|
    3 |2245|
    4 |4845|
    4 |88422|
    4 |9155|
    4 |15154|
    5 |1165|
    5 |98742|

The only constant is that each group of numbers are always included between | characters. Any ideas?

We need to preserve the pipes '|'.


texts: Can/Should rarely used columns be placed in a different table for performance?

texts:
I have a table that contains data (blobs). Data is often read, rarely inserted, and never modified.

For all of this data, I compute hashes that I also store in the DB. These hashes are only checked when inserting new data, therefore, they are hardly used in normal, day-to-day routine.

My question is, is it worth the trouble to make a separate table for the hashes, is it reasonable to expect any performance gain for putting them aside? Or can I confidently just add an extra column to the table for the hashes, even though the hashes are rarely used?

(Although I would be interested in a general answer, I must point out that I use MariaDB and sqlite).


texts: mysql 5.6 Unknown system variable 'optimizer_trace'

texts:
I'm trying to follow this tutorial to trace optimizer that is used by mysql for certain query.
But when I do

SET optimizer_trace="enabled=on";

It said ERROR 1193 (HY000): Unknown system variable 'optimizer_trace'.
When I check, the optimizer_trace table isn't in information_schema db.

I had build mysql from source version 5.6 because in that page stated:

"In MySQL 5.6, a new tracing capability was added to the MySQL optimizer. The interface is provided by a set of optimizer_trace_xxx system variables and the INFORMATION_SCHEMA.OPTIMIZER_TRACE table, but is subject to change."

So my quiestion is:
Is the OPTIMIZER_TRACE table had already been changed?
If it is, is there any substitution for this table?
Or maybe should I build another version?


texts:
Oracle has some packages that will dump objects in SQL format, such as dbms_metadata.set_transform_param.  One option that can be set is to "pretty print" the SQL.

Is there anything in Oracle that will pretty print a random SQL query string?  Something like:

select pretty_print('select * from emp;') sql from dual;

sql
---
'select *
   from emp;'


texts:
My Joomla mysql database has about 3.4 million rows in content table. It is just static article. Very slow and often use out of resources. To speed up it, I segmented it into 6 sub-tables and added many indexes.

However, the problem still there and some times more serious. Current situation:
Memory used out, could not connect to database, table locked
500 and 404 errors reported by Google webmaster tool 16000 times per day,
Some pages load quickly but some pages load slow or not show(query long time, 10 minutes, or 30 minutes more)

My VPS is host gator level 7.

Please give me some advices on how to change the situation.


texts: How to get all roles that a user is a member of (including inherited roles)?

texts:
Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest".

create role authors;

create role editors;

create user maxwell;

create user ernest;

grant authors to editors; --editors can do what authors can do

grant editors to maxwell; --maxwell is an editor

grant authors to ernest; --ernest is an author

I would like to write a performant function that returns a list of the roles (preferably their oid's) that maxwell belongs to, something like this:

create or replace function get_all_roles() returns oid[] ...

It should return the oids for maxwell, authors, and editors (but not ernest).

But I am not sure how to do it when there is inheritance.


texts: Strategies for rotating backend production MySQL data into a test environment

texts: I've been assigned a task where I need to replicate a web application for creating a test environment, and part of the project is to create a test database with sample production data to help testing. I'm no database expert, and I haven't been able to find any solid strategies online for rotating data. All I know for sure is that I would need to spin up another MySQL instance on our DB server and connect the new instance to that, but how do I go about getting actual data out of our production DB without harming production data? Are there any well-known strategies I can deploy?


texts: how to stop ms access 2016 from connecting back to microsoft?

texts:
I have a small program which compacts access database using DAO COM api. The program works, except around 50% of the time hangs on exit for about 15 seconds. So I have decided to investigate and try to understand why.
What I have discovered is a https connection originating from the program back to ip addresses belonging to MS. Wireshark shows a dns lookup for nexus.officeapps.live.com.
If I fire Access app, I get the same behavior, dns lookup followed by connection to MS
If I downgrade DAO provider from DAO.DBEngine.120 to DAO.DBEngine.36, there is no network activity.
If I block the network (disable network in VM), the timeout is gone and of course no connection.
I also tried to tweak Access settings. In Trust Center/Privacy Options, both checkboxes are off.

Is there any programmatic or configuration way to stop access calling back home?

The program should be used in corporate environment, so there is internet connection but the direct connection will be blocked. I can't switch to Jet4, some of the sample files I have don't compact with older version.

Here is the program for the reference:

import os
import win32com.client

# DAO_ENGINE = "DAO.DBEngine.36"
DAO_ENGINE = "DAO.DBEngine.120"

def compact_db(db_filename):
    tmp_filename = db_filename + "-compacted"
    try:
        os.unlink(tmp_filename)
    except FileNotFoundError:
        pass
    print("compacting ", end='')
    try:
        engine = win32com.client.Dispatch(DAO_ENGINE)
        engine.CompactDatabase(db_filename, tmp_filename)
        print("done")
    finally:
        if engine is not None:
            del engine
    os.replace(tmp_filename, db_filename)

compact_db("db.mdb")


texts: Should Azure host write-caching be enabled when using MongoDB's WiredTiger storage engine

texts:
I have several virtual machines on Azure running a mongoDB replicaSet with the WiredTiger storage engine. I'm not sure about the proper host caching option to set on the disks that contain the MongoDB data.

Azure docs regarding disk caching state the following:

  By default, the OS disks have ReadWrite caching enabled. We have recently added support for ReadWrite caching on data disks as well. If you are using ReadWrite caching, you must have a proper way to write the data from cache to persistent disks. For example, SQL Server handles writing cached data to the persistent storage disks on its own. Using ReadWrite cache with an application that does not handle persisting the required data can lead to data loss, if the VM crashes.

I use disk snapshots to backup the replicaSet. I am unsure as to how mongo manages write caches and prefer not to take the risk of getting inconsistent data in my snapshot or data loss when a vm crashes, so I disabled write caching on the disks containing the Mongo data.

Today I ran into the following note in the Mongo documentaion, under Production notes referencing Azure:

  For all MMAPv1 MongoDB deployments using Azure, you must mount the volume that hosts the mongod instance’s dbPath with the Host Cache Preference READ/WRITE. This applies to all Azure deployments running MMAPv1, using any guest operating system.

I assume that if MMAPv1 knows how to handle host write-caching and flushing, WiredTiger can do the same without risking inconsistency. The production notes don't mention WiredTiger though.

Are disk snapshots of MongoDB data consistent when using WiredTiger storage engine and Azure host write-caching to the data disk is enabled?


texts: Why does the column content say “Array”?

texts: I got this problem when I'm doing mysql that every time that I post pictures on the blog it will say to the database that I got array on image column. it is supposed to have the image that I inserted