texts: MySQL Commited data not seen to select query

texts:
Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level repeatable-read is set.

Problem: An Insert happens inside a transaction and a select which reads the same data inserted does not see the data. The select runs after the insert and after the insert transaction has commited.

I've enabled bin log as well as general log in mysql. Relevant logs below

bin-log:

SET TIMESTAMP=1438265764/*!*/;
BEGIN
/*!*/;
# at 249935389
#150730 14:16:04 server id 1  end_log_pos 249935606 CRC32 0xa6aca292    Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1438265764/*!*/;
insert into user_geo_loc_latest(user_id, lat, lng) values(x,y,z) on duplicate key update lat=y, lng=z
/*!*/;
# at 249935606
#150730 14:16:06 server id 1  end_log_pos 249936255 CRC32 0x2a52c734    Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1438265766/*!*/;
INSERT INTO table(txnid) VALUES ('885851438265675046')
/*!*/;
# at 249936255
#150730 14:16:06 server id 1  end_log_pos 249936514 CRC32 0x6cd85eb5    Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1438265766/*!*/;
INSERT INTO table2(x) VALUES (y)
/*!*/;
# at 249936514
#150730 14:16:06 server id 1  end_log_pos 249936545 CRC32 0xceb9ec56    Xid = 9406873
COMMIT/*!*/;

Query Log

150730 14:16:04    40 Query ...
....
40 Query     select count(*) from table where txnid = '885851438265675046'
                   40 Query     select @@session.tx_read_only
                   40 Query     INSERT INTO table(txnid) VALUES ('885851438265675046')
                   40 Query     select @@session.tx_read_only
                   40 Query     INSERT INTO table2(x) values(y)
                   40 Query     commit
....
150730 14:16:07    36 Query     select pp.*, b.create_date from table pp left join bill b on pp.bill_id = b.bill_id where pp.txnid = '885851438265675046'

Curiously, First insert (249935389) should not be part of the transaction at all. It is a separate API call and completely unrelated. It could be spring mixing it with the transaction or I am reading the log wrong? AFAIK since it's on the same thread it implies the insert is in the transaction.

The next two inserts are part of the transaction and it looks like it commits. (249936514). Now the select query (last one in general log) runs after the commit and it does not see the data. It returns 0 rows. How can this happen considering the data is committed? Or is the commit not on the thread 40? Since it does not have the thread id.

To summarize I have two questions.

Does the BEGIN in the binlog being before the INSERT INTO user_geo_loc (which is not part of the transaction), is this a bug with spring/Jdbc or MySql simply does this as it knows this transaction has already committed (as transactions are written to binlog when they've succeeded) and therefore would never be rolled back.
Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction?

This is extremely perplexing. Any help would be appreciated

Note: The queries in both the bin and query log have been edited to remove sensitive information. But the essence of the queries remain the same

Edit: Updated with general log and query log with a detailed example.


texts:
As a DBA trainee, what needs to be known in order to be part of datawarehousing projects?

I mean what resources are suggested to have an overview knowledge of how PL/SQL and other concepts are used for Datawarehouse projects?


texts: Configuring MySQL (my.cnf) for MyISAM and InnoDB

texts:
I want to convert some (not all) the MyISAM tables to InnoDB.

Current my.cnf
Show engines output
Server has two 6-core Xeon processors (2 threads per core = 24 threads)

What would be the best configuration? (need to add InnoDB settings, because there are no InnoDB settings in my my.cnf).


texts: How can I determine the edition an Oracle session is using?

texts: We are using the Edition Based Redefinition feature in some software and as a DBA, I want to be able to determine which edition each given connection is using.  This way, we can be sure all connections to the database are using the correct edition.  How can I tell the edition being used by any given session?


texts: How does InnoDB store tables in ibdata file?

texts:
There are two tables and their default engine is InnoDB.

I want know the following

What's inside the ibdata file ?
How does InnoDB store these two tables ?


texts: Migrating a Replicated Database from one SQLServer 2012 instance to another

texts:
We are working on a migration plan from old hardware to new hardware, these are SQL 2012 servers.
A couple of the databases heavily use transaction replication so I'm trying to avoid recreating and re-initializing them if possible.

Can I just restore the database on the new hardware with "Keep_Replication" and everything will be good or would restoring other databases such as MSDB/Master be required as well?

I'm not sure where all the metadata for replication is contained.

Also, any additional steps besides "Keep_CDC" to keep CDC tables active on the new hardware?


texts:
Currently, I am using the SQL Server 2012 Express version and it is installed on Windows Server 2012 R2. Now I just want to connect to this database from a Windows 7 client. The Windows 7 computer already has its own SQL Server 2012 Express.

When I browse the database from the client, I only see client database's instance name only. There is no server's instance.

I already enabled the Named Pipes and TCP/IP on the server and I am new in that kind of business. So please let me know how can I connect to the SQL Server on the server from the client.


texts: Running pt-table-checksum on a table in different databases

texts:
I'm using filters for replication:

replicate-wild-do-table = test_slave.%
replicate-rewrite-db = test->test_slave

Is there an option to specify different DB name on slave for "pt-table-checksum"

pt-table-checksum --host=localhost --databases=test --max-load=Threads_connected:25 --no-check-replication-filters

EDIT 1:

mysql> SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test_slave';
+----------+
| COUNT(*) |
+----------+
|      145 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM percona.checksums;
+----------+
| COUNT(*) |
+----------+
|       40 |
+----------+
1 row in set (0.00 sec)

EDIT 2:

# ls -l *.frm | wc -l
145
# ls -l | awk '{print $3,$4}' | uniq

mysql mysql

It's also complaining that two tables have no indexes and are oversized. It looks like it's just skipping some tables?


texts: How to create a table with a field 99% INT; 1% STR values?

texts:
I am creating a table in PostgreSQL and looking for a best approach that I should follow.

One of my fields are mostly consists of integer values. But really mostly.

Like: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 and over

Last one, 20 and over, kills everything. If it wouldn't be there I am going to create that table with integer field.

Should I create the field in VARCHAR(11) type, just because of the 1% string field? Or is there any other method to solve this issue smoothly?


texts: Oracle table transfer between databases in two different hosts

texts:
I have a 500Gb table which I need to transfer to another Oracle database in another remote host. I have a 10 Megabits/sec speed connection between two hosts. What can be the most efficient way to transfer the table? With minimal chance of disaster.

There shouldn't be any data loss even with fluctuating connection. The method must be fastest among all.

I want a solution without using external drives.


texts: Permissions to be granted to developers to investigate jobs, queries, and packages

texts:
I have just installed a new sql server 2014 server, migrated some databases there and connected some applications to them.

These servers have also SSAS and SSIS.

I have a group of developers there were helping with the connections and doing all the last developments on packages, and data warehousing stuff.

Now that everything is working, I want to remove the sysadmin permissions that I had previously granted them.

Now, these guys they have some requirements,

that I am willing to grant them, so that it is less weight on my shoulders:

  1) They need to see query plans, 2) they need to identify missing
  indexes and other expensive operations like key lookups, etc

  3) they need to query sys views and tables - monitor what procedures
  are running, collect WAIT STATS, etc

  4) they also need to troubleshoot JOBS - so they need to be able to
  see them, see the job history, in order to find out what went wrong

  5) they also need to see everything in the SSIS integration catalog,
  so that when something goes wrong inside one of there packages they
  are able to figure it out by themselves

I have granted them with the following SERVER permissions:

  1) view server state

  2) view any definition

on the MSDB database:

I got some ideas from "SQL Server Agent Fixed Database Roles"

and finally on the SSISDB database

Anything else that I should be aware of?


texts: If a relationship is (logically) deleted, is this marked in the link table or just the satellite table(s)?

texts:
I'm learning about the Data Vault data warehousing approach, and so far the examples I've seen have a DateLoaded or similar field in all tables (hub, link, and satellite), but a DateExpired or EndDate field only in satellite tables.  If a relationship is deleted in a source system, should this be recorded only by setting the DateExpired field in the link's satellite table(s), or should one also maintain a DateDeleted field in the link table?

Note that I'm asking about "better" from a "Data Vault best practices" perspective.  For a specific application there may be good reasons to favor one approach or the other.


texts: How and where do I upload a mysql database?

texts:
I am new to Ubuntu (server edition) and mysql.

How do I upload (import) an existing database with a MYSQL or (mysql.zip) extension that was downloaded from a web host (cPanel?)

I do not have any of the UI bells and whistles such as zPanel (although I tried to install it.) I did install phpMyAdmin with Ubunto but I do not know how to access it from a remote computer )In the past, I only used it with cPanel.)

My site will reside in /var/www.

I have installed mysql server Dist. 5.5.32.  Thanks. :)


texts: Best way to backup/restore a very large DB (~2TB)

texts:
The ibdata1 file has increased to ~2TB and leaves only a few GBs free space in the same disk (this is because I forgot to turn on innodb_file_per_table, before it was too late)

From what I have read in What is the best way to reduce the size of ibdata in mysql? the only way to reduce ibdata1 is to:

  backup -> delete ibdata1 -> restore database.

Now, since the ibdata1 is so large, what's the best way to do it?
And how much time will it take?

I have two other free disks of 2TB size available which can be used for backup.

My thought is as follows:

mysqldump current database into disk-x
restore from disk-x to disk-y
delete original ibdata1 in disk-z

Is this good way and how long will it take?


texts: How to display multiple row records of similar ID's in single row?

texts:
I have two tables user and work. Work table has multiple user's work experience.I have to retrieve most recent 2 companies on the basis of start date.

Conditions :

1.If Work Table has multiple user's Id's records, retrieve recent 2 records and show in single row (e.g. User_id = 1)

2.If Work Table has single record is most recent companies record and second record should be "NULL"(e.g. user_id = 3).

user
user_id  First_name  Last_name
  1        AAA         BBB
  2        PPP         QQQ
  3        SSS         RRR

work
user_id recent_company position start_year end_year
    1        ABC          CCC     2014       2015
    1        PQR          DDD     2013       2014
    1        MNO          EEE     2012       2013
    1        MNO          EEE     0000       0000
    2        XYZ          TTT     2008       2009
    2        IJK          MMM     2005       2008
    3        QRS          ZZZ     2001       2002

