How to Identify Invalid Views in MySQL

 

How to Identify Invalid Views in MySQL

When working with MySQL, ensuring that all database objects, including views, are valid and functional is essential. A common issue is the presence of "invalid" views, which may occur due to schema changes, missing dependencies, or incorrect view definitions. Detecting these broken views is crucial for maintaining database integrity and preventing runtime errors.

Why Views Become Invalid

Views in MySQL can become invalid for several reasons:

1. Schema Changes: Alterations to the underlying tables, such as column deletions or renames.
2. Missing Dependencies: Dropping tables or other views referenced in the view definition.
3. Incorrect Definition: Errors in the view creation statement.

Identifying Invalid Views

You can use the following query to detect invalid views in your MySQL database:

SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE table_type = 'VIEW' 
AND table_comment LIKE '%invalid%';

Explanation:

• information_schema.tables: This system table stores metadata about all tables and views in the database.
• table_type = 'VIEW': Filters only views from the list of tables.
• table_comment LIKE '%invalid%': MySQL marks invalid views with specific comments, and this filter captures those.

Next Steps After Detection

1. Review the View Definitions: Use SHOW CREATE VIEW <view_name> to inspect the view's SQL definition.
2. Check Dependencies: Ensure that all underlying tables and columns exist and are accessible.
3. Recreate or Drop Invalid Views: If fixing the view is not feasible, consider dropping and recreating it with the correct definition.

Automating the Process

To routinely check for invalid views, you can schedule this query in a monitoring script or integrate it into a database health check routine.

By proactively identifying and resolving invalid views, you can maintain database performance and reliability, ensuring smooth operation for your applications.

 

High Availability Writes

Spanner is a relational database with 99.999% availability which is roughly 5 mins a year. Spanner is a distributed system and can span multiple machines, multiple datacenters (and even geographical regions when configured). It splits the records automatically among its replicas and provides automatic failover. Unlike traditional failover models, Spanner doesn’t failover to a secondary cluster but can elect an available read-write replica as the new leader.

In relational databases, providing both high availability and high consistency in writes is a very hard problem. Spanner’s synchronous replication, the use of dedicated networking and Paxos voting provides high availability without compromising consistency.

High availability of reads vs writes

In traditional relational databases (e.g. MySQL or PostgreSQL), scaling and providing higher availability to reads is easier than writes. Read-only replicas provide a copy of the data read-only transactions can retrieve from. Data is replicated to the read-only replicas from a read-write master either synchronously or asynchronously.

In synchronous models, master synchronously writes to the read replicas at each write. Even though this model ensures that read-only replicas always have the latest data, it makes the writes quite expensive (and causes availability issues for writes) because the master has to write to all available replicas before it returns.

In asynchronous models, read-only replicas get the data from a stream or a replication log. Asynchronous models make writes faster but introduce a lag between the master and the read-only replicas. Users have to tolerate the lag and should be monitoring it to identify replication outages. The asynchronous writes make the system inconsistent because not all the replicas will have the latest version of the until asynchronous synchronization is complete. The synchronous writes make data consistent by ensuring all replicas got the change before a write succeeds.

Horizontally scaling reads by adding more read replicas is only part of the problem. Scaling writes is a harder problem. Having more than one master is introducing additional problems. If a master is having outage, other(s) can keep serving writes without users experiencing downtime. This model requires replication of writes among masters. Similar to read replication, multi-master replication can be implemented asynchronously or synchronously. If implemented synchronously, it often means less availability of writes because a write should replicate in all masters and they should be all available before it can succeed. As a tradeoff, multi-master replication is often implemented with asynchronous replication but it negatively impacts the overall system by introducing:

• Looser consistency characteristics that violate ACID promises.
• Increased risk of timeouts and communication latency.
• Necessity for conflict resolution between two or more masters if conflicting updates happened but not communicated.

Due to the complexity and the failure modes multi-master replication introduces, it’s not a commonly preferred way of providing high availability in practice.

As an alternative, high-availability clusters are a more popular choice. In this model, you’d have an entire cluster that can take over when the primary master goes down. Today, cloud providers implement this model to provide high availability features for their managed traditional relational database products.

Topology

Spanner doesn’t use high availability clusters but approaches to the problem from a different angle. A Spanner cluster* contains multiple read-write, may contain some read-only and some witness replicas.

