texts: How to use SQL CASE result to compare a column and get another case result

texts:
I'm trying to compare my case result (desirecountry) with an existing column (so.country_name) to get another case result (Final_Country_Map_Name):
     SELECT subs."user email", subs."user join date", subs.location,
                iso.country_name as iso_country, iso.country_id as iso_country_id,
                    users.country, users.event_type,
    -- 1.
    CASE  WHEN subs.location = iso.country_name THEN iso.country_name
             ELSE users.country END AS **desirecountry**,
    -- 2.
    CASE  WHEN **desirecountry** = iso.country_name THEN **desirecountry**
            --ELSE Null END AS **Final_Country_Map_Name**

    FROM subs
    LEFT JOIN
    iso
    ON
    subs.location = iso.country_name
    LEFT JOIN
    users
    ON
    users.user_id = subs."user email"

When I'm using the second CASE conditions the logic is failing, it's not giving the expected output, it's giving me a null value in "final_country_map_name" column instead of "desirecountry" value = United States. Any small help would be helpful, Thank you.
Ex: Column names are:->
location | iso_country  |   country     | desirecountry  | final_country_map_name   &&& Column o/p are: ->
U.s,      null,          United States,   United States,       null,


texts:
For teaching and testing purposes I would like to run MySQL without actually having to install it.

I have found portable PostgreSQL for both Macintosh and Windows, which is good, but have been unable to find the same for mySQL.

I know that I can run MySQL from the popular web server packages, such as AMPPS and XAMPP, but they need installing anyway, and are over the top for my requirements.

Is there a portable app for OS X and for Windows which will do the job?

Thanks


texts: Not able to change the AUTO_DEL_REC_OBJ value to ON

texts:
I changed the value of AUTO_DEL_REC_OBJ to on as below in UAT and prod environment; in UAT it's done but in prod its current value is still showing OFF. What did I do wrong?
In UAT:
db2 update db cfg for CMNDB using AUTO_DEL_REC_OBJ ON

 Description                                   Parameter   Current Value              Delayed Value
 -------------------------------------------------------------------------------------------------------
Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = ON                         ON

In Prod:
 Description                                   Parameter   Current Value              Delayed Value
 -------------------------------------------------------------------------------------------------------
Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF                        ON

Why has it changed in the UAT server but not in Prod?


texts: does max_connect_errors not check Aborted_connects?

texts:
In MySQL (5.5) which status var reflects the current number of connection errors that are compared against max_connect_errors by the server? I thought it was Aborted_connects but in my testing I can still connect to server after driving aborted_connects (in succession) well above max_connect_errors...

Asked differently, does mysql server check aborted_connects against any threshold variable? Is any behavior dependent on value of that status variable (or is just FYI for DBAs)?

EDIT 1

to clarify, I am less interested in how to reach max_connect_errors, and more in whether having a high aborted_connects will have any negative consequences.

For context:
we're using a layer 4 (TCP) load balancer to distribute JDBC connections of appservers' db connection pools across a cluster of MySQL read-only slaves. The load balancer (AWS ELB) has a TCP-based health check feature - it periodically checks health of the db servers by establishing a TCP connection with db server's mysql port. Since the LB does not speak 'JDBC', literally all it does is create a TCP connection and immediately disconnect. Each of these connections, however, increment aborted_connects on mysqld. I see no other side-effects, but wonder whether running up aborted_connects will eventually have negative consequences (for example, mysqld will ban the LB's IP, thus causing health check failures)

Thank you,
-nikita


texts: Can I get similar Full Text Search functionality without using Full-Text Search?

texts:
Normally, I wouldn't post this kind of question here, but I can't seem to find anyone who has actually used SQL Server Full Text Search, so I'm posting here.

I'm creating a solution for a client that involves searching an Item catalog.  The number of items is currently around 56k items.  This number could reach 200k in a few years, so I'm going to safely assume 500k max items.

Here are my requirements?

Searching needs to be fast.

Need to be able to do a keyword search that will search multiple columns (i.e. Material, Feature, VendorItemName, ItemName, etc...)

An item may have a value of 'Aluminum' in all 4 of the columns mentioned above.  Multiple matches for an item would 'rank' higher than an item matching on only one column.

Word Inflection: i.e. Search for PE should return Polyethelene material items, or search for RND (industry standard) should return items with Shape of Round.

Results will be weighted bases upon matches, number of matches, and other criteria that may be handled in code.

It seems Full-Text search will handle a lot of this, but I'm not sure it's the right approach.  I don't have any single columns with a lot of text (like a description field), which from what I understand, Full-Text search is really designed for.  I've considered creating a table that contains a concatenation of all the columns data combined into a single column.  I would use this column for searching only, and then use the returned index keys to join on my actual Item table.

When I start to consider these kinds of solutions, I begin to wonder if perhaps I'm better off not using Full-Text search.

On one hand, it seems like overkill. But on the other hand, it has a lot of features that I could use (thesaurus comes to mind).

So, should I continue to look into using SQL Server's Full-Text Search, or try and come up with my own solution.


texts: how to get collection based on number of duplicate foreign keys?

texts:
I have a batch table, and an item table, the relationship is the batch has many items (item has a batch_id foreign key).
How would I make a query that will find the batches which have more than 100 items?


texts: What is the difference between lock_timeout and deadlock_timeout?

texts:
What is the play between lock_timeout and deadlock_timeout ?

What is the reason for a lock_timeout to occur not leading to a deadlock timeout. I am not able to think of scenarios from database perspective ?


texts: Capitalize only the first letter of each word of each sentence in SQL Server

texts:
I want to capitalize only the first letter of each word of each sentence in a SQL column.

For example, if the sentence is:

  'I like movies'

then I need the output:

  'I Like Movies'

Query:

declare @a varchar(15)

set @a = 'qWeRtY kEyBoArD'

select @a as [Normal text],
upper(@a) as [Uppercase text],
lower(@a) as [Lowercase text],
upper(left(@a,1)) + lower(substring(@a,2,len(@a))) as [Capitalize first letter only]

Here I did upper, lower, and capitalize first letter only in my column (here I put just a random word).

Here are my results:

Is there any possibilities to do that?

Any possibilities to get results without using user defined function?

I need the output Qwerty Keyboard


texts: Database restore without deleting old database

texts:
I've done a copy of my database, using SQL Server 2008 R2 (DB -> Tasks -> Back Up).
Now I'm trying to restore it (DB -> Tasks -> Restore DataBase).

But SQL Studio give me an error and say, that I need to delete old DB to do it.
How to restore it without deleting?


texts: How do you combine the input of three parameters into one?

texts:
I'm working on a query that will run via C# to SQL server. I'm trying to make the query flexible and provide parameters for all the inputs.

Here is the query:

SELECT
  Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project,
  Test_Matrix.tblTestMatrix.Request,
  Test_Matrix.tblTestMatrix.Release,
  Test_Matrix.tblTestMatrix.TestName,
  Test_Matrix.tblTestMatrix.Formulation,
  Test_Matrix.tblTestMatrix.Container,
  Test_Matrix.tblTestMatrix.Closure,
  Test_Matrix.tblTestMatrix.Shipper,
  Test_Matrix.tblTestMatrix.Label_Front,
  Test_Matrix.tblTestMatrix.Label_Back,
  Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other,Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM
Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix
  ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber
  AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request
  AND Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE  (Test_Matrix.tblTestMatrix.Formulation = 'N5200-500') OR
       (Test_Matrix.tblTestMatrix.Container = '') OR
       (Test_Matrix.tblTestMatrix.Closure = '') OR
       (Test_Matrix.tblTestMatrix.Shipper = '') OR
       (Test_Matrix.tblTestMatrix.Label_Front = '') OR
       (Test_Matrix.tblTestMatrix.Label_Back = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release

When I run this in SSMS it returns data but when looking at it in the query designer I see that the three "other" components that I'm trying to combine into one field name "Other" does not appear to be correct.

I would appreciate some comments and suggestions on what I can do to combine these three input parameters into one output field.


texts: Update unpivoted table with one to many relation

texts:
Imagin you have a main table like that

DECLARE @ExistentData TABLE (ID INT IDENTITY(1000,1), [Row] INT, [Col] INT, Val NVARCHAR(50));
INSERT INTO @ExistentData([Row], [Col], Val)
VALUES
(1,10,'Value 1'),
(1,11,'Value 2'),
(1,12,'Value 3.1'),
(1,12,'Value 3.2')

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Value 1
1001        1           11          Value 2
1002        1           12          Value 3.1
1003        1           12          Value 3.2

Then you want to update above table with data below

DECLARE @NewDataToUpdateExistent TABLE ([Row] int, [Col] int, Val nvarchar(50));
INSERT INTO @NewDataToUpdateExistent([Row], [Col], Val)
VALUES
(1,10,'Updated Value 1'),
/*(1,11,'Deleted Value 2'),*/
(1,12,'Updated Value 3.1'),
(1,12,'Updated 3.2'),
(1,12,'New Value 3.3')

Row         Col         Val
----------- ----------- --------------------------------------------------
1           10          Updated Value 1
1           12          Updated Value 3.1
1           12          Updated 3.2
1           12          New Value 3.3

I want to do some performant update (without using cursors or other ugly iteration stuff)
resulting in the following final data on main table (@ExistentData)

ID          Row         Col         Val
----------- ----------- ----------- --------------------------------------------------
1000        1           10          Updated Value 1
1002        1           12          Updated Value 3.1
1003        1           12          Updated Value 3.2
1004        1           12          New Value 3.3

Deleting all related data and insert again (atm I'm doing this ) is not an option cause is an expansive operation regarding FK with other tables and index/statics management.


texts: Duplicate login details per user in error log

texts:
Being able to view details of who successfully logs into a database and which user's login failed via the error logs is a good thing. However I have a scenario that I need help with:

For some reason the success and failure details are logged multiple times and as such if I login to a SQL Server 2008 R2 instance this very minute, I see multiple "Login successful for user ..." messages.

Can anyone help to resolve this (without turning off the option to save login success/failure) as it is filling up the logs and pushing down relevant information.


texts:
I’m trying to pg_restore a db dump that contain a materialized views.

If it is relevant, the materialized view is of a foreign data wrapper.

It fails with the following errors:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 354; 1259 266280 MATERIALIZED VIEW x_daily_stats_mv u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  user mapping not found for "erik"
    Command was: CREATE MATERIALIZED VIEW x_daily_stats_mv AS
 SELECT x_daily_stats_v.campaign_id,
    x_daily_stats_v.t...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "x_daily_stats_mv" does not exist
    Command was: ALTER TABLE x_daily_stats_mv OWNER TO u5cuus46hhtdfs;

pg_restore: [archiver (db)] Error from TOC entry 4056; 1259 433791 INDEX x_daily_stats_mv_campaign_id u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "x_daily_stats_mv" does not exist
    Command was: CREATE INDEX x_daily_stats_mv_campaign_id ON x_daily_stats_mv USING btree (campaign_id);

pg_restore: [archiver (db)] Error from TOC entry 4057; 1259 433793 INDEX x_daily_stats_mv_creative_id u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "x_daily_stats_mv" does not exist
    Command was: CREATE INDEX x_daily_stats_mv_creative_id ON x_daily_stats_mv USING btree (creative_id);

pg_restore: [archiver (db)] Error from TOC entry 4058; 1259 433790 INDEX x_daily_stats_mv_date u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "x_daily_stats_mv" does not exist
    Command was: CREATE INDEX x_daily_stats_mv_date ON x_daily_stats_mv USING btree (date);

pg_restore: [archiver (db)] Error from TOC entry 4059; 1259 433792 INDEX x_daily_stats_mv_targeting_group_id u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "x_daily_stats_mv" does not exist
    Command was: CREATE INDEX x_daily_stats_mv_targeting_group_id ON x_daily_stats_mv USING btree (targeting_group_id);

pg_restore: [archiver (db)] Error from TOC entry 4484; 0 266280 MATERIALIZED VIEW DATA x_daily_stats_mv u5cuus46hhtdfs
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "x_daily_stats_mv" does not exist
    Command was: REFRESH MATERIALIZED VIEW x_daily_stats_mv;

WARNING: errors ignored on restore: 7

Looks like errors 2-7 are because the materialized view doesn't exist -- but why did it fail in the first place?  User erik is my local superuser.


texts: Why is my query not using "Missing index tip"? ( SQL Server 2008 R2 )

texts:
I know there's a lot of questions like this one, but I think this is different, and none of them could help me.

When I run a query with execution plan, I get this:

But, even with the index created, it's not using it.

The funny thing is, when I create it in my test environment (same database, same query, same index), it uses it.

Why is my production server not using the index I've created?

This is the same query in my test environment with the same index in the same database.

It's a simple query:

select *
from Database
where col1 = 'string'
  and col2 = 'string'

The index I created is:

create index (ix_col1_col2)
on Table (col1, col2)

Edit 1:

With Aaron's answers, I now have this:

I have the same result with * and using all fields.

The server still want me to create the same index it's already using. it's because the 0%? it thinks the query it's not using it?

EDIT 3:

What a strange thing. I used this:

alter index all on table rebuild

and update stats, and now, the query is using the index with 50% CPU.

Is this a good question to help others? or we should close it? since it was a simple problem.

Again thanks Aaron for your help.


texts:
I planned to migrate SQL Server 2008 Standard edition to Linux server instance. I have two problems with this instance:

How to activate this instance?
How to run instance in single mode to restore all databases (with master)?


texts: Multi language where this feature never existed

texts:
I need to implement multi language system in an existing system that was not developed for this.
The number of entries in the tables that will hold data that has to be translated will not exceed 5 digits and more importantly the number of results per page should be limited to a few dozen at most.

So I came up with this table to house additional languages for everything.
I'm planning to alter the code that selects the data and use a JOIN to get the appropriate translation for the fields in question.

Will this cause me problems and is there a better way that will not take much more time to implement as there is a time constraint on this.

CREATE TABLE `translations` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `row` BIGINT(20) NOT NULL,
    `field` VARCHAR(50) NOT NULL,
    `table` VARCHAR(50) NOT NULL,
    `language` INT(11) NOT NULL,
    `value` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `row_field_table_lang` (`row`, `field`, `table`, `language`),
    INDEX `entry` (`row`),
    INDEX `field_table` (`field`, `table`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

SELECT IFNULL(`t1`.`value`, `c`.`name`) AS `name`, IFNULL(`t2`.`value`, `c`.`text`) AS `text`
  FROM `entries` AS `c`
    LEFT JOIN `translations` AS `t1`
       ON `t1`.`row` = `c`.`id` AND `t1`.`field` = 'name' AND `t1`.`table` = 'entries' AND `t1`.`language` = 2
    LEFT JOIN `translations` AS `t2`
       ON `t2`.`row` = `c`.`id` AND `t2`.`field` = 'text' AND `t2`.`table` = 'entries' AND `t2`.`language` = 2
;


texts: How is a composite database different from an ESB in that both provide a single point of access for multiple data sources

texts:
After reading this article on composite db software here

it seems to me that the composite db software is not exactly a databse store like Oracle which physically stores the data. It is more like a adapter that connects to different sources in the back end and provides a single point of data access to the client. is this correct?

If the above is correct, then how is it different from an ESB which can act as a single point for the clients to access data? ESB can fetch data from a variety of cources and present ot the client and client dont have to know here or how the data is stored.


texts: Decision tables in the database: a valid solution?

texts:
I have to extend a .Net 4/C# case management application that uses Oracle 11g to handle legal cases.

I have minimal budget, my experience with Oracle and a very junior C# developer to accomplish this.  The additional module will be used by no more than 20 people on an intranet.

The existing rules are generally like this:

in order to complete an action there must be property a, property
b, property c present in a table with a clearly defined relationship
to the table where the new insert must be validated
For legal cases new business rules are more like "everything is an exception"
More formally restated as data entries in other tables may or may not affect the outcome of future transactions based on unknown conditions

The existing application uses Microsoft Workflow to implement the business rules. This has proven to be unwieldy, almost unusable for us. Drools and their commercial equivalents are equally complicated and will not achieve the goal of allowing managers to understand their business logic.

it would be a benefit if the business logic could be seen by users in a report without having to read C# comments or code.
logic can be added or disabled quickly without taking the web application down
fairly easy to implement given we have limited budget and skill sets

This brings me to the old fashioned idea of holding the business logic in the database as a series of decision tables. Is this still done or has the idea that the efficiency of holding the business logic in the application so obviously better?

Edit: Workflow is implemented as a service. My understanding is that it was aimed at long running workflows.  In our case users press the save button and the workflow runs. Our issues are
 - Painfully slow to develop with on our machines, freezes, crashes
 - difficult to debug
 - now and again the workflow manager throws errors for a few hours for no reason we can find
 - touch one workflow and you have to change 14 other files
 - application is slow for users, part of which we have traced to the workflows.

Validating in the database starts to look good because the validation is all based on data in the database.
@catcall There are 10 or 12 lawyers who do not use the application but have 8 or 10 admins do the data entry.
We just finished the desktop upgrade but the boss does not believe that programmer contractors should have better equipment than staff. So Windows XP with 4 cores and 3 GB of ram is the best we can do.
I will investigate if there is any classification of types of business logic. Some of it can be done more efficiently in the application.  Other types might be more easily changed and configured on the database side.


texts: Why doesn't my trigger put data into my table?

texts:
When I execute my trigger, it says it executed commands, but there is still no data in the tAttackPlan where it is supposed to insert data into. Can not figure out why.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID ('trAttack', 'tr') IS NOT NULL
    DROP TRIGGER trAttack
GO
CREATE TRIGGER trAttack
   ON tAttackPlan
   AFTER INSERT, UPDATE
AS
BEGIN

    SET NOCOUNT ON;
    DECLARE @AttackRequirementID INT, @MinionID INT, @DomainID INT, @MinimumCount INT, @Count INT, @MinionID_min INT
    SELECT @DomainID = (SELECT @DomainID FROM inserted)
    SELECT @Count = (SELECT SUM(Count) FROM tCensus GROUP BY MinionID)
    SELECT @MinionID_min = tMinion.MinionID
    FROM dbo.tMinion
    INNER JOIN dbo.tCensus ON dbo.tMinion.MinionID = dbo.tCensus.MinionID
    CROSS JOIN dbo.tAttackRequirement
    CROSS JOIN dbo.tAttackPlan
    INNER JOIN dbo.tDomain ON dbo.tAttackPlan.DomainID = dbo.tDomain.DomainID

    INSERT INTO tAttackPlan (DateOfAttack, DomainID)
    VALUES (1050, @DomainID)

    IF @Count < @MinimumCount
        RAISERROR ('There are not enough minions to attack', 16, 10)
END
GO


texts: I have a database server in HQ, while in branch I need user able to get the data from database server to perform daily task.  The problem is the connectivity is not that good.  I wish to have another set of data which from HQ database server to sit in branch to let user to do a daily task and what ever update in branch will sync back to database server in HQ and vice versa.  Any idea how I can do this?  using replication or mirroring and any reference I can refer how to do this?


texts: ALL permission is deprecated and maintained only for compatibility?

texts:
How can i resolve this issue below?

use eNtsaMemberRegistrationDB
go
grant all on tablename to Users;

Message

  The ALL permission is deprecated and maintained only for
  compatibility. It DOES NOT imply ALL permissions defined on the
  entity.


texts: MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

texts:
i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql 5.0 / 64bit) into a bi-xeon / 24gb ram / 2x 120gb intel 320 ssd (ubuntu 10.10 / mysql 5.1 / 64bit).

now i'm facing the problem that the performance is worse than on the "smaller box". On both servers i've been using nginx / php fcgi to serve the content.

i'm using innodb only, having Reads / Writes about 65% / 35%. Around 800 - 1000 qps but all Queries are simple and never join more than 1 additional table. All indexes are set and no individual query is logged in the slow log (> 2s).
At the moment i have around 400mb of data (around 1gb with indexes) expecting it to double every month.

I'd adore everyone who could give me a hint what to change to make it run smoother.

The old configuration on the i7 box was like this (mixed myisam / innodb), performed pretty good up to 800+ users.

old my.cnf

   key_buffer              = 3000M
   max_allowed_packet      = 128M
   thread_stack            = 192K
   thread_cache_size       = 8
   max_connections        = 400
   table_cache            = 8000
   thread_concurrency     = 16
   query_cache_limit       = 8M
   query_cache_size        = 128M
   wait_timeout            = 10
   interactive_timeout     = 10
   connect_timeout         = 600
   low_priority_updates    = 1
   join_buffer_size        = 8M
   read_buffer_size        = 2M
   sort_buffer_size        = 3M
   myisam_sort_buffer_size = 32M
   read_rnd_buffer_size    = 4M
   innodb_buffer_pool_size = 3G
   innodb_log_buffer_size  = 8M

The new configuration on the bi-xeon box is like this (pure innodb), causing high load with 300+ users. Around 30 mysql processes sitting on the top of the process list.

Disk I/O:

    avg-cpu:  %user   %nice %system %iowait  %steal   %idle
              36.28    0.00    1.60    0.17    0.00   61.95

my.cnf

    key_buffer              = 64M
    max_allowed_packet      = 1M
    thread_stack            = 192K
    thread_cache_size       = 128
    max_connections        = 500
    table_cache            = 512
    #thread_concurrency     = 10
    sort_buffer_size        = 256K
    read_buffer_size        = 256K
    read_rnd_buffer_size    = 256K
    tmp_table_size          = 32M
    max_heap_table_size     = 32M
    query_cache_limit       = 1M
    query_cache_size        = 128M
    query_cache_type        = 1

    innodb_file_per_table = 1
    innodb_data_file_path = ibdata1:1000M:autoextend
    innodb_buffer_pool_size = 16384M
    innodb_additional_mem_pool_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_support_xa = 0
    innodb_lock_wait_timeout = 50
    innodb_flush_method=O_DIRECT
    innodb_log_files_in_group = 2
    innodb_log_file_size = 128M
    innodb_log_buffer_size = 8M
    innodb_thread_concurrency = 12


texts: SQL Server: subscriber as publisher and hierarchical replication

texts:
In Oracle Streams one way replication from one DB to another is a basic block for many replication topologies (N-way, hierarchical, combined and so on), and changes could be captured and applied for the same table at the same time.

But I can't find anywhere in SQL Server documentation whether table (article) could be a source (publisher) and destination (subscriber) simultaneously. Is it possible, for example, to setup bidirectional replication using two pairs of publisher->subscriber transactional replication?

Naturally I am more interested in multi-level hierarchy: for example one table is replicated from root node through intermediate nodes to leaves, second one - from leaves to root, and third one - bidirectionally, and for all tables intermediate nodes could perform DMLs to be replicated too. With Oracle Streams it is easy to achieve, and even more sophisticated configurations are possible, but are they with SQL Server?

UPDATE: It seems it is with use of merge replication and republishing (http://msdn.microsoft.com/en-us/library/ms152553.aspx), but what about transactional replication?


texts: Oracle autotask results of space advisor

texts:
When querying the dba_autotask_client view on a Oracle 12c database, I can see that the auto space advisor is enabled. Oracle docs says the findings are now implemented automatically. How can I view the results of this autotask?

CLIENT_NAME                      STATUS   CONSUMER_GROUP       MEAN_JOB_DURATION
-------------------------------- -------- -------------------- --------------------------------------------------
sql tuning advisor               ENABLED  ORA$AUTOTASK         +000000000 00:00:00.700000000
auto optimizer stats collection  ENABLED  ORA$AUTOTASK         +000000000 00:00:06.841993315
auto space advisor               ENABLED  ORA$AUTOTASK         +000000000 00:00:01.273776967


texts: Why can't I persist binary(4) computed column?

texts:
I'm playing with storing and indexing IP addresses.  I'm starting with a simple, stupid table:
CREATE TABLE [dbo].[IP_addresses](
    [IP_as_text] [char](16) NOT NULL,
    [IP]  AS ([dbo].[fnBinaryIPv4]([IP_as_text]))
) ON [PRIMARY]

Where fnBinaryIPv4 is from https://stackoverflow.com/questions/1385552.
CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END

However, when I try to add PERSISTED to the IP column or use it in an index, I get a message that it is not deterministic.  I've Googled around a bit and that usually has to do with the style passed to CONVERT() for a date but that doesn't seem to apply here.  http://www.sql-server-helper.com/functions/system-functions/index.aspx says CAST() and PARSENAME() are deterministic so I don't see why fnBinaryIPv4() is nondeterministic.  But it turns out that PARSENAME() used to be but is no longer deterministic.  So I rewrote that function:
CREATE FUNCTION [dbo].[fnBinaryIPv4](@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @int_addr AS bigint = 0;
    DECLARE @b CHAR(3);

    DECLARE bCursor CURSOR FOR (
        SELECT value FROM STRING_SPLIT(@ip, '.')
    )

    OPEN bCursor

    FETCH NEXT FROM bCursor INTO @b
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @int_addr = (@int_addr * 256) + CAST(@b AS INTEGER)
        FETCH NEXT FROM bCursor INTO @b
    END

    CLOSE bCursor
    DEALLOCATE bCursor

    RETURN CAST(@int_addr AS BINARY(4))
END

But this version is still nondeterministic.


texts:
I've been pouring over threads regarding this issue, but ours seems unique in that it's not an issue with the query but the row itself; or at least I couldn't find a similar topic addressing how to fix the row.

A simple query like this:

> update customer set customer_name = 'Health Net of CA' where customer_id = '15484';

Results in:

ERROR 1406 (22001): Data too long for column 'customer_name' at row 1

Checking the character length:

mysql> select char_length(customer_name) from customer where customer_id = '15484';
+----------------------------+
| char_length(customer_name) |
+----------------------------+
|                         54 |
+----------------------------+
1 row in set (0.00 sec)

Describe shows:

 | customer_name      | varchar(255) | YES  |     | NULL    |                |

This database was populated using an import. I'm fairly sure strict mode was on but I didn't handle the import myself so I can't say for certain. This table has 39 columns, and most are Varchar(50) or tinyint so it shouldn't be an issue with the row being too big.

Any suggestions on fixing these bad rows?

Update:

SHOW CREATE TABLE customer;
| customer | CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_name` varchar(255) DEFAULT NULL,
  `customer_code` varchar(12) DEFAULT NULL,
  `customer_type` text,
  `bill_type` text,
  `attention` varchar(100) DEFAULT NULL COMMENT 'Attention to who we deal biz with',
  `address_id` int(11) DEFAULT NULL,
  `b_bad_debt` tinyint(1) DEFAULT '0',
  `b_fee_approval` tinyint(1) DEFAULT NULL COMMENT 'boolean flag for Fee Approval 1=set, 0=unset',
  `approval_amount` decimal(5,2) DEFAULT NULL,
  `notification` varchar(45) DEFAULT NULL COMMENT 'notified customer by email / fax or ftp',
  `b_tax_exempt` tinyint(1) DEFAULT '0' COMMENT 'Tax Exempt Flag',
  `sales_tax_number` varchar(20) DEFAULT NULL COMMENT 'sales tax/ permit no.',
  `b_prepay` tinyint(1) DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  `last_updated` timestamp NULL DEFAULT NULL,
  `active` tinyint(4) DEFAULT '1',
  `created_by` varchar(45) DEFAULT NULL,
  `updated_by` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `email_extra` mediumtext COMMENT 'extra emails as per creation',
  `state_tax_code` varchar(2) DEFAULT NULL COMMENT 'this is as CA if CA state and refer to TAX table',
  `email_verified_by` varchar(45) DEFAULT 'NA',
  `fax_verified_by` varchar(45) DEFAULT 'NA',
  `b_always_send` tinyint(1) DEFAULT '0' COMMENT 'there is customer that we need always send',
  `b_project_customer` tinyint(1) DEFAULT '0',
  `b_exception_list` tinyint(1) DEFAULT '0',
  `b_has_inslist` tinyint(1) DEFAULT '0',
  `customer_passwrd` varchar(255) DEFAULT NULL,
  `delivery_opt` varchar(45) DEFAULT NULL,
  `max_fax_pages` int(11) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `fax` varchar(20) DEFAULT NULL,
  `phone_extra` mediumtext,
  `phone_second` varchar(20) DEFAULT NULL,
  `b_multi_suites` tinyint(1) DEFAULT '0',
  `suite_list` varchar(255) DEFAULT NULL,
  `b_portal_download` tinyint(1) DEFAULT '0',
  `b_no_download` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`customer_id`),
  KEY `customer_idx` (`customer_code`,`customer_name`)
) ENGINE=InnoDB AUTO_INCREMENT=18870 DEFAULT CHARSET=utf8 |


texts:
I have a MariaDB 10.1 master/slave using InnoDB setup running on CentOS 7 which is mostly working as expected. However, somehow the slave has ended up with an extra column in a few tables. This column was deleted on the Master as part of the data import process but that change has not happened on the slave. The column is not required at all so I'd like to delete it.

So my question is (hopefully) simple: Can I safely delete the column directly on the slave without breaking replication?

Many thanks.


texts:
The Problem
I am trying to document a large number of SQL queries at my workplace. I'd like to programmatically parse the queries and generate graphical representations (diagrams) of the queries. For context, here are some existing solutions I have found:
https://queryvis.com/  (demo at http://demo.queryvis.com/)
or
http://revj.sourceforge.net/
Constraints
Due to security policies, I am not allowed to use online tools so queryvis is out.
The second solution revj is promising. It works on my personal Linux system, but it needs Graphviz, and I'm not allowed to install Graphviz on the work computer.
Available tools
IT/Security team very tightly control what I'm allowed to install on my work computer. For now, I can have any Python libraries I want. I use DBeaver to access databases.
What are my options?


texts: how to script out the user defined table types?

texts:
I can Get name and definition of all table types
using either of the following scripts:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL
   DROP TABLE #RADHE

CREATE TABLE #RADHE
(
 RADHE SYSNAME,
 COLUMN_NAME SYSNAME,
 TYPE_COLUMN SYSNAME,
 PRIMARY KEY CLUSTERED (RADHE,COLUMN_NAME)
 )

DECLARE @sql nvarchar(max) = N'',
  @stub nvarchar(max) = N'SELECT [RADHE]=N''$--RADHE--$'',
   COLUMN_NAME=name, TYPE_COLUMN=system_type_name
   FROM sys.dm_exec_describe_first_result_set(''DECLARE
   @tvp $--RADHE--$; SELECT * FROM @tvp;'',null,null)
   ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--RADHE--$',
  QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

INSERT INTO #RADHE
EXEC sys.sp_executesql @sql;

SELECT * FROM #RADHE

SELECT
    tt.name AS table_type_name,
    c.name AS column_name,
    c.column_id,
    t.name AS type_name,
    c.max_length,
    c.precision,
    c.scale,
    c.collation_name,
    c.is_nullable
FROM
    sys.columns As c
    JOIN sys.table_types AS tt
        ON c.object_id = tt.type_table_object_id
    JOIN sys.types AS t
        ON t.user_type_id = c.user_type_id
ORDER BY
    tt.name,
    c.column_id

and I can even GRANT REFERENCE on all user defined types using the following script:

    SELECT t.name,
           'GRANT REFERENCES ON TYPE::'
           + SCHEMA_NAME(t.schema_id)
           + '.'
           + t.name
           + ' TO public;' AS command_to_run
    FROM   sys.types AS t
where 1=1
 AND T.is_table_type = 1

But is there a way to script out all table types in a database?

I am looking to script out this table type, please note the constraints and index created with it:

use TableBackups
go

IF EXISTS(SELECT *
            FROM SYS.table_types tt
           WHERE tt.NAME=N'DistCritGroupData'
             AND SCHEMA_NAME(tt.SCHEMA_ID) = N'dbo')
   DROP TYPE DBO.DistCritGroupData

CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL UNIQUE,
    [ItemAction] [int] NOT NULL,
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL,

    PRIMARY KEY NONCLUSTERED
    (
       [DistCritTypeId] ASC
    ),

    INDEX CIX CLUSTERED (ObjectId, OperatorType)
  );


texts: Select query on information_schema is too slow

texts:
SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME   = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY'
  AND CONSTRAINT_TYPE   = 'FOREIGN KEY';

The above query takes around 2 mins to execute
is there any other alternate query or how can we optimize this query


texts: Capacity planning for SQL Server consolidation

texts:
My company has a small SQL environment for LANDesk and they want to migrate all the servers to a single SQL cluster per region. What I want to know is, how do I determine the capacity for such a scenario? I have obtained the database growth per individual server (All Landesk) and can I take this data as a rough estimate for the consolidation in to the single SQL cluster? The Windows team tells me that the data from these servers is kinda wonky and might not give an accurate result what they want.

The new cluster in question already has a couple of databases but it is not at it's fullest capacity yet. Once they start adding the remaining data from the servers, it should reach it's full capacity. I have been running Perfmon on this for a while and for checking database growth  I used the below TSQL code from sqlskills.com

SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

Is there anything I can do to get an accurate result? The LANDesk team doesn't want me to use the older servers and the only option I have is to use the new cluster where they plan to migrate all the data and I only have 6 months worth of data on this. Should I ask them questions like number of years this new cluster be used? etc...

Thank you all for your answers. Here is what I have from the PAL tool

I used the PAL tool to determine perfmon data and these are some of the warnings it gave
  1)Response time greater than 25ms
  2) Less than 10% Idle Time - the disk queue has at least 1 outstanding I/O 90% of the time
  3)Greater than or equal to 64 KB IO sizes. Generally, the larger the IO size, the more data can be transferred per second, but the response times are longer.

4)Physical disk read latency - greater than 25ms
  5)Process % Privileged Time - SQL server using more than 30% of privilged mode CPU usage
  6)SQL Server Access methods FreeSpace Scans/sec - A ratio of more than 1 freespace scan for every 10 batch requests - confirmed with High paglatches, async i/o completion and low page life expectancy
  7)SQLServer:Access Methods Workfiles Created/sec - A ratio of more than 1 workfile created for every 20 batch requests
  8)SQL Buffer cache hit ratio : Less than 97 percent buffer cache hit ratio
  9)Red: Greater than 20 Lazy Writes per second
  10)Red: Page life expectancy is less than 5 minutes (300 seconds)
  11)Yellow : A ratio of more than 1 SQL Re-Compilation for every 10 SQL Compilations

Regarding I/O we are moving to SQL 2012 cluster so I am not sure which is another possible way to measure IOPS. Would you suggest SQLsim or IOmeter etc? Thanks again!


texts: How can I find the Database ID of a deleted database?

texts:
Where can I view/find the database ID after a database was deleted?

I work in software/hardware environment and we have locking unit (prototype) synced in our S/W with unique DB ID. In order to reset this locking unut we need to know the DB ID. Unfortunately DB was completely deleted and we have no way to continue our work.

Database was deleted about 6-7 days ago.


texts: Mysql Select Subquery where clause subquery equal alias ( AS subquery named )

texts:
I'am tired looking for this issue on internet, I don't know what a keyword for search for that, and I can`t find same issue for my issue.

Ok to the point.

select id, name,

(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as haveBonA,

(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as haveConA,

IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC

from tableA;

If this query run output error : Unknown column 'haveBonA' in 'field list'

I don't know how to express it. and I'm sorry, can't speak english.
Thank Your.

SOLVED

select id, name,

(select count(tableB.id) from tableB where tableB.id_tableA = tableA.id) as haveBonA,

(select count(tableC.id) from tableC where tableC.id_tableA = tableA.id) as haveConA,

(select (IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC)

from tableA;

I change to (select (IF(haveBonA=haveConA,"Equal","Not Equal") AS equalTableBTableC)
Thank you for all answering.


texts: How to make use of Batch Mode with an UNPIVOT (a loop join)?

texts:
I have a query of the following form:

SELECT ...
FROM ColumnstoreTable cs
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('A', cs.DataA)
        , ('B', cs.DataB)
        , ('C', cs.DataC)
    ) x(Col0, Col1)
) someValues

This takes every row from a Columnstore-backed subquery (ColumnstoreTable) and multiplies those rows. This is essentially an UNPIVOT. The real query is larger than this. This part of the query feeds into other processing.

The problem here is that this CROSS APPLY is implemented as a loop join which is a reasonable choice. Unfortunately, loop joins do not support batch mode.

This part of the query is very performance critical and I suspect that running it in batch mode could be very beneficial to performance.

How can I rewrite this query so that I do not transition out of batch mode?

I did try using a temporary table instead of VALUES, but that did not change the fact that there is no equality join condition to hash join on.


texts:
I've stumbled upon an article describing nested sets model for storing hierarchical data and was quite intrigued by the supposed performance boost.

after implementing this in our database, i've found that the adjecency list model performs far better. though the article I've read and a couple of SA posts (one, two) stated that any tree traversal is supposed to benefit, my tests demonstrate the opposite.

Am I missing something?

there are roughly 1.1 Million entries in the following table:

SQL> show table tax_nodes;
TAX_ID                          INTEGER Not Null
PARENT_TAX_ID                   INTEGER Nullable
RANK                            VARCHAR(50) Nullable
DIVISION_ID                     INTEGER Nullable
TREE_RIGHT                      INTEGER Nullable
TREE_LEFT                       INTEGER Nullable
CONSTRAINT INTEG_202:
  Primary key (TAX_ID)

Adjecency List Query to fetch parents

with recursive lineage as
(select tax_id, parent_tax_id
 from tax_nodes
 where tax_id = 365612
 union ALL
 select tax_id, parent_tax_id
 from tax_nodes t
 join lineage l on l.parent_tax_id = t.tax_id
)
select l.tax_id
from lineage l

Nested Sets Query to fetch parents

select
  p2.tax_id from tax_nodes as p1,
  tax_nodes as p2
where
    p1.tree_left between p2.tree_left and p2.tree_right
    and p1.tax_id = 365612

Result

365612 336487 10260 10241 10240 35237 10239 1  //1 is the root

Why is the Adjecency List performing better though most resources suggest otherwise?

I'm running Firebird 2.5 superserver by the way.

UPDATE

On @MarkRotteveel's hint, I took a look at the execution plans and I suspect the NATURAL being the culprit here. Question now is, how do I get rid of it?

adjecency list execution plan

PLAN (LINEAGE TAX_NODES INDEX (RDB$PRIMARY108))
PLAN (LINEAGE T INDEX (RDB$PRIMARY108))

nested sets execution plan

PLAN JOIN (P1 INDEX (RDB$PRIMARY108), P2 NATURAL)


texts: How to efficiently store 672 bits of information in PostgreSQL

texts:
I have a requirement that needs to store lots of bits and it's actually a time setting for every 15 minutes in a week (indicating if a user is free during that time).

So for one day we need: 60 minutes/ 15 = 4 * 24 hours = 96 bit

And for a week, it will be: 96 * 7 = 672

I was originally thinking that I could store this in 7 columns of bigint[2],
each column representing a day and the first bigint of the array represening the bit from 00:00 to 12:00 (12 * 4 = 48bit).

And here is how I calculate the bigint:

  start_idx := extract(EPOCH FROM (first_half[1]::time - '00:00'::time))::integer/60/15;
  end_idx   := extract(EPOCH FROM (first_half[2]::time - '00:00'::time))::integer/60/15;
  -- RAISE NOTICE 'idx % %', start_idx, end_idx;

  WHILE start_idx < end_idx LOOP

   first_r := first_r | (1::bigint << start_idx);

   start_idx = start_idx + 1;
  END LOOP;

So the start_idx will be the result and contains all the bits from 00:00 to 12:00.

But this is ugly because I have to separate the time setting into two parts and I just come across this thread that says that I should store the bits separately.

So I am not sure if I should use the array or if I should just create 672 columns of Boolean which I think is a bit crazy!


texts: monitoring mongodb and mysql replications using nagios

texts:
I have set replication for mongoDB and mySQL.

Now I have to manage them using nagios. I have to write some php-script that will read server status.

I am good with nagios setup.

Now I want to know if there is some plugin that may ease my work, otherwise how exactly do I get the server status from nagios.

One solution I thought is to read the log file of nagios and look for pattern to know the server status, is it feasible?


texts: Error "Msg 10054, level 20, State 0, Line 0" with MSSQL under Docker

texts:
I've set up several docker containers in my Ubuntu 16.04 LTS machine. All of them have in common the same container image.

It works pretty well, but some users have reported me a common issue when attempting a long-lasting query and about a minute after it execution, they get an error message:

  Mens. 10054, Nivel 20, Estado 0, Línea 0
  Error en el nivel del transporte al recibir los resultados del servidor.
  (provider: TCP Provider, error: 0 - Se ha forzado la interrupción de una conexión existente por el host remoto.)

The error message is translated into english as Error 10054, level 20, State 0, Line 0.

Is there something I can modify under their configuration?

PS: I've noticed that in server's properties Connection > Remote servers connections > I've "Permitir conexiones remotas con este servidor checked", having 0 seconds (no wait time) of waittime.

EDIT
I attach one container's configuration snapshots:

Omitted the permission config, as I don't think it's relevant.


texts: Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

texts:
I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases.

I have then passed this .trc trace file as a parameter to the database engine tuning advisor.

After running the DTA I get the recommendations:

How do I script out the recommendations?

I am using SQL Server 2005, and I don't seem to find any other way than scripting them individually which is overly time consuming.


texts: Database design advice on a marksheet schema

texts:
I am not sure whether this type of question is allowed here, but seeing other questions, i think it is.

I am doing a database design of processing exam marks. There is a marksheet for each of the answersheet. Marksheet is identified by reference of answer sheet. Each answer sheet is evaluated by 2 or more examiner.

I want to store the marks for each questions. So, if a question paper has 8 questions, there should be 8 entry for marks, some of which can be blank depending on whether the student has answered that question or something.

And there is a paper code associated with each answer sheet, which is unique for a given exam.

This is my AnswerSheet Table

Id (Primary Key)
Exam_ID (foreign key to exam table)
Paper Code (manually given for each paper, unique for a single exam)

Note that, by exam I am referring to a single course exam, not whole set of exams.

Here is my Marksheet table:

Id (Primary key)
Answer_sheet_id (foriegn key to the answer sheet table)
Examiner_ID (to identify the teacher evaluated this sheet)
Mark on Question 1
.
.
Mark on Question 8

Here, you see that I am hardcoding the question 1 to 8 in the database, which I think a bad design. My logic is that, It's very unlikely to change the no of questions in an exam in future. But, It's not absolutely guranteed.

I am seeking other alternative/better design for this problem?


texts: Incorrect datetime value for str_to_date

texts:
I'm trying to copy a varchar column which contains dates in the format YYYY-MM-DD to a date column using str_to_date.

My query is:

UPDATE `subscribers` SET `srtdt` = STR_TO_DATE(`StartDate`, '%Y%m%d%');

I am getting error 1411: Incorrect datetime value: '2001-09-16'... This seems to be happening on the first row.

I have searched a lot for this but cannot find why this is happening - all other answers point towards the string being in the wrong format etc, and that doesn't seem to be the case with my problem.


texts: Why innodb doesn't store geospatial data?

texts:
I am very curious.

I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam.

But why would I do so?

Why can't innodb store points?


texts: Mongodb how to cancel a --fork without mongod service 'option'

texts:
I am currently having issues with sudo service mongod start/stop/etc so instead I have been running mongo with sudo mongod --storageEngine wiredTiger --dbpath /data --bind_ip_all  --logpath /var/log/mongodb/mongod.log --auth --fork but I received errors if I wanted to change any of those --options (such as --bind_ip_all to something more specific) to something else and I am assuming it is because the --fork has never been cancelled.

To summarize, how do you cancel mongod through the cli so that I can update the call?


texts: Does MySQL Enterprise Backup work across Operating Systems?

texts:
The problem with testing is that one can never be sure (it may seem to work and then you find some thing changed later). A definitive statement from the vendor would definitely be helpful (I cannot seem to find any).

I am thinking about running MySQL Enterprise Backup between MacOS and Liunx OSes


texts: Service Credentials to use in AlwaysOn Availability Groups?

texts:
When using AlwaysOn Availability Groups does each SQL Instance need to use the same service credentials?

We are setting up a new SQL2014 deployment and will be using AlwaysOn. If I have 2 SQL servers (call them ServerA and ServerB) then can I use 2 active directory accounts called Domain\ServerA_SQLServerService and Domain\ServerB_SQLServerService (one on each server)?

Or do they need to use the same account so I should just create Domain\SQLServerService

The accounts would be in the same domain and can have access to both servers. Previously we've had the SQL Server Service account locked out which has obviously caused problems. What I'd like to do is have 2 accounts so that the likelihood of both being locked out is reduced.

Is there a requirement and/or what is Microsoft's best practice?


texts:
I've been tasked with designing a system to recur events. Events may recur daily, hourly, monthly or yearly. Users should be able to schedule events "every 3rd Wednesday of the month" or "The 1st of each month" or "hourly."

Say I'm starting with a 'events' table:

events
-------
id INT
name VARCHAR
notes TEXT
...

What fields/entities/architecture should be used to persist the recurrence rules?

fwiw, I'm using MySQL 5.5.x.

UPDATE (Aug 29, 2012):

Considering iCalendar standards
Reviewed this SO link
Still mulling a better path.


texts: Always ON - What is the difference between the virtual cluster name and Listener

texts:
In clustering you specify DNS name/IP address of cluster that is then used by all applications. In case of failover, the same DNS name/IP address will point to the appropriate database server.

In Always on Availability Groups, there is concept of Listener which to my understanding does the same as above, so do we really need a Listener when the Failover clustering already handles this concept (IP address) of failover?


texts: What are reasons **NOT** to use the MEMORY storage engine in MySQL?

texts:
I recently discovered that MySQL has a "memory" engine that I wasn't aware of (most of my database work is for hobby projects so I learn what I need as I go).  It seems like this option should give me drastically improved performance, so I'm wondering if there are any drawbacks that go with it.  The two that I know of are:

I need to have enough RAM to hold the table(s) in question.
The tables are lost if the machine shuts down.

I believe #1 shouldn't be an issue since I'm using AWS EC2 and can move to an instance type with more memory if needed.  I believe I can mitigate #2 by dumping back to disk as needed.

What other issues are there?  Can the memory engine ever give worse performance than either MyISAM or InnoDB?  I think I read something that indices are different with this engine; is this something I need to worry about?


texts: Minimizing downtime during MariaDB Replication Setup

texts:
I have the task of setting up MariaDB (10.0) replication from a master running in a datacenter to a slave running out in an AWS VPC. Reading up on the instructions on Setting Up Replication - MariaDB Knowledge Base there's mention of taking a lock on all tables on the master (FLUSH TABLES WITH READ LOCK) while using mysqldump to transfer the Databases across to the slave. This step has me concerned a slight bit for the following reasons

We have a number of large databases (~20 DBs @ 40G) with some huge ones (~3 @ 100G).
It takes about ~40 minutes to transfer each DB across to the slave. In total, this entire process could take up to 20 hours (assuming good conditions).
We can't keep locks on the master for too long - as we disrupt business while the tables are locked.
Should anything fail in the process of copying the dumps across to the slave - we'll have taken the master down in vain and may need to attempt this again.

What would be a good way to go about setting up replication in this scenario with minimal downtime to production. I'm open to some out of the box thinking - is there a way to do this by breaking things down and doing them in stages so that we can verify each stage?

EDIT
I should add that we do take nightly dumps of the DBs as part of the backup strategy. Perhaps there's a way of using these to aid the process?


texts: Which MySQL storage engine is the most similar to ARCHIVE when that is not available?

texts:
I have a table that contains a log of events that is written to often and read very infrequently by comparison.  I would love to use the ARCHIVE engine, because it is basically designed for exactly this.  However, this engine is not available on my hosting provider.

The only (useful) options available are MyISAM, InnoDB, and CSV.  I am pretty sure I should not use CSV for this option, so let's discount that one.

What I'm looking for then is the engine that provides the best alternative, keeping in mind the following:

Good compression would be very nice.  A lot of the data may be very similar between rows (as is the case with log messages)
I don't need a fast look up.  Indexes aren't really necessary.  Again, I'm looking to save as much space as possible.
There needs to be support for lots of rows.  Hence the need for compression and small footprint.
Rows do have a foreign key to another table, but it is not necessary to retain the relationship.  Each log entry has a name associated with it that is stored in the other table, which doesn't change.

So based on that, which is better, InnoDB, or MyISAM.  I know that the MyISAM engine installed on this server only supports 2^32 rows.  If InnoDB has similar support for compression and supports more rows, that would be better (but I don't know if that is the case).


texts: SQL pre-login handshake connection failure

texts:
I'm having an intermittent problem with one of my old SQL 2000 servers. Every once in a while it stops accepting logins. I fix the problem by bouncing sqlservr.exe and then it starts working properly for a few days. For example, running the PowerShell:

$connectionstring = "Server=.;Integrated Security=SSPI;"
$sqlconnection = new-object 'System.Data.SqlClient.SqlConnection'
$sqlconnection.connectionstring = $connectionstring
$sqlconnection.open()

Produces the error:

Exception calling "Open" with "0" argument(s): "A connection was successfully established with the server, but then an
error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no lon
ger available.)"

Similarly, if I change the connection string to:

$connectionstring = "Server=\\.\pipe\MSSQL`$SMS3000\sql\query;Integrated Security=SSPI;"

I get a similar error, except the end of the error says: (provider: Named Pipes Provider, error: 0 - The pipe has been ended.)

Checking C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SVRNETCN.exe. Both Named Pipes and TCP/IP are enabled and "Force protocol encryption" is unchecked. And as I mentioned, if I bounce sqlservr.exe then the above commands create a successful connection.

I've checked the certificate store and there are no expired certificates in there. Checking the Windows event logs and SQL Server logs I'm not finding anything remotely useful there.

Lastly, I've run some network traces. For a failed connection I show:

TLS:TLS Rec Layer-1 HandShake: Client Hello.
TCP:Flags=...A.R.., ScrPort=1433, DstPort=18721, PayloadLen=0, Seq=888695317, Ack=3640041213, Win=0 ...

A successful connection looks like:

TLS:TLS Rec Layer-1 HandShake: Client Hello.
TLS:TLS Rec Layer-1 HandShake: Server Hello. Certificate.

I've hunted around the registry to try to figure out which certificate SQL is using, but I haven't had any luck there yet. Any ideas on what to look for next?


texts: User names in postgres docker image logs

texts:
I'm looking to get thorough logs of user activity attributable back to users from the official docker image of Postgres.  I'm running my docker image like so:
docker run --name pg -e POSTGRES_PASSWORD=password -p 54320:5432 postgres -c log_statement=all -c log_line_prefix=`t=%m u=%u db=%d pid=[%p]:

But I'm getting logs that look like:
PostgreSQL init process complete; ready for start up.

LOG:  starting PostgreSQL 13.1 (Debian 13.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  listening on IPv6 address "::", port 5432
LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
LOG:  database system was shut down at 2021-01-24 11:58:13 UTC
LOG:  database system is ready to accept connections
FATAL:  database "test" does not exist
LOG:  statement: SELECT VERSION()
LOG:  statement: SHOW ALL
LOG:  statement: SELECT datName FROM pg_database WHERE datAllowConn ORDER BY datName
LOG:  statement: CREATE DATABASE "test"
LOG:  statement: /* setup database */

    CREATE TABLE IF NOT EXISTS users (
       id SERIAL PRIMARY KEY,
       name varchar(255) NOT NULL,
       email varchar(255) NOT NULL,
       password_hash varchar(255) NOT NULL
    );

    /* fill with seed data */
    INSERT INTO users
    (name, email, password_hash) VALUES
    ('Robert', 'robert@example.com', 'hash'),
    ('Bob', 'bob@example.com', 'new_hash'),
    ('Rob', 'rob@example.com', 'another hash');
    /* read data, write data using good user */

    /* read data, write data */
LOG:  statement: SELECT name, setting FROM pg_settings WHERE source = 'session';

Which doesn't give me a good timestamp or attribute the activity to a user.  What am I doing wrong?


texts: Sysbench insert data between a given range

texts:
I was trying to insert 1,000,000 records of data into 1,000 tables in sysbench. But after installing about 278, sybench just crashed with the following error:

  FATAL: failed to execute function `prepare': /usr/share/doc/sysbench/tests/db/common.lua:84: db_bulk_insert_next() failed

The sysbench command I used for preparing the data is given below:

sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-tables-count=1000 --oltp-table-size=100000 --mysql-user=superuser --mysql-password=superpass --mysql-socket=/var/lib/mysql/mysql.sock prepare

I do not want to start preparing the data from scratch again. Is there a way I can declare a range on which to insert data e.g. oltp-tables-count=279-1000. I know this does not work because I tried already, but you get the idea what I am trying to achieve here. Any ideas how to accomplish this in sysbench?


texts:
I am fairly new to DBMS and backend management, and was hoping to get some advice on the following issue.

We have a Postgres database with the following tables: members and activity_scores. The activity_scores belong to each member, and are written to every 10 minutes. Here is an example of one of the rows of the activity_scores table (indexed on member_id):

 id | member_id | score |         created_at         |         updated_at
----+-----------+-------+----------------------------+----------------------------
  1 |         8 |    73 | 2016-11-04 05:32:55.564235 | 2016-11-04 05:32:55.564235
  2 |        10 |    20 | 2016-11-04 05:22:55.564235 | 2016-11-04 05:22:55.564235

Size of activity_scores data per member could be 10,000's to millions.

The query we are trying to run essentially takes scores for a member, and runs the average grouped by the date:

SELECT TO_CHAR((activity_scores.created_at AT TIME ZONE ? AT TIME ZONE members.zone),
         ?) AS date,
         AVG(score) AS average_score
FROM "activity_scores"
INNER JOIN "members"
    ON "members"."id" = "activity_scores"."member_id"
WHERE "activity_scores"."member_id" = $1
GROUP BY  date

An example result:

    date    |     average_score
------------+------------------------
 2016/10/15 | 52.00000000000000000000
 2016/10/29 | 60.25000000000000000000
 2016/09/05 | 70.05000000000000000000

These values are then used for graphing.

Unfortunately, the above query takes a long time (sometimes up to a few minutes), and causes the entire server (hosted on Heroku) to timeout.

There isn't really a need for us to have the data saved in 10 minute increments for months. Therefore, I've thought about making a separate table to just store the daily averages which we can poll directly from for graphing purposes. However this is somewhat of a duplicated data (which allegedly is a no-no in the DBM sense).

Would you guys have any suggestions on how to handle something like this?

Thank you!


texts: Good ratio for data, journal, and log drive sizes for MongoDB?

texts:
Is there a good ratio for drive sizes for MongoDB?

For example if you have a 8Gig drive for the data, then how big should my drive for the log be? How about the drive for the journal?

Just a little more background I'm following this tutorial, which states:

According to this MongoDB tutorial which explains how to manually deploy MongoDB on EC2, one of the steps states that you should have:

"Individual PIOPS EBS volumes for data (1000 IOPS), journal (250 IOPS), and log (100 IOPS)."


texts: Increasing system load due to multiple inserts every millisecond

texts:
In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows.

I am doing inserts from my Java project using JPA. I also have a trigger on that table to insert data in its monthly partitions.

What is possible solution in this case?


texts: Wrong data type while using linked tables in MS Access

texts:
I have a SQL server database which needs to be linked to MS Access forms.

I have a table with one column as both Identity and is a also a primary key and is big int

But after linking the table , if i open the design window of table in MS access , it is converted as text .

Why it is converting the Identity column to Text while linking.


texts: Best Practices for Creating and Deleting Records in MYSQL

texts:
I'm fairly new to working with databases, but I've currently have a webCrawler that I've written in C# using MYSQL database.   The crawler is frequently writing and deleting records from the database as sites are scraped.

Each record has primary key, which is MD5 checksum for the URL,  to insure that no table has two duplicate entries.

Currently is it good practice to do a check on table before inserting the database to see if there is a duplicate.   I.e. two operations on the database.

Or is it sufficient to add the record and let the database gracefully fail to add it.

The same question is relevant for other operations like deleting etc.

At the moment I'm trying to operate on 1000's of records a minute from a single client (multiple connections from that client).  Does the answer change knowing that level of database activity.

Also it fairly frequently that there will be duplicate. and the add code will be skipped.  Say every ten adds, there is one new record.


texts:
I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table.

Create table visitors_today(
today int unsigned not null default 0,
vcount bigint unsigned not null default 0
)
engine = Innodb;

create table visitors_stored(
v_count bigint unsigned not null default 0,
vcount_date DATE not null primary key
)
engine=MyIsam;

My questions:

Do I need to set up something?
A cache table is it just a regular table, the difference is the data will be delete after update the visitors_stored?, or just because the storage engine it will be faster?


texts: Is there a way, in a single SQL statement to ensure that all items in a list are present in some column?

texts:
What I'm looking for is a way of verifying that all of a list of items (let's say 'FOO', 'BAR' and 'BAZ') are all in a given table.column.  This would be something like SELECT something FROM sometable WHERE somevalue IN ('THIS', 'THAT', 'OTHER THING') ... except it's sort of the inverse:

Ideally the statement would give me everything from ('FOO', 'BAR', 'BAZ') NOT IN sometable.somecolumn then I could use that as my check (assert that my SQL query returns an empty set or take the resulting set and complain that each of these is an "unrecognized" key).  (A brute force approach would be to insert all of the terms/tags/items into a temporary, single column table and perform a JOIN ON my actual table's column for the set of rows which are NOT IN it.

I'm sure I'm missing some syntactic trick that'll seem obvious once I see an example.

In this particular case my table structures are something like:

CREATE TABLE items (id     INTEGER PRIMARY KEY, item TEXT UNIQUE NOT NULL);

CREATE TABLE tags  (tag_id INTEGER PRIMARY KEY, tag  TEXT UNIQUE NOT NULL);

CREATE TABLE item_tag (item_id integer, tag_id integer,
                               FOREIGN KEY(item_id) REFERENCES items(id),
                               FOREIGN KEY(tag_id) REFERENCES tags(tag_id),
                               PRIMARY KEY (item_id, tag_id));

... but I'm simply trying to come up with a template for taking an arbitrary number of "tags" and ensure that all of them are valid entries the "tags.tag" column.  (The application will have to support arbitrarily complex queries for subsets of these tags to return DISTINCT subsets of the items; but I want to raise an error on any non-existent tag before building the JOIN's WHERE expression).

Obviously I could just loop over the tags doing a separate SELECT tag_id FROM tags where tag=? ... entailing numerous round trips to the database.   But this seems silly when I could send all of them to some sort of query in a single statement.


texts: Join multiple PostgreSQL tables that have both common and individual columns

texts:
Processing speed matters not for my needs. What I want to do is meant to be done once and then stay that way. It should be an automated process though, which can be performed by sending SQL commands to the database with psql.exe.

General problem

I have several tables that I want to merge into a single table. Assume that I can't know the names of all the columns (there are A LOT of them). What I do know is that some columns are the same for every table while other columns are individual for each table. If necessary I can specify the columns that are common to all tables, but I can't specify the columns which are common only to a few tables.

You may assume that I know the names of all the tables that I want to merge. However, a solution that doesn't assume that would be superior.

If you can give me an answer based on this then I may be able to proceed to solving the specifics of my problem on my own. However, I'm adding the specifics in case you have knowledge that may help based on that.

Specifics

The tables that I want to merge into one were generated by ogr2ogr (GDAL 2.1.0) from data in the S-57 format. I've already imported the base .000 file and updated with the available .00x files. I'm currently testing with only one .000 file, but I intend to append the import with data from several files. This shouldn't really affect the solution unless I've missed some ogr2ogr option that automatically imports everything into a single table.

I asked a different but related question on the GIS stackexchange.

Problems I've faced

I can't use UNION because the tables don't have the same columns. I also tried FULL OUTER JOIN, but it doesn't work because some columns are defined in multiple tables.

Clarifications

I want to merge several tables into one.
These tables have some columns in common. I can name these if needed.
These tables have some columns that are not in common. There's too many of these for me to name.
The new table should have all the columns, but no duplicate columns.
I want all rows from all the original tables inserted into the new table.

A solution where all the tables to be merged are known is good. A solution for any number of tables with unknown names is better.

The specifics of my problem is that I'm working with the S-57 format and importing to the database using ogr2ogr. These specifics are not necessarily important for the solution, but I included it in the question because someone might know something that I've missed that could have solved this already in the import.

I've tried using UNION and FULL OUTER JOIN, but it did not work.


texts: Unable to create read-only user in PostgreSQL 9.5

texts:
I seem to be having trouble creating a read-only user for a database in a fresh install of PostgreSQL 9.5 with PostGIS 2.2 on Ubuntu 16.04. Here is what I've done so far:

Ran this,

CREATE ROLE dbowner LOGIN ENCRYPTED PASSWORD 'dbownerpassword';
CREATE DATABASE thedb WITH OWNER dbowner;
GRANT ALL PRIVILEGES ON DATABASE thedb TO dbowner;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

GRANT ALL ON ALL TABLES IN SCHEMA public to dbowner;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public to dbowner;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to dbowner;

CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'ropassword';
GRANT CONNECT ON DATABASE thedb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

Then I restarted,
    sudo service postgresql restart

Now,

psql -h localhost -U dbowner thedb
thedb=> CREATE TABLE testtable(col1 VARCHAR(100));
CREATE TABLE
thedb=> INSERT INTO testtable values('123');
INSERT 0 1
thedb=> SELECT * FROM testtable;
 col1
------
 123
(1 row)

psql -h localhost -U readonly thedb
thedb=> SELECT * FROM testtable;
ERROR:  permission denied for relation testtable

pg_hba.conf already has local all all md5 and host all all 127.0.0.1/32 md5. Anything else that I might be missing?


texts: postgresql: how do I dump and restore roles for a cluster?

texts:
Where are roles stored in a cluster, and how do I dump them?

I did a pg_dump of a db and then loaded it into a different cluster, but I get a lot of these errors:

psql:mydump.sql:3621: ERROR:  role "myrole" does not exist

So apparently the dump of my db does not include roles.  I tried dumping the 'postgres' db, but I don't see the roles there either.

Do I need to use pg_dumpall --roles-only ?

Postgresql versions 8.4.8 and 9.1.4
OS: Ubuntu 11.04 Natty


texts: Property Size is not available for database

texts:
I recently restored a database to the same instance it was backed up from (SQL Server 2008 R2 Enterprise) and found that I couldn't access the database properties.

I have done the following:

Checked the database owner was set correctly using sp_helpdb.
Changed the database owner to sa. Not a fix.
Changed the database owner back to my sysadmin user. Not a fix.
Issued DBCC updateusage against the affected database. Not a fix.
Run DBCC CheckDB on a restored copy to another instance. No corruption found. The restored copy (from the same backup file) did not throw any errors when accessing the database properties window.

Can anyone help?

The error message I get when trying to view properties is:

  Cannot show requested dialog. (SqlMgmt)
  Property Size is not available for Database '[DBNAME]'.
  This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

I am a sysadmin on this instance.

Update: As suggested I created a new user, made it sysadmin and changed the database owner to it. Not a fix unfortunately. I will see if a profiler trace yields anything useful.

Update: Aaron - the original database was renamed and taken offline but is still on that instance. The backup of that database was then restored using the original name. The filenames of the new database files are different from the original as they live in the same folder as the original mdf/ldf. The restored db is currently driving our critical apps as normal.


texts: SQL Server Reporting Services 2017 Cant connect and doesnt show in Configuration Manager

texts:
I have a few question about SSRS 2017 that I cant find answers to.

Can you no longer add it to the default instance?  It seems to force it to an instance called SSRS

After doing the configuration steps I am still not able to connect to SSRS through mangement studio, it says it cant find a sql server reporting instance.  If I go to browse it does see it under defaultinstance/SSRS but I cant connect.

I cannot see SSRS listed under the configuration manager.

I can connect through the Reporting services configuration manager and I can load the reports page.

Any help is greatly appreciated.


texts: Oracle query against SQL Server database giving inconsistent results

texts:
We have multiple Oracle 11.2.0.2 databases with og4odbc database links to a SQL Server 2008 database.  A particular table in the SQL Server database has two Varchar(36) columns.  The following statement returns 1292 rows:

select "id", "folder_id" from dbo.mediasite_presentation@ms;

Querying for a specific value in the first column returns a row:

select "id", "folder_id" from dbo.mediasite_presentation@ms
   where "id"='006d815d-6e9e-4004-9104-51213a1ecd52';
id                                   folder_id
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 54aa9b6e-1c55-4de5-a06f-033d8b19fff0

But querying for a specific value in the second column returns no rows:

select "id", "folder_id" from dbo.mediasite_presentation@ms
   where "folder_id"='54aa9b6e-1c55-4de5-a06f-033d8b19fff0';
no rows selected

Using the where clause that works and wrapping each field in a to_char returns the following interesting results:

select to_char("id"), to_char("folder_id") from dbo.mediasite_presentation@ms
   where "id"='006d815d-6e9e-4004-9104-51213a1ecd52';
TO_CHAR("ID")                        TO_CHAR("FOLDER_ID")
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 ┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐

Interestingly enough I can produce the same results just by adding a rownum clause:

select "id", "folder_id" from dbo.mediasite_presentation@ms
   where "id"='006d815d-6e9e-4004-9104-51213a1ecd52' and rownum<=1;

id                                   folder_id
------------------------------------ ------------------
006d815d-6e9e-4004-9104-51213a1ecd52 ┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐┐

Even stranger is the fact that creating a materialized view for query that returns no data actually returns data.

Create Materialized View TempMV as (
   select "id", "folder_id" from dbo.mediasite_presentation@ms
      where "folder_id"='54aa9b6e-1c55-4de5-a06f-033d8b19fff0' and rownum<=1);
Materialized view created.
select * from TempMV;

id                                   folder_id
------------------------------------ ------------------------------------
006d815d-6e9e-4004-9104-51213a1ecd52 54aa9b6e-1c55-4de5-a06f-033d8b19fff0

Please answer even if you can only explain a piece of this puzzle.


texts: How to restore a MySQL InnoDB with ONLY .ibd files?

texts:
I had a Bitnami Joomla! stack running on a computer that suddenly kicked the bucket. I had neglected to make a back-up before this happened so I do not have any dumps or backed up files, but was able to save the directory and everything in it when I recovered its hard drive. I was a fool and wiped the recovered drive after removing the files I wanted to keep, so I can't go back to get anything else....
I've been trying to recover the tables (76 in total) into a live MySQL installation to get access to them, create a back up and import them into a fresh stack to restore my site. I have saved every .ibd in the database's folder, along with ib_logfile0, ib_logfile1, ibdata1, mysql.ibd, binlog.index, etc. My installation never had a .frm file that so many posts mention having, and it never did even when it was live. I do not know the tables' structures in order to replace them one by one, and Joomla's documentation on them is apparently outdated.
My installation used MySQL 8.0.21, running under Windows 10. I would really appreciate help to solve this issue. I've looked for tutorials on how to fix my issues, but they were either for prior versions of MySQL (which I am unfamiliar with compatibility) or required a back-up or that .frm file (which I don't have). Additionally, I compared my saved directory with a fresh install of the same stack installer 3.9.22-0, and the base install did not have a .frm file from what I remember. I tried importing the raw .ibd files through phpMyAdmin and had no luck there either.
I can offer more details if necessary. Thank you for any help you can offer me!


texts: How to take advantage of AWS EC2 instance store (SSD) in a database environment?

texts:
I am trying to set up a database environment with Ubuntu and PostgreSQL (I didn't want to use RDS because I'm a Ubuntu person).

I looked at description for the R3 instance type:

  Use Cases

  High performance databases, data mining & analysis, in-memory
  databases, distributed web scale in-memory caches, applications
  performing real-time processing of unstructured big data, Hadoop/Spark
  clusters, and other enterprise applications. R3

  R3 instances are optimized for memory-intensive applications and offer
  lower price per GiB of RAM.

  Features:

High Frequency Intel Xeon E5-2670 v2 (Ivy Bridge) Processors
SSD Storage
Support for Enhanced Networking

So, I went for the SSD Storage, and thought I can somehow utilize it for fast database operations.

Now when I begin to launch a Ubuntu instance, e.g. :

  Ubuntu Server 16.04 LTS (HVM), SSD Volume Type - ami-da05a4a0

I realize that I cannot use the SSD storage for the root device hosting PostgreSQL and all the system software. Moreover, the instance store is said to be ephemeral and data are lost if the AWS EC2 instance is stopped. The only use case I was able to find to use the instance store as Linux swap space.

I'm very new to AWS EC2. I'd just like to ask how should the SSD instance store be utilized in a database server (other than as swap)?

So far, the use case I can think of (swap, mount as /tmp) seems to be a waste of the money spent on reserving the SSD storage.


texts: Is it possible to configure Oracle so that AWR reports contain more that the top ten SQL queries by elapsed time and also include the explain plan for those queries?


texts: Update row if it has a certain set of values, add new row if it returns to initial value

texts:
I'm having some trouble importing data into a table that I'm not sure how to describe succintly enough that can I can properly search for it (not that I haven't tried).

I have a table with three columns (actually more like 150, but these are the ones that matter):

order_number, date, status

that I update with some CSV data I don't control.

Status can have 5 states: 'pending', 'started', 'cancelled', 'not done' and 'complete'.

Each order will start 'pending', then progress to 'started', and finally end at 'cancelled', 'not done' or 'complete'.

If they end at 'cancelled' or 'complete', there's no problem, I just keep updating the status for the day and that's it.

The problem is when the status ends at 'not done'.
Usually when that happens, there's a new attempt only the next day, so I can just use the date to differentiate between attempts, but frequently enough to be a problem, a new attempt is made on the same day, and I need to keep both the first attempt, and the new one (that can still fail).

There's no field meant specifically for differentiating the attempts, nor changes to the order number, the time fields I could possibly try to use are populated with estimated times until the attempt is finished, and the only reason I know there was more than one attempt is because I get two rows for the same order on the same day.

Currently I'm loading the data into a temporary table, then using "INSERT...ON DUPLICATE KEY UPDATE" with a unique key on order_number and date to load it into its proper place, but that's causing me some loss of data.

I've tried adding a calculated field with value based on the final status to the index, but then I got at least two rows for each order: one for the final status, and one for 'started'.

I'm looking for some ideas for how I can solve it without processing the data outside the database before insert, since it would take awhile on the limited hardware available at the moment.


texts: Can RMAN duplicate be used to "replace" an existing database?

texts:
I have an unused database B, not valuable.  I'd like to make a copy of database A, but do not want to go through all the manual steps RMAN requires to set up the auxiliary database.  Can I use RMAN duplicate to copy database B over database A, replacing everything in it but reusing the instance?  I know you can synchronize database copies this way, I'm hoping this is just a more drastic version of that procedure.

Thanks!


texts: Suddenly a massive amount of binlog files


texts: SQL Server Basic Availability group: one or zero listener for multiple AG

texts:
In my testing of SQL Server 2019 Basic Availiiblity Group on Ubuntu 20.04, I successfully created AG1 for DB1 and AG2 for DB2 with only AG1 config'ed with listener IP. But when I use sqlcmd to test, I can connect to AG1's listener and use AG2's DB, do DDL and DML. Is it OK for this configuration? Only 1 listener for several AG.
Besides, I'm using pacemaker for cluster management, is it possible to config no listener for AG, just use pacemaker's IP resource to bind IP on master server?


texts: Is there a way to set an order to the way files are available in Azure? I want the latest backup first

texts:
I use Azure to store backups, the backups are saved in Azure.

this is how I am doing my backups and saving to azure:

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master
-Q "EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'SQLAG',
@URL = 'https://MYCOMPANYbackups.blob.core.windows.net/SQLMAINSERVER-datafiles-full',
@Credential = 'SQLAzureCredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@LogToTable = 'Y'" -b

This is working fine.

There are some points I would like\need to improve though. When I need to restore a backup from Azure the backups are stored in the order they have been saved, and generally I need the most recent backup.

For example the picture below (apologies I have used this thick green to hide sensitive information like server, accounts and database names, it got really ugly but just to have an idea):

these are differential  backups:

As I often need the latest backup of an specific database(s) is there a way to order how the files are stored in azure so that the first one available is the latest saved?

conclusion

After downloading and installing Microsoft Azure Storage Exporer
I can order the backup files as you can see on the picture below.


texts:
I have a table in SQL Server 2012 Express with a lot of unused space.

I need to free up space in the database.

|        NAME |   ROWS |     RESERVED |         DATA | INDEX_SIZE |       UNUSED |
|-------------|--------|--------------|--------------|------------|--------------|
| MyTableName | 158890 |  8928296 KB  |  5760944 KB  |   2248 KB  |  3165104 KB  |

How do I get SQL to release the 3165104KB?

I've already tried:

Alter table MyTableName Rebuild
DBCC CLEANTABLE (MyDbName,"MyTableName ", 0)
ALTER INDEX ALL ON MyTableName REORGANIZE ;
ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF)

Here is the table:

CREATE TABLE [dbo].[MyTableName](
    [ImageID] [int] IDENTITY(1,1) NOT NULL,
    [DateScan] [datetime] NULL,
    [ScanImage] [image] NULL,
 CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED
(
    [ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The only thing we have done is replaced ScanImage on every row with a much smaller image (this is how so much unused space is there).


texts: Mysql enable remote connection in centos,my.cnf does not have bind-address to comment out

texts:
I'm trying to make my MySQL Server running on CentOS to allow remote database connections. I'm reading this article that says I should comment out bind-address but bind address is not available in the my.conf, this line is available at /etc/my.cnf.

How should I enable remote connections now that bind-address is missing?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
event_scheduler=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
event_scheduler=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


texts:
I am not sure if this is on topic here.

When selecting VARCHAR2 columns that contain values with leading blanks this leading blanks will not be displayed in the output of sqlfiddle. so

select '     x' from dual

gives some html equivalent to

+-------------+
|"X"          |
+-------------+
|x            |
+-------------+

(without preceding blanks).

Here is the ouput in sqlfiddle.

A workaround is

SELECT translate('     x',' ',chr(128)) FROM dual
/

copy and paste the output to a spreadsheet
and then substitute this character by a blank.

Is there a better way get tproper output?


texts: Mysqldump from production database, every morning

texts:
Our Db is about 2GB in size.
We run a scheduled job at 3am every morning, when traffic is pretty low, where we export the whole db and sftp it off site.

Is it bad practice to do this on a production db?
The slow query log always grows with 10 or so queries when doing this. Open/opened tables also grows a bit.

So, is this not a good backup strategy? What's a good/standard alternative to this? Is it even bad for the db somehow?

Thanks.

EDIT

We're on a Linode 4096 where the DB is the only thing run on that server.
It has SSD's but not world class. We're using 2GB of the 4GB memory for the innodb_buffer_pool.
The command is as follows:

mysqldump --opt --u USER -pPASSWORD DATABASE | gzip > /path/to/database.sql.gz


texts: SQL server 2012 reports "Missing Index" on column which is covered by "Clustered Index"

texts:
My table creation script is as follows

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[quotation_section](
    [quotation_id] [varchar](24) NOT NULL,
    [section_id] [varchar](24) NOT NULL,
    [name] [nvarchar](100) NOT NULL,
    [description] [nvarchar](1000) NULL,
    [sort_order] [tinyint] NULL,
    [copied_from_library_flag] [bit] NULL,
    [max_sequence] [tinyint] NULL,
    [collaborator_id] [varchar](24) NULL,
    [status] [tinyint] NULL,
    [mandatory_flag] [bit] NULL,
    [read_only_flag] [bit] NULL,
    [draft_flag] [bit] NULL,
    [has_comment_flag] [bit] NULL,
    [revision_time] [datetime] NULL,
    [report_revision_time] [datetime] NULL,
    [last_submitted_max_sequence] [tinyint] NOT NULL,
    [buyer_code] [varchar](4) NULL,
 CONSTRAINT [PK_quotation_SECTION_01] PRIMARY KEY CLUSTERED
(
    [quotation_id] ASC,
    [section_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

In one of my database queries the execution planner is reporting the following:

                <MissingIndexGroup Impact="25.8056">
                  <MissingIndex Database="[myschema]" Schema="[dbo]" Table="[quotation_section]">
                    <ColumnGroup Usage="EQUALITY">
                      <Column Name="[section_id]" ColumnId="2" />
                    </ColumnGroup>
                  </MissingIndex>
                </MissingIndexGroup>
              </MissingIndexes>

I was under the impression that Clustered indexes are used by query optimiser even when non-clustered index seek is performed (unless I have messed up something here, sorry in that case). I do admit that other tables I am using for queries do have the columns present in both clustered and non-clustered indexes. So what should the approach be here? Does adding the missing index (as reported by SQL server execution planner) is a good idea?


texts: How to select rows that date is in range from ranges table

texts:
I have a table1 that hold ranges

rangeId, rangeName, fromDate, toDate,cat

another table2 has rows

rowTitle,rowDate

I need to

select  all rows
from table2
that table2.rowDate is between table1.fromDate
                           and table1.toDate
and cat = 1


texts: Why is there automatic failover for HA and manual failover for DR?

texts:
I am reading this summary from the book 'Pro SQL Server 2019 Administration' from Carter:

And it specifies:
hot → automatic failover → used for high availability (HA)
warm → manual failover → used for disaster recovery (DR)
Since 'high availability' is usually planned and 'disaster recovery' is not, shouldn't we aim for manual failover in 'high availability' scenarios?
What's the point of having automatic failover for high availability?
It makes sense to me that if DR occurs, failover should be automatic, and if we have something planned (patching, etc.) and we have HA, we can do the failover manually...


texts:
I have written code in Visual Studio.NET and used data from SQL Server 2008. After debugging, it says:

Login failed for user 'sunny'.

When I try to set the user as something else, it keeps on reporting that error. I start sql with windows authentication and the username there is: sunny-lptp\sunny.

I do not have any sql authentication user/pass on sqlserver express 2008.

I tried changing the authentication in server/security tab. I changed it to both windows and sql. but it didn't fix the error.

What should I do?

Here is the connection string in web.config:

<configuration>
    <appSettings>
        <add
            key="SchoolsDBConnectionString"
            value="server=sunny-lptp\sqlexpress;database=SchoolsDB;">
        </add>
    </appSettings>

And the code:

scn = new sqlConnection(
    ConfigurationManager.AppSettings["SchoolsDBConnectionString"].ToString());


texts: Why is ACCESS EXCLUSIVE LOCK necessary in PostgreSQL?

texts:
I know PostgreSQL provides multiple lock modes for concurrency control. When I read through their documents, I cannot see why the ACCESS EXCLUSIVE lock is necessary in any case. The only difference between an EXCLUSIVE lock and ACCESS EXCLUSIVE lock seems to be that ACCESS EXCLUSIVE lock disallows reads from the table.

But isn't it a good thing that people are able to read? In what case should you prevent users from reading? In the documentation I see that usually DROP TABLE, etc. will use ACCESS EXCLUSIVE lock, but why? I was thinking that maybe when you drop a table the data is already partially gone when you are trying to read so the result will be corrupted. But then I realize since everything in Postgres is transactional, a DROP TABLE should not take effect until commit. So my guess should be wrong.

I am still confused. Why is ACCESS EXCLUSIVE lock needed again when you compared it to EXCLUSIVE lock? My best guess is that replacing ACCESS EXCLUSIVE lock with EXCLUSIVE lock is not going to break PostgreSQL. It is just sometimes people do not want others to read. Did I miss something?


texts:
This may be a simple a question but I just wanted to clarify, at work I have a table containing a list of titles and I also have a list of authors in two different tables.

What would be best way to connect them? I have a connector table with the IDs of the Authors and ID of the titles so you can left join them, is there a better way to do this? i looked at having an extra column in the title table which would allow the author ID but this would be problematic for multiple titles to a single author.

Does anyone have any suggestions of a better way of doing this ( instead of a connector table)?

any help would be greatly appreciated.

Thanks

James


texts:
I'm using SQL Server 2016.

I have the below table:

SKU     Shop       Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         NULL
111     200         1            2           2           NULL       NULL
111     300         1            3           0           NULL       NULL
111     400         1            4           0           NULL       NULL

222     100         2            1           5          17          NULL
222     200         2            2           5          NULL        NULL
222     300         2            3           5          NULL        NULL
222     400         2            4           5          NULL        NULL

This is the desired result:

SKU     Shop      Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         17
111     200         1            2           2          NULL        17
111     300         1            3           0          NULL        15
111     400         1            4           0          NULL        15

222     100         2            1           20           17        17
222     200         2            2           15          NULL       12
222     300         2            3           12          NULL        7
222     400         2            4           10          NULL        2

I need to update the 'Open_Stk Calc' based on the previous row:

'Open_Stk Calc' - IIF('Replen'<=IIF('Open_Stk'>=0,'Open_Stk',0),'Replen',0)

I am using a CTE to update a row based on a calculation of the previous rows.  This is my SQL:

    ;WITH CTE AS
    (
        SELECT
        SKU,
        [Shop],
        [Week],
        [Store_Priority],
        [Replen],
        [Open_Stk],
        [Open_Stk Calc],
        FIRST_VALUE([Open_Stk]) OVER ( PARTITION BY [SKU] ,[Week] ORDER BY [Store_Priority] ROWS UNBOUNDED PRECEDING)
        -
ISNULL(SUM(IIF([Replen] <= IIF([Open_Stk]>=0,[Open_Stk],0),[Replen],0))
        OVER (PARTITION BY [SKU] ,[Week] ORDER BY [Store_Priority] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS CurrentStock
    FROM [tblTEST])

    UPDATE CTE
    SET [Open_Stk Calc] = CurrentStock

However, this produces the following result:

SKU     Shop       Week    ShopPrioirty    Replen      Open_Stk  Open_Stk Calc
111     100         1            1           0           17         17
111     200         1            2           2           NULL       17
111     300         1            3           0           NULL       17
111     400         1            4           0           NULL       17

And not the desired result - where have I gone wrong?