texts: Is it safe to restart PostgreSQL if process terminated for some reason?

texts:
I am writing a docker-compose config with a container with PostgreSQL in it. docker-compose has a configuration options called restart. If I enable it then whenever the Postgres process stops it will restart Postgres rightaway. However it is (probably) not relevant how it is restarted.

I know that in MySQL it is not safe to start the database right after it stopped because MyISAM tables may be broken. One has to manually repair MyISAM tables first with an unrelated command-line tool and then start the db.

So I wonder if it is generally safe to just restart PostgreSQL in case something is wrong. Or maybe we should first backup, repair, read logs and only then try to start it back.

A related situation would be a power outage for instance. What do I do after that?


texts:
What is the recommended way to back up large data sets in MongoDB? Let's say we have a data size in the order of 10TB - how would you back that up?

We're considering a hidden, possibly delayed, replica set node. The delay would protect us from accidental drops of the whole database. Is this a viable solution, and what other options would you recommend investigating?

Thanks!


texts: MySQL GROUP BY and ORDER BY giving inconsistent results

texts:
I'm currently working with WordPress, and I've added a new meta value which is a number between 0 and 30. Zero is ignored and up to 30 need to be organised in order and only the latest (based on the date) has to be returned. I.e. if we have 5 entries, 2 of which have the _position 2, it should return only the latest one (based on a date field).