• Read-write replicas serve reads and writes.
• Read-only replicas serve reads.
• Witnesses don’t serve data but participate in leader election.

Read-only and witness replicas are only used for multi-regional Spanner clusters that can span across multiple geographical regions. Single region clusters only use read-write replicas. Each replica lives in a different zone in the region to avoid single point of failure due to zonal outages.

Splits

Spanner’s replication and sharding capabilities come from its splits. Spanner splits data to replicate and distribute them among the replicas. Split happens automatically when Spanner detects high read or high write load among the records. Each split is replicated and has a leader replica.

When a write arrives, Spanner finds the split the row is in. Then, we look for the leader of that split and route the write to the leader. This is true even in multi-region setups where user is geographically closer to another non-leader read-write replica. In the case of an outage of the leader, an available read-write replica is elected as the leader and user’s write is served from there.

In order for a write to succeed, a leader needs to synchronously replicate the change to the other replicas. But isn’t this impacting the availability of the writes negatively? If writes need to wait for all replicas to succeed, a replica can be a single point of failure because writes wouldn’t succeed until all replicas replicate the change.

This is where Spanner does something better. Spanner only requires a majority of the Paxos voters to successfully write. This allows writes to succeed even when a read-write replica goes down. Only the majority of the voters are required not all of the read-write replicas.

Synchronous replication

As mentioned above, synchronous replication is hard and impacts the availability of the writes negatively. On the other hand when replication happens asynchronously, they cause inconsistencies, conflicts and sometimes data loss. For example, when a master becomes unavailable due to a networking issue, it may still have committed changes but might have not delivered them to the secondary master. If the secondary master updates the same records after a failover, data loss can happen or conflict resolution may be required. PostgreSQL provides a variety of replication models with different tradeoffs. The tradeoffs summary below can give you a very high level idea of how many different concerns to worry about when designing replication models. A summary of various PostgreSQL replication models and their tradeoffs:

Spanner’s replication is synchronous. Leaders have to synchronously communicate with other read/write replicas about the change and confirm it in order for a write to succeed.

Two-phase commit (2PC)

While writes only affecting a single split uses a simpler and faster protocol, if two or more splits are required for a write transaction, two-phase commit (2PC) is executed. 2PC is infamously known as “the anti-availability protocol” because it requires participation from all the replicas and any replica can be a single point of failure. Spanner still serves writes even if some of the replicas are unavailable, because only a majority of voting replicas are required in order to commit a write.

Network

Spanner is a distributed system and is inherently affected by problems that are impacting distributed systems in general. Networking itself is a factor of outages in distributed systems. On the other hand, Google cites only 7.6% of the Spanner failures were networking related. Spanner’s 99.999% availability is not highly affected from networking outages. This is mostly because it runs on Google’s private network. Years of operational maturity, reserved resources, having control over upgrades and hardware makes networking not a significant source of outages. Eric Brewer’s earlier article explains the role of networking in this case more in detail.

Colossus

Spanner’s durability guarantees come from Google’s distributed file system, Colossus. Spanner also mitigates some more risk by depending on Colossus. The use of Colossus allows us to have the file storage decoupled from the database service. Spanner is a “shared nothing” architecture and because any server in a cluster can read from Colossus, replicas can recover quickly from whole-machine failures.

Colossus also provides replication and encryption. If a Colossus instance goes down, Spanner can still work on the data via the available Colossus instances. Colossus encrypts data and this is why Spanner provides encryption at rest by default out of the box.

Spanner read-write replicas hands off the data to Colossus where data is replicated for 3 times. Given there are three read-write replicas in a Spanner cluster, this means the data is replicated for 9 times.

Automatic Retries

As repeatedly mentioned above, Spanner is a distributed system and is not magic. It experiences more internal aborts and timeouts than traditional databases when writing. A common strategy in distributed systems in order to deal with partial and temporary failures is to retry. Spanner client libraries provide automatic retries for read/write transactions. In the following Go snippet, you see the APIs to create a read-write transaction. The client automatically retries the body if it fails due to aborts or conflicts:

import "cloud.google.com/go/spanner"
_, err := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
    // User code here.
})