I've tried for most recent company record 1 :

select u.user_id,u.first_name,u.last_name,uw.recent_company1,uw.position1,uw.start_year,uw.end_year from muser u
left join
(SELECT user_id,recent_company,position,MAX(start_year) as start_year,end_year
FROM work
group by user_id
order by user_id) uw ON u.user_id =uw.user_id

Result of my query for 1st recent company on start year basis:

user_id  First_name  Last_name     recent_company1
  1        AAA         BBB            ABC
  2        PPP         QQQ            XYZ
  3        SSS         RRR            QRS

position1    start_year1    end_year1
   CCC         2014           2015
   TTT         2008           2009
   ZZZ         2001           2002

Required Output for both 1st and 2nd recent company on start_year basis:

user_id  First_name  Last_name   recent_company1   position1
  1        AAA         BBB          ABC             CCC
  2        PPP         QQQ          XYZ             TTT
  3        SSS         RRR          QRS             ZZZ

start_year1    end_year1
    2014         2015
    2008         2009
    2001         2002

   recent_company2   position2   start_year2    end_year2
      PQR               DDD         2013          2014
      IJK               MMM         2005          2008
      NULL              NULL        NULL          NULL


texts: How to insert values in junction table for many to many relationships (MySQL)?

texts:
I'm new to relational database design and I've hit a stumbling block.

I have three tables...

PROJECTS
project_id, name, ...

PROJECTS_UNDERGROUND
projects_FK, underground_FK

(I have both FKs setup as a composite key to eleminate duplicate data)

UNDERGROUND
underground_id, underground_name

—
Both PROJECTS and UNDERGROUND have a columns and rows of data already populated, such as id's and names but at present, there is no connection with the Junction table, they're currently sitting unconnected.
I'm puzzled to the queries I have to write to link the projects with underground station names?
The concept of composite keys make sense, but I can't seem to fiqure out the queries to update the PROJECTS_UNDERGROUND table.
Thanks for your help, everyone.


texts: How often should MySQL (stock, Percona, etc.) be upgraded?

texts:
My organization runs several data-driven web applications backed by various flavors of MySQL on RHEL (i.e. we're just like everyone else!)  Some of our web apps use the standard build of MySQL, and we use yum update periodically to update all yum-managed software on those systems.  Other webapps on other hosts use the Percona build, which we installed manually.  We use yum update periodically on those systems, too, but as our installation of Percona on those builds is not managed by yum, it isn't upgraded by that process.

Is there a rule of thumb for when to upgrade a component like MySQL (e.g. monthly, quarterly, or after each release)?  We want to find the right balance between staying up-to-date and spending too much time on administration.  What factors affect the decision?  Regarding security, we use hardware firewalls and iptables to restrict access, so MySQL/Percona is not exposed to the outermost attack surface.  The systems in question are dedicated, single-tenant hosts (i.e. there are no other users on the host).


texts: Reporting Services - Host header and Windows Authentication

texts:
SQL 2016 - Enterprise Edition
Windows 2016
Reporting Services runs on domain account (SPN registered for new host header)

Problem:
When adding a host header in Reporting Services Configuration tool, like reports.server.net,  users need to login to the reporting services.
When I remove the host header and try to connect to hostname/reports I do not get prompted for user/password.

Fact:
 - When i use the default site address http://servername/Reports, before adding a new host header, the site opens up without login prompt

What I've done:

Created a Host record in DNS for the new address, pointing at reporting services server
Registered a SPN for the new host record to the service account for reporting service
Added the new host header to reporting services for web service url and web portal url
Played around authentication types in the reportserver.config file according to (https://docs.microsoft.com/en-us/sql/reporting-services/security/configure-windows-authentication-on-the-report-server?view=sql-server-ver15&viewFallbackFrom=sql-server-ver13)
Enabled delegation for the reporting services service account "just in case"

I can't seem to add a host header without beening forced to login, despite above.

Anyone been here?


texts: Should I write to MySQL slave (replica) for reporting?

texts:
I intent to put reporting/analytics database on slave and run job scheduler every night to aggregate and insert data from operational database to reporting database.

Should I do this or I have to setup dedicated server for reporting/analytics database and use some tools to aggregate and insert data from slave to reporting server?

Thank for help,


texts:
I work with language with special characters (example: e -> é). I have a query with a LIKE but when I search a word with special char and my query doesn't have the special char, I don't get the results I expect:

select * from a where name like '%e%'

The above returns no results when the database contains "dés". Is there a way to have this query return the desired results.


texts:
I am trying to find records where the post code starts with a certain set of characters from some 10,000 records, 1300 or so of which meet the select below.
My code is

Select * from table where mid(postcode,1,2 ) IN ("CV DE LE MK NN")

This returns a zero result
A typical postcode would be something like CV1 9AD

If I write

Select * from table where mid(postcode,1,2 ) = "CV"

I get the full list of records for post codes beginning with "CV"
I would have thought that the IN statement would work. Can anyone tell me why it does not?


texts:
I have following tables

Tags

id | tag_name | slug |
1 | tag1 |tag1
2 | tag1 |tag1

products

id | proudct_name
1|product1
2|product2

product_tags

id | product_id | tag_id
1|1||1
2|1|2
3|2|1

i need retrieve only those product which belongs to both tag1 and tag2

  select * from `products` INNER JOIN product_tags ON products.id=product_tags.product_id

INNER JOIN tags ON tags.id=product_tags.tag_id WHERE product_tags.tag_id=1 AND product_tags.tag_id=2

But my query return empty result

js fiddle

http://sqlfiddle.com/#!9/34f43a/1/0


texts: How to Search values Integer column alias in a WHERE Clause

texts:
How to Filter values in where clause  alias variable,

CREATE TABLE BASE_Chq
(
ChequeInvoicePaymentId BIGINT NULL,
ChequeNumber VARCHAR(45) NOT NULL,
Amount MONEY NOT NULL
)

Once i select the whole table value i'm getting the result like below,

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
BASE_Chq

i want to filter Cheque Type wise but i'm getting error,

ERROR CODE:

SELECT IIf(ChequeInvoicePaymentId IS NULL, 'OWN', 'PARTY') AS ChequeType, ChequeNumber, Amount
FROM BASE_Chq
WHERE (ChequeType = 'PARTY')

How to search using alias  column in where clause?


texts: What's the term for a schema that has field+value column rather than individual columns?

texts:
I sometimes see a schema pattern where instead of:

|id|mystring|myinteger|mysecondstring|
| 1|hello   |        1|world!        |

the table looks like:

|field         |value |
|mystring      |hello |
|myinteger     |     1|
|mysecondstring|world!|

i.e. the columns are not homogenous, and essentially not typed (everything must be a string, so that string values are possible).

Does this style of schema have a name?

(I would like to convince people to not use this, and it would help if I can find articles about it, but that's very difficult without knowing what it's called).


texts: How to properly stop MySQL server on Mac OS X?

texts:
I installed MySQL Community Server following instructions at

http://dev.mysql.com/doc/refman/5.0/en/macosx-installation.html

with the Startup Item and the MySQL Preference Pane.

I can stop the server with

$ /usr/local/mysql/bin/mysqladmin -u root -p shutdown
Enter password:
$ ps aux | grep mysqld
.... grep mysqld

Is this the proper way?

Every time when I try to stop the sever using System Preference -> MySQL -> Stop MySQL Server, it hangs.  I will need to force quit the System Preference from the dock.  Any idea why the hang or how to find out?


texts: I dont know the best way to fix that, but for lucky now I am using one Transactional Replication Repository server, please someone can save me? The main server is down beacuse I set the max memory to 256mb by mistake because before this the server was using 40gb ram beware the server have 96gb ram.


texts: What should I use? A string or 15 integer fields?

texts:
I am developing a student tracking program where I need to store 15 exam marks.

I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical operations. However, I need as much performance as possible.

Which is better?  A single string field, or 15 individual int fields?


texts: SQL Server 2008 R2 Setup Client Tools Connectivity

texts: During SQL Server 2008 R2 setup I see a checkbox for "Client Tools Connectivity" and "Client Tools Backward Compatibility" installation options. It says these are component for client server communication. Can anyone explains what are these components? What is being actually install on the server when you choose these components.


texts: Always On Availability Group manual failover and OS upgrade considerations

texts:
New to AG - have inherited my first existing system. Have spent quite some time reading MS docs and watching a few training videos but a) concepts seem blurred (terminology similar between different technologies) and b) all this reading raises more questions than answers.

My question is essentially how to proceed with taking over and managing this AG, and how to prepare for, and carry out, some failovers in order to upgrade the OS on the servers?

There are 3 replicas - SQL01, SQL02, SQl04. SQL01 is currently primary.

Firstly - is there a good script to give me an overview of AG configuration and health? I understand the console in SSMS gives me an overview, but is there no script that can automate the task and be used to detect or report on servers lagging behind, etc?

Secondly, how can I check whether any secondary has been set up for read-only routing, and/or backups? I believe backups are run only against SQL01, so what happens if/when there is a failover making SQL02 the primary?

Thirdly and probably related - if the AG exists to route queries to a given node, I can understand that all databases are kept in sync so that applications can continue querying and any failover between nodes should be transparent to the application, but if backups (or mailing of query/report results) are run by scheduled Agent jobs, how are these handled after failover? Is this something manually kept in sync by the DBA?

Fourth, along similar lines - is it conceivable that there are differences between databases across two nodes. ie. I'm not talking about differences that will synchronise automatically; I mean might someone have made a change - such as adding a trigger to a table - on a replica that won't have replicated across all nodes?

Fifth - to start thinking about manual failovers, what things should be checked ahead of failover? I saw a question here talking about checking how quorum is formed - do I need some kind of report on Quorum setup before I fail over? If so, what am I looking for?

Sixth, another question here mentions checking the is_failover_ready column on sys.dm_hadr_database_cluster_states - but when I query that table, I'm told "invalid object name". What gives?

Seventh To the specifics of OS upgrade, what is the basic approach - Should I, say, take SQL04 off the AG (or pause syncing), then upgrade its OS, restart the machine, then re-add to the AG / continue syncing? If so, how? And how do I know at the end of everything that all is ok? What would be the "rollback plan" if something doesn't work out as hoped?