The current SQL I have been able to generate through WordPress is the following:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
  FROM wp_posts
    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
  WHERE 1=1 AND wp_posts.post_type = 'post' AND
    (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND
    ( (
      wp_postmeta.meta_key = '_position' AND CAST(wp_postmeta.meta_value AS UNSIGNED) > '0'
    ) )
  GROUP BY wp_postmeta.meta_value+0
  ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

Now that works fine for the most part, except, I'm getting weird results. The results below are grouped by wp_posts.ID (as WordPress does by default). The fields below are in format: Position: Title - date - time. Note date and time are saved as one field.

1: Title - August 10, 2012 - 10:24 am
1: Title - December 8, 2011 - 4:48 pm
2: Title - August 16, 2012 - 12:56 pm
2: Title - August 7, 2012 - 10:08 am
11: Title - March 28, 2012 - 2:11 pm
12: Title - February 27, 2012 - 2:12 pm

However, when I group it by wp_postmeta.meta_value+0 it somehow doesn't get the right option for some of them as shown below.

1: Title - August 10, 2012 - 10:24 am
2: Title - August 7, 2012 - 10:08 am
11: Title - March 28, 2012 - 2:11 pm
12: Title - February 27, 2012 - 2:12 pm

What I need it to return is something like this:

1: Title - August 10, 2012 - 10:24 am
2: Title - August 16, 2012 - 12:56 pm
11: Title - March 28, 2012 - 2:11 pm
12: Title - February 27, 2012 - 2:12 pm

As you can see it returns the one with the latest date. I've tried swapping the order of the date to ASC but I'm getting the exact same results.

What is going wrong here and how can fix it?

Thanks in advance.

EDIT:
The SQL used for the first results:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
  FROM wp_posts
    INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
  WHERE 1=1 AND wp_posts.post_type = 'post' AND
    (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND
    ( (
      wp_postmeta.meta_key = '_position' AND CAST(wp_postmeta.meta_value AS UNSIGNED) > '0'
    ) )
  GROUP BY wp_posts.ID
  ORDER BY wp_postmeta.meta_value+0 ASC, wp_posts.post_date DESC LIMIT 0, 31

Also, the tables are from wp, I've copied the structure to pastebin: http://pastebin.com/60tYev1P

Thanks again!


texts: Add a New Column with ALIASES on 'date' by automation based on search results

texts:
I am trying to create a new column based on date search result.

I have written a code in the link SQL fiddle as I am looking for date automation on each column based on the date format. the date format Coloum can be up to 7 column or less or more, just based on how many dates in the record.

SQL fiddle

Here is a screenshot as I would like to get this similar and the data is based on the number of sold SKU.


texts: Uninstalling SQL Server Cumulative Update Issue

texts:
When I am trying to uninstall a CU on my SQL Server 2017 server getting below issue. Has anyone faced this problem and please guide me on how can I proceed. I don't have access the SQL 2017 image right now.

Thanks..


texts:
I have 2 databases .mdf SQL Server 2000, and I need to restore them on SQL Server 2014. After some research, I saw that the best approach is making a .bak of this 2 files.

Well, I need to install SQL Server 2000 to do this. I tried some youtube tutorial videos but without results.

Can you show me a way to do this?


texts: sql server 2012 developers edition license and 3rd party applications

texts: Our company is using an ERP system for our product inventory management and customers orders. The newest version uses MS SQL 2012. if we are not developing software and the only users accessing the MS SQL 2012 database is the 3rd party application (ERP system) can we use the Developers Edition of SQL 2012?


texts: Failed sending an email to outlook account using SQL server management studio

texts:
I have been trying to send an email to my outlook account using SQL server management studio but keep facing with error like: The smtp server requires a secure connection or the client was not authenticated 5.7.57; client was not authenticated to send anonymous mail during MAIL FROM [__.prod.outlook.com] even though I have ensured that everything was inputted correctly.

Here's the query:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Test_Email',
    @description = 'Profile used for sending outgoing notifications using Outlook.' ;
GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Test_Email',
    @principal_name = 'public',
    @is_default = 1 ;
GO

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Outlook',
    @description = 'Mail account for sending outgoing notifications.',
    @email_address = 'mail@mycompany.com',
    @display_name = 'Automated Mailer',
    @mailserver_name = 'smtp.office365.com',
    @port = 587,
    @enable_ssl = 1,
    @username = 'mail@mycompany.com',
    @password = 'password' ;
GO

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Test_Email',
    @account_name = 'Outlook',
    @sequence_number =1 ;
GO

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'Test_Email',
     @recipients = 'mail@mycompany.com',
     @body = 'The database mail configuration was completed successfully.',
     @subject = 'Automated Success Message';
GO

sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO

SELECT * FROM msdb.dbo.sysmail_event_log;

Can anyone advise what went wrong?

Thank you in advance!

Edited: Added @ to email addresses!


texts: Simple query on sys.dm_db_partition_stats is running for 30 minutes

texts:
Simple query on sys.dm_db_partition_stats on user database is running for close to 30 mins where as it used to complete in less than 5 seconds earlier.
Query is just to fetch the right partition for loading the data and is not reading any data from user databases. Same query ran and completed in less than 5 seconds on any other large user database. Looking at the execution plan for this db and query output messages , we observed for one Table 'sysrowsets' . Scan count 1, logical reads is hundreds of thousands where as on other databases, it is less than a thousand.
Here is the output for reference.

Table 'sysrowsets'. Scan count 888, logical reads 3227880, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

We are unable to query this sysrowsets directly as it is a underlying table. Able to query when connected via DAC but couldn't find any thing abnormal from this .
Still wondering what is the reason for this many reads and why is it taking such long time.
SQL Server Version : 2019, Compatibility Level : 150
SELECT Max(pstats.partition_number) AS PartitionNumber
FROM sys.dm_db_partition_stats AS pstats(NOLOCK)
INNER JOIN sys.destination_data_spaces AS dds(NOLOCK) ON pstats.partition_number = dds.destination_id
INNER JOIN sys.partition_schemes AS ps(NOLOCK) ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf(NOLOCK) ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS prv(NOLOCK) ON pf.function_id = prv.function_id
AND pstats.partition_number = (
CASE pf.boundary_value_on_right
WHEN 0
THEN prv.boundary_id
ELSE (prv.boundary_id + 1)
END
)
WHERE OBJECT_Name(pstats.object_id) = 'OBJECTNAME'
AND OBJECT_SCHEMA_NAME(pstats.object_id) = 'DBNAME'


texts:
I managed to get MongoDB running as a three member replica on Windows 8, but I had problems with configuration files. If I used the new (MongoDB 2.6) file format (YAML) and used the replica set name option as:

replication:
    replSetName: rs1

(or any of the other variants I had tried in YAML format) I would get "Invalid option: replication" error during the installation. When I switched to old conf. file format and used:

replSet=rs1

I had no problems.
I am very (very!) new to all this, so I am wondering if I was doing something wrong or is it Mongo 2.6 - Windows problem. Thanks!


texts: Get unique rows based on max of a column in mysql

texts:
I came across many solutions like the one in this question. However, they all are restricted to data from one table. I'm very very new to SQL. I'd like to get the same results as in the linked question with two tweaks.

The column 'User' is from table1, 'Date' from table2, the rest from table3.
I need to input a list of 'User'(s) for which the data is to be extracted.

I have really huge tables on which I need to work. I've tried distinct and group by but they are taking forever.
I really appreciate any help.

Sample data:

table1

Global_id       User    User_id     table_id    table_type  Disease         source
1               TT964   -1          555         1           Breast cancer   1
3               TT2345  -1          876         1           Lung cancer     2
5               TT083   TT083-01    123         1           Brain tumor     4
87              TT4456  -1          4843        1           Cardiac arrest  7
65              TT6688  -1          5850        1           Brain tumor     4
350             TT4678  -1          969         1           Cardiac arrest  7
678             TT4567  -1          3985        1           Lung cancer     2

table2

fid gid gene
3   1   BRCA1
6   23  TP53
7   4   APO
1   6   APC
6   72  HFE
4   96  KRT5

table3

table_id    m_id    f_id
555         1       3
876         1       1
123         1       6
875         2       6
409         1       4
3985        2       3

Tried this query the latest:

SELECT distinct a.User, f.fid, table_type, f.gene, s.table_id, s.m_id, Disease
FROM data03.table3 s
inner JOIN data01.table1 a
inner join data01.table2 f
where f.fid in
  (SELECT s.f_id FROM data03.table3 s
   where s.table_id in
     (SELECT a.table_id FROM data01.table1 a
      where a.User in ("TT964","TT083","TT6688"))
   and s.m_id in (1,2,3,4,5,6)
   and table_type = 1
   and Disease like "Brain%"
   and fid = 623
   and a.User in ("TT964","TT083","TT6688"))

where fid and f_id are the same. I need to get data where the table_ids match for table_type 1 and disease brain cancer for users and a few other conditions mentioned in the query. My code could be quite messy as I'm pretty new to SQL again.

Here's the show create table data:

CREATE TABLE `table1` (
  `Global_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `User` varchar(128) NOT NULL,
  `User_id` varchar(255) NOT NULL,
  `table_id` bigint(20) NOT NULL,
  `table_type` smallint(6) NOT NULL,
  `Disease` varchar(255) NOT NULL,
  `source` int(11) DEFAULT NULL,
  PRIMARY KEY (`Global_id`),
  KEY `fk_table_id_table_type` (`table_id`,`table_type`),
  KEY `fk_User` (`User`)
);

CREATE TABLE `table2` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `gid` int(11) DEFAULT NULL,
  `gene` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`fid`),
  KEY `ix_gid` (`gid`),
  KEY `ix_gene` (`gene`)
);

CREATE TABLE `table3` (
  `table_id` bigint(20) NOT NULL,
  `m_id` smallint(6) DEFAULT NULL,
  `f_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`m_id`),
  KEY `idx_f_id_table_id` (`f_id`,`table_id`)
);


texts: Unable to access SSRS web URL outside Azure VM

texts:
I've configured SSRS in SQL Server 2016 on Azure VM. However I am unable to access the web URL outside of the VM. Since it is my test box I've tried disabling the firewall as well. Still didn't help.

Any suggestions where to look? I've attached screenshot of my browser in my laptop.

The URL is accessible inside the VM where Report Server and Database Engine is Installed.


texts: single query using OR operator vs multiple queries using equals operator

texts:
Case 1 - Single query using OR operator in where clause:

select * from users where name='smith' or nick='smith' (using index_merge )

Case 2 - multiple queries using equals operator in where clause:

select * from users where name='smith'; (using single index);

select * from users where nick='smith'; (using single index);

Q: Is there any performance loss in Case 2?


texts: What is the best database design to keep track of fields with multiple values?

texts:
I am trying to dynamically build a form with a label (field) and a user entered value (fieldValue) but the form can have several different control, like a combobox, textbox, listbox, checkbox, etc....

Here is my schema right now

Page
* PageId

Field
* FieldId
* FiledTypeId
* FieldName

FieldType
* FieldTypeId
* FieldTypeName (Combobox, Textbox, etc...)

FieldValue
* FieldValueId
* FieldId
* PageId
* FieldValueValue
* CurrentValue (This is a bit because a combobox could have multiple values but I wanna show which one is selected. Similarly, a listbox could have multiple values selected, a textbox would not have any values selected.)

The problem with my design is that every time I have a form using the same fields, I have to add all of the items in the combobox again as it is a new field, specific to a new page AND when I create a new page how will it know that "these" values belong to a combobox, for example.  I've thought of having a 0 to many table to keep track of comboboxes and things with multiple values and then my field value table will only hold one value per field and the CurrentValue column will be gone but I'm not 100% sure if this is correct.

Any suggestions?


texts:
I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID.

For example, say I have a users table and login_coordinates table. Every time the user logs in, the most current coordinates get fed to a column in the users table, as well as inserting into the login_coordinates table.

I only want to keep the last 10 records, and deleting the 11th(oldest) record in the login_coordinates table for all users.

Users

   user_id            | current_coordinates        |
----------------------+----------------------------+
    1                 | aaaa.bbbbb, aaaaa.bbbbbb   |
    2                 | zzzz.xxxxxx, xxxxx.xxxcxx  |
    3                 | dddd.xxxxxx, xxxxx.xxxcxx  |

Login Coordinates

  coordinates_id      | old_login_coordinates      |        user_id           |
----------------------+----------------------------+--------------------------+
    1                 | aaaa.bbbbb, aaaaa.bbbbbb   |     1                    |
    2                 | xxxxx.xxxxxx, xxxxx.xxxcxx |     1                    |
    3                 | xxxxx.xxxxxx, xxxxx.xxxcxx |     1                    |

Is there anything that would limit the records to 10 coordinate_id per user, always deleting the oldest records?

I'm using PostgreSQL 9.5.


texts: Why does sql server need to convert count(*) result into int before comparing it with an int variable?

texts:
I have many queries in my application where in the having clause, I have comparison of count aggregate function with int variable.
In the query plans, I can see an implicit_convert before the comparison.
I want to know why this happens because as per sql server documentation, the return type of count function is int. So why should there be an implicit conversion for comparison of two int values?

Following is a part of one such query plan where @IdCount is defined as an int variable.

|--Filter(WHERE:([Expr1022]=[@IdCount]))
 |--Compute Scalar(DEFINE:([Expr1022]=CONVERT_IMPLICIT(int,[Expr1028],0)))
  |--Stream Aggregate(GROUP BY:([MOCK_DB].[dbo].[Scope].[ScopeID]) DEFINE:([Expr1028]=Count(*)))


texts: I have a table with more than million rows. I need to copy data to temporary table, make changes to table structure and copy back all data. This table is on transactional replication. And I wanted to know how deleting and copying data will affect the replicated data. If I will turn off the replication, will the data in replicated table be deleted and copied back?


texts: Are Delete operations written to AppendOnlyFile in Redis?

texts:
AppendOnlyFile in Redis logs every write operation done to the redis database.

My Question is that when we delete data from the redis database, are those operations logged in the AppendOnlyFile ? And the data which is deleted from the database, is the record corresponding to that data in the AppendOnlyFile is also deleted?

For example,

I set a key,

127.0.0.1:6379 > set a apple

This will be logged in the AppendOnlyFile, suppose as "Log:set:a:apple"

Now, If I do,

127.0.0.1:6379 > del a

What will this do?
Will a log corresponding to this be made, like "Log:del:a" ?
And once we delete the key, will the log "Log:set:a:apple" also get deleted ?


texts: Can I include extra columns in a m->m mapping table?

texts:
I guess this is sort of an odd question, but consider this scenario.

table classes (id_pk)
table students (id_pk)
table students_classes_map (students_id_fk_pk, classes_id_fk_pk, finalExamScore decimal, finalGrade decimal ...)

I am using unique combinations of foreign keys to map students to all classes that they ever took. Standard practice. But, say that I wanted to store some statistics for students and their classes, statistics that didn't change after a class ended.

Is what I did ok practice or should map tables simply map m2m relations and not include any other columns? I.e. should I create a new table called player_classes with an autoinc int pk as a one to many table that holds some data for a user in a particular class? (Grade, attendance, etc) and separate this data from the student_class_map table


texts: How to force higher values of open_files_limit or table_open_cache?

texts:
I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'.

This is MySQL 5.6.14 on MacOS X 10.8.3

I'm trying to address dropped connections and other seemingly capacity related issues.

For example:  My database's current stats are:

mysql> SHOW GlOBAL STATUS LIKE "%open%";

+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Com_ha_open                | 0      |
| Com_show_open_tables       | 0      |
| Innodb_num_open_files      | 1581   |
| Open_files                 | 5557   |
| Open_streams               | 0      |
| Open_table_definitions     | 4199   |
| Open_tables                | 5000   |
| Opened_files               | 54805  |
| Opened_table_definitions   | 4247   |
| Opened_tables              | 8515   |
| Slave_open_temp_tables     | 0      |
| Table_open_cache_hits      | 104991 |
| Table_open_cache_misses    | 8479   |
| Table_open_cache_overflows | 3459   |
+----------------------------+--------+

My current my.cnf values are:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=128M
wait_timeout=57600
table_open_cache = 5000
table-definition-cache = 10000
open_files_limit = 10000
max-connections = 15
thread-cache-size = 16K
max-heap-table-size = 6024M
tmp-table-size = 6024M
query-cache-size = 256K
query-cache-limit = 16M
key_buffer = 512M
long-query-time = 5
join-buffer-size = 1M
innodb_buffer_pool_size = 3046M

And that produces:

mysql> SHOW VARIABLES LIKE '%open%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| have_openssl               | DISABLED |
| innodb_open_files          | 5000     |
| open_files_limit           | 10025    |
| table_open_cache           | 5000     |
| table_open_cache_instances | 1        |
+----------------------------+----------+
5 rows in set (0.00 sec)

Which is at it should be.

I would like to raise my open limits to

table_open_cache = 10000
table-definition-cache = 20000
open_files_limit = 60000

But when I do the my.cnf seems to invalidate and mysql reports:

mysql> SHOW VARIABLES LIKE '%open%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| have_openssl               | DISABLED |
| innodb_open_files          | 400      |
| open_files_limit           | 256      |
| table_open_cache           | 400      |
| table_open_cache_instances | 1        |
+----------------------------+----------+
5 rows in set (0.00 sec)

Which obviously kills my server.


texts: Use of '@' '.' and 'public operator/keywords in Oracle database?

texts:
I got into a weird SQL query-patch which looks for @ . and public keywords.  I was wondering what is the purpose of these operators/keywords?

I suspect that it might be looking for SQL injection or some weird buffer overflow
but I can't seem to understand why it looks for those characters, etc, and I can't find much information about those keywords.

If anyone could enlighten my eyes, it will be very helpful.


texts: SQL Server Agent - remotely starting jobs from machine outside AWS fails with Semaphore Timeout

texts:
Starting the same job using SSMS on the machine directly always works. Can't figure out why. Is there some firewall rule I need to allow?

It works via SSMS when logged into the box directly. It does not work via SSMS when the SSMS is on a separate box outside the AWS sub-network. We have a private line, but this problem has been happening since even before we activated the private line and just had a tunnel.

Pretty annoying the help links forward to ads to buy more Microsoft stuff.

  TITLE: Microsoft.SqlServer.Smo

  Start failed for Job 'XXXXXXXXXXXXXXXXXX'.

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17177.0+((SSMS_Rel).170803-0429)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

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

  A transport-level error has occurred when receiving results from the
  server. (provider: TCP Provider, error: 0 - The semaphore timeout
  period has expired.) (Microsoft SQL Server, Error: 121)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=121&LinkId=20476

  The semaphore timeout period has expired

  ------------------------------ BUTTONS:

  OK

Update based on feedback from a Microsoft employee

Here is a reproducible .sql script file:

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'XXXXXXXXXXXXXXXXXX',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_page=2,
        @delete_level=0,
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'XXXXXXXXXXXXXXXXXX', @server_name = N'DEVSQL1'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'XXXXXXXXXXXXXXXXXX',
@step_name=N'Hello, World',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_fail_action=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'print ''Hello, World''',
        @database_name=N'master',
        @flags=0
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'XXXXXXXXXXXXXXXXXX',
        @enabled=1,
        @start_step_id=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa',
        @notify_email_operator_name=N'',
        @notify_page_operator_name=N''
GO

EXEC msdb.dbo.sp_start_job @job_name = 'XXXXXXXXXXXXXXXXXX'
GO


texts: Will frequent delete and inserts of the same data lead to fragmentation?

texts:
I have a table which caches images from another data source:

CREATE TABLE [dbo].[ProductImages] (
    [ProductId] INT NOT NULL ,
    [ImageId] INT NOT NULL,
    [Image] VARBINARY(MAX) NOT NULL,
    PRIMARY KEY ([ProductId], [ImageId])
)

The images are served together with [Product] data in the form of:

SELECT [Image] FROM [ProductImages] WHERE [ProductId] = 123

Now, the problem is that the images from the data source may sometimes be updated, and there is no trigger-like or event-like mechanism to detect this change. So, my solution is to have a scheduled job which periodically downloads images from the source. Since the images are pre-processed (format changed, resized) before being stored into the database, I have no way of actually knowing whether the image has changed. Therefore, every time I do the following:

DELETE FROM [ProductImages] WHERE [ProductId] = 123
INSERT INTO [ProductImages] VALUES (123, 0, {Image_0})
INSERT INTO [ProductImages] VALUES (123, 1, {Image_1})

Most of the time (say at least 98%), there has been no change - I am just deleting and re-inserting the same data for nothing.

If this scheduled job is executed long enough, will it eventually lead to poor table performance because the data is scattered all over the place?


texts:
I am trying to estimate storage requirements for the data our application will store. After doing some research I can pull out data from the stats table. However, I don't understand how those numbers are calculated. For example, if I do a simulation as follows:

~2000 document write:

written_bytes_total: 40,668,160
data_bytes: 3,804,672
metadata_bytes: 8,388,608

Q1. Looking at the above numbers I get roughly 20,334B = 20KB per document, which is quite a bit - the document stored has a six UUID(4) values, 3 dates, and a couple of booleans, but nothing that will require KBs (like text blobs). Is there an overhead per document that RethinkDB adds on?

Q2. How is it that written_bytes is 40MB, but data_bytes under "storage" are 3.8MB? I thought they might be cached (and not persisted), but cache size is 3,253,800B, which also does not add up.

So I guess my question, how can I estimate how much space will be required for 2K/10K/100K documents for a given table in RethinkDB? Which value(s) should I be looking at? I am not looking for byte precision, but would really like to know how those numbers are calculated. Thanks!

Result of running a query on TableA:

r.db('rethinkdb').table('stats')
  .filter(function(row){
      return row('id')(0).eq('table_server').and(
             row('table').eq('TableA'))
})

"query_engine": {
  "read_docs_per_sec": 0 ,
    "read_docs_total": 0 ,
    "written_docs_per_sec": 0 ,
    "written_docs_total": 1974
} ,
  "server":  "MacBook_Pro_local_b0b" ,
  "storage_engine": {
    "cache": {
      "in_use_bytes": 3253800
    } ,
    "disk": {
      "read_bytes_per_sec": 0 ,
      "read_bytes_total": 171520 ,
      "space_usage": {
        "data_bytes": 3804672 ,
        "garbage_bytes": 389632 ,
        "metadata_bytes": 8388608 ,
        "preallocated_bytes": 6291456
      } ,
      "written_bytes_per_sec": 0 ,
      "written_bytes_total": 40668160
    }
  }


texts: Changing collation of SQL Database in Production environment to minimize downtime

texts:
We have a scenario in which we want to change the collation of our production database (including columns) from SQL_Scandinavian_Pref_CP850_CI_AS to Finnish_Swedish_CI_AS. We have developed scripts to do that. But executing this script in a large database of size more than 100GB will take considerable time and we can't afford to take very long downtime. So we decided to reduce this downtime using the below strategy:

We will setup Transactional Replication and we will initialize the subscriber using the database backup method.
Publisher database will be live with the application and its transactions will be delivered to the subscriber database through Transactional Replication.
We will execute the Collation Change Script on the subscriber side, and it does allow us to execute this script when SQL Server is the same for publisher and subscriber databases. We recently found this in SQL Server 2019.
Now, the pain point is, it's not replicating correctly the varchar, char column's data when it contains special characters like 'åÅäÄöÖ'. On the subscriber side, we are getting weird characters like '†„Ž”™'

Can you please suggest how we can resolve this bug or any alternative architecture to minimize the downtime in production while changing the database collation(including columns)?
Furthermore, my collation change script is doing the following tasks on the subscriber database to change its collation:

Drop Foreign Key Constraints
Drop indexes including Primary Key
Drop check and default constraints
Drop user statistics
Drop views, computed columns, SPs to resolve Object Bound bugs
After execution of the above steps, the Tables are ready for collation change. So, the script will change the collations of the columns for every table one by one.
Recreate above listed constraints after successful execution of step 6.


texts: In mysql system_time_zone doesn't reflects DST changes, isn't it a bug?

texts:
If a Ubuntu machine was in 'PST' timezone and on 10 march 2019 at 2:00 AM its timezone gets changed to 'PDT' timezone to accommodate DST changes then does this change is immediately reflected in SYSTEM_TIME_ZONE of mysql 5.7 server running on it i.e. will it also update the  value from 'PST' to 'PDT'?

As per my observation it seems the answer is 'NO' but then how does mysql  accommodates for DST changes because I can see it taking effect on 'timestamp' value being inserted.

Let me share example:

My Ubuntu machine was in 'PST' timezone and its current timezone is 'PDT' but  mysql 5.7 server running on it still shows @@global.system_time_zone as 'PST'.

However, I also see that none of records having timestamp column have value b/w 2019-03-10 02:00:00 to 2019-03-10 02:59:59 i.e. a time shift of 1hr takes place as per DST.

To summarize,
1) are my observations correct?
2) If yes then is SYSTEM_TIME_ZONE not getting updated is a bug that should be fixed?