One of the challenges of developing ORM framework support for Google Cloud Spanner was the fact most ORMs didn’t have automatic retries, therefore their APIs didn’t give developers a sense that they shouldn’t maintain any application state in the scope of a transaction. In contrast, Spanner libraries care a lot of retries and make an effort to automatically deliver them without creating extra burden to the user.

Spanner approaches to sharding and replication differently than traditional relational databases. It utilizes Google’s infrastructure and fine-tunes several traditionally hard problems to provide high availability without compromising consistency.

---

• (*) Google Cloud Spanner’s terminology for a cluster is an instance. I avoided to use “instance” because it is an overloaded term and might mean “replica” for the majority of the readers of this article.
• (**) The write is routed to the split leader. Read the Splits section for more.

React vs Vue: The Core Similarities and Differences

As one of the most dynamic fields in software development, front-end evolves constantly. So much so that it sometimes seems impossible to keep up with its many emerging trends and tools. The adoption of ES6 and the functional nature of JavaScript saw people abandon the imperative universe of jQuery. Instead, they embraced the declarative, component-based way of structuring applications.

As of 2019, three players dominate the front-end battleground — React, Angular, and Vue. According to “State of JavaScript 2018,” React and Vue have the highest satisfaction rate. These are also the technologies are use the most. Facebook backs React and Netflix, Airbnb, Instagram, and many others use it. Evan You and a small core team created Vue and You still backs it. Alibaba, Wizzair GitLab, and others use it.

React vs Vue: Similarities

Before we discuss the core differences in React vs Vue, let’s note the traits that they have in common.

One of the most significant common traits is the use of Virtual DOM. The Virtual DOM is an abstraction of the HTML DOM where every HTML element is a JavaScript object. This approach offers some performance gain and enables DOM manipulation in a more declarative fashion.

Here’s an example of the Virtual DOM:

<div class="container">

<img class="img-responsive" />

</div>

<script>

// below you will find an example of Virtual DOM representation of the HTML code above

const domNode = {

tag: 'div',

attributes: { className: 'container' },

children: [

{

tag: 'img',

attributes: { className: 'img-responsive' },

children: []

}

]

};

</script>

React and Vue are both lightweight, possess component-based architecture, and expose lifecycle methods. Their performance is fairly similar so those differences are too negligible to discuss. Both technologies work with any existing web application, even if it’s not a Single Page Application. This is the case with the Gutenberg editor. It’s built with React and was recently implemented in the WordPress ecosystem. Similar is the case with GitLab, where the jQuery codebase is being gradually replaced with Vue.

Last but not least, both React and Vue have big proactive communities and plenty of libraries and tools available.

React vs Vue: Differences

Let’s begin with the most obvious distinction: React is a library, whereas Vue is a framework*.

*The official docs describe Vue as a framework and React as a library. The difference between framework and library is subtle and remains open for interpretations.

React allows DOM manipulation, component architecture, and state management (the component-level state, not Redux). All the rest gets left up to the community. This approach offers a lot of freedom for the developers. Many React applications rely on third-party libraries, built, and supported by the community. The choice for the right one is a challenge for beginners.

Vue comes with a lot of syntactic sugars, plugin system, built-in directives, transitions, etc. Plus, the core team created companion libraries for routing and state management along with other helpful tools. Some examples of such companion libraries are the Vue-router, Vuex for state management, and Vue CLI.

Of course, users are not obliged to use these tools as there are some alternatives. The main benefit is that these are built and supported by the core team. Vue’s main focus is simplicity. The team takes care of these common concerns and enables faster setup and development. Vue quickly caught up to React and the community built plenty of third-party libraries and enriched its ecosystem.

Data Mutation

In most modern web applications, JavaScript handles a big part of the logic in the browser. To maximize the web app’s interactivity, the UI should constantly react to data changes. Usually, we refer to the application’s data as “state”. Most often, the state is a JavaScript object where key/value pairs represent that data. In JavaScript, objects and arrays are reference types, hence mutable by design.

For instance, if we change one of the properties on an object, the object keeps its memory location, unlike with strings and numbers. Read more about that here. One of the biggest contrast between React and Vue is the way they handle state change. This heavily affects the mechanism behind the UI updates, also known as re-rendering.