Eighth Continuing with the OS update, do I do the same thing for SQL02, then failover from SQL01 to SQL02, upgrade SQL01 OS then fail back? Again, how to determine each step is "successful"?

Ninth - what happens if I initiate a manual failover but SQL01 is in the middle of backing up 20 or so databases? Is there something to check for me ahead of time, on how database backups might be affected, or something to check after failover, to ensure database backups are still functioning as required, post failover? We have full nightly backups on all databases plus partial and incremental backups - but only on some databases.

Tenth - similarly, what if automated jobs are running at the time of failover? These might be Agent jobs updating data or running queries for mailout, or they might be a third-party BI system pulling data for ETL prior to import into the BI end. Is it safe to presume the BI systems will continue receiving their data post-failover, or should I be checking how they're making their connections to the DB first? (Is it possible they are connecting to SQL01 specifically (or any other node really), in spite of the AG, so that when SQL01 goes down for the OS upgrade, the BI extract fails?

Like I said, have put a fair amount of reading into AGs overall, but not easy to find these big picture answers :/


texts:
How can I insert any type of text into a column of type text?

At the moment I am using this:

$$description$$

But a lot of my descriptions contain $ and other signs, so the insert will fail. Do I need to convert the text to something?


texts: Procedure runs slower over time; fixed by cache clear or reboot

texts:
We are experiencing a strange issue where an application which was fixed by creating missing index requests, updating stale stats, and removing lookups and other scans, was running fine once fixed, say executing in 5-10 seconds.

But after 2 weeks or so performance starts degrading. The tables are unchanged as they are updated once in month. There has been no change as such in SP's. Its when application runs the filter may change as designed in SP parameters.

Moreover on reboot or when proc cache is cleared, things go back normal. Again after some period of time , the performance degraded and still there has not been any updates on tables.

Please help where start? I believe this could be something related to parameter sniffing but not sure.


texts: How to take partitions or filegroups out of service

texts:
I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition.

I've inherited a large customer metrics database that grows by about 10 GB per day. This database currently consists of one large data file in one filegroup (PRIMARY). All tables have a datetime column called InsertedDate. I want to horizontally partition the data by InsertedDate, using a separate data file for each calendar week.

In a test environment I added the required additional filegroups and data files to this database, put a clustered index on InsertedDate in each table, and set up the partition function and partition scheme. By querying sys.partitions and other system tables, I've confirmed that the data is now physically residing in the correct partitions and data files.

Among others, the goals are:

Decrease backup time by only backing up the PRIMARY file group and the file group for the current date range (I'm currently running nightly full backups). Once a date range is in the past, that partition will never be written to again, so I'd like to set the filegroup to read-only and back it up one final time.
Be able to eventually take a partition "out of service". After 3 months there's no longer a need to keep older data online, so I'd like to take take that data offline (but be able to bring that data back online again if necessary).

Questions:

1) How to I perform backups on this partitioned database without having to back up the entire database? I can back up an individual filegroup, but to restore it requires the rest of the database, which defeats the purpose of using multiple smaller data files.

2) How to I take a partition out of service? I've read about switching, but it seems that only works if you want to move data between partitions within the same database. I want to be able to simply and safely take a range of data offline (and bring it back online of necessary).


texts:
How to setup replication(Master/slave) in MySQL 5.6 while both servers are windows based?
All the things i have fond are Linux based replication. kindly someone help who experienced replication with windows based server.


texts: How can I force myisamchk to modify the original data file in case of duplicate keys?

texts:
In the documentation for myisamchk, it says:

--quick, -q
Achieve a faster repair by modifying only the index file, not the data file. You can specify this option twice to force myisamchk to modify the original data file in case of duplicate keys.

Does this really mean to execute the command like myisamchk -qq table_name??? In fact, I tried this, but I still get many errors of the form:
myisamchk: warning: Duplicate key for record x against record at y