texts:
I'm in the process of decommissioning an old mysql server.  Of course there's a few things left connecting to it periodically people had since forgotten about.  I turned on general query logging to get a better view of what was still going on there to track down the culprits and noticed something odd.

There was an entry like:

870 Connect     <user>@<server> on

It went on and did it's thing ending with a

870 Quit

The very next line was

1143 Connect     <user>@<server>

Why the jump in thread IDs?  Just grepping out all the "Connect" lines there are bursts of incrementing thread ids +1 then varying size gaps for the next connect id.

Is this expected behavior?


texts: TSQL - SQL Server 2008 Maintenance Plan - anything else?

texts:
I have a number of SQL 2008 R2 DBs that haven't had any maintenance plans run on them since birth, as my ISP's server provides a SQL version that doesn't have the Maintenance Plan option.

I've trawled through the numerous pointers, options, etc, but I just want to run something clean and simple on them. Having trawled, I've come up with the following simple TSQL script:

USE dbname

ALTER INDEX ALL ON tablename1 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON tablename2 REBUILD WITH (FILLFACTOR = 80);
etc etc for each tablename in a db

EXEC sp_updatestats;

That's it. None of the SQL DBs are bigger than 300MB each at the moment (probably a lot of bloatware in there already) and each one is backed up by another process so have I covered the bases? Is there anything I've missed?

UPDATE: on this topic I've run the above and for some reason the frag % changes on the tables each time I do it???  It goes up and down erratically per table/index - is this normal???

Regards

Tom


texts:
I am running 5.5.60-MariaDB and I need to upload a DB on the server. The .sql file is located on an SSD drive (locally) and it contains ~700 million rows, with 4 columns indexed. Everything is included in the dump file.

My question is, is it normal that it is taking already 24h and still the DB is not up? The server is not busy doing other things, has 16 cores and 125 GB of RAM.

The command I am using is:

mysql -u root myDB < database_dump.sql

My configuration file is as follows:

[mysqld]
datadir=/home/ssd/mysql_datadir
tmpdir=/home/ssd/mysql_tmdir
socket=/var/lib/mysql/mysql.sock
innodb_buffer_pool_size=4GB

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

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

# include all files from the config directory
!includedir /etc/my.cnf.d

Both the tmp and data directory are on the same SSD disk, and the sql dump as well. I do not know if this a normal time to expect, or I should change something in my settings, that it why I reach out. The engine of the DB to upload is InnoDB.

Thanks!


texts: High availability sql server, secondary replicas can't be connected on the Availability group

texts: I created an availability group (two nodes) , then my secondary replica isn't connected like on the picture. I verified on error logs and i have this error . how can I give permission to my secondary replica ? thank you.


texts:
We have installed sql server on a virtual machine and we have registered the machine so that way SQL Server IaaS Agent Extension (SQLIaaSAgent) gets enable and runs on Azure virtual machines to automate administration tasks for sql server. One of those task is automate the backups as you can see the option in the next image

Now, to perform the automated backups we need an storage account, we have created that storage account and inside the storage account a blob container, but the problem we have right now is that when we are selecting the storage where we want the backups to go we can only select the storage account but not the container, So where are the backups going? we can't find them in the storage account, how can we be sure the backups are being made? and how can we select the blob storage in this feature?. thanks in advance


texts:
CREATE TABLE approvalreasonexemption (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `personId` INT(10) UNSIGNED NOT NULL,
    `reasonId` INT(10) UNSIGNED NOT NULL,
    `createdBy` INT(10) UNSIGNED NOT NULL,
    `createdDtTm` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `modifiedBy` INT(10) UNSIGNED NOT NULL,
    `updatedDtTm` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `PersonReason` (`personId`, `reasonId`)
) ENGINE=INNODB;

On executing this i'm getting