React promotes the functional programming (FP) style. It implements FP principles, such as higher-order functions, immutability, pure functions, etc. The philosophy behind React is that the state remains immutable. When trying to mutate the state object, no re-rendering occurs. In order to trigger re-rendering, the method setState should be used. This updates not only the root component but the entire component sub-tree as well. The re-rendering process can be controlled by using PureComponent or shouldComponentUpdate lifecycle hook. That flexibility comes at a cost, though, so all the optimizations should be done manually. This makes the data flow more predictable. Overall, React gives developers a lot of control over the re-rendering process.

HERE’S AN EXAMPLE IN THIS DEMO REACT APPLICATION I MADE.

In Vue, the state is represented in the data object. Unlike React, the mutation of the state object triggers re-rendering. You can find an example in this demo Vue application I made.

However, there are some gotchas. For example, mutating nested objects or arrays might not trigger re-rendering. In this case, we either use the Vue.set method (similar to the setState method in React) or make the changes in immutable fashion with Object.assign or the ES6 spread operators. This might confuse beginners but this guide about Vue’s internal mechanism should help.

Vue automatically performs optimizations to update specific parts of the component tree but doesn’t offer a manual way to prevent re-renders. In React, the developer decides when and where to manually prevent them.

Templating and Styling

Templates and styles are essential parts of every UI library. They’re also where the differences between React and Vue are most obvious since it affects the code design. There’s a large contrast in the way both technologies approach it.

As mentioned above, React relies heavily on its functional, JavaScript-y nature. The logic and the markup are regarded as a whole and therefore are mixed. This is achieved with the use of JSX which is an abstraction of React.createElement method and is used to create Virtual DOM entities. The syntax resembles HTML with some significant differences. It offers smoother developer experience, debugging, and better code readability than the createElement method. You can also use React without JSX.

