texts: SysAdmin server role and Active Directory Groups

texts:
I've been struggling to find best practices information or any advice regarding Active Directory Groups that provide group members with the SysAdmin server role. I'm at a new job where I'm the only SQL Server DBA for 50 - 100 instances of SQL. There is one other (network) system administrator that will have the SysAdmin server role. There may be others in the future as well (may or may not be DBA's).

I'm picturing two options:

Option 1: create a group in Active Directory, add myself and any others to the group as needed, create SQL Server logins on each SQL Server instance for the AD group, and give the logins the SysAdmin server role.

This is convenient, and allows some flexibility. But it seems like a huge security risk. As a DBA, I don't have full control over who is in (or out) of the AD group. Currently, each SQL Server instance has a handful of logins with the SysAdmin role. I want to take SysAdmin away from all of them except for me, the network system admin, and sa. If I accomplish this task and implement Option 1, I end up no better than where I started from.

Option 2: use individual Windows Authentication (AD account) logins on each SQL Server and give each the SysAdmin server role.

This is not so convenient, but only two people/AD logins have SysAdmin privileges. As long as neither of us grant SysAdmin role membership to others, there should be a certain level of order.

Which option is preferred?
Are there other options I've overlooked?


texts:
I have a website that runs perfectly normal, until today it becomes very slow. After a few hours debugging, it all points down to database.

I ran these queries

show status like 'Connections';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 78    |
+---------------+-------+
1 row in set (0.01 sec)

show status like "%thread%";

+------------------------------------------+-------+
| Variable_name                            | Value |
+------------------------------------------+-------+
| Delayed_insert_threads                   | 0     |
| Performance_schema_thread_classes_lost   | 0     |
| Performance_schema_thread_instances_lost | 0     |
| Slow_launch_threads                      | 0     |
| Threads_cached                           | 3     |
| Threads_connected                        | 6     |
| Threads_created                          | 17    |
| Threads_running                          | 6     |
+------------------------------------------+-------+
8 rows in set (0.01 sec)

show variables like "%max_connections%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.02 sec)

Version :
+----------------------------------+
| version()                        |
+----------------------------------+
| 5.6.35-1+deb.sury.org~xenial+0.1 |
+----------------------------------+
1 row in set (0.00 sec)

What i found out is, when ever the # of connections above 20, the site becomes very slow to load.

I am not sure why this is happening, or whether this is normal. I tried to adjust the 'max_connections' variables but still no luck. Has anyone ever got this problem as well? How can i optimize this ?

Thanks for the help.

Here's a log related to Connections:

+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 1     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 48    |
| Max_used_connections                          | 6     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 6     |
+-----------------------------------------------+-------+
14 rows in set (0.01 sec)


texts: SQL Server 2012 Express Report Services report builder button not visible

texts: I am trying to run SQL Server 2012 Express Report Services on Windows 7. I finally have it configured, but I'm not sure the missing steps that is preventing the Report Builder button from being visible. My desktop only has one account (administrator) and does not require any password when booting up, so I'd presume that when I launched the Report Services through an IE browser via, localhost/Reports, that I'm logged in as an administrator. Is the button not showing up because of some role is not defined and not tied to my desktop admin account?  Below is the display of the Report Services without the Report Builder button.


texts:
Is it possible in PostgreSQL 9.6 to apply a foreign key constraint on the lower and upper bounds of a range (of integers in my case)?

Currently I keep the lower bound and upper bound in two columns, but would like to unify them into a range column while keeping the foreign key references.

Example

I have a table containing a list of sessions, something like:

CREATE TABLE sessions (
    session_id    SERIAL      PRIMARY KEY,
    session_start TIMESTAMPTZ NOT NULL,
    description   TEXT
);

and then a table of groups of those sessions which is currently expressed as:

CREATE TABLE session_groups (
    group_id      SERIAL  PRIMARY KEY,
    first_session INTEGER NOT NULL UNIQUE,
    last_session  INTEGER NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (first_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (last_session)
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,

    CONSTRAINT last_session_after_first
         CHECK (last_session >= first_session)
);

What I would like:

CREATE TABLE session_groups (
    group_id      SERIAL    PRIMARY KEY,
    session_range INT4RANGE NOT NULL UNIQUE,
    description   TEXT,

    FOREIGN KEY (lower(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT,
    FOREIGN KEY (upper(session_range))
                REFERENCES sessions (session_id)
                ON UPDATE CASCADE
                ON DELETE RESTRICT

    -- Dropped last_session_after_first constraint since
    -- the INT4RANGE type checks it automatically.
);

which is throwing a syntax error.


texts: Approach to take regarding a field that is frequently re-computed

texts:
I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text.

Now, the way in which this text is processed is expected to change over time so that at some point a new version of the processed text would have to be stored in the database. We would keep the newest and previous versions of the processed data, but stop inserting new data into the previous version.

I see a number of options:

Create a table for each version of the processor which stores the text and reference the main table. Drop table as they become redundant.
Add new column to the existing processed table and delete the oldest column.
Clear the oldest column and then insert new data into it.

I am wondering what the best approach is here.

As a sidenote - what would be the requirement on using a vacuum etc.

I am using PostgreSQL 9.4 on Debian and the number of rows are around 56 million and continuously increasing.


texts:
This is my first question so please bear with me !

We have an external company whose developers need to login into an instance on our on-premise SQL Server 2016.

They have AD logins, and these are in a distribution group that has access to the relevant instance.

I've created a Self Certificate, installed it to SQL server and encrypted the connection using it, and even installed it on my laptop (which is off our Domain).

To test the situation I use a local laptop login, VPN to our network, and run SSMS using 'runas /netonly /user.....' with the user being a domain user having the same rights as the remote developers.

When I try to connect I get

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

even though the certificate is trusted.

If I tick Trust Server Certificate in the SSMS connection options I get "Login failed for user ''"

I'm thinking we need to buy a Certificate - but if anyone has come across a similar situation with a different resolution please let me know !

Thanks.


texts: ORA-12899 when inserting from sqlplus, but ok with jdbc

texts:
Inserting 'ééééé' into a column defined as varchar(5) runs fine from squirrel sql client (jdbc).
But doing the same insert from sqlplus fails with ORA-12899 (too long value).

NLS_CHARACTERSET is set to AL32UTF8
NLS_LENGTH_SEMANTICS is set to CHAR

It look like sqlplus is not honoring these settings...

Where should I look ?


texts: We are using SentryOne to monitor the servers and noticed something very peculiar. When looking at the same server data but with SentryOne software running in different timezone, the data seems to be shifted and out of sync. I was wondering if this is due to some setting somewhere but couldn't find anything. Anyone know how to fix this.. or if this is a bug?


texts: Best way to defrag/compact a database for archival purposes

texts:
We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this quarterly in the past, but we're looking to do it monthly now. The amount of data being archived is about 15 - 20 GB per month, and the bulk of the data resides in only a handful of tables (usually 2 - 4).

Once we roll over to a new database, the old one becomes used on a strictly read-only basis. What I'd like to do is optimize it into a nice, tight data file, with all the tables/indexes contiguous and having a very high fill factor, and not much empty space at the end of the data file. Also, we're using Standard Edition on this server, with all the limitations that implies (otherwise I'd be using data compression already).

A few possibilities I can think of:

REBUILD/REORGANIZE indexes, DBCC SHRINKFILE (Okay, this isn't a sensible option, since DBCC SHRINKFILE will fragment the piss out of anything it touches, but I'm including it for completeness.)
Create a new database with auto-stats off. Script and recreate all tables from the source database. Use bcp to export/import the data into the new database, in cluster-key order. Script and recreate all indexes. Recalculate all statistics with full scan.
Create a new database with auto-stats off. Script and recreate all tables from the source database. Use SSIS or T-SQL to transfer data to the new database. Script and recreate all indexes. Recalculate all statistics with full scan.

The final step in every case would be setting the database to read-only mode.

What other good/better options are there for doing this? My concern is moving the data over in such a way to preserve a high fill factor, and in a logically contiguous fashion.

Edit:

I should mention that about 75% of the data seems to be stored in image (LOB) columns.


texts: Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement

texts:
Within an Oracle procedure, after opening a cursor for a select statement, I fail to find a mean to count the number of rows fetched.

OPEN mycursor FOR
SELECT * FROM TABLE;

-- mycursor%ROWCOUNT is always set to 0, even if the cursor has rows.
IF mycursor%ROWCOUNT = 0
THEN
  <error processing code here>
END IF;

This is expected, as documented at Oracles's documentation website:

  The OPEN-FOR-USING statement associates a cursor variable with a
  query, executes the query, identifies the result set, positions the
  cursor before the first row in the result set, then zeroes the
  rows-processed count kept by %ROWCOUNT.

So, except for running a second, redundant 'select count(*) from table', are there any other means to find out the number of rows within the cursor?

EDIT #1:  I am not looping after loading the cursor; it is returned as is to the calling procedure.  However, I must raise an exception if there are no row.  These are the specifications.


texts: Calculate total size of transaction log backups in the last 24 hours

texts:
I wanted to get the total backup sizes in KB, MB and GB for each database on an instance. I have pieced together the below but haven't been able to do the sum part.

SELECT
   A.[Server],
    A.DATABASE_NAME,
   A.last_db_backup_date,
   B.backup_start_date,
   B.expiration_date,
   CAST(CAST(b.backup_size / 1000 AS INT) AS VARCHAR(14)) + ' ' + 'kB' AS bkSize,
   CAST(CAST(b.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'mB' AS bkSize,
   CAST(CAST(b.backup_size / 1000000000 AS INT) AS VARCHAR(14)) + ' ' + 'gB' AS bkSize,
   B.logical_device_name,
   B.physical_device_name,
   B.backupset_name,
   B.description
FROM
   (
   SELECT
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       msdb.dbo.backupset.backup_finish_date AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset
       ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE   msdb..backupset.type = 'l'
   --GROUP BY
   --    msdb.dbo.backupset.database_name
   ) AS A

   LEFT JOIN

   (
   SELECT
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size,
   msdb.dbo.backupmediafamily.logical_device_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'l'
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
ORDER BY
   A.database_name


texts: How can I partition records vertically across machines?

texts:
I recently asked two questions: (1) how to store and connect partial records in two or more machines, with the record halves correctly associated, and (2) how to determine the increase in time/space complexity due to the use of remote databases/multiple machines. The responses didn't really address the heart of the issue, so I'd like to clarify my questions:

First, I'm not asking if I should do this but rather the best way to do it. For example, I want to store a huge family tree with info such as date of birth (DOB), place of birth, mother's maiden name, citizenship/immigration status, ethnic/religious affiliation, and so on for each member, but I want to partition that info across databases (and machines) to reduce the amount of personally identifiable information (PII) exposure that would occur through the breach of a single machine.
This security measure is not replacing the standard safeguards of encryption, etc. but rather is in addition to them. The issue is that databases with those safeguards already in place have still been hacked (recent examples are the Anthem breach, OPM breach, etc.), so, clearly, "standard" isn't enough.
Data replication is not an option; I do not want any server to store the full data from the records at any time. A proxy server may aggregate the data during a query, but at no time should any server actually store the full record. The records queried will obviously be stored at the client side, but if a client is hacked, the only records exposed are those actually queried, not the millions of others that a server might store.

So I'm looking for the best, most efficient way to partition the data found in a record while ensuring that all parts of the record are linked in a query. Example: I create personal records containing DOB, place of birth, mother's maiden name, citizenship status, ethnic/religious affiliation, and medical information, but only the DOB and citizenship status are stored in the tables contained in database 1. Then database 2 contains tables with medical information and mother's maiden name, and so on. The databases are on separate servers so that if one machine is hacked, only that part of the record is accessed.

As a client, if I pull a record, I want to acquire all information about a single entity, but as noted above, I don't want to store all of the info of any record in a single database or server.

Does anyone have suggestions for the best ways to do this? I'm also interested in how to test the increase in time/space complexity due to the use of remote databases/multiple machines.


texts: Retreive data from two tables of the same structure

texts:
I have a SQLite database where I fetch data from two tables with the following query.

select ie.*
from (select *
      from History where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from
      union all
      select *
      from Pending where Station = @station and TimeStampCome <= @till and TimeStampCome >= @from
     ) ie
order by TimeStampCome desc LIMIT 100 OFFSET 1

Is this the best and most efficient way? I have stored alarm objects in the database. So there can easily be more than 1 million entries.


texts: What does this error mean "A slave with the same server_uuid/server_id as this slave has connected to the master;"?

texts:
Upon searching for this, the main cause of this error is usually when using snapshots or cloning the machine. But I did not do this. I perform mysqldump --master-data=2 on the master then restore the dump on my slave server. Then start the slave on the position indicated on the outfile.
Any idea on what caused this? I am running a multi-source replica and this error is from just 1 source.
here is the complete error:
Got fatal error 1236 FROM MASTER WHEN reading DATA FROM BINARY LOG: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'VM1-bin.000006' at 641809774, the last event read from '/binlogdata/VM1-bin.000009' at 146836375, the last byte read from '/binlogdata/VM1-bin.000009' at 146836375.';


texts:
Environment: Windows 8.1, SQL Server 2008 R2 Express database, SQL Server 2008 R2 BIDS
Database: Performance counter data collected on client servers. The particular values involved in this question are % Processor Time and Batch Request/sec.

I did not start this report from scratch. I am actually just modifying one Allen White provided from his TechEd 2010 session that you can find here. I am using the same table structure with a few modifications to column names and such for my liking, pretty much the same other than that. I also added some filter/parameters into the report because I have multiple servers in this database.

I take the RDL file and import it into my SSRS project. Modify the procedures and column names to match my setup. I noted the format of the graph to be a simple line graph:

The border for each series is set to a solid line style with 2pt line width, no shadow. I don't see why I don't get a pretty line versus this. Is there some format setting I am missing? It has been sometime since I have tried setting up graphs in BIDS.


texts:
I'm doing training and one of the scripts has the following command:

SELECT SUM(Col2) FROM clust_table WHERE Col1 % 3 = 1

I would like to know what this snippet is for in the WHERE clause: Col1 % 3 = 1

I did some research on the internet and found no references about this command.


texts:
How can I compare 2 phrases in PostgreSQL, omitting word-forms? I tried to test Full text features:

select ts_rank(to_tsvector('eat apple'), phraseto_tsquery('eating apples'))

I expected to get rank 100%, but got 0.0991032. Is there any other type of queries to check that "eat apple" completely matches "eating apples"?


texts: Is there a way to throttle MySQL requests to mitigate DoS traffic?

texts:
I've a server with ~200 wordpress websites hosted. When Google Favicon bot pays us a visit it searches all the sites indexed on our IP, that means it's requesting ~200 favicons. Most of the sites doesn't have one, so the request is passed to wordpress which has to render a 404 page. This means in a matter of seconds dozens of php processes and mysql connections (with different users), which causes a server overload.

I mitigated this by limiting the number of MySQL connection and it partially works, because the server is not crashing anymore, but when this happens it still gets overloaded and unresponsive for some time.

Given that I could either

create an empty favicon.ico file for every site
add a custom htaccess rule for favicon.ico
enable some caching plugin on wordpress

is there a better way of controlling this issue, which doesn't involve touching websites?

UPDATE

Indeed I understand I didn't explain very well why I posted here and not on stackexchange.

I believe the MySQL server can be more fine-tuned in order to avoid overloading the server. As said I already limited the maximum number of connections, but I was wondering what can be done on the databases side, if there's some way to queue or semaphore them, so that when there are 200 users connected and doing basic queries the server can somehow sustain them.

The server is a quad core with 8G of ram, running Ubuntu 12.04. My custom MySQL config:

[mysqld]
table_cache=1024
query_cache_size=64M
join_buffer_size=512K
tmp_table_size=64M
max_heap_table_size=64M
open_files_limit=3200
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
innodb_buffer_pool_size=600M
innodb_thread_concurrency=8
thread_concurrency=8
max_user_connections = 15
max_connections = 80


texts: TimescaleDB database design - should I use inheritance?

texts:
I have the following TimescaleDB hypertable:
CREATE TABLE public.data
(
    event_time timestamp with time zone NOT NULL,
    pair_id integer NOT NULL,
    entry_id bigint NOT NULL,
    event_data1 int NOT NULL,
    event_data2 int NOT NULL,
    CONSTRAINT con1 UNIQUE (pair_id, entry_id ),
    CONSTRAINT pair_id_fkey FOREIGN KEY (pair_id)
        REFERENCES public.pairs (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    <unique index on event_time, pair_id, entry_id>
)

<Some continuous aggregates>

But when querying this data I would actually never need to just get data across pair_id's such as:
SELECT * FROM data WHERE <some condition on the time>
Instead I would like to query the data such that I get the data joined on the event_time for each pair_id - something like this:

event_time
pair_id1_event_data1
pair_id1_event_data2
pair_id2_event_data1
pair_id2_event_data2
...

Or:
|event_time | pair_id1_continous_agregate1 | pair_id1_continous_agregate2 | pair_id2_continous_agregate1 | pair_id2_continous_agregate2 | ... |
| --- | --- | --- | --- | --- | --- |
Sorry for not writing the actual query, I'm still learning how to do this.
Given I have 1000s of pair_ids, does this database design make sense to have efficient query performance?
The alternative I am considering is to use inheritance like this https://www.postgresql.org/docs/current/ddl-inherit.html:\
I have about 5 types of data stored in this table, some of them have an extra column or two

Create a parent table
For each data_type create a data_type_table inheriting from the parent table
For each pair_id create a table inheriting from the appropriate data_type_table

Questions

A) Is this type of inheritance even supported in TimescaleDB?
B) Would this improve my query performance?
C) Is there another alternative which would be better?


texts: Adding foreign keys constraint on databases used

texts:
We have a server with about 20 databases, all duplicates of the same original db, and we just noticed the original is missing all the foreign keys constraint for some unknown reason. So of course all the databases on that server are missing them too.
Now, these databases have been used for some time now, and there is data in them. We noticed that in the export done from phpmyadmin, the constraint are at the end, so I did an export from a valid database and I now have a few hundreds lines of ALTER TABLE to add the constraints, the problem being that if I start applying that to our databases, MySQL will reject it because the keys that should have been deleted by the constraint weren't.

Is there any way around that ?
That's a lot of databases and a lot of tables, it's just not possible to go in all of them and delete the data manually, and since those constraints should have been here from the start, I feel that all the data that would prevent those keys from being added could be dropped to get back to the "normal" state, am I wrong ?

I'm not a database administrator, so I have no idea if that's even possible. I've been told that ALTER IGNORE TABLE works like that for UNIQUE, would it work for constraints too ?
Or is there a way to tell MySQL to ignore the errors when adding the constraints and then DELETE all the unmatching entries ?

Thanks a lot,


texts: Retrieving 10 records from multiple tables

texts:
I have two tables where I store post data. What I want to do is to get 10 records from those two tables. Table 1 - all posts, Table 2 posts that user read.
There are some scenarios;

User has never read a post. So all the records will be gotten from table 1.
This is easy: select * from table1;

User has read some posts but we don't know how many. In this case maximum 3 posts will be fetched from table 2, and the rest of the records will come from table 1.
We can do this by counting how many posts there are in table 2, if there are more than 3 records, get only 3 of them (if lower than 3, then get how many records there are), do the math to learn how many records will be fetched from table 1 and a second query for table 1 to get 7 (or more). But in this case we combine PHP with MySQL and do more work.

How can I get this done in one query?


texts: In-Memory Optimized Error: The code generation directory cannot be created or set up correctly

texts:
I ran into a problem yesterday trying to create the first memory_optimized table on a SQL Server 2016 SP1 Enterprise Edition.

I created the database and filegroup as so

CREATE DATABASE imoltp   --  Transact-SQL
 CONTAINMENT = NONE
 ON  PRIMARY
(
    NAME = N'imoltp',
    FILENAME = N'F:\UNREFRESHED_DB\imoltp.mdf' ,
    SIZE = 5120KB ,
    FILEGROWTH = 1024KB
)
 LOG ON
    (
        NAME = N'imoltp_log',
        FILENAME = N'F:\UNREFRESHED_DB\imoltp_log.ldf' ,
        SIZE = 2048KB , FILEGROWTH = 10%
    )
GO

ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE imoltp ADD FILE
    (name = [imoltp_dir], filename= 'F:\UNREFRESHED_DB\imoltp_dir')
    TO FILEGROUP imoltp_mod;
go

Then I created the table

USE imoltp
GO

CREATE TABLE imoltp.[dbo].[T1] (
  [TempID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
  [object_ids] NVARCHAR(255) NOT NULL,
  [names] NVARCHAR(255) NOT NULL,
  [comment] NVARCHAR(50)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY);

GO

Which results in this error:

  Msg 41334, Level 16, State 0, Line 8
  The code generation directory cannot be created or set up correctly.

I finally figured out that SQL was trying to create a directory (XTP) at the default database location defined in the server properties which no longer existed in this situation. This directory is where it wants to store dll files that in-memory needs.

https://blogs.msdn.microsoft.com/bobsql/2016/08/23/create-table-disk-vs-in-memory-optimized/

So my questions is this.

Is there a way to define where the XTP directory will be created in the CREATE TABLE syntax or elsewhere?

Thanks,
Craig


texts:
I have the following tables:
fabrics
| id| title   | code|
|-- |---------| ----|
| 1 | Fabric 1| FBR1|
| 2 | Fabric 2| FBR2|
| 3 | Fabric 3| FBR3|
| 4 | Fabric 4| FBR4|

attributes
| id| title      | category|
|---|------------| -----   |
| 1 | Type       | printed |
| 2 | Color      | printed |
| 3 | Composition| printed |

attribute_values
| id| attribute_id| text  |
|---|-----------  | ----- |
| 1 | 1           | soft  |
| 2 | 2           | yellow|
| 3 | 2           | blue  |
| 4 | 2           | black |
| 5 | 1           | dry   |
| 6 | 2           | green |
| 7 | 3           | cotton|
| 8 | 3           | wool  |

fabric_attribute_value
| id| attribute_value_id| fabric| attribute_id|
|---|-------------------| ------| ------------|
| 1 | 6                 | 1     | 2           |
| 2 | 7                 | 1     | 3           |
| 3 | 2                 | 2     | 2           |
| 4 | 7                 | 2     | 3           |
| 5 | 6                 | 3     | 2           |
| 6 | 8                 | 3     | 3           |
| 7 | 2                 | 4     | 2           |
| 8 | 8                 | 4     | 3           |

My problem is that I want to filter for example Fabrics with Color attribute: yellow OR green AND with the Composition attribute: cotton.
I should be getting Fabric 1 and Fabric 2 as results back.
How do I accomplish this query?
When there are multiple filters for the same attribute like Color it should be an OR inside it, but if it binds with condition with another attribute like Composition it should be AND. So for example in pseudo-code:
(Yellow(Color) OR Green(Color) AND Cotton(Composition))


texts:
Our workflow requires two users to work on the same Access database. The problem is that users are geographically separated and can't work on same .accdb file simultaneously.

So, the only way i see to solve this problem is to periodically merge each user's DB's into one. If there is another way, please share you thoughts.

I've googled for how to do this in Access, but haven't found easy solution. I mean, this operation seems to me quite natural and i've been expecting that Access can do merging "out of the box". But what i found are advices to write special queries for each table being merged.

I'm fine with this, since my tables are simple, but i wanted to know if there's better way and how such problem should be properly solved.


texts: Create User, Tablespace Replication in Oracle GoldenGate

texts:
How to replicate - Create User, Procedure and Tablespace using Oracle GoldenGate.

I am able to replicate DDL under schema, however above is not getting replicated i.e. when I create an user, procedure or tablespace.

GoldenGate Version - 12.2.0.1
Database Version - 11.2.0.4

Extract Conf:

GGSCI (sourceGG) 36> view params ext4

extract ext4
userid ogguser, password ogguser
exttrail /u01/app/oracle/product/11.2.0.4/oggsrc/dirdat/mt
DDL INCLUDE ALL
table ogguser.*;

GGSCI (sourceGG) 37>

Dump Conf:

GGSCI (sourceGG) 37> view params dpump2

extract dpump2
userid ogguser, password ogguser
rmthost targetGG, mgrport 7809
rmttrail /u01/app/oracle/product/11.2.0.4/oggtrg/dirdat/pt
passthru
DDL INCLUDE ALL
table ogguser.*;

GGSCI (sourceGG) 38>

Replicat Conf:

GGSCI (targetGG) 37> view params rep4
replicat rep4
assumetargetdefs
handlecollisions
userid ogguser, password ogguser
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
map ogguser.*, target ogguser.*;

GGSCI (targetGG) 38>

Thanks!


texts:
I have imported a csv file into my database created in phpmyadmin using sql.

  Server version: 5.6.37 - MySQL Community Server (GPL

I have a column for dates which has a datatype of date. The data displayed on webpage for all the dates are as 0000-00-00. However in the csv file the format is 12/2/2016. Could any one please help how could I resolve this issue?


texts: MariaDB low CPU usage, high load average, spiky load

texts:
I have been trying to figure out, what could possibly cause very strange spiky load on a DB server. I am running Ubuntu 20.04LTS and mariaDB 10.5.7. 8CPUs, 16Gb RAM. There are 2 separate APIs accessing the DB server though private network.
This is a statistics server with just a few tables. Most of the queries are to 2 tables of roughly 1 million rows each right as of now. All the fields used for queries are indexed (3 int, 2 timestamps). InnoDB buffer pool size is 4Gb of which roughly 200Mb used.
This behavior seems to happen no matter what level of load I have. Currently having roughly 25 selects / second and 6 writes / second. Load averages moving around 30-40. Periods of different load seems to be split into 2 phases. 40 seconds of basically 2-3% CPU load. SHOW FULL PROCESSLIST shows basically nothing. Then suddenly CPU usage spikes to 70-80% for 20 seconds and lots of queries are being ran. Checking SHOW FULL PROCESSLIST show roughly 20+ operations being done.
No application behavior explains this, as the load should be pretty much random as there are no timed actions related to selects or inserts.
I feel like this has to be some buffer issue. Queries are possibly being buffered somewhere, I just have no idea where. Tried figuring out if it could be innodb_change_buffer_max_size, but I don't think it could be that as we are sitting at 200Mb/4Gb of buffer pool used.
My config changes. Rest are default.
[mysqld]
query_cache_size=0
query_cache_type=0
innodb_buffer_pool_size=4G
innodb_log_file_size=1G
max_connections=550
thread_cache_size=250

[mariadb]
log_error=/var/log/mysql/mariadb-errors.log
slow_query_log
slow_query_log_file=/var/log/mysql/mariadb-slow.log
long_query_time=1.0

I have slow queries logged. There is nothing special. Even very simple queries get listed there during the "slowdown". Queries, that usually run in few ms, take over 1 second. All the queries logged in slow query log are also timestamped hh:mm:50-55. Which seems to be pretty much the same 1 minute cycle I measured manually just watching top.


texts:
i want to change table name in mysql it seems easy but every times gives a error

I want to change table name
    from qwerty to qwerty_2

So, how can i do so?

Any help will be appreciated thanks


texts: What's the problem with my password while installing Apex 20.1?

texts:
I am installing Apex 20.1 on my windows 10 with oracle 19c.  After running the command : @apxchpwd.sql  I get this error even after respecting all the password regulation and trying different combinations of mails and passwords :
User "ADMIN" does not yet exist and will be created.
Enter ADMIN's email [ADMIN]
Enter ADMIN's password []
ERROR:
ORA-01756: quoted string not properly terminated

declare
*
ERROR at line 1:
ORA-20001: Invalid password.
ORA-06512: at line 30
ORA-06512: at "APEX_200100.WWV_FLOW_FND_USER_INT", line 3723
ORA-06512: at line 20```


texts: tds: unable connect to SQL Server using servername specified in freetds.conf file

texts:
I am using freetds to connect to SQL Server from ubuntu.

First, the SQL Server is running at port 1433 currently. I can telnet it. The following command also works. I can query table data using it,

sqsh -H 192.168.10.249 -p 1433 -H xx -P xxxxxx

however, this command does not work, and consequently making my ror app crash.

sqsh -S developer -U xxx -P xxxxxxxxx

this is error inside freetds.log

1:24:59.068648 5010 (log.c:190):Starting log file for FreeTDS 0.82 on 2012-06-04 11:24:59 with debug flags 0xffff.
11:24:59.069259 5010 (iconv.c:197):names for ISO-8859-1: ISO-8859-1
11:24:59.069298 5010 (iconv.c:197):names for UTF-8: UTF-8
11:24:59.069317 5010 (iconv.c:197):names for UCS-2LE: UCS-2LE
11:24:59.069335 5010 (iconv.c:197):names for UCS-2BE: UCS-2BE
11:24:59.069352 5010 (iconv.c:363):iconv to convert client-side data to the "UTF-8" character set
11:24:59.069394 5010 (iconv.c:516):tds_iconv_info_init: converting "UTF-8"->"UCS-2LE"
11:24:59.069461 5010 (iconv.c:516):tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
11:24:59.069492 5010 (net.c:836):tds7_get_instance_port(192.168.10.249, MSSQLSERVER)
11:25:00.070784 5010 (net.c:905):tds7_get_instance_port: timed out on try 0 of 16
11:25:01.072121 5010 (net.c:905):tds7_get_instance_port: timed out on try 1 of 16
11:25:02.073441 5010 (net.c:905):tds7_get_instance_port: timed out on try 2 of 16
11:25:03.074756 5010 (net.c:905):tds7_get_instance_port: timed out on try 3 of 16
...
11:25:15.095377 5010 (net.c:905):tds7_get_instance_port: timed out on try 15 of 16
11:25:15.095480 5010 (net.c:974):instance port is 0
11:25:15.095506 5010 (login.c:418):invalid port number
11:25:15.095532 5010 (mem.c:563):tds_free_all_results()
11:25:15.095580 5010 (ct.c:630):leaving ct_connect() returning 0
11:25:15.095605 5010 (ct.c:334):ct_con_props() action = CS_GET property = 9143
11:25:15.095630 5010 (ct.c:1865):ct_con_drop()
11:25:15.095652 5010 (ct.c:242):ct_exit()

this is my freetds.conf file

[global]
;   tds version = 4.2

    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
;   dump file = /tmp/freetds.log
;   debug flags = 0xffff

    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10

    text size = 64512
[egServer50]
    host = symachine.domain.com
    port = 5000
    tds version = 5.0

[developer]
    host = 192.168.10.249
     port = 1433
    #instance = MSSQLSERVER
    tds version = 8.0
    client charset = UTF-8
    dump file = /home/daisy/freetds.log
    dump file append = yes
    debug flags = 0xffff

From the log output, I can see the program does not read the SQL Server port correctly in this case, it reads 0 which is wrong.

It also outputs some information relating to encoding. so I guess it may relate to the translation issue.


texts: Delete document from nested array in MongoDB


texts: Determining what objects SHRINKDATABASE will free space from

texts:
First off, yes, shrink is bad. Don't use it, ever. I understand. Unfortunately, it's kind of SOP and I'm not in a position to argue since it's usually a stopgap until we can get a maintenance window to allocate more space and rebuild/reorganize indexes.

We recently had an issue where we were switching over from traditional backups to a third-party tool. Due to the size of the database and some large objects we'd recently removed, it made sense to shrink it prior to taking the clean backup with the new tool before the cutover. I had some reservations, but we went ahead with it as it rarely causes problems. Obviously, clearing the logs isn't really an issue, but we were shrinking the mdf. This caused a lot of blocking on just a few large, highly-transactional objects and, rightly so, brought up some pretty serious questions about how we use this.

I've been doing a lot of research, but haven't yet found enough detail to my satisfaction.

Is there anywhere that actually describes the internals of how SHRINK is operating?
Is there a way to determine which objects are more fragmented than others and how it's freeing up space? Alternatively, does it not matter how fragmented a table is and it's more about the overall size of the object compared to others in the db?

Paul Randall talks a bit about it here, but it's not enough detail.

I know you could sort of base it on the dm index stats, but it's been pretty difficult to run on some of our larger databases due to the size and over-indexing. I'm also not sure if that's all I'd need to be concerned with. Main tables are stored as heaps and only the indexes are really fragmented, but I'm not sure how different partitions affect things or if other parts of the underlying DB could also be getting freed up.

I did also see this question, but I'm not sure if I'd be able to configure it to the kind of granularity we're looking for.


texts: Should a MySQL slave used for backup also be used to service read-only requests in a production environment?

texts:
Based on best practices, I'm wondering if we should have at least one slave that's used solely as a backup server, without servicing any forward-facing requests from users.

For example, if we have a master server with a single slave, and that slave is used to back up data via mysqldump a few times a day, are there any compelling reasons not to use that same slave to calculate some quick-to-load (<10 seconds) analytics for a handful of customers in production?


texts: Performance impact of including result column in indexed columns

texts:
I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of:

texelid INT (PK AI NN)
texelx TINYINT (NN)
texely TINYINT (NN)
texelhue INT (NN)
identifiedtex INT (NN)

Identifiedtex is a foreign key in a 1:N relation of one identifiedtex to many texels(texture elements)

If I create an index(B-tree) using texelx, texely, and texelhue then I can quickly find a row. However, if I add identifiedtex as the last column indexed in the index, will this speed up a lookup of the texture since the result is part of the index? Just as a note, a combination of texelx, texely, and texelhue will result in multiple identifiedtex's, and a few different rows will need to be looked up to identify the texture.

I plan to identify textures by using an inner join against the same table(self join) in order to find rows that have a matching X, matching Y, and matching hue, and grouping by distinct identifiedtex's.


texts:
Is there any way to determine a rough estimate of the size (on disk) of an index before creating it? The size of the table and each column are known. I am particularly interested in GIN indexes. Also any information of how the sizes of different index types relate to each other is appreciated. Is there a general rule of thumb like a GIN index is always bigger than a B-TREE index? Or is it too dependent on the data size and distribution?

To clarify: i am not looking for a tool. I am happy to do it by hand.


texts: Mysql to Mongodb data migration application for use in production environment

texts: I have a mysql database that receives data every 10 seconds and its a lot of data i receive.I am looking for an application that can transfer my database from mysql to mongodb. I am looking for a proven solution that works in production environment.


texts: MariaDB System Versioned Tables, Update queries causing duplicates

texts:
I have just enabled system versioned tables on an existing system and am experiencing an oddity, not sure if it is a bug or if I am misunderstanding something.
I have a table called assets, in this table are some basic columns for details about an asset as well as some "cached" status columns. these status columns are updated when something changes and are things like "nextServiceDue" which is calculated based on a somewhat complex join that is not "quick", So I have decided to "cache" it for quickness.
Now, I am paranoid that there is a possibility that I have written a function somewhere that updates the asset but does not trigger the cache to be updated, so I have a cronjob set up to run a query at 2am to recalculate it for all assets.
99.9999% of the time, this affects 0 results. Query executed OK, 0 rows affected. but I have noticed when trying to "go back in time" with system version tables, that when I go back, it returns duplicate rows, if I go back 1 day, there will be 2 copies of an asset returned on a simple SELECT * FROM assets if I go back 2 days, it will return 3 and so on.
to test, I ran the update query again and then selected the exact same asset and an extra duplicate appeared. (so now 2 days returns 4 instead of 3).
(I have also noticed a 10% jump in mariadb backup size in the space of 2 days)
I am not sure what is going on and why it is duplicating in this way. if there are 0 rows updated in an update query, why is it creating these duplicates and why is it showing them to me, surely even if this DID happen, it should only show me the 1 row for each time point.
Sorry for the rambling, not sure how to explain it as I do not know what is going on.


texts: SQL Agent parsing actually executes instead of just validating

texts:
I'm seeing some really odd behavior with SSMS 2014/2016 that I stumbled accross by pure accident.  This was run on SQL2014 SP1+CU6 (the fixed one) in a non-prod environment. Here's the backstory.

I use SQL Compare to deploy changes to our SQL servers.  We are moving to a TFS/DB Project approach but this is what we are using right now.

Let's say one of the changes is to add a column to a table.  This is the code that SQL Compare generates for that change:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [DBA_Support]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ParseExec]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ParseExec] ADD
[BlockType] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

Seems legit, and it is.  Load it into an Agent job step, Parse it, no issues.

Let's take it one step further, what happens if you sausage finger the Paste button and accidentally hit it twice, or Ctl+V twice.  You get the code, stacked twice, as shown below:

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [DBA_Support]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ParseExec]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ParseExec] ADD
[BlockType] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
USE [DBA_Support]
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[ParseExec]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[ParseExec] ADD
[BlockType] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    PRINT 'The database update failed'
END
GO

Here's the odd behavior, in SSMS, we get The command was parsed successfully but, it actually performs the operation on the table:

And, no, it wasn't on the table before the parsing, I double checked.

Has anyone ever seen this happen.  I can't help think that it has something to with a SET option or the fact that we are suing SERIAL transactions.  Regardless of that, should the Parse button simply hit the algebrizer, validate that everything is legit and not actually execute anything?

Is this a bug or am I just overlooking something simple here?


texts: Index rebuild canceled because of .mdf growth

texts:
I have the Standard Edition of SQL Server 2012 and used a script to rebuild index. I ran the query and then I saw that the .mdf file for the database started the grow a lot.

I chose to cancel/stop my query that run the rebuild of the database.

I checked in database what happened with the following queries:

SP_who2

select cmd,*
from sys.sysprocesses
where blocked > 0

I saw that a process where blocked so I chose to kill that one.

The query/spid became marked with cancel/rollback in SP_who2.

It took a lot of time to rollback the query.

The table that was rebuilding was a big with over 4 million rows and a fragmentation of 99%.

My questions are:

Why did the .mdf grow that fast? About 1 gb/(10/sec)
When I stopped my query, can the database get inconsistent in any way?
Can the data in the database have been changed because of this?

Thanks in advance!


texts:
I'm trying to verify that I am actually getting data in my pg_dump from a PostgreSQL 9.3 server. I'm specifically running the following to get a dump of my Foreman database:

pg_dump -Fc foreman -U foreman -h foreman-db -p 5432 > foreman.sql

I am reading the dump using pg_restore -l foreman.sql but when I search for known fqdns of hosts that should be in the hosts table, I can not find them.

I'm no DBA, but it seems I'm doing something very wrong or I don't know how to search well enough yet to find data within pg_dump's.

Any assistance is much appreciated.


texts: Replace Null values with values from the same column by matching values in a different column

texts:
I have a table with some null values in column "date":

platform   date         id
---------------------------
web        2018-10-10   1
mob                     1
mob                     1
web        2018-10-15   2
mob                     2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob                     3

I want to update null values in 'date' for 'mob' platform by matching the 'id' column from platform 'web'. The result should look like this:

platform   date         id
---------------------------
web        2018-10-10   1
mob        2018-10-10   1
mob        2018-10-10   1
web        2018-10-15   2
mob        2018-10-15   2
ntl        2018-10-09   3
web        2018-10-12   3
web        2018-10-11   4
mob        2018-10-12   3

Will really appreciate your help!


texts:
I am newbie in this field.

I need to design database. Abstract: there is a counter in every apartment, which is monitoring utility bills. Payments for gas and electricity. There are many houses in a city, but there is only one counter in one apartment. I need to create proper database.

I suppose that it looks like 'one-to-many' connection, so:

First table: id_counter (primary key), address
Second table: id_counter, cubicmetre_amounts
Third table: id_counter, consumed_Vt

Do I understand it correct, is it really 'one-to-many' and if so, what should be the primary key in a second and third tables?

Thanks in advance!


texts:
I am using  MySQL version 5.7.15 , ubuntu0.16.04.1. When I start the MySQL, I am getting below log,

2016-11-06T13:44:56.838233Z 0 [Warning] option 'table_open_cache': unsigned value 33554432 adjusted to 524288
2016-11-06T13:44:56.839061Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 1049386)
2016-11-06T13:44:56.839078Z 0 [Warning] Changed limits: max_connections: 214 (requested 800)
2016-11-06T13:44:56.839082Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 524288)

  $ ulimit -Sa | grep "open files"

  open files                      (-n) 1024

In google, It is specified like open_files_limit value need to be increased to have more max_connections.

Will it be enough to set below values on the /etc/mysql/mysql.conf.d/mysqld.cnf and restarting the MySQl server ?

open_files_limit = 2048

max_connections = 800

table_open_cache = 600

Is there any other way to change the values without restarting the MySQL server ?

Is setting the open_files_limit on the MySQL configuration file enough ?


texts: enable logging of password-like patterns in mysql client history

texts:
From MySQL 5.6.8 onwards, statements containing the pattern *PASSWORD* or *IDENTIFIED* are not logged in the mysql command line client history. I know I can add to this list with --histignore or MYSQL_HIST_IGNORE, but how do I defeat this filtering altogether? I'm working on a development server, never storing cleartext passwords and above all, I did not ask for this behaviour. It's my server, and I want to be able to cursor-up and get a previous query without cutting and pasting.

I posted a previous question on this topic when I couldn't work out why some queries were not being logged, but now we're on 5.6.22 and I'm wondering if anyone has found a solution.


texts: Rebuilding partitioned table clustered indexes

texts:
I need to know how can we rebuild the partition table clustered index with the table size being around 270 GB with 126 Partitions on it.

Also, I want to execute it in the production environment, so what will be the quickest way to do it and how.

This is a very critical change which needs to be done, so any help or suggestion would be highly appreciated.


texts:
I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had to rebuild the primary inclusive of the dataID and dataInsertDateTime which I think is correct step. I just tried like below scripts. How to verify if my partition is being build accordingly? I tried explain a query with the dataInsertTime<'2012-12-11' is showing me both p0 and p1. Kind of confuse here.

ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) (
PARTITION p0 VALUES LESS THAN (to_days('2012-12-10')),
PARTITION p1 VALUES LESS THAN (to_days('2012-12-11')) )


texts: Change transaction isolation level inside running transaction

texts:
I'm studying transaction isolation levels in SQL Server, and I'm trying to figure out how SQL Server behaves when the isolation level changes during the lifetime of the transaction.

It seems that something like this is possible in SQL Server:

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* (some selects/inserts/updates/deletes) */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

/* (some selects/inserts/updates/deletes) */

COMMIT TRANSACTION;

To be honest, I cannot think of an example when a decrease in the isolation level would make sense, I can only think of some scenarios where a part of the transaction would require serializable isolation while other parts wouldn't. I have a feeling that mixing isolation levels using snapshots and row-versioning with other kinds of isolation levels won't work well, but I cannot find much information to back this up.

Is it a thing that happens in practice that a single transaction switches between multiple isolation levels during its lifetime? Are there some caveats and details to know about?


texts:
I want to add a second archive log destination to an Oracle 11gR2 RAC database with ASM, the idea is that I will then have some redundancy if the primary storage should fail.

Archive log is already enabled, and logs are currently archived in the Fast Recovery Area. However, as far as I can see, none of the LOG_ARCHIVE_DEST_n init parameters have been configured.

Is there now an unset default that specifies the Fast Recovery Area as a log destination?

If that is the case, I assume I must now configure two destinations. One entry to continue writing archive to that default destination, and one entry for the additional backup destination. If so, how do I specify the existing default location in the fast recovery area?

Will this work, is there another preferred way?

alter system set log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';
alter system set log_archive_dest_2 = 'LOCATION=/s01/archive/TESTDB';
alter system set log_archive_dest_state_1 = enable;
alter system set log_archive_dest_state_2 = enable;
alter system set log_archive_min_succeed_dest = 1;

Naturally, the /s01 filesystem is available on all (both) cluster nodes.


texts: mysql 5.7 replication - slave_parallel_workers and slave_pending_jobs_size_max

texts:
I am using slave_parallel_type = LOGICAL_CLOCK and slave_parallel_workers = 20 to achieve replication performance.

Lets say I am using m4.4xlarge with 16 cores and 64GB memory for the master and the slave:

As far I understand theseslave_parallel_type and slave_parallel_workers parameter should be only on the slave, right ?
What value slave_parallel_workers parameter should have? 16 as number of cores ? (I test it with 20 and I got nice performance).
Any suggestion about the slave_pending_jobs_size_max ?
sync_binlog=1 should be on both master and slave to increase group commits and increase the potential to execute queries in parallel?

slave_parallel_workers=20
slave_parallel_type = LOGICAL_CLOCK
slave_pending_jobs_size_max=1G
sync_binlog=1
max_allowed_packet = 256M

Thanks


texts: How to get all parents associated to a child, one row for each association

texts:
I have a table with two columns, Parent and Child.
i need to get an output list showing a child and all associated parents, one parent per row.
The youngest child will show as a child in rows equal to the total number of parents, as shown in the output table.

Input table

+-------+--------+
| Child  | Parent|
+-------+--------+
| b | a  |
| c | b  |
| d | c  |
| e | d  |
| f | e  |
| x | f  |
+----------------+

Output table

+------------------------------+
| Child | Parent  | level  |
+------------------------------+
| a | NULL    | 0      |
| b | a   |
| c | a   |
| c | b   |
| d | a   |
| d | b       |
| d | c   |
| e | a   |
| e | b   |
| e | c       |
| e | d       |
| f | a       |
| f | b       |
| f | c       |
| f | d       |
| f | e       |
| x | a       |
| x | b       |
| x | c       |
| x | d       |
| x | e       |
| x | f       |
+--------------------------+

Here is my syntax from SSMS 2017

create table tx  ( parent varchar(10), child varchar(10) )

insert into tx values
('a','b'),
('a','c'),
('b','e'),
('b','f'),
('a','d'),
('b','g'),
('c','h'),
('c','i'),
('d','j'),
('f','k'),
('x','y'),
('y','z'),
('m','n');

WITH REC AS (
SELECT child, parent
FROM tx
UNION ALL
SELECT a.child, a.parent
FROM tx A
INNER JOIN REC B ON B.parent = A.child)
SELECT Child, Parent
FROM REC
order by child

It is not giving me the results i expect as shown in the output table.

Any idea on how to modify my recursive query to get the output table?


texts:
I'm using SSMS's Audit feature to see who's successfully logging in and logging out of the server and I'm currently testing it in a server in which only I have access to it.
I used for SUCCESSFUL_LOGIN_GROUP and LOGOUT_GROUP for my server audit specifications.

Every time I view the audit logs, why is it that I see that my account constantly logging in and out of the server when in reality I didn't even log out?


texts:
I have a SQL table that is layed out as follows:

{Itemcode}  {FiscalYear}    {FiscalMonth}   {StartingQty}   {PeriodChangeQty}

Ball    2013    01  15000   -7500

Ball    2013    02  0   1000

Ball    2013    03  0   -500

Ball    2014    01  8000    -1000

I want to generate a table that looks like this

{ItemCode}  {FiscalYear}    {01}    {02}    {03}    {04}    {05}    {06}    {07}    {08}    {09}    {10}    {11}    {12}

Ball    2013    7500    8500    8000

Ball    2014    7000

Not sure at all how to do this.  I can get close with a pivot table but not quite.  Any ideas or clues as to how to accomplish this?


texts: Collect performance information on high CPU condition in SQL Server

texts:
I have a SQL server cluster that is often reaching 100% CPU out of working hours. I want to gather some information to help troubleshooting when the actual condition is taking place.

I'm thinking of gathering info from sys.dm_exec_requests for example.

Question: How can i trigger this information gathering to take place based on CPU utilization condition

For Example: Insert the results of sys.dm_exec_requests into a table when CPU > 95%

suggestions for anything else I should gather are very welcome!

It appears that I might be able to do this with alerts, but I need to better understand the counters as my example doesn't fire, despite CPU >5%

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'My Test CPU Alert',
    @message_id=0,
    @severity=0,
    @enabled=1,
    @delay_between_responses=0,
    @include_event_description_in=0,
    @database_name=N'',
    @notification_message=N'',
    @event_description_keyword=N'',
    @performance_condition=N'Resource Pool Stats|CPU usage %|default|=|5',
    @wmi_namespace=N'',
    @wmi_query=N'',
    @job_id=N'00000000-0000-0000-0000-000000000000'
GO


texts: SSAS error deploying project: Ensure that 'SQL Browser' service is running

texts:
After successfully building my project, I got this error when deploying it:

The project could not be deployed to the '\SQLEXPRESS' server because of the following connectivity problems :
A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running.
To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

Things I've checked/tried so far:

Changing the deployment server name (from localhost to my actual SQL Server name)
Making sure SQL Server Browser service is running (which is running in SSCM)
Registry SSrpListener value (should be 1 -- mine is 1)
Running both Visual Studio and SSCM as administrator

Versions:

Visual Studio Community 2019 (16.9.2)
SQL Server Data Tools (16.0.62103.10080)
SQL Server Analysis Services (15.0.19445.0)
SQL Server Configuration Manager (2019.0150.2000.05)
SQL Server Management Studio (15.0.18369.0 or v18.8)
SQL Server 2019 Express (15.0.2000.5)
Windows 10 (10.0.19042)

Based on the information I've provided above, I still could not deploy the project.


texts:
May I know how to export MySQL data and prevent the data display a weird character?

This table uses to store the record about crawling information. It stores IP, PatternSignature, status, updated and PTR.

But whenever I export out the table, all the IP will become an unknown character which unable to import into database.

May I know how to rectify this issue?


texts:
How does pt-archiver handle foreign keys?

Does it archive associated data? (seems not but the documentation is not very clear to me)

And if not, how could one perform an archive of a table with all its associated data from other tables also being archived?

The documentation for custom module/plugin says that we can achieve this

before_delete(row => @row)
This method is called for each row just before it is deleted. This applies only to --source. This is a good place for you to handle dependencies, such as deleting things that are foreign-keyed to the row you are about to delete. You could also use this to recursively archive all dependent tables.

....

before_insert(row => @row)
This method is called for each row just before it is inserted. This applies only to --dest. You could use this to insert the row into multiple tables, perhaps with an ON DUPLICATE KEY UPDATE clause to build summary tables in a data warehouse.

but not how one can achieve this

Any experiences with this toll for this kind of of usecase?


texts: Grant Permissions to all databases using server role

texts:
I know this question has been asked many times but they are all 3+ years old so I'm going to try my luck by asking again.

I need to grant Read permissions to all databases on MSSQL server. I would like to achieve this using server roles.

I've created server role with following permissions

CREATE SERVER ROLE ReadOnlyAccess
GO
GRANT VIEW ANY DEFINITION TO ReadOnlyAccess
Go
GRANT VIEW ANY DATABASE TO ReadOnlyAccess
GO
GRANT VIEW SERVER STATE TO ReadOnlyAccess
GO
GRANT CONNECT SQL TO ReadOnlyAccess;
GO

Assigning user to this role allows me to connect to the server but not expand any of the databases. I would of thought that 'GRANT VIEW ANY DATABASE' would be the one that gives those rights but boy was I wrong...

Is there a way to achieve this without granting db_datareader database role on each DB for the user? New databases are constantly being created so doing this manually would be quite a nightmare.
I'm guessing that creating a job that runs every few hours would do the trick but I would still rather have it done via Server Role.

Any ideas? I'm using SQL 2016 and above.


texts: Can Temporal Tables be implemented in SQL 2008 in a way that can be migrated to SQL 2016?

texts:
We have a system that is currently used by customers that have versions of SQL from 2008 R2 all the way to 2019.  We want to start introducing, in a limited way, Temporal Tables, even for customers who are using SQL versions earlier than 2016.

Our plan is to create the necessary History tables, which are identical to the respective root tables, except for two main differences:

1) 2 additional columns, SysStartTime datetime2 NOT NULL DEFAULT SYSUTCDATETIME() and SysEndTime datetime2 NOT NULL DEFAULT CONVERT( DATETIME2, '9999-12-31 23:59:59' ), added to the new History table.  For 2016 usage, these columns would also be added to the root table, but we're using triggers for the pre-2016 version, and we can't be modifying the SysStartTime/SysEndTime columns in the middle of the trigger.

2) No primary key, foreign keys, or other constraints on the History table, although from what I've read, indexes are permitted, though these likely would need to be dropped before converting to SQL Server 2016.

Then, for the root table, create an Update/Delete trigger that will populate the history table when data is updated or deleted.

My questions are:

1) Will this methodology be able to be migrated without loss of data when a client upgrades to SQL 2016 or newer?

2) What would those migration steps be?

I believe we would need to have two schemas for our database, one for clients pre-2016 that uses the home-grown pseudo-temporal tables, and one for 2016 clients (or newer) that uses the built-in functionality, with a series of scripts that can take them from one form to the other, but with a solid upgrade plan, I believe this would be manageable.


texts: How to track overhead of Change Tracking in SQL Server

texts:
We're going to implement a solution based on Change Tracking feature
from SQL Server.
Before going in production (even after), we'd like to keep track of the overhead
the CT will introduce.

Once in production, we'd like to have a counter / event / DMV telling us how much time
SQL Server is spending to "keep track" of changes.

Our current (production) installation have tens of (multi-gigabyte) databases (on every instance) and, even if the overhead is low on a single DB, we should measure how much it
will be on several Databases.

Thank you
Igor.


texts: Loading MySQL Dump From one Character Set to Another

texts:
As you can see from these configuration differences, the MySQL server is configured with latin1, while the MariaDB server is using utf8mb4:

Variable                  MySQL Server       MariaDB Server
========================= ================== ===================
character_set_client      latin1             utf8mb4
character_set_connection  latin1             utf8mb4
character_set_database    latin1             utf8mb4
character_set_results     latin1             utf8mb4
character_set_server      latin1             utf8mb4
collation_connection      latin1_swedish_ci  utf8mb4_general_ci
collation_database        latin1_swedish_ci  utf8mb4_general_ci
collation_server          latin1_swedish_ci  utf8mb4_general_ci

UTF8 is the preferred character set, but am I going to run into issues when loading data from one database to the other? Are there steps I should take? Change configurations?

MySQL Dump command:

mysqldump --routines --single-transaction --quick --skip-extended-insert

Here are the directives from the MySQL Dump file:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


texts: I have a database that is RCSI enabled and I've been rebuilding my indexes online (SQL 2014 Enterprise) for as long as I can remember.  My understanding is that if we were to rebuild the indexes offline, we would lose those additional 14 bytes. However as long as we continue to rebuild online or reorganize we will retain the 14 bytes per row.  Is that correct?


texts:
I have two tables:

Sources:

(SName)  (SId)
Apple1   100
Apple2   200
Banks    300
BanksB   400
Bankerly 500

Prefixes:

(PName) (PId)
App     1
Bank    2
Banker  3

My goal: To find the longest prefix that matches each of sources. The results would look like this:

(SName)  (SId) (PName) (PId)
Apple1   100   App     1
Apple2   200   App     1
Banks    300   Bank    2
BanksB   400   Bank    2
Bankerly 500   Banker  3

Constraints: I am using SQL Server 2000 and cannot upgrade. I know how to solve this problem using analytic functions, but they are not available in SQL Server 2000, as far as I know.


texts: Any reasons trigger AFTER INSERT would not fire?

texts:
Context:

We're using microsoft sync framework in our project.

Sync creates triggers AFTER INSERT, DELETE, UPDATE triggers for tables that are provisioned to sync.
the framework also creates tracking tables, that get updated by these triggers, so it can successfully keep database in sync with each other, when a sync is performed.

The issue:

We have a customer, whom is managing to insert data into one of these tables that contain these triggers, but the information isn't making it into the corresponding tracking table via the trigger.

The Question:

Are there any ways to insert data into a table, that would circumvent the firing of an AFTER INSERT trigger?

Trigger Example:

CREATE TRIGGER [dbo].[Claims_insert_trigger]
ON [dbo].[Claims]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE [side]
SET [sync_row_is_tombstone] = 0
    , [local_update_peer_key] = 0
    , [restore_timestamp] = NULL
    , [update_scope_local_id] = NULL
    , [last_change_datetime] = GETDATE()
FROM [Claims_tracking] [side]
    JOIN INSERTED AS [i] ON [side].[ClaimID] = [i].[ClaimID];

INSERT INTO [Claims_tracking]
(
    [i].[ClaimID]
    , [create_scope_local_id]
    , [local_create_peer_key]
    , [local_create_peer_timestamp]
    , [update_scope_local_id]
    , [local_update_peer_key]
    , [sync_row_is_tombstone]
    , [last_change_datetime]
    , [restore_timestamp]
)
SELECT [i].[ClaimID]
    , NULL
    , 0
    , @@DBTS+1
    , NULL
    , 0
    , 0
    , GETDATE()
    , NULL
FROM INSERTED AS [i]
    LEFT JOIN [Claims_tracking] [side] ON [side].[ClaimID] = [i].[ClaimID]
WHERE [side].[ClaimID] IS NULL;
SET NOCOUNT OFF;
END


texts:
I have a table TempTable with one column having a default constraint

ALTER TABLE [dbo].[TempTable]
ADD  CONSTRAINT [DF_TempTable_Version]
DEFAULT ((1)) FOR [Version]

Version is defined as decimal (18,1)

Most of the time the value 1.00 is inserted into the column but in some cases its inserting a null value.

How we can identify if the constraint is failing or if there is some other issues causing this?


texts:
is it possible with Java Script to capture the errors of MongoDB backup failer and send this message to Windows event logs
Best regards


texts:
I am creating a league management system. I came up with the diagram below. I am no database expert but I feel it would be better to merge the three following tables: season_league, league_division and division schedule(see second image). This is for mySQL using doctrine with Symfony2 if it matters.

What are your opinions? Any suggestions?


texts: Is Percona xtrabackup the right choice for my 500GB MySQL backup?

texts:
I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime of ~4hrs.
My Needs:

Low cost.
Recovery time can be long. No need of real-time replication.
Can handle loss of data in ranges of 1week-2weeks.
Would prefer a short as possible down-time for backup, but can live with anything up to 2hrs.

Currently considering trying to move to Percona xtrabackup. Is it the right solution for my use case? I'm specifically concerned that it locks the MyISAM table and learning curve.


texts: Is there a standard implementation of a Users Database?

texts:
I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a single table with each user having his own row, or should this info be split among different tables and linked together (maybe for efficiency?) I'm not tremendously concerned with security at this point, but I'll obviously want to have password encryption before too long.

I tried to find what I was looking for on google, but to no avail. Just let me know if the question needs further clarification or anything.


texts: What does MySQL mean by "Major Version", and is MySQL replication supported between 5.1 and 5.6?

texts:
I am migrating from some MySQL 5.1 servers to MySQL 5.6 servers. The MySQL docs are a bit unclear if replication is supported between a 5.1 master and a 5.6 slave, and MySQL's documentation is unclear if this is supported.

In terms of version numbers, MySQL seems to follow the norms for semantic version numbers

5.1 has a "Major version" of 5, and a "release level" of 1.
5.6 has a "Major version" of 5, and a "release level" of 6.
MySQL 4, MySQL 5 and MySQL 6 would all be considered "Major versions".

This is documented at https://dev.mysql.com/doc/refman/5.6/en/which-version.html :

  The naming scheme in MySQL 5.6 uses release names that consist of
  three numbers and a suffix; for example, mysql-5.6.1-m1. The numbers
  within the release name are interpreted as follows:

  The first number (5) is the major version and describes the file format. All MySQL 5 releases have the same file format.
  The second number (6) is the release level. Taken together, the major version and release level constitute the release series number.

https://dev.mysql.com/doc/refman/5.6/en/replication-compatibility.html says replication is supported between one major version and the next highest major version. This wording is pretty ambiguous if replication is supported between 5.1 and 5.6.

  MySQL supports replication from one major version to the next higher
  major version. For example, you can replicate from a master running
  MySQL 4.1 to a slave running MySQL 5.0, from a master running MySQL
  5.0 to a slave running MySQL 5.1, and so on.

The updated 5.7 beta docs sort of simply that replication is only supported between release levels, although the manual uses the term "major version". What do they really mean here?

  MySQL supports replication from one major version to the next higher
  major version. For example, you can replicate from a master running
  MySQL 5.0 to a slave running MySQL 5.1, from a master running MySQL
  5.1 to a slave running MySQL 5.5, and so on.

Is MySQL replication supported between MySQL 5.1 and MySQL 5.6? I understand that MySQL 5.1 is EOL on most platforms, but it's still shipped with RHEL6.


texts: Cannot install SQL Server on a local disk

texts:
I'm installing a new instance of SQL Server, and I got this error:

instance directory (or command line option /instancedir) value for a cluster installation cannot be a directory on shared drive

It is pretty clear, and this is my situation:

I have a microsoft cluster with 3 servers. All with Windows 2012R2 installed. SQL Server is version2016 SP1.

On the first 2 servers I have some SQL Server instance in failover with dedicated storage. One of the drive is Y:

This istance (IS1) cannot move on server03. Also for the disc, in failover cluster manager, under Advanced Policies I deleted server03 from possible owners.

Now I have another disk, in server03, with letter Y. This is a local disk.

To configure AlwaysON I need to have some local instances on Server03, and I'd like to use this disk to have an automatic seeding.

How can I convince sql server installation Y is a local disk?


texts: sp_BlitzIndex "Missing Index" section has columns but no values/results

texts:
when I run sp_BlitzIndex @Version = '7.5', @VersionDate = '20190427', on my Microsoft SQL Server 2017 v14.0.3048.4, I have some tables where the missing indexes reports one column Finding with the value "No Missing Indexes".  This is perfect.

But in many cases I have what appears to be result columns with no values. It looks like this:

Finding | URL | Estimated Benefit | Missing Index Request | Estimated Impact | Create TSQL

And there is nothing in the rows.  My fear is that I actually have some missing indexes but some wire is crossed and the output isn't being produced.  I have been using this tool for years and find it invaluable, so I want to be certain I am actually seeing the correct results.


texts: How to change a web application that now needs to become OLTP and OLAP application?

texts:
Previously, I did a simple web application for an enterprise client using PHP and MySQL.

After some time, it has grown to a point where I realized after talking with people here that I need to treat it as two separate apps OLTP and OLAP.

I have tentatively decided that

the OLTP will still continue to run using the current PHP codebase and MySQL
the OLAP will use PostgreSQL
both should appear as a single app to the end user. This is to avoid inconsistencies and too many logins.
the OLAP should help with sending out scheduled reports via email

I want to avoid a "big bang" approach. At the same time, I don't want to do the OLAP as an isolated app.

I want to slowly evolve to the point where transaction processing and the analytic processing functions are modular and consistent.

How do I approach this and bearing in mind, I currently have only 1 physical server that acts as both database and application server at the moment?

I am most familiar with PHP and am okay to use Python.

The physical server is running ubuntu 14.04 and nginx as web server.

The normalized MySQL currently is at 20mb and growing at a rate of 1 mb every month or so.

EDIT

My server shows 8 when I run grep -c processor /proc/cpuinfo

Each processor (I think?) shows the following stats:

vendor_id   : GenuineIntel
cpu family  : 6
model       : 44
model name  : Intel(R) Xeon(R) CPU           L5630  @ 2.13GHz
stepping    : 2
microcode   : 0x13
cpu MHz     : 1600.000
cache size  : 12288 KB

When I type free -m

             total       used       free     shared    buffers     cached
Mem:          7972       3052       4920         46        226       2039
-/+ buffers/cache:        785       7186
Swap:            0          0          0

When I ssh in

System load:  0.28                Processes:          229
  Usage of /:   13.9% of 126.32GB   Users logged in:    1
  Memory usage: 10%
  Swap usage:   0%


texts: Specifying columns in LEFT JOIN through subquery beneficial performance wise?

texts:
I'm trying to understand how JOINS are handled, and was wondering about the following:

Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty important query. What would be faster in the long run. The 'normal':

SELECT
    TOP x
    A.a
    , A.xxx
    , B.a
    , B.xxx
FROM A
LEFT JOIN B ON (A.xxx = B.xxx)
ORDER BY A.a

Or specifying the columns of the joined table beforehand?

SELECT
    TOP x
    A.a
    , A.xxx
    , B.a
    , B.xxx
FROM A
LEFT JOIN
(
    SELECT
        B.A AS A
        , B.xxx AS xxx
    FROM B
 ) AS B ON (A.xxx = B.xxx)
ORDER BY A.a

I guess this boils down to having a good understanding of how SQL Server handles optimization. In my preliminary tests I haven't found any difference, but these tests aren't quite as complicated as 'The Real Thing'. Will the latter just be optimized to the former by the interpreter?

Since I have never seen the latter side being used (but admittedly, I'm really new to this), my gut feeling tells me the first one will be preferred, but a gut feeling won't (or shouldn't) hold up as real evidence.

Bare in mind the real query really has alot of tables involved with probably only 1 or 2 relevant columns per table, so I was thnking perhaps I can doe some sort of preselection to avid including the whole table in the query.

So, any ideas?


texts: pgpool-II configuring multiple master databases

texts:
I could not find a solution for this anywhere, not even in the pgpool manuals.

How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master should have its own set of slaves using streaming replication. The reason for multiple master is that each master holds a partition of a large data set.

It is a large data set (say about 100 million users, their account information, plus additional information about their accounts) which is partitioned; so it is siloed. The data model is being designed so that the silos do not need to talk to each other.

Any help and guidance is much appreciated.


texts: Find fragmented space free below HWM Oracle

texts:
That High Watermark Value always eludes me when calculating free space in my tablespaces.

Will the following query consider fragmented free spaces of object below the HWM value for each TS?

select * from sys.sm$ts_free;

(That would be in Oracle 10gR2)

In short, I want to know how much (the total, over and below the HWM) space does Oracle have to keep inserting things.

If my query is wrong, can someone provide one that does that?

pd. I also have other messy queries summing the segment sizes of objects, but then again, I do not know how all this wraps together with fragmented space and HWM. An explanation here would be highly appreciated.


texts: ibdata1 corrupted, recover from ib_logfile?

texts:
A failing hard drive have left me with a corrupted ibdata1 file. I know where the file starts but not where it ends. The hard drive recovery software claims the file is larger than the hard drive itself, leaving me to the conclusion that I can't trust the integrity of the file at all.

The .frm and ib_logfile0 and ib_logfile1 files are safe and sound, though.

Is it possible to recreate the table data from the ib_logfile[0,1] files, or should I cut my losses?


texts: mysql db backup excluding specific tables

texts:
Are there any mysql scripts for taking a database backup by excluding specified tables?

Also, what is the best way to take backup of a running mysql system?


texts:
OK I was adding an index to a table, and I checked on progress using show processlist and under state it output Repair by sorting.

From the MySQL docs:

  Repair by sorting

  The repair code is using a sort to create indexes.

So what is the 'repair code'? Did something break?


texts: Unable to change engine to MyISAM in MySQL

texts:
I have  installed MySQL 5.6.10 on Mac OS X 10.6.5. My issue is that MySQL is using InnoDB as its default engine.

I have checked following engines are supported in MySQL:

show engines\G

*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

I want to change it to MyISAM, so I have changed the setting in my.cnf:

[mysqld]
default-storage-engine=MyISAM

After that I restarted MySQL from the system preferences. But if I check check the engines again and create a table, it is still being created with InnoDB, not MyISAM.

Can anyone tell me how would I change it to MyISAM?

Here is my my.cnf file content:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
default-storage-engine=MyISAM

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# sql_mode=STRICT_TRANS_TABLES


texts: Need to Display Query Result without Null Values in one column

texts:
I created a query which produced four column result, I wanted to display result in which the Outstanding column > 0.  My query below:

SELECT
CAST(xx_master_tickets.CreatedMoment AS DATE) TicketDate,
Outstanding =  COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 1 Then 1 End),
Closed = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 2 Then 1 End),
Cancelled = COUNT(Case when XX_MASTER_TICKETS.CurrentStatus = 3 Then 1 End)
FROM         XX_MASTER_TICKETS INNER JOIN
             XX_DEF_ATM_LIST ON XX_MASTER_TICKETS.MachineID = XX_DEF_Machine_LIST.MachineID INNER JOIN
             XX_DEF_BRANCHES ON XX_DEF_Machine_LIST.BranchID = XX_DEF_BRANCHES.BranchID
WHERE     (XX_MASTER_TICKETS.CreatedMoment BETWEEN '01-01-2014' AND GETDATE()) and (XX_DEF_BRANCHES.BranchName = 'New York')
Group by CAST(xx_master_tickets.CreatedMoment AS DATE)
Order by CAST(xx_master_tickets.CreatedMoment AS DATE)

Result below:

TicketDate  Outstanding Closed  Cancelled
8/24/2014   0            65         1
8/25/2014   1            83         3
8/26/2014   0            77         3
8/27/2014   1            70         0
8/28/2014   4            88         1
8/29/2014   1            35         3
8/30/2014   2            65         2
8/31/2014   7            92         4
9/1/2014    0            77         2
9/2/2014    0            79         1
9/3/2014    39           44         2
9/4/2014    18           17         0

Result i wanted is below:

TicketDate  Outstanding Closed  Cancelled
8/25/2014   1            83         3
8/27/2014   1            70         0
8/28/2014   4            88         1
8/29/2014   1            35         3
8/30/2014   2            65         2
8/31/2014   7            92         4
9/3/2014    39           44         2
9/4/2014    18           17         0

Basically tracking dates with only outstanding values.


texts: Is it possible to Clone a RedShift Database?

texts:
For some test and development work we would like the ability to routinely clone existing RedShift databases (all schema and all data).  Something like a backup-restore.

The best I can find starts by creating a snapshot of the entire cluster, which is not practical at this point in time.

We do have scripts for creating the database and the schema, but we don't have a simple/tidy mechanism for populating the tables from another AWS RedShfit database.  (We've found that we can't use INSERT INTO db_new.schema.table SELECT * FROM db_old.schema.table as cross-database syntax is not supported)

Does anyone have any suggestions?


texts: Getting "Cannot open datafile for read-only" for an intentionally DROPped InnoDB table

texts:
It's been too long to remember how we got here accurately, but we get the following error on MySQL start (though it does still start the service with InnoDB):

[ERROR] InnoDB: Cannot open datafile for read-only: './oldschema/tablename.ibd' OS Error: 71

The table it is referring to used to exist there, but was renamed into a different schema and then dropped, or possibly just dropped (foggy memory) in order to get all references to it to follow into the other schema. Ultimately, the table was drop-swapped for one intentionally established as a copy via replication.

It doesn't appear in information schema, doesn't appear in the SHOW TABLES list, can't be queried with SELECT statements, and DROP/ALTER TABLE for that name in that schema (after getting a view out of the way) just gets us:

mysql> drop table oldschema.tablename;
ERROR 1051 (42S02): Unknown table 'oldschema.tablename'
mysql> ALTER TABLE oldschema.tablename DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'oldschema.tablename' doesn't exist

...exactly as we expect. There is, however, now a view by the same name in its place (which refers to the new table in the other schema), and it has a corresponding .frm file, and of course, no .ibd file, as we would expect.

Further, the table in the new schema works just as expected, as does the view in the old schema that aliases it. In fact, as far as we can tell, this is causing no problems...we just want to stop worrying whoever sees the log. We can even create and drop a table in its place (after moving the view and before putting it back), and still get that error.

So why does MySQL expect an ibd file by that name in the old schema?

How do we convince it to forget?


texts: Can I limit SQL server one database resources

texts: I have SQL server 2014 with resources 90G of RAM for the whole database engine, engine contain around 15 database instance, I want to make databases X to have at max 5G of RAM while other databases still share resources. please advise how to make it if its applicable .


texts: Using GETDATE() in a variable returns 0 rows

texts:
I have a query that declares and sets a datetime variable at the very beginning:

declare @_NextAppt as datetime
set @_NextAppt = dateadd(dd,3,getdate())

when I check it and execute a select @_NextAppt it returns three days from today which is what I want.

The problem occurs in my next select statement when I use

where table.apptdate = @_NextAppt

The query returns no rows.

Here's the sample query:

select distinct PatID, StaffID
from dbo.Appointments
where PatID is not null and ApptDate = @_NextAppt

I can get everything to work if I replace getdate() with an exact date like '11/09/2016'. But that doesn't work long term for my report.

What am I missing?  I'm relatively new to SQL and self taught so I'm sure there is some SQL quirk that I don't know about.


texts: Does teradata provides free or trial setup of multinode (at least 2 node) environment

texts:
I want to test my code on Teradata on 2 node environment. It doesn't need a power of multiple AMPs multiple nodes ( more than 2 ).
Like Teradata provides VM of single node setup of its every version,
does it provide vm for 2 node setup


texts:
I am looking for advice on storing rows of data.

What I have a bunch of users (id), and data about their activities.   Each user can perform a type of activity (type_id).  And then about 20 columns of integer data related to the type of activity.

This next bit is slightly wrong.  Originally I said: I currently fetch the data and store it once per hour, so I have a unique index over 3 columns (id, type_id, timestamp) + the 20 integers.

But actually I currently fetch and store the data every 15 mins, but only keep the last record for each hour.  I have been doing this with "on duplicate key update"

The data for type_id might not change for hours, days, weeks or even never again.  Some users have 3 or 4 types, others 20 or more types of activities.  In a typical hour I am adding 1000's of rows of data, most of which are duplicates (eg when sleeping none of the users data changes but I still store a row for each type_id for each hour, each row contains the 20 identical integers)

I need to look at this data and generate graphs and other reports so I am storing all the data even though it is mostly duplicates but I think I am looking for a way to do the reverse of "insert or on duplicate key update" where I only insert a new row if the integer data is different for each type_id for each user id.

If I could do that, can I then use SQL to fill in the blanks when I select data for a time period? Buy that I mean if users A type B has not changed since January I don't want to store rows for them in February and March, but still be able to select and get data for those months. In other words select the last row before a given timestamp?

Are there better ways to store and retrieve this data?


texts: Is there any way to collect script of running SP with it's input value using DMVs?

texts:
I am using below script to collect information about active sessions. It returns Query/SP (script), it's estimated E-Plan, resource consumption and other information.

I know to use profiler to collect script of SP with it's input value but because performance of SP varies on different input value, `it's not good idea to run profile for long time, so I want to collect it using DMV.

Is there any way to get it (exec sp_name input1, input2, input3, ..) using DMV?

select
            db_name(sp.dbid),sp.spid,er.wait_type,er.wait_time,er.wait_resource,er.total_elapsed_time,st.text,qp.query_plan
            ,ec.net_packet_size,ec.client_net_address,es.host_name,es.program_name,es.client_interface_name
            ,es.status,es.cpu_time,qmg.granted_memory_kb,es.total_scheduled_time,es.total_elapsed_time
            ,es.reads,es.writes,es.logical_reads

from
                sys.dm_exec_requests er
inner join      master.dbo.sysprocesses sp
on              er.session_id=sp.spid
inner join      sys.dm_exec_connections ec
on              er.session_id=ec.session_id
inner join      sys.dm_exec_sessions es
on              ec.session_id=es.session_id
inner join      sys.dm_exec_query_memory_grants qmg
on er.session_id=qmg.session_id
cross apply     (select text from sys.dm_exec_sql_text(er.sql_handle)) st
cross apply     (select * from sys.dm_exec_query_plan(er.plan_handle)) qp

Thanks


texts:
My friends and I are building a database for statistical research.
So we have a user that answers 10 questions in a row.
The questions are face-images, one is mirrored and the other one is the original, and the user should answer what the original is. From the images we know, where it lies (URL) the id and the sex.

He can participate as many times as he wants, so we need to save all his answered questions.

We want to save how many things he got right, how many he got wrong, and see if it is easier for the user to differentiate between women or men faces.

We want to store as many information as possible, without a lot being redundant.

Here is our current ER -Modell and we are not really sure if it is correct.

I would appreciate any help


texts: Eliminate duplicates in ListAgg (Oracle)

texts:
Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row.  11.2 Added the LISTAGG function, so I am trying to use that instead.  My problem is that I need to eliminate duplicates in the results and don't seem to be able to do that.

Here is an example.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual
     CONNECT BY rownum<=6
  );
SELECT * FROM ListAggTest;

      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    << Duplicate 2
         3 3
         4 4
         5 5
         6 6

What I want to see is this:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6

Here is a listagg version that is close, but doesn't eliminate duplicates.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s
FROM ListAggTest;

I have a solution, but it's worse than continuing to use the custom aggregate function.