Can I assume that myisamchk is correctly deleting duplicates? As a side-question, what is a good way to check a table for duplicates?
Per request, results of SHOW CREATE TABLE:
Table: entities5
Create Table: CREATE TABLE `entities5` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`ext_id` bigint(20) unsigned NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`first_name` varchar(130) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_name` varchar(130) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`locale` char(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL,
`username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`name`,`id`),
UNIQUE KEY `ext_id` (`ext_id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


texts: Function returning "No data - zero rows fetched, selected, or processed" in MySQL 5.5

texts:
I have upgraded MySQL version to 5.5.16

After upgrading, some of the functions are giving warning as No data - zero rows fetched, selected, or processed.

In those functions, cursors are there, but the control is not even going to the cursor.

I have also used continue Handler as follows,

Declare continue handler for NOT FOUND
Begin
  set Done = 1;
End ;

I have tried using continue handler in following way,

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'

Even after changing it is returning same warning.


texts: Size of Log Backup is not reducing from past three days

texts:
Recently we implemented Incremental(Enabled allow incremental dumps) and Log Backup(Disabled trunc Log on chkpt) for Sybase ASE(15.7 SP139) running on HP-UX Itanium server and to our great surprise, Size of Log backup is increasing from past 3 days continuously. Even after Full/Cumulative backup, size of the Log file kept on increasing at disk and even size at database is not clearing.

Checked logsize at different interval by running command sp_helpsegment logsegment within database and noticed that used_pages is constantly increasing and free_pages is on decline.

We even checked dbcc gettrunc to get details however it was of no use. Below is size of files

We are using 2 stripe for database dump and compress 5 for compressing size on disk while taking dump.

Replication is running on this database and is working fine.

Appreciate if I can get expert advise on this issue.


texts: Issue With SSRS Emailing Reports As PDFs

texts:
I have a strange issue that I'm unable to find the answer to online or by looking at it myself.

Basically when I set up an email subscription for a report I've created in SSRS Report Builder (2008); I can render my report into any format other than PDF and it will be sent to me successfully. The report is a table that returns some client information from our recruiting database; nothing complicated and it does work correctly if I choose another format.

If I try and send as a PDF I receive the error:
'Failure sending mail: The transport lost its connection to the server.'

I can export the report into PDF format if I run it from the report designer and the file size for the fully rendered PDF is ~35kb so I can't imagine a timeout.

It is baffling me and I'd like to know the root cause.

Thanks for your time.


texts:
So I have this problem with one of my queries.

The query is meant to get data from table patients and join the table patient_nurse, then table nurses to it. The patients table is as follows

id | name | added
----------------------
1  | John | 2014-03-30
2  | Doe  | 2014-03-31

The patient_nurse table looks like this

id | patient_id | nurse_id | shift     | added
---------------------------------------------------
1  | 1          | 2        | MORNING   | 2014-03-30
2  | 1          | 3        | EVENING   | 2014-03-30
3  | 2          | 2        | AFTERNOON | 2014-03-31
4  | 2          | 1        | MORNING   | 2014-03-31
5  | 2          | 3        | EVENING   | 2014-03-31
6  | 2          | 4        | EVENING   | 2014-03-31

The nurses table looks like this

id | name
----------
1  | Jane
2  | Joe
3  | Will
4  | Harry

How should the query be if I want to get the following information?:

patient_name | nurse_primary | nurse_morning | nurse_afternoon | nurse_evening
------------------------------------------------------------------------------
John         | NULL          | Joe           | NULL            | Will
Doe          | NULL          | Jane          | Joe             | Will

As you can see, I just need 1 nurse data for 1 shift on a patient (why nurse Harry is left out), which is the nurse inserted first (lower id value).


texts: SQLServer - Detect if an insert trigger was fired by an Application insert or a Replication insert

texts: Using SQLServer 2014+. The database i'm interested in, is part of a Merge Replication schema. Is there a way to detect in an insert trigger, if it was fired by an Application insert statement or a Replication-Agent insert trigger?


texts: Should I delete disabled windows Active Directory accounts from SQL 2008?

texts:
Is there a risk of not deleting unused Windows logins that have been disabled in AD or is it simply a housekeeping issue?

e.g. Windows authentication is used to provide database access. A user leaves the organisation. Their Active Directory account is disabled, but their SQL login remains.


texts: Is it possible to reference multiple foreign keys with one primary key?

texts:
Really hope this is the right place to ask this, but I just want to make sure this is modelled correctly.

I'm trying to create a relationship between store ownership (i.e., stores that bought other stores, or stores that are owned by other stores).

I have 3 tables:
Stores
parentStores
childStores

Essentially a store can have multiple children stores, but only one parent store. I just don't know the best way and type of relationship to link them--currently I have:

stores has a 1-to-Many relationship with parentStores (on storeID which is PK in stores and FK in parentStores)

stores has a 1-to-1 relationship with childStores (on storeID which is PK in stores and FK in childStores)

Is this the right way to go about it?

Sorry if it's an obvious question; new to 'backend' work.


texts: Storing overridable hierarchical settings

texts:
I've searched the Internet and found the question Pattern(s) about hierarchical settings overwriting on Software Engineering SE. It exactly describes my need, the only difference is that I need to store the settings in a SQL Server database.

How can I implement it in SQL Server?

Whenever I select the setting for level1-child1, I want all overridden settings from child1, plus inherited ones from level1. For example:

level1: max-value:100;  min-value:10;  enable-alarm:true;
level-child1: min-value:30;

after select:

level1-child1: max-value:100; min-value:30; enable-alarm:true;

Currently I use hierarchy path to implement hierarchies.


texts: getting error while installing memcached_function

texts:
now i am working with memcached with mysql cluster 7.2
for that i am installed

libevent-1.4.14b-stable.tar.gz
memcached-1.4.5.tar.gz
libmemcached-0.8.tar.gz

then

memcached_functions_mysql-0.8.tar.gz

i have installed first 3 successfully
but while installing memcached_functions with

./configure  --prefix=/usr/local/ --with-mysql=/usr/local/mysql/bin/mysql_config

i am getting error like

  checking for DEPS... configure: error: Package requirements (libmemcached >= 0.17) were not met:

can any one help me out in finding the solution...

thanks in adv

Deepak M


texts: How to see percentage of existing values for all columns in mysql table?

texts:
I have three large-ish tables (~20 columns each) and I want to know what percentage of rows have a value for each column.

For instance, this table:

ID   | TITLE               | SERVING     | NOTE
-------------------------------------------------------------------
6716 |  Yummy Molasses ... | 1 cookie    |
3765 |  Rosemary-Red Wi... |             |
5178 |  Stuffed Avocado... |             |
6025 |  Amazing Pea Sou... | about 1 cup |
4412 |  Overnight Oatme... | 1 cup       | Note: Steel-cut oats...

Might yield results similar to this:

ID   | TITLE               | SERVING     | NOTE
-------------------------------------------------------------------
100% | 100%                | 60%         | 20%

This is for an internal report, so the format isn't too important.  If I only have the count() instead of the percent, that's fine.  Having the column name would be nice, but not necessary.  Etc.

I'm not very familiar with queries that involve more meta questions like gathering table names or working over all the columns in a table, so I'm interested to see what people suggest.


texts: selecting different categories of values from one column using limit

texts:
i have table question_bank in my database where i store questions and there is one column called complexity. which defines complexity of question (High,Med,Low). for example i entered 30 question in my table ,10 have high complexity ,10 have medium and 10 have low. is there any way so i can show user 15 questions (5 from each category)
what i want to achieve

select * from question_bank where complexity=high and complexity=medium and complexity=low limit 5

this query is just for your idea

thanks in advance


texts: Data Corruption on base backups created with wal-e?

texts:
I have streaming replication setup on Postgresql 9.1 with two machines on AWS EC2. The master has a cron job that does a daily base backup using the wal-e tool and uploading it to Amazon's S3 storage. The WAL files are also archived to S3 by using the same tool.

At some point, when I had to replace the slave instance, I ran a wal-e based script that loads the base backup from S3 and writes it to $PGDATA. After that when I started up the database, I got the following errors:

...
Apr 10 10:15:29 dbc postgres[25865]: [12-1] 2014-04-10 10:15:29 UTC LOG:  restored log file "00000005000000B800000089" from archive
Apr 10 10:15:31 dbc postgres[25865]: [13-1] 2014-04-10 10:15:31 UTC LOG:  restored log file "00000005000000B80000008A" from archive
Apr 10 10:15:33 dbc postgres[25865]: [14-1] 2014-04-10 10:15:33 UTC LOG:  restored log file "00000005000000B80000008B" from archive
Apr 10 10:15:47 dbc postgres[25865]: [15-1] 2014-04-10 10:15:47 UTC LOG:  restored log file "00000005000000B80000008C" from archive
Apr 10 10:15:47 dbc postgres[25865]: [16-1] 2014-04-10 10:15:47 UTC FATAL:  could not access status of transaction 0
Apr 10 10:15:47 dbc postgres[25865]: [16-2] 2014-04-10 10:15:47 UTC DETAIL:  Could not open file "pg_subtrans/2214": No such file or directory.
Apr 10 10:15:47 dbc postgres[25865]: [16-3] 2014-04-10 10:15:47 UTC CONTEXT:  xlog redo hot_update: rel 1663/24577/5279107; tid 22/32; new 22/84
Apr 10 10:15:47 dbc postgres[25863]: [1-1] 2014-04-10 10:15:47 UTC LOG:  startup process (PID 25865) exited with exit code 1
Apr 10 10:15:47 dbc postgres[25863]: [2-1] 2014-04-10 10:15:47 UTC LOG:  terminating any other active server processes

The server does not start up.

Does this mean that my base backup is corrupted? Or does it mean that some WAL files are corrupted?

The master server is running Postgres 9.1.12 (Debian Wheezy), but the newly launched slave is running Postgres 9.1.13 (Debian Wheezy) - this happened because when installing the new machine, only the minor upgraded version of Postgres 9.1 was available in the repositories. My understanding is that this should not be a cause of the problem.

Interestingly, if I do an rsync of the master's data directory to the slave, and then start the slave with streaming replication setup, the slave comes up fine.

Because of this it is not clear if I have a corruption in my DB data directory (which is on an AWS EBS drive) or if there is something wrong with the backup tool wal-e. Any help is appreciated.


texts: Enabling both local and remote connections to a mysql server on linux (ubuntu)

texts:
Is there any way to enable both remote connections and connections via "localhost" to a mysql server. I tried adding both

bind-address = 127.0.0.1

and

bind-address = externalIP

but it just used the one later in the file

Is this something that is possible or will I have to just use one?


texts: How would you track DB storage use and then extrapolate it?

texts:
I need to track the size of various DBs we have, then see it's growth rate so that we can guess where it will be at certain times. I'm using Oracle 11g first, but will eventually be applying this to other DBMSs like DB2 and Sybase.

I'm not even sure where to begin or what commands/queries I'll need to run.


texts: Installing SQL Server on not the default path

texts:
I am trying to install SQL Server 2012 on a machine which has

764 Mb Free Space on C:
832520 Mb Free Space on D:

So during the installation process I changed the default location from C:\Program Files\Microsoft SQL Server\ to D:\Microsoft SQL Server\

But during the Disk Space Requirements check I cannot proceed saying I dont have enough space left on C:

Is it possible to not use C: at all or is it not configurable?


texts: PostgreSQL: Removing standby from streaming replication

texts:
I have PostgreSQL 9.4 streaming replication setup between three nodes (master and two hot standbys). I use repmgr to control the replication cluster. I need to retire one of the standbys and remove it from the replication set. I'd like to keep the data on that standby. I use replication slots.

My question is: what is the preferred way of removing a standby node from the replication set? There is a standby unregister command in repmgr but it will only remove the node from repmgr's catalog and won't touch the actual replication setup.


texts: How can I set TIMESTAMP's default to future date?

texts:
I know I can set default value with current timestamp like this

... DEFAULT CURRENT_TIMESTAMP

Is there any way to set the column with fixed amount of units based on current timestamp?

... DEFAULT CURRENT_TIMESTAMP + 10 days?


texts: Should I use SSIS to write programming logic?

texts:
I have come across a fairly complex system where the business logic is written in SSIS. Since SSIS is not able to perform a big portion of this logic, it is heavily dependent on C# script tasks to perform the operations.

To give you an example of things that requires C# Script task, the application requires making API calls to a 3rd party application for getting the data and also requires a third party library for performing some calculations on the data.

Calling out to C# script tasks makes development and debugging more difficult... but at the same time, SSIS allows programmer to make use of C# script tasks.

What are the applications of SSIS?
Is there any benefit in implementing a complex logic using SSIS + C#
Script Tasks? If we need to use C# Script , then why not
write the whole logic in C#?


texts: What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage?

texts:
In one application, we have an audit table that uses the ArchiveEngine in conjunction with a few triggers to capture 100% of the changes that occur. The upside to this is that 20+ million rows occupies 145MB, not to mention that ArchiveEngine only supports INSERT and SELECT, rendering the trail immutable.

The downside comes when you want to access that data in order to give the user audit information. Since the data is stored in compressed form and there are no indices, selection time can be ridiculous:

mysql> select * from AUDIT_LOG WHERE Table_Name = 'myTable' AND Row_ID = 9024;
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
| Table_Name | Row_ID | Field_Name  | Old_Value           | New_Value           | DB_User        | modified_by | date_modified       |
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
| myTable    |   9024 | Flags       | 0                   | 1                   | user@localhost |         826 | 2011-06-10 22:05:11 |
| myTable    |   9024 | Status      | Unavailable         | Pending             | user@localhost |         826 | 2011-07-08 22:41:45 |
| myTable    |   9024 | Status      | Pending             | Processing          | user@localhost |         826 | 2011-07-08 22:41:49 |
| myTable    |   9024 | Status      | Processing          | Calculated          | user@localhost |         826 | 2011-07-08 22:41:54 |
| myTable    |   9024 | Fee_Paid    | NULL                | 185.00              | user@localhost |         826 | 2011-07-08 22:41:54 |
| myTable    |   9024 | Status      | Calculated          | Approved            | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved    | NULL                | 2011-07-08 22:47:06 | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved_TZ | America/Yellowknife | America/Vancouver   | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved_By | NULL                | 826                 | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Invoice_ID  | NULL                | 2372                | user@localhost |         825 | 2011-10-17 19:32:59 |
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
**10 rows in set (13.54 sec)**

After conversion to InnoDB and adding a PK and indexing Row_ID and Table_Name, this same operation takes roughly 1/10th of a second.

What I'm considering is a leaving the triggers + Archive, and then using a cron job to mirror the Archive in InnoDB.

Are there other ways this has been dealt with?
Any drawbacks or trade-offs that are apparent?


texts: Log Shipping with Failover Cluster Server Cores

texts:
Failover Over Cluster 2 Node - 6 Cores- Enterprise Edition 2014
Log Shipping with Cluster with 4 Core License- Enterprise Edition 2014

My question is if I need to log ship do I need to match the server core of the Primary server that is the failover cluster (6 core) in my case or I can go with 4 core on the Log Shipped server to save licensing costs?


texts:
I get a lot of following messages

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction. Statement:

I don't really understand why this happens. According to messages, it happens because in the code I try to INSERT and UPDATE Innodb table and MyISAM table. Is it right?

Is this dangerous? What's the consequences of this error.

How can I solve it. Can it be solved without changing the code?

Currently, it looks like by default, mysql uses BINLOG_FORMAT = STATEMENT. It means that replication on slaves runs like mysql statements? What's the preferable option for BINLOG_FORMAT?

Mysql version is 5.5.24.

Thanks


texts:
I have a challenge with replication on SQL Server 2000. The replication has full permissions, but still I get this below error. The SQL Server 2000 is installed on the C: Drive, but the database is on E:.

  \myserver\Replication\unc\myserver_GS_ECC25_2675_Publication1\20130813090100\SpInsertSbraConf_1469.sch'
  could not be propagated to the subscriber. (Source: Merge Replication
  Provider (Agent); Error number: -2147201001)

  Insert Error: Column name or number of supplied values does not match
  table definition. (Source: myserver (Data source); Error number: 213)


texts: ALTER DEFAULT PRIVILEGES - permission denied

texts:
I have a problem with ALTER DEFAULT PRIVILEGES. I grant the permission:

ALTER DEFAULT PRIVILEGES
    FOR USER user_name
    IN SCHEMA schema_name
    GRANT SELECT ON TABLES TO user_name;

in next step I create a table

create table schema_name.a (q int);

Now, I connect to dabasabe using conn_user and try to read the data in the new table.

select * from schema_name.a

and I receive the error:

SQL Error [42501]: ERROR: permission denied for relation a
  org.postgresql.util.PSQLException: ERROR: permission denied for relation a

Do you have any ideas why the error appears?


texts:
I have a query in SQL Server 2008R2 that I want to run against a computed column that in turn I am going to index - in order to speed things up.

The column is of type int right now.

I cannot get the syntax right.

alter table dbo.tOutput Add StateNew as State IN (0, 10)

I get an error

Incorrect syntax near the keyword 'IN'.

The query, coming from C# entity framework, should then later on use the index and - well - be fast.

The C# seems to be converted to the IN usage, the original is like this:

tOutput.Where(x => (x.State == 0) || (x.State == 10))

The generated SQL reads

.. FROM tOutput WHERE State IN (0, 10) ..


texts: how to obtain the functionality of LISTAGG function in Oracle 10g?

texts:
I need to put output of a query in single line so that I can create a single tar file of all the files. But my database is running on 10g version. LISTAGG would have been very useful in this case. How to acheive same output as LISTAGG without using it

example:

SQL> select col1 from table1;

col1

--------
file1

file2

file3

OUTPUT is required in following format,

col

--------
file1 file2 file3


texts: getting "table is full" error even if the table size is much smaller than tablespace

texts:
Currently I am using MySQL NDBCluster 7.3.5.
I have created one UNDOFILE hhme.dat with INITIAL_SIZE=200m.

Also one tablespace hhmefep_tbs with DATAFILE hhmefep_tbs.dat having INITIAL_SIZE=500M ENGINE=NDB.

Also the output of query is:

mysql> select FILE_NAME,TOTAL_EXTENTS,FREE_EXTENTS,EXTENT_SIZE, extra  from information_schema.FILES where TABLESPACE_NAME='hhmefep_tbs' and FILE_TYPE="DATAFILE";
+-----------------+---------------+--------------+-------------+----------------+
| FILE_NAME       | TOTAL_EXTENTS | FREE_EXTENTS | EXTENT_SIZE | extra          |
+-----------------+---------------+--------------+-------------+----------------+
| hhmefep_tbs.dat |           500 |           21 |     1048576 | CLUSTER_NODE=1 |
| hhmefep_tbs.dat |           500 |           21 |     1048576 | CLUSTER_NODE=2 |
+-----------------+---------------+--------------+-------------+----------------+

Database total size is 32MB and all tables are using ENGINE=NDB.

Still I am getting "table is full" error when I perform inserts.

Can anyone point out the issue here since 500MB of tablespace is not yet exhausted.

Why am I getting free_extents = 21 ?

Edit:
The DDL for creating this DB schema is as given below:

mysql> CREATE LOGFILE GROUP hhme ADD UNDOFILE 'hhme.dat' UNDO_BUFFER_SIZE=16M INITIAL_SIZE=200m ENGINE=NDB;
Query OK, 0 rows affected (9.70 sec)

mysql> CREATE DATABASE hhmefep;
Query OK, 1 row affected (0.05 sec)

mysql> CREATE TABLESPACE hhmefep_tbs ADD DATAFILE 'hhmefep_tbs.dat' USE LOGFILE GROUP hhme INITIAL_SIZE=500M ENGINE=NDB;
Query OK, 0 rows affected (24.30 sec)

[root@mgmtserver ~]$ ndb_mgm -e "all report mem"
Connected to Management Server at: localhost:1186
Node 1: Data usage is 16%(2723 32K pages of total 16384)
Node 1: Index usage is 9%(1605 8K pages of total 16416)
Node 2: Data usage is 16%(2723 32K pages of total 16384)
Node 2: Index usage is 9%(1605 8K pages of total 16416)

Edit2: Output of query suggested by @user3420095:

mysql> select FILE_NAME, FREE_EXTENTS*EXTENT_SIZE as bytes_free, extra
    -> from information_schema.FILES
    -> where TABLESPACE_NAME='hhmefep_tbs'
    -> and   FILE_TYPE="DATAFILE";
+-----------------+------------+----------------+
| FILE_NAME       | bytes_free | extra          |
+-----------------+------------+----------------+
| hhmefep_tbs.dat |   22020096 | CLUSTER_NODE=1 |
| hhmefep_tbs.dat |   22020096 | CLUSTER_NODE=2 |
+-----------------+------------+----------------+

Hope it clears confusion.


texts: What is the best practice when creating boolean columns in Oracle?

texts:
I faced again the serious problem, that Oracle simply doesn't have a boolean column type. I need to use char(1), or smallint, or some other.

What is the best practice, how to emulate boolean values in Oracle?

(Space consumption isn't important now - but a good cooperation with java/hibernate line were important).


texts: Real time or close to real time with MySQL or MSSQL

texts:
I work at a startup that is working with different databases among which are MySQL (v4/5) and MSSQL (2014 and older). Different clients we cater to run different dbs. Currently we have a Java program that connects to the db and then pushes data to the API - we tap into the db. We were looking to upgrade the system to have real time updates to the API and then got into a bit of standstill with designing real time updates with a MySQL or MSSQL databases.

One of the developers is pushing for having triggers in either db that run an external command (our java program perhaps) that will initiate a push to the API. Somewhat similar to some NoSQL dbs like Rethink. Another dev is against triggers and is proposing more frequent polling of the db, which will not provide real time, but will get pretty close to real time.

I searched for a while for SQL real time solution, but was not able to find any solid reads with use cases and examples that support either approach. So my questions are:

Is frequent polling of the db engine like that of MySQL or MSSQL an ok thing to do? I am talking about 10 or 30 second polling. The db normally gets traffic from 3-4 clients at most and has roughly 20-40k records in table we are interested in. So we'd be adding another client that's polling aggressively.
Has anyone ran into actual negative use cases with triggers calling external commands? We won't be using them to guarantee integrity and consistency is also not that big of a concern in our use case (it is, but there is some wiggle room).

I am not a DBA or an experienced dev yet. Quite fresh from CS university with some experience. And all of us are like that so I am concerned that this lack of knowledge might backfire later with clients.


texts: Postgres still connecting via SSL - despite invalid certificates

texts:
I'm playing around with SSL connections to a test postgres server and I'm finding I can still initiate an SSL connection despite invalid certificates. (The valid date has expired)

I have enforced SSL connections in the pg_hba.conf

## pg_hba.conf - SSL TESTING
hostssl         mike            mike            192.168.56.106/32       md5

SSL Certificate is expired:

$ openssl x509 -in server.crt -noout -dates
notBefore=Feb 22 18:29:39 2019 GMT
notAfter=Feb 23 18:29:39 2019 GMT

Still able to connect via SSL:

postgres:/db/postgresql/10/data>psql "sslmode=require host=192.168.56.105 dbname=mike user=mike"
Password:
psql (10.7)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
mike=>

Why can I still connect?

I'm hoping for a situation where the connection is rejected because the certificate is invalid - am I missing something? Any help appreciated :)


texts: Proper Index for WHERE X  1 AND Y  1 ORDER BY Z

texts:
I have a relatively large table that is accessed in exactly one way, by exactly one query (on the read side).

The query filters the table by two columns (both bit), and sorts by a third column (an integer).  Note that the query names the columns (it doesn't actually use "*").  The "TOP 1000" is intentional and is part of the production query:

The query is generated--not sure why it generates the way it does.

SELECT TOP 1000
     *
FROM
     MyTable m
WHERE
     m.IsFlag1 != 1
     AND m.IsFlag2 != 1
ORDER BY
     m.SomeId

I have a covering index, with the main columns being IsFlag1 and IsFlag2.

The execution plan shows and Index seek on this index, and then a sort.  The sort takes up 97% of the cost of the query according to the execution plan.

I tried adding SomeId as a main column of the index (the third column), but the execution plan remained the same.

I then tried adding a covering index using only SomeId as a main column, leaving the original index in place.  The execution plan then does an index scan on the new query, and the operator cost is significantly lower (and is functionally much faster).

All that said, I'd like to optimize this query as much as possible.  Is there a way to modify the index so that it simply does a seek?


texts: Best approach to design a database model for this situation? Clients and records

texts:
We need to store the following information for each client:

Three types of record per file, for example (obviously, with real data):

HDR2007022399999900000000000Soft/XX        0.0  XXX
XXX000000000000000               200702200000000000000000000           999999999               000000000100001
XXX003001958208255               200702200000000000000000000               999999999               000000000100001
XXX003001958208255               200702200000000000000000000               999999999               000000000100001
TRA200702222222222222222222XXX             0.0  XXX

In the name of the file is the client code which is used to identify each client.

The line starting with "HDR" is the Header.
Those starting with "XXX" are the Body and finally "TRA" is the footer.

Although there must be presented always together, at a first glance I decided to store them in three different tables, as they're different types of records (Don't know even if this is the best way)

I thought of two different models (can't post images):

With hierarchy: http://i.stack.imgur.com/ThHWn.png

I'm not sure if I will need a supertype field in the "Record" table in this case.

The other:
Without hierarchy: http://i.stack.imgur.com/FiJDf.png

Maybe I can remove idClient and just identify with the clientCode, as it is unique, but I have read is always good to have an auto incremental id.

I think the hierarchical approach is more correct, but not sure of that.

Also, with a hierarchical model it will be more difficult to retrieve all data, right?

The parameters I will have at first, to make queries are the dateReceived and clientCode. So if I want to retrieve all records for a client, I should use an INNER JOIN to join all subtypes, right?

Well, sorry for all questions.

Thank you very much in advance


texts: Trying to use SSRS rsconfig.exe in silent mode to create the ReportServer and ReportServerTemp databases on a local instance

texts:
I have installed a brand new SQL 2017 Enterprise instance on a Windows 2019 Datacentre VM.

I have then installed SQL Server Reporting Services via the command line (/quiet, /norestart, etc) and all has installed fine.

I am able to use rsconfigtool.exe to manually create the ReportServer and ReportServerTemp databases via the GUI, but I would like to be able to complete this task via automation (if possibly silently and via the command line).

I would like to have the ReportServer and ReportServerTemp databases installed on the instance I have created and not a new SSRS instance.

rsconfigtool.exe does not appear to except any parameters in order to make this change.

Any advice/help would be gratefully received.


texts: Deploying to SSIS catalog and get permissions error

texts: When I attempt to deploy to SSIS 2012 Catalog on new server I get an error.  I have researched it on the web for several hours and all of the information available online does not fix my issue.


texts: SQL Server 2008 R2 Express edition limitation

texts:
I have website running on SQL Server 2008 R2 Standard edition and on new server we are provided with SQL Server 2008 R2 Express edition. I am sure that there are certain limitations like

maximum of 10GB of DB size per database
Utilization of 1 process only
Use of 1 GB Internal memory only.

I would like to is there any limitation on maximum number of concurrent connection in Express edition and how slow it can be when it comes to performance.


texts:
I have the following table:

Date,Type
2012-01-25 13:48:00.000,1
2012-01-25 13:49:00.000,2
2012-01-26 13:48:00.000,1
2012-01-26 13:49:00.000,2

I tried the pivot function to get this layout:

1,2
2012-01-25 13:48:00.000,2012-01-25 13:49:00.000
2012-01-26 13:48:00.000,2012-01-26 13:49:00.000

I'm not sure if it's even possible to use the pivot function, because I have to use an aggregate function in pivot, right? Is it possible to use pivot or should i use an self join?

Best regards
Stefan

EDIT:

This is my pivot statement:

SELECT
  [1],
  [2]

  FROM
  (
    SELECT
      [Date],
      [Type]

      FROM
        [Test]
  ) AS SourceTable
  PIVOT
  (
    SUM([Date]) FOR [Type] IN ([1],[2])
  ) AS PivotTable;


texts: SQL AlwaysOn Availability 2 synchronous nodes on either side of an asynchronous link

texts:
Say we have two datacentres, DC-A and DC-B. Say in each DC, there are two SQL servers. DC-A has SQL-A1 and SQL-A2 and DC-B has SQL-B1 and SQL-B2.

Is it possible to have SQL with an AlwaysOn availability group running synchronously within each datacentre, and an asynchronous push from the primary site to the secondary? Such that:

SQL-A1 and SQL-A2 are synchronously replicating
and
SQL-B1 and SQL-B2 are synchronously replicating
and
SQL servers in DC-A are asynchronously replicating their synchronous copies across to DC-B SQL servers.

Is this possible?


texts: What is the word that means a query predicate can use an index

texts: I remember reading in a paper a word that means “query predicate can be evaluated using an index” but I can’t recall the word. For example if you had a table with a column called “name” and you had an index on name, then a query that had a WHERE clause involving “name” could be succinctly described by this word as “An X-able query”, where X is the word I don’t remember.


texts:
I am getting the below error on occasion. Maybe every 30mins or so. I am not sure how to track what is causing the issue. I think it could be a Network problem as there are disk backups running as well.

Any suggestions on where to begin? Thanks!

The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.) ---> System.ComponentModel.Win32Exception: The wait operation timed out


texts: Passively load a monitoring shared object into an Oracle process

texts:
I'm trying to load my own code, which should add some security features to my environment, into each oracle BEQ dedicated process.
The reason I'm posting this on this platform, is that I hope there is some DBA hack to trick Oracle to load my shared object when a new instance of oracle starts to run.
I have found out about external_libraries that in first glance seemed to fit, but after investigating how it works, I'm have found out that the library is being loaded into another external process and the loading will only happen on triggering a specific external function.
I know DB2 has an option to load external plugins to add functionality to the database (e.g 1, 2 and 3) - this would fulfill my needs. - Again, I'm not intrested in the actual logic behind the plugin, all I want is the oracle to pull/load me passively into it's memory space when it starts.
Can you think of anything?
Thanks


texts: How to drop all scheduler events in MySQL 5.6?

texts: In MySQL 5.6 is there a way to drop all scheduler events with an administrative command or do I have to go through them one by one with DROP EVENT [IF EXISTS] event_name?


texts: select query to fetch json column internal objects

texts:
I have a json  like {"disks":{"value":[{"Type":"NA"},{"Type":"NA"}]}}.

How can I write a query to fetch value in Type


texts: Optimising an Update statemement with a very large Where clause

texts:
I have a table (TableOne) with a field called [ReviewNote], this table has more than 1.5 million record, I have to assign keywords to each record based on the content of the [ReviewNote], using another table [KeywordsTable] which contains of more than 17000 record of [SearchTerm] and relevant [Keyword].

So the objective is searching for the [SearchTerm] in [ReviewNote] and update the record and assign the appropriate [Keyword].

The problem is, it takes toooooo long to do it in conventional ways, so finally I came up with following script ( I am not sticking to this, just for you to know what I am talking about), it has been generated using the [SearchTerm] from The [KeywordsTable] (like clause).
I used where Clause, joining, temp tables.... before, all too damn slow for this... and field are indexed as as well.

So my question is, is there a way to do what I want to do in a much faster way? optimization? anything ?

Thanks, Zac

UPDATE [DatabeseOne].[dbo].[TableOne]
SET    [KeyWord] = (
        CASE
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%')
                 THEN 'X Ray'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone densitometry -%')
                 THEN 'BMD'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Cons Dr Reitsma -%')
                 THEN 'Consultation'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- consult -%')
                 THEN 'Consultation'

            -- 17000 more WHEN clause
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zostavix -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster @ Safeway Pharmacy -%')
                 THEN 'Immunization'
            WHEN [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- Zoster Immunization -%')
                 THEN 'Immunization'
        END
    )
GO


texts:
Recently, while fine tuning the db for better perf, I changed a couple of indexes on tables which were hit very often.

I am using the following query to keep tab on index stats

SELECT DISTINCT Object_name(sis.OBJECT_ID) TableName,
                si.name                    AS IndexName,
                sc.Name                    AS ColumnName,
                sic.Index_ID,
                sis.user_seeks,
                sis.user_scans,
                sis.user_lookups,
                sis.user_updates
FROM   sys.dm_db_index_usage_stats sis
       INNER JOIN sys.indexes si
         ON sis.OBJECT_ID = si.OBJECT_ID
            AND sis.Index_ID = si.Index_ID
       INNER JOIN sys.index_columns sic
         ON sis.OBJECT_ID = sic.OBJECT_ID
            AND sic.Index_ID = si.Index_ID
       INNER JOIN sys.columns sc
         ON sis.OBJECT_ID = sc.OBJECT_ID
            AND sic.Column_ID = sc.Column_ID
WHERE  sis.Database_ID = Db_id('mydb')

Earlier, there was hardly any user_seeks and most of the queries did a user_scan. As I changed the indexes, the user scans has gone down to zero and all the queries are using user_seek. Now I have read many places that index seeks are desired over index scans, so with that thought my db perf should be better.

But I am getting a lot of SQL Timeouts on the Asp.NET web app. Given that the index I defined contains 11 columns and a larger number of included columns, I was wondering whether maintaining the index itself has affected the perf?

After creating the new index, the user seeks to user updates ratio is 4489587/111656.


texts: Can SQL Server be forced to use a cached plan for executing a SP or SQL script?

texts:
Can we somehow force SQL Server to use a cached plan for executing a SP or sql query?

We have a parallel plan in prod which estimates data size in GB's and estimates number of rows to rows equal to table cardinality.

Just before the final output , query plan estimates

Estimated Number of Rows: 650970000
Estimated Row Size: 1043 B
Estimated Data Size: 632 GB

The execution plan does not show "Actual Number of rows read" , so have to go with above parameters.

I am unable to get the same plan while executing the SP or the internal query manually so cannot really do a like for like comparison of any performance improvement that I make to the query / SP.


texts: Database events that will cause deletion of index

texts: In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem of index being deleted so i wanted to know is there any database changes of events (SQL commands) that will cause deletion of index.


texts: MongoDB: Server sockets closed after a few minutes

texts:
I am working with multiple AWS intances connected to the same MongoDB database (inside Compose.io Elastic deployment). But i am getting the error server <url>:<port> sockets closed after a few minutes. Can anyone give me any hint about what may be wrong with the connection code?

CONNECTION CODE

var url = "mongodb://<user>:<password>@<url1>:<port1>,<url2>:<port2>/<dbName>?replicaSet=<replicaSetName>";
var options = {
    server : {"socketOptions.keepAlive": 1},
    replSet : { "replicaSet": <replicaSetName>, "socketOptions.keepAlive": 1 }
};
MongoClient.connect(url, options, function(err, db) { ... });

ERROR MESSAGE

Potentially unhandled rejection [2] MongoError: server <url>:<port> sockets closed
at null. (/var/app/current/node_modules/mongodb/node_modules/mongodb-core/lib/topologies/server.js:328:47)
at g (events.js:199:16)
at emit (events.js:110:17)
at null. (/var/app/current/node_modules/mongodb/node_modules/mongodb-core/lib/connection/pool.js:101:12)
at g (events.js:199:16)
at emit (events.js:110:17)
at Socket. (/var/app/current/node_modules/mongodb/node_modules/mongodb-core/lib/connection/connection.js:142:12)
at Socket.g (events.js:199:16)
at Socket.emit (events.js:107:17)
at TCP.close (net.js:485:12)


texts: Adding column from SSMS wizard in SQL Server

texts:
Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process:

Create a New Table with new columns
Insert Data from previous table to new table
Redo all the keys and constraints
Drop old table Rename
the new table to use the old table’s name

Why does SQL SERVER take such time and resource consuming process? Is there any benefit from such long process....


texts: How do I recover from MySQL InnoDB table/index corruption?

texts:
While trying to upgrade a database (in place) I got the following error.  The server starts up and everything seems to be fine, except that the table mentioned in the message is no longer accessible.

This is Oracle MySQL "5.5.17-log MySQL Community Server (GPL)"

InnoDB: Error: trying to load index <FF>the_idx for table db/the_table
InnoDB: but the index tree has been freed!
141126 15:41:02 [ERROR] Cannot find or open table db/the_table from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

I made a backup using innobackupex before trying the upgrade, but unfortunately if I try to "prepare" the backup it crashes with signal 6 and I suspect it's also crashing on the same table.

So I've been searching around here and elsewhere and have not found very much about how to recover from this sort of thing.  Obviously I'd like to recover the data from this table.  The message seems to imply that an index is corrupt so maybe the rest is OK?  I can't run CHECK TABLE on it, since MySQL says the table doesn't exist.

I discovered innochecksum, but unfortunately that doesn't like the file either:

# innochecksum -d db/the_table.ibd
file db/the_table.ibd = 3749707776 bytes (228864 pages)...
checking pages in range 0 to 228863
page 0: log sequence number: first = 1383255984; second = 62128266
page 0 invalid (fails log sequence number check)

I'm not sure if that has anything to do with me running it on a separate server after trying to do the innobackupex --apply-log, though.

I have not yet tried innodb_force_recovery because I want to get a better understanding of what went wrong and how I might fix it first to avoid making things worse.

So what's the best course of action?  Set innodb_force_recovery=1 (and then 2, 3, etc. until it works?) and see if I can dump the table?  Will I be able to drop the table and reload it with innodb_force_recovery=1?

I've also just discovered http://www.percona.com/docs/wiki/innodb-data-recovery-tool%3astart and am having a look at it.

Thanks.


texts: Is there a way to prevent special characters on schema objects?

texts:
I would like to prevent the creation of objects with spaces or special characters.
Is there a way to allow only letters, numbers, and the underscore?

Thanks!


texts: Is this package missing and if so, how do I fix?

texts:
I am following the given link.
https://oracle-base.com/articles/10g/oracle-data-pump-10g

I'm an Oracle newbie. Oracle 12c is the target version. I have a Development database running in a docker image and I'm logged in as sys user to make sure that I can see everything. I'm using DataGrip as a client. I'm looking for the DBMS_DATAPUMP package but I don't see it in the GUI and I've also tried SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','package') order by OBJECT_NAME;, but I still don't see it. If it isn't installed, where do I download it from? I believe I've already figured out how to install it.

Our objective is as follows. We want to try creating a master schema in CI that is kept up to date on a weekly or monthly basis and then we want to copy that schema as our starting point for integration tests. Right now we are suffering through a painfully long flyway based migration process.


texts:
How can I make the REPLACE function dynamic, by dynamic I mean that if for example one entry has 3 quotes and an other on has 5, I want it to cater for all situations

BEGIN TRAN
       update contacts
       set address1 = REPLACE(address1,'''','')
       where address1 like '%''''%'

ROLLBACK


texts: Is it possible to backup users from MongoDB admin database?

texts:
The title sums it up nicely.
Essentially what I am asking is, if its possible to backup (use mongodump perhaps?) only users (accounts created for human users)?


texts: Relational tables should contain data and not logic - correct?

texts:
Ok, I couldn't find a better title, to be honest.

I'm recently landed in a new working place, and I've been asked to help with the DB. I wanted to try to explain a concept, but I'm not sure how to word it at best, and maybe this concept has already a better explanation and a name, too. I don't label myself as a DB expert, so I'd like to present them something more official than my own point of view, no matter how many good DBs I've designed in my life.

One of the things I've found mostly amusing (but not in a good way) is that they seems to follow design the db according to the logic of the problem and not according to "this is a database, it should contain data", so for example to know if an element is in a certain category, the query goes this way (in pseudocode):

SELECT Column1, Column2...ColumnN
FROM Invoices [LIST OF JOIN]
WHERE
Invoces.Field1 IN
    (TableA WHERE TableA.Field NOT IN
        (TableB WHERE TableB.Field IN ((TableC WHERE) OR (TableD WHERE)))
OR Invoces.Field1 NOT IN
    (TableB WHERE TableB.Field IN ((TableC WHERE) AND (TableD WHERE))
OR TableB.Field IN
    (TableC WHERE TableC.Field IN (TableC WHERE))
OR TableB.Field NOT IN
    (TableC WHERE TableC.Field NOT IN (TableD WHERE))

and so on and on for other twenty conditions or so. No, I'm not joking.

This happens because instead of saying "this is an invoice and its category is an attribute of the invoice itself", they go all the way with things like "the invoice is been inserted by that user, in that day, and he could have inserted it into a certain department or none at all, and in case of the department then that department could be part of a certain structure..." and so on. It's not even a matter of referential integrity, there are no FKs at all in this case...

Another practical example is the date of last edit of a document: instead of being stored as an attribute, it's calculated by a function which goes something along this way:

  if that document is related to another, then if the other is this
  kind, then if the first document is related to this other document
  too, then the date of last edit is the date when the administrator has
  printed the daily appointment calendar"...

clearly with a bunch of else for every if, too. AND, it's not because there is some sort of dependencies, it's just because many things are stored to reflect their logic.

In a way I'd be tempted to say "write once, read many", with the meaning of "write that attribute once and then read it for free as many times as you need", because we have a low frequency of writes and a very high frequency of reads, and clearly all reads must every time recalculate everything. But I've got a feeling there must be some principle that says that you should design databases to hold data...


texts: SQL Server - Log file not shrinking in AlwaysOn group

texts:
We have three-node AO setup in SQL Server. Primary node, one secondary node in Sync mode and another secondary node in Async mode.
Main Database on Primary node, log file-size is 434GB, Used 99.91%. Log backups is happening every hour only on Primary node.

Ran following query on Primary and Secondary servers.

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'Main DB Name'

Result:

node        log_reuse_wait_desc
------------------------------------------------------------
primary     AVAILABILITY_REPLICA
Secondary-1 LOG_BACKUP
Secondary-2 AVAILABILITY_REPLICA

While other DBs (much smaller than main DB) in AO groups log usage is very minimum (1 to 10%) and can be shrunk, main DB log usage continuously showing more than 99% and not shrinking.
I am guessing since log_reuse_wait_desc is showing "AVAILABILITY_REPLICA" on Primary Node, means not all the log is transferred to Secondary replica, and so its still full.

I checked Redo Queue Size in Secondary-1 Node showing 27GB, Secondary-2 is showing nothing. So most likely Secondary 1needs to get all logs from Primary. The problem is Redo Queue Size in Secondary-1 Node is increasing and not decreasing as what I through it should. Also log-file on Primary node is slowly increasing which is going to fill-up whole disk-space.

Now Secondary-1 Node showing LOG-BACKUP. How do I solve this issue? Should I take Log backup on Secondary-1 Node and check?


texts: DBCC PAGE read page from buffer pool or directly from the file system?

texts: We know that SQL Server always read pages from disk to memory for all its operations. So it's possible that a page in memory is different from it's counterpart in disk. I want to know the command DBCC PAGE is reading a page in memory or on disk. Thanks.


texts: MS SQL 2014 AlwaysOn High Availibiliity, Error 15023, not about to connect on failover

texts:
I setup my first AlwaysOn Cluster following the directions here, with some help from the documentation available on bretozar.com. When I started to test the Listener using the UDL process here I wasn't able to connect with any of the user accounts which were built while building the cluster.

I then signed into the current Primary cluster server (server-A) and created a local test user. I was able to get a Successful Connection while accessing though the Availability Group Listener. However When I failed the group over to (server-B) I can no longer connect to the database.

I didn't see the test user in the list of users for that database on server B, now the primary, and when I try to make it again I get the 15023 Error "User already exists." I didn't have any success with the recommended fix

EXEC sp_change_users_login 'Auto_Fix', 'myUser'

The Test-users is also configured with:
Server Roles = public
User Mapping = Always_On_DB
Securables = Server_A (Server-B is not listed as an option, and the Availability Cluster does not save is it is selected.

I can re-add the Test-user to Server-B, I can access all other databases. The 15023 error appears when I try to add the Always_on_DB to the user properties.

Any help would be appreciated, as right now the cluster only allows access when the primary server is on Server-A.


texts: Innodb mysql 5.7 cluster wont join after reboot

texts:
I get the following when I try to recover the cluster from a complete outage. Can anyone advise what's going on

cluster = dba.rebootClusterFromCompleteOutage()
Dba.rebootClusterFromCompleteOutage: Invalid value for localAddress,
string value cannot be empty. (ArgumentError)

Thanks


texts: SSIS deployment failed - working with files

texts:
I've developed a simple SSIS package that will open a csv file, select a number of column, add two derived columns and then copy the results to a file.

This works within visual studio (even if it takes ages) where I constructed it. The problem is when I attempt to deploy it to our sql server it fails.

It keeps say that:

"The specified parameter value string is too long. The string must be no more than 8000 characters."

But I have no idea what it is talking about as none of my columns is over 1000 characters.

There isn't much info on this error but is it possible that the number of rows (400,000+) is the source of the problem?


texts: How can I create notification on user's connecting to database?

texts:
I have users that have access to several databases.
I used to have an alert and notification sent to me when a specific user connects to a specific database, but I accidentally clobbered it. Now I have to recreate it.

If I understand correctly, logon triggers won't help, since I don't need to know when they connect to other databases.

Please help!
Thank you!

Sergey


texts: How do I force a Writable Materialized View instead of an Updatable one?

texts:
How do I ensure that an Oracle Materialized View is created as a "Writeable" materialized view and not as an "Updatable" one?  I never want these views to push data back to the source and I want a complete refresh to overwrite anything I change.
The source problem is that one of the remote databases cannot support materialized view logs, but the tables have last modified timestamp columns.  So I'd like to attempt to keep them up to date with Merge statements filtering on that column and then occasionally do normal replication refreshes if any issues turn up.
I tried CREATE MATERIALIZED VIEW Test.SomeTable_MVW TABLESPACE SOME_DATA BUILD DEFERRED REFRESH COMPLETE FOR UPDATE AS (SELECT ... FROM SomeTable@OverThere);
But I got the error:
12013. 00000 -  "updatable materialized view must be simple enough and have a materialized view log on its master table to do fast refresh"
*Cause:    The updatable materialized view query contained a JOIN, SUBQUERY,
           UNION, CONNECT BY, ORDER BY, or GROUP BY clause, or the master
           table did not have a materialized view log.
*Action:   Make the materialized view simpler. If a join is really needed,
           make multiple simple materialized views then put a view on top of
           them. Make sure a materialized view log is created for the master
           table.

There is only one remote table.  I do not understand why this error even contains or the master table did not have a materialized view log since that's how you get a writeable materialized view.  Too bad there isn't a "FOR WRITEABLE" clause for when you know that you don't want to push data back.  And the REFRESH COMPLETE should clue it in that I don't want to push the data back.


texts: Export a subset of data, from mysql production database, to make a test database

texts:
I have a mysql production database, with multiple tables. Tables are related with foreign key constraints. I want to make a small test database, by collecting/exporting only a small subset of data from the whole database (i.e. from all tables), so that foreign key constraints are preserved in the data subset.

How can I do that?


texts: Using SSIS Catalog with an old (updated) SSIS package. The driver is out of date. Any way to update?

texts:
We are in the process of upgrading a server from SQL 2008 R2 to SQL 2016. I've taken a 2008 SSIS project and saved it in SSISDB.  I'm getting a validation error that the driver isn't available.  This is unsurprising since it's a very old driver.  The connection manager in question has an expression that overwrites the connection string with the contents of a variable. I've updated the variable in an Environment, however the validation appears to be happening before the connection manager is updated.

I've checked various places, including the Connection Managers tab of the configuration, a couple of the system tables in SSISDB and the Environment but can't see any way to update the driver or even just tell the package to delay validation.

I know several ways I could fix this if I was willing to edit the packages in the project but I would rather avoid that if possible as I'm going to be doing a fair number of these.


texts: Could not fork new process for connection: Could not allocate memory

texts:
I need help to understand the cause of this error and how to fix it.

I have a server running PostgreSQL 9.3. The cluster has around 1.000 databases. I use pgBouncer for pooling connections, and I set up a pool_size of only 1 connection per database.
The total amount of active connections at any time is ~80. This is because most of databases have almost no activity at all.

My application load has been increasing slowly during the last months, and in the last couple of days I started to see the error showing up intermitently in the postgres log file. The time the error is logged corresponds with brief moments where the load is a bit higher than usual. Notice the error happens when postgres tries to fork a new process for connection and even for an autovacuum process:

2020-05-07 07:16:16 -03  LOG:  main could not fork new process for connection: Cannot allocate memory
2020-05-07 07:16:17 -03  LOG:  could not fork autovacuum worker process

These are some of my PostgreSQL settings:

max_connections = 300
shared_buffers = 2GB
effective_cache_size = 2GB
maintenance_work_mem = 1GB
work_mem = 288MB
wal_buffers = 8MB
checkpoint_segments = 16

The server has 64GB of total RAM, 16 CPU cores, and it is running CentOS 7.
My stack is: Nginx, uWSGI, Redis, pgBouncer and PostgreSQL.
It's all installed in the same server, so resources must be shared between the elements of the stack.Redis is set to use no more than 30GB of RAM. Nginx ~8GB of RAM. uWSGI uses ~10GB of RAM. PostgreSQL uses ~8GB of RAM.

I'm no an expert at PostgreSQL. I've been reading the documentation regarding memory consumption, and considering my settings, my best guess is that I need to set higher values for shared_buffers and effective_cache_size. I got to that conclusion because those settings have been there for a long time (when my server had much less resources). But in the last few years, I've been adding resources to my server, but at the same time it has become busier. So I think maybe shared_buffers should be at least of 8gb, what do you think about that?

Also, for what I've read, I think I should make effective_cache_size higher than shared_buffers (considering the amount of RAM the server has available). Am I right?

One more thing: what do you think about my work_mem setting? I'm planning to reduce it (I'm not sure why it is set to 288MB). I know that the vast majority of the queries are very simple and fast. This is because all the databases correspond to simple blog applications, where most of the queries are SELECTs and the UPDATEs involve changing some value at an specific row, nothing complex. So, what do you think? I plan to reduce work_mem, am I in the right direction?

Thank you very much in advance!
Warm regards,
Lisandro.


texts: Postgresql Equivalent of MySQL SHOW CREATE xxx

texts:
Tried to search what are Postgresql equivalent of MySQL's SHOW CREATE TABLE <TABLE-NAME> and SHOW CREATE DATABASE <DB-NAME>. Checking old Answer, it seems impossible. I would have given up if it wasn't for PgAdmin4 which shows them nicely which emboldened me for the search adventure.

So far it have been unsuccessful, with Google showing nothing useful and up-to-date on the issue. trying reading PgAdmin4 proved to be disastrous as I was lost in the code without any guide.

So my question is, is this possible yet? If no how does PgAdmin4 do, if any have an idea?

Note: My app is not allowed to modify the database and insert Function at all


texts:
Can someone confirm if pg_dumpall does a vacuum? I cannot explain the size differences between a local and dumped database but thinking duplicate/rewritten entries are ignored

350mb inside psql with \l
350mb with du -sch at postgres' folder
158mb with pgdumpall to file

running pgsql 9.5


texts: How to fix SQL Server Management Studio 2014 IntelliSense when intermittent

texts:
Our remote SQL Server was recently upgraded to 2014. I installed SQL Server Management Studio 2014 on my Windows 10 client computer.

At first, everything seemed to be going great, until I noticed that IntelliSense is intermittent. By "intermittent" I mean that on the same computer, while working on the same script, IntelliSense might appear some times and not appear at other times. Before the upgrade to 2014, this never happened. Now it happens all the time.

Here's a specific example. Suppose I'm typing SELECT DISTINCT * from dbo. The moment I type the period, I expect IntelliSense to list the available tables, as it always does. So by intermittent I mean that sometimes I see the list of tables, and sometimes I don't.  This just a specific example of how the symptom manifests. This is not restrictive to table names. I mean that whenever I expect Intellisense to appear,  it doesn't always appear.

I checked the obvious: Troubleshooting IntelliSense (SQL Server Management Studio) reconfirmed what I already knew and didn't help. I've tried these things.

Closing the remote database connection and reconnecting
Refreshing the IntelliSense cache, both by Ctrl-R and through the menu Edit > IntelliSense > Refresh Local Cache
Making sure all Windows Updates were applied
Checking server connectivity with Ping (average 9 milliseconds)

This is not a duplicate because while other forum posts list the above steps as "answers" they are not answers for me. They do not solve the problem of intermittent IntelliSense.

Is there anything else i can do to fix this problem?


texts: DB Normalization ends up with 2 similar tables

texts:
I've been googling for a long time now, reading lots of Normalization Tutorials, but haven't found an answer to my question....

The problem (in this question) is when I have something like a trip, where it has a start location and end location, but those locations are stored on a table and assigned an ID.

If I follow the normalization tutorials I end up with:

UNF

TripID, VehicleId, EndLocId, StartLocId, StartLocAddress, EndLocAddress

So if I normalize, I end up with these tables

1NF = UNF (no repeating groups)

2NF = 1NF (no partial dependency)

3NF (Separate Transitional Dependency)

Trip(TripId, VehicleId, StartLocId, EndLocId)
TripStartLoc(StartLocId, StartAddress)
TripEndLoc(EndLocId, EndLocAddress)

In my 3NF the last 2 tables will be exactly the same, so I want to use only one, a Location(LocId, LocAddress)

Trip(TripId, VehicleId, StartLocId, EndLocId)
Location(LocId, LocAddress)

But I can't find any step that allows me to do this, I know I can do it because there's no point in maintaining 2 tables with repeated information... but how to justify? or is this step outside of Normalization? I just want 2 foreign keys in my trip table pointing to the location table, one for the starting point and one to the end point.

Thank you for your help


texts: What is the SSMS option "Execute stored procedure shortcuts without additional execution options"?

texts: This is a wordy option; there is no documentation and web searches are no help. When I'm executing queries, I don't notice a difference with the option checked or not checked. What does this setting control?


texts: DBCC CHECKDB behavior with MAXDOP settings

texts:
I am trying to understand the DBCC CHECKDB usage with MAXDOP settings-
If i run DBCC CHECKDB with MAXDOP = 0 on a SQL instance where my MAXDOP settings is 8, what is happening actually? Is all of my available say 98 schedulers would be used? or still just 8? or just few?
if i run DBCC CHECKDB with no MAXDOP say a default with server level MAXDOP 8 , should i see then all 8 schedulers used? or it can be less than 8
Why I am raising above questions is for same CHECKDB runs for some server it seems to use less schedulers and some server with same run all schedulers causing 100 % CPU spikes
Thank you


texts:
I am trying to import a csv file dynamically.

This works:

BULK
INSERT #CSV
FROM 'C:\Users\MyName\Desktop\Players.csv'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

I would like to change it to be with a @path parameter like that:

DECLARE @path NVARCHAR(4000) = 'C:\Users\MyName\Desktop\Players.csv';

BULK
INSERT #CSV
FROM @path
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)

But this does not compiles.

How could I do that?

Thanks in advance.


texts: How can I import multiple .xlsx and .xls files into multiple tables in Access?

texts:
I have 100 .xslx and .xls files with different formats and I need to import them all separately into Access keeping their original file names intact.

Folder example:
C:\Users\username\Desktop\2019_data\foldername\

sample names  of the many different sheets in the folder=

LGPO221154-1-ANDERSON-CELL 18-49.xlsx
LGPO221154-1-ANDERSON-CELL 50_UP.xlsx
LGPO222975_1_CELL_49_UNDER_Bandera.XLS
LGPO222975_1_LL_49_UNDER_Comal.XLS


texts: Does Postgres preserve insertion order of records?

texts:
For example when I'm using query which returns record ids

INSERT INTO projects(name)
VALUES (name1), (name2), (name3) returning id;

Which produce output:

1
2
3

Will this ids point to corresponding inserted values?

1 -> name1
2 -> name2
3 -> name3


texts: What's the most unsafe MySQL configuration?

texts:
I have MySQL 5.5.27-29.0-log instance running specifically for Jenkins. Since tests data is always recreated during setup, ACID is unnecessary and I'm hoping to sacrifice it to gain more performance.

The box has 32GB of RAM, but most of those are used for running tests. I can probably spend another 64M for innodb_buffer_pool_size

The box has 8 cores with 24% utilization as well as SSD drive for MySQL.

Here's what I have so far for my.cnf:

[mysqld]
innodb_buffer_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_io_capacity = 4000
innodb_doublewrite = 'OFF'
innodb_adaptive_flushing_method = keep_average

Anyone have other ideas what else can I turn off to gain performance?