In terms of styling, the React community came up with different solutions like JSS and Styled components. This is a typical example of the freedom and the rich choices that the React community offers. For years, they addressed the CSS`s flaws and came up with great — revolutionary even — ideas. Personally, I’m a big fan of Styled components.

Vue takes a more conservative approach to templates and styels — one separated from the logic. It represents the markup as templates that look just like old school HTML. In fact, every valid HTML is also a valid Vue template. Inside the templates, the framework offers a lot of syntactic sugars like conditionals, iterations, etc.

The same is true for the way Vue handles styling. You can write pure CSS or any preprocessor in the style tag. In addition, the „scoped” attribute allows styles encapsulation on component level. In general, Vue styling feels more natural to newcomers but lacks some of the flexibility that the CSS-in-JS solutions from the React ecosystem offer.

Extensibility

Extending Vue or React applications with third-party libraries is quite simple. Most of the vendor libraries for React are simply components that enhance the existing ones already. For instance, the React-Redux library uses the Context API and exposes a higher-order component that makes state accessible from every component of choice.

In Vue, many of the third-party libraries are plugins and take advantage of the built-in plugin system. Add the plugins via the Vue.use method.

React vs Vue Summary

Both technologies offer great advantages. Being a library, React gives more control to its users, like the manual re-rendering control. It heavily employs the functional programming principles, indicated in the way the library handles the state and the communication between the components. In contrast, Vue as a framework provides more built-in features and companion libraries from the core team. This makes the development experience smoother.

If you’d like to try React out for yourself, you can start with Create React App. It’s a great tool for generating React projects without all the boilerplate. If you want to build a Vue project, you can use Vue-CLI, which offers great options out of the box.

Additional contributions and edits by MentorMate Senior Front-End Developer Rosen Kanev. 

10 Best Practices for Better RESTful API

Web APIs have become a very important topic in the last year. We are working every day with different backend systems and therefore we know about the importance of clean API design.

Typically we use a RESTful design for our web APIs. The concept of REST is to separate the API structure into logical resources. There are used the HTTP methods GET, DELETE, POST and PUT to operate with the resources.

These are 10 best practices to design a clean RESTful API:

1. Use nouns but no verbs

For an easy understanding use this structure for every resource:

Resource	GET read	POST create	PUT update	DELETE
/cars	Returns a list of cars	Create a new car	Bulk update of cars	Delete all cars
/cars/711	Returns a specific car	Method not allowed (405)	Updates a specific car	Deletes a specific car

Do not use verbs:

/getAllCars
/createNewCar
/deleteAllRedCars

2. GET method and query parameters should not alter the state

Use PUT, POST and DELETE methods instead of the GET method to alter the state.
Do not use GET for state changes:

GET /users/711?activate or
GET /users/711/activate

3. Use plural nouns

Do not mix up singular and plural nouns. Keep it simple and use only plural nouns for all resources.

/cars instead of /car
/users instead of /user
/products instead of /product
/settings instead of /setting

4. Use sub-resources for relations

If a resource is related to another resource use subresources.

GET /cars/711/drivers/ Returns a list of drivers for car 711
GET /cars/711/drivers/4 Returns driver #4 for car 711

5. Use HTTP headers for serialization formats

Both client and server, need to know which format is used for the communication. The format has to be specified in the HTTP-Header.

Content-Type defines the request format.
Accept defines a list of acceptable response formats.

6. Use HATEOAS

Hypermedia as the Engine of Application State is a principle that hypertext links should be used to create better navigation through the API.

01

{

02	"id": 711,

03	"manufacturer": "bmw",

04	"model": "X5",

05	"seats": 5,

06	"drivers": [

07

{

08	"id": "23",

09	"name": "Stefan Jauker",

10	"links": [

11

{

12	"rel": "self",

13	"href": "/api/v1/drivers/23"

14

}

15

]

16

}

17

]

18

}

7. Provide filtering, sorting, field selection and paging for collections

Filtering:

Use a unique query parameter for all fields or a query language for filtering.

GET /cars?color=red Returns a list of red cars
GET /cars?seats<=2 Returns a list of cars with a maximum of 2 seats

Sorting:

Allow ascending and descending sorting over multiple fields.

GET /cars?sort=-manufactorer,+model

This returns a list of cars sorted by descending manufacturers and ascending models.

Field selection

Mobile clients display just a few attributes in a list. They don’t need all the attributes of a resource. Give the API consumer the ability to choose returned fields. This will also reduce the network traffic and speed up the usage of the API.

GET /cars?fields=manufacturer,model,id,color

Paging

Use limit and offset. It is flexible for the user and common in leading databases. The default should be limit=20 and offset=0

GET /cars?offset=10&limit=5

To send the total entries back to the user use the custom HTTP header: X-Total-Count.

Links to the next or previous page should be provided in the HTTP header link as well. It is important to follow this link header values instead of constructing your own URLs.

Link: ; rel="next",
; rel="last",
; rel="first",
; rel="prev",

8. Version your API

Make the API Version mandatory and do not release an unversioned API. Use a simple ordinal number and avoid dot notation such as 2.5.

We are using the url for the API versioning starting with the letter „v“

/blog/api/v1

9. Handle Errors with HTTP status codes

It is hard to work with an API that ignores error handling. Pure returning of a HTTP 500 with a stacktrace is not very helpful.

Use HTTP status codes

The HTTP standard provides over 70 status codes to describe the return values. We don’t need them all, but there should be used at least an amount of 10.

200 – OK – Everything is working
201 – OK – New resource has been created
204 – OK – The resource was successfully deleted
304 – Not Modified – The client can use cached data
400 – Bad Request – The request was invalid or cannot be served. The exact error should be explained in the error payload. E.g. „The JSON is not valid“
401 – Unauthorized – The request requires an user authentication
403 – Forbidden – The server understood the request, but is refusing it or the access is not allowed.
404 – Not found – There is no resource behind the URI.
422 – Unprocessable Entity – Should be used if the server cannot process the entity, e.g. if an image cannot be formatted or mandatory fields are missing in the payload.
500 – Internal Server Error – API developers should avoid this error. If an error occurs in the global catch blog, the stracktrace should be logged and not returned as response.

Use error payloads

All exceptions should be mapped in an error payload. Here is an example of how a JSON payload should look like.

01

{

02	"errors": [

03

{

04	"userMessage": "Sorry, the requested resource does not exist",

05	"internalMessage": "No car found in the database",

06	"code": 34,

07	"more info": "http://dev.mwaysolutions.com/blog/api/v1/errors/12345"

08

}

09

]

10

}

10. Allow overriding HTTP method

Some proxies support only POST and GET methods. To support a RESTful API with these limitations, the API needs a way to override the HTTP method.

Use the custom HTTP Header X-HTTP-Method-Override to override the POST Method.