Query: CREATE TABLE approvalreasonexemption ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `personId` int(10) UNSIGNED NOT NULL, `rea...

Error Code: 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause;

I'm using 5.6.21 mysql server.


texts: Mongo - Find is returning differents results with each request

texts:
Currently in my collection has the following spec:

Version: MongoDb - 4.0.6
Sharding: 3 shards
Shard key: Publication identifier
The shards are consistent

The problem is due using the same operation find is returning three types of results with each find.

mongos> db.publications.find({"tag":"university"},{"name":1}).skip(0).limit(3)

Example:

mongos> db.publications.find({"tag":"university"},{"name":1}).skip(0).limit(10)
{ "_id" : ObjectId("58b804a04bf7c61ab9ce85a2"), "name" : "db7e50d9" }
{ "_id" : ObjectId("58b805a44bf7c61ad5cfbbc9"), "name" : "8dfe6ba9" } // SET TYPE 1
{ "_id" : ObjectId("58b8081d4bf7c61ae3d2e215"), "name" : "0552ffdc" }
...
mongos> db.publications.find({"tag":"university"},{"name":1}).skip(0).limit(10)
{ "_id" : ObjectId("58b7d2164bf7c6346c4abad1"), "name" : "495f87f5" }
{ "_id" : ObjectId("58b8062e4bf7c61ad5d06cef"), "name" : "b61d2d6b" }  // SET TYPE 2
{ "_id" : ObjectId("58b807ee4bf7c61ab9d2b159"), "name" : "8e8c9bb0" }
...
mongos> db.publications.find({"tag":"university"},{"name":1}).skip(0).limit(10)
{ "_id" : ObjectId("58b804a04bf7c61ab9ce85a2"), "name" : "db7e50d9" }
{ "_id" : ObjectId("58b805a44bf7c61ad5cfbbc9"), "name" : "8dfe6ba9" }  // SET TYPE 1
{ "_id" : ObjectId("58b8081d4bf7c61ae3d2e215"), "name" : "0552ffdc" }
...
mongos> db.publications.find({"tag":"university"},{"name":1}).skip(0).limit(10)
{ "_id" : ObjectId("58b804a04bf7c61ab9ce85a2"), "name" : "db7e50d9" }
{ "_id" : ObjectId("58b805a44bf7c61ad5cfbbc9"), "name" : "8dfe6ba9" }  // SET TYPE 1
{ "_id" : ObjectId("58b8081d4bf7c61ae3d2e215"), "name" : "0552ffdc" }
...
mongos> db.publications.find({"tag":"university"},{"name":1}).skip(0).limit(10)
{ "_id" : ObjectId("58b7d2164bf7c6346c4abad1"), "name" : "013d86af" }
{ "_id" : ObjectId("58b8062e4bf7c61ad5d06cef"), "name" : "ab267d90" }  // SET TYPE 3
{ "_id" : ObjectId("58b807ee4bf7c61ab9d2b159"), "name" : "f27f9a10" }

The find is returning three set different with the same find, the problem is:

It is returning different 3 types of results on the first page.
I can't use the option sort (takes more of 70 seconds in order the publications. More 2B results)

I think that this behavior is due to sharding.

Do you know any way to solve it or return the same results without sort operation?


texts: Combining 2 SELECT queries and printing the results in PostgreSQL

texts:
I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to combine both queries with UNION:

Query failed: ERROR: syntax error at or near "UNION" LINE 6: UNION ^

I have had a look at a similar question but the solutions & discussions provided do not seem to work.

$name = pg_escape_string($_POST['name']); // Start destination from user input
$name2 = pg_escape_string($_POST['name2']); // End destination from user input

//If no results are given, the following query below will execute instead.

if (pg_num_rows($result) == 0 ) {

$query = "SELECT dt1.name as name1,dt1.time as time1,dt2.name as name2,dt2.time as time2
From departure_times as dt1
inner join departure_times as dt2 on dt2.tram_id = dt1.tram_id
where dt1.name = '$name' and dt2.name = 'CitySquare' LIMIT 5

UNION

SELECT dt1.name as name3,dt1.time as time3,dt2.name as name4,dt2.time as time4
From departure_times as dt1
inner join departure_times as dt2 on dt2.tram_id = dt1.tram_id
where dt1.name = 'CitySquare' and dt2.name = '$name2' LIMIT 5";
$result = pg_query($query) or die('Query failed: ' . pg_last_error());
} ...

The desired output:

------------------------------------------------------------------------
    name1 | time1  | name2  | time2 | name3 |  time3    | name4  | time4
------------------------------------------------------------------------


texts: How to recover performance_schema after restoring from partial innobackupex backup?

texts:
I am taking a partial backup from a Master server, which needs to be restored on a to-be slave server. I only need one of the databases, and the mysql database.

I am following instructions on https://www.percona.com/doc/percona-xtrabackup/2.2/innobackupex/partial_backups_innobackupex.html

The command I'm using looks like this:

innobackupex --user=<the_user> --password --databases="<database_name> mysql" --slave-info /path/backup

The prepare command looks like this:

innobackupex --apply-log --export /path/backup

And then I

  Copy back the prepared backup to a “clean” datadir (in that case, make sure to include the mysql database).

MySQL starts fine, but without a performance_schema and with a lot of

  588 [ERROR] Native table 'performance_schema'.'' has the wrong structure

on its error log.

Any idea why this might happen, and how to get the performance_schema back?


texts: In SQL Server 2008R2 Will currently deployed applications still work if I turn on Mirroring without changing connection strings?

texts:
I've googled a great deal, and can't come up with an answer to this.

In SQL Server 2008R2 will currently deployed applications (Asp.Net, .Net Smart Client) still work (with a connection string pointing to a single DB server) if I turn on Mirroring without redeploying the apps?

The original apps are deployed and only expecting one server.

I understand the client apps without the "Failover Partner" section of the connection string won't know to use failover partner in the event of a primary failover event, however I don't want to break currently deployed apps. Rolling out new apps that are failover-aware will happen at one point, but would rather know the implications of setting up mirroring first.

Apologies if this should be on ServerFault as it pertains to .Net Application Access. Would ask in the Meta if my deadline allowed me the time.


texts: Unexpected Shutdown affects SQL Server DB start at OS start up

texts:
I have an application that uses a sql server db (SQL Express 2008 R2) and starts when Windows OS starts. When the pc shutdown unexpectedly and then restart the pc the application run automatically but it' can't query the DB
I looked for the error log and it shows no record for starting the DB and I have
to open the SSMS and query the DB. Only after that the db will start (even after seconds from starting the OS)

2018-02-28 13:12:51.05 Server      Server process ID is 2420.
2018-02-28 13:12:51.50 spid7s      Starting up database 'master'.
2018-02-28 13:12:55.60 spid7s      Recovery is complete. This is an informational message only. No user action is required.
2018-02-28 13:12:58.87 spid52      Starting up database 'ReportServer$SQLEXPRESS2008R2TempDB'.
2018-02-28 13:35:27.38 spid57      Starting up database 'DBName'.

as you can see there are about 23 minutes between starting master and my db.
I also checked the service and it's running fine in the OS start.

I have 2 questions:
1- What SQL Server make to start the db and can I do that programmatically?
2- I noticed that starting 'ReportServer$SQLEXPRESS2008R2TempDB' is the beginning of the delay and it may be the cause of the delay, so can I change the order of starting the databases on the server?

Thanks in advance


texts: Understand PostgreSQL's statistics about rows and times

texts:
I'm trying to find out how much database load an aplication causes. Therefore I'm looking at the statistics of a single database (there's only a single user working with it). The numbers I get are partially redundant and massively different. Here's the query I use to get the data:

with pss as (
    select pd.datname,
        sum(calls) as query_count,   -- Number of executed queries
        sum(total_time)::integer as time,   -- Total time spent for queries, in milliseconds
        sum(rows) as rows,   -- Number of rows retrieved or affected by queries
        sum(blk_read_time)::integer as read_time,   -- Total time queries spent reading blocks, in milliseconds
        sum(blk_write_time)::integer as write_time   -- Total time queries spent writing blocks, in milliseconds
    from pg_stat_statements as pss
        left join pg_database as pd on (pd.oid = pss.dbid)
    group by pd.datname
)
select psd.numbackends,   -- Currently connected clients
    pss.query_count,
    psd.xact_commit + psd.xact_rollback as xact_count,   -- Transaction count
    psd.blks_read,   -- Disk blocks read
    psd.blks_hit,   -- Disk block found in buffer cache
    pss.rows,
    psd.tup_returned,   -- Rows returned by queries
    psd.tup_fetched,   -- Rows fetched by queries
    psd.tup_inserted + psd.tup_updated + psd.tup_deleted as tup_written,   -- Rows written by queries
    psd.blk_read_time::integer,   -- Time spent reading data file blocks by backends in this database, in milliseconds
    psd.blk_write_time::integer,   -- Time spent writing data file blocks by backends in this database, in milliseconds
    pss.time,
    pss.read_time,
    pss.write_time
from pg_stat_database as psd
    join pss on (pss.datname = psd.datname)
where psd.datname = 'myappdb';

I have loaded and installed the pg_stat_statements extension and enabled the track_io_timing option. I've also reset both statistics at the same time a while ago as a superuser in this database:

select pg_stat_reset(), pg_stat_statements_reset();

The result currently looks like this:

numbackends:            28
query_count:       270,302
xact_count:        270,313
blks_read:      17,666,658
blks_hit:        5,063,072
rows:              462,105
tup_returned:   24,494,192
tup_fetched: 1,131,085,600
tup_written:        17,509
blk_read_time:      39,337
blk_write_time:      9,257
time:              229,049
read_time:          50,303
write_time:          9,261

My questions are these:

Why are the numbers in rows, tup_returned and tup_fetched so different? At least rows and tup_returned should be the same, right?
Why are blk_read_time and read_time a bit off while blk_write_time and write_time are so close?


texts: Can I seed Percona XtraDB cluster from the existing backup?

texts:
I have a running setup of Percona MySQL 5.7. I would like to try out Percona XtraDB cluster. The current data size is ~500GB. From what I've observed (seen in a couple of YouTube videos: https://www.youtube.com/watch?v=uUVdte3wB3I ), I reckon that the cluster members do the initial sync from the currently serving master.
However, in my case, syncing 500 GB of data would take ages, which I can't afford.
On the other hand, I have a backup of the current data, taken using percona-innobackupex. Can I use this backup to seed the cluster members (like I would feed the slave while doing master-slave replication) so that the initial sync takes less time?


texts: Unexpected results while using HAVING without GROUP BY

texts:
Given below is the snapshot of the SQL queries:

MariaDB [university]> select * from department;
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Elec. Eng. | Taylor   |  85000.00 |
| Finance    | Painter  | 120000.00 |
| History    | Painter  |  50000.00 |
| Music      | Packard  |  80000.00 |
| Physics    | Watson   |  70000.00 |
+------------+----------+-----------+
7 rows in set (0.000 sec)

MariaDB [university]> select * from department where budget > (select avg(budget) from department);
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Finance    | Painter  | 120000.00 |
+------------+----------+-----------+
3 rows in set (0.000 sec)

MariaDB [university]> select * from department having budget > avg(budget);
+-----------+----------+----------+
| dept_name | building | budget   |
+-----------+----------+----------+
| Biology   | Watson   | 90000.00 |
+-----------+----------+----------+
1 row in set (0.051 sec)

MariaDB [university]> select * from department having budget < avg(budget);
Empty set (0.000 sec)

I am trying to select all the departments where the budget is greater than the average budget.

The expected result includes three entries but when I use HAVING clause it selects only one of the rows.

Also, in the last SQL statement: select * from department having budget < avg(budget) the result is an empty set which I haven't expected. I am using MariaDB 10.3. Any explanation of why it's happening like that would be appreciated! Thanks!


texts:
What is the format of the mysql query log? In particular, for lines like:

133 Query     commit
133 Query     rollback

What does '133' stand for? And is it true that each line represents a round trip communication to the database (i.e., they are not batched)?


texts: How do I create a user for SQL Server whose queries (any/all) are time limited?

texts:
Is there any way to create a database user in SQL Server 2005 so that none of their queries are allowed to exceed a certain time limit (for e.g 20-30 seconds) or all queries are limited to a certain number of cycles - and then the query times out - i.e. preventing this user from really using up any significant resources per query?

Anyone know of a way to do this?

thank you!


texts: Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

texts:
I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs).  I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those databases up.  When I go to restore them into the MySQL 5.7 server, do I need to run an upgrade on those databases to bring them "up to speed"?

The mysql_upgrade command seems to be related to all database within an instance, and I will be moving dbs one at a time into the server that has had dbs created from scratch there, as well as other dbs that have been moved to it from the previous version (5.1).


texts: Any tools to simulate innodb crash for innodb_force_recovery levels 1-6?

texts:
I am curious if there's any tools out there or methods to simulate mysql innodb crashes which would test and require the use of each of the 6 levels of innodb_force_recovery 1-6 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html ?

What would I need to do to replicate each level of crash recovery ?

cheers


texts: Extended Entity-Relationship Diagram: Do the sub classes have foreign keys or primary keys?

texts:
Consider this example (they are disjoint, by the way):

There are two scenarios which I am  guessing:

While translating it to relational tables,
the rows in PROGRAMMER are unique which do not exist in EMPLOYEE table and it has a PRIMARY KEY
They already exist in EMPLOYEE table and the PROGRAMMER has a foreign key which references it.

Which scenario is correct? or both are wrong?


texts: Does it make sense to add indexes to a clustered columnstore index table?

texts:
Question is similar to this one, but the answer doesn't seem to answer this question.
My understanding of a clustered columnstore table (and please correct me if I am wrong) is that each column is stored in some physically ordered manner, implying that each column already has what amounts to a clustered index on it.  If that were the case, it wouldn't make a lot of sense to add any more indexes on the table... or would it?  Perhaps a composite index?
Is my thinking correct on this?


texts:
I have a MariaDB server with several databases.

On my MariaDB server, some queries (with joins between tables of different databases) are slower after the daily mysqdump (one file sql by database).

After the daily mysqldump, the first run for one query will be 1.5 minutes  and the second run 3.2 secondes (this time is coherent with current run on the day).
I understand the second run is faster but why the first run after the daily mysqldump is slower ? And how improve it ?

An extrait of my server.cnf :

key_buffer_size = 384M
max_allowed_packet = 16M
table_open_cache = 2000
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
query_cache_limit= 128M
query_cache_size= 256M
query_cache_strip_comments=1
thread_concurrency = 8
tmp_table_size = 32M
max_heap_table_size = 32M
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 3
innodb_log_file_size = 448M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_print_all_deadlocks = 1
innodb_lock_wait_timeout = 210

Thanks for you help.


texts: Empty CDC scans on highly active SQL Server

texts:
We have a SQL Server which has highly active databases which will have number of transactions in an hour. CDC is enabled on this database to carry forward the committed changes to another server. But recently we see that the changes were not reflecting on the target server. There are no errors in the dm_cdc_errors table and also the log_scan_sessions is also returning all empty scans. I mean the empty_scan_count is around 3500. That means that the  capture job is running fine and it is scanning the log for any changes.

I even did an update on a table and queried the corresponding CT table but no records were popped up there. Is there any other setting/configuration that I need to check ?


texts: I try to use gmail in database mail. I have no problem to send out email from it. But how I can configure my send mail task to send out email by using database mail. Thanks


texts: What to do if all columns should be queried

texts:
I'm currently developing an application which saves the progression of a player during an online game. Which means, I want to store for example his score, played time, Flag Captures etc. All in all there are 15-20 columns for different types of data, most of them are integers.

Now I want to sort each column to create statistics for player-ranking, for example:
SELECT players.UserName AS Name, Score FROM player_data JOIN players USING(UserID) ORDER BY Score DESC LIMIT 1, 20.
It should not be limited to query only "Score", every other existing columns should be queried in this way.

Is there any other way except to create indexes on all columns to speed up such queries? Or are there any other ways for optimization?
I'm fairly new to databases but as far as I read, creating many indexes will slow down update and insert-statements, as well as increasing the database size. Obviously I want small database size and fast queries.


texts: How to configure partial replication of Oracle DB

texts:
Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE.
The goal is to keep in PRODUCTION the minimal amount of data required for online operations,
and use ARCHIVE to store all the data ever appeared in PRODUCTION to build reports.
To be clear:

we define "obsolete data" using some custom rules
"active data" is all the data except for "obsolete data"
ARCHIVE should be populated by "active data" from PRODUCTION by schedule (for example every midnight or every week)
any new "obsolete data" should be removed from PRODUCTION but remained in ARCHIVE

I believe all these points could be satisfied by independently running:

Uni-directional replication (for instance, Oracle Streams)
application for "Obsolete data" removal (suppose it is implemented)

But the problem is that any Obsolete data removing application implicitly produces REDO logs in PRODUCTION which are going to be propagated to ARCHIVE and hence this process leads to data loss in ARCHIVE.
Could you give an advice, how to organize the process of "partial replication",
which could ignore some specific deletions in master schema.


texts: Compare same tables in 2 different databases and 2 different servers

texts:
I have 2 servers, one named DB2 (cloud), and one named OUS (on prem).

I have 2 databases, one named version1 (cloud), and one named version2 (on prem).

A person is adding a column in xyz entity on the version1 (cloud) database, and this new column in xyz entity must be added to version2 (on prem) database.

I am facing the issue of schema mismatch due to unmatched column, and
I need a SQL query to address this issue.


texts: SSIS 2012: The environment reference n is not associated with the project

texts:
I have a package that I am updating. The package is executed by a Job Agent job. After deleting the package and then deploying the new version, I run a script that executes any necessary [create_environment], [create_environment_reference], [create_environment_variable], and [set_object_parameter_value] statements.

However, when I start the job, it fails with the following error message (where "n" is a number which doesn't appear in any of the relevant SSISDB tables or views as an id of any kind):

  The environment reference n is not associated with the project


texts: Mysql Workbench: Renaming PK does not reflect the name change on child - How to fix?

texts:
First, I connect 2 tables with 1 to many relationship. Workbench correctly creates FK column in the child table.

1-When I change the name of the PK in the parent, the old FK name remains unchanged in the child.

2-The name generated for the fk is not following the standard pattern set in the options (even when using the defaults).

Any suggestions to keep names in sync? (besides me performing the manual change)?

Note: I am using Workbench 6.3 Community Edition.


texts: Search for nested values in jsonb array with greater operator

texts:
Here is the table definition (simplified):

CREATE TABLE documents (
    document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    data_block jsonb NULL
);

Sample values:

INSERT INTO documents (document_id, data_block)
VALUES
   (878979,
    '{"COMMONS": {"DATE": {"value": "2017-03-11"}},
     "PAYABLE_INVOICE_LINES": [
         {"AMOUNT": {"value": 52408.53}},
         {"AMOUNT": {"value": 654.23}}
     ]}')
 , (977656,
    '{"COMMONS": {"DATE": {"value": "2018-03-11"}},
     "PAYABLE_INVOICE_LINES": [
         {"AMOUNT": {"value": 555.10}}
     ]}');

I want to search all documents where one of the 'PAYABLE_INVOICE_LINES' elements contains a 'value' greater than 1000.00.

My query is

select *
from documents d
cross join lateral jsonb_array_elements(d.data_block -> 'PAYABLE_INVOICE_LINES') as pil
where (pil->'AMOUNT'->>'value')::decimal > 1000

But, as I want to limit to 50 documents, I have to group on the document_id and limit the result to 50.

With millions of documents, this query is very expensive - 10 seconds with 1 million.

I try to add a GIN index on the array of the jsonb object. But it seems it's applied only while using a jsonb operator like @>.

Do you have some ideas to have better performance?


texts: Pgpool2 doesn't use the slave for load balancing

texts:
I've enabled streaming replication in my postgres 9.5.10 installed from ubuntu xenial repo. I want to enable load balance for pgpool-2 (3.7.0 amefuriboshi) installed from sources. So I have this pgpool.conf: https://pastebin.com/qWWgejQN

As you can see, I set both nodes, turned off replication, turned on master/slave mode and set it to stream. And also I enabled memcached caching.

Now I have very big problem that pgpool doesn't want to use slave for balancing:

postgres=# show pool_nodes;
 node_id |    hostname    | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay
---------+----------------+------+--------+-----------+---------+------------+-------------------+-------------------
 0       | localhost      | 5433 | up     | 0.500000  | primary | 0          | true              | 0
 1       | host2          | 5433 | unused | 0.500000  | standby | 0          | false             | 0
(2 rows)

What am I do wrong?


texts: Convert a row to a nested JSON document containing all relations

texts:
In PostgreSQL 10.10, I have created a trigger function in PL/pgSQL that converts the NEW row to a JSON object using to_jsonb(NEW). But now I need to include the records on the other side of the foreign keys of the NEW record in the JSON object in a nested fashion.

For example:

before:

employee = {
    "id": 1,
    "name": "myname",
    "department": 2,
    "phone_no": "123456789"
}

after:

employee = {
    "id": 1,
    "name": "myname",
    "department": {
        "id": 2,
        "name": "IT"
    },
    "phone_no": "123456789"
}

What is the best and most generic way to accomplish this without prior knowledge about the schema of the NEW record? I need to keep this trigger function as generic as possible because I plan to use it on all tables. One level of depth in following foreign keys is currently enough for me. Also to simplify, I can assume that all foreign keys should be a single column.

As I understand, I need to loop over all columns in the NEW record, find out if the column is a foreign key using information_schema or pg_catalog, find the foreign key details like to which column on which table it points to, then perform a dynamic SQL SELECT (because I presume table and column names would be strings, not SQL identifiers) over the target table for the target record, convert the record to JSON and finally assign it to the appropriate key of the top-level row JSON object.

I'm yet trying to write the actual working code for this, for which I welcome any help or directions. And there might be simpler solutions to this problem, which I would like to know about.


texts: double (3,3) > sending 1.3 > stored 0.999

texts:
It is knocking my socks off...
MySQL DB

weight is defined as double (3,3)

INSERT INTO xxx (`weight`) VALUES ( 1.3)

why does the DB store 0.999?


texts: Starting a Postgres slave that is in hotstandy - stuck after start

texts:
I have a postgres 9.1 hot-standby that has been down for a few days and having problems starting back up. When I try to restart the server I see the following:

-sh-4.1$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data
server starting
-sh-4.1$ LOG:  database system was shut down in recovery at 2013-02-13 05:11:29 UTC
LOG:  entering standby mode
cp: cannot stat `/usr/local/pgsql/wals/000000010000003B000000BA': No such file or directory
LOG:  redo starts at 3B/BA0022E0
LOG:  record with zero length at 3B/BA820590
cp: cannot stat `/usr/local/pgsql/wals/000000010000003B000000BA': No such file or directory
LOG:  streaming replication successfully connected to primary

What does this mean? Why isn't the standby getting the data it needs from the master and making itself available? Any suggestions on how to diagnose and resolve? Thanks


texts: Find all unique columns that are allowed null

texts:
I am trying to write a query that displays all unique columns that are allowed to be null in Oracle.

The information I need is in these 3 tables:

ALL_TAB_COLUMNS
ALL_IND_COLUMNS
ALL_INDEXES

I am having difficulty joining these tables to display the information I'm looking for. Any help would be appreciated.

Looking for output such as:

OWNER, COLUMN_NAME, INDEX, UNIQUENESS, NULLABLE

WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE'


texts: How to collapse rows with individual, contiguous dates into single rows with date range start and end

texts:
Given a data set like this:

Id
Date
Value

1
01/01/2021
100

1
01/02/2021
100

1
01/03/2021
100

...
... (rows omitted for brevity)
...

1
12/29/2021
100

1
12/30/2021
100

1
12/31/2021
100

2
01/01/2021
100

2
01/02/2021
100

2
01/03/2021
100

2
06/01/2021
100

2
06/02/2021
100

2
06/03/2021
100

Is it possible to write a query to return a result set where each row is a contiguous date range, grouped by the Id column? Like this:

Id
Start Date
End Date
Sum of Value

1
01/01/2021
12/31/2021
36500

2
01/01/2021
01/03/2021
300

2
06/01/2021
06/03/2021
300

I'm imagining it will be some combination of window functions and group bys, but I'm not even sure it's possible and didn't want to fumble towards a solution that didn't exist.
I'm using SQL Server 2017 and 2019, if that matters.
The date ranges can be long - months or years. For example, one of the collapsed rows could be a year long, coming from 365 individual rows, like in the given example.


texts: Optimizing PostgreSQL hstore for lots of small updates

texts:
I have a situation where I will have one web session per row, but this session will generate dozens of updates to an hstore field in that row, one k/v at a time.  At the end, I will have a completed structure.  The keys will be more or less the same per session, but will evolve over time.  The values will be mixed, with some being unique per session (e.g. email) and some having lots of repetition (e.g. male/female).

I am running under the assumption that lock contention will only be present at the row level, which is fine.  What other gotchas should I look out for?  I have zero experience with hstore, so I really want to make sure I understand what I am in for.  Any feedback is appreciated.


texts: How do I log into the Dedicated Admin Console (DAC) on Linux?

texts:
sqlcmd is documented to have,

  Login-Related Options

  -A
  Logs in to SQL Server with a Dedicated Administrator Connection (DAC). This kind of connection is used to troubleshoot a server. This will only work with server computers that support DAC. If DAC is not available, sqlcmd generates an error message and then exits. For more information about DAC, see Diagnostic Connection for Database Administrators. The -A option is not supported with the -G option. When connecting to SQL Database using -A, you must be a SQL server administrator. The DAC is not availble for an Azure Active Directory adminstrator.


texts: About creating a View With additional coumns (that are not in the table)

texts:
I have this view with following definition.

create view VIEW_IRS1 as Select
MBR.PRSN_MBRSH_ID
, Atr.Ssn_Tx
, Names.First_Na
, Names.Middle_Na
, Names.Last_Na
, Reff.Ref_Cd_Desc_Tx Suffix_Na
,'FALSE' AS [Request Title II Monthly Income Verification]

From Hix.T_Enrt En
Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')

Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id

Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id

Left Outer Join
(
    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
    where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id

Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id

Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id

Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID=Pe.PRSN_ENRT_ID;

I want to create a new view (not alter this view) with an additional column name say "ABC" whose value should always be FALSE. How can I do that?


texts: How to free up disk space to the OS, ideally without using SHRINKFILE?

texts:
Our server admin is asking me to free up disk space on our SQL server, since the drive where the SQL datafiles reside is at 97% usage.

I have found a table that has grown out of control. It now contains over 300 million rows, so I will set a job to delete these overnight.

What are my options to then free up the disk space?
Will the deletion of those rows free up space for other tables to use?

I would like to avoid SHRINKFILE as it is not recommended.


texts:
We have a very large database that we want to write almost real time. We want to take data and move it to another instance of the database on top of which will be a GUI and MI tool which will be able to report on the data.

I've got the following so far - does anybody else have any considerations I should bear in mind when spec'ing this:

Data Migration / Retention Period / Legal Requirements of Storage
Tables / Fields that need to be available in reporting database
Permissioning / User Access
Key fields that need to be queried
Disaster Recovery Requirements
Replication Timing


texts:
I have this table with 500,000 rows:

CREATE TABLE IF NOT EXISTS `listings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_id` int(10) unsigned NOT NULL,
  `cat_id` mediumint(5) NOT NULL,
  `title` mediumtext NOT NULL,
  `views_point` int(10) unsigned NOT NULL DEFAULT '0',
  `publishedon_hourly` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `views_point` (`views_point`),
  KEY `listings` (`publishedon_hourly`,`published`,`cat_id`,`source_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED AUTO_INCREMENT=365513 ;

I want to make a query like this:

SELECT *
FROM listings
WHERE (`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
    AND UNIX_TIMESTAMP( '2015-10-5 12:00:00' ) )
AND ( published =1 )
AND cat_id IN ( 1, 2, 3, 4, 5 )
ORDER BY views_point DESC
LIMIT 10

This query sometimes works exactly as I want (see this), but according to some opinions, this is incorrect. I searched the web for solutions and found these:

http://venublog.com/2007/11/29/mysql-how-to-avoid-filesort/
http://www.getsymphony.com/discuss/issues/view/657/

I added an index on views_point then tried this query:

select t1.*
from  listings t1
left outer join  listings t2 on (t1.views_point=t2.views_point)
order by t1.views_point
limit 10

This is the explain:

but I cannot add this condition:

`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
    AND UNIX_TIMESTAMP( '2015-09-5 12:00:00' )
AND ( published =1 )
AND cat_id = 5

I don't know, which one should I assign to (t1 or t2)?

On the other hand what about this way:

SELECT *
FROM listings
WHERE (`publishedon_hourly`
    BETWEEN UNIX_TIMESTAMP( '2015-09-5 00:00:00' )
    AND UNIX_TIMESTAMP( '2015-09-5 23:00:00' ))
AND (published =1)
and views_point is not null
ORDER BY views_point DESC limit 20

If neither one will be good, can any one tell me please how the big blogs get the posts by hits?


texts: Permanently delete SQL server database - anti recovery

texts: I rent a server from a server provider. And one day I'm no longer rent it. I have some important database on the server need to wipe out so no one can see it. Is that safe to just right click on the database and select Delete? Is there any tools or method that able to recover a deleted database?


texts: MS SQL servers in different countries linked for ETL via SP

texts:
The title could be a bit complicated, but the question is very simple...
We developed an application which uses a proprietary database (call it DB_ONE). Then, there's another application with its own database (call it DB_TWO). Some data stored in DB_TWO are useful for DB_ONE (in the way they already are or with a little bit of modifications). So we developed some stored procedures to extract data from DB_TWO, trasform them and load them in DB_ONE (like an ETL process). We must maintain the data aligned, so the SPs are called quite often.

We first developed this solution thinking that the two databases would reside in the same SQL Server, but we used synonim with four part name convention to support different servers. In the last case, the servers must be linked and we observed that performance were good, with no modifications or just little modifications to the SPs. This happened when the data weren't so much or in a LAN.

Now the problems start... We would like to use the same solution with a customer having the DB_ONE in a country and DB_TWO in a different one. The amount of data is very big and in the country of DB_ONE the connection quality is not that good... The solution still works, but it requires a lot of bandwidth, the execution time is long and the synchronization can't happen to often (which is bad for some SPs).

Do you have any suggestion to improve the quality of the solution without adopt a different approach (like HTTPS compressed data exchange)...? Is it possible to compress the data, optimize the load and/or the transfer? In this scenario, the encryption of data over the WAN would also be useful...


texts: How do I update a table with the aggregated values (min, max) of a related table, fast?

texts:
I have a table

create table w (
  id integer primary key,
  --
  min_xyz double,
  max_xyz double,
  min_abc double,
  max_abc double
);

with the min* and max* values currently being null.

I have another table

create table n (
    id   integer primary key,
    ---
    xyz  float,
    abc  float
);

There is a n:n relation between these tables:

create table n_in_w (
  n_id   integer not null references n,
  w_id   integer not null references w
  --- further attributes
);

Now, I'd like to fill the min* and max* attributes in w with the respective values in n via the connection via n_in_w. The following would do

update w set
  min_xyz = (select min(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_xyz = (select max(xyz) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  min_abc = (select min(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id),
  max_abc = (select max(abc) from n, n_in_w where n.id = n_in_w.n_id and n_in_w.w_id = w.id);

but I fear that the performance of this sql query is not optimal as it has to scan n 4 times for each n.id;

Ideally, I am looking for something like

update (
  select
    w.id,
    /*----*/
    w.min_xyz,
    w.max_xyz,
    w.min_abc,
    w.max_abc,
    /*-----*/
    min(n.xyz) min_n_xyz,
    max(n.xyz) max_n_xyz,
    min(n.abc) min_n_abc,
    max(n.abc) max_n_abc
  from
    w, n_in_w, n
  where
    w.id = n_in_w.w_id and
    n.id = n_in_w.n_id
) i
set
  i.min_xyz = i.min_n_xyz,
  i.max_xyz = i.max_n_xyz,
  i.min_abc = i.min_n_abc,
  i.max_abc = i.max_n_abc;

(or something similar), but it seems it's not possible with mysql.


texts: always error "cannot insert value NULL" sqlserver

texts:
I want to update table "tmpiscunitmap.tr_kpi_id" year_period=2016
with value from table "tmp_kpi.tr_kpi_id where year_period=2016"
with condition tmpiscunitmap.tr_kpi_id = tmp_kpi.tr_kpi_id on year_period=2015?

this my query and i get error "cannot insert value NULL into tmpiscunitmap.tr_kpi_id":

UPDATE tmpiscunitmap set tmpiscunitmap.tr_kpi_id=(
SELECT TOP 1
        b.tr_kpi_id
        FROM
        tmptr_kpi
        AS a
    INNER JOIN tmptr_kpi AS b ON a.tr_kpi_code = b.tr_kpi_code
    where
    a.year_period = 2015 AND
    b.year_period =2016 AND
    a.tr_kpi_id=tmpiscunitmap.tr_kpi_id)

Table tmptr_kpi:

Table tmpiscunitmap:


texts: MySQL is trimming my numbers and I can't figure out why

texts:
I've got a table with a bunch of decimal(12,2) fields that are supposed to hold money amounts.  For some reason which I can't figure out one of them is trimming the value when either inserted or updated to a whole number.  So if I enter in 2.2 into the PHP web form all that is saved is 2.00.  It's not rounding because if I put in 2.9 it still saves 2.00.

All the columns are updated with a single update statement to the table, and this is the only one with a problem.  For all the values all I'm doing is grabbing the value via $_POST['n'] and sticking the value into an array which I'm then passing to the WordPress $wpdb->query via $wpdb->prepare as shown below.

$wpdb->query(
    $wpdb->prepare($sql,$params)
);

If I go into phpmyadmin I can change the value and it'll take it.

The only difference between this column and the other columns is that this one was added to the table after the table was created, so it's farther to the right and was added via an ALTER TABLE command instead of being part of the CREATE table command.

If I dump the array from $params I can see the correct value, so it's being passed into the the database engine correctly (best I can tell).

I've tried changing the data type to decimal(12,3) and back but that didn't help any.  Anyone seen this before or have an idea where to look?

UPDATE:
As requested, the code to create the table.

        $sql = "CREATE TABLE IF NOT EXISTS `{$wpdb->prefix}timesheet` (
            timesheet_id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            start_date datetime NOT NULL,
            entered_date datetime NOT NULL,
            client_name mediumtext NOT NULL,
            project_name mediumtext NULL,
            monday_hours numeric(12,2) NOT NULL,
            tuesday_hours numeric(12,2) NOT NULL,
            wednesday_hours numeric(12,2) NOT NULL,
            thursday_hours numeric(12,2) NOT NULL,
            friday_hours numeric(12,2) NOT NULL,
            saturday_hours numeric(12,2) NOT NULL,
            sunday_hours numeric(12,2) NOT NULL,
            total_hours numeric(12,2) NOT NULL,
            monday_desc mediumtext NULL,
            tuesday_desc mediumtext NULL,
            wednesday_desc mediumtext NULL,
            thursday_desc mediumtext NULL,
            friday_desc mediumtext NULL,
            saturday_desc mediumtext NULL,
            sunday_desc mediumtext NULL,
            per_diem_days numeric(6,2) NULL,
            hotel_charges numeric(12,2) NULL,
            rental_car_charges numeric(12,2) NULL,
            tolls numeric(12,2) NULL,
            other_expenses numeric(12,2) NULL,
            other_expenses_notes longtext NULL,
            week_complete tinyint(1) NOT NULL,
            marked_complete_by bigint(20) NULL,
            marked_complete_date datetime NULL,
            approved tinyint(1) NOT NULL,
            approved_by bigint(20) NULL,
            approved_date datetime NULL,
            invoiced tinyint(1) NOT NULL,
            invoiced_by bigint(20) NULL,
            invoiced_date datetime NULL,
            invoiceid bigint(20) NULL,
            ClientId bigint(20) NULL,
            mileage bigint(20) NULL,
            EmbargoPendingProjectClose tinyint(1) NULL,
            project_complete tinyint(1) NULL,
            ProjectId bigint(20),
            PRIMARY KEY (timesheet_id),
            INDEX IX_user_id_start_date (user_id, start_date),
            INDEX IX_approved (approved),
            INDEX IX_invoiced (invoiced)
            ) $charset_collate";
        $wpdb->query($sql);

Then later on the new problem column was added.

        $sql = "ALTER TABLE `{$wpdb->prefix}timesheet`
            ADD COLUMN flight_cost decimal(12,2)";
        $wpdb->query($sql);


texts: If I update a column record in a table, will indexes that do NOT have this column in it be affected?

texts:
In terms of performance if I have a table like so:

CREATE TABLE [TESTDATA].[TableA](
    [Col1] [nchar](5) NOT NULL,
    [Col2] [nchar](2) NULL,
    [Col3] [float] NULL
CONSTRAINT [TableA_PK] PRIMARY KEY CLUSTERED
(
    [Col1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And then create a non-clustered index like this:

CREATE NONCLUSTERED INDEX [_idx_TableA]
ON [TESTDATA].[TableA] ([Col2])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

If I perform an update to the table, only changing Col3 does the DB need to touch the index _idx_TableA?

Just curious how far performance touches all indexes?


texts: Duplicate record script | Efficiency boost

texts:
I have devised a script to search through my table for duplicate records and display their count.

I am wondering if there is a way to improve the efficiency of the search, given the current search takes 7 seconds. Here's the code:

use gameStore;
select ID, count(*) as idRepl,
console, count(*) as consoleRepl,
price, count(*) as priceRepl,
purchased, count(*) as purchRepl from wishlist
group by console, price, purchased having count(*) > 1;

EDIT 1

EXPLAIN RESULTS

FIELD     |TYPE       |NULL|Key    |DEFAULT|Extra
ID        |INT        |NO  |PRI    |       |auto_increment
console   |VARCHAR(50)|NO  |       |       |
Price     |FLOAT      |YES |       |NULL   |
Purchased |VARCHAR(4) |YES |       |NO     |


texts: How does normalization fix the three types of update anomalies?

texts:
I have been reading 'Database Systems: A practical approach to design, implementation and management' by Connolly and Begg in order to understand database normalization (chapter 14). I now better understand the three database normalization forms:

First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)

I also understand the three update anomalies:

Insertion anomaly
Deletion anomaly
Update / Modification anomaly

What I am struggling to understand now is how to link the two together. For example, which of the different forms help(s) to fix the insertion anomaly? Similarly for the other anomalies. I would ideally like to know the mapping relationship between the two groups and why those forms fix the certain anomaly.

This is my first question on here and I searched on Google and this site to try and find an answer first, but to no avail.

Thank you.


texts: MySQL possibility to create global routines (stored procedures and/or functions)

texts:
Is it possible to somehow define globally available routines? It seems like every routine must be created in a scope of the database.

When I tried to create a routine from console (without prior issuing use dbname)
I am getting an error:

ERROR 1046 (3D000): No database selected

We have tons of identical databases (data is different) and the goal is  to
create some triggers for some tablenames. But we want to run only one routine so we don't have to create those routines for every database (since they are identical, routines would work the same for each database).


texts: ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON takes forever

texts:
One of vendor software needed to install on a test server for POC. It takes forever when the setup.exe is trying to alter one of the databases with SET ALLOW_SNAPSHOT_ISOLATION ON.

The test server was on Microsoft SQL Server 2016 (SP1-CU8), developer edition.

After rolling back to SP1-CU5, SET ALLOW_SNAPSHOT_ISOLATION ON worked.

Has anyone experienced the same behavior? Any work around besides rolling back the CU?

There was no blocking and no other users at all. POC is in isolated lab environment. Tried on 2 other dev SQL Servers in live environment with 2016 SP1 CU7, experienced same behavior.


texts: Number of ISA relationship triangles in an Entity-Relationship Diagram

texts:
The following two diagrams are nearly identical. However, in one, there are two ISA triangles from the Entity "Movie" and in the other there is just one.

What is the difference in meaning?


texts:
I have two tables containing exactly the same data. Both table have bigint primary key identity column, 60 columns and 300 000 rows. The difference is that all of the columns of the second table have sql-variant type.

I am creating temporary table and importing the data from the two tables in it. When data is extracted from the sql-variant columns it is cast to the corresponding SQL type.

The data extraction from the first table is performed for 1 sec and from the second table for 6 secs.

Basically, the differences in the executions are in the estimations:

and in the read-ahead reads count:

I am wondering why the SQL Server cannot load in advance the data readed from the sql-variant fields (there are almost no read-ahead reads).

Also, the storage size of the tables is almost the same:

Why the SQL Server thinks it should read 67 GB?

The column types are:

16 x BIGINT
8  x DECIMAL(9,2)
36 x NVARCHAR(100)

The dbcc dropcleanbuffers command is used each time before data extraction and population.

In order to test you can download the sample data files from here. Then,

Execute Tech05_01_TableDefinitions.sql
Execute 3 times the Tech05_02_TablePupulation.sql
Open the Tech05_03_TestingInsertionInTempTable.sql file and excute one time like this:

DECLARE @TableStructured SYSNAME = '[dbo].[NormalDataTypes_60Cols]';
DECLARE @TableFromWhichDataIsCoppied SYSNAME = '[dbo].[SQLVariant01_60Cols]';

and one time like this:

DECLARE @TableStructured SYSNAME = '[dbo].[NormalDataTypes_60Cols]';
DECLARE @TableFromWhichDataIsCoppied SYSNAME = '[dbo].[NormalDataTypes_60Cols]';


texts: Intermittent SQL Server connectivity issues in production

texts:
I am running an ASP.NET based application, running on two load-balanced web servers, that communicate with a SQL Server 2008 R2 database running on a separate server on the same network in the datacenter. Each application pool runs under its own service account, which are set up in Active Directory, which runs on another separate server on the same network in the datacenter.

SQL Server is configured to use Windows authentication, and 99% of the time, everything works just fine. However, from time to time, we get the following SqlException thrown on our webservers:

  The login is from an untrusted domain and cannot be used with Windows
  authentication

Usually an app pool recycle will fix the issue, but this is not good for continuous site availability.

Switching to SQL authentication is not an option; is there anything I can be trying to ensure that this error doesn't occur?


texts: WordPress MySQL writes during "Flush Tables With Read Lock" are rejected instead of being queued

texts:
I'm running into an odd issue when setting MySQL into a brief read-only mode for backup.

Reads continue fine during that "read only" minute, but writes are just rejected instead of being queued to run after tables are unlocked.

I'm not seeing the attempted writes in mysql "show processlist" where I would expect to see them, they just seem to be discarded.

Using "Flush Tables With Read Lock", copying the data files (less than a minute), then "unlock tables".

MySQL and backup code:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

system time cp -pdRu /mysql_data_dir/thedb /backup_dir/thedb

SET GLOBAL read_only = OFF;
unlock tables;

Background:

Running WordPress MultiSite on MySQL 5.1, CentOS. 70,000 MyISAM tables.

Looking for the fastest way to back up the DB while keeping the DB online.

Both reads AND writes need to be possible during the backup. But the backup time is very short, so it's fine for the writes to be queued until the "unlock tables" command.

In the WordPress error_log, seeing MySQL errors like this:

  [20-Nov-2014 01:41:56 UTC] Wordpress database error The MySQL server is running
  with the --read-only option so it cannot execute this statement for query
  INSERT INTO wp_options...

So my question is...

Why are writes being quietly discarded instead of being queued, as they would be for a regular table lock?

And more importantly, how can I fix that so they DO get queued and are eventually run in the order they were requested? :)

Thanks!


texts: Measure fragmentation of a single index?

texts:
We are measuring the impact of different defrag approaches that we do one index at a time.

How do we query to see fragmentation of a single index, by index name (or table + index).

This is on SQL Server 2008.


texts: Optimal hash technique to index large text

texts:
Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths.

The upper limit for the insertion rate is tens of billions of rows per year.

For my implementation, the hash never needs to leave the database, but the hashed data must be compared with external data for existence frequently.

In my limited experience with optimization for these purposes, I assume that the best data type for the hash would be bytea.  The alternative is of course a much longer hex string.

Is it correct that bytea is the optimal data type for these hashes?

If bytea is not optimal, what is?

How should my intent be implemented?

Clarification

I'm using the hash of the text per Erwin Brandstetter's recommendation to be able to ensure that large text is unique.  It is my limited understanding that raw binary data is always the most performant especially when compared to strings.

The hash only needs to be compared for existence to preempt uniqueness violations, so the hash happily never needs to leave the database.  Because of libpqxx's incredible design, it appears as if the data can simply be input through a prepared statement and converted with (decode(md5($1::text), 'hex')).  When I become more familiar with bytea insertion via libpqxx 3.1, I'll move it to c++.

For this implementation, collisions are acceptable because data can be reconstructed to conform without breaking the system.

If the maximum throughput is happily reached then it should be expected that the economic resources will be available to accommodate it; therefore, the primary concern will always be performance, so if my understanding of the capabilities of Postgres and its ability for partitioned tables to handle these amounts of rows is accurate, it will hopefully be the right tool for the job for a long while.  Luckily, data older than a few seconds will never change, and it is my understanding that Postgres partitioned tables can make minced meat out of such data.  If not, this will be one of those good problems.


texts: Does a UNIQUE constraint implicity contain the primary key?

texts:
I'm aware that INNODB includes an implicit primary key in an index such as:

INDEX (non_primary_column)

Does it apply to the UNIQUE constraint too?

UNIQUE (other_primary_column) -- equivalent to INDEX (other_primary_column, pkey)?

… and FOREIGN constraint too?

FOREIGN KEY (some_other_column) -- equivalent to INDEX (some_other_column, pkey)?


texts: How often does the Impala StateStore refresh?

texts:
I am using Hive and Impala on the same cluster. I find that when I create new tables, the Impala StateStore does not refresh automatically even after a few hours.

I know that I can accomplish this by running "refresh" in impala-shell (in a cron job if need be), but I would like to know: how often does impalad refresh metadata and can I set this interval to be shorter? If so, how?


texts: IF IS NULL then SINGLE else keep the value of the field

texts:
I am working with a DB in Access and I'd like to be able to fill the field in this row with a result of SINGLE if it is not already filled with another value.

I wrote Iif(MaritalStatus) is null, "SINGLE") but Access returned a result in which all of the fields were filled with SINGLE except those that had had a previous value of Married, Divorced, etc.


texts: Rearrange a string without using loop syntax

texts:
So if I have a table with values:

ID
------
a1b2c3
ac2b31
db13a2

I want the result to look like this

Id
------
abc123
acb231
dba132

In other words, rearrange the string so that all of the characters appear first, followed by all of the numbers. Order is unimportant.

(And what I meant by no loop is, no traditional loop. I was looking at some CTE examples, but couldn't adapt them here.)


texts: CDC capture job failing due to trigger execution error apparently

texts:
I'm using Change Data Capture(CDC) to track DML changes that are being performed on my SQL Server Tables. However, the capture job is failing post a DDL alteration, with the following error:

Msg 3616, Level 16, State 2, An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back. For more information, query the sys.dm_cdc_errors dynamic management view.

I have my own Alter_table trigger(that uses the eventdata() function) enabled on the same database that tracks the DDL command fired, along with the hostname of the user who fired the command.

I fired 2 DDL table alteration commands. The first DDL commmand(alter table add column) was successfully captured by both the triggers. However the second DDL commmand(alter table alter column) hasn't been captured in the cdc.ddl_history table. Why is this happening? My trigger has successfully logged both the commands.

PS. Querying the  sys.dm_cdc_errors system view returns the following information:

1)An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

2)Log scan process failed in processing a ddl log record. Refer to previous errors in the current session to identify the cause and correct any associated problems.

3)Log Scan process failed in processing log records. Refer to previous errors in the current session to identify the cause and correct any associated problems.


texts:
My target is to sort text columns by a custom sorting.
e.g., ADBC instead of the usual ABCD.

With this custom ordering, rows would be ordered by in this way:

ADAGIO
ABACUS
DICE
BEE

Because D comes before B.

If I'd need to sort texts of one letter that would be easy:

SELECT * FROM my_db
ORDER BY case when text_plain = 'A' then 1
              when text_plain = 'D' then 2
              when text_plain = 'B' then 3
              when text_plain = 'C' then 4
              else 5
         end asc

But text contains multiple characters.
Is this feasible?

I am using Sqlite in this case.


texts: IO thread error : 1595 (Relay log write failure: could not queue event from master)

texts:
Slave status :

           Last_IO_Errno: 1595
           Last_IO_Error: Relay log write failure: could not queue event from master
           Last_SQL_Errno: 0

from error log :

       [ERROR] Slave I/O for channel 'db12': Unexpected master's heartbeat data: heartbeat is not compatible with local info; the event's data: log_file_name toku10-bin.000063<D1> log_pos 97223067, Error_code: 1623
       [ERROR] Slave I/O for channel 'db12': Relay log write failure: could not queue event from master, Error_code: 1595

  I tried to restarting the slave_io thread for many times, still its same.


texts: Retrieving latest record using DISTINCT ON is slow

texts:
Using Postgres 9.4.
I have a table cartests with 5.5M rows. Each row is a car test:

\d log.cartests;
             Table "log.cartests"
   Column    |           Type           | Modifiers
-------------+--------------------------+-----------
 carid       | integer                  | not null
 timestamp   | timestamp with time zone | not null
 exhaust     | varchar                  |
 brakes      | varchar

 Indexes:
    "cartests_pkey" PRIMARY KEY, btree (id, "position", "timestamp")
    "cartests_carid_idx" btree (carid)
    "cartests_timestamp_idx" btree ("timestamp")

I need to retrieve the latest log for each car.

SELECT DISTINCT ON (carid) carid, timestamp, exhaust, brakes
FROM log.cartests
ORDER BY carid, timestamp DESC;

This one takes ~ 6 seconds. Running EXPLAIN ANALYZE gives:

Unique  (cost=933787.06..961490.34 rows=472 width=16)
         (actual time=4951.347..6072.534 rows=476 loops=1)
  ->  Sort  (cost=933787.06..947638.70 rows=5540656 width=16)
            (actual time=4951.345..5782.466 rows=5540656 loops=1)
        Sort Key: carid, "timestamp"
        Sort Method: external merge  Disk: 162504kB
        ->  Seq Scan on cartests
            (cost=0.00..123810.56 rows=5540656 width=16)
            (actual time=0.009..620.988 rows=5540656 loops=1)
Planning time: 0.107 ms
Execution time: 6095.598 ms

Why must it sort if I have an index on both carid and timestamp?
Is there a better method to use here?


texts: Create a new database in SQL Server 2008 R2, but it is not Blank

texts:
When I try to create new database in SQL Server 2008 R2, it was created completely, but it is not a blank and empty database, there are many tables that come from another database. I also tried specifying a different name for its .mdf and .ldf file, but nothing changed!

Would you please help me to know where is the problem?

Also, is there any command that would reset my database as a blank db?


texts:
There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest, this is even true if there was a check constrained added after the fact (No nulls present)).

There are various ways around this (including using a BITMAP index or adding a second NON NULL or constant column to the index). However I just noticed that if I do the SELECT DISTINCT with WHERE NOT NULL, Oracle is able to use the index (index fast full scan).

My Question: since which Oracle version this behavior is present, is it reliable (when is the two column index prefered) and why isnt it mentioned more often (for example this otherwise good answer does not mention it)?

Little reproducer

drop table SCOTT.T;
-- will not work with short rows (SELECT OWNER,SUBOBJECT_NAME ...)
create table SCOTT.T AS SELECT * FROM ALL_OBJECTS;
create index SCOTT.IDX_T_OWNER on SCOTT.T(OWNER); -- NOT NULL
-- (subobject_name,1) or (subobject_name,namespace) is NULL, NOT NULL
create index SCOTT.IDX_T_SUBOBJ on SCOTT.T(subobject_name); -- NULL
exec  dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',TABNAME=>'T', cascade=>true );
desc SCOTT.T;

set autotrace on explain
-- fast index scan:
select distinct OWNER from SCOTT.T;
-- full table scan:
select distinct SUBOBJECT_NAME from SCOTT.T;
-- fast index scan:
select distinct SUBOBJECT_NAME from SCOTT.T where SUBOBJECT_NAME IS NOT NULL;

Looks (on 18c) similiar to:

select distinct subobject_name from T;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |   715 |  1430 |   436   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 81426 |   159K|   428   (1)| 00:00:01 |
---------------------------------------------------------------------------

select distinct subobject_name from T where SUBOBJECT_NAME is not null;

------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |   624 |  1248 |     5  (20)| 00:00:01 |
|   1 |  HASH UNIQUE          |              |   624 |  1248 |     5  (20)| 00:00:01 |
|*  2 |   INDEX FAST FULL SCAN| IDX_T_SUBOBJ |  1459 |  2918 |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------


texts: Can I use SQLite to share data among different applications?

texts:
Can I use SQLite as a database to which 2 or more applications connect?

I.e. can I use it to share data among different applications?